Media operations

Run it again. The worst case is one batch written twice, with the same result.

A daily archive sync that can be restarted at any point, by anyone, without checking first whether it is safe.

Two versions of the same sync compared side by side. In version two, three batches are sent while all remain marked pending, the run is interrupted before the status update, and the next run sends all three again. In version three, batches one and two are sent and marked immediately, batch three is sent but not yet marked when the run is interrupted at the same point, and the next run re-sends only batch three. Below, a table of four versions and what each one fixed, with version three highlighted.
The same code, the same interruption. Only the position of the status write changes — and it decides whether the job is safe to run again.

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.

Case studies

More of the work

Have a similar problem?

Tell us what the process looks like today. We will say whether this approach would work for you.