# Data Model: Fraud & AML Ecto Schemas Version: 1.0 | Date: 2026-05-21 --- ## 1. New Schemas Added to infra_repo Six new Ecto schemas extend the existing MySQL database. All are tenant-scoped via tenant_id (integer FK to tenants.id). --- ### 1.1 risk_entity_models Defines which entity targets and windows to track per tenant. Equivalent to Jube's EntityAnalysisModel configuration. id bigint PK auto_increment tenant_id integer NOT NULL (FK tenants.id) name varchar(100) NOT NULL entity_type varchar(50) NOT NULL -- "card", "merchant", "card_merchant", etc. horizon varchar(20) NOT NULL -- "5m", "1h", "1d", "30d", etc. metric varchar(60) NOT NULL -- "count_failed", "sum_eur", "ratio_failed" update_strategy varchar(20) NOT NULL -- "realtime", "batch_daily", "batch_weekly" active boolean DEFAULT true created_at, updated_at Index: (tenant_id, entity_type, active) Unique: (tenant_id, entity_type, horizon, metric) --- ### 1.2 risk_activation_rules Threshold rules that map feature values to decisions. Equivalent to Jube's ActivationRule. DB-backed, ETS-cached at runtime. id bigint PK tenant_id integer NOT NULL name varchar(200) NOT NULL description text rule_type varchar(20) -- "threshold", "list", "composite" entity_type varchar(50) feature_key varchar(100) -- e.g. "card:1h:count_failed" operator varchar(10) -- ">", ">=", "<", "<=", "==", "in" threshold_value float list_values json -- for "in" operator decision varchar(20) -- "decline", "review", "flag" priority integer DEFAULT 100 active boolean DEFAULT true created_at, updated_at Index: (tenant_id, active, priority) Index: (tenant_id, entity_type, feature_key) --- ### 1.3 risk_scores Per-transaction scoring results. Written asynchronously after response is dispatched. This is the primary audit table for fraud decisions. id bigint PK tenant_id integer NOT NULL transaction_ref varchar(100) NOT NULL -- links to transactions.cbs_reference trace_id varchar(64) ml_score float -- 0.0 to 1.0 (null if rules-only mode) decision varchar(20) NOT NULL -- "approve", "review", "decline" fired_rules json -- array of rule names/IDs that fired feature_snapshot json -- feature values at scoring time (for audit/explainability) model_version varchar(50) response_time_ms integer label varchar(20) -- "fraud", "legit", null (filled by label ingestion) labeled_at datetime created_at datetime NOT NULL Index: (tenant_id, transaction_ref) UNIQUE Index: (tenant_id, decision, created_at) Index: (tenant_id, label, labeled_at) Index: (tenant_id, created_at) -- time-range queries for dashboard --- ### 1.4 risk_labels Delayed fraud feedback (TC40, chargebacks, SAFE notifications). Arrives 30–90 days after transaction. Matched to risk_scores by label_ingestion worker. id bigint PK tenant_id integer NOT NULL external_ref varchar(100) NOT NULL -- network-assigned reference transaction_ref varchar(100) -- original transaction reference label_type varchar(30) -- "tc40", "chargeback", "safe", "dispute" label_value varchar(20) -- "fraud", "legit" amount_eur float received_at datetime NOT NULL matched_score_id bigint -- FK risk_scores.id (null until matched) matched_at datetime raw_payload json -- full network notification payload created_at datetime NOT NULL Index: (tenant_id, transaction_ref) Index: (tenant_id, matched_score_id) Index: (tenant_id, received_at) --- ### 1.5 risk_cases Case management for fraud investigators. Linked to risk_scores. Equivalent to Jube's Case Management module. id bigint PK tenant_id integer NOT NULL score_id bigint NOT NULL -- FK risk_scores.id case_ref varchar(50) NOT NULL -- human-readable ref, e.g. "CASE-2026-001234" status varchar(30) -- "open", "under_review", "pending_info", -- "escalated", "closed" priority varchar(20) -- "low", "medium", "high", "critical" assigned_to integer -- FK admin_users.id opened_by integer -- FK admin_users.id (or null = auto-opened) opened_at datetime NOT NULL closed_at datetime resolution varchar(30) -- "confirmed_fraud", "false_positive", -- "inconclusive", "pending" resolution_notes text tags json -- array of string tags created_at, updated_at Index: (tenant_id, status) Index: (tenant_id, assigned_to, status) Index: (tenant_id, score_id) UNIQUE Index: (case_ref) UNIQUE --- ### 1.6 risk_case_events Audit trail of all case state changes and investigator notes. id bigint PK case_id bigint NOT NULL -- FK risk_cases.id event_type varchar(50) -- "opened", "assigned", "status_changed", -- "note_added", "label_received", "closed" from_status varchar(30) to_status varchar(30) user_id integer -- FK admin_users.id note text metadata json -- additional context created_at datetime NOT NULL Index: (case_id, created_at) --- ### 1.7 risk_sanctions_list Sanctions/watchlist data for Levenshtein distance screening. Equivalent to Jube's Sanction loader and cache. id bigint PK tenant_id integer -- NULL = global (all tenants) name varchar(500) NOT NULL name_normalized varchar(500) -- lowercased, whitespace-collapsed (for fast matching) list_type varchar(30) -- "fatf_grey", "fatf_black", "ofac", "eu", "un", "custom" entity_type varchar(30) -- "person", "organization", "country", "vessel" country_code char(2) aliases json -- array of alternate names active boolean DEFAULT true last_updated datetime created_at, updated_at Index: (tenant_id, active, list_type) Index: FULLTEXT (name_normalized) -- for fast similarity candidates --- ### 1.8 risk_model_versions Tracks trained ML model artifacts and deployment history. id bigint PK tenant_id integer NOT NULL name varchar(100) algorithm varchar(50) -- "axon_mlp", "scholar_isolation_forest", -- "scholar_logistic", "ensemble" status varchar(30) -- "training", "trained", "validating", -- "deployed", "retired" feature_set json -- ordered list of feature keys used hyperparameters json training_from datetime training_to datetime metrics json -- {auc_roc, precision, recall, f1, ks_stat} artifact_path varchar(500) -- S3/local path to serialized model deployed_at datetime retired_at datetime created_at, updated_at Index: (tenant_id, status) Index: (tenant_id, deployed_at) --- ## 2. Extended Existing Schemas ### 2.1 transactions (existing, add risk columns) risk_score float -- denormalized for fast dashboard queries risk_decision varchar(20) -- denormalized risk_score_id bigint -- FK risk_scores.id --- ## 3. Entity Relationship Summary tenants (1) ─── (N) risk_entity_models tenants (1) ─── (N) risk_activation_rules tenants (1) ─── (N) risk_scores tenants (1) ─── (N) risk_cases tenants (1) ─── (N) risk_sanctions_list tenants (1) ─── (N) risk_model_versions transactions (1) ── (0..1) risk_scores risk_scores (1) ─── (0..1) risk_cases risk_scores (1) ─── (0..1) risk_labels (matched_score_id) risk_cases (1) ──── (N) risk_case_events admin_users (1) ─── (N) risk_cases (assigned_to / opened_by)