0001_init.sql (2556B)
1 CREATE TABLE farms ( 2 id TEXT PRIMARY KEY NOT NULL, 3 display_name TEXT NOT NULL, 4 readiness TEXT NOT NULL CHECK (readiness IN ('incomplete', 'ready')), 5 created_at TEXT NOT NULL, 6 updated_at TEXT NOT NULL 7 ); 8 9 CREATE TABLE fulfillment_windows ( 10 id TEXT PRIMARY KEY NOT NULL, 11 farm_id TEXT NOT NULL REFERENCES farms(id) ON DELETE CASCADE, 12 starts_at TEXT NOT NULL, 13 ends_at TEXT NOT NULL, 14 capacity_limit INTEGER, 15 created_at TEXT NOT NULL, 16 updated_at TEXT NOT NULL 17 ); 18 19 CREATE TABLE products ( 20 id TEXT PRIMARY KEY NOT NULL, 21 farm_id TEXT NOT NULL REFERENCES farms(id) ON DELETE CASCADE, 22 title TEXT NOT NULL, 23 status TEXT NOT NULL CHECK (status IN ('draft', 'published', 'paused')), 24 stock_count INTEGER NOT NULL DEFAULT 0, 25 updated_at TEXT NOT NULL 26 ); 27 28 CREATE TABLE orders ( 29 id TEXT PRIMARY KEY NOT NULL, 30 farm_id TEXT NOT NULL REFERENCES farms(id) ON DELETE CASCADE, 31 fulfillment_window_id TEXT REFERENCES fulfillment_windows(id) ON DELETE SET NULL, 32 order_number TEXT NOT NULL, 33 customer_display_name TEXT NOT NULL, 34 status TEXT NOT NULL CHECK ( 35 status IN ('needs_action', 'scheduled', 'packed', 'completed') 36 ), 37 updated_at TEXT NOT NULL 38 ); 39 40 CREATE TABLE local_outbox ( 41 id TEXT PRIMARY KEY NOT NULL, 42 aggregate_kind TEXT NOT NULL, 43 aggregate_id TEXT NOT NULL, 44 operation_kind TEXT NOT NULL, 45 payload_json TEXT NOT NULL, 46 created_at TEXT NOT NULL, 47 available_at TEXT NOT NULL, 48 attempt_count INTEGER NOT NULL DEFAULT 0 49 ); 50 51 CREATE TABLE local_conflicts ( 52 id TEXT PRIMARY KEY NOT NULL, 53 aggregate_kind TEXT NOT NULL, 54 aggregate_id TEXT NOT NULL, 55 conflict_kind TEXT NOT NULL, 56 local_payload_json TEXT NOT NULL, 57 remote_payload_json TEXT, 58 detected_at TEXT NOT NULL, 59 resolved_at TEXT 60 ); 61 62 CREATE TABLE sync_checkpoints ( 63 id INTEGER PRIMARY KEY CHECK (id = 1), 64 last_sync_started_at TEXT, 65 last_sync_completed_at TEXT, 66 last_remote_cursor TEXT, 67 last_error_message TEXT 68 ); 69 70 CREATE INDEX idx_products_farm_status ON products(farm_id, status); 71 CREATE INDEX idx_orders_farm_status ON orders(farm_id, status); 72 CREATE INDEX idx_fulfillment_windows_farm_starts_at ON fulfillment_windows(farm_id, starts_at); 73 CREATE INDEX idx_local_outbox_available_at ON local_outbox(available_at); 74 CREATE INDEX idx_local_outbox_aggregate ON local_outbox(aggregate_kind, aggregate_id); 75 CREATE INDEX idx_local_conflicts_aggregate ON local_conflicts(aggregate_kind, aggregate_id); 76 77 INSERT INTO sync_checkpoints (id) VALUES (1);