Database Schema Designer

Design normalized relational database schemas from requirements and generate migrations, TypeScript/Python types, seed data, Row-Level Security policies, index strategies, and ERD diagrams. Handles multi-tenancy, soft deletes, audit trails, optimistic locking, polymorphic associations, and temporal data patterns. Supports PostgreSQL, MySQL, and SQLite with Drizzle, Prisma, TypeORM, and Alembic.

Keywords

database schema, schema design, normalization, migration, ERD, row-level security, indexing, multi-tenancy, soft deletes, audit trail, Drizzle, Prisma, PostgreSQL

Core Capabilities

• Schema design from requirements — extract entities/relationships from natural language, apply 1NF–3NF normalization, add timestamps/soft-delete/audit/versioning, generate complete DDL.
• Migration planning — forward and rollback migrations, zero-downtime patterns for large tables, column additions/type changes/backfills across Drizzle, Prisma, TypeORM, Alembic, and raw SQL.
• Index strategy — composite, partial, covering, and GIN/GiST indexes mapped to query patterns; bloat detection and maintenance.
• Type generation — TypeScript interfaces + Zod schemas and Python dataclasses + Pydantic models from the DB schema (enums as string unions).
• Security — Row-Level Security for multi-tenant isolation, column-level PII encryption, audit logging with before/after JSON snapshots.

When to Use

• Designing tables for a new feature
• Reviewing an existing schema for normalization or performance issues
• Adding multi-tenancy to a single-tenant schema
• Planning a breaking schema migration
• Generating ERD documentation for a service

Clarify First

Before designing the schema, confirm these inputs. If any is unknown or vague, ASK — do not assume:

• Requirements or existing DDL — the entities/relationships in natural language, or the schema to review (the source the design is derived from)
• Engine & ORM — PostgreSQL/MySQL/SQLite and Drizzle/Prisma/TypeORM/Alembic (sets the migration and type-generation output format)
• Cross-cutting needs — multi-tenancy/RLS, soft deletes, audit trails, or temporal data (determines which patterns and policies are generated)

Stop rule: ask only the 2-3 that most change the output. If the user says "just draft it," proceed and list your assumptions at the top of the artifact.

Tools

Tool	Purpose	Command
erd_generator.py	Parse SQL DDL and generate a Mermaid ER diagram	python scripts/erd_generator.py schema.sql -o erd.mmd
migration_diffr.py	Diff two SQL schemas into migration ALTER statements (with rollback)	python scripts/migration_diffr.py old.sql new.sql
schema_validator.py	Validate DDL for normalization violations, missing indexes, naming	python scripts/schema_validator.py schema.sql --strict

References

Load the reference that matches the task — keep this file lean and pull detail on demand:

• references/schema-design-and-security.md — the 4-step requirements-to-schema process, the full Drizzle ORM schema example, cross-cutting concerns, and PostgreSQL Row-Level Security policies. Read when designing a new schema or adding multi-tenancy.
• references/indexes-and-migrations.md — the index-type decision framework, index anti-patterns, zero-downtime migration patterns (add NOT NULL column, rename column), and Mermaid ERD generation. Read when choosing indexes or planning a safe migration.
• references/best-practices-and-troubleshooting.md — common pitfalls, best practices, the troubleshooting table, and the success-criteria bar. Read before shipping a schema or when diagnosing a problem.

Scope & Limitations

This skill covers:

• Relational schema design for PostgreSQL, MySQL, and SQLite including normalization through 3NF
• Migration generation and zero-downtime migration planning for Drizzle, Prisma, TypeORM, and Alembic
• Row-Level Security policies, index strategy, and type generation (TypeScript and Python)
• Cross-cutting patterns: multi-tenancy, soft deletes, audit trails, optimistic locking, and temporal data

This skill does NOT cover:

• NoSQL or document database design (MongoDB, DynamoDB, Cassandra) — see senior-data-engineer for broader data store guidance
• Query optimization and execution plan analysis beyond index recommendations — see performance-profiler for runtime profiling
• Database infrastructure provisioning, replication, or failover configuration — see senior-cloud-architect for cloud database setup
• Application-layer ORM patterns, connection pooling, or caching strategies — see senior-backend for backend architecture decisions

Integration Points

Skill	Integration	Data Flow
migration-architect	Hands off generated DDL and migration files for sequencing across services	Schema Designer produces migrations, Migration Architect orchestrates cross-service rollout order
api-design-reviewer	Schema entities map directly to API resource models and endpoint structure	Schema entities and relationships feed into REST/GraphQL resource definitions and validation rules
senior-backend	Generated types and ORM schemas plug into repository and service layers	TypeScript interfaces and Pydantic models from schema become the backend's data access contracts
performance-profiler	Index strategy recommendations are validated against real query execution plans	Schema Designer proposes indexes, Performance Profiler confirms effectiveness with EXPLAIN ANALYZE data
senior-secops	RLS policies and column encryption align with security compliance requirements	Security requirements flow in, RLS policies and encryption specifications flow out for audit verification
observability-designer	Audit log schema provides the foundation for operational dashboards and alerting	Audit log table structure feeds into observability pipelines for change tracking and anomaly detection