schema-consistency-checker

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".

Ir para a instalação

Repositório: lichunboa/Ob-Al-brooks-PA
Última atividade na origem: 30 de janeiro de 2026 às 12:13
Idioma detectado do SKILL.md: inglês
Estrelas: 5
Forks: 1

Opções de instalação

Por padrão, está selecionado o prompt que primeiro revisa a origem. Você pode mudar para um comando direto ou baixar uma cópia local.

Instalar com Codex ou Claude Copie este prompt, cole no Codex, Claude ou outro assistente e deixe que ele revise a página da skill e instale para você.

Um comando direto ignora o prompt de revisão. Verifique a origem antes de executá-lo.

npx skills add https://github.com/lichunboa/Ob-Al-brooks-PA --skill schema-consistency-checker

O comando permanece em uma só linha. Role horizontalmente para revisá-lo antes de copiar.

Prefere uma cópia local? Baixe os arquivos disponíveis atualmente no SkillsMP.

Revise os arquivos de origem

Leia o SKILL.md e os arquivos complementares exibidos pelo SkillsMP antes de decidir se vai instalar.

SKILL.md

Instruções da origem · Visualização somente leitura

Voltar à pesquisa Voltar à instalação

-- ❌ 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)
);

-- ❌ 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()
);

-- ❌ 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()
);

// 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();

// 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
}

// 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));
}

schema-consistency-checker

Opções de instalação

Revise os arquivos de origem