-- integrations: squashed from 1200_integrations.sql, 1201_integrations_hardening.sql.
-- Third-party integrations (Slack, Google Chat, Linear, GitHub, generic webhooks) scoped to a repo or an org.
-- Credentials live AES-GCM encrypted in `credentials_ciphertext`; `config` only holds non-secret settings.
CREATE TABLE integrations (
id TEXT PRIMARY KEY,
scope_type TEXT NOT NULL CHECK (scope_type IN ('repo', 'org')),
scope_id TEXT NOT NULL,
kind TEXT NOT NULL CHECK (kind IN ('slack', 'google_chat', 'linear', 'github', 'webhook')),
name TEXT NOT NULL,
config TEXT NOT NULL DEFAULT '{}',
events TEXT NOT NULL DEFAULT '[]',
delivery_mode TEXT NOT NULL DEFAULT 'instant' CHECK (delivery_mode IN ('instant', 'digest')),
digest_minutes INTEGER NOT NULL DEFAULT 60,
last_digest_at INTEGER NOT NULL DEFAULT 0,
credentials_ciphertext TEXT,
credentials_iv TEXT,
active INTEGER NOT NULL DEFAULT 1,
created_by TEXT REFERENCES accounts(id) ON DELETE SET NULL,
created_at INTEGER NOT NULL,
updated_at INTEGER NOT NULL
);
CREATE INDEX integrations_scope ON integrations(scope_type, scope_id, active);
CREATE INDEX integrations_digest ON integrations(delivery_mode, active, last_digest_at);
CREATE TABLE integration_deliveries (
id TEXT PRIMARY KEY,
integration_id TEXT NOT NULL REFERENCES integrations(id) ON DELETE CASCADE,
direction TEXT NOT NULL DEFAULT 'outbound' CHECK (direction IN ('outbound', 'inbound')),
event TEXT NOT NULL,
repo_id TEXT,
status TEXT NOT NULL CHECK (status IN ('pending', 'success', 'failed', 'dead')),
status_code INTEGER,
error TEXT,
payload TEXT NOT NULL,
response_body TEXT,
duration_ms INTEGER,
attempt INTEGER NOT NULL DEFAULT 0,