Kernlift

Data access

How business code reaches the database: repositories only, one contract over SQLite, D1 and Postgres, what a transaction means on each, and why migrations are kept per dialect.

Kernlift runs on SQLite, Cloudflare D1 and Postgres from the same business code. All database access goes through src/server/db/repositories/*, and everything that differs between databases stays in the schema, migration and adapter layers underneath. To switch databases, see Use Postgres.

Repositories are the only door

Server functions, services and API routes call repository functions. They do not import Drizzle tables, write SQL or branch on a dialect. A repository gets its database through withRepositoryDialect, which passes it three values:

ValueWhat it is
dbThe Drizzle database of the active adapter
schemaThe table definitions for that database: schema/postgres.ts or schema/sqlite.ts
dialect"postgres", "sqlite" or "d1"

Both schema files define the same tables with the same names and columns, so a repository writes one query against schema.billingOrders and it runs on either. Where a column's storage differs, most often a timestamp, the repository converts at that one point by checking dialect. Agent tables follow the same pattern in agent-sqlite.ts and agent-postgres.ts.

Two schema families, five adapters

The adapter determines how the app connects. The schema family determines the SQL it runs.

AdapterSchema familyRuns onConnection
sqliteSQLiteNodeA local file through better-sqlite3
tursoSQLiteNodelibSQL with DATABASE_URL and DATABASE_AUTH_TOKEN
d1SQLiteCloudflare WorkersThe EDGE_DB binding
postgresPostgresNodeDATABASE_URL, one client per process
hyperdrivePostgresCloudflare WorkersThe HYPERDRIVE binding, one client per request

DATABASE_ADAPTER selects one and defaults to DATABASE_DIALECT. You can register your own for either family. See Write a custom database adapter.

A Worker may not reuse a socket that was opened while handling another request. The hyperdrive adapter therefore creates its Postgres client inside a per-request scope. The Worker entry wraps each request and each scheduled event in runInRequestScope, and the client lives as long as that scope. Hyperdrive pools the real connections behind it. On Node there is no scope and the postgres adapter keeps one client.

What a transaction means on each database

Repositories are written once, with async transaction callbacks: db.transaction(async (tx) => { ... }). What happens underneath depends on the database.

DatabaseBehaviour
Postgres, Hyperdrive, TursoThe driver runs the callback as an interactive transaction
SQLite (better-sqlite3)The driver is synchronous and rejects an async callback, so the repository facade runs it as a real transaction on the same connection: BEGIN IMMEDIATE, then COMMIT, or ROLLBACK with the original error. Transactions in one process run one at a time, in order, and a nested call joins the open one
D1No interactive transactions. The repository context reports d1, and code that would open a transaction runs the same statements one by one instead

On SQLite a transaction callback should do database work only and stay short, because other queued work in the process can run between its statements. On D1 a multi-step write is not atomic, so it must be safe to repeat or to stop halfway. For the same reason the Agent, which needs transactions for its runs, does not use D1.

Where one statement is enough, the code uses one. The scheduled-jobs lock is a single conditional update and the fulfilment claim is a single insert under a unique key, so both behave the same on all three databases.

Migrations are kept per dialect

There are two migration chains, one per schema family, with the same file names:

ChainUsed byApplied with
drizzle/migrations/sqliteSQLite, Turso, D1pnpm db:migrate; pnpm db:migrate:d1:local and pnpm db:migrate:d1:remote for D1
drizzle/migrations/postgresPostgres, Hyperdrivepnpm db:migrate, or pnpm db:migrate:runtime in a production image

The chains are maintained by hand. A schema change means editing both schema files and adding a migration with the same name to both chains. pnpm db:generate:sqlite and pnpm db:generate:postgres produce a first draft for each. pnpm validate:migrations then checks that the two chains agree: the journals and the files match, each chain replays onto an empty database, each --> statement-breakpoint segment holds one statement, and both dialects end with the same tables.

pnpm db:migrate wraps the migration tool to report the one state it otherwise fails on silently: a database whose tables exist but whose migration bookkeeping is empty, because something other than the chain created it.

Files

src/server/db/repositories
src/server/db/repositories/repository-dialect.ts
src/server/db/schema/sqlite.ts
src/server/db/schema/postgres.ts
src/server/db/schema/agent-sqlite.ts
src/server/db/schema/agent-postgres.ts
src/server/db/client.ts
src/server/db/adapter-registry.ts
src/server/db/sqlite-transactions.ts
src/server/platform/request-scope.ts
drizzle/migrations/sqlite
drizzle/migrations/postgres
scripts/db/migrate.ts
scripts/db/migrate-runtime.mjs

Three validators cover this layer: validate:db-adapters (the registry, the bindings and the server entry), validate:migrations (the two chains) and validate:sqlite-transactions (commit, rollback, ordering and nesting on the default SQLite adapter).

Next steps