Skip to content

Database

One Cloudflare D1 database holds everything except the per-client codes and tokens, which live in one Durable Object per client. The schema is 21 SQL files in the auth repo. Apply them in the order below.

Applying them

where command what it does
your machine npm run worker:db:local runs server/db-apply.mjs: every file in order, and skips any ALTER TABLE whose column is already there, so it is safe to run again
production npm run worker:db:remote runs wrangler d1 execute omxus-auth-v2 --remote on each file in order

The remote command is safe on a new database only. SQLite has no ADD COLUMN IF NOT EXISTS, so a second run stops at the first ALTER TABLE with "duplicate column name". On a database that already has the tables, run just the files you need, one at a time.

Two things to change before you run it

  • The database name omxus-auth-v2 is written into both commands. If yours is called something else, change it in package.json and server/db-apply.mjs.
  • The local list leaves out server/accounts/avatar-uploads.sql, so on your machine avatar uploads have no table. Run that one file by hand: npx wrangler d1 execute omxus-auth-v2 --local --file=server/accounts/avatar-uploads.sql.

A full local run gives 41 tables; production has 42 (with avatar_uploads). Checked 30 Oct 2026 against a scratch database.

The files, in order

# file adds
1 server/schema.unified.sql auth_nonces auth_sessions auth_attempts oidc_clients oidc_sector_pending oidc_codes oidc_access_tokens identities vouches
2 server/passkey/passkey.sql auth_passkeys passkey_challenges
3 server/vault/vault.sql auth_vaults
4 server/areas/areas.sql auth_areas
5 server/email/email.sql auth_email_accounts auth_email_pending auth_email_resets
6 server/oidc-revocation.sql column oidc_access_tokens.revoked_at
7 server/oidc-clients/seed-clients.sql the first client, omxus-web
8 server/vouch/settle.sql settlements
9 server/qr/qr-signin.sql auth_qr_signins
10 server/qr/qr-code.sql column auth_qr_signins.code (only for tables made before the column was in file 9)
11 server/help/help.sql help_calls help_responses
12 server/accounts/accounts.sql accounts account_contacts account_usernames account_passkeys account_codes account_challenges account_sessions account_throttle
13 server/accounts/door-standard.sql column oidc_clients.username_scope
14 server/accounts/accounts-hardening.sql account_families account_access_tokens account_refresh_tokens account_audit account_state account_passkey_flags account_pending_contacts account_vouch_verified
15 server/accounts/oidc-standard.sql account_oidc_codes
16 server/accounts/client-theme.sql column oidc_clients.theme
17 server/accounts/legacy-import.sql account_legacy_passwords account_legacy_ids account_roles
18 server/accounts/accounts-claims.sql column account_pending_contacts.salt_contact, and moves unverified contacts into pending claims
19 server/accounts/account-home.sql column oidc_clients.account_home
20 server/accounts/profile.sql account_profiles
21 server/accounts/avatar-uploads.sql avatar_uploads (production list only; see above)

Files 6, 10, 13, 16, 18 and 19 hold an ALTER TABLE. Every other statement uses IF NOT EXISTS, OR IGNORE or OR REPLACE.

Use schema.unified.sql, not schema.sql

server/schema.sql stops at the seven sign-in and OIDC tables. It has no identities or vouches, and the worker reads both, so the first sign-in or vouch fails.

What each group is for

tables used by
auth_nonces auth_sessions auth_attempts Omxus identity sign-in: issued challenges, sessions, throttling. See Security model.
oidc_clients oidc_sector_pending every registered site, and sector requests waiting for an operator. See Registering a site.
oidc_codes oidc_access_tokens the shared code and token tables. Per-client codes and tokens live in Durable Objects instead.
identities vouches settlements Omxus identities, vouches, and which verified people have been written to the chain. See Vouching.
auth_passkeys passkey_challenges passkeys for Omxus identities. See Passkeys.
auth_vaults auth_areas one encrypted blob per identity each; the server cannot read them. See Memory.
auth_email_* the email door. See Email and texts.
auth_qr_signins sign in on a screen by scanning it with a phone (two-minute rows, with a typed code as a fallback).
help_calls help_responses calls for help and who accepted them.
accounts and every account_* the standard door: ordinary accounts, contacts, usernames, passkeys, sessions, tokens, audit, imports, profiles. See Sessions, activity and the account and Profiles and claims.
avatar_uploads uploaded pictures, up to three per account.

The first client

File 7 inserts omxus-web, the Omxus app (door full, sector omxus). Without at least one client, /oidc/authorize refuses every request. On your own provider, replace its redirect URIs with yours, or register a site through /developers or POST /oidc/register and leave the seed alone.

oidc_clients.account_home is never set by registration. An operator turns it on for the one site allowed to list and disconnect a person's other sites:

npx wrangler d1 execute <your-db> --remote \
  --command="UPDATE oidc_clients SET account_home = 1 WHERE client_id = '<id>'"

Clean-up

The hourly cron (scheduled in worker/index.js) runs the standard door's prune:

rows go when
account_codes account_challenges account_oidc_codes expired
account_access_tokens account_refresh_tokens account_families expired, or 30 days after the family was revoked
account_throttle older than 15 minutes
account_audit older than 90 days
account_pending_contacts older than 30 days (imported accounts keep theirs)
unverified accounts 30 days with no verified contact, no sign-in and no activity; up to 200 per run, everything attached goes too

The other tables are not swept by the cron. One-use rows (auth_nonces, passkey_challenges, oidc_codes) are deleted as they are spent; auth_qr_signins drops expired rows each time a new QR sign-in starts.