Database

eetr-auth persists everything in Cloudflare D1 (SQLite). The schema is managed with a snapshot plus versioned patches.

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 idempotent INSERT OR IGNORE so 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:

1. Read schema_metadata.schema_version.
2. If the metadata table doesn't exist, assume version 0.0.0.
3. If the database has no application tables yet, apply schema.sql as the clean-install snapshot.
4. Otherwise, apply every patch in db/patches with a version greater than the current, in ascending semver order.
5. Each patch updates schema_metadata.schema_version to 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:

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. The authorize flow skips the consent screen when that set already covers every requested scope, and 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.

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:

1. Update schema.sql so it matches the new latest schema.
2. Add / continue the unreleased patch file, named with the target schema release version, with only the delta from the previous released schema.
3. Make the patch idempotent enough for the expected upgrade path.
4. End the patch by updating schema_metadata.schema_version to the released version.