@zephyrcloudio/harness-storage-sqlite

The Node SQLite adapter for Core's Storage service.

Use makeSqliteStorage in an Effect scope, or provide sqliteStorageLayer:

import { makeSqliteStorage, sqliteStorageLayer } from '@zephyrcloudio/harness-storage-sqlite';

The adapter owns schema migrations, transactions, SQL, and row decoding. It opens SQLite in WAL mode with a 5-second busy timeout, so several host processes may share one database file: readers run concurrently and writers serialize. Claims stamp the owner process id; recovery reaps only work whose owner is dead. Use :memory: only for ephemeral runs and tests.

Two cursors order observation. session.activity_cursor is per Session and stamps changed turn rows for listSessionActivity. The one-row server_activity counter is per database and stamps session, turn, and deleted_session rows in the same transaction as the change for listServerActivity, and readServerActivityCursor reports its current value; it only grows, so a server cursor stays valid after Sessions are deleted or the deletion markers are purged.

Schema migrations

Every opener brings the database to this build's schema before it serves, on Node and in Durable Objects alike. Migrations are ordered TypeScript modules in src/migration/, listed in src/migration/registry.ts (serverMigrations for the server database, providerAuthorityMigrations for the Worker's provider authority object). The harness_migration table records which ones a database has applied, and harness_schema_object records the tables, indexes, and triggers those migrations left behind.

On open the runner:

  1. Reads the ledger and checks that every recorded schema object still exists with the same definition. With nothing pending it returns without writing.
  2. On Node, copies a database that already holds data to <db>.pre-<migration-id>.bak (owner-only, newest copy kept). If the copy cannot be written, nothing is migrated and the open fails with StorageMigrationFailed. In a Durable Object it logs the point-in-time recovery bookmark instead.
  3. Turns foreign key enforcement off, takes the write lock (BEGIN IMMEDIATE, or transactionSync), re-reads the ledger, applies every pending migration, runs PRAGMA foreign_key_check, and commits them together. Any failure rolls the whole batch back.

A database without a ledger that holds tables from a release before migrations existed fails with StorageResetRequired; move it aside (pnpm clean:host in a source checkout) and start again. A database migrated by a newer build opens only if this build knows its compatible_floor (the newest breaking migration); otherwise it fails with StorageNewerThanBinary. A ledger whose order differs from this build's list fails with StorageMigrationDiverged. A recorded table, index, or trigger that was dropped or redefined outside a migration, for example by hand or by a partial restore, fails with StorageSchemaDrifted. None of these write to the database.

To change the schema:

  1. Add src/migration/<YYYYMMDDHHMMSS>-<name>.ts exporting a Migration whose id is <YYYYMMDDHHMMSS>_<name>, and append it to the end of the registry list.
  2. Set breaking: true when a build without the migration could no longer safely read or write the database, for example after a rename, a dropped column, or a changed JSON shape. Additive changes that older builds ignore stay breaking: false.
  3. Write up against the synchronous SqliteDatabase. It may run SQL or read rows, decode and re-encode JSON columns with Effect Schema, and write them back. For changes ALTER TABLE cannot make, rebuild the table (create the new table, copy rows, drop the old one, rename); the runner has foreign keys off, so the drop never cascades.
  4. Keep migrations short. A Durable Object migrates inside blockConcurrencyWhile, which has a 30-second limit, so large backfills belong in alarm-driven follow-up work.

Never edit, remove, or reorder a migration once it has shipped.

Runtime-neutral SQLite hosts can provide a synchronous structural bridge through the portable entry:

import {
  makePortableSqliteStorage,
  type SqliteDatabase,
} from '@zephyrcloudio/harness-storage-sqlite/portable';

The bridge may provide transaction to use a host transaction API such as DurableObjectStorage.transactionSync. Otherwise, the portable core uses SQLite transaction SQL. The caller owns the database lifecycle.

Bun and Deno are not supported package hosts.

Public TypeScript exports

  • applyMigrations
  • ApplyMigrationsOptions
  • isStorageMigrationFailure
  • makePortableProviderAuthorityStorage
  • makePortableSessionCatalogAdoptionStorage
  • makeSqliteHostStorage
  • makeSqliteStorage
  • Migration
  • MigrationRegistry
  • pendingMigrations
  • PortableProviderAuthorityStorageOptions
  • PortableSessionCatalogAdoptionStorage
  • PortableSessionCatalogAdoptionStorageOptions
  • providerAuthorityMigrations
  • ProviderAuthorityStorage
  • ProviderAuthorityStorageService
  • ProviderConnectionRecord
  • serverMigrations
  • SetProviderConnectionInput
  • SqliteHostStorage
  • sqliteStorageLayer
  • SqliteStorageOptions
  • StorageMigrationDiverged
  • StorageMigrationFailed
  • StorageMigrationFailure
  • StorageMigrationProgress
  • StorageNewerThanBinary
  • StorageResetRequired
  • StorageSchemaDrifted