Data safety

Status: designed, not implemented. Recorded now because retrofitting is expensive and because the exposure below is real today.

This is the agency's operating system. What it holds — verified client facts, raw transcripts, approved messaging, finished creative, eventually footage — is largely irreplaceable. A source transcript is a recording of a conversation that happened once.

The exposure, today

Two facts that combine badly:

1. Thirteen tables cascade-delete from clients: brand_kits, strategies, features, content_sources, brand_messaging, escalations, reconciliations, extraction_conflicts, scenes, creatives, media_assets, media_projects.
2. The application connects as postgres — the superuser.

So a single delete from clients where slug = '…' destroys that client's entire history: every verified fact, every citation, every raw transcript, every angle, every creative. No confirmation, no soft state, and the current backup granularity is daily — so up to a day of work goes with it.

Nothing in the UI does this. But the capability is one query away, and this system is explicitly meant to be driven by an agent over MCP.

The fix, in priority order

1. A least-privilege application role. Stop connecting as postgres. Grant select, insert, update on the application tables and revoke delete and truncate entirely.

This is the same principle as the on-strategy trigger: make the bad outcome *impossible by construction* rather than forbidden by convention. Deletion then requires a deliberate administrative connection with different credentials, which is exactly the friction it should have.

2. restrict instead of cascade for irreplaceable rows. content_sources above all — those are the recordings. A cascade is right for derived data and wrong for source material.

3. Retire, never delete, everywhere. Already true for features and message_angles. Should be the pattern for every table a human can act on, because a claim that turned out to be wrong is the history most worth keeping.

4. Destructive MCP tools stay absent. mcp-tools.ts currently exposes none, which is correct and should be deliberate rather than accidental. An agent driving the portal must not be able to remove anything.

5. An audit trail — who changed what status, when. Cheap while volumes are small.

Backups: three copies, two media, one offsite

The gap is the same in both rows: our only independent database copy sits on the machine it is protecting. If the droplet is lost, we fall back to Supabase's managed backups, which is a real safety net but not one we control or verify.

Fix: ship the nightly dump offsite immediately after verify-backup.sh passes, and never treat a backup as valid until a restore has been exercised.

Also worth enabling: Supabase PITR. Daily granularity means a bad afternoon costs the whole day. PITR is a paid add-on beyond Pro and is the difference between losing a day and losing a minute.

Where Google Drive fits — and where it does not

Alex asked about relying on a third party like Drive rather than only our servers. The instinct is right; the placement matters.

Drive is a poor primary store for a programmatic system: no S3-compatible API, no presigned upload URLs, per-file rate limits and daily quotas, a folder model that gets fragile at scale, and service-account storage that counts against a user's quota. Building the asset pipeline on it would fight the platform.

Drive is a good archive and a good human surface: it is already paid for, browsable by the team, and shareable with clients without us building a delivery mechanism. Finished deliverables, raw footage archives, and a copy of each nightly dump all belong there.

So: R2 as the working store, Drive as the archive and the client-facing surface. Not a choice between them — two roles. Using a second, unrelated provider for the cold copy is itself the point: a mistake or an account problem at one does not reach the other.

The honest ranking

The thing most likely to lose data here is not disk failure or a provider outage. It is us, deleting something on purpose that turns out to matter. That is why the least-privilege role is first on the list and the offsite copy is second.