Arquitectura del Ledger Transaccional SQLite

🏛️ Arquitectura del Ledger Transaccional SQLite

El Ledger Transaccional (criptofolio_ledger.db) constituye la fuente inmutable y única de verdad para todas las operaciones financieras en Criptofolio.

Garantiza cumplimiento ACID estricto, conservación de la precisión numérica, integridad referencial y auditoría no destructiva.

---

📊 Diagrama Entidad-Relación (ERD)

erDiagram
    accounts ||--o{ spot_transactions : "posee"
    accounts ||--o{ futures_transactions : "posee"
    assets ||--o{ spot_transactions : "asset_in"
    assets ||--o{ spot_transactions : "asset_out"
    spot_transactions ||--o{ tax_lots : "crea"
    tax_lots ||--o{ lot_history_events : "consumido por"
    tax_lots ||--o{ lot_custody_entries : "custodiado en"
    accounts ||--o{ accounts : "parent_account"

    accounts {
        TEXT id PK "UUID"
        TEXT parent_account_id FK "Cuentas anidadas en exchange"
        INTEGER is_synthetic "0 o 1"
        TEXT name "Nombre de la cuenta"
        TEXT type "CEX, COLD, ONCHAIN"
    }

    assets {
        TEXT id PK "UUID"
        TEXT symbol UK "ej. BTC, ETH, EUR"
        INTEGER is_fiat "0 o 1"
    }

    spot_transactions {
        TEXT id PK "UUID"
        TEXT id_hash UK "Hash SHA-256 criptográfico"
        TEXT tx_type "BUY, SELL, SWAP, etc"
        TEXT amount_in "TEXT para precisión"
        TEXT amount_out "TEXT para precisión"
        TEXT total_fiat "Magnitud no negativa"
        TEXT transfer_group_id "Agrupa lados de custodia"
        TEXT flag "Clasificación fiscal"
    }

    tax_lots {
        TEXT id PK "UUID"
        TEXT status "OPEN, PARTIAL, CLOSED"
        TEXT original_qty "TEXT"
        TEXT remaining_qty "TEXT"
        TEXT unit_cost_fiat "Coste base unitario"
        TEXT fx_rate "Tipo de cambio histórico"
        TEXT exchange_location "Lugar de adquisición"
    }

    lot_custody_entries {
        TEXT id PK "UUID"
        TEXT tax_lot_id FK "Apunta al lote fiscal"
        TEXT account_id FK "Apunta a la billetera"
        TEXT qty_delta "Delta con signo (+/-)"
    }

---

🔒 Restricciones de Integridad y PRAGMAs de Rendimiento

La base de datos está optimizada para alta concurrencia y máxima tolerancia a caídas:

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

Reglas de Integridad:

1. Tablas en Modo STRICT: Impide que SQLite realice coerciones implícitas de tipos.
2. Cantidades en Texto (GLOB '*[0-9]*'): Precios, comisiones e importes se almacenan en columnas TEXT para erradicar el redondeo de coma flotante.
3. Magnitudes No Negativas: Los campos total_fiat y price_fiat son siempre $\ge 0$; la dirección de la operación la define el tipo de transacción y no un signo negativo.
4. Integridad de Comisiones: Si existe fee_amount, fee_asset_id no puede ser nulo.

---

📜 Registro de Auditoría y Triggers Nativos

En la contabilidad financiera institucional, los registros jamás deben sobreescribirse ni desaparecer en silencio:

1. Borrado Lógico (Soft Deletes): La eliminación de registros marca el campo deleted_at.
2. Tabla audit_log: Almacena instantáneas JSON del estado anterior y posterior de cada fila modificada.
3. Triggers Nativos en SQLite: Los apuntes de auditoría se generan a nivel de motor de base de datos, garantizando que cambios efectuados por scripts externos o migraciones queden auditados sin depender del código de aplicación:

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;