Drizzle Database

Drizzle Database

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

Table Definition

Tables use pgTable with sharedColumns spread for consistent id, createdAt, updatedAt. For a real workspace-scoped table to copy, read packages/database/src/schema/minigame.ts or any sibling in schema/; the shape is:

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:

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:

export * from "./<domain>"

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

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:

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):

CRITICAL — relations/index.ts requires TWO edits:

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

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)

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)

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)

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.

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