# Database Migrations

How schema changes are tracked and deployed. Established 2026-07-14.

## Layout

```
db/
  migrate.php          — CLI migration runner
  006_app_modules.sql  — pending/applied migrations, applied in filename order
  007_...
  archive/             — migrations already folded into the baseline dump (never run these)
sql/
  netplanner_schema_20260706.sql  — BASELINE: full schema as of 2026-07-06,
                                    includes archive/000-005. Fresh-install restore point.
```

`sql/geolynx_ddl.sql` is superseded by the baseline above — don't use it for new installs.

## Rules

1. **One change-set per file, numbered sequentially:** `db/NNN_short_description.sql`. Next number = highest existing (including `archive/`) + 1. Never reuse a number.
2. **Immutable once applied anywhere** (including your local dev DB). Found a mistake? Write a new migration that corrects it. Never edit or renumber an applied file.
3. **No `BEGIN`/`COMMIT` inside migration files** — the runner wraps each file in its own transaction and records it in `public.schema_migrations` in that same transaction. A failed migration rolls back fully and is not recorded; fix and rerun.
4. **Idempotency guards as a seatbelt** (`IF NOT EXISTS`, `ON CONFLICT DO NOTHING`) are encouraged where natural, but the tracking table is the source of truth — don't rely on guards.
5. When the baseline is eventually regenerated (`pg_dump --schema-only`), move the migrations it absorbs into `db/archive/` and note the cutoff in the dump header comment.

## Running

```bash
php db/migrate.php            # dry run (default) — lists pending migrations
php db/migrate.php --apply    # applies pending migrations in order
```

- CLI only (`db/` sits outside the `www/` docroot and the script refuses non-CLI SAPIs).
- Connects using `www/fn/db.php` — so it targets whatever database the app on that server targets. Deploying to a client = deploy code, then run the runner on their server.
- Each database tracks its own applied set in `public.schema_migrations`, so the runner applies exactly what that environment is missing, whether it's one migration behind or ten.

## Deploy checklist (per environment)

1. Deploy code (which includes any new `db/NNN_*.sql`).
2. `php db/migrate.php` — review the pending list.
3. `php db/migrate.php --apply`.
4. Done — the app picks up schema changes immediately.

## Fresh install

1. Create the database, then restore the baseline: `psql -U postgres -d <dbname> -f sql/netplanner_schema_20260706.sql`
2. Restore data dumps as required (`sql/netplanner_data_*.sql.gz`).
3. `php db/migrate.php --apply` — creates `schema_migrations` and applies 006 onward. No seeding needed: baseline-absorbed migrations live in `archive/` where the runner never sees them.