lc-orphaned-records

name	lc:orphaned-records
description	Find orphaned database records where foreign key parent records don't exist.
argument-hint	[analyze | fix | fix --dry-run | table-name | fix table-name]
user-invocable	true
allowed-tools	Read Grep Bash Edit Write Glob

Find Orphaned Records

Detect orphaned database records where a foreign key column references a parent record that no longer exists. This runs actual queries against the database via Docker.

Subcommands

Subcommand	Description
(no argument)	Scan all tables for orphaned records. Read-only report.
fix	Find and delete or nullify orphaned records. Asks for confirmation before each table.
fix --dry-run	Show what cleanup queries would run without executing them.
[table-name]	Analyze orphaned records in a specific table only.
fix [table-name]	Fix orphaned records in a specific table, with confirmation.

Process

Step 1: Detect Docker Container

1. Use Read to examine docker-compose.yml in the project root.
2. Identify the PHP/application service container name (look for container_name or derive from service name).
3. Verify the container is running using Bash:

   docker ps --filter name={container_name} --format '{{.Names}}'
   

4. If not running, inform the user and suggest docker compose up -d.

Step 2: Identify Foreign Key Relationships

If a [table-name] argument is provided, only analyze that table. Otherwise, analyze all tables.

Method A: From Migrations (primary)

1. Use Glob to find all database/migrations/*.php files.
2. Use Grep and Read to parse foreign key definitions:
   • $table->foreign('column')->references('ref_column')->on('ref_table')
   • $table->foreignId('ref_table_id')->constrained()
   • $table->foreignId('column')->constrained('ref_table')
   • $table->foreignIdFor(Model::class)
3. Build a list of FK relationships: {table}.{column} -> {ref_table}.{ref_column}

Method B: From Database (supplementary) If migrations are complex or unclear, query the database directly:

docker exec {container} php artisan tinker --execute="
    \$results = DB::select('
        SELECT TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
        FROM information_schema.KEY_COLUMN_USAGE
        WHERE REFERENCED_TABLE_NAME IS NOT NULL
        AND TABLE_SCHEMA = DB::getDatabaseName()
    ');
    foreach (\$results as \$r) {
        echo \$r->TABLE_NAME . '.' . \$r->COLUMN_NAME . ' -> ' . \$r->REFERENCED_TABLE_NAME . '.' . \$r->REFERENCED_COLUMN_NAME . PHP_EOL;
    }
"

Step 3: Check for Orphaned Records

For each FK relationship, run a query to find records where the parent doesn't exist:

docker exec {container} php artisan tinker --execute="
    \$count = DB::table('{table}')
        ->whereNotNull('{column}')
        ->whereNotIn('{column}', DB::table('{ref_table}')->select('{ref_column}'))
        ->count();
    echo '{table}.{column} -> {ref_table}.{ref_column}: ' . \$count . ' orphans';
"

For tables with many records, use a LEFT JOIN approach for better performance:

SELECT COUNT(*) FROM {table} t
LEFT JOIN {ref_table} r ON t.{column} = r.{ref_column}
WHERE t.{column} IS NOT NULL AND r.{ref_column} IS NULL

Step 4: Get Sample Data

For each relationship with orphaned records, fetch sample IDs and values:

docker exec {container} php artisan tinker --execute="
    \$samples = DB::table('{table}')
        ->select('id', '{column}')
        ->whereNotNull('{column}')
        ->whereNotIn('{column}', DB::table('{ref_table}')->select('{ref_column}'))
        ->limit(5)
        ->get();
    foreach (\$samples as \$s) {
        echo 'ID: ' . \$s->id . ' | {column}: ' . \$s->{column} . PHP_EOL;
    }
"

Step 5: Check Soft Deletes

Before reporting orphans, check if the referenced table uses soft deletes:

1. Use Grep to check if the referenced model uses SoftDeletes trait.
2. If it does, also check if the "missing" parent records exist but are soft-deleted:

   SELECT COUNT(*) FROM {table} t
   LEFT JOIN {ref_table} r ON t.{column} = r.{ref_column}
   WHERE t.{column} IS NOT NULL AND r.{ref_column} IS NULL
   

   vs.

   SELECT COUNT(*) FROM {table} t
   INNER JOIN {ref_table} r ON t.{column} = r.{ref_column} AND r.deleted_at IS NOT NULL
   WHERE t.{column} IS NOT NULL
   

3. Distinguish between truly orphaned records and records pointing to soft-deleted parents.

Step 6: Report Findings

Present findings in a structured format:

ORPHANED RECORDS REPORT
========================

Table: operations
-----------------
Column: client_seller_id -> clients.id
  Orphaned records: 12
  Soft-deleted parents: 3 (these have deleted parent records)
  Truly orphaned: 9 (parent record does not exist at all)
  Sample IDs: 45, 67, 89, 102, 115
  FK onDelete action: SET NULL

Column: property_id -> properties.id
  Orphaned records: 0 (clean)

Table: activities
-----------------
Column: activitable_id (polymorphic)
  Note: Polymorphic relationships cannot be checked via FK constraints.
  Skipped -- use manual inspection if needed.

Fix Mode (fix or fix [table-name])

For each table with orphaned records, offer the appropriate fix based on FK's onDelete action and column nullability:

Option A: Delete Orphaned Records

For non-nullable FK columns or when records are meaningless without the parent:

DB::table('{table}')
    ->whereNotNull('{column}')
    ->whereNotIn('{column}', DB::table('{ref_table}')->select('{ref_column}'))
    ->delete();

Option B: Set to NULL (if column is nullable)

For nullable FK columns where the record is still meaningful:

DB::table('{table}')
    ->whereNotNull('{column}')
    ->whereNotIn('{column}', DB::table('{ref_table}')->select('{ref_column}'))
    ->update(['{column}' => null]);

Option C: Generate a Cleanup Migration

For production-safe approach, create a migration file:

public function up()
{
    DB::statement('DELETE FROM {table} WHERE {column} NOT IN (SELECT {ref_column} FROM {ref_table}) AND {column} IS NOT NULL');
}

Each fix requires explicit user confirmation before executing. The user chooses which option (A, B, or C) for each table.

Dry Run Mode (fix --dry-run)

Show exactly what queries would be executed and how many records would be affected, without running any destructive queries. The count queries still run to show impact.

Summary Table

End with a summary:

SUMMARY
========
Tables checked:          35
FK relationships checked: 62
Tables with orphans:     4
Total orphaned records:  47
Soft-deleted parents:    8
Truly orphaned:          39
Polymorphic (skipped):   5

Important Notes

• This skill runs actual database queries. It should be used in development/staging environments.
• Polymorphic relationships (morphTo, morphMany) cannot be checked via FK constraints since they use type+id columns. These are flagged but skipped.
• Large tables may take time to scan. For tables with millions of rows, the LEFT JOIN approach is used for better performance.
• Always check for soft deletes before suggesting deletion of "orphaned" records.
• Never execute destructive queries without explicit user confirmation.