Database

Run commands from the repository root.

• pnpm push — push the schema to the dev (pnpm push-dev) and test (pnpm push-test) databases.
• pnpm seed — seed data.
• pnpm run setup — reset, push, and seed (use the local-stack skill first).

Schema changes

Edit packages/db/src/schema.ts, then use the migrations skill. Refresh local state with pnpm run setup. Production applies migrations before rolling out new images, so new code may rely on new columns and backfills at startup.

A new organization column goes into SerializedOrganization's Omit list (packages/db/src/types.ts) when internal, or into the /orgs response schemas when the dashboard shows it; apps/ui derives its type from it. Confirm with a full pnpm build.

Queries

• Use Drizzle's object syntax; reads via db().query.<table>.findMany() / findFirst().
• Columns are camelCase in TypeScript and snake_case in the database (tables use snakeCase.table, so column.name is the snake_case name); raw SQL uses user_id, not userId.
• Usage and analytics read the hourly aggregation tables: project_hourly_stats, project_hourly_model_stats (adds used_model/used_provider), project_hourly_source_stats (adds source), api_key_hourly_stats, api_key_hourly_model_stats, global_model_stats, global_source_stats. Join project → organization for org fields such as billing_email. Query log only for data no aggregate holds (payloads, request_id lookups, individual finish reasons), and say why.
• log can hold billions of rows, takes an insert per request, and holds prompts, completions, and tool payloads. Retention nulls its payload columns but never deletes rows, so an unbounded scan covers the full history.
• Bound every log read by an indexed leading column (project_id, api_key_id, …) and by created_at on both ends. A keyset cursor written as created_at > c OR (created_at = c AND id > i) gives no index lower bound; add created_at >= c. EXPLAIN (ANALYZE, BUFFERS) against data skewed toward one large org, where plans that are cheap for small orgs flip to scans from the start of the index.
• created_at has microsecond precision and a JS Date keeps milliseconds, so a cursor round-tripped through Drizzle re-matches its own row. Keep the cursor as text or compare date_trunc('milliseconds', created_at).
• Select log payload columns only when the feature needs them, and send them outside the platform only on an explicit opt-in.
• The gateway request path reads no log rows at all; hot-path signals come from Redis counters maintained on the write path or small cached rows.
• used_model stores provider/model[:region]. Compare with catalogue ids after split_part(split_part(used_model, '/', 2), ':', 1) (as in stats-calculator.ts), and use the same shape in seeds and fixtures.
• Cost columns are real, and SUM(real) accumulates in float4. Sum as SUM(CAST(col AS DOUBLE PRECISION)); for exact reads SUM(CAST(CAST(col AS DOUBLE PRECISION) AS NUMERIC)). real::numeric rounds to 6 digits.
• Coordinate settings updates by validating state at each write boundary; advisory locks such as pg_advisory_xact_lock are not used (the migration runner's advisory lock is the only one).

Cached client

The cached client stores positional driver rows. Cache keys are namespaced by SCHEMA_CACHE_VERSION (derived from schema.ts) and runMigrations clears them, so a column change needs no manual bump.