admin / Syanpse-Vanguard
public
Syanpse-Vanguard / synapse-vanguard-v3 / sv / storage / schema.sql
5131 B · main
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 | -- Synapse-Vanguard core schema (DuckDB dialect). -- Ordering: timestamp-first + low-cardinality dims maximizes zone-map pruning -- and dictionary/RLE compression. Insert batches time-sorted for best layout. CREATE TABLE IF NOT EXISTS events ( ts TIMESTAMP NOT NULL, ingested_ts TIMESTAMP NOT NULL, tenant_id VARCHAR NOT NULL, -- UUID as text source_type VARCHAR NOT NULL, -- winlog | journald | syslog | auditd | netflow host VARCHAR NOT NULL, host_ip VARCHAR, event_id INTEGER, severity UTINYINT, -- 0..7 syslog scale category VARCHAR, action VARCHAR, outcome VARCHAR, user_name VARCHAR, process_name VARCHAR, mitre_technique VARCHAR, message VARCHAR, raw JSON, labels JSON ); CREATE TABLE IF NOT EXISTS alerts ( id VARCHAR PRIMARY KEY, created_ts TIMESTAMP NOT NULL, tenant_id VARCHAR NOT NULL, rule_id VARCHAR NOT NULL, rule_name VARCHAR NOT NULL, severity UTINYINT NOT NULL, status VARCHAR NOT NULL DEFAULT 'open', mitre VARCHAR, summary VARCHAR, assignee VARCHAR ); -- Agent registry: identity, enrolled channels, revocation. CREATE TABLE IF NOT EXISTS agents ( id VARCHAR PRIMARY KEY, tenant_id VARCHAR NOT NULL, hostname VARCHAR NOT NULL, platform VARCHAR NOT NULL, agent_version VARCHAR, channels JSON, cert_cn VARCHAR, -- CN bound to the signed client cert ingest_token_hash VARCHAR, -- dev-mode bearer fallback (sha256) last_seq BIGINT DEFAULT 0, last_ip VARCHAR, -- source IP of the agent's last connection revoked BOOLEAN DEFAULT FALSE, enrolled_ts TIMESTAMP NOT NULL, last_seen_ts TIMESTAMP ); -- Key/value settings (per tenant). Holds the vault-encrypted ingress API key -- used by external Synapse-suite apps (e.g. IronNode) to push events. CREATE TABLE IF NOT EXISTS settings ( tenant_id VARCHAR NOT NULL, key VARCHAR NOT NULL, value JSON, -- app-defined; secrets stored as an encrypted blob updated_ts TIMESTAMP, PRIMARY KEY (tenant_id, key) ); -- False-positive tuning. When an event matches an enabled suppression, alert -- rules are NOT applied to it (it is still logged, just not escalated). CREATE TABLE IF NOT EXISTS suppressions ( id VARCHAR PRIMARY KEY, tenant_id VARCHAR NOT NULL, alert_name VARCHAR, -- optional: only this alert type host VARCHAR, -- optional exact host source_type VARCHAR, -- optional exact source message_contains VARCHAR, -- optional substring (case-insensitive) note VARCHAR, -- analyst reason created_by VARCHAR, enabled BOOLEAN DEFAULT TRUE, created_ts TIMESTAMP, hits BIGINT DEFAULT 0, last_hit_ts TIMESTAMP ); -- Keyword alert rules. When an ingested event's message matches a rule's -- keyword(s), Vanguard re-tags it as a named "Alert" at the rule's severity. CREATE TABLE IF NOT EXISTS alert_rules ( id VARCHAR PRIMARY KEY, tenant_id VARCHAR NOT NULL, name VARCHAR NOT NULL, -- the Alert type name (admin-configurable) keywords JSON NOT NULL, -- array of keyword strings match_mode VARCHAR DEFAULT 'any', -- 'any' | 'all' severity UTINYINT DEFAULT 1, -- assigned severity (default 1 = ALERT) enabled BOOLEAN DEFAULT TRUE, webhook_id VARCHAR, -- optional: fire this webhook on match created_ts TIMESTAMP, last_hit_ts TIMESTAMP, hits BIGINT DEFAULT 0 ); -- Outbound webhooks: Vanguard POSTs qualifying (high-severity) events to these -- endpoints, e.g. an IronNode tenant webhook that triggers a response playbook. CREATE TABLE IF NOT EXISTS webhooks ( id VARCHAR PRIMARY KEY, tenant_id VARCHAR NOT NULL, name VARCHAR NOT NULL, url VARCHAR NOT NULL, secret VARCHAR, -- optional HMAC-SHA256 signing secret min_severity UTINYINT DEFAULT 2, -- fire when event.severity <= this (0=emergency) enabled BOOLEAN DEFAULT TRUE, created_ts TIMESTAMP, last_status VARCHAR, -- last delivery result (e.g. "200" / "error") last_fired_ts TIMESTAMP ); -- Local user store (RBAC). Federation (OIDC/SAML) maps external identities -- onto these same roles. CREATE TABLE IF NOT EXISTS users ( id VARCHAR PRIMARY KEY, tenant_id VARCHAR NOT NULL, username VARCHAR NOT NULL UNIQUE, password_hash VARCHAR NOT NULL, -- bcrypt role VARCHAR NOT NULL, -- READ_ONLY_VIEWER | SECURITY_ANALYST | TENANT_ADMIN disabled BOOLEAN DEFAULT FALSE, created_ts TIMESTAMP NOT NULL ); |