In a CRM where every association type has an inverse row (A->B and B->A) and a labeled link implies the unlabeled one, a "primary company" label is often modeled symmetrically, so the label shows on both records. Rules like "first company becomes primary", "a contact with companies must always have exactly one primary" and "replace the primary" then break: immediate checks fail mid-transaction (remove old primary before adding the new one), and bulk operations such as record merges or imports that delete and reinsert links trip the rules in the wrong order.

A child row (e.g. an order line) must have exactly one parent, enforced by a DEFERRABLE INITIALLY DEFERRED constraint trigger checked at commit. Any plain DELETE of a parent (cleanup scripts, test teardown, purge jobs) cascades the link rows away, leaving a live child with zero parents, so the whole transaction fails at commit. Batched imports that wrap each row in its own exception block also lose: the deferred check runs at commit, outside the per-row block, so one bad row aborts the whole batch instead of becoming a row error.

Inserting a parent row (e.g. a queue) and a child row that references it in one statement through WITH ... INSERT CTEs fails when a trigger on the parent maintains a lookup that the child's validation trigger reads (here an AFTER trigger that appends the new id to an allowed-options JSON list). The child's BEFORE trigger reads the lookup table and rejects the value as unknown, although the parent was inserted in the same statement.

An AFTER UPDATE trigger that enqueues work only when some tracked column changed, or when updated_at changed (set to now() by a BEFORE trigger), silently does nothing for the second update of the same row inside one transaction. now() is the transaction start time, so new.updated_at = old.updated_at. Changes to columns the trigger does not list (for example created_at) are then lost to the queue. This surfaces in pgTAP tests, which run everything in one transaction.

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.

Record merge implemented in SQL fails with a unique violation (23505) as soon as the secondary record has side data. Unique values (email, domain) live in a side table rebuilt by an AFTER UPDATE trigger on the record (delete all its rows, re-insert from props). If the merge removes the old records' unique rows by hand, then moves associations or visits, other triggers (recomputed activity properties) update the OLD record, the sync trigger re-inserts its email, which is already taken by the new record, and the whole merge transaction aborts.

A Postgres trigger fills a re-evaluation queue only when a consumer already exists (e.g. "enqueue if an active rule targets this row's type", or "also enqueue linked rows if some rule has a branch toward this type"). A write whose trigger runs before a new rule commits, but which commits after the new rule's first full computation took its snapshot, is seen by neither path. Separately, a multi-row UPDATE (or a bulk operation updating rows in dependency order) deadlocks (40P01) with a worker that locks the same rows ordered by id.

A business rule enforced by a BEFORE INSERT ROW trigger (e.g. 'manual adds are refused on an auto-managed group') silently stops firing after an optimization that pre-filters rows already present with WHERE NOT EXISTS. With plain INSERT ... ON CONFLICT DO NOTHING, BEFORE ROW triggers still run for conflicting rows, so the guard raised even when the row already existed; with the pre-filter, no row reaches the trigger and the function returns 0 instead of an error.