# 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.