DEV Community

Cover image for I Overengineered My Personal Finances into a Local-First Lakehouse (DuckDB + Polars + SQLite)
Sudharshan TK
Sudharshan TK

Posted on AI-assisted

I Overengineered My Personal Finances into a Local-First Lakehouse (DuckDB + Polars + SQLite)

#backend #data #database #python

By professional training, I am a Chartered Accountant based in India.

By night and obsession, I fell completely down the rabbit hole of Python, data engineering, and quantitative modeling.

That sounds like an unusual combination, but in practice, Chartered Accountancy and data engineering share almost the exact same DNA:

Finance / Audit Instinct Engineering Analogue
Evidence Source lineage & cryptographic content hashes
Reconciliation Data quality controls & invariant checks
Classification Canonical semantic modeling
Accounting Period Temporal grain & windowing
Audit Trail Run manifests & provenance logs
Financial Statements Serving contracts & dimensional data marts

Accounting makes you deeply uncomfortable with totals that are merely "plausible". If a balance sheet doesn't balance, or if a portfolio valuation cannot be traced back to the underlying statement byte, it is wrong.

About a year ago, I reached my breaking point with consumer fintech.

🇮🇳 The Problem: The Chaos of Indian Personal Finance

If you manage personal finances in India, your data is scattered across incompatible walled gardens:

  • Mutual Funds: CAMS and KFintech CAS (Consolidated Account Statements) in password-protected PDFs.
  • Domestic Equities: Zerodha, Groww, or Angel One trade books and contract notes.
  • Retirement: EPFO passbooks with annual interest crediting delays, plus PPF and NPS statements.
  • Foreign Equities: US stocks held via INDmoney or Vested, requiring complex RBI LRS tracking, TCS deductions, and Schedule FA disclosures for Indian tax returns (ITR-2 / ITR-3).
  • Taxation: Computing Section 112A Long Term Capital Gains (LTCG) with grandfathering rules, the ₹1.25 Lakh exemption limit, and strict FIFO (First-In, First-Out) lot depletion across bonus shares and stock splits.

Most people try to solve this by uploading their most sensitive financial documents to venture-backed fintech apps. But these apps frequently:

  1. Harvest your financial data to cross-sell personal loans and credit cards.
  2. Get acquired or shut down without warning (the fate of Mint and countless others).
  3. Oversimplify the math, giving you pretty pie charts while butchering actual realized capital gains or currency attribution.

Instead of accepting that, I spent months doing what any data-obsessed CA would do:

I built an open-source, local-first medallion lakehouse and quantitative decision engine for an audience of exactly one person (myself).

It’s called Personal Finance ETL, and it runs 100% locally on my machine silicon with zero telemetry and zero cloud bills.

📊 Real Production Metrics: Not a Five-Row Demo

Open source projects often demo toy datasets with 10 rows. Here is the actual scale of my personal financial production baseline:

Metric Production Observation
Raw Source Artifacts 1,600+ statements, trade books, and passbooks
Investment Instruments 147 active & historical workloads
Published Rows 449,000+ Silver & Gold warehouse records
Registered Contracts 66 explicit contracts (20 Bronze, 24 Silver, 22 Gold)
Fresh Ingestion + Rebuild 40–45 seconds (entire 4-year history from scratch)
Warm-Cache Pipeline Run 23.95 seconds
Analytical Query Latency 5 to 15 milliseconds on DuckDB
Monte Carlo Simulation 38 milliseconds for 10,000 trajectories via Numba

🏗 The Architecture: Why Two Separate Databases?

When building local-first, you don't have AWS credits or Snowflake auto-scaling to mask architectural flaws. You are sharing resources with your operating system, browser, and IDE.

Early on, I realized that transactional state and analytical querying need two fundamentally different engines:

┌─────────────────────────────────────────────────────────────┐
│                      Your Local Machine                     │
│                                                             │
│  CAS PDFs, Zerodha CSVs, EPF Passbooks, INDmoney Statements │
│         │                                                   │
│         ▼ (SHA-256 Content-Addressable Dedup)               │
│  ┌───────────────────────────────┐                          │
│  │   SQLite Control Plane        │ ── ACID Run Manifests    │
│  │   (WAL Mode, zero analytics)  │ ── Pipeline State Logs   │
│  └──────────────┬────────────────┘ ── Hot Backup Snapshots  │
│                 │                                           │
│                 ▼                                           │
│  ┌───────────────────────────────┐                          │
│  │      Polars Engine            │ ── Vectorized FIFO Match │
│  │   (LazyFrames, Zero-Copy)     │ ── FX RBI Attribution    │
│  └──────────────┬────────────────┘                          │
│                 │ (Arrow IPC Stream)                        │
│                 ▼                                           │
│  ┌───────────────────────────────┐                          │
│  │    DuckDB OLAP Warehouse      │                          │
│  │  ├─ Bronze: Raw Ingestion     │ ── Analytical Lakehouse  │
│  │  ├─ Silver: Canonical Facts   │ ── 66 Strict Contracts   │
│  │  └─ Gold:   Decision Marts    │ ── Sub-15ms Reporting   │
│  └───────────────────────────────┘                          │
│                 │                                           │
│                 ▼                                           │
│  ┌───────────────────────────────┐                          │
│  │  Numba Stochastic Simulation  │ ── 10k Paths in 38ms     │
│  └───────────────────────────────┘                          │
└─────────────────────────────────────────────────────────────┘
Enter fullscreen mode Exit fullscreen mode

1. SQLite: The Transactional Control Plane

  • Role: Operational watchdog.
  • Runs in WAL (Write-Ahead Logging) mode.
  • Stores zero financial balances or analytics.
  • Manages raw statement SHA-256 hashes, pipeline run manifests, and audit state.
  • If an ingestion process crashes or a bank re-exports the same statement with a different file name, SQLite intercepts it cleanly without polluting the warehouse.

2. DuckDB: The Columnar Analytical Warehouse

  • Role: OLAP analytics and dimensional reporting.
  • Organizes financial data into a classical Medallion Architecture:
    • Bronze: Raw tabular mirrors of bank exports, contract notes, and EPF ledgers. Totally immutable.
    • Silver: Canonical financial facts. Ingestion-specific quirks are stripped. Multi-currency trades are matched with historical daily exchange rates, and investment lots are tracked via FIFO depletion.
    • Gold: Ready-to-query dimensional marts for net worth timelines, tax realization schedules, and benchmark shadow portfolios (comparing real returns against Nifty 50 or S&P 500 shadow indices).

🛡 Grounded in Real Code: The 66 Data Contracts

In our warehouse, contracts aren't vague concepts—they are strongly typed Python models and strict DuckDB DDL schemas.

Here is the exact in-memory TaxLot model that represents active acquisition tranches (domain/investments/contracts.py):

@dataclass(frozen=True, slots=True)
class TaxLot:
    """Represents a single active purchase lot in memory."""

    date: dt.date | None
    qty: float
    price: float
    bm_buy: float | None
    shadow_qty: float
    price_local: float = 0.0
    fx_rate_buy: float = 1.0
    currency_id: str | None = None
    bm_buy_local: float | None = None
    purchase_id: str | None = None
    lot_id: str | None = None
    lot_source_type: str = "PURCHASE"
Enter fullscreen mode Exit fullscreen mode

And here is the corresponding DuckDB Silver contract for realized sale events (infrastructure/warehouse/schema/silver.py), capturing separate asset PnL, foreign exchange PnL, and tax rates:

CREATE TABLE IF NOT EXISTS silver.f_Investment_Realized_Events (
    Realized_Event_ID TEXT PRIMARY KEY,
    Sale_ID TEXT NOT NULL,
    Lot_ID TEXT NOT NULL,
    Purchase_ID TEXT,
    ISIN TEXT,
    Acquisition_Date DATE,
    Disposal_Date DATE,
    FY TEXT,
    Quantity_Disposed DOUBLE,
    Acquisition_Price DOUBLE,
    Disposed_Cost_Basis DOUBLE,
    Sale_Price DOUBLE,
    Sale_Proceeds DOUBLE,
    Realized_Gain_Loss DOUBLE,
    Holding_Type TEXT,       -- LTCG vs STCG
    Tax_Type TEXT,
    Tax_Subtype TEXT,
    Lot_Source_Type TEXT,
    Currency_ID TEXT,
    Asset_PnL_Local DOUBLE,  -- Gain in local currency (e.g. USD)
    Asset_PnL DOUBLE,        -- Converted asset gain in INR
    Forex_PnL DOUBLE,        -- Currency fluctuation gain in INR
    Applied_Rate DOUBLE,     -- Tax rate resolved from macro parameters
    Estimated_Tax DOUBLE,
    UNIQUE(Sale_ID, Lot_ID)
);
Enter fullscreen mode Exit fullscreen mode

⚡ Real FIFO Math: Separating Asset PnL from Forex PnL

When selling foreign equities (e.g. US stocks held via INDmoney), computing capital gains is notoriously tricky. If the stock went up 20% in USD, but the USD/INR exchange rate went from ₹75 to ₹84, what part of your gain is stock return versus currency appreciation?

Here is the exact math running in our FIFO depletion engine (domain/investments/lots/fifo.py):

# From FIFOPortfolio.sell() in domain/investments/lots/fifo.py
while rem > 0 and self._active_lots:
    lot = self._active_lots[0]
    consumed = min(rem, lot.qty)

    if lot.price <= 0:
        pnl = 0.0
        asset_pnl_local = 0.0
        asset_pnl = 0.0
        forex_pnl = 0.0
    else:
        pnl = (price - lot.price) * consumed
        if lot.currency_id and lot.currency_id != self.default_currency_id:
            # Asset PnL is the pure equity price change in USD, converted at disposal FX
            asset_pnl_local = (price_local - lot.price_local) * consumed
            asset_pnl = asset_pnl_local * fx_rate_sell
            # Forex PnL is the currency movement on the original acquisition cost
            forex_pnl = (lot.price_local * (fx_rate_sell - lot.fx_rate_buy)) * consumed
        else:
            asset_pnl_local = pnl
            asset_pnl = pnl
            forex_pnl = 0.0

    realized_event_id = generate_deterministic_id(
        "REALIZED",
        {"Sale_ID": sale_id, "Lot_ID": lot.lot_id, "Consumed": consumed}
    )
Enter fullscreen mode Exit fullscreen mode

Every penny of gain is deterministically decomposed into asset performance versus currency movement, completely eliminating tax ambiguity.

🚀 Accelerating Monte Carlo Simulations: 14.2s ➔ 38ms with Numba

In India, retirement (FIRE) planning cannot rely on generic US rules like the "4% Trinity rule". Real inflation in India typically runs at 6–7%, and sequence-of-returns risk during a market crash can devastate a corpus if it hits in the first few years of retirement.

To model this, the simulation engine implements:

  • A 3-regime Markov chain (Bull, Bear, Stagnation)
  • Human capital shocks (unemployment / income interruption probabilities)
  • Guyton-Klinger dynamic withdrawal guardrails
  • Ornstein-Uhlenbeck mean-reverting inflation dynamics

Here is the actual Numba-compiled simulation kernel (domain/fire/simulation.py):

@njit(fastmath=True, parallel=True, cache=True)
def _run_mc_simulations_numba(
    pv_arr: npt.NDArray[np.float64],
    pmt_total_arr: npt.NDArray[np.float64],
    fv_total_arr: npt.NDArray[np.float64],
    burn_total_arr: npt.NDArray[np.float64],
    inf_rates: npt.NDArray[np.float64],
    seed_ints: npt.NDArray[np.int32],
    gens: Any,
    iterations: int,
    max_months: int,
    vol_r: float,
    mean_r: float,
    swr: float,
    current_age_months_arr: npt.NDArray[np.int32],
    target_lifespan_months: int,
    tm_flat: npt.NDArray[np.float64],
    state_bull_params: npt.NDArray[np.float64],
    state_bear_params: npt.NDArray[np.float64],
    state_stag_params: npt.NDArray[np.float64],
    hc_shock_prob: float,
    hc_shock_min: int,
    hc_shock_max: int,
    ...
) -> tuple[npt.NDArray[np.float64], ...]:
    n_rows = len(pv_arr)
    # Evaluates 10,000 trajectories in parallel across CPU cores
    for i in prange(n_rows):
        # Markov regime updates + stochastic returns + adaptive withdrawals
        ...
Enter fullscreen mode Exit fullscreen mode

By compiling down to native machine code with LLVM and utilizing parallel=True via OpenMP/TBB, evaluating 10,000 trajectories over 40 years (4.8 million state evaluations) dropped from 14.2 seconds down to 38 milliseconds.

🤖 A Pragmatic View on AI & Engineering Rigor

In 2026, building a large software project as a solo developer almost certainly involves AI. I used Claude and Gemini throughout development—primarily as a fast pair-programmer for scaffolding repetitive test fixtures, drafting docstrings, and deciphering obscure mypy/ruff errors.

However, financial software has a unique, unforgiving property: it is binary.

Either a balance sheet reconciles to the exact rupee, or it is wrong. Either a foreign capital gains lot matches its historical RBI exchange rate, or the tax filing is incorrect. A 95% statistically plausible answer from an LLM is a 100% defective accounting system.

That's why I kept AI strictly at the perimeter (documentation, test scaffolding, and IDE diagnostics). The core system is governed entirely by deterministic software engineering:

  • 66 Explicit Data Contracts: Ingestion stops cold if schemas or types drift.
  • Zero Loose Types: The entire codebase passes mypy --strict and pyright in strict mode with zero type ignores.
  • Auditable Lineage: Every single number displayed in a Gold mart can be traced back through Silver facts to the exact SHA-256 hash of the original bank statement.

My working rule for financial architecture:

A conversational AI interface over an unverified data model doesn't fix ambiguity—it just hallucinates with confidence.

Trust comes from deterministic data contracts, explicit reconciliation controls, and reproducible code.

📦 How to Try It

The project is packaged with uv and available on PyPI:

# Install via pip
pip install personal-finance-etl

# Launch the desktop UI
personal-finance-etl-gui
Enter fullscreen mode Exit fullscreen mode

Or clone the repo:

git clone https://github.com/tks18/personal-finance-etl.git
cd personal-finance-etl
uv sync
uv run poe gui
Enter fullscreen mode Exit fullscreen mode

Links & Docs:

💬 Conclusion

Building an enterprise-grade lakehouse with 66 data contracts, dual databases, and compiled Numba kernels just to track my own investments and expenses might be textbook overengineering.

But it works.

I have complete data sovereignty, mathematically verifiable tax numbers, sub-15ms SQL queries, and zero SaaS subscription anxiety.

I'd love to hear from other developers and finance folks in the comments: How do you manage your personal financial data? Have you built your own local tooling, or are you still relying on spreadsheets?

Top comments (0)