Your upsert can succeed while quietly replacing a different award product.

Tonight's import needs to update rates. A uniqueness constraint on issuer, originRegion, destinationRegion, and cabin looks reasonable. The uncomfortable part is that the database can accept it without protecting the product you meant to store.

Issuer, requested regions, and cabin don't uniquely identify an award-chart row. Native zones are finer than macro regions; products, distance bands, and published seasons can distinguish otherwise similar rows.

Fix the request basis before choosing a key

This Example input fixes the mileage basis and keeps every published product within the selected scope.

import { ApifyClient } from 'apify-client'; import { z } from 'zod'; import pg from 'pg'; const run_input = { originRegion: 'north_america', destinationRegion: 'europe', cabin: 'business', issuers: ['anaMileage', 'qantas'], oneWay: true, partnerOnly: false, awardType: 'any', maxResults: 1000, fallbackMode: 'live_only', includeStatusRecords: true, }; const client = new ApifyClient({ token: process.env.APIFY_TOKEN }); 

miles follows the requested basis. Changing oneWay changes what that value means, even when the native product stays identical. Put that basis in your identity namespace.

Otherwise, a round-trip import can overwrite a one-way value without changing the supposed product. Your rate alert then reports arithmetic as a commercial change. That's an avoidable page.

The requested regions need similar care. Distance-priced charts can match any macro pair, while native zones preserve distinctions that regional mapping compresses. These rows describe published chart products within a request scope, rather than airport routes.

Keep the remaining filters fixed for scheduled imports too. Narrowing a distance or product filter changes which rows you can observe. It doesn't establish that absent products have disappeared.

Read the whole dataset before deciding anything

Start the run, wait for completion, and read dataset pages until exhausted.

const started = await client .actor('nimbryxo-studio/airline-mileage-chart-redemption-rate-scraper') .start(run_input); const run = await client.run(started.id).waitForFinish(); if (!run) throw new Error('Run details unavailable'); const items: unknown[] = []; const pageSize = run_input.maxResults; for (let offset = 0; ; offset += pageSize) { const url = new URL( `https://api.apify.com/v2/datasets/${run.defaultDatasetId}/items`, ); url.search = new URLSearchParams({ format: 'json', clean: 'true', offset: String(offset), limit: String(pageSize), }).toString(); const response = await fetch(url, { headers: { Authorization: `Bearer ${process.env.APIFY_TOKEN}` }, }); if (!response.ok) throw new Error(`Dataset read failed: ${response.status}`); const page: unknown = await response.json(); if (!Array.isArray(page)) throw new Error('Expected a dataset array'); items.push(...page); if (page.length < pageSize) break; } 

Status records sit beside rate records and don't count toward maxResults. Treating the requested rate cap as the dataset length would therefore lose evidence about issuer coverage.

A transport failure stops this program before publication. A failed Actor run can still reach staging with whatever dataset it produced, but it can't update the current table.

Your scheduler should persist started.id immediately after starting. If only ingestion fails, resume that run's dataset. Starting another run creates another charged start and fetches a potentially different source revision.

The code holds this bounded dataset in memory because collision detection needs the complete candidate set. For a larger ingestion system, move grouping into a staging table. Don't replace whole-dataset checks with independent page upserts; competing products can arrive on different pages.

Validate fields without hiding new dimensions

Validate the known contract, preserve additive fields, and retain failures with their original JSON.

const text = z.string().min(1); const optText = text.nullish(); const optFlag = z.boolean().nullish(); const count = z.number().int().nonnegative(); const optDistance = count.nullish(); const issuer = text.refine(value => run_input.issuers.includes(value)); const rateSchema = z.object({ recordType: z.literal('mileage_chart'), issuer, issuerName: optText, originRegion: z.literal(run_input.originRegion), destinationRegion: z.literal(run_input.destinationRegion), cabin: z.literal(run_input.cabin), originZone: optText, destinationZone: optText, awardType: optText, tier: optText, direction: z.enum(['forward', 'reverse']), nativeDirection: z.enum(['one_way', 'round_trip']), miles: count, milesOneWay: count.optional(), milesRoundTrip: count.optional(), derivedFields: z.array(text).optional(), isPartner: z.boolean(), partnerName: optText, distanceBandMin: optDistance, distanceBandMax: optDistance, season: optText, isPeak: optFlag, isCombinedCabin: optFlag, milesNote: optText, sourceUrl: z.string().url(), sourceTitle: optText, sourceFormat: optText, sourceEffectiveDate: optText, retrievedAt: text.optional(), scrapedAt: text.optional(), verificationStatus: z.enum(['live', 'static_snapshot']), parserVersion: text, contentHash: z.string().regex(/^[a-f0-9]{16}$/i), }).passthrough(); const statusSchema = z.object({ recordType: z.literal('chart_status'), issuer, status: z.enum([ 'live', 'static_snapshot', 'blocked', 'no_chart', 'parse_error', 'requires_browser', ]), recordsReturned: count, statusMessage: z.string().optional(), }).passthrough(); const itemSchema = z.discriminatedUnion('recordType', [rateSchema, statusSchema]); type Rate = z.infer<typeof rateSchema>; const rates: Rate[] = []; const statuses: z.infer<typeof statusSchema>[] = []; const quarantined: { reason: string; raw: unknown }[] = []; const unexpectedFields = new Set<string>(); const knownFields = new Set(Object.keys(rateSchema.shape)); for (const raw of items) { const parsed = itemSchema.safeParse(raw); if (!parsed.success) { quarantined.push({ reason: parsed.error.message, raw }); continue; } const row = parsed.data; if (row.recordType === 'chart_status') statuses.push(row); else { rates.push(row); Object.keys(row).forEach(field => { if (!knownFields.has(field)) unexpectedFields.add(field); }); } } 

This is a consumer contract, including deliberate requirements for provenance. A record that can't satisfy it stays out of the current table, even if accepting it would make the import look healthier.

Strict validation rejects every added field. Plain passthrough accepts additions without asking whether they change identity. Here, passthrough preserves the evidence while an unfamiliar rate field holds publication for review.

That matters for award products. A newly exposed product distinction could mean your candidate key is incomplete. Silently stripping the field would make the payload appear simpler precisely when it needs more scrutiny.

Optional identity fields remain optional because not every chart uses distance bands or seasons. Their absence is preserved, rather than filled with a guessed value.

Challenge the candidate key before trusting it

There's no universal row identifier to adopt. Build a versioned candidate from the priced product, then test whether it separates the returned rows.

const identityFields = [ 'issuer', 'originRegion', 'destinationRegion', 'originZone', 'destinationZone', 'cabin', 'awardType', 'tier', 'distanceBandMin', 'distanceBandMax', 'season', 'isPeak', 'isPartner', 'partnerName', 'isCombinedCabin', 'nativeDirection', 'direction', ] as const; function candidateKey(row: Rate): string { return JSON.stringify([ 'award-key:v1', run_input.oneWay, ...identityFields.map(field => row[field] ?? null), ]); } function canonical(value: unknown): string { if (value === null || typeof value !== 'object') return JSON.stringify(value) ?? 'null'; if (Array.isArray(value)) return `[${value.map(canonical).join(',')}]`; return `{${Object.entries(value) .filter(([, v]) => v !== undefined) .sort(([a], [b]) => a < b ? -1 : a > b ? 1 : 0) .map(([k, v]) => `${JSON.stringify(k)}:${canonical(v)}`).join(',')}}`; } function fingerprint(row: Rate): string { const payload = { ...row }; delete payload.retrievedAt; delete payload.scrapedAt; return canonical(payload); } const groups = new Map<string, Rate[]>(); for (const row of rates) { const key = candidateKey(row); groups.set(key, [...(groups.get(key) ?? []), row]); } const batch: { key: string; raw: Rate }[] = []; for (const [key, group] of groups) { const first = group[0]; if (!first) continue; if (new Set(group.map(fingerprint)).size === 1) batch.push({ key, raw: first }); else quarantined.push({ reason: `Candidate-key collision: ${key}`, raw: group }); } 

miles stays outside identity. Including the price would turn every rate change into a new product and leave the previous price sitting beside it.

Retrieval timestamps and contentHash also stay outside the key. The hash describes fetched source content. A changed source should trigger comparison, rather than rename every product it contains.

The fingerprint ignores retrieval timestamps but retains other differences, including provenance. Conflicting evidence gets reviewed instead of resolved by arrival order. A conservative hold is cheaper than investigating a plausible, incorrect rate.

validated rates | candidate-key groups | +-- equivalent payloads --> collapse repeats | +-- conflicting payloads --> quarantine and hold publication 

Normalizing missing dimensions to null makes the key deterministic. It doesn't prove those dimensions are irrelevant.

Within-import collision checks also have a boundary: they can't catch every identity error across imports. If a missing distinction produces different products on different nights, neither import necessarily contains a collision.

Native labels have another cost. A zone or product rename can create a new key. Confirm the equivalence and migrate it explicitly; fuzzy matching can recreate the overwrite problem with better spelling.

Keep searchable columns beside the evidence

Use typed columns for operational comparisons and JSONB for the accepted record.

Storage choice Useful when Cost
Typed columns Filtering products and comparing numeric rates Schema changes require deliberate maintenance
Raw JSONB Inspecting provenance and preserving additive fields Queries need more care
Both Serving current rates with inspectable evidence Some values occupy storage twice

The current table needs convenient comparison columns, not a typed copy of every field. Promote additional dimensions when real queries justify indexes.

The import table doubles as durable staging. It preserves candidates, statuses, validation failures, and publication checks when the current table stays untouched.

Define the tables once through this setup statement.

const schemaSql = ` CREATE TABLE IF NOT EXISTS award_rate_current ( candidate_key text PRIMARY KEY, issuer text NOT NULL, origin_region text NOT NULL, destination_region text NOT NULL, cabin text NOT NULL, miles bigint NOT NULL CHECK (miles >= 0), raw jsonb NOT NULL, last_run text NOT NULL ); CREATE TABLE IF NOT EXISTS award_import ( run_id text PRIMARY KEY, publishable boolean NOT NULL, raw jsonb NOT NULL ); CREATE TABLE IF NOT EXISTS award_rate_event ( run_id text NOT NULL, candidate_key text NOT NULL, flags jsonb NOT NULL, PRIMARY KEY (run_id, candidate_key) );`; 

Retaining raw imports forever is a separate storage decision. Set a retention policy that matches your audit needs, and keep the current table independent of that cleanup.

This schema stores present observations. It deliberately has no automatic deletion path.

Commit the batch only when the scope is trustworthy

Require successful execution, live issuer coverage, clean validation, and an unsaturated rate cap before publishing.

const checks = { succeeded: run.status === 'SUCCEEDED', issuersLive: run_input.issuers.every(issuer => { const found = statuses.filter(row => row.issuer === issuer); return found.length === 1 && found[0]?.status === 'live'; }), belowCap: rates.length < run_input.maxResults, clean: quarantined.length === 0, knownShape: unexpectedFields.size === 0, liveRates: rates.every(row => row.verificationStatus === 'live'), }; const publishable = Object.values(checks).every(Boolean); const publicationLock = 'award rates'; const pool = new pg.Pool(); try { await pool.query(schemaSql); const db = await pool.connect(); try { await db.query('BEGIN'); await db.query('SELECT pg_advisory_xact_lock(hashtext($1))', [publicationLock]); await db.query(` INSERT INTO award_import VALUES ($1, $2, $3::jsonb) ON CONFLICT (run_id) DO UPDATE SET publishable = EXCLUDED.publishable, raw = EXCLUDED.raw `, [started.id, publishable, JSON.stringify({ input: run_input, checks, statuses, quarantined, unexpectedFields: [...unexpectedFields], batch, })]); if (publishable) await db.query(` WITH incoming AS ( SELECT x.key, x.raw, (x.raw->>'miles')::bigint AS miles FROM jsonb_to_recordset($1::jsonb) AS x(key text, raw jsonb) ), events AS ( INSERT INTO award_rate_event SELECT $2, i.key, jsonb_build_object( 'rate_changed', c.miles IS DISTINCT FROM i.miles, 'parser_changed', (c.raw->>'parserVersion') IS DISTINCT FROM (i.raw->>'parserVersion'), 'source_changed', (c.raw->>'contentHash') IS DISTINCT FROM (i.raw->>'contentHash') ) FROM incoming i JOIN award_rate_current c ON c.candidate_key = i.key WHERE (c.miles, c.raw->>'parserVersion', c.raw->>'contentHash') IS DISTINCT FROM (i.miles, i.raw->>'parserVersion', i.raw->>'contentHash') ON CONFLICT (run_id, candidate_key) DO NOTHING ) INSERT INTO award_rate_current SELECT key, raw->>'issuer', raw->>'originRegion', raw->>'destinationRegion', raw->>'cabin', miles, raw, $2 FROM incoming ON CONFLICT (candidate_key) DO UPDATE SET miles = EXCLUDED.miles, raw = EXCLUDED.raw, last_run = EXCLUDED.last_run `, [JSON.stringify(batch), started.id]); await db.query('COMMIT'); } catch (error) {Source:https://dev.to/nimbryxo-studio/importing-airline-mileage-rates-in-typescript-without-mixing-mileage-bases-44g1

Related posts