AkurAI-Build / migrations/008_webhooks.sql

Latest change 3367b5f4b77e4085d5cc513b20e67403d2207543 - OLI-272: outbound webhooks with HMAC signatures, delivery log and redelivery; git Basic challenge by Óli

CREATE TABLE webhooks (
    id INTEGER PRIMARY KEY,
    repository_id INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
    url TEXT NOT NULL CHECK (length(url) BETWEEN 8 AND 2048),
    secret TEXT NOT NULL CHECK (length(secret) BETWEEN 8 AND 256),
    events TEXT NOT NULL CHECK (length(events) BETWEEN 1 AND 200),
    active INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0, 1)),
    created_at INTEGER NOT NULL DEFAULT (unixepoch())
) STRICT;

CREATE TABLE webhook_deliveries (
    id INTEGER PRIMARY KEY,
    webhook_id INTEGER NOT NULL REFERENCES webhooks(id) ON DELETE CASCADE,
    event TEXT NOT NULL CHECK (length(event) BETWEEN 1 AND 40),
    payload TEXT NOT NULL,
    status TEXT NOT NULL CHECK (status IN ('pending', 'delivered', 'failed')),
    attempts INTEGER NOT NULL DEFAULT 0,
    next_attempt_at INTEGER NOT NULL,
    response_code INTEGER,
    response_excerpt TEXT,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    delivered_at INTEGER
) STRICT;

CREATE INDEX webhook_deliveries_due ON webhook_deliveries(status, next_attempt_at);
CREATE INDEX webhook_deliveries_hook ON webhook_deliveries(webhook_id, id);