database-schema

Database Schema Awareness Skill

Problem: Claude forgets schema details mid-session - wrong column names, missing fields, incorrect types. TDD catches this at runtime, but we can prevent it earlier.

---

Core Rule: Read Schema Before Writing Database Code

MANDATORY: Before writing ANY code that touches the database:

┌─────────────────────────────────────────────────────────────┐
│  1. READ the schema file (see locations below)              │
│  2. VERIFY columns/types you're about to use exist          │
│  3. REFERENCE schema in your response when writing queries  │
│  4. TYPE-CHECK using generated types (Drizzle/Prisma/etc)   │
└─────────────────────────────────────────────────────────────┘

If schema file doesn't exist → CREATE IT before proceeding.

---

Schema File Locations (By Stack)

Stack	Schema Location	Type Generation
Drizzle	src/db/schema.ts or drizzle/schema.ts	Built-in TypeScript
Prisma	prisma/schema.prisma	npx prisma generate
Supabase	supabase/migrations/*.sql + types	supabase gen types typescript
SQLAlchemy	app/models/*.py or src/models.py	Pydantic models
TypeORM	src/entities/*.ts	Decorators = types
Raw SQL	schema.sql or migrations/	Manual types required

Schema Reference File (Recommended)

Create _project_specs/schema-reference.md for quick lookup:

# Database Schema Reference

*Auto-generated or manually maintained. Claude: READ THIS before database work.*

## Tables

### users
| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | uuid | NO | gen_random_uuid() | PK |
| email | text | NO | - | Unique |
| name | text | YES | - | Display name |
| created_at | timestamptz | NO | now() | - |
| updated_at | timestamptz | NO | now() | - |

### orders
| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | uuid | NO | gen_random_uuid() | PK |
| user_id | uuid | NO | - | FK → users.id |
| status | text | NO | 'pending' | enum: pending/paid/shipped/delivered |
| total_cents | integer | NO | - | Amount in cents |
| created_at | timestamptz | NO | now() | - |

## Relationships
- users 1:N orders (user_id)

## Enums
- order_status: pending, paid, shipped, delivered