Schema & Semantic Layer

# Risk & Compliance Analytics - Schema & Semantic Layer Documentation

**Version 1.0 | DataVisuals™**

---

## Overview

This document provides comprehensive technical documentation for the Risk & Compliance Analytics data model, including database schema, semantic layer definitions, business rules, and API contracts.

---

## Database Schema

### Core Tables

#### `compliance_metrics`

Stores current and historical compliance metrics for regulatory monitoring.

```sql
CREATE TABLE compliance_metrics (
    metric_id SERIAL PRIMARY KEY,
    credit_union_id INTEGER NOT NULL,
    measurement_date DATE NOT NULL,
    bsa_aml_compliance DECIMAL(5,2),
    net_worth_ratio DECIMAL(5,2),
    capital_adequacy_ratio DECIMAL(5,2),
    loan_to_share_ratio DECIMAL(5,2),
    delinquency_rate DECIMAL(5,2),
    charge_off_rate DECIMAL(5,2),
    concentration_risk_score INTEGER,
    composite_camel_rating INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT valid_date CHECK (measurement_date <= CURRENT_DATE),
    CONSTRAINT valid_compliance CHECK (bsa_aml_compliance BETWEEN 0 AND 100),
    CONSTRAINT valid_camel CHECK (composite_camel_rating BETWEEN 1 AND 5)
);

CREATE INDEX idx_compliance_cu_date ON compliance_metrics(credit_union_id, measurement_date);
CREATE INDEX idx_compliance_date ON compliance_metrics(measurement_date DESC);
```

**Field Definitions:**

| Column | Type | Description | Business Rules |
|--------|------|-------------|----------------|
| `metric_id` | SERIAL | Primary key | Auto-increment |
| `credit_union_id` | INTEGER | Foreign key to credit union | Required, references `credit_unions.cu_id` |
| `measurement_date` | DATE | Date of measurement | Cannot be future date |
| `bsa_aml_compliance` | DECIMAL(5,2) | BSA/AML compliance percentage | 0-100, NULL if not measured |
| `net_worth_ratio` | DECIMAL(5,2) | Net worth as % of assets | Typically 6-15% |
| `capital_adequacy_ratio` | DECIMAL(5,2) | Capital adequacy percentage | Min 7% for well-capitalized |
| `loan_to_share_ratio` | DECIMAL(5,2) | Loans/Shares percentage | Optimal 70-80% |
| `delinquency_rate` | DECIMAL(5,2) | 60+ day delinquency rate | Lower is better, <1% excellent |
| `charge_off_rate` | DECIMAL(5,2) | Annual charge-off rate | Industry avg ~0.5% |
| `concentration_risk_score` | INTEGER | Risk concentration (0-100) | >80 requires attention |
| `composite_camel_rating` | INTEGER | NCUA CAMEL composite | 1 (strong) to 5 (unsatisfactory) |

---

#### `risk_alerts`

Tracks active and historical risk alerts requiring management attention.

```sql
CREATE TABLE risk_alerts (
    alert_id SERIAL PRIMARY KEY,
    credit_union_id INTEGER NOT NULL,
    alert_date DATE NOT NULL,
    severity VARCHAR(10) NOT NULL,
    category VARCHAR(50) NOT NULL,
    description TEXT NOT NULL,
    affected_accounts INTEGER,
    alert_status VARCHAR(20) DEFAULT 'active',
    assigned_to VARCHAR(100),
    resolution_date DATE,
    resolution_notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT valid_severity CHECK (severity IN ('high', 'medium', 'low')),
    CONSTRAINT valid_status CHECK (alert_status IN ('active', 'under_review', 'resolved', 'false_positive')),
    CONSTRAINT valid_resolution CHECK (
        (alert_status = 'resolved' AND resolution_date IS NOT NULL) OR
        (alert_status != 'resolved' AND resolution_date IS NULL)
    )
);

CREATE INDEX idx_alerts_cu_status ON risk_alerts(credit_union_id, alert_status);
CREATE INDEX idx_alerts_severity ON risk_alerts(severity, alert_date DESC);
CREATE INDEX idx_alerts_category ON risk_alerts(category);
```

**Field Definitions:**

| Column | Type | Description | Business Rules |
|--------|------|-------------|----------------|
| `alert_id` | SERIAL | Primary key | Auto-increment |
| `severity` | VARCHAR(10) | Alert priority level | 'high', 'medium', or 'low' |
| `category` | VARCHAR(50) | Risk category | BSA/AML, Credit Risk, Operational, etc. |
| `description` | TEXT | Alert description | Required, max 1000 chars |
| `affected_accounts` | INTEGER | Number of accounts impacted | NULL if N/A |
| `alert_status` | VARCHAR(20) | Current status | Defaults to 'active' |
| `resolution_date` | DATE | Date alert resolved | Required when status='resolved' |

---

#### `portfolio_risk`

Detailed risk metrics by loan product category.

```sql
CREATE TABLE portfolio_risk (
    risk_id SERIAL PRIMARY KEY,
    credit_union_id INTEGER NOT NULL,
    measurement_date DATE NOT NULL,
    product_category VARCHAR(50) NOT NULL,
    outstanding_balance DECIMAL(15,2) NOT NULL,
    number_of_loans INTEGER NOT NULL,
    delinquent_30_days DECIMAL(15,2) DEFAULT 0,
    delinquent_60_days DECIMAL(15,2) DEFAULT 0,
    delinquent_90_plus_days DECIMAL(15,2) DEFAULT 0,
    charge_offs_mtd DECIMAL(15,2) DEFAULT 0,
    charge_offs_ytd DECIMAL(15,2) DEFAULT 0,
    weighted_avg_rate DECIMAL(5,2),
    weighted_avg_term_months INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT valid_category CHECK (product_category IN (
        'Auto', 'Mortgage', 'Personal', 'Credit Card', 'Business'
    )),
    CONSTRAINT valid_balances CHECK (
        outstanding_balance >= 0 AND
        delinquent_30_days >= 0 AND
        delinquent_60_days >= 0 AND
        delinquent_90_plus_days >= 0
    )
);

CREATE INDEX idx_portfolio_cu_date ON portfolio_risk(credit_union_id, measurement_date);
CREATE INDEX idx_portfolio_category ON portfolio_risk(product_category);
```

**Field Definitions:**

| Column | Type | Description | Business Rules |
|--------|------|-------------|----------------|
| `product_category` | VARCHAR(50) | Loan product type | Enum: Auto, Mortgage, Personal, Credit Card, Business |
| `outstanding_balance` | DECIMAL(15,2) | Total portfolio balance | Must be >= 0 |
| `number_of_loans` | INTEGER | Count of active loans | Must be > 0 |
| `delinquent_30_days` | DECIMAL(15,2) | 30-59 days past due | Subset of outstanding_balance |
| `delinquent_60_days` | DECIMAL(15,2) | 60-89 days past due | Subset of outstanding_balance |
| `delinquent_90_plus_days` | DECIMAL(15,2) | 90+ days past due | Subset of outstanding_balance |
| `charge_offs_mtd` | DECIMAL(15,2) | Month-to-date charge-offs | Accumulated monthly |
| `charge_offs_ytd` | DECIMAL(15,2) | Year-to-date charge-offs | Resets January 1 |

---

#### `camel_ratings`

NCUA CAMEL component ratings and composite scores.

```sql
CREATE TABLE camel_ratings (
    rating_id SERIAL PRIMARY KEY,
    credit_union_id INTEGER NOT NULL,
    rating_date DATE NOT NULL,
    capital_adequacy INTEGER NOT NULL,
    asset_quality INTEGER NOT NULL,
    management INTEGER NOT NULL,
    earnings INTEGER NOT NULL,
    liquidity INTEGER NOT NULL,
    sensitivity INTEGER NOT NULL,
    composite_rating INTEGER NOT NULL,
    examiner_notes TEXT,
    next_exam_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT valid_ratings CHECK (
        capital_adequacy BETWEEN 1 AND 5 AND
        asset_quality BETWEEN 1 AND 5 AND
        management BETWEEN 1 AND 5 AND
        earnings BETWEEN 1 AND 5 AND
        liquidity BETWEEN 1 AND 5 AND
        sensitivity BETWEEN 1 AND 5 AND
        composite_rating BETWEEN 1 AND 5
    )
);

CREATE INDEX idx_camel_cu_date ON camel_ratings(credit_union_id, rating_date DESC);
```

**CAMEL Rating Scale:**
- **1** = Strong (performance substantially exceeds standards)
- **2** = Satisfactory (performance meets standards)
- **3** = Fair (performance below standards, weaknesses present)
- **4** = Marginal (serious weaknesses, regulatory concern)
- **5** = Unsatisfactory (critical weaknesses, immediate action required)

---

#### `fraud_incidents`

Tracks fraud detection, prevention, and loss metrics.

```sql
CREATE TABLE fraud_incidents (
    incident_id SERIAL PRIMARY KEY,
    credit_union_id INTEGER NOT NULL,
    incident_date DATE NOT NULL,
    detection_date DATE NOT NULL,
    fraud_type VARCHAR(50) NOT NULL,
    fraud_amount DECIMAL(15,2) NOT NULL,
    recovered_amount DECIMAL(15,2) DEFAULT 0,
    prevented BOOLEAN DEFAULT FALSE,
    member_id INTEGER,
    account_number VARCHAR(50),
    incident_status VARCHAR(20) DEFAULT 'investigating',
    resolution_date DATE,
    sar_filed BOOLEAN DEFAULT FALSE,
    law_enforcement_notified BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT valid_fraud_type CHECK (fraud_type IN (
        'Card Not Present', 'Card Present', 'ACH', 'Wire Transfer',
        'Check Fraud', 'Account Takeover', 'Identity Theft', 'Other'
    )),
    CONSTRAINT valid_status CHECK (incident_status IN (
        'investigating', 'confirmed', 'resolved', 'false_positive'
    )),
    CONSTRAINT valid_amounts CHECK (
        fraud_amount >= 0 AND
        recovered_amount >= 0 AND
        recovered_amount <= fraud_amount
    )
);

CREATE INDEX idx_fraud_cu_date ON fraud_incidents(credit_union_id, incident_date);
CREATE INDEX idx_fraud_type ON fraud_incidents(fraud_type);
CREATE INDEX idx_fraud_status ON fraud_incidents(incident_status);
```

---

## Semantic Layer

### Calculated Metrics

#### Compliance Metrics

**BSA/AML Compliance Score**
```sql
-- Percentage of transaction monitoring rules functioning correctly
-- Calculated from: CTR accuracy + SAR timeliness + CDD completion + Sanctions screening
SELECT 
    (ctr_accuracy * 0.25 + 
     sar_timeliness * 0.25 + 
     cdd_completion * 0.25 + 
     sanctions_screening * 0.25) AS bsa_aml_compliance
FROM compliance_components;
```

**Delinquency Rate**
```sql
-- Total loans 60+ days past due / Total loan portfolio
SELECT 
    ROUND((delinquent_60_days + delinquent_90_plus_days) / 
          NULLIF(outstanding_balance, 0) * 100, 2) AS delinquency_rate
FROM portfolio_risk
WHERE measurement_date = CURRENT_DATE;
```

**Net Worth Ratio**
```sql
-- Total Equity / Total Assets
-- Well Capitalized: >= 7%, Adequately Capitalized: 6-7%, Undercapitalized: < 6%
SELECT 
    ROUND((total_equity / NULLIF(total_assets, 0)) * 100, 2) AS net_worth_ratio,
    CASE 
        WHEN (total_equity / NULLIF(total_assets, 0)) >= 0.07 THEN 'Well Capitalized'
        WHEN (total_equity / NULLIF(total_assets, 0)) >= 0.06 THEN 'Adequately Capitalized'
        ELSE 'Undercapitalized'
    END AS capital_classification
FROM balance_sheet;
```

#### Risk Metrics

**Concentration Risk Score**
```sql
-- Composite score from product concentration, member concentration, geographic concentration
-- Scale: 0-100 (higher = greater risk)
SELECT 
    ROUND(
        (product_concentration * 0.40 +
         member_concentration * 0.30 +
         geographic_concentration * 0.30), 0
    ) AS concentration_risk_score,
    CASE 
        WHEN score >= 80 THEN 'High Risk - Enhanced Monitoring Required'
        WHEN score >= 60 THEN 'Moderate Risk - Monitor Quarterly'
        ELSE 'Low Risk - Standard Monitoring'
    END AS risk_classification
FROM (
    SELECT 
        -- Product concentration (commercial loans > 25% = 100 points)
        LEAST((commercial_loans / total_loans * 100 / 25) * 100, 100) AS product_concentration,
        -- Member concentration (top 10 borrowers > 15% = 100 points)
        LEAST((top_10_borrowers / total_loans * 100 / 15) * 100, 100) AS member_concentration,
        -- Geographic concentration (single county > 40% = 100 points)
        LEAST((max_county_concentration / 40) * 100, 100) AS geographic_concentration
    FROM concentration_analysis
) t;
```

**Fraud Prevention Rate**
```sql
-- Percentage of fraud attempts successfully prevented
SELECT 
    ROUND((SUM(CASE WHEN prevented = TRUE THEN fraud_amount ELSE 0 END) /
           NULLIF(SUM(fraud_amount), 0)) * 100, 1) AS prevention_rate
FROM fraud_incidents
WHERE incident_date >= DATE_TRUNC('month', CURRENT_DATE);
```

---

### Business Rules

#### Alert Generation Rules

**High Severity Alerts:**

1. **BSA/AML Red Flags**
   - Structuring patterns detected (transactions just below $10K)
   - Unusual wire transfer volume (3+ std deviations from baseline)
   - Sanctions screening hits
   - High-risk country transactions exceeding threshold

2. **Capital Adequacy**
   - Net worth ratio falls below 7% (well-capitalized threshold)
   - Quarterly decline > 0.5 percentage points
   - Projected to fall below 6% within 6 months

3. **Regulatory Deadlines**
   - Call Report 5300 due within 5 days
   - Exam response due within 7 days
   - SAR filing deadline approaching (30 days from discovery)

**Medium Severity Alerts:**

1. **Credit Risk**
   - Month-over-month delinquency increase > 10%
   - Product category delinquency > 2x portfolio average
   - Charge-off rate exceeds 1% annualized

2. **Concentration Risk**
   - Product concentration exceeds 25% threshold
   - Member concentration (top 10) exceeds 15%
   - Single industry exposure > 30%

**Low Severity Alerts:**

1. **Operational Issues**
   - Failed login attempts from unusual locations
   - System downtime > 2 hours
   - Batch processing delays

---

### Data Quality Rules

**Validation Rules:**

```sql
-- Ensure delinquency components don't exceed total balance
CONSTRAINT valid_delinquency CHECK (
    (delinquent_30_days + delinquent_60_days + delinquent_90_plus_days) 
    <= outstanding_balance
);

-- Net worth ratio must be reasonable (0-100%)
CONSTRAINT valid_net_worth CHECK (
    net_worth_ratio BETWEEN 0 AND 100
);

-- CAMEL ratings must follow valid scale
CONSTRAINT valid_camel_range CHECK (
    ALL (
        SELECT rating FROM (VALUES 
            (capital_adequacy), (asset_quality), (management),
            (earnings), (liquidity), (sensitivity), (composite_rating)
        ) AS t(rating)
    ) BETWEEN 1 AND 5
);
```

**Referential Integrity:**

- All `credit_union_id` foreign keys must reference valid credit union
- Alert resolution dates cannot precede alert creation dates
- Measurement dates cannot be future dates
- Fraud incident detection date must be >= incident date

---

## API Contracts

### GET /api/metrics

Returns current compliance metrics.

**Response Schema:**
```json
{
    "bsa_aml_compliance": 96.5,
    "net_worth_ratio": 10.2,
    "capital_adequacy": 12.8,
    "delinquency_rate": 0.87,
    "composite_camel": 2
}
```

**Data Types:**
- All numeric fields: `number` (decimal precision 2)
- `composite_camel`: `integer` (1-5)

---

### GET /api/trends

Returns 12-month regulatory trend data.

**Response Schema:**
```json
[
    {
        "month": "Jan 25",
        "bsa_compliance": 95.2,
        "net_worth": 10.1,
        "delinquency": 0.82
    }
]
```

**Array Length:** Exactly 12 items (current month + 11 previous)

---

### GET /api/alerts

Returns active risk alerts.

**Response Schema:**
```json
[
    {
        "severity": "high",
        "category": "BSA/AML",
        "description": "Unusual transaction pattern detected",
        "accounts": 3,
        "date": "2026-01-15"
    }
]
```

**Severity Values:** `"high"`, `"medium"`, `"low"`

**Category Values:** 
- `"BSA/AML"`
- `"Credit Risk"`
- `"Concentration"`
- `"Regulatory"`
- `"Operational"`

---

### GET /api/portfolio

Returns portfolio risk breakdown by product category.

**Response Schema:**
```json
{
    "categories": ["Auto", "Mortgage", "Personal", "Credit Card", "Business"],
    "delinquency": [0.42, 0.68, 1.23, 2.45, 0.91],
    "chargeoff": [0.15, 0.08, 0.52, 1.87, 0.34]
}
```

**Array Requirements:**
- All arrays must have same length
- Values are percentages (0-100)
- Order must be consistent across arrays

---

### GET /api/fraud

Returns fraud detection metrics for 12 months.

**Response Schema:**
```json
[
    {
        "month": "Jan 25",
        "losses": 18500,
        "prevented": 52000
    }
]
```

**Data Types:**
- `losses`: `integer` (dollars)
- `prevented`: `integer` (dollars)

---

### POST /api/chat

AI agent query endpoint.

**Request Schema:**
```json
{
    "message": "What is our current BSA/AML status?"
}
```

**Response Schema:**
```json
{
    "response": "BSA/AML compliance is at 96.5%...",
    "timestamp": "2026-01-17T13:45:00Z"
}
```

---

## Data Refresh Schedule

| Data Type | Refresh Frequency | Source System | Lag Time |
|-----------|------------------|---------------|----------|
| Compliance Metrics | Daily | Core Banking | T+1 |
| Risk Alerts | Real-time | Transaction Monitoring | < 5 min |
| Portfolio Risk | Daily | Loan Servicing | T+1 |
| CAMEL Ratings | On Exam | NCUA Exam Results | Manual entry |
| Fraud Incidents | Real-time | Fraud Detection | < 1 min |
| Regulatory Trends | Monthly | Call Report System | Month-end |

---

## Data Retention Policy

| Table | Retention Period | Archive After | Purge After |
|-------|-----------------|---------------|-------------|
| `compliance_metrics` | 7 years | 2 years | 7 years |
| `risk_alerts` | 5 years | 1 year | 5 years |
| `portfolio_risk` | 7 years | 2 years | 7 years |
| `camel_ratings` | Permanent | 3 years | Never |
| `fraud_incidents` | 7 years | 2 years | 7 years |

**Regulatory Requirements:**
- NCUA requires 7 years for financial records
- SAR documentation: 5 years from filing
- CAMEL ratings: Permanent retention

---

## Glossary

**BSA/AML** - Bank Secrecy Act / Anti-Money Laundering  
**CAMEL** - Capital, Asset Quality, Management, Earnings, Liquidity, Sensitivity  
**SAR** - Suspicious Activity Report  
**CTR** - Currency Transaction Report  
**CDD** - Customer Due Diligence  
**NCUA** - National Credit Union Administration  
**Net Worth Ratio** - Total Equity / Total Assets  
**Delinquency Rate** - Loans 60+ days past due / Total loan portfolio  
**Concentration Risk** - Risk from over-exposure to single category/borrower/geography  

---

## Technical Notes

### Performance Optimization

- Primary indexes on `(credit_union_id, measurement_date)` for time-series queries
- Materialized views for complex calculations (refresh nightly)
- Partitioning by month for high-volume tables (fraud_incidents, risk_alerts)

### Data Consistency

- All timestamps stored in UTC
- Decimal precision: 2 places for percentages, financial amounts
- Date format: ISO 8601 (YYYY-MM-DD)
- All calculations use `NULLIF` to prevent division by zero

---

**Document Version:** 1.0  
**Last Updated:** January 17, 2026  
**Owner:** DataVisuals™  
**Classification:** Internal Technical Documentation