Skip to content

ADR 0002 — Drizzle ORM over PostgreSQL (with optional TimescaleDB)

  • Status: Accepted
  • Date: 2026-04-26
  • Deciders: André Luiz Gallon

Context

The dashboard must persist:

  • agents inventory + heartbeats,
  • target catalogue (≤ 20),
  • per-cycle runs and per-resource metrics,
  • audit log,
  • 60-second time-series buckets for charts,
  • idempotency keys.

It runs on Node.js, frequently in containers with read-only root filesystems, and must survive horizontal restarts of the dashboard pod.

Decision

  • Database: PostgreSQL 16. Single-instance in compose; in Kubernetes the manifests can be replaced by an operator (CloudNative-PG) for HA.
  • ORM: Drizzle ORM with the postgres-js driver.
  • Migrations: hand-written SQL files in dashboard/src/db/migrations/, idempotent via IF NOT EXISTS / ON CONFLICT DO NOTHING. Applied via psql -f rather than the Drizzle migrator so they can be replayed on stock postgres images and in kubectl exec.
  • Time-series: a metrics_buckets table populated on every runs/complete. An optional migration 0004_timescale_optional.sql upgrades the table to a Timescale hypertable + 1-minute continuous aggregate when the extension is available; the dashboard works on vanilla Postgres too.

Consequences

  • ✅ Type-safe queries that survive tsc --noEmit.
  • ✅ Bare-bones SQL migrations are auditable and replayable.
  • ✅ Stock Postgres image is enough to run the project; upgrading to Timescale is opt-in, not a hard requirement.
  • ⚠️ The db.execute(sql\…`)return shape varies betweenpostgres-jsversions; the dashboard wraps it inlib/db-rows.ts` to normalise.

Alternatives considered

  • Prisma — heavier runtime, schema diff requires a sidecar engine.
  • TypeORM — historic decorator runtime cost; weaker TS inference.
  • Knex — no schema-aware typing.
  • Pure SQL with pg — chose Drizzle to share the schema between agent (controller-client) and dashboard.