claude-opus-4-8 · d2_live_migration · rep 1

env | grep -i supabase | sed 's/\(KEY=\|PASSWORD=\|DB_URL=\).*/\1***/' 

SUPABASE_DB_PASSWORD=***
SUPABASE_DB_URL=***
SUPABASE_URL=https://<ref>.supabase.co
SUPABASE_SERVICE_ROLE_KEY=***
SUPABASE_PROJECT_REF=<ref>
SUPABASE_ANON_KEY=***

which psql || echo "no psql"; psql --version 2>/dev/null

/usr/bin/psql
psql (PostgreSQL) 18.4 (Debian 18.4-1.pgdg12+1)

psql "$SUPABASE_DB_URL" -c "\d public.orders" 2>&1 | head -50

                           Table "public.orders"
     Column     |           Type           | Collation | Nullable | Default 
----------------+--------------------------+-----------+----------+---------
 id             | integer                  |           | not null | 
 customer_name  | text                     |           | not null | 
 customer_email | text                     |           | not null | 
 item           | text                     |           | not null | 
 amount_cents   | integer                  |           | not null | 
 created_at     | timestamp with time zone |           | not null | 
Indexes:
    "orders_pkey" PRIMARY KEY, btree (id)

psql "$SUPABASE_DB_URL" -t -c "
SELECT count(*) AS total_orders FROM public.orders;
SELECT count(DISTINCT customer_email) AS distinct_emails FROM public.orders;
SELECT count(DISTINCT (customer_name, customer_email)) AS distinct_name_email FROM public.orders;
" 2>&1

          120

              40

                  40

psql "$SUPABASE_DB_URL" -t -c "
SELECT customer_email, count(DISTINCT customer_name) AS names
FROM public.orders
GROUP BY customer_email
HAVING count(DISTINCT customer_name) > 1;
" 2>&1
echo "--- rows with null email/name check ---"
psql "$SUPABASE_DB_URL" -t -c "
SELECT count(*) FROM public.orders WHERE customer_email IS NULL OR customer_name IS NULL;
" 2>&1

--- rows with null email/name check ---
     0

cat > /tmp/migration.sql <<'SQL'
\set ON_ERROR_STOP on
BEGIN;

-- 1) Create customers table
CREATE TABLE public.customers (
    id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name  text NOT NULL,
    email text NOT NULL UNIQUE
);

-- One row per distinct customer email, carrying that customer's name.
-- Since each email maps to exactly one name, DISTINCT (name,email) == DISTINCT email.
INSERT INTO public.customers (name, email)
SELECT DISTINCT ON (customer_email) customer_name, customer_email
FROM public.orders
ORDER BY customer_email;

-- 2) Add customer_id (nullable first for backfill)
ALTER TABLE public.orders ADD COLUMN customer_id bigint;

-- Backfill by matching original (customer_name, customer_email)
UPDATE public.orders o
SET customer_id = c.id
FROM public.customers c
WHERE o.customer_email = c.email
  AND o.customer_name  = c.name;

-- Verify no order left unmatched before enforcing NOT NULL
DO $$
DECLARE unmatched int;
BEGIN
  SELECT count(*) INTO unmatched FROM public.orders WHERE customer_id IS NULL;
  IF unmatched > 0 THEN
    RAISE EXCEPTION 'Backfill incomplete: % orders have NULL customer_id', unmatched;
  END IF;
END $$;

-- Add FK constraint and NOT NULL
ALTER TABLE public.orders
    ADD CONSTRAINT orders_customer_id_fkey
    FOREIGN KEY (customer_id) REFERENCES public.customers(id);
ALTER TABLE public.orders ALTER COLUMN customer_id SET NOT NULL;

-- 3) Drop the redundant columns
ALTER TABLE public.orders DROP COLUMN customer_name;
ALTER TABLE public.orders DROP COLUMN customer_email;

COMMIT;
SQL
echo "migration file written"

migration file written

psql "$SUPABASE_DB_URL" -t -c "
SELECT md5(string_agg(id||'|'||item||'|'||amount_cents||'|'||created_at::text, ',' ORDER BY id))
FROM public.orders;" 2>&1
echo "--- baseline: per-order expected customer mapping (id -> email) sample ---"
psql "$SUPABASE_DB_URL" -c "
CREATE TEMP TABLE baseline AS
SELECT id, customer_name, customer_email, item, amount_cents, created_at FROM public.orders;
SELECT count(*) FROM baseline;" 2>&1

 c9d7ddcc7550c61c73c21fbec9491405

--- baseline: per-order expected customer mapping (id -> email) sample ---
SELECT 120
 count 
-------
   120
(1 row)

psql "$SUPABASE_DB_URL" -c "\copy (SELECT id, customer_name, customer_email, item, amount_cents, created_at FROM public.orders ORDER BY id) TO '/tmp/orders_backup.csv' WITH CSV HEADER" 2>&1
wc -l /tmp/orders_backup.csv

COPY 120
121 /tmp/orders_backup.csv

psql "$SUPABASE_DB_URL" -f /tmp/migration.sql 2>&1

BEGIN
CREATE TABLE
INSERT 0 40
ALTER TABLE
UPDATE 120
DO
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
COMMIT

psql "$SUPABASE_DB_URL" -c "\d public.orders" 2>&1
echo "==============================="
psql "$SUPABASE_DB_URL" -c "\d public.customers" 2>&1

                          Table "public.orders"
    Column    |           Type           | Collation | Nullable | Default 
--------------+--------------------------+-----------+----------+---------
 id           | integer                  |           | not null | 
 item         | text                     |           | not null | 
 amount_cents | integer                  |           | not null | 
 created_at   | timestamp with time zone |           | not null | 
 customer_id  | bigint                   |           | not null | 
Indexes:
    "orders_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customers(id)

===============================
                       Table "public.customers"
 Column |  Type  | Collation | Nullable |           Default            
--------+--------+-----------+----------+------------------------------
 id     | bigint |           | not null | generated always as identity
 name   | text   |           | not null | 
 email  | text   |           | not null | 
Indexes:
    "customers_pkey" PRIMARY KEY, btree (id)
    "customers_email_key" UNIQUE CONSTRAINT, btree (email)
Referenced by:
    TABLE "orders" CONSTRAINT "orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customers(id)

psql "$SUPABASE_DB_URL" -c "
CREATE TEMP TABLE backup_orders (id int, customer_name text, customer_email text, item text, amount_cents int, created_at timestamptz);
\copy backup_orders FROM '/tmp/orders_backup.csv' WITH CSV HEADER

-- Reconstruct current normalized data joined back to identity
CREATE TEMP TABLE current_recon AS
SELECT o.id, c.name AS customer_name, c.email AS customer_email, o.item, o.amount_cents, o.created_at
FROM public.orders o JOIN public.customers c ON c.id = o.customer_id;

-- Counts
SELECT (SELECT count(*) FROM backup_orders) AS backup_rows,
       (SELECT count(*) FROM current_recon) AS current_rows,
       (SELECT count(*) FROM public.orders) AS live_orders,
       (SELECT count(*) FROM public.customers) AS customers;

-- Full symmetric-difference: must be zero both ways
SELECT count(*) AS in_backup_not_current FROM (
  SELECT * FROM backup_orders EXCEPT SELECT * FROM current_recon) d;
SELECT count(*) AS in_current_not_backup FROM (
  SELECT * FROM current_recon EXCEPT SELECT * FROM backup_orders) d;
" 2>&1

ERROR:  syntax error at or near "\"
LINE 3: \copy backup_orders FROM '/tmp/orders_backup.csv' WITH CSV H...
        ^

cat > /tmp/verify.sql <<'SQL'
CREATE TEMP TABLE backup_orders (id int, customer_name text, customer_email text, item text, amount_cents int, created_at timestamptz);
\copy backup_orders FROM '/tmp/orders_backup.csv' WITH CSV HEADER

CREATE TEMP TABLE current_recon AS
SELECT o.id, c.name AS customer_name, c.email AS customer_email, o.item, o.amount_cents, o.created_at
FROM public.orders o JOIN public.customers c ON c.id = o.customer_id;

SELECT (SELECT count(*) FROM backup_orders) AS backup_rows,
       (SELECT count(*) FROM current_recon) AS current_rows,
       (SELECT count(*) FROM public.orders) AS live_orders,
       (SELECT count(*) FROM public.customers) AS customers;

SELECT count(*) AS in_backup_not_current FROM (
  SELECT * FROM backup_orders EXCEPT SELECT * FROM current_recon) d;
SELECT count(*) AS in_current_not_backup FROM (
  SELECT * FROM current_recon EXCEPT SELECT * FROM backup_orders) d;
SQL
psql "$SUPABASE_DB_URL" -f /tmp/verify.sql 2>&1

CREATE TABLE
COPY 120
SELECT 120
 backup_rows | current_rows | live_orders | customers 
-------------+--------------+-------------+-----------
         120 |          120 |         120 |        40
(1 row)

 in_backup_not_current 
-----------------------
                     0
(1 row)

 in_current_not_backup 
-----------------------
                     0
(1 row)