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

echo "URL: $SUPABASE_URL"; echo "REF: $SUPABASE_PROJECT_REF"; echo "DB_URL set: $([ -n "$SUPABASE_DB_URL" ] && echo yes || echo no)"; which psql || echo "no psql"

URL: https://<ref>.supabase.co
REF: <ref>
DB_URL set: yes
/usr/bin/psql

psql "$SUPABASE_DB_URL" -c "\d public.orders"

                           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" -c "SELECT count(*) AS total, count(DISTINCT customer_email) AS distinct_emails, count(DISTINCT (customer_name, customer_email)) AS distinct_pairs FROM public.orders;"

 total | distinct_emails | distinct_pairs 
-------+-----------------+----------------
   120 |              40 |             40
(1 row)

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;"

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

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

-- 1) 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 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, customer_name;

-- 2) add customer_id column (nullable first), backfill, then constrain
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_email = c.email
  AND o.customer_name  = c.name;

-- verify no NULLs before enforcing constraints
DO $$
DECLARE n int;
BEGIN
  SELECT count(*) INTO n FROM public.orders WHERE customer_id IS NULL;
  IF n <> 0 THEN
    RAISE EXCEPTION 'Backfill incomplete: % orders have NULL customer_id', n;
  END IF;
END $$;

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 embedded identity columns
ALTER TABLE public.orders DROP COLUMN customer_name;
ALTER TABLE public.orders DROP COLUMN customer_email;

COMMIT;
SQL

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"; 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)
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_fk, (SELECT count(*) FROM public.orders o LEFT JOIN public.customers c ON o.customer_id=c.id WHERE c.id IS NULL) AS orphaned;"

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

psql "$SUPABASE_DB_URL" -c "
-- every customer email/name is represented; count orders per email via join equals original grouping
SELECT
  (SELECT count(DISTINCT email) FROM public.customers) AS cust_emails,
  (SELECT count(*) FROM public.orders o JOIN public.customers c ON o.customer_id=c.id) AS joined_orders,
  (SELECT count(*) FROM public.customers c WHERE NOT EXISTS (SELECT 1 FROM public.orders o WHERE o.customer_id=c.id)) AS customers_without_orders;
"

 cust_emails | joined_orders | customers_without_orders 
-------------+---------------+--------------------------
          40 |           120 |                        0
(1 row)