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
*Storeclass inapps/demeter/api/src/db/that owns its queries; routes keep their Zod validation and HTTP schemas but delegate persistence to the store. - The Fastify
databaseplugin builds a pooledpg.Pool(via@oshun/database) and a Drizzle instance, decorated asfastify.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). - − Schema changes require a migration + the demeter_test reset discipline (see the migration strategy).