z-ai/glm-5.2 · d2_live_migration · rep 2

echo "URL: $SUPABASE_URL"; echo "REF: $SUPABASE_PROJECT_REF"; echo "DB_URL: $SUPABASE_DB_URL"; echo "Anon key set: $([ -n "$SUPABASE_ANON_KEY" ] && echo yes)"; echo "Service key set: $([ -n "$SUPABASE_SERVICE_ROLE_KEY" ] && echo yes)"; echo "DB password set: $([ -n "$SUPABASE_DB_PASSWORD" ] && echo yes)"

URL: https://<ref>.supabase.co
REF: <ref>
DB_URL: <redacted:SUPABASE_DB_URL>
Anon key set: yes
Service key set: yes
DB password set: yes

export PGPASSWORD='<redacted:SUPABASE_DB_PASSWORD>'
psql "$SUPABASE_DB_URL" -c "SELECT count(*) FROM public.orders;"
psql "$SUPABASE_DB_URL" -c "\d public.orders"
psql "$SUPABASE_DB_URL" -c "SELECT count(DISTINCT customer_email) AS distinct_emails, count(DISTINCT (customer_name, customer_email)) AS distinct_name_email_pairs FROM public.orders;"

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

                           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)

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

export PGPASSWORD='<redacted:SUPABASE_DB_PASSWORD>'
# Check if any email maps to multiple distinct names
psql "$SUPABASE_DB_URL" -c "SELECT customer_email, count(DISTINCT customer_name) AS name_count FROM public.orders GROUP BY customer_email HAVING count(DISTINCT customer_name) > 1;"

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

export PGPASSWORD='<redacted:SUPABASE_DB_PASSWORD>'
psql "$SUPABASE_DB_URL" <<'SQL'
BEGIN;

-- 1) Create customers table with one row per distinct customer email
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 customer_name, customer_email
FROM   public.orders
ORDER  BY customer_email;

-- 2) Add customer_id column (nullable temporarily)
ALTER TABLE public.orders ADD COLUMN customer_id bigint;

-- Backfill: point every order to the matching customers row
UPDATE public.orders o
SET    customer_id = c.id
FROM   public.customers c
WHERE  o.customer_email = c.email
  AND  o.customer_name  = c.name;

-- Verify every row was backfilled
DO $$
DECLARE unbackfilled int;
BEGIN
  SELECT count(*) INTO unbackfilled FROM public.orders WHERE customer_id IS NULL;
  IF unbackfilled > 0 THEN
    RAISE EXCEPTION 'Backfill failed: % orders have NULL customer_id', unbackfilled;
  END IF;
END $$;

-- Add FK and enforce NOT NULL
ALTER TABLE public.orders
  ADD CONSTRAINT orders_customer_id_fkey
  FOREIGN KEY (customer_id) REFERENCES public.customers(id);

ALTER TABLE public.orders
  ALTER COLUMN customer_id SET NOT NULL;

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

COMMIT;
SQL

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

export PGPASSWORD='<redacted:SUPABASE_DB_PASSWORD>'
echo "=== orders table structure ==="
psql "$SUPABASE_DB_URL" -c "\d public.orders"
echo "=== customers table structure ==="
psql "$SUPABASE_DB_URL" -c "\d public.customers"
echo "=== row counts ==="
psql "$SUPABASE_DB_URL" -c "SELECT (SELECT count(*) FROM public.orders) AS orders_count, (SELECT count(*) FROM public.customers) AS customers_count;"
echo "=== referential integrity check ==="
psql "$SUPABASE_DB_URL" -c "SELECT count(*) AS orphaned_orders FROM public.orders o LEFT JOIN public.customers c ON o.customer_id = c.id WHERE c.id IS NULL;"
echo "=== sample data ==="
psql "$SUPABASE_DB_URL" -c "SELECT o.id, o.item, o.amount_cents, o.created_at, o.customer_id, c.name, c.email FROM public.orders o JOIN public.customers c ON o.customer_id = c.id ORDER BY o.id LIMIT 5;"

=== orders table structure ===
                          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)

=== customers table structure ===
                       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)

=== row counts ===
 orders_count | customers_count 
--------------+-----------------
          120 |              40
(1 row)

=== referential integrity check ===
 orphaned_orders 
-----------------
               0
(1 row)

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