mirror of https://github.com/synctv-org/synctv
You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
38 lines
1.2 KiB
SQL
38 lines
1.2 KiB
SQL
CREATE TABLE IF NOT EXISTS email_outbox (
|
|
id TEXT PRIMARY KEY,
|
|
kind SMALLINT NOT NULL,
|
|
recipient TEXT NOT NULL,
|
|
encrypted_payload TEXT NOT NULL,
|
|
dedupe_key TEXT NOT NULL UNIQUE,
|
|
status SMALLINT NOT NULL,
|
|
attempts INTEGER NOT NULL,
|
|
next_attempt_at TIMESTAMPTZ NOT NULL,
|
|
locked_by TEXT,
|
|
locked_at TIMESTAMPTZ,
|
|
lock_version BIGINT NOT NULL,
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL,
|
|
sent_at TIMESTAMPTZ,
|
|
cleanup_completed_at TIMESTAMPTZ,
|
|
last_error TEXT
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_email_outbox_pending
|
|
ON email_outbox (next_attempt_at, created_at, id)
|
|
WHERE status = 1;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_email_outbox_processing_lease
|
|
ON email_outbox (locked_at)
|
|
WHERE status = 2 AND locked_at IS NOT NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_email_outbox_terminal_retention
|
|
ON email_outbox (COALESCE(sent_at, created_at))
|
|
WHERE status IN (3, 4);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_email_outbox_dead_cleanup
|
|
ON email_outbox (created_at, id)
|
|
WHERE status = 4 AND cleanup_completed_at IS NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_email_outbox_recipient_created
|
|
ON email_outbox (recipient, created_at DESC);
|