Eve library

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

ADR

Revision 1

On this page

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).
  • − Schema changes require a migration + the demeter_test reset discipline (see the migration strategy).