Db Conventions

DB Conventions — Domain Tables

Supplements schema.md. These rules apply to hand-authored domain tables (catalog.ts, scheduling.ts, rbac.ts, etc.).

updated_at

.notNull().defaultNow(). Do NOT use $onUpdate/$onUpdateFn — Drizzle update hooks are not used in this codebase. Bump explicitly in the service layer:

await tx.update(table).set({ updated_at: new Date(), ...changes })

Junction table indexes

Two single-column FK indexes (one per FK column) + one unique composite over the pair:

index("idx_user_roles_user").on(t.user_id),
index("idx_user_roles_role").on(t.role_id),
uniqueIndex("uq_user_roles_user_role").on(t.user_id, t.role_id),

Naming: idx_<table>_<col> for single-col, uq_<table>_<cols> for unique composite.

Full-text search (long text)

tsvector generated column + rum index (rum_tsvector_ops). NOT a plain GIN index. See ARCHITECTURE §“Full-text search” for the locked pattern.