Your schema.sql Is Fiction: The Missing ON DELETE CASCADE That Kept 'Deleted' Data Alive

The setup

A user filed an issue: "I can't delete my account." Fair — there was no account deletion. GDPR-shaped hole, my fault, so I built it. The store method deleted the users row and trusted the foreign keys: our schema.sql declares ON DELETE CASCADE on every child table. Code review passed. Syntax checked. The SQL was correct.

The test I almost skipped

Before shipping, I ran an end-to-end test against production: signed up a disposable account, filled it with real data across every table (facts, events, workflows, embeddings), deleted it through the new endpoint — and then audited every table row-by-row with direct SQL.

Result: api_keys: 1, entities: 4, usage_log: 1 — still there.

The schema file is fiction. The database is fact.

Production had no cascade constraints at all . The schema file declares them — but production tables were created over months by incremental migrations (CREATE TABLE IF NOT EXISTS ..., ALTER TABLE ADD COLUMN ...) that never included the foreign keys. The pristine schema.sql is what a fresh install gets. Production is what history gets.

It got worse. If cascades never worked, what about the regular "delete entity" feature we'd had for months? One audit query across the whole database later:

12,053 orphaned facts. 442 orphaned embeddings. Every entity deletion since launch had silently left its children behind. Users clicked a button that said "permanently delete" — the parent row vanished, the content stayed on disk, invisible to the API but very much alive.

For a product whose whole pitch is "trust me with your personal memory," that's about the worst class of bug there is.

The fixes

Deletion is now fully explicit — children before parents, 22 tables, one transaction, zero reliance on cascades. The endpoint returns per-table deletion counts so the user can verify.

Same treatment for single-entity and delete-all paths (they had the same disease).

Second e2e round with a fresh disposable account: zero residue in every table.

Lessons that generalize

Your schema file is fiction. The database is fact. Audit information_schema.table_constraints, not your repo.

"Syntax OK" and "code review passed" prove nothing about deletion. Only a row-level audit after a real delete does.

Test destructive paths against the real database (with a disposable account) — a fresh local install has exactly the constraints your production is missing, so local tests pass for the wrong reason.

Check your own prod — this one-liner lists FK constraints and their delete rules:

SELECT conrelid::regclass AS table, conname,<br>CASE confdeltype WHEN 'c' THEN 'CASCADE' WHEN 'a' THEN 'NO ACTION'<br>WHEN 'r' THEN 'RESTRICT' WHEN 'n' THEN 'SET NULL' END AS on_delete<br>FROM pg_constraint WHERE contype = 'f' ORDER BY 1;<br>If what you see doesn't match your schema file — welcome to the club, and go count your orphans.

Context: this is Mengram (an AI memory layer) — the account-deletion work, the audit, and both e2e rounds are in the public commit history.

Related articles

Try Mengram free

No credit card. Get your API key in 30 seconds.

Get API Key<br>Read Docs