PostgreSQL Schema Architect

PostgreSQL Schema Architect takes your domain model, query patterns, and scale expectations as inputs and returns a complete schema design with every structural decision explained. It works through table layout, normalization versus deliberate denormalization trade-offs, index strategy matched to your specific query shapes, constraint definitions that enforce business rules at the database level rather than in application code, and a migration path if you are evolving an existing schema rather than starting fresh.

A typical input looks like this: a SaaS product serving multiple clients, a domain description covering users, subscriptions, usage events, and billing records, three or four representative slow queries or query patterns you know you need to run fast, and a note that you expect roughly 50 million event rows within a year. The skill uses that context to calibrate every decision — partition strategy, which columns warrant a partial index, where a composite index beats two single-column indexes, and whether a join table or a JSONB column is the right call for your variable-attribute problem.

The output is structured and decision-oriented. Example excerpt: Table: usage_events — Partitioned by range on occurred_at (monthly). Rationale: query filters almost always include a date range; partition pruning eliminates full-table scans at current growth rate. Index: (tenant_id, occurred_at DESC) — covers the tenant-scoped time-series query pattern identified above; a separate index on event_type added only if cardinality analysis supports selectivity. Constraint: tenant_id NOT NULL with FK to tenants enforced at DB level; application-layer checks removed as redundant. Migration note: backfill occurred_at partition key before attaching existing rows to avoid lock escalation.