database-design

name	database-design
description	This skill should be used when designing database schemas for web applications. It covers entity-relationship modeling, table design, indexing strategies, migration planning, and ORM schema definition using Drizzle ORM.
depends	["real.md","cog.md"]
generates	["spec-database-design.md"]

Note for AI Agents: This skill generates specification documents for AI/Agent consumption (especially Claude Code). Before generating specs, you MUST load context from real.md and cog.md. If these files don't exist, invoke the 00-meta skill first to create them.

Prerequisites

Pre-execution Checklist

Before using this skill, verify:

1. real.md exists - Contains reality constraints (max 4 required + 3 optional)
2. cog.md exists - Contains cognitive model (Agents + Information + Context)

If either file is missing, execute:

Invoke skill: 00-meta

Context Loading

From cog.md, extract:

• Information entities: All data objects with unique codes and classifications
• Entity relationships: How entities connect to each other
• Agent-entity mappings: Which agents create/read/update/delete which entities

From real.md, extract:

• Data constraints: Encryption requirements, storage formats (e.g., JSONB for messages)
• Security constraints: Password hashing, API key encryption requirements
• Business rules: Auto-admin assignment, unique constraints

Database Design

Overview

This skill guides the design of database schemas for modern web applications. To create effective database designs, model entities and relationships, define table structures, plan indexes, establish migration strategies, and generate ORM schemas.

When to Use This Skill

• Starting database design for a new project
• Adding new entities to existing schema
• Optimizing query performance with indexes
• Planning database migrations
• Generating Drizzle ORM schema definitions

Process

Phase 1: Model Entities and Relationships

Entity Identification:

From the cognitive model (cog.md), identify:

• Core entities (users, conversations, messages)
• Supporting entities (configurations, templates)
• Junction entities (for many-to-many relationships)

Relationship Types:

Type	Example	Implementation
One-to-One	User → Profile	FK with unique
One-to-Many	User → Conversations	FK in child table
Many-to-Many	Users ↔ Roles	Junction table

ER Diagram Template:

┌─────────────┐       ┌─────────────────┐
│   Entity A  │──────<│    Entity B     │
├─────────────┤  1:N  ├─────────────────┤
│ id (PK)     │       │ id (PK)         │
│ field1      │       │ entity_a_id(FK) │
│ field2      │       │ field1          │
└─────────────┘       └─────────────────┘

Phase 2: Define Table Structures

Table Definition Template:

## Table: [table_name]

**Description:** [What this table stores]

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| id | UUID | PK, DEFAULT gen_random_uuid() | Primary key |
| name | VARCHAR(100) | NOT NULL | Display name |
| created_at | TIMESTAMP | DEFAULT NOW() | Creation time |

**Indexes:**
- PRIMARY KEY (id)
- INDEX idx_name (name)

**Constraints:**
- Reference: [real.md constraint if applicable]

**Relationships:**
- FK: column → other_table.id

Standard Column Types:

Use Case	PostgreSQL Type	Notes
Primary Key	UUID	Use gen_random_uuid()
Short Text	VARCHAR(N)	Specify max length
Long Text	TEXT	Unlimited length
JSON Data	JSONB	For flexible schemas
Boolean	BOOLEAN	true/false
Integer	INTEGER	Standard int
Timestamp	TIMESTAMP	Without timezone
Enum	VARCHAR	Or PostgreSQL ENUM

Naming Conventions:

Element	Convention	Example
Table	snake_case, plural	users, api_configurations
Column	snake_case	user_id, created_at
Primary Key	id	id
Foreign Key	singular_id	user_id
Index	idx_table_column	idx_users_email
Unique	uniq_table_column	uniq_users_email

Phase 3: Design Indexes

Index Strategy:

Query Pattern	Index Type
Equality (WHERE x = ?)	B-tree (default)
Range (WHERE x > ?)	B-tree
Full-text search	GIN
JSON queries	GIN
Unique constraint	Unique index

Common Index Patterns:

-- Primary key (automatic)
PRIMARY KEY (id)

-- Unique constraint
UNIQUE INDEX idx_users_email (email)

-- Foreign key lookup
INDEX idx_messages_conversation (conversation_id)

-- Composite for multi-column queries
INDEX idx_config_user_provider (user_id, provider)

-- Partial index for filtered queries
INDEX idx_active_users (id) WHERE status = 'active'

Index Guidelines:

• Index foreign keys for JOIN performance
• Index columns used in WHERE clauses
• Avoid over-indexing (slows writes)
• Consider composite indexes for multi-column queries

Phase 4: Plan Migrations

Migration Strategy:

Phase	Actions	Risk Level
Create	Add tables, columns, indexes	Low
Modify	Alter columns, add constraints	Medium
Delete	Drop tables, columns	High

Safe Migration Practices:

1. **Adding column:** Safe
   - Add with DEFAULT or NULL

2. **Removing column:** Risky
   - Ensure no code references it
   - Consider deprecation period

3. **Renaming column:** Risky
   - Use migration in steps
   - Add new → copy data → drop old

4. **Changing type:** Risky
   - May require data transformation
   - Test with production-like data

Drizzle Migration Commands:

# Generate migration
bunx drizzle-kit generate

# Apply migration
bunx drizzle-kit migrate

# View database
bunx drizzle-kit studio

Phase 5: Generate Drizzle Schema

Drizzle Schema Template:

// lib/db/schema.ts
import { 
  pgTable, 
  uuid, 
  varchar, 
  text, 
  timestamp, 
  boolean, 
  jsonb,
  integer,
  uniqueIndex,
  index 
} from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  password: varchar('password', { length: 255 }).notNull(),
  name: varchar('name', { length: 100 }),
  role: varchar('role', { length: 20 }).default('user'),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow(),
});

export const conversations = pgTable('conversations', {
  : ().().(),
  : ().().( users.),
  : (, { :  }),
  : (, { :  }),
  : ().(),
  : ().(),
},  ({
  : ().(table.),
}));

  messages = (, {
  : ().().(),
  : ()
    .()
    .( conversations.),
  : (, { :  }).(),
  : ().(),  
  : ().(),
},  ({
  : ().(table.),
}));

Type Export Pattern:

// lib/db/types.ts
import { InferSelectModel, InferInsertModel } from 'drizzle-orm';
import { users, conversations, messages } from './schema';

export type User = InferSelectModel<typeof users>;
export type NewUser = InferInsertModel<typeof users>;

export type Conversation = InferSelectModel<typeof conversations>;
export type NewConversation = InferInsertModel<typeof conversations>;

export type Message = InferSelectModel<typeof messages>;
export type NewMessage = InferInsertModel<typeof messages>;

Output Template

# Database Design Document

## 1. Entity-Relationship Diagram
[ER diagram]

## 2. Table Definitions

### 2.1 users
[Table definition]

### 2.2 conversations
[Table definition]

...

## 3. Indexes
[Index definitions and rationale]

## 4. Constraints
[Business constraints and validation rules]

## 5. Migration Plan
[Migration strategy]

## 6. Drizzle Schema
[TypeScript schema code]

Quality Checklist

• All entities from cog.md are represented
• Relationships are properly defined with FKs
• Indexes cover common query patterns
• Constraints from real.md are implemented
• Naming conventions are consistent
• JSONB is used where flexibility is needed
• Migration strategy is safe
• Drizzle schema is type-safe

Integration with Other Skills

Skill	Relationship
system-architecture	Input: architecture defines entities
coding	Output: schema used in application code
quality-assurance	Output: schema tested in integration tests