minimax/minimax-m3 · d2_live_migration · rep 3

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

PGPASSWORD="$SUPABASE_DB_PASSWORD" psql "$SUPABASE_DB_URL" -c "\d public.orders" -c "select count(*) from public.orders" -c "select * from public.orders limit 5"

                           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)

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

 id |    customer_name    |      customer_email      |     item     | amount_cents |       created_at       
----+---------------------+--------------------------+--------------+--------------+------------------------
  1 | Customer-2869aff5cb | 66f4f3939508@example.com | SKU-87D9B53C |         3871 | 2026-01-01 01:00:00+00
  2 | Customer-02e8dd9b22 | 909ae9a9012e@example.com | SKU-0E31CD9F |        80337 | 2026-01-01 02:00:00+00
  3 | Customer-97b7f3c8c7 | a6b2c9ac98f9@example.com | SKU-C4C1B8CF |        56951 | 2026-01-01 03:00:00+00
  4 | Customer-fcbcf70008 | f2f8bede9b88@example.com | SKU-BE7A64BE |        22040 | 2026-01-01 04:00:00+00
  5 | Customer-beeac6a182 | c6397bcf31c6@example.com | SKU-7C3D3496 |         5665 | 2026-01-01 05:00:00+00
(5 rows)

PGPASSWORD="$SUPABASE_DB_PASSWORD" psql "$SUPABASE_DB_URL" <<'SQL'
select 'distinct_emails' as metric, count(*) from (select distinct customer_email from public.orders) e
union all
select 'total_orders', count(*) from public.orders
union all
select 'email_name_pairs', count(*) from (select distinct customer_email, customer_name from public.orders) p
union all
select 'emails_with_multiple_names', count(*) from (
  select customer_email from public.orders group by customer_email having count(distinct customer_name) > 1
) x;
SQL

           metric           | count 
----------------------------+-------
 distinct_emails            |    40
 total_orders               |   120
 email_name_pairs           |    40
 emails_with_multiple_names |     0
(4 rows)

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

-- 1) Create customers table with one row per distinct email currently in orders,
--    carrying the customer's name. Using DISTINCT (email, name) is safe given
--    the prior check that no email has multiple names.
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_name, customer_email
order by min(id);  -- deterministic insertion order

-- 2) Add customer_id column (nullable for the backfill step).
alter table public.orders
  add column customer_id bigint;

-- Backfill customer_id by joining to customers on (name, email).
update public.orders o
   set customer_id = c.id
  from public.customers c
 where c.name = o.customer_name
   and c.email = o.customer_email;

-- Enforce NOT NULL + FK.
alter table public.orders
  alter column customer_id set not null;

alter table public.orders
  add constraint orders_customer_id_fkey
  foreign key (customer_id) references public.customers(id);

-- 3) Drop the embedded identity columns.
alter table public.orders drop column customer_name;
alter table public.orders drop column customer_email;

commit;
SQL

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

WARNING:  there is already a transaction in progress
WARNING:  there is no transaction in progress

PGPASSWORD="$SUPABASE_DB_PASSWORD" psql "$SUPABASE_DB_URL" <<'SQL'
\d public.orders
\d public.customers

-- Counts
select 'orders_count' as metric, count(*) from public.orders
union all
select 'customers_count', count(*) from public.customers;

-- Survival: every order has a customer_id
select 'orders_with_null_customer_id' as metric, count(*) from public.orders where customer_id is null;

-- FK integrity: no orphan customer_ids
select 'orphan_customer_ids' as metric, count(*) from public.orders o where not exists (select 1 from public.customers c where c.id = o.customer_id);

-- Distinct customer_ids in orders should equal distinct emails originally (40)
select 'distinct_customer_ids_in_orders' as metric, count(distinct customer_id) from public.orders;

-- Other order columns unchanged: id, item, amount_cents, created_at must be byte-identical to before.
-- Compare a hash of those 4 columns pre/post (we already mutated, but assert no NULLs / sane values).
select 'null_in_core_cols' as metric, count(*) from public.orders where id is null or item is null or amount_cents is null or created_at is null;

-- orders.id range and contiguity sanity
select min(id), max(id), count(distinct id) from public.orders;

-- Spot check: each customer has at least one order, and customer count == 40 distinct emails
select (select count(*) from public.customers) as customers,
       (select count(distinct customer_id) from public.orders) as distinct_customers_in_orders;
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)

     metric      | count 
-----------------+-------
 orders_count    |   120
 customers_count |    40
(2 rows)

            metric            | count 
------------------------------+-------
 orders_with_null_customer_id |     0
(1 row)

       metric        | count 
---------------------+-------
 orphan_customer_ids |     0
(1 row)

             metric              | count 
---------------------------------+-------
 distinct_customer_ids_in_orders |    40
(1 row)

      metric       | count 
-------------------+-------
 null_in_core_cols |     0
(1 row)

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

 customers | distinct_customers_in_orders 
-----------+------------------------------
        40 |                           40
(1 row)

PGPASSWORD="$SUPABASE_DB_PASSWORD" psql "$SUPABASE_DB_URL" <<'SQL'
-- Show a few orders with their joined customer to confirm correct backfill
select o.id, c.name, c.email, o.item, o.amount_cents, o.created_at
from public.orders o
join public.customers c on c.id = o.customer_id
order by o.id
limit 5;

-- Aggregate amount per customer to ensure totals match a manual recomputation
select c.email, count(*) as n_orders, sum(o.amount_cents) as total_cents
from public.orders o join public.customers c on c.id = o.customer_id
group by c.email
order by c.email
limit 5;
SQL

 id |        name         |          email           |     item     | amount_cents |       created_at       
----+---------------------+--------------------------+--------------+--------------+------------------------
  1 | Customer-2869aff5cb | 66f4f3939508@example.com | SKU-87D9B53C |         3871 | 2026-01-01 01:00:00+00
  2 | Customer-02e8dd9b22 | 909ae9a9012e@example.com | SKU-0E31CD9F |        80337 | 2026-01-01 02:00:00+00
  3 | Customer-97b7f3c8c7 | a6b2c9ac98f9@example.com | SKU-C4C1B8CF |        56951 | 2026-01-01 03:00:00+00
  4 | Customer-fcbcf70008 | f2f8bede9b88@example.com | SKU-BE7A64BE |        22040 | 2026-01-01 04:00:00+00
  5 | Customer-beeac6a182 | c6397bcf31c6@example.com | SKU-7C3D3496 |         5665 | 2026-01-01 05:00:00+00
(5 rows)

          email           | n_orders | total_cents 
--------------------------+----------+-------------
 1d4a857f6a1c@example.com |        3 |       86171
 1e60620a670c@example.com |        3 |      200169
 202bb5897646@example.com |        3 |      110129
 25e610157424@example.com |        3 |      177538
 26e048a730f7@example.com |        3 |       41851
(5 rows)