# Delta sync — hosted Supabase → self-hosted (Datafy)

Move **only data created or changed on supabase.com after the initial migration**, including **new storage files (images)**. Do **not** re-run the full `data.sql` restore or bulk-copy buckets that are already complete.

> **Initial migration:** `docs/migrate-supabase-cloud-to-self-hosted.md`  
> **Environment setup:** `docs/supabase-aws-rds-proxy-setup.md`  
> **EC2 access:** `docs/connect-private-ec2-ssm.md`

---

## What this guide is for


| Scenario                                        | Use this doc?                                       |
| ----------------------------------------------- | --------------------------------------------------- |
| First-time migration (empty target)             | No — use `migrate-supabase-cloud-to-self-hosted.md` |
| Hosted project kept live; self-hosted is behind | **Yes**                                             |
| Re-copy everything from scratch                 | No — risks duplicates and long downtime             |


Supabase Storage has **two** parts that can drift independently:

1. **Files** in S3 (`datafy-prod-supabase-storage`)
2. **Metadata** in Postgres (`storage.objects`, `storage.buckets`)

A delta run must handle **both** for new uploads on hosted.

### Database scope — all tables, not just a short list

The delta covers **every user-data table** in these schemas:

| Schema | Included | Notes |
|--------|----------|-------|
| `public` | **Yes** | All app tables (`sjreport`, `task`, lookups, junction tables, etc.) |
| `auth` | **Yes** | `auth.users`, identities, sessions, etc. |
| `storage` | **Yes** | `storage.buckets`, `storage.objects` (metadata) |
| `extensions` | No | Extensions only — not data |
| `cron` | Usually no | Job definitions were migrated separately; only re-sync if you added jobs on hosted after cutoff |
| `realtime`, `supabase_functions`, `_realtime` | No | Runtime / platform — not in your app dump |

`sjreport`, `task`, and `auth.users` appear in examples below because they are large and were checked during the initial migration. **Step 1 discovers drift across all tables automatically** — you do not maintain a manual table list.

---



## Production values (Datafy)


| Setting                              | Value                                                                                                                      |
| ------------------------------------ | -------------------------------------------------------------------------------------------------------------------------- |
| Hosted project ref                   | `hantiuuvmjzvquwwkfor`                                                                                                     |
| Hosted DB (IPv4)                     | Session pooler — `postgresql://postgres.hantiuuvmjzvquwwkfor:[PASSWORD]@aws-0-eu-west-1.pooler.supabase.com:5432/postgres` |
| Self-hosted API                      | `https://supabase.datafy.co.za`                                                                                            |
| Target DB host                       | `datafy-prod-postgres.c7aqcoiku4hr.eu-west-1.rds.amazonaws.com` (direct RDS for admin `psql`)                              |
| Target S3 bucket                     | `datafy-prod-supabase-storage`                                                                                             |
| EC2 (migration host)                 | `i-0e3ee87bcaac5d0fd` — SSM shell as `ubuntu`                                                                              |
| Supabase project path                | `/var/snap/amazon-ssm-agent/13009/supabase-project`                                                                        |
| Migration working dir                | `~/migration`                                                                                                              |
| Secrets Manager (master DB password) | `rds!db-d6f075f5-94d0-4ce6-8162-a3b20397d94c`                                                                              |
| Region                               | `eu-west-1`                                                                                                                |




### Already migrated (baseline — do not repeat)

From the initial migration (~Jun 2026):


| Area                           | Status                                                                                                                |
| ------------------------------ | --------------------------------------------------------------------------------------------------------------------- |
| **Public / auth schema**       | Bulk `data.sql` restore completed (all tables in dump) |
| **`storage.objects` metadata** | ~220,182 rows restored (~180,183 in `media`) |
| **Storage files (rclone)**     | Buckets: `media`, `ml`, `Reports`, `uploads`, `profile`, `assets`, `backups`, `test` → `datafy-prod-supabase-storage` |
| **URL rewrite (partial)**      | `sjreport.category_image` / `image_url` — old `datafy.appmonitor.co.za` / `*.supabase.co` → `supabase.datafy.co.za`   |


Record your **cutoff timestamp** before starting delta work (when the initial migration finished, UTC):

```bash
# Example — replace with your real cutoff
export MIGRATION_CUTOFF='2026-06-27 18:00:00+00'
```

Everything **after** `MIGRATION_CUTOFF` on hosted is in scope for this guide.

---



## Before you start



### 1. Maintenance / write strategy

Pick one:


| Mode                        | Hosted writes                          | Risk                                                |
| --------------------------- | -------------------------------------- | --------------------------------------------------- |
| **Recommended for go-live** | Stop app writes to hosted during delta | Lowest drift                                        |
| **Live delta**              | App still writes to hosted             | Must run delta again or accept small gap at cutover |




### 2. RDS snapshot (rollback)

```bash
aws rds create-db-snapshot \
  --db-instance-identifier datafy-prod-postgres \
  --db-snapshot-identifier datafy-pre-delta-$(date +%Y%m%d) \
  --region eu-west-1
```

Wait until status is **available**.

### 3. Shell on EC2 + connection env

```bash
# SSM session, then:
sudo su - ubuntu
mkdir -p ~/migration && cd ~/migration
```

Create `~/migration/db-env.sh` (run `source ~/migration/db-env.sh` in every new session):

```bash
cat > ~/migration/db-env.sh <<'EOF'
# --- TARGET (self-hosted RDS) ---
export PGHOST=datafy-prod-postgres.c7aqcoiku4hr.eu-west-1.rds.amazonaws.com
export PGPORT=5432
export PGDATABASE=postgres
export PGUSER=postgres
export PGSSLMODE=require
export PGPASSWORD=$(aws secretsmanager get-secret-value \
  --secret-id 'rds!db-d6f075f5-94d0-4ce6-8162-a3b20397d94c' \
  --region eu-west-1 \
  --query SecretString --output text | jq -r '.password')

# --- SOURCE (hosted supabase.com) — set password manually each session ---
export OLD_DB_URL='postgresql://postgres.hantiuuvmjzvquwwkfor:YOUR_PASSWORD@aws-0-eu-west-1.pooler.supabase.com:5432/postgres'

# --- Delta cutoff (UTC) — update to your initial migration end time ---
export MIGRATION_CUTOFF='2026-06-27 18:00:00+00'
EOF

chmod 600 ~/migration/db-env.sh
```

Edit `OLD_DB_URL` password and `MIGRATION_CUTOFF`, then:

```bash
source ~/migration/db-env.sh
psql -c "SELECT 1 AS target_ok;"
psql "$OLD_DB_URL" -c "SELECT 1 AS source_ok;"
```

> Use the **Session pooler (port 5432)**, not the transaction pooler (6543), for dumps and queries.

---



## Step 1 — Measure drift (what is missing?)

Run these **before** copying anything. Save the output — it is your delta baseline report.

### 1.1 List all tables in scope

```bash
source ~/migration/db-env.sh

psql "$OLD_DB_URL" -c "
SELECT schemaname, tablename
FROM pg_tables
WHERE schemaname IN ('public', 'auth', 'storage')
  AND tablename NOT LIKE 'pg_%'
ORDER BY schemaname, tablename;
" | tee ~/migration/all_tables.txt
```

### 1.2 Row counts — all tables (source vs target)

**Phase A — fast approximate check** (seconds). Flags tables worth an exact count.

Use `-At` for clean `table|count` lines.  
**Important:** `join -t '|'` sorts on the **table name only**. A plain `sort` sorts the whole line, and `|` vs `_` break order (e.g. `user_store` vs `user`). Always use:

```bash
LC_ALL=C sort -t '|' -k1,1
```

```bash
# Source approx counts
psql "$OLD_DB_URL" -At -c "
SELECT schemaname || '.' || relname || '|' || n_live_tup
FROM pg_stat_user_tables
WHERE schemaname IN ('public', 'auth', 'storage');
" | LC_ALL=C sort -t '|' -k1,1 > ~/migration/counts_source_approx.txt

# Target approx counts
psql -At -c "
SELECT schemaname || '.' || relname || '|' || n_live_tup
FROM pg_stat_user_tables
WHERE schemaname IN ('public', 'auth', 'storage');
" | LC_ALL=C sort -t '|' -k1,1 > ~/migration/counts_target_approx.txt

# Tables where approximate counts differ (no "is not sorted" errors)
LC_ALL=C join -t '|' -a1 -a2 \
  ~/migration/counts_source_approx.txt \
  ~/migration/counts_target_approx.txt \
  | awk -F'|' '$2 != $3 {print $1, "source="$2, "target="$3}' \
  | tee ~/migration/drift_candidates.txt
```

**Alternative without `join`** (same result, no sort quirks):

```bash
awk -F'|' '
  FNR==NR { src[$1]=$2; next }
  {
    if (src[$1] != $2) print $1, "source="src[$1], "target="$2
    delete src[$1]
  }
  END {
    for (t in src) print t, "source="src[t], "target="
  }
' ~/migration/counts_source_approx.txt ~/migration/counts_target_approx.txt \
  | tee ~/migration/drift_candidates.txt
```

**Phase B — exact counts** only for drift candidates (or all tables at cutover if you want certainty):

> **Important:** Generate count SQL from the **target** (or the intersection of both), not only from source. If source has a table that target does not (e.g. `public.user_deactivation_log`), the target `psql -f` aborts/skips and you can get a **false** “everything is missing” report.

```bash
# Required in every new SSM/shell session. Without this, psql attempts the
# nonexistent local socket /var/run/postgresql/.s.PGSQL.5432.
source ~/migration/db-env.sh
: "${OLD_DB_URL:?OLD_DB_URL is not set}"
: "${PGHOST:?PGHOST is not set}"
psql -c "SELECT 1 AS target_ok;"
psql "$OLD_DB_URL" -c "SELECT 1 AS source_ok;"

# 1) Tables that exist on BOTH sides
psql "$OLD_DB_URL" -At -c "
SELECT schemaname || '.' || tablename
FROM pg_tables
WHERE schemaname IN ('public', 'auth', 'storage')
ORDER BY 1;
" | LC_ALL=C sort > ~/migration/tables_source.txt

psql -At -c "
SELECT schemaname || '.' || tablename
FROM pg_tables
WHERE schemaname IN ('public', 'auth', 'storage')
ORDER BY 1;
" | LC_ALL=C sort > ~/migration/tables_target.txt

# Missing on target (need schema + data pull)
LC_ALL=C comm -23 ~/migration/tables_source.txt ~/migration/tables_target.txt \
  | tee ~/migration/tables_missing_on_target.txt

# Shared tables only — build COUNT SQL
LC_ALL=C comm -12 ~/migration/tables_source.txt ~/migration/tables_target.txt \
  | awk -F. '{printf "SELECT '\''%s.%s'\'' AS tbl, count(*)::bigint AS cnt FROM %s.%s;\n", $1,$2,$1,$2}' \
  > ~/migration/gen_counts.sql

# Exact counts (psql -At already prints table|count — do NOT re-awk with $1|$2)
psql "$OLD_DB_URL" -At -f ~/migration/gen_counts.sql \
  | LC_ALL=C sort -t '|' -k1,1 > ~/migration/counts_source_exact.txt

psql -At -f ~/migration/gen_counts.sql \
  | LC_ALL=C sort -t '|' -k1,1 > ~/migration/counts_target_exact.txt

# Sanity: both files should have the same number of lines
wc -l ~/migration/counts_source_exact.txt ~/migration/counts_target_exact.txt

# Tables where source has MORE rows than target
LC_ALL=C join -t '|' -a1 -a2 \
  ~/migration/counts_source_exact.txt \
  ~/migration/counts_target_exact.txt \
  | awk -F'|' '$2+0 > $3+0 {print $1, "src="$2, "tgt="$3, "missing=" ($2-$3)}' \
  | tee ~/migration/tables_needing_delta.txt
```

#### Pull a table that exists on source but not on target

Example: `public.user_deactivation_log` (17 rows).

```bash
# Schema + data from hosted → file
pg_dump "$OLD_DB_URL" \
  --schema=public \
  --table=user_deactivation_log \
  --no-owner --no-privileges \
  -f ~/migration/user_deactivation_log.sql

# Restore onto self-hosted (uses your PG* env)
psql --variable ON_ERROR_STOP=1 -f ~/migration/user_deactivation_log.sql

# Verify
psql -c "SELECT count(*) FROM public.user_deactivation_log;"
```

If `pg_dump` is unavailable, use:

```bash
# DDL only (run on source, inspect, then run on target)
psql "$OLD_DB_URL" -c "\d+ public.user_deactivation_log"

psql "$OLD_DB_URL" -c "\copy (
  SELECT * FROM public.user_deactivation_log
) TO '~/migration/user_deactivation_log.csv' CSV HEADER"

# After CREATE TABLE on target:
psql -c "\copy public.user_deactivation_log FROM '~/migration/user_deactivation_log.csv' CSV HEADER"
```

Skip platform-only tables you do not use (e.g. `storage.iceberg_*`) unless you need them.

Any table in `tables_needing_delta.txt` must be synced in Step 2.

> **Target > source** on a table usually means test data on self-hosted only — ignore unless unexpected.

### 1.3 New/changed rows since cutoff (all tables with timestamps)

Auto-detect tables that have `created_at` and/or `updated_at`, then count delta rows on **source**.

> **Important (fixed 2026-07-14):** Many tables have **only** `created_at` or **only** `updated_at`. The generator below builds a per-table query from columns that actually exist. Do **not** reuse the old generator that always referenced both columns — that caused `ERROR: column "updated_at" does not exist` / `created_at` does not exist (e.g. `auth.audit_log_entries`, `public.sjreport`, `public.product_images`). Step 2.1 export already handled this; only this count step was wrong in earlier doc versions.

```bash
# Requires: source ~/migration/db-env.sh  (MIGRATION_CUTOFF set)
psql "$OLD_DB_URL" -At -c "
WITH tables AS (
  SELECT
    t.schemaname,
    t.tablename,
    EXISTS (
      SELECT 1 FROM information_schema.columns c
      WHERE c.table_schema = t.schemaname AND c.table_name = t.tablename
        AND c.column_name = 'created_at'
    ) AS has_c,
    EXISTS (
      SELECT 1 FROM information_schema.columns c
      WHERE c.table_schema = t.schemaname AND c.table_name = t.tablename
        AND c.column_name = 'updated_at'
    ) AS has_u
  FROM pg_tables t
  WHERE t.schemaname IN ('public', 'auth', 'storage')
)
SELECT format(
  \$q\$
SELECT %L AS tbl,
       %s AS inserted,
       %s AS updated_only
FROM %I.%I
WHERE %s;
\$q\$,
  schemaname || '.' || tablename,
  CASE WHEN has_c
    THEN format('count(*) FILTER (WHERE created_at > timestamptz %L)', '${MIGRATION_CUTOFF}')
    ELSE '0::bigint'
  END,
  CASE
    WHEN has_c AND has_u THEN format(
      'count(*) FILTER (WHERE updated_at > timestamptz %L AND created_at <= timestamptz %L)',
      '${MIGRATION_CUTOFF}', '${MIGRATION_CUTOFF}')
    WHEN has_u THEN format(
      'count(*) FILTER (WHERE updated_at > timestamptz %L)', '${MIGRATION_CUTOFF}')
    ELSE '0::bigint'
  END,
  schemaname,
  tablename,
  CASE
    WHEN has_c AND has_u THEN format(
      'created_at > timestamptz %L OR updated_at > timestamptz %L',
      '${MIGRATION_CUTOFF}', '${MIGRATION_CUTOFF}')
    WHEN has_c THEN format('created_at > timestamptz %L', '${MIGRATION_CUTOFF}')
    WHEN has_u THEN format('updated_at > timestamptz %L', '${MIGRATION_CUTOFF}')
  END
)
FROM tables
WHERE has_c OR has_u
ORDER BY schemaname, tablename;
" > ~/migration/gen_delta_counts.sql

psql "$OLD_DB_URL" -f ~/migration/gen_delta_counts.sql | tee ~/migration/delta_since_cutoff.txt
# Expect: no ERROR lines. Review non-zero inserted/updated_only tables before Step 2.
```

Tables with **zero** timestamp columns (common for small lookup tables) are handled in Step 2.1 via primary-key / full-table diff instead.



### 1.4 Storage file drift (per bucket)

Requires rclone remotes `supabase_source` and `aws_target` (see [Step 3](#step-3--sync-new-storage-files-images)). Compare object count + bytes:

```bash
for b in media ml Reports uploads profile assets backups test; do
  echo "=== $b ==="
  echo -n "source: "; rclone size "supabase_source:$b" 2>/dev/null | tail -1
  echo -n "target: "; rclone size "aws_target:datafy-prod-supabase-storage/$b" 2>/dev/null | tail -1
done
```

Buckets where **source > target** need an rclone delta copy.

### 1.5 Find storage metadata rows missing on target

Objects that exist on hosted but not self-hosted (by primary key `id`):

```bash
psql "$OLD_DB_URL" -c "\copy (
  SELECT o.id, o.bucket_id, o.name, o.created_at
  FROM storage.objects o
  WHERE o.created_at > timestamptz '${MIGRATION_CUTOFF}'
  ORDER BY o.created_at
) TO '~/migration/new_storage_objects_source.csv' CSV HEADER"
```

On target, check how many of those IDs are absent:

```sql
-- Run on target after loading IDs into a temp table (see Step 2.3)
SELECT count(*) AS missing_on_target FROM missing_storage_ids;
```

---



## Step 2 — Sync new database rows (all drifted tables)

**Do not** re-import full `data.sql`. Process **every table** listed in `~/migration/tables_needing_delta.txt` from Step 1.2.

Load order: same as original `data.sql` `COPY` order if parent/child FK errors appear; otherwise `SET session_replication_role = replica` usually allows any order.

### 2.1 Export delta — loop all drifted tables

Save as `~/migration/export_delta.sh`, then `chmod +x ~/migration/export_delta.sh`.

> **Important:** Tables often have **only** `created_at` or **only** `updated_at`. Build the `WHERE` clause from columns that actually exist — do not always use both.

```bash
#!/usr/bin/env bash
set -euo pipefail
source ~/migration/db-env.sh
cd ~/migration
mkdir -p delta_csv

while read -r line; do
  [[ -z "$line" || "$line" =~ ^# ]] && continue
  tbl=$(echo "$line" | awk '{print $1}')          # e.g. public.sjreport
  schema=${tbl%%.*}
  table=${tbl#*.}
  out="delta_csv/${schema}.${table}.csv"

  # Skip if already exported and non-empty (safe to re-run)
  if [[ -s "$out" ]]; then
    echo "skip $tbl (already exported)"
    continue
  fi

  # Which timestamp columns exist? (0/1 each)
  cols=$(psql "$OLD_DB_URL" -At -c "
    SELECT
      EXISTS (SELECT 1 FROM information_schema.columns
              WHERE table_schema='$schema' AND table_name='$table' AND column_name='created_at')::int,
      EXISTS (SELECT 1 FROM information_schema.columns
              WHERE table_schema='$schema' AND table_name='$table' AND column_name='updated_at')::int;
  ")
  has_created=${cols%|*}
  has_updated=${cols#*|}

  where=""
  if [[ "$has_created" == "1" && "$has_updated" == "1" ]]; then
    where="created_at > timestamptz '${MIGRATION_CUTOFF}' OR updated_at > timestamptz '${MIGRATION_CUTOFF}'"
  elif [[ "$has_created" == "1" ]]; then
    where="created_at > timestamptz '${MIGRATION_CUTOFF}'"
  elif [[ "$has_updated" == "1" ]]; then
    where="updated_at > timestamptz '${MIGRATION_CUTOFF}'"
  fi

  if [[ -n "$where" ]]; then
    echo "export $tbl (timestamp filter)..."
    psql "$OLD_DB_URL" -c "\copy (
      SELECT * FROM ${schema}.${table}
      WHERE ${where}
    ) TO '$out' CSV HEADER"
  else
    # No timestamps — export rows with PK greater than target max
    pk_col=$(psql -At -c "
      SELECT a.attname
      FROM pg_index i
      JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey)
      WHERE i.indrelid = '${schema}.${table}'::regclass AND i.indisprimary
      LIMIT 1;
    ")
    if [[ -z "$pk_col" ]]; then
      echo "WARN: $tbl has no timestamps and no PK — export full table manually" | tee -a export_delta.warn
      continue
    fi
    pk_type=$(psql -At -c "
      SELECT format_type(a.atttypid, a.atttypmod)
      FROM pg_index i
      JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey)
      WHERE i.indrelid = '${schema}.${table}'::regclass AND i.indisprimary
      LIMIT 1;
    ")
    if [[ ! "$pk_type" =~ ^(smallint|integer|bigint)$ ]]; then
      # UUID/text PKs are not sequential. A target MAX cutoff can omit missing
      # rows below that value, so export full and let the loader upsert.
      echo "export $tbl (full — non-sequential PK type ${pk_type})..."
      psql "$OLD_DB_URL" -c "\copy (SELECT * FROM ${schema}.${table}) TO '$out' CSV HEADER"
      continue
    fi
    max_pk=$(psql -At -c "SELECT coalesce(max(${pk_col})::text, '') FROM ${schema}.${table};")
    if [[ -z "$max_pk" ]]; then
      echo "export $tbl (full — empty on target)..."
      psql "$OLD_DB_URL" -c "\copy (SELECT * FROM ${schema}.${table}) TO '$out' CSV HEADER"
    else
      echo "export $tbl (PK ${pk_col} > ${max_pk})..."
      psql "$OLD_DB_URL" -c "\copy (
        SELECT * FROM ${schema}.${table} WHERE ${pk_col} > '${max_pk}'
      ) TO '$out' CSV HEADER"
    fi
  fi

  if [[ -f "$out" ]]; then
    rows=$(($(wc -l < "$out") - 1))
    echo "  -> $out ($rows rows)"
  else
    echo "  -> FAILED $tbl (no file written)" | tee -a export_delta.warn
  fi
done < tables_needing_delta.txt
```

```bash
chmod +x ~/migration/export_delta.sh
# Remove failed/empty CSVs so the script retries them
find ~/migration/delta_csv -type f -size 0 -delete
rm -f \
  ~/migration/delta_csv/auth.audit_log_entries.csv \
  ~/migration/delta_csv/public.category_images.csv \
  ~/migration/delta_csv/public.product_images.csv \
  ~/migration/delta_csv/public.promos.csv \
  ~/migration/delta_csv/public.report_files.csv \
  ~/migration/delta_csv/public.sjreport.csv \
  ~/migration/delta_csv/public.sjreport_uploads.csv \
  ~/migration/delta_csv/public.store_metrics.csv \
  ~/migration/delta_csv/public.trigger_execution_log.csv \
  ~/migration/delta_csv/public.user_metrics.csv

nohup ~/migration/export_delta.sh > ~/migration/export_delta.log 2>&1 &
tail -f ~/migration/export_delta.log
```

> **Large tables** (`sjreport`, `task`): timestamp filter keeps exports small. If Step 1 shows drift but export returns 0 rows, the gap is from the **initial migration** (not post-cutoff writes) — use `id > target_max` or a one-off `COPY` block from `data.sql` for that table only.

### 2.2 Load into target — loop all exports (upsert-safe)

**Do not paste the script body into the shell.** Write it to a file first (`cat > … << 'EOF'`), then run that file. Pasting interactively breaks `psql` variables and history (`!/usr/bin`).

Canonical copy: `docs/scripts/load_delta.sh`.

> **Schema drift:** Hosted CSVs may have extra / generated columns. This script intersects CSV headers with target columns in **Python** (do not use `psql -v` / `:'csv_header'` with `-c` — that causes false `SKIP … no shared columns`).

```bash
# As ubuntu — write file, then run (second block)
cat > /home/ubuntu/migration/load_delta.sh << 'EOF'
#!/usr/bin/env bash
# Load delta CSVs from ~/migration/delta_csv into self-hosted Postgres.
# Filters each CSV to shared non-generated columns (handles schema drift).
set -euo pipefail
source /home/ubuntu/migration/db-env.sh
cd /home/ubuntu/migration/delta_csv
mkdir -p filtered
: > ../load_delta.log
set +e   # continue after one table fails

for csv in *.csv; do
  [[ -f "$csv" ]] || continue
  schema=${csv%%.*}
  table=${csv#*.}
  table=${table%.csv}
  tbl="${schema}.${table}"

  pk_cols=$(psql -At -c "
    SELECT string_agg(a.attname, ', ' ORDER BY x.n)
    FROM pg_index i
    JOIN LATERAL unnest(i.indkey) WITH ORDINALITY AS x(attnum, n) ON true
    JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = x.attnum
    WHERE i.indrelid = '${tbl}'::regclass AND i.indisprimary;
  ")
  if [[ -z "$pk_cols" ]]; then
    echo "SKIP $tbl — no primary key" | tee -a ../load_delta.log
    continue
  fi

  # Target non-generated columns (one per line)
  psql -At -c "
    SELECT a.attname
    FROM pg_attribute a
    JOIN pg_class c ON c.oid = a.attrelid
    JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE n.nspname='${schema}' AND c.relname='${table}'
      AND a.attnum > 0 AND NOT a.attisdropped
      AND a.attgenerated = ''
    ORDER BY a.attnum;
  " > "/tmp/tgt_cols_${schema}_${table}.txt"

  if [[ ! -s "/tmp/tgt_cols_${schema}_${table}.txt" ]]; then
    echo "SKIP $tbl — table missing on target?" | tee -a ../load_delta.log
    continue
  fi

  filtered="filtered/${csv}"
  # Intersect CSV header with target cols in Python (avoids psql -v / CRLF bugs)
  shared_cols=$(python3 - "$csv" "$filtered" "/tmp/tgt_cols_${schema}_${table}.txt" <<'PY'
import csv, sys
src, dst, tgt_path = sys.argv[1], sys.argv[2], sys.argv[3]
with open(tgt_path, encoding="utf-8") as f:
    tgt = {line.strip() for line in f if line.strip()}
with open(src, newline="", encoding="utf-8-sig") as f:
    reader = csv.DictReader(f)
    if not reader.fieldnames:
        print("", end="")
        sys.exit(1)
    cols = [c.strip() for c in reader.fieldnames if c and c.strip() in tgt]
    if not cols:
        print("", end="")
        sys.exit(2)
    rows = list(reader)
with open(dst, "w", newline="", encoding="utf-8") as f:
    w = csv.DictWriter(f, fieldnames=cols, extrasaction="ignore")
    w.writeheader()
    for r in rows:
        w.writerow({c: r.get(c, "") for c in cols})
print(",".join(cols), end="")
PY
)
  rc=$?
  if [[ $rc -ne 0 || -z "$shared_cols" ]]; then
    echo "SKIP $tbl — no shared columns (python rc=$rc)" | tee -a ../load_delta.log
    echo "  CSV header: $(head -1 "$csv" | tr -d '\r' | cut -c1-120)..." | tee -a ../load_delta.log
    echo "  Target cols sample: $(head -5 /tmp/tgt_cols_${schema}_${table}.txt | tr '\n' ' ')" | tee -a ../load_delta.log
    continue
  fi

  quoted_cols=$(psql -At -c "
    SELECT string_agg(quote_ident(x), ', ')
    FROM unnest(string_to_array('${shared_cols}', ',')) AS x;
  ")

  updates=$(psql -At -c "
    WITH pk AS (
      SELECT trim(x) AS col FROM unnest(string_to_array('${pk_cols}', ',')) AS x
    )
    SELECT coalesce(
      string_agg(format('%I = EXCLUDED.%I', x, x), ', '),
      ''
    )
    FROM unnest(string_to_array('${shared_cols}', ',')) AS x
    WHERE trim(x) NOT IN (SELECT col FROM pk);
  ")

  echo "load $tbl (cols=$(echo "$shared_cols" | tr ',' ' ' | wc -w)) ..." | tee -a ../load_delta.log
  if [[ -n "$updates" ]]; then
    conflict_sql="ON CONFLICT (${pk_cols}) DO UPDATE SET ${updates}"
  else
    conflict_sql="ON CONFLICT (${pk_cols}) DO NOTHING"
  fi

  psql --variable ON_ERROR_STOP=1 <<EOSQL 2>&1 | tee -a ../load_delta.log
SET session_replication_role = replica;
CREATE TEMP TABLE staging_delta (LIKE ${tbl} INCLUDING DEFAULTS);
\\copy staging_delta (${quoted_cols}) FROM '${PWD}/${filtered}' CSV HEADER
INSERT INTO ${tbl} (${quoted_cols})
SELECT ${quoted_cols} FROM staging_delta
${conflict_sql};
DROP TABLE staging_delta;
EOSQL

  if [[ $? -ne 0 ]]; then
    echo "FAILED $tbl" | tee -a ../load_delta.log
  else
    echo "OK $tbl" | tee -a ../load_delta.log
  fi
done

echo "Done. Check /home/ubuntu/migration/load_delta.log" | tee -a ../load_delta.log
EOF
ls -l /home/ubuntu/migration/load_delta.sh
```

```bash
# Run only after the file exists (as ubuntu)
chmod +x /home/ubuntu/migration/load_delta.sh
: > /home/ubuntu/migration/load_delta.log
nohup /home/ubuntu/migration/load_delta.sh > /home/ubuntu/migration/load_delta.out 2>&1 &
echo $!
tail -f /home/ubuntu/migration/load_delta.log
```

Expect `load public.…` then `OK …`, not a wall of `SKIP … no shared columns`.

**Do not restart from scratch** if some tables already loaded (`ON CONFLICT` upsert is safe to re-run; or move done CSVs out of `delta_csv/`).

### 2.2b Retry known load failures

`load_delta.sh` uses PK `ON CONFLICT`. These tables often need a **special** conflict target or `OVERRIDING SYSTEM VALUE` (identity columns). Use `docs/scripts/retry_failed_delta.sh`:

| Failure | Cause | Retry fix |
| ------- | ----- | --------- |
| `public.product` — `GENERATED ALWAYS` / identity | Inserting explicit `id` | `OVERRIDING SYSTEM VALUE` + `ON CONFLICT (id)` |
| `public.product_images` — `image_path` NOT NULL | CSV empty → NULL on `\copy` | Drop null/`''` rows in staging, then insert |
| `public.product_store` — unique `(product_id, store_code)` | Conflict not on PK alone | `ON CONFLICT (product_id, store_code) DO NOTHING` |

```bash
# Write docs/scripts/retry_failed_delta.sh to EC2 if missing, then:
chmod +x /home/ubuntu/migration/retry_failed_delta.sh
cd /home/ubuntu/migration/delta_csv
bash /home/ubuntu/migration/retry_failed_delta.sh
# Watch: ~/migration/retry_failed.log — expect OK for product, product_images, product_store
```

Safe to re-run; already-loaded tables get `DO NOTHING` / no-op upserts.

### 2.3 Auth-related tables

`export_delta.sh` includes `auth.*` automatically when they appear in `tables_needing_delta.txt`. Typical extras beyond `auth.users`:

- `auth.identities`
- `auth.sessions` (optional — users re-login after cutover anyway)
- `auth.refresh_tokens`

Users must **sign in again** on self-hosted after cutover (new JWT).

### 2.4 Fix hosted URLs in all text columns (post-load)

Run once after Step 2.2 / 2.2b. Updates known URL columns in `public` that still contain old domains.

**Do not paste the SQL into bash.** Run it via `psql` (target DB from `db-env.sh`).

```bash
source /home/ubuntu/migration/db-env.sh

# 1) Generate UPDATEs (fast)
psql -At -c "
SELECT format(
  'UPDATE %I.%I SET %I = replace(replace(%I::text,
    ''https://datafy.appmonitor.co.za'', ''https://supabase.datafy.co.za''),
    ''https://hantiuuvmjzvquwwkfor.supabase.co'', ''https://supabase.datafy.co.za'')
  WHERE %I::text LIKE ''%%supabase.co%%'' OR %I::text LIKE ''%%appmonitor.co.za%%'';',
  table_schema, table_name, column_name, column_name, column_name, column_name
)
FROM information_schema.columns
WHERE table_schema = 'public'
  AND data_type IN ('text', 'character varying')
  AND column_name IN ('category_image', 'image_url', 'avatar_url', 'logo_url', 'file_url', 'url', 'photo_url');
" > /home/ubuntu/migration/url_rewrite_updates.sql

wc -l /home/ubuntu/migration/url_rewrite_updates.sql
# Optional review: less /home/ubuntu/migration/url_rewrite_updates.sql

# 2) Always run in background — large tables can take a long time / drop SSH
nohup psql -v ON_ERROR_STOP=1 -f /home/ubuntu/migration/url_rewrite_updates.sql \
  > /home/ubuntu/migration/url_rewrite.log 2>&1 &
echo $! | tee /home/ubuntu/migration/url_rewrite.pid
tail -f /home/ubuntu/migration/url_rewrite.log
# Ctrl+C only stops tail — does not stop the rewrite
```

Each `UPDATE` auto-commits. If the job dies mid-file, **re-run the same `-f`** — already-rewritten rows no longer match `LIKE '%…%'` so they become `UPDATE 0`. Safe and idempotent.

---



## Step 3 — Sync new storage files (images)

Run from EC2. `rclone copy` is **idempotent**: it skips files already on target with matching size/hash.

### 3.1 One-time rclone setup (skip if already configured)

```bash
# Prefer rclone >= 1.74
curl -fsSL https://downloads.rclone.org/rclone-current-linux-amd64.zip -o /tmp/rclone.zip
sudo unzip -o /tmp/rclone.zip -d /tmp && sudo cp /tmp/rclone-*/rclone /usr/local/bin/
rclone version

# Hosted Supabase S3 API (create access key in hosted Studio → Storage → S3)
rclone config create supabase_source s3 \
  provider Other \
  access_key_id "HOSTED_ACCESS_KEY" \
  secret_access_key "HOSTED_SECRET_KEY" \
  endpoint "https://hantiuuvmjzvquwwkfor.storage.supabase.co/storage/v1/s3" \
  region eu-west-1 \
  force_path_style true

# Target — EC2 IAM role → raw AWS bucket
rclone config create aws_target s3 \
  provider AWS \
  env_auth true \
  region eu-west-1 \
  location_constraint eu-west-1 \
  no_check_bucket true

rclone lsd supabase_source:
```



### 3.2 Delta copy per bucket (background-safe)

Supabase Storage objects are immutable/versioned for this migration. Compare by
size so differing S3 modification times do not re-copy tens of gigabytes of
unchanged objects. Re-running is safe.

```bash
# Use absolute paths (do NOT quote "~/..." — tilde will not expand)
MIG=/home/ubuntu/migration
mkdir -p "$MIG"

run_bucket() {
  local b="$1"
  # Reset prior-run output so current failures are unambiguous.
  : > "${MIG}/delta-copy-${b}.log"
  : > "${MIG}/delta-copy-${b}.out"
  nohup rclone copy "supabase_source:${b}" "aws_target:datafy-prod-supabase-storage/${b}" \
    --fast-list --no-traverse --size-only \
    --transfers 8 --checkers 16 \
    --retries 10 --low-level-retries 20 \
    --log-file "${MIG}/delta-copy-${b}.log" --log-level NOTICE --stats 30s \
    > "${MIG}/delta-copy-${b}.out" 2>&1 &
  echo "started delta copy for ${b} (PID $!)"
}

for b in media ml Reports uploads profile assets backups test; do
  run_bucket "$b"
done
```

Monitor:

```bash
ps aux | grep '[r]clone'
tail -f /home/ubuntu/migration/delta-copy-media.log
```

### 3.2b Remap S3 keys for `STORAGE_TENANT_ID` (required on this stack)

Self-hosted `.env` has `STORAGE_TENANT_ID=stub`. **Storage 1.60+** (current self-hosted) stores objects as:

```text
s3://datafy-prod-supabase-storage/stub/<bucket_id>/<name>/<version>
# if version is null:
s3://datafy-prod-supabase-storage/stub/<bucket_id>/<name>
```

Confirm with a **new Studio upload**, then inspect S3 — you should see a “folder” named like `….jpg/` containing a version UUID object.

`rclone copy` writes the **flat** layout:

```text
s3://datafy-prod-supabase-storage/<bucket_id>/<name>
```

**Wrong** (do not use — older guess): `stub/<bucket>/<name>-$v-<version>`

Symptoms if you skip remap: Studio **lists** files (Postgres OK), public GET returns **404/400**. Docker is fine.

**Do not** rewrite `storage.objects`. After rclone, **copy** bytes to the tenant/`name`/`version` key (idempotent, never deletes sources). Same-bucket copy is **server-side** (EC2 does not download the 400GB).

```bash
# Rebuild manifest from storage.objects (dry-run) — always after rclone
# Scripts: docs/scripts/remap_storage_s3_keys.sh + docs/scripts/remap_storage_fast.py
chmod +x ~/migration/remap_storage_s3_keys.sh
~/migration/remap_storage_s3_keys.sh   # writes ~/migration/remap_keys.tsv

# Preferred: fast boto3 remapper (install once: sudo apt-get install -y python3-boto3)
# Idempotent — SKIPs keys that already exist under stub/.../version
nohup env JOBS=128 python3 ~/migration/remap_storage_fast.py \
  > ~/migration/remap-fast.out 2>&1 &
echo $! | tee ~/migration/remap-fast.pid
tail -f ~/migration/remap-fast.out

# Fallback (slower): GNU parallel + aws cli
# sudo apt-get install -y parallel
# nohup ~/migration/remap_storage_s3_keys.sh --execute --jobs 32 \
#   > ~/migration/remap-execute.out 2>&1 &
```

- Re-run after every migrate/delta rclone (skips keys that already exist).
- `MISSING` in the log → file never under `<bucket_id>/<name>`; rclone first, then re-run.
- Optional later: delete unused wrong keys matching `*-$v-*` under `stub/` from a mistaken remap.

### 3.3 Verify a sample image end-to-end

Pick a **new** object from hosted (created after cutoff):

```bash
# On source — find a recent media object
psql "$OLD_DB_URL" -c "
SELECT bucket_id, name, created_at
FROM storage.objects
WHERE bucket_id = 'media'
ORDER BY created_at DESC
LIMIT 5;
"

# On target — same row must exist
psql -c "
SELECT id, bucket_id, name FROM storage.objects
WHERE bucket_id = 'media' AND name = '<path-from-above>';
"

# Flat rclone key (source of remap)
aws s3 ls "s3://datafy-prod-supabase-storage/media/<path-from-above>" --region eu-west-1

# Tenant key Storage reads (after §3.2b): stub/<bucket>/<name>/<version>
aws s3 ls "s3://datafy-prod-supabase-storage/stub/media/<path-from-above>/" --region eu-west-1

# HTTP download via self-hosted API
curl -sI "https://supabase.datafy.co.za/storage/v1/object/public/media/<path-from-above>" | head -5
```

Expected: row in DB + object under **`stub/<bucket>/<name>/<version>`** + HTTP **200**.

---



## Step 4 — Verify delta (sign-off checklist)

```bash
source ~/migration/db-env.sh

# 1) Re-run exact count compare — tables_needing_delta.txt should be empty
psql "$OLD_DB_URL" -f ~/migration/gen_counts.sql > ~/migration/counts_source_final.txt
psql -f ~/migration/gen_counts.sql > ~/migration/counts_target_final.txt
paste <(sort ~/migration/counts_source_final.txt) <(sort ~/migration/counts_target_final.txt) \
  | awk -F'\t' '$2 != $4 {print "MISMATCH:", $1, "src="$2, "tgt="$4}'

# 2) Storage bucket sizes (source vs target) — should match per bucket
for b in media ml Reports uploads profile assets backups test; do
  echo "=== $b ==="
  rclone size "supabase_source:$b" | tail -1
  rclone size "aws_target:datafy-prod-supabase-storage/$b" | tail -1
done
```

Manual checks:

- [ ] Sign in on self-hosted with a user created after cutoff
- [ ] Open a report/image uploaded after `MIGRATION_CUTOFF`
- [ ] Upload a **new** test image on self-hosted (confirms write path + IMDS/S3)

---



## Step 5 — Application cutover (after delta is green)

Only after Step 4 passes:

1. Point mobile/web app to `https://supabase.datafy.co.za` and new anon key.
2. Update Lambda SSM parameters (`/supabase/url`, `/supabase/anon`, `/supabase/service_role`).
3. Stop writes to hosted supabase.com (or accept one final micro-delta).
4. Optionally run Step 1–3 once more for a **final** 15-minute delta.
5. Update `MIGRATION_CUTOFF` in this doc / `db-env.sh` to the final sync time for audit.

See `migrate-supabase-cloud-to-self-hosted.md` → **Step 8** for SSM and redirect URL details.

---



## What not to do


| Action                                         | Why                                                   |
| ---------------------------------------------- | ----------------------------------------------------- |
| Re-run full `data.sql` restore                 | Duplicates rows, hours of downtime, may break FKs     |
| `rclone sync` (instead of `copy`)              | Can **delete** target files not on source             |
| Copy only to S3 without `storage.objects` rows | Studio/API 404 — metadata missing                     |
| Use transaction pooler (6543) for dumps        | Breaks consistent `pg_dump` / large exports           |
| Assume `net.http_post` triggers work on RDS    | `pg_net` is not available — handle HTTP in app/Lambda |


---



## Troubleshooting


| Issue                                      | Fix                                                                                       |
| ------------------------------------------ | ----------------------------------------------------------------------------------------- |
| `column "updated_at"/"created_at" does not exist` in §1.3 | Doc bug: use the **column-aware** generator in §1.3 (updated 2026-07-14). Step 2.1 export was already correct. |
| `SKIP … no shared columns` + `syntax error at or near ":"` | Pasted old loader into shell, or used `:'csv_header'` with `psql -c`. Use `docs/scripts/load_delta.sh` (Python intersect). Write with `cat > … << 'EOF'` as **ubuntu**. |
| `tls: handshake failure` on rclone source  | `force_path_style true` on `supabase_source`; project-level endpoint, not bucket hostname |
| `s3:CreateBucket` 403 on target            | `no_check_bucket true` on `aws_target` or `--s3-no-check-bucket`                          |
| rclone stuck at `Listed 2`                 | Add `--fast-list --no-traverse`                                                           |
| Row exists in S3 but 404 in app            | Missing `storage.objects` row — run Step 2.3                                              |
| Row in DB but 404/400 in app               | Missing S3 file **or** wrong key layout — rclone (§3.2) then `remap_storage_s3_keys.sh` (§3.2b) |
| Studio lists file, public URL 404/400      | Flat `bucket/name` on S3; Storage wants `stub/bucket/name/version` — run §3.2b (`-$v-` layout is wrong for Storage 1.60+) |
| `OLD_DB_URL` empty / socket error          | Re-`source ~/migration/db-env.sh`                                                         |
| IPv6 unreachable to hosted DB              | Use Session pooler URL (IPv4), not `db.[ref].supabase.co`                                 |
| Duplicate key on delta load                | Switch to staging + `ON CONFLICT DO UPDATE`                                               |
| Upload works on hosted but not self-hosted | Check storage container IMDS hop limit + S3 IAM (see setup guide)                         |


---



## Related

- `docs/migrate-supabase-cloud-to-self-hosted.md` — full initial migration
- `docs/supabase-aws-rds-proxy-setup.md` — storage, SSL, IAM
- `docs/connect-private-ec2-ssm.md` — reach the migration EC2 instance