# Mortgage Banker Portal Developer Guide — Living Architecture Reference

**Version:** 2.2.2  
**Updated:** 2026-11-17  
**Maintainer intent:** Keep this file in sync with every significant module, data-contract, security or route change. Pair each change with an entry in `CHANGELOG.md` and a handoff update.

## 1. Product architecture

Mortgage Banker Portal is a Flask modular monolith. It deliberately separates (a) public synthetic showcase data, (b) authenticated local operational data, and (c) on-demand read-only external database data. A modular monolith is appropriate for the current scope: single-machine deployability and independently maintainable domains without premature microservice infrastructure.

| Area | Primary files | Responsibility |
|---|---|---|
| App factory / identity | `core.py` | Config, sessions, CSRF, RBAC, security headers, hash-chained `audit_event()`, route registration |
| Schema | `services/migrations.py` | Numbered forward-only migrations, `schema_migrations` table |
| Validation rules | `services/loan_rules.py` | Single loan field/status/cross-field rule set for API and imports |
| Health & recovery | `services/health.py`, `services/backup.py`, `web/health.py`, `manage.py` | Integrity/data-quality checks, backups, restore, operations CLI |
| Synthetic demo | `demo_data.py` | Reproducible isolated fake data |
| Dashboard | `web/dashboard.py`, `templates/dashboard.html`, `static/js/dashboard.js` | Local aggregation, API, native SVG presentation |
| Analytics | `web/analytics.py`, `templates/analytics.html`, `static/js/analytics.js` | Operational summaries and turnaround metrics |
| Loans / brokers | `web/loans.py`, `web/brokers.py` | Read/list and authenticated mutation APIs |
| Identity / profiles | `web/users.py`, `templates/users.html`, `templates/profile.html` | Account creation, roles, self-service profile/password |
| Reporting / audit | `web/reporting.py`, `templates/reporting.html`, `templates/audit.html` | CSV exports, configuration snapshot, audit log |
| Connectors | `services/external.py`, `web/integrations.py` | SQLAlchemy read-only view adapter and diagnostics |
| Capabilities | `services/capabilities.py`, `web/tools.py` | Role-aware module catalog and discovery API |
| UI design system | `static/css/experience.css`, `templates/base.html` | Shared layout and responsive components |

All Flask views should be thin: validate user input, call the relevant service, return HTML/JSON/CSV. For large additions, move data logic to a new `services/<domain>.py` module. Keep external database credentials out of `settings`, frontend assets, audit fields, and the repository.

## 2. Running / testing

```bash
python -m venv .venv
# Activate the environment, then:
python -m pip install -r requirements.txt
python -m unittest discover -s tests -v
python core.py
```

Windows: double-click `run_demo.bat` for the read-only showcase. Local URL: `http://127.0.0.1:8080`. App listens on loopback by default.

**First database decision:** SQLite is the selected local storage engine. It works without a separate database server. Private mode requires `MORTGAGE_DEMO=0`, a unique 12+ character `MORTGAGE_ADMIN_PASSWORD` at first creation, and a stable cryptographically random `MORTGAGE_SECRET_KEY`. The private DB is `data/mortgage.db`, isolated from `data/mortgage_demo.db`.

## 3. Accounts and permissions

| Capability | Viewer | Analyst | Administrator | Public demo |
|---|---|---|---|---|
| View dashboard, loans, brokers, reports | Yes | Yes | Yes | Yes, fake data |
| Export aggregate reports | Yes | Yes | Yes | Yes, fake data |
| Export borrower-level loan CSV | No | Yes | Yes | Yes, fake data |
| Edit loans / brokers | No | Yes | Yes | No |
| Import CSV / change global settings | No | No | Yes | No |
| Add users / assign roles | No | No | Yes | No |
| Edit own profile / password | Yes | Yes | Yes | No |
| Export config / audit history | No | No | Yes | No |
| Test external DB connection | No | No | Yes | No |

Auth uses Werkzeug password hashing, session cookies, role checks against the database on every request, and a session-bound CSRF token for non-GET methods. JavaScript clients use `csrfHeaders()` and `apiWrite()` from `static/js/app.js`; HTML forms submit `csrf_token` hidden fields. Avoid implementing state-changing GET routes.

**Sign-in protection (2.2.0):** 5 failures for a username (since its last success) or 20 failures from one address within 15 minutes returns HTTP 429. Unknown usernames are checked against a dummy hash so timing does not reveal valid accounts. Administrators can **Unlock** or **Reset password** (temporary password) from User management; both are audited.

**Session policy:** idle timeout `MORTGAGE_IDLE_MINUTES` (default 30), absolute lifetime `MORTGAGE_SESSION_HOURS` (default 8). Set `MORTGAGE_COOKIE_SECURE=1` when served over HTTPS. Private mode refuses to start without `MORTGAGE_SECRET_KEY` of 24+ characters.

**Browser security headers:** strict CSP (`script-src 'self'`, no inline script), `X-Frame-Options: DENY`, `nosniff`, Permissions-Policy, COOP, `no-store`. Never add inline `<script>` blocks or `on*=` handlers; put code in `static/js/<page>.js`.

## 3a. Schema migrations

Schema changes live in `services/migrations.py` as numbered functions. On startup (or `python manage.py migrate`) pending migrations run in order, each in its own `BEGIN IMMEDIATE` transaction followed by `PRAGMA foreign_key_check`. An existing private database with pending migrations is backed up first (`reason=pre-migration`).

| Version | Change |
|---|---|
| 1 | Baseline 2.1 tables |
| 2 | User profile columns, role normalization |
| 3 | `loan_events` rebuilt with `ON DELETE CASCADE`, `actor_user_id`, index |
| 4 | Audit hash chain columns + append-only triggers (existing rows chained at migration time) |
| 5 | `login_attempts` |
| 6 | `import_batches` + `loans.import_batch_id` |
| 7 | Reporting indexes on loans |

Rules: never edit or renumber a shipped migration; make each safe on any older database; add a test.

## 3b. Imports

`POST /api/import/loans` (admin, multipart `file`, optional `mode=strict|partial`):

1. Decodes UTF-8 (BOM-safe) with cp1252 fallback; maps known lending-system headers (`LOAN_COLUMN_MAP`).
2. Validates every row with `loan_rules` before writing; rejects in-file duplicate loan numbers.
3. **strict** (default): any invalid row → HTTP 422, nothing written. **partial**: valid rows written, invalid rows reported.
4. Upserts on `loan_number`, writing only columns present in the file; logs a `loan_events` row per loan.
5. Records an `import_batches` row (file SHA-256, counts, actor) and flags a file that was already imported.

## 3b-2. De-identified copies

`python manage.py obfuscate [--output file.db]` → `services/obfuscate.py`. Writes a copy with institution, people and identifier fields replaced or removed and with history tables emptied. The copy is compacted and scanned for any surviving original value before it's kept. Amounts, rates and dates are **not** changed: get the data owner's approval before sharing figures derived from real business data.

## 3c. Backups, restore, health

- `POST /api/backups` (admin) or `python manage.py backup` — SQLite online backup into `data/backups/` with a `.json` manifest (SHA-256, size, schema version, reason, actor). Automatic backups beyond `MORTGAGE_BACKUP_KEEP` (default 14) are pruned; manual ones are kept.
- `python manage.py verify-backup <file>` — hash + `PRAGMA integrity_check`.
- `python manage.py restore <file>` — **server stopped**; verifies the backup, takes a `pre-restore` safety backup, replaces the DB, then migrates it to the current schema.
- `/health`, `/api/health`, `python manage.py health` — integrity, FK, schema version, audit chain, data quality, freshness. Exit code 2 on failure (usable from a scheduler).

## 4. External database contract

`services/external.py` provides a read-only SQLAlchemy connection for an *optional second SQLite file*, suitable for testing upstream data integration. Supported future SQL dialects are `postgresql` and `mssql`, which require drivers and their own integration validation. The connector reads an administrator-defined table or view called `mortgage_dashboard_source` by default. Core dashboards already use the primary local SQLite database without configuring this connector.

### Required columns and types

| Column | Expected value |
|---|---|
| `loan_number` | String identifier |
| `loan_status` | `Loan Open`, `Underwriting`, `Approved`, `Clear to Close`, `Docs Out`, or `Funded` |
| `loan_channel` | `Wholesale`, `Correspondent`, `Retail` |
| `loan_program` | String (e.g. Conventional) |
| `loan_amount` | Numeric USD principal |
| `note_rate` | Numeric annual interest percentage, e.g. `6.25` |
| `registered_date` | SQL `DATE` or ISO `YYYY-MM-DD` |
| `funded_date` | Nullable SQL `DATE` or ISO date |
| `gross_profit` | Nullable numeric USD amount |
| `broker_company` | Nullable display name |
| `rate_locked_date` | Nullable SQL `DATE` or ISO date |

Create an upstream read-only view mapping actual source fields and statuses to these exact names. The app does **not** accept raw arbitrary SQL text or table names from browser input. `MORTGAGE_EXTERNAL_VIEW` must be a simple SQL identifier or `schema.view` pair, validated server-side.

### Connection example

1. Build a synthetic external source: `python tools/create_external_sample.py`.
2. Set `MORTGAGE_DEMO=0` and private admin credentials in your shell environment.
3. Set `MORTGAGE_EXTERNAL_URL` to the SQLAlchemy connection string for the generated `data/external_example.db` file (the sample tool prints a starting value).
4. Optionally set `MORTGAGE_EXTERNAL_VIEW=mortgage_dashboard_source`.
5. Start the private app, sign in, open **Data connections**, click **Test connection**.
6. In **Overview**, switch the source dropdown from Local database to External database.

Examples of driver schemes: `postgresql+psycopg://...` with `pip install psycopg[binary]`; `mssql+pyodbc://...` with `pip install pyodbc` and a locally installed ODBC Driver 18 for SQL Server. Use your organization’s credential manager or OS-backed environment configuration. Never commit actual connection strings, credentials or client data.

The current implementation streams up to **50,000 rows** from the canonical view and aggregates them in Python, on request, without copying records to the Mortgage Banker Portal DB. This is deliberate for initial integration and tests; the production direction is filtered aggregate SQL queries, materialized views, pagination, timeouts, and scheduled refresh with source-specific health telemetry. External loan detail drawer is disabled until a safe detail endpoint and authorization model exist.

### Adding a new connector

1. Define a canonical mapping view in the upstream DB; leave private application schema unchanged.
2. Extend `services/external.py` only if a custom dialect or auth mechanism requires it.
3. Put driver dependencies in an optional requirements file, not hardcoded installation routines.
4. Add a temporary database fixture and test driver-specific date, decimal and NULL behavior.
5. Add diagnostics to Connections; log only non-secret outcome metadata.

## 5. Reporting and configuration audits

`web/reporting.py` owns CSV report generation, separate from chart rendering. The current exports are:

- `GET /api/reports/dashboard.csv`: current KPIs, funding history, pipeline values; accepts `source`, `period`, `channel`.
- `GET /api/reports/portfolio.csv`: local aggregate by status and channel; no borrower names.
- `GET /api/audit/export/config.csv`: administrator-only sanitized configuration snapshot and SHA-256 fingerprint.
- `GET /api/audit/export/events.csv`: administrator-only event log (default 90 days).
- `GET /api/audit/events`: administrator-only recent events API.

The config snapshot includes app version, demo status, source metadata and stored settings. Keys indicating secrets/tokens/passwords/credentials are redacted. The snapshot is reproducible but does not prove production-grade immutability. CSV fields are escaped against spreadsheet formula execution. For signed audit evidence, add stable report schemas, export identifiers, central append-only event storage, checksums, and retention policies.

`audit_event()` writes actor, action, category, subject, details and a UTC timestamp, plus `prev_hash` and `row_hash` = SHA-256 over the previous hash and this row's fields. Triggers reject UPDATE/DELETE. `verify_audit_chain()` (API `/api/audit/verify`, CLI `verify-audit`, Health page) recomputes the chain and reports the first broken row. This is **tamper-evident, not tamper-proof**: someone with file access could rewrite the whole chain. Next step is anchoring the chain tip externally (e.g. exporting it to a write-once store or central log on a schedule). Do not log secrets, full borrower data, raw CSV rows or passwords.

## 6. New module checklist

1. Create `web/<domain>.py` with a named Blueprint and role-specific decorators.
2. Put reusable logic in `services/<domain>.py`, not in Jinja templates.
3. Add templates and static JavaScript/CSS components using the shared design system.
4. Register the module in `core.create_app()` and add it to `services/capabilities.py`. Add only necessary navigation items.
5. Add tests for happy path, RBAC denial, CSRF denial, invalid input, empty data and demo safety.
6. Update this document, `HANDOFF.md`, and `CHANGELOG.md`.

## 7. Security and engineering limitations

This is an **application foundation**, not a bank-certified production system. Current gaps before external deployment with real borrower data:

- MFA/SSO (OIDC/SAML) and self-service recovery (admin reset and lockout exist since 2.2.0).
- Central user provisioning, password policy/rotation standards, conditional access and finer-grained permissions.
- Reverse proxy with TLS, secure-cookie enforcement, secret rotation and deployment environment controls.
- External anchoring of the audit chain, central log shipping, scheduled backup jobs and restore drills, retention/legal holds.
- Formal data classification, encryption at rest, PII masking, field-level authorizations, regulatory privacy review.
- Soft delete, pagination, async jobs, concurrency/load tests.
- Formal WCAG 2.2 audit and full keyboard/screen-reader flow review.
- Real lock expiry fields; the existing 45-day rule remains an **estimate**, never the source of legal commitments.

Relevant references: OWASP CSRF Prevention Cheat Sheet, W3C WCAG 2.2, CFPB Regulation P. Consult applicable counsel and institutional security teams for actual regulated workflows.

## 8. Architecture decisions (rolling log)

| Date | Decision | Reason / tradeoff |
|---|---|---|
| 2026-11-17 | Keep a Flask modular monolith | Independent domain modules without distributed deployment overhead |
| 2026-11-17 | Separate demo/private SQLite databases | Safe public sample and controlled migration path |
| 2026-11-17 | Keep external connector read-only, env-configured | Avoid credential storage and accidental upstream writes |
| 2026-11-17 | Require canonical SQL view contract | Isolate LMS/vendor-specific data mapping |
| 2026-11-17 | Roles: admin / analyst / viewer | Minimum useful permissions for an internal app |
| 2026-11-17 | CSV config snapshot + activity log | Immediate audit utility while immutable logging is deferred |
| 2026-11-17 | Name the product **Mortgage Banker Portal** | Owner-selected permanent project identity; former working title retired |
| 2026-11-17 | Select SQLite as the first database | Portable, serverless, ideal for local prototype and audit-friendly setup |
| 2026-11-17 | Hand-rolled numbered migrations instead of Alembic | App uses raw `sqlite3`, not SQLAlchemy ORM models; zero new dependencies. Revisit if PostgreSQL becomes primary |
| 2026-11-17 | Hash-chained audit log in the same DB | Detects edits/deletes cheaply; external anchoring planned |
| 2026-11-17 | Strict import mode by default | Partial financial loads cause silent reporting gaps; partial mode is explicit opt-in |
| 2026-11-17 | Restore is CLI-only with the server stopped | Avoids replacing a live database under open connections |
| 2026-11-17 | Strict CSP, no inline script | Removes the main XSS execution path for a data-heavy app |
