Why Content Sync Needs Source Ownership, Not Just Upserts

Teams often frame synchronization as a database question: choose a unique key, write an upsert, and let the latest payload win. That solves duplicate insertion, but it does not solve authority. Once an editor can revise a title, approve a draft, prepare platform variants, or publish a record, the database row is no longer a disposable copy of the source file.

The sharper model is not source versus destination, but field ownership plus state ownership. Code may own the initial candidate. Editors may own revised prose and review notes. A publication workflow owns whether the record is visible. Synchronization should move data only where that ownership contract permits it; otherwise a technically successful write can become silent content loss or an accidental release.

A broad upsert usually maps every incoming field onto every stored field. That is attractive because it is compact and repeatable. It also collapses four different events: discovering a new item, refreshing source-controlled metadata, proposing changes to an edited item, and changing editorial state. Those events have different owners and different recovery requirements.

Consider a record that entered the database as an approved draft and was then rewritten by an editor. A later build sees the same slug with updated source prose. If the source wins, the edit disappears. If the database always wins, legitimate source corrections never arrive. If the incoming payload also carries status, a sync can demote published content, reset review evidence, or publish a candidate without a human decision. The conflict is semantic, not syntactic; ON CONFLICT cannot decide it for the product.

The Portfolio Content & Growth Engine provides a public example. Static insight definitions are evaluated during deployment, while the managed content database holds editorial status, sections, platform variants, gate results, and the eventual publish decision. Public project context: portfolio projects.

Its daily intake path deliberately checks existing slugs and inserts only unseen records. A gate-passed item enters as Approved, which means ready for manual review rather than publicly visible. Existing records are skipped so a deployment cannot overwrite admin edits or publication state. This does not solve every future refresh case, but it establishes the critical boundary: automated generation can create a candidate; only the editorial workflow can make it public.

Start with a record contract such as {stable_id, source_revision, source_hash, source_fields, managed_fields, editorial_status, publication_revision, last_synced_at}. Use a stable identifier that does not change when the title changes. Classify each field as source-owned, editor-owned, derived, or state-owned. Do not let a single incoming object implicitly claim every category.

Make intake insert-only for new identities. For an existing identity, compare the incoming source revision or normalized hash with the last accepted source version. If nothing changed, skip. If source-owned metadata changed, update only those permitted fields. If prose changed after a human edit, create a Refresh needed state or a reviewable difference rather than overwriting the managed body. Publication state, approval evidence, analytics identity, and destination URLs remain under the managed workflow unless an explicitly authorized transition changes them.

Write the parent record, sections, variants, and gate evidence in one transaction or through an idempotent job with a durable receipt. One operating invariant should hold across every path: synchronization may create or flag content, but it cannot transition a record to Published. Another invariant is that a failed child write cannot leave a new parent looking complete. Record an audit event with the source revision, previous managed revision, fields changed, fields withheld, resulting state, and job id.

Insert-only synchronization protects edits, but it can also hide source corrections if there is no refresh path. Field-level ownership is safer, but it adds schema and review complexity. Hashes are useful for detecting change, yet unstable serialization or generated timestamps can create false differences. Slugs are convenient identities until a rename turns one article into two records or breaks canonical links.

Partial writes are another risk. A parent row can exist while sections or variants fail, making an incomplete item appear reviewable. Concurrent deployments can both observe absence before either inserts. Unique constraints, transactions, idempotent job keys, and retry-safe child writes are therefore part of the content contract. They do not replace editorial policy; they keep infrastructure failures from impersonating editorial decisions.

Test the sync with six synthetic cases: first insertion, identical replay, source metadata correction, source prose change after a human edit, slug rename, and failure halfway through child-record creation. Add a seventh case in which two sync jobs attempt the same new identity concurrently. For each case, capture the final body, status, canonical identity, child completeness, audit event, and whether a retry changes the result.

The contract fails if identical replay creates new work, a source refresh erases an editor change, a build changes publication state, a rename creates an untraceable duplicate, or a partial failure leaves the item looking complete. Useful measures include duplicate-block rate, refreshes awaiting decision, human edits preserved, incomplete-write recovery time, ambiguous ownership conflicts, and incidents where automated sync changed public visibility.

Once source ownership is explicit, the team stops treating the database as either a perfect mirror or an isolated editorial island. New content can arrive automatically, source corrections can be detected, editors can retain control of managed fields, and publication remains a separate accountable decision. The synchronization layer becomes narrower because it no longer pretends to resolve product policy through a generic write primitive.

Apply this tomorrow by choosing one synchronized record and labeling every field with its owner. Then replay the source after changing one editor-owned field and one source-owned field. If the system cannot preserve the first, accept the second, and leave publication state untouched, replace the broad upsert with an explicit intake-and-refresh workflow before adding more automation.