On this page
In short#
Data travels from a vendor API, sheet tab or file into Bronze, is promoted to typed Silver facts and rolled up into Gold; a re-run lands on the same numbers. Failures are classified as permanent, transient or parked.
Four mechanisms make re-runs converge: content-hash deduplication in Bronze, natural-key upserts in Silver, watermarks that advance only on success, and a transactional outbox between web app and worker (System architecture).
Bronze: what lands first#
Bronze keeps the source bytes, so a replay re-derives everything downstream without calling a vendor. It is append-only: a correction is a later row, not an edit.
| Table | Holds | Deduplication |
|---|---|---|
raw_ |
Orders, creators, inventory: exact payload | sha256 of the key-sorted payload in a unique index; conflicts ignored |
import_ |
Every other stream: ad spend, payouts, leads, datasets | Dedupe key, plus template and version; queued, promoted, rejected or superseded |
Silver: typed facts#
Canonical marketplace streams upsert from the sync processor; every other stream goes through a named promoter. Idempotency is structural:
- Duplicates inside one batch collapse last-wins before the write, because Postgres refuses to update one conflict key twice in a statement.
- A chunked upsert applies
ON CONFLICT DO UPDATEon a declared natural key, for example organization, platform, shop and order id. Nullable key columns useNULLS NOT DISTINCT; otherwise a null-keyed row duplicates on every sync.
Marketplace facts update on conflict, so a re-pull of a closed day corrects a late-settling number.
Gold: what dashboards read#
The agg_* rollups are the read surface: organization, SKU and creator by day, shopper by month, campaign, content-intelligence and dataset-period rollups, refreshed nightly and on demand. The brand is in every rollup's unique key; without it a row becomes the sum of all brands, shown under every brand.
Watermarks and re-pulls#
Each connector stream keeps one watermark row: cursor, high-water mark, last success and backfill times, running row count. Order streams start five minutes before the cursor so a boundary order is never missed; the shoppable-video stream finalizes closed days once a day at 01:00 Vietnam time; a rolling-window third-party pull prunes its stale predecessors after each write.
The durable handoff#
The web app never publishes to the queue directly, so the state change and the intent to do work commit or roll back together. The dispatcher claims committed rows with FOR UPDATE SKIP LOCKED and publishes outside any transaction; a stable job id makes a crash between publish and mark replay-safe, and the claim predicate reclaims an expired lease.
Platform events use a separate outbox: platform_outbox rows are written with the change, and a worker sweep every minute claims and delivers them (Platform API and events).
Failure classification#
| Failure | Class | What happens |
|---|---|---|
| Unprocessable payload, authentication error, decrypt failure, template error, shop-to-brand mismatch, constraint violation | Permanent | One dead-letter row; retries stop |
| Vendor rate limit | Parked | Connector cools down, health shows degraded; no alert |
| Anything else | Transient | Retried with backoff |
A dead-letter row carries the payload, error code, message and attempt count; repeats per connector, job type and error code bump the count, and the next successful sync resolves that connector's open rows.
Uploads and mapping templates#
- Canonical streams (orders, creators, inventory, creator costs, targets, products) parse straight into Bronze.
- Every other stream uses a mapping template, data not code: source kind, target stream and one transform per column from a closed set. Its dedupe key must cover the stream's natural key, and an active template is immutable: an edit writes version N+1 and archives N (Import mapping template).
- XLSX exports often prepend a pivot sheet, so the decoder picks the largest sheet. Uploads are capped at 8 MiB.
- Google Sheets is the same contract over another transport, one template per tab; ranges are read concurrently under a hard cap, then merged in configured order.
Brand binding and shop ownership#
Every ingest write resolves its brand through one function that never defaults silently: a single-brand organization pins automatically; a missing, disagreeing or non-viewable brand is refused. A shop maps to exactly one brand: an unregistered shop is claimed by its first writer, and another brand's shop raises a permanent mismatch.
Queues and schedules#
Ingest work is spread over named queues: shared sync, per-marketplace, marketing sync, datasets, webhooks and manual upload. Schedulers are declared in code and upserted by name at boot; an undeclared one is removed, and every pattern is pinned to Vietnam time.
| Queues | Retries |
|---|---|
| Sync and manual upload | Five, exponential backoff from 30 seconds |
| Shorter operational queues | Two or three |
Where it stops#
Third-party estimates bypass Bronze, Silver and Gold and are never reconciled. Uploads above the cap are refused, and a new corpus shape may need operator help. A restatement outside a stream's trailing window is picked up only by a backfill. Connector readiness: Project status.
Decisions#
| Decision | Why | Enforced by |
|---|---|---|
| Durable async work is an outbox row in the business row's transaction; the worker publishes it | Direct publish either announces a rolled-back row or loses work when the process dies | Audit refuses direct publish and after-commit callbacks; lease and job id make replay safe |
| Permanent failures dead-letter once; transient ones back off; rate limits park the connector | Blanket retries bury one bad credential; blanket dead-letters turn blips into manual work | One classifier in connector core; dedupe key; refused dead-letter writes metered at zero |
Content-hash Bronze, natural-key Silver with NULLS NOT DISTINCT, watermarks on success |
Vendors restate days and jobs crash; a non-idempotent pipeline inflates every retry | Nullable-upsert audit, save-time dedupe check, CI promoters over adversarial fixtures |