Asset Allocation (AA) — Overview
Two PostgreSQL tables — a general instrument registry and a general time-series price
table — powering frontend/src/pages/AssetAllocation.jsx (not yet rewired to the
current schema — see Status below): "what would starting amount X, split across
stocks/bonds/cash, invested in year Y for N years, be worth today". Modelled on
wealthdashboard.app's "Asset Allocation Visualizer"
(a Plotly Dash demo driven by the NYU Stern/Damodaran US stocks/bonds/bills/inflation
dataset back to 1928), but built on Indian market data instead, and on a genuinely
open-ended instrument list rather than a fixed handful of series.
This is an educational/analytical tool only — no live account linking, no per-user data, no auth.
For the full schema-evolution history, non-obvious gotchas (Alembic autogenerate
silently not proposing a DROP TABLE for a retired model, unnamed FK constraints
breaking downgrade(), ...), and exactly what's stale vs current, see
backend/AA/CLAUDE.md — that file is the actively-maintained source of truth for
this package; this page is a lighter-weight overview alongside it.
Package File Map
| File | Role |
|---|---|
AA/models.py | SQLAlchemy models — AAsset (te_asset table), AAPrice (te_price table) |
AA/seed_financial_data_2000_2026.py | Idempotent seed — Savings/FD/Inflation, from a user-supplied workbook |
AA/data/India_Financial_Data_2000-2026.xlsx | That workbook, copied into the repo as durable evidence (its own "Sources & References" sheet lists all underlying citations) |
AA/allocation_engine.py | Pure functions — backtest(), summary_stats() — no DB/FastAPI import. Stale: still queries the retired wide-table shape, not yet rewritten against te_price |
app/api/aa_routes.py | FastAPI routes. Disabled — commented out of app/main.py's router registration since it still imports a retired model class |
tests/test_aa_models.py, tests/test_aa_allocation_engine.py | pytest — schema constraints + CAGR/backtest math |
frontend/src/pages/AssetAllocation.jsx | The visualizer page (/asset-allocation) — stale, built against the retired schema |
Why a separate declarative base
AA/models.py defines its own AABase(DeclarativeBase) instead of reusing the shared
app.database.session.Base, so alembic --autogenerate never proposes changes against
the pre-existing hand-mapped production tables (scr_master, scr_nseeq_eod, ...) that
Alembic did not create and does not own. Same rationale as strategies/models.py (see
its own overview doc).
alembic/env.py imports every package's base and unions their table names into
_OWNED_TABLES for the include_object filter — any future package that wants Alembic
autogenerate follows the same pattern: its own base, added as another entry.
Schema
te_asset — instrument registry
One row per tracked instrument, named te_asset (not aa_asset) because nothing about
its shape is Asset-Allocation-specific — any future package needing a generic
(instrument, date) -> price/rate series can reuse it directly.
| Column | Type | Notes |
|---|---|---|
id | SERIAL PK | |
code | TEXT UNIQUE NOT NULL | 'SAVINGS_RATE_SBI', 'FD_RATE_SBI_1Y', 'CPI_INFLATION_INDIA', ... |
name | TEXT NOT NULL | |
asset_class | TEXT NOT NULL | CHECK IN ('equity','precious_metal','fixed_income','cash','economic_data') — UI-section grouping; economic_data is for context/benchmark series (Inflation) that aren't themselves an allocation choice |
sub_type | TEXT nullable | free-text finer grouping (savings, fd, cpi, large_cap, ...) |
value_kind | TEXT NOT NULL | CHECK IN ('level','rate_pct') — 'level': te_price.close is a price/index point, compute returns from consecutive values. 'rate_pct': close is the rate already (FD %, savings %) — never diffed, looked up point-in-time instead |
unit | TEXT nullable | '%', 'INR/10g', 'USD/oz', ... |
source_title, source_url, source_publisher, source_retrieved_date, source_note | this asset's default citation | |
created_at | TIMESTAMPTZ NOT NULL DEFAULT now() |
te_price — one row per (asset, date) observation
| Column | Type | Notes |
|---|---|---|
asset_id | INTEGER FK -> te_asset.id, part of PK | |
price_date | DATE, part of PK | not necessarily daily — see point-in-time lookup below |
close | NUMERIC(14,4) NOT NULL | |
open, high, low | NUMERIC(14,4) nullable | null for rate-only series with no OHLC |
row_note | TEXT nullable | per-row source override, or a judgment-call explanation when a source row wasn't a single clean number |
created_at | TIMESTAMPTZ NOT NULL DEFAULT now() |
Point-in-time lookup for value_kind='rate_pct' assets — the query is "most recent
row on or before the date you want", not an exact-date match:
SELECT close FROM te_price
WHERE asset_id = :id AND price_date <= :query_date
ORDER BY price_date DESC LIMIT 1;
Data currently loaded
| Asset | asset_class | Years | Notes |
|---|---|---|---|
SAVINGS_RATE_SBI | cash | 2000-2025 (26 rows, no gaps) | This is what "Cash %" in the visualizer is meant to represent — not FD, not a repo-rate proxy |
FD_RATE_SBI_1Y | fixed_income | 2000-2011, 2019, 2023-2025 (16 rows) | Several years skipped — the source itself marks them unconfirmed/N/A; never estimated |
CPI_INFLATION_INDIA | economic_data | 2000-2025 (26 rows) | World Bank/OECD CPI annual %, chosen as the one continuous series spanning the full range (MOSPI's own domestic series changed methodology in 2014) |
All from AA/data/India_Financial_Data_2000-2026.xlsx, seeded via
seed_financial_data_2000_2026.py. Every value is dated Dec 31 of its year
(one row per calendar year, matching how the source itself is shaped — not the sparser
"only on change" pattern the schema also supports). Several source rows gave a range or
multiple in-year revisions instead of one number; each such judgment call is recorded in
that row's row_note rather than hidden in parsing code — see AA/CLAUDE.md rule 6a
for the full reasoning and a known dating-convention quirk (a mid-year point-in-time
query resolves to the prior year's row until Dec 31 actually lands).
Not loaded yet: Nifty/Midcap/Smallcap equity indices (to be sourced from
NSEEOD/NSEIEOD directly, not re-derived from a CSV), MCX Gold/Silver spot, and
XAUUSD/XAGUSD (for a Gold-Silver Ratio — a plain join on price_date, no extra table
needed).
Status — what's stale vs current
The schema (te_asset/te_price) and the Savings/FD/Inflation data in it are current.
allocation_engine.py, app/api/aa_routes.py, and the frontend page are not — they
were built against an earlier wide-table schema (aa_annual_return) that no longer
exists, and haven't been rewritten yet. aa_routes.py is commented out of
app/main.py's router registration for exactly this reason (importing it used to crash
the whole backend at startup, not just the AA feature). Rewriting them — including how
allocation_engine.py's CAGR/backtest math should query te_price (a self-join /
window function over 'level' assets) instead of reading pre-computed columns — is
pending further design.
Running Locally
See Local CLI for the exact commands to migrate, seed, and test this
against your local tedb.