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.

// ✅ 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