borghei/Claude-Skills / engineering/database-designer
database-designer
VerifiedAnalyse an existing SQL schema for normalization and constraint gaps, recommend indexes from query patterns, and generate forward/rollback migrations. Use when reviewing DDL, tuning indexes, or diffing two schema versions.
| File | Kind | Size |
|---|---|---|
| README.md | markdown | 11.8 KB |
| SKILL.md | skill md | 7.7 KB |
| assets/sample_query_patterns.json | config | 9.0 KB |
| assets/sample_schema.json | config | 9.1 KB |
| assets/sample_schema.sql | other | 8.1 KB |
| assets/sample_schema_target.json | config | 9.9 KB |
| expected_outputs/index_optimization_sample.txt | markdown | 2.6 KB |
| expected_outputs/migration_sample.txt | markdown | 4.4 KB |
| expected_outputs/schema_analysis_sample.txt | markdown | 7.3 KB |
| references/database_selection_decision_tree.md | markdown | 16.0 KB |
| references/index_strategy_patterns.md | markdown | 11.6 KB |
| references/normalization_guide.md | markdown | 10.4 KB |
| references/patterns-and-troubleshooting.md | markdown | 3.9 KB |
| references/workflows-and-tools.md | markdown | 7.4 KB |
| scripts/index_optimizer.py | script | 38.7 KB |
| scripts/migration_generator.py | script | 49.3 KB |
| scripts/schema_analyzer.py | script | 41.5 KB |
references/patterns-and-troubleshooting.md
View on GitHub# Database Designer — Patterns, Troubleshooting & Success Criteria
Read this when choosing an index type, avoiding common design mistakes, diagnosing tool
output, or checking work against the quality bar.
## Index Type Selection
| Index Type | Best For | Example |
|------------|----------|---------|
| B-tree | Range queries, sorting, equality | `CREATE INDEX idx ON tasks (status, created_date)` |
| Partial | Subset queries on hot data | `CREATE INDEX idx ON users (email) WHERE status = 'active'` |
| Covering | Avoiding table lookups | `CREATE INDEX idx ON users (email) INCLUDE (name, status)` |
| Hash | Exact match only | Primary keys, cache keys |
| GIN | JSONB, array, full-text | `CREATE INDEX idx ON docs USING GIN (data)` |
---
## Anti-Patterns
- **Over-indexing** -- every column indexed wastes write performance and storage; index only columns appearing in WHERE, JOIN, and ORDER BY
- **Missing foreign keys** -- relying on application-layer referential integrity leads to orphaned records; always declare FK constraints
- **VARCHAR(255) everywhere** -- oversized columns waste memory in indexes; right-size columns based on actual data
- **Premature denormalization** -- denormalize only when EXPLAIN ANALYZE shows join-related bottlenecks, not preemptively
- **Direct ALTER on large tables** -- `ALTER TABLE ... SET NOT NULL` on a 100M-row table locks the table; use expand-contract pattern
- **No validation queries in migrations** -- migrations without post-step validation risk silent data corruption
## Troubleshooting
| Problem | Cause | Solution |
|---------|-------|----------|
| Schema analyzer reports false 1NF violations | JSON or array columns detected as multi-valued fields | Review flagged columns; intentional JSONB/array usage is valid for document-style storage patterns |
| Index optimizer recommends indexes on low-selectivity columns | Boolean or status columns appear in frequent WHERE clauses | Use partial indexes (`WHERE status = 'active'`) instead of full-column indexes to reduce overhead |
| Migration generator produces high-risk steps for column type changes | Direct `ALTER COLUMN ... TYPE` can lock tables and fail on incompatible data | Use the `--zero-downtime` flag to generate expand-contract migration patterns with safe backfill steps |
| ERD output missing relationships | Foreign key constraints not declared in DDL or JSON input | Ensure all FK relationships are explicitly defined; the analyzer only detects declared constraints |
| Composite index column order seems wrong | Optimizer orders by estimated selectivity, not query clause order | Verify cardinality estimates in the schema JSON; provide `cardinality_estimate` per column for accurate ordering |
| Redundancy analysis flags covering indexes as overlapping | Overlap ratio calculation uses Jaccard similarity on column sets | Review flagged pairs manually; covering indexes with INCLUDE columns serve a different purpose than their subsets |
| Validation queries fail after migration | Target schema JSON does not match actual post-migration state | Run `--validate-only` before and after migration; ensure the target JSON reflects all intended changes precisely |
## Success Criteria
- Schema analysis detects 90%+ of normalization violations (1NF through BCNF) when provided complete DDL input
- Index recommendations reduce query execution time by 40%+ for analyzed query patterns (measured via EXPLAIN ANALYZE before/after)
- Migration scripts execute with zero data loss and include verified rollback for every forward step
- ERD generation produces valid Mermaid diagrams that render correctly for schemas with up to 50 tables
- Redundant index detection identifies 95%+ of duplicate and overlapping indexes with less than 5% false positive rate
- Zero-downtime migrations maintain full application availability during schema changes on tables with 10M+ rows
- Generated SQL statements are syntactically valid and compatible with PostgreSQL 14+ and MySQL 8.0+
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code. | Stars: 119k | Updated | Claude CodeCodex | Verified | Install |
| performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks. | Stars: 119k | Updated | Claude CodeCodex | Verified | Install |
| agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |
| agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |