如何在 TypeScript 匯入航空裏數兑換費率而不混淆裏數基準
Importing airline mileage rates in TypeScript without mixing mileage bases
以 TypeScript 匯入航空裏數兑換費率時,應把單程或來回的裏數基準及獎賞產品維度納入識別,避免 upsert 覆寫不同產品的費率。匯入前須讀取完整 dataset 並檢查全量資料,不能只按分頁 upsert,或把篩選結果縮窄後便判定其他產品已消失。驗證時應保留新增欄位,並連同原始 JSON 保存未通過驗證的記錄。
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) {
await db.query('ROLLBACK');
throw error;
} finally {
db.release();
}
} finally {
await pool.end();
}
The batch statement avoids per-row database round trips. The transaction commits audit evidence and current-rate changes together. A database failure rolls both back, leaving the same dataset available for retry.
Reprocessing that dataset addresses the same candidate keys. Event uniqueness prevents duplicate drift events for its run. An unchanged product from a fresh run refreshes provenance without creating another product row.
Publication is deliberately all-or-nothing across this request. An unhealthy issuer holds the healthy issuer's updates too. That costs freshness, but keeps the comparison coherent.
If your consumers can tolerate mixed freshness, split scheduling and publication by issuer. Make that choice visible through import identifiers rather than quietly weakening the gate.
The database lock serializes publication. It can't establish which overlapping source fetch is newer. Keep a single scheduled writer per scope, and don't resume an obsolete import after a newer import has published.
Why can a successful import still be incomplete?
The worked example is Airline Mileage Chart Redemption Rate Scraper.
Its status records distinguish access or parsing failures from a live chart that matches no rows. A successful run alone therefore can't authorize replacing an issuer's existing data.
The cap creates another ambiguity. Reaching maxResults might mean truncation or an exactly full result. This recipe holds both cases rather than guessing.
The STATUS key-value record includes explicit truncation information. Consuming it can make the gate less conservative, but the integration above doesn't depend on an unspecified nested shape.
Missing rows also have legitimate causes. Unsupported cabins are skipped, and odd round-trip values aren't halved into one-way rows. A missing row is insufficient evidence that a stored product was withdrawn.
That's why this workflow upserts observed keys and retains absent ones. last_run lets consumers distinguish observations from a chosen import from older retained evidence. Showing every retained row as equally current would undo that distinction at the dashboard.
Deletion needs a complete comparison within the same request scope and an explicit retirement policy. Clean parsing and untruncated ingestion are prerequisites, not proof that every absence means withdrawal.
Alert separately on blocked publication, candidate collisions, and unfamiliar rate fields. Those signals threaten ingestion correctness.
Keep parser changes and source changes as diagnostic events. A changed content hash alone doesn't establish a changed award rate. Rate changes deserve their own downstream review, and published chart prices still don't establish bookable seat availability.
Before tonight's import
- Fix the request basis and prevent overlapping publication.
- Save the run identifier before ingesting its dataset.
- Review quarantined payloads before changing the candidate key.
- Hold uncertain coverage; retain absent rows with visible provenance.
- Route rate drift separately from parser and source drift.
- Approve label migrations and retirement rules explicitly.
When a collision appears, leave the previous rate in place. Arrival order isn't an award-product policy.
Drafted with AI assistance and reviewed for accuracy.
來源:DEV Community · dev.to