patternsqldockerTip
Erasable demo seed data in Postgres: track created rows in a side schema, verify erase by whole-schema fingerprint
Viewed 0 times
demo dataerasable seedfingerprint md5 tablemanifest schemaon delete set nullfixture cleanup
dockermacoslinux
Problem
You need to load realistic demo data into a shared local Postgres app database (with triggers that write history, unique-value tables, denormalized counters) and later remove exactly that data in one command, without touching rows that existed before or that a user created during the demo. Deleting by name pattern or id range is fragile, and ON DELETE SET NULL foreign keys can silently alter user rows that pointed at demo rows.
Solution
1) Load inside one transaction (psql --single-transaction -v ON_ERROR_STOP=1) and insert through the app's normal write path (same session settings/source markers the app uses) so its triggers fill history and derived tables. 2) In the same transaction, create a dedicated schema (e.g. demo_seed) with a table listing every created row id and kind, plus a 'loaded' marker row; no foreign keys from it to app tables, so the app schema stays untouched. Refuse to load twice. 3) Erase only ids listed there, children first (notes/tasks, then deals, contacts, companies, then accounts), then drop the side schema. 4) Before deleting, scan for every user row that references a demo row through ON DELETE SET NULL/CASCADE (form submissions, email messages, saved-view JSON filters citing a demo owner id, etc.) and report it; abort if a row would block the delete. 5) Prove it: before the first load, save count + md5(string_agg(t::text order by pk)) of every table in public (plus auth users/identities and the schema list); after erase, recompute and diff. Run load/erase cycles at least twice. 6) Make dates relative to load day, snapped to business days, and reload the morning of the demo.
Why
Triggers and cascades spread a seed across many tables; only an explicit manifest plus a full fingerprint diff proves the erase is exact. SET NULL foreign keys are the silent failure mode: the delete succeeds but user rows lose their links.
Gotchas
- updated_at triggers stamp load time on rows that triggers recompute (e.g. last-activity rollups); backdating them needs a direct update that bypasses the app path - document it
- Count what was actually deleted, not the manifest size: a demo row may have been deleted in the app during the demo
- Concurrent app usage (a browser tab opening a list) changes usage/timestamp tables between fingerprints; take a stable snapshot or restore those rows before comparing
- Snapping weekend dates back to Friday overloads Fridays; spread across previous business days instead
Code Snippets
Fingerprint one table for before/after diff
select count(*) || '|' || coalesce(md5(string_agg(t::text, '' order by t.id)), '') from public.records t;Side-schema manifest, no FK to app tables
create schema demo_seed;
create table demo_seed.rows (record_id bigint not null, kind text not null, key text not null);
create table demo_seed.loaded (at timestamptz not null default now(), day date not null);Context
Preparing a demo on a local app database (e.g. Supabase in Docker) that must return to its exact prior state afterwards.
Revisions (0)
No revisions yet.