Dual-Database Analytical Strategy

🗄️ Dual-Database Analytical Strategy

Kryptofolio operates as a local-first, single-user application. To balance the need for bulletproof transactional integrity with computationally intensive mathematical operations, the backend implements a Dual-Database Architecture:

1. Transactional Ledger (SQLite): Fast, ACID-compliant persistence for transactions, settings, and encrypted credentials.
2. Analytical Engine (DuckDB): Columnar, vectorized OLAP engine for complex Window Functions, FIFO lot matching, and portfolio risk calculations.
3. Historical Storage (Apache Parquet): Local columnar storage for multi-year daily asset prices and FX rates, partitioned by year and month.

---

🏛️ Storage & Calculation Pipeline

flowchart TD
    subgraph DiskStorage ["Disk Storage Layer"]
        SQLite[("SQLite Ledger\n(kryptofolio_ledger.db)")]
        Parquet[("Apache Parquet Time-Series\n(year=YYYY/month=MM)")]
    end

    subgraph DuckDBEngine ["DuckDB Analytical Engine (In-Memory / File-Backed)"]
        ZeroCopy["ATTACH 'kryptofolio_ledger.db' AS ledger\n(TYPE SQLITE)"]
        VectorChain["Vectorized Derived FIFO Chain\n(m_calculated_tax_lots)"]
        MetricsViews["Portfolio Valuation & Risk Views\n(Vol 30d, Sharpe, Alpha/Beta)"]
    end

    subgraph ApplicationService ["Application & Synchronization Layer"]
        FreshnessService["FifoChainFreshnessService\n(Coalesced Rebuild Protocol)"]
        Materializer["FifoMaterializerService\n(Set Reconciliation into SQLite)"]
        UseCases["Backend Use Cases & Hono RPC"]
    end

    SQLite <--> ZeroCopy
    Parquet --> ZeroCopy
    ZeroCopy --> VectorChain
    VectorChain --> MetricsViews
    MetricsViews --> FreshnessService
    FreshnessService --> Materializer
    Materializer --> SQLite
    MetricsViews --> UseCases

---

⚙️ How the Engines Cooperate

1. Zero-Copy SQLite Attachment

DuckDB includes native support for SQLite. Upon engine startup, DuckDB attaches directly to the SQLite ledger file:

ATTACH 'kryptofolio_ledger.db' AS ledger (TYPE SQLITE);

This operation requires zero memory duplication: DuckDB directly queries SQLite’s B-Trees while executing analytical SQL queries (such as Window Functions) using its vectorized SIMD columnar execution engine.

2. The Rebuild & Materialization Protocol

Rather than recomputing the multi-year FIFO queue on every HTTP read request, Kryptofolio materializes derived analytical results into physical tables (m_<name>):

1. Freshness Detection (FifoChainFreshnessService): Tracks whether any new transactions or user overrides were saved to SQLite. If the state is fresh, read queries execute instantly without recalculation.
2. Coalesced In-Flight Runs: If multiple concurrent requests trigger a rebuild, they share a single in-flight Promise, preventing duplicate CPU load.
3. Set Reconciliation (FifoMaterializerService): Once DuckDB calculates the new state, the materializer reconciles the SQLite ledger tables (inserting newly matched lots, updating partially filled lots, and soft-deleting retired ones) rather than doing naive destructive wipes.