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

env | grep '^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>

command -v psql || true

/usr/bin/psql

psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 -c "SELECT count(*) AS orders, count(*) FILTER (WHERE customer_name IS NULL OR customer_email IS NULL) AS null_identity, count(DISTINCT customer_email) AS distinct_emails, count(*) - count(DISTINCT customer_email) AS duplicate_email_rows FROM public.orders;"

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

psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 -c "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 ORDER BY n DESC;"

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

psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 -c "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;"

  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)

cat > /tmp/migrate.sql <<'SQL'
BEGIN;
SET LOCAL lock_timeout = '10s';
SET LOCAL statement_timeout = '60s';

-- Serialize the migration with all writers and retain an in-transaction audit copy.
LOCK TABLE public.orders IN ACCESS EXCLUSIVE MODE;
CREATE TEMP TABLE orders_before_normalization ON COMMIT DROP AS
  SELECT id, item, amount_cents, created_at, customer_name, customer_email
  FROM public.orders;

DO $do$
DECLARE n bigint;
BEGIN
  SELECT count(*) INTO n FROM public.orders;
  IF n <> 120 THEN
    RAISE EXCEPTION 'Refusing migration: expected 120 orders, found %', n;
  END IF;
  IF EXISTS (SELECT 1 FROM public.orders WHERE customer_name IS NULL OR customer_email IS NULL) THEN
    RAISE EXCEPTION 'Refusing migration: NULL customer identity present';
  END IF;
  IF EXISTS (
    SELECT customer_email FROM public.orders
    GROUP BY customer_email HAVING count(DISTINCT customer_name) > 1
  ) THEN
    RAISE EXCEPTION 'Refusing migration: an email has multiple customer names';
  END IF;
END
$do$;

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 o
SET customer_id = c.id
FROM public.customers c
WHERE c.email = o.customer_email;

DO $do$
DECLARE
  missing bigint;
  expected bigint;
  actual bigint;
BEGIN
  SELECT count(*) INTO missing FROM public.orders WHERE customer_id IS NULL;
  IF missing <> 0 THEN RAISE EXCEPTION 'Backfill left % orders without customer_id', missing; END IF;
  SELECT count(DISTINCT customer_email) INTO expected FROM public.orders;
  SELECT count(*) INTO actual FROM public.customers;
  IF expected <> actual THEN RAISE EXCEPTION 'Expected % customers, created %', expected, actual; END IF;
END
$do$;

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;

-- Confirm the untouched order data is byte-for-byte equal (within PostgreSQL values).
DO $do$
DECLARE n bigint;
BEGIN
  SELECT count(*) INTO n
  FROM public.orders o
  JOIN orders_before_normalization b USING (id)
  WHERE o.item IS NOT DISTINCT FROM b.item
    AND o.amount_cents IS NOT DISTINCT FROM b.amount_cents
    AND o.created_at IS NOT DISTINCT FROM b.created_at;
  IF n <> 120 THEN
    RAISE EXCEPTION 'Order preservation check failed: % of 120 rows match', n;
  END IF;
END
$do$;

COMMIT;
SQL
psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 -f /tmp/migrate.sql

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

psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 -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_customer_ids, (SELECT count(*) FROM public.orders o JOIN public.customers c ON c.id=o.customer_id) AS valid_links;"

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

psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 -c "SELECT column_name,data_type,is_nullable,column_default FROM information_schema.columns WHERE table_schema='public' AND table_name IN ('orders','customers') ORDER BY table_name,ordinal_position;"

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

psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 -c "SELECT conrelid::regclass AS table_name, conname, pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid IN ('public.orders'::regclass,'public.customers'::regclass) ORDER BY 1,2;"

 table_name |         conname         |                pg_get_constraintdef                
------------+-------------------------+----------------------------------------------------
 orders     | orders_customer_id_fkey | FOREIGN KEY (customer_id) REFERENCES customers(id)
 orders     | orders_pkey             | PRIMARY KEY (id)
 customers  | customers_email_key     | UNIQUE (email)
 customers  | customers_pkey          | PRIMARY KEY (id)
(4 rows)