SQLite Transactional Ledger Architecture

🏛️ SQLite Transactional Ledger Architecture

The Transactional Ledger (kryptofolio_ledger.db) is the immutable single source of truth for all financial operations in Kryptofolio.

It enforces strict ACID compliance, precision preservation, referential constraints, and non-destructive audit logging.

---

📊 Entity Relationship Diagram (ERD)

erDiagram
    accounts ||--o{ spot_transactions : "owns"
    accounts ||--o{ futures_transactions : "owns"
    assets ||--o{ spot_transactions : "asset_in"
    assets ||--o{ spot_transactions : "asset_out"
    spot_transactions ||--o{ tax_lots : "creates"
    tax_lots ||--o{ lot_history_events : "consumed by"
    tax_lots ||--o{ lot_custody_entries : "held in"
    accounts ||--o{ accounts : "parent_account"

    accounts {
        TEXT id PK "UUID"
        TEXT parent_account_id FK "Nested Exchange Accounts"
        INTEGER is_synthetic "0 or 1"
        TEXT name "Account Label"
        TEXT type "CEX, COLD, ONCHAIN"
    }

    assets {
        TEXT id PK "UUID"
        TEXT symbol UK "e.g. BTC, ETH, EUR"
        INTEGER is_fiat "0 or 1"
    }

    spot_transactions {
        TEXT id PK "UUID"
        TEXT id_hash UK "Cryptographic SHA-256"
        TEXT tx_type "BUY, SELL, SWAP, etc"
        TEXT amount_in "TEXT for precision"
        TEXT amount_out "TEXT for precision"
        TEXT total_fiat "Non-negative magnitude"
        TEXT transfer_group_id "Groups custody legs"
        TEXT flag "Fiscal classification"
    }

    tax_lots {
        TEXT id PK "UUID"
        TEXT status "OPEN, PARTIAL, CLOSED"
        TEXT original_qty "TEXT"
        TEXT remaining_qty "TEXT"
        TEXT unit_cost_fiat "Cost basis"
        TEXT fx_rate "Historical FX rate"
        TEXT exchange_location "Acquiring venue"
    }

    lot_custody_entries {
        TEXT id PK "UUID"
        TEXT tax_lot_id FK "Points to lot"
        TEXT account_id FK "Points to wallet"
        TEXT qty_delta "Signed (+/-) delta"
    }

---

🔒 Engine Constraints & Performance PRAGMAs

The database is configured for high concurrency and immediate crash safety:

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;

Table Integrity Policies:

1. STRICT Mode Tables: Prevents SQLite from performing silent type coercion.
2. Text Amounts (GLOB '*[0-9]*'): All quantities, prices, and fees are stored as TEXT to eradicate IEEE-754 floating-point rounding.
3. Non-Negative Magnitudes: total_fiat and price_fiat are constrained to non-negative values; directionality is determined by transaction type rather than negative amounts.
4. Referential Integrity: If fee_amount exists, fee_asset_id must not be null.

---

📜 Automated Audit Log & Native Triggers

In institutional accounting, financial records must never be destroyed silently. Kryptofolio implements non-destructive auditing:

1. Soft Deletes: Deleting records sets a deleted_at timestamp.
2. audit_log Table: Captures before-and-after snapshots of updated or deleted rows as JSON payloads.
3. Native SQLite Triggers: Audit entries are created at the database level by SQLite triggers, guaranteeing that changes made by external scripts or migrations are logged without requiring application-level boilerplate:

CREATE TRIGGER IF NOT EXISTS trg_spot_tx_audit
AFTER UPDATE ON spot_transactions
BEGIN
    INSERT INTO audit_log (id, table_name, record_id, action, old_values, new_values)
    VALUES (
        lower(hex(randomblob(16))),
        'spot_transactions',
        OLD.id,
        'UPDATE',
        json_object('amount_in', OLD.amount_in, 'amount_out', OLD.amount_out, 'deleted_at', OLD.deleted_at),
        json_object('amount_in', NEW.amount_in, 'amount_out', NEW.amount_out, 'deleted_at', NEW.deleted_at)
    );
END;