Scope: apps/demeter/api and its canonical schema in @demeter/core.
Tooling: Drizzle Kit on
PostgreSQL.
This document is the deliverable for TODOS §29.19.4.6 and describes how schema
changes are authored, reviewed, applied, tested, and rolled back. It reflects
the migrations actually present in the repo (drizzle/0000–0005), not an
aspirational process.
1. Source of truth#
- Canonical schema:
libs/demeter/core/src/db-schema.ts. Every table, column, index, and relation is declared here in Drizzle's TypeScript DSL. The schema is exported from@demeter/coreand consumed by the API (src/plugins/database.tswires Drizzle to the pooledpg.Pool). - Applied history:
apps/demeter/api/drizzle/*.sqlplusdrizzle/meta/. The API app — not the library — owns the migration history so the integration-test harness can replay it and so the SQL is reviewable in PRs. - Config:
apps/demeter/api/drizzle.config.ts. NotetablesFilter: ['demeter_*']— Drizzle will never generate statements touching tables outside thedemeter_namespace, which is essential in this shared-database monorepo.
2. Authoring a change (generate, never hand-edit the schema diff)#
- Edit the schema in
libs/demeter/core/src/db-schema.ts(add table/column, change a default, add an index, etc.). - Generate the migration from the API app directory:This diffs the schema againstbash
pnpm --filter @demeter/api db:generate # drizzle-kit generatedrizzle/meta/and emits the next numbered file, e.g.0006_<slug>.sql, updating the snapshot. - Read the generated SQL. Drizzle is conservative but not omniscient:
rename columns are emitted as drop+add (data loss) unless you intervene, and
non-trivial data backfills are never generated. Hand-edit the SQL to add
UPDATE/backfill statements or to convert a drop+add into anALTER … RENAMEwhere you intend a rename. - Keep the filename's descriptive slug meaningful — the existing history reads
as a changelog:
0001_demeter_auth_tables,0002_garden_members_and_bed_fields,0003_sensor_alert_configs,0004_weather_locations,0005_community_tables.
3. Applying migrations#
| Environment | Command / mechanism |
|---|---|
| Local dev | pnpm --filter @demeter/api db:migrate (drizzle-kit migrate) against demeter |
| Throwaway prototyping only | db:push — pushes the schema directly with no migration file. Never use against a database whose history matters. |
| CI / deploy | db:migrate run as a release step (pre-cutover) against the target database, gated on the migration being backwards-compatible (see §5). |
| Integration tests | Auto-replayed by the harness (§4). |
Migrations are not run automatically on API boot — the server expects the schema to already be present. This keeps process startup fast and avoids N app instances racing to migrate. Apply migrations as an explicit, single-runner release step.
4. Test harness replay (how correctness is proven)#
apps/demeter/api/test/integration/setup.ts is the contract that keeps
migrations honest:
- On first use it connects to a dedicated
demeter_testdatabase (DEMETER_TEST_DATABASE_URL, defaultpostgresql://oshun:oshun_dev@localhost:5432/demeter_test) — never dev/prod. - It reads
drizzle/*.sqlin order and replays each file, recording applied filenames in_demeter_api_test_migrationsso re-runs are no-ops. cleanDatabase()issuesTRUNCATE … CASCADEbetween tests for isolation.
Consequence — the schema-reset rule: because the harness only applies new files and never re-applies changed ones, if you edit an already-applied migration (or change column types mid-stream) you must reset the test DB so it replays from scratch:
psql "$DEMETER_TEST_DATABASE_URL" -c 'DROP SCHEMA public CASCADE; CREATE SCHEMA public;'
Do not hand-apply 0003+ into demeter_test with psql — let the harness
own the replay, or its tracking table and the real DB will disagree.
A server.integration.spec.ts smoke test asserts the table count (currently
29 tables). When a migration adds/removes a table, update that assertion in the
same change so a forgotten migration can't pass silently.
5. Backwards compatibility & zero-downtime (expand → migrate → contract)#
The API is deployed as multiple stateless instances; old and new code run concurrently during a rollout. Migrations must therefore be additive first:
- Expand — add the new nullable column / new table / new index
(
CREATE INDEX CONCURRENTLYfor large tables to avoid write locks). Deploy. - Migrate — backfill data and start dual-writing/reading in app code. Deploy.
- Contract — only once no running code references the old shape, drop the old column/table in a later migration. Deploy.
Never combine a destructive DROP/NOT NULL-tightening with the deploy that
first depends on it. The conversion history followed this: auth columns were
added with defaults (0001), is_public was added to gardens before the
community layer read it (0005).
6. Rollback policy: forward-fix#
Drizzle Kit does not generate down-migrations, and rolling a schema backwards in production risks data loss. The policy is roll forward:
- If a migration is bad, author a new migration that corrects it.
- Because every migration is backwards-compatible (§5), the previous app version keeps working, so a bad deploy is rolled back by redeploying the prior image — without touching the schema.
- For an emergency manual reversal, write the inverse SQL by hand and apply it as a new numbered migration so the history stays linear and replayable.
7. Time-series / TimescaleDB note#
The roadmap (§29.19.1.2 / §29.19.4.7) anticipates TimescaleDB hypertables for
sensor readings. The current implementation uses a plain
demeter_sensor_readings table on stock PostgreSQL (no hypertable, no
extension) — verified: no create_hypertable/timescaledb statement exists in
drizzle/. Adopting TimescaleDB later is itself an expand-style migration
(SELECT create_hypertable('demeter_sensor_readings', 'recorded_at', migrate_data => true)),
gated on the extension being available in the target environment; it is deferred
with the rest of the deployment-infrastructure work in §29.19.
8. Pre-merge migration checklist#
- Schema edited in
@demeter/core, migration generated (not hand-diffed). - Generated SQL read line-by-line; renames/backfills corrected by hand.
- Change is additive / backwards-compatible (§5), or explicitly sequenced as a later contract step.
-
tablesFilterkept the diff insidedemeter_*. - If a table was added/removed, the
server.integration.spec.tstable-count assertion was updated. - Integration suite green (harness replays the new file). If an applied file
changed,
demeter_testschema was reset first.