# Broker/Bank Mapping Plan — PostFinance, True Wealth, Raiffeisen

Purpose: structural mapping only. No productive DB changes, no real broker values in Git, and no row-level values in chat/reporting.

## Source-file analysis

Files were inspected from the external runtime workspace only; the original downloaded files remain outside the repository under `~/jarvis_runtime/finance-system/imports/...` and are not committed.

### PostFinance

- Source type: DOCX portfolio/depot export.
- Detected structure: multiple tables; separate cash/account summary tables and one positions table.
- Fields present structurally:
  - name/product: present.
  - quantity: present.
  - currency: present.
  - market value/current total value: present.
  - cost basis / entry price: present as legacy/reference field.
  - account/cash sections: present.
- Fields not clearly present from structural scan:
  - ISIN: not detected.
  - exchange: not detected.
  - full ticker metadata: partial/ambiguous; requires manual mapping.
  - fees/taxes: not detected in current export structure.
  - reliable snapshot date in table headers: not detected structurally; must be captured from document metadata or user-provided import date.
- Data-quality implications:
  - `missing_isin` for position rows until manually mapped.
  - `snapshot_only` because export is current holdings, not transaction history.
  - `cost_basis_uncertain` for entry/cost fields until broker semantics are confirmed.
  - `market_value_legacy` because imported values are source snapshot controls, not live valuation truth.
  - `needs_manual_review` for instrument identity.

### True Wealth

- Source type: DOCX portfolio export.
- Detected structure: one mixed table containing cash/account and allocation/position-like rows.
- Fields present structurally:
  - cash/balance sections: present.
  - account/portfolio context: present.
  - currency: present.
  - quantity/percentage-like information: present in table structure.
  - date-like text: present structurally.
- Fields not clearly present from structural scan:
  - ISIN: not detected.
  - ticker: not detected.
  - cost basis: not detected.
  - fees/taxes: not detected.
  - explicit market-value columns are not cleanly separable without source-specific parsing.
- Data-quality implications:
  - `missing_isin` and `needs_manual_review` for instruments.
  - `snapshot_only` for all holdings/cash.
  - `cost_basis_uncertain` unless later export contains transaction/cost history.
  - likely `missing_fx` where non-CHF rows exist and no trusted FX source is attached.

### Raiffeisen

- Source type: XLSX overview workbook.
- Detected structure: one sheet with compact account/platform overview.
- Fields present structurally:
  - account/platform labels: present.
  - cash/account rows: present.
  - high-level depot/investment row: present.
  - account balances/control totals: present.
- Fields not clearly present from structural scan:
  - ISIN: not detected.
  - ticker: not detected.
  - quantity: not detected.
  - instrument names: not detected beyond aggregate labels.
  - cost basis: not detected.
  - fees/taxes: not detected.
  - detailed position rows: not detected.
- Data-quality implications:
  - usable primarily as account/cash/control snapshot until detailed depot export is provided.
  - `snapshot_only`, `market_value_legacy`, and `needs_manual_review`.
  - instrument import should be blocked from this file unless a later detailed export is identified.

## Mapping foundation prepared in schema

Migration `003_broker_bank_mapping_foundation` prepares non-destructive mapping tables:

- `instrument_mappings`
  - Source-level instrument identity cache.
  - Captures source name, source instrument label, ISIN, ticker, exchange, currency, mapped `instrument_id`, status, notes and quality flags.
  - Intended statuses: `unmapped`, `selected`, `manual_review`, `rejected`.

- `platform_account_mappings`
  - Source-level platform/account identity cache.
  - Captures source platform/account labels, source type/currency, mapped platform/account IDs, status, notes and quality flags.

- `broker_import_dry_runs`
  - Stores structural dry-run metadata only: source label/type/hash, detected sections, field coverage, quality flags and aggregate row counts.
  - Must not store raw values, quantities, market values, account balances or complete source rows.

Existing target tables remain the canonical domain model:

- ISIN/instrument master: `instruments` with ISIN as the primary identity for stocks/ETFs when available; ticker/exchange are supplementary.
- Platform/account mapping: `platforms`, `accounts`.
- Initial position snapshots: `positions_snapshot` with transaction type/source semantics recorded via import session and audit log.
- Cash snapshots: `cash_balances` with `source_type='initial_cash_snapshot'`.
- Auditability: `import_sessions`, `audit_log`, `alerts`/quality flags.

## Dry-run mapping proposal

For each source importer, implement a dry-run pipeline before any commit mode:

1. Copy source file to external runtime temp/import directory only.
2. Hash file and create/read `import_sessions` with `status='dry_run'`.
3. Parse structure into candidate sections, never writing raw rows into Git or chat.
4. Split candidates:
   - position candidates
   - cash snapshot candidates
   - account/platform mapping candidates
   - rejected/unusable rows
5. Validate required fields:
   - Position snapshot requires source account/depot, instrument name or ISIN/ticker, quantity, currency and snapshot date.
   - Cash snapshot requires account/depot, currency, amount and snapshot date.
   - ISIN is strongly preferred; if missing, create mapping candidate with manual review.
6. Attach quality flags:
   - `missing_isin`
   - `missing_fx`
   - `snapshot_only`
   - `cost_basis_uncertain`
   - `market_value_legacy`
   - `needs_manual_review`
7. Produce aggregate-only dry-run output:
   - source name/type
   - detected sections
   - position candidate count
   - cash candidate count
   - blocked rows count
   - quality-flag counts
   - no values, quantities, market values or account balances.
8. Commit mode remains disabled until explicit user approval and a runtime DB backup exists.

## Source-specific initial mapping stance

- PostFinance: can become the first equity/ETF initial-snapshot dry-run source after manual instrument identity resolution, because position-like rows and cost/control fields are structurally present.
- True Wealth: dry-run source after parser can distinguish cash from allocation/position rows; likely high manual-review rate.
- Raiffeisen: use as cash/account/control snapshot only until detailed securities export is available.

## Hard blocks before productive import

- No source file in Git.
- Runtime DB backup verified.
- Dry-run reviewed.
- Manual mapping selected for every row with missing ISIN/ticker ambiguity.
- FX source selected for non-CHF rows.
- Audit-log events prepared for every created platform/account/instrument/snapshot row.
- Import is idempotent by file hash + row hash + target identity.