from __future__ import annotations import base64 import json from dataclasses import replace from decimal import Decimal from hashlib import sha256 import pytest from jarvis_finance.dashboard.data import ( get_cash_overview, get_command_center_summary, get_positions, ) from jarvis_finance.imports.postfinance_documents import ( PFBundle, PFDocument, PFEvent, PFSnapshot, PFSnapshotCash, PFSnapshotPosition, _reconcile_cash_components, ) from jarvis_finance.services import postfinance_service as service from jarvis_finance.services.cash_service import get_cash_position_detail, get_cash_summary from jarvis_finance.services.equity_service import get_equity_summary from jarvis_finance.services.portfolio_aggregation import ( latest_official_postfinance_cash, latest_official_postfinance_positions, ) from jarvis_finance.services.portfolio_analytics import confirmed_canonical_positions from jarvis_finance.services.portfolio_data import build_portfolio_reconciliation from jarvis_finance.services.portfolio_service import get_overview from jarvis_finance.storage.database import connect_memory from jarvis_finance.storage.migrations import apply_migrations DEPOT = "account_pf_depot_test" CASH = "account_pf_trading_cash_test" def test_cash_component_reconciliation_preserves_negative_balances(): reconciled = _reconcile_cash_components( [ PFSnapshotCash("EUR", Decimal("-100"), Decimal("1"), Decimal("-100")), PFSnapshotCash("USD", Decimal("20"), Decimal("1"), Decimal("20")), ], Decimal("-80.01"), ) assert [item.source_value_chf for item in reconciled] == [ Decimal("-100.01"), Decimal("20.00"), ] assert sum((item.source_value_chf for item in reconciled), Decimal("0")) == Decimal( "-80.01" ) def make_conn(): conn = connect_memory() apply_migrations(conn) conn.execute( "INSERT INTO platforms(platform_id,name,platform_type,country,default_currency,is_active,created_at) VALUES('pf','PostFinance','bank','CH','CHF',1,'2026-01-01')" ) conn.execute( "INSERT INTO accounts(account_id,platform_id,account_name,account_type,currency,performance_included,is_active,created_at,portfolio_bucket) VALUES(?,'pf','PostFinance E-Trading','brokerage','CHF',1,1,'2026-01-01','postfinance')", (DEPOT,), ) conn.execute( "INSERT INTO accounts(account_id,platform_id,account_name,account_type,currency,performance_included,is_active,created_at,portfolio_bucket) VALUES(?,'pf','PostFinance Cash-Konto','cash','CHF',1,1,'2026-01-01','cash')", (CASH,), ) for index in range(22): instrument = f"instrument_pf_{index:02d}" label = f"Synthetic Asset {index + 1:02d}" conn.execute( "INSERT INTO instruments(instrument_id,name,ticker,isin,asset_class,currency,is_active,created_at) VALUES(?,?,?,?,?,?,1,?)", ( instrument, label, f"S{index:02d}", f"CH{index:010d}", "equity" if index < 11 else "etf", "CHF", "2026-01-01", ), ) conn.execute( "INSERT INTO positions_snapshot(position_snapshot_id,snapshot_date,account_id,platform_id,instrument_id,quantity,average_cost_original,cost_basis_original,market_price_original,market_value_original,market_fx_rate_to_chf,market_value_chf,category,data_quality_status,created_at) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", ( f"baseline_{index}", "2026-06-30", DEPOT, "pf", instrument, "1", "50", "50", "90", "90", "1", "90", "equity" if index < 11 else "etf", "complete", "2026-06-30T00:00:00Z", ), ) conn.commit() return conn def bundle( zip_raw: bytes = b"synthetic-zip", overview_raw: bytes = b"%PDF-synthetic-overview" ) -> PFBundle: zip_hash = sha256(zip_raw).hexdigest() overview_hash = sha256(overview_raw).hexdigest() doc_raw = b"%PDF-synthetic-trade" document = PFDocument( sha256(doc_raw).hexdigest(), sha256(b"trade.pdf").hexdigest(), "trade_confirmation", "synthetic-reference", sha256(b"semantic").hexdigest(), "2026-07-01", sha256(b"account").hexdigest(), "etrading_cash", 1, doc_raw, "synthetic", ) event = PFEvent( sha256(b"event").hexdigest(), document.document_hash, document.semantic_reference, "buy", "etrading_cash", "2026-07-01", "2026-07-03", "out", "Synthetic Asset 01", "CH0000000000", Decimal("1"), Decimal("50"), Decimal("50"), Decimal("1"), Decimal("0"), Decimal("51"), "CHF", Decimal("1"), None, "complete", (), ) transfer_out = PFEvent( sha256(b"transfer-out").hexdigest(), document.document_hash, "transfer-reference", "internal_transfer", "efinance", "2026-07-02", "2026-07-02", "out", None, None, None, None, Decimal("10"), Decimal("0"), Decimal("0"), Decimal("10"), "CHF", Decimal("1"), "synthetic-transfer-group", "partial", ("internal_transfer_not_income_or_expense",), ) transfer_in = PFEvent( sha256(b"transfer-in").hexdigest(), document.document_hash, "transfer-reference", "internal_transfer", "etrading_cash", "2026-07-02", "2026-07-02", "in", None, None, None, None, Decimal("10"), Decimal("0"), Decimal("0"), Decimal("10"), "CHF", Decimal("1"), "synthetic-transfer-group", "partial", ("internal_transfer_not_income_or_expense",), ) positions = tuple( PFSnapshotPosition( sha256(f"row-{index}".encode()).hexdigest()[:32], f"Synthetic Asset {index + 1:02d}", f"synthetic asset {index + 1:02d}", "stock" if index < 11 else "etf", Decimal("1"), Decimal("50"), Decimal("50"), Decimal("100"), "CHF", Decimal("100"), Decimal("4.17"), ) for index in range(22) ) snapshot = PFSnapshot( overview_hash, 5, "2026-07-27T09:14:30+02:00", positions, ( PFSnapshotCash("EUR", Decimal("100"), Decimal("1"), Decimal("100")), PFSnapshotCash("USD", Decimal("100"), Decimal("1"), Decimal("100")), ), Decimal("2200"), Decimal("200"), Decimal("2400"), Decimal("1100"), Decimal("1100"), 0, ) return PFBundle( zip_hash, overview_hash, sha256((zip_hash + overview_hash).encode()).hexdigest(), (document,), (event, transfer_out, transfer_in), snapshot, ("synthetic-transfer-group",), (), ) def request(zip_raw: bytes = b"synthetic-zip", overview_raw: bytes = b"%PDF-synthetic-overview"): return { "zip_file_name": "documents.zip", "overview_file_name": "overview.pdf", "zip_content_base64": base64.b64encode(zip_raw).decode(), "overview_content_base64": base64.b64encode(overview_raw).decode(), } def test_preview_confirm_and_reparse_are_atomic_idempotent_and_keep_roles_separate( monkeypatch, tmp_path ): conn = make_conn() calls = [] monkeypatch.setenv("JARVIS_FINANCE_RUNTIME_DIR", str(tmp_path)) monkeypatch.setattr( service, "parse_postfinance_bundle", lambda zip_raw, overview_raw: ( calls.append((zip_raw, overview_raw)) or bundle(zip_raw, overview_raw) ), ) payload = request() before = conn.total_changes preview = service.preview_postfinance_import(conn, payload) assert conn.total_changes == before assert preview["mapped_position_count"] == 22 first = service.confirm_postfinance_import( conn, { **payload, "preview_id": preview["preview_id"], "confirmation_id": preview["confirmation_id"], "confirm": True, }, ) again_preview = service.preview_postfinance_import(conn, payload) second = service.confirm_postfinance_import( conn, { **payload, "preview_id": again_preview["preview_id"], "confirmation_id": again_preview["confirmation_id"], "confirm": True, }, ) assert len(calls) >= 4 assert first["idempotent"] is False and second["idempotent"] is True safe_summary = service.get_postfinance_summary(conn) serialized = str(safe_summary) assert "account_pf_" not in serialized and "CH0000000000" not in serialized assert "snapshot_id" not in safe_summary["latest_snapshot"] assert "instrument_id" not in safe_summary["positions"][0] assert "batch_id" not in safe_summary["imports"][0] assert conn.execute("SELECT COUNT(*) FROM postfinance_import_batches").fetchone()[0] == 1 assert conn.execute("SELECT COUNT(*) FROM postfinance_snapshots").fetchone()[0] == 1 assert conn.execute("SELECT COUNT(*) FROM postfinance_snapshot_positions").fetchone()[0] == 22 assert conn.execute("SELECT COUNT(*) FROM postfinance_account_roles").fetchone()[0] == 3 roles = dict(conn.execute("SELECT role,account_id FROM postfinance_account_roles")) assert roles["etrading_depot"] == DEPOT assert roles["etrading_cash"] == CASH assert roles["efinance"] not in {DEPOT, CASH} assert len(set(roles.values())) == 3 assert ( conn.execute( "SELECT account_name FROM accounts WHERE account_id=?", (roles["efinance"],) ).fetchone()[0] == "PostFinance E-Finance" ) transfer_rows = conn.execute( "SELECT account_id,activity_kind FROM transactions WHERE internal_transfer_group_id='synthetic-transfer-group' ORDER BY account_id" ).fetchall() assert {row["account_id"] for row in transfer_rows} == {roles["efinance"], CASH} assert {row["activity_kind"] for row in transfer_rows} == {"internal_transfer"} assert ( conn.execute( "SELECT activity_kind FROM transactions WHERE source_type='postfinance_official_import'" ).fetchone()[0] == "trade" ) assert len(list((tmp_path / "imports" / "postfinance" / "archive").glob("*"))) == 3 def test_provider_and_computed_cost_basis_are_separate(monkeypatch, tmp_path): conn = make_conn() monkeypatch.setenv("JARVIS_FINANCE_RUNTIME_DIR", str(tmp_path)) monkeypatch.setattr( service, "parse_postfinance_bundle", lambda zip_raw, overview_raw: bundle(zip_raw, overview_raw), ) payload = request() preview = service.preview_postfinance_import(conn, payload) service.confirm_postfinance_import( conn, { **payload, "preview_id": preview["preview_id"], "confirmation_id": preview["confirmation_id"], "confirm": True, }, ) first = conn.execute( "SELECT provider_cost_total_original,computed_cost_basis_original,computed_cost_basis_status FROM postfinance_snapshot_positions WHERE computed_cost_basis_original IS NOT NULL" ).fetchone() assert first[0] == "50" and first[1] == "51" and first[2] == "complete" assert conn.execute("SELECT COUNT(*) FROM postfinance_cost_basis_lots").fetchone()[0] == 1 def test_sell_consumes_documented_fifo_lot_before_cost_basis_persistence(monkeypatch, tmp_path): conn = make_conn() monkeypatch.setenv("JARVIS_FINANCE_RUNTIME_DIR", str(tmp_path)) original = bundle() buy = replace( original.events[0], quantity=Decimal("2"), gross_original=Decimal("100"), net_original=Decimal("101"), ) sell = replace( buy, event_fingerprint=sha256(b"sell-event").hexdigest(), event_type="sell", occurred_on="2026-07-10", direction="in", quantity=Decimal("1"), price_original=Decimal("60"), gross_original=Decimal("60"), fee_original=Decimal("1"), net_original=Decimal("59"), ) fifo_bundle = replace(original, events=(buy, sell, *original.events[1:])) monkeypatch.setattr(service, "parse_postfinance_bundle", lambda _zip, _overview: fifo_bundle) payload = request() preview = service.preview_postfinance_import(conn, payload) service.confirm_postfinance_import( conn, { **payload, "preview_id": preview["preview_id"], "confirmation_id": preview["confirmation_id"], "confirm": True, }, ) lot = conn.execute( "SELECT quantity_acquired,quantity_remaining FROM postfinance_cost_basis_lots" ).fetchone() assert tuple(lot) == ("2", "1") computed = conn.execute( "SELECT computed_cost_basis_original,computed_cost_basis_status FROM postfinance_snapshot_positions WHERE computed_cost_basis_original IS NOT NULL" ).fetchone() assert tuple(computed) == ("50.5", "complete") def test_same_snapshot_with_different_original_source_is_blocked(monkeypatch, tmp_path): conn = make_conn() monkeypatch.setenv("JARVIS_FINANCE_RUNTIME_DIR", str(tmp_path)) monkeypatch.setattr( service, "parse_postfinance_bundle", lambda zip_raw, overview_raw: bundle(zip_raw, overview_raw), ) payload = request() preview = service.preview_postfinance_import(conn, payload) service.confirm_postfinance_import( conn, { **payload, "preview_id": preview["preview_id"], "confirmation_id": preview["confirmation_id"], "confirm": True, }, ) alternate = request(b"synthetic-zip-revision", b"%PDF-synthetic-overview-revision") conflict = service.preview_postfinance_import(conn, alternate) assert conflict["conflict"] is True with pytest.raises(ValueError, match="conflict"): service.confirm_postfinance_import( conn, { **alternate, "preview_id": conflict["preview_id"], "confirmation_id": conflict["confirmation_id"], "confirm": True, }, ) assert conn.execute("SELECT COUNT(*) FROM postfinance_import_batches").fetchone()[0] == 1 def test_existing_same_day_canonical_projection_is_blocked_during_preview(monkeypatch): conn = make_conn() conn.execute( """INSERT INTO positions_snapshot(position_snapshot_id,snapshot_date,account_id,platform_id, instrument_id,quantity,market_value_chf,data_quality_status,created_at) VALUES('same-day','2026-07-27',?,'pf','instrument_pf_00','1','100','complete','2026-07-27')""", (DEPOT,), ) conn.commit() monkeypatch.setattr(service, "parse_postfinance_bundle", lambda _zip, _overview: bundle()) preview = service.preview_postfinance_import(conn, request()) assert preview["conflict"] is True assert preview["conflicting_batch_id"] == "canonical_projection_date_conflict" def test_immutable_postfinance_source_and_snapshot_rows(monkeypatch, tmp_path): conn = make_conn() monkeypatch.setenv("JARVIS_FINANCE_RUNTIME_DIR", str(tmp_path)) monkeypatch.setattr( service, "parse_postfinance_bundle", lambda zip_raw, overview_raw: bundle(zip_raw, overview_raw), ) payload = request() preview = service.preview_postfinance_import(conn, payload) result = service.confirm_postfinance_import( conn, { **payload, "preview_id": preview["preview_id"], "confirmation_id": preview["confirmation_id"], "confirm": True, }, ) with pytest.raises(Exception, match="immutable"): conn.execute( "UPDATE postfinance_snapshots SET total_chf='0' WHERE snapshot_id=?", (result["snapshot_id"],), ) with pytest.raises(Exception, match="cannot be deleted"): conn.execute( "DELETE FROM postfinance_snapshots WHERE snapshot_id=?", (result["snapshot_id"],) ) immutable_columns = { "postfinance_batch_documents": "document_hash=document_hash", "postfinance_snapshot_positions": "created_at=created_at", "postfinance_snapshot_cash": "created_at=created_at", "postfinance_event_components": "created_at=created_at", } for table, assignment in immutable_columns.items(): with pytest.raises(Exception, match="immutable"): conn.execute(f"UPDATE {table} SET {assignment}") with pytest.raises(Exception, match="cannot be deleted"): conn.execute(f"DELETE FROM {table}") canonical_tables = { "positions_snapshot": "source_type='postfinance_official_import'", "cash_balances": "source_type='postfinance_official_import'", "transactions": "source_type='postfinance_official_import'", } for table, predicate in canonical_tables.items(): with pytest.raises(Exception, match="immutable"): conn.execute(f"UPDATE {table} SET created_at=created_at WHERE {predicate}") with pytest.raises(Exception, match="cannot be deleted"): conn.execute(f"DELETE FROM {table} WHERE {predicate}") def test_global_read_model_counts_official_postfinance_components_once(monkeypatch, tmp_path): """Reproduce the production placeholder/exposure/settlement-position collision.""" conn = make_conn() monkeypatch.setenv("JARVIS_FINANCE_RUNTIME_DIR", str(tmp_path)) original = bundle() stock_total = Decimal("255791.97") etf_total = Decimal("37673.19") def split_total(total: Decimal) -> list[Decimal]: values = [(total / Decimal("11")).quantize(Decimal("0.01")) for _ in range(11)] values[-1] += total - sum(values, Decimal("0")) return values source_values = split_total(stock_total) + split_total(etf_total) positions = tuple( replace(item, quantity=Decimal("1"), market_price_original=value, market_value_chf=value) for item, value in zip(original.snapshot.positions, source_values, strict=True) ) realistic = replace( original, snapshot=replace( original.snapshot, positions=positions, cash=( PFSnapshotCash("EUR", Decimal("21693.27"), Decimal("0.9293"), Decimal("71623.09")), PFSnapshotCash("USD", Decimal("9235.55"), Decimal("0.8146"), Decimal("249526.14")), ), securities_total_chf=Decimal("293465.16"), cash_total_chf=Decimal("27684.07"), total_value_chf=Decimal("321149.23"), stock_total_chf=stock_total, etf_total_chf=etf_total, ), ) monkeypatch.setattr(service, "parse_postfinance_bundle", lambda _zip, _overview: realistic) conn.execute( """INSERT INTO cash_account_snapshots(snapshot_id,account_id,snapshot_type,balance_date, amount_original,currency,amount_chf,source,note,created_at,created_by) VALUES('legacy-total',?,'manual_balance','2026-07-26','321149.23','CHF', '321149.23','manual','historical depot total placeholder', '2026-07-26T00:00:00Z','synthetic')""", (CASH,), ) conn.execute( "INSERT INTO platforms(platform_id,name,platform_type,country,default_currency,is_active,created_at) VALUES('tw','True Wealth','wealth','CH','CHF',1,'2026-01-01')" ) conn.execute( """INSERT INTO accounts(account_id,platform_id,account_name,account_type,currency, performance_included,is_active,created_at,portfolio_bucket) VALUES('account_tw','tw','True Wealth','brokerage','CHF',1,1,'2026-01-01','truewealth')""" ) conn.execute( """INSERT INTO account_value_snapshots(snapshot_id,account_id,valuation_date, total_value_chf,currency,source_type,quality_status,created_at,is_active) VALUES('tw-value','account_tw','2026-07-27','152845.00','CHF', 'synthetic_truewealth','complete','2026-07-27T00:00:00Z',1)""" ) conn.commit() payload = request() preview = service.preview_postfinance_import(conn, payload) service.confirm_postfinance_import( conn, {**payload, "preview_id": preview["preview_id"], "confirmation_id": preview["confirmation_id"], "confirm": True}, ) before_gets = conn.total_changes cash = get_cash_summary(conn) equity = get_equity_summary(conn) overview = get_overview(conn) command = get_command_center_summary(conn) reconciliation = build_portfolio_reconciliation(conn, as_of="2026-07-27") rows = get_positions(conn) assert conn.total_changes == before_gets assert cash.cash_chf == "27684.07" assert sum(Decimal(item.used_value_chf) for item in cash.positions if item.account_id == CASH) == Decimal("27684.07") usd_position = next(item for item in cash.positions if item.account_id == CASH and item.currency == "USD") assert get_cash_position_detail(conn, usd_position.id).currency == "USD" assert equity.equity_value_chf == "293465.16" assert equity.as_of == "2026-07-27T09:14:30+02:00" assert sum(Decimal(row["market_value_chf"]) for row in rows if row["account_id"] == DEPOT) == Decimal("293465.16") assert not [row for row in rows if row["account_id"] == CASH and row["asset_class"] in {"stock", "equity", "etf"}] assert Decimal(cash.cash_chf) + Decimal(equity.equity_value_chf) == Decimal("321149.23") assert overview.truewealth_value_chf == "152845.00" assert command["cash_total_chf"] == "27684.07" pf_reconciliation = next( item for item in reconciliation["account_totals"] if item["reported_total"] == "321149.23" ) assert pf_reconciliation["derived_total"] == "321149.23" assert pf_reconciliation["status"] == "matched" assert conn.execute("SELECT amount_chf FROM cash_account_snapshots WHERE snapshot_id='legacy-total'").fetchone()[0] == "321149.23" assert conn.execute("SELECT COUNT(*) FROM transactions WHERE internal_transfer_group_id IS NOT NULL").fetchone()[0] == 2 market_positions = [] for row, value in zip( [row for row in rows if row["account_id"] == DEPOT], split_total(Decimal("160000")) + split_total(Decimal("140000")), strict=True, ): market_positions.append({ "account_id": DEPOT, "instrument_id": row["instrument_id"], "quality_status": "fresh", "value_chf": str(value), "close": str(value), "currency": "CHF", "provider": "synthetic-market", }) conn.execute( """INSERT INTO market_data_runs(run_id,source_key,as_of,input_fingerprint,status, started_at,completed_at,price_total,price_stored,fx_total,fx_stored, benchmark_total,benchmark_stored,valuation_stored,missing_instruments_json, reason_codes_json) VALUES('newer-run','synthetic','2026-07-28','fp','complete','2026-07-28T00:00:00Z', '2026-07-28T00:01:00Z',22,22,0,0,0,0,22,'[]','[]')""" ) conn.execute( """INSERT INTO portfolio_analysis_snapshots(analysis_snapshot_id,run_id,as_of, base_currency,total_value_chf,price_coverage_pct,fx_coverage_pct, benchmark_coverage_pct,quality_status,reason_codes_json,summary_json,created_at) VALUES('newer-analysis','newer-run','2026-07-28','CHF','300000','100','100','0', 'complete','[]',?,'2026-07-28T00:01:00Z')""", (json.dumps({"positions": market_positions}),), ) conn.commit() newer = get_equity_summary(conn) assert newer.equity_value_chf == "300000.00" assert newer.as_of == "2026-07-28" assert get_cash_summary(conn).cash_chf == "27684.07" assert service.get_postfinance_summary(conn)["latest_snapshot"]["total_chf"] == "321149.23" duplicate = service.preview_postfinance_import(conn, payload) second = service.confirm_postfinance_import( conn, {**payload, "preview_id": duplicate["preview_id"], "confirmation_id": duplicate["confirmation_id"], "confirm": True}, ) assert duplicate["duplicate"] is True and duplicate["conflict"] is False assert second["idempotent"] is True assert conn.execute("SELECT COUNT(*) FROM postfinance_import_batches").fetchone()[0] == 1 def test_official_snapshot_resolvers_honor_historical_as_of_and_cutoff(monkeypatch, tmp_path): conn = make_conn() monkeypatch.setenv("JARVIS_FINANCE_RUNTIME_DIR", str(tmp_path)) monkeypatch.setattr(service, "parse_postfinance_bundle", lambda _zip, _overview: bundle()) payload = request() preview = service.preview_postfinance_import(conn, payload) service.confirm_postfinance_import( conn, {**payload, "preview_id": preview["preview_id"], "confirmation_id": preview["confirmation_id"], "confirm": True}, ) original_cash_total = sum( (item.amount_chf for item in latest_official_postfinance_cash(conn)), Decimal("0") ) original_position_total = sum( ( item.market_value_chf for item in latest_official_postfinance_positions(conn).values() ), Decimal("0"), ) conn.execute("UPDATE accounts SET account_type='checking' WHERE account_id=?", (CASH,)) assert sum( ( Decimal(item.used_value_chf) for item in get_cash_summary(conn).positions if item.account_id == CASH ), Decimal("0"), ) == original_cash_total platform_id = conn.execute( "SELECT platform_id FROM accounts WHERE account_id=?", (CASH,) ).fetchone()["platform_id"] conn.execute( """INSERT INTO accounts( account_id,platform_id,account_name,account_type,currency,balance_mode, portfolio_bucket,is_active,created_at) VALUES('generic-usd-cash',?,'Generic Multi-Currency','cash','CHF','csv_calculated', 'cash',1,'2026-07-27T10:00:00Z')""", (platform_id,), ) conn.execute( """INSERT INTO transactions( transaction_id,transaction_type,account_id,trade_date,gross_amount_original, net_amount_original,currency_original,fx_rate_to_chf,fx_status,source_type, source_id,is_confirmed,quality_status,created_at) VALUES('generic-usd-deposit','cash_deposit','generic-usd-cash','2026-07-27', '100','100','USD','0.9','ok','csv_import','generic-usd-deposit',1, 'ok','2026-07-27T10:00:00Z')""" ) conn.execute( """INSERT INTO transactions( transaction_id,transaction_type,account_id,trade_date,gross_amount_original, net_amount_original,currency_original,fx_rate_to_chf,fx_status,source_type, source_id,is_confirmed,quality_status,created_at) VALUES('generic-eur-deposit','cash_deposit','generic-usd-cash','2026-07-27', '50','50','EUR','1.1','ok','csv_import','generic-eur-deposit',1, 'ok','2026-07-27T10:00:01Z')""" ) conn.execute( """INSERT INTO cash_balances( cash_balance_id,account_id,balance_date,currency,amount_original, fx_rate_to_chf,amount_chf,source_type,quality_status,created_at) VALUES('generic-gbp-dividend','generic-usd-cash','2026-07-27','GBP','10', '1.1','11','vue_equity_dividend','ok','2026-07-27T10:00:02Z')""" ) generic_rows = [ row for row in get_cash_overview(conn) if row["account_id"] == "generic-usd-cash" ] assert {row["currency"] for row in generic_rows} == {"EUR", "GBP", "USD"} generic_usd = next(row for row in generic_rows if row["currency"] == "USD") generic_eur = next(row for row in generic_rows if row["currency"] == "EUR") generic_gbp = next(row for row in generic_rows if row["currency"] == "GBP") assert generic_usd["amount_original"] == "100.00" assert generic_usd["amount_chf"] == "90.00" assert generic_eur["amount_original"] == "50.00" assert generic_eur["amount_chf"] == "55.00" assert generic_gbp["amount_original"] == "10.00" assert generic_gbp["amount_chf"] == "11.00" conn.execute( "UPDATE accounts SET balance_mode='manual' WHERE account_id='generic-usd-cash'" ) conn.execute( """INSERT INTO cash_account_snapshots( snapshot_id,account_id,snapshot_type,balance_date,amount_original,currency, amount_chf,source,created_at,created_by) VALUES('generic-manual-total','generic-usd-cash','manual_balance','2026-07-27', '1000','CHF','1000','manual','2026-07-27T10:01:00Z','user')""" ) conn.execute( """INSERT INTO cash_balances( cash_balance_id,account_id,balance_date,currency,amount_original, fx_rate_to_chf,amount_chf,source_type,quality_status,created_at) VALUES('generic-missing-fx','generic-usd-cash','2026-07-27','GBP','10', NULL,NULL,'manual_import','missing_fx','2026-07-27T10:01:01Z')""" ) controlled_rows = [ row for row in get_cash_overview(conn) if row["account_id"] == "generic-usd-cash" ] assert [(row["currency"], row["amount_chf"]) for row in controlled_rows] == [ ("CHF", "1000.00") ] batch = dict(conn.execute("SELECT * FROM postfinance_import_batches LIMIT 1").fetchone()) batch.update( batch_id="future-batch", bundle_sha256=sha256(b"future-bundle").hexdigest(), snapshot_at="2026-08-01T09:14:30+02:00", confirmed_at="2026-08-01T10:00:00Z", ) columns = list(batch) conn.execute( f"INSERT INTO postfinance_import_batches({','.join(columns)}) VALUES({','.join('?' for _ in columns)})", [batch[column] for column in columns], ) source_snapshot = dict(conn.execute("SELECT * FROM postfinance_snapshots LIMIT 1").fetchone()) source_snapshot.update( snapshot_id="future-snapshot", batch_id="future-batch", snapshot_at="2026-08-01T00:30:00+02:00", total_chf="1299.00", securities_chf="300.00", cash_chf="999.00", stocks_chf="100.00", etfs_chf="200.00", component_total_chf="1299.00", difference_chf="0.00", created_at="2026-08-01T10:00:00Z", ) columns = list(source_snapshot) conn.execute( f"INSERT INTO postfinance_snapshots({','.join(columns)}) VALUES({','.join('?' for _ in columns)})", [source_snapshot[column] for column in columns], ) conn.execute( """INSERT INTO postfinance_snapshot_positions( snapshot_position_id,snapshot_id,source_row_reference,instrument_id,asset_class, quantity,provider_average_cost_original,provider_cost_total_original, price_original,price_currency,value_chf,weight_pct,computed_cost_basis_original, computed_cost_basis_status,provenance_json,created_at) SELECT 'future-'||snapshot_position_id,'future-snapshot',source_row_reference, instrument_id,asset_class,quantity,provider_average_cost_original, provider_cost_total_original,price_original,price_currency,value_chf,weight_pct, computed_cost_basis_original,computed_cost_basis_status,provenance_json, '2026-08-01T10:00:00Z' FROM postfinance_snapshot_positions WHERE snapshot_id<> 'future-snapshot'""" ) conn.execute( """INSERT INTO postfinance_snapshot_cash( snapshot_cash_id,snapshot_id,currency,amount_original,fx_rate_to_chf,value_chf,created_at) SELECT 'future-'||snapshot_cash_id,'future-snapshot',currency,amount_original, fx_rate_to_chf,value_chf,'2026-08-01T10:00:00Z' FROM postfinance_snapshot_cash WHERE snapshot_id<> 'future-snapshot'""" ) conn.commit() assert sum((item.amount_chf for item in latest_official_postfinance_cash(conn)), Decimal("0")) == Decimal("999.00") historical_cash = latest_official_postfinance_cash(conn, as_of="2026-07-27") assert sum((item.amount_chf for item in historical_cash), Decimal("0")) == original_cash_total prior_local_day_cash = latest_official_postfinance_cash(conn, as_of="2026-07-31") assert sum( (item.amount_chf for item in prior_local_day_cash), Decimal("0") ) == original_cash_total cutoff_cash = latest_official_postfinance_cash( conn, as_of="2026-08-01", data_cutoff="2026-07-31T23:59:59Z", ) assert sum((item.amount_chf for item in cutoff_cash), Decimal("0")) == original_cash_total historical_positions = latest_official_postfinance_positions(conn, as_of="2026-07-27") assert sum( (item.market_value_chf for item in historical_positions.values()), Decimal("0") ) == original_position_total assert {item.valuation_at for item in historical_positions.values()} == {"2026-07-27T09:14:30+02:00"} prior_local_day_positions = latest_official_postfinance_positions( conn, as_of="2026-07-31" ) assert sum( (item.market_value_chf for item in prior_local_day_positions.values()), Decimal("0"), ) == original_position_total current_positions = latest_official_postfinance_positions(conn) assert sum((item.market_value_chf for item in current_positions.values()), Decimal("0")) == Decimal("300.00") assert {item.valuation_at for item in current_positions.values()} == {"2026-08-01T00:30:00+02:00"} selected = next(iter(current_positions.values())) baseline_quantity = selected.quantity conn.execute( """INSERT INTO transactions( transaction_id,transaction_type,account_id,instrument_id,trade_date, settlement_date,quantity,currency_original,source_type,source_id, external_transaction_id,row_hash,is_confirmed,quality_status,created_at, updated_at,is_voided) VALUES('post-snapshot-buy','buy',?,?, '2026-08-02','2026-08-02','2', 'CHF','manual_confirmed_trade','post-snapshot-buy','post-snapshot-buy',?, 1,'ok','2026-08-02T10:00:00Z','2026-08-02T10:00:00Z',0)""", (CASH, selected.instrument_id, sha256(b"post-snapshot-buy").hexdigest()), ) conn.execute( """INSERT INTO transactions( transaction_id,transaction_type,account_id,trade_date,gross_amount_original, net_amount_original,currency_original,fx_rate_to_chf,fx_status,source_type, source_id,is_confirmed,quality_status,created_at,is_voided) VALUES('post-snapshot-cash','cash_deposit',?,'2026-08-02','10','10','USD', '0.8','ok','manual_confirmed_cash','post-snapshot-cash',1,'ok', '2026-08-02T10:01:00Z',0)""", (CASH,), ) conn.execute( """INSERT INTO transactions( transaction_id,transaction_type,account_id,instrument_id,trade_date, settlement_date,quantity,gross_amount_original,net_amount_original, currency_original,fx_rate_to_chf,fx_status,source_type,source_id, is_confirmed,quality_status,created_at,is_voided) VALUES('post-snapshot-sell','sell',?,?, '2026-08-02','2026-08-02','1', '20','20','USD','0.8','ok','manual_confirmed_trade', 'post-snapshot-sell',1,'ok','2026-08-02T10:02:00Z',0)""", (CASH, selected.instrument_id), ) conn.execute( """INSERT INTO cash_balances( cash_balance_id,account_id,balance_date,currency,amount_original, fx_rate_to_chf,amount_chf,source_type,quality_status,created_at) VALUES('post-snapshot-dividend-cash',?,'2026-08-02','EUR','5','1','5', 'vue_equity_dividend','ok','2026-08-02T10:03:00Z')""", (CASH,), ) conn.commit() post_snapshot_cash = [ item for item in get_cash_summary(conn).positions if item.account_id == CASH ] assert sum( (Decimal(item.used_value_chf) for item in post_snapshot_cash), Decimal("0") ) == Decimal("1028.00") assert next(item for item in post_snapshot_cash if item.currency == "USD").amount == "130.00" assert next(item for item in post_snapshot_cash if item.currency == "EUR").amount == "105.00" dashboard_position = next( row for row in get_positions(conn) if row["account_id"] == DEPOT and row["instrument_id"] == selected.instrument_id ) assert Decimal(dashboard_position["quantity"]) == baseline_quantity + Decimal("1") canonical_position = next( item for item in confirmed_canonical_positions(conn, as_of="2026-08-02") if item.account_id == DEPOT and item.instrument_id == selected.instrument_id ) assert canonical_position.quantity == baseline_quantity + Decimal("1")