§ ÍOhj›1ãó—dZgd¢ZdS)a. CREATE TABLE IF NOT EXISTS schema_migrations ( version INTEGER PRIMARY KEY, name TEXT NOT NULL, applied_at TEXT NOT NULL, checksum TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS platforms ( platform_id TEXT PRIMARY KEY, name TEXT NOT NULL UNIQUE, platform_type TEXT NOT NULL, country TEXT, default_currency TEXT NOT NULL DEFAULT 'CHF', is_active INTEGER NOT NULL DEFAULT 1, notes TEXT, created_at TEXT NOT NULL, updated_at TEXT ); CREATE TABLE IF NOT EXISTS accounts ( account_id TEXT PRIMARY KEY, platform_id TEXT NOT NULL REFERENCES platforms(platform_id), account_name TEXT NOT NULL, account_type TEXT NOT NULL, currency TEXT NOT NULL DEFAULT 'CHF', performance_included INTEGER NOT NULL DEFAULT 0, is_health_reserve INTEGER NOT NULL DEFAULT 0, target_cash_min_chf NUMERIC, target_cash_max_chf NUMERIC, is_active INTEGER NOT NULL DEFAULT 1, notes TEXT, created_at TEXT NOT NULL, updated_at TEXT, UNIQUE(platform_id, account_name) ); CREATE TABLE IF NOT EXISTS instruments ( instrument_id TEXT PRIMARY KEY, asset_class TEXT NOT NULL, name TEXT NOT NULL, ticker TEXT, isin TEXT, exchange TEXT, currency TEXT NOT NULL, country TEXT, sector TEXT, industry TEXT, provider_symbol TEXT, data_provider_primary TEXT, position_category TEXT, ter TEXT, distribution_policy TEXT, index_name TEXT, fund_domicile TEXT, benchmark TEXT, data_provider_fallback TEXT, is_active INTEGER NOT NULL DEFAULT 1, notes TEXT, created_at TEXT NOT NULL, updated_at TEXT ); CREATE TABLE IF NOT EXISTS transactions ( transaction_id TEXT PRIMARY KEY, transaction_type TEXT NOT NULL, account_id TEXT NOT NULL REFERENCES accounts(account_id), instrument_id TEXT REFERENCES instruments(instrument_id), trade_date TEXT NOT NULL, settlement_date TEXT, quantity TEXT, price_original TEXT, gross_amount_original TEXT, fee_original TEXT DEFAULT '0', tax_original TEXT DEFAULT '0', net_amount_original TEXT, currency_original TEXT NOT NULL, fx_rate_to_chf TEXT, fx_source TEXT, fx_status TEXT NOT NULL DEFAULT 'ok', gross_amount_chf TEXT, fee_chf TEXT, tax_chf TEXT, net_amount_chf TEXT, source_type TEXT NOT NULL, source_id TEXT, external_transaction_id TEXT, row_hash TEXT, is_confirmed INTEGER NOT NULL DEFAULT 1, quality_status TEXT NOT NULL DEFAULT 'ok', notes TEXT, created_at TEXT NOT NULL, updated_at TEXT ); CREATE TABLE IF NOT EXISTS positions_snapshot ( position_snapshot_id TEXT PRIMARY KEY, snapshot_date TEXT NOT NULL, account_id TEXT NOT NULL REFERENCES accounts(account_id), platform_id TEXT NOT NULL REFERENCES platforms(platform_id), instrument_id TEXT NOT NULL REFERENCES instruments(instrument_id), quantity NUMERIC NOT NULL, average_cost_original NUMERIC, cost_basis_original NUMERIC, cost_basis_chf NUMERIC, market_price_original NUMERIC, market_value_original NUMERIC, market_fx_rate_to_chf NUMERIC, market_value_chf NUMERIC, unrealized_price_pnl_chf NUMERIC, unrealized_fx_pnl_chf NUMERIC, realized_pnl_chf NUMERIC, income_chf NUMERIC, fees_chf NUMERIC, taxes_chf NUMERIC, total_return_chf NUMERIC, portfolio_weight_pct NUMERIC, category TEXT, data_quality_status TEXT NOT NULL DEFAULT 'ok', created_at TEXT NOT NULL, UNIQUE(snapshot_date, account_id, instrument_id) ); CREATE TABLE IF NOT EXISTS cash_balances ( cash_balance_id TEXT PRIMARY KEY, account_id TEXT NOT NULL REFERENCES accounts(account_id), balance_date TEXT NOT NULL, currency TEXT NOT NULL, amount_original NUMERIC NOT NULL, fx_rate_to_chf NUMERIC, amount_chf NUMERIC, source_type TEXT NOT NULL, quality_status TEXT NOT NULL DEFAULT 'ok', notes TEXT, created_at TEXT NOT NULL, UNIQUE(account_id, balance_date, currency, source_type) ); CREATE TABLE IF NOT EXISTS fx_rates ( fx_rate_id TEXT PRIMARY KEY, base_currency TEXT NOT NULL, quote_currency TEXT NOT NULL DEFAULT 'CHF', rate_date TEXT NOT NULL, rate_timestamp TEXT, rate NUMERIC NOT NULL, provider TEXT NOT NULL, rate_type TEXT NOT NULL, quality_status TEXT NOT NULL DEFAULT 'ok', created_at TEXT NOT NULL, UNIQUE(base_currency, quote_currency, rate_date, provider, rate_type) ); CREATE TABLE IF NOT EXISTS market_prices ( market_price_id TEXT PRIMARY KEY, instrument_id TEXT NOT NULL REFERENCES instruments(instrument_id), price_date TEXT NOT NULL, price_timestamp TEXT, open NUMERIC, high NUMERIC, low NUMERIC, close NUMERIC NOT NULL, adjusted_close NUMERIC, currency TEXT NOT NULL, provider TEXT NOT NULL, provider_symbol TEXT, quality_status TEXT NOT NULL DEFAULT 'ok', created_at TEXT NOT NULL, UNIQUE(instrument_id, price_date, provider) ); CREATE TABLE IF NOT EXISTS crypto_wallets ( wallet_id TEXT PRIMARY KEY, wallet_name TEXT NOT NULL UNIQUE, wallet_type TEXT NOT NULL, platform_provider TEXT, network_chain TEXT, wallet_address TEXT, owner TEXT, is_active INTEGER NOT NULL DEFAULT 1, last_verified_at TEXT, notes TEXT, created_at TEXT NOT NULL, updated_at TEXT ); CREATE TABLE IF NOT EXISTS crypto_assets ( asset_id TEXT PRIMARY KEY, coin_name TEXT NOT NULL, symbol TEXT NOT NULL, coingecko_id TEXT UNIQUE, network_chain_default TEXT, is_stablecoin INTEGER NOT NULL DEFAULT 0, price_provider_primary TEXT NOT NULL DEFAULT 'CoinGecko', price_provider_fallback TEXT, is_active INTEGER NOT NULL DEFAULT 1, notes TEXT, created_at TEXT NOT NULL, updated_at TEXT ); CREATE TABLE IF NOT EXISTS crypto_holdings ( crypto_holding_id TEXT PRIMARY KEY, asset_id TEXT NOT NULL REFERENCES crypto_assets(asset_id), wallet_id TEXT NOT NULL REFERENCES crypto_wallets(wallet_id), quantity TEXT NOT NULL, acquisition_source TEXT, last_verified_at TEXT, verification_status TEXT NOT NULL DEFAULT 'unverified', legacy_snapshot_value_original TEXT, legacy_snapshot_value_chf TEXT, legacy_snapshot_currency TEXT, legacy_snapshot_date TEXT, notes TEXT, created_at TEXT NOT NULL, updated_at TEXT, UNIQUE(asset_id, wallet_id) ); CREATE TABLE IF NOT EXISTS crypto_transactions ( crypto_transaction_id TEXT PRIMARY KEY, transaction_id TEXT REFERENCES transactions(transaction_id), transaction_type TEXT NOT NULL, asset_id TEXT NOT NULL REFERENCES crypto_assets(asset_id), quantity TEXT NOT NULL, price_original TEXT, currency_original TEXT, gross_amount_original TEXT, fee_quantity TEXT, fee_original TEXT, fee_currency TEXT, fx_rate_to_chf TEXT, fx_source TEXT, amount_chf TEXT, from_wallet_id TEXT REFERENCES crypto_wallets(wallet_id), to_wallet_id TEXT REFERENCES crypto_wallets(wallet_id), transaction_datetime TEXT NOT NULL, tx_hash TEXT, source TEXT NOT NULL, confirmation_status TEXT NOT NULL DEFAULT 'confirmed', parse_confidence NUMERIC, original_input_text TEXT, notes TEXT, created_at TEXT NOT NULL, updated_at TEXT ); CREATE TABLE IF NOT EXISTS crypto_prices ( crypto_price_id TEXT PRIMARY KEY, asset_id TEXT NOT NULL REFERENCES crypto_assets(asset_id), coingecko_id TEXT, price_currency TEXT NOT NULL, price TEXT NOT NULL, provider TEXT NOT NULL DEFAULT 'CoinGecko', provider_timestamp TEXT, fetched_at TEXT NOT NULL, quality_status TEXT NOT NULL DEFAULT 'fresh', error_message TEXT ); CREATE TABLE IF NOT EXISTS watchlist ( watchlist_id TEXT PRIMARY KEY, instrument_id TEXT REFERENCES instruments(instrument_id), crypto_asset_id TEXT REFERENCES crypto_assets(asset_id), name TEXT NOT NULL, asset_class TEXT NOT NULL, reason TEXT NOT NULL, target_entry_price NUMERIC, target_entry_currency TEXT, desired_position_size_chf NUMERIC, desired_weight_pct NUMERIC, trigger_rules TEXT, risk_notes TEXT, investment_case TEXT, bear_case TEXT, sources TEXT, status TEXT NOT NULL DEFAULT 'active', next_review_date TEXT, created_at TEXT NOT NULL, updated_at TEXT ); CREATE TABLE IF NOT EXISTS reports ( report_id TEXT PRIMARY KEY, report_type TEXT NOT NULL, title TEXT NOT NULL, period_start TEXT, period_end TEXT, generated_at TEXT NOT NULL, file_path TEXT, format TEXT NOT NULL, data_quality_status TEXT NOT NULL DEFAULT 'ok', summary_json TEXT, created_at TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS alerts ( alert_id TEXT PRIMARY KEY, priority TEXT NOT NULL, category TEXT NOT NULL, entity_type TEXT, entity_id TEXT, rule_id TEXT, message TEXT NOT NULL, evidence_json TEXT, status TEXT NOT NULL DEFAULT 'active', created_at TEXT NOT NULL, resolved_at TEXT, muted_until TEXT, last_seen_at TEXT, occurrence_count INTEGER NOT NULL DEFAULT 1, fingerprint TEXT, dedup_key TEXT ); CREATE INDEX IF NOT EXISTS idx_alerts_dedup ON alerts(dedup_key, status); CREATE TABLE IF NOT EXISTS audit_log ( audit_id TEXT PRIMARY KEY, timestamp TEXT NOT NULL, source TEXT NOT NULL, action TEXT NOT NULL, entity_type TEXT NOT NULL, entity_id TEXT NOT NULL, old_values_json TEXT, new_values_json TEXT, user_text_note TEXT, original_input_text TEXT, confirmed INTEGER NOT NULL DEFAULT 1, confirmation_timestamp TEXT, auto_parsed INTEGER NOT NULL DEFAULT 0, parse_confidence NUMERIC, created_by TEXT NOT NULL DEFAULT 'system', quality_status TEXT NOT NULL DEFAULT 'ok', created_at TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS import_sessions ( import_session_id TEXT PRIMARY KEY, import_type TEXT NOT NULL, source_filename TEXT NOT NULL, source_hash TEXT, started_at TEXT NOT NULL, finished_at TEXT, status TEXT NOT NULL, rows_total INTEGER DEFAULT 0, rows_imported INTEGER DEFAULT 0, rows_failed INTEGER DEFAULT 0, errors_json TEXT, notes TEXT ); CREATE TABLE IF NOT EXISTS decision_journal ( decision_id TEXT PRIMARY KEY, decision_date TEXT NOT NULL, decision_type TEXT NOT NULL, entity_type TEXT, entity_id TEXT, system_recommendation TEXT, human_decision TEXT NOT NULL, rationale TEXT NOT NULL, investment_case TEXT, risks TEXT, exit_rule TEXT, alternatives_considered TEXT, sources TEXT, review_date TEXT, outcome_status TEXT, outcome_return_chf NUMERIC, created_at TEXT NOT NULL, updated_at TEXT ); CREATE INDEX IF NOT EXISTS idx_accounts_platform_id ON accounts(platform_id); CREATE INDEX IF NOT EXISTS idx_transactions_account_date ON transactions(account_id, trade_date); CREATE INDEX IF NOT EXISTS idx_transactions_instrument_date ON transactions(instrument_id, trade_date); CREATE INDEX IF NOT EXISTS idx_transactions_type ON transactions(transaction_type); CREATE UNIQUE INDEX IF NOT EXISTS idx_transactions_external_id ON transactions(external_transaction_id) WHERE external_transaction_id IS NOT NULL AND external_transaction_id != ''; CREATE UNIQUE INDEX IF NOT EXISTS idx_transactions_row_hash ON transactions(row_hash) WHERE row_hash IS NOT NULL AND row_hash != ''; CREATE INDEX IF NOT EXISTS idx_crypto_transactions_asset_datetime ON crypto_transactions(asset_id, transaction_datetime); CREATE INDEX IF NOT EXISTS idx_alerts_priority_status ON alerts(priority, status); CREATE INDEX IF NOT EXISTS idx_audit_entity ON audit_log(entity_type, entity_id); )Úschema_migrationsÚ platformsÚaccountsÚ instrumentsÚ transactionsÚpositions_snapshotÚ cash_balancesÚfx_ratesÚ market_pricesÚequity_price_pointsÚcrypto_walletsÚ crypto_assetsÚcrypto_holdingsÚcrypto_transactionsÚ crypto_pricesÚcrypto_price_pointsÚ watchlistÚreportsÚalertsÚ audit_logÚimport_sessionsÚdecision_journalÚinstrument_mappingsÚplatform_account_mappingsÚbroker_import_dry_runsN)ÚINITIAL_SCHEMA_SQLÚREQUIRED_TABLES©óú]/home/agent/.hermes/worktrees/FinanceManager-main-deploy/src/jarvis_finance/storage/schema.pyúr s$ððk~Ðððð€€€r