# Mortgage Banker Portal Handoff — Living Work Register

**Latest build:** 2.2.2  
**As of:** 2026-11-17  
**Owner:** Carlos Ramos / portfolio and product framework  
**Status:** Local functional prototype, not production approved

## Executive handoff

Mortgage Banker Portal began as an empty mortgage dashboard and is now a modular Flask / SQLite application framework. The portfolio demo stays read-only with invented records. Private mode adds authenticated users, role-based access, editable profiles and passwords, administration, CSV reporting, audit events and a real read-only database adapter.

## Delivered in current build

- [x] Polished responsive executive overview, pipeline, broker and analytics screens.
- [x] Deterministic synthetic loan demo with isolated database.
- [x] Individual accounts with `admin`, `analyst`, and `viewer` roles.
- [x] Admin user creation, access changes and self-service profile/password APIs.
- [x] Session-bound CSRF verification on private mutations.
- [x] Report Studio: dashboard, aggregate portfolio, config snapshot, audit events CSV.
- [x] Audit events for login/logout, user/profile/password changes, configuration, loan/broker mutations, imports and connector tests.
- [x] Config export redacts secret-like settings and includes SHA-256 snapshot hash.
- [x] Connections module: SQLAlchemy adapter for SQLite/PostgreSQL/SQL Server and validation of source-view schema.
- [x] External data source can power Overview via source selector (when configured).
- [x] Example synthetic upstream SQLite database generator.
- [x] Role-aware Tools & Capabilities catalog with dedicated module registry.
- [x] Windows launcher, Python requirements and regression test suite.
- [x] **2.2.0:** versioned migrations with pre-migration backup; validated upsert imports (strict/partial) with batch provenance; shared loan rule set; hash-chained append-only audit log; backups with SHA-256 manifests and offline restore; System Health page and CLI; sign-in lockout, admin unlock/reset; idle/absolute session timeout; strict CSP; `manage.py` operations CLI.

## How to pick up the project

1. Extract the project ZIP and read `README.md`.
2. Run `python -m unittest discover -s tests -v` from `mortgage_banker_portal`.
3. Launch the demo with `run_demo.bat` on Windows or `python core.py` elsewhere.
4. For protected admin features, configure the private mode variables listed in `docs/DEVELOPER.md`.
5. For connector tests, run `python tools/create_external_sample.py` and configure its database URL in the private environment.

## Critical next decisions from Carlos

| Decision needed | Why it matters | Recommendation |
|---|---|---|
| <s>First database platform</s> — resolved: **SQLite / local** | Portable first build, no server infrastructure | Keep `data/mortgage.db` private; demo uses `data/mortgage_demo.db` |
| Later external lender/LMS data source | Connector mapping and migration strategy | Defer until the local SQLite application and reporting are mature |
| Source ownership and schema | Prevent ambiguous metrics across lenders/LMS tools | Provide field mapping + status dictionary, no live customer data in chat |
| User administration model | Local accounts vs enterprise identity | Local profiles for prototype; OIDC/SSO for actual teams |
| Reporting requirements | Audit evidence shape, retention and exports | Start with CSV templates approved by stakeholders |
| Hosting environment | Local intranet, VPN, cloud, on-prem | Keep local until threat model and production controls are specified |
| Data sensitivity / privacy policy | Loan data may include regulated personal information | Enforce least privilege and formal privacy/security review |

## Verification log

| Check | Result | Notes |
|---|---|---|
| Python compile and JavaScript syntax | Pass | Backend and modular frontend scripts |
| Public demo pages and CSV | Pass | Read-only and synthetic |
| Private login, CSRF, user creation | Pass | Test identity and session checks |
| Viewer denied admin APIs and loan writes | Pass | RBAC regression |
| Configuration and event exports | Pass | CSV response produced |
| External SQLite canonical view | Pass | Separate database drives Overview |
| Live visual browser QA | Partial | 2.2.0: headless Chrome at 1440px — Overview, System Health, Data Center import and backup flows render with zero console errors under the strict CSP. Mobile widths and Windows browsers still to check |
| 2.2.0 regression suite | Pass | 14 tests: legacy 2.1.1 DB upgrade, import upsert/strict/partial/duplicates, validation, analyst delete denied, lockout/unlock/reset, idle timeout + CSP, secret-key enforcement, audit tamper detection, backup verify/restore via CLI, demo health |

## Risk register / unfinished work

1. **Security:** Add production SSO/MFA, central logging, secure deployment, and independent penetration review. (Brute-force lockout and session timeouts done in 2.2.0.)
2. **Data scale:** External adapter currently caps at 50k source rows and aggregates in Python; push calculations down to DB views for scale.
3. **Regulatory privacy:** Never expose private loan records through a public portfolio demo; use fake data only.
4. **Reporting:** Agree on fixed audit report schemas, timestamps, retention and signatures. Audit log is now tamper-*evident* (hash chain); external anchoring of the chain tip is still needed.
5. **Configuration UI:** Connection URLs are environment-managed; a production secrets-store integration and per-tenant sources remain future scope.
6. **User lifecycle:** Admin reset/unlock exist (2.2.0); invitations, forced change after reset, self-service recovery and richer access groups are not implemented.
7. **Real locks:** Current expiry uses a disclosed 45-day assumption; needs a source field for actual expiration.
8. **Design QA:** Confirm responsive behavior, keyboard navigation, contrast and screen-reader labels in a real browser session.

## Next recommended implementation sprint

**Sprint A — Strengthen the SQLite application foundation — ✅ complete in 2.2.0**

- [x] Select SQLite as the first database and keep demo/private storage isolated.
- [x] Tracked SQLite schema migrations and a local backup/restore workflow.
- [x] Admin-accessible data freshness and integrity checks (System Health).
- [x] Import provenance and configuration/audit integrity (hash chain).
- [ ] Validate all major UI modules on Windows and mobile browsers (desktop headless check done).

**Next: Release 2.3 — Pipeline accuracy & loan workflow** (full plan in `docs/ROADMAP.md`)

- [ ] Real rate-lock fields (expiration, period, extensions) replacing the 45-day estimate.
- [ ] Loan detail page with timeline, validated edit form and soft delete.
- [ ] Loan list pagination; retire legacy `/api/dashboard/*` metric endpoints.
- [ ] Status dictionary and per-source column-mapping profiles for imports.
- [ ] Quick wins: forced password change after admin reset; cached read-only external engine; scheduled backup task.

## Ongoing handoff rule

For each future update: record changed modules, test evidence, configuration changes, known issues and next actions here; add an architecture decision to `DEVELOPER.md` when a design choice changes; add a dated entry to `CHANGELOG.md`.
