System Architecture Diagrams¶
Overall System Architecture¶
┌────────────────────────────────────────────────────────────────────────────┐
│ External Integrations │
├────────────────────────────────────────────────────────────────────────────┤
│ Finnhub (Market Data) │ Alpha Vantage (Fallback) │ Ollama (LLM) │
│ Google Sheets (Budget) │ CSV Bank Export (FinTS) │ OpenAI/Gemini │
└────────────────────────────────────────────────────────────────────────────┘
▲
┌───────────────────┼───────────────────┐
▼ ▼ ▼
┌───────────────────────┐ ┌─────────────────────┐
│ Frontend │ │ oauth2-proxy │
│ React + Vite │ │ (Production) │
│ Tailwind + CSS │ │ (Port 4180) │
│ (Port 3000 dev) │ └─────────────────────┘
│ │
│ - Asset Dashboard │
│ - Transaction Upload │
│ - Budget Comparison │
└───────────────────────┘
│
▼
┌────────────────────────────────────┐
│ Backend - FastAPI │
│ (Port 8000) │
├────────────────────────────────────┤
│ • ~50 API Endpoints │
│ • Asset Management │
│ • Transaction Pipeline │
│ • Budget Comparison │
│ • Settings & Configuration │
└────────────────────────────────────┘
│
├────────────────────┬──────────────────┐
▼ ▼ ▼
┌─────────────────────┐ ┌──────────────┐ ┌─────────────────┐
│ SQLAlchemy ORM │ │ market_data │ │ AI Service │
│ (Database Layer) │ │ _service.py │ │ (Ollama) │
│ │ │ │ │ │
│ 7 SQLModel Tables │ │ • Finnhub │ │ • Categorize │
│ • User │ │ • AlphaV │ │ transactions │
│ • Asset │ │ • EUR Conv │ │ • 6 categories │
│ • Price Cache │ │ • Fallback │ │ • Batch proc │
│ • Transaction │ └──────────────┘ └─────────────────┘
│ • etc. │
└─────────────────────┘
│
▼
┌────────────────────────────────────┐
│ PostgreSQL (Prod) / SQLite (Dev) │
│ Uberspace (Prod SSH Tunnel) │
└────────────────────────────────────┘
Backend Endpoint Organization (Current Monolith)¶
main.py (~50 endpoints)
├── Assets Endpoints
│ ├── GET /api/assets
│ ├── POST /api/assets
│ ├── GET /api/assets/{symbol}
│ ├── GET /api/assets/{symbol}/details
│ ├── POST /api/assets/{symbol}/analysis
│ ├── GET /api/assets/analyze ⚠️ ORDERING ISSUE: must be before {symbol}
│ ├── DELETE /api/assets/{symbol}
│ └── [more...]
│
├── Transaction Endpoints
│ ├── GET /api/transactions
│ ├── POST /api/transactions/upload/{account_id}
│ ├── POST /api/transactions
│ ├── DELETE /api/transactions/{id}
│ └── [more...]
│
├── Settings Endpoints
│ ├── GET /api/settings
│ ├── POST /api/settings
│ └── [more...]
│
├── AI/Analysis Endpoints
│ ├── POST /api/transactions/categorize
│ ├── POST /api/assets/analyze
│ └── [more...]
│
└── Health/Utility
├── GET /health
├── POST /api/refresh-cache
└── [more...]
⚠️ Problem: Endpoint ordering matters. /api/assets/analyze must come before /api/assets/{symbol}/details.
Data Flow: Asset Price Updates¶
Frontend (30s poll)
│
▼
GET /api/assets
│
├─→ Fetch current prices from market_data_service.py
│ ├─→ Try Finnhub API
│ └─→ Fallback to Alpha Vantage (if Finnhub fails)
│
├─→ Convert prices to EUR
│
├─→ Write to price_cache table (important!)
│ └─→ Historical data built from 30s polling
│
└─→ Return JSON response with prices
│
▼
Frontend updates UI
Key insight: GET /api/assets is NOT read-only. It writes prices to price_cache table. Frontend polling creates the price history.
Data Flow: Transaction Ingestion Pipeline¶
User uploads CSV
│
▼
POST /api/transactions/upload/{account_id}
│
├─ Detect encoding (UTF-8, ISO-8859-1, CP1252)
│
├─ Detect delimiter (; vs ,)
│
├─ Parse CaMT52-like format
│
├─ Batch categorize via Ollama
│ └─→ 6 categories: Miete, Lebensmittel, Versicherungen, Freizeit, Gehalt, Sonstiges
│
├─ For each transaction:
│ ├─ Generate SHA-256 fingerprint (deduplication)
│ │ └─→ Check if exists in database (skip if dup)
│ │
│ ├─ Encrypt description with Fernet AES-256
│ │ └─→ Uses ALE_KEY (must remain stable)
│ │
│ ├─ Generate HMAC blind index
│ │ └─→ Uses BLIND_INDEX_KEY (for searchable encryption)
│ │
│ └─ Store in database:
│ {
│ description_encrypted: "gAAAAAB...",
│ blind_index: "sha256:abc123...",
│ fingerprint: "sha256:xyz789...",
│ category: "Lebensmittel",
│ amount: 42.50,
│ date: "2026-05-10"
│ }
│
└─→ Response: "5 transactions imported, 2 duplicates skipped"
GET /api/transactions returns: Descriptions are DECRYPTED before response, but response field is still named description_encrypted (frontend/tests depend on shape).
Security Model¶
┌──────────────────────────────────────────────────────────────────┐
│ Transaction Data Security │
├──────────────────────────────────────────────────────────────────┤
│ │
│ Raw Description: "REWE SUPERMARKT MUENCHEN 42.50" │
│ │
│ Step 1: Encrypt with Fernet (AES-256) │
│ ┌──────────────────────────────────────────────────┐ │
│ │ description_encrypted = Fernet(ALE_KEY).encrypt()│ │
│ │ Result: "gAAAAAB1234567890..." (172 chars) │ │
│ └──────────────────────────────────────────────────┘ │
│ │
│ Step 2: Generate Blind Index (HMAC) │
│ ┌──────────────────────────────────────────────────┐ │
│ │ blind_index = HMAC(BLIND_INDEX_KEY, plaintext) │ │
│ │ Result: "sha256:abc123def456..." (deterministic) │ │
│ └──────────────────────────────────────────────────┘ │
│ │
│ Step 3: Generate Fingerprint (Deduplication) │
│ ┌──────────────────────────────────────────────────┐ │
│ │ fingerprint = SHA256(raw_line) │ │
│ │ Result: "xyz789abc123..." (unique per row) │ │
│ └──────────────────────────────────────────────────┘ │
│ │
│ Database Storage: │
│ { │
│ description_encrypted: "gAAAAAB...", ← Cannot read │
│ blind_index: "sha256:abc123...", ← Can search on │
│ fingerprint: "sha256:xyz789...", ← Can check dupes │
│ category: "Lebensmittel" ← AI categorized │
│ } │
│ │
│ When returning to user: │
│ description = Fernet(ALE_KEY).decrypt(description_encrypted) │
│ Returns: "REWE SUPERMARKT MUENCHEN 42.50" (decrypted plaintext) │
│ │
└──────────────────────────────────────────────────────────────────┘
Benefits:
✅ Encrypted at rest (cannot read if DB compromised)
✅ Searchable without decrypting (blind index)
✅ Deduplication without comparing plaintext (fingerprint)
✅ HMAC prevents tampering (blind index validation)
✅ Fernet prevents replay (uses timestamp + nonce)
Critical: ALE_KEY and BLIND_INDEX_KEY must NEVER change
(Changing either breaks all existing encrypted data)
Database Schema (Simplified)¶
-- Users
User
├── id: int
├── email: str
└── created_at: datetime
-- Assets
Asset
├── id: int
├── symbol: str (e.g., "VTI")
├── name: str
├── quantity: float
├── avg_cost: float
└── user_id: int (FK)
-- Price Cache (built from polling)
PriceCache
├── id: int
├── asset_id: int (FK)
├── price: float
├── currency: str ("EUR")
├── timestamp: datetime
└── source: str ("Finnhub" | "AlphaVantage")
-- Transactions (encrypted)
Transaction
├── id: int
├── date: date
├── amount: float
├── description_encrypted: str ← Fernet encrypted
├── blind_index: str ← HMAC for search
├── fingerprint: str ← SHA-256 for dedup
├── category: str
├── user_id: int (FK)
└── created_at: datetime
-- Settings
AppSettings
├── id: int
├── key: str
├── value: str
└── user_id: int (FK)
-- PriceCache (if using it)
PriceCache
├── id: int
├── symbol: str
├── price: float
├── timestamp: datetime
└── source: str
Frontend Component Structure¶
App.tsx (Root)
├── tab state: "assets" | "transactions"
├── selectedSymbol state
│
├── When tab="assets":
│ ├── AssetDashboard
│ │ ├── AssetList (grid view)
│ │ │ └── AssetCard (dark theme, inline styles)
│ │ │
│ │ └── AssetDetailPanel (dark theme, inline styles)
│ │ ├── Chart (price history from price_cache)
│ │ ├── Holdings
│ │ ├── Performance metrics
│ │ └── Add/Edit/Delete buttons
│ │
│ └── useAssets hook (polls GET /api/assets every 30s)
│
└── When tab="transactions":
├── TransactionPage
│ ├── TransactionUpload (Tailwind, light theme)
│ │ └── File input + progress
│ │
│ ├── TransactionList (Tailwind, light theme)
│ │ └── Table: date, amount, description, category
│ │
│ └── TransactionFilter
│ └── By category, date range, amount
│
└── useTransactions hook (polls GET /api/transactions every 30s)
⚠️ Theme inconsistency: Asset screens (dark + inline) vs Transaction screens (light + Tailwind).
Key Statistics¶
- Backend endpoints: ~50 in monolithic main.py
- Database tables: 7 SQLModel tables
- Frontend routes: 2 tabs (assets, transactions)
- LLM categories: 6 transaction categories
- Encryption: Fernet AES-256 + HMAC + SHA-256
- Market data sources: 2 (Finnhub primary, Alpha Vantage fallback)
- Frontend polling cadence: 30 seconds (hard-coded, no backoff)
- Transaction fingerprinting: SHA-256 for deduplication
For more details, see Detailed Reference.