# Database setup

## Migrations

`migrations/*.sql` are applied in filename order and recorded in
`pf.schema_version`. They are the **source of truth** for the `pf` schema — there
is no hand-maintained current-state file to drift out of sync.

```bash
npm run migrate            # apply pending migrations
npm run migrate:status     # what is applied, what is pending
npm run migrate:baseline   # record pending as applied WITHOUT running them
```

`server.js` refuses to start when the database is behind, so drift surfaces at
boot rather than as `column "x" does not exist` inside an unrelated request. Set
`PF_SKIP_MIGRATION_CHECK=1` to bypass.

### Fresh database

```bash
npm run migrate
psql -d <db> -f setup_sql/gen_dim_period.sql
```

### Existing database that already matches

Use `baseline` so the runner does not try to re-create tables that exist:

```bash
npm run migrate:baseline
```

To baseline only part of the way — the schema matches through `0003` but not
`0004` — pass `--up-to` and then migrate the rest:

```bash
node scripts/migrate.js baseline --up-to=0003_col_meta_dim_group_period.sql
npm run migrate
```

## Writing a migration

- Name it `NNNN_short_description.sql`, numbered after the highest existing file.
- One concern per file. Keep it forward-only; there are no down migrations.
- **Applied migrations are immutable.** The runner stores a checksum and refuses
  to proceed if a file changes after being applied, because editing one means
  databases silently disagree about what the schema is. To fix a mistake, add a
  new migration.
- No `IF NOT EXISTS` guards on new migrations. The bookkeeping already guarantees
  each runs once, and the guards hide ordering mistakes — the reason the old
  `01_schema.sql` had `ALTER`s sitting above the `CREATE TABLE` they depended on,
  broken for anyone installing from scratch. `0004` is the one exception, since it
  was applied by hand before migrations existed.

## Not migrations

- **`gen_dim_period.sql`** — creates and populates `pf.dim_period`. It is a
  parameterised data load (configurable fiscal year start month), not a schema
  change, so it stays a script you run deliberately.
- **`pf.fc_{tname}_{version_id}`** — per-version forecast tables, created and
  dropped at runtime by `routes/versions.js` from `col_meta`. Never migrated.
- **`schema.generated.sql`** — a `pg_dump` snapshot for reading, refreshed with
  `npm run schema:dump`. Generated, never edited, never applied.