215 lines
9.4 KiB
SQL
215 lines
9.4 KiB
SQL
-- EvoBGP initial schema (SQLite, optional microVPS_sqlite / single-container).
|
|
-- UUIDs: supply from application (ULID/UUIDv7). JSON columns stored as TEXT.
|
|
|
|
PRAGMA foreign_keys = ON;
|
|
|
|
CREATE TABLE tenant (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
name TEXT NOT NULL,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
|
|
updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
|
|
CHECK (length(trim(slug)) > 0)
|
|
);
|
|
|
|
CREATE TABLE doh_profile (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
tenant_id TEXT NOT NULL REFERENCES tenant (id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL DEFAULT '',
|
|
url TEXT NOT NULL,
|
|
timeout_ms INTEGER,
|
|
secret_ref TEXT,
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
|
|
updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
|
|
CHECK (length(trim(url)) > 0)
|
|
);
|
|
|
|
CREATE INDEX idx_doh_profile_tenant ON doh_profile (tenant_id);
|
|
|
|
CREATE TABLE bgp_community (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
tenant_id TEXT NOT NULL REFERENCES tenant (id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL DEFAULT '',
|
|
kind TEXT NOT NULL,
|
|
value_json TEXT NOT NULL DEFAULT '{}',
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
|
|
updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
|
|
CHECK (length(trim(kind)) > 0)
|
|
);
|
|
|
|
CREATE INDEX idx_bgp_community_tenant ON bgp_community (tenant_id);
|
|
|
|
CREATE TABLE module (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
tenant_id TEXT NOT NULL REFERENCES tenant (id) ON DELETE CASCADE,
|
|
type TEXT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
enabled INTEGER NOT NULL DEFAULT 1,
|
|
priority INTEGER NOT NULL DEFAULT 0,
|
|
doh_profile_id TEXT REFERENCES doh_profile (id) ON DELETE SET NULL,
|
|
refresh_interval_sec INTEGER,
|
|
cron_expr TEXT,
|
|
default_community_id TEXT REFERENCES bgp_community (id) ON DELETE SET NULL,
|
|
deleted_at TEXT,
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
|
|
updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
|
|
CHECK (type IN ('AS_PREFIXES', 'CDN_CIDRS', 'DOMAINS', 'IP_RANGES')),
|
|
CHECK (length(trim(name)) > 0)
|
|
);
|
|
|
|
CREATE INDEX idx_module_tenant ON module (tenant_id);
|
|
CREATE INDEX idx_module_tenant_type ON module (tenant_id, type);
|
|
CREATE INDEX idx_module_tenant_enabled ON module (tenant_id, enabled) WHERE deleted_at IS NULL;
|
|
|
|
CREATE TABLE module_cdn_source (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
module_id TEXT NOT NULL REFERENCES module (id) ON DELETE CASCADE,
|
|
source_kind TEXT NOT NULL,
|
|
url TEXT NOT NULL,
|
|
etag TEXT,
|
|
refresh_interval_sec INTEGER,
|
|
community_id TEXT REFERENCES bgp_community (id) ON DELETE SET NULL,
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
|
|
updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
|
|
CHECK (length(trim(url)) > 0)
|
|
);
|
|
|
|
CREATE INDEX idx_module_cdn_source_module ON module_cdn_source (module_id);
|
|
|
|
CREATE TABLE module_domain_entry (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
module_id TEXT NOT NULL REFERENCES module (id) ON DELETE CASCADE,
|
|
fqdn TEXT NOT NULL,
|
|
community_id TEXT REFERENCES bgp_community (id) ON DELETE SET NULL,
|
|
resolve_meta TEXT NOT NULL DEFAULT '{}',
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
|
|
updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
|
|
CHECK (length(trim(fqdn)) > 0)
|
|
);
|
|
|
|
CREATE INDEX idx_module_domain_entry_module ON module_domain_entry (module_id);
|
|
|
|
CREATE TABLE module_as_entry (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
module_id TEXT NOT NULL REFERENCES module (id) ON DELETE CASCADE,
|
|
asn INTEGER,
|
|
prefix TEXT,
|
|
community_id TEXT REFERENCES bgp_community (id) ON DELETE SET NULL,
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
|
|
updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
|
|
CHECK (asn IS NOT NULL OR prefix IS NOT NULL)
|
|
);
|
|
|
|
CREATE INDEX idx_module_as_entry_module ON module_as_entry (module_id);
|
|
|
|
CREATE TABLE module_ip_range_entry (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
module_id TEXT NOT NULL REFERENCES module (id) ON DELETE CASCADE,
|
|
prefix TEXT NOT NULL,
|
|
community_id TEXT REFERENCES bgp_community (id) ON DELETE SET NULL,
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
|
|
updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
|
|
);
|
|
|
|
CREATE INDEX idx_module_ip_range_entry_module ON module_ip_range_entry (module_id);
|
|
|
|
CREATE TABLE config_revision (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
tenant_id TEXT NOT NULL REFERENCES tenant (id) ON DELETE CASCADE,
|
|
module_id TEXT REFERENCES module (id) ON DELETE SET NULL,
|
|
content_hash TEXT NOT NULL,
|
|
parent_revision_id TEXT REFERENCES config_revision (id) ON DELETE SET NULL,
|
|
artifact_ref TEXT,
|
|
meta_json TEXT NOT NULL DEFAULT '{}',
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
|
|
CHECK (length(trim(content_hash)) > 0)
|
|
);
|
|
|
|
CREATE INDEX idx_config_revision_tenant ON config_revision (tenant_id, created_at DESC);
|
|
CREATE INDEX idx_config_revision_module ON config_revision (module_id) WHERE module_id IS NOT NULL;
|
|
|
|
CREATE TABLE bgp_speaker (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
tenant_id TEXT NOT NULL REFERENCES tenant (id) ON DELETE CASCADE,
|
|
role TEXT NOT NULL,
|
|
endpoint TEXT,
|
|
last_applied_revision_id TEXT REFERENCES config_revision (id) ON DELETE SET NULL,
|
|
meta_json TEXT NOT NULL DEFAULT '{}',
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
|
|
updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
|
|
CHECK (role IN ('master', 'replica'))
|
|
);
|
|
|
|
CREATE INDEX idx_bgp_speaker_tenant ON bgp_speaker (tenant_id);
|
|
|
|
CREATE TABLE bgp_peer (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
tenant_id TEXT NOT NULL REFERENCES tenant (id) ON DELETE CASCADE,
|
|
bgp_speaker_id TEXT REFERENCES bgp_speaker (id) ON DELETE SET NULL,
|
|
neighbor TEXT NOT NULL,
|
|
remote_asn INTEGER NOT NULL,
|
|
enabled INTEGER NOT NULL DEFAULT 1,
|
|
policies_json TEXT NOT NULL DEFAULT '{}',
|
|
meta_json TEXT NOT NULL DEFAULT '{}',
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
|
|
updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
|
|
);
|
|
|
|
CREATE INDEX idx_bgp_peer_tenant ON bgp_peer (tenant_id);
|
|
CREATE INDEX idx_bgp_peer_speaker ON bgp_peer (bgp_speaker_id);
|
|
|
|
CREATE TABLE revision_materialized_prefix (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
revision_id TEXT NOT NULL REFERENCES config_revision (id) ON DELETE CASCADE,
|
|
prefix TEXT NOT NULL,
|
|
community_id TEXT REFERENCES bgp_community (id) ON DELETE SET NULL,
|
|
source TEXT NOT NULL DEFAULT '',
|
|
meta_json TEXT NOT NULL DEFAULT '{}'
|
|
);
|
|
|
|
CREATE INDEX idx_rev_mat_prefix_revision ON revision_materialized_prefix (revision_id);
|
|
CREATE INDEX idx_rev_mat_prefix_value ON revision_materialized_prefix (prefix);
|
|
|
|
CREATE TABLE job_audit (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
tenant_id TEXT NOT NULL REFERENCES tenant (id) ON DELETE CASCADE,
|
|
kind TEXT NOT NULL,
|
|
status TEXT NOT NULL,
|
|
idempotency_key TEXT,
|
|
module_id TEXT REFERENCES module (id) ON DELETE SET NULL,
|
|
error_message TEXT,
|
|
progress_pct INTEGER,
|
|
meta_json TEXT NOT NULL DEFAULT '{}',
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
|
|
started_at TEXT,
|
|
finished_at TEXT,
|
|
CHECK (status IN ('queued', 'running', 'succeeded', 'failed', 'cancelled')),
|
|
CHECK (length(trim(kind)) > 0)
|
|
);
|
|
|
|
CREATE INDEX idx_job_audit_tenant_created ON job_audit (tenant_id, created_at DESC);
|
|
CREATE INDEX idx_job_audit_tenant_status ON job_audit (tenant_id, status);
|
|
CREATE UNIQUE INDEX idx_job_audit_idempotency ON job_audit (tenant_id, idempotency_key)
|
|
WHERE idempotency_key IS NOT NULL;
|
|
|
|
CREATE TABLE global_settings (
|
|
tenant_id TEXT NOT NULL REFERENCES tenant (id) ON DELETE CASCADE,
|
|
key TEXT NOT NULL,
|
|
value_json TEXT NOT NULL DEFAULT '{}',
|
|
updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
|
|
PRIMARY KEY (tenant_id, key),
|
|
CHECK (length(trim(key)) > 0)
|
|
);
|
|
|
|
CREATE TABLE module_cdn_fetch_log (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
module_id TEXT NOT NULL REFERENCES module (id) ON DELETE CASCADE,
|
|
source_id TEXT REFERENCES module_cdn_source (id) ON DELETE SET NULL,
|
|
http_status INTEGER,
|
|
bytes INTEGER,
|
|
error TEXT,
|
|
fetched_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
|
|
);
|
|
|
|
CREATE INDEX idx_module_cdn_fetch_log_module ON module_cdn_fetch_log (module_id, fetched_at DESC);
|