# ADR-0001: PostgreSQL + Drizzle for persistence (over in-memory stores)

**Status:** Accepted

## Context

The Demeter API originally backed every resource with module-level in-memory
`Map` stores. That made the routes self-contained and fast to prototype, but the
data evaporated on restart, could not be shared across horizontally-scaled
instances, and could not honestly satisfy the integration-testing goals
(§29.18.2) — a "persisted" record that lives in a process Map is not
persistence.

We needed durable, relational, multi-instance storage with strong typing and a
migration story, in a monorepo that already standardises on PostgreSQL.

## Decision

Persist all mutable domain state in **PostgreSQL**, accessed through **Drizzle
ORM**. Concretely:

- The canonical schema is declared once in `@demeter/core`
  (`libs/demeter/core/src/db-schema.ts`) and shared by the API and tests.
- Each resource gets a `*Store` class in `apps/demeter/api/src/db/` that owns
  its queries; routes keep their Zod validation and HTTP schemas but delegate
  persistence to the store.
- The Fastify `database` plugin builds a pooled `pg.Pool` (via
  `@oshun/database`) and a Drizzle instance, decorated as `fastify.db` /
  `fastify.dbPool`.
- Reference-only data that is genuinely static (the plant encyclopedia, AI
  knowledge bases) may remain curated in-memory — it is read-only content, not
  mutable user state.

## Consequences

- **+** Data survives restarts and is shared across instances; the integration
  harness can prove cross-session and cross-restart durability.
- **+** One typed schema drives the DB, the stores, and the tests.
- **+** Clear separation: routes = transport + validation, stores = persistence.
- **−** Tests now require a real Postgres (`demeter_test`); the harness replays
  migrations and truncates between tests (see
  [ADR-0006](0006-shared-db-namespace.md)).
- **−** Schema changes require a migration + the demeter_test reset discipline
  (see the
  [migration strategy](../../../api/docs/operations/migration-strategy.md)).
