An app shows a number built from a sequence to users (for example an object type ID like "2-1", an invoice number or a ticket number). Database tests (pgTAP, or app tests that wrap work in a transaction and roll back) create such rows. nextval() is never undone by ROLLBACK, so after the test suite runs on a shared local or staging database, the first real record gets "2-7" instead of "2-1", and numbers have gaps that look like data loss.

pgTAP tests that need a second session (for example "two workers claim the same row with FOR UPDATE SKIP LOCKED: one skips", or "a writer waits for a row held by another transaction without deadlocking") try dblink inside the test transaction. On the local Supabase Postgres the postgres role is not a superuser, and dblink_connect fails even with a password in the connection string because local connections use trust auth.

A per-day quota (for example "at most 100,000 success log lines per day, errors always written") implemented as an UPDATE of a single counter row serializes every writer transaction on that row until commit. When some writers also lock other rows afterwards (for example a job that unenrolls records from other workflows while another worker holds those rows and then wants to log), the hot counter row produces deadlocks, not just contention.

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.

A pgTAP test that checks "this function writes nothing to table X" by comparing count(*) before and after is flaky on a shared local database: in READ COMMITTED each statement sees rows committed by other sessions (dev-server background loops, other tests), so the counts drift and the test fails at random. Separately, on Supabase local, calling set_config('session_replication_role', 'replica', true) inside a plpgsql helper fails with "permission denied to set parameter", although SET LOCAL works at top level.

You need to rename user-facing labels stored in the database (object names, property labels, enum option labels in a jsonb array, pipeline stage names) on existing databases, and keep the seed file in sync for fresh databases. Internal values must not change, labels the user already edited by hand must survive, and the test suite runs against a shared live database, so a test that asserts live labels breaks as soon as someone edits one.

To check that migrations replay from scratch without resetting a local Supabase database that holds data you must keep, you can create a separate database in the same container (createdb) and apply the migration files, then seed.sql. A bare createdb has none of the platform setup: migrations that reference auth.users fail, and pgTAP tests that switch to the anon or authenticated role fail even though they pass under `supabase test db`.