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.

view source

Declarations
#

18 declarations

ACCOUNT_EMAIL_INDEX
#

auth/auth_ddl.ts view source

"\nCREATE UNIQUE INDEX IF NOT EXISTS idx_account_email ON account (LOWER(email)) WHERE email IS NOT NULL" import {ACCOUNT_EMAIL_INDEX} from '@fuzdev/fuz_app/auth/auth_ddl.js';

ACCOUNT_SCHEMA
#

auth/auth_ddl.ts view source

"\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 ...

import {ACCOUNT_SCHEMA} from '@fuzdev/fuz_app/auth/auth_ddl.js';

ACCOUNT_USERNAME_CI_INDEX
#

auth/auth_ddl.ts view source

"\nCREATE UNIQUE INDEX IF NOT EXISTS idx_account_username_ci ON account (LOWER(username))" import {ACCOUNT_USERNAME_CI_INDEX} from '@fuzdev/fuz_app/auth/auth_ddl.js';

ACTOR_INDEX
#

auth/auth_ddl.ts view source

"\nCREATE INDEX IF NOT EXISTS idx_actor_account ON actor(account_id)" import {ACTOR_INDEX} from '@fuzdev/fuz_app/auth/auth_ddl.js';

ACTOR_NAME_LOWER_INDEX
#

auth/auth_ddl.ts view source

"\nCREATE INDEX IF NOT EXISTS idx_actor_name_lower ON actor (LOWER(name) text_pattern_ops)" import {ACTOR_NAME_LOWER_INDEX} from '@fuzdev/fuz_app/auth/auth_ddl.js';

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
#

auth/auth_ddl.ts view source

"\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,...

import {ACTOR_SCHEMA} from '@fuzdev/fuz_app/auth/auth_ddl.js';

API_TOKEN_INDEX
#

auth/auth_ddl.ts view source

"\nCREATE INDEX IF NOT EXISTS idx_api_token_account ON api_token(account_id)" import {API_TOKEN_INDEX} from '@fuzdev/fuz_app/auth/auth_ddl.js';

API_TOKEN_SCHEMA
#

auth/auth_ddl.ts view source

"\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)"

import {API_TOKEN_SCHEMA} from '@fuzdev/fuz_app/auth/auth_ddl.js';

APP_SETTINGS_SCHEMA
#

auth/auth_ddl.ts view source

"\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)"

import {APP_SETTINGS_SCHEMA} from '@fuzdev/fuz_app/auth/auth_ddl.js';

APP_SETTINGS_SEED
#

auth/auth_ddl.ts view source

"\nINSERT INTO app_settings (id) VALUES (1) ON CONFLICT DO NOTHING" import {APP_SETTINGS_SEED} from '@fuzdev/fuz_app/auth/auth_ddl.js';

AUTH_SESSION_INDEXES
#

auth/auth_ddl.ts view source

string[] import {AUTH_SESSION_INDEXES} from '@fuzdev/fuz_app/auth/auth_ddl.js';

AUTH_SESSION_SCHEMA
#

auth/auth_ddl.ts view source

"\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)"

import {AUTH_SESSION_SCHEMA} from '@fuzdev/fuz_app/auth/auth_ddl.js';

BOOTSTRAP_LOCK_SCHEMA
#

auth/auth_ddl.ts view source

"\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)"

import {BOOTSTRAP_LOCK_SCHEMA} from '@fuzdev/fuz_app/auth/auth_ddl.js';

BOOTSTRAP_LOCK_SEED
#

auth/auth_ddl.ts view source

"\nINSERT INTO bootstrap_lock (id, bootstrapped)\n SELECT 1, EXISTS(SELECT 1 FROM account)\n ON CONFLICT DO NOTHING" import {BOOTSTRAP_LOCK_SEED} from '@fuzdev/fuz_app/auth/auth_ddl.js';

Seed the bootstrap_lock table, setting bootstrapped based on whether accounts exist.

INVITE_INDEXES
#

auth/auth_ddl.ts view source

string[] import {INVITE_INDEXES} from '@fuzdev/fuz_app/auth/auth_ddl.js';

INVITE_SCHEMA
#

auth/auth_ddl.ts view source

"\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...

import {INVITE_SCHEMA} from '@fuzdev/fuz_app/auth/auth_ddl.js';

ROLE_GRANT_INDEXES
#

auth/auth_ddl.ts view source

string[] import {ROLE_GRANT_INDEXES} from '@fuzdev/fuz_app/auth/auth_ddl.js';

ROLE_GRANT_SCHEMA
#

auth/auth_ddl.ts view source

"\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...

import {ROLE_GRANT_SCHEMA} from '@fuzdev/fuz_app/auth/auth_ddl.js';

Imported by
#

auth/migrations.ts