prisma_schema_validator_with_index_and_naming_checks.py

1# SKILL.md
2
3---
4name: database-design
5description: Database design principles and decision-making. Schema design, indexing strategy, ORM selection, serverless databases.
6allowed-tools: Read, Write, Edit, Glob, Grep
7---
8
9# Database Design
10
11> **Learn to THINK, not copy SQL patterns.**
12
13## 🎯 Selective Reading Rule
14
15**Read ONLY files relevant to the request!** Check the content map, find what you need.
16
17| File | Description | When to Read |
18|------|-------------|--------------|
19| `database-selection.md` | PostgreSQL vs Neon vs Turso vs SQLite | Choosing database |
20| `orm-selection.md` | Drizzle vs Prisma vs Kysely | Choosing ORM |
21| `schema-design.md` | Normalization, PKs, relationships | Designing schema |
22| `indexing.md` | Index types, composite indexes | Performance tuning |
23| `optimization.md` | N+1, EXPLAIN ANALYZE | Query optimization |
24| `migrations.md` | Safe migrations, serverless DBs | Schema changes |
25
26---
27
28## ⚠️ Core Principle
29
30- ASK user for database preferences when unclear
31- Choose database/ORM based on CONTEXT
32- Don't default to PostgreSQL for everything
33
34---
35
36## Decision Checklist
37
38Before designing schema:
39
40- [ ] Asked user about database preference?
41- [ ] Chosen database for THIS context?
42- [ ] Considered deployment environment?
43- [ ] Planned index strategy?
44- [ ] Defined relationship types?
45
46---
47
48## Anti-Patterns
49
50❌ Default to PostgreSQL for simple apps (SQLite may suffice)
51❌ Skip indexing
52❌ Use SELECT * in production
53❌ Store JSON when structured data is better
54❌ Ignore N+1 queries
55
56
57
58# schema_validator.py
59
60```python
61#!/usr/bin/env python3
62"""
63Schema Validator - Database schema validation
64Validates Prisma schemas and checks for common issues.
65
66Usage:
67    python schema_validator.py <project_path>
68
69Checks:
70    - Prisma schema syntax
71    - Missing relations
72    - Index recommendations
73    - Naming conventions
74"""
75
76import sys
77import json
78import re
79from pathlib import Path
80from datetime import datetime
81
82# Fix Windows console encoding
83try:
84    sys.stdout.reconfigure(encoding='utf-8', errors='replace')
85except:
86    pass
87
88
89def find_schema_files(project_path: Path) -> list:
90    """Find database schema files."""
91    schemas = []
92    
93    # Prisma schema
94    prisma_files = list(project_path.glob('**/prisma/schema.prisma'))
95    schemas.extend([('prisma', f) for f in prisma_files])
96    
97    # Drizzle schema files
98    drizzle_files = list(project_path.glob('**/drizzle/*.ts'))
99    drizzle_files.extend(project_path.glob('**/schema/*.ts'))
100    for f in drizzle_files:
101        if 'schema' in f.name.lower() or 'table' in f.name.lower():
102            schemas.append(('drizzle', f))
103    
104    return schemas[:10]  # Limit
105
106
107def validate_prisma_schema(file_path: Path) -> list:
108    """Validate Prisma schema file."""
109    issues = []
110    
111    try:
112        content = file_path.read_text(encoding='utf-8', errors='ignore')
113        
114        # Find all models
115        models = re.findall(r'model\s+(\w+)\s*{([^}]+)}', content, re.DOTALL)
116        
117        for model_name, model_body in models:
118            # Check naming convention (PascalCase)
119            if not model_name[0].isupper():
120                issues.append(f"Model '{model_name}' should be PascalCase")
121            
122            # Check for id field
123            if '@id' not in model_body and 'id' not in model_body.lower():
124                issues.append(f"Model '{model_name}' might be missing @id field")
125            
126            # Check for createdAt/updatedAt
127            if 'createdAt' not in model_body and 'created_at' not in model_body:
128                issues.append(f"Model '{model_name}' missing createdAt field (recommended)")
129            
130            # Check for @relation without fields
131            relations = re.findall(r'@relation\([^)]*\)', model_body)
132            for rel in relations:
133                if 'fields:' not in rel and 'references:' not in rel:
134                    pass  # Implicit relation, ok
135            
136            # Check for @@index suggestions
137            foreign_keys = re.findall(r'(\w+Id)\s+\w+', model_body)
138            for fk in foreign_keys:
139                if f'@@index([{fk}])' not in content and f'@@index(["{fk}"])' not in content:
140                    issues.append(f"Consider adding @@index([{fk}]) for better query performance in {model_name}")
141        
142        # Check for enum definitions
143        enums = re.findall(r'enum\s+(\w+)\s*{', content)
144        for enum_name in enums:
145            if not enum_name[0].isupper():
146                issues.append(f"Enum '{enum_name}' should be PascalCase")
147        
148    except Exception as e:
149        issues.append(f"Error reading schema: {str(e)[:50]}")
150    
151    return issues
152
153
154def main():
155    project_path = Path(sys.argv[1] if len(sys.argv) > 1 else ".").resolve()
156    
157    print(f"\n{'='*60}")
158    print(f"[SCHEMA VALIDATOR] Database Schema Validation")
159    print(f"{'='*60}")
160    print(f"Project: {project_path}")
161    print(f"Time: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")
162    print("-"*60)
163    
164    # Find schema files
165    schemas = find_schema_files(project_path)
166    print(f"Found {len(schemas)} schema files")
167    
168    if not schemas:
169        output = {
170            "script": "schema_validator",
171            "project": str(project_path),
172            "schemas_checked": 0,
173            "issues_found": 0,
174            "passed": True,
175            "message": "No schema files found"
176        }
177        print(json.dumps(output, indent=2))
178        sys.exit(0)
179    
180    # Validate each schema
181    all_issues = []
182    
183    for schema_type, file_path in schemas:
184        print(f"\nValidating: {file_path.name} ({schema_type})")
185        
186        if schema_type == 'prisma':
187            issues = validate_prisma_schema(file_path)
188        else:
189            issues = []  # Drizzle validation could be added
190        
191        if issues:
192            all_issues.append({
193                "file": str(file_path.name),
194                "type": schema_type,
195                "issues": issues
196            })
197    
198    # Summary
199    print("\n" + "="*60)
200    print("SCHEMA ISSUES")
201    print("="*60)
202    
203    if all_issues:
204        for item in all_issues:
205            print(f"\n{item['file']} ({item['type']}):")
206            for issue in item["issues"][:5]:  # Limit per file
207                print(f"  - {issue}")
208            if len(item["issues"]) > 5:
209                print(f"  ... and {len(item['issues']) - 5} more issues")
210    else:
211        print("No schema issues found!")
212    
213    total_issues = sum(len(item["issues"]) for item in all_issues)
214    # Schema issues are warnings, not failures
215    passed = True
216    
217    output = {
218        "script": "schema_validator",
219        "project": str(project_path),
220        "schemas_checked": len(schemas),
221        "issues_found": total_issues,
222        "passed": passed,
223        "issues": all_issues
224    }
225    
226    print("\n" + json.dumps(output, indent=2))
227    
228    sys.exit(0)
229
230
231if __name__ == "__main__":
232    main()
233
234```