Schema

Better IAM stores every record in one generic table, iam_records, as a JSON document tagged with its collection (the record kind, such as identities or sessions). There are no per-feature tables, so a new feature never needs a new table, and the schema is identical on PostgreSQL, SQLite, and libSQL apart from column types and index definitions. This page lists the tables, their indexes, the rules every record follows, and every collection the server writes.

Read it to plan capacity, write reporting queries, or review what Better IAM keeps. Never write to these tables directly: services enforce references between records inside serialized transactions, and a raw SQL write can leave a revoked session alive or break the audit chain.

Tables

iam_records

CREATE TABLE IF NOT EXISTS iam_records (
  collection TEXT COLLATE "C" NOT NULL,
  id         TEXT COLLATE "C" NOT NULL,
  tenant_id  TEXT COLLATE "C" NOT NULL,
  unique_key TEXT COLLATE "C",
  data       TEXT NOT NULL,
  PRIMARY KEY (collection, id),
  UNIQUE (collection, tenant_id, unique_key)
);

On SQLite and libSQL the same table uses plain TEXT columns, whose default BINARY collation also orders by code point.

Uniqueness that must hold across all tenants is expressed through the record ID instead: tenantAliases uses the alias itself as its ID, and domainOwners is keyed by the verified domain.

iam_schema_version and iam_migrations

iam_schema_version holds one row with version = 1; a database with another version is refused with SCHEMA_VERSION. iam_migrations holds one row per applied schema step (name, applied_at in epoch milliseconds). See Migrations.

Indexes

All index names start with iam_records_. Every B-tree index added after 0001_records ends in id, so one index both finds rows and returns them in ORDER BY id order.

Indexed fields

INDEXED_FIELDS from better-iam/core lists the high-cardinality fields that lookups filter on across tenants or inside large collections. On SQLite and libSQL each one has its own partial expression index; on PostgreSQL the single GIN document index serves them all. The list affects only speed, never results. LOOKUP_INDEX_STEPS maps each field to the step that indexes it.

Two more field lists drive ordered reads (findOrdered):

• ORDERED_FIELDS: timestamp and sequence, indexed per tenant for audit paging by time and by chain sequence.
• EXPIRY_FIELDS: expiresAt, deliveredAt, and failedAt, indexed per collection so the retention sweep finds due records without scanning.

Record rules

Every adapter enforces the same rules through the shared RecordStore base. Breaking one fails with INVALID_RECORD before anything is written.

Field values may hold any string, including U+0000; the PostgreSQL adapter stores such values in a reversible encoding (value encoding).

Collections

The lists below are taken from the server source. Every tenant-scoped collection is swept when a deleted tenant is purged (iam.purgeDeleted(), the purge CLI command), except where noted. better-iam doctor lists the collections that actually hold records in your database, with counts, under storage.collections.

Tenancy and hosts

Identities and sessions

Authentication

Access model and catalog

Governance

Organizations, product features, and billing

AI agents and inference

Keys, secrets, and certificates

Security

Federation protocols

Delivery and audit

Plugins

Plugins keep their records in collections of their own, through the same IamStore, and remove them in their purge callback. The reference @better-iam/projects plugin stores tenant-scoped records in projects.

What the retention sweep deletes

iam.sweepExpired() (the sweep CLI command) deletes records the system already treats as dead, oldest first, in short batches. Pending deliveries are never swept.

Everything else (invitations, access requests, usage records, SCIM connections) is history or can be renewed, so the sweep never deletes it by age. See Scheduled jobs.

Read the schema at runtime

IamStore.describe() (and doctor) report the schema version, applied migrations, record counts per collection, and adapter settings:

import { iam } from '../iam';

const description = await iam.store.describe?.();
for (const { name, records } of description?.collections ?? []) console.log(name, records);
await iam.store.close();

{
  "adapter": "postgres",
  "schemaVersion": 1,
  "migrations": [{ "name": "0001_records", "appliedAt": 1767225600000 }],
  "collections": [{ "name": "identities", "records": 1204 }],
  "settings": { "serverVersion": "17.2", "synchronousCommit": "on", "sizeBytes": 18874368, "lockTimeoutMs": 5000 }
}

describe() is optional in the IamStore contract; a custom adapter without it makes doctor report storage-undescribed.

Next steps