0001_outbox.up.sql (2395B)
1 CREATE TABLE IF NOT EXISTS outbox_operation ( 2 operation_id INTEGER PRIMARY KEY AUTOINCREMENT, 3 operation_kind TEXT NOT NULL, 4 expected_pubkey TEXT NOT NULL, 5 idempotency_key TEXT, 6 idempotency_digest TEXT NOT NULL, 7 status TEXT NOT NULL CHECK (status IN ('queued', 'complete', 'failed_terminal', 'cancelled')), 8 created_at_ms INTEGER NOT NULL, 9 updated_at_ms INTEGER NOT NULL 10 ); 11 12 CREATE UNIQUE INDEX IF NOT EXISTS outbox_operation_idempotency_idx 13 ON outbox_operation(operation_kind, expected_pubkey, idempotency_key) 14 WHERE idempotency_key IS NOT NULL; 15 16 CREATE INDEX IF NOT EXISTS outbox_operation_status_idx 17 ON outbox_operation(status, created_at_ms, operation_id); 18 19 CREATE TABLE IF NOT EXISTS outbox_event ( 20 outbox_event_id INTEGER PRIMARY KEY AUTOINCREMENT, 21 operation_id INTEGER NOT NULL REFERENCES outbox_operation(operation_id) ON DELETE CASCADE, 22 event_id TEXT NOT NULL, 23 expected_pubkey TEXT NOT NULL, 24 draft_json TEXT NOT NULL, 25 signed_event_json TEXT, 26 raw_event_json TEXT, 27 state TEXT NOT NULL CHECK (state IN ('draft_queued', 'signing', 'signed', 'publishing', 'published', 'sign_retryable', 'publish_retryable', 'failed_terminal', 'cancelled')), 28 accepted_quorum INTEGER NOT NULL CHECK (accepted_quorum >= 0), 29 attempt_count INTEGER NOT NULL, 30 claim_token TEXT, 31 claim_owner TEXT, 32 claim_expires_at_ms INTEGER, 33 next_attempt_after_ms INTEGER NOT NULL, 34 last_error TEXT, 35 event_store_ingested INTEGER NOT NULL, 36 event_store_inserted INTEGER NOT NULL, 37 event_store_ingested_at_ms INTEGER, 38 created_at_ms INTEGER NOT NULL, 39 updated_at_ms INTEGER NOT NULL 40 ); 41 42 CREATE INDEX IF NOT EXISTS outbox_event_ready_idx 43 ON outbox_event(state, next_attempt_after_ms, claim_expires_at_ms, created_at_ms, outbox_event_id); 44 45 CREATE INDEX IF NOT EXISTS outbox_event_event_id_idx 46 ON outbox_event(event_id); 47 48 CREATE TABLE IF NOT EXISTS outbox_event_relay_status ( 49 outbox_event_id INTEGER NOT NULL REFERENCES outbox_event(outbox_event_id) ON DELETE CASCADE, 50 relay_url TEXT NOT NULL, 51 status TEXT NOT NULL CHECK (status IN ('pending', 'accepted', 'failed_retryable', 'failed_terminal')), 52 attempt_count INTEGER NOT NULL, 53 last_attempt_at_ms INTEGER, 54 acknowledged_at_ms INTEGER, 55 last_error TEXT, 56 PRIMARY KEY (outbox_event_id, relay_url) 57 ); 58 59 CREATE INDEX IF NOT EXISTS outbox_event_relay_status_idx 60 ON outbox_event_relay_status(status, relay_url, outbox_event_id);