---
name: drizzle-database
description: >-
  Work with Drizzle ORM database schema, migrations, relations, and queries in
  PostgreSQL. Use when creating tables, modifying schema, writing migrations,
  defining relations, or querying the database.
---

# Drizzle Database

Package: `packages/database` (`@chatbotx.io/database`)

## Table Definition

Tables use `pgTable` with `sharedColumns` spread for consistent `id`, `createdAt`, `updatedAt`:

```typescript
import { pgTable, text, index, uniqueIndex } from "drizzle-orm/pg-core"
import { sharedColumns, bigintAsString, timestampConfig } from "../partials/shared"
import { otherModel } from "./other"

export const myModel = pgTable(
  "MyModel",
  {
    ...sharedColumns,
    name: text().notNull(),
    description: text(),
    workspaceId: bigintAsString()
      .notNull()
      .references(() => workspaceModel.id, {
        onDelete: "cascade",
        onUpdate: "cascade",
      }),
  },
  (table) => [
    index("MyModel_workspaceId_idx").using(
      "btree",
      table.workspaceId.asc().nullsLast(),
    ),
    uniqueIndex("MyModel_name_workspaceId_key").on(
      table.name,
      table.workspaceId,
    ),
  ],
)
```

### Key Conventions

- Table name: `PascalCase` string matching the SQL table name
- Export name: `camelCaseModel` (e.g. `contactModel`, `workspaceModel`)
- `sharedColumns` provides: `id` (bigint as string, auto-generated), `createdAt`, `updatedAt`
- `bigintAsString()` custom type: stores `bigint` in DB, exposes as `string` in app
- FK pattern: `.references(() => otherModel.id, { onDelete, onUpdate })`
- Index naming: `TableName_columnName_idx` or `TableName_column_key` for unique

### Enums

Any column whose value is constrained to a fixed set of strings **must** use `pgEnum`, not `text`. This enforces the constraint at the database level and provides TypeScript types automatically.

**Step 1** — define the Zod enum in `src/partials/<domain>.ts`:

```typescript
import z from "zod"

export const myStatusTypes = z.enum(["active", "inactive", "pending"])
export type MyStatusType = z.infer<typeof myStatusTypes>
```

**Step 2** — export it from `src/partials/index.ts`:

```typescript
export * from "./<domain>"
```

**Step 3** — create the `pgEnum` and use it in the table schema:

```typescript
import { pgEnum, pgTable } from "drizzle-orm/pg-core"
import { myStatusTypes } from "../partials/<domain>"

export const myStatus = pgEnum(
  "myStatus",
  myStatusTypes.options as [string, ...string[]],
)

export const myModel = pgTable("MyModel", {
  ...sharedColumns,
  status: myStatus().default("active").notNull(),
})
```

The enum name passed to `pgEnum` becomes the PostgreSQL enum type name — use `camelCase` matching the column name.

## Relations

Define in `src/relations/<domain>.ts` using `defineRelationsPart`:

```typescript
import { defineRelationsPart } from "drizzle-orm"
import * as schema from "../schema"

export const myModelRelations = defineRelationsPart(schema, (r) => ({
  myModel: {
    workspace: r.one.workspaceModel({
      from: r.myModel.workspaceId,
      to: r.workspaceModel.id,
    }),
    items: r.many.myItemModel({
      from: r.myModel.id,
      to: r.myItemModel.myModelId,
    }),
  },
}))
```

Then add the export to `src/relations/index.ts`.

### Relation Types

- `r.one.targetModel({ from, to })` — belongs-to
- `r.many.targetModel({ from, to })` — has-many
- `r.one.through(r.junctionModel.fk)` — many-to-many via junction

## Migration Workflow

1. **Modify schema** in `src/schema/` (and `src/relations/` if needed)
2. **Generate migration:** `pnpm --filter database make:migration <descriptive_name>`
3. **Apply migration:** `pnpm --filter database db:migrate`
4. **Inspect:** `pnpm --filter database db:studio`

Migrations output to `packages/database/drizzle/<timestamp>_<name>/migration.sql`.

## Schema Registration

After creating a new table, update **3 files** (do all in one batch):

| # | File | Edit |
|---|------|------|
| 1 | `src/schema/index.ts` | `export * from "./<file>"` |
| 2 | `src/types.ts` | `export type MyModel = typeof schema.myModel.$inferSelect` |
| 3 | `src/relations/index.ts` | **TWO** edits: import at top + spread in `relations` object |

### CRITICAL — `relations/index.ts` requires TWO edits:

```typescript
// 1. Add import at top of file (near other imports)
import { myModelRelations } from "./<file>"

// 2. Add spread inside the relations object
export const relations = {
  ...existingRelations,
  ...myModelRelations,  // ← add this
}
```

After editing, always **read back the file** to verify both the import line AND the spread exist. It is very common for one to be added but not the other.

### Enum Registration (channel/integration types)

When adding a new channel or integration type:

| File | Edit |
|------|------|
| `src/partials/channel.ts` | Add value to `channelTypes` z.enum |
| `src/partials/integration.ts` | Add value to `integrationTypes` z.enum |

**CRITICAL cascade:** Adding a value to `channelTypes` causes compile errors in every `Record<ChannelType, ...>` that doesn't include the new key. Always grep `Record<ChannelType` across the codebase and fix ALL hits.

## Query Patterns

**Important:** Direct `db` usage is only allowed inside `packages/database/src/repositories/` and `packages/business/src/`. App-layer code (`apps/`, `integrations/`) must call a service or repository instead. See `.agents/rules/data-access.md`.

### Relational Queries (inside a service or repository)

```typescript
import { db } from "@chatbotx.io/database/client"

const items = await db.query.myModel.findMany({
  where: { workspaceId },
  with: { workspace: true },
  columns: { id: true, name: true },
})

const item = await db.query.myModel.findFirst({
  where: { id: itemId, workspaceId },
})
```

### SQL Builder (inside a service or repository)

```typescript
import { db, eq, and, inArray } from "@chatbotx.io/database/client"
import { myModel } from "@chatbotx.io/database/schema"

await db
  .update(myModel)
  .set({ name: "new name" })
  .where(and(eq(myModel.id, id), eq(myModel.workspaceId, workspaceId)))

await db.insert(myModel).values({ name, workspaceId })
```

### Helpers (inside a service or repository)

```typescript
import { findOrFail } from "@chatbotx.io/database/client"
import { myModel } from "@chatbotx.io/database/schema"

const item = await findOrFail({ table: myModel, where: { id } })
```

## Soft-delete convention

`Workspace.scheduledDeletionAt` is the repository's soft-delete convention:
use a nullable timestamp with an index. Scheduling sets it to `now + 24h`,
cancellation clears it, and the workspace row remains live during the grace
window. The hourly purge cron then hard-deletes due rows, allowing the normal
foreign-key cascade to remove children. `UserQuota.channelsTornDownAt` is the
sibling marker for one-shot trial-expiry channel teardown; it is not a second
soft-delete state.

## Transactions

**Rule:** Use `db.transaction()` whenever an action performs 2 or more write operations (INSERT, UPDATE, DELETE) so all succeed or fail together.

```typescript
import { db } from "@chatbotx.io/database/client"

await db.transaction(async (tx) => {
  const product = await productService.create({ data, tx })

  await Promise.all([
    productVariantService.createBulk({ productId: product.id, variants, tx }),
    productAddonService.createBulk({ productId: product.id, addons, tx }),
  ])
})
```

- Pass `tx` down to every service method inside the callback — never mix `tx` and bare `db` within the same transaction.
- Services accept `tx?: DatabaseClient` as an optional parameter (defaults to `db`), so they work both inside and outside a transaction.
- Return values from the transaction callback are returned by `db.transaction()`.

## Imports Cheatsheet

| What | Import from |
|------|-------------|
| `db`, `eq`, `and`, `inArray`, etc. | `@chatbotx.io/database/client` |
| Table models | `@chatbotx.io/database/schema` |
| TypeScript types | `@chatbotx.io/database/types` |
| Partials, Zod enums | `@chatbotx.io/database/partials` |
| `sharedColumns`, `bigintAsString` | `../partials/shared` (within package) |