Securities Platform

1. Overview

A securities data platform built on TimescaleDB (PostgreSQL 16 + time-series extension) with a FastAPI REST layer. The database contains 224 GB of realistic financial data spanning 1,000 securities across 5 years (2020–2025).

Technology Stack

Variance Scenarios Embedded in Data

• Tech equities: +10–20% over the 5-year period
• Energy equities: -8–15%
• Government bond yields: +0.25–0.50% (prices down)
• High-yield corporate bonds: spread widening, prices -3–8%
• EM FX depreciation: 5–12%
• 2 stock splits (2:1), dividend payments on 5 equities
• 3 bond coupon payments, 2 bond maturities
• 1 issuer downgrade (BBB+ → BB), 1 upgrade (BBB- → BBB)

2. Data Summary

Scale Tables (224 GB)

Core Reference Tables

3. Project Structure

securities-platform/
├── api/
│   ├── main.py                  # FastAPI app entry point
│   ├── deps.py                  # Database session dependency
│   ├── models/
│   │   └── schemas.py           # Pydantic response schemas
│   └── routers/
│       ├── securities.py        # /api/securities endpoints
│       ├── portfolios.py        # /api/portfolios, /api/holdings
│       ├── variance.py          # /api/variance endpoints
│       └── reference.py         # /api/corporate-actions, /api/fx-rates
├── database/
│   ├── schema.sql               # Core table definitions (13 tables)
│   ├── schema_scale.sql         # Scale tables (tick_data, pricing_history, etc.)
│   ├── indexes.sql              # Performance indexes
│   └── loader.py                # CSV-to-PostgreSQL loader
├── generator/
│   ├── generate_data.py         # Original 120-security generator
│   └── generate_scale_data.py   # Scale generator (1,000 securities, 224 GB)
├── docker/
│   └── docker-compose.yml       # TimescaleDB service
├── data/
│   ├── schema/                  # Source schema files
│   └── generated/               # Generated CSV files
├── .env                         # Environment configuration
├── .env.example                 # Template
└── requirements.txt             # Python dependencies

4. Getting Started

Prerequisites

• Docker and Docker Compose installed
• Python 3.12+ with venv

Step 1: Start the Database

cd /Users/imac/securities-platform
docker compose -f docker/docker-compose.yml up -d

Verify it's running:

docker exec securities-db psql -U securities_user -d securities_platform \
  -c "SELECT pg_size_pretty(pg_database_size(current_database()));"

Step 2: Start the API Server

cd /Users/imac/securities-platform
source venv/bin/activate
uvicorn api.main:app --host 0.0.0.0 --port 8000

The API will be available at http://localhost:8000.

Step 3: Verify

curl http://localhost:8000/api/health
# {"status":"ok","service":"securities-platform","version":"1.0.0"}

Interactive API Docs

When the API is running, FastAPI provides auto-generated interactive documentation:

• Swagger UI: http://localhost:8000/docs
• ReDoc: http://localhost:8000/redoc

5. API Reference

Base URL: http://localhost:8000

curl http://localhost:8000/api/health

# Response:
{"status": "ok", "service": "securities-platform", "version": "1.0.0"}

Securities

# All equities (first 10)
curl "http://localhost:8000/api/securities?security_type=EQUITY&limit=10"

# Technology sector in USD
curl "http://localhost:8000/api/securities?sector=Technology&currency=USD"

# Page 2
curl "http://localhost:8000/api/securities?limit=100&offset=100"

curl http://localhost:8000/api/securities/SEC-0001

# Response:
{
  "security_id": "SEC-0001",
  "isin": "US0000000001",
  "ticker": "TECH_A",
  "security_name": "TechCorp Alpha",
  "security_type": "EQUITY",
  "asset_class": "Equity",
  "currency": "USD",
  "exchange": "NYSE",
  "issuer_id": "ISS-001",
  "status": "ACTIVE"
}

Portfolios

curl http://localhost:8000/api/portfolios

# PORT-001 holdings at period 1
curl "http://localhost:8000/api/holdings?portfolio_id=PORT-001&period=1"

# Period 2 top holdings by market value
curl "http://localhost:8000/api/holdings?period=2&limit=20"

Variance Attribution

curl http://localhost:8000/api/variance/portfolio/PORT-001

# Response:
{
  "summary": {
    "portfolio_id": "PORT-001",
    "total_mv_start": 125000000.00,
    "total_mv_end": 131500000.00,
    "total_price_effect": 4200000.00,
    "total_qty_effect": 1100000.00,
    "total_fx_effect": -300000.00,
    "total_corp_action_effect": 1500000.00,
    "total_pnl": 6500000.00
  },
  "details": [ ... ]
}

curl "http://localhost:8000/api/variance/top-movers?limit=5"

Reference Data

curl http://localhost:8000/api/corporate-actions
curl "http://localhost:8000/api/corporate-actions?action_type=DIVIDEND"

curl http://localhost:8000/api/fx-rates
curl "http://localhost:8000/api/fx-rates?from_currency=GBP"

6. Direct Database Access

Connect via psql

# Through Docker
docker exec -it securities-db psql -U securities_user -d securities_platform

# From host
psql -h localhost -p 5432 -U securities_user -d securities_platform
# Password: securities_pass

Connection String

postgresql://securities_user:securities_pass@localhost:5432/securities_platform

Useful Queries

Database Info

-- Database size
SELECT pg_size_pretty(pg_database_size(current_database()));

-- Row counts for all tables
SELECT schemaname, relname, n_live_tup
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY n_live_tup DESC;

-- Table sizes
SELECT tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename))
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Securities & Pricing

-- Securities by type
SELECT security_type, count(*) FROM securities_master GROUP BY security_type;

-- Daily price for a security
SELECT price_date, close_price, volume
FROM pricing_history
WHERE security_id = 'SEC-0001'
ORDER BY price_date DESC
LIMIT 10;

-- Intraday ticks for a security on a specific day
SELECT tick_time, price, bid, ask, volume
FROM tick_data
WHERE security_id = 'SEC-0001'
  AND tick_time >= '2025-03-28 09:30:00'
  AND tick_time <  '2025-03-28 16:00:00'
ORDER BY tick_time;

Portfolio & Holdings

-- Portfolio holdings on a date
SELECT h.security_id, sm.security_name, h.quantity, h.market_value, h.weight
FROM holdings_history h
JOIN securities_master sm ON h.security_id = sm.security_id
WHERE h.portfolio_id = 'PORT-001'
  AND h.as_of_date = '2025-03-31'
ORDER BY h.market_value DESC
LIMIT 20;

-- Quarterly variance for a portfolio
SELECT quarter_label, SUM(total_pnl) as total_pnl,
       SUM(price_effect) as price_eff, SUM(fx_effect) as fx_eff
FROM variance_quarterly
WHERE portfolio_id = 'PORT-001'
GROUP BY quarter_label
ORDER BY quarter_label;

Transactions

-- Top 10 trades by value
SELECT t.security_id, sm.security_name, t.txn_type,
       t.quantity, t.price, t.quantity * t.price as trade_value
FROM transactions t
JOIN securities_master sm ON t.security_id = sm.security_id
ORDER BY t.quantity * t.price DESC
LIMIT 10;

7. Database Schema

Core Tables schema.sql

Scale Tables schema_scale.sql

Indexes

-- Core indexes (indexes.sql)
idx_sm_type, idx_sm_currency, idx_sm_issuer, idx_sm_isin, idx_sm_cusip, idx_sm_sedol
idx_hp1_security, idx_hp1_portfolio, idx_hp2_security, idx_hp2_portfolio
idx_pp1_security, idx_pp2_security
idx_txn_security, idx_txn_portfolio, idx_txn_date
idx_cr_issuer, idx_ca_security, idx_rm_security, idx_rm_portfolio
idx_vs_portfolio, idx_vs_security

-- Scale indexes
idx_ph_sec_date   ON pricing_history(security_id, price_date)
idx_ph_date       ON pricing_history(price_date)
idx_ph_sid        ON pricing_history(security_id)
idx_td_sec        ON tick_data(security_id, tick_time DESC)
idx_hh_port_date  ON holdings_history(portfolio_id, as_of_date)
idx_hh_sec        ON holdings_history(security_id)
idx_vq_port       ON variance_quarterly(portfolio_id, quarter_label)

8. Configuration

Environment Variables (.env)

Python Dependencies

fastapi==0.115.6
uvicorn[standard]==0.34.0
sqlalchemy==2.0.36
psycopg2-binary==2.9.10
pandas==2.2.3
numpy==2.2.1
openpyxl==3.1.5
alembic==1.14.0
python-dotenv==1.0.1

9. Regenerating Data

To regenerate from scratch (warning: takes ~6 hours for full dataset):

cd /Users/imac/securities-platform
source venv/bin/activate

# Step 1: Generate original 120-security base data
python generator/generate_data.py

# Step 2: Load base data into database
python database/loader.py

# Step 3: Generate scale data (1,000 securities, 224 GB)
python generator/generate_scale_data.py

The scale generator is resumable — if interrupted, it will skip completed phases and continue tick data from the last completed security.