schema-consistency-checker — Claude Code skill: database schema audit
schema-consistency-checker reads your tables and columns, flags names that break the agreed style, mismatched ID types, nullable critical fields and foreign keys without an index, then hands back a fix list.
MIT 265 installs ★ 64 updated 2026-01-12
npx skills add patricio0312rev/skills --skill schema-consistency-checker -a claude-codeWhat schema-consistency-checker does
The skill carries a set of house rules: tables in PascalCase, columns in camelCase, indexes prefixed idx_, foreign keys fk_, constraints chk_ or unq_. They sit in naming-rules.ts as regex patterns, so the report names the exact column that breaks style.
The pass itself runs from scripts/audit-schema.ts. It connects with PrismaClient, pulls column metadata from information_schema.columns, then looks for NULLs in fields like email, user_id or created_at, for an id whose type differs from the first one found, for foreign keys with no index in pg_indexes, and for tables missing a creation timestamp. Every finding gets a severity level (error, warning, info) and a recommendation.
The queries are PostgreSQL-specific and the script imports @prisma/client, so another engine or a project without Prisma means rewriting the checks first.
What it helps with
- check table and column names against one naming style before review
- find tables where
iduses a different type - list foreign keys that have no index behind them
- catch nullable critical fields such as
emailorcreated_at - get a violation report with a fix next to each item
Example prompts for the agent
- Audit the database schema for naming and nullability problems.
- Show me which foreign keys are missing an index.
- Run the schema consistency check and list the violations with fixes.
How to install schema-consistency-checker
1Skills CLI (Vercel)
npx skills add patricio0312rev/skills --skill schema-consistency-checker -a claude-code2Manually: put the files in place
~/.claude/skills/schema-consistency-checker/SKILL.mdCopy the whole skill folder; for one project use .claude/skills/schema-consistency-checker/
1Skills CLI (Vercel)
npx skills add patricio0312rev/skills --skill schema-consistency-checker -a codex2Manually: put the files in place
.agents/skills/schema-consistency-checker/SKILL.mdFor all projects use ~/.agents/skills/
1Skills CLI (Vercel)
npx skills add patricio0312rev/skills --skill schema-consistency-checker -a cursor2Manually: put the files in place
.cursor/skills/schema-consistency-checker/SKILL.mdCursor also reads .agents/skills/ and .claude/skills/
1GitHub CLI
gh skill install patricio0312rev/skills db-management/schema-consistency-checker/SKILL.md2Manually: put the files in place
.github/skills/schema-consistency-checker/SKILL.md1Skills CLI (Vercel)
npx skills add patricio0312rev/skills --skill schema-consistency-checker -a gemini-cli2Platform CLI
gemini skills install https://github.com/patricio0312rev/skills --path db-management/schema-consistency-checkerManually: put the files in place
.devin/skills/schema-consistency-checker/SKILL.mdThe legacy .windsurf/skills/ path still works
Skills CLI (Vercel)
npx skills add patricio0312rev/skills --skill schema-consistency-checker -a opencodeClaude.ai
Zip the folder and upload it in Settings → Capabilities → Skills
Step by step, with commands for each platform: How to install skills in Claude Code: five ways
Questions about schema-consistency-checker
How do I install schema-consistency-checker in Claude Code?
Run npx skills add patricio0312rev/skills --skill schema-consistency-checker -a claude-code. The skill ends up at ~/.claude/skills/schema-consistency-checker/SKILL.md.
What does schema-consistency-checker do?
It compares the schema against a rule set covering table and column naming, ID types, NULL handling on critical fields and indexes for foreign keys, then returns the violations with suggested fixes.
Which database does it work with?
PostgreSQL. The audit script goes through PrismaClient and queries information_schema and pg_indexes.
Source text
SKILL.md · MIT
---
name: schema-consistency-checker
description: Audits database schemas for naming conventions, type consistency, nullability patterns, and missing constraints. Provides violations report with recommended fixes. Use for "schema validation", "database linting", "schema standards", or "consistency checks".
---
# Schema Consistency Checker
Enforce schema consistency and best practices across your database.
## Consistency Rules
### 1. Naming Conventions
```typescript
// naming-rules.ts
export const NAMING_RULES = {
tables: {
pattern: /^[A-Z][a-zA-Z0-9]*$/, // PascalCase
examples: ["User", "OrderItem", "ProductCategory"],
},
columns: {
pattern: /^[a-z][a-zA-Z0-9]*$/, // camelCase
examples: ["id", "firstName", "createdAt"],
},
indexes: {
pattern: /^idx_[a-z_]+$/, // idx_table_column
examples: ["idx_users_email", "idx_orders_user_id"],
},
foreignKeys: {
pattern: /^fk_[a-z_]+$/, // fk_table_column
examples: ["fk_orders_user_id", "fk_products_category_id"],
},
constraints: {
pattern: /^(chk|unq)_[a-z_]+$/, // chk_ or unq_prefix
examples: ["chk_age_positive", "unq_users_email"],
},
};
```
### 2. Type Consistency
```sql
-- ❌ Bad: Inconsistent types for IDs
CREATE TABLE users (
id INTEGER PRIMARY KEY,
email TEXT
);
CREATE TABLE orders (
id BIGINT PRIMARY KEY, -- ❌ Different ID type
user_id TEXT -- ❌ Wrong type for FK
);
-- ✅ Good: Consistent types
CREATE TABLE users (
id BIGINT PRIMARY KEY,
email TEXT
);
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
user_id BIGINT REFERENCES users(id)
);
```
### 3. Nullability Patterns
```sql
-- ❌ Bad: Inconsistent NULL handling
CREATE TABLE users (
id BIGINT PRIMARY KEY,
email TEXT, -- ❌ No NOT NULL on critical field
name TEXT, -- ❌ Should be NOT NULL
phone TEXT NULL, -- ⚠️ Explicit NULL unnecessary
created_at TIMESTAMP -- ❌ Missing NOT NULL
);
-- ✅ Good: Clear nullability
CREATE TABLE users (
id BIGINT PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
phone TEXT, -- Optional field
created_at TIMESTAMP NOT NULL DEFAULT NOW()
);
```
### 4. Missing Constraints
```sql
-- ❌ Bad: Missing constraints
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
user_id BIGINT, -- ❌ Missing FK
status TEXT, -- ❌ No CHECK constraint
total DECIMAL(10,2), -- ❌ No CHECK for positive
created_at TIMESTAMP
);
-- ✅ Good: Proper constraints
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
status TEXT NOT NULL CHECK (status IN ('pending', 'paid', 'shipped', 'delivered')),
total DECIMAL(10,2) NOT NULL CHECK (total >= 0),
created_at TIMESTAMP NOT NULL DEFAULT NOW()
);
```
## Audit Script
```typescript
// scripts/audit-schema.ts
import { PrismaClient } from "@prisma/client";
const prisma = new PrismaClient();
interface Violation {
severity: "error" | "warning" | "info";
category: string;
table: string;
column?: string;
message: string;
recommendation: string;
}
async function auditSchema(): Promise<Violation[]> {
const violations: Violation[] = [];
// Get schema metadata
const tables = await prisma.$queryRaw<any[]>`
SELECT
table_name,
column_name,
data_type,
is_nullable,
column_default
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, ordinal_position
`;
// Check 1: Naming conventions
tables.forEach((col) => {
// Table naming
if (!/^[A-Z][a-zA-Z0-9]*$/.test(col.table_name)) {
violations.push({
severity: "warning",
category: "naming",
table: col.table_name,
message: `Table name '${col.table_name}' doesn't follow PascalCase convention`,
recommendation: `Rename to PascalCase (e.g., 'UserProfile', 'OrderItem')`,
});
}
// Column naming
if (!/^[a-z][a-zA-Z0-9]*$/.test(col.column_name)) {
violations.push({
severity: "warning",
category: "naming",
table: col.table_name,
column: col.column_name,
message: `Column '${col.column_name}' doesn't follow camelCase convention`,
recommendation: `Rename to camelCase (e.g., 'firstName', 'createdAt')`,
});
}
});
// Check 2: Missing NOT NULL on critical fields
const criticalFields = [
"email",
"name",
"user_id",
"created_at",
"updated_at",
];
tables.forEach((col) => {
if (
criticalFields.some((f) => col.column_name.includes(f)) &&
col.is_nullable === "YES"
) {
violations.push({
severity: "error",
category: "nullability",
table: col.table_name,
column: col.column_name,
message: `Critical field '${col.column_name}' allows NULL`,
recommendation: `Add NOT NULL constraint`,
});
}
});
// Check 3: Type consistency for IDs
const idTypes = new Map<string, string>();
tables.forEach((col) => {
if (col.column_name === "id") {
idTypes.set(col.table_name, col.data_type);
}
});
const primaryIdType = Array.from(idTypes.values())[0];
idTypes.forEach((type, table) => {
if (type !== primaryIdType) {
violations.push({
severity: "error",
category: "type-consistency",
table,
column: "id",
message: `ID type '${type}' inconsistent with primary type '${primaryIdType}'`,
recommendation: `Standardize all IDs to ${primaryIdType}`,
});
}
});
// Check 4: Missing indexes on foreign keys
const foreignKeys = await prisma.$queryRaw<any[]>`
SELECT
tc.table_name,
kcu.column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
ON tc.constraint_name = kcu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
`;
const indexes = await prisma.$queryRaw<any[]>`
SELECT
tablename,
indexname,
indexdef
FROM pg_indexes
WHERE schemaname = 'public'
`;
foreignKeys.forEach((fk) => {
const hasIndex = indexes.some(
(idx) =>
idx.tablename === fk.table_name && idx.indexdef.includes(fk.column_name)
);
if (!hasIndex) {
violations.push({
severity: "warning",
category: "performance",
table: fk.table_name,
column: fk.column_name,
message: `Foreign key '${fk.column_name}' has no index`,
recommendation: `CREATE INDEX idx_${fk.table_name}_${fk.column_name} ON "${fk.table_name}"("${fk.column_name}")`,
});
}
});
// Check 5: Missing timestamps
const tablesGrouped = tables.reduce((acc, col) => {
if (!acc[col.table_name]) acc[col.table_name] = [];
acc[col.table_name].push(col.column_name);
return acc;
}, {} as Record<string, string[]>);
Object.entries(tablesGrouped).forEach(([table, columns]) => {
if (!columns.includes("created_at")) {
violations.push({
severity: "info",
category: "audit",
table,
message: `Table missing 'created_at' timestamp`,
recommendation: `Add: created_at TIMESTAMP NOT NULL DEFAULT NOW()`,
});
}
if (!columns.includes("updated_at") && !columns.includes("updatedAt")) {
violations.push({
severity: "info",
category: "audit",
table,
message: `Table missing 'updated_at' timestamp`,
recommendation: `Add: updated_at TIMESTAMP NOT NULL DEFAULT NOW()`,
});
}
});
return violations;
}
// Generate report
async function generateReport() {
const violations = await auditSchema();
console.log("📊 Schema Audit Report\n");
console.log(`Total violations: ${violations.length}\n`);
// Group by severity
const grouped = violations.reduce((acc, v) => {
if (!acc[v.severity]) acc[v.severity] = [];
acc[v.severity].push(v);
return acc;
}, {} as Record<string, Violation[]>);
// Print by severity
(["error", "warning", "info"] as const).forEach((severity) => {
const items = grouped[severity] || [];
if (items.length === 0) return;
console.log(
`\n${
{ error: "❌ Errors", warning: "⚠️ Warnings", info: "ℹ️ Info" }[
severity
]
} (${items.length})\n`
);
items.forEach((v, i) => {
console.log(
`${i + 1}. [${v.category}] ${v.table}${v.column ? `.${v.column}` : ""}`
);
console.log(` Message: ${v.message}`);
console.log(` Fix: ${v.recommendation}\n`);
});
});
// Exit code based on errors
process.exit(grouped.error?.length > 0 ? 1 : 0);
}
generateReport();
```
## Recommended Schema Standards
```prisma
// schema.prisma with best practices
model User {
// 1. ID: Consistent type (Int or String/cuid)
id Int @id @default(autoincrement())
// 2. Critical fields: NOT NULL
email String @unique
name String
// 3. Optional fields: Clearly nullable
phone String?
bio String?
// 4. Audit timestamps: Always include
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
// 5. Relations: Proper foreign keys
orders Order[]
// 6. Indexes: On frequently queried fields
@@index([email])
@@index([createdAt])
}
model Order {
id Int @id @default(autoincrement())
// Foreign key with clear naming
userId Int
user User @relation(fields: [userId], references: [id])
// Enum for status (type safety)
status OrderStatus @default(PENDING)
// Decimal for money
total Decimal @db.Decimal(10, 2)
// Timestamps
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
// Indexes on foreign keys
@@index([userId])
@@index([status])
@@index([createdAt])
}
enum OrderStatus {
PENDING
PAID
SHIPPED
DELIVERED
CANCELLED
}
```
## Auto-fix Migrations
```typescript
// scripts/fix-schema.ts
async function generateFixMigrations(violations: Violation[]) {
const migrations: string[] = [];
violations.forEach((v) => {
if (v.category === "nullability" && v.column) {
migrations.push(
`ALTER TABLE "${v.table}" ALTER COLUMN "${v.column}" SET NOT NULL;`
);
}
if (
v.category === "performance" &&
v.recommendation.startsWith("CREATE INDEX")
) {
migrations.push(v.recommendation + ";");
}
if (v.category === "audit" && v.message.includes("created_at")) {
migrations.push(
`ALTER TABLE "${v.table}" ADD COLUMN "created_at" TIMESTAMP NOT NULL DEFAULT NOW();`
);
}
});
console.log("-- Auto-generated fixes\n");
migrations.forEach((m) => console.log(m));
}
```
## Best Practices
1. **Run regularly**: Weekly schema audits
2. **Enforce in CI**: Fail builds on errors
3. **Document standards**: Team agreement on conventions
4. **Gradual adoption**: Fix incrementally
5. **Use enums**: For status fields
6. **Always timestamp**: created_at and updated_at
7. **Index foreign keys**: Performance best practice
## Output Checklist
- [ ] Naming violations report
- [ ] Type consistency checks
- [ ] Nullability issues identified
- [ ] Missing constraints flagged
- [ ] Performance issues (missing indexes)
- [ ] Recommended fixes generated
- [ ] Auto-fix migrations provided
- [ ] Schema standards documented
© patricio0312rev · MIT · patricio0312rev/skills