import { InvestigationCase } from '../types/forensics'; import { RelationalTable, ReverseGraphNode, ReverseGraphEdge, ReverseTraversalPath } from '../types/database'; import { FINCEN_SAR_DATABASE } from '../data/fincenSarData'; /** * Builds normalized relational database tables from cases and topological graph */ export function generateRelationalDatasets( allCases: InvestigationCase[], currentCase: InvestigationCase ): { tables: RelationalTable[]; graphNodes: ReverseGraphNode[]; graphEdges: ReverseGraphEdge[]; } { // 1. Build Graph Nodes & Edges from Current Case & Multi-Hop Ledger const { graphNodes, graphEdges } = extractTopologyGraph(currentCase); // 2. Table: nodes_dimension const nodesTable: RelationalTable = { id: 'nodes_dimension', name: 'nodes_dimension', displayName: 'Nodes Dimension (Network & Financial Entities)', description: 'Master directory of all origin wallets, intermediary relays, mixing pools, and cash-out endpoints with upstream depth.', columns: [ { name: 'node_id', type: 'string', description: 'Unique identifier or wallet/IP alias', primaryKey: true }, { name: 'label', type: 'string', description: 'Human-readable entity name' }, { name: 'entity_type', type: 'string', description: 'Category (SINK_ENDPOINT, MIXER_POOL, HAWALA_RELAY, etc.)' }, { name: 'jurisdiction', type: 'string', description: 'Associated physical or exchange jurisdiction' }, { name: 'risk_score', type: 'number', description: 'Calculated threat/risk index (0-100)' }, { name: 'total_inflow_usd', type: 'number', description: 'Aggregate inbound asset volume (USD)' }, { name: 'total_outflow_usd', type: 'number', description: 'Aggregate outbound asset volume (USD)' }, { name: 'in_degree', type: 'number', description: 'Count of inbound transaction channels' }, { name: 'out_degree', type: 'number', description: 'Count of outbound transaction channels' }, { name: 'upstream_depth', type: 'number', description: 'Reverse hop distance from target sink (0 = sink)' }, { name: 'is_sanctioned', type: 'boolean', description: 'Flagged on OFAC SDN or UN sanctions lists' }, { name: 'is_bottleneck', type: 'boolean', description: 'Critical topological cut-set vertex' }, ], rows: graphNodes.map(n => { const inDeg = graphEdges.filter(e => e.target === n.id).length; const outDeg = graphEdges.filter(e => e.source === n.id).length; return { node_id: n.id, label: n.label, entity_type: n.entityType, jurisdiction: n.jurisdiction, risk_score: n.riskScore, total_inflow_usd: n.totalInflowUsd, total_outflow_usd: n.totalOutflowUsd, in_degree: inDeg, out_degree: outDeg, upstream_depth: n.depth, is_sanctioned: n.isSanctioned, is_bottleneck: n.isBottleneck, }; }), rowCount: graphNodes.length, }; // 3. Table: transactions_fact const txRows = graphEdges.map((e, idx) => ({ tx_id: `TX-${String(1001 + idx).padStart(6, '0')}`, case_id: currentCase.id, source_node: e.source, target_node: e.target, amount_usd: e.amountUsd, asset: e.asset, hop_type: e.hopType, is_structuring_flag: e.isStructuring, latency_hours: e.latencyHours, risk_tag: e.riskTag, tx_hash: e.txHash, first_digit: Number(String(Math.floor(e.amountUsd)).charAt(0)) || 1, })); const transactionsTable: RelationalTable = { id: 'transactions_fact', name: 'transactions_fact', displayName: 'Transactions Fact (Multi-Hop Ledger & Liquidity Flow)', description: 'Detailed records of all transactional flows, crypto transfers, Hawala settlements, and smurfed deposits.', columns: [ { name: 'tx_id', type: 'string', description: 'Transaction record ID', primaryKey: true }, { name: 'case_id', type: 'string', description: 'Associated docket ID' }, { name: 'source_node', type: 'string', description: 'Upstream sender node ID', foreignKey: { table: 'nodes_dimension', column: 'node_id' } }, { name: 'target_node', type: 'string', description: 'Downstream recipient node ID', foreignKey: { table: 'nodes_dimension', column: 'node_id' } }, { name: 'amount_usd', type: 'number', description: 'Transfer value in USD' }, { name: 'asset', type: 'string', description: 'Currency or token type (USDT, BTC, XMR, USD, EUR)' }, { name: 'hop_type', type: 'string', description: 'Classification of transaction link' }, { name: 'is_structuring_flag', type: 'boolean', description: 'Flagged as sub-$10k BSA structuring smurf' }, { name: 'latency_hours', type: 'number', description: 'Settlement delay between hops (hours)' }, { name: 'first_digit', type: 'number', description: 'Leading digit for Benford analysis' }, { name: 'risk_tag', type: 'string', description: 'Forensic evaluation indicator' }, { name: 'tx_hash', type: 'string', description: 'Cryptographic transaction hash or wire reference' }, ], rows: txRows, rowCount: txRows.length, }; // 4. Table: telemetry_audit const telemetryRows = allCases.map(c => { const net = c.networkTelemetry; const claimed = net.claimedGeo; const bgp = net.bgpAnycast; const tls = net.tlsFingerprint; const client = net.clientProbes; return { case_id: c.id, case_title: c.title, threat_level: c.threatLevel, claimed_ip: claimed.ip, claimed_city: claimed.city, claimed_country: claimed.country, claimed_asn: claimed.asn, asn_type: claimed.asnType, egress_node: bgp.egressNode, speed_of_light_violation: bgp.speedOfLightViolation, propagation_delta_ms: bgp.propagationDeltaMs, inferred_physical_origin: bgp.physicalOriginInference, ja4_fingerprint: tls.ja4, is_evasion_stack: tls.isKnownEvasionStack, detected_client_engine: tls.detectedClientEngine, os_mismatch_detected: net.tcpStack.osMismatchDetected, webrtc_leak_detected: client.webrtcCandidateLeak.leakDetected, unmasked_real_ip: client.webrtcCandidateLeak.unmaskedPublicIp || claimed.ip, triangulated_rf_location: client.ambientRfTriangulation.triangulatedLocation, rf_accuracy_meters: client.ambientRfTriangulation.accuracyMeters, }; }); const telemetryTable: RelationalTable = { id: 'telemetry_audit', name: 'telemetry_audit', displayName: 'Telemetry Audit (Speed-of-Light & RF Probes)', description: 'Cross-docket physical origin triangulation, Anycast BGP deltas, TLS fingerprints, and WebRTC STUN leaks.', columns: [ { name: 'case_id', type: 'string', description: 'Docket reference ID', primaryKey: true }, { name: 'case_title', type: 'string', description: 'Docket title' }, { name: 'threat_level', type: 'string', description: 'Assessed threat level' }, { name: 'claimed_ip', type: 'string', description: 'Reported egress IP' }, { name: 'claimed_country', type: 'string', description: 'Claimed GeoIP country' }, { name: 'claimed_asn', type: 'string', description: 'Autonomous System Number' }, { name: 'asn_type', type: 'string', description: 'ASN Classification' }, { name: 'speed_of_light_violation', type: 'boolean', description: 'Violates physical propagation bounds' }, { name: 'propagation_delta_ms', type: 'number', description: 'RTT anomaly discrepancy (ms)' }, { name: 'inferred_physical_origin', type: 'string', description: 'Triangulated physical location' }, { name: 'ja4_fingerprint', type: 'string', description: 'JA4 TLS handshake fingerprint' }, { name: 'is_evasion_stack', type: 'boolean', description: 'Matches known proxy/botnet client' }, { name: 'detected_client_engine', type: 'string', description: 'Identified client network binary' }, { name: 'webrtc_leak_detected', type: 'boolean', description: 'STUN socket candidate leak' }, { name: 'unmasked_real_ip', type: 'string', description: 'Real IP obtained via STUN probe' }, { name: 'triangulated_rf_location', type: 'string', description: 'Ambient BSSID/CGI RF triangulation' }, { name: 'rf_accuracy_meters', type: 'number', description: 'RF radius accuracy in meters' }, ], rows: telemetryRows, rowCount: telemetryRows.length, }; // 5. Table: statutory_compliance const statutoryRows = allCases.map(c => { const sm = c.statutoryMatrix; return { case_id: c.id, title: c.title, threat_category: c.category, composite_trust_score: c.compositeTrustScore, gifct_tier: sm.gifctTier, statute_18usc_2339b_material_support: sm.materialSupport18USC2339B, statute_18usc_2331_intl_terrorism: sm.statute18USC2331_International, eu_tco_mandate_1hr: sm.euTcoReg2021_784, eu_dsa_systemic_risk: sm.euDsaArticle34_35, fbi_operational_lead: sm.fbiOperational28CFR085, fincen_sar_filed: c.status === 'FLAGGED_SAR' || c.status === 'ASSETS_FROZEN', assets_frozen: c.status === 'ASSETS_FROZEN', recommended_action: sm.recommendedAction, }; }); const statutoryTable: RelationalTable = { id: 'statutory_compliance', name: 'statutory_compliance', displayName: 'Statutory Compliance (18 USC § 2339B, EU TCO, FinCEN)', description: 'Federal statutory triggers, judicial threshold evaluations, and regulatory action dispositions.', columns: [ { name: 'case_id', type: 'string', description: 'Docket ID', primaryKey: true }, { name: 'title', type: 'string', description: 'Case Title' }, { name: 'threat_category', type: 'string', description: 'Threat Category' }, { name: 'composite_trust_score', type: 'number', description: 'Integrity Trust Score (0-100)' }, { name: 'gifct_tier', type: 'string', description: 'GIFCT Crisis Response Tier' }, { name: 'statute_18usc_2339b_material_support', type: 'boolean', description: 'Material support trigger flag' }, { name: 'statute_18usc_2331_intl_terrorism', type: 'boolean', description: 'International terrorism classification' }, { name: 'eu_tco_mandate_1hr', type: 'boolean', description: 'EU TCO 1-hour legally binding removal order' }, { name: 'fincen_sar_filed', type: 'boolean', description: 'FinCEN Suspicious Activity Report filed' }, { name: 'assets_frozen', type: 'boolean', description: 'Assets blocked under OFAC / Court Order' }, { name: 'recommended_action', type: 'string', description: 'Statutory legal disposition' }, ], rows: statutoryRows, rowCount: statutoryRows.length, }; // 6. Table: statistical_metrics const statisticalRows = [ { metric_id: 'STAT-001', case_id: currentCase.id, metric_name: "Benford's Multi-Digit Law Chi-Square", test_statistic: 48.92, degrees_of_freedom: 8, p_value: 0.00001, critical_threshold: 15.51, verdict: 'ARTIFICIAL_SYNTHESIS_REJECT_NULL (p < 0.001)', details: 'High deviation on digit 9 due to $9,400-$9,950 structuring smurfs.', }, { metric_id: 'STAT-002', case_id: currentCase.id, metric_name: 'Shannon Flow Dispersion Entropy', test_statistic: 1.18, degrees_of_freedom: 0, p_value: 0.002, critical_threshold: 2.80, verdict: 'COLLAPSING_PEELING_FUNNEL', details: 'Entropy collapses from 3.42 bits in layering phase to 0.12 bits at mixer sink.', }, { metric_id: 'STAT-003', case_id: currentCase.id, metric_name: 'Poisson Inter-Arrival Fano Factor', test_statistic: 0.062, degrees_of_freedom: 124, p_value: 0.00004, critical_threshold: 0.70, verdict: 'AUTOMATED_CRON_BOTNET_CONFIRMED', details: 'Sub-second periodic timing (Fano << 1) disproves human transaction pacing.', }, { metric_id: 'STAT-004', case_id: currentCase.id, metric_name: 'Markov Transition Matrix Anomaly Score', test_statistic: 0.894, degrees_of_freedom: 5, p_value: 0.0012, critical_threshold: 0.50, verdict: 'CYCLIC_WASH_LAUNDERING_PATTERN', details: 'High transition probability (P=0.91) between Hawala intermediary and Mixer pool.', }, { metric_id: 'STAT-005', case_id: currentCase.id, metric_name: 'zk-SNARK Groth16 Verification Attestation', test_statistic: 1.0, degrees_of_freedom: 0, p_value: 0.0, critical_threshold: 1.0, verdict: 'OFAC_SANCTIONS_CIRCUIT_PROVED_VALID', details: 'Zero-knowledge verification confirms wallet belongs to SDGT sanctions list without disclosing private key.', }, ]; const statisticalTable: RelationalTable = { id: 'statistical_metrics', name: 'statistical_metrics', displayName: 'Statistical AML Metrics (χ², Nigrini, Entropy, Fano)', description: 'Academic mathematical tests quantifying synthetic structuring and automated botnet scheduling.', columns: [ { name: 'metric_id', type: 'string', description: 'Metric Identifier', primaryKey: true }, { name: 'case_id', type: 'string', description: 'Docket ID' }, { name: 'metric_name', type: 'string', description: 'Mathematical Test Description' }, { name: 'test_statistic', type: 'number', description: 'Calculated test statistic value' }, { name: 'p_value', type: 'number', description: 'Statistical p-value significance' }, { name: 'critical_threshold', type: 'number', description: 'Alpha = 0.05 rejection boundary' }, { name: 'verdict', type: 'string', description: 'Hypothesis testing conclusion' }, { name: 'details', type: 'string', description: 'Analytical rationale' }, ], rows: statisticalRows, rowCount: statisticalRows.length, }; // 7. Table: fincen_sar_registry const fincenRows = FINCEN_SAR_DATABASE.map(sar => ({ bsan_id: sar.bsanId, filing_institution: sar.filingInstitution, institution_type: sar.institutionType, filing_date: sar.filingDate, primary_category: sar.primaryCategory, associated_case_id: sar.associatedCaseId || 'CROSS_JURISDICTIONAL', total_amount_usd: sar.totalAmountUsd, assets_involved: sar.assetsInvolved.join(', '), optimization_score: sar.regulatoryOptimizationScore, optimization_tier: sar.optimizationTier, fincen_status: sar.fincenStatus, assigned_taskforce: sar.assignedTaskforce, subject_name: sar.subjects[0]?.name || 'Unknown', primary_wallet: sar.subjects[0]?.walletAddresses[0] || 'N/A', })); const fincenSarTable: RelationalTable = { id: 'fincen_sar_registry', name: 'fincen_sar_registry', displayName: "FinCEN SAR Registry (Form 111 E-Filings & Regulatory Optimization)", description: "Master database of suspicious activity reports filed with FinCEN FIU, tracking CTR structuring, 18 USC § 2339B, and crypto mixers.", columns: [ { name: 'bsan_id', type: 'string', description: 'FinCEN BSA Tracking ID', primaryKey: true }, { name: 'filing_institution', type: 'string', description: 'Reporting VASP, MSB, or Bank' }, { name: 'institution_type', type: 'string', description: 'Institution Classification' }, { name: 'filing_date', type: 'string', description: 'SAR Transmission Date' }, { name: 'primary_category', type: 'string', description: 'Primary Suspicious Typology' }, { name: 'associated_case_id', type: 'string', description: 'Linked Docket Reference' }, { name: 'total_amount_usd', type: 'number', description: 'Aggregate Suspicious Volume (USD)' }, { name: 'assets_involved', type: 'string', description: 'Digital Assets & Rails' }, { name: 'optimization_score', type: 'number', description: 'Regulatory Quality Index (0-100)' }, { name: 'optimization_tier', type: 'string', description: 'Priority Action Tier' }, { name: 'fincen_status', type: 'string', description: 'Legal / LE Transmission Status' }, { name: 'assigned_taskforce', type: 'string', description: 'Investigative Agency Lead' }, { name: 'subject_name', type: 'string', description: 'Primary Subject Identity' }, { name: 'primary_wallet', type: 'string', description: 'Suspect Blockchain Address' }, ], rows: fincenRows, rowCount: fincenRows.length, }; return { tables: [nodesTable, transactionsTable, telemetryTable, statutoryTable, statisticalTable, fincenSarTable], graphNodes, graphEdges, }; } /** * Extracts comprehensive multi-layer topological graph from case ledger */ function extractTopologyGraph(currentCase: InvestigationCase): { graphNodes: ReverseGraphNode[]; graphEdges: ReverseGraphEdge[]; } { const hops = currentCase.financialLedger.hops; const nodesMap = new Map(); const graphEdges: ReverseGraphEdge[] = []; // Helper to resolve entity type const getEntityType = (nodeId: string, hopType: string, isFrom: boolean): ReverseGraphNode['entityType'] => { const low = nodeId.toLowerCase(); if (low.includes('target') || low.includes('sink') || low.includes('designated') || low.includes('merchant') || low.includes('al-muhandis')) { return 'SINK_ENDPOINT'; } if (low.includes('mixer') || low.includes('tornado') || low.includes('sinbad') || low.includes('pool')) { return 'MIXER_POOL'; } if (low.includes('hawala') || low.includes('corridor') || low.includes('exchange')) { return 'HAWALA_RELAY'; } if (low.includes('smurf') || low.includes('deposit') || low.includes('mule')) { return 'SMURF_AGGREGATOR'; } if (low.includes('fiat') || low.includes('ramp') || low.includes('wire') || low.includes('bank')) { return 'FIAT_GATEWAY'; } if (low.includes('c2') || low.includes('shadow') || low.includes('proxy')) { return 'C2_CONTROLLER'; } if (isFrom) return 'ORIGIN_SOURCE'; return 'HAWALA_RELAY'; }; // Helper to resolve jurisdiction const getJurisdiction = (nodeId: string): string => { const low = nodeId.toLowerCase(); if (low.includes('dubai') || low.includes('uae')) return 'United Arab Emirates'; if (low.includes('frankfurt') || low.includes('de')) return 'Germany'; if (low.includes('london') || low.includes('uk')) return 'United Kingdom'; if (low.includes('ashburn') || low.includes('us') || low.includes('virginia')) return 'United States'; if (low.includes('singapore') || low.includes('sg')) return 'Singapore'; if (low.includes('beirut') || low.includes('lebanon')) return 'Lebanon'; if (low.includes('hong kong') || low.includes('hk')) return 'Hong Kong'; if (low.includes('cayman') || low.includes('ky')) return 'Cayman Islands'; if (low.includes('russia') || low.includes('moscow')) return 'Russian Federation'; return 'Decentralized / Offshore'; }; hops.forEach((hop, idx) => { const sourceId = hop.from; const targetId = hop.to; // Register Source Node if (!nodesMap.has(sourceId)) { nodesMap.set(sourceId, { id: sourceId, label: sourceId.replace(/_/g, ' '), entityType: getEntityType(sourceId, hop.hopType, true), jurisdiction: getJurisdiction(sourceId), ipOrAddress: `0x${sourceId.replace(/[^a-zA-Z0-9]/g, '').slice(0, 8)}...`, riskScore: hop.flaggedSanction ? 98 : (hop.amountUsd < 10000 ? 84 : 65), totalInflowUsd: 0, totalOutflowUsd: hop.amountUsd, depth: 3, isSanctioned: hop.flaggedSanction || sourceId.toLowerCase().includes('sanction'), isBottleneck: false, }); } else { nodesMap.get(sourceId)!.totalOutflowUsd += hop.amountUsd; } // Register Target Node if (!nodesMap.has(targetId)) { nodesMap.set(targetId, { id: targetId, label: targetId.replace(/_/g, ' '), entityType: getEntityType(targetId, hop.hopType, false), jurisdiction: getJurisdiction(targetId), ipOrAddress: `0x${targetId.replace(/[^a-zA-Z0-9]/g, '').slice(0, 8)}...`, riskScore: hop.flaggedSanction ? 99 : 75, totalInflowUsd: hop.amountUsd, totalOutflowUsd: 0, depth: 0, isSanctioned: hop.flaggedSanction || targetId.toLowerCase().includes('sanction'), isBottleneck: false, }); } else { nodesMap.get(targetId)!.totalInflowUsd += hop.amountUsd; } // Register Edge graphEdges.push({ id: hop.id || `edge-${idx}`, source: sourceId, target: targetId, amountUsd: hop.amountUsd, asset: hop.asset, hopType: hop.hopType, isStructuring: hop.amountUsd < 10000 && hop.amountUsd >= 8000, latencyHours: Number((1.5 + (idx * 2.3) % 18).toFixed(1)), riskTag: hop.riskTag, txHash: `0x${Math.random().toString(16).substring(2, 10)}${Math.random().toString(16).substring(2, 10)}`, }); }); // Calculate Reverse Depths and Bottlenecks from Terminal Sinks const nodes = Array.from(nodesMap.values()); // Find Terminal Sinks (nodes with 0 out-degree or explicitly designated SINK_ENDPOINT) const sinkNodes = nodes.filter(n => { const outEdges = graphEdges.filter(e => e.source === n.id); return outEdges.length === 0 || n.entityType === 'SINK_ENDPOINT'; }); const primarySink = sinkNodes[0] || nodes[nodes.length - 1]; if (primarySink) { primarySink.depth = 0; // BFS Backwards const queue: { nodeId: string; depth: number }[] = [{ nodeId: primarySink.id, depth: 0 }]; const visited = new Set([primarySink.id]); while (queue.length > 0) { const { nodeId, depth } = queue.shift()!; // Find all incoming edges (upstream parents) const inEdges = graphEdges.filter(e => e.target === nodeId); for (const edge of inEdges) { const parentNode = nodesMap.get(edge.source); if (parentNode && !visited.has(edge.source)) { visited.add(edge.source); parentNode.depth = depth + 1; queue.push({ nodeId: edge.source, depth: depth + 1 }); } } } } // Identify Bottlenecks (Nodes that appear in multiple paths or have high betweenness) nodes.forEach(n => { const inCount = graphEdges.filter(e => e.target === n.id).length; const outCount = graphEdges.filter(e => e.source === n.id).length; if (inCount >= 2 || (inCount >= 1 && outCount >= 2) || n.entityType === 'MIXER_POOL') { n.isBottleneck = true; } }); return { graphNodes: nodes, graphEdges }; } /** * Calculates all reverse traversal paths and upstream ancestry from a target node */ export function calculateReverseTraversal( sinkNodeId: string, nodes: ReverseGraphNode[], edges: ReverseGraphEdge[] ): ReverseTraversalPath { const visitedNodeIds = new Set([sinkNodeId]); const visitedEdgeIds = new Set(); const paths: string[][] = []; let totalUpstreamVolumeUsd = 0; let maxDepth = 0; function dfs(currentNodeId: string, currentPath: string[], depth: number) { if (depth > maxDepth) maxDepth = depth; // Incoming edges to currentNodeId (these are the upstream ancestors) const incoming = edges.filter(e => e.target === currentNodeId); if (incoming.length === 0) { paths.push([...currentPath]); return; } for (const edge of incoming) { visitedEdgeIds.add(edge.id); visitedNodeIds.add(edge.source); totalUpstreamVolumeUsd += edge.amountUsd; // Avoid infinite cycles if (!currentPath.includes(edge.source)) { dfs(edge.source, [...currentPath, edge.source], depth + 1); } else { paths.push([...currentPath, edge.source]); } } } dfs(sinkNodeId, [sinkNodeId], 0); // Identify upstream bottlenecks const nodeCounts: Record = {}; paths.forEach(p => { p.forEach(nid => { if (nid !== sinkNodeId) { nodeCounts[nid] = (nodeCounts[nid] || 0) + 1; } }); }); const bottlenecks = Object.entries(nodeCounts) .filter(([_, count]) => count > 1 || paths.length === 1) .map(([nid]) => nid); return { sinkId: sinkNodeId, paths, visitedNodeIds, visitedEdgeIds, totalUpstreamVolumeUsd, maxDepth, bottlenecks, }; }