Database
The D1 schema, the versioned patch/migration convention, and how to apply schema changes.
eetr-auth persists everything in Cloudflare D1 (SQLite). The schema is managed with a snapshot
plus versioned patches.
Authoritative source
db/README.md in the repo is authoritative on schema mechanics (it lives next to schema.sql and
db/patches/). This page is the public overview.
Snapshot vs patches
db/schema.sql— the complete authoritative snapshot of the current schema, for a fresh install. It seeds the default OIDC scopes (openid,profile,email) with idempotentINSERT OR IGNOREso a new install can do OpenID Connect out of the box.db/patches/<version>.sql— incremental patches named after released schema versions (e.g.0.1.0.sql). A patch describes only the delta from the previous released schema — not a copy of the full snapshot.
Rule of thumb:
schema.sql= the latest full schema for a fresh database.patches/<version>.sql= how an existing database catches up from the previous released version.
Migration strategy
db:migrate / db:migrate:remote do the following:
- Read
schema_metadata.schema_version. - If the metadata table doesn't exist, assume version
0.0.0. - If the database has no application tables yet, apply
schema.sqlas the clean-install snapshot. - Otherwise, apply every patch in
db/patcheswith a version greater than the current, in ascending semver order. - Each patch updates
schema_metadata.schema_versionto its own version at the end.
Not every app release needs a database patch. If a release doesn't change the schema, no new patch is added and the schema version stays put.
Applying the schema
From the repository root:
| Command | Use |
|---|---|
npm run db:schema | Apply the fresh snapshot to local D1 |
npm run db:schema:remote | Apply the fresh snapshot to remote D1 |
npm run db:bootstrap | Both local + remote |
npm run db:migrate | Apply versioned patches to local D1 (upgrades) |
npm run db:migrate:remote | Apply versioned patches to remote D1 (upgrades) |
Fresh install vs upgrade
Use the db:schema* commands for a fresh installation. Use db:migrate* only when upgrading an
existing database with versioned patches. On a clean install, npm run setup:remote applies the
snapshot for you.
Schema domains
The database is organized around users, OAuth clients, tokens, and environments:
Key tables include users, user_totp (encrypted TOTP secrets), clients, environments,
scopes, user_consents, client_claims, api_keys, authorization_codes, tokens, refresh_tokens,
passkeys, password_policies, and token_activity_log.
client_claims holds the static custom claims a client injects into its access tokens, with a
value_type so a numeric or boolean claim keeps its JSON type in the JWT.
user_consents records what an end user has authorized: one row per (user, client) holding the
accumulated union of consented scope names. When that set already covers every requested
scope, the consent screen still appears (to allow switching accounts) but no longer lists the scopes.
Deleting a row withdraws consent.
api_keys holds the long-lived API keys a machine caller exchanges for
an access token. key_id is stored in the clear as a lookup handle — an Argon2id digest is not
searchable — and only the secret half is hashed into key_hash. user_id is mandatory and becomes
the minted token's sub; both it and client_id cascade on delete, so a key never outlives either.
api_key_scopes keys on client_scopes(id) like token_scopes, so ungranting a scope from the
client removes it from every key that held it.
tokens.api_key_id records provenance: it is set only on tokens minted by the API-key exchange and
is NULL for every OAuth grant. The
self-service API-key routes
refuse a token that carries it, so a key cannot issue itself a successor with a later expiry or
wider scopes than it holds.
users.is_test_user and clients.is_test are the passwordless
test user / test client pair. A test user stores the empty
sentinel '' in password_hash — the same idiom clients.client_secret uses for public clients —
which verifyPassword() rejects on shape, so the column stays NOT NULL without a table rebuild.
Both flags are set at creation and immutable, and each carries a CHECK that SQLite applies on
INSERT and UPDATE alike: a test user can never be an admin, and a dynamically registered client
can never be a test client.
clients.home_url, clients.home_display_name and clients.show_on_home (patch 0.6.0) are the
optional home link: a client with the flag set and a URL is listed
on the page a signed-in non-admin sees at /.
Identifiers vs labels
environments.name and scopes.scope_name are identifiers, not display text.
environments.name is emitted as the environment JWT claim, is the environmentName field on
POST /api/token/validate, and is denormalized into token_activity_log.environment_name;
scopes.scope_name is the protocol token clients send in scope. Renaming either changes
behaviour. Each has a nullable display_name beside it for the human-facing label, and scopes
additionally has a description for consent-screen copy.
Creating an admin user
Create an admin (users.is_admin = 1) in both local and remote D1 with username + email. The
script stores a random placeholder password hash; finish setup via password reset (remote requires a
configured site URL and working email).
npm run db:create-admin -- <username> <email>- Local seed admin:
npm run db:seed-local-admin(admin/admin, MD5, local-only) - Seed default remote admin:
npm run db:seed-remote-admin(admin/admin, Argon2id)
Authoring the next schema release
When the schema changes in a future release:
- Update
schema.sqlso it matches the new latest schema. - Add / continue the unreleased patch file, named with the target schema release version, with only the delta from the previous released schema.
- Make the patch idempotent enough for the expected upgrade path.
- End the patch by updating
schema_metadata.schema_versionto the released version.