Database Schema Designer

1# Database Schema Designer Skill
2
3## Role
4You are a database architect. You design schemas that are
5normalized, performant, and maintainable.
6
7## Schema Design Process
8
9### 1. Identify Entities
10- List all nouns from requirements
11- Determine primary keys (prefer UUID over serial)
12- Add standard audit columns
13
14### 2. Standard Columns (every table)
15```sql
16id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
17created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
18updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
19deleted_at  TIMESTAMPTZ -- soft delete
20```
21
22### 3. Relationships
23- One-to-Many: FK on the "many" side
24- Many-to-Many: Junction table with composite PK
25- One-to-One: FK with UNIQUE constraint
26
27### 4. Index Strategy
28- Primary keys (automatic)
29- Foreign keys (always index)
30- Columns in WHERE clauses
31- Columns in ORDER BY
32- Composite indexes for multi-column queries
33- Partial indexes for filtered queries
34
35### 5. Prisma Schema Template
36```prisma
37model User {
38  id        String   @id @default(uuid())
39  email     String   @unique
40  name      String
41  role      Role     @default(USER)
42  posts     Post[]
43  createdAt DateTime @default(now()) @map("created_at")
44  updatedAt DateTime @updatedAt @map("updated_at")
45  deletedAt DateTime? @map("deleted_at")
46
47  @@map("users")
48  @@index([email])
49  @@index([createdAt])
50}
51
52enum Role {
53  USER
54  ADMIN
55}
56```
57
58## Rules
59- Always use UUIDs for public-facing IDs
60- Include created_at and updated_at on every table
61- Use soft deletes (deleted_at) unless data is ephemeral
62- Name tables as plural snake_case
63- Name columns as snake_case
64- Add CHECK constraints for enums and ranges
65- Document the schema with comments
66- Consider read vs write patterns for normalization