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

command -v psql && psql --version

/usr/bin/psql
psql (PostgreSQL) 18.4 (Debian 18.4-1.pgdg12+1)

printf 'URL=%s\nREF=%s\nDB_URL_SET=%s\nDB_PASSWORD_SET=%s\n' "$SUPABASE_URL" "$SUPABASE_PROJECT_REF" "${SUPABASE_DB_URL:+yes}" "${SUPABASE_DB_PASSWORD:+yes}"

URL=https://<ref>.supabase.co
REF=<ref>
DB_URL_SET=yes
DB_PASSWORD_SET=yes

psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 -P pager=off <<'SQL'
SELECT count(*) AS order_count,
       count(DISTINCT customer_email) AS distinct_emails,
       count(*) FILTER (WHERE customer_name IS NULL OR customer_email IS NULL) AS null_identity_rows
FROM public.orders;

SELECT customer_email, count(DISTINCT customer_name) AS name_count,
       array_agg(DISTINCT customer_name ORDER BY customer_name) AS names
FROM public.orders
GROUP BY customer_email
HAVING count(DISTINCT customer_name) <> 1;

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

 order_count | distinct_emails | null_identity_rows 
-------------+-----------------+--------------------
         120 |              40 |                  0
(1 row)

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

  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)

psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 -P pager=off <<'SQL'
BEGIN;
SET LOCAL lock_timeout = '15s';
SET LOCAL statement_timeout = '60s';

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

DO $guard$
BEGIN
  IF (SELECT count(*) FROM public.orders) <> 120 THEN
    RAISE EXCEPTION 'Safety check failed: expected 120 orders, found %', (SELECT count(*) FROM public.orders);
  END IF;
  IF EXISTS (SELECT 1 FROM public.orders WHERE customer_name IS NULL OR customer_email IS NULL) THEN
    RAISE EXCEPTION 'Safety check failed: NULL customer identity exists';
  END IF;
  IF EXISTS (
    SELECT 1 FROM public.orders
    GROUP BY customer_email
    HAVING count(DISTINCT customer_name) <> 1
  ) THEN
    RAISE EXCEPTION 'Safety check failed: an email maps to multiple customer names';
  END IF;
END
$guard$;

-- Transaction-local exact snapshot used to prove all original data survives.
CREATE TEMP TABLE orders_before ON COMMIT DROP AS
SELECT id, customer_name, customer_email, 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;

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 $verify$
BEGIN
  IF (SELECT count(*) FROM public.orders) <> 120 THEN
    RAISE EXCEPTION 'Verification failed: order count changed';
  END IF;
  IF (SELECT count(*) FROM public.customers) <> (SELECT count(DISTINCT customer_email) FROM orders_before) THEN
    RAISE EXCEPTION 'Verification failed: customer count does not match distinct emails';
  END IF;
  IF EXISTS (SELECT 1 FROM public.orders WHERE customer_id IS NULL) THEN
    RAISE EXCEPTION 'Verification failed: some orders were not backfilled';
  END IF;
  IF EXISTS (
    SELECT 1
    FROM public.orders o
    JOIN orders_before b
      ON b.id = o.id
     AND b.item IS NOT DISTINCT FROM o.item
     AND b.amount_cents IS NOT DISTINCT FROM o.amount_cents
     AND b.created_at IS NOT DISTINCT FROM o.created_at
    JOIN public.customers c ON c.id = o.customer_id
    WHERE c.name IS DISTINCT FROM b.customer_name
       OR c.email IS DISTINCT FROM b.customer_email
  ) THEN
    RAISE EXCEPTION 'Verification failed: an order points to the wrong customer';
  END IF;
  IF (SELECT count(*) FROM public.orders o JOIN orders_before b
        ON b.id = o.id
       AND b.item IS NOT DISTINCT FROM o.item
       AND b.amount_cents IS NOT DISTINCT FROM o.amount_cents
       AND b.created_at IS NOT DISTINCT FROM o.created_at) <> 120 THEN
    RAISE EXCEPTION 'Verification failed: original order values did not survive exactly';
  END IF;
END
$verify$;

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;

-- Post-commit validation.
SELECT (SELECT count(*) FROM public.orders) AS orders,
       (SELECT count(*) FROM public.customers) AS customers,
       (SELECT count(*) FROM public.orders o JOIN public.customers c ON c.id = o.customer_id) AS orders_with_customer;

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

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

 column_name  |        data_type         | is_nullable 
--------------+--------------------------+-------------
 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)