196 lines
6.7 KiB
SQL
196 lines
6.7 KiB
SQL
PRAGMA foreign_keys = ON;
|
|
|
|
CREATE TABLE IF NOT EXISTS schema_migrations (
|
|
version INTEGER PRIMARY KEY,
|
|
applied_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS providers (
|
|
provider_id TEXT PRIMARY KEY,
|
|
display_name TEXT NOT NULL,
|
|
notes TEXT NOT NULL DEFAULT '',
|
|
lifecycle TEXT NOT NULL CHECK (lifecycle IN ('active','archived')),
|
|
revision INTEGER NOT NULL CHECK (revision >= 1),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
created_by TEXT NOT NULL,
|
|
updated_by TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS egress_pools (
|
|
egress_pool_id TEXT PRIMARY KEY,
|
|
provider_id TEXT NOT NULL DEFAULT '',
|
|
display_name TEXT NOT NULL,
|
|
fixed_ips_json TEXT NOT NULL DEFAULT '[]',
|
|
whitelist_status TEXT NOT NULL DEFAULT 'unknown',
|
|
whitelist_checked_at TEXT,
|
|
source TEXT NOT NULL DEFAULT 'mock',
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS cells (
|
|
cell_id TEXT PRIMARY KEY,
|
|
revision INTEGER NOT NULL CHECK (revision >= 1),
|
|
config_json TEXT NOT NULL,
|
|
cloud_instance_id TEXT NOT NULL DEFAULT '',
|
|
instance_name TEXT NOT NULL DEFAULT '',
|
|
region TEXT NOT NULL DEFAULT '',
|
|
boot_id TEXT NOT NULL DEFAULT '',
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
updated_by TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS trunks (
|
|
trunk_id TEXT PRIMARY KEY,
|
|
provider_id TEXT NOT NULL REFERENCES providers(provider_id),
|
|
latest_revision INTEGER NOT NULL CHECK (latest_revision >= 1),
|
|
active_revision INTEGER NOT NULL DEFAULT 0 CHECK (active_revision >= 0),
|
|
active_status TEXT NOT NULL CHECK (active_status IN ('draft','published','disabled')),
|
|
updated_at TEXT NOT NULL,
|
|
updated_by TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS trunk_versions (
|
|
trunk_id TEXT NOT NULL REFERENCES trunks(trunk_id) ON DELETE CASCADE,
|
|
revision INTEGER NOT NULL CHECK (revision >= 1),
|
|
state TEXT NOT NULL CHECK (state IN ('draft','publishing','published','superseded')),
|
|
config_json TEXT NOT NULL,
|
|
config_sha256 TEXT NOT NULL,
|
|
created_at TEXT NOT NULL,
|
|
created_by TEXT NOT NULL,
|
|
PRIMARY KEY (trunk_id, revision)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS admission_barriers (
|
|
trunk_id TEXT PRIMARY KEY REFERENCES trunks(trunk_id) ON DELETE CASCADE,
|
|
operation_id TEXT NOT NULL,
|
|
target_revision INTEGER NOT NULL,
|
|
reason TEXT NOT NULL,
|
|
state TEXT NOT NULL CHECK (state IN ('active','released','pending')),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS publications (
|
|
trunk_id TEXT NOT NULL REFERENCES trunks(trunk_id) ON DELETE CASCADE,
|
|
revision INTEGER NOT NULL,
|
|
cell_id TEXT NOT NULL REFERENCES cells(cell_id),
|
|
status TEXT NOT NULL CHECK (status IN ('pending','applied','failed')),
|
|
error_code TEXT,
|
|
target_digest TEXT NOT NULL,
|
|
local_revision INTEGER NOT NULL DEFAULT 0,
|
|
local_digest TEXT NOT NULL DEFAULT '',
|
|
applied_at TEXT,
|
|
operation_id TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
PRIMARY KEY (trunk_id, revision, cell_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS operations (
|
|
operation_id TEXT PRIMARY KEY,
|
|
actor TEXT NOT NULL,
|
|
action TEXT NOT NULL,
|
|
resource_type TEXT NOT NULL,
|
|
resource_id TEXT NOT NULL,
|
|
request_id TEXT NOT NULL,
|
|
request_digest TEXT NOT NULL,
|
|
expected_revision INTEGER NOT NULL DEFAULT 0,
|
|
state TEXT NOT NULL CHECK (state IN ('in_progress','succeeded','failed','pending','needs_reconciliation')),
|
|
http_status INTEGER NOT NULL DEFAULT 200,
|
|
response_json TEXT,
|
|
error_json TEXT,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
expires_at TEXT NOT NULL,
|
|
UNIQUE(actor, action, resource_type, resource_id, request_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS observations (
|
|
observation_id TEXT PRIMARY KEY,
|
|
cell_id TEXT NOT NULL REFERENCES cells(cell_id) ON DELETE CASCADE,
|
|
trunk_id TEXT,
|
|
boot_id TEXT NOT NULL,
|
|
sequence INTEGER NOT NULL,
|
|
observed_at TEXT NOT NULL,
|
|
received_at TEXT NOT NULL,
|
|
source TEXT NOT NULL,
|
|
config_revision INTEGER NOT NULL DEFAULT 0,
|
|
states_json TEXT NOT NULL,
|
|
occupancy_json TEXT NOT NULL DEFAULT '{}',
|
|
sample_id TEXT NOT NULL DEFAULT ''
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_observations_cell_trunk_received
|
|
ON observations(cell_id, trunk_id, received_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_observations_boot_sequence
|
|
ON observations(cell_id, boot_id, sequence DESC);
|
|
|
|
CREATE TABLE IF NOT EXISTS attempts (
|
|
attempt_id TEXT PRIMARY KEY,
|
|
execution_id TEXT NOT NULL DEFAULT '',
|
|
call_id TEXT NOT NULL DEFAULT '',
|
|
tenant_key TEXT NOT NULL DEFAULT '',
|
|
provider_id TEXT NOT NULL DEFAULT '',
|
|
trunk_id TEXT NOT NULL DEFAULT '',
|
|
cell_id TEXT NOT NULL DEFAULT '',
|
|
egress_pool_id TEXT NOT NULL DEFAULT '',
|
|
config_revision INTEGER NOT NULL DEFAULT 0,
|
|
mode TEXT NOT NULL CHECK (mode IN ('mock','real')),
|
|
dialed_number TEXT NOT NULL DEFAULT '',
|
|
attempt_started_at TEXT,
|
|
origin_status TEXT NOT NULL CHECK (origin_status IN ('confirmed','uncertain','not_started','rejected')),
|
|
answered_at TEXT,
|
|
ended_at TEXT,
|
|
termination_reason TEXT NOT NULL DEFAULT 'unknown',
|
|
sip_code INTEGER,
|
|
q850 TEXT NOT NULL DEFAULT '',
|
|
ai_status TEXT NOT NULL DEFAULT 'unknown',
|
|
source TEXT NOT NULL DEFAULT 'mock',
|
|
fact_version INTEGER NOT NULL DEFAULT 1,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_attempts_started ON attempts(attempt_started_at, attempt_id);
|
|
CREATE INDEX IF NOT EXISTS idx_attempts_route ON attempts(provider_id, trunk_id, cell_id, mode);
|
|
CREATE INDEX IF NOT EXISTS idx_attempts_execution ON attempts(execution_id, call_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS attempt_events (
|
|
event_id TEXT PRIMARY KEY,
|
|
attempt_id TEXT NOT NULL REFERENCES attempts(attempt_id) ON DELETE CASCADE,
|
|
event_type TEXT NOT NULL,
|
|
event_time TEXT NOT NULL,
|
|
received_at TEXT NOT NULL,
|
|
source TEXT NOT NULL,
|
|
fact_version INTEGER NOT NULL DEFAULT 1,
|
|
payload_json TEXT NOT NULL DEFAULT '{}'
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS trunk_verifications (
|
|
trunk_id TEXT NOT NULL REFERENCES trunks(trunk_id) ON DELETE CASCADE,
|
|
revision INTEGER NOT NULL,
|
|
check_name TEXT NOT NULL,
|
|
result TEXT NOT NULL CHECK (result IN ('confirmed','failed','unknown','not_applicable')),
|
|
evidence_ref TEXT NOT NULL DEFAULT '',
|
|
checked_by TEXT NOT NULL,
|
|
checked_at TEXT NOT NULL,
|
|
PRIMARY KEY (trunk_id, revision, check_name)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_verifications_revision ON trunk_verifications(trunk_id, revision, result);
|
|
|
|
CREATE TABLE IF NOT EXISTS audit_entries (
|
|
audit_id TEXT PRIMARY KEY,
|
|
resource_type TEXT NOT NULL,
|
|
resource_id TEXT NOT NULL,
|
|
action TEXT NOT NULL,
|
|
revision INTEGER NOT NULL DEFAULT 0,
|
|
actor TEXT NOT NULL,
|
|
request_id TEXT,
|
|
details_json TEXT NOT NULL,
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_audit_resource ON audit_entries(resource_type, resource_id, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_audit_request ON audit_entries(request_id);
|