102 lines
3.4 KiB
SQL
102 lines
3.4 KiB
SQL
PRAGMA foreign_keys = ON;
|
|
|
|
CREATE TABLE IF NOT EXISTS inbox (
|
|
command_id TEXT PRIMARY KEY,
|
|
tenant_id TEXT NOT NULL,
|
|
tenant_key TEXT NOT NULL,
|
|
command_type TEXT NOT NULL,
|
|
body_hash TEXT NOT NULL,
|
|
body BLOB NOT NULL,
|
|
status TEXT NOT NULL CHECK (status IN ('received', 'persisted', 'rejected')),
|
|
received_at TEXT NOT NULL,
|
|
persisted_at TEXT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS tasks (
|
|
execution_id TEXT PRIMARY KEY,
|
|
tenant_key TEXT NOT NULL,
|
|
tenant_id TEXT NOT NULL,
|
|
task_id TEXT NOT NULL,
|
|
task_item_id TEXT NOT NULL,
|
|
task_revision INTEGER NOT NULL,
|
|
trace_id TEXT NOT NULL,
|
|
callee TEXT NOT NULL,
|
|
route_policy_id TEXT NOT NULL,
|
|
caller_profile_id TEXT NOT NULL,
|
|
agent_version_id TEXT NOT NULL,
|
|
variables BLOB NOT NULL,
|
|
ring_timeout_ms INTEGER NOT NULL,
|
|
max_call_duration_ms INTEGER NOT NULL,
|
|
status TEXT NOT NULL CHECK (status IN ('accepted', 'reserved', 'running', 'draining', 'paused', 'stopped', 'finished', 'unknown')),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
UNIQUE (tenant_key, task_id, task_item_id, task_revision)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS tasks_pending_idx ON tasks (status, created_at);
|
|
CREATE INDEX IF NOT EXISTS tasks_tenant_idx ON tasks (tenant_key, status, created_at);
|
|
|
|
CREATE TABLE IF NOT EXISTS quotas (
|
|
scope TEXT PRIMARY KEY,
|
|
limit_value INTEGER NOT NULL CHECK (limit_value >= 0),
|
|
reserved_value INTEGER NOT NULL DEFAULT 0 CHECK (reserved_value >= 0),
|
|
unknown_value INTEGER NOT NULL DEFAULT 0 CHECK (unknown_value >= 0),
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS reservations (
|
|
reservation_id TEXT PRIMARY KEY,
|
|
execution_id TEXT NOT NULL,
|
|
tenant_key TEXT NOT NULL,
|
|
scopes BLOB NOT NULL,
|
|
state TEXT NOT NULL CHECK (state IN ('held', 'released', 'unknown')),
|
|
created_at TEXT NOT NULL,
|
|
released_at TEXT,
|
|
UNIQUE (execution_id)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS reservations_tenant_idx ON reservations (tenant_key, state);
|
|
|
|
CREATE TABLE IF NOT EXISTS outbox (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
event_id TEXT NOT NULL UNIQUE,
|
|
tenant_key TEXT NOT NULL,
|
|
exchange TEXT NOT NULL,
|
|
routing_key TEXT NOT NULL,
|
|
body BLOB NOT NULL,
|
|
status TEXT NOT NULL CHECK (status IN ('pending', 'dispatching', 'published', 'retry')),
|
|
attempts INTEGER NOT NULL DEFAULT 0,
|
|
last_error TEXT,
|
|
created_at TEXT NOT NULL,
|
|
published_at TEXT
|
|
);
|
|
CREATE INDEX IF NOT EXISTS outbox_pending_idx ON outbox (status, id);
|
|
|
|
CREATE TABLE IF NOT EXISTS controls (
|
|
execution_id TEXT PRIMARY KEY,
|
|
task_revision INTEGER NOT NULL,
|
|
action TEXT NOT NULL CHECK (action IN ('pause', 'resume', 'drain', 'stop', 'hangup')),
|
|
active_call_policy TEXT,
|
|
reason TEXT,
|
|
idempotency_key TEXT,
|
|
applied INTEGER NOT NULL DEFAULT 0,
|
|
requested_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS replays (
|
|
idempotency_key TEXT PRIMARY KEY,
|
|
tenant_id TEXT NOT NULL,
|
|
source_command_id TEXT NOT NULL,
|
|
tenant_key TEXT NOT NULL,
|
|
reason TEXT NOT NULL,
|
|
created_at TEXT NOT NULL,
|
|
UNIQUE (idempotency_key, source_command_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS leases (
|
|
lease_id TEXT PRIMARY KEY,
|
|
scope TEXT NOT NULL,
|
|
holder_id TEXT NOT NULL,
|
|
expires_at TEXT NOT NULL,
|
|
state TEXT NOT NULL CHECK (state IN ('active', 'expired', 'released'))
|
|
);
|
|
CREATE INDEX IF NOT EXISTS leases_scope_idx ON leases (scope, state, expires_at);
|