Data layer and schema

Cusp keeps all of its structured data in one Cloudflare D1 database (SQLite), with large values spilling out to a single R2 bucket. There are no service splits at the data layer. The public render handler, the admin API, the headless API, the migration runner, the Durable Objects, and the Workflows all read and write through the same database and the same repository code. This page covers how that data layer is shaped and the conventions every table follows.

One repository layer, no ORM

Every read and write that touches D1 goes through the repository layer under src/shared/db/. Application code never reaches D1 outside a repository function. Each table has its own module under src/shared/db/repos/ — users, collections, documents, entity-versions, and so on.

The repositories hand-roll SQL through env.DB.prepare(sql).bind(...). There is no ORM and no query builder: per-row TypeScript interfaces give the type safety an ORM would provide at the boundary, the Worker bundle stays smaller, and the SQL reads as SQL. Raw prepared statements behind a chunked-write repository is a deliberate choice — the reasoning, and what was given up by not adopting one, is recorded in the decision below.

Repository methods are pure functions over the database handle: every method takes a D1Database as its first argument rather than holding one in a singleton. Tests pass a Miniflare D1 binding directly, and Workflows, DOs, and the Worker share the same code with their own env.DB references.

Schema conventions

• snake_case — table and column names are snake_case (collections, field_definitions, entity_versions).
• TEXT primary keys holding ULIDs — never auto-increment integers.
• Unix-epoch INTEGER timestamps — created_at and updated_at on mutable tables, changed_at on the version tables, all seconds since 1970.
• Foreign keys declared, and indexed where a join or lookup needs it — most foreign keys carry their own index, though a few that are never queried on their own (revisions.changed_by, entity_versions.parent_version_id) are deliberately left unindexed.
• Spillover columns — a nullable *_r2_key alongside each large JSON column; when the key is set, the JSON column is NULL and the canonical state lives in R2.

Migrations are sequential, idempotent, and reversible. SQLite has no ALTER COLUMN, so a column change is done by rebuilding the table (create the new table, copy the rows with INSERT ... SELECT, drop the old one, rename).

The migration runner applies pending migrations and tracks their state in its own table, with a request-time safety net that ensures the schema is current before the first request is served. The reasoning is in ADR 0008: embedded migration runner.

ULID primary keys

Every primary key is a ULID — a 26-character, Crockford-base32 identifier whose leading 48 bits encode the creation millisecond and whose remaining 80 bits are random. IDs are generated in the Worker at insert time, never by the database. Generate one through the shared helper:

import { generateId } from "../db/ids.ts";

const id = generateId(); // e.g. 01JXP4G3K8M2N7QHRSV5ZWBYCM

ULIDs sort lexicographically by creation time, so ORDER BY id DESC is already most-recent-first and admin listings need no separate created_at index. They cannot be enumerated, so document and user IDs are unguessable. They are URL-safe without escaping, which matters because they appear in admin URLs, API paths, and R2 keys. And they are shorter than a UUID, which adds up across the millions of rows the version table accumulates. UUIDv7 was the close runner-up; swapping to it later would be a format change, not a schema change.

Unix-epoch timestamps

Audit timestamps are INTEGER seconds since the Unix epoch, defaulted in SQL with (unixepoch()). Application code that sets a timestamp uses the nowSeconds() helper in src/shared/db/time.ts. Conversion to a human-readable date happens only at the boundary, in API serialisation and admin rendering. The schema and the repository layer always speak epoch seconds.

This is load-bearing for site-wide time travel: reconstructing the state of every entity at a past moment is a plain integer comparison against changed_at, with no datetime() casts, no timezone reasoning, and a small integer B-tree index.

-- Latest version of every entity as of a target timestamp
SELECT entity_type, entity_id, MAX(version_number) AS latest_version
  FROM entity_versions
  WHERE changed_at <= ?1
  GROUP BY entity_type, entity_id;

Chunked writes

D1 caps a statement at 999 bound parameters. A naive multi-row insert blows through it and surfaces as a production 500. The repository chunks every multi-row write: a batchInsert splits its rows into batches of at most 50 (the CHUNK_SIZE constant) before issuing them. Fifty is conservative — even a wide row stays well under 999 binds — and gives application code one ceiling to remember. No call site can issue an unbounded multi-row write.

Two further D1 idioms are enforced at the bind boundary: every query is parameterised (user input is never interpolated into SQL), and optional fields are coerced from undefined to null with ?? null, because D1 rejects undefined.

Versioned writes

Most content entities are versioned: users, roles, permissions, user roles, settings, collections, field definitions, documents, API tokens, block definitions, themes, plugins, plugin config, and media metadata. Every mutation of one of these goes through versionedWrite() in the repository layer, which composes the live-row update and an entity_versions snapshot insert into a single D1 batch. A direct UPDATE on a versioned table is a bug — it is blocked at lint and guarded at runtime by a context symbol that only the repository modules can pass.

History is append-only and never rewritten: restoring an old version creates a new version, so the restore itself is recorded in the audit trail. Documents have their own version path — snapshot rows in the revisions table plus the operation log inside the DocumentSession Durable Object. The mechanics of snapshots, R2 spillover for versions, pruning policies, and site-wide time travel are covered in Versioning and time travel.

R2 spillover for large rows

D1 has a per-row size limit, so Cusp keeps large JSON values out of the row. When a serialised value exceeds 200 kB (the SPILLOVER_THRESHOLD_BYTES constant) the repository writes it to R2 and stores only the key in D1, leaving the JSON column NULL. This is transparent to callers: the decision lives inside the repository helpers, and reads rehydrate from R2 automatically when the key is set. The client never touches R2 directly.

• Document blocks — when documents.blocks_json exceeds 200 kB the blocks are written to documents/{id}/blocks.json and the key stored in blocks_r2_key.
• Per-field data — the document's typed-field values spill the same way: past 200 kB documents.data_json moves to documents/{id}/data.json with the key in data_r2_key.
• Entity-version snapshots — when an entity_versions snapshot exceeds the threshold it is written to versions/{entity_type}/{entity_id}/{version_id}.json and the key stored in state_r2_key.
• Other large JSON columns — the same nullable *_r2_key pattern applies wherever a stored value can grow past the threshold, for symmetry even where spillover is rare.

Because R2 is not transactional with D1, the spillover object is written before the D1 batch. If the batch fails the R2 object is orphaned — harmless, because it is keyed by an ID that never collides, and cheap to leave in place. Nothing actively sweeps these orphans: the pruning workflow only deletes the spillover blobs belonging to version rows it is pruning, so a blob from a failed write is never reclaimed. At an ID that never repeats, the cost is a few unreferenced objects, not a correctness problem.