-- ============================================================ -- delete-orders-before-2026-02-10.sql -- -- DESTRUCTIVE. Deletes every order created before 2026-02-10 (midnight, -- exclusive -- anything on/after 2026-02-10 00:00 is kept) and the rows that -- hang off those orders. -- -- Cutoff: `created_at < '2026-02-10'` compares correctly for the timestamp -- shapes the app writes ('2026-02-09T23:00:00.000Z' and '2026-02-09 23:00:00'). -- -- Schema: this targets the post-0006 database (applied to the remote now). -- 0006 removed online payments, so `payments`, `refunds` and `payment_info` no -- longer exist and `orders.payment_info_id` is gone -- none are referenced here. -- (The same statements also run on a pre-0006 copy; they just leave the dropped -- payment tables alone.) -- -- Per order it: -- 1. detaches coupon_usage / complaints / user_incidents (rows kept, order -- references set to NULL, so coupon limits and complaint history survive) -- 2. deletes its order_items and order_status rows -- 3. deletes the order -- -- Post-0006 orders whose items were archived into `orders.items_json` have no -- `order_items` rows, so only the order/status row is removed for them. -- -- Counts from the local dump at time of writing (pre-0006 copy): -- 110 of 2548 orders (oldest 2025-12-24), 194 order_items, 110 order_status, -- 23 coupon_usage detached, 1 complaint detached, 0 user_incidents. -- Counts will differ on the target database. -- -- Apply from apps/backend: -- wrangler d1 execute freshyo-backend-dev --file=../../address_util/delete-orders-before-2026-02-10.sql --config wrangler.dev.toml --remote -- -- Dry run (counts only): -- SELECT COUNT(*) FROM orders WHERE created_at < '2026-02-10'; -- ============================================================ -- 1. Preserve cross-cutting history; drop only the order references. UPDATE coupon_usage SET order_id = NULL, order_item_id = NULL WHERE order_id IN (SELECT id FROM orders WHERE created_at < '2026-02-10') OR order_item_id IN ( SELECT oi.id FROM order_items oi JOIN orders o ON o.id = oi.order_id WHERE o.created_at < '2026-02-10' ); UPDATE complaints SET order_id = NULL WHERE order_id IN (SELECT id FROM orders WHERE created_at < '2026-02-10'); UPDATE user_incidents SET order_id = NULL WHERE order_id IN (SELECT id FROM orders WHERE created_at < '2026-02-10'); -- 2. Delete the order-owned item/status rows. DELETE FROM order_items WHERE order_id IN (SELECT id FROM orders WHERE created_at < '2026-02-10'); DELETE FROM order_status WHERE order_id IN (SELECT id FROM orders WHERE created_at < '2026-02-10'); -- 3. Delete the orders themselves. DELETE FROM orders WHERE created_at < '2026-02-10'; -- Verification (run after applying): -- SELECT COUNT(*) FROM orders WHERE created_at < '2026-02-10'; -- expect 0 -- SELECT COUNT(*) FROM order_items -- WHERE order_id NOT IN (SELECT id FROM orders); -- expect 0 -- SELECT COUNT(*) FROM order_status -- WHERE order_id NOT IN (SELECT id FROM orders); -- expect 0 -- SELECT COUNT(*) FROM coupon_usage -- WHERE order_id IS NOT NULL -- AND order_id NOT IN (SELECT id FROM orders); -- expect 0 -- SELECT COUNT(*) FROM complaints -- WHERE order_id IS NOT NULL -- AND order_id NOT IN (SELECT id FROM orders); -- expect 0 -- SELECT COUNT(*) FROM user_incidents -- WHERE order_id IS NOT NULL -- AND order_id NOT IN (SELECT id FROM orders); -- expect 0 -- PRAGMA foreign_key_check; -- expect no rows