CREATE TABLE slack_endpoints (
    id          TEXT PRIMARY KEY,
    tenant      TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    name        TEXT NOT NULL,
    -- JSON array of subscribed event-type strings, e.g. ["Todos_ItemCreated"].
    event_types TEXT NOT NULL DEFAULT '[]',
    active      INTEGER NOT NULL DEFAULT 1,
    -- The Slack incoming-webhook URL, sealed at rest under the tenant's DEK (the
    -- per-tenant data key in the deks table; see core/envelope). The URL is a
    -- secret: holding it grants posting to the channel, so it is never stored or
    -- returned in the clear.
    dek_id      TEXT NOT NULL,
    url_enc     BLOB NOT NULL,
    created_at  TEXT NOT NULL DEFAULT (datetime('now'))
) STRICT;

CREATE INDEX idx_slack_endpoints_tenant ON slack_endpoints(tenant, active);

-- Standard task queue (see 007_tasks): one delivery per subscribed endpoint,
-- drained by a tasks.Worker replaying POST /Slack_Deliver.
CREATE TABLE slack_delivery_tasks (
    task_id     TEXT PRIMARY KEY,
    payload     TEXT NOT NULL,
    status      TEXT NOT NULL,
    origin      TEXT NOT NULL DEFAULT '',
    attempts    INTEGER NOT NULL DEFAULT 0,
    run_after   TEXT NOT NULL DEFAULT (datetime('now')),
    lease_until TEXT,
    created_at  TEXT NOT NULL DEFAULT (datetime('now'))
) STRICT;