Reversible demo data in a trigger-heavy Postgres app: simulated time, tracking schema, fingerprint check

You need to fill a local database with believable fake data (records spread over months, activities, computed fields) for a demo, going through the same triggers as the app (history, unique values, computed properties, queues), loaded atomically, and removable later so that every table returns exactly to its previous content. Naive seeds bypass triggers, write everything at now(), or rely on name patterns to delete, which is unsafe.

1) Run one psql --single-transaction script with ON_ERROR_STOP. Refuse to run if a dedicated schema (e.g. demo_tracking) already exists. 2) Create that schema with plain tables listing every created row id (no foreign keys to app tables: an FK would add RI triggers on the app table). 3) Put the dataset in temp tables (relative day offsets), build an operations table (instant, kind, key) and replay it in chronological order inside a DO loop, setting the same session settings the app's write wrapper sets (source, actor) and inserting through the normal tables so triggers run. 4) Set created_at/updated_at explicitly at INSERT (BEFORE UPDATE triggers that force updated_at=now() don't fire on insert). After each operation, re-date the rows the triggers just wrote: UPDATE history SET changed_at = :instant WHERE changed_at = now() AND record_id IN (tracked ids) — now() is constant for the transaction, so only this run's undated rows match. 5) End with assertions: snapshot md5(row::text) of pre-existing rows and config tables at start and compare at the end; raise to roll back. 6) The erase script deletes only tracked ids (activities first, then parents), lets ON DELETE CASCADE remove history/links/queues, deletes tracked auth users/owners, then drops the schema. 7) Prove it: fingerprint every table (count + md5(string_agg(t::text order by t::text))) before load and after erase; they must match except sequences.

Triggers derive computed fields from the data and timestamps, so replaying in simulated chronological order keeps derived values and history consistent. Tracking ids explicitly makes deletion exact, and table fingerprints prove reversibility.