# Verified portfolio snapshots with provisional manual totals

Use this contract when an externally managed portfolio is imported only sporadically from official statements, while the user still needs to update the current total between imports.

## Data model

Keep three distinct layers:

1. **Official source snapshots** — immutable import batch, snapshot, position rows, per-currency cash rows, parser/version, source digest, archive reference, reconciliation and audit.
2. **Account valuations** — one historical value row per official or manual valuation; older values are never overwritten.
3. **Current-value read model** — selects exactly one account total, independently from the latest official position-detail snapshot.

Never distribute the gap between a manual total and stale positions, and never invent cash to reconcile it.

## Current-value precedence

Use valuation date as the primary ordering key:

- newer valuation dates win;
- on the same date, an official source wins over a manual provisional value;
- a newer manual value remains current when an older statement is imported later;
- a same-date or newer official source automatically supersedes a provisional value;
- inactive manual values remain in history but are excluded from the current read model.

Store official date-only evidence as a date. Do not invent `23:59:59` or another synthetic source time merely to make ordering convenient. Use an ordering expression internally and return the evidenced date/time honestly.

## Manual provisional valuations

Require:

- CHF total;
- valuation date/time;
- optional bounded note;
- side-effect-free Preview;
- explicit Confirm;
- manual/provisional quality label;
- audit record;
- reversible activation/deactivation without hard delete.

Reject non-finite decimals (`NaN`, infinities) explicitly before comparison or quantization. Bound unreasonable input sizes. For browser `datetime-local` defaults, derive local wall-clock digits by offsetting `Date.getTimezoneOffset()` before slicing an ISO string; otherwise non-UTC users silently confirm a shifted time.

When a manual total is current, label both:

- `Gesamtwert manuell aktualisiert am …`
- `Positionsdetails gemäss letztem Import vom …`

Mark allocation/performance usability false. Exclude provisional values from generic performance-input selection unless a dedicated method explicitly supports them without mixing stale positions.

## Account reconciliation without production IDs in Git/UI

Resolve the target in this order:

1. persisted portfolio-to-account mapping;
2. runtime-only configured override;
3. explicit request account for a controlled bootstrap;
4. exactly one active provider account with evidenced historical valuation;
5. if no history exists, exactly one active provider account.

Any ambiguity fails closed. Validate the resolved account against the provider. Normalize provider-name spelling for validation (for example spaces/hyphens) consistently with candidate lookup; do not select `TrueWealth` and then reject it because validation only accepts `True Wealth`.

Do not commit production account identifiers or real customer fixtures. Frontend previews should not receive source hashes or internal conflicting-batch IDs when booleans and user-safe diagnostics suffice.

## Source gate and atomic confirm

Preview must be storage-free. During Confirm:

1. decode the original bytes again;
2. recompute/verify digest;
3. reparse with the same parser version;
4. archive outside Git with owner-only permissions;
5. write mapping, batch, snapshot, components, account valuation and audit in one DB transaction;
6. if the DB transaction rolls back, remove an archive newly created by this attempt;
7. if an identical digest already exists, return the existing result without new rows/audit.

Add a unique `(portfolio, snapshot_date)` constraint so concurrent, different sources for the same date cannot both commit after racing through Preview. Treat a same-date different-digest source as a blocking conflict.

## Migration compatibility pitfall

Before adding `is_active`, inspect legacy lifecycle semantics. Some FinanceManager account valuation rows use `updated_at IS NOT NULL` as a tombstone/superseded marker. Adding `COALESCE(is_active,1)=1` alone can resurrect those rows.

Preserve the old tombstone predicate in generic performance queries, then add the new active-state predicate. Do not overload a legacy tombstone column for reversible manual activation unless every affected reader is updated and tested.

## Production release and evidence sequence

For an additive SQLite release, fix the evidence contract before touching production:

1. stop scheduled writers and the application services for a bounded deployment window;
2. create a consistent SQLite online backup outside Git, mode `0600`, and record SHA-256;
3. compute a logical business digest over the **pre-migration table/column contract** (exclude migration metadata only), plus table row counts;
4. restore the backup to a disposable DB using the deployment interpreter and configured connection helper;
5. apply the exact merged migration twice, require the same schema head, `integrity_check=ok`, unchanged baseline digest/counts, and zero rows in new import tables;
6. migrate production once and repeat schema, integrity, digest, count, and empty-table checks;
7. deploy/restart only the exact verified remote-main SHA, then check listeners, service logs, a shallow health route, and a DB-backed route.

Keep service restart and post-restart HTTP/Tailscale probes as separate approval/execution steps. A listener check immediately after `systemctl start` can race startup; inspect unit status and startup logs before treating an empty first `ss` result or a trailing `grep` exit as a failed deployment.

For the live source operation, bracket Preview and Confirm with strict sentinels: import/mapping/snapshot/component/audit row counts plus archive file count/digest. Preview must leave every sentinel unchanged. After Confirm, require only the expected deltas, correct mapping, historical valuation preservation, official-current selection, unchanged unrelated provider accounts, and `integrity_check=ok`. Then run a **fresh** duplicate Preview and Confirm—the old preview identity may be stale after the DB revision changes—and require `duplicate=true`, `idempotent=true`, and every sentinel unchanged.

Store preview IDs, batch/snapshot/audit IDs, full hashes, and detailed evidence only in owner-only runtime JSON. Normal reports should contain aggregate counts and booleans. For responsive production UAT, use exact laptop/tablet/mobile CSS widths, verify zero horizontal overflow, labels/history/forms, provider-free rendering, and zero console errors. Open Preview/Confirm panels without submitting them; do not save sensitive screenshots when aggregate DOM evidence is sufficient.

## Verification matrix

At minimum test:

- parser against anonymized format-faithful text and the protected real source outside Git;
- Preview side-effect freedom;
- exact component count and reconciliation tolerance;
- legacy value preserved and never labelled official;
- same-date official priority;
- newer manual over older official;
- older late official cannot displace newer manual;
- reversible deactivation/reactivation;
- manual value does not change positions/cash;
- identical source idempotency;
- same-date different-source conflict;
- DB rollback removes newly created archive;
- immutable source rows and no hard deletes;
- legacy tombstone rows stay excluded from performance;
- provider spelling variants and empty-account bootstrap;
- non-finite values rejected as controlled validation errors;
- GET and Preview paths have no writes;
- responsive UI and no technical IDs in rendered user-mode content;
- production-copy staging import before release.