/drizzle

name: drizzle
description: Use this skill when writing or modifying Drizzle ORM schemas, queries, or migrations in this repo — specifically the `@internal/dashboard-agent-db` package (the dashboard agent's conversation datastore). Covers pg-core schema definition, the postgres-js driver, drizzle-kit migrations, and this repo's conventions: a dedicated Postgres schema, foreign-key-free cross-database design, pooler-safe connections, and the access-pattern query layer. Drizzle is NOT the main database — that's Prisma.
allowed-tools: Read, Write, Edit, Glob, Grep, Bash

Drizzle ORM (this repo)

Drizzle is used in exactly one place: **`internal-packages/dashboard-agent-db`** (`@internal/dashboard-agent-db`), the in-dashboard agent's conversation store. Everything else in the monorepo is **Prisma** (`@trigger.dev/database`). Keep them separate.

Pinned versions: **`drizzle-orm` ^0.45**, **`drizzle-kit` ^0.31** (dev), **`postgres` ^3.4** (postgres.js driver). drizzle-orm and drizzle-kit are intentionally on different version lines — 0.31.x is the correct companion for 0.45.x, there is no peer dependency between them.

Critical rules

1. **Drizzle is only the agent's own datastore.** The agent (and its task bundle) must have **no access to the main Prisma database or ClickHouse**. Never import the Prisma client into the agent task or into `@internal/dashboard-agent-db`. Main data is reached via the API, not Drizzle. 2. **Foreign-key-free.** In cloud this DB is a *separate* PlanetScale database, so it can't FK into the main DB. Reference main entities (`organizationId`, `userId`, …) **by id only — never `.references()`**. Joins happen in app code; tenant scoping is enforced in the query layer. 3. **One dedicated Postgres schema.** All tables live under `pgSchema("trigger_dashboard_agent")` so they're schema-qualified and isolated from Prisma's `public` schema (this is what makes the OSS single-database fallback safe). 4. **Pooler-safe connections.** Connections go through a transaction-mode pooler (PlanetScale / PgBouncer-style), so postgres.js must run with **`prepare: false`** — prepared statements don't survive a connection being handed to another client between checkouts. 5. **Node16 module resolution.** Relative imports need explicit **`.js`** extensions (`import { chats } from "./schema.js"`), even though the source is `.ts`. 6. **Scope every user query.** All queries that touch user data go through `src/queries.ts` and are scoped by `organizationId` / `userId`, so callers can't forget the `where`. Don't write ad-hoc cross-tenant queries elsewhere.

Package layout

internal-packages/dashboard-agent-db/
  drizzle.config.ts      # drizzle-kit config (schema path, out dir, schemaFilter)
  drizzle/               # generated migrations (committed)
  src/
    schema.ts            # pgSchema + table definitions
    client.ts            # createDashboardAgentDb() — postgres.js + drizzle
    queries.ts           # the access-pattern layer (org/user-scoped)
    index.ts             # barrel: re-exports schema, client, queries

`package.json` points `main`/`types` at `./src/index.ts` (consumed as source, no build step) — same as other simple internal packages.

Schema (pg-core)

Use `pgSchema(...).table(...)`, not the bare `pgTable`, so tables land in the dedicated schema. ([schemas](https://orm.drizzle.team/docs/schemas), [pg column types](https://orm.drizzle.team/docs/column-types/pg), [indexes](https://orm.drizzle.team/docs/indexes-constraints))

import { sql } from "drizzle-orm";
import { index, jsonb, pgSchema, text, timestamp } from "drizzle-orm/pg-core";

export const dashboardAgentSchema = pgSchema("trigger_dashboard_agent");

export const chats = dashboardAgentSchema.table(
  "chats",
  {
    id: text("id").primaryKey(),
    organizationId: text("organization_id").notNull(), // FK-free: id only, no .references()
    userId: text("user_id").notNull(),
    title: text("title").notNull().default("New chat"),
    // JSONB with a typed view; .default([]) / .default({}) emit '[]'::jsonb / '{}'::jsonb
    messages: jsonb("messages").$type<unknown[]>().notNull().default([]),
    metadata: jsonb("metadata").$type<Record<string, unknown>>().notNull().default({}),
    deletedAt: timestamp("deleted_at", { withTimezone: true }), // soft delete
    lastMessageAt: timestamp("last_message_at", { withTimezone: true }),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
  },
  // Extra config returns an ARRAY in drizzle-orm 0.36+ (not an object).
  (t) => [
    // Partial + ordered composite index. `.desc()` on the column, `.where(sql`...`)` for partial.
    index("chats_org_user_last_msg_idx")
      .on(t.organizationId, t.userId, t.lastMessageAt.desc())
      .where(sql`${t.deletedAt} is null`),
  ]
);

// Inferred row types for the query layer + consumers.
export type Chat = typeof chats.$inferSelect;
export type NewChat = typeof chats.$inferInsert;

Notes:

• `timestamp(..., { withTimezone: true })` → `timestamp with time zone`. Use `.defaultNow()` for `DEFAULT now()`.
• For a "newest first, nulls last" sort the partial index uses `.desc()`; the *query* uses raw `sql` for `NULLS LAST` (see below).
• Don't add `.references()` — see critical rule 2.

Client (postgres.js + drizzle)

([connect overview](https://orm.drizzle.team/docs/connect-overview)) One small pool, `prepare: false`. In the agent task create it once in `onBoot` (per-process); in the webapp wrap it in the `singleton(...)` helper.

import { drizzle, type PostgresJsDatabase } from "drizzle-orm/postgres-js";
import postgres, { type Sql } from "postgres";
import * as schema from "./schema.js";

export type DashboardAgentDb = PostgresJsDatabase<typeof schema>;

export function createDashboardAgentDb(connectionString: string, opts: { max?: number } = {}) {
  const sql: Sql = postgres(connectionString, {
    max: opts.max ?? 5,        // small — the pooler does the