Gebos/supabase/README.md

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:

# 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

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 TYPEs 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:

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):

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.