--- name: sk-db description: Kit-shipped DB helpers for the TimeKast Starter Kit — mandatory `auditFields`, optional `softDeleteFields` + `notDeleted()`, dual-ID via `getNextHumanId` (PG sequences + 23505 retry), pagination utils (`parsePaginationParams`/`buildPaginationSQL`/`createCachedCount`), `canHardDeleteUser`, migration workflow (`db:generate` → `db:migrate`), and `pnpm db:query` runner. Invoke when defining schema, writing mutations, or inspecting DB in kit projects. For portable Drizzle patterns → `kb-db`. last-verified: 2026-04-23 --- # sk-db — Kit-shipped DB Helpers & Conventions > **Kit-shipped — not portable.** Travels with the TimeKast Starter Kit. Grounded in real files: `@/lib/db/drizzle`, `@/lib/db/schema/**`, `@/lib/db/helpers/audit-fields`, `@/lib/db/helpers/soft-delete`, `@/lib/db/helpers/can-hard-delete`, `@/lib/db/utils/pagination`, `@/lib/utils/human-id`, `drizzle.config.ts`, `src/lib/db/migrations/**`. > > Pair: [`kb-db`](../kb-db/SKILL.md) — portable Drizzle patterns (schema conventions, dual-ID modeling, cursor vs offset, index selection, `EXPLAIN ANALYZE`). For Server Actions that consume these helpers → [`sk-api`](../sk-api/SKILL.md). For tests that mock the DB client → [`sk-testing-nextjs`](../sk-testing-nextjs/SKILL.md). For DB security (SQLi prevention, parameterized queries) → [`sk-security`](../sk-security/SKILL.md) / [`kb-security`](../kb-security/SKILL.md). > > **Canonical symbol names:** grep [`project/reference/HOOKS.md`](../../../project/reference/HOOKS.md) before naming `auditFields` / `softDeleteFields` / `notDeleted` / `canHardDeleteUser` / pagination utilities in a new file — autogenerated on pre-commit, SSOT for names and import paths. --- ## 1. File layout (what the kit ships) ``` src/lib/db/ schema/ index.ts # re-exports every schema file (SSOT consumed by drizzle-kit) users.ts # users + NextAuth tables (accounts/sessions/…) enums.ts # centralized pgEnum (one-file-per-domain convention) [entity].ts # one file per domain helpers/ audit-fields.ts # createdAt/createdBy/modifiedAt/modifiedBy soft-delete.ts # deletedAt/deletedBy + notDeleted() can-hard-delete.ts # eligibility check before permanent delete (§6) queries/ # thin data-access helpers (e.g. getFreshUser) utils/ pagination.ts # parsePaginationParams, buildPaginationSQL, createCachedCount (§5) drizzle.ts # db client (Neon Pool + drizzle) migrations/ # generated SQL (drizzle-kit output) seed.ts / seeds/ # idempotent seeders ``` > `drizzle.config.ts` points `schema` at `./src/lib/db/schema/index.ts` and `out` at `./src/lib/db/migrations`. Re-export every new schema file from `index.ts` so drizzle-kit + the relational API pick it up. > **One-file-per-domain is mandatory** (SK.md §1.2 points here). Tables of distinct domains in one file violates the kit convention — what counts as "domain" is a project-level decision. --- ## 2. Audit fields — mandatory on every business table ```ts import { auditFields } from '@/lib/db/helpers/audit-fields'; export const orders = pgTable('orders', { id: uuid('id').primaryKey().defaultRandom(), // ...business columns ...auditFields, }); ``` | Column | Type | Notes | | ------------ | -------------- | -------------- | | `createdAt` | timestamp (TZ) | auto on insert | | `createdBy` | uuid, nullable | user id | | `modifiedAt` | timestamp (TZ) | auto on update | | `modifiedBy` | uuid, nullable | user id | Always set `createdBy` on insert, `modifiedBy` on update — read from the session (the wrapper from [`sk-api`](../sk-api/SKILL.md) injects `userId`): ```ts await db.insert(orders).values({ ...validated, createdBy: userId, modifiedBy: userId, }); await db .update(orders) .set({ ...patch, modifiedBy: userId }) .where(eq(orders.id, id)); ``` > `createdBy`/`modifiedBy` are nullable for seed data and system operations. > FK constraints to `users.id` belong in migration SQL (avoids circular imports at the schema layer). --- ## 3. Soft delete — `softDeleteFields` + `notDeleted()` ```ts import { softDeleteFields, notDeleted } from '@/lib/db/helpers/soft-delete'; export const orders = pgTable('orders', { id: uuid('id').primaryKey().defaultRandom(), ...softDeleteFields, // deletedAt + deletedBy ...auditFields, }); // Read — always filter out soft-deleted const active = await db.select().from(orders).where(notDeleted(orders)); // Soft delete await db.update(orders).set({ deletedAt: new Date(), deletedBy: userId }).where(eq(orders.id, id)); ``` Use soft delete whenever other tables reference this row; otherwise a hard delete is fine. --- ## 4. Dual-ID pattern — `humanId` discipline For rows users reference by a human-readable id (users, orders, invoices, tickets): ```ts export const orders = pgTable('orders', { id: uuid('id').primaryKey().defaultRandom(), // technical (PK, joins, indexes) humanId: text('human_id').notNull().unique(), // human (URLs, breadcrumbs, display) }); ``` The kit ships four helpers in `@/lib/utils/human-id`: | Helper | Role | | -------------------- | ------------------------------------------------------------------------------- | | `generateHumanId` | Format helper — turns a positive integer + prefix into `ORD-2026-0042`. Pure. | | `getNextHumanId` | Atomic next via a Postgres `SEQUENCE` (`nextval`). Lock-free under concurrency. | | `getNextHumanIdSeq` | Application-level `MAX(trailing-int) + 1`. **Gap-resilient** (delete-safe). | | `withHumanIdRetry()` | Retry-on-`23505` wrapper for races. Orthogonal to the two resolvers. | ### 4.1 Mandatory rules ``` ✅ OBLIGATORIO: Toda generación de humanId pasa por `getNextHumanId` (PG SEQUENCE) o `getNextHumanIdSeq` (MAX + 1) — nunca `count(*) + 1`. ✅ OBLIGATORIO: Toda generación pasa además por `withHumanIdRetry()`. Las dos protecciones son ortogonales: SEQUENCE/MAX cubren gaps, el retry cubre races. ✅ OBLIGATORIO: Si el código corre dentro de un `tx` recibido del caller → envolver el insert en `tx.transaction(async sp => ...)` (SAVEPOINT) ANTES del retry. Sin esto, un 23505 aborta el tx padre y el retry falla con "current transaction is aborted". ❌ PROHIBIDO: `generateHumanId(count + 1, …)` — colisiona en cuanto se borra una fila intermedia. `withHumanIdRetry()` no rescata este caso. ❌ PROHIBIDO: Omitir `withHumanIdRetry()` en mutaciones que generan humanId. ❌ PROHIBIDO: Usar `withHumanIdRetry()` dentro de un parent `tx` SIN savepoint. ❌ PROHIBIDO: Usar `\d`, `\w`, `\s` o cualquier escape con backslash en regex literales dentro de `sql\`...\`` — Drizzle lee el COOKED del template literal y JS colapsa `\d` → `d`. Usar siempre POSIX char classes literales (`[0-9]`, `[A-Za-z]`, `[[:alnum:]]`). ``` ### 4.2 Pattern A — PG SEQUENCE (preferred for high-throughput) Add one sequence per dual-ID entity in a migration: ```sql -- src/lib/db/migrations/XXXX_add_order_human_id_seq.sql CREATE SEQUENCE IF NOT EXISTS order_human_id_seq START 1; ``` Then in the mutation: ```ts import { generateHumanId, getNextHumanId, withHumanIdRetry, HUMAN_ID_PREFIXES, } from '@/lib/utils/human-id'; const created = await withHumanIdRetry(() => db.transaction(async (tx) => { const humanId = await getNextHumanId(tx, 'order_human_id_seq', { prefix: HUMAN_ID_PREFIXES.ORDER, }); const [row] = await tx .insert(orders) .values({ ...data, humanId, createdBy: userId, modifiedBy: userId }) .returning(); return row; }) ); ``` The users table ships with `user_human_id_seq` created in migration 0003 / `seed.ts`. ### 4.3 Pattern B — Application-level MAX + 1 (gap-resilient, no migration needed) When you cannot or do not want to ship a PG `SEQUENCE` per series (e.g. shared series, late addition to an existing table, low-throughput entity), use the MAX-based helper: ```ts // Helper opens its own tx — withHumanIdRetry wraps the WHOLE transaction so // each retry starts fresh. const created = await withHumanIdRetry(() => db.transaction(async (tx) => { const seq = await getNextHumanIdSeq(tx, orders, orders.humanId); const humanId = generateHumanId(seq, { prefix: HUMAN_ID_PREFIXES.ORDER }); const [row] = await tx .insert(orders) .values({ ...data, humanId, createdBy: userId, modifiedBy: userId }) .returning(); return row; }) ); ``` Per-type partitioning (e.g. movements where each `type` has its own series): ```ts const seq = await getNextHumanIdSeq(tx, movements, movements.humanId, eq(movements.type, 'APO')); ``` ### 4.4 Pattern C — Inside a parent `tx` (mandatory savepoint) If the helper **receives** a `tx` from the caller (instead of opening its own), it cannot wrap the whole tx in `withHumanIdRetry()` — a `23505` aborts the parent tx and every retry fails with "current transaction is aborted". Open a SAVEPOINT via nested `tx.transaction(...)`: ```ts async function appendMovementWithinTx( tx: PgTransaction, data: NewMovement, userId: string ) { return withHumanIdRetry(() => tx.transaction(async (sp) => { // ← nested SAVEPOINT const seq = await getNextHumanIdSeq( sp, movements, movements.humanId, eq(movements.type, data.type) ); const humanId = generateHumanId(seq, { prefix: data.type }); const [row] = await sp .insert(movements) .values({ ...data, humanId, createdBy: userId, modifiedBy: userId }) .returning(); return row; }) ); } ``` A 23505 inside `sp` rolls back ONLY the savepoint; the parent tx survives, and the next retry opens a new savepoint. > See [`sk-api`](../sk-api/SKILL.md) §6 for the wrapper-integrated version that bubbles the error as an `ActionResult`. Skip dual-ID for settings tables, audit logs, or anything the user never sees. --- ## 5. Pagination — `@/lib/db/utils/pagination` helpers Do NOT hand-roll `page * limit` math. The kit ships the canonical building blocks: | Helper | Purpose | | ----------------------- | ----------------------------------------------------------------------- | | `parsePaginationParams` | Safe `page`/`pageSize` from URL search params (clamped bounds) | | `buildPaginationSQL` | Returns `{ limit, offset, safePage, safePageSize }` | | `createCachedCount` | `unstable_cache`-wrapped count with `revalidateTag` invalidation | | `clampPageSize` | Guard against clients requesting huge pages (max `MAX_PAGE_SIZE = 100`) | | `calculateTotalPages` | `Math.ceil` with min 1 | ```ts import { asc, count } from 'drizzle-orm'; import { notDeleted } from '@/lib/db/helpers/soft-delete'; import { parsePaginationParams, buildPaginationSQL, createCachedCount, calculateTotalPages, } from '@/lib/db/utils/pagination'; // Module-level: cached COUNT invalidated via revalidateTag('user-counts') const getUserCount = createCachedCount( async () => { const [{ total }] = await db.select({ total: count() }).from(users); return total; }, { tags: ['user-counts'], revalidate: 300 } ); export async function listUsers(searchParams: Record) { const { page, pageSize } = parsePaginationParams(searchParams); const { offset, safePage, safePageSize } = buildPaginationSQL({ page, pageSize }); const [data, total] = await Promise.all([ db .select() .from(users) .where(notDeleted(users)) .orderBy(asc(users.createdAt)) .limit(safePageSize) .offset(offset), getUserCount(), ]); return { data, total, page: safePage, pageSize: safePageSize, totalPages: calculateTotalPages(total, safePageSize), }; } ``` > After a mutation that invalidates the count, call `revalidateTag('user-counts')` from the Server Action — otherwise the cached total goes stale. --- ## 6. Hard-delete eligibility — `canHardDeleteUser` If an admin needs to permanently delete a user, first verify no downstream audit references exist: ```ts import { canHardDeleteUser } from '@/lib/db/helpers/can-hard-delete'; import { ActionError } from '@/lib/actions/types'; const result = await canHardDeleteUser(userId); if (!result.canDelete) { throw new ActionError(result.reason ?? 'No se puede eliminar: tiene registros asociados'); } await db.delete(users).where(eq(users.id, userId)); ``` The helper counts rows where the target user appears in `createdBy` / `modifiedBy` / `deletedBy` and refuses the hard delete when >0. For other entities, mirror the pattern — a custom helper per table, not a generic scan. --- ## 7. Migration workflow & DB inspection ### Scripts shipped by the kit ```bash pnpm db:generate # generate SQL from schema changes pnpm db:migrate # apply pending migrations pnpm db:studio # open Drizzle Studio (GUI) pnpm db:seed # run src/lib/db/seed.ts (idempotent) pnpm db:seed:admin # seed admin user only pnpm db:query --tables # list tables pnpm db:query --describe users # describe a table's schema pnpm db:query "SELECT id, email FROM users LIMIT 5" pnpm db:query --json "SELECT …" # JSON output (for pipes) ``` ### Rules (per SK.md §1.1 + §1.3) - 🛑 `pnpm db:push` requires explicit consent (SK.md §1.1). Prefer generate → migrate (reversible). If unavoidable, show `--dry-run` output and wait for approval. - 🛑 For any read-only DB polling/inspection always use `pnpm db:query` (SK.md §1.3). The runner: - Blocks writes (`INSERT`/`UPDATE`/`DELETE`/`DROP`/`ALTER`/`TRUNCATE`/`CREATE`/`GRANT`/`REVOKE`) - Loads `.env.local` via the kit pattern - Pools Neon correctly - **Never write ad-hoc `tsx` scripts with `dotenv + @neondatabase/serverless` for one-shot queries.** - For writes, only `pnpm db:generate` + `pnpm db:migrate` — never a one-shot script. ### Workflow 1. Edit `src/lib/db/schema/*.ts` (and re-export from `index.ts` if new file). 2. `pnpm db:generate` → review the generated SQL in `src/lib/db/migrations/`. 3. `pnpm db:migrate` locally. 4. Verify with `pnpm db:query --describe ` if you need to confirm the shape. 5. Commit schema + migration together. 6. CI applies migrations in staging / prod. --- ## 8. Kit-specific anti-patterns | ❌ | ✅ | | -------------------------------------------------- | --------------------------------------------------------------- | | Manual `createdAt/By` + `modifiedAt/By` columns | `...auditFields` helper | | Skipping `auditFields` on a business table | Always spread `...auditFields` (mandatory per §2) | | `updatedAt` (legacy naming) | `modifiedAt` (pairs with `modifiedBy`) | | Hard delete on referenced tables | `softDeleteFields` + `notDeleted()` | | `generateHumanId(count+1, …)` (gap + race bugs) | `getNextHumanId` (SEQUENCE) o `getNextHumanIdSeq` (MAX) (§4) | | `humanId` mutation without `withHumanIdRetry()` | Always wrap in `withHumanIdRetry()` (§4.1) | | `withHumanIdRetry()` directly inside a parent `tx` | Open a SAVEPOINT first via nested `tx.transaction(...)` (§4.4) | | `\d` / `\w` / `\s` inside `sql\`...\`` | POSIX char classes (`[0-9]`, `[A-Za-z]`) — Drizzle reads cooked | | `db:push` without consent | `db:generate` → review SQL → `db:migrate` | | Ad-hoc `tsx` + dotenv to inspect DB | `pnpm db:query "…"` / `--tables` / `--describe` | | Hand-rolled `page * limit` math | `parsePaginationParams` + `buildPaginationSQL` | | Cached count left stale after a write | `revalidateTag(...)` in the Server Action after mutation | | Inventing a new `hasMovements` per table | `canHardDeleteUser` (or analogous typed helper) | | Tables of distinct domains in one schema file | One file per domain (SK.md §1.2) | | Forgetting to re-export new schema file | Add to `src/lib/db/schema/index.ts` | | Hand-deriving Zod without the inferred types | Reference `$inferSelect` / `$inferInsert` types | > This repo does NOT ship `drizzle-zod`. Derive Zod schemas by hand in `@/lib/validations/…` referencing the inferred types — `createInsertSchema` / `createSelectSchema` are not available. --- ## 9. Checklist (kit-specific) **New schema file:** - [ ] Lives under `src/lib/db/schema/.ts` (one file per domain, SK.md §1.2) - [ ] Re-exported from `src/lib/db/schema/index.ts` - [ ] `...auditFields` included on business tables - [ ] `softDeleteFields` if the row can't be hard-deleted - [ ] Enums, if any, live in `src/lib/db/schema/enums.ts` - [ ] `$inferSelect` + `$inferInsert` exported **New mutation:** - [ ] `createdBy` / `modifiedBy` set from the wrapper-injected `userId` - [ ] `notDeleted()` applied on reads where soft delete exists - [ ] `revalidateTag(...)` called after mutations that invalidate `createCachedCount` **New migration:** - [ ] Generated via `pnpm db:generate` - [ ] SQL reviewed before `pnpm db:migrate` - [ ] Schema + migration committed together - [ ] No `db:push` used (or explicit user consent recorded) --- Cross-reference: [`kb-db`](../kb-db/SKILL.md) — portable patterns. [`sk-features-index`](../sk-features-index/SKILL.md) — feature catalog. [`sk-crud-scaffold`](../sk-crud-scaffold/SKILL.md) — orquestador CRUD que compone estos helpers.