Database Review

Analyze database schema design, normalization, and query patterns for PostgreSQL/Drizzle.

Scope

Review schema files and queries for:

1. Normalization Analysis

• First Normal Form (1NF): Atomic values, no repeating groups
• Second Normal Form (2NF): No partial dependencies on composite keys
• Third Normal Form (3NF): No transitive dependencies
• Identify intentional denormalization and validate reasoning

2. Schema Design

• Appropriate data types (varchar lengths, numeric precision)
• Proper use of PostgreSQL enums vs. lookup tables
• UUID vs. serial for primary keys (project uses UUIDs)
• Nullable columns that should have defaults
• Missing NOT NULL constraints
• Proper foreign key relationships

3. Indexing Strategy

• Missing indexes on foreign keys
• Missing indexes on frequently filtered columns
• Missing composite indexes for common query patterns
• Over-indexing (indexes that slow writes without read benefit)
• Missing partial indexes for soft-delete patterns (WHERE deleted_at IS NULL)

4. Soft Delete Consistency

• All core tables should have deletedAt timestamp (per CLAUDE.md)
• Queries consistently filter WHERE deletedAt IS NULL
• Cascade behavior on soft deletes

5. Relationship Patterns

• Verify relations in lib/db/relations.ts match schema
• Many-to-many join table design
• Proper ON DELETE/ON UPDATE behaviors
• Missing relationships that should exist

6. Query Patterns

• N+1 queries in lib/queries/
• Missing eager loading opportunities
• Inefficient joins
• Unbounded queries (missing LIMIT)

7. Migration Safety

• Migrations that could lock tables
• Backwards-incompatible changes
• Missing data migrations for schema changes

8. No Row Level Security (RLS) - CRITICAL

This project NEVER uses RLS. All access control is handled in the application layer.

Flag as 🔴 CRITICAL if found:

• ENABLE ROW LEVEL SECURITY in any migration
• CREATE POLICY statements in migrations
• pgPolicy() calls in lib/db/schema.ts
• Import of pgPolicy from drizzle-orm/pg-core
• Helper functions like is_admin() for RLS

Why RLS is prohibited:

• Application-layer access control is easier to test and debug
• RLS creates hidden complexity and hard-to-trace permission issues
• Supabase RLS requires auth.uid() which couples DB to auth provider
• All data access flows through permission-checked functions in lib/auth/permissions.ts

If RLS is found: Immediately flag and recommend removal. This is a blocking issue.

9. Column Usage Audit

Goal: Prevent storing data that isn't immediately being used. Every column should have a purpose.

Exempt columns (standard audit fields):

• id (primary key)
• createdAt, created_at
• updatedAt, updated_at
• deletedAt, deleted_at
• Foreign key columns (e.g., userId, projectId, clientId)

For each non-exempt column, verify it is:

• Used in a query (SELECT, WHERE, ORDER BY, GROUP BY) in lib/queries/ or lib/data/
• OR displayed in a UI component (check components/ and app/)
• OR used in API responses or server actions

Flags:

• 🔴 UNUSED: Column exists but no evidence of usage anywhere
• 🟡 WRITE-ONLY: Column is written to but never read/displayed
• 🟢 ACTIVE: Column is both written and read/displayed

10. Enum Value Usage Audit

Goal: Ensure all enum values are actively used. Unused enum values create confusion and maintenance burden.

For each PostgreSQL enum type in the schema, verify:

• All values are used in at least one of:

- Query filters (WHERE status = 'VALUE') - UI rendering (status labels, colors, icons) - Business logic (switch statements, conditionals) - API responses or form options

Flags:

• 🔴 UNUSED VALUE: Enum value exists but has no usage
• 🟡 DEPRECATED: Value exists for historical data only (document if intentional)
• 🟢 ACTIVE: Value is actively used in queries and UI

Document findings for each enum:

Enum: taskStatus
Values: [BACKLOG, ON_DECK, IN_PROGRESS, IN_REVIEW, BLOCKED, DONE, ARCHIVED]
Usage Analysis:
- BACKLOG: ✅ Query filter in fetchBacklogTasks(), UI in board column
- ON_DECK: ✅ ...

Output Format

For each finding:

[TYPE: NORMALIZATION|INDEXING|DESIGN|QUERY|MIGRATION|RLS_VIOLATION|UNUSED_COLUMN|UNUSED_ENUM]
Location: table/file reference
Issue: Brief description
Current State: What exists now
Recommendation: How to improve
Migration Required: Yes/No

For column/enum usage audit, include a summary table:

## Column Usage Summary

| Table | Column | Status | Evidence |
|-------|--------|--------|----------|
| tasks | priority | 🟢 ACTIVE | Used in TaskCard, fetchTasks WHERE clause |
| tasks | estimatedHours | 🔴 UNUSED | No usage found |
| leads | budget | 🟡 WRITE-ONLY | Set in intake webhook, never displayed |

## Enum Usage Summary

| Enum | Value | Status | Evidence |
|------|-------|--------|----------|
| taskStatus | BACKLOG | 🟢 ACTIVE | Board column, query filter |
| leadStatus | UNQUALIFIED | 🔴 UNUSED | No UI or query usage found |

Actions

1. Read lib/db/schema.ts and lib/db/relations.ts
2. Check for RLS violations - search for pgPolicy, ENABLE ROW LEVEL SECURITY, CREATE POLICY
3. Analyze recent migrations in drizzle/migrations/
4. Review query patterns in lib/queries/
5. Check for missing indexes using common query patterns
6. Validate soft-delete consistency
7. Column usage audit:

- Extract all column names from schema tables - For each non-exempt column, search for usage in: - lib/queries/ (Drizzle query builders) - lib/data/ (data layer functions) - components/ (UI rendering) - app/ (pages, server actions, API routes) - Flag columns with no evidence of read usage

1. Enum value audit:

- Extract all PostgreSQL enum definitions from schema - For each enum value, search for usage in: - Query filters and conditions - UI components (labels, colors, status badges) - Business logic files - Constants/config files (e.g., lib/projects/task-status.ts) - Flag values with no evidence of usage

Post-Review

Generate:

• Schema diagram recommendations
• Index creation SQL statements
• Migration plan for improvements
• Risk assessment for proposed changes
• Column cleanup recommendations: Migration SQL to drop unused columns
• Enum cleanup recommendations: Migration SQL to remove unused enum values (with data migration plan if values exist in historical data)