Ingestion Normalization Pipeline

🧹 Ingestion Normalization Pipeline

Every transaction imported into Kryptofolio — whether through the browser CSV/XLSX wizard or API endpoints — is processed through a single, pure, deterministic function:

prepareIngestionRows() (packages/core-domain/src/domain/services/normalizer/ingestionPipeline.ts).

---

🔄 The 4-Step Pipeline Flow

flowchart LR
    A["1. classifyCustodyMovement\n(normalize direction & sign)"] --> B["2. applyProfileToRow\n(per leg, per source format)"]
    B --> C["3. aggregateRows\n(merge legs sharing an operation)"]
    C --> D["4. resolveTradeDirection\n(BUY / SELL / SWAP resolution)"]

The order of operations is mathematically and architecturally critical:

Step 1: Classify Direction First (classifyCustodyMovement)

classifyCustodyMovement inspects the raw mathematical sign of the amount and the asset moved to determine its fiscal intent before aggregation destroys that sign by splitting it into amount_in and amount_out.

[!IMPORTANT] Running aggregation before classification was a primary historical bug in portfolio tools: the classifier received a record with no sign left to read, returned UNCLASSIFIED, and defaulted every transaction to a purchase.

Step 2: Apply the Source Profile Per Leg (applyProfileToRow)

What an omitted fee currency means and whether a row writes one movement onto both directional columns are facts about the source exchange, not the row. Applying the profile per leg while each leg’s asset is still in scope guarantees correct fee denomination.

Step 3: Aggregate Rows Sharing an Operation (aggregateRows)

Many exchanges (like Kraken) write two separate rows for a single trade: one row for the fiat currency and one for the cryptocurrency. Step 3 merges complementary legs that share a common transaction reference ID.

Step 4: Resolve Real Trade Direction (resolveTradeDirection)

Kraken assigns the generic label trade to both legs of both purchases and sales. The true direction only exists once both sides are present:

• paidInMoney → BUY
• receivedMoney → SELL
• Neither (crypto-to-crypto) → SWAP

If a record cannot be resolved, it is rejected with a descriptive error rather than defaulted, preventing silent ledger corruption.

---

⏰ Timezone Normalization (Exactly Once)

A frequent bug in distributed apps is double-timezone offset shifts:

1. The frontend converts local user time to UTC.
2. The backend re-interprets the UTC string by applying the local offset a second time.

Kryptofolio guarantees timezone conversion occurs exactly once inside dateNormalizer.ts:

normalizeToUtcIso(dateStr, timeStr, timezone);

All timestamps in the SQLite ledger and DuckDB views are stored in canonical UTC ISO-8601 format (YYYY-MM-DDTHH:mm:ss.sssZ).

---

🔐 Cryptographic Idempotency Hash (id_hash)

To prevent duplicate transactions when importing overlapping monthly or annual CSV statements, each row receives a deterministic SHA-256 cryptographic hash:

$$\text{id_hash} = \text{SHA-256}(\text{account_id} + \text{source_id} + \text{timestamp} + \text{asset} + \text{amount})$$

The SQLite database enforces a UNIQUE index on id_hash. Re-importing a file simply skips existing rows without modifying existing cost basis lots or duplicating balances.