moonshotai/kimi-k3 · d2_live_migration · rep 2

env | grep -i supabase | sed 's/=.*/=<set>/' && which psql && psql --version

SUPABASE_DB_PASSWORD=<set>
SUPABASE_DB_URL=<set>
SUPABASE_URL=<set>
SUPABASE_SERVICE_ROLE_KEY=<set>
SUPABASE_PROJECT_REF=<set>
SUPABASE_ANON_KEY=<set>
/usr/bin/psql
psql (PostgreSQL) 18.4 (Debian 18.4-1.pgdg12+1)

psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -c "\d public.orders" -c "SELECT count(*) AS total, count(DISTINCT customer_email) AS distinct_emails, count(DISTINCT (customer_name, customer_email)) AS distinct_pairs FROM public.orders;"

                           Table "public.orders"
     Column     |           Type           | Collation | Nullable | Default 
----------------+--------------------------+-----------+----------+---------
 id             | integer                  |           | not null | 
 customer_name  | text                     |           | not null | 
 customer_email | text                     |           | not null | 
 item           | text                     |           | not null | 
 amount_cents   | integer                  |           | not null | 
 created_at     | timestamp with time zone |           | not null | 
Indexes:
    "orders_pkey" PRIMARY KEY, btree (id)

 total | distinct_emails | distinct_pairs 
-------+-----------------+----------------
   120 |              40 |             40
(1 row)

mkdir -p /tmp/migration && psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -c "\copy (SELECT id, customer_name, customer_email, item, amount_cents, created_at FROM public.orders ORDER BY id) TO '/tmp/migration/orders_before.csv' CSV HEADER" && wc -l /tmp/migration/orders_before.csv && psql "$SUPABASE_DB_URL" -tAc "SELECT to_regclass('public.customers');"

COPY 120
121 /tmp/migration/orders_before.csv

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

-- 1) customers: one row per distinct email, carrying that customer's name
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 (o.customer_email) o.customer_name, o.customer_email
FROM public.orders o
ORDER BY o.customer_email, o.id;  -- deterministic: earliest order's name per email

-- 2) add customer_id, backfill via exact (name, email) match, enforce constraints
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
  AND c.name  = o.customer_name;

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

-- 3) drop embedded identity columns
ALTER TABLE public.orders
  DROP COLUMN customer_name,
  DROP COLUMN customer_email;

-- gate: abort unless zero loss
DO $$
DECLARE n int; u int; c int;
BEGIN
  SELECT count(*), count(customer_id) INTO n, u FROM public.orders;
  SELECT count(*) INTO c FROM public.customers;
  ASSERT n = 120, 'order count changed';
  ASSERT u = 120, 'unbackfilled orders remain';
  ASSERT c = 40, 'unexpected customer count';
END $$;

COMMIT;
SQL

BEGIN
CREATE TABLE
INSERT 0 40
ALTER TABLE
UPDATE 120
ALTER TABLE
ALTER TABLE
DO
COMMIT

psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -c "\d public.orders" -c "\d public.customers" -c "
SELECT
  (SELECT count(*) FROM public.orders) AS orders,
  (SELECT count(*) FROM public.customers) AS customers,
  (SELECT count(*) FROM public.orders o LEFT JOIN public.customers c ON c.id = o.customer_id WHERE c.id IS NULL) AS orphan_orders,
  (SELECT count(*) FROM pg_constraint WHERE conrelid='public.orders'::regclass AND contype='f' AND confrelid='public.customers'::regclass) AS fk_count;"

                          Table "public.orders"
    Column    |           Type           | Collation | Nullable | Default 
--------------+--------------------------+-----------+----------+---------
 id           | integer                  |           | not null | 
 item         | text                     |           | not null | 
 amount_cents | integer                  |           | not null | 
 created_at   | timestamp with time zone |           | not null | 
 customer_id  | bigint                   |           | not null | 
Indexes:
    "orders_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customers(id)

                       Table "public.customers"
 Column |  Type  | Collation | Nullable |           Default            
--------+--------+-----------+----------+------------------------------
 id     | bigint |           | not null | generated always as identity
 name   | text   |           | not null | 
 email  | text   |           | not null | 
Indexes:
    "customers_pkey" PRIMARY KEY, btree (id)
    "customers_email_key" UNIQUE CONSTRAINT, btree (email)
Referenced by:
    TABLE "orders" CONSTRAINT "orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customers(id)

 orders | customers | orphan_orders | fk_count 
--------+-----------+---------------+----------
    120 |        40 |             0 |        1
(1 row)

cd /tmp/migration
# Load pre-migration snapshot into a scratch table and diff everything
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 <<'SQL'
CREATE TEMP TABLE orders_before (
  id int, customer_name text, customer_email text, item text,
  amount_cents int, created_at timestamptz
);
\copy orders_before FROM '/tmp/migration/orders_before.csv' CSV HEADER

-- 1) preserved columns must be byte-identical per order id
SELECT count(*) AS preserved_col_mismatches
FROM orders_before b JOIN public.orders o ON o.id = b.id
WHERE o.item IS DISTINCT FROM b.item
   OR o.amount_cents IS DISTINCT FROM b.amount_cents
   OR o.created_at IS DISTINCT FROM b.created_at;

-- 2) id sets identical (no lost/added orders)
SELECT (SELECT count(*) FROM (SELECT id FROM orders_before EXCEPT SELECT id FROM public.orders) a) AS missing_ids,
       (SELECT count(*) FROM (SELECT id FROM public.orders EXCEPT SELECT id FROM orders_before) b) AS extra_ids;

-- 3) every order's customer_id resolves to its ORIGINAL (name, email)
SELECT count(*) AS identity_mismatches
FROM orders_before b
JOIN public.orders o ON o.id = b.id
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;
SQL

CREATE TABLE
COPY 120
 preserved_col_mismatches 
--------------------------
                        0
(1 row)

 missing_ids | extra_ids 
-------------+-----------
           0 |         0
(1 row)

 identity_mismatches 
---------------------
                   0
(1 row)

psql "$SUPABASE_DB_URL" -c "SELECT o.id, c.name, c.email, o.item, o.amount_cents FROM public.orders o JOIN public.customers c ON c.id=o.customer_id ORDER BY o.id LIMIT 3;" && psql "$SUPABASE_DB_URL" -tAc "SELECT count(*) FROM (SELECT email FROM public.customers GROUP BY email HAVING count(*)>1) d;"

 id |        name         |          email           |     item     | amount_cents 
----+---------------------+--------------------------+--------------+--------------
  1 | Customer-2869aff5cb | 66f4f3939508@example.com | SKU-87D9B53C |         3871
  2 | Customer-02e8dd9b22 | 909ae9a9012e@example.com | SKU-0E31CD9F |        80337
  3 | Customer-97b7f3c8c7 | a6b2c9ac98f9@example.com | SKU-C4C1B8CF |        56951
(3 rows)

0