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 |
expected_outputs/migration_sample.txt
View on GitHubDATABASE MIGRATION PLAN
==================================================
Migration ID: a7b3c9d2
Created: 2024-02-16T15:30:00Z
Zero Downtime: false
MIGRATION SUMMARY
-----------------
Total Steps: 12
Tables Added: 1
Tables Dropped: 0
Tables Renamed: 0
Columns Added: 3
Columns Dropped: 1
Columns Modified: 2
Constraints Added: 4
Constraints Dropped: 1
Indexes Added: 2
Indexes Dropped: 1
RISK ASSESSMENT
---------------
High Risk Steps: 3
Medium Risk Steps: 4
Low Risk Steps: 5
MIGRATION STEPS
---------------
1. Create table brands with 4 columns (LOW risk)
Type: CREATE_TABLE
Forward SQL: CREATE TABLE brands (
id INTEGER PRIMARY KEY,
name VARCHAR(100) NOT NULL,
description TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Rollback SQL: DROP TABLE IF EXISTS brands;
2. Add column brand_id to products (LOW risk)
Type: ADD_COLUMN
Forward SQL: ALTER TABLE products ADD COLUMN brand_id INTEGER;
Rollback SQL: ALTER TABLE products DROP COLUMN brand_id;
3. Add column email_verified to users (LOW risk)
Type: ADD_COLUMN
Forward SQL: ALTER TABLE users ADD COLUMN email_verified BOOLEAN DEFAULT false;
Rollback SQL: ALTER TABLE users DROP COLUMN email_verified;
4. Add column last_login to users (LOW risk)
Type: ADD_COLUMN
Forward SQL: ALTER TABLE users ADD COLUMN last_login TIMESTAMP;
Rollback SQL: ALTER TABLE users DROP COLUMN last_login;
5. Modify column price: type: DECIMAL(10,2) -> DECIMAL(12,2) (LOW risk)
Type: MODIFY_COLUMN
Forward SQL: ALTER TABLE products
ALTER COLUMN price TYPE DECIMAL(12,2);
Rollback SQL: ALTER TABLE products
ALTER COLUMN price TYPE DECIMAL(10,2);
6. Modify column inventory_count: nullable: true -> false (HIGH risk)
Type: MODIFY_COLUMN
Forward SQL: ALTER TABLE products
ALTER COLUMN inventory_count SET NOT NULL;
Rollback SQL: ALTER TABLE products
ALTER COLUMN inventory_count DROP NOT NULL;
7. Add primary key on id (MEDIUM risk)
Type: ADD_CONSTRAINT
Forward SQL: ALTER TABLE brands ADD CONSTRAINT pk_brands PRIMARY KEY (id);
Rollback SQL: ALTER TABLE brands DROP CONSTRAINT pk_brands;
8. Add foreign key constraint on brand_id (MEDIUM risk)
Type: ADD_CONSTRAINT
Forward SQL: ALTER TABLE products ADD CONSTRAINT fk_products_brand_id FOREIGN KEY (brand_id) REFERENCES brands(id);
Rollback SQL: ALTER TABLE products DROP CONSTRAINT fk_products_brand_id;
9. Add unique constraint on name (MEDIUM risk)
Type: ADD_CONSTRAINT
Forward SQL: ALTER TABLE brands ADD CONSTRAINT uq_brands_name UNIQUE (name);
Rollback SQL: ALTER TABLE brands DROP CONSTRAINT uq_brands_name;
10. Add check constraint: price > 0 (MEDIUM risk)
Type: ADD_CONSTRAINT
Forward SQL: ALTER TABLE products ADD CONSTRAINT chk_products_price_positive CHECK (price > 0);
Rollback SQL: ALTER TABLE products DROP CONSTRAINT chk_products_price_positive;
11. Create index idx_products_brand_id on (brand_id) (LOW risk)
Type: ADD_INDEX
Forward SQL: CREATE INDEX idx_products_brand_id ON products (brand_id);
Rollback SQL: DROP INDEX idx_products_brand_id;
Estimated Time: 1-5 minutes depending on table size
12. Create index idx_users_email_verified on (email_verified) (LOW risk)
Type: ADD_INDEX
Forward SQL: CREATE INDEX idx_users_email_verified ON users (email_verified);
Rollback SQL: DROP INDEX idx_users_email_verified;
Estimated Time: 1-5 minutes depending on table size
VALIDATION CHECKS
-----------------
• Verify table brands exists
SQL: SELECT COUNT(*) FROM information_schema.tables WHERE table_name = 'brands';
Expected: 1
• Verify column brand_id exists in products
SQL: SELECT COUNT(*) FROM information_schema.columns WHERE table_name = 'products' AND column_name = 'brand_id';
Expected: 1
• Verify column email_verified exists in users
SQL: SELECT COUNT(*) FROM information_schema.columns WHERE table_name = 'users' AND column_name = 'email_verified';
Expected: 1
• Verify column modification in products
SQL: SELECT data_type, is_nullable FROM information_schema.columns WHERE table_name = 'products' AND column_name = 'price';
Expected: 1
• Verify index idx_products_brand_id exists
SQL: SELECT COUNT(*) FROM information_schema.statistics WHERE index_name = 'idx_products_brand_id';
Expected: 1
• Verify index idx_users_email_verified exists
SQL: SELECT COUNT(*) FROM information_schema.statistics WHERE index_name = 'idx_users_email_verified';
Expected: 1Related 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 |