@delightstack/database

Type-safe database layer for Cloudflare Durable Objects with built-in full-text search, schema validation, automatic migrations, and a reactive Svelte 5 client that syncs a local search index.

Features

• Declarative schema — Define tables with a fluent TypeScript API. Field types, constraints, and validators are inferred at compile time.
• Full-text & vector search — A built-in search engine with no third-party dependency: BM25 ranking, typo tolerance, prefix search, facets, geo and vector/hybrid queries. Mark fields as .searchable() and query them. The index lives in SQLite rows written inside the same transaction as the entity row, so there is no snapshot to serialize and no cold-start rebuild.
• Automatic migrations — New columns are added automatically when you update your schema. No migration files to manage.
• Dependency-free validation — Every create() and update() call validates data against the schema at runtime. String formats (.email(), .url(), .datetime()), length/number ranges (.min(), .max()), and custom .check() functions are enforced — all in-house, no zod.
• Transactions — Batch multiple create/update/delete/exec operations into a single atomic transaction.
• Change log & history — Opt a table into { history: true } and every create/update/delete is recorded as a field-level diff in _change_log, readable with db.history() / db.changesSince() and undoable with db.revert() — or, a whole operation at a time, with db.revertOperation(operation_id). Retention is swept on an alarm.
• Actor attribution — Every mutator takes a trailing { actor }, or wrap a run of writes with scoped(db, actor, operation?). The actor and operation id land on every change-log row; unattributed writes are 'system'.
• Binary & file fields — schema.blob() stores raw bytes in a SQLite BLOB column; schema.file({ store }) stores a small { key, size, mime } descriptor for an object living in R2/S3/GCS, resolved on demand with resolveFile(). Orphaned objects are queued for you in _file_gc when their row dies — drain it with db.pendingFileDeletions().
• Three field tiers — .searchable() syncs and indexes, .carried() syncs without indexing, and the default does neither. Keeps large values a client needs out of the term dictionary.
• Incremental sync — sync() returns only the changes since a given timestamp, enabling efficient client-side search index mirroring.
• Form generation — Schema definitions automatically produce HTML form field attributes plus a Standard Schema validator for whole-form validation.
• Auto-generated API routes — createDatabaseHandle({ tables, hooks }) plugs into SvelteKit's handle and serves CRUD + sync routes for every table, with lifecycle hooks for auth and side effects.
• Reactive client (Svelte 5) — DatabaseClient gives you three reactive read handles (get / entity / list), IndexedDB caching, a local IndexedDB search index, optimistic updates, and coverage-based client/server query routing — all heavy work running off-thread in a SharedWorker.

Upgrading to v2

v2 is a major release: the search engine, the client read API, and the schema validation layer were all replaced. The full set of breaking changes:

Client: three handles replace five read APIs

db.watch (DatabaseWatch), db.read (EntityReader), EntityState.from, and markHydrated are removed. db.get, db.entity, and db.list now return reactive handle objects — not promises.

// v1
let person = $derived(await db.get('person', id)); // db.get returned a Promise
const people = db.watch('person', { term: 'alice' }); // live list
const reader = db.read('person', () => id); // read-only reactive handle

// v2
const person = db.get('person', id); // reactive EntityHandle
person.value; // live value (undefined until loaded)
let loaded = $derived(await db.get('person', id).load()); // awaited form, still reactive
const people = db.list('person', { term: 'alice' }); // live ListHandle (the old watch + list merged)
const once = await db.list('person', { term: 'alice' }).load(); // one-shot form

• WatchStatus is renamed HandleStatus (same 'loading' | 'refreshing' | 'ready' | 'error' union).
• The client SearchResult drops elapsed and gains mode: 'client' | 'server' — the live routing decision, per result.
• The worker RPCs getSearchMode / isSynced are removed; read result.mode (or handle.mode) instead.

Newer v2 changes (same release)

• ListHandle.results → hits and ListHandle.docs → items (and SearchResult.docs → items). The hit objects keep their { id, score, document } shape — document is also the wire field name in server list responses.
• createDatabaseHandle's sync option now defaults to true — pass sync: false to opt out. (Previously it was off unless enabled, which silently broke client search.)
• Reserved field names: and, or, not, and $derived can no longer be used as table field names (compile-time and runtime errors) — the first three collide with the where-clause grammar, the last with the internal derived-values store. created_at/updated_at were already reserved.
• sparse: false now routes client queries to the server automatically, and combining it with source: 'client' is an error (same treatment as vector).
• New: db.signOut() wipes all locally persisted data (entity cache, sync cursors, and the local search index) — see Lifecycle.
• DatabaseClient's lifecycle booleans consolidated: db.status ('idle' | 'initializing' | 'ready' | 'signed_out') replaces initialized, hydrated (now internal), and signed_out; the syncing/synced booleans remain.

Schema: zod is removed

Validation is now in-house and dependency-free. parse() behavior is unchanged, except failures throw DelightError (status 400, with an issues array attached).

• Some zod-passthrough builder methods are deleted: length, nonempty, lowercase/uppercase (the case asserts), gte/lte (use min/max), positive/negative (use gt(0)/lt(0)), multipleOf (use step), refine/superRefine (use check), transform, and prefault. Others were re-implemented in-house and still work: startsWith/endsWith/includes, the transforms trim/toLowerCase/toUpperCase/normalize, and the exclusive bounds gt/lt.
• .check(fn) is the custom-validation escape hatch (return an error string to fail).
• .step(n) replaces multipleOf — it validates divisibility (decimal-safe) and sets the form input's step attribute.
• Database.Table is the one table type; Database.AnyTable remains as a deprecated alias.
• table() no longer takes a third options argument (the temporary search_engine opt-in is gone).

// v1
username: schema.string().nonempty().lowercase().refine((v) => !v.includes(' '), 'No spaces'),

// v2
username: schema.string().min(1).check((v) => (v.includes(' ') ? 'No spaces' : undefined)),

Server & handler

• createDatabaseHandle supports only the tables + hooks mode. defineRoute and the routes array are removed:

  // v1
  createDatabaseHandle({ getDatabase, routes: [defineRoute({ entity: 'post', table: postTable, hooks })] });
  
  // v2
  createDatabaseHandle({ getDatabase, tables, hooks: { post: { ... } } });

• The sync endpoint is POST-only, and DatabaseSyncRequest drops the top-level start_updated_at / end_updated_at / limit — ranges and limits are per-entity in the entity map (one request still syncs any number of entity types; config_version is now optional).

• The batch array overload of DatabaseServer.get() is removed — call get() per id.

• The never-populated event.user_id fields on transaction operations are removed, as is EntityChangedMessage.user_id in @delightstack/websocket (entityChanged is now (action, entity_type, id, data?, sparse?)).

• SqlServer moved to @delightstack/auth (it was auth-private in practice), and prepareSql / SqlTaggedTemplate moved to @delightstack/utilities (still re-exported from @delightstack/database/server for compatibility).

• The root entry is now schema + types only. It no longer re-exports /server, so createDatabaseHandle (and prepareSql) must be imported from @delightstack/database/server. The unused ./schema export is removed. This keeps a schema-only import from dragging the SvelteKit handler into worker/client bundles.

• New: DatabaseStub<Config> — the typed async RPC projection of DatabaseServer for typing DO stubs (see Typed stubs).

• New: alarm registry + instanceName — registerAlarm(name, fn) and a base alarm() with per-handler error isolation (integrations self-register); DatabaseServer.instanceName(ctx) / this.instance_name recover the idFromName() key without the ctx.id cast.

• New: the database ↔ websocket contract is typed — DatabaseBroadcast / DatabaseClientHooks / DatabaseEntityChange are exported from the root; @delightstack/websocket implements them, so drift between the packages is a compile error.

• foreignKey accepts the referenced table object (table: organizationTable) in addition to its name string — a typo'd reference becomes a compile error.

Search engine: Orama is gone (unreleased 1.x → 2.0 changesets)

• The built-in SQLite engine is the only engine; @orama/orama and @msgpack/msgpack are no longer dependencies. Durable Objects migrate themselves once on first wake: sync metadata moves to search_state/search_tombstones, search rows rebuild from entity rows, config_version bumps (every client resyncs once), and the legacy search_index/search_journal tables are dropped. table.config.orama is now table.config.index_schema (the flat search schema itself).
• Query-key renames, with no legacy aliases — old spellings stop working in typed queries, URLs, and old cursors: distinctOn → distinct_on, properties → fields, vector.property → vector.field, order[].key → order[].field, containsAll/containsAny/nin → contains_all/contains_any/not_in, and q is removed entirely (use term). vector gains an optional similarity floor (default 0.8). The tokenizer also changed (apostrophe folding, camelCase splitting, dotted acronyms, whole numbers kept) — rankings shift slightly on affected corpora.
• The client index moved into IndexedDB — no memory ceiling, no rebuild-on-load, transactional index writes, and byte-identical results between client and server. The old 5000-document auto-switch is replaced by coverage-based routing (see Query routing), and synced sparse documents no longer carry vector[...] fields (vector search is server-only).
• schema.array(schema.enum([...])).searchable() now actually works (enum[] in the index schema: filterable and facetable, never term-matched), and searchable arrays now appear in the inferred Database.SearchSchema<Table> type.

Architecture

  ┌─────────────────────────────────────────────────────┐
  │               Cloudflare Durable Object             │
  │                                                     │
  │  ┌────────────┐   ┌──────────┐   ┌───────────────┐  │
  │  │   Schema   │──>│  SQLite  │   │  Search Tables│  │
  │  │ Definition │   │ (storage)│   │   (SQLite)    │  │
  │  └────────────┘   └──────────┘   └───────────────┘  │
  │        │               ▲ ▲               ▲          │
  │        │               │ │               │          │
  │        ▼               │ │               │          │
  │  ┌─────────────────────┴─┴───────────────┴───────┐  │
  │  │              DatabaseServer                   │  │
  │  │  create() get() update() delete() list() ...  │  │
  │  └───────────────────────────────────────────────┘  │
  │                        │                            │
  │                        ▼                            │
  │               ┌────────────────┐                    │
  │               │   WebSocket    │                    │
  │               │  (broadcast)   │                    │
  │               └────────────────┘                    │
  └─────────────────────────────────────────────────────┘

Schema → SQLite + search tables: Each table definition produces a SQLite table (for persistent storage) and a set of shared search tables (search_postings, search_tokens, search_docs, search_field_stats, search_vectors, plus search_state/search_tombstones for sync). The DatabaseServer writes both inside one transaction, so a rolled-back write can never leave a stale index behind.

Single-writer model: Durable Objects guarantee a single instance handles all writes, eliminating race conditions. SQLite transactions provide atomicity within that instance.

JSON catch-all column: Root-level scalar fields (string, number, boolean, enum, foreign key) get their own SQLite columns. Non-scalar fields (object, array, vector, geopoint) are serialized into a single json TEXT column and transparently deserialized on read.

Quickstart

1. Define your schema

import { Database } from '@delightstack/database';

const personTable = Database.table('person', (schema) => ({
	name: schema.string().min(1).max(100).searchable(),
	email: schema.string().email().unique().searchable(),
	role: schema.enum(['admin', 'user', 'guest']).searchable(),
	bio: schema.string().optional(),
	avatar_url: schema.string().url().optional(),
}));

const postTable = Database.table('post', (schema) => ({
	title: schema.string().searchable(),
	body: schema.string().searchable(),
	author_id: schema.foreignKey({
		type: 'string',
		table: 'person',
		column: 'id',
		on_delete: 'CASCADE',
	}),
	tags: schema.array(schema.string()).searchable().optional(),
	published: schema.boolean().default(false),
}));

export const tables = { person: personTable, post: postTable };

id, created_at, and updated_at are auto-managed — do not declare them. Every table gets a string id primary key injected automatically (declare schema.primaryKey() yourself only for a numeric auto-increment key or a custom name). created_at / updated_at are epoch-millisecond numbers set by the server on every write; declaring either in your schema throws at table-definition time. Declaring them as datetime strings (the v1 pattern) would break parse() — they are numbers now.

2. Create your Durable Object

// worker entry point (e.g. server/src/index.ts)
import { DatabaseServer } from '@delightstack/database/worker';
import { tables } from './schema';

export class MyDatabase extends DatabaseServer<typeof tables> {
	constructor(ctx: DurableObjectState, env: Env) {
		super(tables, () => env.WEBSOCKET, ctx, env);
	}
}

Import DatabaseServer from @delightstack/database/worker, not the package root. The class imports cloudflare:workers, which only resolves inside the Workers runtime — the main entry (and /server) export only its type, so import { DatabaseServer } from '@delightstack/database' fails to resolve the value in a SvelteKit/Vite build. The /worker entry belongs in your Worker entry point; app code that needs the type uses import type { DatabaseServer } from '@delightstack/database'.

The second constructor argument lazily returns your WebSocket Durable Object (any object with an entityChanged(action, entity_type, id, data?, sparse?) method) for broadcasting changes; return undefined to skip broadcasting.

On first instantiation, DatabaseServer automatically creates the SQLite tables, indexes, and search tables. On subsequent runs, it detects schema changes and adds new columns. It does not automatically delete old tables.

3. Use the API

// Create
const person = db.create('person', {
	email: '[email protected]',
	name: 'Alice',
	role: 'admin',
});
// person.id is auto-generated, created_at/updated_at are set automatically

// Read
const alice = db.get('person', person.id);

// Read with foreign key expansion
const post = db.get('post', post_id, ['author_id']);
// post.expanded.author_id → full person record

// Update (deep partial merge)
db.update('person', person.id, { name: 'Alice B.' });

// Delete
db.delete('person', person.id);

// Search
const results = db.list('person', {
	term: 'alice',
	where: { role: 'admin' },
	limit: 20,
});

// Raw SQL
const rows = db.exec(`SELECT * FROM person WHERE role = ?`, 'admin');

// Tagged template (prevents SQL injection)
const rows = db.exec((sql) => {
	const role = 'admin';
	return sql`SELECT * FROM person WHERE role = ${role}`;
});

Schema Reference

Reserved field names. id is auto-injected (unless you declare your own primary key), created_at/updated_at are auto-managed, and and/or/not/$derived are reserved (where-grammar keywords and the internal derived-values store). Declaring any of them is a compile-time and runtime error.

Field Types

| Type | Constructor | Storage | Notes | | --------------- | ------------------------------- | --------------------- | ------------------------------------------------------------------------------------- | | Primary Key | schema.primaryKey() | TEXT PRIMARY KEY | Optional — an id string key is auto-injected when omitted. { type: 'number' } for auto-increment integer. | | String | schema.string() | TEXT | Format validators, length constraints, regex patterns. | | Number | schema.number() | NUMERIC | Use .int() for INTEGER. Supports min/max and a form step. | | Boolean | schema.boolean() | BOOLEAN | Stored as 0/1 in SQLite. | | Enum | schema.enum(['a', 'b']) | TEXT | Constrained to the provided values. Also accepts [{ value, label }] pairs — labels drive form Select options. | | Foreign Key | schema.foreignKey({...}) | TEXT REFERENCES ... | Typed reference to another table with cascade options. | | Object | schema.object({...}) | json column | Nested fields. Stored in the internal JSON overflow column. | | Array | schema.array(schema.string()) | json column | Typed array. string[]/number[]/boolean[]/enum[] items can be .searchable(). | | Geopoint | schema.geopoint() | json column | { lat, lon }. Always searchable for geospatial queries. | | Vector | schema.vector(768) | json column | Fixed-dimension embedding. Always searchable; vector search runs server-side only. | | Blob | schema.blob({ max_bytes }) | BLOB | Raw bytes (Uint8Array/ArrayBuffer). Never searchable, never in a sync payload. See Binary Data & File References. | | File | schema.file({ store }) | json column | A { key, size, mime, sha256?, name? } reference to an object in an external store. Read the bytes with resolveFile(ref, store). |

Modifiers

schema
	.string()
	.optional() // Nullable (stored as NULL)
	.readonly() // Immutable after creation
	.default('foo') // Default value (or a function: () => value)
	.searchable() // Synced to the client AND indexed for full-text search
	.carried() // Synced to the client, never indexed (see Field tiers)
	.sortable() // Sortable in search results (implies searchable)
	.indexable() // SQLite B-tree index for fast WHERE queries
	.unique() // UNIQUE constraint in SQLite
	.label('Name') // UI label for form generation
	.placeholder('Enter name') // UI placeholder
	.description('Shown below the input') // UI helper text
	.check((v) => (v === 'bad' ? 'Not allowed' : undefined)); // Custom validation

Not every modifier exists on every type: indexable/unique are for string and number fields (which own a SQLite column); default, label, placeholder, description, check, and derived are for the scalar types (string, number, boolean, enum); arrays support min/max (length), label, placeholder, description, and searchable; optional/readonly work everywhere. blob() and file() deliberately expose neither searchable nor sortable nor indexable nor unique — bytes and object references have nothing to tokenize, so the builders simply don't exist and a mistake is a compile error rather than a silently-dead index. blob() additionally offers .maxBytes(n) as the chainable form of its constructor option.

String Formats

Mutually exclusive — pick one. Formats compose with .min()/.max() (both are checked).

schema.string().email(); // RFC email
schema.string().url(); // Valid URL
schema.string().uuid(); // UUID
schema.string().datetime(); // ISO 8601 datetime
schema.string().date(); // YYYY-MM-DD
schema.string().time(); // HH:MM[:SS]
schema.string().ipv4(); // IPv4 address
schema.string().ipv6(); // IPv6 address
schema.string().base64(); // Base64 string
schema.string().color(); // Hex color (#RGB or #RRGGBB)
schema.string().password(); // Masked input (UI hint only)
schema.string().phone(); // Phone number (UI hint only)

Input coercion. Before validating, parse() (and the form standard schema — both share one validator) converts inputs with a single canonical representation to the field's declared type:

• Date → epoch-ms number for number() fields (including created_at/updated_at), "YYYY-MM-DD" for .date(), "HH:MM:SS" for .time(), full ISO 8601 for .datetime()
• URL instance → .href for .url() fields
• Whole numeric strings ("42", " 4.5 ") → numbers for number() fields — so native FormData values validate
• 'true'/'on'/'1'/1 → true and 'false'/'0'/0 → false for boolean() fields
• Float32Array/Float64Array (and other typed arrays) → plain number arrays for vector() fields
• { latitude, longitude } (e.g. a GeolocationCoordinates) → { lat, lon } for geopoint() fields; out-of-range coordinates clamp

Ambiguous conversions are deliberately not attempted: JSON strings never auto-parse, ISO text never coerces into plain number() fields, and a Date into an unformatted string() field is still an error.

String Validators

schema
	.string()
	.min(1) // Minimum length
	.max(255) // Maximum length
	.regex(/^[A-Z]+$/) // Regex pattern (repeatable)
	.startsWith('user_') // Must start with the prefix
	.endsWith('.md') // Must end with the suffix
	.includes('@') // Must contain the substring
	.textarea(); // Renders as textarea in forms (no validation effect)

Transforms rewrite the value during parse() — they run in declaration order, before validators (so .trim().min(1) rejects a whitespace-only string), and the transformed value is what gets stored:

schema.string().trim(); // Strip surrounding whitespace
schema.string().toLowerCase(); // e.g. emails, slugs
schema.string().toUpperCase();
schema.string().normalize(); // Unicode normalization (NFC by default)

Anything beyond these goes through .check() (runs last, after transforms and built-in validators):

schema.string().check((value) => {
	if (value.endsWith('.test')) return 'Test domains are not allowed';
});

Number Validators

schema
	.number()
	.int() // Must be integer (INTEGER column)
	.min(0) // >= value (inclusive)
	.max(100) // <= value (inclusive)
	.gt(0) // > value (exclusive — "price must be positive")
	.lt(100) // < value (exclusive)
	.step(0.01); // Must be a multiple (decimal-safe) + sets the form step attribute

Derived Fields

A derived field is search-only: computed from other fields at index time, never stored in SQLite, absent from Database.Entity but present in Database.SearchEntity. derived() implies searchable + readonly.

const personTable = Database.table('person', (s) => ({
	first_name: s.string(),
	last_name: s.string(),
	// Same-table derived value
	full_name: s.string().derived((data) => `${data.first_name} ${data.last_name}`),
	organization_id: s.foreignKey({ type: 'string', table: 'organization', column: 'id' }),
	// Cross-table derived value: declare the FK dependencies, receive the
	// referenced rows. Writes to the referenced organization cascade a reindex.
	organization_name: s
		.string()
		.derived(['organization_id'], (data, refs) => refs.organization_id?.name ?? ''),
}));

Foreign Keys

schema.foreignKey({
	type: 'string', // Type of the referenced column
	table: 'person', // Referenced table name
	column: 'id', // Referenced column name
	on_delete: 'CASCADE', // CASCADE | SET NULL | RESTRICT | NO ACTION | SET DEFAULT
	on_update: 'CASCADE', // Same options
});
// or chain: schema.foreignKey({...}).onDelete('CASCADE').onUpdate('CASCADE')

Foreign key constraints are always enforced in Durable Object SQLite (workerd compiles it with foreign keys ON by default).

Field tiers: indexed, carried, server-only, excluded

Whether a field reaches the client and whether it is searchable are two separate questions, and every combination of the two is expressible:

| Tier | In sync() / the client's cached entity | In the search index | | --- | --- | --- | | searchable — .searchable() / .sortable() | yes | yes, both sides | | carried — .carried() | yes | no | | server-only — .searchable().serverOnly() | no | yes, server only | | excluded — the default | no | no |

A carried field is delivered to the client but never tokenized. It gets no entry in index_schema, joins neither searchable_fields nor sortable_fields, and cannot be used in a where clause, in order, or in a search's fields list — attempting any of those is a compile error in a typed schema, and a DelightError (carried_field_not_queryable, status 400) at runtime.

const page = Database.table('page', (s) => ({
	id: s.primaryKey(),
	title: s.string().searchable(), // synced and indexed
	rendered_html: s.string().carried(), // synced, never indexed
	hero: s.file({ store: 'MEDIA' }), // carried automatically
	thumbnail: s.blob(), // neither — stays on the server
}));

Reach for .carried() for anything a client renders but nobody searches: a rendered HTML body, a layout config object, a media descriptor. It keeps the term dictionary and posting lists small without forcing a second round-trip to fetch the value.

Know the cost, though: a carried field is shipped to every client on every change to the row, and stored in each one's IndexedDB. A multi-megabyte carried value bloats the sync payload and the client's local cache in proportion — the tier exists precisely for largish values, so this is a trade to make deliberately rather than a rule against it.

.carried() exists on every field type except blob(), and is mutually exclusive with .searchable() / .sortable() — declaring both throws when the table is built. Only top-level carried fields appear in the SearchEntity type; a field carried from inside an object is projected at runtime and listed in config.carried_fields, but is not expressible in the type.

Server-only searchable fields

.serverOnly() indexes a field in the Durable Object and keeps it out of the sync payload. It is the mirror image of .carried(): carried means the client gets the value and the index does not, server-only means the index gets it and the client does not.

const note = Database.table('note', (s) => ({
	id: s.primaryKey(),
	title: s.string().searchable(), // synced and indexed everywhere
	body: s.string().searchable().serverOnly(), // indexed here, never shipped
}));

Combine it with .searchable() in either order. On its own it would describe a field that is neither synced nor indexed — which is just the default tier — so it throws when the table is built.

This is the only way to make a large field full-text searchable without copying it to every device. A full document body is the motivating case: you want term search across every body in the vault, and you emphatically do not want every body in every client's IndexedDB.

A query that names such a field — in where, order, facets, boost, fields, or distinct_on — routes to the server, the same way vector and sparse: false queries already do. Forcing source: 'client' on one is a DelightError (invalid_search_source, status 400) naming the field, because the local index provably cannot answer it.

A bare term search is deliberately not redirected. Its default fields: '*' means "everything indexed here", and on the client that is legitimately a subset — ordinary coverage-based routing, not an unanswerable query. If you need a term search to see body text, ask for the server.

Three things follow from the tier being server-only:

• It cannot be .sortable(), and it cannot be .carried(). Sorting is something the client index does too, and it cannot order by a value it never receives; carried is the opposite tier outright. Both throw when the table is built.
• The value is held back from the wire, not from the database. db.get() and any sparse: false read return it as normal — it is the sync payload and the client index that never see it.
• It is typed absent on the synced document. SearchEntity gives the field type undefined, so reading it from a synced entity is a compile error rather than a silent any. The full Entity type still has it.

There is deliberately no client-only counterpart. index_schema is a single object and the server indexes whatever it stores, so a client-only tier would mean maintaining a second, divergent schema. A value only the client needs is .carried().

Binary Data & File References

Two field types carry binary content, and which one you want depends on where the bytes live.

const documentTable = Database.table('document', (schema) => ({
	title: schema.string().searchable(),

	// Bytes stored in the row itself, in a real SQLite BLOB column.
	snapshot: schema.blob({ max_bytes: 5_000_000 }).optional(),

	// A descriptor pointing at an object in the `MEDIA` binding.
	cover: schema.file({ store: 'MEDIA' }).optional(),
}));

schema.blob({ max_bytes })

Raw bytes in a BLOB column. Accepts a Uint8Array, an ArrayBuffer, or any ArrayBufferView, and reads back as a Uint8Array.

• max_bytes is validated on every write; exceeding it throws a DelightError (400) whose message names the limit and the received size. .maxBytes(n) is the chainable equivalent.
• A blob is never tokenized, so it is not in the search index — and there is no .searchable() builder on it to suggest otherwise.
• A blob is never part of a sync() payload. Broadcasting a multi-megabyte column to every connected client on every change is never the intent, so the field type simply cannot opt in.
• A blob is opaque to update()'s deep merge: writing a new value replaces the old bytes rather than merging them index-by-index.
• A blob is stripped from the change log before recording — see the caveat there.

Durable Object storage has a hard 2 MB per value limit and bills per row read, so keep blobs small (CRDT snapshots, thumbnails, signatures). Anything user-sized belongs in a store, behind a file().

schema.file({ store })

A reference, not a payload. The row holds only:

interface FileReference {
	key: string; // The object key within the store
	size: number; // Size in bytes
	mime: string; // MIME type
	sha256?: string; // Hex SHA-256, when the store or the caller supplies one
	name?: string; // The original filename, for uploads
	store?: string; // Overrides the field's store for this row
	metadata?: Record<string, string>; // App-defined string pairs
}

store is the name of a binding, not the binding itself — a binding cannot cross a Durable Object RPC boundary, and the reference outlives whichever backend it currently points at. key, size and mime are required and validated on write; sha256 must be 64 hex characters when present.

Per-row store

The store passed to schema.file({ store }) is the field's default, not a constraint. A reference may carry its own store, and the effective store for a value is:

const store_name = row.cover?.store ?? 'MEDIA'; // reference.store ?? field default

Without the override every row in a column is pinned to one bucket forever, which rules out three ordinary things: migrating a bucket (old rows must keep pointing at the old one until they are copied), hot/cold tiering (an archived object moves to cheaper storage while its row does not move at all), and per-tenant buckets (one column, one bucket per customer). It must be a non-empty string when present — a blank one is a caller who built the name from something missing, not a request for the default.

resolveFile() takes the binding you hand it and does not do this lookup itself (it would need the whole env and the schema, both of which are yours). Pick the binding from the effective store, or a migrated row is looked up in the old bucket and reads as null:

const object = await resolveFile(row.cover, platform.env[row.cover?.store ?? 'MEDIA']);

metadata

An open bag of app-defined string pairs travelling with the reference — page count, duration, EXIF date, encoding, whatever this particular kind of file needs:

schema.file({ store: 'MEDIA' }); // the field is unchanged; the reference carries it
db.update('document', id, {
	cover: { key, size, mime, metadata: { pages: '12', taken: '2024-03-01' } },
});

Values are strings only. Numbers, null, arrays and nested objects are rejected with a message naming the key, deliberately mirroring how S3/R2 object metadata works — an app that later moves these pairs onto the real object finds the same shape waiting for it. Serialize anything richer yourself.

It exists so the descriptor does not grow a new optional field every time an application stores a new kind of file. But the reason it lives on the reference rather than in sibling columns is stronger than tidiness: it is written in the same value as key, so it moves atomically with the file. Replacing the object replaces its description in one write, and there is no window in which the row claims a 12-page PDF while key already points at the 40-page one.

metadata is not indexed and not queryable — it rides inside the json overflow column like the rest of the reference. Declare a real field for anything you need to search or sort on.

The reference is stored in the internal json overflow column (it is a small object, not a scalar), and it is not tokenized — there is no .searchable() builder on a file field. If you want filename search, declare a separate schema.string().searchable() alongside it.

A file reference is carried by default (see Field tiers): it syncs to the client, because its whole purpose is to let the client build a URL, but it never enters the index. Opt out for a reference the client has no business holding:

schema.file({ store: 'PRIVATE', carried: false });

A non-carried reference reaches neither tier — exactly like a blob(). It is still stored on the row and still readable server-side with db.get(); it simply never leaves the Durable Object. Use it for a private object key you do not want sitting in every browser's IndexedDB.

Read the bytes with resolveFile, which is structurally typed against { get(key) } — a Cloudflare R2Bucket satisfies it, and so does any S3/GCS/filesystem wrapper you write:

import { resolveFile } from '@delightstack/database';

// In the Worker or SvelteKit endpoint that holds the binding — NOT inside the
// Durable Object, which has no access to it.
const doc = await locals.db.get('document', id);
const object = await resolveFile(doc.cover, platform.env.MEDIA);
const bytes = object ? await object.arrayBuffer() : undefined;

resolveFile returns null for a missing reference and for a missing object, so a deleted object reads the same way as a never-set field. It does not verify sha256, size or mime against the bytes it returns — those are checked on write. Verify on read yourself if the store is not trusted.

The database never writes to the store and never deletes from it. Uploading the object is the application's job — a file() field is a pointer, and deleting a pointer does not delete what it points at. It does, however, tell you which pointers died; see below.

File deletion queue

Deleting a row deletes its reference, not its object. Cleaning up the orphan is the application's job, but the application cannot do it unaided: by the time it sees the row is gone, the key went with it. Only the database knows when a row died and what key it held, inside the same write transaction — so it records that, in an internal _file_gc table, and you drain it.

The queue is created at boot when at least one table has a file() column (a database with none never creates it, and the read APIs throw a DelightError with code: 'file_gc_disabled'). A row is enqueued when, inside the write transaction:

• a row holding a file reference is deleted — every reference it held is queued; or
• an update replaces a reference with one whose key differs — the old key is now unreachable from that row.

Nothing is enqueued on create, on an update that leaves the key alone (changing mime, name or metadata describes the same object better), or on an update that changes only store (same key in a new bucket is a migration record, not an orphaning). Queue rows are written in the entity's transaction, so a rolled-back write leaves none behind.

The store recorded is the reference's own store when it has one, else the field's default — so an object that was migrated to another bucket is deleted from the bucket it actually lives in.

for (const pending of db.pendingFileDeletions({ limit: 100 })) {
	// pending: { id, store, key, entity_type, entity_id, deleted_at }
	await env[pending.store].delete(pending.key);
	db.releaseFileDeletion(pending.id);
}

// Or drain a page and release it in one statement:
const page = db.pendingFileDeletions();
await Promise.all(page.map((p) => env[p.store].delete(p.key)));
db.releaseFileDeletions(page.map((p) => p.id));

Rows come back oldest-first; limit defaults to 100 and clamps to 1000. Delete first, release second — a crash in between re-delivers the row, and deleting an already-deleted object is a no-op in every store worth using, whereas the reverse order loses the key forever. releaseFileDeletion is idempotent and returns whether a row was actually removed; releaseFileDeletions returns how many went.

The queue reports intent, not safety. It records that this row stopped referencing this key. It does not know whether some other row still points at the same object. If your keys are content-addressed — a SHA-256 key, a dedup layer, a "duplicate" that reuses the key — then two rows can share one object, and deleting on this signal alone breaks the survivors. In that case you must check for remaining references yourself before calling the store's delete, and release the queue row either way.

Reference counting is deliberately not implemented, and no LIKE-based scan ships. File references live inside the internal json overflow column, so the only way for the database to find other holders of a key would be a LIKE scan across every row of every table with a file() column — expensive on storage billed per row read, and wrong besides: LIKE '%key%' matches keys that merely contain each other, and matches the key appearing anywhere else in the JSON. An application that dedups by key knows its own dedup rule and can answer the question with an index; the database cannot.

The queue tracks every file() field, including one nested inside an object() — such a reference is recorded in config.file_fields at its dot path (payload.attachment) and enqueues like any other.

Indexes

// Simple index
schema.string().indexable();

// Named unique index
schema.string().indexable({ name: 'idx_email', unique: true });

// Composite index (covering)
schema.string().indexable({
	additional_columns: [{ column: 'created_at', descending: true }],
});

Type Inference

The schema system infers TypeScript types from your field definitions:

const personTable = Database.table('person', (schema) => ({
	name: schema.string(),
	age: schema.number().int().optional(),
	role: schema.enum(['admin', 'user']),
}));

type Person = Database.Entity<typeof personTable>;
// {
//   readonly id: string;          // auto-injected primary key
//   name: string;
//   age?: number | null;
//   role: 'admin' | 'user';
//   readonly created_at: number;  // epoch ms, auto-managed
//   readonly updated_at: number;  // epoch ms, auto-managed
// }

Optional fields become T | undefined | null. Readonly fields get the readonly modifier. Enum values are narrowed to their literal union. Other useful types: Database.SearchEntity<Table> (the sparse indexed document), Database.SearchQuery<Table> (a typed query), and Database.Table (the widened constraint any table satisfies).

DatabaseServer API

Constructor

class DatabaseServer<Config, Meta> extends DurableObject {
	constructor(
		config: Config, // Record of table definitions
		ws: () => DatabaseBroadcast | undefined, // Lazy WebSocket DO for broadcasting
		ctx: DurableObjectState, // Durable Object context
		env: Env, // Environment bindings ({ DEV: boolean })
	);
}

The ws factory returns anything implementing the DatabaseBroadcast contract (WebsocketServer from @delightstack/websocket does), or undefined to skip broadcasting.

Instance name & alarms

export class OrgDatabaseServer extends DatabaseServer<typeof tables> {
	constructor(ctx: DurableObjectState, env: Env) {
		// The name this DO was created with via idFromName() (e.g. the org id).
		// Static so it's usable before super(); also available as `this.instance_name`.
		const room = DatabaseServer.instanceName(ctx);
		super(tables, () => env.WS.get(env.WS.idFromName(room)), ctx, env);
		// imageProcessing()/aiProcessing() call db.registerAlarm() themselves —
		// the base alarm() runs every registered handler with per-handler
		// error isolation, so no hand-written alarm() fan-out is needed.
	}
}

registerAlarm(name, handler) adds a named handler to the base alarm(). Only override alarm() when you need full control — the override then owns calling the registered handlers.

Typed stubs (DatabaseStub)

Cloudflare types DO stubs opaquely, so cast once at the boundary with DatabaseStub<typeof tables> — the async RPC projection of DatabaseServer — and every later call is fully typed:

// app.d.ts
import type { DatabaseStub } from '@delightstack/database';
interface Locals { db: DatabaseStub<typeof tables> | undefined }

// hooks.server.ts
event.locals.db = penv.DB.get(id) as unknown as DatabaseStub<typeof tables>;

// anywhere on the server
const post = await locals.db.get('post', post_id); // Database.Entity<typeof postTable>

Only RPC-serializable methods are projected — the tagged-template exec overload and batch() take function arguments that don't survive the RPC boundary.

CRUD

| Method | Signature | Notes | | ---------- | -------------------------------------------- | ------------------------------------------------------------------------------------ | | create | create(type, data, options?) → Entity | Auto-generates ID and timestamps. Validates against the schema. Updates search index. | | get | get(type, id, expand?) → Entity | Throws DelightError (404) if not found. expand populates foreign key references into entity.expanded. | | update | update(type, id, data, options?) → Entity | Deep partial merge. Validates merged result. Updates search index. | | delete | delete(type, id, options?) → void | Throws DelightError (404) if not found. Removes from SQLite and search index. Tracks deletion (tombstone) for sync. |

All CRUD methods are synchronous (SQLite in Durable Objects is synchronous).

create() strips id, created_at, and updated_at from input data — these are auto-managed. update() auto-sets updated_at, and additionally strips every .readonly() field from the incoming partial. Validation failures throw DelightError (status 400) with an issues array of { path, message }.

update() is a deep merge, which has one consequence worth stating plainly: an undefined value means "leave this field alone", not "clear it". To clear an optional field, pass null.

The trailing options is WriteOptions — currently just { actor }, threaded into the change log. transaction(), batch() and revert() take the same trailing argument. See Actor attribution.

Search & List

db.list(type, query) runs a search query and returns matching documents. All query fields:

db.list('person', {
  // Full-text search term (prefix-matched per token by default)
  term: 'alice',

  // Which searchable fields the term matches against. '*' (default) = all.
  fields: ['name', 'email'],

  // Filters applied before scoring (see the where grammar below)
  where: { role: { eq: 'admin' } },

  // Ordering — only `updated_at` and fields marked `.sortable()` are valid.
  // Browse queries (no term/vector) default to updated_at DESC; relevance
  // queries default to score order.
  order: [{ field: 'updated_at', direction: 'DESC' }],

  // Pagination
  limit: 20, // clamped to 5000 sparse / 100 full; defaults 100 sparse / 10 full
  offset: 40, // skip N results (clamped to 100 000)
  cursor: previous.cursor, // keyset cursor — when set, other params are ignored
                           // (except your `where`, which is re-ANDed for safety)

  // Facet counts alongside the results
  facets: {
    role: { limit: 10, sort: 'desc' }, // string/enum facet
    age: { ranges: [{ from: 0, to: 18 }, { from: 18, to: 65 }] }, // number facet
    published: {}, // boolean facet — both buckets always reported
  },

  // Per-field score multipliers during term matching
  boost: { title: 2 },

  // Match the term exactly instead of by prefix
  exact: false,

  // Max levenshtein distance for typo tolerance (clamped to 0–3)
  tolerance: 1,

  // Multi-token combining: 0 = every token must match, 1 (default) = any
  // token, fractional = all-token matches + that top fraction of partials
  threshold: 0.5,

  // Keep only the first result per distinct value of this field
  distinct_on: 'author_id',

  // Vector search (server-only). `vector` alone = vector search,
  // term + vector = hybrid, term alone = full text. `similarity` is the
  // inclusive minimum cosine similarity (default 0.8).
  vector: { value: [0.1, 0.2 /* … */], field: 'embedding', similarity: 0.8 },

  // true (default) = sparse search documents; false = full entities from
  // SQLite. Like `vector`, `sparse: false` is server-only — on the client it
  // routes the query to the server (only the server has the full rows).
  sparse: true,

  // Client-only routing override — see the client's Query routing section.
  // Ignored by the server. 'client' cannot be combined with `vector` or
  // `sparse: false` (both compile-time and runtime errors).
  source: 'auto',
});

Returns:

{
  count: number;       // Total matching results
  elapsed: { raw: number; formatted: string };
  cursor: string | null; // Next page cursor (base64); null when exhausted
  facets?: FacetResult;  // When facets were requested
  hits: Array<{
    id: string;
    document: Entity | SearchEntity; // full or sparse, per `sparse`
    score: number;
  }>;
}

The where grammar

where: {
  // string / string[] fields — exact token match (value or one-of array)
  name: 'alice',
  tags: ['a', 'b'],

  // number fields — comparison operators
  age: { gte: 18, lt: 65 },       // gt / gte / lt / lte / eq / between: [a, b]

  // boolean fields
  published: true,

  // enum fields
  role: { eq: 'admin' },           // eq / in / not_in
  role: 'admin',                   // shorthand — normalized to { eq: 'admin' }
  role: ['admin', 'editor'],       // shorthand — normalized to { in: [...] }

  // enum[] / array fields
  labels: { contains_all: ['a', 'b'] }, // every value present
  labels: { contains_any: ['x', 'y'] }, // at least one present

  // geopoint fields
  location: { radius: { coordinates: { lat, lon }, value: 5, unit: 'km' } },
  location: { polygon: { coordinates: [{ lat, lon }, /* … */] } },

  // composites (nestable up to 10 levels)
  and: [ { role: { eq: 'admin' } }, { or: [ /* … */ ] } ],
  not: { role: { eq: 'guest' } },
}

Raw SQL

// String + bindings
db.exec(`SELECT * FROM person WHERE role = ?`, 'admin');

// Tagged template (recommended — prevents SQL injection)
db.exec((sql) => {
	const role = 'admin';
	return sql`SELECT * FROM person WHERE role = ${role}`;
});

Returns Record<string, SqlStorageValue>[].

Transactions

Batch multiple operations atomically. Order is preserved, so later operations can depend on earlier ones:

const results = db.transaction([
  { create: { type: 'person', data: { name: 'Alice', ... } } },
  { create: { type: 'post', data: { title: 'Hello', ... } } },
  { update: { type: 'org', id: 'org-1', data: { user_count: 5 } } },
  { delete: { type: 'invite', id: 'inv-1' } },
  { exec: { statement: 'UPDATE stats SET count = count + 1' } },
]);

Maximum 5,000 operations per transaction. All operations succeed or all roll back — including the search index writes.

A create operation additionally accepts preserve_id: true, which keeps the id and created_at present in data instead of generating fresh ones. This is what makes restoring a deleted row give you the row back rather than a copy of it — it powers revert() of a delete, and is equally useful for imports and restores.

db.transaction([
	{ create: { type: 'post', data: archived_row, preserve_id: true } },
]);

Inserting onto an id that is still live throws a DelightError (entity_exists, 409) rather than SQLite's raw UNIQUE constraint failed — the shape a double-revert() or a re-run import actually produces.

db.create() takes the same option directly, which is what lets a client mint a row's id before the server has ever seen it:

db.create('post', { id: client_minted_id, title: 'Draft' }, { preserve_id: true });

Idempotent writes (op_id)

Every mutation crosses at least one retry boundary — an offline queue drain, a replayed sync, an agent that timed out and tried again. Pass an op_id and the write becomes idempotent:

db.create('post', data, { op_id });
db.update('post', id, patch, { op_id });
db.delete('post', id, { op_id });
db.transaction(operations, { op_id });

The first write carrying an id is applied and recorded in an internal _op_log. Every later write carrying the same id is not applied and returns the original result. Mint the id on the client — generateTimestampID() from @delightstack/utilities — before the first attempt; an id minted per attempt deduplicates nothing.

db.appliedOperation(op_id);
// → { op_id, kind, table, entity_id, result, created_at } | undefined

Details worth knowing:

• The log row commits with the write. A write that throws records nothing, so retrying a failed operation is a real retry rather than a silent no-op.
• A replayed delete is a no-op, not a 404. The row is gone because this operation removed it; re-raising "not found" would turn a successful drain into a permanent failure.
• blob() columns are recorded as omitted, exactly as the change log records them — a Uint8Array has no lossless JSON form, and the expanded one would blow past the Durable Object's 2 MB per-value ceiling. A replayed write therefore returns the __blob_omitted marker where the bytes were. file() references are small descriptors and are recorded verbatim.
• batch() refuses an op_id (op_id_unsupported, 400): its return value is arbitrary, so there is nothing the log could hand back on replay. Put the id on transaction() or on the individual mutators.
• Retention is 7 days, swept by a registered alarm. An unknown op_id means "never applied, or applied more than a week ago".
• The table is created lazily, on the first write that carries an op_id — a database that never uses one never gets it.

The SvelteKit handler reads the id from an Operation-ID request header (a header, not a body field: the body is the entity, and every byte of it goes through table.parse()). The DatabaseClient outbox sets it automatically — see Offline writes.

Change Log & History

History is opt-in per table, via the third argument to Database.table():

const postTable = Database.table(
	'post',
	(schema) => ({
		title: schema.string().searchable(),
		body: schema.string().searchable().optional(),
	}),
	{ history: true, history_retention_days: 90 },
);

| Option | Default | Description | | ------------------------ | ------- | --------------------------------------------------------------------------- | | history | false | Record every create/update/delete for this table in _change_log. | | history_retention_days | 365 | How long rows survive the retention sweep. 0 keeps them forever. Must be a non-negative number, or Database.table() throws. Ignored unless history is on. |

When at least one table opts in, a shared _change_log table (plus two indexes) is created at boot. It is idempotent, so turning history on for a table later needs no migration — the first wake after the config change creates the table. When no table opts in, the table is never created and the read APIs throw a DelightError with code: 'history_disabled'.

What gets recorded

interface ChangeLogEntry {
	id: string; // The change's own id (time-sortable)
	table: string; // The entity type
	entity_id: string; // The changed entity's primary key, as text
	operation: 'create' | 'update' | 'delete';
	actor: string; // 'system' when the write was unattributed
	operation_id?: string; // The operation this change was part of, if any
	patch: Record<string, unknown> | undefined; // What the change wrote
	previous: Record<string, unknown> | undefined; // What it overwrote
	created_at: number; // Epoch ms, from the Durable Object's clock
}

| Operation | patch | previous | | ---------- | ------------------------------------------- | -------------------------------- | | create | the full entity | undefined | | update | only the fields whose value actually changed | the previous value of exactly those fields | | delete | undefined | the full entity |

Recording the whole entity twice per update would make the log larger than the table it describes within a handful of edits; a field-level diff keeps a typical single-field edit at a few dozen bytes, and is exactly what revert() needs. Rough overhead: a create costs roughly one extra copy of the row, an update costs about twice the size of the fields it touched, and a delete costs one copy of the row.

Three behaviours follow from that and are worth knowing:

• updated_at is excluded from update diffs. It changes on every write by definition, so recording it would make every diff non-empty and tell the reader nothing.
• A no-op update writes no change row. A save button that changed nothing must not manufacture an audit entry.
• Change rows are written inside the entity's write transaction. A rolled-back write leaves no history claiming it happened.

blob() bytes are never recorded. A change log that copied binary payloads would multiply the storage cost of exactly the largest thing in the row (a 5 MB blob updated ten times would cost 50 MB of history), and JSON has no compact representation for bytes — JSON.stringify(new Uint8Array([1, 2])) is {"0":1,"1":2}, roughly five bytes of JSON per byte of payload, which would also breach the Durable Object's 2 MB per-value limit.

Each omitted column is recorded as a { __blob_omitted: true } marker rather than dropped, so revert() can distinguish "bytes we did not keep" from "this column was empty". Reverting a change that would lose blob data throws a DelightError (blob_not_recoverable, 409) naming the columns instead of restoring an incomplete row. Pass { without_blobs: true } to accept the loss and restore everything else — the columns are left unset, so it still fails if one is required.

db.revert(change_id); // throws: 'bytes' would be lost
db.revert(change_id, { without_blobs: true }); // restores the rest

file() references, which are small descriptors rather than payloads, are recorded normally.

Reading

// One entity's changes, newest first. `before` pages backwards through time —
// pass the `created_at` of the oldest entry you already have.
db.history('post', post_id, { limit: 50, before: cursor });

// Every change at or after a timestamp, oldest first — the feed shape (audit
// trails, outbound replication, "what happened while I was away").
db.changesSince(last_seen, { limit: 500, table: 'post' });

// Narrowed to one operation, alone or together with `table`.
db.changesSince(last_seen, { operation: import_id });

history() defaults limit to 50 and clamps it to 1000; changesSince() defaults to 500 and clamps to 5000. The changesSince timestamp boundary is inclusive.

Reverting

const [latest] = db.history('post', post_id);
const restored = db.revert(latest.id); // → the resulting entity, or undefined

| Reverting a… | Does | | ------------ | -------------------------------------------------------------------- | | create | deletes the row (returns undefined) | | update | writes back the previous values of exactly the fields it changed | | delete | recreates the row with its original id and created_at |

The revert is itself an ordinary mutation, so it appends its own change-log entry attributed to the current actor. History is append-only and a revert can be reverted. An unknown change_id throws 404; a change whose table is no longer in the config throws 400.

revert() on an update restores readonly and cleared fields. It is built on update() but deliberately opts out of two of its rules. A previously-unset value is recorded as null rather than being dropped by JSON.stringify, so clearing a field is revertible. And .readonly() columns are written rather than stripped — a revert restores a state the row demonstrably held, so the usual immutability guard would skip exactly the columns it was asked to put back.

Operations — grouped undo

Row-level undo is rarely what anyone wants. "Undo that import", "undo that bulk retag", "undo what the agent just did" are operations spanning thousands of rows, and nothing else in the log can reconstruct which rows belonged together — timestamps and actor both group too much. So every write takes an optional { operation }, recorded on each change row it produces:

const import_id = crypto.randomUUID();

db.create('post', data, { actor: 'import', operation: import_id });

// Or, for a whole run — `scoped()` takes the operation as a third argument:
const importer = scoped(db, 'import', import_id);
for (const row of rows) importer.create('post', row);

db.batchChanges(import_id); // every change of that operation, oldest first
db.revertBatch(import_id); // → 4_000 (how many changes were undone)

The batch id is opaque: any non-empty string, blank or whitespace-only treated as absent. It is threaded exactly like actor, so a bare-db write inside a scoped batch() inherits it too, and db.batch_id reports whichever batch the currently running write belongs to.

operationChanges() returns oldest-first — an operation is read as a story, and the order the writes happened in is the order that explains it. It returns [] for an unknown id (an operation is not an entity; "no changes under this id" is an ordinary answer). revertOperation() is the one that throws 404 / operation_not_found there, because undoing an import and silently undoing nothing hides the real bug, which is usually an operation id that never reached the writes.

Three properties distinguish revertBatch() from calling revert() in a loop:

• Reverse chronological. The operation is walked newest-first. This is a correctness requirement: an operation that creates a row and then updates it must undo the update before the create, or the update's revert hits a row that no longer exists.
• Atomic. The whole walk runs inside batch(), so one failure — a blob_not_recoverable, a foreign key, anything — rolls back every revert in the run. A half-undone import is worse than an un-undone one.
• Revertible. The reverts are themselves recorded, under a new operation id, so revertOperation(that_id) redoes the original operation. Pass { operation } to choose that id — the return value is a count, so this is the only way to know it up front — and one is generated when you do not.

db.revertOperation(import_id, { actor: 'user:bob', operation: 'undo-of-import' });
db.revertBatch('undo-of-import'); // redo

An operation of more than 5,000 changes throws 413 / operation_too_large: the whole revert runs in one Durable Object transaction — that is what makes it atomic — and a DO transaction has a CPU budget. Split the original work across several operation ids.

Retention

A change_log_retention handler is registered on the Durable Object's alarm at boot and re-arms itself daily. Each invocation deletes up to 1,000 rows per history-enabled table older than that table's history_retention_days; when a sweep fills its batch the alarm re-arms immediately rather than waiting a day, so a large backlog drains without ever exceeding the DO's CPU limit. A table with history_retention_days: 0 is skipped entirely.

The sweeper never pushes an existing earlier alarm later — alarms are a single slot shared by every registered handler, so it reads the current alarm first and leaves it alone when it already fires sooner.

Actor attribution

Every mutator takes an optional trailing WriteOptions:

db.create('post', { title: 'Hello' }, { actor: 'user:alice' });
db.update('post', id, { title: 'Edited' }, { actor: 'agent:claude' });
db.delete('post', id, { actor: 'user:alice' });
db.transaction(operations, { actor: 'import' });
db.batch(() => { … }, { actor: 'migration' });
db.revert(change_id, { actor: 'user:bob' });

The actor is recorded on every change-log row the write produces. An unset, empty, or whitespace-only actor becomes DEFAULT_ACTOR — the string 'system'. Actors are trimmed but otherwise opaque: the user: / agent: prefixes above are a convention, not a requirement.

For a run of writes that share one actor, scoped(db, actor) returns a handle that applies it to each:

import { scoped } from '@delightstack/database/worker';

const claude = scoped(db, 'agent:claude');
claude.create('post', { title: 'Drafted' });
claude.update('post', id, { body });
claude.batch(() => {
	// Writes made through the bare `db` inside a scoped run inherit the actor
	// too — `batch()` sets it ambiently for the duration of the callback.
	db.update('post', id, { title });
});

A third argume