command -v psql && psql --version
/usr/bin/psql psql (PostgreSQL) 18.4 (Debian 18.4-1.pgdg12+1)
printf 'URL=%s REF=%s ANON=%s SERVICE=%s DBURL=%s DBPASS=%s\n' "${SUPABASE_URL:+set}" "${SUPABASE_PROJECT_REF:+set}" "${SUPABASE_ANON_KEY:+set}" "${SUPABASE_SERVICE_ROLE_KEY:+set}" "${SUPABASE_DB_URL:+set}" "${SUPABASE_DB_PASSWORD:+set}"URL=set REF=set ANON=set SERVICE=set DBURL=set DBPASS=set
PGPASSWORD="$SUPABASE_DB_PASSWORD" psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 -P pager=off <<'SQL'
SELECT count(*) AS total_orders, count(DISTINCT customer_email) AS distinct_emails,
count(*) FILTER (WHERE customer_name IS NULL OR customer_email IS NULL) AS null_identities
FROM public.orders;
SELECT customer_email, count(DISTINCT customer_name) AS name_count, array_agg(DISTINCT 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 total_orders | distinct_emails | null_identities
--------------+-----------------+-----------------
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)
PGPASSWORD="$SUPABASE_DB_PASSWORD" psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 -P pager=off -c "SELECT to_regclass('public.customers') AS existing_customers;"existing_customers -------------------- (1 row)
PGPASSWORD="$SUPABASE_DB_PASSWORD" psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 -P pager=off <<'SQL'
BEGIN;
SET LOCAL lock_timeout = '15s';
SET LOCAL statement_timeout = '2min';
LOCK TABLE public.orders IN ACCESS EXCLUSIVE MODE;
DO $$
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 customer_email FROM public.orders
GROUP BY customer_email
HAVING count(DISTINCT customer_name) <> 1
) THEN
RAISE EXCEPTION 'Safety check failed: an email maps to multiple names';
END IF;
END $$;
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 $$
BEGIN
IF (SELECT count(*) FROM public.orders) <> 120 THEN
RAISE EXCEPTION 'Order count changed during migration';
END IF;
IF (SELECT count(*) FROM public.customers) <> (SELECT count(DISTINCT customer_email) FROM public.orders) THEN
RAISE EXCEPTION 'Customer cardinality mismatch';
END IF;
IF EXISTS (
SELECT 1
FROM public.orders o
LEFT JOIN public.customers c
ON c.id = o.customer_id
AND c.email = o.customer_email
AND c.name = o.customer_name
WHERE c.id IS NULL
) THEN
RAISE EXCEPTION 'Backfill verification failed';
END IF;
END $$;
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;
SQLBEGIN SET SET LOCK TABLE DO CREATE TABLE INSERT 0 40 ALTER TABLE UPDATE 120 DO ALTER TABLE ALTER TABLE COMMIT
PGPASSWORD="$SUPABASE_DB_PASSWORD" psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 -P pager=off <<'SQL'
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 orphaned_orders;
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema='public' AND table_name IN ('orders','customers')
ORDER BY table_name, ordinal_position;
SELECT conname, contype, pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid IN ('public.orders'::regclass, 'public.customers'::regclass)
ORDER BY conrelid::regclass::text, conname;
SQL orders | customers | orphaned_orders
--------+-----------+-----------------
120 | 40 | 0
(1 row)
column_name | data_type | is_nullable | column_default
--------------+--------------------------+-------------+----------------
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)
conname | contype | definition
-------------------------+---------+----------------------------------------------------
customers_email_key | u | UNIQUE (email)
customers_pkey | p | PRIMARY KEY (id)
orders_customer_id_fkey | f | FOREIGN KEY (customer_id) REFERENCES customers(id)
orders_pkey | p | PRIMARY KEY (id)
(4 rows)
A shell script executed against the live project and asserted on the state the agent left behind. No model judged another model.
Raw transcript · Task definition and verifier · Every attempt