0010. Project data store: SQLite DO or Postgres, Kysely repositories, capacity, retention, and archival
- Status: Accepted
- Date: 2026-11-08
Context
Section titled “Context”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 explicitBEGIN. - The existing DO dialect packages (
kysely-durable-objects,kysely-do) are immature.
Decision
Section titled “Decision”Storage per target:
- DO SQLite and
node:sqliteshare one SQLite schema. Postgres has its own schema and migrations. Every table keeps aproject_idcolumn, 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 calldb.transaction(). - A per-target transaction runner owns the transaction: on a DO,
ctx.storage.transaction(async …); onnode:sqliteand Postgres, Kyselydb.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.
Consequences
Section titled “Consequences”- 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.