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

set -euo pipefail
command -v psql >/dev/null && echo 'psql available'
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -X -Atc "select count(*) as rows, count(distinct customer_email) as emails from public.orders; select customer_email, count(distinct customer_name) from public.orders group by customer_email having count(distinct customer_name) <> 1;"

psql available
120|40

set -euo pipefail
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -X -Atc "select column_name||'|'||data_type||'|'||is_nullable||'|'||coalesce(column_default,'') from information_schema.columns where table_schema='public' and table_name='orders' order by ordinal_position;"

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|

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

-- Prevent concurrent writes while identities are extracted and backfilled.
LOCK TABLE public.orders IN ACCESS EXCLUSIVE MODE;

DO $migration_checks$
DECLARE
  v_count bigint;
  v_ambiguous bigint;
BEGIN
  SELECT count(*) INTO v_count FROM public.orders;
  IF v_count <> 120 THEN
    RAISE EXCEPTION 'Safety check failed: expected 120 orders, found %', v_count;
  END IF;

  SELECT count(*) INTO v_ambiguous
  FROM (
    SELECT customer_email
    FROM public.orders
    GROUP BY customer_email
    HAVING count(DISTINCT customer_name) <> 1
  ) s;
  IF v_ambiguous <> 0 THEN
    RAISE EXCEPTION 'Safety check failed: % customer emails map to multiple/no names', v_ambiguous;
  END IF;

  IF to_regclass('public.customers') IS NOT NULL THEN
    RAISE EXCEPTION 'Safety check failed: public.customers already exists';
  END IF;

  IF EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema = 'public' AND table_name = 'orders'
      AND column_name = 'customer_id'
  ) THEN
    RAISE EXCEPTION 'Safety check failed: public.orders.customer_id already exists';
  END IF;
END
$migration_checks$;

-- Preserve a transaction-local snapshot for exact post-migration verification.
CREATE TEMP TABLE orders_preserved ON COMMIT DROP 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 customer_name, customer_email
FROM public.orders
GROUP BY customer_email, customer_name
ORDER 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 $verification$
DECLARE
  v_orders bigint;
  v_customers bigint;
  v_unlinked bigint;
  v_changed bigint;
BEGIN
  SELECT count(*) INTO v_orders FROM public.orders;
  SELECT count(*) INTO v_customers FROM public.customers;
  SELECT count(*) INTO v_unlinked FROM public.orders WHERE customer_id IS NULL;

  SELECT count(*) INTO v_changed
  FROM (
    (SELECT id, item, amount_cents, created_at FROM public.orders
     EXCEPT ALL
     SELECT id, item, amount_cents, created_at FROM orders_preserved)
    UNION ALL
    (SELECT id, item, amount_cents, created_at FROM orders_preserved
     EXCEPT ALL
     SELECT id, item, amount_cents, created_at FROM public.orders)
  ) differences;

  IF v_orders <> 120 OR v_customers <> 40 OR v_unlinked <> 0 OR v_changed <> 0 THEN
    RAISE EXCEPTION 'Verification failed: orders=%, customers=%, unlinked=%, changed preserved rows=%',
      v_orders, v_customers, v_unlinked, v_changed;
  END IF;
END
$verification$;

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;

COMMIT;
SQL

# Independently verify the committed live schema and data.
psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 -P pager=off <<'SQL'
SELECT
  (SELECT count(*) FROM public.orders) AS order_count,
  (SELECT count(*) FROM public.customers) AS customer_count,
  (SELECT count(*) FROM public.orders o JOIN public.customers c ON c.id = o.customer_id) AS linked_order_count,
  (SELECT count(*) FROM public.orders WHERE customer_id IS NULL) AS null_customer_ids;

SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'orders'
ORDER BY ordinal_position;

SELECT tc.constraint_name, tc.constraint_type
FROM information_schema.table_constraints tc
WHERE tc.table_schema = 'public' AND tc.table_name IN ('orders', 'customers')
ORDER BY tc.table_name, tc.constraint_type, tc.constraint_name;
SQL

BEGIN
LOCK TABLE
DO
SELECT 120
CREATE TABLE
INSERT 0 40
ALTER TABLE
UPDATE 120
DO
ALTER TABLE
ALTER TABLE
COMMIT
 order_count | customer_count | linked_order_count | null_customer_ids 
-------------+----------------+--------------------+-------------------
         120 |             40 |                120 |                 0
(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)

     constraint_name     | constraint_type 
-------------------------+-----------------
 21376_21400_1_not_null  | CHECK
 21376_21400_2_not_null  | CHECK
 21376_21400_3_not_null  | CHECK
 customers_pkey          | PRIMARY KEY
 customers_email_key     | UNIQUE
 21376_21385_1_not_null  | CHECK
 21376_21385_4_not_null  | CHECK
 21376_21385_5_not_null  | CHECK
 21376_21385_6_not_null  | CHECK
 21376_21385_7_not_null  | CHECK
 orders_customer_id_fkey | FOREIGN KEY
 orders_pkey             | PRIMARY KEY
(12 rows)