Idempotency is the difference between "run the pipeline again" and "explain to the customer why they were billed twice."
In high-volume billing, files arrive late, partial, and out of order. Operators reprocess. Cron syncs hourly. Webhooks retry. Without idempotency keys you are trusting luck.
What idempotency means here
An idempotency key identifies one logical event across retries:
- Same key + same payload → no duplicate billable row.
- Same key + changed payload → conflict you must resolve deliberately.
- New key → new event, even if the row "looks similar."
Sonic never auto-checks every column in your sheet. You pick the columns (or formula) that uniquely identify a row in your export shape.
Usage templates
Usage ingestion templates define:
- Column → meter mappings
- Transforms (unit conversion, rounding)
- Customer resolution (
user_facing_id, name fuzzy match — not legacy object ids) - Idempotency key built from stable columns
The Process gate on usage mirrors seats in spirit: attaching a sample does not silently stage billable data. You explicit Process when sample + worksheet are ready. After success, mapping tweaks reprocess through their own controls without re-arming the whole gate unless the sample file changes.
Postgres sync uses the same template mind-set: a marker column with an index, incremental reads, no full-table replay each hour.
Seat snapshots
Seat uploads key imported daily rows by seat_snapshot_key — configured columns only, never "all columns" by default.
Already-imported snapshot rows skip insert while the running balance still advances. That lets you re-upload a month-end file without duplicating events for days 1–28 while still picking up day 29–31.
Webhook ingest is a synthetic JSON upload on the same path: identity miss fails before Process; routed records carry per-row outcomes after.
Idempotency column picker behaviour
When you switch samples or worksheets:
- Previous manual picks survive only if still valid on the new sheet.
- Columns that existed only on the old sheet drop — Sonic does not pretend they still apply.
- Sheet columns are shown as options; nothing auto-checks without operator intent.
This prevents silent key changes that turn a safe re-run into a duplicate storm.
Invoice-side idempotency
Ingestion idempotency is upstream. Downstream, invoice generation respects product-line identity on open drafts so regeneration after late events does not stack duplicate lines for the same product in the same period.
Sent invoices do not accept new lines at all — a different layer of safety.
Watch out for
- Keys must be stable, not clever. Transaction id beats "customer + date" if the vendor sends corrections with the same date.
- Test on a small file before hourly Postgres sync. Bad mapping × 24 runs is a long weekend.
- Changing a live template changes future reads only. History does not rewrite.
- Seat delta columns are not keys. They are not even import columns.
- Webhook JSON must match template field paths. Excel formulas in a JSON template are a smell.