Database Schema, Index & Migration Design
Schemas designed from your access patterns, with index plans, zero-downtime migrations, and a defended SQL vs NoSQL choice.
Before & after
Same prompt, same agent — the only difference is the skill.
You'll need to decide between SQL and NoSQL based on your query patterns and consistency requirements. For a multi-tenant system, you'll want to consider isolation strategies and how to partition data. You should think about indexing strategies to keep query performance acceptable at scale. You might also want to plan for sharding early if you're expecting millions of records. What specific features does your application need to support?
MULTI-TENANT SAAS SCHEMA DESIGN (SQL, row-level security) FUNDAMENTAL DECISIONS: SQL (PostgreSQL) chosen over NoSQL because: ACID transactions needed for billing accuracy, relational queries (cross-customer analytics), strong data integrity. Row-level security via tenant_id column + RLS policy = security at DB layer. CORE TABLES: table tenants (id, name, plan_tier, created_at) Multi-tenant anchor. One row = one customer account. table users (id, tenant_id, email, password_hash, role, created_at) Index: (tenant_id, email). RLS policy: users can only access rows where tenant_id matches their session. table events (id, tenant_id, user_id, event_type, properties_jsonb, created_at) Large table (scale target: 100M+ rows). Partitioning by tenant_id or created_at (monthly). Index: (tenant_id, created_at DESC) for fast "events for tenant in last 30 days" queries. table subscriptions (id, tenant_id, plan_tier, status, billing_cycle, renewal_date, amount_cents, currency, created_at, updated_at) Billing source-of-truth. Immutable insert pattern: never UPDATE, only insert new rows (soft delete old). Audit trail preserved. RELATIONSHIP DIAGRAM (simplified): tenants 1:many users tenants 1:many subscriptions tenants 1:many events users 1:many events PARTITIONING STRATEGY (scale plan): At 10M events/month (120M/year), partition events table by created_at (monthly partitions). This: 1) keeps index scan fast (only needed partition scanned), 2) enables easy archival (drop old partitions to cold storage). MIGRATION PLAN: V1 (week 1): Create schema, populate tenants/users/subscriptions from production. Keep old system live in parallel (reads only). V2 (week 2): Dual-write new events table while legacy system still active. Verify parity. V3 (week 3): Cutover: switch application to new schema, backfill historical events if needed, decommission legacy. BACKUP/RECOVERY: Daily automated backups. Point-in-time recovery enabled (WAL archiving). Tenant-level restoration possible (restore single tenant to test environment without affecting others). QUERY PATTERNS OPTIMIZED: "Show me events for tenant X in last 7 days" → Partition + index scan = 200ms (millions of total events). "Billing revenue by plan_tier last month" → denormalized summary table refreshed nightly = 50ms. "Export user data for GDPR" → tenant_id filter returns exact set = 500ms for 1M users.
About this skill
Your schema looked clean in the design doc. Then the hot page needed a 6-way join, the index you added never got used, and a one-line migration locked a big table in production. This skill designs the data model from the queries your app actually runs, and stages every change so it is safe on a live database.
What it does
- Builds an access-pattern table first (filter, sort, frequency, latency target), then derives tables, keys, and constraints from it
- Picks the right column order for each composite index, plus when to use partial and covering indexes, and names the query each index serves
- Reads your pasted EXPLAIN (ANALYZE, BUFFERS) output and separates real causes (stale stats, N+1, OFFSET paging, wrong column order) from "just add an index"
- Writes expand-contract migrations with lock behavior and rollback for each step: CONCURRENTLY builds, NOT VALID constraints, batched backfills, safe NOT NULL
- Flags schema traps like polymorphic FKs, EAV tables, float money, random UUID primary keys, and soft deletes that break unique constraints
- Makes a defended SQL vs NoSQL call and states what would flip it
What you get
Ready-to-run DDL, an index list tied to specific queries, and a step-by-step production migration plan with locks, deploy order, and rollback notes. Quick questions get a direct answer and one line of reasoning.
Best for
Backend engineers and tech leads on PostgreSQL or MySQL who are designing new features, fixing slow queries, or changing tables that are already large and serving traffic.
SKILL.md preview
---
name: database-schema-index-migration-design
description: Designs relational and NoSQL data models from the workload's access patterns, then specifies keys, constraints, indexes, and lock-safe expand-contract migrations, with PostgreSQL and MySQL specifics. Use when the user says "design a schema for this feature", "how should I model these tables", "this query is slow, what index do I need", "plan a migration to rename/split this column", "should we use Postgres or DynamoDB/Mongo", "review my schema", "my migration locked the table in prod", or "my queries keep getting slower as the table grows".
---
# Database Schema, Index & Migration Design
… (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.