SQLite

Your application owns the database, migration ledger, backup/recovery, configuration, and connection lifecycle. Compose focused direct Drizzle stores over one scoped database.

Runtime matrix

Cloudflare D1 is SQLite-compatible but has a remote binding and different transaction model. Use Cloudflare D1, not local-file assumptions.

The full composition provides all 42 maintained ports. The existing *AccountAuthStorageLayer entrypoints still provide exactly the nine account/password ports for selective schemas.

Node example

Apply migrations before startup, then keep one storage Layer inside the application scope rather than creating a client per request or query:

import * as DrizzleNodeSqlite from "@effect-auth/core/DrizzleNodeSqlite";
import { DrizzleNodeSqliteAuthStorageLayer } from "@effect-auth/core/DrizzleNodeSqliteAuthStorage";
import { UserId } from "@effect-auth/core/Identifiers";
import { UserStore } from "@effect-auth/core/Storage";
import * as Effect from "effect/Effect";
import * as Layer from "effect/Layer";

const DatabaseLayer = DrizzleNodeSqlite.layer({
  filename: "./data/auth.sqlite",
});
const AuthStorageLayer = DrizzleNodeSqliteAuthStorageLayer().pipe(
  Layer.provide(DatabaseLayer)
);

const findUser = Effect.gen(function* () {
  const users = yield* UserStore;
  return yield* users.findById(UserId("01J00000000000000000000000"));
});

await Effect.runPromise(
  Effect.scoped(findUser.pipe(Effect.provide(AuthStorageLayer)))
);

For Bun, switch to DrizzleBunSqlite.layer and DrizzleBunSqliteAuthStorageLayer. Provide the same database Layer to application repositories at the composition root. Scope finalization closes clients created by the Node/Bun Layers; application-provided databases remain application-owned.

First-party durable direct support covers all 42 ports tracked in the project plan, including the current OAuth, token, passkey, login-security, MFA, audit-log, domain-verification, security-timeline, permission, invitation, and access-grant ports. Migrations and generated tables remain available for all features; do not use memory storage for production.

Focused login-risk history storage is available without the broad storage composition. Construct it from the same application-owned Effect Drizzle database with makeDrizzleSqliteLoginRiskHistoryStore; the returned Effect validates bounded history policy before yielding the store. The same constructor accepts Bun, Node, and D1 SQLite databases. Apply the complete login-risk migrations first; typed Drizzle tables do not create or harden DDL.

Trusted-device storage has the same focused path through makeDrizzleSqliteTrustedDeviceStore, with a matching PostgreSQL constructor. It uses a native guarded upsert so concurrent refreshes retain the freshest observation and preserve omitted metadata. Import the contract from @effect-auth/core/TrustedDeviceStorage when only persistence types and the store tag are needed; that subpath excludes cookie and cryptographic services.

TOTP factors use makeDrizzleSqliteTotpFactorStore with Bun, Node, or D1 Effect Drizzle databases, or makeDrizzlePostgresTotpFactorStore on PostgreSQL. Import the runtime-neutral contract from @effect-auth/core/TotpFactorStorage. New inserts and secret replacements are ciphertext-only; reads and compare-and-swap expectations retain compatibility with legacy Base32 secrets while applications rotate them.

Recovery codes use the transaction-backed makeDrizzleSqliteRecoveryCodeStore on Bun and Node, the native-batch makeDrizzleD1RecoveryCodeStore on D1, and makeDrizzlePostgresRecoveryCodeStore on PostgreSQL. Import the focused contract from @effect-auth/core/RecoveryCodeStorage. Set insertion and active replacement are atomic, replacement rows must share the requested owner, and a set is limited to 100 codes. Set commands reject duplicate identifiers. Replacement creation times cannot follow the rotation timestamp, and a stale rotation older than an active owner row fails without mutating the active set. Pass an application-owned typed Drizzle table through the table option when the canonical recovery-code table is not used.

Recovery-code session rotation is also available as focused direct adapters: makeDrizzleSqliteRecoveryCodeSessionRotateStore for Bun and Node, makeDrizzleD1RecoveryCodeSessionRotateStore for D1, and makeDrizzlePostgresRecoveryCodeSessionRotateStore for PostgreSQL. The operation atomically consumes one active owner code and rotates only an active, strictly unexpired owner session matching the exact secret and event snapshot. Conflicts return None without consuming the code. D1 performs the two writes as one native batch; local SQLite and PostgreSQL use native transactions. Custom schemas use typed recoveryCodeTable and sessionTable options rather than string table names. D1 pre-decodes the current session, but malformed or inconsistent results observed after a successful batch response cannot be rolled back because D1 returns them after commit. Such a tagged technical error has unknown commit state. This does not affect ordinary CAS conflicts: immediate changes() gating keeps those mutation-free and returns None.

D1 and PostgreSQL recovery adapters expose focused layer and layerNoDeps constructors over their application database services. Bun and Node use the generic SQLite layerNoDeps exports, which are also included in their full storage compositions.

TOTP login completion is available through makeDrizzleSqliteTotpLoginCommitStore, makeDrizzleD1TotpLoginCommitStore, and makeDrizzlePostgresTotpLoginCommitStore. The operation consumes the exact pending flow, advances the confirmed factor replay counter, checks an optional password credential snapshot, and either inserts the bound session or commits a login-approval continuation atomically. D1 submits one native batch; post-commit response corruption therefore has unknown commit state.

Recovery-code login completion has parallel focused constructors: makeDrizzleSqliteRecoveryCodeLoginCommitStore, makeDrizzleD1RecoveryCodeLoginCommitStore, and makeDrizzlePostgresRecoveryCodeLoginCommitStore. They atomically consume the exact pending flow and one active owner code before inserting the bound session or committing a login-approval continuation. The commit is bound to the exact verified code snapshot. D1 post-commit response corruption has unknown commit state and is reported as a tagged technical error.

Migrations

Commit SQL and apply it exactly once before auth traffic. Never apply a fresh baseline over an existing database. Drizzle declarations describe tables for application queries; neither they nor drizzle-kit push replace effect-auth migration SQL.

Concurrency

• SQLite permits concurrent reads but serializes writes; keep indexed auth writes and transactions short.
• WAL can improve local overlap, but do not assume it is safe on network or serverless filesystems.
• The adapter makes defined store transitions atomic; it does not make several arbitrary store calls one transaction.
• Put app-owned multi-call invariants in a transaction supported by the same application-owned connection.
• Test lock/busy handling, rollback, consume/claim/CAS races, and lost updates on the production filesystem and driver.

For another persistence model, implement the contracts in Custom Database.