mirror of
https://github.com/zvx-echo6/central.git
synced 2026-05-21 18:14:44 +02:00
83b1e45fa8
- Add docs/test-database.md with one-time setup, DSN convention, reset instructions, and explanation of why PostGIS is not in migrations - Update docs/migrations.md with "Extensions are not in migrations" section explaining superuser requirement - Restore geom GEOMETRY(Geometry, 4326) column to test fixture now that central_test has PostGIS installed - Add CREATE EXTENSION IF NOT EXISTS postgis to test fixture for self-bootstrap (central_test is superuser) - Add Testing section to README.md pointing to docs/test-database.md Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
49 lines
1.8 KiB
Markdown
49 lines
1.8 KiB
Markdown
# Migration policy
|
|
|
|
## Migrations are the sole source of truth
|
|
|
|
The `sql/migrations/` directory contains all schema definitions. There is
|
|
no separate schema.sql file; use `pg_dump -s central` to generate a
|
|
human-readable snapshot of the current schema when needed.
|
|
|
|
## Migrations must be idempotent
|
|
|
|
New migration files (007+) must use guards so they can be safely
|
|
re-run without error:
|
|
|
|
- `CREATE TABLE IF NOT EXISTS ...`
|
|
- `CREATE INDEX IF NOT EXISTS ...`
|
|
- `INSERT ... ON CONFLICT DO NOTHING` (or `ON CONFLICT ... DO UPDATE`
|
|
where the intent is upsert)
|
|
- `ALTER TABLE ... ADD COLUMN IF NOT EXISTS ...`
|
|
|
|
Migrations 003-006 predate this policy and are grandfathered. Do not
|
|
rewrite them.
|
|
|
|
## All schema changes go through migrate.py
|
|
|
|
Direct `psql` execution bypasses the `schema_migrations` tracker and
|
|
was the cause of the v0.2.0 reconcile. If a migration needs to be
|
|
applied on the live system, run:
|
|
|
|
sudo -u central /opt/central/.venv/bin/python -m central.migrate
|
|
|
|
Never apply migration SQL directly via `psql`, even as a superuser,
|
|
even "just to test." If migrate.py has a bug that's blocking you, fix
|
|
migrate.py.
|
|
|
|
## Extensions are not in migrations
|
|
|
|
PostgreSQL extensions like PostGIS require superuser privileges to
|
|
install. The production `central` role is intentionally not a superuser.
|
|
Therefore, extensions live outside the migration system:
|
|
|
|
- **Production bootstrap:** A DBA runs `CREATE EXTENSION postgis` once
|
|
before the first `migrate.py` run.
|
|
- **Test database:** The `central_test` role is a superuser, allowing
|
|
test fixtures to self-bootstrap extensions.
|
|
|
|
This is documented in [docs/test-database.md](test-database.md).
|
|
|
|
Do not add `CREATE EXTENSION` statements to migrations — they will fail
|
|
in production where migrations run as the non-superuser `central` role.
|