Relational Schema Design and Safe Migrations
Turns requirements into a constraint-enforced relational schema with ERD, DDL, and a zero-downtime migration plan.
Before & after
Same prompt, same agent — the only difference is the skill.
A database schema for a podcast platform would need to handle users, episodes, subscriptions, and that kind of thing. Normalization is important so you don't have data redundancy. You'll probably want separate tables for different entities and then think about relationships between them. There are different approaches you could take depending on how you want to query the data.
PODCAST PLATFORM SCHEMA TABLE: users • id (PK) • email (unique) • role (creator | listener) • created_at TABLE: shows • id (PK) • creator_id (FK→users) • title • description • cover_url • created_at TABLE: episodes • id (PK) • show_id (FK→shows) • title • description • audio_url • duration_seconds • published_at • created_at TABLE: subscriptions • id (PK) • listener_id (FK→users) • show_id (FK→shows) • subscribed_at • (composite index: listener_id, show_id) TABLE: listens • id (PK) • listener_id (FK→users) • episode_id (FK→episodes) • position_seconds • completed_at (nullable) • created_at KEY RELATIONSHIPS • One creator has many shows • One show has many episodes • Listeners subscribe to shows • Listeners generate listen records per episode INDEXES: (show_id, published_at), (listener_id, subscribed_at)
About this skill
Your tables look fine until duplicate accounts appear, a deleted user takes their invoices with them, or a one-line ALTER TABLE locks production for ten minutes. This skill designs relational schemas so the database enforces the business rules itself. Every change to a live table is planned step by step around locks.
What it does
- States the grain of every table and picks keys deliberately: surrogate key plus a unique natural key, and composite or surrogate keys for join tables
- Chooses CASCADE, RESTRICT, or SET NULL per foreign key, based on ownership and audit weight
- Turns business rules into constraints: CHECKs, partial uniques for soft deletes, exclusion constraints for no-overlap, and tenant-safe composite foreign keys
- Replaces fragile patterns (polymorphic type/id columns, boolean state soup, EAV, floats for money) with ones that can be enforced
- Plans live migrations as expand/contract steps, with lock behavior, batched backfills, and a rollback for each step
- Reviews pasted schemas or ORM models and ranks findings by data-corruption risk
What you get
A schema package: assumptions, a table-by-table grain list, a Mermaid ERD, DDL for your engine, and a map from each business rule to the constraint that enforces it. When you're changing a live database, you also get a step-by-step migration plan.
Best for
Backend and full-stack developers designing tables for a new feature, reviewing an existing schema, or changing a production table without downtime on PostgreSQL, MySQL, or SQLite.
SKILL.md preview
---
name: relational-schema-design-migrations
description: Designs relational database schemas from requirements. Covers table grain, keys, cardinality, foreign-key actions, and constraints that enforce business rules, and delivers a Mermaid ERD, DDL, and a lock-aware expand/contract migration plan for live databases. Use when the user says "design the tables for this feature", "draw an ERD for my app", "is this schema normalized?", "how do I model many-to-many with extra fields", "my polymorphic comments table is a mess", "how do I rename this column without downtime", "my migration locked the table in production", or "review my schema".
---
# Relational Schema Design and Safe Migrations
… (sign up to view the full skill)More development skills
View all Development skills →Production Go: Concurrency, Services & Profiling
Production Go code with leak-free goroutines, context-correct services, wrapped errors, and pprof-driven performance fixes.
Backend Feature Build Plan
Builds one backend feature through gates: confirmed requirements, one-screen design, small tested slices, and a deploy/rollback handoff.
Consistent, Safe Application Logging
Adds structured, correlated logs with useful severity, safe fields, bounded volume, and targeted verification.
Reliable WebSocket Messaging and Scaling
Build WebSocket and Socket.IO systems with authorized rooms, reconnect recovery, bounded queues, and multi-node presence.