"\nCREATE UNIQUE INDEX IF NOT EXISTS idx_account_email ON account (LOWER(email)) WHERE email IS NOT NULL" auth/auth_ddl.ts
Identity-table DDL — CREATE TABLE, index, and seed statements for the
core auth tables (account, actor, role_grant, auth_session, api_token,
bootstrap_lock, invite, app_settings).
Consumed by auth/migrations.ts. Paired with auth/audit_log_ddl.ts
(audit table) and auth/role_grant_offer_ddl.ts (offer table) — DDL lives
in *_ddl.ts, Zod schemas in *_schema.ts.
Declarations #
18 declarations
ACCOUNT_EMAIL_INDEX #
ACCOUNT_EMAIL_INDEX
ACCOUNT_SCHEMA #
ACCOUNT_SCHEMA
"\nCREATE TABLE IF NOT EXISTS account (\n id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n username TEXT UNIQUE NOT NULL,\n email TEXT,\n email_verified BOOLEAN NOT NULL DEFAULT false,\n password_hash TEXT NOT NULL,\n created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),\n created_by UUID,\n updated_at TIMESTAMPTZ ... Identity-table DDL — CREATE TABLE, index, and seed statements for the
core auth tables (account, actor, role_grant, auth_session, api_token,
bootstrap_lock, invite, app_settings).
Consumed by auth/migrations.ts. Paired with auth/audit_log_ddl.ts
(audit table) and auth/role_grant_offer_ddl.ts (offer table) — DDL lives
in *_ddl.ts, Zod schemas in *_schema.ts.
ACCOUNT_USERNAME_CI_INDEX #
ACCOUNT_USERNAME_CI_INDEX
"\nCREATE UNIQUE INDEX IF NOT EXISTS idx_account_username_ci ON account (LOWER(username))" ACTOR_INDEX #
ACTOR_INDEX
"\nCREATE INDEX IF NOT EXISTS idx_actor_account ON actor(account_id)" ACTOR_NAME_LOWER_INDEX #
ACTOR_NAME_LOWER_INDEX
"\nCREATE INDEX IF NOT EXISTS idx_actor_name_lower ON actor (LOWER(name) text_pattern_ops)" Functional index on LOWER(actor.name) supporting case-insensitive
prefix search by actor_search (LOWER(name) LIKE LOWER(query) || '%').
text_pattern_ops keeps the LIKE-prefix pattern index-eligible — without
it the planner falls back to a sequential scan once the table grows.
ACTOR_SCHEMA #
ACTOR_SCHEMA
"\nCREATE TABLE IF NOT EXISTS actor (\n id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n account_id UUID NOT NULL REFERENCES account(id) ON DELETE CASCADE,\n name TEXT NOT NULL,\n created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),\n updated_at TIMESTAMPTZ,\n updated_by UUID REFERENCES actor(id) ON DELETE SET NULL\... API_TOKEN_INDEX #
API_TOKEN_INDEX
"\nCREATE INDEX IF NOT EXISTS idx_api_token_account ON api_token(account_id)" API_TOKEN_SCHEMA #
API_TOKEN_SCHEMA
"\nCREATE TABLE IF NOT EXISTS api_token (\n id TEXT PRIMARY KEY,\n account_id UUID NOT NULL REFERENCES account(id) ON DELETE CASCADE,\n name TEXT NOT NULL,\n token_hash TEXT NOT NULL,\n expires_at TIMESTAMPTZ,\n last_used_at TIMESTAMPTZ,\n last_used_ip TEXT,\n created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()\n)" APP_SETTINGS_SCHEMA #
APP_SETTINGS_SCHEMA
"\nCREATE TABLE IF NOT EXISTS app_settings (\n id INTEGER PRIMARY KEY DEFAULT 1 CHECK (id = 1),\n open_signup BOOLEAN NOT NULL DEFAULT false,\n updated_at TIMESTAMPTZ,\n updated_by UUID\n)" APP_SETTINGS_SEED #
APP_SETTINGS_SEED
"\nINSERT INTO app_settings (id) VALUES (1) ON CONFLICT DO NOTHING" AUTH_SESSION_INDEXES #
AUTH_SESSION_INDEXES
string[] AUTH_SESSION_SCHEMA #
AUTH_SESSION_SCHEMA
"\nCREATE TABLE IF NOT EXISTS auth_session (\n id TEXT PRIMARY KEY,\n account_id UUID NOT NULL REFERENCES account(id) ON DELETE CASCADE,\n created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),\n expires_at TIMESTAMPTZ NOT NULL,\n last_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW()\n)" BOOTSTRAP_LOCK_SCHEMA #
BOOTSTRAP_LOCK_SCHEMA
"\nCREATE TABLE IF NOT EXISTS bootstrap_lock (\n id INTEGER PRIMARY KEY DEFAULT 1 CHECK (id = 1),\n bootstrapped BOOLEAN NOT NULL DEFAULT false\n)" BOOTSTRAP_LOCK_SEED #
BOOTSTRAP_LOCK_SEED
"\nINSERT INTO bootstrap_lock (id, bootstrapped)\n SELECT 1, EXISTS(SELECT 1 FROM account)\n ON CONFLICT DO NOTHING" Seed the bootstrap_lock table, setting bootstrapped based on whether accounts exist.
INVITE_INDEXES #
INVITE_INDEXES
string[] INVITE_SCHEMA #
INVITE_SCHEMA
"\nCREATE TABLE IF NOT EXISTS invite (\n id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n email TEXT,\n username TEXT,\n claimed_by UUID REFERENCES account(id) ON DELETE SET NULL,\n claimed_at TIMESTAMPTZ,\n created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),\n created_by UUID REFERENCES actor(id) ON DELETE SET NU... ROLE_GRANT_INDEXES #
ROLE_GRANT_INDEXES
string[] ROLE_GRANT_SCHEMA #
ROLE_GRANT_SCHEMA
"\nCREATE TABLE IF NOT EXISTS role_grant (\n id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n actor_id UUID NOT NULL REFERENCES actor(id) ON DELETE CASCADE,\n role TEXT NOT NULL,\n created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),\n expires_at TIMESTAMPTZ,\n revoked_at TIMESTAMPTZ,\n revoked_by UUID REFERENCES a...