hammy-backend/migrations/README.md

Database setup

Once, as a superuser

psql -U postgres -f scripts/bootstrap.sql

Creates the roles, the hammy database, the four schemas, and the default privileges. Change the three CHANGE_ME passwords first.

Migrations, as hammy_owner

for f in migrations/*.sql; do
    psql -U hammy_owner -d hammy -v ON_ERROR_STOP=1 -f "$f"
done

Running these as hammy_owner is not optional. ALTER DEFAULT PRIVILEGES FOR ROLE hammy_owner only fires for objects that role creates; run them as a superuser and the tables end up owned by the superuser with no grants, so hammy_app sees four empty schemas.

hammy_owner is NOLOGIN, so connect as a superuser and switch:

psql -U postgres -d hammy -c 'SET ROLE hammy_owner' -f migrations/001_types.sql

or give it LOGIN for the duration of the setup and revoke it after.

Verify afterwards:

SELECT relname, relacl FROM pg_class
 WHERE relnamespace = 'core'::regnamespace AND relkind = 'r';

Every row should show hammy_app=arwd/hammy_owner. All NULL means the migrations ran as the wrong role.

Files

File	Contents
scripts/bootstrap.sql	Roles, database, schemas, default privileges. Superuser, once.
migrations/001_types.sql	touch_updated_at(), the callsign/email/gridsquare domains.
migrations/002_core.sql	users, verifications, subscriptions.
migrations/003_api.sql	owners, keys, usage_daily.
migrations/004_logbook.sql	imports, qsos.
migrations/005_privacy.sql	forget_user(), forget_owner(), prune_verifications().

sqlc

sqlc.yaml points only at 001-004. It parses SQL to learn the schema and will choke on CREATE ROLE, ALTER DEFAULT PRIVILEGES and DO $$ blocks, which is another reason those live in scripts/ rather than migrations/.

Already have the old numbering?

The previous layout was 001_bootstrap through 006_fixes, applied by hand. If that database exists and has no data worth keeping, DROP DATABASE hammy plus DROP ROLE the four roles, then start from bootstrap.sql. The corrections from the old 006_fixes.sql are folded into 002-005 here, so there is no separate fixes file any more.