0025_order_workflow_agreement_projection.sql (5721B)
1 DROP INDEX IF EXISTS idx_order_lines_order_sort; 2 DROP INDEX IF EXISTS idx_buyer_order_coordination_context_state_updated_at; 3 DROP INDEX IF EXISTS idx_buyer_order_coordination_state_updated_at; 4 DROP INDEX IF EXISTS idx_orders_farm_status; 5 DROP INDEX IF EXISTS idx_orders_farm_window_status_updated_at; 6 DROP INDEX IF EXISTS idx_orders_buyer_context_updated_at; 7 8 ALTER TABLE order_lines RENAME TO order_lines_agreement_legacy; 9 ALTER TABLE buyer_order_coordination_records RENAME TO buyer_order_coordination_records_agreement_legacy; 10 ALTER TABLE orders RENAME TO orders_agreement_legacy; 11 12 CREATE TABLE orders ( 13 id TEXT PRIMARY KEY NOT NULL, 14 farm_id TEXT NOT NULL REFERENCES farms(id) ON DELETE CASCADE, 15 fulfillment_window_id TEXT REFERENCES fulfillment_windows(id) ON DELETE SET NULL, 16 order_number TEXT NOT NULL, 17 customer_display_name TEXT NOT NULL, 18 status TEXT NOT NULL CHECK ( 19 status IN ('needs_action', 'scheduled', 'packed', 'completed', 'declined', 'needs_review') 20 ), 21 updated_at TEXT NOT NULL, 22 buyer_context_key TEXT, 23 buyer_email TEXT NOT NULL DEFAULT '', 24 buyer_phone TEXT NOT NULL DEFAULT '', 25 buyer_order_note TEXT NOT NULL DEFAULT '', 26 workflow_revision TEXT NOT NULL DEFAULT 'none' CHECK ( 27 workflow_revision IN ('none', 'change_proposed', 'updated', 'kept_as_placed') 28 ), 29 workflow_agreement TEXT NOT NULL DEFAULT 'ordered' CHECK ( 30 workflow_agreement IN ('ordered', 'confirmed', 'declined', 'cancelled', 'needs_review') 31 ), 32 workflow_inventory TEXT NOT NULL DEFAULT 'needs_review' CHECK ( 33 workflow_inventory IN ('available', 'reserved', 'sold_out', 'needs_review') 34 ), 35 workflow_provenance_source TEXT NOT NULL DEFAULT 'unknown' CHECK ( 36 workflow_provenance_source IN ('app', 'cli', 'relay', 'local_events', 'unknown') 37 ), 38 workflow_provenance_last_event_id TEXT 39 ); 40 41 INSERT INTO orders ( 42 id, 43 farm_id, 44 fulfillment_window_id, 45 order_number, 46 customer_display_name, 47 status, 48 updated_at, 49 buyer_context_key, 50 buyer_email, 51 buyer_phone, 52 buyer_order_note, 53 workflow_revision, 54 workflow_agreement, 55 workflow_inventory, 56 workflow_provenance_source, 57 workflow_provenance_last_event_id 58 ) 59 SELECT 60 id, 61 farm_id, 62 fulfillment_window_id, 63 order_number, 64 customer_display_name, 65 status, 66 updated_at, 67 buyer_context_key, 68 buyer_email, 69 buyer_phone, 70 buyer_order_note, 71 workflow_revision, 72 workflow_agreement, 73 workflow_inventory, 74 workflow_provenance_source, 75 workflow_provenance_last_event_id 76 FROM orders_agreement_legacy; 77 78 CREATE TABLE order_lines ( 79 id TEXT PRIMARY KEY NOT NULL, 80 order_id TEXT NOT NULL REFERENCES orders(id) ON DELETE CASCADE, 81 title TEXT NOT NULL, 82 quantity_value INTEGER NOT NULL CHECK (quantity_value >= 0), 83 quantity_unit_label TEXT NOT NULL DEFAULT '', 84 quantity_display TEXT NOT NULL, 85 sort_index INTEGER NOT NULL DEFAULT 0, 86 listing_bin_id TEXT, 87 unit_price_minor_units INTEGER CHECK ( 88 unit_price_minor_units IS NULL OR unit_price_minor_units >= 0 89 ), 90 price_currency TEXT NOT NULL DEFAULT 'USD', 91 farm_key TEXT, 92 listing_addr TEXT, 93 listing_event_id TEXT, 94 seller_pubkey TEXT, 95 listing_relays_json TEXT 96 ); 97 98 INSERT INTO order_lines ( 99 id, 100 order_id, 101 title, 102 quantity_value, 103 quantity_unit_label, 104 quantity_display, 105 sort_index, 106 listing_bin_id, 107 unit_price_minor_units, 108 price_currency, 109 farm_key, 110 listing_addr, 111 listing_event_id, 112 seller_pubkey, 113 listing_relays_json 114 ) 115 SELECT 116 id, 117 order_id, 118 title, 119 quantity_value, 120 quantity_unit_label, 121 quantity_display, 122 sort_index, 123 listing_bin_id, 124 unit_price_minor_units, 125 price_currency, 126 farm_key, 127 listing_addr, 128 listing_event_id, 129 seller_pubkey, 130 listing_relays_json 131 FROM order_lines_agreement_legacy; 132 133 CREATE TABLE buyer_order_coordination_records ( 134 order_id TEXT PRIMARY KEY NOT NULL REFERENCES orders(id) ON DELETE CASCADE, 135 buyer_context_key TEXT NOT NULL, 136 record_id TEXT, 137 state TEXT NOT NULL CHECK (state IN ('pending', 'synced', 'failed')), 138 payload_json TEXT, 139 attempt_count INTEGER NOT NULL DEFAULT 0 CHECK (attempt_count >= 0), 140 last_error_message TEXT, 141 created_at TEXT NOT NULL, 142 updated_at TEXT NOT NULL, 143 synced_at TEXT 144 ); 145 146 INSERT INTO buyer_order_coordination_records ( 147 order_id, 148 buyer_context_key, 149 record_id, 150 state, 151 payload_json, 152 attempt_count, 153 last_error_message, 154 created_at, 155 updated_at, 156 synced_at 157 ) 158 SELECT 159 order_id, 160 buyer_context_key, 161 record_id, 162 state, 163 payload_json, 164 attempt_count, 165 last_error_message, 166 created_at, 167 updated_at, 168 synced_at 169 FROM buyer_order_coordination_records_agreement_legacy; 170 171 CREATE INDEX idx_orders_farm_status ON orders(farm_id, status); 172 CREATE INDEX idx_orders_farm_window_status_updated_at 173 ON orders(farm_id, fulfillment_window_id, status, updated_at DESC, id DESC); 174 CREATE INDEX idx_orders_buyer_context_updated_at 175 ON orders(buyer_context_key, updated_at DESC, id DESC) 176 WHERE buyer_context_key IS NOT NULL AND trim(buyer_context_key) <> ''; 177 CREATE INDEX idx_order_lines_order_sort 178 ON order_lines(order_id, sort_index, id); 179 CREATE INDEX idx_buyer_order_coordination_context_state_updated_at 180 ON buyer_order_coordination_records(buyer_context_key, state, updated_at); 181 CREATE INDEX idx_buyer_order_coordination_state_updated_at 182 ON buyer_order_coordination_records(state, updated_at); 183 184 DROP TABLE order_lines_agreement_legacy; 185 DROP TABLE buyer_order_coordination_records_agreement_legacy; 186 DROP TABLE orders_agreement_legacy;