DrizzleCAUTION

🤯 LobeHub is your Chief Agent Operator, organizing your agents into 7×24 operations by hiring, scheduling, and reporting on your entire AI team.

The instruction file, verbatim from the audited commit — this is the text the model reads, and the surface the audit's instruction layer examines. Quoted here so you can judge it without cloning anything.

---
name: drizzle
description: 'Use for Drizzle schemas and queries: tables, indexes, relations, joins and inferred types. Rollout belongs to db-migrations.'
user-invocable: false
---

# 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 (names are
// illustrative — swap `widget` for the real noun).
export const userWidgetLogs = pgTable('user_widget_logs', { ... });
export const workspaceWidgetLogs = pgTable('workspace_widget_logs', { ... });

// ❌ Bad: introduces a new suffix for the same concept.
export const workspaceWidgetRecords = pgTable('workspace_widget_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 (migrations 0110–0111 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–0111 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 a custom-execution attempt actually runs
// (packages/database/src/schemas/agentIntervention.ts).
export const AGENT_INTERVENTION_CUSTOM_EXECUTION_STATES = ['pending', 'executing', 'completed'] as const;
export type AgentInterventionCustomExecutionState =
  (typeof AGENT_INTERVENTION_CUSTOM_EXECUTION_STATES)[number];

customExecutionState: text('custom_execution_state').$type<AgentInterventionCustomExecutionState>(),

// ❌ Bad: invents a new state that callers now need to handle everywhere.
export type AgentInterventionCustomExecutionState = 'pending' | 'executing' | 'completed' | 'unknown';

customExecutionState: text('custom_execution_state')
  .$type<AgentInterventionCustomExecutionState>()
  .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 fra

CAUTIONgrade B · trust 89/100 Install with care. The audit found things worth knowing before you trust its output.

What the source does

Filesystem

none-observed

Network

none-observed

Shell

none-observed

Dependencies

pinned

Secrets in source

none-found

Findings (4)

.claude/skills

.codex/skills

.cursor/skills

.gemini/skills

Gates applied: no_behavioural_pass.

[![ModelsAtlas audit: Drizzle](https://modelsatlas.com/skills/lobehub/drizzle/badge.svg)](https://modelsatlas.com/skills/lobehub/drizzle/)

Not your listing, or something out of date? Claim or update your listing.