Context
A media organisation running content operations across several teams. Operational records
lived in separate tables across separate bases with no central archive, and an ordinary
Airtable table caps at 250,000 rows — which a few years of accumulation passes.
The problem
Beyond capacity, two things were missing.
Nothing knew where it came from. Records aggregated from several sources arrived in one
place with no trace of their origin, so you could not compare one source against another,
diagnose a source sending bad data, or re-sync one source without touching the rest.
And any sync that stops halfway is a decision. Either it is safe to run again, or
somebody has to work out what got through before anyone touches it. The second is how data
gets written twice.
What we built
A small daily job — a few hundred lines, no framework, scheduled in CI with no state of its
own. What matters is the order of operations, not the code.
- Every write is an upsert on a single primary key, never an insert. Re-running is safe
by design rather than by luck.
- Sync status is marked after each batch, not at the end of the run. This was the fix in
the third version, and the reason is not performance: an interruption used to leave
batches that had already been sent still marked as pending, so the next run sent them
again. Marking per batch makes the worst case one duplicated batch instead of everything
since the last checkpoint.
- Two source-tracking fields on every row — which table it came from, and its identifier
there. Added in the fourth version and backfilled across the existing archive by a one-off
job. Without them an aggregated record is anonymous.
- An explicit field map in one constant, so the shape of the transformation is readable
in one place rather than inferred from the code.
The constraint that cost two versions. The archive store does not use the ordinary table
endpoint. It differs in the verb, the path and the identifier space — all three at once.
The ordinary endpoint returns a plain 404, which is the hardest failure to diagnose: a “not
found” on a path that looks entirely correct sends you hunting through permissions and
identifiers rather than questioning the shape of the path itself.
The result
The job runs daily and unattended. It can be re-run at any point without an investigation
first. Every archived row knows its origin, so a single source can be compared, diagnosed or
re-synced on its own.
Restartability is not a feature you add afterwards. It is a property of the order you do
things in.