cotera

Supabase Database Cleanup Agent

Find duplicates, orphaned rows, and stale records hiding in your Supabase tables. Get a cleanup report with specific fixes, then let the agent apply them safely.

Data hygieneDuplicate removalOrphan detectionStale record cleanup

The Challenge

Your Supabase database has been accumulating junk for months. Duplicate rows from race conditions, orphaned records left behind when parent rows were deleted, and old entries that nobody has touched since last year. You know the data is messy but cleaning it up means writing SQL queries, cross-referencing foreign keys, and manually deciding which duplicates to keep. So the problem compounds while you work around it.

What This Prompt Does

Scan for Issues

Reads the table and identifies duplicates, orphaned foreign keys, and records past their expiration date

Classify Records

Groups problem records by type and severity so you can prioritize what to fix first

Soft-Delete or Flag

Updates records with archived flags or deleted_at timestamps for reversible cleanup

Hard-Delete Junk

Permanently removes confirmed garbage data after you approve the batch

The Prompt

The Prompt

Task

Use @Supabase/Read RecordsName it "Supabase/Read Records" and call it with @Supabase/Read Records to find records matching cleanup criteria (old, orphaned, duplicate), @Supabase/Update RecordName it "Supabase/Update Record" and call it with @Supabase/Update Record to soft-delete or flag records for review, and @Supabase/Delete RecordName it "Supabase/Delete Record" and call it with @Supabase/Delete Record to permanently remove confirmed junk data.

Input

The user provides:

  1. The Supabase table name to clean up
  2. The type of cleanup to run: duplicates, stale records, orphaned records, or all
  3. Criteria for each cleanup type (e.g., "older than 90 days", "duplicate email", "no matching parent record")
  4. Whether to soft-delete (flag) or hard-delete matched records

Example: "Clean up the events table. Delete any records older than 180 days, soft-delete duplicates by keeping the most recent, and flag orphaned records where user_id does not match any row in the users table."

Context

Duplicate Detection

  1. Use @Supabase/Read RecordsName it "Supabase/Read Records" and call it with @Supabase/Read Records to pull records and identify groups that share the same value in a key column (e.g., email, name, external_id)
  2. For each duplicate group, determine which record to keep based on:
    • Most recently updated
    • Most complete (fewest null fields)
    • Highest ID (most recent creation)
  3. Flag duplicates for review or delete on confirmation

Stale Record Detection

  1. Use @Supabase/Read RecordsName it "Supabase/Read Records" and call it with @Supabase/Read Records with filters on updated_at or created_at to find records older than the threshold
  2. Group stale records by age bracket (90-180 days, 180-365 days, 365+ days)
  3. Check if stale records are referenced by other tables before recommending deletion

Orphaned Record Detection

  1. Use @Supabase/Read RecordsName it "Supabase/Read Records" and call it with @Supabase/Read Records to pull records from the target table
  2. Use @Supabase/Read RecordsName it "Supabase/Read Records" and call it with @Supabase/Read Records on the parent table to get valid foreign key values
  3. Compare to find records in the target table whose foreign key does not match any parent record
  4. These orphaned records are safe to remove since their parent no longer exists

Applying Cleanup

  • Use @Supabase/Update RecordName it "Supabase/Update Record" and call it with @Supabase/Update Record to set a deleted_at timestamp, is_archived flag, or status column for soft-deletes
  • Use @Supabase/Delete RecordName it "Supabase/Delete Record" and call it with @Supabase/Delete Record for confirmed hard-deletes after user approval
  • Always show the user a preview of what will be affected before making changes
  • Process deletions in batches and report progress

Safety Guidelines

  • Never hard-delete without explicit user confirmation
  • Show record counts and sample data before any destructive action
  • If more than 50 records would be deleted, require batch confirmation
  • Keep a running log of every record modified or removed

Output

Cleanup Report:

Table: [table_name] Total Records Scanned: [count]

Issues Found: | Issue Type | Count | Action Taken | |------------|-------|-------------| | Duplicates | [n] | [soft-deleted / hard-deleted / flagged] | | Stale Records (90+ days) | [n] | [soft-deleted / hard-deleted / flagged] | | Orphaned Records | [n] | [soft-deleted / hard-deleted / flagged] |

Records Modified: [count] Records Deleted: [count] Records Flagged for Review: [count]

Sample Affected Records:

  • Duplicates: [sample key values and which was kept]
  • Stale: [sample records with last updated dates]
  • Orphaned: [sample records with missing parent references]

Example Usage

Try asking:

  • →"Find duplicate rows in the contacts table by email address. Keep the most recently updated record and soft-delete the rest."
  • →"Scan the order_items table for orphaned records where order_id does not exist in the orders table. Delete them."
  • →"Find all records in the sessions table older than 90 days and delete them in batches of 100."