Skip to content

@substrat-run/adapter-sqlite ​

The pure-SQLite scope host — real kernel semantics with no Cloudflare dependency. One SQLite file per scope, a per-scope actor for strict serialization, a directory database for fail-closed addressing, and a kernel-stamped event outbox.

It is not a mock: it is the adapter local development and CI run on, it passes the full conformance suite that the production adapter passes, and it is the reason the self-host and escrow story is literally true rather than aspirational.

sh
pnpm add @substrat-run/adapter-sqlite

Usage ​

ts
import { SqliteScopeHost } from '@substrat-run/adapter-sqlite';
import { UNSAFE_allowAllChecker, webCryptoSecretBox } from '@substrat-run/kernel';

const host = new SqliteScopeHost({
  dir: './data',                    // one .sqlite file per scope + _directory.sqlite
  checker: UNSAFE_allowAllChecker,  // omit for the default tuple checker (deny by default)
  secretBox: webCryptoSecretBox('k', key), // omit if you use no connectors — see below
  fetch: myFetch,                   // omit for the runtime's fetch
});

host.registerModule(myModule);
host.admin.createTenant(actor, { id: tenantId, slug, name });                // tenant first
await host.provisionScope(actor, { tenantId, scopeId, jurisdiction: 'eu' }); // idempotent
const stub = await host.getScope(principal, tenantId, scopeId);
await stub.invoke('workorder/create', input);
await host.close();

The options ​

optiondefaultwhy it is shaped that way
dirrequiredholds _directory.sqlite plus one <tenantId>__<scopeId>.sqlite per scope, and tstore__… files for per-tenant stores
checkerthe built-in tuple checkerdeny-by-default on empty tuples. The permissive one is called UNSAFE_allowAllChecker because the name is the warning
secretBoxunconfiguredseals per-tenant credentials at rest. Omitted, the host refuses to store a credential at all rather than storing it in the clear — every other surface keeps working, so a deployment using no connectors needs no key
fetchthe runtime's fetchegress for connectors, injectable so a test or a dev server can stand a provider up in memory. It is the only way to exercise a connector end to end before real credentials exist, and it stays the way to test failure paths a real provider will not produce on demand
clockthe wall clockwhat ctx.now() reads, and what the host judges elapsed time against: tuple and session expiry, entitlement expiry, schedule cadence. Hand in a frozen or scripted clock and an expiry becomes assertable without waiting. Rows that only stamp when something happened (an outbox row, an admin-log entry) stay on the real clock
attachmentExtractorsnonethe parsers attachment text is extracted with, e.g. defaultAttachmentExtractors() from @substrat-run/attachment-extractors. The adapter parses no file format itself; omitted, every upload records unsupported, which is a valid configuration
logSink · invocationLineSinkthe consolewhere ctx.log lines and the per-delivery / per-schedule-run invocation lines go, one JSON line each. A test passes a collector
connectorCallsa no-opwhere each connector call's data point goes. Self-host keeps the same shape and writes nothing; the hosted platform records them instead
versionIdnonethe registry id of the vertical version this host runs, stamped into every outbox row's version. Handed in by the harness, never read from the directory; omitted, rows read NULL

How the semantics map ​

Contract guaranteeImplementation hereIn production (the Cloudflare adapter)
strict serialization per scopein-process actor, one queue per scopeDurable Object single-threaded execution
scope storage isolationone SQLite file per scopeone DO (SQLite-backed) per scope
fail-closed addressing_directory.sqlite cross-checkdirectory + DO addressing
structured-clone boundaryexplicit structuredClone both directionsthe RPC boundary itself
stamped event envelopesoutbox table written in the same transactionsame, drained to the event spine
per-tenant relational storea separate tstore__*.sqlite filea minted per-tenant D1

What lives in the spine ​

Every scope database carries the kernel's own tables alongside your module's. Module code may read them for projections like a timeline; writing to them is a boundary-lint error, because a forged spine row is indistinguishable from a real one.

tablewhat it holds
_substrat_outboxstamped domain events, with the checks that authorized them (K-34) and a drained_at marker
_substrat_denialsrefused permission checks — recorded outside the transaction the denial rolled back (K-35)
_substrat_migrationsthe applied journal, per (module, version)
_substrat_tuplesscope-local permission tuples
_substrat_deliveriesexecutor delivery attempts, retries, and dead letters
_substrat_attachmentsattachment records against your entities
_substrat_platform_requestsdurable platform intents a vertical enqueued for the platform to execute
_substrat_schedule_statethe platform sweep's gating state, one row per unit it sweeps, keyed (kind, schedule_op): kind = 'schedule' for an operation keyed module/verb (when it last ran, how it ended), kind = 'freshness' for an expectation keyed freshness:<eventType> (when its verdict was last recorded, and what it was — nothing ran). The kind column is what tells the two apart, so a schedule operation that happens to be named freshness:… gets its own row rather than sharing the evaluator's
_substrat_idempotencyrecorded responses per (subject, Idempotency-Key), with the request fingerprint that tells a replay from a reuse; pruned after 24 hours
_substrat_job_runsone row per long, resumable run: its status, the resume cursor the last committed pass handed forward, a counter bag, start/end and last error. Retained after the run ends — it is what says afterwards what the walk did
_substrat_job_stepsthe named steps of the pass a run currently has in flight, with each one's committed result — what makes a resumed pass skip the steps that already succeeded. Dropped when a pass commits; a failed run keeps its last pass's rows as the record of how far it got. Either way it holds one pass's worth of steps rather than growing with the length of the walk

The directory database holds the other half — tenants, scopes, hostnames, verticals and their versions and channels, orgs, roles, entitlements, identity pools and links, connections and their sealed secrets, subject keys, impersonation sessions (_substrat_impersonations), and the admin, access and ops-failure logs.

The host surface ​

Beyond getScope, the host implements the admin-side contract the platform drives:

  • lifecycle — provisionScope, migrateScope, migrationFrontier, close
  • copies — snapshotScope, importScope, restoreScope, deleteSnapshot (snapshots & test copies)
  • stores — provisionTenantStore, openTenantStore, provisionBlobStore
  • delivery — registerExecutor, drainDue, executorDeadLetters
  • connectors — registerConnector, dispatchConnector, getConnectorScope, getConnectorAttachments
  • schedules — registeredSchedules, runDueSchedules, getSystemScope
  • resumable runs — registerJob, startJobRun, runDueJobs, jobRuns
  • platform intents — listPlatformRequests, listPlatformRequestHistory, settlePlatformRequest
  • attachments — attachments

…plus host.admin, the HostAdmin surface every control-plane operation goes through.

Per-tenant relational stores ​

A vertical that needs one database per tenant rather than per scope gets a handle, never a path: provisionTenantStore returns an opaque ref — here a tstore__…​.sqlite filename, in production a minted D1 id — and openTenantStore turns it back into a store. The prefix exists so a store file can never collide with a scope database.

Scope stores ​

A scope store is answered here by a runner, one per driver, which the driver ships. scopeStoreRunnersFor reads the bindings from the vertical's own declaration, so the bindings a dev host answers are the ones a push declares:

ts
import { SqliteScopeHost, scopeStoreRunnersFor } from '@substrat-run/adapter-sqlite';

const host = new SqliteScopeHost({
  dir: './data',
  scopeStores: {
    // `graphRunner` is the driver's own factory; the map takes the runner it returns.
    runners: scopeStoreRunnersFor(manifest.runtimeNeeds.scopeStores, {
      graph: graphRunner({ dir: './data/graph' }),
    }),
  },
});

Two bindings of one driver share its runner. A declared driver with no runner is refused, and so is a runner given under a key that is not its driver.

Debugging is opening a file ​

Scope databases run in WAL mode and can be opened read-only with any SQLite tool, which is the single biggest practical reason to develop against this adapter:

sh
sqlite3 ./data/<tenantId>__<scopeId>.sqlite '.tables'
sqlite3 ./data/<tenantId>__<scopeId>.sqlite 'SELECT type, entity_id, actor FROM _substrat_outbox ORDER BY occurred_at DESC LIMIT 20;'
sqlite3 ./data/_directory.sqlite 'SELECT * FROM scopes;'

Notes ​

  • Uses better-sqlite3 (a native module). It ships prebuilt binaries: do not list it in pnpm.onlyBuiltDependencies, which would make pnpm run a needless node-gyp rebuild.
  • Single-node by design. It preserves the serialization semantics, not the scale-out; production scale-out is the Cloudflare adapter's job. That is a deliberate split, not a gap — the semantics are what the contract tests pin, and they are identical.
  • One JavaScript thread owns a dir. The first host to open it takes an exclusive lock on _host.lock. Further hosts on the same thread share the directory, as two verticals on one platform directory do, and the lock is released when the last of them closes, or when the process exits however it exits. A host in another process, or on another worker thread of the same process, is refused with conflict (reason host_dir_in_use) naming the directory. The reason: some guarantees read the directory and a scope's file with nothing awaited before the commit, which no code on the same thread can interleave but a writer on another thread or in another process could. Opening the files read-only with a SQLite tool, as above, is unaffected.
  • The directory schema is re-applied on every open and after a restore, because a dump taken before a directory migration carries the old shape and replaying it verbatim would roll the schema backwards.
  • Pre-release: interfaces change without notice until the first vertical ships.

See also ​