41 lines
1.7 KiB
SQL
41 lines
1.7 KiB
SQL
-- Create outbound event queue for offline event creation and reliable relay transmission
|
|
-- This allows users to create events when offline and sync them when connectivity is restored
|
|
|
|
CREATE TABLE outbound_event_queue (
|
|
queue_id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
event_id TEXT NOT NULL,
|
|
encrypted_event TEXT NOT NULL,
|
|
ccn_pubkey TEXT NOT NULL,
|
|
created_at INTEGER NOT NULL DEFAULT (unixepoch()),
|
|
attempts INTEGER NOT NULL DEFAULT 0,
|
|
last_attempt INTEGER NULL,
|
|
status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'sending', 'sent', 'failed')),
|
|
error_message TEXT NULL,
|
|
|
|
-- Ensure one queue entry per event
|
|
UNIQUE(event_id),
|
|
|
|
-- Foreign key constraints
|
|
FOREIGN KEY (ccn_pubkey) REFERENCES ccns(pubkey) ON DELETE CASCADE,
|
|
FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- Indexes for efficient querying
|
|
CREATE INDEX idx_outbound_queue_status ON outbound_event_queue(status);
|
|
CREATE INDEX idx_outbound_queue_ccn_pubkey ON outbound_event_queue(ccn_pubkey);
|
|
CREATE INDEX idx_outbound_queue_created_at ON outbound_event_queue(created_at);
|
|
CREATE INDEX idx_outbound_queue_last_attempt ON outbound_event_queue(last_attempt);
|
|
|
|
-- Cleanup trigger for old completed/failed events
|
|
CREATE TRIGGER cleanup_old_queue_entries
|
|
AFTER UPDATE ON outbound_event_queue
|
|
WHEN NEW.status IN ('sent', 'failed') AND NEW.attempts >= 5
|
|
BEGIN
|
|
-- Keep failed events for 30 days for debugging, sent events for 1 day
|
|
DELETE FROM outbound_event_queue
|
|
WHERE queue_id = NEW.queue_id
|
|
AND (
|
|
(status = 'sent' AND created_at < (unixepoch() - 86400)) OR
|
|
(status = 'failed' AND created_at < (unixepoch() - 2592000))
|
|
);
|
|
END;
|