KArtSell.Aegis/docs/contracts/data/VS-07_DATA_CONTRACT.md

VS-07: Risk Alerts — Data Contract

Version: 1.0
Compliance: Soft-Delete + Audit Trail
Migration: 0036_risk_alerts.sql (DbUp)

---

Schema Design

1. alert_thresholds (Configuration — Mutable)

Portfolio-specific or organization-wide alert thresholds.

CREATE TABLE risk_management.alert_thresholds (
    threshold_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    portfolio_id UUID NOT NULL REFERENCES risk_management.portfolios(portfolio_id),
    
    -- Threshold definition
    threshold_type VARCHAR(50) NOT NULL, -- 'concentration', 'var', 'volatility', 'singlePosition'
    threshold_name VARCHAR(255),
    threshold_value DECIMAL(5, 2),
    
    -- Escalation timing (minutes from initial)
    warn_at_minutes INT DEFAULT 2,
    critical_at_minutes INT DEFAULT 5,
    
    -- Status
    is_active BOOLEAN DEFAULT true,
    
    -- Audit
    created_by VARCHAR(100),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE(portfolio_id, threshold_type)
);

2. risk_alerts (Soft-Delete — Alert Lifecycle)

Active and historical alerts. Current state filtered by removed_at IS NULL.

CREATE TABLE risk_management.risk_alerts (
    alert_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    portfolio_id UUID NOT NULL REFERENCES risk_management.portfolios(portfolio_id),
    threshold_id UUID NOT NULL REFERENCES risk_management.alert_thresholds(threshold_id),
    
    -- Alert definition
    threshold_type VARCHAR(50) NOT NULL,
    threshold_name VARCHAR(255),
    current_value DECIMAL(10, 4),
    threshold_value DECIMAL(10, 4),
    
    -- Lifecycle
    status VARCHAR(50) NOT NULL DEFAULT 'Initial', -- Initial, Warning, Critical, Resolved
    triggered_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    warned_at TIMESTAMP NULL,
    critical_at TIMESTAMP NULL,
    resolved_at TIMESTAMP NULL,
    
    -- Soft-delete
    removed_at TIMESTAMP NULL,
    
    -- Message
    message TEXT,
    
    -- Audit
    correlation_id UUID,
    created_by VARCHAR(100),
    
    UNIQUE(portfolio_id, threshold_type, triggered_at, correlation_id),
    CHECK (removed_at IS NULL OR resolved_at IS NOT NULL)
);

3. alert_escalations (Append-Only — Audit)

Immutable record of all escalation events.

CREATE TABLE risk_management.alert_escalations (
    escalation_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    alert_id UUID NOT NULL REFERENCES risk_management.risk_alerts(alert_id),
    
    -- Escalation
    from_status VARCHAR(50),
    to_status VARCHAR(50),
    escalated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    -- Reason
    reason VARCHAR(255), -- 'time_threshold', 'manual', 'critical_threshold'
    
    -- Audit
    triggered_by VARCHAR(100),
    correlation_id UUID
);

4. alert_resolutions (Append-Only — How Resolved)

Immutable record of alert resolution.

CREATE TABLE risk_management.alert_resolutions (
    resolution_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    alert_id UUID NOT NULL REFERENCES risk_management.risk_alerts(alert_id),
    
    -- Resolution
    resolved_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    duration_minutes INT,
    
    -- How resolved
    resolution_type VARCHAR(50), -- 'auto', 'manual', 'threshold_back_to_safe'
    
    -- Notes
    resolution_notes TEXT,
    
    -- Audit
    resolved_by VARCHAR(100),
    correlation_id UUID
);

5. alert_events (Append-Only — Published Events)

Published to shared.outbox.

Schema (JSONB in outbox.payload):

{
  "eventType": "RiskAlertTriggered|RiskAlertEscalated|RiskAlertResolved",
  "alertId": "550e8400-e29b-41d4-a716-446655440008",
  "portfolioId": "550e8400-e29b-41d4-a716-446655440001",
  "thresholdType": "concentration",
  "severity": "Warning",
  "currentValue": 65.2,
  "threshold": 60,
  "triggeredAt": "2026-08-05T10:30:00Z",
  "correlationId": "alert-2026-08-05-001"
}

---

Query Patterns

Current Active Alerts

SELECT
    alert_id,
    threshold_type,
    threshold_name,
    current_value,
    threshold_value,
    status,
    triggered_at,
    DATEDIFF(MINUTE, triggered_at, CURRENT_TIMESTAMP) as duration_minutes
FROM risk_management.risk_alerts
WHERE
    portfolio_id = @portfolioId
    AND removed_at IS NULL
    AND status IN ('Initial', 'Warning', 'Critical')
ORDER BY critical_at DESC NULLS LAST;

Alert History (Last 30 Days)

SELECT
    alert_id,
    threshold_type,
    status,
    triggered_at,
    resolved_at,
    DATEDIFF(MINUTE, triggered_at, resolved_at) as duration_minutes
FROM risk_management.risk_alerts
WHERE
    portfolio_id = @portfolioId
    AND triggered_at >= CURRENT_DATE - INTERVAL 30 DAY
ORDER BY triggered_at DESC;

Pending Escalations

SELECT
    a.alert_id,
    a.threshold_type,
    a.status,
    DATEDIFF(MINUTE, a.triggered_at, CURRENT_TIMESTAMP) as minutes_elapsed,
    t.warn_at_minutes,
    t.critical_at_minutes
FROM risk_management.risk_alerts a
JOIN risk_management.alert_thresholds t ON a.threshold_id = t.threshold_id
WHERE
    a.portfolio_id = @portfolioId
    AND a.removed_at IS NULL
    AND (
        (a.status = 'Initial' AND DATEDIFF(MINUTE, a.triggered_at, CURRENT_TIMESTAMP) >= t.warn_at_minutes)
        OR (a.status = 'Warning' AND DATEDIFF(MINUTE, a.triggered_at, CURRENT_TIMESTAMP) >= t.critical_at_minutes)
    )
ORDER BY a.triggered_at ASC;

Idempotency Check

SELECT alert_id FROM risk_management.risk_alerts
WHERE
    portfolio_id = @portfolioId
    AND threshold_type = @thresholdType
    AND triggered_at >= CURRENT_TIMESTAMP - INTERVAL 5 MINUTE
    AND correlation_id = @correlationId
    AND removed_at IS NULL
LIMIT 1;

---

Indexes

Table	Columns	Reason
alert_thresholds	(portfolio_id, is_active)	Fast active threshold lookup
risk_alerts	(portfolio_id, removed_at, status)	Fast active alert queries
risk_alerts	(triggered_at)	Fast escalation time checks
alert_escalations	(alert_id, escalated_at)	Fast escalation audit trail
alert_resolutions	(alert_id)	Fast resolution lookup

---

Pre-loaded Thresholds

On fresh install, create default thresholds per portfolio:

INSERT INTO risk_management.alert_thresholds VALUES
    (gen_random_uuid(), @portfolioId, 'concentration', 'Top-5 Holdings > 60%', 60.0, 2, 5, true, ...),
    (gen_random_uuid(), @portfolioId, 'var', 'VAR > 20% of Portfolio', 20.0, 2, 5, true, ...),
    (gen_random_uuid(), @portfolioId, 'volatility', 'Annualized Vol > 30%', 30.0, 3, 7, true, ...),
    (gen_random_uuid(), @portfolioId, 'singlePosition', 'Single Position > 40%', 40.0, 0, 5, true, ...);

---

Escalation Job Logic (Hangfire)

Scheduled: Every 1 minute (after metric updates)

FOR each active alert WHERE removed_at IS NULL:
    minutes_elapsed = NOW - triggered_at
    threshold = alert_thresholds[alert.threshold_type]
    
    IF status = 'Initial' AND minutes_elapsed >= threshold.warn_at_minutes:
        UPDATE risk_alerts SET status = 'Warning', warned_at = NOW
        INSERT alert_escalations(from_status='Initial', to_status='Warning')
        PUBLISH RiskAlertEscalated event
    
    ELSE IF status = 'Warning' AND minutes_elapsed >= threshold.critical_at_minutes:
        UPDATE risk_alerts SET status = 'Critical', critical_at = NOW
        INSERT alert_escalations(from_status='Warning', to_status='Critical')
        PUBLISH RiskAlertEscalated event
    
    ELSE IF metric_back_to_safe(alert.threshold_type, current_value):
        UPDATE risk_alerts SET status = 'Resolved', removed_at = NOW
        INSERT alert_resolutions(resolution_type='threshold_back_to_safe')
        PUBLISH RiskAlertResolved event

---

Compliance

✅ AGENTS.md v16.0:

• Soft-delete (removed_at, not hard delete)
• Append-only audit (alert_escalations, alert_resolutions immutable)
• Correlation ID tracing (correlation_id)
• Idempotency key (portfolio_id + threshold_type + triggered_at + correlation_id)
• Full lifecycle tracked (triggered → escalated → resolved)

✅ Alert Accuracy:

• Thresholds configurable per portfolio
• Escalation timing deterministic (minutes from triggered_at)
• Automatic resolution when metric back to safe
• No false duplicates (UNIQUE constraint)

---

Test Scenarios

Test	Data Setup	Assertion
Threshold trigger	Metric exceeds threshold	Alert created with status=Initial
Escalation (2min)	Wait 2 minutes	Alert status → Warning, warned_at populated
Escalation (5min)	Wait 5 minutes	Alert status → Critical, critical_at populated
Auto-resolution	Metric back to safe	Alert status → Resolved, removed_at populated
Idempotency	Same breach twice in 5min	Single alert, no duplicate
Soft-delete	Resolve alert	Query filters correctly (removed_at IS NULL)
History query	Resolved alert	Appears in history, not current alerts