# Demeter Database Migration Strategy

**Scope:** `apps/demeter/api` and its canonical schema in `@demeter/core`.
**Tooling:** [Drizzle Kit](https://orm.drizzle.team/kit-docs/overview) 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.
