schema-health-scanner

What this is for

Database schemas accumulate structural debt silently - tables without primary keys, foreign-key fields without indexes, unbounded VARCHAR(255) columns, inconsistent naming conventions, mixed timestamp types, and god-tables with 20+ fields. No linter watches DDL the way ESLint watches JavaScript.

This skill reads DDL from raw SQL, Prisma, Drizzle, TypeORM, or SQLAlchemy model files and scores structural health with anti-pattern detection, then the LLM judges each finding in its domain context - because a missing primary key on a logging table may be acceptable, but on a customers table it is a design flaw.

PROTECTION RULE - never ~/.claude/

Read-only skill. Guard required if write mode added later.

## What You Must Do When Invoked

Step 1

1. If -help or -h is passed, print the ## Usage block below and stop.

Step 2

2. Confirm -ProjectDir is provided and the path exists. If not, print usage and stop.

Step 3

3. Run the collector script:

Step 4

scripts/schema-scan.ps1 -ProjectDir "<path>"

Step 5

4. LLM reads the JSON output. For each table:

Step 6

• Examine metrics (field count, has timestamps, naming style, FK index coverage).

Step 7

• Read the antiPatterns[] list.

Step 8

5. Per anti-pattern: is this finding justified by domain context?

Step 9

• "No PK on audit_log" may be acceptable for append-only logs.

Step 10

• "22 columns on user_settings" may be intentional denormalization.

Step 11

• Classify: real-issue / acceptable-given-context / false-positive.

Step 12

• Add confidence: proven / likely / suspected.

Step 13

6. Per table: assign an overall health score (A = healthy, B = minor issues, C = needs attention, D = critical) with brief reasoning. Write schema-health-report.md to the working directory with: executive summary, table-by-table breakdown, cross-cutting concerns, open questions.

Usage

/schema-health                        # interactive, prompts for directory
/schema-health <dir>                  # scan DDL files in directory
/schema-health -help                  # show usage

Returns JSON with tables[]: each entry {name, columns[], pk, fk[], indexes[], metrics{fieldCount, hasTimestamps, namingStyle, fkIndexCoverage}, antiPatterns[]} plus summary counts: {scannedFiles, tables, antiPatterns}.

Report Format

schema-health-report.md with:

• Executive summary (files scanned, tables found, anti-pattern counts)
• Table-by-table breakdown (table name, health score, metrics, anti-patterns with verdict)
• Cross-cutting concerns (naming convention drift, type consistency, timestamp coverage)
• Open questions (suspected-confidence findings needing human review)