Skip to main content
skill CX Codex

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

Install into Claude Code · Skills CLI (Vercel) All methods and platforms ↓
npx skills add patricio0312rev/skills --skill schema-consistency-checker -a claude-code
Open on GitHub

What 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 id uses a different type
  • list foreign keys that have no index behind them
  • catch nullable critical fields such as email or created_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-code

2Manually: put the files in place

~/.claude/skills/schema-consistency-checker/SKILL.md

Copy the whole skill folder; for one project use .claude/skills/schema-consistency-checker/

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

Similar skills

All skills for Data and databases →
design-database-schema BuildWithClaude Community design-database-schema — a command for Claude Code and Cursor that designs database schemas with indexes, constraints, and relationships. Triggered by an explicit request for a schema or ER model. CX Codex +1 ★ 3,785repo stars database-schema alinaqi database-schema is a Claude Code skill that checks database schemas before queries or model changes. It activates before database work and around schema, migration, and model files. CX Codex +1 308installs database-schema-designer borghei database-schema-designer — a Claude Code skill that turns product requirements into a normalized relational schema with tables, ORM models, RLS, indexes and migrations. Works across six platforms, including Codex, Cursor and Gemini CLI. CX Codex +1 183installs data-schema-knowledge-modeling lyndonkl The data-schema-knowledge-modeling skill builds rigorous models of entities, relationships, and constraints for databases, knowledge graphs, and ontologies. Use it to formalize your data structure before development. CX Codex +1 159installs schema-designer rohitg00 schema-designer — a Claude Code plugin that generates entity-relationship diagrams (ERD) and designs database schemas. Creates visual representations of tables, columns, and relationships directly in the editor. ★ 2,858repo stars database First Fluke Enforces database schema design, normalization, indexing, and migration standards for SQL, NoSQL, and vector stores. Activates on .sql and .prisma files. CX Codex +1 ★ 1,472repo stars

From the same set148

The whole set →
docs-starter-kit docs-starter-kit generates documentation templates for open-source and internal codebases, including README, CONTRIBUTING, and security policies. CX Codex +1 265installs prompt-regression-tester prompt-regression-tester compares prompt revisions across structured test suites, detects regressions, and drafts targeted fixes. CX Codex +1 265installs release-automation-builder release-automation-builder is a Claude Code skill for automating releases and package publishing. It responds to requests about versioning, changelogs, or release workflows. CX Codex +1 265installs postmortem-writer postmortem-writer turns an incident into a written postmortem: timeline, root cause, contributing factors, action items with owners. Runs as a skill inside your coding agent. CX Codex +1 265installs deployment-checklist-generator deployment-checklist-generator generates deployment runbooks, pre-release checklists, smoke-test scripts, and post-launch verification plans. CX Codex +1 264installs api-versioning-deprecation-planner The api-versioning-deprecation-planner is a Claude Code skill for planning API version changes and client migrations. It covers releases of a new version while existing clients still rely on the old one. CX Codex +1 264installs state-ux-flow-builder The state-ux-flow-builder skill structures loading, error, empty, and success states for data-driven screens. Use it when a UI flow has several views or state transitions. CX Codex +1 264installs zustand-state-builder zustand-state-builder — a skill for managing app state with Zustand: minimal API, full TypeScript support, persistence, and devtools. Runs on Claude Code, Codex, Cursor, GitHub Copilot, Gemini CLI, and OpenCode. CX Codex +1 264installs

Project sponsors

Companies that keep this analytics open