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`.

Do not use composite primary keys on new tables. Give every table a single-column
surrogate PK and carry business uniqueness in a `uniqueIndex` instead. PK columns
cannot be nullable, so when the uniqueness scope later grows by a nullable
dimension the composite PK must be torn down and rebuilt — exactly what happened
when `ai_providers` / `ai_models` were workspace-scoped (migration 0110 replaced
their composite PKs with a surrogate `_id` plus partial unique indexes). A unique
index still works as the arbiter for `onConflictDoUpdate` upserts.

```typescript
// ✅ Good: surrogate PK; uniqueness scope can evolve without a PK rebuild.
export const workspaceUserSettings = pgTable(
  'workspace_user_settings',
  {
    id: uuid('id').defaultRandom().notNull().primaryKey(),
    workspaceId: text('workspace_id').references(() => workspaces.id, { onDelete: 'cascade' }).notNull(),
    userId: text('user_id').references(() => users.id, { onDelete: 'cascade' }).notNull(),
    ...timestamps,
  },
  (t) => [uniqueIndex('workspace_user_settings_workspace_id_user_id_unique').on(t.workspaceId, t.userId)],
);

// ❌ Bad: locked to exactly these columns; adding a nullable scope column
// (workspaceId, deviceId, …) later forces a full PK rebuild migration.
(t) => [primaryKey({ columns: [t.workspaceId, t.userId] })],
```

Existing composite PKs are legacy — leave them alone unless they block a scope
change, then migrate them the 0110 way.

### 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');
```

### Database Enums

Default to **not** using PostgreSQL/Drizzle `pgEnum`. Database enums are
expensive to evolve safely: adding members needs migrations, removing or
renaming members is awkward, and deployment order becomes more fragile.

For product/business states, use `text()` or `varchar()` with a TypeScript value
type via `$type<...>()`. Keep those TS-only value types in the domain/shared type
module, then import them into the schema. For cloud DB schemas, that usually
means `cloudDB/types.ts`.

Do not copy existing DB enums as a pattern. Treat them as legacy or explicitly
reviewed exceptions. If a new `pgEnum` seems necessary, stop and justify why the
value set is effectively immutable and why the migration cost is acce