Database setup

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

Choosing a provider

Provider	Best for
`sqlite`	Local dev and small single-process deploys. Runs on `sql.js` (SQLite compiled to WebAssembly), no native build step
`sqlite-native`	Node.js servers that want the fastest SQLite. Uses `better-sqlite3`, which needs a native build
`postgres`	Production, high-concurrency, multi-tenant
`mysql`	Existing MySQL infrastructure
`d1`	Cloudflare Workers, using a D1 binding

Setup

SQLite
SQLite (native)
Postgres
MySQL
Cloudflare D1

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.

import { createTheAuth } from '@glinr/theauth';

const theauth = await createTheAuth({
  database: {
    provider: 'sqlite',
    url: './theauth.db',
  },
});

For in-memory SQLite (tests and CI), use :memory: as the URL:

const theauth = await createTheAuth({
  database: {
    provider: 'sqlite',
    url: ':memory:',
  },
});

sql.js keeps the database in memory. For a file path, theAuth loads the file at startup if it exists and rewrites the whole file after each write statement. That is fine for development and small apps, but it is a single-process setup: do not point several processes at the same file. theAuth turns on foreign keys (PRAGMA foreign_keys = ON). It does not enable WAL mode for this provider.

The sqlite-native provider uses better-sqlite3. Install it yourself, it is an optional peer dependency and needs C++ build tools:

npm install better-sqlite3

import { createTheAuth } from '@glinr/theauth';

const theauth = await createTheAuth({
  database: {
    provider: 'sqlite-native',
    url: './theauth.db',
  },
});

theAuth enables WAL mode and foreign keys automatically for this provider:

PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;

Install the pg peer dependency:

npm install pg
npm install --save-dev @types/pg

import { createTheAuth } from '@glinr/theauth';

const theauth = await createTheAuth({
  database: {
    provider: 'postgres',
    url: process.env.DATABASE_URL!,
    // postgresql://user:password@host:5432/dbname
  },
});

theAuth uses drizzle-orm/node-postgres with a connection pool via pg.Pool. The pg package is loaded with a dynamic import, so it stays an optional peer dep.

Install the mysql2 peer dependency:

npm install mysql2

import { createTheAuth } from '@glinr/theauth';

const theauth = await createTheAuth({
  database: {
    provider: 'mysql',
    url: process.env.DATABASE_URL!,
    // mysql://user:password@host:3306/dbname
  },
});

theAuth uses drizzle-orm/mysql2 with a connection pool. mysql2 is loaded via dynamic import and stays an optional peer dep.

Pass the D1 binding from your Worker environment. The drizzle-orm/d1 driver is loaded with a dynamic import.

import { createTheAuth } from '@glinr/theauth';

export default {
  async fetch(request: Request, env: { DB: D1Database }) {
    const theauth = await createTheAuth({
      database: {
        provider: 'd1',
        binding: env.DB,
      },
    });
    // ...
  },
};

The D1 provider takes a binding instead of a url. Set skipMigrations: true if you create the tables yourself.

Auto-migration

By default, theAuth calls CREATE TABLE IF NOT EXISTS at startup for the tables your config needs. This is safe to run on every start, but it only creates missing tables, it does not alter existing ones. There is no manual migration step for a new project. 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:

Config	Tables created
`agents` (or `did`)	agents, permissions, delegation chains, agent cards, approval requests, trust scores, registration tokens, DIDs, ephemeral sessions, tenants, audit logs, cost and stream events, rate limits, budget policies, ReBAC
`auth.session`	sessions, trusted devices, login history, JWT refresh tokens and token families
`mcp`	MCP servers
`oauth()` plugin	OAuth accounts and states, plus OAuth client, token and code tables
`magicLink`, `emailOtp`, `totp`, `passkey`, `username`, `phone`, `org`, `sso`, `apiKeys`	The matching table(s), whether you use the config key or the plugin form (for example `plugins: [magicLink(...)]`)
`passwordReset`, `magicLink`, `emailOtp`	One-time tokens

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:

database: {
  provider: 'postgres',
  url: process.env.DATABASE_URL!,
  skipMigrations: true,
},

When skipMigrations: true, you are responsible for keeping the schema in sync. theAuth will fail at runtime if expected tables or columns are missing.

Schema overview

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

Table	Purpose
`theauth_secondary_storage`	Key/value rows used when `secondaryStorage: 'database'` (counters, device codes)
`theauth_users`	Human user identities (also holds ban, Stripe and Polar billing fields)
`theauth_tenants`	Multi-tenant isolation
`theauth_agents`	AI agent identities (the core entity)
`theauth_permissions`	Per-agent resource+action permissions with constraints
`theauth_delegation_chains`	Agent-to-agent delegation records
`theauth_audit_logs`	Log of every agent action
`theauth_rate_limits`	Per-agent call-rate counters
`theauth_mcp_servers`	Registered MCP servers
`theauth_sessions`	theAuth-managed human user sessions
`theauth_oauth_clients`, `theauth_oauth_access_tokens`, `theauth_oauth_authorization_codes`	OAuth 2.1 client registrations, issued tokens, and short-lived PKCE codes
`theauth_agent_cards`	A2A capability discovery cards
`theauth_agent_registration_tokens`	One-time tokens for agent registration
`theauth_oauth_accounts`, `theauth_oauth_states`	Linked social accounts and in-flight OAuth state (`oauth()` plugin)
`theauth_approval_requests`	CIBA async approval flow records
`theauth_trust_scores`	Graduated autonomy trust scores per agent
`theauth_budget_policies`	Token and call budget caps per agent/user/tenant
`theauth_magic_links`, `theauth_email_otps`, `theauth_phone_verifications`, `theauth_totp`	Magic link, email OTP, phone OTP, and TOTP records
`theauth_username_accounts`, `theauth_passkey_credentials`, `theauth_passkey_challenges`	Username/password and passkey records
`theauth_organizations`, `theauth_org_members`, `theauth_org_invitations`, `theauth_org_roles`, `theauth_sso_connections`, `theauth_api_keys`	Organizations, SSO connections, and API keys
`theauth_trusted_devices`, `theauth_one_time_tokens`, `theauth_login_history`, `theauth_jwt_refresh_tokens`, `theauth_refresh_tokens`, `theauth_refresh_token_families`	Trusted devices, one-time tokens, login history, and refresh tokens
`theauth_ephemeral_sessions`, `theauth_agent_dids`, `theauth_cost_events`, `theauth_stream_events`	Ephemeral sessions, agent DIDs, cost attribution, and event streaming
`theauth_oidc_clients`, `theauth_oidc_auth_codes`, `theauth_oidc_refresh_tokens`	OIDC provider records
`theauth_rebac_resources`, `theauth_rebac_relationships`	ReBAC graph
`theauth_federation_instances`, `theauth_federation_tokens`	Federation records

All table and column names use snake_case. IDs are text. In the SQLite schema, timestamps are stored as integers.

Peer dependencies

Provider	Required package
`sqlite`	`sql.js` (installed with core)
`sqlite-native`	`better-sqlite3`
`postgres`	`pg`
`mysql`	`mysql2`
`d1`	`drizzle-orm/d1` (a Cloudflare Workers binding, no extra install)

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

TheAuth: provider "postgres" requires the "pg" package.
Install it with: npm install pg

Testing with in-memory SQLite

Use :memory: for fast, isolated tests that need no setup or teardown:

import { createTheAuth } from '@glinr/theauth';
import { describe, beforeEach, it } from 'vitest';

let theauth: Awaited<ReturnType<typeof createTheAuth>>;

beforeEach(async () => {
  theauth = await createTheAuth({
    database: { provider: 'sqlite', url: ':memory:' },
    agents: { enabled: true },
  });
});

it('creates an agent', async () => {
  const agent = await theauth.agent.create({
    ownerId: 'user_1',
    name: 'Test Agent',
    type: 'autonomous',
    permissions: [],
  });

  expect(agent.id).toBeDefined();
});

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.

Start here

Setup

Sessions

Choosing a provider

Setup

Auto-migration

Schema overview

Peer dependencies

Testing with in-memory SQLite

Prisma adapter

Configuration

Test utilities

Terraform

​Choosing a provider

​Setup

​Auto-migration

​Schema overview

​Peer dependencies

​Testing with in-memory SQLite

​Related

Prisma adapter

Configuration

Test utilities

Terraform

Choosing a provider

Setup

Auto-migration

Schema overview

Peer dependencies

Testing with in-memory SQLite

Related