# Mortgage Banker Portal — Project Details & Roadmap

**Current build:** 2.2.0 (2026-11-17) · **Owner:** Carlos Ramos · **Status:** local functional prototype, not production approved

---

## 1. Project details

### Vision
A modular, governance-first dashboard for mortgage bankers: pipeline, rate locks, broker network, profitability and operational analytics — built so every number is traceable to a validated source record, every change is attributable to a person, and the platform can grow from a local SQLite app into a production system without a rewrite.

### Who it serves

| Persona | Primary needs | Role today |
|---|---|---|
| Executive / sales leader | Funded volume, pipeline, margin, channel mix | viewer |
| Operations / lock desk | Aging loans, expiring locks, stage bottlenecks | analyst |
| Account executive | Broker performance, own pipeline | viewer (AE scoping planned) |
| Administrator / data owner | Imports, users, audit evidence, recovery | admin |
| Auditor / compliance | Change history, config snapshots, integrity proof | admin (read-only auditor role planned) |

### Current state at a glance

| Area | 2.2.0 status |
|---|---|
| Architecture | Flask modular monolith · 12 domain blueprints · 66 routes · ~3,650 lines Python, 443 lines tests |
| Data | SQLite primary (demo and private DBs isolated) · 7 versioned migrations · read-only SQLAlchemy adapter for an external view |
| Identity | Local accounts · admin/analyst/viewer · lockout + throttling · idle/absolute session timeout · admin unlock/reset |
| Governance | Hash-chained append-only audit log · import provenance (file SHA-256) · backups with manifests · System Health page · config snapshot |
| Security | CSRF on all writes · strict CSP (no inline script) · security headers · secret key enforced · errors logged, not leaked |
| Analytics | Overview KPIs, funding velocity, pipeline stages, channel mix, broker leaderboard, lock watch (45-day *estimate*), profitability, cycle time |
| Reporting | CSV: dashboard snapshot, portfolio by stage/channel, loan-level, analytics, config, audit events |
| Quality | 14 automated tests (demo boundary, RBAC, CSRF, migrations, import, lockout, audit tamper detection, backup/restore) |
| Ops | `manage.py`: status, migrate, backup, list/verify/restore, import-loans, verify-audit, health |

### Technology
Python 3.10+ · Flask 3 · Waitress · SQLite (WAL) · SQLAlchemy 2 (external adapter only) · Jinja templates · vanilla JS + native SVG charts · no CDN dependencies.

---

## 2. Review findings (2.1.1 → fixed in 2.2.0)

| # | Severity | Finding | Fix |
|---|---|---|---|
| 1 | High | Re-importing any loan that had history failed (`FOREIGN KEY constraint failed`) because `INSERT OR REPLACE` deletes the row | Upsert (`ON CONFLICT DO UPDATE`) + cascade FK |
| 2 | High | Imports set every column missing from the CSV to NULL | Only columns present in the file are written |
| 3 | High | Data Center JavaScript was outside any Jinja block → silently dropped; import buttons did nothing | Page rebuilt, script externalized |
| 4 | Medium | Invalid dates stored as raw text; API accepted any status/amount via `PUT` | Shared `loan_rules` validation on every write path |
| 5 | Medium | No brute-force protection; no session expiry; random secret fallback in private mode | Lockout, idle/absolute timeout, required secret |
| 6 | Medium | Audit log editable; documented as not tamper-evident | Hash chain + append-only triggers + verification |
| 7 | Medium | Ad hoc `ALTER TABLE` migrations; no backups | Versioned migrations, pre-migration backup, backup/restore tooling |
| 8 | Low | DB exception text returned to clients | Logged to `data/logs/app.log`, generic client messages |
| 9 | Low | `import_data.py` CLI bypassed all validation | Now delegates to the shared importer |

### Open observations (not yet fixed)

- **Package structure:** every module uses `sys.path.insert` and imports `core` as a top-level module. Works, but blocks clean packaging, type-checking and multiple app instances in tests. → Phase 2.6.
- **Legacy dashboard endpoints** (`/api/dashboard/kpis`, `/pipeline`, `/aging`, …) define "open loans" as 3 stages while Overview uses 5. Two definitions of one metric will eventually disagree in a meeting. → Deprecate in 2.3.
- **Loan list** hard-caps at 100 rows with no pagination; no loan edit UI (API only); hard delete.
- **External adapter** creates and disposes an engine per request, aggregates up to 50k rows in Python, and doesn't enforce read-only at the connection level.
- **Lock expiry** is still `rate_locked_date + 45 days` — an estimate, clearly labelled, but the most decision-relevant number on the dashboard.
- Admin password reset doesn't force a change at next sign-in.

---

## 3. Suggestions, prioritized

**Quick wins (≤ 1 day each)**
1. `must_change_password` flag set by admin reset; redirect to profile until changed.
2. Deprecate the legacy `/api/dashboard/*` endpoints (they're unused by the UI); keep one metric definition.
3. Pagination + sort on the loan list; server-side `LIMIT/OFFSET` with total count.
4. Cache the external SQLAlchemy engine; open SQLite sources with `mode=ro`; add a statement timeout.
5. Scheduled backup via Windows Task Scheduler running `manage.py backup --reason scheduled` + `manage.py health` (exit code 2 on failure).

**High value (next 2 releases)**
6. Real rate-lock model: `lock_expiration_date`, `lock_period_days`, extensions table, lock desk view. Replaces the 45-day estimate.
7. Status dictionary + column-mapping profiles per source system (Encompass, LendingQB/MeridianLink) so imports map vendor status values instead of rejecting them.
8. Loan detail page: field history from `loan_events`, edit form with validation feedback, soft delete with reason.
9. PII masking for viewers (borrower names → initials) and a read-only **auditor** role.
10. Alerts: expiring locks, aged loans, stale imports, failed health checks — in-app first, email later.

**Platform (before any shared deployment)**
11. OIDC SSO with Microsoft Entra ID (MFA enforced by the IdP); local accounts become break-glass only.
12. Restructure into an installable package, add CI (tests, ruff, bandit, pip-audit), pinned dependencies with hashes.
13. External anchoring of the audit-chain tip (scheduled export to write-once storage or a central log).

---

## 4. Roadmap

Each phase ends with: tests green, CHANGELOG + HANDOFF + DEVELOPER updated, a backup/restore check, and a demo walkthrough.

### ✅ 2.2 — Foundation hardening (done)
Migrations, validated imports with provenance, audit hash chain, backups, System Health, lockout, sessions, CSP.

### 2.3 — Pipeline accuracy & loan workflow
**Goal:** the numbers a lock desk acts on are real, not estimated.
- Rate-lock model (expiration, period, extensions, relock history) + migration 8; lock desk page with 7/14/30-day horizons.
- Loan detail page: timeline from `loan_events`, validated edit form, soft delete (`deleted_at`, reason) filtered everywhere.
- Loan list pagination, saved filters, column chooser.
- Status dictionary + per-source column mapping profiles (admin UI), used by importer.
- Retire legacy dashboard endpoints; single metrics module (`services/metrics.py`) used by Overview, reports and external adapter.
- **Exit:** lock watch uses source expiration dates; zero metric definitions duplicated; import accepts a mapped vendor extract end-to-end.

### 2.4 — Identity, access & privacy
**Goal:** safe for a small internal team.
- OIDC sign-in (Entra ID / generic OIDC) with group → role mapping; MFA via IdP; TOTP for local break-glass accounts.
- Forced password change, password history, account invitation flow.
- Permission strings (e.g. `loans.edit`, `reports.export.pii`) behind the existing roles; new **auditor** role.
- Field-level PII masking; borrower-level export requires `reports.export.pii` and is audited with row counts.
- AE / branch data scoping (row-level: users see their own pipeline).
- **Exit:** RBAC matrix tested per permission; PII never reaches a viewer response (test asserts on JSON payloads).

### 2.5 — Reporting, alerts & insight
**Goal:** the portal tells you what needs attention.
- Report builder: saved report definitions (fields, filters, grouping) with versioning; scheduled exports to a folder.
- Alert rules engine: expiring locks, aged-by-stage SLAs, stale import, health failure; in-app inbox, optional email.
- Pull-through / fallout by registration cohort; AE, processor and underwriter scorecards; margin by investor.
- Executive PDF snapshot; configurable dashboard per role.
- **Exit:** every report states source, filters, as-of time and export ID; alerts are audited when acknowledged.

### 2.6 — Engineering platform
**Goal:** maintainable by more than one person.
- Installable package (`mortgage_banker_portal/`), `pyproject.toml`, no `sys.path` edits, config object instead of module globals.
- Pytest, coverage gate, ruff, mypy (services first), bandit, pip-audit; GitHub Actions CI; pre-commit.
- Playwright smoke + screenshot regression; axe-core accessibility checks (WCAG 2.2 AA target).
- Structured JSON logging with request IDs.
- **Exit:** CI blocks merges on test/lint/security failures; one-command dev setup.

### 3.0 — Production readiness
**Goal:** eligible for a formal security/compliance review.
- Optional PostgreSQL primary via SQLAlchemy Core + Alembic (migrations 1–N ported); SQLite stays for demo/local.
- Reverse proxy with TLS, secure cookies on, HSTS; secrets from a vault (e.g. Azure Key Vault) instead of env vars.
- Encryption at rest (DB-level or volume-level), key rotation runbook.
- Central log shipping to SIEM; audit-chain anchoring; retention and legal-hold policy.
- Threat model (STRIDE), dependency SBOM, independent penetration test.
- DR plan with stated RPO/RTO and a recorded restore drill.
- Data classification, privacy review and control mapping with your security/compliance team.
- **Exit:** signed-off threat model, pen-test findings closed, restore drill evidence, runbooks in `docs/`.

---

## 5. Decisions needed from Carlos

| Decision | Needed by | Recommendation |
|---|---|---|
| Source system(s) for the first real extract and its status vocabulary | 2.3 | Start with one LOS extract; build its mapping profile first |
| Where rate-lock expiration comes from (LOS field vs. lock desk system) | 2.3 | Use the LOS lock expiration field; keep 45-day estimate only as labelled fallback |
| Identity provider | 2.4 | Entra ID via OIDC if the audience is a Microsoft shop |
| Who may see borrower names | 2.4 | Analyst+ only; viewers masked by default |
| Hosting target after local | 3.0 | Internal VM behind reverse proxy before any cloud option |
| Retention for audit events and backups | 3.0 | Align with institutional records policy |

## 6. Risks

| Risk | Impact | Mitigation |
|---|---|---|
| Real borrower data enters a demo/portfolio copy | High | Demo DB isolation, `DEMO-` loan numbers enforced by test, never import real data on a demo machine |
| Lock-expiry estimate used as a commitment | High | Labelled "assumed"; real field in 2.3 |
| Single maintainer / bus factor | Medium | Living docs + handoff rule; CI in 2.6 |
| SQLite write contention as users grow | Medium | WAL + short transactions now; PostgreSQL option in 3.0 |
| Audit chain rewritten by someone with file access | Medium | External anchoring in 3.0; OS-level file permissions now |
