Open-source AI Security Operations Center: alert fusion, LLM-agent triage, MITRE ATT&CK investigation, and a replayable decision ledger for every agent step. Self-hostable, runs with no API keys, MIT licensed. Ships an MCP server for Claude, Cursor and Continue.
Python agentic-aiai-securityalert-triageblue-teamcybersecuritydetection-engineeringincident-responselanggraphllm-agentsmcpmitre-attackmodel-context-protocolpurple-teamsecurity-operationsself-hostedsiemsoarsocthreat-detectionthreat-intelligence
-- 068: where the SIEM reconciliation sweep got to, and why it stopped if it did.
--
-- Gap-closure Phase 2.1, closing D15.
--
-- The sweep polls a customer's own SIEM for the closures their analysts made
-- there, so a tenant whose queue lives in Splunk ES still accumulates a track
-- record. That is bounded work against somebody else's API on a timer, and
-- three properties have to survive a restart for it to be safe to run at all.
--
-- A watermark, so a window is read once
-- -------------------------------------
-- Without one the sweep re-reads the same hours forever: every tick costs the
-- vendor a search and returns findings that were already graded. `watermark_at`
-- is the close time the last successful pass reached, and the next window
-- starts there minus a small overlap, because a vendor's search index lags its
-- own close events and a window that starts exactly where the last one ended
-- steps over anything indexed late. The overlap is safe to re-read:
-- `reconcile_findings` only writes decisions whose `resolved_at` is still null,
-- so an overlapping finding is reported as `already_resolved` rather than
-- graded twice.
--
-- A distinction between a fault that will clear and one that will not
-- -------------------------------------------------------------------
-- `blocked_reason` is set when the vendor refused the stored credentials, or
-- when the credentials cannot be decrypted at all. Neither resolves by waiting,
-- so the sweep stops polling that connector and says what an operator must do.
-- `blocked_connector_updated_at` is what unblocks it: the sweep compares it to
-- `connectors.updated_at`, so re-saving the connector resumes polling and
-- nothing else does. A timer would be the churn this column exists to avoid,
-- and a manual reset would be a support ticket.
--
-- A record of the healthy case, not only the broken one
-- -----------------------------------------------------
-- `last_status` is written on every pass including the ones with nothing to
-- do. A sweep that silently stopped and a sweep whose tenant closed nothing
-- last night are indistinguishable from outside unless the quiet case is
-- recorded, and that ambiguity is the failure this table's health endpoint
-- exists to remove.
--
-- Keyed on the connector, not the tenant
-- --------------------------------------
-- A tenant can have Splunk and Sentinel both connected, each with its own
-- index lag, its own rate limit and its own credential lifetime. One watermark
-- across both would replay whichever is slower and skip whichever is faster.
CREATE TABLE IF NOT EXISTS aisoc_shadow_reconcile_state (
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
connector_id UUID NOT NULL REFERENCES connectors(id) ON DELETE CASCADE,
-- Denormalised from the connector so a health read does not need a join
-- and so the row still names its vendor after the connector is deleted
-- and the cascade has not yet run.
vendor TEXT NOT NULL,
-- The close time the last successful pass reached. NULL before the first
-- one, which is how the sweep knows to start from when measurement began
-- rather than from the beginning of the vendor's retention.
watermark_at TIMESTAMPTZ,
last_run_at TIMESTAMPTZ,
-- ok | idle | blocked | transient. Written every pass, including the ones
-- that found nothing, so "nothing to do" is a recorded state rather than
-- an absence that looks like a stopped worker.
last_status TEXT NOT NULL DEFAULT 'idle',
-- One operator-facing sentence. Never an exception repr: a reason nobody
-- can act on is the same as no reason.
last_detail TEXT,
-- Counters from the most recent pass, kept apart because they answer
-- different questions. `last_matched` at zero with `last_considered` high
-- means the vendor finding id is not reaching the decision rows, which is
-- a wiring fault rather than a quiet week.
last_considered INTEGER NOT NULL DEFAULT 0,
last_matched INTEGER NOT NULL DEFAULT 0,
last_unmatched INTEGER NOT NULL DEFAULT 0,
-- Transient-failure run length, so a caller can tell one bad night from a
-- fault that has outlived any explanation a retry would fix.
consecutive_failures INTEGER NOT NULL DEFAULT 0,
-- Set only for a condition that will not clear on its own.
blocked_reason TEXT,
blocked_at TIMESTAMPTZ,
-- `connectors.updated_at` as it stood when the block was recorded. The
-- sweep resumes when the live value differs, which is exactly "somebody
-- changed the connector" and nothing weaker.
blocked_connector_updated_at TIMESTAMPTZ,
-- Honoured before the next poll. Set from a vendor's own `Retry-After`,
-- so a SIEM that asks us to slow down is obeyed rather than estimated.
retry_after TIMESTAMPTZ,
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
PRIMARY KEY (tenant_id, connector_id)
);
COMMENT ON TABLE aisoc_shadow_reconcile_state IS
'Per-connector progress of the shadow-reconciliation sweep: how far it has '
'read, when it last ran, and whether what stopped it was transient or needs '
'an operator.';
COMMENT ON COLUMN aisoc_shadow_reconcile_state.watermark_at IS
'Latest vendor close time successfully reconciled. The next window starts '
'here minus the configured overlap, never at the wall clock.';
COMMENT ON COLUMN aisoc_shadow_reconcile_state.blocked_connector_updated_at IS
'connectors.updated_at at the moment of the block. Polling resumes when the '
'live value differs, so a revoked credential stops churning and restarts on '
'the one event that could have fixed it.';
-- The sweep picks its next batch by "least recently run, not blocked", so that
-- is the index. Partial on the unblocked rows because a blocked connector is
-- never a candidate and carrying it in the index only widens the scan.
CREATE INDEX IF NOT EXISTS idx_shadow_reconcile_due
ON aisoc_shadow_reconcile_state (last_run_at NULLS FIRST)
WHERE blocked_reason IS NULL;
-- Row-level security. The `OR current_tenant_id() IS NULL` arm is what lets the
-- cross-tenant sweep read every tenant's row; dropping it would make the sweep
-- silently see nothing, which here means every tenant's scorecard quietly
-- stopping with no error anywhere. FORCE so the owner does not walk past.
ALTER TABLE aisoc_shadow_reconcile_state ENABLE ROW LEVEL SECURITY;
ALTER TABLE aisoc_shadow_reconcile_state FORCE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS aisoc_shadow_reconcile_state_tenant ON aisoc_shadow_reconcile_state;
CREATE POLICY aisoc_shadow_reconcile_state_tenant ON aisoc_shadow_reconcile_state
USING (tenant_id = current_tenant_id() OR current_tenant_id() IS NULL);
-- 061 set ALTER DEFAULT PRIVILEGES so new tables pick these up, but only for
-- tables created by the role that ran it. Granting explicitly means a chain
-- replayed by a different owner still leaves the runtime role able to work.
DO $$
BEGIN
IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'aisoc_app') THEN
GRANT SELECT, INSERT, UPDATE, DELETE ON aisoc_shadow_reconcile_state TO aisoc_app;
END IF;
END
$$;