58 lines
3.0 KiB
SQL
58 lines
3.0 KiB
SQL
-- Migration 009: Persistent Subscriptions
|
|
-- Adds support for persistent, correlation-based event subscriptions
|
|
|
|
CREATE TABLE IF NOT EXISTS persistent_subscriptions (
|
|
id TEXT PRIMARY KEY,
|
|
subscriber_id TEXT NOT NULL,
|
|
correlation_id TEXT NOT NULL,
|
|
event_types JSONB NOT NULL DEFAULT '[]'::jsonb,
|
|
terminal_event_types JSONB NOT NULL DEFAULT '[]'::jsonb,
|
|
delivery_mode INT NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
expires_at TIMESTAMPTZ NULL,
|
|
completed_at TIMESTAMPTZ NULL,
|
|
last_delivered_sequence BIGINT NOT NULL DEFAULT -1,
|
|
status INT NOT NULL DEFAULT 0,
|
|
connection_id TEXT NULL,
|
|
data_source_id TEXT NULL
|
|
);
|
|
|
|
-- Index for looking up subscriptions by subscriber
|
|
CREATE INDEX IF NOT EXISTS idx_persistent_subscriptions_subscriber_id
|
|
ON persistent_subscriptions (subscriber_id);
|
|
|
|
-- Index for looking up subscriptions by correlation ID (most common query)
|
|
CREATE INDEX IF NOT EXISTS idx_persistent_subscriptions_correlation_id
|
|
ON persistent_subscriptions (correlation_id);
|
|
|
|
-- Index for looking up subscriptions by status
|
|
CREATE INDEX IF NOT EXISTS idx_persistent_subscriptions_status
|
|
ON persistent_subscriptions (status);
|
|
|
|
-- Index for looking up subscriptions by connection ID
|
|
CREATE INDEX IF NOT EXISTS idx_persistent_subscriptions_connection_id
|
|
ON persistent_subscriptions (connection_id)
|
|
WHERE connection_id IS NOT NULL;
|
|
|
|
-- Index for finding expired subscriptions
|
|
CREATE INDEX IF NOT EXISTS idx_persistent_subscriptions_expires_at
|
|
ON persistent_subscriptions (expires_at)
|
|
WHERE expires_at IS NOT NULL AND status = 0; -- Active status
|
|
|
|
-- Composite index for active subscriptions by correlation (hot path)
|
|
CREATE INDEX IF NOT EXISTS idx_persistent_subscriptions_correlation_active
|
|
ON persistent_subscriptions (correlation_id, status)
|
|
WHERE status = 0;
|
|
|
|
COMMENT ON TABLE persistent_subscriptions IS 'Stores persistent event subscriptions that survive client disconnection';
|
|
COMMENT ON COLUMN persistent_subscriptions.id IS 'Unique subscription identifier';
|
|
COMMENT ON COLUMN persistent_subscriptions.subscriber_id IS 'User/client who owns this subscription';
|
|
COMMENT ON COLUMN persistent_subscriptions.correlation_id IS 'Correlation ID to filter events by';
|
|
COMMENT ON COLUMN persistent_subscriptions.event_types IS 'Array of event type names to deliver (empty = all)';
|
|
COMMENT ON COLUMN persistent_subscriptions.terminal_event_types IS 'Event types that complete the subscription';
|
|
COMMENT ON COLUMN persistent_subscriptions.delivery_mode IS 'How events are delivered: 0=Immediate, 1=Batched, 2=OnReconnect';
|
|
COMMENT ON COLUMN persistent_subscriptions.last_delivered_sequence IS 'Last event sequence successfully delivered';
|
|
COMMENT ON COLUMN persistent_subscriptions.status IS 'Subscription status: 0=Active, 1=Completed, 2=Expired, 3=Cancelled, 4=Paused';
|
|
COMMENT ON COLUMN persistent_subscriptions.connection_id IS 'Optional connection ID if client is currently connected';
|
|
COMMENT ON COLUMN persistent_subscriptions.data_source_id IS 'Optional data source ID for client-side routing';
|