db-handler

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.
3. Mandatory: Use Zod schema for any JSONB columns.
4. Relations: Define relations and Foreign Keys.
5. Verification: Ask the user: "Is this structure correct? Are there any missing relations?"
6. Migration:
7. DO NOT generate migration files (e.g., drizzle-kit generate).
8. 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](reference.md).