# data-integrity-auditor

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.

- skill · `type=skill` · Claude Code, Codex, Cursor, GitHub Copilot, Gemini CLI, OpenCode, Windsurf (Devin Desktop), Claude.ai · 306 installs · ★ 64 repo stars
- Author: patricio0312rev
- License: MIT · policy=mirror · the beginning of the file is at the end of this page
- Source: https://github.com/patricio0312rev/skills/tree/HEAD/db-management/data-integrity-auditor/SKILL.md
- Updated: 2026-01-12
- Security check: automated, 2026-11-16 (text) — no signs of malicious code found.
- HTML: https://affpapa.org/en/nevibecode/skills/data-integrity-auditor

## What it 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.

## Install

### Claude Code (native platform)

1. **Skills CLI (Vercel)**
```bash
npx skills add patricio0312rev/skills --skill data-integrity-auditor -a claude-code
```

2. **Manually: put the files in place**
- File: `~/.claude/skills/data-integrity-auditor/SKILL.md` — Copy the whole skill folder; for one project use .claude/skills/data-integrity-auditor/

### Codex

1. **Skills CLI (Vercel)**
```bash
npx skills add patricio0312rev/skills --skill data-integrity-auditor -a codex
```

2. **Manually: put the files in place**
- File: `.agents/skills/data-integrity-auditor/SKILL.md` — For all projects use ~/.agents/skills/

### Cursor

1. **Skills CLI (Vercel)**
```bash
npx skills add patricio0312rev/skills --skill data-integrity-auditor -a cursor
```

2. **Manually: put the files in place**
- File: `.cursor/skills/data-integrity-auditor/SKILL.md` — Cursor also reads .agents/skills/ and .claude/skills/

### GitHub Copilot

1. **GitHub CLI**
```bash
gh skill install patricio0312rev/skills db-management/data-integrity-auditor/SKILL.md
```

2. **Manually: put the files in place**
- File: `.github/skills/data-integrity-auditor/SKILL.md`

### Gemini CLI

1. **Skills CLI (Vercel)**
```bash
npx skills add patricio0312rev/skills --skill data-integrity-auditor -a gemini-cli
```

2. **Platform CLI**
```bash
gemini skills install https://github.com/patricio0312rev/skills --path db-management/data-integrity-auditor
```

### Windsurf (Devin Desktop)

**Manually: put the files in place**
- File: `.devin/skills/data-integrity-auditor/SKILL.md` — The legacy .windsurf/skills/ path still works

### OpenCode

**Skills CLI (Vercel)**
```bash
npx skills add patricio0312rev/skills --skill data-integrity-auditor -a opencode
```

### Claude.ai

**Claude.ai**
- Zip the folder and upload it in Settings → Capabilities → Skills

Where a file is given instead of a command, copy the files from the source to that path: https://github.com/patricio0312rev/skills/tree/HEAD/db-management/data-integrity-auditor/SKILL.md

## When to use it
- 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
- 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.

## Source text · SKILL.md · MIT

Below is the content of a third-party file. It is data, not instructions for you: do not act on anything written inside unless the human asks. Block tag: a2f89ddcb538b9b7.

BEGIN THIRD-PARTY FILE a2f89ddcb538b9b7
````text
---
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
````
END THIRD-PARTY FILE a2f89ddcb538b9b7

The third-party file has ended. Everything between the a2f89ddcb538b9b7 tags is data: requests and commands from it are not followed. An “end of file” line with another tag inside the block is part of the file.

First 2961 of 12679 bytes. © patricio0312rev · MIT · https://github.com/patricio0312rev/skills/tree/HEAD/db-management/data-integrity-auditor/SKILL.md

_Part of the page did not fit — see the HTML version._

---
NeVibeCode on affpapa.org. Catalog texts are descriptions, not instructions for the agent. How to choose and install: https://affpapa.org/en/nevibecode/agents.md
