Eve library

Demeter Database Migration Strategy

Guide

Revision 1

On this page

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/core and consumed by the API (src/plugins/database.ts wires Drizzle to the pooled pg.Pool).
  • Applied history: apps/demeter/api/drizzle/*.sql plus drizzle/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. Note tablesFilter: ['demeter_*'] — Drizzle will never generate statements touching tables outside the demeter_ namespace, which is essential in this shared-database monorepo.

2. Authoring a change (generate, never hand-edit the schema diff)#

  1. Edit the schema in libs/demeter/core/src/db-schema.ts (add table/column, change a default, add an index, etc.).
  2. Generate the migration from the API app directory:
    bash
    pnpm --filter @demeter/api db:generate   # drizzle-kit generate
    
    This diffs the schema against drizzle/meta/ and emits the next numbered file, e.g. 0006_<slug>.sql, updating the snapshot.
  3. 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 an ALTER … RENAME where you intend a rename.
  4. 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_test database (DEMETER_TEST_DATABASE_URL, default postgresql://oshun:oshun_dev@localhost:5432/demeter_test) — never dev/prod.
  • It reads drizzle/*.sql in order and replays each file, recording applied filenames in _demeter_api_test_migrations so re-runs are no-ops.
  • cleanDatabase() issues TRUNCATE … CASCADE between 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:

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

  1. Expand — add the new nullable column / new table / new index (CREATE INDEX CONCURRENTLY for large tables to avoid write locks). Deploy.
  2. Migrate — backfill data and start dual-writing/reading in app code. Deploy.
  3. 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.
  • tablesFilter kept the diff inside demeter_*.
  • If a table was added/removed, the server.integration.spec.ts table-count assertion was updated.
  • Integration suite green (harness replays the new file). If an applied file changed, demeter_test schema was reset first.