Imported from jackmaxwil/alfred (packages/db/AGENTS.md). Install upstream with npx skills add jackmaxwil/alfred --skill db. Copyright stays with the author.

Database Rules

1. Source of truth. Migrations in packages/db/src/migrations define the schema. Never edit generated SQL directly in production environments—add new migrations, and mirror new features (e.g., eval definitions/runs/scores) with dedicated SQL files.
2. Vector support. Local Postgres runs via pgvector/pgvector:pg16. Keep CREATE EXTENSION IF NOT EXISTS vector in the earliest migration and verify with \dx. When defining columns, import vector from drizzle-orm/pg-core (not drizzle-orm-pgvector/pg) to avoid SSR bundling issues in the web app.
3. Idempotent migrations. Wrap DDL in IF NOT EXISTS / IF EXISTS when safe. Non-idempotent operations must document irreversible effects.
4. Index discipline. Composite indexes should match the repo query predicates. When adding new queries, expand 0008_indexes.sql or subsequent migrations accordingly. Eval tables must index (def_id, dataset_id, started_at) for run listings and (run_id, point_id, scorer) for score lookups, as seen in 0012_evals.sql. Use partial indexes (WHERE column IS NOT NULL) to reduce index size for sparse columns.
5. Migration runner. Use packages/db/scripts/migrate.ts everywhere (CI, local dev). It records applied migrations in _migrations.
6. Schema sync. Keep Drizzle schema files (packages/db/src/schema/*.ts) aligned with migrations. Vector dimensions must use EMBEDDING_DIM from @alfred/embed (single source of truth). When changing vector dimensions, drop indexes before ALTER COLUMN TYPE, recreate with IF NOT EXISTS, and document that existing embeddings become NULL.
7. Testing. Write Vitest suites under packages/db/test that spin up an isolated database schema and assert repo behaviour (notes, reminders, timers, eval runs/scores, etc.).
8. Laminar correlation. Columns like laminar_eval_id belong in the primary run table to enable dual-write correlation. Always backfill with ALTER TABLE ... ADD COLUMN IF NOT EXISTS migrations so replays remain idempotent.
9. Transactions. Use db.transaction() for multi-step operations that must be atomic. Transactions automatically rollback on error. Use for operations that must succeed or fail together. PostgreSQL reserves a dedicated connection from the pool—keep transactions short to avoid connection exhaustion.
10. Batch operations. Use db.batch() for multiple independent queries (Drizzle batch API). Batch operations execute sequentially in a single round-trip. Use for independent queries that don't require atomicity.
11. Savepoints. Use savepoints for partial rollbacks within transactions. Call tx.savepoint() inside a transaction to create a nested transaction that can rollback independently while the outer transaction continues.
12. Query performance. All repo queries must complete in <10ms (p99). Instrument with metrics before optimizing.
13. Connection pooling. PostgreSQL transactions reserve connections. Avoid long-running transactions to prevent connection exhaustion.
14. Bulk updates. Prefer batch updates with Promise.all + chunks (size 10-50) over db.transaction or sequential loops for high-volume writes. Use UPDATE ... FROM (VALUES ...) for massive updates if possible.

Drizzle Query Patterns

Core Principle

Use Drizzle ORM's type-safe query builder consistently. Leverage TypeScript inference, optimize with indexes, and avoid anti-patterns that reduce type safety or performance.

Rules

1. Type-safe queries. Always use Drizzle's query builder (db.select().from(table).where(...)), never raw SQL (db.execute(sql...)).

2. Query inference. Use typeof table.$inferSelect and typeof table.$inferInsert for types.

3. Index usage. Match query predicates to composite indexes. Use and()/or() for compound conditions.

4. Batch operations. Use db.batch([...]) for multiple independent queries instead of loops.

5. Transactions. Use db.transaction(async (tx) => {...}) for atomic operations. Keep transactions short.

6. Returning clauses. Use .returning() to get inserted/updated rows instead of separate SELECT queries.

7. JSONB handling. Use as any for JSONB fields (Drizzle limitation). Document this pattern in code comments.

8. Safe tsquery generation. When constructing tsquery for search, always use plainto_tsquery('english', ...) for user input or sql.join(..., sql || ) for combining queries. Never string-template raw variables into to_tsquery without sanitization.

9. Performance. All queries must complete in <10ms (p99). Use indexes for all WHERE clauses. Prefer batch operations over loops. Keep transactions short (<100ms).

10. Bulk updates. For bulk updates of the same column (e.g., confidence decay), prefer single SQL UPDATE ... FROM (VALUES ...) statement over Promise.all loops. This reduces DB roundtrips and improves performance.

11. SQL-level JSON filtering. When filtering rows by JSONB properties, use SQL-level filtering (sql\json_extract(column, '$.path') LIKE '%pattern%'``) instead of fetching all rows and filtering in memory. This reduces data transfer and improves performance.

Migration Guide

1. Create new files using the next sequential number (0011_name.sql). Keep names single-word snake case if unavoidable (e.g. vectorindex).
2. Structure. Place DDL statements in dependency order: extensions → tables → indexes → constraints → seeds (if any).
3. Transaction boundaries. The migration runner wraps each file in a transaction. Avoid statements that implicitly break transactions (e.g. CREATE INDEX CONCURRENTLY).
4. Rollback notes. Add comments describing manual rollback steps when dropping columns or performing destructive operations.
5. Testing. After authoring a migration, run bun run db:migrate against a fresh database and ensure _migrations count matches file count.

Schema Rules

1. Single source. Define tables with Drizzle schema builders in packages/db/src/schema. Keep column names aligned with migration SQL.
2. Timestamps. Use timestamp("created_at", { withTimezone: true }).defaultNow() style helpers. Avoid relying on application clocks for created/updated fields.
3. Vector columns. Use the pgvector helper for embedding columns. Always document the expected dimensionality in comments.
4. Foreign keys. Declare relationships explicitly so we can leverage Drizzle relations when needed. Name constraints <table>_<column>_fkey.
5. Enums. Prefer Postgres enums defined in migrations and referenced via Drizzle pgEnum. Avoid TypeScript-only enums for persisted values.

SQLite Fallback Schema

1. SQLite-default UUIDs. SQLite schema must not use Postgres-only functions like gen_random_uuid(); use SQLite-compatible defaults.
2. Default expressions. SQLite DEFAULT expressions that call functions must be wrapped in parentheses.
3. Normalization layer. Any SQLite schema normalizer must only rewrite known Postgres-only functions and must produce valid SQLite SQL.
4. Drift-catcher test. Add a sqlite-only repo roundtrip test for new tables so schema errors fail immediately.