schema-consistency-checker

What this skill does

# 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.len