Two clean changes, nothing else touched. Here's the scrubbed post:

TLDR: if a sync job upserts on a shared key, every re-run can silently blow away manual edits. Add a per-record lock flag the importer checks before writing — and keep a separate admin-only endpoint so your own security projections don't hide the data from the people managing it.

The Setup

We're building a facility finder for a cancer education business, an AI-powered referral tool that helps patients connect with cancer treatment centers.

The facility data — 859 centers — lives in TakeShape (our headless CMS), and we pull it into Supabase on demand with npx tsx scripts/import-takeshape.ts.

For the webhook pipeline that fires consultation data to facility staff, we needed a new field on each facility: referral_recipients — a curated list of email addresses the webhook would actually send to.

The problem? That list had to be hand-curated by an admin, one facility at a time.

The Wall

Our importer does an upsert on name.

So every time someone runs the import — to pull in a new facility, update hours, anything — the upsert happily overwrites referral_recipients with whatever TakeShape has. Which is… nothing. TakeShape doesn't know about our webhook emails.

There's no dramatic data loss story here. We caught it before it burned us. But the risk was obvious: anyone running a routine import would silently nuke hours of admin curation.

What We Tried First

My first instinct was "just don't import that field" — skip referral_recipients in the upsert payload entirely.

That works until you want TakeShape to also manage some recipient state. And it doesn't generalize — any field you want to protect needs its own carve-out logic scattered across the importer.

Not great.

The Fix That Worked

We added a second column: recipients_locked_by_admin bool.

The importer now checks the flag before writing. If a record is locked, it uses a destructure to omit locked referralRecipients — the import just skips that field entirely for that facility.

On the admin side, we added a UI toggle in the facility modal. When an admin finishes editing recipients and checks the lock box, the PUT route sets the flag. From that point on, no import can touch those emails unless an admin explicitly unlocks.

recipients_locked_by_admin bool  -- admin-edit lock; importer skips when true
referral_recipients jsonb[]       -- curated webhook emails, default []

Clean, explicit, per-record control. 229 of our 859 facilities ended up with data in that column within the first few days.

The Twist (Don't Do This)

We also ran a security audit around the same time that added a toPublicFacility projection — stripping internal fields like referral_recipients from public API responses.

Good idea. Except: the admin facilities tab was still fetching from the public /api/facilities endpoint.

Result? Every facility modal opened with empty recipient rows even though the data was sitting right there in the DB.

The fix was a dedicated GET /api/admin/facilities route — auth-gated, returns raw rows, no projection.

It's worth logging as its own lesson: your read-side security projections can accidentally hide the fields you just worked hard to protect. Admins and public consumers should never share an endpoint when the field sets diverge.

Why This Matters to Me

Sync jobs are easy to reason about in isolation — import the record, upsert, done.

But the moment a human starts editing a field that a sync also touches, you have a conflict model. Most apps ignore it until data disappears and someone is angry.

The lock-flag pattern is cheap — one boolean column, one if locked: skip check in the importer — and it makes human intent explicit and durable. The sync owns what it owns; the admin owns what they own; and the boundary is in the DB, not in someone's head.

Build that boundary early. It's nearly free before the first import clobbers something. It's not free after.