Data Integrity Auditor

Detect and fix data integrity issues automatically.

Integrity Check Types

1. Orphaned Records

-- Find orphaned orders (no matching user) SELECT o.id, o.user_id FROM orders o LEFT JOIN users u ON u.id = o.user_id WHERE u.id IS NULL;

-- Find orphaned order items (no matching order) SELECT oi.id, oi.order_id FROM order_items oi LEFT JOIN orders o ON o.id = oi.order_id WHERE o.id IS NULL;

2. Broken Foreign Keys

// scripts/check-foreign-keys.ts async function checkForeignKeys() { const issues: string[] = [];

// Orders → Users const orphanedOrders = await prisma.$queryRaw<any[]> SELECT o.id, o.user_id FROM orders o LEFT JOIN users u ON u.id = o.user_id WHERE u.id IS NULL LIMIT 100 ;

if (orphanedOrders.length > 0) { issues.push( ❌ Found ${orphanedOrders.length} orders with invalid user_id ); console.log( " Sample IDs:", orphanedOrders.slice(0, 5).map((o) => o.id) ); }

// Order Items → Orders const orphanedItems = await prisma.$queryRaw<any[]> SELECT oi.id, oi.order_id FROM order_items oi LEFT JOIN orders o ON o.id = oi.order_id WHERE o.id IS NULL LIMIT 100 ;

if (orphanedItems.length > 0) { issues.push( ❌ Found ${orphanedItems.length} order items with invalid order_id ); }

// Products → Categories const orphanedProducts = await prisma.$queryRaw<any[]> SELECT p.id, p.category_id FROM products p LEFT JOIN categories c ON c.id = p.category_id WHERE p.category_id IS NOT NULL AND c.id IS NULL LIMIT 100 ;

if (orphanedProducts.length > 0) { issues.push( ❌ Found ${orphanedProducts.length} products with invalid category_id ); }

return issues; }

3. Constraint Violations

async function checkConstraints() { const issues: string[] = [];

// Check email uniqueness (should be caught by DB, but verify) const duplicateEmails = await prisma.$queryRaw<any[]> SELECT email, COUNT(*) as count FROM users GROUP BY email HAVING COUNT(*) > 1 ;

if (duplicateEmails.length > 0) { issues.push(❌ Found ${duplicateEmails.length} duplicate emails); }

// Check negative quantities const negativeStock = await prisma.$queryRaw<any[]> SELECT id, name, stock FROM products WHERE stock < 0 ;

if (negativeStock.length > 0) { issues.push( ❌ Found ${negativeStock.length} products with negative stock ); }

// Check negative prices const negativePrices = await prisma.$queryRaw<any[]> SELECT id, name, price FROM products WHERE price < 0 ;

if (negativePrices.length > 0) { issues.push( ❌ Found ${negativePrices.length} products with negative prices ); }

// Check invalid order status const invalidStatus = await prisma.$queryRaw<any[]> SELECT id, status FROM orders WHERE status NOT IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled') ;

if (invalidStatus.length > 0) { issues.push(❌ Found ${invalidStatus.length} orders with invalid status); }

return issues; }

4. Missing Required Fields

async function checkMissingFields() { const issues: string[] = [];

// Users missing required fields const usersNoEmail = await prisma.user.count({ where: { email: null }, });

if (usersNoEmail > 0) { issues.push(❌ Found ${usersNoEmail} users without email); }

// Orders with NULL totals const ordersNoTotal = await prisma.order.count({ where: { total: null }, });

if (ordersNoTotal > 0) { issues.push(❌ Found ${ordersNoTotal} orders without total); }

return issues; }

Comprehensive Audit Script

// scripts/audit-data-integrity.ts interface IntegrityIssue { severity: "critical" | "warning" | "info"; category: string; message: string; count: number; query?: string; fix?: string; }

async function auditDataIntegrity(): Promise<IntegrityIssue[]> { const issues: IntegrityIssue[] = [];

console.log("🔍 Auditing data integrity...\n");

// 1. Check orphaned records const orphanedOrders = await prisma.$queryRaw<any[]> SELECT COUNT(*) as count FROM orders o LEFT JOIN users u ON u.id = o.user_id WHERE u.id IS NULL ;

if (orphanedOrders[0].count > 0) { issues.push({ severity: "critical", category: "orphaned-records", message: "Orders with invalid user references", count: orphanedOrders[0].count, query: "SELECT id, user_id FROM orders o LEFT JOIN users u ON u.id = o.user_id WHERE u.id IS NULL", fix: "DELETE FROM orders WHERE id IN (...)", }); }

// 2. Check duplicate unique constraints const duplicateEmails = await prisma.$queryRaw<any[]> SELECT email, COUNT(*) as count FROM users GROUP BY email HAVING COUNT(*) > 1 ;

if (duplicateEmails.length > 0) { issues.push({ severity: "critical", category: "constraint-violation", message: "Duplicate email addresses", count: duplicateEmails.length, fix: "Keep newest record, delete duplicates", }); }

// 3. Check data inconsistencies const invalidPrices = await prisma.$queryRaw<any[]> SELECT COUNT(*) as count FROM products WHERE price < 0 ;

if (invalidPrices[0].count > 0) { issues.push({ severity: "warning", category: "data-quality", message: "Products with negative prices", count: invalidPrices[0].count, fix: "UPDATE products SET price = ABS(price) WHERE price < 0", }); }

// 4. Check referential integrity const brokenOrderItems = await prisma.$queryRaw<any[]> SELECT COUNT(*) as count FROM order_items oi LEFT JOIN orders o ON o.id = oi.order_id WHERE o.id IS NULL ;

if (brokenOrderItems[0].count > 0) { issues.push({ severity: "critical", category: "referential-integrity", message: "Order items referencing non-existent orders", count: brokenOrderItems[0].count, fix: "DELETE FROM order_items WHERE order_id NOT IN (SELECT id FROM orders)", }); }

return issues; }

async function generateReport() { const issues = await auditDataIntegrity();

console.log("\n📊 Data Integrity Report\n"); console.log(Total issues: ${issues.length}\n);

const grouped = issues.reduce((acc, issue) => { if (!acc[issue.severity]) acc[issue.severity] = []; acc[issue.severity].push(issue); return acc; }, {} as Record<string, IntegrityIssue[]>);

(["critical", "warning", "info"] as const).forEach((severity) => { const items = grouped[severity] || []; if (items.length === 0) return;

console.log(`\n${severity.toUpperCase()} (${items.length})\n`);

items.forEach((issue, i) => {
  console.log(`${i + 1}. [${issue.category}] ${issue.message}`);
  console.log(`   Count: ${issue.count}`);
  if (issue.query) {
    console.log(`   Query: ${issue.query.substring(0, 80)}...`);
  }
  if (issue.fix) {
    console.log(`   Fix: ${issue.fix}`);
  }
  console.log();
});

});

// Exit with error if critical issues process.exit(grouped.critical?.length > 0 ? 1 : 0); }

generateReport();

Automated Fixes

// scripts/fix-integrity-issues.ts async function fixOrphanedRecords() { console.log("🔧 Fixing orphaned records...\n");

// Delete orphaned orders const deletedOrders = await prisma.$executeRaw DELETE FROM orders WHERE id IN ( SELECT o.id FROM orders o LEFT JOIN users u ON u.id = o.user_id WHERE u.id IS NULL ) ; console.log(✅ Deleted ${deletedOrders} orphaned orders);

// Delete orphaned order items const deletedItems = await prisma.$executeRaw DELETE FROM order_items WHERE id IN ( SELECT oi.id FROM order_items oi LEFT JOIN orders o ON o.id = oi.order_id WHERE o.id IS NULL ) ; console.log(✅ Deleted ${deletedItems} orphaned order items); }

async function fixDuplicates() { console.log("🔧 Fixing duplicate records...\n");

// Keep newest user, delete old duplicates await prisma.$executeRaw DELETE FROM users WHERE id IN ( SELECT id FROM ( SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) as rn FROM users ) t WHERE rn > 1 ) ; console.log(✅ Fixed duplicate emails); }

async function fixConstraintViolations() { console.log("🔧 Fixing constraint violations...\n");

// Fix negative prices const fixedPrices = await prisma.$executeRaw UPDATE products SET price = ABS(price) WHERE price < 0 ; console.log(✅ Fixed ${fixedPrices} negative prices);

// Fix negative stock const fixedStock = await prisma.$executeRaw UPDATE products SET stock = 0 WHERE stock < 0 ; console.log(✅ Fixed ${fixedStock} negative stock values); }

Prevention: Add Missing Constraints

-- Migration to add missing constraints

-- 1. Add foreign key constraints ALTER TABLE orders ADD CONSTRAINT fk_orders_user_id FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

ALTER TABLE order_items ADD CONSTRAINT fk_order_items_order_id FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE;

-- 2. Add check constraints ALTER TABLE products ADD CONSTRAINT chk_products_price_positive CHECK (price >= 0);

ALTER TABLE products ADD CONSTRAINT chk_products_stock_non_negative CHECK (stock >= 0);

-- 3. Add unique constraints CREATE UNIQUE INDEX idx_users_email_unique ON users(LOWER(email));

-- 4. Add NOT NULL constraints ALTER TABLE users ALTER COLUMN email SET NOT NULL;

ALTER TABLE orders ALTER COLUMN total SET NOT NULL;

Automated Testing

// tests/data-integrity.test.ts describe("Data Integrity", () => { it("should not allow orphaned orders", async () => { // Try to create order with non-existent user await expect( prisma.order.create({ data: { userId: 99999, // Non-existent total: 100, status: "pending", }, }) ).rejects.toThrow("Foreign key constraint"); });

it("should not allow negative prices", async () => { await expect( prisma.product.create({ data: { name: "Test", price: -10, // Invalid stock: 100, }, }) ).rejects.toThrow("Check constraint"); });

it("should not allow duplicate emails", async () => { await prisma.user.create({ data: { email: "test@example.com", name: "Test" }, });

await expect(
  prisma.user.create({
    data: { email: "test@example.com", name: "Test 2" },
  })
).rejects.toThrow("Unique constraint");

}); });

Monitoring Dashboard

// Monitor data quality metrics async function getDataQualityMetrics() { return { orphanedOrders: await prisma.$queryRaw SELECT COUNT(*) FROM orders o LEFT JOIN users u ON u.id = o.user_id WHERE u.id IS NULL , duplicateEmails: await prisma.$queryRaw SELECT COUNT(*) FROM ( SELECT email FROM users GROUP BY email HAVING COUNT(*) > 1 ) t , invalidPrices: await prisma.$queryRaw SELECT COUNT(*) FROM products WHERE price < 0 , missingData: await prisma.$queryRaw SELECT SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) as users_no_email, SUM(CASE WHEN total IS NULL THEN 1 ELSE 0 END) as orders_no_total FROM users CROSS JOIN orders , }; }

Best Practices

• Add constraints: Prevent issues at database level

• Regular audits: Weekly integrity checks

• Automated fixes: Safe, reversible repairs

• Monitor metrics: Track data quality over time

• Test constraints: Ensure they work

• Soft deletes: Easier recovery

• Backup before fixes: Always

Output Checklist

• Orphaned record detection

• Foreign key integrity checks

• Constraint violation detection

• Missing field checks

• Automated audit script

• Fix scripts (with dry-run)

• Prevention migrations (add constraints)

• Automated tests

• Monitoring dashboard

• Regular audit schedule