/schema

Schema Examples And Templates

Purpose: Use this file when you need concrete schema, migration, ORM, or ER diagram examples.

Contents:

1. Entity design template
2. Common modeling patterns
3. Migration templates
4. Index examples
5. DB-specific examples
6. Framework snippets
7. ER diagram example
8. Output quality examples

Entity Design Template

## Entity: [EntityName]

**Purpose:** [What this entity represents]
**Owner:** [Which domain/service owns this]

### Attributes

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| id | UUID/BIGINT | PK | Primary identifier |
| created_at | TIMESTAMP | NOT NULL, DEFAULT NOW() | Creation time |
| updated_at | TIMESTAMP | NOT NULL | Last modification |
| [column] | [type] | [constraints] | [description] |

### Relationships

| Related Entity | Cardinality | FK Column | On Delete |
|----------------|-------------|-----------|-----------|
| [Entity] | 1:N / N:1 / N:M | [fk_column] | CASCADE / SET NULL / RESTRICT |

### Indexes

| Name | Columns | Type | Purpose |
|------|---------|------|---------|
| idx_[table]_[column] | [columns] | BTREE/GIN/etc | [Query pattern supported] |

Common Modeling Patterns

Migration Templates

Create Table

-- Migration: create_[table_name]

-- Up
CREATE TABLE [table_name] (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    [columns...],
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_[table]_[column] ON [table_name]([column]);

-- Down
DROP TABLE IF EXISTS [table_name];

Add Column

-- Up
ALTER TABLE [table_name]
ADD COLUMN [column_name] [TYPE] [CONSTRAINTS];

-- Down
ALTER TABLE [table_name]
DROP COLUMN IF EXISTS [column_name];

Add Foreign Key

-- Up
ALTER TABLE [child_table]
ADD CONSTRAINT fk_[child]_[parent]
FOREIGN KEY ([column]) REFERENCES [parent_table]([column])
ON DELETE [CASCADE|SET NULL|RESTRICT];

-- Down
ALTER TABLE [child_table]
DROP CONSTRAINT IF EXISTS fk_[child]_[parent];

Safe Column Rename

-- Phase 1: Expand
ALTER TABLE [table_name] ADD COLUMN [new_name] [TYPE];
UPDATE [table_name] SET [new_name] = [old_name];

-- Phase 2: Application dual-write / verification

-- Phase 3: Contract
ALTER TABLE [table_name] DROP COLUMN [old_name];

Index Examples

Composite Index Rule

## Composite Index: idx_[table]_[col1]_[col2]

**Columns:** (col1, col2, col3)

**Effective for:**
- WHERE col1 = ? ✅
- WHERE col1 = ? AND col2 = ? ✅
- WHERE col1 = ? AND col2 = ? AND col3 = ? ✅
- ORDER BY col1, col2 ✅

**Not effective for:**
- WHERE col2 = ? ❌
- WHERE col3 = ? ❌
- ORDER BY col2, col1 ❌

DB-Specific Examples

PostgreSQL: JSONB + GIN

CREATE TABLE products (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  attributes JSONB DEFAULT '{}',
  tags TEXT[] DEFAULT '{}'
);

CREATE INDEX idx_products_attributes ON products USING GIN (attributes);
CREATE INDEX idx_products_tags ON products USING GIN (tags);
CREATE INDEX idx_products_active ON products (name) WHERE deleted_at IS NULL;

MySQL: JSON + Virtual Column Index

CREATE TABLE products (
  id CHAR(36) PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  attributes JSON,
  category VARCHAR(100) AS (JSON_UNQUOTE(attributes->'$.category')) STORED,
  INDEX idx_category (category)
);

SQLite: JSON1 / FTS5

CREATE TABLE products (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  attributes TEXT
);

CREATE VIRTUAL TABLE docs USING fts5(title, body);

Framework Snippets

Prisma

model User {
  id        String   @id @default(uuid())
  email     String   @unique
  name      String?
  posts     Post[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@index([email])
  @@map("users")
}

TypeORM

@Entity('users')
export class User {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column({ unique: true })
  @Index()
  email: string;
}

Drizzle

export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  email: varchar('email', { length: 255 }).notNull().unique(),
}, (table) => ({
  emailIdx: index('idx_users_email').on(table.email),
}));

ER Diagram Example

erDiagram
    USER ||--o{ POST : writes
    USER ||--o{ COMMENT : writes
    POST ||--o{ COMMENT : has

    USER {
        uuid id PK
        string email UK
        timestamp created_at
    }

    POST {
        uuid id PK
        uuid author_id FK
        string title
    }

    COMMENT {
        uuid id PK
        uuid post_id FK
        uuid user_id FK
        text content
    }

Output Quality Examples

Good Schema Output

CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    status VARCHAR(20) NOT NULL DEFAULT 'pending'
        CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled')),
    total_amount DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status) WHERE status != 'cancelled';

Bad Schema Output

CREATE TABLE orders (
    id INT,
    user INT,
    status TEXT,
    amount FLOAT
);

Per-Recipe Behavior Notes (SKILL.md excerpt)

• design (default): SURVEY → MODEL → VALIDATE → PRESENT; load schema-examples.md + schema-design-anti-patterns.md.
• migration: Draft step-by-step migration DDL with rollback; load migration-patterns.md; flag zero-downtime risks.
• er: Generate Mermaid ER diagram from schema description or codebase; load schema-examples.md.
• normalize: Assess NF level and propose denormalization trade-offs; apply the gates in data-modeling-anti-patterns.md.
• index: Analyze query patterns and propose covering/partial indexes; load index-strategies.md.
• rollback: Provide reverse migration DDL, dual-write windows, backfill scripts, and safe alternatives for destructive changes (DROP COLUMN / data conversion). Ask First: destructive change without rollback path.
• tenant: Compare the 4 strategies (shared-DB / schema-per-tenant / DB-per-tenant / shard-based) against tenant count, isolation requirements, and cost constraints. Includes RLS / connection routing / per-tenant backup strategies. Coordinates with the Schema[tenant] agent.
• index: Query patterns → covering / partial / expression index design. Existing index-strategies.md.
• partition: Select range / list / hash / time-based. Present pruning impact, partition maintenance (auto-creation, old-partition deletion), and staged migration from existing tables.
• audit-log: Load audit-log-schema.md. Append-only audit table design — actor / action / target / before-image / after-image / timestamp / correlation-id. Choose Postgres temporal tables vs trigger-based vs CDC (Debezium). Define retention + WORM compliance + tamper-evidence (HMAC chain). Never UPDATE / DELETE on audit rows.
• event-sourcing: Load event-sourcing-schema.md. Event store table (event_id / aggregate_id / aggregate_version / event_type / payload / metadata) with optimistic concurrency, projections (read models), snapshots, outbox pattern for transactional event publishing. Map aggregate boundaries; CQRS-friendly.
• soft-delete: Load soft-delete-patterns.md. Compare deleted_at timestamp vs status enum vs tombstone row. Design partial unique indexes. Address FK cascade behavior, query default-filter risk (visible vs deleted set), GDPR right-to-erasure pathway (soft → hard delete + audit-log).