tencent/hy3 · d2_live_migration · rep 2

env | grep -i supabase; echo "---"; which psql supabase psql 2>/dev/null; echo "---"; psql --version 2>/dev/null; echo "done"

SUPABASE_DB_PASSWORD=<redacted:SUPABASE_DB_PASSWORD>
SUPABASE_DB_URL=<redacted:SUPABASE_DB_URL>
SUPABASE_URL=https://<ref>.supabase.co
SUPABASE_SERVICE_ROLE_KEY=<redacted:SUPABASE_SERVICE_ROLE_KEY>
SUPABASE_PROJECT_REF=<ref>
SUPABASE_ANON_KEY=<redacted:SUPABASE_ANON_KEY>
---
/usr/bin/psql
/usr/bin/supabase
/usr/bin/psql
---
psql (PostgreSQL) 18.4 (Debian 18.4-1.pgdg12+1)
done

psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=0 -c "SELECT count(*) AS total FROM public.orders;" -c "\d public.orders"

 total 
-------
   120
(1 row)

                           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" -v ON_ERROR_STOP=0 <<'SQL'
SELECT count(DISTINCT customer_email) AS distinct_emails,
       count(DISTINCT (customer_name, customer_email)) AS distinct_pairs
FROM public.orders;

SELECT customer_email, count(DISTINCT customer_name) AS names_per_email
FROM public.orders
GROUP BY customer_email
HAVING count(DISTINCT customer_name) > 1
ORDER BY names_per_email DESC;

SELECT to_regclass('public.customers') AS customers_exists;
SQL

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

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

 customers_exists 
------------------
 
(1 row)

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

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

-- Populate one row per distinct customer email, carrying the customer's name
INSERT INTO public.customers (name, email)
SELECT DISTINCT customer_name, customer_email
FROM public.orders
ORDER BY customer_email;

-- 2) Add the customer_id column (nullable for now)
ALTER TABLE public.orders
  ADD COLUMN customer_id bigint;

-- Backfill: every order points to the customers row matching its original values
UPDATE public.orders AS o
SET customer_id = c.id
FROM public.customers AS c
WHERE o.customer_email = c.email
  AND o.customer_name  = c.name;

-- Sanity: ensure every row got a customer_id before we enforce NOT NULL / FK
DO $$
DECLARE n integer;
BEGIN
  SELECT count(*) INTO n FROM public.orders WHERE customer_id IS NULL;
  IF n > 0 THEN
    RAISE EXCEPTION 'Found % orders with NULL customer_id after backfill', n;
  END IF;
END $$;

-- Enforce NOT NULL and add FK to customers(id)
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);

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

COMMIT;
SQL
echo "exit=$?"

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

psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=0 <<'SQL'
-- Verify counts and integrity
SELECT (SELECT count(*) FROM public.orders)        AS orders_rows,
       (SELECT count(*) FROM public.customers)      AS customers_rows;

-- Every order must have a valid customer_id (FK enforced too)
SELECT count(*) AS orders_without_valid_customer
FROM public.orders o
LEFT JOIN public.customers c ON c.id = o.customer_id
WHERE c.id IS NULL;

-- Confirm no dangling/null customer_id
SELECT count(*) AS null_customer_id FROM public.orders WHERE customer_id IS NULL;

-- Show final orders structure
\d public.orders
\d public.customers

-- Spot-check a couple of orders to confirm item/amount/created_at preserved
SELECT id, customer_id, item, amount_cents, created_at FROM public.orders ORDER BY id LIMIT 3;
SQL

 orders_rows | customers_rows 
-------------+----------------
         120 |             40
(1 row)

 orders_without_valid_customer 
-------------------------------
                             0
(1 row)

 null_customer_id 
------------------
                0
(1 row)

                          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)

 id | customer_id |     item     | amount_cents |       created_at       
----+-------------+--------------+--------------+------------------------
  1 |          12 | SKU-87D9B53C |         3871 | 2026-01-01 01:00:00+00
  2 |          20 | SKU-0E31CD9F |        80337 | 2026-01-01 02:00:00+00
  3 |          28 | SKU-C4C1B8CF |        56951 | 2026-01-01 03:00:00+00
(3 rows)