## 1-65 1:from __future__ import annotations 2: 3:import hashlib 4:import json 5:from datetime import datetime, timezone 6:from sqlite3 import Connection 7: 8:from .schema import INITIAL_SCHEMA_SQL 9:from .postfinance_schema import create_postfinance_ledger_import_v1 10: 11:MIGRATION_VERSION = 51 12:MIGRATION_NAME = "051_current_source_coverage_and_truewealth_activity_v1" 13: 14:INSTRUMENT_OPTIONAL_COLUMNS = { 15: "position_category": "TEXT", 16: "ter": "TEXT", 17: "distribution_policy": "TEXT", 18: "index_name": "TEXT", 19: "fund_domicile": "TEXT", 20: "benchmark": "TEXT", 21: "is_currency_hedged": "INTEGER NOT NULL DEFAULT 0", 22: "hedged_to_currency": "TEXT", 23: "hedge_status": "TEXT NOT NULL DEFAULT 'unknown'", 24: "base_exposure_currency": "TEXT", 25: "trading_currency": "TEXT", 26: "instrument_status": "TEXT NOT NULL DEFAULT 'unknown'", 27: "valuation_policy": "TEXT NOT NULL DEFAULT 'live_price'", 28: "corporate_action_status": "TEXT NOT NULL DEFAULT 'not_checked'", 29: "split_or_corporate_action_review_required": "INTEGER NOT NULL DEFAULT 0", 30:} 31: 32:CATALOG_OPTIONAL_COLUMNS = { 33: "last_price": "TEXT", 34: "price_currency": "TEXT", 35: "price_date": "TEXT", 36: "price_source": "TEXT", 37: "exchange_name": "TEXT", 38: "mic": "TEXT", 39: "security_type": "TEXT", 40:} 41: 42:TEXT_AFFINITY_COLUMNS = { 43: "transactions": {"quantity", "price_original", "gross_amount_original", "fee_original", "tax_original", "net_amount_original", "fx_rate_to_chf", "gross_amount_chf", "fee_chf", "tax_chf", "net_amount_chf"}, 44: "crypto_holdings": {"quantity", "legacy_snapshot_value_original", "legacy_snapshot_value_chf"}, 45: "crypto_transactions": {"quantity", "price_original", "gross_amount_original", "fee_quantity", "fee_original", "fx_rate_to_chf", "amount_chf"}, 46: "crypto_prices": {"price", "market_cap", "volume_24h", "change_24h_pct"}, 47: "fx_rates": {"rate"}, 48: "market_prices": {"open", "high", "low", "close", "adjusted_close"}, 49: "equity_price_points": {"price"}, 50: "equity_intraday_candles": {"open", "close", "low", "high", "volume"}, 51: "crypto_price_points": {"price"}, 52:} 53: 54: 55:def utc_now() -> str: 56: return datetime.now(timezone.utc).isoformat() 57: 58: 59:def checksum_sql(sql: str) -> str: 60: return hashlib.sha256(sql.encode("utf-8")).hexdigest() 61: 62: 63:def get_schema_version(conn: Connection) -> int: 64: row = conn.execute("SELECT MAX(version) AS version FROM schema_migrations").fetchone() 65: return int(row["version"] or 0) if row else 0 ## 500-580 500: proposed_ticker TEXT, 501: proposed_isin TEXT, 502: proposed_exchange TEXT, 503: proposed_currency TEXT, 504: proposed_asset_class TEXT, 505: provider TEXT, 506: provider_symbol TEXT, 507: mapping_status TEXT NOT NULL DEFAULT 'needs_manual_review', 508: review_note TEXT, 509: created_at TEXT NOT NULL, 510: updated_at TEXT 511: ); 512: CREATE INDEX IF NOT EXISTS idx_instrument_import_candidates_status ON instrument_import_candidates(mapping_status); 513: CREATE INDEX IF NOT EXISTS idx_instrument_import_candidates_platform ON instrument_import_candidates(platform); 514: CREATE INDEX IF NOT EXISTS idx_instrument_import_candidates_isin ON instrument_import_candidates(proposed_isin, raw_isin); 515: """ 516: ) 517: _add_missing_columns(conn, "instrument_import_candidates", { 518: "account": "TEXT", 519: "provider": "TEXT", 520: "provider_symbol": "TEXT", 521: "review_note": "TEXT", 522: }) 523: 524: 525: 526:def _create_budget_phase1_tables(conn: Connection) -> None: 527: conn.executescript( 528: """ 529: CREATE TABLE IF NOT EXISTS budget_accounts ( 530: budget_account_id TEXT PRIMARY KEY, 531: linked_account_id TEXT REFERENCES accounts(account_id), 532: name TEXT NOT NULL, 533: account_type TEXT NOT NULL CHECK(account_type IN ('checking','credit_card','cash','savings','investment_cash','virtual','reserve','other')), 534: currency TEXT NOT NULL, 535: is_active INTEGER NOT NULL DEFAULT 1, 536: archived_at TEXT, 537: notes TEXT, 538: created_at TEXT NOT NULL, 539: updated_at TEXT 540: ); 541: CREATE INDEX IF NOT EXISTS idx_budget_accounts_linked_account ON budget_accounts(linked_account_id); 542: CREATE INDEX IF NOT EXISTS idx_budget_accounts_active ON budget_accounts(is_active); 543: 544: CREATE TABLE IF NOT EXISTS budget_categories ( 545: category_id TEXT PRIMARY KEY, 546: parent_category_id TEXT REFERENCES budget_categories(category_id), 547: name TEXT NOT NULL, 548: category_type TEXT NOT NULL CHECK(category_type IN ('income','expense','transfer','neutral')), 549: color TEXT, 550: icon TEXT, 551: is_active INTEGER NOT NULL DEFAULT 1, 552: sort_order INTEGER DEFAULT 0, 553: created_at TEXT NOT NULL, 554: updated_at TEXT 555: ); 556: CREATE INDEX IF NOT EXISTS idx_budget_categories_parent ON budget_categories(parent_category_id); 557: CREATE INDEX IF NOT EXISTS idx_budget_categories_active ON budget_categories(is_active); 558: 559: CREATE TABLE IF NOT EXISTS budget_tags ( 560: tag_id TEXT PRIMARY KEY, 561: name TEXT NOT NULL UNIQUE, 562: color TEXT, 563: is_active INTEGER NOT NULL DEFAULT 1, 564: created_at TEXT NOT NULL, 565: updated_at TEXT 566: ); 567: 568: CREATE TABLE IF NOT EXISTS budget_transactions ( 569: budget_transaction_id TEXT PRIMARY KEY, 570: account_id TEXT NOT NULL REFERENCES budget_accounts(budget_account_id), 571: transaction_type TEXT NOT NULL CHECK(transaction_type IN ('income','expense','transfer','refund','fee','adjustment','reversal')), 572: transaction_date TEXT NOT NULL, 573: booking_date TEXT, 574: description TEXT NOT NULL, 575: payee TEXT, 576: merchant_id TEXT, 577: amount_original TEXT NOT NULL, 578: currency_original TEXT NOT NULL, 579: fx_rate_to_chf TEXT, 580: amount_chf TEXT, ## 1549-1595 1549:def _create_account_value_snapshot_tables(conn: Connection) -> None: 1550: conn.executescript( 1551: """ 1552: CREATE TABLE IF NOT EXISTS account_value_snapshots ( 1553: snapshot_id TEXT PRIMARY KEY, 1554: account_id TEXT NOT NULL REFERENCES accounts(account_id), 1555: valuation_date TEXT NOT NULL, 1556: total_value_chf TEXT NOT NULL, 1557: currency TEXT NOT NULL DEFAULT 'CHF', 1558: source_type TEXT NOT NULL DEFAULT 'manual_total_value', 1559: quality_status TEXT NOT NULL DEFAULT 'ok', 1560: notes TEXT, 1561: created_at TEXT NOT NULL, 1562: updated_at TEXT 1563: ); 1564: CREATE INDEX IF NOT EXISTS idx_account_value_snapshots_account_date ON account_value_snapshots(account_id, valuation_date, created_at); 1565: """ 1566: ) 1567: 1568: 1569:def _create_cash_account_snapshot_tables(conn: Connection) -> None: 1570: _add_missing_columns(conn, "accounts", { 1571: "balance_mode": "TEXT NOT NULL DEFAULT 'manual'", 1572: "portfolio_bucket": "TEXT NOT NULL DEFAULT 'cash'", 1573: }) 1574: conn.executescript( 1575: """ 1576: CREATE TABLE IF NOT EXISTS cash_account_snapshots ( 1577: snapshot_id TEXT PRIMARY KEY, 1578: account_id TEXT NOT NULL REFERENCES accounts(account_id), 1579: snapshot_type TEXT NOT NULL, 1580: balance_date TEXT NOT NULL, 1581: amount_original TEXT NOT NULL, 1582: currency TEXT NOT NULL DEFAULT 'CHF', 1583: amount_chf TEXT NOT NULL, 1584: source TEXT NOT NULL, 1585: note TEXT, 1586: created_at TEXT NOT NULL, 1587: created_by TEXT NOT NULL DEFAULT 'user', 1588: audit_id TEXT 1589: ); 1590: CREATE INDEX IF NOT EXISTS idx_cash_account_snapshots_account_type_date ON cash_account_snapshots(account_id, snapshot_type, balance_date, created_at); 1591: """ 1592: ) 1593: 1594: 1595:def _create_transfer_pairing_v2_tables(conn: Connection) -> None: ## 2350-2415 2350:def _create_household_import_v1_tables(conn: Connection) -> None: 2351: """Add import lineage around the existing household candidates and ledger.""" 2352: _add_missing_columns(conn, "budget_transaction_candidates", { 2353: "household_batch_id": "TEXT", 2354: "source_row_fingerprint": "TEXT", 2355: "logical_fingerprint": "TEXT", 2356: }) 2357: conn.executescript( 2358: """ 2359: CREATE TABLE IF NOT EXISTS household_account_source_mappings ( 2360: mapping_id TEXT PRIMARY KEY, 2361: contract_version TEXT NOT NULL CHECK(contract_version='household_import_v1'), 2362: source_type TEXT NOT NULL, 2363: source_reference_hash TEXT NOT NULL CHECK(length(source_reference_hash)=64), 2364: budget_account_id TEXT NOT NULL REFERENCES budget_accounts(budget_account_id), 2365: canonical_account_id TEXT NOT NULL REFERENCES accounts(account_id), 2366: reference_hint TEXT NOT NULL, 2367: is_active INTEGER NOT NULL DEFAULT 1 CHECK(is_active IN (0,1)), 2368: created_at TEXT NOT NULL, 2369: updated_at TEXT NOT NULL, 2370: UNIQUE(source_type, source_reference_hash) 2371: ); 2372: CREATE INDEX IF NOT EXISTS idx_household_source_mapping_account 2373: ON household_account_source_mappings(budget_account_id, is_active); 2374: CREATE TABLE IF NOT EXISTS household_import_batches ( 2375: batch_id TEXT PRIMARY KEY, 2376: contract_version TEXT NOT NULL CHECK(contract_version='household_import_v1'), 2377: preview_fingerprint TEXT NOT NULL UNIQUE CHECK(length(preview_fingerprint)=64), 2378: baseline_fingerprint TEXT NOT NULL CHECK(length(baseline_fingerprint)=64), 2379: input_fingerprint TEXT NOT NULL CHECK(length(input_fingerprint)=64), 2380: file_count INTEGER NOT NULL, row_count INTEGER NOT NULL, 2381: candidate_count INTEGER NOT NULL, transfer_pair_count INTEGER NOT NULL, 2382: duplicate_count INTEGER NOT NULL, review_count INTEGER NOT NULL, 2383: receipt_link_count INTEGER NOT NULL, 2384: status TEXT NOT NULL CHECK(status='confirmed'), 2385: audit_id TEXT NOT NULL REFERENCES audit_log(audit_id), 2386: confirmed_at TEXT NOT NULL, confirmed_by TEXT NOT NULL 2387: ); 2388: CREATE INDEX IF NOT EXISTS idx_household_batches_confirmed 2389: ON household_import_batches(confirmed_at DESC); 2390: CREATE TABLE IF NOT EXISTS household_import_files ( 2391: household_file_id TEXT PRIMARY KEY, 2392: batch_id TEXT NOT NULL REFERENCES household_import_batches(batch_id), 2393: source_type TEXT NOT NULL, 2394: file_fingerprint TEXT NOT NULL UNIQUE CHECK(length(file_fingerprint)=64), 2395: row_count INTEGER NOT NULL, created_at TEXT NOT NULL 2396: ); 2397: CREATE INDEX IF NOT EXISTS idx_household_files_batch ON household_import_files(batch_id); 2398: CREATE TABLE IF NOT EXISTS household_import_items ( 2399: household_item_id TEXT PRIMARY KEY, 2400: batch_id TEXT NOT NULL REFERENCES household_import_batches(batch_id), 2401: source_type TEXT NOT NULL, 2402: source_row_fingerprint TEXT NOT NULL CHECK(length(source_row_fingerprint)=64), 2403: logical_fingerprint TEXT NOT NULL CHECK(length(logical_fingerprint)=64), 2404: disposition TEXT NOT NULL CHECK(disposition IN 2405: ('candidate','transfer_confirmed','duplicate_file','duplicate_source_row', 2406: 'duplicate_logical','pending','superseded_pending','receipt_detail','review')), 2407: candidate_id TEXT REFERENCES budget_transaction_candidates(transaction_candidate_id), 2408: transfer_pair_id TEXT REFERENCES budget_transfer_pairs(transfer_pair_id), 2409: created_at TEXT NOT NULL, 2410: UNIQUE(source_type, source_row_fingerprint), UNIQUE(logical_fingerprint) 2411: ); 2412: CREATE INDEX IF NOT EXISTS idx_household_items_batch 2413: ON household_import_items(batch_id, disposition); 2414: CREATE TABLE IF NOT EXISTS household_migros_links ( 2415: receipt_link_id TEXT PRIMARY KEY, ## 2450-2537 2450: BEGIN SELECT RAISE(ABORT, 'household import items are immutable'); END; 2451: CREATE TRIGGER IF NOT EXISTS household_items_no_delete 2452: BEFORE DELETE ON household_import_items 2453: BEGIN SELECT RAISE(ABORT, 'household import items cannot be deleted'); END; 2454: """ 2455: ) 2456: 2457: 2458:def _create_household_review_corrections_v1(conn: Connection) -> None: 2459: """Add durable, source-preserving relations for Sprint 17B corrections.""" 2460: _add_missing_columns( 2461: conn, 2462: "budget_transaction_candidates", 2463: {"review_version": "INTEGER NOT NULL DEFAULT 1"}, 2464: ) 2465: conn.executescript( 2466: """ 2467: CREATE TABLE IF NOT EXISTS household_credit_card_settlements ( 2468: settlement_id TEXT PRIMARY KEY, 2469: contract_version TEXT NOT NULL 2470: CHECK(contract_version='household_credit_card_settlement_v1'), 2471: payment_candidate_id TEXT NOT NULL UNIQUE 2472: REFERENCES budget_transaction_candidates(transaction_candidate_id), 2473: bank_transaction_id TEXT NOT NULL UNIQUE 2474: REFERENCES budget_transactions(budget_transaction_id), 2475: bank_account_id TEXT NOT NULL REFERENCES budget_accounts(budget_account_id), 2476: card_account_id TEXT NOT NULL REFERENCES budget_accounts(budget_account_id), 2477: counterpost_candidate_id TEXT 2478: REFERENCES budget_transaction_candidates(transaction_candidate_id), 2479: counterpost_transaction_id TEXT UNIQUE 2480: REFERENCES budget_transactions(budget_transaction_id), 2481: currency TEXT NOT NULL, 2482: payment_amount TEXT NOT NULL, 2483: mapped_purchase_sum TEXT NOT NULL, 2484: completeness_status TEXT NOT NULL 2485: CHECK(completeness_status IN ('complete','partial')), 2486: data_status TEXT NOT NULL CHECK(data_status IN ('current','partial')), 2487: acknowledged_partial INTEGER NOT NULL DEFAULT 0 2488: CHECK(acknowledged_partial IN (0,1)), 2489: audit_id TEXT NOT NULL UNIQUE REFERENCES audit_log(audit_id), 2490: created_at TEXT NOT NULL, 2491: updated_at TEXT NOT NULL, 2492: CHECK(bank_account_id<>card_account_id), 2493: CHECK((completeness_status='complete' AND data_status='current') OR 2494: (completeness_status='partial' AND data_status='partial')), 2495: CHECK(completeness_status='complete' OR acknowledged_partial=1) 2496: ); 2497: CREATE INDEX IF NOT EXISTS idx_household_card_settlement_card_date 2498: ON household_credit_card_settlements(card_account_id, created_at DESC); 2499: CREATE INDEX IF NOT EXISTS idx_household_card_settlement_candidate 2500: ON household_credit_card_settlements(payment_candidate_id); 2501: CREATE TRIGGER IF NOT EXISTS household_card_settlement_no_delete 2502: BEFORE DELETE ON household_credit_card_settlements 2503: BEGIN SELECT RAISE(ABORT, 'credit card settlement relations cannot be deleted'); END; 2504: CREATE TRIGGER IF NOT EXISTS household_card_settlement_identity_immutable 2505: BEFORE UPDATE ON household_credit_card_settlements 2506: WHEN NEW.settlement_id IS NOT OLD.settlement_id 2507: OR NEW.payment_candidate_id IS NOT OLD.payment_candidate_id 2508: OR NEW.bank_transaction_id IS NOT OLD.bank_transaction_id 2509: OR NEW.bank_account_id IS NOT OLD.bank_account_id 2510: OR NEW.card_account_id IS NOT OLD.card_account_id 2511: OR NEW.counterpost_candidate_id IS NOT OLD.counterpost_candidate_id 2512: OR NEW.counterpost_transaction_id IS NOT OLD.counterpost_transaction_id 2513: OR NEW.currency IS NOT OLD.currency 2514: OR NEW.payment_amount IS NOT OLD.payment_amount 2515: OR NEW.mapped_purchase_sum IS NOT OLD.mapped_purchase_sum 2516: OR NEW.completeness_status IS NOT OLD.completeness_status 2517: OR NEW.data_status IS NOT OLD.data_status 2518: OR NEW.acknowledged_partial IS NOT OLD.acknowledged_partial 2519: OR NEW.audit_id IS NOT OLD.audit_id 2520: OR NEW.created_at IS NOT OLD.created_at 2521: BEGIN SELECT RAISE(ABORT, 'credit card settlement identity is immutable'); END; 2522: CREATE TRIGGER IF NOT EXISTS household_correction_audit_immutable_update 2523: BEFORE UPDATE ON audit_log 2524: WHEN OLD.entity_type IN ('household_review_item','household_credit_card_settlement', 2525: 'household_transaction_category') 2526: BEGIN SELECT RAISE(ABORT, 'household correction audit is immutable'); END; 2527: CREATE TRIGGER IF NOT EXISTS household_correction_audit_no_delete 2528: BEFORE DELETE ON audit_log 2529: WHEN OLD.entity_type IN ('household_review_item','household_credit_card_settlement', 2530: 'household_transaction_category') 2531: BEGIN SELECT RAISE(ABORT, 'household correction audit cannot be deleted'); END; 2532: """ 2533: ) 2534: 2535: 2536:def _create_annual_budget_recurring_semantics_v1(conn: Connection) -> None: 2537: """Add canonical planning semantics without rebuilding legacy budget tables.""" ## 2660-2755 2660: CREATE TRIGGER IF NOT EXISTS budget_plan_version_audit_no_delete 2661: BEFORE DELETE ON audit_log WHEN OLD.entity_type='budget_plan_version' 2662: BEGIN SELECT RAISE(ABORT, 'budget plan version audit cannot be deleted'); END; 2663: """ 2664: ) 2665: 2666: 2667:def _create_current_source_coverage_and_truewealth_activity_v1(conn: Connection) -> None: 2668: """Store explicit file coverage plus source-evidenced managed-portfolio activity.""" 2669: column_contracts = { 2670: "household_import_files": { 2671: "period_start": "TEXT", 2672: "period_end": "TEXT", 2673: "physical_row_count": "INTEGER", 2674: "logical_row_count": "INTEGER", 2675: }, 2676: "truewealth_import_batches": { 2677: "period_from": "TEXT", 2678: "period_to": "TEXT", 2679: "activity_from": "TEXT", 2680: "activity_to": "TEXT", 2681: "external_cashflows_complete": "INTEGER NOT NULL DEFAULT 0", 2682: }, 2683: "postfinance_import_batches": { 2684: "activity_coverage_from": "TEXT", 2685: "activity_coverage_to": "TEXT", 2686: "performance_coverage_complete": "INTEGER NOT NULL DEFAULT 0", 2687: }, 2688: "cash_account_snapshots": {"semantic_identity": "TEXT"}, 2689: } 2690: alters: list[str] = [] 2691: for table, columns in column_contracts.items(): 2692: existing = {str(row[1]) for row in conn.execute(f'PRAGMA table_info("{table}")')} 2693: alters.extend( 2694: f'ALTER TABLE "{table}" ADD COLUMN "{name}" {definition};' 2695: for name, definition in columns.items() 2696: if name not in existing 2697: ) 2698: script = "SAVEPOINT migration_051;\n" + "\n".join(alters) + "\n" + ( 2699: """ 2700: CREATE TABLE IF NOT EXISTS postfinance_projection_corrections ( 2701: correction_id TEXT PRIMARY KEY, 2702: cash_balance_id TEXT NOT NULL UNIQUE REFERENCES cash_balances(cash_balance_id), 2703: snapshot_id TEXT NOT NULL REFERENCES postfinance_snapshots(snapshot_id), 2704: reason_code TEXT NOT NULL CHECK(reason_code='legacy_cash_projection_misallocation'), 2705: audit_id TEXT NOT NULL REFERENCES audit_log(audit_id), 2706: confirmed_at TEXT NOT NULL, 2707: confirmed_by TEXT NOT NULL DEFAULT 'user' 2708: ); 2709: CREATE TRIGGER IF NOT EXISTS postfinance_projection_corrections_no_update 2710: BEFORE UPDATE ON postfinance_projection_corrections 2711: BEGIN SELECT RAISE(ABORT, 'postfinance projection corrections are immutable'); END; 2712: CREATE TRIGGER IF NOT EXISTS postfinance_projection_corrections_no_delete 2713: BEFORE DELETE ON postfinance_projection_corrections 2714: BEGIN SELECT RAISE(ABORT, 'postfinance projection corrections cannot be deleted'); END; 2715: CREATE TABLE IF NOT EXISTS truewealth_activities ( 2716: activity_id TEXT PRIMARY KEY, 2717: batch_id TEXT NOT NULL REFERENCES truewealth_import_batches(batch_id), 2718: portfolio_id TEXT NOT NULL REFERENCES truewealth_portfolios(portfolio_id), 2719: account_id TEXT NOT NULL REFERENCES accounts(account_id), 2720: occurred_on TEXT NOT NULL, 2721: event_type TEXT NOT NULL CHECK(event_type IN ('buy','sell','dividend','split_out','split_in')), 2722: instrument_name TEXT NOT NULL, 2723: isin TEXT NOT NULL, 2724: quantity TEXT NOT NULL, 2725: gross_amount_chf TEXT, 2726: tax_amount_chf TEXT, 2727: external_cashflow INTEGER NOT NULL DEFAULT 0 CHECK(external_cashflow=0), 2728: source_row_fingerprint TEXT NOT NULL UNIQUE, 2729: evidence_json TEXT NOT NULL DEFAULT '{}' 2730: CHECK(json_valid(evidence_json) AND json_type(evidence_json)='object'), 2731: created_at TEXT NOT NULL 2732: ); 2733: CREATE INDEX IF NOT EXISTS idx_truewealth_activities_period 2734: ON truewealth_activities(portfolio_id,occurred_on,event_type); 2735: DROP INDEX IF EXISTS ux_cash_snapshot_semantic_identity; 2736: CREATE UNIQUE INDEX IF NOT EXISTS ux_cash_snapshot_semantic_identity 2737: ON cash_account_snapshots(semantic_identity) 2738: WHERE semantic_identity IS NOT NULL; 2739: CREATE TRIGGER IF NOT EXISTS truewealth_activities_no_update 2740: BEFORE UPDATE ON truewealth_activities 2741: BEGIN SELECT RAISE(ABORT, 'truewealth activities are immutable'); END; 2742: CREATE TRIGGER IF NOT EXISTS truewealth_activities_no_delete 2743: BEFORE DELETE ON truewealth_activities 2744: BEGIN SELECT RAISE(ABORT, 'truewealth activities cannot be deleted'); END; 2745: RELEASE migration_051; 2746: """ 2747: ) 2748: try: 2749: conn.executescript(script) 2750: except Exception: 2751: if conn.in_transaction: 2752: conn.execute("ROLLBACK TO migration_051") 2753: conn.execute("RELEASE migration_051") 2754: raise 2755: ## 2825-2925 2825: BEFORE UPDATE ON crypto_balance_snapshot_items 2826: BEGIN SELECT RAISE(ABORT, 'crypto snapshot items are immutable'); END; 2827: CREATE TRIGGER IF NOT EXISTS crypto_balance_snapshot_items_no_delete 2828: BEFORE DELETE ON crypto_balance_snapshot_items 2829: BEGIN SELECT RAISE(ABORT, 'crypto snapshot items cannot be deleted'); END; 2830: CREATE TRIGGER IF NOT EXISTS crypto_internal_transfer_pairs_no_update 2831: BEFORE UPDATE ON crypto_internal_transfer_pairs 2832: BEGIN SELECT RAISE(ABORT, 'crypto transfer pairs are immutable'); END; 2833: CREATE TRIGGER IF NOT EXISTS crypto_internal_transfer_pairs_no_delete 2834: BEFORE DELETE ON crypto_internal_transfer_pairs 2835: BEGIN SELECT RAISE(ABORT, 'crypto transfer pairs cannot be deleted'); END; 2836: """ 2837: ) 2838: 2839: 2840:def _apply_compat_migrations(conn: Connection) -> None: 2841: _add_missing_instrument_columns(conn) 2842: for table, text_columns in TEXT_AFFINITY_COLUMNS.items(): 2843: _rebuild_table_with_text_columns(conn, table, text_columns) 2844: _create_broker_bank_mapping_tables(conn) 2845: _create_broker_import_review_items(conn) 2846: _create_broker_import_execution_plans(conn) 2847: _add_transaction_void_columns(conn) 2848: _create_fx_market_data_tables(conn) 2849: _create_market_quote_chart_tables(conn) 2850: _create_account_value_snapshot_tables(conn) 2851: _create_cash_account_snapshot_tables(conn) 2852: _create_instrument_import_candidates(conn) 2853: _create_budget_phase1_tables(conn) 2854: _create_budget_phase11_tables(conn) 2855: _create_budget_phase14_tables(conn) 2856: _create_budget_phase15_tables(conn) 2857: _create_budget_phase18_tables(conn) 2858: _create_budget_phase19_tables(conn) 2859: _create_budget_import_production_v1_tables(conn) 2860: _create_budget_categories_ux_fix_tables(conn) 2861: _create_budget_planning_forecast_v1_tables(conn) 2862: _create_budget_fixed_costs_subscriptions_v1_tables(conn) 2863: _create_budget_monthly_import_rule_learning_v1_tables(conn) 2864: _create_transfer_pairing_v2_tables(conn) 2865: _create_portfolio_policy_tables(conn) 2866: _create_portfolio_performance_tables(conn) 2867: _create_portfolio_ingestion_reconciliation_tables(conn) 2868: _create_daily_market_analytics_tables(conn) 2869: _create_postfinance_baseline_mapping_audit_v1(conn) 2870: _create_truewealth_verified_snapshot_v1(conn) 2871: create_postfinance_ledger_import_v1(conn) 2872: _create_investment_performance_scope_v1(conn) 2873: _create_grocery_optimizer_v1_tables(conn) 2874: _add_grocery_price_provider_v1_columns(conn) 2875: _create_grocery_matching_learning_v2_tables(conn) 2876: _create_household_import_v1_tables(conn) 2877: _create_household_review_corrections_v1(conn) 2878: _create_annual_budget_recurring_semantics_v1(conn) 2879: _create_current_source_coverage_and_truewealth_activity_v1(conn) 2880: _create_crypto_reconciliation_cockpit_v1(conn) 2881: 2882: 2883:def apply_migrations(conn: Connection) -> None: 2884: conn.executescript(INITIAL_SCHEMA_SQL) 2885: existing_initial = conn.execute("SELECT 1 FROM schema_migrations WHERE version = 1").fetchone() 2886: if not existing_initial: 2887: conn.execute( 2888: "INSERT INTO schema_migrations(version, name, applied_at, checksum) VALUES (?, ?, ?, ?)", 2889: (1, "001_initial_schema", utc_now(), checksum_sql(INITIAL_SCHEMA_SQL)), 2890: ) 2891: _apply_compat_migrations(conn) 2892: existing = conn.execute("SELECT 1 FROM schema_migrations WHERE version = ?", (MIGRATION_VERSION,)).fetchone() 2893: if not existing: 2894: conn.execute( 2895: "INSERT INTO schema_migrations(version, name, applied_at, checksum) VALUES (?, ?, ?, ?)", 2896: (MIGRATION_VERSION, MIGRATION_NAME, utc_now(), checksum_sql(MIGRATION_NAME)), 2897: ) 2898: conn.commit() Traceback (most recent call last): File "", line 5, in IndexError: list index out of range __HERMES_CWD_8d46a20096ed__/home/agent__HERMES_CWD_8d46a20096ed__