The database

Everything that is not git lives in Postgres, defined once in packages/db/src/schema/. The API and the Go delivery service both read it; only packages/db defines or migrates it.

The schema files

Conventions

Ids are prefixed text.

id: text().primaryKey().$defaultFn(() => `pr_${createId()}`),

The prefix tells you what an id is when you see it in a log. GitHub compatibility needs integers, so a few tables also carry a numeric_id identity column, used only by that layer.

snake_case in the database, camelCase in TypeScript (ADR 0030). Both the Drizzle client and drizzle-kit run with casing: "snake_case", so repositoryId is the column repository_id. Raw SQL never spells a column by hand; it interpolates the column object (${schema.issue.number}), so a rename cannot leave a stale string behind.

Timestamps are timestamptz, always (ADR 0018). A bare timestamp stores a wall clock whose meaning depends on the session's timezone. Ghost lost a day to dates shifted by three hours because the database session ran in Istanbul time and the API in UTC. Use timestamp({ withTimezone: true }).

Constraints over code. Uniqueness that matters is an index: one open pull request per branch pair is a partial unique index (where state = 'open'), and numbering races are settled by a unique index plus retry rather than a lock.

Migrations

# 1. edit packages/db/src/schema/*.ts
bun run db:generate          # at the repository root: drizzle-kit writes SQL + a snapshot
bun run db:migrate           # apply locally

Never hand-edit generated SQL or write a migration from scratch; regenerate from the schema. Review the generated SQL anyway: drizzle-kit asks whether a changed column is a rename or a drop-and-create, and choosing wrong loses data. A type conversion of timestamps needs an explicit USING … AT TIME ZONE (see ADR 0018).

In production, packages/db/src/migrate.ts applies migrations (retrying, each batch in one transaction) and then runs seedBuiltInRows from packages/db/src/seed.ts, which inserts rows that must exist but are defined in code, like the built-in gh OAuth app. The ghost-importer user is created by a migration instead, with a hyphenated name Better Auth's validator refuses, so no person can claim it.

Querying

The API injects the Drizzle client with @Inject(DATABASE) private readonly db: Database. Two habits from the codebase:

• Select the shape you return. No to<Format> mappers (code standard 2). The only sanctioned exception is the GitHub compatibility layer, and each mapper there carries a one-line comment saying why.
• Keyset pagination with apps/api/src/lib/db/keyset.ts › keyset, never OFFSET, except where GitHub's REST paging forces page numbers.