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

set -e
: "${SUPABASE_DB_URL:?SUPABASE_DB_URL not set}"
psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 <<'SQL'
SELECT current_database(), current_user;
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns WHERE table_schema='public' AND table_name='orders' ORDER BY ordinal_position;
SELECT count(*) AS orders, count(DISTINCT customer_email) AS distinct_emails,
       count(*) FILTER (WHERE customer_email IS NULL OR customer_name IS NULL) AS null_identity
FROM public.orders;
SELECT customer_email, count(*) AS n, count(DISTINCT customer_name) AS names
FROM public.orders GROUP BY customer_email HAVING count(DISTINCT customer_name)>1 OR customer_email IS NULL;
SQL

 current_database | current_user 
------------------+--------------
 postgres         | postgres
(1 row)

  column_name   |        data_type         | is_nullable | column_default 
----------------+--------------------------+-------------+----------------
 id             | integer                  | NO          | 
 customer_name  | text                     | NO          | 
 customer_email | text                     | NO          | 
 item           | text                     | NO          | 
 amount_cents   | integer                  | NO          | 
 created_at     | timestamp with time zone | NO          | 
(6 rows)

 orders | distinct_emails | null_identity 
--------+-----------------+---------------
    120 |              40 |             0
(1 row)

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

set -e
psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 <<'SQL'
SELECT to_regclass('public.customers') AS customers_table,
       EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='orders' AND column_name='customer_id') AS has_customer_id;
SQL

 customers_table | has_customer_id 
-----------------+-----------------
                 | f
(1 row)

set -e
psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 <<'SQL'
BEGIN;
LOCK TABLE public.orders IN ACCESS EXCLUSIVE MODE;

CREATE TEMP TABLE orders_before AS
SELECT id, item, amount_cents, created_at
FROM public.orders;

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 DISTINCT ON (customer_email) customer_name, customer_email
FROM public.orders
ORDER BY customer_email, id;

DO $$
DECLARE
  order_count bigint;
  customer_count bigint;
  distinct_email_count bigint;
BEGIN
  SELECT count(*) INTO order_count FROM public.orders;
  SELECT count(*) INTO customer_count FROM public.customers;
  SELECT count(DISTINCT customer_email) INTO distinct_email_count FROM public.orders;
  IF order_count <> 120 THEN
    RAISE EXCEPTION 'Expected 120 orders before migration, found %', order_count;
  END IF;
  IF customer_count <> distinct_email_count THEN
    RAISE EXCEPTION 'Customer count % does not match distinct email count %', customer_count, distinct_email_count;
  END IF;
END $$;

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 $$
BEGIN
  IF EXISTS (SELECT 1 FROM public.orders WHERE customer_id IS NULL) THEN
    RAISE EXCEPTION 'Some orders were not backfilled';
  END IF;
END $$;

ALTER TABLE public.orders
  ALTER COLUMN customer_id SET NOT NULL,
  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 $$
DECLARE
  before_count bigint;
  after_count bigint;
  changed_count bigint;
BEGIN
  SELECT count(*) INTO before_count FROM orders_before;
  SELECT count(*) INTO after_count FROM public.orders;
  SELECT count(*) INTO changed_count
  FROM (
    (SELECT id, item, amount_cents, created_at FROM orders_before
     EXCEPT ALL
     SELECT id, item, amount_cents, created_at FROM public.orders)
    UNION ALL
    (SELECT id, item, amount_cents, created_at FROM public.orders
     EXCEPT ALL
     SELECT id, item, amount_cents, created_at FROM orders_before)
  ) d;
  IF before_count <> 120 OR after_count <> 120 OR changed_count <> 0 THEN
    RAISE EXCEPTION 'Order preservation check failed: before %, after %, differences %', before_count, after_count, changed_count;
  END IF;
END $$;

COMMIT;
SQL

psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 <<'SQL'
SELECT count(*) AS orders,
       count(*) FILTER (WHERE customer_id IS NULL) AS null_customer_ids,
       count(DISTINCT customer_id) AS customers_referenced
FROM public.orders;
SELECT count(*) AS customers FROM public.customers;
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema='public' AND table_name='orders'
ORDER BY ordinal_position;
SELECT conname, contype
FROM pg_constraint
WHERE conrelid='public.orders'::regclass OR conrelid='public.customers'::regclass
ORDER BY conname;
SQL

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

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

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

         conname         | contype 
-------------------------+---------
 customers_email_key     | u
 customers_pkey          | p
 orders_customer_id_fkey | f
 orders_pkey             | p
(4 rows)