ADR-0007 Database

Context

Every app needs a system of record. The deploy decision — Docker containers running Bun on our own infra (ADR-0003, ADR-0035) — removed the biggest constraint: long-lived direct TCP connections with an in-process pool, so the full range of relational databases is on the table with no pooling proxy or edge-runtime workarounds.

Requirements:

• Relational core — our domains are structured and interconnected
• Strong consistency and transactional integrity by default
• Works over direct TCP from the app runtime
• Pairs well with a type-safe TypeScript query layer (ADR-0008)
• Room to grow: reporting queries, full-text search, JSON blobs, future AI/vector needs

Options considered

1. PostgreSQL

The default choice of the industry for good reason: the most capable open-source relational database.

Pros

• Richest feature set: JSONB, full-text search, CTEs, window functions, transactional DDL
• Extension ecosystem: PostGIS, pgvector (future AI/embedding needs), pg_trgm
• Every tool supports it first — drivers, ORMs (Drizzle is Postgres-first), dashboards, migration tools
• Direct TCP from our app container — no proxy, no HTTP layer
• Runs as a sibling container in our Docker setup; managed Postgres (Neon, RDS, Supabase) is a drop-in replacement per-product if ops burden ever matters

Cons

• We own ops: backups, upgrades, vacuum/monitoring (true of any self-hosted DB, but real)
• Heavier than SQLite for trivial apps

2. MySQL

The other mainstream open-source RDBMS.

Pros

• Mature, widely deployed, well understood operationally
• Perfectly adequate for standard CRUD workloads

Cons

• Weaker feature set where it matters: JSON support, CTE/window function history, transactional DDL
• Thinner extension ecosystem; no pgvector equivalent with the same traction
• Oracle stewardship shadows long-term direction
• No compensating advantage over Postgres for our stack

3. SQLite (+ Litestream)

Embedded database, replicated to object storage for durability.

Pros

• Simplest possible ops: a file, no server, no connections to manage
• Extremely fast for single-node read-heavy apps
• Cheapest to run

Cons

• Single writer — conflicts with horizontal scaling, which ADR-0003 explicitly keeps on the table
• No network access model; every app server must co-locate with the data file
• Weaker fit as a company-wide default: great for specific small tools, wrong as the standard all products start from
• Litestream adds a replication model the team would have to learn and trust for primary data

Decision

PostgreSQL is the company-standard database engine for all new apps.

Default topology: Postgres runs as a sibling container next to the app in our Docker setup (ADR-0003), connected over direct TCP. A managed Postgres service is an acceptable per-product substitution when it clearly reduces ops burden — the engine standard does not change.

The deciding factors: the richest feature set (JSONB, full-text, extensions like pgvector for future AI work), first-class support in every piece of our stack, direct TCP from the app container, and a hiring pool that overwhelmingly knows it. We accept owning database ops.

Consequences

Easier:

• One engine to learn deeply: connection pooling, backup/restore, indexing, query tuning become shared team knowledge
• Future needs are covered without a second store: JSONB documents, full-text search, pgvector embeddings
• The query layer decision (ADR-0008) gets Drizzle’s best-supported dialect

Harder — what we now own:

• Backups, restore drills, and upgrade cadence per environment
• Connection management discipline (pool sizing) as apps scale
• Migrations are our process — handled by Drizzle Kit (ADR-0008)

Given up: SQLite’s zero-ops simplicity for small tools. Accepted — a single, capable default beats a per-project database lottery; a genuinely tiny tool can still propose an exception ADR.