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

env | grep -i supabase | sed 's/=.*/=<set>/'

SUPABASE_DB_PASSWORD=<set>
SUPABASE_DB_URL=<set>
SUPABASE_URL=<set>
SUPABASE_SERVICE_ROLE_KEY=<set>
SUPABASE_PROJECT_REF=<set>
SUPABASE_ANON_KEY=<set>

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 -40

                           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, count(DISTINCT customer_email) AS distinct_emails, count(DISTINCT (customer_name, customer_email)) AS distinct_name_email FROM public.orders;"

          120 |              40 |                  40

psql "$SUPABASE_DB_URL" -c "SELECT customer_email, count(DISTINCT customer_name) AS names FROM public.orders GROUP BY customer_email HAVING count(DISTINCT customer_name) > 1;"
echo "--- case/space variants check ---"
psql "$SUPABASE_DB_URL" -c "SELECT count(DISTINCT lower(trim(customer_email))) AS distinct_norm FROM public.orders;"

 customer_email | names 
----------------+-------
(0 rows)

--- case/space variants check ---
 distinct_norm 
---------------
            40
(1 row)

psql "$SUPABASE_DB_URL" -t -c "SELECT md5(string_agg(id||'|'||item||'|'||amount_cents||'|'||created_at||'|'||customer_name||'|'||customer_email, ',' ORDER BY id)) FROM public.orders;"

 243057de2e885de317d9d0353ccde186

psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 <<'SQL'
BEGIN;

-- Lock the table to prevent concurrent writes during migration
LOCK TABLE public.orders IN SHARE ROW EXCLUSIVE MODE;

-- 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
INSERT INTO public.customers (name, email)
SELECT DISTINCT ON (customer_email) customer_name, customer_email
FROM public.orders
ORDER BY customer_email, id;

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

UPDATE public.orders o
SET customer_id = c.id
FROM public.customers c
WHERE o.customer_name = c.name
  AND o.customer_email = c.email;

-- Safety checks BEFORE dropping anything
DO $$
DECLARE
    unmatched int;
    orphan    int;
BEGIN
    SELECT count(*) INTO unmatched FROM public.orders WHERE customer_id IS NULL;
    IF unmatched <> 0 THEN
        RAISE EXCEPTION 'Backfill left % orders without customer_id', unmatched;
    END IF;

    -- verify name+email round-trips exactly (no mismatched joins)
    SELECT count(*) INTO orphan
    FROM public.orders o JOIN public.customers c ON c.id = o.customer_id
    WHERE o.customer_name <> c.name OR o.customer_email <> c.email;
    IF orphan <> 0 THEN
        RAISE EXCEPTION '% orders mapped to wrong customer', orphan;
    END IF;
END $$;

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

CREATE INDEX orders_customer_id_idx ON public.orders(customer_id);

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

COMMIT;
SQL

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

psql "$SUPABASE_DB_URL" -c "\d public.orders"
echo "=================="
psql "$SUPABASE_DB_URL" -c "\d public.customers"

                          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)
    "orders_customer_id_idx" btree (customer_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 "SELECT (SELECT count(*) FROM public.orders) AS orders, (SELECT count(*) FROM public.customers) AS customers, (SELECT count(*) FROM public.orders WHERE customer_id IS NULL) AS null_cid;"
echo "--- reconstruct original & compare checksum ---"
psql "$SUPABASE_DB_URL" -t -c "SELECT md5(string_agg(o.id||'|'||o.item||'|'||o.amount_cents||'|'||o.created_at||'|'||c.name||'|'||c.email, ',' ORDER BY o.id)) FROM public.orders o JOIN public.customers c ON c.id=o.customer_id;"

 orders | customers | null_cid 
--------+-----------+----------
    120 |        40 |        0
(1 row)

--- reconstruct original & compare checksum ---
 243057de2e885de317d9d0353ccde186