Skip to content

0010. Project data store: SQLite DO or Postgres, Kysely repositories, capacity, retention, and archival

  • Status: Accepted
  • Date: 2026-11-08

ADR 0008 places each project’s data in one SQLite Durable Object on Cloudflare and in Postgres in containers. A DO holds at most 10 GB and has a soft limit of about 1,000 requests per second (Cloudflare limits checked 2026-11-07). At the largest plan tier (1M emails and about 8M events per month, ADR 0014), 90 days of raw events is about 12 GB, which does not fit.

A spike (2026-11-07, Miniflare) found:

  • Kysely queries inside ctx.storage.transaction(async …) commit together and roll back together, including after a timer await, and concurrent read-modify-write commands inside it do not lose updates.
  • Without a transaction, a non-storage await inside a read-modify-write loses updates.
  • A Kysely db.transaction() call cannot work on a DO, because DO SQLite rejects explicit BEGIN.
  • The existing DO dialect packages (kysely-durable-objects, kysely-do) are immature.

Storage per target:

  • DO SQLite and node:sqlite share one SQLite schema. Postgres has its own schema and migrations. Every table keeps a project_id column, including in DO SQLite, so the schemas stay aligned.
  • Repository code is shared across dialects through Kysely.

Kysely rules:

  • Repositories take a Kysely<DB> and never call db.transaction().
  • A per-target transaction runner owns the transaction: on a DO, ctx.storage.transaction(async …); on node:sqlite and Postgres, Kysely db.transaction(). Every command runs inside it.
  • No external I/O inside a transaction.
  • We maintain our own small DO dialect (about 40 lines over ctx.storage.sql) instead of depending on the existing packages.
  • Repositories normalize types at their boundary (booleans, timestamps, JSON, big integers), so core sees the same values on every dialect.
  • Migrations are per dialect.

Capacity and retention:

  • The DO keeps a hot window: current state, a bounded window of recent events and step logs, and long-lived deduplication keys and tombstones. Raw events, step logs, and raw clicks older than the hot window are archived to R2 (or S3) and removed from the DO.
  • Each DO reports its storage bytes; an alert fires at 70% of the DO limit.
  • The largest tier is load-tested before it is sold.
  • Ingest for hot projects is buffered through Queues, so a burst does not exceed the DO’s request rate. The payload is stored outside the queue (ADR 0023).

Cross-project data:

  • A cross-project analytics and roll-up sink exists from day one, because DOs cannot be queried across projects. D1 holds roll-ups that need exact values (usage ledger exports, admin search indexes); Analytics Engine holds sampled operational metrics (abuse signals, deliverability trends, DO health); R2 holds archives.
  • Analytics Engine is never used for quotas or billing.

Point-in-time restore of a DO:

  • Restore with sending disabled for the affected workspace.
  • Each DO has a restore generation, recorded in D1 and incremented by every restore. The ledger export watermark is (restore_generation, sequence), and D1 reconciles exported ledger rows on (message_id, kind), so sequences reused after a rollback neither hide new rows nor double-count re-exported ones.
  • Deletions are replayed from outside the DO: every customer deletion also appends (project_id, HMAC(external_id), deleted_at) to a deletion log in D1, using a per-project HMAC key. After a restore, entries newer than the restore point are applied to the DO before it accepts commands, so a restore cannot bring back a deleted customer.
  • Quarantine messages in the rollback window, because some may already have been sent.
  • Reconcile the quarantined messages against SES events and an attempt journal kept outside the DO, then re-enable sending.
  • The full procedure is a runbook, written before launch.
  • Hot-window lengths and archive formats are set by the load test, before the first paid tier ships.
  • Customer-facing history older than the hot window is read from archives, which is slower.
  • DO schema migrations follow the rules in docs/architecture.md.
  • The attempt journal’s store is chosen before the delivery code is written.