Schema

Schema & Auth Conventions (LOCKED — do not freelance)

plans/ARCHITECTURE.md is the locked source of truth. Stubs and ralph-loop implementations MUST follow it exactly. Do not invent table shapes, column names, or permission formats. If the architecture seems wrong, raise it with the user and update ARCHITECTURE.md first — never silently diverge.

DB schema organization (packages/db/src/schema/)

Domain-split, one file per domain. Re-export all from schema/index.ts.

• auth.ts — Better Auth core tables only: users, sessions, accounts, verifications, two_factor
• rbac.ts — roles, role_permissions, user_roles (NOT in auth.ts)
• login-audit.ts — login_audit_events (security log, 2-yr; distinct from business audit_events)
• audit.ts — audit_events (business audit, 7-yr, immutable INSERT-only)
• Domain schemas: catalog.ts, scheduling.ts, … per ARCHITECTURE §14+

ID / timestamp / duration standards (ARCHITECTURE §4)

• PK: uuid('id').primaryKey().default(sql\uuidv7()`)` — Postgres 18 native uuidv7
• Timestamps: timestamp('col', { withTimezone: true }) — always timestamptz
• Durations: integer/bigint with _ms suffix (millisecond precision)
• Tables: snake_case, plural. Columns: snake_case.
• Every FK relationship needs a Drizzle relations().

Better Auth schema flow — configure naming, generate, then postfix (ARCHITECTURE §11.2)

Better Auth core + plugin tables (users, sessions, accounts, verifications, two_factor, …) are generated by the Better Auth CLI: do as much naming as possible in the config, generate, then postfix only what config can’t express. Never hand-author them from memory, and never ship the raw generator output.

Step 1 — Map every name in the Better Auth config (do NOT postfix names). Model + field names are scattered across core AND each plugin, and each accepts its own mapping. Set them all so the generator emits snake_case columns and snake_case Drizzle property keys (verified: mapping a field renames both the column and the generated property key):

• Core models — user/session/account/verification: modelName + fields: { camelKey: "snake_col" } (e.g. image: "avatar_url", userId: "user_id").
• Each plugin — pass schema: { <model>: { modelName?, fields: { camelKey: "snake_col" } } }. A plugin only exposes the fields IT owns, so you must enumerate its field keys. llms.txt usually omits them — read the plugin’s TS schema def: node_modules/better-auth/dist/plugins/<name>/schema.d.mts. Verified keys (1.7.0-beta.3):
  • admin: user.{role,banned,banReason,banExpires}, session.{impersonatedBy} → map banReason→ban_reason, banExpires→ban_expires, impersonatedBy→impersonated_by.
  • twoFactor: user.{twoFactorEnabled}, twoFactor.{secret,backupCodes,userId,verified} → set twoFactor.modelName: "two_factor", map twoFactorEnabled→two_factor_enabled, backupCodes→backup_codes, userId→user_id.
• additionalFields on user: user_type (default ADMIN), account_state (default ACTIVE), superuser (default false) — all input: false (already snake, no mapping needed).

Use the same schema.fields mapping to disambiguate field names that clash with our business domain, not just for casing. Required example: the admin plugin’s role field is the plugin’s API-access role ONLY (admin/user/superadmin) — it is NOT our RBAC. A bare role column on users collides with our RBAC (roles/user_roles/role_permissions), so rename it: admin({ schema: { user: { fields: { role: "admin_role" } } } }) → column/property admin_role. Apply the same judgement to any future plugin field whose default name would be ambiguous against domain concepts.

Step 2 — Generate. auth CLI (the new tool — replaces @better-auth/cli; root devDep):

node node_modules/.bin/auth generate --config <auth-config> --output <dest> --yes

Monorepo gotcha (verified): jiti resolves better-auth relative to the config file’s directory, so the config MUST live in a workspace that depends on better-auth (apps/web/ or packages/auth/). Running from repo root, or via isolated npx/bunx, fails with Cannot find module 'better-auth'. The real config is apps/web/src/lib/auth.ts (§11.2).

Step 3 — Postfix what config can’t express (the whole point — schema MUST conform to §4). After Step 1 the names are already correct; these remain:

1. PK: id: text("id").primaryKey() → uuid("id").primaryKey().default(sql\uuidv7()`)on every table (the generator always emitstext` ids).
2. FK: text("user_id").references(...) → uuid("user_id").references(...) (must match the uuid PK).
3. Timestamps: every timestamp("col") → timestamp("col", { withTimezone: true }) (timestamptz). Drop the generator’s .$onUpdate(() => new Date()); ensure all created_at/updated_at are .notNull().defaultNow().
4. notNull / typing: add .notNull() where convention requires (e.g. banned); narrow user_type/account_state with .$type<UserType>() / .$type<AccountState>().
5. pg_trgm GIN indexes — DO NOT SKIP. The generator emits NO index on text search fields. Add a gin_trgm_ops GIN index on every frequently-searched field, at minimum users.email AND users.name (the hot search fields in ListUsers ilike + similarity): index("idx_users_email_trgm").using("gin", sql\${t.email} gin_trgm_ops`)and the same forname. Keep idx_users_account_state`. Same rule for domain tables (§4/§14).
6. Index naming: with Step-1 mappings the generator emits <snake_table>_<snake_col>_idx; rename to our idx_<table>_<col>.
7. Relations: DELETE every generated inline relations() block (they also mis-pluralize, e.g. sessionss). Relations live ONLY in packages/db/src/relations.ts (Drizzle v1 defineRelations, RQB v2).
8. Split: keep auth.ts to BA core + plugin tables only; RBAC/login-audit/business tables in their own files; re-export from schema/index.ts.

The codebase schema is the source of truth only after postfix.

Plugin changes REQUIRE re-map + regenerate + re-postfix. Whenever an auth plugin is added, removed, or upgraded such that its DB schema changes (e.g. enabling 2FA, passkey, organization, magic-link), you MUST (1) add that plugin’s schema field mappings to the config (read its schema.d.mts for the new field keys), (2) re-run auth generate, and (3) re-apply the Step-3 postfix to the affected tables. Do not hand-patch only the new columns and skip the mapping/postfix pass — that is exactly how the bootstrap drifted from §11.

RBAC & permissions (ARCHITECTURE §11.4)

• Permission key format: 3-segment <domain>:<entity>:<action>[_self] (e.g. master:subject:write, schedule:session:approve). Never 2-segment.
• PERMISSIONS const in packages/auth/src/permissions.ts is the SINGLE source of truth. No permissions DB table.
• role_permissions.key is the column name (NOT permission). Leading ! negates.
• Resolution (packages/auth/src/rbac.ts): union keys across user’s roles → split granted/negated → remove negated. superuser bypasses all.
• roles has slug (stable id, lowercase [a-z0-9_], unique CI), dual-language name_en/name_id + description_en/description_id, is_system.

Audit action naming (ARCHITECTURE §17)

• Business audit action field: entity:verb (e.g. subject:create, session:request). This is DISTINCT from permission keys.
• recordAudit(db | tx, ctx, payload) runs inside the caller’s transaction (rolls back with it).
• recordLoginAudit(db, record) is a standalone insert (security log).

DB client (CF Workers)

• Use the createDB(env) factory + DB / TX types from @packages/db. There is NO module-level db singleton — connection string comes from per-request env (Hyperdrive in prod, NEON_DATABASE_URL in dev). ARCHITECTURE pseudo-code that imports a singleton db is illustrative only.
• Service functions that may run in a transaction accept db: DB | TX.

Service layer boundary (ARCHITECTURE §1)

• @packages/service is auth-agnostic: it receives pre-authorized input + AuditContext. Never checks permissions inside service functions.
• requirePermission(...) is called by the Hono route handler BEFORE invoking the service function.