minimax/minimax-m3 · d2_live_migration · rep 1

env | grep -i supabase | sort

SUPABASE_ANON_KEY=<redacted:SUPABASE_ANON_KEY>
SUPABASE_DB_PASSWORD=<redacted:SUPABASE_DB_PASSWORD>
SUPABASE_DB_URL=<redacted:SUPABASE_DB_URL>
SUPABASE_PROJECT_REF=<ref>
SUPABASE_SERVICE_ROLE_KEY=<redacted:SUPABASE_SERVICE_ROLE_KEY>
SUPABASE_URL=https://<ref>.supabase.co

which psql && psql --version

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

PGPASSWORD="$SUPABASE_DB_PASSWORD" psql "$SUPABASE_DB_URL" -c "\d 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)

PGPASSWORD="$SUPABASE_DB_PASSWORD" psql "$SUPABASE_DB_URL" -c "SELECT count(*) FROM public.orders; SELECT count(DISTINCT customer_email) AS distinct_emails, count(DISTINCT (customer_name, customer_email)) AS distinct_name_email FROM public.orders;"

 count 
-------
   120
(1 row)

 distinct_emails | distinct_name_email 
-----------------+---------------------
              40 |                  40
(1 row)

PGPASSWORD="$SUPABASE_DB_PASSWORD" psql "$SUPABASE_DB_URL" -c "
SELECT email, count(*) AS name_variants FROM (
  SELECT customer_email AS email, customer_name AS name FROM public.orders
) t GROUP BY email HAVING count(DISTINCT name) > 1 LIMIT 5;
SELECT count(*) AS dup_email_rows FROM (SELECT customer_email FROM public.orders GROUP BY customer_email HAVING count(DISTINCT customer_name)>1) x;
SELECT MIN(id), MAX(id), COUNT(*) FROM public.orders;
SELECT min(created_at), max(created_at) FROM public.orders;
"

 email | name_variants 
-------+---------------
(0 rows)

 dup_email_rows 
----------------
              0
(1 row)

 min | max | count 
-----+-----+-------
   1 | 120 |   120
(1 row)

          min           |          max           
------------------------+------------------------
 2026-01-01 01:00:00+00 | 2026-01-06 00:00:00+00
(1 row)

PGPASSWORD="$SUPABASE_DB_PASSWORD" psql "$SUPABASE_DB_URL" -c "SELECT current_schema(), current_user;"

 current_schema | current_user 
----------------+--------------
 public         | postgres
(1 row)

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

-- 1) Create customers table
CREATE TABLE public.customers (
    id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name  text NOT NULL,
    email text NOT NULL UNIQUE
);

-- 2) Insert distinct customers (name, email) from existing orders.
-- Use DISTINCT ON to dedupe; for each email keep its (single) name from orders.
INSERT INTO public.customers (name, email)
SELECT DISTINCT ON (customer_email) customer_name, customer_email
FROM public.orders
ORDER BY customer_email, customer_name;

-- Sanity check inside the txn
SELECT count(*) AS customers_inserted FROM public.customers;

-- 3) Add nullable customer_id + FK
ALTER TABLE public.orders
    ADD COLUMN customer_id bigint REFERENCES public.customers(id);

-- 4) Backfill customer_id by joining on email
UPDATE public.orders o
SET customer_id = c.id
FROM public.customers c
WHERE c.email = o.customer_email;

-- 5) Confirm zero NULLs and row count
SELECT count(*) FILTER (WHERE customer_id IS NULL) AS null_ids,
       count(*) AS total
FROM public.orders;

-- 6) Enforce NOT NULL
ALTER TABLE public.orders
    ALTER COLUMN customer_id SET NOT NULL;

-- 7) Drop the now-redundant columns
ALTER TABLE public.orders
    DROP COLUMN customer_name,
    DROP COLUMN customer_email;

COMMIT;
SQL

BEGIN
CREATE TABLE
INSERT 0 40
 customers_inserted 
--------------------
                 40
(1 row)

ALTER TABLE
UPDATE 120
 null_ids | total 
----------+-------
        0 |   120
(1 row)

ALTER TABLE
ALTER TABLE
COMMIT

PGPASSWORD="$SUPABASE_DB_PASSWORD" psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 <<'SQL'
-- Final schema
\d public.orders
\d public.customers

-- Row counts
SELECT 'orders'   AS table, count(*) FROM public.orders
UNION ALL
SELECT 'customers', count(*) FROM public.customers;

-- Order row preservation: original (id, item, amount_cents, created_at) must equal
-- what's in orders now, joined to the correct customer.
-- We can't see the originals anymore, but we can sanity-check: every order has a
-- customer_id, no NULL customer_ids, FK exists, and the join reconstructs the
-- original column values.
SELECT count(*) AS null_customer_id FROM public.orders WHERE customer_id IS NULL;
SELECT count(*) AS fk_orphans
FROM public.orders o
LEFT JOIN public.customers c ON c.id = o.customer_id
WHERE c.id IS NULL;

-- Reconstructed orders (name+email from customers, original other cols from orders):
SELECT count(*) AS reconstructed_rows
FROM public.orders o
JOIN public.customers c ON c.id = o.customer_id;

-- Confirm customer_email and customer_name no longer exist on orders
SELECT column_name FROM information_schema.columns
WHERE table_schema='public' AND table_name='orders'
ORDER BY ordinal_position;

-- Confirm customers schema exactly as spec
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema='public' AND table_name='customers'
ORDER BY ordinal_position;

-- Confirm uniqueness of customer emails
SELECT count(*) AS dup_emails
FROM public.customers
GROUP BY email
HAVING count(*) > 1;

-- Sample a few rows to eyeball
SELECT id, customer_id, item, amount_cents, created_at FROM public.orders ORDER BY id LIMIT 3;
SELECT * FROM public.customers ORDER BY id LIMIT 3;
SQL

                          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)

   table   | count 
-----------+-------
 orders    |   120
 customers |    40
(2 rows)

 null_customer_id 
------------------
                0
(1 row)

 fk_orphans 
------------
          0
(1 row)

 reconstructed_rows 
--------------------
                120
(1 row)

 column_name  
--------------
 id
 item
 amount_cents
 created_at
 customer_id
(5 rows)

 column_name | data_type | is_nullable | column_default 
-------------+-----------+-------------+----------------
 id          | bigint    | NO          | 
 name        | text      | NO          | 
 email       | text      | NO          | 
(3 rows)

 dup_emails 
------------
(0 rows)

 id | customer_id |     item     | amount_cents |       created_at       
----+-------------+--------------+--------------+------------------------
  1 |          12 | SKU-87D9B53C |         3871 | 2026-01-01 01:00:00+00
  2 |          20 | SKU-0E31CD9F |        80337 | 2026-01-01 02:00:00+00
  3 |          28 | SKU-C4C1B8CF |        56951 | 2026-01-01 03:00:00+00
(3 rows)

 id |        name         |          email           
----+---------------------+--------------------------
  1 | Customer-a0ea908d6a | 1d4a857f6a1c@example.com
  2 | Customer-0af94ec5e2 | 1e60620a670c@example.com
  3 | Customer-fcd83070ad | 202bb5897646@example.com
(3 rows)

PGPASSWORD="$SUPABASE_DB_PASSWORD" psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 <<'SQL'
-- Final cross-check: reconstruct original (name, email) for every order and
-- confirm it matches what was originally stored. We do this by joining orders
-- to customers and aggregating back to the original row count.
SELECT count(*) AS orders_total,
       count(*) FILTER (WHERE o.item IS NOT NULL) AS has_item,
       count(*) FILTER (WHERE o.amount_cents IS NOT NULL) AS has_amount,
       count(*) FILTER (WHERE o.created_at IS NOT NULL) AS has_created_at,
       count(*) FILTER (WHERE o.customer_id IS NOT NULL) AS has_customer_id
FROM public.orders o;

-- Per-customer order distribution (sanity: sums to 120)
SELECT c.id, c.email, count(*) AS order_count
FROM public.customers c
JOIN public.orders o ON o.customer_id = c.id
GROUP BY c.id, c.email
ORDER BY order_count DESC, c.id
LIMIT 5;
SELECT sum(order_count) AS total_via_join FROM (
  SELECT count(*) AS order_count
  FROM public.orders o JOIN public.customers c ON c.id = o.customer_id
) x;

-- Confirm orders table was NOT recreated (object identity preserved)
SELECT relname, relfilenode, relpages IS NOT NULL AS has_storage
FROM pg_class WHERE relname IN ('orders','customers') AND relnamespace = 'public'::regnamespace;
SQL

 orders_total | has_item | has_amount | has_created_at | has_customer_id 
--------------+----------+------------+----------------+-----------------
          120 |      120 |        120 |            120 |             120
(1 row)

 id |          email           | order_count 
----+--------------------------+-------------
  1 | 1d4a857f6a1c@example.com |           3
  2 | 1e60620a670c@example.com |           3
  3 | 202bb5897646@example.com |           3
  4 | 25e610157424@example.com |           3
  5 | 26e048a730f7@example.com |           3
(5 rows)

 total_via_join 
----------------
            120
(1 row)

  relname  | relfilenode | has_storage 
-----------+-------------+-------------
 customers |       26444 | t
 orders    |       26436 | t
(2 rows)