Skip to main content
skill CX Codex

data-integrity-auditor — Claude Code skill for data audits

data-integrity-auditor is a Claude Code skill that finds database integrity problems and suggests fixes. It responds to requests about orphaned rows, broken relationships, and data quality.

MIT 306 installs ★ 64 updated 2026-01-12

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

What data-integrity-auditor does

The skill checks for rows without matching records, broken foreign keys, constraint violations, and missing required fields. Its examples cover duplicate emails, negative prices and stock values, and invalid order statuses. The TypeScript scripts use Prisma and SQL queries.

An audit report groups findings by category and severity, with affected-record counts and possible SQL fixes. Some examples suggest deleting records or updating prices. The foreign-key checks shown in the examples cap each query at 100 rows.

What it helps with

  • find orders whose users no longer exist
  • check references between orders and their items
  • flag duplicate emails and negative prices
  • find records missing required fields

Example prompts for the agent

  • Check the database for orders without users and other broken references.
  • Find duplicate emails and products with negative prices or stock.
  • Create a data-integrity report and suggest SQL fixes.

How to install data-integrity-auditor

1Skills CLI (Vercel)

npx skills add patricio0312rev/skills --skill data-integrity-auditor -a claude-code

2Manually: put the files in place

~/.claude/skills/data-integrity-auditor/SKILL.md

Copy the whole skill folder; for one project use .claude/skills/data-integrity-auditor/

Step by step, with commands for each platform: How to install skills in Claude Code: five ways

Questions about data-integrity-auditor

How do I install data-integrity-auditor in Claude Code?

Run npx skills add patricio0312rev/skills --skill data-integrity-auditor -a claude-code. The skill file is located at ~/.claude/skills/data-integrity-auditor/SKILL.md.

What does data-integrity-auditor do?

It checks for missing relationships, constraint problems, and required fields, then produces a report with potential fixes.

Which problems can data-integrity-auditor detect?

Examples include orphaned rows, broken foreign keys, duplicate emails, negative prices or stock, invalid order statuses, and missing required values.

Source text

SKILL.md · MIT
---
name: data-integrity-auditor
description: Detects data integrity issues including orphaned records, broken foreign key relationships, constraint violations, and provides automated fix migrations. Use for "data integrity", "orphaned records", "broken relationships", or "data quality".
---

# Data Integrity Auditor

Detect and fix data integrity issues automatically.

## Integrity Check Types

### 1. Orphaned Records

```sql
-- 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

```typescript
// 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

```typescript
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

```typescript
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

```typescript
// 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

```typescript
// 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

```sql
-- 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

```typescript
// 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: "[email protected]", name: "Test" },
    });

    await expect(
      prisma.user.create({
        data: { email: "[email protected]", name: "Test 2" },
      })
    ).rejects.toThrow("Unique constraint");
  });
});
```

## Monitoring Dashboard

```typescript
// 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

1. **Add constraints**: Prevent issues at database level
2. **Regular audits**: Weekly integrity checks
3. **Automated fixes**: Safe, reversible repairs
4. **Monitor metrics**: Track data quality over time
5. **Test constraints**: Ensure they work
6. **Soft deletes**: Easier recovery
7. **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

© patricio0312rev · MIT · patricio0312rev/skills

Similar skills

All skills for Data and databases →

From the same set148

The whole set →
readme-generator readme-generator generates structured README.md files with status badges, setup steps, API tables, and contribution guides. Useful when documenting a new repository. CX Codex +1 313installs cypress-playwright-setup cypress-playwright-setup — a skill for Claude Code, Codex, Cursor, and other agents that sets up E2E testing with Cypress or Playwright: page objects, fixtures, and CI integration. CX Codex +1 313installs page-layout-builder page-layout-builder builds page shells for dashboards, auth, settings, and CRUD flows. Use it when a new screen needs routes, navigation, and state placeholders. CX Codex +1 312installs curl-command-generator curl-command-generator turns Express, Next.js or Fastify route definitions into cURL commands you can paste into a terminal, auth headers and JSON bodies included. CX Codex +1 311installs queue-job-processor queue-job-processor sets up background job processing with BullMQ and Redis, covering typed queues, worker concurrency, and retry strategies. CX Codex +1 300installs api-mock-server api-mock-server configures mock API servers for testing and frontend development using MSW, json-server, or custom handlers. CX Codex +1 300installs guardrails-safety-filter-builder guardrails-safety-filter-builder builds a safety layer for an LLM app: masks personal data, catches prompt injection attempts and returns a refusal when a topic is off-policy. CX Codex +1 299installs sql-query-optimizer The sql-query-optimizer skill inspects SQL execution plans, suggests indexes, and rewrites queries. Use it when slow database queries need a concrete diagnosis. CX Codex +1 297installs

Project sponsors

Companies that keep this analytics open