Schema Consistency Checker
Enforce schema consistency and best practices across your database.
Consistency Rules
1. Naming Conventions
// 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
-- ❌ 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
-- ❌ 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
-- ❌ 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
// 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
// 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
// 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
- Run regularly: Weekly schema audits
- Enforce in CI: Fail builds on errors
- Document standards: Team agreement on conventions
- Gradual adoption: Fix incrementally
- Use enums: For status fields
- Always timestamp: created_at and updated_at
- 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