Skip to main content
theAuth uses Drizzle ORM under the hood. You pick a provider and pass the connection URL; theAuth handles the rest.

Choosing a provider

Setup

The sqlite provider uses sql.js (SQLite compiled to WebAssembly), which ships with @glinr/theauth. It needs no native build and runs on Node.js, Bun, Deno, and edge runtimes. It is in-memory only: it never writes a database file, so use it for tests, CI and throwaway demos. For a database file that survives a restart, use the SQLite (native) tab (sqlite-native) or Postgres.For in-memory SQLite (tests and CI), use :memory: as the URL:
sql.js keeps the database in memory and nothing is saved when the process exits. Passing a file path (for example url: './theauth.db') throws THEAUTH_SQLITE_NOT_PERSISTENT at startup, because the file would never be written and your data would disappear on restart. theAuth turns on foreign keys (PRAGMA foreign_keys = ON).

Auto-migration

By default, theAuth migrates the database at startup: it creates the tables your config needs (CREATE TABLE IF NOT EXISTS) and applies any pending versioned migration, recording each one in the theauth_schema_migrations ledger (see Schema migrations). This is safe to run on every start, and there is no manual migration step for a new project or an upgrade. Tables are created per feature, so a small config gives you a small schema. Users and the secondary storage table are always created. Everything else depends on the config: If you add a feature later, restart once and the new tables appear. Add agents: { enabled: true } if you use agents, since agent tables are not created without it. The @glinr/theauth-email plugin creates its own theauth_email_accounts table. To disable this (e.g. when you manage migrations externally with Flyway, Liquibase, or drizzle-kit push), set skipMigrations: true:
When skipMigrations: true, you apply the schema yourself, and theAuth checks the ledger at startup. If migrations are pending it throws SchemaOutdatedError listing their ids, instead of failing on the first write. See Using skipMigrations.

Schema migrations

theAuth keeps a ledger table, theauth_schema_migrations, with one row per applied migration: id, name, checksum and applied_at. Migrations are numbered and append only: On startup theAuth runs every migration that is not in the ledger yet, in order, and records it. Rules that make this safe on an existing database:
  • A database from before the ledger is adopted, not rebuilt. If the tables exist but the ledger does not, theAuth probes the columns, treats 0000 (and 0001, when its columns are present) as already applied, records them, and runs the rest. Your rows are untouched.
  • Reruns are no-ops. Applied migrations are skipped. Statements tolerate “already exists”, so a half finished upgrade can be resumed.
  • Applied migrations cannot change. The ledger stores a checksum of each migration’s SQL. If it no longer matches the code, startup fails with SchemaChecksumError rather than guessing.
  • Features still decide which tables exist. A migration that touches the audit table only applies when your config creates it. Enable the feature later and the next start creates the table and applies the migrations that were waiting.
  • The audit hash does not change. decision_code, recorded_at_ms and event are not part of the hashed payload, so existing chains keep verifying.
Read the ledger from code:
Or from the shell, with the CLI:
--db <url> (or DATABASE_URL) picks the database; --config <file> loads your config module so only the tables your features need are considered. Add --json for machine readable output.

Using skipMigrations

With skipMigrations: true theAuth never changes the schema, but it still reads the ledger and throws SchemaOutdatedError when migrations are pending. To keep the schema in step with the SDK:
  1. Generate the SQL for your dialect: theauth db sql --dialect postgres > theauth-schema.sql. On a database that already has the 1.x tables, start at the first pending id instead, for example --from 0002. That output first creates the ledger and stamps the earlier migrations as applied.
  2. Review it and run it with your own tooling (Flyway, Liquibase, psql). Each migration ends with an INSERT into the ledger, so theAuth sees it as applied.
  3. Deploy the new SDK version. If anything is still pending the process fails at startup and names the ids.
The same SQL ships in the repository as generated files, packages/core/src/db/migrations/sql/<dialect>/<id>.sql. A test fails when they drift from the TypeScript migrations.
Upgrading to 2.0 with skipMigrations: true: apply 0002 and 0003 (or run theauth db sql --dialect <yours> --from 0002) before deploying. The audit queries select decision_code and recorded_at_ms, and startup now fails with SchemaOutdatedError until the ledger shows them applied.

Schema overview

The full set of tables theAuth can create is below. Which ones exist in your database depends on your config, see above.

Upgrading the audit table

Migrations 0002 and 0003 add decision_code TEXT (with the index theauth_audit_logs_code) and recorded_at_ms (INTEGER on SQLite, BIGINT on Postgres, with the index theauth_audit_logs_order). Old rows keep a NULL code, and recorded_at_ms is backfilled from the second-resolution timestamp. Audit reads order by recorded_at_ms, so rows written in the same second keep their write order. Migration 0005 adds a nullable event TEXT column (with the index theauth_audit_logs_event). It backfills event from action for rows whose action is itself an event name (delegation.create, delegation.revoke, agent.cascade.revoked, approval.*, vault.read, audit.*), marks policy engine rows policy.evaluate, marks the remaining rows that have a decision code authorize, and leaves every other row NULL. None of these columns is covered by the audit hash, so existing hash chains keep verifying. If you use skipMigrations, see Using skipMigrations.
Do not run TheAuth and theauth-go in the same database or schema. Both create theauth_audit_secrets with different column types. TheAuth detects the foreign layout before it migrates (and on every start with skipMigrations) and throws AUDIT_SECRETS_LAYOUT_MISMATCH without changing the table. Use a separate database or schema for each library. See Audit receipts and chain vectors.
All table and column names use snake_case. IDs are text. Timestamps are integer epoch seconds on both SQLite and Postgres.

Peer dependencies

theAuth uses dynamic imports for the SQLite and Postgres drivers so they remain optional. You will get a clear error message at startup if the required package is missing, for example:

Testing with in-memory SQLite

Use :memory: for fast, isolated tests that need no setup or teardown:
Each createTheAuth() call with :memory: gets a completely isolated database, so tests never share state.

Prisma adapter

Read theAuth tables through an existing PrismaClient.

Configuration

Full createTheAuth() options including database and secrets.

Test utilities

In-memory mock server and factories for auth-dependent tests.

Terraform

Provision agents and permissions as infrastructure-as-code.
Last modified on October 19, 2026