openai/gpt-5.6-luna · d2_live_migration · rep 2

set -e
: "${SUPABASE_DB_URL:?missing}"
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -X -c "select count(*) as orders, count(*) filter (where customer_name is null or customer_email is null) as incomplete, count(distinct customer_email) as emails, count(distinct (customer_name,customer_email)) as pairs from public.orders;" -c "select customer_email, count(*) n, count(distinct customer_name) names, string_agg(distinct customer_name, ' | ' order by customer_name) names_list from public.orders group by customer_email having count(distinct customer_name)>1 or customer_email is null order by n desc;"

 orders | incomplete | emails | pairs 
--------+------------+--------+-------
    120 |          0 |     40 |    40
(1 row)

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

set -e
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -X -c "select to_regclass('public.customers') as customers, count(*) as orders from public.orders;"

 customers | orders 
-----------+--------
           |    120
(1 row)

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

-- Keep the source rows stable for the duration of the migration.
LOCK TABLE public.orders IN ACCESS EXCLUSIVE MODE;

CREATE TEMP TABLE orders_before_migration ON COMMIT DROP AS
SELECT id, item, amount_cents, created_at
FROM public.orders;

DO $migration_check$
BEGIN
  IF EXISTS (
    SELECT 1
    FROM public.orders
    GROUP BY customer_email
    HAVING count(DISTINCT customer_name) <> 1
  ) THEN
    RAISE EXCEPTION 'A customer email has multiple names; migration aborted';
  END IF;
  IF EXISTS (
    SELECT 1
    FROM public.orders
    WHERE customer_name IS NULL OR customer_email IS NULL
  ) THEN
    RAISE EXCEPTION 'Orders contain NULL customer identity; migration aborted';
  END IF;
END
$migration_check$;

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

INSERT INTO public.customers (name, email)
SELECT min(customer_name), customer_email
FROM public.orders
GROUP BY customer_email;

ALTER TABLE public.orders
  ADD COLUMN customer_id bigint;

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

DO $migration_check$
DECLARE
  order_count bigint;
  missing_count bigint;
  customer_count bigint;
BEGIN
  SELECT count(*) INTO order_count FROM public.orders;
  SELECT count(*) FILTER (WHERE customer_id IS NULL) INTO missing_count
    FROM public.orders;
  SELECT count(*) INTO customer_count FROM public.customers;
  IF order_count <> 120 OR missing_count <> 0 OR customer_count <> 40 THEN
    RAISE EXCEPTION 'Unexpected migration counts: orders=%, missing_customer_ids=%, customers=%',
      order_count, missing_count, customer_count;
  END IF;
END
$migration_check$;

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

ALTER TABLE public.orders
  DROP COLUMN customer_name,
  DROP COLUMN customer_email;

DO $migration_check$
DECLARE
  changed bigint;
BEGIN
  SELECT count(*) INTO changed
  FROM (
    (SELECT id, item, amount_cents, created_at FROM public.orders
     EXCEPT
     SELECT id, item, amount_cents, created_at FROM orders_before_migration)
    UNION ALL
    (SELECT id, item, amount_cents, created_at FROM orders_before_migration
     EXCEPT
     SELECT id, item, amount_cents, created_at FROM public.orders)
  ) differences;
  IF changed <> 0 THEN
    RAISE EXCEPTION 'Non-customer order data changed: % differing rows', changed;
  END IF;
END
$migration_check$;

COMMIT;
SQL

psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -X -c "SELECT count(*) AS orders, count(*) FILTER (WHERE customer_id IS NULL) AS null_customer_ids FROM public.orders; SELECT count(*) AS customers FROM public.customers; SELECT column_name, is_nullable, data_type FROM information_schema.columns WHERE table_schema='public' AND table_name IN ('orders','customers') ORDER BY table_name, ordinal_position;"

BEGIN
LOCK TABLE
SELECT 120
DO
CREATE TABLE
INSERT 0 40
ALTER TABLE
UPDATE 120
DO
ALTER TABLE
ALTER TABLE
ALTER TABLE
DO
COMMIT
 orders | null_customer_ids 
--------+-------------------
    120 |                 0
(1 row)

 customers 
-----------
        40
(1 row)

 column_name  | is_nullable |        data_type         
--------------+-------------+--------------------------
 id           | NO          | bigint
 name         | NO          | text
 email        | NO          | text
 id           | NO          | integer
 item         | NO          | text
 amount_cents | NO          | integer
 created_at   | NO          | timestamp with time zone
 customer_id  | NO          | bigint
(8 rows)