Schema Design

Purpose

Design relational database schemas with normalization trade-offs, migration plans, and indexing strategies. Produces migration-ready SQL that can be applied directly to the database.

Inputs

• Feature requirements (from interview/idea phase)
• Existing schema (current tables, relationships, migrations)
• Query patterns (how the data will be read — drives index and denormalization decisions)
• Access control requirements (who can read/write which rows)

Process

Step 1: Inventory Existing Entities

Read current schema files, migration history, or ORM models. List all existing tables with their columns, types, constraints, and relationships. Note any existing indexes and RLS policies.

Step 2: Identify New Entities Needed

From the feature requirements and interview output, determine what new data needs to be stored. List candidate entities and their purpose.

Step 3: Define Entity Attributes

For each new entity, define:

• Column name, data type, and nullability
• Default values and constraints (UNIQUE, CHECK, etc.)
• Primary key strategy (UUID vs serial vs composite)
• Timestamp columns (created_at, updated_at)

Step 4: Map Relationships

Define all relationships between entities:

• 1:1 — Foreign key with UNIQUE constraint
• 1:N — Foreign key on the "many" side
• N:M — Junction/through table with composite primary key
• Document cascade behavior (ON DELETE CASCADE vs SET NULL vs RESTRICT)

Step 5: Apply Normalization

Default to 3NF. For each denormalization decision, document:

• What is being denormalized
• Why (query performance, read frequency, join cost)
• How consistency is maintained (triggers, application logic, eventual consistency)

Step 6: Design Indexes

Drive index choices from query patterns:

• Single-column indexes for filtered/sorted columns
• Composite indexes for multi-column WHERE clauses (column order matters)
• Partial indexes for filtered subsets (WHERE active = true)
• GIN indexes for array/JSONB columns if applicable

Step 7: Plan RLS Policies

If using Supabase or row-level security:

• Define SELECT, INSERT, UPDATE, DELETE policies per table
• Map policies to user roles (anon, authenticated, service_role)
• Use auth.uid() for user-scoped access
• Document any service_role bypass patterns

Step 8: Draft Migration SQL

Write executable SQL:

• CREATE TABLE statements for new tables
• ALTER TABLE statements for existing table modifications
• CREATE INDEX statements
• RLS policy statements
• Include a rollback section (DROP TABLE, DROP INDEX, etc.)

Output Format

# Schema Design: [Feature Name]

## Entity Overview

| Entity | Purpose | New/Modified |
|--------|---------|-------------|
| ...    | ...     | ...         |

## Entity Definitions

### [entity_name]
| Column | Type | Nullable | Default | Constraints |
|--------|------|----------|---------|-------------|
| id     | uuid | NO       | gen_random_uuid() | PK |
| ...    | ...  | ...      | ...     | ...         |

## Relationships

[ASCII diagram] users 1──N posts posts N──M tags (through: post_tags)

## Index Strategy
| Table | Index | Columns | Type | Rationale |
|-------|-------|---------|------|-----------|
| ...   | ...   | ...     | ...  | ...       |

## Denormalization Decisions
| What | Why | Consistency Strategy |
|------|-----|---------------------|
| ...  | ... | ...                 |

## RLS Policies
| Table | Operation | Policy | Using |
|-------|-----------|--------|-------|
| ...   | ...       | ...    | ...   |

## Migration SQL

### Up
```sql
-- New tables
CREATE TABLE ...

-- Indexes
CREATE INDEX ...

-- RLS
ALTER TABLE ... ENABLE ROW LEVEL SECURITY;
CREATE POLICY ...

Down

DROP POLICY ...
DROP INDEX ...
DROP TABLE ...

## Quality Checks

- [ ] Every entity has a primary key
- [ ] Foreign keys reference existing or newly created tables
- [ ] Indexes support the identified query patterns
- [ ] Migration is reversible (Down section undoes Up completely)
- [ ] RLS policies cover all access patterns (SELECT, INSERT, UPDATE, DELETE)
- [ ] Timestamp columns (created_at, updated_at) are present on mutable entities
- [ ] CASCADE behavior is explicitly defined for all foreign keys
- [ ] No orphan tables (every table is reachable via relationships or has a documented reason for isolation)

## Evolution Notes
<!-- Observations appended after each use -->