Data model

Cairn owns a fixed set of tables: identity (users, user_identities, sessions), tenancy (workspaces, memberships), access and accountability (api_keys, audit_events, files), and the short-lived state behind sign-in and MFA. EnsureSchema creates and migrates all of them; they are not yours to alter. The authoritative snapshots ship in the module under db/schema/, with a Postgres tree and a SQLite twin that differ only in column types (TIMESTAMPTZ vs TIMESTAMP, BIGINT vs INTEGER).

Your application’s domain tables live beside these, in the same database, holding foreign keys into users and workspaces. That contract — what you may reference, how you migrate, how you scope queries — is covered in Extending the model. This page documents what Cairn puts there.

Conventions

• IDs are prefix_ULID — usr_01J9Z3K7Q8X2… — minted by cairn.NewID. ULIDs embed their creation time, so ids sort chronologically and ORDER BY id is a valid time ordering (the audit log and file listings rely on this). The prefixes in use: usr (users), wks (workspaces), ses (sessions), key (API keys), aud (audit events), file (files), pk (passkey credentials).
• Not every table gets a prefixed id. memberships is keyed by the composite (user_id, workspace_id); the flow tables are keyed by the SHA-256 hash of their token or state; mfa_totp is keyed by user_id.
• Tenant data carries workspace_id. Every workspace-scoped table has a workspace_id TEXT NOT NULL REFERENCES workspaces(id) column plus an index on it — tenancy is a foreign key, not a naming convention.
• Secrets are never stored raw. Session tokens, magic-link tokens, API keys, OAuth state, and recovery codes are stored as hashes; the TOTP secret (which verification needs in plaintext) is stored encrypted.
• Dual-dialect parity. Postgres and SQLite schemas are maintained as twins; every table, column, constraint, and index exists identically in both.

Core identity

users

One row per human. The load-bearing columns:

Column	Notes
id	usr_…, primary key.
primary_email	NOT NULL UNIQUE — the identity anchor. No framework query updates or deletes a user row after creation, so it is write-once in practice.
display_name	NOT NULL DEFAULT ''.
disabled_at	Nullable timestamp reserved for disabling accounts; no framework behavior consults it yet.

user_identities

Links external sign-in identities (OAuth providers) to users. The primary key is (provider, subject): one external identity resolves to exactly one user, while a user may hold many identities. user_id is a foreign key to users(id).

sessions

One row per live session, minted by a successful sign-in flow.

Column	Notes
token_hash	NOT NULL UNIQUE — the SHA-256 hex of the 32-byte random cookie token. The raw token exists only in the cairn_session cookie.
user_id	FK to users(id).
active_workspace_id	Nullable FK to workspaces(id) — the workspace requests are scoped to; SetActiveWorkspace updates it.
expires_at	Enforced when the session is resolved; expired tokens are rejected.
last_seen_at, user_agent, ip	Session-listing metadata.
mfa_required, mfa_satisfied	The MFA gate: a session with mfa_required true and mfa_satisfied false is pending and most RPCs refuse it.

Two invariants worth knowing: Logout deletes the row (revocation is server-side, not cookie-side), and crossing the MFA gate rotates the token — a new token replaces the stored hash while the session id and the rest of its state are preserved, so the pre-MFA token dies at the moment of escalation.

Tenancy

workspaces

The tenant boundary. slug is NOT NULL UNIQUE; name is display text. Every piece of tenant data in Cairn — and, by convention, in your app — hangs off workspaces(id).

memberships

Who belongs to which workspace, and as what. Composite primary key (user_id, workspace_id) — one role per user per workspace — with the role constrained in the schema itself:

role TEXT NOT NULL CHECK (role IN ('owner','admin','member'))

That CHECK is the authorization backbone: RequireRole and every permission decision in Cairn resolve to one of these three values, hierarchically (owner ≥ admin ≥ member — see Auth model). Which workspace a request acts in is not stored here but on the session (sessions.active_workspace_id, above).

A nullable disabled_at timestamp completes the row: null means enabled, and when set the member keeps this row and its role but resolves to no active workspace — a reversible, per-workspace disable (see Disabling members).

Access & accountability

api_keys

Bearer credentials for machine clients. key_hash is NOT NULL UNIQUE — the raw cairn_live_… key is shown once and never stored. Each key belongs to both a user and a workspace (user_id, workspace_id FKs), and its lifecycle is column-driven: expires_at (nullable — keys may never expire), revoked_at (revoked keys are kept so ListAPIKeys shows history), last_used_at. The janitor deletes keys whose expires_at has passed; revoked-but-unexpired and never-expiring keys persist.

audit_events

Append-only accountability log. workspace_id and actor_user_id are NOT NULL foreign keys — every event happened in a workspace and was caused by a user. actor_api_key_id is a plain nullable column (deliberately not a FK): it records which key acted without tying the event’s lifetime to the key row. The (workspace_id, id) index plus chronological ids make cursor pagination by id the natural read pattern — see Audit log.

files

The metadata half of file storage: name, content_type, size_bytes, created_by (FK to users(id)), scoped by workspace_id. The content itself lives in a blob store behind a pluggable port — see Files.

Flow & credential state

The remaining tables hold sign-in ceremony state and MFA material. The background janitor sweeps expired rows from the short-TTL tables every ten minutes; durable rows are only removed by explicit user action.

Table	Purpose	Lifecycle
magic_links	Pending email sign-in tokens, keyed by token_hash.	Short TTL; single-use, enforced atomically (SET used_at … WHERE used_at IS NULL); janitor-reaped at expiry.
oauth_states	In-flight OAuth authorization state + PKCE verifier, keyed by state_hash.	Short TTL; deleted on callback; janitor-reaped at expiry.
webauthn_sessions	In-flight passkey ceremony state, keyed by session_hash. user_id is nullable and not a FK — a discoverable-login ceremony starts before the user is known.	Short TTL; deleted on completion; janitor-reaped at expiry.
passkey_credentials	Registered passkeys: credential_id (UNIQUE), the credential blob, aaguid, a user-facing name, last_used_at.	Durable; removed only when the user deletes the passkey.
mfa_totp	One TOTP enrollment per user (user_id is the PK). secret is stored encrypted; confirmed_at is null until the user proves a first code.	Durable; removed on MFA disable.
mfa_recovery_codes	Hashed one-time recovery codes, keyed by (user_id, code_hash).	Durable; used_at marks consumption; replaced as a set on regeneration.

Relationships

Every foreign key in the schema, in one table:

From	To	Via	Meaning
memberships	users	user_id	The member.
memberships	workspaces	workspace_id	The workspace they belong to.
sessions	users	user_id	Whose session it is.
sessions	workspaces	active_workspace_id (nullable)	The workspace the session’s requests are scoped to.
user_identities	users	user_id	The user an external identity resolves to.
passkey_credentials	users	user_id	The passkey’s owner.
mfa_totp	users	user_id (also the PK)	The enrolled user — at most one TOTP row each.
mfa_recovery_codes	users	user_id	The codes’ owner.
api_keys	users	user_id	Who created (and owns) the key.
api_keys	workspaces	workspace_id	The workspace the key is scoped to.
audit_events	workspaces	workspace_id	Where the action happened.
audit_events	users	actor_user_id	Who did it.
files	workspaces	workspace_id	The workspace that owns the file.
files	users	created_by	Who uploaded it.

Two columns look like foreign keys but intentionally are not: webauthn_sessions.user_id (the user may not exist yet mid-ceremony) and audit_events.actor_api_key_id (audit rows must outlive the key they mention).

Related

• Extending the model — putting your own tables beside these.
• Auth model — how sessions, workspaces, and roles behave at runtime.
• Sign-in — the flows that write the flow tables.
• Files — the blob half of file storage.