Global FIFO vs. Custody Double-Entry Ledger

⚖️ Global FIFO vs. Custody Double-Entry Ledger

In cryptocurrency tracking, accounting engines routinely conflate two completely separate questions:

1. Taxation FIFO: Which acquisition lot does a sale consume? (Determines legal capital gains or losses).
2. Custody Allocation: Which account or hardware wallet physically holds the balance right now?

Answering both questions using the same data structure results in severe calculation bugs. This page explains how Kryptofolio permanently decoupled fiscal lot consumption from account custody.

---

🚫 The 73 Phantom Disposals Problem

In earlier versions of crypto accounting tools (and during Kryptofolio’s early development), transfers were treated as disposals and new acquisitions:

• A WITHDRAWAL from Kraken to a hardware Ledger closed a real tax lot as if it were a SELL.
• The matching DEPOSIT on the other end created a brand-new tax lot with zero cost basis.

Measured on real test data, this flaw produced:

• 73 phantom disposal events from zero actual SELL transactions.
• Fabricated 64 zero-cost-basis lots out of 96, artificially inflating taxable gains by tens of thousands of euros.
• Inverted gains into losses due to sign inconsistencies across exchange CSV exports.

---

⚖️ Two Separate Orderings That Never Merge

Spanish tax regulations (AEAT IRPF) mandate global, per-asset FIFO: when you sell an asset, you must consume your oldest acquired lot of that asset regardless of which account or wallet acquired it or which account executes the sale.

Attributing custody by splitting lots would alter their acquisition order and silently violate tax law.

TAXATION FIFO                             CUSTODY DOUBLE-ENTRY LEDGER
─────────────────────────────────────     ─────────────────────────────────────────
Scope:    Global per asset                Scope:    Per (Account, Asset)
Orders:   acquisition_timestamp           Orders:   acquisition_timestamp
Drives:   Which lot a SALE consumes       Drives:   Which account holds what quantity
Effect:   Fiscal (Capital Gain / Loss)    Effect:   None (Internal provenance only)
View:     v_fifo_matches (m_*)            View:     v_lot_custody_allocation

[!CAUTION] Lot Invariant: An acquisition lot is never split into new rows when moved between accounts. One acquisition remains one row with its original acquisition_timestamp and unit_cost_fiat forever. Internal transfers only alter custody entries.

---

📖 The Custody Double-Entry Ledger

Instead of trying to pair transfer legs with fragile heuristic time-windows (which fail when transactions span hours, cross network delays, or have gas fee deductions), Kryptofolio implements a strict double-entry custody ledger:

Schema: lot_custody_entries

Column	Type	Description
id	TEXT (UUID)	Primary Key.
tax_lot_id	TEXT (UUID)	Foreign Key pointing to the immutable acquisition lot.
account_id	TEXT (UUID)	Foreign Key identifying the physical wallet or exchange account.
qty_delta	TEXT	Signed numerical delta (+ for deposits, - for withdrawals).

When an asset is transferred from Kraken to a Cold Storage Ledger:

1. qty_delta: -0.5 BTC is appended for account_id: Kraken.
2. qty_delta: +0.5 BTC is appended for account_id: Cold_Ledger.
3. The underlying tax_lot (0.5 BTC acquired on 2023-01-15 at €20,000/BTC) remains completely untouched.

---

🦆 DuckDB Read-Side Vectorized Views

The analytical engine compiles the two orderings through a directed acyclic graph (DAG) of SQL views:

flowchart TD
    Raw["ledger.spot_transactions"] --> Flattened["v_flattened_fifo_events"]
    Flattened --> Acq["v_acquisitions (Buys, Staking, Airdrops)"]
    Flattened --> Disp["v_disposals (Sells, Swaps, Spends)"]

    Acq & Disp --> FifoMatches["v_fifo_matches\n(Cumulative-interval overlap join)"]
    FifoMatches --> TaxLots["v_calculated_tax_lots\n(Materialized into m_calculated_tax_lots)"]

    TaxLots --> Custody["v_lot_custody_allocation\n(Tracks physical account balances)"]

Because these views are materialized and cached (m_calculated_tax_lots), the system recalculates them in one batch upon ingestion rather than evaluating expensive joins on every page load.