-- Integrations round 2: delivery leases (dedupe across retried events), per-target Slack delivery state,
-- an audit trail for security-relevant settings (force mirrors), and daily outbound usage counters.
ALTER TABLE integration_deliveries ADD COLUMN lease_until INTEGER;
-- The queue message holding the lease; its own retry may reclaim it, duplicates may not.
ALTER TABLE integration_deliveries ADD COLUMN lease_owner TEXT;
-- Set once a delivery is final (success, permanent failure, paused): duplicates never resend it.
ALTER TABLE integration_deliveries ADD COLUMN terminal INTEGER NOT NULL DEFAULT 0;
CREATE TABLE integration_delivery_targets (
delivery_id TEXT NOT NULL REFERENCES integration_deliveries(id) ON DELETE CASCADE,
target TEXT NOT NULL,
status_code INTEGER,
created_at INTEGER NOT NULL,
PRIMARY KEY (delivery_id, target)
);
CREATE TABLE integration_audit (
id TEXT PRIMARY KEY,
integration_id TEXT NOT NULL REFERENCES integrations(id) ON DELETE CASCADE,
actor_id TEXT REFERENCES accounts(id) ON DELETE SET NULL,
action TEXT NOT NULL,
detail TEXT NOT NULL DEFAULT '{}',
created_at INTEGER NOT NULL
);
CREATE INDEX integration_audit_integration ON integration_audit(integration_id, created_at DESC);
CREATE TABLE integration_usage_daily (
owner_id TEXT NOT NULL,
day TEXT NOT NULL,
kind TEXT NOT NULL,
deliveries INTEGER NOT NULL DEFAULT 0,
failures INTEGER NOT NULL DEFAULT 0,
bytes INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (owner_id, day, kind)
);
-- One row per metered attempt, so a redelivered queue message never counts twice. Pruned after 40 days.
CREATE TABLE integration_usage_keys (
key TEXT PRIMARY KEY,
created_at INTEGER NOT NULL