===== src/jarvis_finance/storage/migrations.py ===== 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 = 52 12 MIGRATION_NAME = "052_professional_portfolio_cockpit_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 66 67 68 def _table_columns(conn: Connection, table: str) -> dict[str, str]: 69 return {row["name"]: (row["type"] or "") for row in conn.execute(f"PRAGMA table_info({table})").fetchall()} 70 71 72 def _add_missing_instrument_columns(conn: Connection) -> None: 73 existing = _table_columns(conn, "instruments") 74 for name, col_type in INSTRUMENT_OPTIONAL_COLUMNS.items(): 75 if name not in existing: 76 conn.execute(f"ALTER TABLE instruments ADD COLUMN {name} {col_type}") 77 78 79 def _rebuild_table_with_text_columns(conn: Connection, table: str, text_columns: set[str]) -> None: 80 cols = conn.execute(f"PRAGMA table_info({table})").fetchall() 81 if not cols: 82 return 83 needs_rebuild = any(row["name"] in text_columns and (row["type"] or "").upper() != "TEXT" for row in cols) 84 if not needs_rebuild: 85 return 86 tmp = f"{table}__text_migration" 87 col_defs: list[str] = [] 88 pk_cols = [row["name"] for row in cols if row["pk"]] 89 for row in cols: 90 name = row["name"] 91 col_type = "TEXT" if name in text_columns else (row["type"] or "TEXT") 92 parts = [name, col_type] 93 if row["pk"] and len(pk_cols) == 1: 94 parts.append("PRIMARY KEY") 95 if row["notnull"]: 96 parts.append("NOT NULL") 97 if row["dflt_value"] is not None: 98 parts.append(f"DEFAULT {row['dflt_value']}") 99 col_defs.append(" ".join(parts)) 100 if len(pk_cols) > 1: 101 col_defs.append("PRIMARY KEY (" + ", ".join(pk_cols) + ")") 102 conn.execute(f"DROP TABLE IF EXISTS {tmp}") 103 conn.execute(f"CREATE TABLE {tmp} (" + ", ".join(col_defs) + ")") 104 names = [row["name"] for row in cols] 105 select_exprs = [f"CAST({name} AS TEXT)" if name in text_columns else name for name in names] 106 conn.execute(f"INSERT INTO {tmp} ({', '.join(names)}) SELECT {', '.join(select_exprs)} FROM {table}") 107 conn.execute(f"DROP TABLE {table}") 108 conn.execute(f"ALTER TABLE {tmp} RENAME TO {table}") 109 if table == "crypto_holdings": 110 conn.execute("CREATE UNIQUE INDEX IF NOT EXISTS idx_crypto_holdings_asset_wallet ON crypto_holdings(asset_id, wallet_id)") 111 112 113 def _add_missing_columns(conn: Connection, table: str, columns: dict[str, str]) -> None: 114 existing = _table_columns(conn, table) 115 for name, col_type in columns.items(): 116 if name not in existing: 117 conn.execute(f"ALTER TABLE {table} ADD COLUMN {name} {col_type}") 118 119 120 def _create_broker_bank_mapping_tables(conn: Connection) -> None: 121 conn.executescript( 122 """ 123 CREATE TABLE IF NOT EXISTS instrument_mappings ( 124 mapping_id TEXT PRIMARY KEY, 125 source_name TEXT, 126 source_platform TEXT NOT NULL, 127 source_account TEXT, 128 source_label TEXT NOT NULL, 129 normalized_name TEXT NOT NULL, 130 isin TEXT, 131 ticker TEXT, 132 exchange TEXT, 133 currency TEXT, 134 asset_class TEXT NOT NULL DEFAULT 'other', 135 source_instrument_name TEXT, 136 source_isin TEXT, 137 source_ticker TEXT, 138 source_exchange TEXT, 139 source_currency TEXT, 140 instrument_id TEXT REFERENCES instruments(instrument_id), 141 mapping_status TEXT NOT NULL DEFAULT 'needs_manual_review', 142 confidence TEXT NOT NULL DEFAULT '0', 143 quality_flags_json TEXT, 144 notes TEXT, 145 created_at TEXT NOT NULL, 146 updated_at TEXT 147 ); 148 149 CREATE TABLE IF NOT EXISTS platform_account_mappings ( 150 mapping_id TEXT PRIMARY KEY, 151 source_name TEXT, 152 source_platform TEXT NOT NULL, 153 source_platform_label TEXT, 154 source_account_label TEXT NOT NULL, 155 normalized_platform TEXT NOT NULL, 156 normalized_account_name TEXT NOT NULL, 157 source_account_type TEXT, 158 account_type TEXT NOT NULL DEFAULT 'other', 159 source_currency TEXT, 160 currency TEXT, 161 platform_id TEXT REFERENCES platforms(platform_id), 162 account_id TEXT REFERENCES accounts(account_id), 163 internal_platform_id TEXT REFERENCES platforms(platform_id), 164 internal_account_id TEXT REFERENCES accounts(account_id), 165 mapping_status TEXT NOT NULL DEFAULT 'needs_manual_review', 166 quality_flags_json TEXT, 167 notes TEXT, 168 created_at TEXT NOT NULL, 169 updated_at TEXT 170 ); 171 172 CREATE TABLE IF NOT EXISTS broker_import_dry_runs ( 173 dry_run_id TEXT PRIMARY KEY, 174 source_name TEXT, 175 source_platform TEXT NOT NULL, 176 source_file_label TEXT, 177 source_file_type TEXT NOT NULL, 178 source_hash TEXT, 179 source_filename_hash TEXT, 180 detected_snapshot_date TEXT, 181 snapshot_date_status TEXT NOT NULL DEFAULT 'missing', 182 detected_sections_json TEXT NOT NULL, 183 field_coverage_json TEXT, 184 rows_total INTEGER NOT NULL DEFAULT 0, 185 rows_position_candidates INTEGER NOT NULL DEFAULT 0, 186 rows_cash_candidates INTEGER NOT NULL DEFAULT 0, 187 candidate_positions INTEGER NOT NULL DEFAULT 0, 188 candidate_cash_rows INTEGER NOT NULL DEFAULT 0, 189 mapped_positions INTEGER NOT NULL DEFAULT 0, 190 blocked_positions INTEGER NOT NULL DEFAULT 0, 191 warnings_count INTEGER NOT NULL DEFAULT 0, 192 errors_count INTEGER NOT NULL DEFAULT 0, 193 quality_flags_json TEXT NOT NULL, 194 summary_json TEXT NOT NULL DEFAULT '{}', 195 created_at TEXT NOT NULL, 196 notes TEXT 197 ); 198 """ 199 ) 200 _add_missing_columns(conn, "instrument_mappings", { 201 "source_name": "TEXT", 202 "source_platform": "TEXT", 203 "source_account": "TEXT", 204 "source_label": "TEXT", 205 "normalized_name": "TEXT", 206 "isin": "TEXT", 207 "ticker": "TEXT", 208 "exchange": "TEXT", 209 "currency": "TEXT", 210 "asset_class": "TEXT DEFAULT 'other'", 211 "confidence": "TEXT DEFAULT '0'", 212 }) 213 _add_missing_columns(conn, "platform_account_mappings", { 214 "source_platform": "TEXT", 215 "normalized_platform": "TEXT", 216 "normalized_account_name": "TEXT", 217 "account_type": "TEXT DEFAULT 'other'", 218 "currency": "TEXT", 219 "internal_platform_id": "TEXT", 220 "internal_account_id": "TEXT", 221 }) 222 _add_missing_columns(conn, "broker_import_dry_runs", { 223 "source_platform": "TEXT", 224 "source_filename_hash": "TEXT", 225 "detected_snapshot_date": "TEXT", 226 "snapshot_date_status": "TEXT DEFAULT 'missing'", 227 "candidate_positions": "INTEGER DEFAULT 0", 228 "candidate_cash_rows": "INTEGER DEFAULT 0", 229 "mapped_positions": "INTEGER DEFAULT 0", 230 "blocked_positions": "INTEGER DEFAULT 0", 231 "warnings_count": "INTEGER DEFAULT 0", 232 "errors_count": "INTEGER DEFAULT 0", 233 "summary_json": "TEXT DEFAULT '{}'", 234 "session_status": "TEXT DEFAULT 'active'", 235 "is_current": "INTEGER DEFAULT 0", 236 "archived_at": "TEXT", 237 "discarded_at": "TEXT", 238 }) 239 240 241 def _create_broker_import_review_items(conn: Connection) -> None: 242 conn.executescript( 243 """ 244 CREATE TABLE IF NOT EXISTS broker_import_review_items ( 245 review_item_id TEXT PRIMARY KEY, 246 dry_run_id TEXT NOT NULL REFERENCES broker_import_dry_runs(dry_run_id), 247 source_platform TEXT NOT NULL, 248 source_file_type TEXT NOT NULL, 249 source_row_ref TEXT NOT NULL, 250 row_hash TEXT NOT NULL, 251 source_label TEXT, 252 normalized_name TEXT, 253 detected_asset_class TEXT, 254 detected_currency TEXT, 255 detected_quantity_present INTEGER NOT NULL DEFAULT 0, 256 detected_market_value_present INTEGER NOT NULL DEFAULT 0, 257 isin TEXT, 258 ticker TEXT, 259 exchange TEXT, 260 mapped_instrument_id TEXT REFERENCES instruments(instrument_id), 261 mapped_account_id TEXT REFERENCES accounts(account_id), 262 quality_flags_json TEXT NOT NULL, 263 review_status TEXT NOT NULL DEFAULT 'open', 264 reviewer_note TEXT, 265 created_at TEXT NOT NULL, 266 updated_at TEXT 267 ); 268 CREATE INDEX IF NOT EXISTS idx_broker_review_items_dry_run ON broker_import_review_items(dry_run_id); 269 CREATE INDEX IF NOT EXISTS idx_broker_review_items_status ON broker_import_review_items(review_status); 270 """ 271 ) 272 _add_missing_columns(conn, "broker_import_review_items", { 273 "import_readiness_status": "TEXT DEFAULT 'not_ready'", 274 "reviewer_confirmed": "INTEGER DEFAULT 0", 275 "snapshot_date_confirmed": "INTEGER DEFAULT 0", 276 "ticker_exchange_confirmed": "INTEGER DEFAULT 0", 277 "account_mapping_status": "TEXT DEFAULT 'missing'", 278 }) 279 280 def _create_broker_import_execution_plans(conn: Connection) -> None: 281 conn.executescript( 282 """ 283 CREATE TABLE IF NOT EXISTS broker_import_execution_plans ( 284 execution_plan_id TEXT PRIMARY KEY, 285 dry_run_id TEXT NOT NULL REFERENCES broker_import_dry_runs(dry_run_id), 286 review_item_id TEXT NOT NULL REFERENCES broker_import_review_items(review_item_id), 287 source_platform TEXT NOT NULL, 288 target_account_id TEXT NOT NULL REFERENCES accounts(account_id), 289 target_instrument_id TEXT NOT NULL REFERENCES instruments(instrument_id), 290 transaction_type TEXT NOT NULL DEFAULT 'initial_position_snapshot', 291 snapshot_date TEXT, 292 payload_status TEXT NOT NULL, 293 payload_quality_flags_json TEXT NOT NULL DEFAULT '[]', 294 source_row_hash TEXT NOT NULL, 295 planned_write_summary_json TEXT NOT NULL DEFAULT '{}', 296 execution_status TEXT NOT NULL DEFAULT 'planned', 297 transaction_id TEXT REFERENCES transactions(transaction_id), 298 created_at TEXT NOT NULL, 299 updated_at TEXT, 300 executed_at TEXT, 301 notes TEXT 302 ); 303 CREATE UNIQUE INDEX IF NOT EXISTS idx_broker_execution_plan_review_item ON broker_import_execution_plans(review_item_id); 304 CREATE UNIQUE INDEX IF NOT EXISTS idx_broker_execution_plan_source_hash ON broker_import_execution_plans(source_row_hash, target_account_id, target_instrument_id, transaction_type); 305 """ 306 ) 307 308 def _add_transaction_void_columns(conn: Connection) -> None: 309 _add_missing_columns(conn, "transactions", { 310 "is_voided": "INTEGER NOT NULL DEFAULT 0", 311 "voided_at": "TEXT", 312 "void_reason": "TEXT", 313 "voided_by": "TEXT", 314 "correction_of_transaction_id": "TEXT REFERENCES transactions(transaction_id)", 315 "correction_reason": "TEXT", 316 }) 317 conn.execute("CREATE INDEX IF NOT EXISTS idx_transactions_voided ON transactions(is_voided)") 318 conn.execute("CREATE INDEX IF NOT EXISTS idx_transactions_correction_of ON transactions(correction_of_transaction_id)") 319 320 321 def _create_fx_market_data_tables(conn: Connection) -> None: 322 conn.executescript( 323 """ 324 CREATE TABLE IF NOT EXISTS instrument_price_mappings ( 325 mapping_id TEXT PRIMARY KEY, 326 instrument_id TEXT NOT NULL REFERENCES instruments(instrument_id), 327 isin TEXT, 328 ticker TEXT, 329 exchange TEXT, 330 currency TEXT, 331 provider TEXT NOT NULL, 332 provider_symbol TEXT, 333 provider_market TEXT, 334 mapping_status TEXT NOT NULL DEFAULT 'needs_manual_review', 335 confidence TEXT, 336 notes TEXT, 337 created_at TEXT NOT NULL, 338 updated_at TEXT, 339 UNIQUE(instrument_id, provider) 340 ); 341 CREATE INDEX IF NOT EXISTS idx_instrument_price_mappings_status ON instrument_price_mappings(mapping_status); 342 CREATE INDEX IF NOT EXISTS idx_instrument_price_mappings_provider_symbol ON instrument_price_mappings(provider, provider_symbol); 343 """ 344 ) 345 _add_missing_columns(conn, "market_prices", { 346 "provider_market": "TEXT", 347 "error_message": "TEXT", 348 "corporate_action_status": "TEXT NOT NULL DEFAULT 'not_checked'", 349 }) 350 _add_missing_columns(conn, "fx_rates", { 351 "error_status": "TEXT", 352 "error_message": "TEXT", 353 }) 354 _add_missing_columns(conn, "instrument_price_mappings", { 355 "hedge_status": "TEXT NOT NULL DEFAULT 'unknown'", 356 "is_currency_hedged": "INTEGER NOT NULL DEFAULT 0", 357 "hedged_to_currency": "TEXT", 358 "instrument_status": "TEXT NOT NULL DEFAULT 'unknown'", 359 "valuation_policy": "TEXT NOT NULL DEFAULT 'live_price'", 360 "trading_currency": "TEXT", 361 "base_exposure_currency": "TEXT", 362 "source_symbol": "TEXT", 363 "source_venue": "TEXT NOT NULL DEFAULT 'unknown'", 364 "source_currency": "TEXT", 365 }) 366 conn.executescript( 367 """ 368 CREATE TABLE IF NOT EXISTS instrument_catalog_entries ( 369 catalog_entry_id TEXT PRIMARY KEY, 370 asset_class TEXT NOT NULL, 371 name TEXT NOT NULL, 372 normalized_name TEXT NOT NULL, 373 isin TEXT, 374 ticker TEXT, 375 exchange TEXT, 376 trading_currency TEXT, 377 instrument_currency TEXT, 378 provider TEXT, 379 provider_symbol TEXT, 380 provider_market TEXT, 381 country TEXT, 382 sector TEXT, 383 issuer TEXT, 384 fund_type TEXT, 385 is_currency_hedged INTEGER, 386 hedged_to_currency TEXT, 387 hedge_status TEXT NOT NULL DEFAULT 'unknown', 388 instrument_status TEXT NOT NULL DEFAULT 'unknown', 389 valuation_policy TEXT NOT NULL DEFAULT 'live_price', 390 source TEXT NOT NULL DEFAULT 'manual', 391 source_confidence TEXT NOT NULL DEFAULT 'low', 392 last_verified_at TEXT, 393 notes TEXT, 394 last_price TEXT, 395 price_currency TEXT, 396 price_date TEXT, 397 price_source TEXT, 398 exchange_name TEXT, 399 mic TEXT, 400 security_type TEXT, 401 created_at TEXT NOT NULL, 402 updated_at TEXT 403 ); 404 CREATE INDEX IF NOT EXISTS idx_catalog_isin ON instrument_catalog_entries(isin); 405 CREATE INDEX IF NOT EXISTS idx_catalog_ticker_exchange ON instrument_catalog_entries(ticker, exchange, trading_currency); 406 CREATE INDEX IF NOT EXISTS idx_catalog_provider_symbol ON instrument_catalog_entries(provider, provider_symbol); 407 CREATE INDEX IF NOT EXISTS idx_catalog_normalized_name ON instrument_catalog_entries(normalized_name); 408 """ 409 ) 410 _add_missing_columns(conn, "instrument_catalog_entries", CATALOG_OPTIONAL_COLUMNS) 411 conn.executescript( 412 """ 413 CREATE TABLE IF NOT EXISTS instrument_price_mapping_candidates ( 414 candidate_id TEXT PRIMARY KEY, 415 instrument_id TEXT NOT NULL REFERENCES instruments(instrument_id), 416 isin TEXT, 417 candidate_provider TEXT NOT NULL, 418 candidate_provider_symbol TEXT NOT NULL, 419 candidate_exchange TEXT, 420 candidate_currency TEXT, 421 candidate_name TEXT, 422 candidate_asset_class TEXT, 423 candidate_is_hedged INTEGER, 424 candidate_hedged_to_currency TEXT, 425 candidate_hedge_status TEXT NOT NULL DEFAULT 'unknown', 426 candidate_instrument_status TEXT NOT NULL DEFAULT 'unknown', 427 candidate_valuation_policy TEXT NOT NULL DEFAULT 'live_price', 428 ranking_score INTEGER NOT NULL DEFAULT 0, 429 ranking_reason TEXT, 430 risk_flags TEXT, 431 recommended_action TEXT NOT NULL DEFAULT 'needs_manual_review', 432 confidence TEXT NOT NULL DEFAULT 'low', 433 evidence_source TEXT, 434 evidence_note TEXT, 435 review_status TEXT NOT NULL DEFAULT 'proposed', 436 created_at TEXT NOT NULL, 437 updated_at TEXT 438 ); 439 CREATE INDEX IF NOT EXISTS idx_ipmc_instrument ON instrument_price_mapping_candidates(instrument_id); 440 CREATE INDEX IF NOT EXISTS idx_ipmc_review_status ON instrument_price_mapping_candidates(review_status); 441 CREATE INDEX IF NOT EXISTS idx_ipmc_confidence ON instrument_price_mapping_candidates(confidence); 442 """ 443 ) 444 _add_missing_columns(conn, "instrument_price_mapping_candidates", { 445 "candidate_asset_class": "TEXT", 446 "candidate_hedge_status": "TEXT NOT NULL DEFAULT 'unknown'", 447 "candidate_valuation_policy": "TEXT NOT NULL DEFAULT 'live_price'", 448 "ranking_score": "INTEGER NOT NULL DEFAULT 0", 449 "ranking_reason": "TEXT", 450 "risk_flags": "TEXT", 451 "recommended_action": "TEXT NOT NULL DEFAULT 'needs_manual_review'", 452 }) 453 conn.execute("CREATE INDEX IF NOT EXISTS idx_ipmc_ranking_score ON instrument_price_mapping_candidates(ranking_score)") 454 conn.execute("CREATE UNIQUE INDEX IF NOT EXISTS idx_market_prices_unique_provider_date ON market_prices(instrument_id, price_date, provider)") 455 conn.execute("CREATE INDEX IF NOT EXISTS idx_market_prices_instrument_date ON market_prices(instrument_id, price_date)") 456 conn.execute("CREATE UNIQUE INDEX IF NOT EXISTS idx_fx_rates_unique_provider_date ON fx_rates(base_currency, quote_currency, rate_date, provider, rate_type)") 457 conn.execute("CREATE INDEX IF NOT EXISTS idx_fx_rates_pair_date ON fx_rates(base_currency, quote_currency, rate_date)") 458 459 460 def _create_postfinance_baseline_mapping_audit_v1(conn: Connection) -> None: 461 """Keep source trading labels separate from provider valuation lines.""" 462 463 _add_missing_columns(conn, "instrument_price_mappings", { 464 "source_symbol": "TEXT", 465 "source_venue": "TEXT NOT NULL DEFAULT 'unknown'", 466 "source_currency": "TEXT", 467 }) 468 duplicates = conn.execute( 469 """SELECT UPPER(isin) FROM instruments 470 WHERE isin IS NOT NULL AND trim(isin)!='' 471 GROUP BY UPPER(isin) HAVING COUNT(*)>1 LIMIT 1""" 472 ).fetchone() 473 if duplicates: 474 raise ValueError("duplicate canonical instrument ISIN prevents migration 43") 475 conn.execute("DROP INDEX IF EXISTS idx_instruments_unique_isin") 476 conn.execute( 477 """CREATE UNIQUE INDEX idx_instruments_unique_isin 478 ON instruments(UPPER(isin)) WHERE isin IS NOT NULL AND trim(isin)!=''""" 479 ) 480 481 482 def _create_instrument_import_candidates(conn: Connection) -> None: 483 conn.executescript( 484 """ 485 CREATE TABLE IF NOT EXISTS instrument_import_candidates ( 486 candidate_id TEXT PRIMARY KEY, 487 source_file TEXT NOT NULL, 488 platform TEXT NOT NULL, 489 account TEXT, 490 source_row_number TEXT, 491 raw_name TEXT, 492 raw_ticker TEXT, 493 raw_isin TEXT, 494 raw_currency TEXT, 495 raw_quantity TEXT, 496 raw_asset_class TEXT, 497 extracted_by TEXT NOT NULL DEFAULT 'deterministic_parser', 498 extraction_confidence TEXT NOT NULL DEFAULT 'low', 499 proposed_name TEXT, 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, 581 fx_status TEXT NOT NULL CHECK(fx_status IN ('not_needed','ok','missing','manual_override','estimated')), 582 category_id TEXT REFERENCES budget_categories(category_id), 583 status TEXT NOT NULL CHECK(status IN ('draft','confirmed','reversed','archived')), 584 source_type TEXT NOT NULL CHECK(source_type IN ('manual','import_candidate_later','system')), 585 notes TEXT, 586 created_at TEXT NOT NULL, 587 updated_at TEXT, 588 reversal_of_transaction_id TEXT REFERENCES budget_transactions(budget_transaction_id) 589 ); 590 CREATE INDEX IF NOT EXISTS idx_budget_transactions_account_date ON budget_transactions(account_id, transaction_date); 591 CREATE INDEX IF NOT EXISTS idx_budget_transactions_category ON budget_transactions(category_id); 592 CREATE INDEX IF NOT EXISTS idx_budget_transactions_status ON budget_transactions(status); 593 594 CREATE TABLE IF NOT EXISTS budget_transaction_tags ( 595 budget_transaction_id TEXT NOT NULL REFERENCES budget_transactions(budget_transaction_id), 596 tag_id TEXT NOT NULL REFERENCES budget_tags(tag_id), 597 PRIMARY KEY (budget_transaction_id, tag_id) 598 ); 599 600 CREATE TABLE IF NOT EXISTS budget_transfers ( 601 transfer_id TEXT PRIMARY KEY, 602 from_transaction_id TEXT NOT NULL REFERENCES budget_transactions(budget_transaction_id), 603 to_transaction_id TEXT NOT NULL REFERENCES budget_transactions(budget_transaction_id), 604 from_account_id TEXT NOT NULL REFERENCES budget_accounts(budget_account_id), 605 to_account_id TEXT NOT NULL REFERENCES budget_accounts(budget_account_id), 606 amount_original TEXT NOT NULL, 607 currency_original TEXT NOT NULL, 608 fx_rate_to_chf TEXT, 609 notes TEXT, 610 created_at TEXT NOT NULL 611 ); 612 """ 613 ) 614 _add_missing_columns(conn, "budget_transactions", {"reversal_of_transaction_id": "TEXT REFERENCES budget_transactions(budget_transaction_id)"}) 615 ts = utc_now() 616 categories = [ 617 ("bcat_income", "Einnahmen", "income"), 618 ("bcat_housing", "Wohnen", "expense"), 619 ("bcat_food_household", "Essen & Haushalt", "expense"), 620 ("bcat_mobility", "Mobilität", "expense"), 621 ("bcat_insurance", "Versicherungen", "expense"), 622 ("bcat_health", "Gesundheit", "expense"), 623 ("bcat_children_family", "Kinder/Familie", "expense"), 624 ("bcat_leisure_subs", "Freizeit/Abos", "expense"), 625 ("bcat_travel", "Ferien/Reisen", "expense"), 626 ("bcat_taxes", "Steuern", "expense"), 627 ("bcat_saving_investing", "Sparen/Investieren", "neutral"), 628 ("bcat_other", "Sonstiges", "neutral"), 629 ("bcat_review_needed", "Review nötig", "neutral"), 630 ] 631 for order, (category_id, name, category_type) in enumerate(categories, start=10): 632 conn.execute( 633 "INSERT OR IGNORE INTO budget_categories(category_id, parent_category_id, name, category_type, color, icon, is_active, sort_order, created_at, updated_at) VALUES (?, NULL, ?, ?, NULL, NULL, 1, ?, ?, ?)", 634 (category_id, name, category_type, order, ts, ts), 635 ) 636 tags = ["Fixkosten", "Subscription", "Kinder", "Ferien", "Projekt", "Migros", "VISA", "Review", "Einmalig", "Rückerstattung"] 637 for name in tags: 638 conn.execute("INSERT OR IGNORE INTO budget_tags(tag_id, name, color, is_active, created_at, updated_at) VALUES (?, ?, NULL, 1, ?, ?)", ("btag_" + name.lower().replace(" ", "_").replace("ü", "ue"), name, ts, ts)) 639 640 641 def _create_budget_phase11_tables(conn: Connection) -> None: 642 conn.executescript( 643 """ 644 CREATE TABLE IF NOT EXISTS budget_plan_items ( 645 plan_item_id TEXT PRIMARY KEY, 646 plan_month TEXT NOT NULL, 647 category_id TEXT NOT NULL REFERENCES budget_categories(category_id), 648 name TEXT NOT NULL, 649 monthly_amount_chf TEXT, 650 annual_amount_chf TEXT, 651 cadence TEXT NOT NULL DEFAULT 'monthly' CHECK(cadence IN ('monthly','quarterly','annual','one_time','irregular')), 652 is_fixed_cost INTEGER NOT NULL DEFAULT 0, 653 source_type TEXT NOT NULL DEFAULT 'manual' CHECK(source_type IN ('manual','excel_seed_dry_run','system')), 654 notes TEXT, 655 is_active INTEGER NOT NULL DEFAULT 1, 656 created_at TEXT NOT NULL, 657 updated_at TEXT 658 ); 659 CREATE INDEX IF NOT EXISTS idx_budget_plan_items_month ON budget_plan_items(plan_month); 660 CREATE INDEX IF NOT EXISTS idx_budget_plan_items_category ON budget_plan_items(category_id); 661 662 CREATE TABLE IF NOT EXISTS budget_excel_seed_dry_runs ( 663 dry_run_id TEXT PRIMARY KEY, 664 source_label TEXT NOT NULL, 665 source_hash TEXT, 666 sheet_count INTEGER NOT NULL, 667 current_budget_sheet TEXT, 668 main_category_count INTEGER NOT NULL DEFAULT 0, 669 position_count INTEGER NOT NULL DEFAULT 0, 670 fixed_cost_candidate_count INTEGER NOT NULL DEFAULT 0, 671 budget_plan_candidate_count INTEGER NOT NULL DEFAULT 0, 672 warnings_json TEXT NOT NULL DEFAULT '[]', 673 created_at TEXT NOT NULL, 674 notes TEXT 675 ); 676 677 CREATE TABLE IF NOT EXISTS budget_excel_seed_candidates ( 678 candidate_id TEXT PRIMARY KEY, 679 dry_run_id TEXT NOT NULL REFERENCES budget_excel_seed_dry_runs(dry_run_id), 680 candidate_type TEXT NOT NULL CHECK(candidate_type IN ('category','budget_plan','fixed_cost')), 681 sheet_name TEXT NOT NULL, 682 category_name TEXT, 683 parent_category_name TEXT, 684 position_name TEXT, 685 cadence TEXT, 686 has_monthly_values INTEGER NOT NULL DEFAULT 0, 687 has_annual_value INTEGER NOT NULL DEFAULT 0, 688 quality_flags_json TEXT NOT NULL DEFAULT '[]', 689 created_at TEXT NOT NULL 690 ); 691 CREATE INDEX IF NOT EXISTS idx_budget_excel_seed_candidates_dry_run ON budget_excel_seed_candidates(dry_run_id); 692 693 CREATE TABLE IF NOT EXISTS budget_seed_candidates ( 694 seed_candidate_id TEXT PRIMARY KEY, 695 source_file_label TEXT NOT NULL, 696 source_sheet TEXT NOT NULL, 697 source_row_or_range TEXT, 698 candidate_type TEXT NOT NULL CHECK(candidate_type IN ('category','budget_plan','recurring_candidate','unclean_range')), 699 source_label TEXT, 700 proposed_category_id TEXT REFERENCES budget_categories(category_id), 701 proposed_parent_label TEXT, 702 proposed_name TEXT, 703 proposed_period_type TEXT CHECK(proposed_period_type IS NULL OR proposed_period_type IN ('monthly','annual','fixed_like','unclear')), 704 proposed_amount_text TEXT, 705 currency TEXT NOT NULL DEFAULT 'CHF', 706 confidence TEXT NOT NULL DEFAULT '0', 707 requires_review INTEGER NOT NULL DEFAULT 1, 708 status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending','accepted','edited','ignored','needs_review','confirmed')), 709 notes TEXT, 710 created_at TEXT NOT NULL, 711 updated_at TEXT 712 ); 713 CREATE INDEX IF NOT EXISTS idx_budget_seed_candidates_status ON budget_seed_candidates(status); 714 CREATE INDEX IF NOT EXISTS idx_budget_seed_candidates_type ON budget_seed_candidates(candidate_type); 715 CREATE INDEX IF NOT EXISTS idx_budget_seed_candidates_sheet ON budget_seed_candidates(source_sheet); 716 """ 717 ) 718 _add_missing_columns(conn, "budget_seed_candidates", { 719 "source_file_label": "TEXT", 720 "source_sheet": "TEXT", 721 "source_row_or_range": "TEXT", 722 "candidate_type": "TEXT", 723 "source_label": "TEXT", 724 "proposed_category_id": "TEXT REFERENCES budget_categories(category_id)", 725 "proposed_parent_label": "TEXT", 726 "proposed_name": "TEXT", 727 "proposed_period_type": "TEXT", 728 "proposed_amount_text": "TEXT", 729 "currency": "TEXT DEFAULT 'CHF'", 730 "confidence": "TEXT DEFAULT '0'", 731 "requires_review": "INTEGER DEFAULT 1", 732 "status": "TEXT DEFAULT 'pending'", 733 "notes": "TEXT", 734 "created_at": "TEXT", 735 "updated_at": "TEXT", 736 }) 737 738 739 740 def _create_budget_phase14_tables(conn: Connection) -> None: 741 conn.executescript( 742 """ 743 CREATE TABLE IF NOT EXISTS budget_transaction_candidates ( 744 transaction_candidate_id TEXT PRIMARY KEY, 745 source_file_label TEXT NOT NULL, 746 source_row_or_range TEXT, 747 source_type TEXT NOT NULL DEFAULT 'csv_seed', 748 transaction_date TEXT, 749 description TEXT NOT NULL, 750 merchant TEXT, 751 amount_original TEXT, 752 currency_original TEXT NOT NULL DEFAULT 'CHF', 753 proposed_category_id TEXT REFERENCES budget_categories(category_id), 754 proposed_category_name TEXT, 755 duplicate_of_transaction_id TEXT REFERENCES budget_transactions(budget_transaction_id), 756 confidence TEXT NOT NULL DEFAULT '0', 757 requires_review INTEGER NOT NULL DEFAULT 1, 758 status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending','needs_review','ignored','confirmed','duplicate')), 759 notes TEXT, 760 created_at TEXT NOT NULL, 761 updated_at TEXT 762 ); 763 CREATE INDEX IF NOT EXISTS idx_budget_transaction_candidates_status ON budget_transaction_candidates(status); 764 CREATE INDEX IF NOT EXISTS idx_budget_transaction_candidates_source ON budget_transaction_candidates(source_file_label); 765 CREATE INDEX IF NOT EXISTS idx_budget_transaction_candidates_category ON budget_transaction_candidates(proposed_category_id); 766 """ 767 ) 768 769 770 def _create_budget_phase15_tables(conn: Connection) -> None: 771 # Phase 1.4 created budget_transaction_candidates with a narrow CHECK on status. 772 # Phase 1.5 needs explicit non-booking statuses such as covered_by_migros, 773 # transfer_candidate and superseded. Rebuild once if the old CHECK is present. 774 row = conn.execute("SELECT sql FROM sqlite_master WHERE type='table' AND name='budget_transaction_candidates'").fetchone() 775 if row and row["sql"] and "CHECK(status IN" in row["sql"]: 776 conn.execute("ALTER TABLE budget_transaction_candidates RENAME TO budget_transaction_candidates__phase14") 777 conn.executescript( 778 """ 779 CREATE TABLE budget_transaction_candidates ( 780 transaction_candidate_id TEXT PRIMARY KEY, 781 source_file_label TEXT NOT NULL, 782 source_row_or_range TEXT, 783 source_type TEXT NOT NULL DEFAULT 'csv_seed', 784 transaction_date TEXT, 785 description TEXT NOT NULL, 786 merchant TEXT, 787 amount_original TEXT, 788 currency_original TEXT NOT NULL DEFAULT 'CHF', 789 proposed_category_id TEXT REFERENCES budget_categories(category_id), 790 proposed_category_name TEXT, 791 duplicate_of_transaction_id TEXT REFERENCES budget_transactions(budget_transaction_id), 792 confidence TEXT NOT NULL DEFAULT '0', 793 requires_review INTEGER NOT NULL DEFAULT 1, 794 status TEXT NOT NULL DEFAULT 'pending', 795 notes TEXT, 796 created_at TEXT NOT NULL, 797 updated_at TEXT, 798 classification TEXT, 799 review_reason TEXT, 800 rule_id TEXT, 801 source_priority INTEGER NOT NULL DEFAULT 50, 802 covered_by_source TEXT, 803 receipt_key TEXT, 804 linked_candidate_id TEXT, 805 account_source TEXT, 806 raw_fingerprint TEXT 807 ); 808 INSERT INTO budget_transaction_candidates( 809 transaction_candidate_id, source_file_label, source_row_or_range, source_type, 810 transaction_date, description, merchant, amount_original, currency_original, 811 proposed_category_id, proposed_category_name, duplicate_of_transaction_id, 812 confidence, requires_review, status, notes, created_at, updated_at 813 ) 814 SELECT transaction_candidate_id, source_file_label, source_row_or_range, source_type, 815 transaction_date, description, merchant, amount_original, currency_original, 816 proposed_category_id, proposed_category_name, duplicate_of_transaction_id, 817 confidence, requires_review, status, notes, created_at, updated_at 818 FROM budget_transaction_candidates__phase14; 819 DROP TABLE budget_transaction_candidates__phase14; 820 """ 821 ) 822 _add_missing_columns(conn, "budget_transaction_candidates", { 823 "classification": "TEXT", 824 "review_reason": "TEXT", 825 "rule_id": "TEXT", 826 "rule_name": "TEXT", 827 "source_priority": "INTEGER NOT NULL DEFAULT 50", 828 "covered_by_source": "TEXT", 829 "receipt_key": "TEXT", 830 "linked_candidate_id": "TEXT", 831 "account_source": "TEXT", 832 "raw_fingerprint": "TEXT", 833 }) 834 conn.executescript( 835 """ 836 CREATE INDEX IF NOT EXISTS idx_budget_transaction_candidates_status ON budget_transaction_candidates(status); 837 CREATE INDEX IF NOT EXISTS idx_budget_transaction_candidates_source ON budget_transaction_candidates(source_file_label); 838 CREATE INDEX IF NOT EXISTS idx_budget_transaction_candidates_category ON budget_transaction_candidates(proposed_category_id); 839 CREATE INDEX IF NOT EXISTS idx_budget_transaction_candidates_classification ON budget_transaction_candidates(classification); 840 CREATE INDEX IF NOT EXISTS idx_budget_transaction_candidates_receipt ON budget_transaction_candidates(receipt_key); 841 842 CREATE TABLE IF NOT EXISTS budget_import_line_items ( 843 line_item_id TEXT PRIMARY KEY, 844 transaction_candidate_id TEXT NOT NULL REFERENCES budget_transaction_candidates(transaction_candidate_id), 845 source_file_label TEXT NOT NULL, 846 receipt_key TEXT NOT NULL, 847 source_row_or_range TEXT, 848 item_name TEXT, 849 quantity TEXT, 850 is_promotion INTEGER NOT NULL DEFAULT 0, 851 amount_original TEXT, 852 currency_original TEXT NOT NULL DEFAULT 'CHF', 853 raw_fingerprint TEXT, 854 created_at TEXT NOT NULL 855 ); 856 CREATE INDEX IF NOT EXISTS idx_budget_import_line_items_candidate ON budget_import_line_items(transaction_candidate_id); 857 CREATE INDEX IF NOT EXISTS idx_budget_import_line_items_receipt ON budget_import_line_items(receipt_key); 858 859 CREATE TABLE IF NOT EXISTS budget_candidate_splits ( 860 split_id TEXT PRIMARY KEY, 861 transaction_candidate_id TEXT NOT NULL REFERENCES budget_transaction_candidates(transaction_candidate_id), 862 category_id TEXT REFERENCES budget_categories(category_id), 863 amount_original TEXT NOT NULL, 864 notes TEXT, 865 created_at TEXT NOT NULL, 866 updated_at TEXT 867 ); 868 CREATE INDEX IF NOT EXISTS idx_budget_candidate_splits_candidate ON budget_candidate_splits(transaction_candidate_id); 869 870 CREATE TABLE IF NOT EXISTS budget_import_rules ( 871 rule_id TEXT PRIMARY KEY, 872 rule_type TEXT NOT NULL, 873 pattern TEXT NOT NULL, 874 source_type TEXT, 875 target_action TEXT NOT NULL, 876 target_category_id TEXT REFERENCES budget_categories(category_id), 877 threshold_amount TEXT, 878 confidence TEXT NOT NULL DEFAULT '0.80', 879 is_active INTEGER NOT NULL DEFAULT 1, 880 notes TEXT, 881 created_at TEXT NOT NULL, 882 updated_at TEXT 883 ); 884 CREATE INDEX IF NOT EXISTS idx_budget_import_rules_active ON budget_import_rules(is_active, source_type); 885 """ 886 ) 887 888 889 def _create_budget_phase18_tables(conn: Connection) -> None: 890 _add_missing_columns(conn, "budget_transaction_candidates", { 891 "confirmed_transaction_id": "TEXT REFERENCES budget_transactions(budget_transaction_id)", 892 "confirmed_at": "TEXT", 893 "confirmed_by": "TEXT", 894 }) 895 _add_missing_columns(conn, "budget_transactions", { 896 "source_candidate_id": "TEXT", 897 }) 898 row = conn.execute("SELECT sql FROM sqlite_master WHERE type='table' AND name='budget_transactions'").fetchone() 899 sql = row["sql"] if row else "" 900 if "source_type IN ('manual','import_candidate_later','system')" in sql: 901 conn.execute("ALTER TABLE budget_transactions RENAME TO budget_transactions__phase18") 902 conn.executescript( 903 """ 904 CREATE TABLE budget_transactions ( 905 budget_transaction_id TEXT PRIMARY KEY, 906 account_id TEXT NOT NULL REFERENCES budget_accounts(budget_account_id), 907 transaction_type TEXT NOT NULL CHECK(transaction_type IN ('income','expense','transfer','refund','fee','adjustment','reversal')), 908 transaction_date TEXT NOT NULL, 909 booking_date TEXT, 910 description TEXT NOT NULL, 911 payee TEXT, 912 merchant_id TEXT, 913 amount_original TEXT NOT NULL, 914 currency_original TEXT NOT NULL, 915 fx_rate_to_chf TEXT, 916 amount_chf TEXT, 917 fx_status TEXT NOT NULL CHECK(fx_status IN ('not_needed','ok','missing','manual_override','estimated')), 918 category_id TEXT REFERENCES budget_categories(category_id), 919 status TEXT NOT NULL CHECK(status IN ('draft','confirmed','reversed','archived')), 920 source_type TEXT NOT NULL CHECK(source_type IN ('manual','import_candidate','import_candidate_later','system')), 921 notes TEXT, 922 created_at TEXT NOT NULL, 923 updated_at TEXT, 924 reversal_of_transaction_id TEXT REFERENCES budget_transactions(budget_transaction_id), 925 source_candidate_id TEXT 926 ); 927 INSERT INTO budget_transactions( 928 budget_transaction_id, account_id, transaction_type, transaction_date, booking_date, 929 description, payee, merchant_id, amount_original, currency_original, fx_rate_to_chf, 930 amount_chf, fx_status, category_id, status, source_type, notes, created_at, updated_at, 931 reversal_of_transaction_id, source_candidate_id 932 ) 933 SELECT budget_transaction_id, account_id, transaction_type, transaction_date, booking_date, 934 description, payee, merchant_id, amount_original, currency_original, fx_rate_to_chf, 935 amount_chf, fx_status, category_id, status, source_type, notes, created_at, updated_at, 936 reversal_of_transaction_id, NULL 937 FROM budget_transactions__phase18; 938 DROP TABLE budget_transactions__phase18; 939 """ 940 ) 941 cand_row = conn.execute("SELECT sql FROM sqlite_master WHERE type='table' AND name='budget_transaction_candidates'").fetchone() 942 cand_sql = cand_row["sql"] if cand_row else "" 943 if "budget_transactions__phase18" in cand_sql: 944 conn.execute("ALTER TABLE budget_transaction_candidates RENAME TO budget_transaction_candidates__phase18") 945 conn.executescript( 946 """ 947 CREATE TABLE budget_transaction_candidates ( 948 transaction_candidate_id TEXT PRIMARY KEY, 949 source_file_label TEXT NOT NULL, 950 source_row_or_range TEXT, 951 source_type TEXT NOT NULL DEFAULT 'csv_seed', 952 transaction_date TEXT, 953 description TEXT NOT NULL, 954 merchant TEXT, 955 amount_original TEXT, 956 currency_original TEXT NOT NULL DEFAULT 'CHF', 957 proposed_category_id TEXT REFERENCES budget_categories(category_id), 958 proposed_category_name TEXT, 959 duplicate_of_transaction_id TEXT REFERENCES budget_transactions(budget_transaction_id), 960 confidence TEXT NOT NULL DEFAULT '0', 961 requires_review INTEGER NOT NULL DEFAULT 1, 962 status TEXT NOT NULL DEFAULT 'pending', 963 notes TEXT, 964 created_at TEXT NOT NULL, 965 updated_at TEXT, 966 classification TEXT, 967 review_reason TEXT, 968 rule_id TEXT, 969 rule_name TEXT, 970 source_priority INTEGER NOT NULL DEFAULT 50, 971 covered_by_source TEXT, 972 receipt_key TEXT, 973 linked_candidate_id TEXT, 974 account_source TEXT, 975 raw_fingerprint TEXT, 976 confirmed_transaction_id TEXT REFERENCES budget_transactions(budget_transaction_id), 977 confirmed_at TEXT, 978 confirmed_by TEXT 979 ); 980 INSERT INTO budget_transaction_candidates( 981 transaction_candidate_id, source_file_label, source_row_or_range, source_type, 982 transaction_date, description, merchant, amount_original, currency_original, 983 proposed_category_id, proposed_category_name, duplicate_of_transaction_id, 984 confidence, requires_review, status, notes, created_at, updated_at, 985 classification, review_reason, rule_id, rule_name, source_priority, covered_by_source, 986 receipt_key, linked_candidate_id, account_source, raw_fingerprint, 987 confirmed_transaction_id, confirmed_at, confirmed_by 988 ) 989 SELECT transaction_candidate_id, source_file_label, source_row_or_range, source_type, 990 transaction_date, description, merchant, amount_original, currency_original, 991 proposed_category_id, proposed_category_name, duplicate_of_transaction_id, 992 confidence, requires_review, status, notes, created_at, updated_at, 993 classification, review_reason, rule_id, rule_name, source_priority, covered_by_source, 994 receipt_key, linked_candidate_id, account_source, raw_fingerprint, 995 confirmed_transaction_id, confirmed_at, confirmed_by 996 FROM budget_transaction_candidates__phase18; 997 DROP TABLE budget_transaction_candidates__phase18; 998 """ 999 ) 1000 phase18_child_repairs = ( 1001 ( 1002 "budget_transaction_tags", 1003 "budget_transactions__phase18", 1004 """ 1005 CREATE TABLE IF NOT EXISTS budget_transaction_tags__phase18_fixed ( 1006 budget_transaction_id TEXT NOT NULL, 1007 tag_id TEXT NOT NULL REFERENCES budget_tags(tag_id), 1008 PRIMARY KEY (budget_transaction_id, tag_id) 1009 ); 1010 INSERT OR IGNORE INTO budget_transaction_tags__phase18_fixed(budget_transaction_id, tag_id) 1011 SELECT budget_transaction_id, tag_id FROM budget_transaction_tags; 1012 DROP TABLE budget_transaction_tags; 1013 ALTER TABLE budget_transaction_tags__phase18_fixed RENAME TO budget_transaction_tags; 1014 """, 1015 ), 1016 ( 1017 "budget_transfers", 1018 "budget_transactions__phase18", 1019 """ 1020 CREATE TABLE IF NOT EXISTS budget_transfers__phase18_fixed ( 1021 transfer_id TEXT PRIMARY KEY, 1022 from_transaction_id TEXT NOT NULL, 1023 to_transaction_id TEXT NOT NULL, 1024 from_account_id TEXT NOT NULL REFERENCES budget_accounts(budget_account_id), 1025 to_account_id TEXT NOT NULL REFERENCES budget_accounts(budget_account_id), 1026 amount_original TEXT NOT NULL, 1027 currency_original TEXT NOT NULL, 1028 fx_rate_to_chf TEXT, 1029 notes TEXT, 1030 created_at TEXT NOT NULL 1031 ); 1032 INSERT OR IGNORE INTO budget_transfers__phase18_fixed(transfer_id, from_transaction_id, to_transaction_id, from_account_id, to_account_id, amount_original, currency_original, fx_rate_to_chf, notes, created_at) 1033 SELECT transfer_id, from_transaction_id, to_transaction_id, from_account_id, to_account_id, amount_original, currency_original, fx_rate_to_chf, notes, created_at FROM budget_transfers; 1034 DROP TABLE budget_transfers; 1035 ALTER TABLE budget_transfers__phase18_fixed RENAME TO budget_transfers; 1036 """, 1037 ), 1038 ( 1039 "budget_import_line_items", 1040 "budget_transaction_candidates__phase18", 1041 """ 1042 CREATE TABLE IF NOT EXISTS budget_import_line_items__phase18_fixed ( 1043 line_item_id TEXT PRIMARY KEY, 1044 transaction_candidate_id TEXT NOT NULL, 1045 source_file_label TEXT NOT NULL, 1046 receipt_key TEXT NOT NULL, 1047 source_row_or_range TEXT, 1048 item_name TEXT, 1049 quantity TEXT, 1050 is_promotion INTEGER NOT NULL DEFAULT 0, 1051 amount_original TEXT, 1052 currency_original TEXT NOT NULL DEFAULT 'CHF', 1053 raw_fingerprint TEXT, 1054 created_at TEXT NOT NULL 1055 ); 1056 INSERT OR IGNORE INTO budget_import_line_items__phase18_fixed(line_item_id, transaction_candidate_id, source_file_label, receipt_key, source_row_or_range, item_name, quantity, is_promotion, amount_original, currency_original, raw_fingerprint, created_at) 1057 SELECT line_item_id, transaction_candidate_id, source_file_label, receipt_key, source_row_or_range, item_name, quantity, is_promotion, amount_original, currency_original, raw_fingerprint, created_at FROM budget_import_line_items; 1058 DROP TABLE budget_import_line_items; 1059 ALTER TABLE budget_import_line_items__phase18_fixed RENAME TO budget_import_line_items; 1060 """, 1061 ), 1062 ( 1063 "budget_candidate_splits", 1064 "budget_transaction_candidates__phase18", 1065 """ 1066 CREATE TABLE IF NOT EXISTS budget_candidate_splits__phase18_fixed ( 1067 split_id TEXT PRIMARY KEY, 1068 transaction_candidate_id TEXT NOT NULL, 1069 category_id TEXT REFERENCES budget_categories(category_id), 1070 amount_original TEXT NOT NULL, 1071 notes TEXT, 1072 created_at TEXT NOT NULL, 1073 updated_at TEXT 1074 ); 1075 INSERT OR IGNORE INTO budget_candidate_splits__phase18_fixed(split_id, transaction_candidate_id, category_id, amount_original, notes, created_at, updated_at) 1076 SELECT split_id, transaction_candidate_id, category_id, amount_original, notes, created_at, updated_at FROM budget_candidate_splits; 1077 DROP TABLE budget_candidate_splits; 1078 ALTER TABLE budget_candidate_splits__phase18_fixed RENAME TO budget_candidate_splits; 1079 """, 1080 ), 1081 ) 1082 for table_name, obsolete_parent, repair_sql in phase18_child_repairs: 1083 table_row = conn.execute( 1084 "SELECT sql FROM sqlite_master WHERE type='table' AND name=?", 1085 (table_name,), 1086 ).fetchone() 1087 table_sql = table_row["sql"] if table_row else "" 1088 if obsolete_parent in table_sql: 1089 conn.executescript(repair_sql) 1090 conn.executescript( 1091 """ 1092 CREATE INDEX IF NOT EXISTS idx_budget_transactions_account_date ON budget_transactions(account_id, transaction_date); 1093 CREATE INDEX IF NOT EXISTS idx_budget_transactions_category ON budget_transactions(category_id); 1094 CREATE INDEX IF NOT EXISTS idx_budget_transactions_status ON budget_transactions(status); 1095 CREATE INDEX IF NOT EXISTS idx_budget_transactions_source_candidate ON budget_transactions(source_candidate_id); 1096 CREATE INDEX IF NOT EXISTS idx_budget_transaction_candidates_confirmed_tx ON budget_transaction_candidates(confirmed_transaction_id); 1097 CREATE INDEX IF NOT EXISTS idx_budget_import_line_items_candidate ON budget_import_line_items(transaction_candidate_id); 1098 CREATE INDEX IF NOT EXISTS idx_budget_import_line_items_receipt ON budget_import_line_items(receipt_key); 1099 CREATE INDEX IF NOT EXISTS idx_budget_candidate_splits_candidate ON budget_candidate_splits(transaction_candidate_id); 1100 """ 1101 ) 1102 1103 1104 1105 def _create_budget_phase19_tables(conn: Connection) -> None: 1106 _add_missing_columns(conn, "budget_candidate_splits", { 1107 "tag_name": "TEXT", 1108 "confirmed_transaction_id": "TEXT", 1109 }) 1110 conn.executescript( 1111 """ 1112 CREATE TABLE IF NOT EXISTS budget_review_rules ( 1113 rule_id TEXT PRIMARY KEY, 1114 merchant_contains TEXT NOT NULL, 1115 source_type TEXT, 1116 category_id TEXT, 1117 target_status TEXT NOT NULL DEFAULT 'review' CHECK(target_status IN ('auto','review')), 1118 is_active INTEGER NOT NULL DEFAULT 1, 1119 created_at TEXT NOT NULL, 1120 updated_at TEXT 1121 ); 1122 CREATE INDEX IF NOT EXISTS idx_budget_review_rules_active ON budget_review_rules(is_active, source_type); 1123 """ 1124 ) 1125 1126 1127 def _create_budget_import_production_v1_tables(conn: Connection) -> None: 1128 _add_missing_columns(conn, "budget_transfers", { 1129 "transfer_type": "TEXT NOT NULL DEFAULT 'internal_transfer'", 1130 }) 1131 _add_missing_columns(conn, "budget_transaction_candidates", { 1132 "merchant_id": "TEXT", 1133 "merchant_display_name": "TEXT", 1134 "status_label": "TEXT", 1135 }) 1136 _add_missing_columns(conn, "budget_review_rules", { 1137 "priority": "INTEGER NOT NULL DEFAULT 100", 1138 "confidence": "TEXT NOT NULL DEFAULT '0.82'", 1139 "notes": "TEXT", 1140 }) 1141 conn.executescript( 1142 """ 1143 CREATE TABLE IF NOT EXISTS budget_merchants ( 1144 merchant_id TEXT PRIMARY KEY, 1145 display_name TEXT NOT NULL, 1146 normalized_name TEXT NOT NULL, 1147 default_category_id TEXT REFERENCES budget_categories(category_id), 1148 is_active INTEGER NOT NULL DEFAULT 1, 1149 notes TEXT, 1150 created_at TEXT NOT NULL, 1151 updated_at TEXT 1152 ); 1153 CREATE UNIQUE INDEX IF NOT EXISTS idx_budget_merchants_normalized ON budget_merchants(normalized_name); 1154 CREATE INDEX IF NOT EXISTS idx_budget_merchants_active ON budget_merchants(is_active); 1155 1156 CREATE TABLE IF NOT EXISTS budget_merchant_aliases ( 1157 alias_id TEXT PRIMARY KEY, 1158 merchant_id TEXT NOT NULL REFERENCES budget_merchants(merchant_id), 1159 pattern TEXT NOT NULL, 1160 match_type TEXT NOT NULL DEFAULT 'contains' CHECK(match_type IN ('contains','exact','regex')), 1161 source_type TEXT, 1162 priority INTEGER NOT NULL DEFAULT 100, 1163 is_active INTEGER NOT NULL DEFAULT 1, 1164 created_at TEXT NOT NULL, 1165 updated_at TEXT 1166 ); 1167 CREATE INDEX IF NOT EXISTS idx_budget_merchant_aliases_merchant ON budget_merchant_aliases(merchant_id); 1168 CREATE INDEX IF NOT EXISTS idx_budget_merchant_aliases_active ON budget_merchant_aliases(is_active, priority); 1169 """ 1170 ) 1171 1172 1173 1174 def _create_budget_categories_ux_fix_tables(conn: Connection) -> None: 1175 _add_missing_columns(conn, "budget_plan_items", {"sort_order": "INTEGER NOT NULL DEFAULT 999"}) 1176 conn.execute("CREATE INDEX IF NOT EXISTS idx_budget_plan_items_sort ON budget_plan_items(is_active, sort_order, name)") 1177 1178 1179 def _create_budget_planning_forecast_v1_tables(conn: Connection) -> None: 1180 conn.executescript( 1181 """ 1182 CREATE TABLE IF NOT EXISTS budget_category_baselines ( 1183 baseline_id TEXT PRIMARY KEY, 1184 category_id TEXT NOT NULL REFERENCES budget_categories(category_id), 1185 year TEXT NOT NULL, 1186 month TEXT, 1187 amount_text TEXT NOT NULL, 1188 currency TEXT NOT NULL DEFAULT 'CHF', 1189 baseline_type TEXT NOT NULL CHECK(baseline_type IN ('actual_previous_year','planned_budget','manual_reference','imported_reference')), 1190 source TEXT NOT NULL DEFAULT 'manual' CHECK(source IN ('manual','excel_budget','import','system')), 1191 notes TEXT, 1192 created_at TEXT NOT NULL, 1193 updated_at TEXT 1194 ); 1195 CREATE INDEX IF NOT EXISTS idx_budget_category_baselines_category_year ON budget_category_baselines(category_id, year, baseline_type); 1196 CREATE UNIQUE INDEX IF NOT EXISTS idx_budget_category_baselines_unique ON budget_category_baselines(category_id, year, COALESCE(month, ''), baseline_type); 1197 """ 1198 ) 1199 1200 1201 def _create_budget_fixed_costs_subscriptions_v1_tables(conn: Connection) -> None: 1202 conn.executescript( 1203 """ 1204 CREATE TABLE IF NOT EXISTS budget_recurring_payments ( 1205 recurring_id TEXT PRIMARY KEY, 1206 name TEXT NOT NULL, 1207 merchant_name TEXT, 1208 merchant_id TEXT REFERENCES budget_merchants(merchant_id), 1209 category_id TEXT NOT NULL REFERENCES budget_categories(category_id), 1210 account_id TEXT REFERENCES budget_accounts(budget_account_id), 1211 expected_amount_text TEXT NOT NULL, 1212 currency TEXT NOT NULL DEFAULT 'CHF', 1213 frequency TEXT NOT NULL CHECK(frequency IN ('monthly','quarterly','yearly','weekly','irregular')), 1214 expected_day_of_month INTEGER, 1215 expected_month INTEGER, 1216 tolerance_amount_text TEXT, 1217 tolerance_percent TEXT, 1218 amount_tolerance_pct TEXT NOT NULL DEFAULT '10', 1219 date_tolerance_days INTEGER NOT NULL DEFAULT 5, 1220 recurring_type TEXT NOT NULL CHECK(recurring_type IN ('fixed_cost','subscription','variable_recurring')), 1221 status TEXT NOT NULL CHECK(status IN ('candidate','active','ignored','paused','archived')), 1222 source TEXT NOT NULL CHECK(source IN ('detected','manual','rule')), 1223 confidence TEXT NOT NULL DEFAULT '0', 1224 last_seen_date TEXT, 1225 next_expected_date TEXT, 1226 notes TEXT, 1227 candidate_evidence_json TEXT, 1228 created_at TEXT NOT NULL, 1229 updated_at TEXT 1230 ); 1231 CREATE INDEX IF NOT EXISTS idx_budget_recurring_status ON budget_recurring_payments(status, recurring_type); 1232 CREATE INDEX IF NOT EXISTS idx_budget_recurring_category ON budget_recurring_payments(category_id); 1233 CREATE INDEX IF NOT EXISTS idx_budget_recurring_merchant ON budget_recurring_payments(merchant_id, name); 1234 """ 1235 ) 1236 row = conn.execute("SELECT sql FROM sqlite_master WHERE type='table' AND name='budget_recurring_payments'").fetchone() 1237 if row and "'ignored'" not in (row["sql"] or ""): 1238 conn.executescript( 1239 """ 1240 ALTER TABLE budget_recurring_payments RENAME TO budget_recurring_payments__old_status; 1241 CREATE TABLE budget_recurring_payments ( 1242 recurring_id TEXT PRIMARY KEY, 1243 name TEXT NOT NULL, 1244 merchant_name TEXT, 1245 merchant_id TEXT REFERENCES budget_merchants(merchant_id), 1246 category_id TEXT NOT NULL REFERENCES budget_categories(category_id), 1247 account_id TEXT REFERENCES budget_accounts(budget_account_id), 1248 expected_amount_text TEXT NOT NULL, 1249 currency TEXT NOT NULL DEFAULT 'CHF', 1250 frequency TEXT NOT NULL CHECK(frequency IN ('monthly','quarterly','yearly','weekly','irregular')), 1251 expected_day_of_month INTEGER, 1252 expected_month INTEGER, 1253 tolerance_amount_text TEXT, 1254 tolerance_percent TEXT, 1255 amount_tolerance_pct TEXT NOT NULL DEFAULT '10', 1256 date_tolerance_days INTEGER NOT NULL DEFAULT 5, 1257 recurring_type TEXT NOT NULL CHECK(recurring_type IN ('fixed_cost','subscription','variable_recurring')), 1258 status TEXT NOT NULL CHECK(status IN ('candidate','active','ignored','paused','archived')), 1259 source TEXT NOT NULL CHECK(source IN ('detected','manual','rule')), 1260 confidence TEXT NOT NULL DEFAULT '0', 1261 last_seen_date TEXT, 1262 next_expected_date TEXT, 1263 notes TEXT, 1264 candidate_evidence_json TEXT, 1265 created_at TEXT NOT NULL, 1266 updated_at TEXT 1267 ); 1268 INSERT INTO budget_recurring_payments(recurring_id,name,merchant_name,merchant_id,category_id,account_id,expected_amount_text,currency,frequency,expected_day_of_month,expected_month,tolerance_amount_text,tolerance_percent,amount_tolerance_pct,date_tolerance_days,recurring_type,status,source,confidence,last_seen_date,next_expected_date,notes,candidate_evidence_json,created_at,updated_at) 1269 SELECT recurring_id,name,name,merchant_id,category_id,account_id,expected_amount_text,currency,frequency,expected_day_of_month,expected_month,NULL,amount_tolerance_pct,amount_tolerance_pct,date_tolerance_days,recurring_type,CASE WHEN status='rejected' THEN 'ignored' ELSE status END,source,confidence,last_seen_date,next_expected_date,notes,candidate_evidence_json,created_at,updated_at FROM budget_recurring_payments__old_status; 1270 DROP TABLE budget_recurring_payments__old_status; 1271 CREATE INDEX IF NOT EXISTS idx_budget_recurring_status ON budget_recurring_payments(status, recurring_type); 1272 CREATE INDEX IF NOT EXISTS idx_budget_recurring_category ON budget_recurring_payments(category_id); 1273 CREATE INDEX IF NOT EXISTS idx_budget_recurring_merchant ON budget_recurring_payments(merchant_id, name); 1274 """ 1275 ) 1276 _add_missing_columns(conn, "budget_recurring_payments", { 1277 "merchant_name": "TEXT", 1278 "tolerance_amount_text": "TEXT", 1279 "tolerance_percent": "TEXT", 1280 }) 1281 conn.execute("UPDATE budget_recurring_payments SET merchant_name=COALESCE(merchant_name, name), tolerance_percent=COALESCE(tolerance_percent, amount_tolerance_pct) WHERE merchant_name IS NULL OR tolerance_percent IS NULL") 1282 1283 1284 def _create_budget_monthly_import_rule_learning_v1_tables(conn: Connection) -> None: 1285 _add_missing_columns(conn, "budget_transaction_candidates", { 1286 "import_session_id": "TEXT", 1287 }) 1288 conn.executescript( 1289 """ 1290 CREATE TABLE IF NOT EXISTS budget_import_sessions ( 1291 import_session_id TEXT PRIMARY KEY, 1292 source TEXT, 1293 file_id TEXT, 1294 file_name TEXT, 1295 file_modified_time TEXT, 1296 file_period_start TEXT, 1297 file_period_end TEXT, 1298 profile TEXT, 1299 rows_total INTEGER NOT NULL DEFAULT 0, 1300 new_candidates INTEGER NOT NULL DEFAULT 0, 1301 already_known INTEGER NOT NULL DEFAULT 0, 1302 duplicate_count INTEGER NOT NULL DEFAULT 0, 1303 ignored_count INTEGER NOT NULL DEFAULT 0, 1304 covered_by_source_count INTEGER NOT NULL DEFAULT 0, 1305 error_count INTEGER NOT NULL DEFAULT 0, 1306 status TEXT NOT NULL, 1307 review_url TEXT, 1308 created_at TEXT NOT NULL, 1309 updated_at TEXT NOT NULL 1310 ); 1311 CREATE INDEX IF NOT EXISTS idx_budget_import_sessions_created ON budget_import_sessions(created_at); 1312 CREATE INDEX IF NOT EXISTS idx_budget_import_sessions_source ON budget_import_sessions(source, profile); 1313 1314 CREATE TABLE IF NOT EXISTS budget_rule_suggestions ( 1315 suggestion_id TEXT PRIMARY KEY, 1316 example_candidate_id TEXT REFERENCES budget_transaction_candidates(transaction_candidate_id), 1317 merchant_pattern TEXT NOT NULL, 1318 description_pattern TEXT, 1319 match_type TEXT NOT NULL DEFAULT 'contains', 1320 source_scope TEXT NOT NULL DEFAULT 'all', 1321 category_id TEXT REFERENCES budget_categories(category_id), 1322 category_name TEXT, 1323 recurring_type TEXT, 1324 amount_min_text TEXT, 1325 amount_max_text TEXT, 1326 confidence TEXT NOT NULL DEFAULT '0.70', 1327 affected_open_candidate_count INTEGER NOT NULL DEFAULT 0, 1328 status TEXT NOT NULL DEFAULT 'suggested', 1329 notes TEXT, 1330 created_at TEXT NOT NULL, 1331 updated_at TEXT NOT NULL 1332 ); 1333 CREATE INDEX IF NOT EXISTS idx_budget_rule_suggestions_status ON budget_rule_suggestions(status, source_scope); 1334 """ 1335 ) 1336 1337 1338 def _create_grocery_optimizer_v1_tables(conn: Connection) -> None: 1339 conn.executescript( 1340 """ 1341 CREATE TABLE IF NOT EXISTS grocery_product_items ( 1342 product_item_id TEXT PRIMARY KEY, 1343 source_type TEXT NOT NULL DEFAULT 'migros_receipt', 1344 receipt_id TEXT NOT NULL, 1345 receipt_key TEXT, 1346 source_line_id TEXT, 1347 purchase_date TEXT, 1348 store_name TEXT, 1349 raw_product_name TEXT NOT NULL, 1350 normalized_product_name TEXT, 1351 normalization_confidence TEXT NOT NULL DEFAULT '0', 1352 brand_hint TEXT, 1353 quantity_hint TEXT, 1354 quantity_text TEXT, 1355 unit TEXT, 1356 unit_price_text TEXT, 1357 total_price_text TEXT NOT NULL, 1358 currency TEXT NOT NULL DEFAULT 'CHF', 1359 action_label TEXT, 1360 include_in_analysis INTEGER NOT NULL DEFAULT 1, 1361 notes TEXT, 1362 health_analysis_status TEXT NOT NULL DEFAULT 'prepared_not_run', 1363 health_flags_json TEXT NOT NULL DEFAULT '{}', 1364 health_notes TEXT, 1365 created_at TEXT NOT NULL 1366 ); 1367 CREATE INDEX IF NOT EXISTS idx_grocery_items_receipt ON grocery_product_items(receipt_id, purchase_date); 1368 1369 CREATE TABLE IF NOT EXISTS grocery_product_matches ( 1370 match_id TEXT PRIMARY KEY, 1371 product_item_id TEXT NOT NULL REFERENCES grocery_product_items(product_item_id), 1372 retailer TEXT NOT NULL, 1373 candidate_product_name TEXT NOT NULL, 1374 candidate_url TEXT, 1375 candidate_brand TEXT, 1376 candidate_package_size TEXT, 1377 candidate_unit TEXT, 1378 candidate_price_text TEXT, 1379 candidate_unit_price_text TEXT, 1380 price_currency TEXT NOT NULL DEFAULT 'CHF', 1381 match_confidence TEXT NOT NULL DEFAULT '0', 1382 match_reason TEXT, 1383 quality_flags_json TEXT NOT NULL DEFAULT '[]', 1384 fetched_at TEXT NOT NULL, 1385 source TEXT NOT NULL DEFAULT 'manual', 1386 status TEXT NOT NULL DEFAULT 'suggested' 1387 ); 1388 CREATE INDEX IF NOT EXISTS idx_grocery_matches_item ON grocery_product_matches(product_item_id, status); 1389 1390 CREATE TABLE IF NOT EXISTS grocery_product_details_cache ( 1391 detail_id TEXT PRIMARY KEY, 1392 retailer TEXT NOT NULL, 1393 product_url TEXT NOT NULL, 1394 product_name TEXT, 1395 price_text TEXT, 1396 unit_price_text TEXT, 1397 package_size TEXT, 1398 ingredients_text TEXT, 1399 nutrition_json TEXT NOT NULL DEFAULT '{}', 1400 fetched_at TEXT NOT NULL, 1401 cache_status TEXT NOT NULL DEFAULT 'cached', 1402 source_hash TEXT 1403 ); 1404 CREATE UNIQUE INDEX IF NOT EXISTS idx_grocery_detail_cache_url ON grocery_product_details_cache(retailer, product_url); 1405 1406 CREATE TABLE IF NOT EXISTS grocery_optimization_runs ( 1407 run_id TEXT PRIMARY KEY, 1408 receipt_id TEXT NOT NULL, 1409 run_date TEXT NOT NULL, 1410 selected_retailers_json TEXT NOT NULL, 1411 max_store_count INTEGER NOT NULL DEFAULT 3, 1412 original_total_text TEXT NOT NULL, 1413 optimized_total_text TEXT NOT NULL, 1414 estimated_savings_text TEXT NOT NULL, 1415 quality_status TEXT NOT NULL, 1416 summary_json TEXT NOT NULL, 1417 report_path TEXT, 1418 created_at TEXT NOT NULL 1419 ); 1420 CREATE INDEX IF NOT EXISTS idx_grocery_runs_receipt ON grocery_optimization_runs(receipt_id, created_at); 1421 """ 1422 ) 1423 1424 1425 def _add_grocery_price_provider_v1_columns(conn: Connection) -> None: 1426 existing = _table_columns(conn, "grocery_product_details_cache") 1427 columns = { 1428 "brand": "TEXT", 1429 "image_url": "TEXT", 1430 "price_decimal_text": "TEXT", 1431 "currency": "TEXT NOT NULL DEFAULT 'CHF'", 1432 "unit": "TEXT", 1433 "unit_price_decimal_text": "TEXT", 1434 "availability_status": "TEXT", 1435 "promotion_text": "TEXT", 1436 "source": "TEXT NOT NULL DEFAULT 'cache'", 1437 "confidence": "TEXT NOT NULL DEFAULT '0'", 1438 "quality_flags_json": "TEXT NOT NULL DEFAULT '[]'", 1439 "raw_result_json": "TEXT NOT NULL DEFAULT '{}'", 1440 } 1441 for name, col_type in columns.items(): 1442 if name not in existing: 1443 conn.execute(f"ALTER TABLE grocery_product_details_cache ADD COLUMN {name} {col_type}") 1444 1445 1446 def _create_grocery_matching_learning_v2_tables(conn: Connection) -> None: 1447 conn.executescript( 1448 """ 1449 CREATE TABLE IF NOT EXISTS grocery_product_mappings ( 1450 mapping_id TEXT PRIMARY KEY, 1451 source_product_normalized_name TEXT NOT NULL, 1452 source_product_raw_name TEXT, 1453 source_retailer TEXT NOT NULL DEFAULT 'Migros', 1454 target_retailer TEXT NOT NULL, 1455 target_product_name TEXT NOT NULL, 1456 target_product_url TEXT NOT NULL, 1457 target_brand TEXT, 1458 target_package_size TEXT, 1459 target_unit TEXT, 1460 target_price_text TEXT, 1461 target_unit_price_text TEXT, 1462 price_currency TEXT NOT NULL DEFAULT 'CHF', 1463 match_type TEXT NOT NULL, 1464 status TEXT NOT NULL DEFAULT 'needs_review', 1465 confidence TEXT NOT NULL DEFAULT '0', 1466 user_note TEXT, 1467 source_match_id TEXT, 1468 quality_flags_json TEXT NOT NULL DEFAULT '[]', 1469 health_analysis_status TEXT NOT NULL DEFAULT 'prepared_not_run', 1470 health_flags_json TEXT NOT NULL DEFAULT '{}', 1471 health_notes TEXT, 1472 created_at TEXT NOT NULL, 1473 updated_at TEXT NOT NULL, 1474 last_price_checked_at TEXT 1475 ); 1476 CREATE INDEX IF NOT EXISTS idx_grocery_mappings_source ON grocery_product_mappings(source_product_normalized_name, source_retailer, status); 1477 CREATE INDEX IF NOT EXISTS idx_grocery_mappings_target ON grocery_product_mappings(target_retailer, target_product_url, status); 1478 CREATE TABLE IF NOT EXISTS grocery_mapping_audit_events ( 1479 audit_id TEXT PRIMARY KEY, 1480 mapping_id TEXT, 1481 product_item_id TEXT, 1482 action TEXT NOT NULL, 1483 source TEXT NOT NULL, 1484 payload_json TEXT NOT NULL DEFAULT '{}', 1485 created_at TEXT NOT NULL 1486 ); 1487 CREATE INDEX IF NOT EXISTS idx_grocery_mapping_audit_mapping ON grocery_mapping_audit_events(mapping_id, created_at); 1488 """ 1489 ) 1490 _add_missing_columns(conn, "grocery_product_mappings", {"target_price_date": "TEXT", "target_price_source": "TEXT"}) 1491 1492 1493 def _create_market_quote_chart_tables(conn: Connection) -> None: 1494 conn.executescript( 1495 """ 1496 CREATE TABLE IF NOT EXISTS equity_price_points ( 1497 point_id TEXT PRIMARY KEY, 1498 instrument_id TEXT NOT NULL REFERENCES instruments(instrument_id), 1499 provider TEXT NOT NULL, 1500 provider_symbol TEXT, 1501 timestamp TEXT NOT NULL, 1502 price TEXT NOT NULL, 1503 currency TEXT NOT NULL, 1504 interval TEXT NOT NULL DEFAULT 'quote', 1505 source_quality TEXT NOT NULL DEFAULT 'fresh', 1506 fetched_at TEXT NOT NULL, 1507 UNIQUE(instrument_id, provider, provider_symbol, timestamp, interval) 1508 ); 1509 CREATE INDEX IF NOT EXISTS idx_equity_price_points_instrument_time ON equity_price_points(instrument_id, timestamp); 1510 CREATE TABLE IF NOT EXISTS equity_intraday_candles ( 1511 candle_id TEXT PRIMARY KEY, 1512 instrument_id TEXT NOT NULL REFERENCES instruments(instrument_id), 1513 provider TEXT NOT NULL, 1514 provider_symbol TEXT NOT NULL, 1515 range_key TEXT NOT NULL DEFAULT '1d', 1516 interval_key TEXT NOT NULL DEFAULT '5m', 1517 timestamp TEXT NOT NULL, 1518 open TEXT NOT NULL, 1519 close TEXT NOT NULL, 1520 low TEXT NOT NULL, 1521 high TEXT NOT NULL, 1522 volume TEXT, 1523 currency TEXT, 1524 exchange_timezone TEXT, 1525 quality_status TEXT NOT NULL DEFAULT 'fresh', 1526 fetched_at TEXT NOT NULL, 1527 UNIQUE(instrument_id, provider, provider_symbol, range_key, interval_key, timestamp) 1528 ); 1529 CREATE INDEX IF NOT EXISTS idx_equity_intraday_candles_lookup ON equity_intraday_candles(instrument_id, range_key, interval_key, fetched_at); 1530 CREATE TABLE IF NOT EXISTS crypto_price_points ( 1531 point_id TEXT PRIMARY KEY, 1532 asset_id TEXT NOT NULL REFERENCES crypto_assets(asset_id), 1533 provider TEXT NOT NULL, 1534 provider_symbol TEXT, 1535 timestamp TEXT NOT NULL, 1536 price TEXT NOT NULL, 1537 currency TEXT NOT NULL, 1538 interval TEXT NOT NULL DEFAULT 'quote', 1539 source_quality TEXT NOT NULL DEFAULT 'fresh', 1540 fetched_at TEXT NOT NULL, 1541 UNIQUE(asset_id, provider, provider_symbol, timestamp, interval, currency) 1542 ); 1543 CREATE INDEX IF NOT EXISTS idx_crypto_price_points_asset_time ON crypto_price_points(asset_id, currency, timestamp); 1544 """ 1545 ) 1546 _add_missing_columns(conn, "crypto_assets", {"binance_symbol": "TEXT", "binance_mapping_status": "TEXT NOT NULL DEFAULT 'missing'"}) 1547 1548 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: 1596 _add_missing_columns( 1597 conn, 1598 "budget_transaction_candidates", 1599 { 1600 "signed_amount_original": "TEXT", 1601 "value_date": "TEXT", 1602 }, 1603 ) 1604 conn.executescript( 1605 """ 1606 CREATE TABLE IF NOT EXISTS budget_transfer_pairs ( 1607 transfer_pair_id TEXT PRIMARY KEY, 1608 source_candidate_id TEXT NOT NULL REFERENCES budget_transaction_candidates(transaction_candidate_id), 1609 target_candidate_id TEXT REFERENCES budget_transaction_candidates(transaction_candidate_id), 1610 source_account_id TEXT NOT NULL REFERENCES budget_accounts(budget_account_id), 1611 target_account_id TEXT REFERENCES budget_accounts(budget_account_id), 1612 source_signed_amount TEXT NOT NULL, 1613 target_signed_amount TEXT, 1614 currency TEXT NOT NULL, 1615 source_booking_date TEXT, 1616 target_booking_date TEXT, 1617 source_value_date TEXT, 1618 target_value_date TEXT, 1619 status TEXT NOT NULL CHECK(status IN ('proposed','confirmed','rejected','superseded','unmatched')), 1620 quality_status TEXT NOT NULL, 1621 evidence_json TEXT NOT NULL DEFAULT '{}', 1622 reason_codes_json TEXT NOT NULL DEFAULT '[]', 1623 budget_effect_chf TEXT NOT NULL DEFAULT '0', 1624 confirmed_transfer_id TEXT REFERENCES budget_transfers(transfer_id), 1625 superseded_by_pair_id TEXT REFERENCES budget_transfer_pairs(transfer_pair_id), 1626 created_at TEXT NOT NULL, 1627 created_by TEXT NOT NULL DEFAULT 'system', 1628 updated_at TEXT NOT NULL, 1629 decided_at TEXT, 1630 decided_by TEXT, 1631 decision_note TEXT 1632 ); 1633 CREATE INDEX IF NOT EXISTS idx_budget_transfer_pairs_status 1634 ON budget_transfer_pairs(status, created_at); 1635 CREATE INDEX IF NOT EXISTS idx_budget_transfer_pairs_candidates 1636 ON budget_transfer_pairs(source_candidate_id, target_candidate_id, status); 1637 CREATE UNIQUE INDEX IF NOT EXISTS idx_budget_transfer_pairs_confirmed_source 1638 ON budget_transfer_pairs(source_candidate_id) WHERE status='confirmed'; 1639 CREATE UNIQUE INDEX IF NOT EXISTS idx_budget_transfer_pairs_confirmed_target 1640 ON budget_transfer_pairs(target_candidate_id) WHERE status='confirmed'; 1641 """ 1642 ) 1643 1644 1645 def _create_portfolio_policy_tables(conn: Connection) -> None: 1646 conn.executescript(""" 1647 CREATE TABLE IF NOT EXISTS portfolio_policies ( 1648 policy_id TEXT PRIMARY KEY, version INTEGER NOT NULL UNIQUE, is_active INTEGER NOT NULL, 1649 effective_from TEXT NOT NULL, previous_policy_id TEXT REFERENCES portfolio_policies(policy_id), 1650 base_currency TEXT NOT NULL, horizon TEXT, objective TEXT, liquidity_reserve TEXT, 1651 monthly_contribution TEXT, max_single_position_pct TEXT, max_crypto_pct TEXT, 1652 rebalance_tolerance_pct TEXT, min_transaction_amount TEXT, benchmarks_json TEXT NOT NULL DEFAULT '[]', 1653 restrictions_json TEXT NOT NULL DEFAULT '[]', request_fingerprint TEXT NOT NULL UNIQUE, 1654 audit_id TEXT NOT NULL, created_at TEXT NOT NULL 1655 ); 1656 CREATE UNIQUE INDEX IF NOT EXISTS idx_portfolio_policies_one_active ON portfolio_policies(is_active) WHERE is_active=1; 1657 CREATE TABLE IF NOT EXISTS portfolio_policy_allocations ( 1658 allocation_id TEXT PRIMARY KEY, policy_id TEXT NOT NULL REFERENCES portfolio_policies(policy_id), 1659 asset_class TEXT NOT NULL, target_pct TEXT NOT NULL, lower_pct TEXT NOT NULL, upper_pct TEXT NOT NULL, 1660 UNIQUE(policy_id, asset_class) 1661 ); 1662 CREATE INDEX IF NOT EXISTS idx_portfolio_policies_version_desc ON portfolio_policies(version DESC); 1663 CREATE INDEX IF NOT EXISTS idx_portfolio_policy_allocations_policy ON portfolio_policy_allocations(policy_id); 1664 CREATE TRIGGER IF NOT EXISTS portfolio_policies_content_immutable 1665 BEFORE UPDATE ON portfolio_policies 1666 WHEN NEW.policy_id != OLD.policy_id 1667 OR NEW.version != OLD.version 1668 OR NEW.effective_from != OLD.effective_from 1669 OR COALESCE(NEW.previous_policy_id, '') != COALESCE(OLD.previous_policy_id, '') 1670 OR NEW.base_currency != OLD.base_currency 1671 OR COALESCE(NEW.horizon, '') != COALESCE(OLD.horizon, '') 1672 OR COALESCE(NEW.objective, '') != COALESCE(OLD.objective, '') 1673 OR COALESCE(NEW.liquidity_reserve, '') != COALESCE(OLD.liquidity_reserve, '') 1674 OR COALESCE(NEW.monthly_contribution, '') != COALESCE(OLD.monthly_contribution, '') 1675 OR COALESCE(NEW.max_single_position_pct, '') != COALESCE(OLD.max_single_position_pct, '') 1676 OR COALESCE(NEW.max_crypto_pct, '') != COALESCE(OLD.max_crypto_pct, '') 1677 OR COALESCE(NEW.rebalance_tolerance_pct, '') != COALESCE(OLD.rebalance_tolerance_pct, '') 1678 OR COALESCE(NEW.min_transaction_amount, '') != COALESCE(OLD.min_transaction_amount, '') 1679 OR NEW.benchmarks_json != OLD.benchmarks_json 1680 OR NEW.restrictions_json != OLD.restrictions_json 1681 OR NEW.request_fingerprint != OLD.request_fingerprint 1682 OR NEW.audit_id != OLD.audit_id 1683 OR NEW.created_at != OLD.created_at 1684 BEGIN SELECT RAISE(ABORT, 'portfolio policy content is immutable'); END; 1685 CREATE TRIGGER IF NOT EXISTS portfolio_policies_no_delete 1686 BEFORE DELETE ON portfolio_policies 1687 BEGIN SELECT RAISE(ABORT, 'portfolio policy versions cannot be deleted'); END; 1688 CREATE TRIGGER IF NOT EXISTS portfolio_policy_allocations_immutable_update 1689 BEFORE UPDATE ON portfolio_policy_allocations 1690 BEGIN SELECT RAISE(ABORT, 'portfolio policy allocations are immutable'); END; 1691 CREATE TRIGGER IF NOT EXISTS portfolio_policy_allocations_no_delete 1692 BEFORE DELETE ON portfolio_policy_allocations 1693 BEGIN SELECT RAISE(ABORT, 'portfolio policy allocations cannot be deleted'); END; 1694 """) 1695 _add_missing_columns( 1696 conn, 1697 "portfolio_policies", 1698 {"confirmation_id": "TEXT", "payload_hash": "TEXT"}, 1699 ) 1700 conn.executescript( 1701 """ 1702 CREATE UNIQUE INDEX IF NOT EXISTS idx_portfolio_policies_confirmation_id 1703 ON portfolio_policies(confirmation_id) 1704 WHERE confirmation_id IS NOT NULL; 1705 CREATE TRIGGER IF NOT EXISTS portfolio_policy_request_identity_immutable 1706 BEFORE UPDATE ON portfolio_policies 1707 WHEN COALESCE(NEW.confirmation_id, '') != COALESCE(OLD.confirmation_id, '') 1708 OR COALESCE(NEW.payload_hash, '') != COALESCE(OLD.payload_hash, '') 1709 BEGIN SELECT RAISE(ABORT, 'portfolio policy request identity is immutable'); END; 1710 """ 1711 ) 1712 1713 1714 def _create_portfolio_performance_tables(conn: Connection) -> None: 1715 """Add reproducible valuation inputs without replacing the transaction ledger.""" 1716 1717 _add_missing_columns( 1718 conn, 1719 "transactions", 1720 { 1721 "activity_kind": "TEXT", 1722 "booking_date": "TEXT", 1723 "event_timestamp": "TEXT", 1724 "base_currency": "TEXT", 1725 "internal_transfer_group_id": "TEXT", 1726 "reversal_of_transaction_id": "TEXT REFERENCES transactions(transaction_id)", 1727 "source_reference": "TEXT", 1728 }, 1729 ) 1730 conn.executescript( 1731 """ 1732 CREATE INDEX IF NOT EXISTS idx_transactions_performance_period 1733 ON transactions(account_id, trade_date, activity_kind); 1734 CREATE INDEX IF NOT EXISTS idx_transactions_internal_transfer_group 1735 ON transactions(internal_transfer_group_id) 1736 WHERE internal_transfer_group_id IS NOT NULL; 1737 CREATE INDEX IF NOT EXISTS idx_transactions_reversal_of 1738 ON transactions(reversal_of_transaction_id) 1739 WHERE reversal_of_transaction_id IS NOT NULL; 1740 1741 CREATE TABLE IF NOT EXISTS portfolio_valuation_snapshots ( 1742 snapshot_id TEXT PRIMARY KEY, 1743 scope_kind TEXT NOT NULL CHECK(scope_kind IN ('account','instrument')), 1744 scope_id TEXT NOT NULL, 1745 account_id TEXT REFERENCES accounts(account_id), 1746 value_original TEXT NOT NULL 1747 CHECK(json_valid(value_original) AND json_type(value_original) IN ('integer','real') AND CAST(value_original AS NUMERIC) >= 0), 1748 currency TEXT NOT NULL CHECK(length(currency)=3 AND currency=upper(currency)), 1749 base_currency TEXT NOT NULL CHECK(length(base_currency)=3 AND base_currency=upper(base_currency)), 1750 fx_rate_to_base TEXT 1751 CHECK(fx_rate_to_base IS NULL OR (json_valid(fx_rate_to_base) AND json_type(fx_rate_to_base) IN ('integer','real') AND CAST(fx_rate_to_base AS NUMERIC) > 0)), 1752 fx_direction TEXT NOT NULL CHECK(fx_direction='original_to_base'), 1753 valuation_at TEXT NOT NULL, 1754 source TEXT NOT NULL, 1755 captured_at TEXT NOT NULL, 1756 snapshot_version INTEGER NOT NULL CHECK(snapshot_version > 0), 1757 supersedes_snapshot_id TEXT REFERENCES portfolio_valuation_snapshots(snapshot_id), 1758 source_reference TEXT, 1759 quality_status TEXT NOT NULL DEFAULT 'complete' 1760 CHECK(quality_status IN ('complete','partial','unavailable')), 1761 reason_codes_json TEXT NOT NULL DEFAULT '[]' 1762 CHECK(json_valid(reason_codes_json) AND json_type(reason_codes_json)='array'), 1763 UNIQUE(scope_kind, scope_id, valuation_at, snapshot_version) 1764 ); 1765 CREATE INDEX IF NOT EXISTS idx_portfolio_valuations_scope_time 1766 ON portfolio_valuation_snapshots(scope_kind, scope_id, valuation_at, captured_at); 1767 CREATE INDEX IF NOT EXISTS idx_portfolio_valuations_account_time 1768 ON portfolio_valuation_snapshots(account_id, valuation_at); 1769 1770 CREATE TRIGGER IF NOT EXISTS portfolio_valuation_snapshots_immutable 1771 BEFORE UPDATE ON portfolio_valuation_snapshots 1772 BEGIN SELECT RAISE(ABORT, 'portfolio valuation snapshots are immutable'); END; 1773 """ 1774 ) 1775 _add_missing_columns( 1776 conn, 1777 "portfolio_valuation_snapshots", 1778 {"reason_codes_json": "TEXT NOT NULL DEFAULT '[]'"}, 1779 ) 1780 conn.executescript( 1781 """ 1782 CREATE TRIGGER IF NOT EXISTS portfolio_valuation_snapshots_no_delete 1783 BEFORE DELETE ON portfolio_valuation_snapshots 1784 BEGIN SELECT RAISE(ABORT, 'portfolio valuation snapshots cannot be deleted'); END; 1785 """ 1786 ) 1787 1788 1789 def _create_investment_performance_scope_v1(conn: Connection) -> None: 1790 """Bind performance inclusion to an explicit, audited role decision.""" 1791 conn.executescript( 1792 """ 1793 CREATE TABLE IF NOT EXISTS performance_scope_classifications ( 1794 account_id TEXT PRIMARY KEY REFERENCES accounts(account_id), 1795 included INTEGER NOT NULL CHECK(included IN (0,1)), 1796 classification_role TEXT NOT NULL, 1797 decision_version TEXT NOT NULL, 1798 audit_id TEXT NOT NULL REFERENCES audit_log(audit_id), 1799 classified_at TEXT NOT NULL 1800 ); 1801 CREATE TABLE IF NOT EXISTS performance_cashflow_coverage ( 1802 account_id TEXT PRIMARY KEY REFERENCES accounts(account_id), 1803 coverage_from TEXT NOT NULL, 1804 coverage_to TEXT NOT NULL, 1805 status TEXT NOT NULL CHECK(status IN ('complete','partial','unavailable')), 1806 source TEXT NOT NULL, 1807 audit_id TEXT NOT NULL REFERENCES audit_log(audit_id), 1808 recorded_at TEXT NOT NULL, 1809 CHECK(coverage_from <= coverage_to) 1810 ); 1811 CREATE INDEX IF NOT EXISTS idx_performance_scope_classifications_decision 1812 ON performance_scope_classifications(decision_version,included,classification_role); 1813 DROP TRIGGER IF EXISTS accounts_new_performance_default_excluded; 1814 DROP TRIGGER IF EXISTS performance_scope_classification_validate_insert; 1815 DROP TRIGGER IF EXISTS performance_scope_classification_validate_update; 1816 DROP TRIGGER IF EXISTS performance_scope_classification_no_delete; 1817 DROP TRIGGER IF EXISTS accounts_performance_include_requires_classification; 1818 DROP TRIGGER IF EXISTS performance_scope_sync_account_insert; 1819 DROP TRIGGER IF EXISTS performance_scope_sync_account_update; 1820 DROP TRIGGER IF EXISTS performance_cashflow_coverage_validate_insert; 1821 DROP TRIGGER IF EXISTS performance_cashflow_coverage_validate_update; 1822 DROP TRIGGER IF EXISTS performance_cashflow_coverage_no_delete; 1823 CREATE TRIGGER performance_scope_classification_validate_insert 1824 BEFORE INSERT ON performance_scope_classifications 1825 WHEN NEW.decision_version<>'investment_performance_scope_v1' 1826 OR NOT ( 1827 (NEW.included=1 AND NEW.classification_role IN ( 1828 'postfinance_etrading_depot','postfinance_etrading_cash', 1829 'canonical_truewealth_total_value','crypto_portfolio' 1830 )) 1831 OR (NEW.included=0 AND NEW.classification_role IN ( 1832 'not_in_investment_performance_scope','postfinance_efinance_control' 1833 )) 1834 ) 1835 OR NOT EXISTS ( 1836 SELECT 1 FROM audit_log al 1837 WHERE al.audit_id=NEW.audit_id 1838 AND al.entity_type='performance_scope_classification' 1839 AND al.entity_id=NEW.account_id 1840 AND al.action='performance_scope_classified' 1841 AND CAST(json_extract(al.new_values_json,'$.performance_included') AS INTEGER)=NEW.included 1842 AND json_extract(al.new_values_json,'$.classification_role')=NEW.classification_role 1843 ) 1844 BEGIN SELECT RAISE(ABORT, 'invalid or unaudited performance scope classification'); END; 1845 CREATE TRIGGER performance_scope_classification_validate_update 1846 BEFORE UPDATE ON performance_scope_classifications 1847 WHEN NEW.audit_id=OLD.audit_id 1848 OR NEW.decision_version<>'investment_performance_scope_v1' 1849 OR NOT ( 1850 (NEW.included=1 AND NEW.classification_role IN ( 1851 'postfinance_etrading_depot','postfinance_etrading_cash', 1852 'canonical_truewealth_total_value','crypto_portfolio' 1853 )) 1854 OR (NEW.included=0 AND NEW.classification_role IN ( 1855 'not_in_investment_performance_scope','postfinance_efinance_control' 1856 )) 1857 ) 1858 OR NOT EXISTS ( 1859 SELECT 1 FROM audit_log al 1860 WHERE al.audit_id=NEW.audit_id 1861 AND al.entity_type='performance_scope_classification' 1862 AND al.entity_id=NEW.account_id 1863 AND al.action='performance_scope_classified' 1864 AND CAST(json_extract(al.new_values_json,'$.performance_included') AS INTEGER)=NEW.included 1865 AND json_extract(al.new_values_json,'$.classification_role')=NEW.classification_role 1866 ) 1867 BEGIN SELECT RAISE(ABORT, 'performance scope update requires a new matching audit'); END; 1868 CREATE TRIGGER performance_scope_classification_no_delete 1869 BEFORE DELETE ON performance_scope_classifications 1870 BEGIN SELECT RAISE(ABORT, 'performance scope classification cannot be deleted'); END; 1871 CREATE TRIGGER performance_cashflow_coverage_validate_insert 1872 BEFORE INSERT ON performance_cashflow_coverage 1873 WHEN NOT EXISTS ( 1874 SELECT 1 FROM audit_log al 1875 WHERE al.audit_id=NEW.audit_id 1876 AND al.entity_type='performance_cashflow_coverage' 1877 AND al.entity_id=NEW.account_id 1878 AND al.action='performance_cashflow_coverage_recorded' 1879 AND json_extract(al.new_values_json,'$.coverage_from')=NEW.coverage_from 1880 AND json_extract(al.new_values_json,'$.coverage_to')=NEW.coverage_to 1881 AND json_extract(al.new_values_json,'$.status')=NEW.status 1882 AND json_extract(al.new_values_json,'$.source')=NEW.source 1883 ) 1884 BEGIN SELECT RAISE(ABORT, 'cashflow coverage requires a matching audit'); END; 1885 CREATE TRIGGER performance_cashflow_coverage_validate_update 1886 BEFORE UPDATE ON performance_cashflow_coverage 1887 WHEN NEW.audit_id=OLD.audit_id OR NOT EXISTS ( 1888 SELECT 1 FROM audit_log al 1889 WHERE al.audit_id=NEW.audit_id 1890 AND al.entity_type='performance_cashflow_coverage' 1891 AND al.entity_id=NEW.account_id 1892 AND al.action='performance_cashflow_coverage_recorded' 1893 AND json_extract(al.new_values_json,'$.coverage_from')=NEW.coverage_from 1894 AND json_extract(al.new_values_json,'$.coverage_to')=NEW.coverage_to 1895 AND json_extract(al.new_values_json,'$.status')=NEW.status 1896 AND json_extract(al.new_values_json,'$.source')=NEW.source 1897 ) 1898 BEGIN SELECT RAISE(ABORT, 'cashflow coverage update requires a new matching audit'); END; 1899 CREATE TRIGGER performance_cashflow_coverage_no_delete 1900 BEFORE DELETE ON performance_cashflow_coverage 1901 BEGIN SELECT RAISE(ABORT, 'cashflow coverage cannot be deleted'); END; 1902 CREATE TRIGGER IF NOT EXISTS accounts_performance_insert_requires_exclusion 1903 BEFORE INSERT ON accounts WHEN NEW.performance_included<>0 1904 BEGIN SELECT RAISE(ABORT, 'new accounts default outside performance scope'); END; 1905 CREATE TRIGGER accounts_performance_include_requires_classification 1906 BEFORE UPDATE OF performance_included ON accounts 1907 WHEN ( 1908 NEW.performance_included=1 AND NOT EXISTS ( 1909 SELECT 1 FROM performance_scope_classifications psc 1910 JOIN audit_log al ON al.audit_id=psc.audit_id 1911 WHERE psc.account_id=NEW.account_id 1912 AND psc.included=1 1913 AND psc.decision_version='investment_performance_scope_v1' 1914 AND psc.classification_role IN ( 1915 'postfinance_etrading_depot','postfinance_etrading_cash', 1916 'canonical_truewealth_total_value','crypto_portfolio' 1917 ) 1918 AND al.entity_type='performance_scope_classification' 1919 AND al.entity_id=NEW.account_id 1920 AND al.action='performance_scope_classified' 1921 AND CAST(json_extract(al.new_values_json,'$.performance_included') AS INTEGER)=1 1922 AND json_extract(al.new_values_json,'$.classification_role')=psc.classification_role 1923 ) 1924 ) OR ( 1925 NEW.performance_included=0 AND EXISTS ( 1926 SELECT 1 FROM performance_scope_classifications psc 1927 WHERE psc.account_id=NEW.account_id AND psc.included<>0 1928 ) 1929 ) 1930 BEGIN SELECT RAISE(ABORT, 'performance flag must match audited classification'); END; 1931 CREATE TRIGGER performance_scope_sync_account_insert 1932 AFTER INSERT ON performance_scope_classifications 1933 BEGIN 1934 UPDATE accounts SET performance_included=NEW.included WHERE account_id=NEW.account_id; 1935 END; 1936 CREATE TRIGGER performance_scope_sync_account_update 1937 AFTER UPDATE OF included ON performance_scope_classifications 1938 BEGIN 1939 UPDATE accounts SET performance_included=NEW.included WHERE account_id=NEW.account_id; 1940 END; 1941 DROP TRIGGER IF EXISTS performance_scope_audit_immutable_update; 1942 DROP TRIGGER IF EXISTS performance_scope_audit_no_delete; 1943 CREATE TRIGGER performance_scope_audit_immutable_update 1944 BEFORE UPDATE ON audit_log 1945 WHEN OLD.entity_type IN ('performance_scope_classification','performance_cashflow_coverage') 1946 BEGIN SELECT RAISE(ABORT, 'performance audit is immutable'); END; 1947 CREATE TRIGGER performance_scope_audit_no_delete 1948 BEFORE DELETE ON audit_log 1949 WHEN OLD.entity_type IN ('performance_scope_classification','performance_cashflow_coverage') 1950 BEGIN SELECT RAISE(ABORT, 'performance audit cannot be deleted'); END; 1951 """ 1952 ) 1953 if conn.execute("SELECT 1 FROM schema_migrations WHERE version>=46").fetchone(): 1954 return 1955 now = utc_now() 1956 rows = conn.execute( 1957 """ 1958 SELECT a.account_id,a.performance_included, 1959 CASE 1960 WHEN pr.role='etrading_depot' THEN 'postfinance_etrading_depot' 1961 WHEN pr.role='etrading_cash' THEN 'postfinance_etrading_cash' 1962 WHEN EXISTS ( 1963 SELECT 1 FROM account_value_snapshots avs 1964 WHERE avs.account_id=a.account_id 1965 AND avs.source_type='truewealth_official_import' 1966 AND avs.updated_at IS NULL AND COALESCE(avs.is_active,1)=1 1967 ) THEN 'canonical_truewealth_total_value' 1968 ELSE 'not_in_investment_performance_scope' 1969 END AS classification_role, 1970 CASE 1971 WHEN pr.role IN ('etrading_depot','etrading_cash') THEN 1 1972 WHEN EXISTS ( 1973 SELECT 1 FROM account_value_snapshots avs 1974 WHERE avs.account_id=a.account_id 1975 AND avs.source_type='truewealth_official_import' 1976 AND avs.updated_at IS NULL AND COALESCE(avs.is_active,1)=1 1977 ) THEN 1 ELSE 0 1978 END AS included 1979 FROM accounts a 1980 LEFT JOIN postfinance_account_roles pr ON pr.account_id=a.account_id 1981 ORDER BY a.account_id 1982 """ 1983 ).fetchall() 1984 for row in rows: 1985 account_id = str(row[0]) 1986 old_value = int(row[1]) 1987 classification_role = str(row[2]) 1988 included = int(row[3]) 1989 audit_id = "audit_perf_scope_v1_" + hashlib.sha256(account_id.encode("utf-8")).hexdigest()[:24] 1990 conn.execute( 1991 """INSERT INTO audit_log( 1992 audit_id,timestamp,source,action,entity_type,entity_id,old_values_json, 1993 new_values_json,user_text_note,created_by,created_at 1994 ) VALUES (?,?,?,?,?,?,?,?,?,?,?)""", 1995 (audit_id, now, "schema_migration_046", "performance_scope_classified", 1996 "performance_scope_classification", account_id, 1997 json.dumps({"performance_included": old_value}, separators=(",", ":"), sort_keys=True), 1998 json.dumps({"performance_included": included, "classification_role": classification_role}, 1999 separators=(",", ":"), sort_keys=True), 2000 "Sprint 14 role-bound investment performance scope decision", "system", now), 2001 ) 2002 conn.execute( 2003 """INSERT INTO performance_scope_classifications( 2004 account_id,included,classification_role,decision_version,audit_id,classified_at 2005 ) VALUES (?,?,?,?,?,?)""", 2006 (account_id, included, classification_role, "investment_performance_scope_v1", audit_id, now), 2007 ) 2008 if old_value != included: 2009 conn.execute( 2010 "UPDATE accounts SET performance_included=? WHERE account_id=?", 2011 (included, account_id), 2012 ) 2013 2014 2015 def _create_portfolio_ingestion_reconciliation_tables(conn: Connection) -> None: 2016 """Add immutable ingestion audit history; previews and reconciliation remain projections.""" 2017 2018 conn.executescript( 2019 """ 2020 CREATE TABLE IF NOT EXISTS portfolio_ingestion_batches ( 2021 batch_id TEXT PRIMARY KEY, 2022 source_key TEXT NOT NULL, 2023 scope_kind TEXT NOT NULL CHECK(scope_kind IN ('portfolio','account')), 2024 scope_id TEXT, 2025 period_from TEXT NOT NULL, 2026 period_to TEXT NOT NULL, 2027 data_cutoff TEXT NOT NULL, 2028 source_revision TEXT NOT NULL, 2029 input_fingerprint TEXT NOT NULL, 2030 preview_id TEXT NOT NULL, 2031 confirmation_id TEXT NOT NULL UNIQUE, 2032 payload_hash TEXT NOT NULL, 2033 status TEXT NOT NULL CHECK(status='confirmed'), 2034 counts_json TEXT NOT NULL CHECK(json_valid(counts_json) AND json_type(counts_json)='object'), 2035 audit_id TEXT NOT NULL, 2036 confirmed_at TEXT NOT NULL, 2037 confirmed_by TEXT NOT NULL DEFAULT 'user' 2038 ); 2039 CREATE INDEX IF NOT EXISTS idx_portfolio_ingestion_batches_source_time 2040 ON portfolio_ingestion_batches(source_key, confirmed_at DESC); 2041 CREATE INDEX IF NOT EXISTS idx_portfolio_ingestion_batches_scope_time 2042 ON portfolio_ingestion_batches(scope_kind, scope_id, confirmed_at DESC); 2043 2044 CREATE TABLE IF NOT EXISTS portfolio_ingestion_items ( 2045 ingestion_item_id TEXT PRIMARY KEY, 2046 batch_id TEXT NOT NULL REFERENCES portfolio_ingestion_batches(batch_id), 2047 source_record_fingerprint TEXT NOT NULL, 2048 source_record_ref TEXT NOT NULL, 2049 record_kind TEXT NOT NULL CHECK(record_kind IN ('activity','valuation')), 2050 disposition TEXT NOT NULL CHECK(disposition IN ('new','unchanged','duplicate','ambiguous','blocked','versioned')), 2051 target_type TEXT, 2052 target_id TEXT, 2053 lineage_hash TEXT NOT NULL, 2054 summary_json TEXT NOT NULL CHECK(json_valid(summary_json) AND json_type(summary_json)='object'), 2055 created_at TEXT NOT NULL, 2056 UNIQUE(batch_id, source_record_ref, record_kind) 2057 ); 2058 CREATE INDEX IF NOT EXISTS idx_portfolio_ingestion_items_batch 2059 ON portfolio_ingestion_items(batch_id, disposition, record_kind); 2060 CREATE INDEX IF NOT EXISTS idx_portfolio_ingestion_items_lineage 2061 ON portfolio_ingestion_items(lineage_hash); 2062 2063 CREATE TRIGGER IF NOT EXISTS portfolio_ingestion_batches_immutable_update 2064 BEFORE UPDATE ON portfolio_ingestion_batches 2065 BEGIN SELECT RAISE(ABORT, 'portfolio ingestion batches are immutable'); END; 2066 CREATE TRIGGER IF NOT EXISTS portfolio_ingestion_batches_no_delete 2067 BEFORE DELETE ON portfolio_ingestion_batches 2068 BEGIN SELECT RAISE(ABORT, 'portfolio ingestion batches cannot be deleted'); END; 2069 CREATE TRIGGER IF NOT EXISTS portfolio_ingestion_items_immutable_update 2070 BEFORE UPDATE ON portfolio_ingestion_items 2071 BEGIN SELECT RAISE(ABORT, 'portfolio ingestion items are immutable'); END; 2072 CREATE TRIGGER IF NOT EXISTS portfolio_ingestion_items_no_delete 2073 BEFORE DELETE ON portfolio_ingestion_items 2074 BEGIN SELECT RAISE(ABORT, 'portfolio ingestion items cannot be deleted'); END; 2075 CREATE TRIGGER IF NOT EXISTS portfolio_ingestion_audit_immutable_update 2076 BEFORE UPDATE ON audit_log WHEN OLD.entity_type='portfolio_ingestion_batch' 2077 BEGIN SELECT RAISE(ABORT, 'portfolio ingestion audit is immutable'); END; 2078 CREATE TRIGGER IF NOT EXISTS portfolio_ingestion_audit_no_delete 2079 BEFORE DELETE ON audit_log WHEN OLD.entity_type='portfolio_ingestion_batch' 2080 BEGIN SELECT RAISE(ABORT, 'portfolio ingestion audit cannot be deleted'); END; 2081 """ 2082 ) 2083 2084 2085 def _create_daily_market_analytics_tables(conn: Connection) -> None: 2086 """Add one bounded run/audit layer around existing price, FX and valuation tables.""" 2087 2088 _add_missing_columns(conn, "market_prices", { 2089 "fetched_at": "TEXT", 2090 "price_type": "TEXT NOT NULL DEFAULT 'unadjusted_close'", 2091 "run_id": "TEXT", 2092 }) 2093 _add_missing_columns(conn, "fx_rates", {"fetched_at": "TEXT", "run_id": "TEXT"}) 2094 conn.executescript( 2095 """ 2096 CREATE TABLE IF NOT EXISTS market_data_runs ( 2097 run_id TEXT PRIMARY KEY, 2098 source_key TEXT NOT NULL DEFAULT 'daily_market_fx_v1', 2099 as_of TEXT NOT NULL, 2100 input_fingerprint TEXT NOT NULL, 2101 status TEXT NOT NULL CHECK(status IN ('running','complete','partial','failed')), 2102 started_at TEXT NOT NULL, 2103 completed_at TEXT, 2104 price_total INTEGER NOT NULL DEFAULT 0, 2105 price_stored INTEGER NOT NULL DEFAULT 0, 2106 fx_total INTEGER NOT NULL DEFAULT 0, 2107 fx_stored INTEGER NOT NULL DEFAULT 0, 2108 benchmark_total INTEGER NOT NULL DEFAULT 0, 2109 benchmark_stored INTEGER NOT NULL DEFAULT 0, 2110 valuation_stored INTEGER NOT NULL DEFAULT 0, 2111 missing_instruments_json TEXT NOT NULL DEFAULT '[]' 2112 CHECK(json_valid(missing_instruments_json) AND json_type(missing_instruments_json)='array'), 2113 reason_codes_json TEXT NOT NULL DEFAULT '[]' 2114 CHECK(json_valid(reason_codes_json) AND json_type(reason_codes_json)='array'), 2115 audit_id TEXT, 2116 UNIQUE(source_key, as_of, input_fingerprint) 2117 ); 2118 CREATE INDEX IF NOT EXISTS idx_market_data_runs_as_of 2119 ON market_data_runs(as_of DESC, started_at DESC); 2120 2121 CREATE TABLE IF NOT EXISTS benchmark_snapshots ( 2122 benchmark_snapshot_id TEXT PRIMARY KEY, 2123 run_id TEXT NOT NULL REFERENCES market_data_runs(run_id), 2124 policy_id TEXT NOT NULL REFERENCES portfolio_policies(policy_id), 2125 benchmark_reference TEXT NOT NULL, 2126 provider TEXT NOT NULL, 2127 provider_symbol TEXT NOT NULL, 2128 price_currency TEXT NOT NULL, 2129 close TEXT NOT NULL, 2130 adjusted_close TEXT, 2131 fx_rate_to_chf TEXT NOT NULL, 2132 value_chf TEXT NOT NULL, 2133 return_type TEXT NOT NULL CHECK(return_type IN ('price_return','total_return','etf_proxy')), 2134 as_of TEXT NOT NULL, 2135 source_as_of TEXT NOT NULL, 2136 fetched_at TEXT NOT NULL, 2137 quality_status TEXT NOT NULL, 2138 reason_codes_json TEXT NOT NULL DEFAULT '[]' 2139 CHECK(json_valid(reason_codes_json) AND json_type(reason_codes_json)='array'), 2140 UNIQUE(run_id, policy_id, benchmark_reference, as_of) 2141 ); 2142 CREATE INDEX IF NOT EXISTS idx_benchmark_snapshots_policy_time 2143 ON benchmark_snapshots(policy_id, benchmark_reference, as_of, fetched_at); 2144 2145 CREATE TABLE IF NOT EXISTS portfolio_analysis_snapshots ( 2146 analysis_snapshot_id TEXT PRIMARY KEY, 2147 run_id TEXT NOT NULL UNIQUE REFERENCES market_data_runs(run_id), 2148 as_of TEXT NOT NULL, 2149 base_currency TEXT NOT NULL DEFAULT 'CHF', 2150 total_value_chf TEXT, 2151 price_coverage_pct TEXT NOT NULL, 2152 fx_coverage_pct TEXT NOT NULL, 2153 benchmark_coverage_pct TEXT NOT NULL, 2154 quality_status TEXT NOT NULL CHECK(quality_status IN ('complete','partial','unavailable')), 2155 reason_codes_json TEXT NOT NULL DEFAULT '[]' 2156 CHECK(json_valid(reason_codes_json) AND json_type(reason_codes_json)='array'), 2157 summary_json TEXT NOT NULL DEFAULT '{}' 2158 CHECK(json_valid(summary_json) AND json_type(summary_json)='object'), 2159 created_at TEXT NOT NULL 2160 ); 2161 CREATE INDEX IF NOT EXISTS idx_portfolio_analysis_snapshots_time 2162 ON portfolio_analysis_snapshots(as_of DESC, created_at DESC); 2163 2164 CREATE TRIGGER IF NOT EXISTS market_data_runs_no_delete 2165 BEFORE DELETE ON market_data_runs 2166 BEGIN SELECT RAISE(ABORT, 'market data runs cannot be deleted'); END; 2167 CREATE TRIGGER IF NOT EXISTS benchmark_snapshots_immutable 2168 BEFORE UPDATE ON benchmark_snapshots 2169 BEGIN SELECT RAISE(ABORT, 'benchmark snapshots are immutable'); END; 2170 CREATE TRIGGER IF NOT EXISTS benchmark_snapshots_no_delete 2171 BEFORE DELETE ON benchmark_snapshots 2172 BEGIN SELECT RAISE(ABORT, 'benchmark snapshots cannot be deleted'); END; 2173 CREATE TRIGGER IF NOT EXISTS portfolio_analysis_snapshots_immutable 2174 BEFORE UPDATE ON portfolio_analysis_snapshots 2175 BEGIN SELECT RAISE(ABORT, 'portfolio analysis snapshots are immutable'); END; 2176 CREATE TRIGGER IF NOT EXISTS portfolio_analysis_snapshots_no_delete 2177 BEFORE DELETE ON portfolio_analysis_snapshots 2178 BEGIN SELECT RAISE(ABORT, 'portfolio analysis snapshots cannot be deleted'); END; 2179 """ 2180 ) 2181 2182 2183 def _create_truewealth_verified_snapshot_v1(conn: Connection) -> None: 2184 _add_missing_columns( 2185 conn, 2186 "account_value_snapshots", 2187 { 2188 "valuation_at": "TEXT", 2189 "source_reference": "TEXT", 2190 "is_active": "INTEGER NOT NULL DEFAULT 1", 2191 "deactivated_at": "TEXT", 2192 "deactivation_reason": "TEXT", 2193 }, 2194 ) 2195 conn.executescript( 2196 """ 2197 CREATE TABLE IF NOT EXISTS truewealth_portfolios ( 2198 portfolio_id TEXT PRIMARY KEY, 2199 account_id TEXT NOT NULL UNIQUE REFERENCES accounts(account_id), 2200 source_reference_hash TEXT NOT NULL, 2201 label TEXT NOT NULL, 2202 portfolio_kind TEXT CHECK(portfolio_kind IN ('free_assets','pillar_3a','child','other')), 2203 base_currency TEXT NOT NULL DEFAULT 'CHF', 2204 is_active INTEGER NOT NULL DEFAULT 1 CHECK(is_active IN (0,1)), 2205 created_at TEXT NOT NULL, 2206 updated_at TEXT 2207 ); 2208 2209 CREATE TABLE IF NOT EXISTS truewealth_import_batches ( 2210 batch_id TEXT PRIMARY KEY, 2211 portfolio_id TEXT NOT NULL REFERENCES truewealth_portfolios(portfolio_id), 2212 account_id TEXT NOT NULL REFERENCES accounts(account_id), 2213 file_sha256 TEXT NOT NULL UNIQUE CHECK(length(file_sha256)=64), 2214 filename_sha256 TEXT NOT NULL CHECK(length(filename_sha256)=64), 2215 source_file_type TEXT NOT NULL CHECK(source_file_type='application/pdf'), 2216 parser_id TEXT NOT NULL, 2217 parser_version TEXT NOT NULL, 2218 provenance TEXT NOT NULL CHECK(provenance='truewealth_customer_export'), 2219 snapshot_date TEXT NOT NULL, 2220 page_count INTEGER NOT NULL CHECK(page_count > 0), 2221 archive_reference TEXT NOT NULL, 2222 status TEXT NOT NULL CHECK(status='confirmed'), 2223 audit_id TEXT NOT NULL, 2224 confirmed_at TEXT NOT NULL, 2225 confirmed_by TEXT NOT NULL DEFAULT 'user' 2226 ); 2227 CREATE INDEX IF NOT EXISTS idx_truewealth_batches_portfolio_date 2228 ON truewealth_import_batches(portfolio_id, snapshot_date DESC, confirmed_at DESC); 2229 2230 CREATE TABLE IF NOT EXISTS truewealth_snapshots ( 2231 snapshot_id TEXT PRIMARY KEY, 2232 portfolio_id TEXT NOT NULL REFERENCES truewealth_portfolios(portfolio_id), 2233 account_id TEXT NOT NULL REFERENCES accounts(account_id), 2234 batch_id TEXT NOT NULL UNIQUE REFERENCES truewealth_import_batches(batch_id), 2235 snapshot_date TEXT NOT NULL, 2236 source_total_chf TEXT NOT NULL, 2237 securities_total_chf TEXT NOT NULL, 2238 cash_total_chf TEXT NOT NULL, 2239 components_total_chf TEXT NOT NULL, 2240 reconciliation_difference_chf TEXT NOT NULL, 2241 reconciliation_tolerance_chf TEXT NOT NULL DEFAULT '1.00', 2242 reconciliation_status TEXT NOT NULL CHECK(reconciliation_status IN ('matched','within_tolerance','mismatch')), 2243 position_count INTEGER NOT NULL, 2244 cash_count INTEGER NOT NULL, 2245 completeness_status TEXT NOT NULL CHECK(completeness_status IN ('complete','partial','blocked')), 2246 reason_codes_json TEXT NOT NULL DEFAULT '[]' 2247 CHECK(json_valid(reason_codes_json) AND json_type(reason_codes_json)='array'), 2248 created_at TEXT NOT NULL, 2249 UNIQUE(portfolio_id, snapshot_date, batch_id) 2250 ); 2251 CREATE INDEX IF NOT EXISTS idx_truewealth_snapshot_date 2252 ON truewealth_snapshots(portfolio_id, snapshot_date DESC); 2253 CREATE UNIQUE INDEX IF NOT EXISTS ux_truewealth_snapshot_portfolio_date 2254 ON truewealth_snapshots(portfolio_id, snapshot_date); 2255 CREATE TABLE IF NOT EXISTS truewealth_snapshot_positions ( 2256 snapshot_position_id TEXT PRIMARY KEY, 2257 snapshot_id TEXT NOT NULL REFERENCES truewealth_snapshots(snapshot_id), 2258 source_row_reference TEXT NOT NULL, 2259 instrument_name TEXT NOT NULL, 2260 isin TEXT NOT NULL, 2261 asset_type TEXT, 2262 quantity TEXT NOT NULL, 2263 price_currency TEXT NOT NULL, 2264 source_price TEXT NOT NULL, 2265 source_value_chf TEXT NOT NULL, 2266 source_evidence_json TEXT NOT NULL DEFAULT '{}' 2267 CHECK(json_valid(source_evidence_json) AND json_type(source_evidence_json)='object'), 2268 created_at TEXT NOT NULL, 2269 UNIQUE(snapshot_id, source_row_reference), 2270 UNIQUE(snapshot_id, isin) 2271 ); 2272 CREATE INDEX IF NOT EXISTS idx_truewealth_positions_snapshot 2273 ON truewealth_snapshot_positions(snapshot_id, isin); 2274 2275 CREATE TABLE IF NOT EXISTS truewealth_snapshot_cash ( 2276 snapshot_cash_id TEXT PRIMARY KEY, 2277 snapshot_id TEXT NOT NULL REFERENCES truewealth_snapshots(snapshot_id), 2278 source_row_reference TEXT NOT NULL, 2279 currency TEXT NOT NULL, 2280 amount_original TEXT NOT NULL, 2281 fx_rate_to_chf TEXT, 2282 source_value_chf TEXT NOT NULL, 2283 source_evidence_json TEXT NOT NULL DEFAULT '{}' 2284 CHECK(json_valid(source_evidence_json) AND json_type(source_evidence_json)='object'), 2285 created_at TEXT NOT NULL, 2286 UNIQUE(snapshot_id, source_row_reference), 2287 UNIQUE(snapshot_id, currency) 2288 ); 2289 CREATE INDEX IF NOT EXISTS idx_truewealth_cash_snapshot 2290 ON truewealth_snapshot_cash(snapshot_id, currency); 2291 2292 CREATE UNIQUE INDEX IF NOT EXISTS ux_account_value_truewealth_official_reference 2293 ON account_value_snapshots(source_reference) 2294 WHERE source_type='truewealth_official_import' AND source_reference IS NOT NULL; 2295 2296 CREATE TRIGGER IF NOT EXISTS truewealth_account_values_no_delete 2297 BEFORE DELETE ON account_value_snapshots 2298 WHEN OLD.source_type IN ('truewealth_official_import','truewealth_manual_provisional') 2299 BEGIN SELECT RAISE(ABORT, 'truewealth account valuations cannot be deleted'); END; 2300 CREATE TRIGGER IF NOT EXISTS truewealth_official_account_values_immutable 2301 BEFORE UPDATE ON account_value_snapshots 2302 WHEN OLD.source_type='truewealth_official_import' 2303 BEGIN SELECT RAISE(ABORT, 'official truewealth account valuations are immutable'); END; 2304 CREATE TRIGGER IF NOT EXISTS truewealth_manual_value_fields_immutable 2305 BEFORE UPDATE ON account_value_snapshots 2306 WHEN OLD.source_type='truewealth_manual_provisional' AND ( 2307 NEW.snapshot_id IS NOT OLD.snapshot_id OR NEW.account_id IS NOT OLD.account_id OR 2308 NEW.valuation_date IS NOT OLD.valuation_date OR NEW.total_value_chf IS NOT OLD.total_value_chf OR 2309 NEW.currency IS NOT OLD.currency OR NEW.source_type IS NOT OLD.source_type OR 2310 NEW.quality_status IS NOT OLD.quality_status OR NEW.notes IS NOT OLD.notes OR 2311 NEW.created_at IS NOT OLD.created_at OR NEW.valuation_at IS NOT OLD.valuation_at OR 2312 NEW.source_reference IS NOT OLD.source_reference 2313 ) 2314 BEGIN SELECT RAISE(ABORT, 'manual truewealth valuation fields are immutable'); END; 2315 2316 CREATE TRIGGER IF NOT EXISTS truewealth_batches_immutable_update 2317 BEFORE UPDATE ON truewealth_import_batches 2318 BEGIN SELECT RAISE(ABORT, 'truewealth import batches are immutable'); END; 2319 CREATE TRIGGER IF NOT EXISTS truewealth_batches_no_delete 2320 BEFORE DELETE ON truewealth_import_batches 2321 BEGIN SELECT RAISE(ABORT, 'truewealth import batches cannot be deleted'); END; 2322 CREATE TRIGGER IF NOT EXISTS truewealth_snapshots_immutable_update 2323 BEFORE UPDATE ON truewealth_snapshots 2324 BEGIN SELECT RAISE(ABORT, 'truewealth snapshots are immutable'); END; 2325 CREATE TRIGGER IF NOT EXISTS truewealth_snapshots_no_delete 2326 BEFORE DELETE ON truewealth_snapshots 2327 BEGIN SELECT RAISE(ABORT, 'truewealth snapshots cannot be deleted'); END; 2328 CREATE TRIGGER IF NOT EXISTS truewealth_positions_immutable_update 2329 BEFORE UPDATE ON truewealth_snapshot_positions 2330 BEGIN SELECT RAISE(ABORT, 'truewealth positions are immutable'); END; 2331 CREATE TRIGGER IF NOT EXISTS truewealth_positions_no_delete 2332 BEFORE DELETE ON truewealth_snapshot_positions 2333 BEGIN SELECT RAISE(ABORT, 'truewealth positions cannot be deleted'); END; 2334 CREATE TRIGGER IF NOT EXISTS truewealth_cash_immutable_update 2335 BEFORE UPDATE ON truewealth_snapshot_cash 2336 BEGIN SELECT RAISE(ABORT, 'truewealth cash is immutable'); END; 2337 CREATE TRIGGER IF NOT EXISTS truewealth_cash_no_delete 2338 BEFORE DELETE ON truewealth_snapshot_cash 2339 BEGIN SELECT RAISE(ABORT, 'truewealth cash cannot be deleted'); END; 2340 CREATE TRIGGER IF NOT EXISTS truewealth_audit_immutable_update 2341 BEFORE UPDATE ON audit_log WHEN OLD.entity_type='truewealth_import_batch' 2342 BEGIN SELECT RAISE(ABORT, 'truewealth import audit is immutable'); END; 2343 CREATE TRIGGER IF NOT EXISTS truewealth_audit_no_delete 2344 BEFORE DELETE ON audit_log WHEN OLD.entity_type='truewealth_import_batch' 2345 BEGIN SELECT RAISE(ABORT, 'truewealth import audit cannot be deleted'); END; 2346 """ 2347 ) 2348 2349 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, 2416 batch_id TEXT NOT NULL REFERENCES household_import_batches(batch_id), 2417 receipt_candidate_id TEXT NOT NULL REFERENCES budget_transaction_candidates(transaction_candidate_id), 2418 money_candidate_id TEXT REFERENCES budget_transaction_candidates(transaction_candidate_id), 2419 money_transaction_id TEXT REFERENCES budget_transactions(budget_transaction_id), 2420 receipt_total TEXT NOT NULL, money_total TEXT, difference TEXT, 2421 status TEXT NOT NULL CHECK(status IN ('linked','review','unmatched')), 2422 created_at TEXT NOT NULL, UNIQUE(receipt_candidate_id) 2423 ); 2424 CREATE UNIQUE INDEX IF NOT EXISTS ux_household_migros_linked_money_candidate 2425 ON household_migros_links(money_candidate_id) 2426 WHERE status='linked' AND money_candidate_id IS NOT NULL; 2427 CREATE UNIQUE INDEX IF NOT EXISTS ux_household_migros_linked_money_transaction 2428 ON household_migros_links(money_transaction_id) 2429 WHERE status='linked' AND money_transaction_id IS NOT NULL; 2430 CREATE UNIQUE INDEX IF NOT EXISTS ux_budget_candidates_household_source_row 2431 ON budget_transaction_candidates(source_type, source_row_fingerprint) 2432 WHERE source_row_fingerprint IS NOT NULL; 2433 CREATE UNIQUE INDEX IF NOT EXISTS ux_budget_candidates_household_logical 2434 ON budget_transaction_candidates(logical_fingerprint) 2435 WHERE logical_fingerprint IS NOT NULL; 2436 CREATE TRIGGER IF NOT EXISTS household_batches_immutable_update 2437 BEFORE UPDATE ON household_import_batches 2438 BEGIN SELECT RAISE(ABORT, 'household import batches are immutable'); END; 2439 CREATE TRIGGER IF NOT EXISTS household_batches_no_delete 2440 BEFORE DELETE ON household_import_batches 2441 BEGIN SELECT RAISE(ABORT, 'household import batches cannot be deleted'); END; 2442 CREATE TRIGGER IF NOT EXISTS household_files_immutable_update 2443 BEFORE UPDATE ON household_import_files 2444 BEGIN SELECT RAISE(ABORT, 'household import files are immutable'); END; 2445 CREATE TRIGGER IF NOT EXISTS household_files_no_delete 2446 BEFORE DELETE ON household_import_files 2447 BEGIN SELECT RAISE(ABORT, 'household import files cannot be deleted'); END; 2448 CREATE TRIGGER IF NOT EXISTS household_items_immutable_update 2449 BEFORE UPDATE ON household_import_items 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.""" 2538 _add_missing_columns( 2539 conn, 2540 "budget_recurring_payments", 2541 { 2542 "planning_cadence": "TEXT", 2543 "planning_type": "TEXT", 2544 "amount_min_text": "TEXT", 2545 "amount_max_text": "TEXT", 2546 "due_months_json": "TEXT NOT NULL DEFAULT '[]'", 2547 "periodicity_status": "TEXT NOT NULL DEFAULT 'unconfirmed'", 2548 "data_version": "INTEGER NOT NULL DEFAULT 1", 2549 "user_override": "INTEGER NOT NULL DEFAULT 0", 2550 }, 2551 ) 2552 _add_missing_columns( 2553 conn, 2554 "budget_plan_items", 2555 { 2556 "planning_cadence": "TEXT", 2557 "item_type": "TEXT", 2558 "payment_amount_text": "TEXT", 2559 "due_months_json": "TEXT NOT NULL DEFAULT '[]'", 2560 "calculation_basis": "TEXT", 2561 "certainty": "TEXT NOT NULL DEFAULT 'safe'", 2562 "manual_override": "INTEGER NOT NULL DEFAULT 0", 2563 "data_version": "INTEGER NOT NULL DEFAULT 1", 2564 }, 2565 ) 2566 conn.execute( 2567 """UPDATE budget_recurring_payments 2568 SET planning_cadence=CASE 2569 WHEN status<>'active' AND json_valid(COALESCE(candidate_evidence_json, '')) 2570 AND COALESCE( 2571 json_extract(candidate_evidence_json, '$.open_candidate_count'), 2572 json_extract(candidate_evidence_json, '$.observation_count'), 2573 json_extract(candidate_evidence_json, '$.transaction_count'), 2574 json_array_length(json_extract(candidate_evidence_json, '$.transaction_ids')) 2575 )=1 THEN 'undetermined' 2576 ELSE COALESCE(planning_cadence, frequency) END, 2577 planning_type=COALESCE(planning_type, recurring_type), 2578 amount_min_text=COALESCE(amount_min_text, expected_amount_text), 2579 amount_max_text=COALESCE(amount_max_text, expected_amount_text), 2580 next_expected_date=CASE 2581 WHEN status<>'active' AND json_valid(COALESCE(candidate_evidence_json, '')) 2582 AND COALESCE( 2583 json_extract(candidate_evidence_json, '$.open_candidate_count'), 2584 json_extract(candidate_evidence_json, '$.observation_count'), 2585 json_extract(candidate_evidence_json, '$.transaction_count'), 2586 json_array_length(json_extract(candidate_evidence_json, '$.transaction_ids')) 2587 )=1 THEN NULL 2588 ELSE next_expected_date END, 2589 periodicity_status=CASE WHEN status='active' THEN 'confirmed' ELSE periodicity_status END""" 2590 ) 2591 conn.execute( 2592 """UPDATE budget_plan_items 2593 SET planning_cadence=COALESCE(planning_cadence, CASE cadence WHEN 'annual' THEN 'yearly' ELSE cadence END), 2594 item_type=COALESCE(item_type, CASE 2595 WHEN category_id IN (SELECT category_id FROM budget_categories WHERE category_type='income') THEN 'income' 2596 WHEN is_fixed_cost=1 THEN 'fixed_cost' 2597 ELSE 'variable_expense' END), 2598 payment_amount_text=COALESCE(payment_amount_text, monthly_amount_chf, annual_amount_chf), 2599 calculation_basis=COALESCE(calculation_basis, 'Bestehender bestätigter Budgetplan')""" 2600 ) 2601 conn.executescript( 2602 """ 2603 CREATE TABLE IF NOT EXISTS budget_plan_versions ( 2604 version_id TEXT PRIMARY KEY, 2605 plan_year TEXT NOT NULL, 2606 version_number INTEGER NOT NULL, 2607 source_type TEXT NOT NULL, 2608 preview_fingerprint TEXT NOT NULL, 2609 source_data_version TEXT NOT NULL, 2610 summary_json TEXT NOT NULL, 2611 snapshot_json TEXT NOT NULL, 2612 audit_id TEXT NOT NULL UNIQUE REFERENCES audit_log(audit_id), 2613 created_by TEXT NOT NULL, 2614 created_at TEXT NOT NULL, 2615 UNIQUE(plan_year, version_number), 2616 UNIQUE(plan_year, preview_fingerprint) 2617 ); 2618 CREATE TABLE IF NOT EXISTS budget_plan_version_items ( 2619 version_item_id TEXT PRIMARY KEY, 2620 version_id TEXT NOT NULL REFERENCES budget_plan_versions(version_id), 2621 source_plan_item_id TEXT, 2622 position_type TEXT NOT NULL, 2623 name TEXT NOT NULL, 2624 category_id TEXT, 2625 payment_amount_text TEXT NOT NULL, 2626 cadence TEXT NOT NULL, 2627 due_months_json TEXT NOT NULL, 2628 annual_amount_text TEXT NOT NULL, 2629 monthly_reserve_text TEXT NOT NULL, 2630 calculation_basis TEXT NOT NULL, 2631 certainty TEXT NOT NULL, 2632 manual_override INTEGER NOT NULL CHECK(manual_override IN (0,1)), 2633 created_at TEXT NOT NULL 2634 ); 2635 CREATE INDEX IF NOT EXISTS idx_budget_plan_versions_year 2636 ON budget_plan_versions(plan_year, version_number DESC); 2637 CREATE INDEX IF NOT EXISTS idx_budget_plan_version_items_version 2638 ON budget_plan_version_items(version_id); 2639 CREATE TRIGGER IF NOT EXISTS budget_plan_versions_no_update 2640 BEFORE UPDATE ON budget_plan_versions 2641 BEGIN SELECT RAISE(ABORT, 'budget plan versions are immutable'); END; 2642 CREATE TRIGGER IF NOT EXISTS budget_plan_versions_no_delete 2643 BEFORE DELETE ON budget_plan_versions 2644 BEGIN SELECT RAISE(ABORT, 'budget plan versions cannot be deleted'); END; 2645 CREATE TRIGGER IF NOT EXISTS budget_plan_version_items_no_update 2646 BEFORE UPDATE ON budget_plan_version_items 2647 BEGIN SELECT RAISE(ABORT, 'budget plan version items are immutable'); END; 2648 CREATE TRIGGER IF NOT EXISTS budget_plan_version_items_no_delete 2649 BEFORE DELETE ON budget_plan_version_items 2650 BEGIN SELECT RAISE(ABORT, 'budget plan version items cannot be deleted'); END; 2651 CREATE TRIGGER IF NOT EXISTS budget_plan_version_items_no_extra_insert 2652 BEFORE INSERT ON budget_plan_version_items 2653 WHEN (SELECT COUNT(*) FROM budget_plan_version_items WHERE version_id=NEW.version_id) >= 2654 (SELECT json_array_length(json_extract(snapshot_json, '$.positions')) 2655 FROM budget_plan_versions WHERE version_id=NEW.version_id) 2656 BEGIN SELECT RAISE(ABORT, 'budget plan version items are immutable after confirm'); END; 2657 CREATE TRIGGER IF NOT EXISTS budget_plan_version_audit_no_update 2658 BEFORE UPDATE ON audit_log WHEN OLD.entity_type='budget_plan_version' 2659 BEGIN SELECT RAISE(ABORT, 'budget plan version audit is immutable'); END; 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 2756 2757 def _create_crypto_reconciliation_cockpit_v1(conn: Connection) -> None: 2758 """Add append-only observed crypto snapshots and explicit transfer-leg relations.""" 2759 conn.executescript( 2760 """ 2761 CREATE TABLE IF NOT EXISTS crypto_balance_snapshots ( 2762 snapshot_id TEXT PRIMARY KEY, 2763 observed_at TEXT NOT NULL, 2764 status TEXT NOT NULL CHECK(status IN ('partial','complete')), 2765 confirmation_key TEXT NOT NULL UNIQUE, 2766 input_fingerprint TEXT NOT NULL, 2767 wallet_count INTEGER NOT NULL, 2768 item_count INTEGER NOT NULL, 2769 source_type TEXT NOT NULL DEFAULT 'manual_observation', 2770 audit_id TEXT NOT NULL UNIQUE REFERENCES audit_log(audit_id), 2771 created_by TEXT NOT NULL DEFAULT 'user', 2772 created_at TEXT NOT NULL 2773 ); 2774 CREATE TABLE IF NOT EXISTS crypto_balance_snapshot_wallets ( 2775 snapshot_wallet_id TEXT PRIMARY KEY, 2776 snapshot_id TEXT NOT NULL REFERENCES crypto_balance_snapshots(snapshot_id), 2777 wallet_id TEXT NOT NULL REFERENCES crypto_wallets(wallet_id), 2778 evidence_source TEXT NOT NULL, 2779 redacted_note TEXT, 2780 confirmation_status TEXT NOT NULL DEFAULT 'confirmed' 2781 CHECK(confirmation_status IN ('confirmed','review_required')), 2782 created_at TEXT NOT NULL, 2783 UNIQUE(snapshot_id, wallet_id) 2784 ); 2785 CREATE TABLE IF NOT EXISTS crypto_balance_snapshot_items ( 2786 snapshot_item_id TEXT PRIMARY KEY, 2787 snapshot_id TEXT NOT NULL REFERENCES crypto_balance_snapshots(snapshot_id), 2788 wallet_id TEXT NOT NULL REFERENCES crypto_wallets(wallet_id), 2789 asset_id TEXT NOT NULL REFERENCES crypto_assets(asset_id), 2790 quantity TEXT NOT NULL, 2791 created_at TEXT NOT NULL, 2792 UNIQUE(snapshot_id, wallet_id, asset_id) 2793 ); 2794 CREATE TABLE IF NOT EXISTS crypto_internal_transfer_pairs ( 2795 transfer_pair_id TEXT PRIMARY KEY, 2796 withdrawal_transaction_id TEXT NOT NULL UNIQUE 2797 REFERENCES crypto_transactions(crypto_transaction_id), 2798 deposit_transaction_id TEXT NOT NULL UNIQUE 2799 REFERENCES crypto_transactions(crypto_transaction_id), 2800 asset_id TEXT NOT NULL REFERENCES crypto_assets(asset_id), 2801 evidence_reference TEXT NOT NULL, 2802 status TEXT NOT NULL DEFAULT 'confirmed' CHECK(status='confirmed'), 2803 audit_id TEXT NOT NULL UNIQUE REFERENCES audit_log(audit_id), 2804 created_by TEXT NOT NULL DEFAULT 'user', 2805 created_at TEXT NOT NULL, 2806 CHECK(withdrawal_transaction_id<>deposit_transaction_id) 2807 ); 2808 CREATE INDEX IF NOT EXISTS idx_crypto_balance_snapshots_latest 2809 ON crypto_balance_snapshots(status, observed_at DESC, created_at DESC); 2810 CREATE INDEX IF NOT EXISTS idx_crypto_snapshot_items_wallet_asset 2811 ON crypto_balance_snapshot_items(wallet_id, asset_id); 2812 CREATE TRIGGER IF NOT EXISTS crypto_balance_snapshots_no_update 2813 BEFORE UPDATE ON crypto_balance_snapshots 2814 BEGIN SELECT RAISE(ABORT, 'crypto balance snapshots are immutable'); END; 2815 CREATE TRIGGER IF NOT EXISTS crypto_balance_snapshots_no_delete 2816 BEFORE DELETE ON crypto_balance_snapshots 2817 BEGIN SELECT RAISE(ABORT, 'crypto balance snapshots cannot be deleted'); END; 2818 CREATE TRIGGER IF NOT EXISTS crypto_balance_snapshot_wallets_no_update 2819 BEFORE UPDATE ON crypto_balance_snapshot_wallets 2820 BEGIN SELECT RAISE(ABORT, 'crypto snapshot wallets are immutable'); END; 2821 CREATE TRIGGER IF NOT EXISTS crypto_balance_snapshot_wallets_no_delete 2822 BEFORE DELETE ON crypto_balance_snapshot_wallets 2823 BEGIN SELECT RAISE(ABORT, 'crypto snapshot wallets cannot be deleted'); END; 2824 CREATE TRIGGER IF NOT EXISTS crypto_balance_snapshot_items_no_update 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 _create_professional_portfolio_cockpit_v1(conn: Connection) -> None: 2841 """Add bounded manual-snapshot and controlled refresh job lineage.""" 2842 conn.executescript( 2843 """ 2844 CREATE TABLE IF NOT EXISTS manual_snapshot_confirmations ( 2845 confirmation_id TEXT PRIMARY KEY, 2846 preview_id TEXT NOT NULL UNIQUE, 2847 input_fingerprint TEXT NOT NULL, 2848 payload_hash TEXT NOT NULL, 2849 snapshot_date TEXT NOT NULL, 2850 source_kind TEXT NOT NULL, 2851 known_wealth_after_chf TEXT NOT NULL, 2852 bank_cash_after_chf TEXT NOT NULL, 2853 separate_membership_asset_after_chf TEXT NOT NULL, 2854 created_snapshot_count INTEGER NOT NULL CHECK(created_snapshot_count=3), 2855 created_at TEXT NOT NULL, 2856 audit_id TEXT NOT NULL UNIQUE REFERENCES audit_log(audit_id), 2857 CHECK(source_kind='dated_manual_screenshot') 2858 ); 2859 CREATE TRIGGER IF NOT EXISTS manual_snapshot_confirmations_no_update 2860 BEFORE UPDATE ON manual_snapshot_confirmations 2861 BEGIN SELECT RAISE(ABORT, 'manual snapshot confirmations are immutable'); END; 2862 CREATE TRIGGER IF NOT EXISTS manual_snapshot_confirmations_no_delete 2863 BEFORE DELETE ON manual_snapshot_confirmations 2864 BEGIN SELECT RAISE(ABORT, 'manual snapshot confirmations cannot be deleted'); END; 2865 CREATE TRIGGER IF NOT EXISTS manual_snapshot_confirmations_no_replace 2866 BEFORE INSERT ON manual_snapshot_confirmations 2867 WHEN EXISTS( 2868 SELECT 1 FROM manual_snapshot_confirmations old 2869 WHERE old.confirmation_id=NEW.confirmation_id 2870 OR old.preview_id=NEW.preview_id 2871 OR old.audit_id=NEW.audit_id 2872 ) 2873 BEGIN SELECT RAISE(ABORT, 'manual snapshot confirmations cannot be replaced'); END; 2874 CREATE TRIGGER IF NOT EXISTS manual_cash_snapshots_no_update 2875 BEFORE UPDATE ON cash_account_snapshots 2876 WHEN OLD.source='manual_screenshot_snapshot' 2877 BEGIN SELECT RAISE(ABORT, 'manual cash snapshots are immutable'); END; 2878 CREATE TRIGGER IF NOT EXISTS manual_cash_snapshots_no_delete 2879 BEFORE DELETE ON cash_account_snapshots 2880 WHEN OLD.source='manual_screenshot_snapshot' 2881 BEGIN SELECT RAISE(ABORT, 'manual cash snapshots cannot be deleted'); END; 2882 CREATE TRIGGER IF NOT EXISTS manual_cash_snapshots_no_replace 2883 BEFORE INSERT ON cash_account_snapshots 2884 WHEN EXISTS( 2885 SELECT 1 FROM cash_account_snapshots old 2886 WHERE old.source='manual_screenshot_snapshot' 2887 AND old.snapshot_id=NEW.snapshot_id 2888 ) 2889 BEGIN SELECT RAISE(ABORT, 'manual cash snapshots cannot be replaced'); END; 2890 CREATE TRIGGER IF NOT EXISTS manual_asset_snapshots_no_update 2891 BEFORE UPDATE ON account_value_snapshots 2892 WHEN OLD.source_type='manual_screenshot_snapshot' 2893 BEGIN SELECT RAISE(ABORT, 'manual asset snapshots are immutable'); END; 2894 CREATE TRIGGER IF NOT EXISTS manual_asset_snapshots_no_delete 2895 BEFORE DELETE ON account_value_snapshots 2896 WHEN OLD.source_type='manual_screenshot_snapshot' 2897 BEGIN SELECT RAISE(ABORT, 'manual asset snapshots cannot be deleted'); END; 2898 CREATE TRIGGER IF NOT EXISTS manual_asset_snapshots_no_replace 2899 BEFORE INSERT ON account_value_snapshots 2900 WHEN EXISTS( 2901 SELECT 1 FROM account_value_snapshots old 2902 WHERE old.source_type='manual_screenshot_snapshot' 2903 AND old.snapshot_id=NEW.snapshot_id 2904 ) 2905 BEGIN SELECT RAISE(ABORT, 'manual asset snapshots cannot be replaced'); END; 2906 CREATE TABLE IF NOT EXISTS asset_price_refresh_jobs ( 2907 job_id TEXT PRIMARY KEY, 2908 status TEXT NOT NULL CHECK(status IN ('queued','running','complete','partial','failed')), 2909 requested_at TEXT NOT NULL, 2910 completed_at TEXT, 2911 stale_before TEXT NOT NULL, 2912 progress_total INTEGER NOT NULL DEFAULT 3, 2913 progress_completed INTEGER NOT NULL DEFAULT 0, 2914 wealth_snapshot_id TEXT, 2915 audit_id TEXT REFERENCES audit_log(audit_id) 2916 ); 2917 CREATE UNIQUE INDEX IF NOT EXISTS uq_asset_price_refresh_single_active 2918 ON asset_price_refresh_jobs((1)) 2919 WHERE status IN ('queued','running'); 2920 CREATE TABLE IF NOT EXISTS asset_price_refresh_sources ( 2921 job_id TEXT NOT NULL REFERENCES asset_price_refresh_jobs(job_id), 2922 source TEXT NOT NULL CHECK(source IN ('equity','crypto','fx')), 2923 status TEXT NOT NULL CHECK(status IN ('pending','running','complete','failed','skipped')), 2924 stale_candidates INTEGER NOT NULL DEFAULT 0, 2925 updated_count INTEGER NOT NULL DEFAULT 0, 2926 error_code TEXT, 2927 started_at TEXT, 2928 completed_at TEXT, 2929 PRIMARY KEY(job_id,source) 2930 ); 2931 CREATE TABLE IF NOT EXISTS aggregated_wealth_refresh_snapshots ( 2932 wealth_snapshot_id TEXT PRIMARY KEY, 2933 job_id TEXT NOT NULL UNIQUE REFERENCES asset_price_refresh_jobs(job_id), 2934 captured_at TEXT NOT NULL, 2935 known_wealth_chf TEXT, 2936 quality_status TEXT NOT NULL CHECK(quality_status IN ('complete','partial')), 2937 source_status_json TEXT NOT NULL CHECK(json_valid(source_status_json)) 2938 ); 2939 CREATE INDEX IF NOT EXISTS idx_asset_price_refresh_jobs_requested 2940 ON asset_price_refresh_jobs(requested_at DESC); 2941 CREATE TRIGGER IF NOT EXISTS aggregated_wealth_refresh_snapshots_no_update 2942 BEFORE UPDATE ON aggregated_wealth_refresh_snapshots 2943 BEGIN SELECT RAISE(ABORT, 'wealth refresh snapshots are immutable'); END; 2944 CREATE TRIGGER IF NOT EXISTS aggregated_wealth_refresh_snapshots_no_delete 2945 BEFORE DELETE ON aggregated_wealth_refresh_snapshots 2946 BEGIN SELECT RAISE(ABORT, 'wealth refresh snapshots cannot be deleted'); END; 2947 CREATE TRIGGER IF NOT EXISTS sprint23_audit_no_update 2948 BEFORE UPDATE ON audit_log 2949 WHEN OLD.entity_type IN ('manual_source_snapshot','asset_price_refresh_job') 2950 BEGIN SELECT RAISE(ABORT, 'sprint23 audit is immutable'); END; 2951 CREATE TRIGGER IF NOT EXISTS sprint23_audit_no_delete 2952 BEFORE DELETE ON audit_log 2953 WHEN OLD.entity_type IN ('manual_source_snapshot','asset_price_refresh_job') 2954 BEGIN SELECT RAISE(ABORT, 'sprint23 audit cannot be deleted'); END; 2955 """ 2956 ) 2957 # Compatibility repair for an interrupted/pre-release schema-52 build where 2958 # the table may already exist without the later payload-binding column. 2959 _add_missing_columns( 2960 conn, 2961 "manual_snapshot_confirmations", 2962 {"payload_hash": "TEXT NOT NULL DEFAULT ''"}, 2963 ) 2964 2965 2966 def _apply_compat_migrations(conn: Connection) -> None: 2967 _add_missing_instrument_columns(conn) 2968 for table, text_columns in TEXT_AFFINITY_COLUMNS.items(): 2969 _rebuild_table_with_text_columns(conn, table, text_columns) 2970 _create_broker_bank_mapping_tables(conn) 2971 _create_broker_import_review_items(conn) 2972 _create_broker_import_execution_plans(conn) 2973 _add_transaction_void_columns(conn) 2974 _create_fx_market_data_tables(conn) 2975 _create_market_quote_chart_tables(conn) 2976 _create_account_value_snapshot_tables(conn) 2977 _create_cash_account_snapshot_tables(conn) 2978 _create_instrument_import_candidates(conn) 2979 _create_budget_phase1_tables(conn) 2980 _create_budget_phase11_tables(conn) 2981 _create_budget_phase14_tables(conn) 2982 _create_budget_phase15_tables(conn) 2983 _create_budget_phase18_tables(conn) 2984 _create_budget_phase19_tables(conn) 2985 _create_budget_import_production_v1_tables(conn) 2986 _create_budget_categories_ux_fix_tables(conn) 2987 _create_budget_planning_forecast_v1_tables(conn) 2988 _create_budget_fixed_costs_subscriptions_v1_tables(conn) 2989 _create_budget_monthly_import_rule_learning_v1_tables(conn) 2990 _create_transfer_pairing_v2_tables(conn) 2991 _create_portfolio_policy_tables(conn) 2992 _create_portfolio_performance_tables(conn) 2993 _create_portfolio_ingestion_reconciliation_tables(conn) 2994 _create_daily_market_analytics_tables(conn) 2995 _create_postfinance_baseline_mapping_audit_v1(conn) 2996 _create_truewealth_verified_snapshot_v1(conn) 2997 create_postfinance_ledger_import_v1(conn) 2998 _create_investment_performance_scope_v1(conn) 2999 _create_grocery_optimizer_v1_tables(conn) 3000 _add_grocery_price_provider_v1_columns(conn) 3001 _create_grocery_matching_learning_v2_tables(conn) 3002 _create_household_import_v1_tables(conn) 3003 _create_household_review_corrections_v1(conn) 3004 _create_annual_budget_recurring_semantics_v1(conn) 3005 _create_current_source_coverage_and_truewealth_activity_v1(conn) 3006 _create_crypto_reconciliation_cockpit_v1(conn) 3007 _create_professional_portfolio_cockpit_v1(conn) 3008 3009 3010 def apply_migrations(conn: Connection) -> None: 3011 conn.executescript(INITIAL_SCHEMA_SQL) 3012 existing_initial = conn.execute("SELECT 1 FROM schema_migrations WHERE version = 1").fetchone() 3013 if not existing_initial: 3014 conn.execute( 3015 "INSERT INTO schema_migrations(version, name, applied_at, checksum) VALUES (?, ?, ?, ?)", 3016 (1, "001_initial_schema", utc_now(), checksum_sql(INITIAL_SCHEMA_SQL)), 3017 ) 3018 _apply_compat_migrations(conn) 3019 existing = conn.execute("SELECT 1 FROM schema_migrations WHERE version = ?", (MIGRATION_VERSION,)).fetchone() 3020 if not existing: 3021 conn.execute( 3022 "INSERT INTO schema_migrations(version, name, applied_at, checksum) VALUES (?, ?, ?, ?)", 3023 (MIGRATION_VERSION, MIGRATION_NAME, utc_now(), checksum_sql(MIGRATION_NAME)), 3024 ) 3025 conn.commit() ===== src/jarvis_finance/services/system_ops.py ===== 1 from __future__ import annotations 2 3 import json 4 import os 5 import socket 6 import subprocess 7 import urllib.request 8 from urllib.parse import urlsplit 9 from datetime import datetime, timezone 10 from pathlib import Path 11 from typing import Any 12 13 from jarvis_finance.config.settings import find_repo_root, load_settings 14 15 ALLOWED_ACTIONS: dict[str, str] = { 16 "backend": "scripts/restart_backend.sh", 17 "frontend": "scripts/restart_frontend.sh", 18 "dashboard": "scripts/restart_dashboard.sh", 19 } 20 _ALLOWED_SCRIPT_NAMES = {"restart_backend.sh", "restart_frontend.sh", "restart_dashboard.sh", "restart_vue_dashboard.sh"} 21 _ALLOWED_ENV = {"PATH", "HOME", "USER", "SHELL", "LANG", "LC_ALL", "JARVIS_FINANCE_RUNTIME_DIR", 22 "VITE_API_BASE_URL", "BACKEND_HOST", "BACKEND_PORT", "FRONTEND_PORT", "JARVIS_FINANCE_API_URL"} 23 _SAFE_OPS_KEYS = { 24 "action", 25 "component", 26 "status", 27 "started_at", 28 "finished_at", 29 "message", 30 "error", 31 "return_code", 32 "worker_started", 33 "log_available", 34 } 35 36 37 def _now() -> str: 38 return datetime.now(timezone.utc).isoformat() 39 40 41 def _port_open(port: int) -> bool: 42 with socket.socket(socket.AF_INET, socket.SOCK_STREAM) as sock: 43 sock.settimeout(0.3) 44 return sock.connect_ex(("127.0.0.1", int(port))) == 0 45 46 47 def _runtime_dir(repo_root: Path | None = None) -> Path: 48 return load_settings(repo_root=repo_root or find_repo_root()).runtime_paths.base_dir 49 50 51 def _safe_env(runtime_dir: Path) -> dict[str, str]: 52 env = {k: v for k, v in os.environ.items() if k in _ALLOWED_ENV} 53 env["JARVIS_FINANCE_RUNTIME_DIR"] = str(runtime_dir) 54 return env 55 56 57 def _healthcheck(url: str) -> bool: 58 try: 59 with urllib.request.urlopen(url.rstrip("/") + "/health", timeout=0.5) as response: 60 return 200 <= int(response.status) < 300 61 except Exception: 62 return False 63 64 65 def _write_ops_log(runtime_dir: Path, entry: dict[str, Any]) -> None: 66 log_dir = runtime_dir / "logs" 67 log_dir.mkdir(parents=True, exist_ok=True) 68 safe_entry = _sanitize_ops_entry(entry) 69 with (log_dir / "ops_actions.jsonl").open("a", encoding="utf-8") as fh: 70 fh.write(json.dumps(safe_entry, ensure_ascii=False, sort_keys=True) + "\n") 71 72 73 def _sanitize_ops_entry(entry: dict[str, Any]) -> dict[str, Any]: 74 return {key: value for key, value in entry.items() if key in _SAFE_OPS_KEYS} 75 76 77 def _last_ops_action(runtime_dir: Path) -> dict[str, Any] | None: 78 path = runtime_dir / "logs" / "ops_actions.jsonl" 79 if not path.exists(): 80 return None 81 try: 82 lines = [line for line in path.read_text(encoding="utf-8", errors="ignore").splitlines() if line.strip()] 83 return _sanitize_ops_entry(json.loads(lines[-1])) if lines else None 84 except Exception: 85 return None 86 87 88 def system_status( 89 *, 90 runtime_dir: Path | None = None, 91 repo_root: Path | None = None, 92 api_url: str | None = None, 93 frontend_url: str | None = None, 94 backend_reachable: bool | None = None, 95 frontend_reachable: bool | None = None, 96 ) -> dict[str, Any]: 97 repo = (repo_root or find_repo_root()).resolve() 98 runtime = (runtime_dir or _runtime_dir(repo)).resolve() 99 db_path = runtime / "data" / "finance.sqlite3" 100 configured_api = api_url or os.environ.get("JARVIS_FINANCE_API_URL") or os.environ.get("VITE_API_BASE_URL") 101 parsed = urlsplit(configured_api) if configured_api else None 102 backend_port = parsed.port if parsed and parsed.hostname else None 103 configured_frontend = frontend_url or os.environ.get("JARVIS_FINANCE_FRONTEND_URL") 104 frontend_parsed = urlsplit(configured_frontend) if configured_frontend else None 105 frontend_port = frontend_parsed.port if frontend_parsed and frontend_parsed.hostname else None 106 backend_running = backend_reachable if backend_reachable is not None else bool(configured_api and _healthcheck(configured_api)) 107 frontend_running = ( 108 frontend_reachable 109 if frontend_reachable is not None 110 else bool(configured_frontend and _healthcheck(configured_frontend.rstrip("/").removesuffix("/api"))) 111 ) 112 if frontend_reachable is None and not frontend_running and frontend_port: 113 frontend_running = _port_open(frontend_port) 114 return { 115 "purpose": "system_ops_status_v1", 116 "status": "ok", 117 "api_url": configured_api, 118 "runtime_db_available": db_path.exists(), 119 "runtime_outside_repo": repo not in runtime.parents and runtime != repo, 120 "backend": {"status": "running" if backend_running else "offline", "port": backend_port}, 121 "frontend": {"status": "running" if frontend_running else "offline", "port": frontend_port}, 122 "last_restart": _last_ops_action(runtime), 123 } 124 125 126 def restart_system_component(action: str, *, runtime_dir: Path | None = None, repo_root: Path | None = None, timeout: int = 30) -> dict[str, Any]: 127 if action not in ALLOWED_ACTIONS: 128 raise ValueError("unsupported_system_action") 129 repo = (repo_root or find_repo_root()).resolve() 130 runtime = (runtime_dir or _runtime_dir(repo)).resolve() 131 script_rel = ALLOWED_ACTIONS[action] 132 script_path = (repo / script_rel).resolve() 133 started_at = _now() 134 base = { 135 "action": f"restart_{action}", 136 "component": action, 137 "started_at": started_at, 138 } 139 if repo not in script_path.parents or script_path.name not in _ALLOWED_SCRIPT_NAMES: 140 raise ValueError("script_not_allowed") 141 if not script_path.exists(): 142 result = {**base, "status": "error", "message": "Restart-Script fehlt.", "error": "script_missing", "finished_at": _now()} 143 _write_ops_log(runtime, result) 144 return result 145 # Restart requests are served by the backend that may itself be stopped by 146 # the restart script. Running those scripts synchronously makes the browser 147 # see a network error before the HTTP response is flushed. Always schedule a 148 # detached worker with a tiny delay so remote/Tailscale clients receive a 149 # deterministic response, then let the shell script stop/start/healthcheck. 150 log_dir = runtime / "logs" 151 log_dir.mkdir(parents=True, exist_ok=True) 152 worker_log = log_dir / f"restart_{action}.log" 153 worker_command = f"sleep 1; exec {str(script_path)!r}" 154 with worker_log.open("ab") as log_fh: 155 worker = subprocess.Popen( 156 ["/usr/bin/env", "bash", "-lc", worker_command], 157 cwd=str(repo), 158 env=_safe_env(runtime), 159 stdout=log_fh, 160 stderr=subprocess.STDOUT, 161 start_new_session=True, 162 ) 163 result = { 164 **base, 165 "status": "scheduled", 166 "finished_at": _now(), 167 "message": f"{action.title()}-Restart wurde gestartet. Bitte in wenigen Sekunden erneut prüfen.", 168 "return_code": None, 169 "worker_started": worker.pid > 0, 170 "log_available": True, 171 } 172 _write_ops_log(runtime, result) 173 return result ===== src/jarvis_finance/api/routers/system.py ===== 1 from __future__ import annotations 2 3 from urllib.parse import urlsplit 4 5 from fastapi import APIRouter, HTTPException, Request 6 7 from jarvis_finance.services.system_ops import restart_system_component, system_status 8 9 router = APIRouter(tags=["system"]) 10 11 12 def _safe_browser_origin(value: str | None) -> str | None: 13 parsed = urlsplit(value or "") 14 hostname = (parsed.hostname or "").lower() 15 if parsed.scheme not in {"http", "https"} or not parsed.netloc: 16 return None 17 if hostname not in {"localhost", "127.0.0.1"} and not hostname.startswith("100.") and not hostname.endswith(".ts.net"): 18 return None 19 return f"{parsed.scheme}://{parsed.netloc}" 20 21 22 @router.get("/system/status") 23 def get_system_status(request: Request) -> dict: 24 api_url = str(request.base_url).rstrip("/") + "/api" 25 origin = _safe_browser_origin(request.headers.get("origin")) 26 if origin is None: 27 referer = urlsplit(request.headers.get("referer", "")) 28 origin = _safe_browser_origin( 29 f"{referer.scheme}://{referer.netloc}" 30 if referer.scheme in {"http", "https"} and referer.netloc 31 else None 32 ) 33 return system_status( 34 api_url=api_url, 35 frontend_url=origin, 36 backend_reachable=True, 37 frontend_reachable=origin is not None, 38 ) 39 40 41 @router.post("/system/restart-backend") 42 def restart_backend() -> dict: 43 return _restart("backend") 44 45 46 @router.post("/system/restart-frontend") 47 def restart_frontend() -> dict: 48 return _restart("frontend") 49 50 51 @router.post("/system/restart-dashboard") 52 def restart_dashboard() -> dict: 53 return _restart("dashboard") 54 55 56 def _restart(action: str) -> dict: 57 try: 58 return restart_system_component(action) 59 except ValueError as exc: 60 raise HTTPException(status_code=400, detail=str(exc)) from exc ===== scripts/ci_portfolio_phase3_gate.py ===== 1 from __future__ import annotations 2 3 import hashlib 4 import io 5 import json 6 import os 7 import shutil 8 import sqlite3 9 import subprocess 10 import sys 11 import tarfile 12 from pathlib import Path 13 14 from jarvis_finance.config.settings import load_settings 15 from jarvis_finance.storage.migrations import apply_migrations, get_schema_version 16 17 SPRINT5_COMMIT = "480d7a1b72a950e864c4bb021d3f58af8f8c15f9" 18 EXPECTED_SCHEMA = 52 19 20 21 def _assert_tmp_path(path: Path) -> Path: 22 resolved = path.resolve() 23 if resolved == Path("/tmp") or Path("/tmp") not in resolved.parents: 24 raise RuntimeError(f"CI path must be isolated below /tmp: {resolved}") 25 return resolved 26 27 28 def _connect(path: Path) -> sqlite3.Connection: 29 connection = sqlite3.connect(path) 30 connection.row_factory = sqlite3.Row 31 return connection 32 33 34 def _integrity(connection: sqlite3.Connection) -> str: 35 return str(connection.execute("PRAGMA integrity_check").fetchone()[0]) 36 37 38 def _seed_digest(connection: sqlite3.Connection, *, include_performance_metadata: bool = False) -> str: 39 payload: dict[str, list[list[object]]] = {} 40 for table in ("platforms", "accounts"): 41 columns = [row[1] for row in connection.execute(f'PRAGMA table_info("{table}")')] 42 selected_columns = columns 43 if table == "accounts" and not include_performance_metadata: 44 selected_columns = [column for column in columns if column != "performance_included"] 45 selected = ", ".join(f'"{column}"' for column in selected_columns) 46 rows = connection.execute(f'SELECT {selected} FROM "{table}" ORDER BY {selected}').fetchall() 47 payload[table] = [[row[column] for column in selected_columns] for row in rows] 48 encoded = json.dumps(payload, ensure_ascii=True, separators=(",", ":"), default=str).encode() 49 return hashlib.sha256(encoded).hexdigest() 50 51 52 def _database_digest(connection: sqlite3.Connection) -> str: 53 payload: dict[str, list[list[object]]] = {} 54 tables = [ 55 str(row[0]) 56 for row in connection.execute( 57 "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name" 58 ) 59 ] 60 for table in tables: 61 columns = [str(row[1]) for row in connection.execute(f'PRAGMA table_info("{table}")')] 62 if not columns: 63 continue 64 selected = ", ".join(f'"{column}"' for column in columns) 65 rows = connection.execute(f'SELECT {selected} FROM "{table}" ORDER BY {selected}').fetchall() 66 payload[table] = [[row[column] for column in columns] for row in rows] 67 encoded = json.dumps(payload, ensure_ascii=True, separators=(",", ":"), default=str).encode() 68 return hashlib.sha256(encoded).hexdigest() 69 70 71 def _create_sprint5_database(repo: Path, export_dir: Path, database: Path) -> None: 72 archive = subprocess.check_output(["git", "-C", str(repo), "archive", SPRINT5_COMMIT]) 73 export_dir.mkdir(parents=True) 74 with tarfile.open(fileobj=io.BytesIO(archive), mode="r:") as bundle: 75 for member in bundle.getmembers(): 76 destination = (export_dir / member.name).resolve() 77 if export_dir.resolve() not in destination.parents and destination != export_dir.resolve(): 78 raise RuntimeError("Unsafe path in Git archive") 79 bundle.extractall(export_dir) 80 code = """ 81 import sqlite3 82 import sys 83 from jarvis_finance.storage.migrations import apply_migrations, get_schema_version 84 85 path = sys.argv[1] 86 conn = sqlite3.connect(path) 87 conn.row_factory = sqlite3.Row 88 apply_migrations(conn) 89 assert get_schema_version(conn) == 40 90 conn.execute( 91 "INSERT INTO platforms(platform_id,name,platform_type,country,default_currency,is_active,notes,created_at,updated_at) VALUES (?,?,?,?,?,?,?,?,?)", 92 ("ci-platform", "Synthetic CI Platform", "bank", "CH", "CHF", 1, "synthetic", "2026-01-01T00:00:00Z", None), 93 ) 94 conn.execute( 95 "INSERT INTO accounts(account_id,platform_id,account_name,account_type,currency,performance_included,is_health_reserve,is_active,notes,created_at,updated_at) VALUES (?,?,?,?,?,?,?,?,?,?,?)", 96 ("ci-account", "ci-platform", "Synthetic CI Account", "brokerage", "CHF", 1, 0, 1, "synthetic", "2026-01-01T00:00:00Z", None), 97 ) 98 conn.commit() 99 assert conn.execute("PRAGMA integrity_check").fetchone()[0] == "ok" 100 print("sprint5_fixture_schema=40") 101 """ 102 environment = os.environ.copy() 103 environment["PYTHONPATH"] = str(export_dir / "src") 104 subprocess.run( 105 [sys.executable, "-c", code, str(database)], 106 cwd=export_dir, 107 env=environment, 108 check=True, 109 ) 110 111 112 def _verify_settings_guard(repo: Path, root: Path) -> None: 113 accepted = { 114 "JARVIS_FINANCE_ENV": "test", 115 "JARVIS_FINANCE_RUNTIME_DIR": str(root / "guard-runtime"), 116 "JARVIS_FINANCE_DB_PATH": str(root / "guard.sqlite3"), 117 } 118 settings = load_settings(repo_root=repo, environ=accepted) 119 assert settings.db_path == (root / "guard.sqlite3").resolve() 120 121 rejected = { 122 "JARVIS_FINANCE_ENV": "test", 123 "JARVIS_FINANCE_RUNTIME_DIR": "/home/agent/jarvis_runtime/finance-system", 124 "JARVIS_FINANCE_DB_PATH": "/home/agent/jarvis_runtime/finance-system/data/finance.sqlite3", 125 } 126 try: 127 load_settings(repo_root=repo, environ=rejected) 128 except ValueError: 129 pass 130 else: 131 raise AssertionError("Productive paths were not rejected in test mode") 132 133 134 def main() -> None: 135 repo = Path(__file__).resolve().parents[1] 136 root = _assert_tmp_path(Path(os.environ.get("JARVIS_FINANCE_CI_ROOT", "/tmp/financemanager-phase3-ci"))) 137 if root.exists(): 138 shutil.rmtree(root) 139 root.mkdir(parents=True) 140 141 _verify_settings_guard(repo, root) 142 143 empty_path = root / "empty.sqlite3" 144 empty = _connect(empty_path) 145 apply_migrations(empty) 146 empty.commit() 147 assert get_schema_version(empty) == EXPECTED_SCHEMA 148 assert _integrity(empty) == "ok" 149 account_default = next( 150 row for row in empty.execute('PRAGMA table_info("accounts")') if row[1] == "performance_included" 151 ) 152 assert str(account_default[4]).strip("()") == "0" 153 assert empty.execute( 154 "SELECT COUNT(*) FROM sqlite_master WHERE type='trigger' AND name='accounts_performance_insert_requires_exclusion'" 155 ).fetchone()[0] == 1 156 for table in ( 157 "portfolio_ingestion_batches", "portfolio_ingestion_items", "market_data_runs", 158 "benchmark_snapshots", "portfolio_analysis_snapshots", "performance_scope_classifications", 159 "performance_cashflow_coverage", 160 ): 161 assert empty.execute(f'SELECT COUNT(*) FROM "{table}"').fetchone()[0] == 0 162 empty.close() 163 164 sprint5_path = root / "sprint5.sqlite3" 165 _create_sprint5_database(repo, root / "sprint5-source", sprint5_path) 166 sprint5 = _connect(sprint5_path) 167 assert get_schema_version(sprint5) == 40 168 digest_before = _seed_digest(sprint5) 169 assert sprint5.execute( 170 "SELECT performance_included FROM accounts WHERE account_id='ci-account'" 171 ).fetchone()[0] == 1 172 counts_before = { 173 table: sprint5.execute(f'SELECT COUNT(*) FROM "{table}"').fetchone()[0] 174 for table in ("platforms", "accounts", "transactions", "portfolio_valuation_snapshots") 175 } 176 apply_migrations(sprint5) 177 sprint5.commit() 178 assert get_schema_version(sprint5) == EXPECTED_SCHEMA 179 assert _integrity(sprint5) == "ok" 180 assert _seed_digest(sprint5) == digest_before 181 assert sprint5.execute( 182 "SELECT performance_included FROM accounts WHERE account_id='ci-account'" 183 ).fetchone()[0] == 0 184 classification = sprint5.execute( 185 """SELECT included,classification_role,decision_version,audit_id 186 FROM performance_scope_classifications WHERE account_id='ci-account'""" 187 ).fetchone() 188 assert tuple(classification[:3]) == ( 189 0, 190 "not_in_investment_performance_scope", 191 "investment_performance_scope_v1", 192 ) 193 assert sprint5.execute( 194 "SELECT COUNT(*) FROM audit_log WHERE audit_id=? AND entity_type='performance_scope_classification'", 195 (classification[3],), 196 ).fetchone()[0] == 1 197 counts_after = { 198 table: sprint5.execute(f'SELECT COUNT(*) FROM "{table}"').fetchone()[0] 199 for table in counts_before 200 } 201 assert counts_after == counts_before 202 for table in ( 203 "portfolio_ingestion_batches", "portfolio_ingestion_items", "market_data_runs", 204 "benchmark_snapshots", "portfolio_analysis_snapshots", 205 ): 206 assert sprint5.execute(f'SELECT COUNT(*) FROM "{table}"').fetchone()[0] == 0 207 first_digest = _database_digest(sprint5) 208 first_migration_rows = sprint5.execute("SELECT COUNT(*) FROM schema_migrations").fetchone()[0] 209 apply_migrations(sprint5) 210 sprint5.commit() 211 assert get_schema_version(sprint5) == EXPECTED_SCHEMA 212 assert _integrity(sprint5) == "ok" 213 assert _database_digest(sprint5) == first_digest 214 assert sprint5.execute("SELECT COUNT(*) FROM schema_migrations").fetchone()[0] == first_migration_rows 215 sprint5.close() 216 217 print("phase3_migration_gate=PASS") 218 print(f"empty_schema={EXPECTED_SCHEMA} integrity=ok ingestion_analysis_scope_rows=0 default_performance_included=0") 219 print( 220 f"sprint5_schema=40_to_{EXPECTED_SCHEMA} integrity=ok business_digest={digest_before} " 221 "scope_normalized=true audit_bound=true second_run_noop=true" 222 ) 223 print("settings_guard=PASS production_paths_rejected=true") 224 225 226 if __name__ == "__main__": 227 main() ===== .github/workflows/portfolio-phase3-integration.yml ===== 1 name: Portfolio Phase 3 Integration Gate 2 3 on: 4 pull_request: 5 branches: [main] 6 workflow_dispatch: 7 8 permissions: 9 contents: read 10 11 concurrency: 12 group: portfolio-phase3-${{ github.event.pull_request.number || github.ref }} 13 cancel-in-progress: false 14 15 jobs: 16 backend-full: 17 name: Backend – full suite (minimum 667 tests) 18 runs-on: ubuntu-24.04 19 timeout-minutes: 30 20 env: 21 PYTHONPATH: src 22 JARVIS_FINANCE_ENV: test 23 JARVIS_FINANCE_DB_PATH: /tmp/financemanager-phase3-backend/finance.sqlite3 24 JARVIS_FINANCE_RUNTIME_DIR: /tmp/financemanager-phase3-backend/runtime 25 JARVIS_FINANCE_WRITE_MODE: disabled 26 steps: 27 - uses: actions/checkout@v4 28 with: 29 fetch-depth: 0 30 - uses: actions/setup-python@v5 31 with: 32 python-version: "3.11" 33 cache: pip 34 cache-dependency-path: requirements-ci.txt 35 - name: Install pinned CI dependencies 36 run: python -m pip install -e . -r requirements-ci.txt 37 - name: Run all backend tests in one invocation 38 shell: bash 39 run: | 40 set -euo pipefail 41 mkdir -p /tmp/financemanager-phase3-backend/runtime 42 python -m pytest tests -q | tee /tmp/financemanager-phase3-backend/pytest.log 43 passed_count=$(sed -nE 's/.*(^|[^0-9])([0-9]+) passed.*/\2/p' /tmp/financemanager-phase3-backend/pytest.log | tail -n 1) 44 test -n "${passed_count}" 45 test "${passed_count}" -ge 667 46 47 frontend: 48 name: Frontend – tests, typecheck, production build 49 runs-on: ubuntu-24.04 50 timeout-minutes: 20 51 steps: 52 - uses: actions/checkout@v4 53 - uses: actions/setup-node@v4 54 with: 55 node-version: "22" 56 cache: npm 57 cache-dependency-path: frontend/package-lock.json 58 - name: Install locked frontend dependencies 59 working-directory: frontend 60 run: npm ci 61 - name: Run complete frontend suite 62 working-directory: frontend 63 run: npm test 64 - name: TypeScript typecheck 65 working-directory: frontend 66 run: npm run typecheck 67 - name: Production build 68 working-directory: frontend 69 run: npm run build 70 71 controls: 72 name: Migration, contracts, quality and repository safety 73 runs-on: ubuntu-24.04 74 timeout-minutes: 25 75 env: 76 PYTHONPATH: src 77 JARVIS_FINANCE_ENV: test 78 JARVIS_FINANCE_DB_PATH: /tmp/financemanager-phase3-controls/finance.sqlite3 79 JARVIS_FINANCE_RUNTIME_DIR: /tmp/financemanager-phase3-controls/runtime 80 JARVIS_FINANCE_CI_ROOT: /tmp/financemanager-phase3-migration 81 JARVIS_FINANCE_WRITE_MODE: disabled 82 steps: 83 - uses: actions/checkout@v4 84 with: 85 fetch-depth: 0 86 - uses: actions/setup-python@v5 87 with: 88 python-version: "3.11" 89 cache: pip 90 cache-dependency-path: requirements-ci.txt 91 - name: Install pinned CI dependencies 92 run: python -m pip install -e . -r requirements-ci.txt 93 - name: Git diff check 94 run: git diff --check "origin/${GITHUB_BASE_REF:-main}...HEAD" 95 - name: Repository safety and secret scan 96 run: python -m jarvis_finance.cli.main git-safety-scan . 97 - name: Ruff – Phase 3 and integration surfaces 98 run: | 99 python -m ruff check \ 100 scripts/ci_portfolio_phase3_gate.py \ 101 src/jarvis_finance/api/main.py \ 102 src/jarvis_finance/api/routers/overview.py \ 103 src/jarvis_finance/api/routers/market.py \ 104 src/jarvis_finance/api/routers/system.py \ 105 src/jarvis_finance/api/schemas/manual_snapshot.py \ 106 src/jarvis_finance/api/schemas/market.py \ 107 src/jarvis_finance/api/schemas/portfolio_data.py \ 108 src/jarvis_finance/api/schemas/portfolio_performance.py \ 109 src/jarvis_finance/api/schemas/portfolio_policy.py \ 110 src/jarvis_finance/api/schemas/performance_activation.py \ 111 src/jarvis_finance/api/schemas/reconciliation.py \ 112 src/jarvis_finance/api/schemas/wealth_cockpit.py \ 113 src/jarvis_finance/api/security.py \ 114 src/jarvis_finance/cli/main.py \ 115 src/jarvis_finance/config/settings.py \ 116 src/jarvis_finance/ledger/cost_basis.py \ 117 src/jarvis_finance/ledger/performance.py \ 118 src/jarvis_finance/market/providers.py \ 119 src/jarvis_finance/quality/freshness.py \ 120 src/jarvis_finance/quality/git_safety.py \ 121 src/jarvis_finance/services/crypto_market_recovery.py \ 122 src/jarvis_finance/services/crypto_reconciliation.py \ 123 src/jarvis_finance/services/crypto_service.py \ 124 src/jarvis_finance/crypto/current_balances.py \ 125 src/jarvis_finance/api/routers/crypto.py \ 126 src/jarvis_finance/api/schemas/crypto_reconciliation.py \ 127 src/jarvis_finance/services/performance_activation.py \ 128 src/jarvis_finance/services/cash_service.py \ 129 src/jarvis_finance/services/household_import.py \ 130 src/jarvis_finance/services/modelled_wealth.py \ 131 src/jarvis_finance/services/asset_price_refresh.py \ 132 src/jarvis_finance/services/market_service.py \ 133 src/jarvis_finance/services/portfolio_analysis_v1.py \ 134 src/jarvis_finance/services/raiffeisen_manual_snapshot.py \ 135 src/jarvis_finance/services/system_ops.py \ 136 src/jarvis_finance/services/wealth_cockpit.py \ 137 src/jarvis_finance/services/portfolio_data.py \ 138 src/jarvis_finance/services/portfolio_performance.py \ 139 src/jarvis_finance/services/portfolio_policy.py \ 140 src/jarvis_finance/services/reconciliation_snapshot.py \ 141 src/jarvis_finance/services/truewealth_productization.py \ 142 src/jarvis_finance/services/truewealth_service.py \ 143 src/jarvis_finance/services/truewealth_valuation.py \ 144 src/jarvis_finance/services/transfer_pairing.py \ 145 src/jarvis_finance/storage/database.py \ 146 src/jarvis_finance/storage/migrations.py \ 147 tests/conftest.py \ 148 tests/unit/test_api_write_security.py \ 149 tests/unit/test_git_safety.py \ 150 tests/unit/test_portfolio_data_ingestion_reconciliation.py \ 151 tests/unit/test_portfolio_performance_foundation.py \ 152 tests/unit/test_portfolio_policy_foundation.py \ 153 tests/unit/test_reconciliation_snapshot_foundation.py \ 154 tests/unit/test_modelled_wealth.py \ 155 tests/unit/test_asset_price_refresh.py \ 156 tests/unit/test_portfolio_analysis_v1.py \ 157 tests/unit/test_raiffeisen_manual_snapshot.py \ 158 tests/unit/test_portfolio_market_analytics_v1.py \ 159 tests/unit/test_wealth_cockpit_v1.py \ 160 tests/unit/test_cash_truewealth_management.py \ 161 tests/unit/test_settings.py \ 162 tests/unit/test_sprint20d_truewealth_productization.py \ 163 tests/unit/test_sprint20d_truewealth_valuation.py \ 164 tests/unit/test_sprint20e_crypto_market_recovery.py \ 165 tests/unit/test_sprint20g1_crypto_reconciliation.py \ 166 tests/unit/test_transfer_pairing_v2.py 167 - name: Python compileall 168 run: python -m compileall -q src tests scripts/ci_portfolio_phase3_gate.py 169 - name: Empty and Sprint-5-to-41 migration gates 170 run: python scripts/ci_portfolio_phase3_gate.py 171 - name: OpenAPI, auth, write, idempotency, reconciliation and performance controls 172 run: | 173 mkdir -p /tmp/financemanager-phase3-controls/runtime 174 python -m pytest -q \ 175 tests/unit/test_api_write_security.py \ 176 tests/unit/test_settings.py \ 177 tests/unit/test_portfolio_data_ingestion_reconciliation.py \ 178 tests/unit/test_portfolio_performance_foundation.py \ 179 tests/unit/test_portfolio_policy_foundation.py \ 180 tests/unit/test_reconciliation_snapshot_foundation.py \ 181 tests/unit/test_modelled_wealth.py \ 182 tests/unit/test_asset_price_refresh.py \ 183 tests/unit/test_portfolio_analysis_v1.py \ 184 tests/unit/test_raiffeisen_manual_snapshot.py \ 185 tests/unit/test_portfolio_market_analytics_v1.py \ 186 tests/unit/test_wealth_cockpit_v1.py \ 187 tests/unit/test_cash_truewealth_management.py \ 188 tests/unit/test_transfer_pairing_v2.py __HERMES_CWD_8d46a20096ed__/home/agent/.hermes/worktrees/FinanceManager-sprint23__HERMES_CWD_8d46a20096ed__