Lars Nolden
85 lines
3.3 KiB
Markdown
85 lines
3.3 KiB
Markdown
# Database migrations
|
|
|
|
Plain-SQL migrations in `migrations/`, applied **manually** with the supabase
|
|
CLI (available in the dev shell). There is deliberately no automated runner:
|
|
schema changes are rare, deliberate, admin-level operations.
|
|
|
|
The CLI records what has been applied in
|
|
`supabase_migrations.schema_migrations` on the target database, so pushing is
|
|
incremental and re-running it is safe.
|
|
|
|
## Applying to prod (db-host)
|
|
|
|
Postgres on db-host only accepts connections from the private network, so go
|
|
through an SSH tunnel:
|
|
|
|
```sh
|
|
# terminal 1 — tunnel to db-host's loopback
|
|
ssh -L 15432:127.0.0.1:5432 deploy@db.gebos.online
|
|
|
|
# terminal 2 — from the repo root, inside `nix develop`
|
|
PGPASS=$(sops -d --extract '["postgres_admin_password"]' nix/secrets/secrets.yaml)
|
|
supabase db push --db-url "postgres://postgres:${PGPASS}@127.0.0.1:15432/postgres"
|
|
```
|
|
|
|
The `postgres` superuser password is set from the same sops secret by the
|
|
`gebos-postgres-passwords` oneshot on db-host (see
|
|
`nix/modules/gebos-postgres.nix`), so it only works after the first deploy of
|
|
that unit.
|
|
|
|
## Adding a migration
|
|
|
|
```sh
|
|
supabase migration new <short_name> # creates migrations/<timestamp>_<short_name>.sql
|
|
```
|
|
|
|
Write plain SQL. Conventions:
|
|
|
|
* Migrations are ordered and applied exactly once — they do NOT need to be
|
|
idempotent (the schema-v1 file's `CREATE TYPE`s aren't).
|
|
* Cluster bootstrap (Supabase schemas/roles, extensions that need
|
|
`shared_preload_libraries`) belongs in `nix/supabase/init.sql`, not here.
|
|
* Role passwords never go in migrations — they are synced from sops by the
|
|
`gebos-postgres-passwords` unit.
|
|
|
|
## Local dev
|
|
|
|
`nix run .#dev` applies everything automatically: its `db-migrate` process
|
|
bootstraps the Supabase roles/schemas, runs `supabase db push`, and loads
|
|
`seed.sql` on every start (all idempotent — see
|
|
`nix/dev/process-compose.nix`). To re-apply by hand against the running dev
|
|
Postgres, do it as the local superuser (your OS user — `gebos_ingest` is only
|
|
*created* by the migration and has no DDL rights). The CLI ignores `sslmode`
|
|
in the URL, so TLS must be disabled via the environment:
|
|
|
|
```sh
|
|
PGSSLMODE=disable supabase db push --db-url "postgres://$USER@127.0.0.1:5432/gebos"
|
|
```
|
|
|
|
(The dev postgres ships TimescaleDB — the Apache edition, which covers
|
|
hypertables; TSL-only features like compression are absent locally.)
|
|
|
|
## Seed / fixture data (dev and tests only)
|
|
|
|
`seed.sql` fills the local database with a self-consistent fixture world:
|
|
2 buildings (gas / district heating), 8 apartments, tenants incl. a
|
|
Mieterwechsel and a vacancy, gateways, sensors (incl. one unassigned), ~13
|
|
months of daily cumulative measurements with realistic seasonality, and
|
|
approximate reference data (thresholds, CO2 factors, prices, HDD). The header
|
|
comment in the file documents every scenario and the fixed-UUID namespaces.
|
|
|
|
Apply it after the migrations, with a superuser (the seed writes tables the
|
|
`gebos_ingest` role may not):
|
|
|
|
```sh
|
|
psql -h 127.0.0.1 -p 5432 -d gebos -f supabase/seed.sql
|
|
```
|
|
|
|
It is idempotent (fixed UUIDs + `ON CONFLICT DO NOTHING`, deterministic
|
|
measurement generation) — re-running on a later day only appends the new
|
|
days of measurements.
|
|
|
|
**Never apply it to prod**: the tenants and devices are fake, and the
|
|
regulatory numbers are development approximations, not verified values from
|
|
the legal sources. Verified reference data belongs in its own migration.
|