34 lines
1.3 KiB
SQL
34 lines
1.3 KiB
SQL
CREATE TABLE IF NOT EXISTS outbound_messages (
|
|
id UUID PRIMARY KEY,
|
|
user_uuid UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
channel VARCHAR(50) NOT NULL,
|
|
payload JSONB NOT NULL,
|
|
available_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
status VARCHAR(20) NOT NULL DEFAULT 'pending',
|
|
attempts INTEGER NOT NULL DEFAULT 0,
|
|
max_attempts INTEGER NOT NULL DEFAULT 3,
|
|
leased_by VARCHAR(255),
|
|
lease_until TIMESTAMPTZ,
|
|
last_error TEXT,
|
|
idempotency_key VARCHAR(255) NOT NULL UNIQUE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
delivered_at TIMESTAMPTZ,
|
|
cancelled_at TIMESTAMPTZ,
|
|
CONSTRAINT outbound_messages_status_check
|
|
CHECK (status IN ('pending', 'delivering', 'delivered', 'failed', 'cancelled')),
|
|
CONSTRAINT outbound_messages_attempts_check
|
|
CHECK (attempts >= 0 AND max_attempts > 0)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS outbound_messages_claim_idx
|
|
ON outbound_messages (channel, available_at, created_at)
|
|
WHERE status = 'pending';
|
|
|
|
CREATE INDEX IF NOT EXISTS outbound_messages_expired_lease_idx
|
|
ON outbound_messages (lease_until)
|
|
WHERE status = 'delivering';
|
|
|
|
CREATE INDEX IF NOT EXISTS outbound_messages_user_idx
|
|
ON outbound_messages (user_uuid, created_at DESC);
|