cotera

Supabase Data Sync Agent

Keep your Supabase tables in sync without writing migration scripts. Read from a source, diff against the target, and apply inserts, updates, and deletes automatically.

Data synchronizationRecord diffingUpsert operationsStale data removal

The Challenge

You have data living in one Supabase table that needs to stay current in another. Maybe it's a staging-to-production sync, a denormalized cache table, or an external feed that lands in a raw table before being cleaned and moved. Every time the source changes you have to write a script, diff the rows, handle conflicts, and pray you don't accidentally delete something that should have stayed. It's tedious, error-prone, and nobody wants to own the sync job.

What This Prompt Does

Read Source

Pulls all records from the source table with optional filters for date ranges or active status

Diff Records

Compares source against target by key columns and classifies each row as new, changed, unchanged, or stale

Upsert Changes

Inserts new rows and updates changed ones using conflict resolution on the primary key

Remove Stale

Deletes or soft-deletes records that no longer exist in the source, with confirmation before proceeding

The Prompt

The Prompt

Task

Use @Supabase/Read RecordsName it "Supabase/Read Records" and call it with @Supabase/Read Records to pull data from a source table, compare it against a target table or external dataset, then @Supabase/Upsert RecordName it "Supabase/Upsert Record" and call it with @Supabase/Upsert Record to insert new rows and update changed ones, and @Supabase/Delete RecordName it "Supabase/Delete Record" and call it with @Supabase/Delete Record to remove entries that no longer exist in the source.

Input

The user provides:

  1. The source table name (or external data context)
  2. The target table name in Supabase
  3. The key column(s) used to match records between source and target
  4. Which columns to compare for changes (or "all" for every column)
  5. Whether to delete stale records or just flag them

Example: "Sync the products table from my staging database into the production products table. Match on sku, compare price and inventory_count, and delete products that no longer exist in staging."

Context

Read Source Data

  1. Use @Supabase/Read RecordsName it "Supabase/Read Records" and call it with @Supabase/Read Records to pull all records from the source table
  2. Apply any filters the user specifies (e.g., only active records, records modified after a date)
  3. Select only the columns needed for comparison to keep payloads small
  4. Page through results if the table is large using limit and offset

Read Target Data

  1. Use @Supabase/Read RecordsName it "Supabase/Read Records" and call it with @Supabase/Read Records to pull the matching records from the target table
  2. Use the same key column(s) to enable comparison
  3. Build a lookup map of existing target records by their key values

Compare and Classify

For each source record, classify it as:

  • New — key does not exist in target, needs insert
  • Changed — key exists but one or more comparison columns differ, needs update
  • Unchanged — key exists and all comparison columns match, skip

For each target record, classify it as:

  • Stale — key does not exist in source, candidate for deletion

Apply Changes

  1. Use @Supabase/Upsert RecordName it "Supabase/Upsert Record" and call it with @Supabase/Upsert Record for all new and changed records
  2. Include the conflict resolution column (usually the primary key or unique constraint) so upserts work correctly
  3. Use @Supabase/Delete RecordName it "Supabase/Delete Record" and call it with @Supabase/Delete Record for stale entries if the user opted for hard deletion
  4. If the user prefers soft deletion, use @Supabase/Upsert RecordName it "Supabase/Upsert Record" and call it with @Supabase/Upsert Record to set a deleted_at timestamp or is_active flag instead
  5. Process changes in batches and report progress after each batch

Safety Guidelines

  • Always show a summary of changes before applying them (X new, Y updated, Z to delete)
  • Never delete records without explicit user confirmation
  • Log each operation so the user can audit what changed
  • If the sync would delete more than 20% of target records, warn the user before proceeding

Output

Sync Summary:

Source Records: [count] Target Records (before sync): [count]

Changes Applied: | Action | Count | Details | |--------|-------|---------| | Inserted | [n] | New records not in target | | Updated | [n] | Records with changed values | | Deleted | [n] | Stale records removed | | Unchanged | [n] | Records already in sync |

Sample Changes:

  • Inserted: [sample key values]
  • Updated: [sample key values with changed columns]
  • Deleted: [sample key values]

Post-Sync Target Count: [count]

Example Usage

Try asking:

  • →"Sync the raw_products table into products. Match on sku, compare name and price, and delete anything in products that is not in raw_products."
  • →"Compare my staging_users table with production_users by email. Upsert any rows where name or role changed, but do not delete anything."
  • →"Read all records from the inventory_feed table and upsert them into inventory using product_id as the key. Soft-delete any inventory rows missing from the feed by setting is_active to false."