# `@zephyrcloudio/harness-storage-sqlite`

The Node SQLite adapter for Core's `Storage` service.

Use `makeSqliteStorage` in an Effect scope, or provide `sqliteStorageLayer`:

```ts
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:

```ts
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`

---

Generated from [`packages/storage-sqlite/README.md`](https://github.com/ZephyrCloudIO/ze-harness/blob/main/packages/storage-sqlite/README.md).