🏗️ Portfolio Tracker (PostgreSQL) — Architecture Documentation¶
Architecture Classification¶
This application is a full-stack Flask web application with a JSON API layer, backed by a managed AWS RDS PostgreSQL database.
| Question | Answer |
|---|---|
| What is this application? | A multi-page Flask web application with server-rendered templates and a JSON API for interactive Chart.js/TradingView dashboards |
| How is it deployed? | As a Gunicorn WSGI service managed by systemd on AWS EC2, connecting to RDS PostgreSQL in a private subnet |
| What pattern does it use? | Application factory with blueprints (auth, portfolio), request-scoped DB connections, and CLI data-loading commands |
Diagram 1 — Software Component Architecture¶
Shows all internal modules, their dependencies, third-party libraries, and external systems.
graph TB
subgraph "Python Package: app_portfolio_tracker_aurora"
INIT["__init__.py<br/><i>Application Factory</i><br/>create_app() · Blueprints<br/>CLI commands · Health check"]
AUTH["auth.py<br/><i>Authentication Blueprint</i><br/>/auth/register · /auth/login<br/>session management"]
PORTFOLIO["portfolio.py<br/><i>Portfolio Blueprint</i><br/>Dashboard · Analytics<br/>11 JSON API endpoints"]
DB["db.py<br/><i>Database Layer</i><br/>Connection pool (psycopg2)<br/>CSV parsers · CLI commands"]
CALC["calculations.py<br/><i>Financial Engine</i><br/>XIRR · NAV · Allocation<br/>Monthly returns · Calendar"]
MARKET["market_data.py<br/><i>Market Data Service</i><br/>Yahoo Finance integration<br/>Price & FX rate cache"]
CONFIG["config.py<br/><i>Configuration</i><br/>Environment-based settings<br/>Session · Upload limits"]
SCHEMA["schema.sql<br/><i>Database Schema</i><br/>8 tables · 3 views<br/>12 indexes"]
end
subgraph "Third-Party Libraries"
LIB_FLASK["🌐 Flask 3.x<br/><i>Web framework</i>"]
LIB_PSYCOPG["🐘 psycopg2<br/><i>PostgreSQL driver</i>"]
LIB_YF["📈 yfinance<br/><i>Yahoo Finance API</i>"]
LIB_SCIPY["🔬 scipy<br/><i>XIRR optimisation</i>"]
LIB_PYTZ["🕐 pytz<br/><i>Timezone conversions</i>"]
LIB_DOTENV["📄 python-dotenv<br/><i>.env file loader</i>"]
LIB_WERKZEUG["🔐 Werkzeug<br/><i>Password hashing</i>"]
LIB_GUNICORN["🦄 Gunicorn<br/><i>WSGI server</i>"]
end
subgraph "Frontend (CDN Libraries)"
FE_CHARTJS["📊 Chart.js<br/><i>Pie · Doughnut · Bar</i>"]
FE_TV["📈 TradingView<br/>Lightweight Charts<br/><i>Time-series lines</i>"]
FE_JINJA["📄 Jinja2 Templates<br/><i>Server-side rendering</i>"]
end
subgraph "External Services"
RDS_PG["🐘 AWS RDS PostgreSQL<br/><i>Private subnet · Port 5432</i>"]
YAHOO["📈 Yahoo Finance API<br/><i>HTTPS — prices & FX rates</i>"]
end
subgraph "Local Filesystem"
FS_ENV[".env<br/><i>DATABASE_URL · SECRET_KEY</i>"]
FS_CSV["csv_data/<br/><i>Broker CSV/TXT files</i>"]
end
%% Internal dependencies
INIT -->|"registers"| AUTH
INIT -->|"registers"| PORTFOLIO
INIT -->|"initialises"| DB
INIT -->|"registers CLI"| MARKET
PORTFOLIO -->|"imports"| CALC
PORTFOLIO -->|"imports"| DB
AUTH -->|"imports"| DB
CALC -->|"imports"| MARKET
CALC -->|"imports"| DB
MARKET -->|"imports"| DB
DB -->|"applies"| SCHEMA
INIT -->|"loads"| CONFIG
%% Library usage
INIT -.-|"uses"| LIB_FLASK
INIT -.-|"uses"| LIB_DOTENV
DB -.-|"uses"| LIB_PSYCOPG
DB -.-|"uses"| LIB_PYTZ
MARKET -.-|"uses"| LIB_YF
CALC -.-|"uses"| LIB_SCIPY
AUTH -.-|"uses"| LIB_WERKZEUG
%% Frontend
PORTFOLIO -->|"renders"| FE_JINJA
FE_JINJA -->|"includes"| FE_CHARTJS
FE_JINJA -->|"includes"| FE_TV
%% External system interactions
DB -->|"psycopg2<br/>connection"| RDS_PG
MARKET -->|"yfinance<br/>HTTPS API"| YAHOO
%% Filesystem
CONFIG -->|"reads"| FS_ENV
DB -->|"reads"| FS_CSV
%% Styling
classDef module fill:#4A90D9,stroke:#2C5F8A,color:#fff,stroke-width:2px
classDef lib fill:#F5A623,stroke:#C77D0A,color:#fff,stroke-width:2px
classDef frontend fill:#9B59B6,stroke:#7D3C98,color:#fff,stroke-width:2px
classDef external fill:#E74C3C,stroke:#C0392B,color:#fff,stroke-width:2px
classDef filesystem fill:#27AE60,stroke:#1E8449,color:#fff,stroke-width:2px
class INIT,AUTH,PORTFOLIO,DB,CALC,MARKET,CONFIG,SCHEMA module
class LIB_FLASK,LIB_PSYCOPG,LIB_YF,LIB_SCIPY,LIB_PYTZ,LIB_DOTENV,LIB_WERKZEUG,LIB_GUNICORN lib
class FE_CHARTJS,FE_TV,FE_JINJA frontend
class RDS_PG,YAHOO external
class FS_ENV,FS_CSV filesystemDiagram 2 — Request Lifecycle Sequence Diagram¶
Shows the full request flow when a user loads the interactive dashboard, including API calls for chart data.
sequenceDiagram
autonumber
participant USER as 👤 Browser
participant GUN as 🦄 Gunicorn<br/>(WSGI)
participant FLASK as 🌐 Flask<br/>(portfolio.py)
participant AUTH as 🔐 Auth<br/>(auth.py)
participant JINJA as 📄 Jinja2<br/>Templates
participant CALC as 🔬 Calculations<br/>(calculations.py)
participant MARKET as 📈 Market Data<br/>(market_data.py)
participant DB as 🐘 psycopg2
participant RDS as ☁️ RDS<br/>PostgreSQL
participant YAHOO as 📊 Yahoo<br/>Finance API
Note over USER,GUN: Phase 1 — Page Load
USER->>GUN: GET /dashboard
GUN->>FLASK: WSGI dispatch
FLASK->>AUTH: @bp.before_app_request<br/>load_logged_in_user()
AUTH->>DB: SELECT * FROM "user" WHERE id = %s
DB->>RDS: SQL query
RDS-->>DB: User row
DB-->>AUTH: g.user = {...}
FLASK->>JINJA: render_template('portfolio/dashboard.html')
JINJA-->>FLASK: HTML with Chart.js + TradingView JS
FLASK-->>GUN: HTTP 200 + HTML
GUN-->>USER: Dashboard page
Note over USER,YAHOO: Phase 2 — Async API Calls (JavaScript fetch)
par Performance Summary
USER->>GUN: GET /api/performance-summary?currency=SGD
GUN->>FLASK: Route dispatch
FLASK->>CALC: get_performance_summary(base_currency='SGD')
CALC->>DB: SELECT holdings, cash flows, transactions
DB->>RDS: Multiple SQL queries
RDS-->>DB: Result sets
CALC->>MARKET: get_latest_price(ticker, date)
MARKET->>DB: SELECT FROM DAILY_PRICE (cache lookup)
DB->>RDS: Cache query
RDS-->>DB: Cached price (or miss)
alt Cache miss
MARKET->>YAHOO: yfinance.Ticker(ticker).history()
YAHOO-->>MARKET: OHLCV data
MARKET->>DB: INSERT INTO DAILY_PRICE
DB->>RDS: Cache write
end
CALC->>MARKET: get_fx_rate('USD', 'SGD', date)
MARKET->>DB: SELECT FROM FX_RATE (cache lookup)
DB->>RDS: Cache query
RDS-->>DB: FX rate
CALC->>CALC: calculate_portfolio_xirr()<br/>scipy.optimize.brentq()
CALC-->>FLASK: {total_nav, xirr_pct, ...}
FLASK-->>USER: JSON response
and Asset Allocation
USER->>GUN: GET /api/allocation?currency=SGD
GUN->>FLASK: Route dispatch
FLASK->>CALC: get_asset_allocation()
CALC-->>FLASK: [{ticker, value, percentage}, ...]
FLASK-->>USER: JSON → Chart.js pie chart
and NAV History
USER->>GUN: GET /api/nav-history?currency=SGD&interval=7
GUN->>FLASK: Route dispatch
FLASK->>CALC: get_nav_history()
Note over CALC: Iterates dates at interval,<br/>calculates NAV at each point
CALC-->>FLASK: [{date, nav, securities_nav, fund_balance}, ...]
FLASK-->>USER: JSON → TradingView line chart
end
Note over USER: Dashboard renders all chartsDiagram 3 — Network & Infrastructure Architecture¶
Shows the AWS infrastructure layout including VPC, subnets, security groups, and traffic flow as defined in the CloudFormation stack.
graph TB
subgraph "Developer Machine (Windows)"
DEV["💻 Developer<br/>PyCharm / VS Code"]
end
subgraph "GitHub"
GH["📦 GitHub Repository<br/>darren2pro/portfolio_tracker"]
end
subgraph "Cloudflare"
CF["🌐 Cloudflare Pages<br/>portfolio.darrenheng.dev<br/><i>MkDocs documentation site</i>"]
end
subgraph "AWS Cloud — ap-southeast-1 (Singapore)"
subgraph "VPC: 10.0.0.0/16"
subgraph "Public Subnet A: 10.0.0.0/24 (AZ-a)"
EC2["🖥️ EC2 Instance<br/>t3.large · Amazon Linux 2023<br/><br/>🦄 Gunicorn (port 8080)<br/>🐍 Python 3.13 venv<br/>📦 app_portfolio_tracker_aurora"]
end
subgraph "Public Subnet B: 10.0.1.0/24 (AZ-b)"
PUB_B["<i>(Available for<br/>future scaling)</i>"]
end
subgraph "Private Subnet A: 10.0.10.0/24 (AZ-a)"
RDS_A["🐘 RDS PostgreSQL<br/>db.t3.medium<br/>Engine: PostgreSQL 16<br/>Port 5432"]
end
subgraph "Private Subnet B: 10.0.11.0/24 (AZ-b)"
RDS_B["<i>(Multi-AZ standby<br/>— available if enabled)</i>"]
end
IGW["🌐 Internet Gateway"]
end
subgraph "Security Groups"
SG_BASTION["🔒 Bastion SG<br/>Inbound: SSH (22), HTTP (8080)<br/>Outbound: All"]
SG_DB["🔒 Database SG<br/>Inbound: PostgreSQL (5432)<br/><i>from Bastion SG only</i><br/>Outbound: All"]
end
end
subgraph "External APIs"
YAHOO["📈 Yahoo Finance<br/>HTTPS (443)"]
end
%% Developer connections
DEV -->|"SSH (port 22)<br/>Key pair auth"| EC2
DEV -->|"git push"| GH
GH -->|"git pull<br/>(deploy script)"| EC2
%% Internet Gateway
IGW -->|"Public route<br/>0.0.0.0/0"| EC2
%% EC2 → Database (private)
EC2 -->|"PostgreSQL (5432)<br/>psycopg2 · Same VPC"| RDS_A
%% Security Group assignments
SG_BASTION -.->|"attached to"| EC2
SG_DB -.->|"attached to"| RDS_A
%% EC2 → External APIs
EC2 -->|"HTTPS (443)<br/>yfinance"| YAHOO
%% Cloudflare
DEV -->|"mkdocs build<br/>wrangler pages deploy"| CF
%% No NAT
RDS_A -.->|"❌ No internet access<br/>(no NAT Gateway)"| IGW
%% Styling
classDef dev fill:#5B9BD5,stroke:#2E75B6,color:#fff,stroke-width:2px
classDef github fill:#333,stroke:#555,color:#fff,stroke-width:2px
classDef ec2 fill:#FF9900,stroke:#CC7A00,color:#fff,stroke-width:2px
classDef rds fill:#3B48CC,stroke:#2A35A0,color:#fff,stroke-width:2px
classDef sg fill:#E67E22,stroke:#CA6F1E,color:#fff,stroke-width:2px
classDef ext fill:#E74C3C,stroke:#C0392B,color:#fff,stroke-width:2px
classDef cf fill:#F48120,stroke:#D06E1A,color:#fff,stroke-width:2px
classDef placeholder fill:#95A5A6,stroke:#7F8C8D,color:#fff,stroke-width:1px
class DEV dev
class GH github
class EC2 ec2
class RDS_A rds
class SG_BASTION,SG_DB sg
class YAHOO ext
class CF cf
class IGW ec2
class PUB_B,RDS_B placeholderNetwork Security Summary¶
| Rule | Source | Destination | Port | Purpose |
|---|---|---|---|---|
| SSH access | Developer IP | EC2 Bastion | 22 | Remote administration |
| Flask app | Allowed CIDR | EC2 Bastion | 8080 | Web application access |
| Database | Bastion SG only | RDS PostgreSQL | 5432 | Application → DB queries |
| Yahoo Finance | EC2 (outbound) | api.yahoo.com | 443 | Market data fetch |
| No NAT Gateway | — | — | — | Cost saving — RDS has no internet |
Diagram 4 — Data Pipeline & ETL Flow¶
Shows how investment data flows from broker platforms through CSV parsing into the database, then through the calculation engine to the frontend visualisations.
flowchart LR
subgraph "Data Sources"
MOOMOO_SEC["📄 Moomoo Securities<br/>CSV Export<br/><i>Stocks · Options · ETFs</i>"]
MOOMOO_FUND["📄 Moomoo Funds<br/>CSV Export<br/><i>Money Market Funds</i>"]
MOOMOO_DEP["📄 Moomoo Deposits<br/>TXT Export<br/><i>External cash inflows</i>"]
MOOMOO_WD["📄 Moomoo Withdrawals<br/>TXT Export<br/><i>External cash outflows</i>"]
YAHOO_API["📈 Yahoo Finance<br/>API<br/><i>Prices · FX Rates</i>"]
end
subgraph "Ingestion Layer (db.py)"
PARSE_SEC["handle_moomoo_securities()<br/><i>Parse filled orders</i><br/><i>Handle partial fills</i><br/><i>Detect asset types</i><br/><i>Convert timezones</i>"]
PARSE_FUND["handle_moomoo_funds()<br/><i>Parse subscribe/redeem</i><br/><i>Extract amount+currency</i>"]
PARSE_FLOW["handle_moomoo_external_flows()<br/><i>Parse 3-line TXT blocks</i><br/><i>DD/MM/YYYY dates</i>"]
LOAD_ORCH["load_csv_data()<br/><i>Orchestrator</i><br/><i>Scans csv_data/ directory</i><br/><i>Routes files to handlers</i>"]
end
subgraph "Market Data Layer (market_data.py)"
FETCH_PRICES["fetch_historical_prices()<br/><i>Smart caching — skip existing dates</i><br/><i>OHLCV → DAILY_PRICE table</i>"]
FETCH_FX["fetch_fx_rates()<br/><i>SGD/USD + USD/SGD pairs</i><br/><i>Daily close → FX_RATE table</i>"]
end
subgraph "RDS PostgreSQL"
T_BROKER[("BROKER<br/><i>Moomoo · Tiger · WeBull</i>")]
T_TRANS[("TRANSACTION<br/><i>Securities trades</i>")]
T_FUND[("FUND_TRANSACTION<br/><i>MMF subscribe/redeem</i>")]
T_CASH[("EXTERNAL_CASH_FLOW<br/><i>Deposits/withdrawals</i>")]
T_PRICE[("DAILY_PRICE<br/><i>OHLCV cache</i>")]
T_FX[("FX_RATE<br/><i>Currency rates</i>")]
T_SNAP[("BALANCE_SNAPSHOT<br/><i>Manual overrides</i>")]
V_HOLD[("VIEW: PORTFOLIO_HOLDINGS<br/><i>Net qty · Avg cost</i>")]
V_SUMMARY[("VIEW: TRANSACTION_SUMMARY<br/><i>Aggregated by ticker</i>")]
V_FBAL[("VIEW: FUND_BALANCE_SUMMARY<br/><i>Net fund balance</i>")]
end
subgraph "Calculation Engine (calculations.py)"
CALC_NAV["calculate_securities_nav()<br/><i>Holdings × Price × FX</i>"]
CALC_FUND["calculate_fund_balance()<br/><i>Subscribe − Redeem</i>"]
CALC_XIRR["calculate_portfolio_xirr()<br/><i>scipy.optimize.brentq</i>"]
CALC_ALLOC["get_asset_allocation()<br/>get_currency_allocation()"]
CALC_HIST["get_nav_history()<br/><i>Weekly NAV snapshots</i>"]
CALC_RET["get_monthly_returns()<br/><i>Cash-flow adjusted</i>"]
end
subgraph "API Layer (portfolio.py)"
API["/api/performance-summary<br/>/api/nav-history<br/>/api/allocation<br/>/api/currency-allocation<br/>/api/monthly-returns<br/>/api/irr<br/>/api/holdings<br/>/api/fund-balance<br/>/api/cash-flows<br/>/api/transactions<br/>/api/transactions-calendar"]
end
subgraph "Frontend Visualisation"
STAT["📊 Stat Cards<br/><i>NAV · XIRR · Return %</i>"]
PIE["🥧 Pie Chart<br/><i>Asset allocation</i>"]
DONUT["🍩 Doughnut Chart<br/><i>Currency allocation</i>"]
BAR["📊 Bar Chart<br/><i>Monthly returns</i>"]
LINE["📈 Line Chart<br/><i>NAV time series</i>"]
HEAT["🗓️ Calendar Heatmap<br/><i>Transaction activity</i>"]
TABLE["📋 Data Table<br/><i>Recent transactions</i>"]
end
%% Source → Ingestion
MOOMOO_SEC --> PARSE_SEC
MOOMOO_FUND --> PARSE_FUND
MOOMOO_DEP --> PARSE_FLOW
MOOMOO_WD --> PARSE_FLOW
LOAD_ORCH --> PARSE_SEC
LOAD_ORCH --> PARSE_FUND
LOAD_ORCH --> PARSE_FLOW
%% Market data
YAHOO_API --> FETCH_PRICES
YAHOO_API --> FETCH_FX
%% Ingestion → Database
PARSE_SEC --> T_TRANS
PARSE_FUND --> T_FUND
PARSE_FLOW --> T_CASH
FETCH_PRICES --> T_PRICE
FETCH_FX --> T_FX
%% Database views
T_TRANS --> V_HOLD
T_TRANS --> V_SUMMARY
T_FUND --> V_FBAL
%% Calculations ← Database
T_TRANS --> CALC_NAV
T_FUND --> CALC_FUND
T_CASH --> CALC_XIRR
T_PRICE --> CALC_NAV
T_FX --> CALC_NAV
T_FX --> CALC_FUND
T_FX --> CALC_XIRR
T_SNAP --> CALC_FUND
CALC_NAV --> CALC_ALLOC
CALC_FUND --> CALC_ALLOC
CALC_NAV --> CALC_HIST
CALC_FUND --> CALC_HIST
CALC_NAV --> CALC_RET
%% Calc → API
CALC_NAV --> API
CALC_FUND --> API
CALC_XIRR --> API
CALC_ALLOC --> API
CALC_HIST --> API
CALC_RET --> API
%% API → Frontend
API --> STAT
API --> PIE
API --> DONUT
API --> BAR
API --> LINE
API --> HEAT
API --> TABLE
%% Styling
classDef source fill:#3498DB,stroke:#2471A3,color:#fff,stroke-width:2px
classDef ingest fill:#E67E22,stroke:#CA6F1E,color:#fff,stroke-width:2px
classDef db fill:#2ECC71,stroke:#27AE60,color:#fff,stroke-width:2px
classDef calc fill:#9B59B6,stroke:#7D3C98,color:#fff,stroke-width:2px
classDef api fill:#1ABC9C,stroke:#16A085,color:#fff,stroke-width:2px
classDef viz fill:#E74C3C,stroke:#C0392B,color:#fff,stroke-width:2px
class MOOMOO_SEC,MOOMOO_FUND,MOOMOO_DEP,MOOMOO_WD,YAHOO_API source
class PARSE_SEC,PARSE_FUND,PARSE_FLOW,LOAD_ORCH,FETCH_PRICES,FETCH_FX ingest
class T_BROKER,T_TRANS,T_FUND,T_CASH,T_PRICE,T_FX,T_SNAP,V_HOLD,V_SUMMARY,V_FBAL db
class CALC_NAV,CALC_FUND,CALC_XIRR,CALC_ALLOC,CALC_HIST,CALC_RET calc
class API api
class STAT,PIE,DONUT,BAR,LINE,HEAT,TABLE vizDiagram 5 — Database Entity-Relationship Diagram¶
Shows the complete database schema with all tables, columns, constraints, and relationships.
erDiagram
BROKER {
SERIAL id PK
VARCHAR name UK "Moomoo, Tiger, WeBull"
TEXT description
TIMESTAMPTZ created_at
}
PORTFOLIO {
SERIAL id PK
VARCHAR name "Main Portfolio"
TEXT description
TIMESTAMPTZ created_at
}
TRANSACTION {
SERIAL id PK
INTEGER portfolio_id FK
INTEGER broker_id FK
VARCHAR ticker "AAPL, NVDA, VOO"
VARCHAR ticker_name "Apple Inc."
VARCHAR side "Buy, Sell, Short Sell"
VARCHAR asset_type "Stock, Option, ETF"
TIMESTAMPTZ order_date
DECIMAL order_price
DECIMAL order_qty
DECIMAL order_amt
VARCHAR order_type
TIMESTAMPTZ fill_date
DECIMAL fill_price
DECIMAL fill_qty
DECIMAL fill_amt
VARCHAR market "US, SG, HK"
VARCHAR currency "USD, SGD"
DECIMAL calculated_fees_sum
DECIMAL total_fees
TEXT notes
VARCHAR source_file
TIMESTAMPTZ created_at
}
FUND_TRANSACTION {
SERIAL id PK
INTEGER broker_id FK
VARCHAR side "Subscribe, Redeem"
VARCHAR fund_name
DECIMAL amount
DECIMAL units
VARCHAR currency
TIMESTAMPTZ order_time
VARCHAR source_file
TIMESTAMPTZ created_at
}
EXTERNAL_CASH_FLOW {
SERIAL id PK
INTEGER broker_id FK
VARCHAR flow_type "Deposit, Withdrawal"
DECIMAL amount
VARCHAR currency
DATE flow_date
VARCHAR source_file
TIMESTAMPTZ created_at
}
BALANCE_SNAPSHOT {
SERIAL id PK
INTEGER broker_id FK
VARCHAR snapshot_type "MMF, Securities, Cash, Total"
VARCHAR description
DECIMAL amount
VARCHAR currency
DATE snapshot_date
TIMESTAMPTZ created_at
}
DAILY_PRICE {
SERIAL id PK
VARCHAR ticker
DATE date UK "UNIQUE(ticker, date)"
DECIMAL open
DECIMAL high
DECIMAL low
DECIMAL close
DECIMAL adj_close
BIGINT volume
VARCHAR currency
VARCHAR source "yfinance"
TIMESTAMPTZ created_at
}
FX_RATE {
SERIAL id PK
VARCHAR from_currency
VARCHAR to_currency
DATE date UK "UNIQUE(from, to, date)"
DECIMAL rate
VARCHAR source "yfinance"
TIMESTAMPTZ created_at
}
BROKER ||--o{ TRANSACTION : "has many"
BROKER ||--o{ FUND_TRANSACTION : "has many"
BROKER ||--o{ EXTERNAL_CASH_FLOW : "has many"
BROKER ||--o{ BALANCE_SNAPSHOT : "has many"
PORTFOLIO ||--o{ TRANSACTION : "groups"Diagram 6 — Deployment & CI/CD Sequence¶
Shows the deployment workflow from code change to live application.
sequenceDiagram
autonumber
participant DEV as 💻 Developer<br/>(Windows)
participant GH as 📦 GitHub
participant EC2 as 🖥️ EC2 Instance<br/>(Amazon Linux)
participant SYS as 🔧 systemd
participant GUN as 🦄 Gunicorn
participant RDS as 🐘 RDS<br/>PostgreSQL
Note over DEV,GH: Code Change & Push
DEV->>DEV: Edit code locally
DEV->>GH: git push origin master
Note over DEV,EC2: Deployment
DEV->>EC2: SSH into EC2
EC2->>EC2: cd ~/portfolio_tracker
EC2->>GH: git pull origin master
GH-->>EC2: Latest code
EC2->>EC2: source venv/bin/activate
EC2->>EC2: pip install -r requirements.txt
Note over EC2,RDS: Database Update (if needed)
opt Schema changes
EC2->>EC2: flask --app app_portfolio_tracker_aurora init-db
EC2->>RDS: DROP + CREATE tables, views, indexes
RDS-->>EC2: Schema applied
end
opt CSV data reload
EC2->>EC2: flask --app app_portfolio_tracker_aurora load-csv
EC2->>RDS: INSERT INTO TRANSACTION, FUND_TRANSACTION, ...
RDS-->>EC2: Data loaded
end
opt Market data refresh
EC2->>EC2: flask --app app_portfolio_tracker_aurora fetch-prices
EC2->>EC2: flask --app app_portfolio_tracker_aurora fetch-fx-rates
end
Note over EC2,GUN: Service Restart
EC2->>SYS: sudo systemctl daemon-reload
EC2->>SYS: sudo systemctl restart portfolio-tracker
SYS->>GUN: ExecStart: gunicorn -w 4 -b 0.0.0.0:8080
GUN->>GUN: Fork 4 worker processes
GUN->>RDS: Test connection (health check)
RDS-->>GUN: Connection OK
Note over GUN: Application live on port 8080
SYS-->>EC2: Service active (running)Diagram 7 — CSV Ingestion Pipeline (Detailed)¶
Shows the detailed parsing flow for Moomoo broker files, including partial fill handling and timezone conversion.
flowchart TD
START(["flask --app app_portfolio_tracker_aurora load-csv"]) --> SCAN["Scan csv_data/ directory<br/>for all files"]
SCAN --> MATCH{"Match file<br/>patterns"}
MATCH -->|"*Securities*.csv"| SEC_HANDLER
MATCH -->|"*Funds*.csv"| FUND_HANDLER
MATCH -->|"*Deposit*.txt"| DEP_HANDLER
MATCH -->|"*Withdrawal*.txt"| WD_HANDLER
subgraph "Securities CSV Handler"
SEC_HANDLER["handle_moomoo_securities()"]
SEC_HANDLER --> SEC_READ["Read CSV with DictReader<br/>(UTF-8 BOM handling)"]
SEC_READ --> SEC_LOOP["For each row"]
SEC_LOOP --> SEC_STATUS{"Status =<br/>'Filled'?"}
SEC_STATUS -->|"No"| SEC_SKIP["Skip row"]
SEC_STATUS -->|"Yes"| SEC_CHECK{"Side & Ticker<br/>empty?"}
SEC_CHECK -->|"Yes"| SEC_PARTIAL["Partial fill continuation<br/>Attach to parent order"]
SEC_CHECK -->|"No"| SEC_PARSE["Parse order + fill data<br/>14 fee columns<br/>Asset type detection"]
SEC_PARSE --> SEC_TZ["convert_to_psql_timestamp()<br/>'Mar 28, 2026 01:21:45 ET'<br/>→ 2026-03-28T14:21:45+08:00"]
SEC_TZ --> SEC_TYPE["_detect_asset_type()<br/>NVDA → Stock<br/>VOO → ETF<br/>NVDA260327P140000 → Option"]
SEC_TYPE --> SEC_INSERT["INSERT INTO TRANSACTION"]
SEC_PARTIAL --> SEC_INSERT
end
subgraph "Funds CSV Handler"
FUND_HANDLER["handle_moomoo_funds()"]
FUND_HANDLER --> FUND_READ["Read CSV"]
FUND_READ --> FUND_STATUS{"Status =<br/>'Completed'?"}
FUND_STATUS -->|"Yes"| FUND_PARSE["Parse Amount/Units<br/>'212.61USD' → 212.61"]
FUND_PARSE --> FUND_INSERT["INSERT INTO FUND_TRANSACTION"]
end
subgraph "External Flow Handler"
DEP_HANDLER["handle_moomoo_external_flows()<br/>(flow_type='Deposit')"]
WD_HANDLER["handle_moomoo_external_flows()<br/>(flow_type='Withdrawal')"]
DEP_HANDLER --> TXT_READ
WD_HANDLER --> TXT_READ
TXT_READ["Read 3-line blocks<br/>Line 1: '3,800.00 SGD'<br/>Line 2: '27/01/2026'<br/>Line 3: 'Completed'"]
TXT_READ --> TXT_STATUS{"Status =<br/>'Completed'?"}
TXT_STATUS -->|"Yes"| TXT_INSERT["INSERT INTO EXTERNAL_CASH_FLOW"]
end
SEC_INSERT --> COMMIT["db.commit()"]
FUND_INSERT --> COMMIT
TXT_INSERT --> COMMIT
COMMIT --> DONE(["✅ Data loaded successfully"])
%% Styling
classDef start fill:#3498DB,stroke:#2471A3,color:#fff,stroke-width:2px
classDef handler fill:#E67E22,stroke:#CA6F1E,color:#fff,stroke-width:2px
classDef decision fill:#9B59B6,stroke:#7D3C98,color:#fff,stroke-width:2px
classDef action fill:#27AE60,stroke:#1E8449,color:#fff,stroke-width:2px
classDef skip fill:#E74C3C,stroke:#C0392B,color:#fff,stroke-width:2px
class START,DONE start
class SEC_HANDLER,FUND_HANDLER,DEP_HANDLER,WD_HANDLER handler
class SEC_STATUS,SEC_CHECK,FUND_STATUS,TXT_STATUS,MATCH decision
class SEC_PARSE,SEC_TZ,SEC_TYPE,SEC_INSERT,FUND_PARSE,FUND_INSERT,TXT_READ,TXT_INSERT,COMMIT action
class SEC_SKIP skipTechnology Stack Summary¶
| Layer | Technology | Purpose |
|---|---|---|
| Language | Python 3.13 | Application runtime |
| Web Framework | Flask 3.x | Routes, blueprints, templates, JSON API |
| WSGI Server | Gunicorn | Production multi-worker process management |
| Database | AWS RDS PostgreSQL 16 | Managed single-instance relational database |
| DB Driver | psycopg2 (RealDictCursor) | PostgreSQL wire protocol adapter |
| Charts | Chart.js (CDN) | Pie, doughnut, and bar charts |
| Financial Charts | TradingView Lightweight Charts (CDN) | Time-series NAV line charts |
| Templates | Jinja2 | Server-side HTML rendering |
| Market Data | yfinance (Yahoo Finance) | Historical prices and FX rates |
| XIRR Engine | scipy.optimize | Brent's method / Newton's method root finding |
| Timezone | pytz | Multi-timezone date conversion (ET, SGT, UTC) |
| Auth | Werkzeug | Password hashing (pbkdf2:sha256) |
| Configuration | python-dotenv | Environment variable loading from .env |
| Process Management | systemd | Daemon lifecycle, auto-restart, boot persistence |
| Infrastructure | AWS CloudFormation | VPC, subnets, EC2, RDS — infrastructure as code |
| Documentation | MkDocs Material + Mermaid | Architecture diagrams published on Cloudflare Pages |
| Source Control | Git + GitHub | Version control |