Database & Migrations

---
globs: drizzle/**/*, sql/**/*, packages/core/src/schemas/**/*, packages/core/src/models/**/*
---

## Database & Migrations

- **Role**: Provide a consistent schema and migration history that works in both:
  - PostgreSQL with pgvector (server mode).
  - PGlite (browser-only mode).

### Schema Definition

- Treat the TypeScript schema in `packages/core/src/schemas` / `models` as the **source of truth**.
- Ensure any schema change is reflected consistently in:
  - TS schema/models.
  - Generated Drizzle migrations under `drizzle/`.
  - Any raw SQL helpers under `sql/` if present.
- Prefer Drizzle's typed query APIs over raw SQL to keep Postgres and PGlite behavior aligned.

### Migrations

- Use `drizzle-kit` (`pnpm run db:generate`) to generate new migrations; avoid hand-writing large SQL files.
- For production-safe changes:
  - Make migrations forward-only and additive when possible.
  - Avoid destructive operations without a clear, tested migration path.
- If a generated migration needs manual tweaks:
  - Keep custom SQL focused and well-commented.
  - Verify both Postgres and PGlite behavior where applicable.

### Performance & Indexing

- For vector search:
  - Ensure vector dimensions match the configured embedding model.
  - Use pgvector indexes where needed, and make them explicit in migrations.
- For analytical queries (stats, charts):
  - Prefer precalculated or indexed aggregates over complex ad‑hoc joins on hot paths.