HSBC QuantumLens：AI 驱动的全球银行智能与风险观测平台

AI-Powered Global Banking Intelligence & Risk Observatory

HSBC QuantumLens is an institutional-grade financial data platform that resembles an internal strategic war room used by COO, CRO, or treasury teams. By analyzing Q1 results (including Net Interest Income volatility, Expected Credit Losses provisioning, capital CET1 compression, and HNWI wealth inflows into Asia), QuantumLens shifts traditional financial reporting from static data visualizers into an AI-native banking intelligence operating system.

The platform is designed like a Bloomberg Terminal × Palantir × McKinsey War Room, integrating data ingestion pipelines, financial knowledge graphs, RAG call intelligence, scenario forecasters, executive war-room dashboards, and multi-agent AI copilots.

6-Layer Architecture Overview

graph TD
    %% Define Nodes
    subgraph ClientLayer ["Layer 5: Apache Superset War Rooms (frontend/)"]
        UI["Next.js Dashboards UI<br>(Pulse, Wealth, Stress, Contagion)"]
    end

    subgraph APILayer ["Layer 6: AI Copilot Router (backend/src/api/)"]
        FastAPI["FastAPI Web Router"]
    end

    subgraph ForecastingLayer ["Layer 4: Advanced Forecasting Engine (ml_models/)"]
        Forecast["Forecaster<br>(Prophet / XGBoost / LSTMs)"]
    end

    subgraph AILayer ["Layer 3: AI Call Intelligence (backend/src/rag/)"]
        Chroma[("ChromaDB Vector Store")]
        FinBERT["FinBERT NLP Pipeline<br>(Anxiety / Sentiment index)"]
        Groq["Groq Cloud API<br>(llama-3.3-70b-versatile)"]
    end

    subgraph KnowledgeGraph ["Layer 2: Financial Knowledge Graph (neo4j/)"]
        Neo4j[("Neo4j Graph Database<br>(Risk Contagion Nodes)")]
    end

    subgraph DataEngineering ["Layer 1: Data Engineering Pipeline (backend/src/ingestion/)"]
        Reader["Workbook Reader"]
        Scanner["Sheet Scanner"]
        Extractor["Metric Extractor"]
        Mapper["Period Mapper"]
        Builder["KPI Builder"]
    end

    subgraph StorageLayer ["Database Warehouse Layer (backend/warehouse/)"]
        Supabase[("Supabase (PostgreSQL 15)<br>[JSONB arrays]")]
        Oracle[("Oracle Database 19c<br>[Period observations]")]
    end

    %% Define Connections
    UI <-->|HTTP REST JSON| FastAPI
    FastAPI <-->|SQL Queries| Supabase
    FastAPI <-->|Retrieve Context| Chroma
    
    %% Ingestion Flow
    xlsx["Raw Excel Files (.xlsx)"] --> Reader
    Reader --> Scanner
    Scanner --> Extractor
    Extractor --> Mapper
    Mapper --> Builder
    Builder -->|Batch Upserts| Supabase
    Builder -->|Insert Rows| Oracle
    Builder -->|Stress nodes| Neo4j
    
    %% Vector Ingestion Flow
    Supabase -->|SQL Extract| FinBERT
    FinBERT -->|Dense Embeddings| Chroma
    
    %% RAG Pipeline Flow
    FastAPI -->|Question Query| Chroma
    Chroma -->|Relevant Context Docs| FastAPI
    FastAPI -->|Context + Prompt| Groq
    Groq -->|Context-Grounded Answer| FastAPI
    
    %% Forecasting Connection
    Oracle --> Forecast
    Forecast --> UI
    
    %% Styles
    classDef client fill:#1f77b4,stroke:#333,stroke-width:2px,color:#fff;
    classDef api fill:#2ca02c,stroke:#333,stroke-width:2px,color:#fff;
    classDef storage fill:#9467bd,stroke:#333,stroke-width:2px,color:#fff;
    classDef etl fill:#ff7f0e,stroke:#333,stroke-width:2px,color:#fff;
    classDef ai fill:#d62728,stroke:#333,stroke-width:2px,color:#fff;
    classDef forecast fill:#e377c2,stroke:#333,stroke-width:2px,color:#fff;
    classDef kg fill:#bcbd22,stroke:#333,stroke-width:2px,color:#fff;
    
    class UI client;
    class FastAPI api;
    class Reader,Scanner,Extractor,Mapper,Builder etl;
    class Supabase,Oracle storage;
    class Chroma,FinBERT,Groq ai;
    class Forecast forecast;
    class Neo4j kg;

Loading

Layer Matrix

Technical Stack Summary

Live Deployments

Local Ingestion and API Setup

Setup Steps Tabular Guide

Environment Variables Configuration

Warning

Ensure all API keys are kept secure and never committed to public repositories.

Executive War Rooms (Next.js Portal)

The dashboard frontend (frontend/) structures metrics into five interactive screens:

Multi-Agent AI Financial Copilot

Queries targeting the backend REST API /ask route are processed by a custom Multi-Agent Orchestrator:

graph TD
    User([User Request]) --> Planner{Planner Agent}
    Planner -->|Intent: SQL| SQLAgent[SQL Agent]
    Planner -->|Intent: RAG| RAGAgent[RAG Agent]
    SQLAgent --> Oracle[(Oracle Database)]
    RAGAgent --> Chroma[(ChromaDB Vector Store)]
    Oracle --> Synthesizer[Response Synthesizer]
    Chroma --> Synthesizer
    Synthesizer --> Groq[Groq Llama-3.3]
    Groq --> FinalResponse([Context-Grounded Board Report])

Loading

Contributing

We welcome contributions to improve HSBC QuantumLens. Open a pull request or file issues under the repository tracker.

License

This project is open-source software licensed under the MIT License.

Complete Documentation Reference

Expand the sections below to view the full contents of all other documentation files in this repository.

System Architecture Specification (docs/architecture/architecture.md)

System Architecture Specification

This document details the architectural layout, system layers, and component interactions of QuantumLens (also known as HSBC Atlas or Project Basilisk), an institutional-grade banking intelligence platform.

For a high-level overview, deployment metrics, or setup instructions, see the primary README.md.

6-Layer Architecture Overview

QuantumLens is built on six decoupled engineering layers. This decoupling ensures that vector-indexed LLM reasoning and graph contagion modeling run independently of core transactional databases and ETL workflows.

graph TD
    %% Define Nodes
    subgraph ClientLayer ["Layer 5: Apache Superset War Rooms (frontend/)"]
        UI["Next.js Dashboards UI<br>(Pulse, Wealth, Stress, Contagion)"]
    end

    subgraph APILayer ["Layer 6: AI Copilot Router (backend/src/api/)"]
        FastAPI["FastAPI Web Router"]
    end

    subgraph ForecastingLayer ["Layer 4: Advanced Forecasting Engine (ml_models/)"]
        Forecast["Forecaster<br>(Prophet / XGBoost / LSTMs)"]
    end

    subgraph AILayer ["Layer 3: AI Call Intelligence (backend/src/rag/)"]
        Chroma[("ChromaDB Vector Store")]
        FinBERT["FinBERT NLP Pipeline<br>(Anxiety / Sentiment index)"]
        Groq["Groq Cloud API<br>(llama-3.3-70b-versatile)"]
    end

    subgraph KnowledgeGraph ["Layer 2: Financial Knowledge Graph (neo4j/)"]
        Neo4j[("Neo4j Graph Database<br>(Risk Contagion Nodes)")]
    end

    subgraph DataEngineering ["Layer 1: Data Engineering Pipeline (backend/src/ingestion/)"]
        Reader["Workbook Reader"]
        Scanner["Sheet Scanner"]
        Extractor["Metric Extractor"]
        Mapper["Period Mapper"]
        Builder["KPI Builder"]
    end

    subgraph StorageLayer ["Database Warehouse Layer (backend/warehouse/)"]
        Supabase[("Supabase (PostgreSQL 15)<br>[JSONB arrays]")]
        Oracle[("Oracle Database 19c<br>[Period observations]")]
    end

    %% Define Connections
    UI <-->|HTTP REST JSON| FastAPI
    FastAPI <-->|SQL Queries| Supabase
    FastAPI <-->|Retrieve Context| Chroma
    
    %% Ingestion Flow
    xlsx["Raw Excel Files (.xlsx)"] --> Reader
    Reader --> Scanner
    Scanner --> Extractor
    Extractor --> Mapper
    Mapper --> Builder
    Builder -->|Batch Upserts| Supabase
    Builder -->|Insert Rows| Oracle
    Builder -->|Stress nodes| Neo4j
    
    %% Vector Ingestion Flow
    Supabase -->|SQL Extract| FinBERT
    FinBERT -->|Dense Embeddings| Chroma
    
    %% RAG Pipeline Flow
    FastAPI -->|Question Query| Chroma
    Chroma -->|Relevant Context Docs| FastAPI
    FastAPI -->|Context + Prompt| Groq
    Groq -->|Context-Grounded Answer| FastAPI
    
    %% Forecasting Connection
    Oracle --> Forecast
    Forecast --> UI
    
    %% Styles
    classDef client fill:#1f77b4,stroke:#333,stroke-width:2px,color:#fff;
    classDef api fill:#2ca02c,stroke:#333,stroke-width:2px,color:#fff;
    classDef storage fill:#9467bd,stroke:#333,stroke-width:2px,color:#fff;
    classDef etl fill:#ff7f0e,stroke:#333,stroke-width:2px,color:#fff;
    classDef ai fill:#d62728,stroke:#333,stroke-width:2px,color:#fff;
    classDef forecast fill:#e377c2,stroke:#333,stroke-width:2px,color:#fff;
    classDef kg fill:#bcbd22,stroke:#333,stroke-width:2px,color:#fff;
    
    class UI client;
    class FastAPI api;
    class Reader,Scanner,Extractor,Mapper,Builder etl;
    class Supabase,Oracle storage;
    class Chroma,FinBERT,Groq ai;
    class Forecast forecast;
    class Neo4j kg;

Loading

Technical Layers Matrix

Modular System Breakdown

The system modules are partitioned as follows:

Detailed Data Flows & Sequence Diagrams

1. Ingestion and ETL Pipeline Data Flow

The following sequence illustrates the stages of processing raw spreadsheets into database tables:

sequenceDiagram
    autonumber
    participant Excel as Raw Spreadsheet (.xlsx)
    participant Reader as Ingestion Layer (workbook_reader.py)
    participant Scanner as Ingestion Layer (sheet_scanner.py)
    participant Extractor as Transformation (metric_extractor.py)
    participant Builder as Transformation (kpi_builder.py)
    participant DB as Warehouse (Supabase/Postgres)
    participant Oracle as Warehouse (Oracle DB)
    participant Neo4j as Graph Database (Neo4j)

    Excel->>Reader: File Path of workbook
    activate Reader
    Reader->>Scanner: Pandas ExcelFile representation
    deactivate Reader
    activate Scanner
    Scanner->>Extractor: JSON Row Coordinates with raw strings
    deactivate Scanner
    activate Extractor
    Extractor->>Builder: Matched Raw Metrics with config mappings
    deactivate Extractor
    activate Builder
    Builder->>DB: Upsert Ingested KPI Payload (JSONB array structure)
    Builder->>Oracle: Insert sequential rows (single period observations)
    Builder->>Neo4j: Create exposure nodes and dependencies
    deactivate Builder

Loading

2. Multi-Agent RAG Query Sequence

This sequence diagram illustrates the steps when a user queries the API for financial analytics:

sequenceDiagram
    autonumber
    actor User as Client App (API/Web)
    participant API as FastAPI REST Router (main.py)
    participant Planner as Planner Agent (planner.py)
    participant SQLAgent as SQL Agent (sql_agent.py)
    participant RAGAgent as RAG Agent (rag_agent.py)
    participant Oracle as Oracle Database
    participant VectorDB as Vector Store (ChromaDB Local)
    participant LLM as Inference Engine (Groq Cloud)

    User->>API: POST /ask { "question": "..." }
    activate API
    API->>Planner: parse_query(question)
    activate Planner
    Planner-->>API: Intent Matrix: {use_sql: true, use_rag: true}
    deactivate Planner
    
    API->>SQLAgent: get_metrics_sql(question)
    activate SQLAgent
    SQLAgent->>Oracle: Query historical values
    Oracle-->>SQLAgent: SQL data rows
    SQLAgent-->>API: Structured metrics values
    deactivate SQLAgent

    API->>RAGAgent: search_context(question)
    activate RAGAgent
    RAGAgent->>VectorDB: search_embeddings(question)
    VectorDB-->>RAGAgent: Retrived text documents
    RAGAgent-->>API: Context documents strings
    deactivate RAGAgent

    API->>LLM: Complete Response (System Prompt + SQL Data + Context Docs + Question)
    activate LLM
    LLM-->>API: Grounded financial response text
    deactivate LLM

    API-->>User: HTTP 200 OK Response (answer, sources)
    deactivate API

Loading

Related Documentation

• Primary Readme: Project overview, installation scripts, API reference.
• Database Schema (Data Dictionary): Detailed columns description, indices, and constraints.
• KPI Catalog Mapping: Synonym dictionaries and lookup rules.

Database Design & Data Dictionary (docs/features/ingestion/data_dictionary.md)

Database Design & Data Dictionary

This document details the relational data warehouse design, table schemas, indices, and database constraints of QuantumLens.

For deployment steps or API integration hooks, see the primary README.md. For system architecture diagrams, see architecture.md.

Database Design Rationale

QuantumLens supports two target database engines to cater to both cloud-native applications and corporate environments:

Side-by-Side Schema Comparison

The following table contrasts the two storage layouts for the same metric:

Detailed Data Dictionaries

1. Supabase (PostgreSQL 15) Schema Details

The metrics table stores normalized KPI records parsed by the ETL loader.

Columns Dictionary

SQL Schema Definition

CREATE TABLE metrics (
    id SERIAL PRIMARY KEY,
    metric_id INTEGER UNIQUE NOT NULL,
    metric_name TEXT NOT NULL,
    abbreviation TEXT,
    period_values JSONB NOT NULL,
    source_workbook TEXT NOT NULL,
    sheet_name TEXT NOT NULL,
    row_number INTEGER NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL
);

Query Optimizations (PostgreSQL Indices)

2. Oracle Database Schema Details

The metrics table stores normalized observations flattened by chronological period.

Columns Dictionary

SQL Schema Definition

CREATE TABLE metrics (
    id NUMBER GENERATED ALWAYS AS IDENTITY,
    metric_id NUMBER,
    metric_name VARCHAR2(200),
    abbreviation VARCHAR2(50),
    sheet_name VARCHAR2(200),
    source_workbook VARCHAR2(200),
    row_number NUMBER,
    period VARCHAR2(20),
    value NUMBER,
    unit VARCHAR2(20),
    category VARCHAR2(100),
    CONSTRAINT metrics_pk PRIMARY KEY(id)
);

Future Schema Extensions

To transition QuantumLens into a multi-tenant enterprise portal, the database will be extended with the following schemas (tabulated):

Related Documentation

• Primary Readme: Project overview, installation scripts, API reference.
• System Architecture Spec: System layers overview and Mermaid diagrams.
• KPI Catalog & Normalization Rules: Dictionary lookup configurations.

KPI Catalog & Normalization Logic (docs/features/rag/kpi_catalog.md)

KPI Catalog & Normalization Logic

This document details the metric normalization engine, lookup dictionary catalog entries, and target mapping strategies utilized by QuantumLens.

For system architecture layouts, see architecture.md. For table details, see data_dictionary.md.

The Normalization Engine

In financial analytics, different business sheets and reporting periods frequently reference the same underlying metric using distinct labels. For example, "Net Interest Income", "Net Interest", and "NII" refer to the same metric.

To handle this variation, the pipeline runs a string-standardization flow:

  Raw Input Text               Clean & Normalize              Dictionary Hash Map             Normalized Output
" Net Interest Income " ──► "net interest income" ──► {"net interest income": ID: 1} ──► ID: 1, net_interest_income

Ingestion Matching Workflow Table

Complete Catalog Lookup Dictionary

The system loads mapping configurations from metric_dictionary.json. Below is the complete catalog:

Normalization Process Benefits

Related Documentation

• Primary Readme: Project overview, installation scripts, API reference.
• System Architecture Spec: Systems layers overview.
• Database Schema (Data Dictionary): Detailed columns description, indices, and constraints.

Troubleshooting Guide & Known Issues (docs/refactoring/troubleshooting.md)

Troubleshooting Guide and Known Issues

This document details the common troubleshooting steps, pipeline warnings, and historical resolutions for developers working with QuantumLens.

For system architecture layouts, see architecture.md. For table details, see data_dictionary.md.

1. Ingestion & Pipeline Troubleshooting

2. Database Connection & Supabase Troubleshooting

3. AI & Vector Database Troubleshooting

Related Documentation

• Primary Readme: Project overview, installation scripts, API reference.
• System Architecture Spec: Systems layers overview.
• Database Schema (Data Dictionary): Detailed columns description.

Detailed Engineering History & Issue Register (docs/refactoring/issues.md)

Engineering History: Issues, Investigations, and Resolutions

This document records the comprehensive engineering history of QuantumLens. It documents the critical issues encountered during development, the root causes identified, the investigation workflows, the specific code corrections, and the long-term architectural prevention strategies.

Issues Summary Table

1. JSON Serialization Failure

Issue Diagnostics

Code Diffs

# backend/src/transformation/kpi_builder.py
-def format_timestamp(ts):
-    return ts
+def format_timestamp(ts):
+    if hasattr(ts, "isoformat"):
+        return ts.isoformat()
+    return str(ts)

Prevention Strategy

• Long-Term Rule: Enforce serialization boundaries at ingestion points.
• Automation: Integrated Pydantic schema validation tests that raise warnings during check-ins if raw timestamps are passed.

2. NaN Values Breaking JSON

Issue Diagnostics

Data Types Comparison Table

Code Diffs

# backend/src/ingestion/value_extractor.py
-        numeric_values = [
-            value
-            for value in row.get("row_values", [])
-            if isinstance(value, (int, float))
-        ]
+        numeric_values = []
+        for value in row.get("row_values", []):
+            if isinstance(value, (int, float)):
+                if pd.isna(value) or value is np.nan:
+                    numeric_values.append(None)
+                else:
+                    numeric_values.append(float(value))

Prevention Strategy

• Long-Term Rule: Clean dataframe matrices using .replace({np.nan: None}) before mapping dicts.
• Automation: Configured structural API tests verifying inputs return valid JSON nulls.

3. Metric Normalization Problems

Issue Diagnostics

Normalization Mapping Table

Code Diffs

# backend/src/ingestion/metric_extractor.py
-def normalize_name(raw_name):
-    if "interest income" in raw_name.lower():
-        return 1, "net_interest_income"
+def normalize_name(raw_name):
+    clean_token = " ".join(raw_name.lower().split())
+    match = metric_dictionary.get(clean_token)
+    if match:
+        return match["metric_id"], match["normalized_metric_name"]
+    return None, None

Prevention Strategy

• Long-Term Rule: Centralize synonyms and aliases in static JSON catalogs.
• Automation: Programmed pipeline tests validating that skipped sheet labels raise build-stage warnings.

4. Duplicate Database Records

Issue Diagnostics

Database Operations Comparison

Code Diffs

# Database migration SQL
-ALTER TABLE metrics ADD COLUMN metric_id INTEGER;
+ALTER TABLE metrics ADD CONSTRAINT unique_metric_id UNIQUE (metric_id);

# backend/warehouse/data_loader.py
-supabase.table("metrics").insert(payload).execute()
+supabase.table("metrics").upsert(payload).execute()

Prevention Strategy

• Long-Term Rule: Enforce natural unique key constraints in database architectures.
• Automation: Configured integration tests that run ingestion twice and check that the row counts remain constant.

5. Supabase Authentication Issues

Issue Diagnostics

API Credentials Access Matrix

Code Diffs

# backend/warehouse/supabase_client.py
-SUPABASE_KEY = os.getenv("SUPABASE_PUBLIC_ANON_KEY")
+SUPABASE_KEY = os.getenv("SUPABASE_KEY")  # Administrative Service Key

Prevention Strategy

• Long-Term Rule: Isolate administrative ETL connections from public client read APIs.
• Automation: Configured CI/CD secrets screening to block hardcoded database service keys.

6. Datetime UTC Deprecation

Issue Diagnostics

Code Diffs

# backend/src/api/services/record_service.py
-from datetime import datetime
-return datetime.utcnow().isoformat()
+from datetime import datetime, timezone
+return datetime.now(timezone.utc).isoformat()

Prevention Strategy

• Long-Term Rule: Avoid timezone-naive datetime objects.
• Automation: Configured unit testing suites to treat Python deprecation warnings as errors, blocking builds containing deprecated datetime calls.

7. KPI Extraction Errors

Issue Diagnostics

Extraction Accuracy Comparison

Code Diffs

# backend/src/ingestion/metric_extractor.py
-def match_metric(label):
-    if "fee income" in label:
-        return "net_fee_income"
+def match_metric(label):
+    clean_label = label.strip().lower()
+    clean_label = re.sub(r'^(total|net|gross)\s+', '', clean_label)
+    # Enforce boundary checks
+    return exact_lookup(clean_label)

Prevention Strategy

• Long-Term Rule: Do not use loose substring checks for semantic categorization.
• Automation: Created evaluation datasets of common financial labels to evaluate extractor matching accuracy during builds.

8. Period Mapping Challenges

Issue Diagnostics

Period Mapping Schema

Raw Excel Layout:  [Column B: 4Q25] [Column C: 1Q26] [Column D: 2Q26]
                          │               │               │
                          ▼               ▼               ▼
Database JSONB:     [Period ID: 1]  [Period ID: 2]  [Period ID: 3]

Code Diffs

# backend/src/transformation/period_mapper.py
+def map_periods(numeric_list, chronological=True):
+    mapped = []
+    iterator = enumerate(numeric_list) if chronological else enumerate(reversed(numeric_list))
+    for idx, val in iterator:
+        mapped.append({
+            "period_index": idx + 1,
+            "value": val
+        })
+    return mapped

Prevention Strategy

• Long-Term Rule: Convert positional arrays to explicit key-value structures before database storage.
• Automation: Integrated chronos-sorting assertions into data loader schemas.

9. Excel Ingestion Parsing Issues

Issue Diagnostics

Excel Layout Parsing Matrix

Code Diffs

# backend/src/ingestion/sheet_scanner.py
-wb = openpyxl.load_workbook(file_path)
+wb = openpyxl.load_workbook(file_path, data_only=True)
# Add merged cell forward-filling
+def forward_fill_merged(row):
+    # Forward-fill logic

Prevention Strategy

• Long-Term Rule: Parse calculated cell values instead of formula strings.
• Automation: Configured pipeline checks to trigger errors if formula indicators are detected in database payloads.

10. ChromaDB Integration

Issue Diagnostics

Database Connection Schema

ETL Loader ──► [Embeddings JSON Cache] ──► [Local ChromaDB Client] ──► Query Engine
                                                    ▲
                                                    │ Persistence Target
                                           [src/rag/vector_db]

Code Diffs

# backend/src/rag/vector_loader.py
-client = chromadb.Client()
+client = chromadb.PersistentClient(path=str(settings.VECTOR_DB_PATH))

Prevention Strategy

• Long-Term Rule: Cache vector embeddings to avoid expensive runtime recalculations.
• Automation: Configured a boot check verifying vector collection presence before routers bind.

11. RAG Quality Problems

Issue Diagnostics

Code Diffs

# backend/src/rag/prompt_builder.py
-def build_prompt(question, documents):
-    return f"Context: {documents} Question: {question}"
+def build_prompt(question, retrieved_docs):
+    context = ""
+    for doc in retrieved_docs:
+        context += f"Metric: {doc['metric_name']}\nValues: {doc['values']}\nSource: {doc['sheet']}\n\n"
+    return f"Context:\n{context}\nQuestion: {question}"

Prevention Strategy

• Long-Term Rule: Structure RAG prompts as clean key-value records rather than raw string dumps.
• Automation: Implemented automated RAG evaluation scripts measuring response accuracy.

12. CORS Deployment Failure

Issue Diagnostics

Code Diffs

# backend/src/api/main.py
 app.add_middleware(
     CORSMiddleware,
     allow_origins=[
         "http://localhost:3000",
+        "https://quantumlens-hsbc.vercel.app",
     ],

Prevention Strategy

• Long-Term Rule: Configure CORS whitelists for all target environments.
• Automation: Programmed URL verification checks to run during CI deployment pipelines.

13. Environment Variable Failures

Issue Diagnostics

Code Diffs

# backend/src/config/settings.py
+from dotenv import load_dotenv
+load_dotenv()
 class Settings:
-    SUPABASE_URL = os.environ["SUPABASE_URL"]
+    SUPABASE_URL = os.getenv("SUPABASE_URL")
+    VECTOR_DB_PATH = os.getenv("VECTOR_DB_PATH", "src/rag/vector_db")

Prevention Strategy

• Long-Term Rule: Enforce fail-fast checks for critical variables on startup.
• Automation: Configured validation scripts to check required environment configurations on server boot.

14. Vercel Framework Detection

Issue Diagnostics

Project Directory Layout

Root folder (quantumlens-HSBC)
 ├── backend/ (Python API)
 └── frontend/ (Next.js App) ◄── Configure as Vercel build target root

Prevention Strategy

• Long-Term Rule: Configure build directory targets when using monorepo setups.
• Automation: Added a vercel.json routing configuration to the repository root.

15. Render Deployment Issues

Issue Diagnostics

Code Diffs

# backend/requirements.txt
+uvicorn==0.49.0
+gunicorn==21.2.0
+sentence-transformers==5.6.0

Prevention Strategy

• Long-Term Rule: Test model memory usage on lower-tier host specs before production deployment.
• Automation: Configured Docker resource limits to simulate production constraints during local testing.

16. Localhost vs Production API

Issue Diagnostics

Code Diffs

# frontend/services/api.ts
-const api = axios.create({ baseURL: 'http://localhost:8000' });
+const api = axios.create({
+  baseURL: process.env.NEXT_PUBLIC_API_URL || 'http://localhost:8000',
+});

Prevention Strategy

• Long-Term Rule: Use environment variables to configure URLs across development environments.
• Automation: Added build checks that fail compile steps if localhost URLs are detected in source code files.

17. Chart Rendering Problems

Issue Diagnostics

Code Diffs

# frontend/components/TimeSeriesChart.tsx
+const prepareChartData = (periodValues: any[]) => {
+  return periodValues
+    .map(item => ({
+      name: `Period ${item.period_index}`,
+      value: item.value
+    }))
+    .sort((a, b) => a.name.localeCompare(b.name));
+};

Prevention Strategy

• Long-Term Rule: Standardize data shapes on the API level before sending them to the client.
• Automation: Added unit tests to verify chart component stability against unsorted datasets.

18. Frontend State Management

Issue Diagnostics

Code Diffs

# frontend/app/dashboard/page.tsx
-const [state, setState] = useState({ metric: null, record: null, query: "" });
+const [selectedMetric, setSelectedMetric] = useState<number | null>(null);
+const [selectedRecord, setSelectedRecord] = useState<any | null>(null);
+const [searchQuery, setSearchQuery] = useState<string>("");

Prevention Strategy

• Long-Term Rule: Keep React state close to the components that use it to avoid redundant rendering.
• Automation: Configured page profiling targets to alert on component re-render counts during development.

19. AI Assistant Development

Issue Diagnostics

Code Diffs

# backend/src/rag/prompt_builder.py
-    return f"Answer: {question} using {retrieved_docs}"
+    return f"""You are a financial analyst copilot.
+    Rules:
+    1. Restrict your answer strictly to the context below.
+    2. Quote exact numbers and sources.
+    3. If context is insufficient, state that the data is not available.
+    Context: {retrieved_docs} Question: {question}"""

Prevention Strategy

• Long-Term Rule: Constrain RAG prompts with strict context guidelines to prevent hallucinations.
• Automation: Added evaluation query test lists to assess citation and answer accuracy during updates.

FastAPI Backend Server Documentation (backend/README.md)

FastAPI Backend Server (backend/)

This is the backend REST API engine for the QuantumLens platform. It handles Excel data ingestion pipelines, manages the dual-database warehouses, generates semantic vector embeddings, and hosts the multi-agent AI copilot.

For general details on the project architecture or dashboards, see the root README.md.

Technical Stack & Configuration

Systems Architecture Diagram

graph TD
    %% Define Nodes
    FastAPI["FastAPI Web Router (src/api/)"]
    
    subgraph ETLPipeline ["ETL Pipeline (src/ingestion/ & src/transformation/)"]
        Reader["Workbook Reader"]
        Scanner["Sheet Scanner"]
        Extractor["Metric Extractor"]
        Mapper["Period Mapper"]
        Builder["KPI Builder"]
    end
    
    subgraph DatabaseLayer ["Database Warehouse Layer (warehouse/)"]
        Supabase[("Supabase (PostgreSQL)<br>[Production JSONB arrays]")]
        Oracle[("Oracle Database 19c<br>[Relational Period rows]")]
    end

    subgraph AIEngine ["AI & Semantic RAG Layer (src/rag/ & src/agents/)"]
        Chroma[("ChromaDB Vector Store<br>[Local Index Cache]")]
        Planner["Planner Agent"]
        SQLAgent["SQL Agent"]
        RAGAgent["RAG Agent"]
    end

    %% Connections
    FastAPI <-->|SQL Queries| Supabase
    FastAPI <-->|Query Context| Chroma
    
    %% ETL Ingestion Flow
    xlsx["Raw XLSX Files"] --> Reader
    Reader --> Scanner
    Scanner --> Extractor
    Extractor --> Mapper
    Mapper --> Builder
    Builder -->|Batch Upserts| Supabase
    Builder -->|Insert Rows| Oracle
    
    %% Vector Ingestion Flow
    Supabase -->|SQL Extract| Chroma
    
    %% Multi-Agent Flow
    FastAPI <-->|Natural Query| Planner
    Planner <-->|Intent Matrix| SQLAgent
    Planner <-->|Intent Matrix| RAGAgent
    SQLAgent <--> Oracle
    RAGAgent <--> Chroma

Loading

Dual-Database Warehouse Layouts

The database layer processes data into two distinct target topologies:

1. Supabase (PostgreSQL 15) Table: metrics

Stores complete time-series observations inside a single row using a dynamic JSONB array column.

• Primary Key: id (SERIAL)
• Unique Constraint: metric_id (enforces 1 row per KPI)
• Time-Series Column: period_values (JSONB)
• Index: B-Tree indices on id, metric_id, and metric_name

2. Oracle Database Table: metrics

Flattens observations into individual rows (one row per quarter/period) for OLAP reports.

• Primary Key: id (NUMBER GENERATED ALWAYS AS IDENTITY)
• Chronological Columns: period (VARCHAR2) & value (NUMBER)
• Classification Columns: category (VARCHAR2), unit (VARCHAR2)

Local Ingestion and API Setup

Prerequisites Checklist

• Python 3.13+ installed.
• SQLite3 and local system path configurations enabled.
• Oracle Client libraries (instant client) configured for python environment connections.

Setup and execution commands

Environment Variables Configuration

[!WARNING]

• Never commit the .env file containing actual secrets to public repositories.
• Ensure all variables are configured in the cloud host panel during deployments.

API Endpoints Reference

Endpoints Matrix

Request/Response JSON Formats

POST /ask

Request JSON Structure

{
  "question": "Why did Expected Credit Losses (ECL) rise?"
}

Response JSON Structure

{
  "question": "Why did Expected Credit Losses (ECL) rise?",
  "answer": "- According to sheet 'Group Income Statement' in '260505-1q-2026-data-pack-excel.xlsx':\n- Expected Credit Losses (ECL) rose due to fraud-related exposure on UK securitisation transactions and rising Middle East geopolitical volatility.",
  "sources": [
    {
      "metric_id": 5,
      "metric_name": "expected_credit_losses",
      "sheet_name": "Group Income Statement",
      "source_workbook": "260505-1q-2026-data-pack-excel.xlsx",
      "row_number": 12
    }
  ]
}

Related Documentation

• Primary Readme: Project overview and complete monorepo details.
• Frontend Readme: Next.js dashboard visual portal instructions.
• System Architecture Spec: Systems layers and sequence flows.

Frontend Analytics Portal Documentation (frontend/README.md)

Next.js Analytics Portal (frontend/)

This is the interactive client dashboard for the QuantumLens financial intelligence platform. It provides a visual interface for executive strategy, risk observation, and natural language reasoning (AI financial assistant).

For general backend endpoints, models, or database queries, see the backend/README.md.

Technical Stack & Visual Framework

Interactive Dashboard Views

The Next.js client organizes analytics into five strategic "War Rooms":

Dashboard 1: Global Banking Pulse

• Core Metrics: Net Interest Income (NII), CET1 capital ratio, Return on Tangible Equity (RoTE), liquidity levels, and loan growth rates.
• Interactivity: Dynamic regional heatmaps, timeline cross-filtering, and animated trend transitions.

Dashboard 2: Wealth Migration Observatory

• Core Metrics: Asia wealth inflows ($34B in Q1 2026), net new wealth assets ($39B net new money), insurance growth, and HNW capital concentration.
• Interactivity: Capital concentration charts and deposit migration flow trackers.

Dashboard 3: Credit Stress Radar

• Core Metrics: Expected Credit Losses (ECL) trends (guidance raised to 45bps), sector-level write-off ratios, and UK securitisation fraud warnings.
• Interactivity: Stress scenario models visualizing asset risk propagation under macroeconomic shocks.

Dashboard 4: Strategic Transformation Tracker

• Core Metrics: Asset disposals, simplification cost savings targets ($1.5B), Hang Seng privatization synergies ($0.5B), and capital reallocation programs.
• Interactivity: Milestone checklist meters and operational budget analytics charts.

Dashboard 5: Banking Contagion Network

• Core Metrics: Relational risk propagation maps pulling from Neo4j (Middle East Conflict ──► Energy Price Volatility ──► Expected Credit Losses ──► Capital Deterioration).
• Interactivity: Interactive node graph visualization showing asset exposure and liquidity dependencies.

Getting Started

Prerequisites Checklist

• Node.js version 18.x or later installed.
• Running instance of the FastAPI backend server.

Local Development Setup

Environment Variables Configuration

[!WARNING] Do not commit .env.local containing actual backend production URLs. Keep configurations restricted to local variables.

Production Deployment (Vercel)

Vercel is the recommended hosting platform for Next.jsApp Router portals. Follow these steps:

Related Documentation

• Primary Readme: Complete repository layouts.
• Backend Readme: FastAPI REST routing specs.
• System Architecture Spec: Systems layers overview.