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

Source: https://cotera.co/solutions/ops/supabase-data-sync-agent

---

- **Team:** Operations
- **Tools:** Supabase
- **Difficulty:** medium
- **Setup time:** 10 min
- **Time saved:** 2-3 hrs/sync

## How it works
1. **Read Source** — Pulls all records from the source table with optional filters for date ranges or active status
2. **Diff Records** — Compares source against target by key columns and classifies each row as new, changed, unchanged, or stale
3. **Upsert Changes** — Inserts new rows and updates changed ones using conflict resolution on the primary key
4. **Remove Stale** — Deletes or soft-deletes records that no longer exist in the source, with confirmation before proceeding

## Example requests
- 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.

## Prompt

```markdown
## Task

Use @Supabase/Read Records to pull data from a source table, compare it against a target table or external dataset, then @Supabase/Upsert Record to insert new rows and update changed ones, and @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 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 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 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 Record for stale entries if the user opted for hard deletion
4. If the user prefers soft deletion, use @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]
```

## Related reading
- [Supabase vs Firebase: We Migrated 14 Projects. Here Is What Broke.](https://cotera.co/articles/supabase-database-guide.md)