from __future__ import annotations from datetime import UTC, datetime from decimal import Decimal from fastapi.testclient import TestClient from jarvis_finance.api.dependencies import get_db from jarvis_finance.api.main import create_app from jarvis_finance.quality.freshness import combined_freshness from jarvis_finance.services.reconciliation_snapshot import _safe_account_label, build_reconciliation_snapshot, list_reconciliation_records, list_snapshot_metadata from jarvis_finance.storage.database import connect_memory from jarvis_finance.storage.migrations import apply_migrations NOW = datetime(2026, 5, 21, tzinfo=UTC) def db(): conn = connect_memory() apply_migrations(conn) conn.execute("INSERT INTO platforms(platform_id, name, platform_type, default_currency, created_at) VALUES ('plat_test', 'Synthetic Bank', 'bank', 'CHF', '2026-01-01')") conn.execute("INSERT INTO accounts(account_id, platform_id, account_name, account_type, currency, is_active, created_at) VALUES ('acct_test', 'plat_test', 'Testkonto', 'cash', 'CHF', 1, '2026-01-01')") conn.commit() return conn def add_snapshot(conn, snapshot_type: str, date: str, amount: str, currency: str = "CHF") -> None: conn.execute( """INSERT INTO cash_account_snapshots(snapshot_id, account_id, snapshot_type, balance_date, amount_original, currency, amount_chf, source, created_at) VALUES (?, 'acct_test', ?, ?, ?, ?, ?, 'synthetic', '2026-05-21T10:00:00Z')""", (f"snap_{snapshot_type}_{date}_{currency}", snapshot_type, date, amount, currency, amount), ) conn.commit() def record(conn): return list_reconciliation_records(conn, now=NOW)[0] def test_identical_comparable_balances_are_reconciled_without_a_write(): conn = db() add_snapshot(conn, "csv_anchor_balance", "2026-05-20", "100.00") add_snapshot(conn, "manual_balance", "2026-05-20", "100.00") item = record(conn) assert item.status == "reconciled" assert item.difference == "0.00" assert item.ledger_balance == "100.00" assert item.snapshot_balance == "100.00" assert item.freshness_status == "fresh" assert conn.execute("SELECT COUNT(*) FROM transactions").fetchone()[0] == 0 def test_difference_uses_ledger_minus_snapshot_with_decimal_semantics(): conn = db() add_snapshot(conn, "csv_anchor_balance", "2026-05-20", "100.10") add_snapshot(conn, "manual_balance", "2026-05-20", "99.90") item = record(conn) assert item.status == "difference" assert item.difference == "0.20" assert Decimal(item.difference) == Decimal("100.10") - Decimal("99.90") def test_missing_comparison_balance_is_partial_not_zero(): conn = db() add_snapshot(conn, "csv_anchor_balance", "2026-05-20", "100.00") item = record(conn) assert item.status == "partial" assert item.snapshot_balance is None assert item.difference is None assert "comparison_snapshot_missing" in item.reason_codes def test_currency_or_as_of_mismatch_is_never_subtracted(): conn = db() add_snapshot(conn, "csv_anchor_balance", "2026-05-20", "100.00") add_snapshot(conn, "manual_balance", "2026-05-20", "100.00", currency="USD") assert record(conn).status == "not_comparable" assert record(conn).difference is None conn = db() add_snapshot(conn, "csv_anchor_balance", "2026-05-19", "100.00") add_snapshot(conn, "manual_balance", "2026-05-20", "100.00") assert record(conn).status == "not_comparable" assert "as_of_mismatch" in record(conn).reason_codes def test_stale_freshness_does_not_replace_a_reconciliation_result(): conn = db() add_snapshot(conn, "csv_anchor_balance", "2026-01-01", "100.00") add_snapshot(conn, "manual_balance", "2026-01-01", "100.00") item = record(conn) assert item.status == "reconciled" assert item.freshness_status == "stale" conn = db() add_snapshot(conn, "csv_anchor_balance", "2026-01-01", "100.004") add_snapshot(conn, "manual_balance", "2026-01-01", "100.000") item = record(conn) assert item.status == "difference" assert item.difference == "0.004" assert item.freshness_status == "stale" conn = db() item = record(conn) assert item.status == "unavailable" assert item.ledger_balance is None and item.snapshot_balance is None def test_snapshot_and_fx_metadata_keep_actual_as_of_and_decimal_strings(): conn = db() conn.execute("INSERT INTO account_value_snapshots(snapshot_id, account_id, valuation_date, total_value_chf, currency, source_type, quality_status, created_at) VALUES ('value_1', 'acct_test', '2026-05-20', '12.34', 'CHF', 'manual_total_value', 'ok', '2026-05-21T10:00:00Z')") conn.execute("INSERT INTO fx_rates(fx_rate_id, base_currency, quote_currency, rate_date, rate, provider, rate_type, quality_status, created_at) VALUES ('fx_1', 'USD', 'CHF', '2026-05-20', '0.91234567', 'synthetic_fx', 'close', 'fresh', '2026-05-21T10:00:00Z')") conn.commit() rows = list_snapshot_metadata(conn, now=NOW) valuation = next(row for row in rows if row.snapshot_kind == "account_valuation") fx = next(row for row in rows if row.snapshot_kind == "fx_rate") assert valuation.as_of == "2026-05-20" and valuation.received_at == "2026-05-21T10:00:00Z" assert valuation.value == "12.34" assert fx.base_currency == "USD" and fx.quote_currency == "CHF" and fx.value == "0.91234567" assert Decimal(fx.value) == Decimal("0.91234567") def test_ledger_anchor_currency_and_subunit_difference_are_never_hidden(): conn = db() add_snapshot(conn, "csv_anchor_balance", "2026-05-20", "100.00", currency="USD") add_snapshot(conn, "manual_balance", "2026-05-20", "100.00", currency="CHF") item = record(conn) assert item.status == "not_comparable" assert item.difference is None conn = db() add_snapshot(conn, "csv_anchor_balance", "2026-05-20", "1.234", currency="CHF") add_snapshot(conn, "manual_balance", "2026-05-20", "1.230", currency="CHF") item = record(conn) assert item.status == "difference" assert item.difference == "0.004" def test_mixed_stale_and_missing_source_is_partial(): assert combined_freshness(["stale", "unavailable"]) == "partial" def test_metadata_uses_only_latest_row_per_logical_series_and_is_bounded(): conn = db() for snapshot_id, date, value in (("old_value", "2026-01-01", "1.00"), ("new_value", "2026-05-20", "2.00")): conn.execute("INSERT INTO account_value_snapshots(snapshot_id, account_id, valuation_date, total_value_chf, currency, source_type, quality_status, created_at) VALUES (?, 'acct_test', ?, ?, 'CHF', 'manual_total_value', 'ok', '2026-05-21T10:00:00Z')", (snapshot_id, date, value)) for rate_id, date, value in (("old_fx", "2026-01-01", "0.90"), ("new_fx", "2026-05-20", "0.91")): conn.execute("INSERT INTO fx_rates(fx_rate_id, base_currency, quote_currency, rate_date, rate, provider, rate_type, quality_status, created_at) VALUES (?, 'USD', 'CHF', ?, ?, 'synthetic_fx', 'close', 'fresh', '2026-05-21T10:00:00Z')", (rate_id, date, value)) conn.commit() rows = list_snapshot_metadata(conn, now=NOW) assert len(rows) == 2 assert {(row.snapshot_kind, row.value, row.as_of) for row in rows} == {("account_valuation", "2.00", "2026-05-20"), ("fx_rate", "0.91", "2026-05-20")} def test_api_redacts_adversarial_account_labels_ids_and_sources(): conn = db() conn.execute("UPDATE accounts SET account_name='CH9300762011623852957', account_id='acct_secret' WHERE account_id='acct_test'") conn.execute("UPDATE cash_account_snapshots SET account_id='acct_secret' WHERE account_id='acct_test'") conn.execute("INSERT INTO cash_account_snapshots(snapshot_id, account_id, snapshot_type, balance_date, amount_original, currency, amount_chf, source, created_at) VALUES ('anchor', 'acct_secret', 'csv_anchor_balance', '2026-05-20', '1.00', 'CHF', '1.00', 'local', '2026-05-20T12:00:00Z')") conn.execute("INSERT INTO cash_account_snapshots(snapshot_id, account_id, snapshot_type, balance_date, amount_original, currency, amount_chf, source, created_at) VALUES ('manual', 'acct_secret', 'manual_balance', '2026-05-20', '1.00', 'CHF', '1.00', 'local', '2026-05-20T12:00:00Z')") conn.execute("INSERT INTO account_value_snapshots(snapshot_id, account_id, valuation_date, total_value_chf, currency, source_type, quality_status, created_at) VALUES ('value_sensitive', 'acct_secret', '2026-05-20', '1.00', 'CHF', '/tmp/secret.csv', 'ok', '2026-05-20T12:00:00Z')") conn.commit() app = create_app(write_mode="disabled") app.dependency_overrides[get_db] = lambda: conn response = TestClient(app).get("/api/portfolio/reconciliation-snapshot") assert response.status_code == 200 rendered = response.text assert "CH9300762011623852957" not in rendered and "acct_secret" not in rendered and "/tmp/secret.csv" not in rendered assert "account-" in rendered and "Konto (redigiert)" in rendered and '"source":"local"' in rendered def test_redaction_covers_account_wallet_and_path_identifiers_but_keeps_friendly_names(): for sensitive in ("CH9300762011623852957", "ch93 0076 2011 6238 5295 7", "1234567890123456", "0X1234567890ABCDEF1234567890ABCDEF12345678", "BC1QW508D6QEJXTDG4Y5R3ZARVARY0C5XW7KYGT080", "1BoatSLRHtKNngkdXEeobR76b53LETtpyT", "/srv/private/finance.sqlite", "wallet_abcdefghijklmnopqrstuvwxyz123456"): assert _safe_account_label(sensitive) == "Konto (redigiert)" assert _safe_account_label("AKB Haushaltskonto") == "AKB Haushaltskonto" assert _safe_account_label("Raiffeisen") == "Raiffeisen" def test_read_only_api_and_openapi_contract_expose_no_sensitive_account_fields(): app = create_app(write_mode="disabled") client = TestClient(app) schema = client.get("/openapi.json").json() operations = schema["paths"]["/api/portfolio/reconciliation-snapshot"] assert set(operations) == {"get"} response = client.get("/api/portfolio/reconciliation-snapshot") assert response.status_code == 200 rendered = response.text.lower() assert "iban" not in rendered and "wallet_address" not in rendered and "source_filename" not in rendered assert build_reconciliation_snapshot(db(), now=NOW)["data_quality_status"] == "unavailable"