legacy_outbox_v1.sql (5470B)
1 CREATE TABLE IF NOT EXISTS outbox_operations ( 2 operation_id INTEGER PRIMARY KEY AUTOINCREMENT, 3 operation_kind TEXT NOT NULL, 4 expected_pubkey TEXT NOT NULL, 5 semantic_scope TEXT NOT NULL CHECK (semantic_scope IN ('generic_event', 'trade_mutation')), 6 trade_id TEXT, 7 mutation_id TEXT, 8 canonical_payload_sha256 TEXT CHECK (canonical_payload_sha256 IS NULL OR length(canonical_payload_sha256) = 64), 9 idempotency_key TEXT, 10 operation_idempotency_digest TEXT NOT NULL, 11 status TEXT NOT NULL CHECK (status IN ('queued', 'complete', 'failed_terminal', 'cancelled')), 12 created_at_ms INTEGER NOT NULL, 13 updated_at_ms INTEGER NOT NULL, 14 CHECK ( 15 (semantic_scope = 'generic_event' AND trade_id IS NULL AND mutation_id IS NULL AND canonical_payload_sha256 IS NULL) 16 OR (semantic_scope = 'trade_mutation' AND trade_id IS NOT NULL AND mutation_id IS NOT NULL AND canonical_payload_sha256 IS NOT NULL) 17 ) 18 ); 19 20 CREATE UNIQUE INDEX IF NOT EXISTS outbox_operation_idempotency_idx 21 ON outbox_operations(operation_kind, expected_pubkey, idempotency_key) 22 WHERE idempotency_key IS NOT NULL; 23 24 CREATE INDEX IF NOT EXISTS outbox_operation_status_idx 25 ON outbox_operations(status, created_at_ms, operation_id); 26 27 CREATE UNIQUE INDEX IF NOT EXISTS outbox_operation_trade_mutation_idx 28 ON outbox_operations(operation_kind, expected_pubkey, mutation_id) 29 WHERE semantic_scope = 'trade_mutation'; 30 31 CREATE TABLE IF NOT EXISTS outbox_event ( 32 outbox_event_id INTEGER PRIMARY KEY AUTOINCREMENT, 33 operation_id INTEGER NOT NULL REFERENCES outbox_operations(operation_id) ON DELETE CASCADE, 34 event_id TEXT NOT NULL, 35 expected_pubkey TEXT NOT NULL, 36 draft_json TEXT NOT NULL, 37 signed_event_json TEXT, 38 raw_event_json TEXT, 39 state TEXT NOT NULL CHECK (state IN ('draft_queued', 'signing', 'signed', 'publishing', 'published', 'sign_retryable', 'publish_retryable', 'failed_terminal', 'cancelled')), 40 attempt_count INTEGER NOT NULL, 41 claim_token TEXT, 42 claim_owner TEXT, 43 claim_expires_at_ms INTEGER, 44 active_delivery_plan_id INTEGER REFERENCES outbox_delivery_plan(delivery_plan_id) ON DELETE SET NULL, 45 next_attempt_after_ms INTEGER NOT NULL, 46 last_error TEXT, 47 event_store_ingested INTEGER NOT NULL, 48 event_store_inserted INTEGER NOT NULL, 49 event_store_ingested_at_ms INTEGER, 50 created_at_ms INTEGER NOT NULL, 51 updated_at_ms INTEGER NOT NULL 52 ); 53 54 CREATE INDEX IF NOT EXISTS outbox_event_ready_idx 55 ON outbox_event(state, next_attempt_after_ms, claim_expires_at_ms, created_at_ms, outbox_event_id); 56 57 CREATE INDEX IF NOT EXISTS outbox_event_event_id_idx 58 ON outbox_event(event_id); 59 60 CREATE TABLE IF NOT EXISTS outbox_delivery_plan ( 61 delivery_plan_id INTEGER PRIMARY KEY AUTOINCREMENT, 62 outbox_event_id INTEGER NOT NULL REFERENCES outbox_event(outbox_event_id) ON DELETE CASCADE, 63 transport_profile_id TEXT NOT NULL, 64 target_policy_fingerprint TEXT NOT NULL, 65 target_policy_version INTEGER NOT NULL, 66 satisfaction_policy TEXT NOT NULL, 67 required_success_count INTEGER NOT NULL, 68 delivery_plan_idempotency_digest TEXT NOT NULL, 69 status TEXT NOT NULL CHECK (status IN ('queued', 'complete', 'failed_terminal', 'cancelled')), 70 satisfied_at_ms INTEGER, 71 created_at_ms INTEGER NOT NULL, 72 updated_at_ms INTEGER NOT NULL, 73 UNIQUE(outbox_event_id, delivery_plan_idempotency_digest) 74 ); 75 76 CREATE INDEX IF NOT EXISTS outbox_delivery_plan_event_idx 77 ON outbox_delivery_plan(outbox_event_id, status, delivery_plan_id); 78 79 CREATE TABLE IF NOT EXISTS outbox_delivery_target ( 80 delivery_target_id INTEGER PRIMARY KEY AUTOINCREMENT, 81 delivery_plan_id INTEGER NOT NULL REFERENCES outbox_delivery_plan(delivery_plan_id) ON DELETE CASCADE, 82 transport_kind TEXT NOT NULL, 83 endpoint_uri TEXT NOT NULL, 84 target_scope TEXT, 85 target_label TEXT, 86 endpoint_fingerprint TEXT NOT NULL, 87 status TEXT NOT NULL CHECK (status IN ('pending', 'accepted', 'delivered', 'forwarded', 'stored_by_gateway', 'seen', 'deferred_until_implemented', 'skipped_policy_denied', 'failed_retryable', 'failed_terminal')), 88 last_outcome_kind TEXT CHECK (last_outcome_kind IS NULL OR last_outcome_kind IN ('accepted', 'duplicate_accepted', 'delivered', 'forwarded', 'stored_by_gateway', 'seen', 'deferred_until_implemented', 'rejected', 'route_unavailable', 'payload_too_large', 'policy_denied', 'timeout', 'connection_failed', 'transport_unavailable')), 89 attempt_count INTEGER NOT NULL, 90 last_attempt_at_ms INTEGER, 91 completed_at_ms INTEGER, 92 last_error TEXT, 93 UNIQUE(delivery_plan_id, endpoint_fingerprint) 94 ); 95 96 CREATE INDEX IF NOT EXISTS outbox_delivery_target_ready_idx 97 ON outbox_delivery_target(status, delivery_plan_id, delivery_target_id); 98 99 CREATE TABLE IF NOT EXISTS outbox_delivery_attempt ( 100 delivery_attempt_id INTEGER PRIMARY KEY AUTOINCREMENT, 101 delivery_plan_id INTEGER NOT NULL REFERENCES outbox_delivery_plan(delivery_plan_id) ON DELETE CASCADE, 102 delivery_target_id INTEGER NOT NULL REFERENCES outbox_delivery_target(delivery_target_id) ON DELETE CASCADE, 103 status TEXT NOT NULL, 104 outcome_kind TEXT NOT NULL CHECK (outcome_kind IN ('accepted', 'duplicate_accepted', 'delivered', 'forwarded', 'stored_by_gateway', 'seen', 'deferred_until_implemented', 'rejected', 'route_unavailable', 'payload_too_large', 'policy_denied', 'timeout', 'connection_failed', 'transport_unavailable')), 105 attempted_at_ms INTEGER NOT NULL, 106 message TEXT 107 ); 108 109 CREATE INDEX IF NOT EXISTS outbox_delivery_attempt_target_idx 110 ON outbox_delivery_attempt(delivery_target_id, attempted_at_ms, delivery_attempt_id);