Bring your own Drizzle instance

Most apps that already use Drizzle have a connection pool, credentials and a driver choice that theAuth should not duplicate. Pass that instance in and theAuth queries it directly.

import { createTheAuth } from "@glinr/theauth";
import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

const auth = await createTheAuth({
  database: { drizzle: drizzle(pool), dialect: "postgres" },
});

dialect is "sqlite", "postgres" or "mysql". It picks the migration SQL, the same way provider does for the URL form. Everything else about the database section is unchanged: tables are created on boot, and skipMigrations: true makes boot fail with SchemaOutdatedError when the schema is behind (see Database setup).

What you own

theAuth does not open this connection, so it never closes it. Shut the pool down yourself when the process exits. Connection settings that provider applies for you become your job:

Dialect	Set this on your own handle
`sqlite`	`PRAGMA foreign_keys = ON`. For a file, `PRAGMA journal_mode = WAL` is a good idea if more than one process opens it.
`mysql`	Create the pool with `timezone: "Z"`. The migration ledger stores UTC. Use a `utf8mb4` database with a binary collation (`utf8mb4_bin`) so ids compare case sensitively.
`postgres`	Nothing extra. Use a pooled connection string on serverless hosts.

Requirements for the handle

It must expose the driver client at db.$client, because migrations and the schema check send raw SQL through it. The node-postgres, mysql2 and better-sqlite3 drivers do, and those are the ones this release tests. A driver that hides its client can still work if you run the migrations yourself (theauth db sql) and every query path goes through Drizzle, but boot calls the schema check even with skipMigrations, so that is untested territory.
Do not pass a schema that renames or replaces theAuth’s tables. theAuth uses its own table objects and never reads db.query, so a plain drizzle(pool) is enough.
Reads must see your own writes. Single use tokens, refresh rotation and the audit chain read right after a write. If you route reads to a replica, pin auth traffic to the primary.

createTheAuth returns your handle as auth.db, and plugins receive it as ctx.db, so it is the same object you passed in.

Hot path indexes

Migration 0014 adds seven indexes that agent token lookup, the delegation walks, the hourly rate limit read and the pending approval list depend on. Without them each of those scans its whole table. If you own the schema and skip theAuth migrations, create them yourself. Keep the names, so a later run of the migration finds them and leaves them alone.

Index	Table	Columns
`theauth_agents_token_hash`	`theauth_agents`	`token_hash`
`theauth_agents_owner_status`	`theauth_agents`	`owner_id, status`
`theauth_delegation_chains_to`	`theauth_delegation_chains`	`to_agent_id, status`
`theauth_delegation_chains_from`	`theauth_delegation_chains`	`from_agent_id, status`
`theauth_rate_limits_lookup`	`theauth_rate_limits`	`agent_id, resource, window_start`
`theauth_approval_requests_status`	`theauth_approval_requests`	`status, created_at`
`theauth_permissions_agent`	`theauth_permissions`	`agent_id` (SQLite and Postgres only)

The SQL for each dialect:

-- SQLite and Postgres
CREATE INDEX IF NOT EXISTS theauth_agents_token_hash ON theauth_agents (token_hash);
CREATE INDEX IF NOT EXISTS theauth_agents_owner_status ON theauth_agents (owner_id, status);
CREATE INDEX IF NOT EXISTS theauth_delegation_chains_to ON theauth_delegation_chains (to_agent_id, status);
CREATE INDEX IF NOT EXISTS theauth_delegation_chains_from ON theauth_delegation_chains (from_agent_id, status);
CREATE INDEX IF NOT EXISTS theauth_rate_limits_lookup ON theauth_rate_limits (agent_id, resource, window_start);
CREATE INDEX IF NOT EXISTS theauth_approval_requests_status ON theauth_approval_requests (status, created_at);
CREATE INDEX IF NOT EXISTS theauth_permissions_agent ON theauth_permissions (agent_id);

-- MySQL (TEXT columns need a prefix length; InnoDB already indexes the foreign key columns)
CREATE INDEX theauth_agents_token_hash ON theauth_agents (token_hash(191));
CREATE INDEX theauth_agents_owner_status ON theauth_agents (owner_id, status);
CREATE INDEX theauth_delegation_chains_to ON theauth_delegation_chains (to_agent_id(191), status(16));
CREATE INDEX theauth_delegation_chains_from ON theauth_delegation_chains (from_agent_id(191), status(16));
CREATE INDEX theauth_rate_limits_lookup ON theauth_rate_limits (agent_id, resource(191), window_start);
CREATE INDEX theauth_approval_requests_status ON theauth_approval_requests (status, created_at);

If you copy the table objects from @glinr/theauth into your own Drizzle schema, they already declare these indexes (SQLite dialect), so drizzle-kit generate emits them.

Typed unique conflicts

Several flows use a unique index as the tiebreaker: creating a user, appending to the audit chain, a second vote from the same approver, banning twice, MCP client and revocation inserts, erasure tombstones. They now map a driver error through toStoreConflict, which returns a StoreConflictError (exported from @glinr/theauth, with code: "STORE_CONFLICT", model and the driver error as cause). Detection reads structured codes first: Postgres 23505, MySQL ER_DUP_ENTRY or errno 1062, SQLite SQLITE_CONSTRAINT_UNIQUE and SQLITE_CONSTRAINT_PRIMARYKEY, SQL Server 2627 and 2601, Prisma P2002, MongoDB 11000. The old text match stays as a fallback for drivers that wrap the code away, so behavior on the existing providers is unchanged.

Run the contract suite against your backend

@glinr/theauth-test-utils/store-contract holds the checks that make a backend safe for auth: 24 parallel callers fighting over one single use row with exactly one winner (magic link, email OTP, approval decision and consumption, refresh rotation), the attempt limit on OTP guesses, unique conflicts, and an audit chain that stays gapless and verifies when two processes append for one agent.

import { createTheAuth } from "@glinr/theauth";
import { runStoreContract } from "@glinr/theauth-test-utils/store-contract";

const url = process.env.TEST_DATABASE_URL;

runStoreContract(
  "my postgres",
  async () => {
    const mail = { magicTokens: [] as string[], otpCodes: [] as string[] };
    const auth = await createTheAuth({
      database: { drizzle: makeMyDrizzle(url), dialect: "postgres" },
      agents: { enabled: true, maxPerUser: 500 },
      audit: { tamperEvident: true, hmacKey: "test-key" },
      magicLink: {
        appUrl: "https://app.test",
        sendMagicLink: async (_email, token) => void mail.magicTokens.push(token),
      },
      emailOtp: { sendOtp: async (_email, code) => void mail.otpCodes.push(code) },
    });
    return { auth, mail, close: async () => {} };
  },
  { skip: !url },
);

Add peer (a second instance with its own handle on the same database) to include the two process audit check, duplicateInsert to check conflict typing with your driver, and tokenFamilies for the refresh rotation race. The repository runs this suite on sql.js, better-sqlite3 through both config forms, Postgres and MySQL; set THEAUTH_TEST_PG_URL and THEAUTH_TEST_MYSQL_URL to include the last two.

The suite needs vitest as a dev dependency. It runs against the instance’s public surface, so it describes how today’s Drizzle code behaves, not a future storage interface.

Start here

Setup

Sessions

Bring your own Drizzle instance

What you own

Requirements for the handle

Hot path indexes

Typed unique conflicts

Run the contract suite against your backend

​What you own

​Requirements for the handle

​Hot path indexes

​Typed unique conflicts

​Run the contract suite against your backend

What you own

Requirements for the handle

Hot path indexes

Typed unique conflicts

Run the contract suite against your backend