schema_skill

Overview

This skill is a data-architecture assistant that designs database schemas, generates reversible migrations, and produces ER diagrams. It focuses on data integrity, safe migrations, normalization-by-default, and pragmatic indexing. Use it to turn requirements into deployable schema artifacts and clear documentation. It’s optimized for PostgreSQL, MySQL, and SQLite patterns and can emit ORM-compatible schema code.

How this skill works

I analyze entities, relationships, and query patterns to produce table definitions, constraints, and index recommendations. I generate up/down migration SQL (including safe expand-contract patterns) and output Mermaid ER diagrams for documentation. I document normalization choices and denormalization trade-offs, and I provide framework schema snippets for Prisma, TypeORM, or Drizzle when requested. Interaction prompts appear at decision points for breaking changes, denormalization, or index strategy.

When to use it

• Designing new data models or adding new tables to an application
• Preparing migrations for production with rollback and zero-downtime patterns
• Optimizing index strategy before or after deployment
• Converting requirements into API-ready data contracts and ER diagrams
• Auditing schema for normalization, constraints, and growth patterns

Best practices

• Default to 3NF; denormalize intentionally and document the trade-off
• Always produce reversible migrations (up and down) and use expand-contract for risky changes
• Define PKs, FKs, NOT NULL, and CHECK constraints explicitly; avoid ambiguous names
• Index based on query patterns; prefer partial or composite indexes only when justified
• Back up or snapshot before destructive operations (DROP COLUMN/TABLE)

Example use cases

• Create billing and invoice tables with audit trail and soft-delete columns
• Add a JSONB metadata column and a GIN index for flexible document queries
• Design junction tables and composite PKs for many-to-many relationships
• Perform a safe rename of a critical column using a 3-phase expand-contract migration
• Generate Prisma/TypeORM schema snippets and a Mermaid ER diagram for handoff