# PostgreSQL-backed tests in buzz-db

The dedicated PostgreSQL CI lane discovers tests and Cargo packages by
structure rather than by exact lists. Follow this checklist so a new database
test is run automatically and remains safe under parallel execution.

## Adding a test

1. Put the test in a module whose name ends in `postgres_tests`.
2. Mark it `#[ignore = "requires Postgres"]` so infrastructure-free unit-test
   jobs stay fast.
3. Connect through `crate::test_support::database_url()`. The CI wrapper sets
   this helper's environment to a unique database for each test process; never
   hard-code the shared development database.
4. Keep tests that need infrastructure beyond PostgreSQL and Redis in an
   `external_infra*_tests` module. The PostgreSQL lane excludes those tests.
5. Run `scripts/test-postgres-test-discovery.sh` after adding or moving the
   test. The same guard runs in CI immediately after changed-path detection.

The wrapper isolates destructive tests by dropping the entire per-test
database after the process exits. It does not `DELETE` rows or `TRUNCATE`
shared tables, so tests may run concurrently without coordinating cleanup.

## Choose the schema intentionally

Most tests use the committed desired-state schema from `schema/schema.sql`,
a manifest that includes one `schema/tables/public/<table>.sql` per table plus
its types, functions, partitions, triggers, and seed rows.
That is the default and is appropriate for data-access behavior.

Tests in `migration::postgres_tests` receive an empty database and own the
embedded migration lifecycle. A test outside that module that intentionally
depends on migration-created triggers or seed rows must prefix its function
name with `migration_schema_`; it also receives an empty database with
`BUZZ_TEST_SCHEMA_MODE=migration`.

Helpers that normally run migrations honor `BUZZ_TEST_SCHEMA_MODE=desired` in
the default lane. Do not rerun migrations against a desired-state database.
When behavior should match in both schema paths, add explicit desired-state and
migration-applied coverage rather than making the bootstrap implicit.

Tests that inspect cluster-wide PostgreSQL state or open least-privilege
sessions include `cluster_global_` in the function name. Migration-backed cases
use `migration_schema_cluster_global_`. Nextest serializes this small group
because separate databases still share `pg_stat_activity` and roles.

## Run the lane locally

Start native PostgreSQL and Redis, activate Hermit, and run:

```bash
. ./bin/activate-hermit
scripts/test-postgres-test-discovery.sh
scripts/postgres-test-run.sh
```

Set `BUZZ_POSTGRES_ADMIN_URL` to a PostgreSQL maintenance database owned by a
role that can create and drop databases. Set `PGHOST`, `PGPORT`, `PGUSER`, and
`PGPASSWORD` for desired-state bootstrap, plus `REDIS_URL` for Redis-backed
tests. The complete privilege-boundary inventory also needs `CREATEROLE` and
membership in `pg_read_all_stats`, or an ephemeral superuser as CI uses.

The runner creates one desired-state source database per invocation and clones
it for ordinary tests. Migration-mode tests start empty. Cleanup retries
transient disconnect races before reporting a warning.

Invite upgrade tests start from the complete schema through migration 0074,
including channel join roles, entity deletion approval, the unlisted channel setting and the user deletion target and fences. They exercise the
invitation schema expansion (migration 0075) through both migration and
desired-state paths against populated
data, retaining a non-default channel join role. They also execute the operator
repair script with `psql`; make sure the PostgreSQL client is installed and
resolves on `PATH`.