Sync & Consistency

Five Systems, One Run, No Disagreement

Section 4 of 4

By this point in the pipeline the hard thinking is done: jobs are parsed, deduplicated, enriched, classified and routed. What remains is the part that quietly ruins products — getting the result into five different systems, each serving a different purpose, without them drifting apart.

Drift here is not an abstract concern. A job in the search index but deleted from the application API is a result that leads to a dead page. A job live in the application but missing from the warehouse is revenue that does not appear in reporting. A job archived in the database but still in the index is a closed role being actively recommended to people. Each of those is a user-visible failure whose root cause is invisible in any single system, because every system involved looks internally consistent.

5

systems reconciled per run

1

declared source of truth

250

records per bulk sync batch

100%

of runs produce a report, success or failure

Ordered Writes

The order is not incidental. Each stage depends on the one before it having completed, and the sequence is chosen so that an interruption anywhere leaves the systems in a recoverable state rather than a contradictory one.

Pipeline Runrouted jobs + the set of hashes that disappeared from the feed1 · BigQuery — analytics warehouseeverything, including newly archived records · stable identity reused per job2 · PostgreSQL — source of truthapproved, manual review and rejected written concurrently · stale rows archived3 · Xano — application APIreconciled to match PostgreSQL exactly — delete what is stale, add what is new4 · Upstash — search indexrebuilt from the authoritative list re-fetched from Xano, never from local state5 · Execution report, feed health, notificationwritten in a cleanup block — runs whether the pipeline succeeded or threw

Reconciliation as Set Arithmetic

Because every job has a stable content hash, keeping two systems in agreement reduces to comparing two sets of hashes. No change log, no per-record diffing, no timestamps to get wrong.

Hashes in PostgreSQLsource of truthHashes in Xanowhat the app currently servesonly localadd to Xanoin bothalready consistentleft untouchedonly remotedelete from XanoDeletions are propagated to the search index first, using the external identifiers Xano holds for each hash —because once a record is gone from Xano, the key needed to remove it from the index is gone with it.

The Decisions Behind the Sequence

Five systems that each hold job data is five systems that can disagree. The thing that makes that tractable is not clever merging — it is declaring, once, that PostgreSQL is authoritative and everything else is derived from it.

That single decision resolves every conflict in advance. If Xano holds a job PostgreSQL does not, Xano is wrong and the job is deleted. If PostgreSQL holds one Xano does not, Xano is wrong and it is added. There is never a question of which system to believe, and therefore never a merge strategy to get wrong. Reconciliation becomes mechanical.

The alternative — letting each system accumulate its own state and reconciling by timestamp or by last-writer-wins — is where distributed data systems go to become unmaintainable, because every inconsistency then requires a human to decide what actually happened.

Every mechanism on this page is readable

The ingestion, deduplication, enrichment and reconciliation code described across these four sections is public. If any of it is worth checking rather than taking on trust, it is all there.

Read the Source on GitHub