---
name: database-schema-validator
description: "Lints a database schema against naming, key, index, and type rules and returns a machine-readable verdict: a pass flag, errors that must be fixed, and warnings worth a look, each tied to the object it concerns. Use when a schema's DDL should be checked for missing keys, mismatched foreign key types, naming inconsistencies, risky column types, and index gaps, with the result as JSON. Do not use for designing or documenting a schema, planning a refactor, or validating the data inside the tables. Related topics: coding, database."
---

# Database Schema Validator

Lints a database schema against naming, key, index, and type rules and returns a machine-readable verdict: a pass flag, errors that must be fixed, and warnings worth a look, each tied to the object it concerns.

## When to use

Use this skill when a schema's DDL should be checked for missing keys, mismatched foreign key types, naming inconsistencies, risky column types, and index gaps, with the result as JSON. Do not use it for designing or documenting a schema, planning a refactor, or validating the data inside the tables.

## Workflow

1. Ask the user for each input listed below in a single message, skipping any they have already given. Where options are listed, offer them, but accept other answers too.
2. Scan every table for error-level rules (missing primary key, foreign key type mismatch, unsafe NOT NULL, reserved words, unsupported types) and record each with table and column.
3. Scan for warning-level rules on naming, text lengths, money and timestamp types, updated_at, and, when enabled, index coverage and redundancy.
4. Promote findings on critical tables to errors and drop findings below the minimum severity.
5. Set valid to true only when the errors array is empty and write a one-sentence summary with the counts.
6. Return the JSON object alone and confirm it parses.

## Inputs

- **Database type** (choose one: MySQL, PostgreSQL, SQLite, SQL Server, Oracle)
- **Schema DDL** (longer free text)
- **Target database version** (free text, e.g. "8.0 or 16")
- **Check indexes** (choose one: Yes, No)
- **Critical tables** (longer free text)
- **Minimum severity to report** (choose one: Info, Warning, Error)

## Output

A single JSON object with keys valid (boolean), errors (array of table.column: finding strings), warnings (same form), and summary (one sentence). No prose outside the JSON.

## Prompt template

Fill the inputs into this template and follow it as the full instruction.

### Database Schema Validator

Validate this schema against best-practice rules and return the findings as JSON.

- Database type: {{Database type}}
- Schema DDL: {{Schema DDL}}
- Target database version: {{Target database version}}
- Check indexes: {{Check indexes}}
- Critical tables: {{Critical tables}}
- Minimum severity to report: {{Minimum severity to report}}

Apply the rules in this order and record each finding with the table and column it concerns. Errors: a table without a primary key, a foreign key column whose type differs from the referenced key, a NOT NULL column with no default on a table that already has rows, a reserved word used as an identifier, and a type that the target version does not support. Warnings: inconsistent naming (mixed snake_case and camelCase, singular and plural table names), text columns without a length where the dialect needs one, money stored in float, timestamps without time zone on PostgreSQL, missing updated_at on mutable tables, and, when index checks are on, foreign keys without an index, duplicate or redundant indexes, and indexes on low-cardinality columns. Info: style points such as column order and comment coverage. Promote any finding on a critical table to an error. Drop findings below the minimum severity. Return only a JSON object with "valid" (true when there are no errors), "errors" and "warnings" (arrays of strings in the form table.column: finding), and "summary" (one sentence with the counts).

## Pitfalls

- Reporting a style preference as an error, which trains the team to ignore the valid flag.
- Flagging a missing index on a column that is already the leading column of a composite index.
- Checking types against the latest database version rather than the target version the user named.
