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

VS-05: Risk Metrics — Data Contract

Version: 1.0
Compliance: Point-in-Time (PIT) + Append-Only Audit
Migration: 0034_risk_metrics.sql (DbUp)

---

Schema Design

1. risk_metrics (PIT — Metric Snapshots)

Daily risk metric snapshots. Each day → new revision. Reads filter WHERE published_at <= cutoff AND removed_at IS NULL.

CREATE TABLE risk_management.risk_metrics (
    metric_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    portfolio_id UUID NOT NULL REFERENCES risk_management.portfolios(portfolio_id),
    
    -- Calculation date
    calculation_date DATE NOT NULL,
    
    -- VAR (Value at Risk)
    var_95_amount DECIMAL(20, 2),       -- 95% confidence, 1-day horizon
    var_95_percent DECIMAL(5, 2),       -- % of portfolio value
    var_model VARCHAR(50),              -- 'Parametric', 'HistoricalSim', 'MonteCarlo'
    
    -- Sharpe Ratio (rolling 252-day)
    sharpe_ratio DECIMAL(5, 3),
    sharpe_rolling_days INT DEFAULT 252,
    risk_free_rate DECIMAL(5, 4),       -- Configurable, default 4.5%
    
    -- Sortino Ratio (downside focus)
    sortino_ratio DECIMAL(5, 3),
    downside_deviation DECIMAL(5, 4),   -- Annual
    
    -- Concentration
    top_five_percent DECIMAL(5, 2),     -- Top 5 holdings as % of portfolio
    hirschman_index DECIMAL(3, 2),      -- 0-1, 1=fully concentrated
    max_single_position DECIMAL(5, 2),  -- Largest position %
    
    -- Volatility
    volatility_annualized DECIMAL(5, 4),
    volatility_rolling_days INT DEFAULT 30,
    
    -- Data quality
    quality_score INT DEFAULT 100,      -- [0, 100]
    quality_issues JSONB,               -- Array of strings
    
    -- PIT
    revision INT NOT NULL DEFAULT 1,
    published_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    removed_at TIMESTAMP NULL,
    
    -- Audit
    correlation_id UUID,
    job_run_id UUID,
    
    -- Constraints
    UNIQUE(portfolio_id, calculation_date, revision),
    CHECK (var_95_percent BETWEEN 0 AND 100),
    CHECK (hirschman_index BETWEEN 0 AND 1),
    CHECK (quality_score BETWEEN 0 AND 100)
);

2. risk_metric_components (Append-Only — Breakdown)

Decomposition of risk into asset-class and sector contributions.

CREATE TABLE risk_management.risk_metric_components (
    component_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    metric_id UUID NOT NULL REFERENCES risk_management.risk_metrics(metric_id),
    
    -- Decomposition
    component_type VARCHAR(50), -- 'AssetClass', 'Sector', 'Geography'
    component_name VARCHAR(255),
    
    -- Contribution to VAR
    var_contribution DECIMAL(20, 2),
    var_contribution_percent DECIMAL(5, 2),
    
    -- Contribution to Sharpe
    sharpe_contribution DECIMAL(5, 3),
    
    -- Exposure
    position_count INT,
    total_value DECIMAL(20, 2),
    
    -- Audit
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    correlation_id UUID
);

3. risk_calculation_jobs (Append-Only — Audit)

Immutable log of all metric calculations.

CREATE TABLE risk_management.risk_calculation_jobs (
    job_id UUID PRIMARY KEY,
    portfolio_id UUID NOT NULL REFERENCES risk_management.portfolios(portfolio_id),
    
    -- Execution
    calculation_date DATE NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'Queued', -- Queued, Running, Completed, Failed
    started_at TIMESTAMP NULL,
    completed_at TIMESTAMP NULL,
    duration_seconds INT NULL,
    
    -- Input data
    price_cutoff DATE NOT NULL,
    sample_size INT,                -- Number of days used for Sharpe/Sortino
    
    -- Results
    metrics_rows_created INT DEFAULT 0,
    components_rows_created INT DEFAULT 0,
    
    -- Error handling
    error_message TEXT NULL,
    retry_count INT DEFAULT 0,
    
    -- Audit
    correlation_id UUID NOT NULL,
    job_run_id UUID NOT NULL,
    triggered_by VARCHAR(100),      -- 'Scheduler', 'Manual', 'Alert'
    
    UNIQUE(portfolio_id, calculation_date, correlation_id)  -- Idempotency
);

4. risk_metric_alerts (Append-Only — Published Events)

Published to shared.outbox via EventPublisher.

Schema (JSONB in outbox.payload):

{
  "eventId": "550e8400-e29b-41d4-a716-446655440005",
  "eventType": "PortfolioMetricsCalculated",
  "portfolioId": "550e8400-e29b-41d4-a716-446655440001",
  "calculationDate": "2026-08-05",
  "metrics": {
    "var95": 15250.00,
    "sharpe": 1.85,
    "sortino": 2.45,
    "concentration": 52.3
  },
  "qualityFlags": ["high_concentration"],
  "calculatedAt": "2026-08-05T09:30:00Z",
  "correlationId": "risk-2026-08-05-001"
}

---

PIT Query Patterns

Current Risk Metrics

SELECT
    portfolio_id,
    calculation_date,
    var_95_amount,
    var_95_percent,
    sharpe_ratio,
    sortino_ratio,
    top_five_percent,
    volatility_annualized
FROM risk_management.risk_metrics
WHERE
    portfolio_id = @portfolioId
    AND published_at <= @cutoff
    AND removed_at IS NULL
ORDER BY calculation_date DESC
LIMIT 1;

Historical Metrics (as of Date)

SELECT * FROM risk_management.risk_metrics
WHERE
    portfolio_id = @portfolioId
    AND calculation_date <= @asOfDate
    AND published_at <= @asOfDate
    AND removed_at IS NULL
ORDER BY calculation_date DESC
LIMIT 1;

Concentration Trend

SELECT
    calculation_date,
    top_five_percent,
    hirschman_index,
    max_single_position
FROM risk_management.risk_metrics
WHERE
    portfolio_id = @portfolioId
    AND published_at <= @cutoff
    AND removed_at IS NULL
ORDER BY calculation_date DESC
LIMIT 30;

Idempotency Check

SELECT job_id FROM risk_management.risk_calculation_jobs
WHERE
    portfolio_id = @portfolioId
    AND calculation_date = @date
    AND correlation_id = @correlationId
    AND status IN ('Running', 'Completed')
LIMIT 1;

---

Upsert Strategy

On new calculation request:

INSERT INTO risk_management.risk_calculation_jobs
    (job_id, portfolio_id, calculation_date, correlation_id, status)
VALUES
    (@jobId, @portfolioId, @date, @correlationId, 'Queued')
ON CONFLICT (portfolio_id, calculation_date, correlation_id)
    DO UPDATE SET
        status = 'Queued'
    WHERE EXCLUDED.status = 'Completed';

Idempotency: Same portfolio_id + calculation_date + correlation_id → no duplicate job

---

Indexes (Performance SLA: <200ms GET)

Table	Columns	Reason
risk_metrics	(portfolio_id, published_at, removed_at)	Fast current snapshot lookup
risk_metrics	(calculation_date)	Fast historical queries
risk_metric_components	(metric_id)	Fast component breakdown retrieval
risk_calculation_jobs	(portfolio_id, status)	Fast pending job lookup
risk_calculation_jobs	(calculation_date, correlation_id)	Fast idempotency check

---

Data Freshness Guarantees

• Prices: Updated daily at 9:00 KST (from VS-03)
• Metrics: Calculated at 9:30 KST (after market open)
• Caching: Results cached <1hr (refresh daily)
• Events: Published synchronously (no queue lag)

---

Compliance

✅ AGENTS.md v16.0:

• No SELECT * (explicit columns)
• PIT versioning (published_at, revision, removed_at)
• Append-only audit (risk_calculation_jobs immutable)
• Correlation ID tracing (correlation_id + job_run_id)
• Idempotency key (portfolio_id + calculation_date + correlation_id)

✅ Calculation Accuracy:

• VAR: Parametric model (95% confidence, 1-day horizon)
• Sharpe: 252-day rolling average (annual)
• Sortino: Downside deviation focus

✅ Auditability:

• All calculations traced (job_run_id + correlation_id)
• Quality scores recorded (quality_score, quality_issues)
• Decomposition preserved (risk_metric_components)

---

Test Scenarios

Test	Data Setup	Assertion
VAR calculation	252 days of prices	VAR-95 amount within ±5% of historical
Sharpe ratio	Positive returns	Sharpe ratio > 0
Concentration	40% in single stock	top_five_percent >= 40
Idempotency	Same calculation_date twice	Job not duplicated
Soft-delete	Set removed_at on metric	Query filters correctly
Quality flag	Missing price data	quality_score < 100, quality_issues populated