drizzle

# Drizzle ORM Schema Style Guide

> **Adding a Model or Repository?** Ship a sibling test in the same PR — every new
> file under `packages/database/src/models/**` or `src/repositories/**` needs a
> matching `__tests__/<name>.test.ts`. See the **testing** skill
> (`.agents/skills/testing/references/db-model-test.md`) for the `getTestDB()`
> integration pattern, user-isolation tests, the BM25 `describe.skipIf(!isServerDB)`
> guard, and schema gotchas. CI's coverage patch gate won't reliably catch a brand-new
> untested file, so this is on you.

## Configuration

- Config: `drizzle.config.ts`
- Schemas: `packages/database/src/schemas/`
- Migrations: `packages/database/migrations/`
- Dialect: `postgresql` with `strict: true`

## Helper Functions

Location: `packages/database/src/schemas/_helpers.ts`

- `timestamptz(name)`: Timestamp with timezone
- `createdAt()`, `updatedAt()`, `accessedAt()`: Standard timestamp columns
- `timestamps`: Object with all three for easy spread

## Naming Conventions

- **Tables**: Plural snake_case (`users`, `session_groups`)
- **Columns**: snake_case (`user_id`, `created_at`)
- **New tables**: Check nearby existing tables before naming a new one. Preserve
  the established noun family and suffix. For example, if the user-scoped table
  is `user_xxx_logs`, the workspace-scoped counterpart should be
  `workspace_xxx_logs`, not `workspace_xxx_records` or another new synonym.

```typescript
// ✅ Good: follows the existing user/workspace table family.
export const userSignupLogs = pgTable('user_signup_logs', { ... });
export const workspaceSignupLogs = pgTable('workspace_signup_logs', { ... });

// ❌ Bad: introduces a new suffix for the same concept.
export const workspaceSignupRecords = pgTable('workspace_signup_records', { ... });
```

## Column Definitions

### Primary Keys

Do not use auto-incrementing primary keys (`serial`, `bigserial`, generated
identity columns). They create sequence-state problems during cross-database
migrations, restores, and data copy jobs. Prefer text IDs from application
generators (`idGenerator`, `createNanoId`) or `uuid` for internal tables.

Keep `$defaultFn(...)` when a table normally owns ID generation. Callers can
still pass an explicit `id`; the default only runs when the insert omits it. Do
not remove the default just because one flow needs to supply a request-scoped ID.

```typescript
// ✅ Good: app-generated text ID; explicit inserts can still override it.
id: text('id')
  .primaryKey()
  .$defaultFn(() => idGenerator('agents'))
  .notNull(),

// ❌ Bad: sequence state is fragile across DB migrations and restores.
id: serial('id').primaryKey(),
```

ID prefixes make entity types distinguishable. For internal tables, use `uuid`.

### Foreign Keys

```typescript
userId: text('user_id')
  .references(() => users.id, { onDelete: 'cascade' })
  .notNull(),
```

### Timestamps

```typescript
...timestamps,  // Spread from _helpers.ts
```

### Optional and Undefined Values

Do not introduce artificial sentinel strings for missing values, such as
`unknown`, unless the domain already has that explicit state and existing code
uses it consistently. Prefer nullable columns, optional TypeScript fields, or a
separate concrete status enum when the value is genuinely absent.

```typescript
// ✅ Good: absent until the final stage writes a real decision.
export type UserSignupLogFinalDecision = 'allow' | 'block' | 'error';

finalDecision: varchar('final_decision', { length: 32 }).$type<UserSignupLogFinalDecision>(),

// ❌ Bad: invents a new state that callers now need to handle everywhere.
export type UserSignupLogFinalDecision = 'allow' | 'block' | 'error' | 'unknown';

finalDecision: varchar('final_decision', { length: 32 })
  .$type<UserSignupLogFinalDecision>()
  .notNull()
  .default('unknown');
```

### Field Descriptions

For columns whose meaning is not obvious from the name alone, add JSDoc on the
schema field. Include a concrete example when it clarifies the stored value or
the lifecycle moment that writes it. This is especially important for external
IDs, lifecycle statuses, denormalized snapshots, JSONB signals, and fields whose
name could mean either a request ID or a persisted row ID.

```typescript
// ✅ Good: explain the table's business object first, then only document
// non-obvious lifecycle or risk-control fields.
/**
 * User signup logs - one row per signup flow, collecting stage-level
 * risk-control decisions before and after the auth provider creates a user.
 */
export const userSignupLogs = pgTable('user_signup_logs', {
  /** Final signup outcome reason, for example user_created, llm_block, or guard_error */
  finalReason: text('final_reason'),

  /** Aggregated risk level derived from stage decisions, for example block -> high */
  riskLevel: varchar('risk_level', { length: 16 }).$type<UserSignupLogRiskLevel>(),

  /** Ordered stage-level decisions and metadata grouped by signup review stage */
  stageResults: jsonb('stage_results').$type<UserSignupLogStageResults>(),
});

// ❌ Bad: comments restate obvious column names without adding domain meaning.
/** User email */
email: text('email'),
```

### JSONB Types

Avoid `Record<string, unknown>` or similarly loose JSONB types for schema
columns. Define a concrete interface that describes the expected JSON shape, even
when most properties are optional. This keeps callers, migrations, and review
queries aligned on the same data contract.

```typescript
interface UserSignupLogMetadata {
  payloadPath?: string;
  requestPath?: string;
}

metadata: jsonb('metadata').$type<UserSignupLogMetadata>(),
```

```typescript
// ❌ Bad: hides the contract and makes downstream access untyped.
metadata: jsonb('metadata').$type<Record<string, unknown>>(),
```

### Indexes

```typescript
// Return array (object style deprecated)
(t) => [uniqueIndex('client_id_user_id_unique').on(t.clientId, t.userId)],
```

## Type Inference

```typescript
export const insertAgentSchema = createInsertSchema