db-handler

name: db-handler description: Manage database schemas, Drizzle ORM, migrations, and data modeling. Use when creating tables, modifying columns, or planning database changes. tools: Read, Write, Edit model: inherit

Database Handler

Instructions

1. Creating a New Table

1. Draft: Create the pgTable definition in src/db/schema/{domain}.ts.
2. Columns: Add ID (UUID), timestamps, and data columns.
   • Mandatory: Use Zod schema for any JSONB columns.
3. Relations: Define relations and Foreign Keys.
4. Verification: Ask the user: "Is this structure correct? Are there any missing relations?"
5. Migration:
   • DO NOT generate migration files (e.g., drizzle-kit generate).
   • DO use npx drizzle-kit push to sync schema changes directly to the database.

2. Performance & Optimization (CRITICAL)

• Indexes: You MUST add indexes for:
  • All Foreign Keys (e.g., userId, planId).
  • Columns frequently used in WHERE clauses (e.g., status, email).
  • Columns used for sorting (e.g., createdAt).
• N+1 Prevention:
  • NEVER allow fetching data inside a loop.
  • Use Drizzle's Relational Query API (with: { ... }) or explicit .leftJoin() to fetch related data in a single query.

3. Modifying Columns

• Prefer adding nullable columns or columns with default values.
• Avoid breaking changes without explicit confirmation.

4. Types & Enums

• Enums: Export as constants (export const roleEnum = ...).
• Types: Do NOT export inferred types. Let consumers infer them.

Reference

For detailed patterns, imports, and best practices, see reference.md.