Skip to main content

ADR-0002 — D1 (SQLite) over Postgres

  • Status: Accepted
  • Date: 2026-04-15
  • Decision-makers: Tom Anderson
  • Supersedes: —

Context​

Each shop needs a relational database. Single-tenant (ADR-0003) means one database per shop, not one shared database with shop_id columns.

Per-shop scale: ~2,800 customers, ~9,000 products, ~17,000 transactions/year, ~2,500 service tickets/year. 82 tables. ~100MB total. Will grow but never to "big data" scale.

Alternatives:

  • Postgres — most full-featured option. Has FTS, GIS, JSONB. But: per-shop Postgres requires per-shop instances (operationally heavy) or shared instances (multi-tenant, defeats the isolation property). Cost-per-shop is high at small scale.
  • Supabase Postgres — managed Postgres with row-level security for multi-tenancy. Excellent product but requires multi-tenant model; can't get per-shop isolation cleanly.
  • D1 (SQLite-on-edge) — Cloudflare's per-database SQLite, integrated with Workers, low ops, no per-shop infra to provision separately.
  • DynamoDB or other NoSQL — no SQL means no AI/RAG-friendly introspection, no ad-hoc queries, no schema evolution story we want.

Constraints SQLite satisfies for our use case:

  • Reads scale to millions/day per shop trivially
  • Writes scale to thousands/day per shop trivially
  • 75-table joins fine
  • No PostGIS / FTS needed (FTS5 is in SQLite if we ever want it)

Constraints SQLite struggles with that we don't hit:

  • High-concurrency writes (we have ~7 staff/shop, not 700)
  • Complex stored procedures (we keep logic in the Worker)
  • Cross-database queries (we deliberately don't want them)

Decision​

Use Cloudflare D1 as the per-shop database. Schema is written in plain SQLite-compatible SQL (no D1-specific extensions) so it remains portable. Migrations are sequenced SQL files in migrations/.

Consequences​

Positive:

  • Per-shop database is created with one CLI command; no infra ticket
  • Backups are SQLite dumps — readable, restorable in any SQLite environment
  • Schema portability if we ever leave D1 (or D1 leaves us)
  • Single-tenant is enforced at the binding layer (ADR-0003)
  • No connection pool to manage; the Worker's binding handles it
  • D1's read replication gives us low-latency reads in the operator's PoP

Negative:

  • D1 is younger than Postgres; fewer Stack Overflow answers
  • 10GB max database size today (we're 100MB; never hitting this for 1 shop)
  • Some Postgres features missing (window functions are present, GIS isn't, advanced JSONB ops aren't)
  • D1 query latency from a different PoP than the database is hosted in is higher than Postgres on a regional VPC
  • Migration tooling (wrangler d1 migrations) is less mature than pgadmin or flyway

Mitigations:

  • We test against local SQLite (which is what D1 runs) so dev parity is excellent
  • Schema is portable; if D1 limits become real, migration to a SQLite-on-Litestream or DuckDB-on-S3 setup is a few weeks, not a year
  • We don't use D1-specific syntax — every query runs on standard SQLite

Notes​

If a shop's data grows past 5GB (very unlikely for a single bike shop), reconsider. If D1 introduces breaking changes (unlikely given Cloudflare's track record), reconsider. Otherwise this is stable.

See also​