56 lines
2.5 KiB
SQL
56 lines
2.5 KiB
SQL
CREATE TABLE IF NOT EXISTS event_outbox (
|
|
id SERIAL PRIMARY KEY,
|
|
event_id VARCHAR(64) NOT NULL UNIQUE,
|
|
event_type VARCHAR(128) NOT NULL,
|
|
event_version INTEGER NOT NULL DEFAULT 1,
|
|
producer_service VARCHAR(128) NOT NULL,
|
|
entity_type VARCHAR(128) NOT NULL,
|
|
entity_id VARCHAR(128) NOT NULL,
|
|
correlation_id VARCHAR(128) NULL,
|
|
routing_key VARCHAR(128) NOT NULL,
|
|
payload_json TEXT NOT NULL DEFAULT '{}',
|
|
status VARCHAR(32) NOT NULL,
|
|
attempt_count INTEGER NOT NULL DEFAULT 0,
|
|
last_error TEXT NULL,
|
|
available_at VARCHAR(64) NOT NULL,
|
|
published_at VARCHAR(64) NULL,
|
|
created_at VARCHAR(64) NOT NULL,
|
|
updated_at VARCHAR(64) NOT NULL
|
|
);
|
|
CREATE INDEX IF NOT EXISTS ix_event_outbox_event_id ON event_outbox (event_id);
|
|
CREATE INDEX IF NOT EXISTS ix_event_outbox_event_type ON event_outbox (event_type);
|
|
CREATE INDEX IF NOT EXISTS ix_event_outbox_status ON event_outbox (status);
|
|
CREATE INDEX IF NOT EXISTS ix_event_outbox_available_at ON event_outbox (available_at);
|
|
CREATE INDEX IF NOT EXISTS ix_event_outbox_entity_type ON event_outbox (entity_type);
|
|
CREATE INDEX IF NOT EXISTS ix_event_outbox_entity_id ON event_outbox (entity_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS event_inbox (
|
|
id SERIAL PRIMARY KEY,
|
|
consumer_name VARCHAR(128) NOT NULL,
|
|
event_id VARCHAR(64) NOT NULL,
|
|
event_type VARCHAR(128) NOT NULL,
|
|
processed_at VARCHAR(64) NOT NULL,
|
|
status VARCHAR(32) NOT NULL,
|
|
notes TEXT NULL
|
|
);
|
|
CREATE INDEX IF NOT EXISTS ix_event_inbox_consumer_name ON event_inbox (consumer_name);
|
|
CREATE INDEX IF NOT EXISTS ix_event_inbox_event_id ON event_inbox (event_id);
|
|
CREATE INDEX IF NOT EXISTS ix_event_inbox_event_type ON event_inbox (event_type);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS uq_event_inbox_consumer_event ON event_inbox (consumer_name, event_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS reporting_event_log (
|
|
id SERIAL PRIMARY KEY,
|
|
event_id VARCHAR(64) NOT NULL,
|
|
event_type VARCHAR(128) NOT NULL,
|
|
queue_id VARCHAR(64) NULL,
|
|
channel VARCHAR(32) NULL,
|
|
interaction_id VARCHAR(64) NULL,
|
|
payload_json TEXT NOT NULL DEFAULT '{}',
|
|
created_at VARCHAR(64) NOT NULL
|
|
);
|
|
CREATE INDEX IF NOT EXISTS ix_reporting_event_log_event_id ON reporting_event_log (event_id);
|
|
CREATE INDEX IF NOT EXISTS ix_reporting_event_log_event_type ON reporting_event_log (event_type);
|
|
CREATE INDEX IF NOT EXISTS ix_reporting_event_log_queue_id ON reporting_event_log (queue_id);
|
|
CREATE INDEX IF NOT EXISTS ix_reporting_event_log_channel ON reporting_event_log (channel);
|
|
CREATE INDEX IF NOT EXISTS ix_reporting_event_log_interaction_id ON reporting_event_log (interaction_id);
|