ippadmin/dre
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
dre/operations/architecture-2026-08-21.md
T
root be0750d001 DRE customer portal: FastAPI + SQLite backend (18 endpoints) 
- Magic-link auth (sha256-only token storage, 15-min single-use, 7-day sessions)
- Staff-key auth via X-DRE-Staff-Key (constant-time compare)
- SQLite WAL, foreign_keys, parameterized queries, atomic DRE/CLT sequence allocation
- Intake validator rejects SSN/PAN patterns (FDCPA/TDCPA compliance)
- Document upload allowlist + magic-byte check, 20MB cap
- Unified error envelope, money as integer cents
- systemd unit (port 8093, User=root, hardening directives)
- Fixes import bug (auth.py relative imports) and audit_log placeholder mismatch
2026-08-21 18:43:33 -04:00

722 lines
41 KiB
Markdown
Raw Permalink Blame History

# DRE Customer Portal — Backend Architecture Specification
**Author:** Claude Opus 4-8 (System Architect)
**Date:** 2026-08-21
**Status:** BUILD-READY — hand off to GLM-5.2 (backend) + Sonnet 5 (frontend)
**Stack:** FastAPI + SQLite (single source of truth), magic-link auth, systemd + uvicorn behind Caddy
---
## 0. Scope & Principles
This spec defines the FIRST production backend for DRE. It replaces the 12 static mockups' dead
`<form>` with a live intake pipeline and adds a magic-link customer portal.
**In scope:** self-serve claim intake → creates client + claim → emails DRE team → returns claim
number; email magic-link auth (no passwords); customer portal (claim list/detail, document upload,
messaging); internal staff read/write endpoints (staff-key auth) that back the existing dashboards.
**Out of scope (fast-follow, do NOT block):** TwentyCRM sync, DocuSeal LPOA wiring, Stripe, AI
scoring, LetterStream, RON. Schema carries a nullable `twentycrm_id` on every synced entity so a
later one-way push is clean.
**Non-negotiable compliance:**
- NEVER collect/store SSNs, full bank account numbers, or card data. No column exists for them; the
intake validator rejects any field that pattern-matches a 9-digit SSN or a 13-19 digit PAN.
- All debtor + client data is PII. HTTPS only (Caddy terminates TLS). Secrets via env only.
- Store only what recovery needs (contract/invoice metadata + uploaded docs).
**Core conventions (locked, from platform spec):**
- Claim number: `DRE-YYYY-NNNN` (per-year sequence, zero-padded to 4).
- Client ID: `CLT-YYYY-NNNN` (per-year sequence, zero-padded to 4).
- Both generated server-side on first submission. Sequences are per calendar year.
---
## 1. SQLite Schema
**DB file:** `/opt/dre-portal/data/dre.db`
**Pragmas (set on every connection):** `PRAGMA foreign_keys = ON; PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;`
All timestamps are ISO-8601 UTC strings (`YYYY-MM-DDTHH:MM:SSZ`), stored as TEXT. All monetary
amounts stored as INTEGER cents (never float). All primary keys are TEXT UUID4 unless noted.
```sql
-- ============================================================
-- clients : one row per customer account (the creditor / claimant)
-- ============================================================
CREATE TABLE clients (
id TEXT PRIMARY KEY, -- uuid4
client_number TEXT UNIQUE NOT NULL, -- CLT-YYYY-NNNN
company_name TEXT NOT NULL,
contact_name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL, -- lowercased; magic-link identity
phone TEXT,
tos_accepted_at TEXT, -- set when ToS accepted at intake
twentycrm_id TEXT, -- nullable; set by future CRM sync
created_at TEXT NOT NULL,
updated_at TEXT NOT NULL
);
CREATE INDEX idx_clients_email ON clients(email);
-- ============================================================
-- debtors : the party the money is owed by (denormalized per claim is avoided;
-- one debtor row, referenced by claims). Minimal PII.
-- ============================================================
CREATE TABLE debtors (
id TEXT PRIMARY KEY, -- uuid4
name TEXT NOT NULL, -- business or individual name
business_type TEXT NOT NULL DEFAULT 'OTHER' -- enum below
CHECK (business_type IN
('INDIVIDUAL','SOLE_PROPRIETORSHIP','LLC','CORPORATION','PARTNERSHIP','OTHER')),
contact_email TEXT,
contact_phone TEXT,
physical_address TEXT, -- free-text single line; NOT named "address"
twentycrm_id TEXT, -- nullable
created_at TEXT NOT NULL,
updated_at TEXT NOT NULL
);
-- ============================================================
-- claims : the collection case. Belongs to one client, one debtor.
-- ============================================================
CREATE TABLE claims (
id TEXT PRIMARY KEY, -- uuid4
claim_number TEXT UNIQUE NOT NULL, -- DRE-YYYY-NNNN
client_id TEXT NOT NULL REFERENCES clients(id) ON DELETE RESTRICT,
debtor_id TEXT NOT NULL REFERENCES debtors(id) ON DELETE RESTRICT,
amount_cents INTEGER NOT NULL CHECK (amount_cents > 0),
currency TEXT NOT NULL DEFAULT 'USD',
status TEXT NOT NULL DEFAULT 'NEW' -- lifecycle enum below
CHECK (status IN
('NEW','UNDER_REVIEW','ACTIVE','NEGOTIATION','LEGAL','SETTLED','CLOSED','WRITE_OFF','REJECTED')),
tier TEXT NOT NULL DEFAULT 'TIER_1'
CHECK (tier IN ('TIER_1','TIER_2','TIER_2_5','TIER_3','TIER_4')),
description TEXT, -- what the debt is for (invoice desc, service)
client_reference TEXT, -- customer's own invoice/PO number
invoice_date TEXT, -- ISO date; when debt originated
date_assigned TEXT, -- set when moved out of NEW
date_resolved TEXT, -- set on SETTLED/CLOSED/WRITE_OFF
twentycrm_id TEXT, -- nullable
created_at TEXT NOT NULL,
updated_at TEXT NOT NULL
);
CREATE INDEX idx_claims_client ON claims(client_id);
CREATE INDEX idx_claims_status ON claims(status);
CREATE INDEX idx_claims_debtor ON claims(debtor_id);
-- ============================================================
-- documents : uploaded evidence, stored on disk; row holds metadata only
-- ============================================================
CREATE TABLE documents (
id TEXT PRIMARY KEY, -- uuid4
claim_id TEXT NOT NULL REFERENCES claims(id) ON DELETE CASCADE,
original_name TEXT NOT NULL, -- sanitized display name
stored_path TEXT NOT NULL, -- absolute path on disk (uuid-named)
mime_type TEXT NOT NULL,
size_bytes INTEGER NOT NULL,
sha256 TEXT NOT NULL, -- integrity + dedupe
uploaded_by TEXT NOT NULL DEFAULT 'CLIENT' -- CLIENT | STAFF
CHECK (uploaded_by IN ('CLIENT','STAFF')),
twentycrm_id TEXT, -- nullable
created_at TEXT NOT NULL
);
CREATE INDEX idx_documents_claim ON documents(claim_id);
-- ============================================================
-- case_notes : messages + internal notes on a claim (threaded log)
-- visibility controls whether the client can see it in the portal.
-- ============================================================
CREATE TABLE case_notes (
id TEXT PRIMARY KEY, -- uuid4
claim_id TEXT NOT NULL REFERENCES claims(id) ON DELETE CASCADE,
author_type TEXT NOT NULL -- who wrote it
CHECK (author_type IN ('CLIENT','STAFF','SYSTEM')),
author_name TEXT NOT NULL, -- display name (client contact, staff name, 'System')
subject TEXT, -- for client->team structured messages
content TEXT NOT NULL, -- plaintext; rendered escaped (see security)
visibility TEXT NOT NULL DEFAULT 'SHARED' -- SHARED = client sees it; INTERNAL = staff only
CHECK (visibility IN ('SHARED','INTERNAL')),
twentycrm_id TEXT, -- nullable
created_at TEXT NOT NULL
);
CREATE INDEX idx_notes_claim ON case_notes(claim_id);
-- ============================================================
-- auth_tokens : single-use magic-link tokens
-- ============================================================
CREATE TABLE auth_tokens (
id TEXT PRIMARY KEY, -- uuid4
client_id TEXT NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
token_hash TEXT UNIQUE NOT NULL, -- sha256 of the raw token (raw never stored)
expires_at TEXT NOT NULL, -- created_at + 15 min
consumed_at TEXT, -- set on successful verify; NULL = unused
requested_ip TEXT, -- for rate-limit audit
created_at TEXT NOT NULL
);
CREATE INDEX idx_tokens_hash ON auth_tokens(token_hash);
CREATE INDEX idx_tokens_client ON auth_tokens(client_id);
-- ============================================================
-- sessions : bearer session tokens issued after magic-link verify
-- ============================================================
CREATE TABLE sessions (
id TEXT PRIMARY KEY, -- uuid4
client_id TEXT NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
session_hash TEXT UNIQUE NOT NULL, -- sha256 of the raw session token
expires_at TEXT NOT NULL, -- created_at + 7 days (sliding not required v1)
revoked_at TEXT,
created_at TEXT NOT NULL,
last_seen_at TEXT
);
CREATE INDEX idx_sessions_hash ON sessions(session_hash);
-- ============================================================
-- audit_log : append-only trail for claim/status changes (compliance)
-- ============================================================
CREATE TABLE audit_log (
id TEXT PRIMARY KEY, -- uuid4
entity_type TEXT NOT NULL, -- 'claim' | 'client' | 'document' | 'note'
entity_id TEXT NOT NULL,
action TEXT NOT NULL, -- 'create' | 'status_change' | 'update' | 'upload' | 'note_add'
field TEXT, -- changed field name (nullable)
old_value TEXT,
new_value TEXT,
actor TEXT NOT NULL, -- staff email/name, client_number, or 'system'
reason TEXT,
created_at TEXT NOT NULL
);
CREATE INDEX idx_audit_entity ON audit_log(entity_type, entity_id);
-- ============================================================
-- number_sequences : per-year counters for claim/client numbers
-- (avoids race by using an atomic UPDATE...RETURNING in a txn)
-- ============================================================
CREATE TABLE number_sequences (
prefix TEXT NOT NULL, -- 'DRE' | 'CLT'
year INTEGER NOT NULL,
last_value INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (prefix, year)
);
```
### 1.1 Claim lifecycle status enum (authoritative)
| Value | Meaning | Client sees |
|----------------|-------------------------------------------------------------------|--------------------------|
| `NEW` | Just submitted via intake; awaiting DRE review | "Received" |
| `UNDER_REVIEW` | DRE reviewing docs / AI analysis | "Under Review" |
| `ACTIVE` | Approved; recovery in progress (tier drives the sub-stage) | "In Progress" |
| `NEGOTIATION` | Debtor engaged; settlement talks | "In Negotiation" |
| `LEGAL` | Referred to partner law firm (Tier 4) | "Legal Action" |
| `SETTLED` | Payment received / agreed; disbursement pending | "Settled" |
| `CLOSED` | Fully resolved & disbursed; binders generated | "Closed — Recovered" |
| `WRITE_OFF` | Uncollectible; case closed without recovery | "Closed — Uncollectible" |
| `REJECTED` | DRE declined the claim at intake review | "Not Accepted" |
**Tier enum:** `TIER_1` (Soft Touch), `TIER_2` (Formal Demand), `TIER_2_5` (Lien Threat), `TIER_3`
(Escalation), `TIER_4` (Legal Action). Tier is independent of status: a claim can be `ACTIVE` at any
tier. The frontend 4-step progress bar maps TIER_1→TIER_4 (TIER_2_5 renders as a sub-badge on TIER_2).
**Note on CRM enum divergence:** TwentyCRM Claims.status uses `NEW, ACTIVE, NEGOTIATION, LEGAL,
SETTLED, CLOSED, WRITE_OFF` and tier `TIER_1/2/3`. Our schema adds `UNDER_REVIEW`, `REJECTED`,
`TIER_2_5`. The future sync layer maps these: `UNDER_REVIEW`→`NEW`, `REJECTED`→`CLOSED`(+note),
`TIER_2_5`→`TIER_2`. This mapping is a sync-layer concern, not a schema constraint — build the
schema as specified above. **[CONDUCTOR DECISION #1 — see §7.]**
---
## 2. REST API Contract
**Base URL:** `https://portal.debtrecoveryexperts.com/api` (Caddy reverse-proxies `/api/*` to
`localhost:8090`; static HTML continues to be served by Caddy from `/var/www/capabilities/`).
**Auth models:**
- **Public** — no auth (intake, magic-link request/verify).
- **Client** — `Authorization: Bearer <session_token>`. Resolves to a `client_id`; every claim
query is scoped to that client. 401 if missing/invalid/expired.
- **Staff** — `X-DRE-Staff-Key: <key>` header, compared constant-time to env `DRE_STAFF_KEY`.
403 if absent/wrong. (v1 uses a single shared staff key; internal pages are already behind
Cloudflare Access, so this is defense-in-depth, not the primary gate.)
**Global conventions:**
- All request/response bodies are JSON (`Content-Type: application/json`) except document upload
(`multipart/form-data`).
- Errors: `{"error": {"code": "<machine_code>", "message": "<human msg>"}}` with appropriate HTTP
status. Codes: `validation_error`, `not_found`, `unauthorized`, `forbidden`, `rate_limited`,
`payload_too_large`, `unsupported_media_type`, `conflict`, `internal_error`.
- Money in responses returned BOTH as `amount_cents` (int) and `amount_display` (e.g. `"$15,000.00"`).
- Timestamps returned as ISO-8601 UTC.
### 2.1 Health
**`GET /api/health`** — Public. → `200 {"status":"ok","time":"<iso>"}`. No DB write.
### 2.2 Intake (public)
**`POST /api/intake`** — Public. Creates client (or reuses by email) + debtor + claim, writes a
`SYSTEM` case note, emails the DRE team, returns the claim number. This is what `debt-recovery.html`
posts to.
Request body:
```json
{
"client": {
"company_name": "Acme Builders LLC",
"contact_name": "Jane Doe",
"email": "jane@acmebuilders.com",
"phone": "512-555-0100"
},
"debtor": {
"name": "Delinquent Corp",
"business_type": "LLC",
"contact_email": "ap@delinquent.com",
"contact_phone": "214-555-0199",
"physical_address": "100 Main St, Dallas, TX 75201"
},
"claim": {
"amount_cents": 1500000,
"description": "Unpaid invoices for framing subcontract",
"client_reference": "INV-2048",
"invoice_date": "2026-03-15"
},
"tos_accepted": true,
"turnstile_token": "<optional; validated if TURNSTILE_SECRET set>"
}
```
Behavior:
- Validate all fields (see §6). `amount_cents` > 0 and ≤ 100_000_000 ($1M cap; larger flagged
`validation_error` — **[CONDUCTOR DECISION #2]**). Reject if any free-text field matches an
SSN or PAN regex.
- If a client with this (lowercased) email exists, reuse it and update contact fields; else create
a new client with a fresh `CLT-YYYY-NNNN`. `tos_accepted` must be `true` → set `tos_accepted_at`.
- Always create a new debtor row + new claim (`status=NEW`, `tier=TIER_1`) with `DRE-YYYY-NNNN`.
- Insert `audit_log` create rows; insert a `SYSTEM`/`SHARED` case note "Claim received."
- Send email to `dre@debtrecoveryexperts.com` (team notification) via the germainebrown.com relay
(`mail.germainebrown.com:2525`, per platform email pitfalls) with claim summary. Email send
failure must NOT fail the request — log it, still return success (**[CONDUCTOR DECISION #3]**).
- Fire-and-forget confirmation email to the client (optional v1).
Response `201`:
```json
{
"claim_number": "DRE-2026-0001",
"client_number": "CLT-2026-0001",
"status": "NEW",
"message": "Claim received. Our team will review and contact you shortly."
}
```
### 2.3 Magic-link auth (public)
**`POST /api/auth/request`** — Public. Requests a login link.
```json
{ "email": "jane@acmebuilders.com" }
```
- Always returns `200 {"message":"If an account exists, a login link has been sent."}` regardless of
whether the email exists (no account enumeration).
- If the email maps to a client: generate a 32-byte URL-safe random token, store only its sha256
in `auth_tokens` with `expires_at = now + 15min`, email the link to the client via the DRE relay:
`https://portal.debtrecoveryexperts.com/portal/verify?token=<raw>`.
- Rate limits: max 3 requests per email per 15 min AND max 10 per IP per hour → `429 rate_limited`.
**`POST /api/auth/verify`** — Public. Exchanges a magic-link token for a session.
```json
{ "token": "<raw token from email link>" }
```
- Hash the token, look up an unconsumed, unexpired row. If none → `401 unauthorized`.
- Mark `consumed_at`, create a `sessions` row (7-day expiry), return the session token.
- Response `200`: `{ "session_token": "<raw>", "expires_at": "<iso>", "client": {"client_number":"CLT-2026-0001","company_name":"...","contact_name":"..."} }`
- Frontend stores `session_token` (localStorage or an HttpOnly cookie set by the backend —
**[CONDUCTOR DECISION #4: cookie vs bearer]**; spec defaults to bearer in localStorage for
simplicity, documented XSS mitigations in §6).
**`POST /api/auth/logout`** — Client. Revokes current session. → `200 {"message":"Logged out."}`
**`GET /api/auth/me`** — Client. → `200 { client: {...}, claim_count: N }`. Used by portal to
confirm session on load.
### 2.4 Claims (client)
**`GET /api/claims`** — Client. Lists the caller's claims (newest first).
```json
{ "claims": [
{ "claim_number":"DRE-2026-0001", "status":"ACTIVE", "status_label":"In Progress",
"tier":"TIER_2", "amount_cents":1500000, "amount_display":"$15,000.00",
"debtor_name":"Delinquent Corp", "created_at":"<iso>", "date_resolved":null }
]}
```
**`GET /api/claims/{claim_number}`** — Client. Full detail; 404 if not owned by caller (never leak
existence of other clients' claims — return 404, not 403).
```json
{
"claim_number":"DRE-2026-0001", "status":"ACTIVE", "status_label":"In Progress",
"tier":"TIER_2", "tier_step":2, "amount_cents":1500000, "amount_display":"$15,000.00",
"description":"Unpaid invoices...", "client_reference":"INV-2048",
"invoice_date":"2026-03-15", "date_assigned":"<iso>", "date_resolved":null,
"debtor": { "name":"Delinquent Corp", "business_type":"LLC" },
"documents": [ { "id":"...", "original_name":"invoice.pdf", "size_bytes":48210,
"mime_type":"application/pdf", "uploaded_by":"CLIENT", "created_at":"<iso>" } ],
"notes": [ { "author_type":"STAFF", "author_name":"Anita", "subject":null,
"content":"We've sent the first demand.", "created_at":"<iso>" } ]
}
```
Notes list returns only `visibility='SHARED'` rows for client callers. Debtor block excludes
internal fields (contact/address hidden from client — **[CONDUCTOR DECISION #5]**; spec default:
client sees debtor name + type only).
### 2.5 Documents
**`POST /api/claims/{claim_number}/documents`** — Client. `multipart/form-data`, field `file`.
- Enforce: max 20 MB per file; allowed MIME/extensions `.pdf .jpg .jpeg .png .doc .docx`; verify
magic bytes, not just extension. Reject others → `415 unsupported_media_type` / `413 payload_too_large`.
- Store to `/opt/dre-portal/data/uploads/<claim_id>/<uuid><ext>` (mode 0640), compute sha256, insert
`documents` row + audit + `SYSTEM` shared note "Document uploaded: <name>".
- Response `201`: the document metadata object.
**`GET /api/claims/{claim_number}/documents/{document_id}`** — Client or Staff. Streams the file with
`Content-Disposition: attachment`. 404 if not owned (client) / not found (staff). Never serve uploads
via Caddy static — always through this authenticated endpoint.
### 2.6 Messaging / case notes (client)
**`POST /api/claims/{claim_number}/messages`** — Client. Structured message to the team.
```json
{ "subject": "New information about the debtor", "content": "They changed their address to..." }
```
- `subject` must be one of the fixed options (validated): `Question about my claim`,
`New information about the debtor`, `Payment received / want to stop recovery`,
`Update my contact info`, `Complaint or concern`, `Other`.
- Insert `case_notes` (`author_type=CLIENT`, `visibility=SHARED`), audit, email the DRE team.
- Response `201`: the created note object.
(Client reads notes via the claim-detail endpoint §2.4; no separate GET needed for v1.)
### 2.7 Internal staff endpoints (staff-key auth)
These back the existing internal dashboards (replace mock rows).
**`GET /api/staff/claims`** — Staff. All claims with filters:
`?status=NEW&tier=TIER_2&q=<search>&limit=50&offset=0`. Search matches claim_number, company_name,
debtor_name. Returns claims joined with client + debtor summary + counts.
**`GET /api/staff/claims/{claim_number}`** — Staff. Full detail incl. INTERNAL notes, debtor contact
fields, all documents, and audit trail.
**`PATCH /api/staff/claims/{claim_number}`** — Staff. Update status/tier and resolution dates.
```json
{ "status":"ACTIVE", "tier":"TIER_2", "reason":"Docs approved, moving to formal demand" }
```
- Validate enum values. On status change to a resolved state, set `date_resolved`; on first move out
of `NEW`, set `date_assigned`. Write audit rows (old→new, actor=staff, reason). Optionally auto-add
a `SYSTEM`/`SHARED` note so the client sees the status change. **This is the write-back that flows
to the client portal.**
- Response `200`: updated claim detail.
**`POST /api/staff/claims/{claim_number}/notes`** — Staff. Add a note.
```json
{ "content":"Called debtor, left VM", "visibility":"INTERNAL", "author_name":"Tony" }
```
`visibility` defaults `INTERNAL`; set `SHARED` to make it client-visible. Response `201`.
**`POST /api/staff/claims/{claim_number}/documents`** — Staff. Same as client upload but
`uploaded_by=STAFF`; may be marked to appear (or not) to client via a `client_visible` flag
(**[CONDUCTOR DECISION #6]**; spec default: staff uploads are internal-only, not shown to client).
**`GET /api/staff/stats`** — Staff. Aggregate rollups for dashboard/analytics cards:
```json
{
"total_claims": 12, "by_status": {"NEW":3,"ACTIVE":5,"SETTLED":2,"CLOSED":2},
"by_tier": {"TIER_1":4,"TIER_2":5,"TIER_3":3},
"total_amount_cents": 42000000, "total_amount_display":"$420,000.00",
"recovered_amount_cents": 12000000, "open_amount_cents": 30000000,
"aging": { "over_30_days": 2, "over_60_days": 1 }
}
```
**`GET /api/staff/audit?entity_type=claim&entity_id=<id>`** — Staff. Audit trail for change history UI.
### 2.8 Endpoint summary table
| Method | Path | Auth | Purpose |
|--------|--------------------------------------------------|--------|----------------------------------|
| GET | `/api/health` | Public | Liveness |
| POST | `/api/intake` | Public | Create client+debtor+claim |
| POST | `/api/auth/request` | Public | Request magic link |
| POST | `/api/auth/verify` | Public | Exchange token → session |
| POST | `/api/auth/logout` | Client | Revoke session |
| GET | `/api/auth/me` | Client | Session/account check |
| GET | `/api/claims` | Client | List own claims |
| GET | `/api/claims/{claim_number}` | Client | Own claim detail |
| POST | `/api/claims/{claim_number}/documents` | Client | Upload document |
| GET | `/api/claims/{claim_number}/documents/{id}` | Client/Staff | Download document |
| POST | `/api/claims/{claim_number}/messages` | Client | Message the team |
| GET | `/api/staff/claims` | Staff | All claims + filters |
| GET | `/api/staff/claims/{claim_number}` | Staff | Full internal detail |
| PATCH | `/api/staff/claims/{claim_number}` | Staff | Update status/tier (write-back) |
| POST | `/api/staff/claims/{claim_number}/notes` | Staff | Add internal/shared note |
| POST | `/api/staff/claims/{claim_number}/documents` | Staff | Staff upload |
| GET | `/api/staff/stats` | Staff | Aggregate dashboard metrics |
| GET | `/api/staff/audit` | Staff | Change history |
---
## 3. Magic-Link Auth Flow
**Goal:** passwordless, secure-by-default client login.
1. **Request.** Client enters email on `login.html` → `POST /api/auth/request {email}`.
2. **Generate.** Backend: if email matches a client, create `token = secrets.token_urlsafe(32)`.
Store ONLY `sha256(token)` in `auth_tokens` with `expires_at = now + 15 minutes`, `consumed_at=NULL`,
`requested_ip`. Never store or log the raw token.
3. **Deliver.** Email the client (via `mail.germainebrown.com:2525`, from `dre@debtrecoveryexperts.com`)
a link: `https://portal.debtrecoveryexperts.com/portal/verify?token=<raw>`. Always respond `200`
with a generic message (anti-enumeration).
4. **Click.** The `verify` page reads `token` from the query string and calls
`POST /api/auth/verify {token}`.
5. **Exchange.** Backend hashes the token, finds a row that is unexpired AND unconsumed. If found:
set `consumed_at=now` (single-use), create a `sessions` row (`session_token=token_urlsafe(32)`,
store `sha256`, `expires_at = now + 7 days`), return the raw session token + client summary.
6. **Authenticated calls.** Frontend sends `Authorization: Bearer <session_token>` on every portal
API call. Backend hashes it, looks up a non-revoked, unexpired session, resolves `client_id`,
updates `last_seen_at`.
7. **Logout.** `POST /api/auth/logout` sets `revoked_at`.
**Security notes:**
- Tokens are 256-bit random (`secrets`), URL-safe. Only sha256 hashes are persisted → DB leak does
not yield usable tokens.
- Magic-link TTL 15 min; single-use (consumed on verify). Session TTL 7 days, revocable.
- Constant-time comparison for hashes and the staff key (`hmac.compare_digest`).
- Rate limit `/api/auth/request` (3/email/15min, 10/IP/hour) to stop link-spam / mailbox flooding.
- No account enumeration: identical `200` response whether or not the email exists.
- Verify page must POST the token (not GET-navigate to the API) so the raw token stays out of the
API's access logs / Referer chains; the page strips `?token=` from the URL after reading it.
- Expired/consumed tokens are pruned by a lightweight sweep on each verify attempt (delete rows
where `expires_at < now - 1 day`).
---
## 4. Frontend Page Inventory + Data Mapping
Existing files live in `/var/www/capabilities/` (public) and `/var/www/internal/` (staff). Sonnet 5
wires these to the API. **New pages** are flagged NEW.
| Page (file) | Location | Auth | Calls | Displays / Action |
|-------------------------------------|------------|-------------|----------------------------------------------------|-------------------|
| `debt-recovery.html` (intake) | public | none | `POST /api/intake` | Wire the dead `<form>`: collect Your Info / Debtor Info / Claim Details, submit JSON, show returned claim number + confirmation. Optional Turnstile. |
| `login.html` | public | none | `POST /api/auth/request` | Add an email field + "Email me a login link" button. Replace/append to the SSO-only pattern. Show "check your email" state. |
| `portal/verify` (NEW) | public | none→client | `POST /api/auth/verify` | Reads `?token`, exchanges for session, stores session token, redirects to client dashboard. Handles invalid/expired token error state. |
| `dre-client-dashboard.html` | public* | client | `GET /api/auth/me`, `GET /api/claims` | Replace empty-state/mock rows with real active + past claims, stat cards (count, recovered, open), tier progress bar. Requires session; redirect to login if 401. |
| `portal/claim` (NEW or extend dash) | public* | client | `GET /api/claims/{n}`, `POST .../documents`, `POST .../messages` | Claim detail: status/tier progress, document list + upload dropzone (real `<input type=file>`), shared notes thread, "message the team" form with fixed subjects. |
| `dre-dashboard.html` (internal) | internal | staff | `GET /api/staff/claims`, `PATCH /api/staff/claims/{n}` | Replace mock claim rows with live data; status/tier update controls that write back. |
| `dre-case-aging.html` | internal | staff | `GET /api/staff/claims` (sort by age), `GET /api/staff/stats` | Aging buckets from real `created_at`/`date_assigned`. |
| `dre-analytics.html` | internal | staff | `GET /api/staff/stats` | Replace static charts with real by_status / by_tier / recovered totals. |
| `inbox.html` | internal | staff | (unchanged — IMAP poller JSON) | Out of scope; keep as-is. |
| `letter-queue.html` | internal | staff | (v1: unchanged; later reads `GET /api/staff/claims`) | Not wired in v1. |
\* Client dashboard/claim pages are currently in the public docroot. Since they now require a session
token (enforced by the API — every data call is 401 without a valid session), they can stay in
`/var/www/capabilities/`; the pages themselves render an empty shell + "please log in" until the
session resolves. **[CONDUCTOR DECISION #7: keep client portal on `portal.` public docroot vs move
behind its own path.]** Spec default: keep in public docroot, gate by API session.
**Frontend session handling:** store the session token in `localStorage` under `dre_session`. Send
as `Authorization: Bearer`. On any `401`, clear it and redirect to `login.html`. (If Conductor picks
HttpOnly cookies in Decision #4, backend sets `Set-Cookie: dre_session=...; HttpOnly; Secure;
SameSite=Lax` and frontend drops the localStorage logic.)
---
## 5. Deployment Plan
Mirror the `/opt/ops-portal` pattern: venv + uvicorn under systemd, localhost port, Caddy in front.
### 5.1 Layout
```
/opt/dre-portal/
├── app/ # FastAPI code (from GLM-5.2)
│ ├── main.py # app + routers
│ ├── db.py # sqlite connection helper (pragmas), migrations runner
│ ├── schema.sql # the CREATE TABLE block from §1
│ ├── auth.py, intake.py, claims.py, staff.py, email.py, ...
├── data/
│ ├── dre.db # SQLite (WAL)
│ └── uploads/<claim_id>/ # uploaded docs, mode 0640
├── .env # secrets (mode 0600)
└── venv/ # python venv
```
Code home for git is `/root/projects/dre/` (repo). Deploy = `git pull` in the repo then rsync/symlink
the `app/` into `/opt/dre-portal/app/` (or clone the repo directly into `/opt/dre-portal` and run
from there — **[CONDUCTOR DECISION #8: run-from-repo vs deploy-copy]**; spec default: clone repo at
`/opt/dre-portal`, `data/` and `.env` gitignored).
### 5.2 Environment (`/opt/dre-portal/.env`, chmod 600)
```
DRE_STAFF_KEY=<64-hex random>
DRE_DB_PATH=/opt/dre-portal/data/dre.db
DRE_UPLOAD_DIR=/opt/dre-portal/data/uploads
DRE_BASE_URL=https://portal.debtrecoveryexperts.com
# Email relay (per platform email pitfalls — use germainebrown.com relay, NOT MXroute:587)
DRE_SMTP_HOST=mail.germainebrown.com
DRE_SMTP_PORT=2525
DRE_SMTP_FROM=dre@debtrecoveryexperts.com
DRE_TEAM_NOTIFY=dre@debtrecoveryexperts.com
DRE_SMTP_USER=<from ~/.hermes/.env DRE_EMAIL_* if relay auth required>
DRE_SMTP_PASS=<...>
TURNSTILE_SECRET=<optional; if unset, intake skips captcha check>
```
Reuse existing `DRE_EMAIL_*` creds from `~/.hermes/.env` for SMTP if the relay needs auth.
### 5.3 systemd unit — `/etc/systemd/system/dre-portal.service`
```ini
[Unit]
Description=DRE Customer Portal API (FastAPI/uvicorn)
After=network.target
[Service]
Type=simple
WorkingDirectory=/opt/dre-portal
EnvironmentFile=/opt/dre-portal/.env
ExecStart=/opt/dre-portal/venv/bin/uvicorn app.main:app --host 127.0.0.1 --port 8090
Restart=on-failure
RestartSec=3
# hardening
NoNewPrivileges=true
PrivateTmp=true
ProtectSystem=strict
ReadWritePaths=/opt/dre-portal/data
ProtectHome=true
[Install]
WantedBy=multi-user.target
```
Enable: `systemctl daemon-reload && systemctl enable --now dre-portal`.
### 5.4 Caddy routing
Extend the existing `portal.debtrecoveryexperts.com` block so `/api/*` proxies to the app while
static files continue to serve. **Order matters** — the `handle /api/*` must precede `file_server`.
```caddyfile
portal.debtrecoveryexperts.com {
handle /api/* {
reverse_proxy localhost:8090
}
handle {
root * /var/www/capabilities/
try_files {path} {path}.html /index.html
file_server
}
}
```
`pay.` and `internal.` blocks unchanged. After edit: `caddy validate --config /etc/caddy/Caddyfile
&& systemctl reload caddy`. **The app must NEVER be exposed on a public port — only 127.0.0.1:8090.**
### 5.5 Init / migration steps
1. `python3 -m venv /opt/dre-portal/venv`
2. `venv/bin/pip install fastapi uvicorn[standard] python-multipart` (+ `email-validator`; stdlib
`sqlite3`, `secrets`, `hashlib`, `hmac`, `smtplib` cover the rest — no ORM in v1).
3. On first boot, `db.py` runs `schema.sql` if the DB is absent (idempotent `CREATE TABLE IF NOT
EXISTS`), then seeds `number_sequences` for the current year if missing.
4. Migrations: numbered SQL files in `app/migrations/NNNN_*.sql`, tracked in a `schema_migrations`
table; runner applies unapplied ones on startup. (v1 ships with `0001_init.sql` == schema.sql.)
5. `chown -R dre-portal:dre-portal /opt/dre-portal/data` (or the service user); `chmod 750 data`,
`chmod 640` on the db file. **[CONDUCTOR DECISION #9: dedicated service user vs run as existing
ops user like ops-portal.]** Spec default: reuse the ops-portal service user pattern.
---
## 6. Security & Edge Cases
**Input validation (all endpoints):**
- Use Pydantic models for every request body; reject unknown fields (`extra="forbid"`).
- Email validated (`email-validator`), lowercased before storage/lookup.
- `amount_cents`: positive int, ≤ 100_000_000 (see Decision #2). Reject non-integer / float.
- `business_type`, `status`, `tier`, message `subject` validated against their fixed enums server-side.
- String length caps: names ≤ 200, description ≤ 5000, note content ≤ 10000, address ≤ 500.
- **PII rejection:** run every free-text intake field through SSN regex `\b\d{3}-?\d{2}-?\d{4}\b`
and PAN regex `\b(?:\d[ -]?){13,19}\b`; if matched, reject with `validation_error` "Do not include
Social Security or bank/card numbers." (Compliance-critical.)
**SQL injection:** ALL queries use parameterized statements (`?` placeholders / named params via
`sqlite3`). NEVER f-string/format user input into SQL. Table/column names are never taken from input.
**XSS in case notes / messages:** store content as raw plaintext; the frontend renders it with
`textContent` (never `innerHTML`) OR the backend returns an `content_html` that is HTML-escaped
server-side. Spec: **store raw, escape on output**; API returns escaped `content` and the frontend
inserts via `textContent`. No markdown/HTML allowed in v1. Subject is enum-only (no free text).
**Magic-link rate limiting:** in-process token-bucket / sliding-window counters keyed by email and
by IP (backed by a small in-memory dict with periodic cleanup; acceptable for single-instance v1).
`3/email/15min`, `10/IP/hour` on `/api/auth/request`; `10/IP/15min` on `/api/auth/verify` (brute-force
guard — though 256-bit tokens make guessing infeasible). Intake: `20/IP/hour` + Turnstile if configured.
**File upload:**
- Max 20 MB/file (enforced by reading `Content-Length` AND streaming with a hard byte cap).
- Allowlist extensions + MIME + magic-byte sniff (`python-magic` optional; else check known
signatures for PDF `%PDF`, JPEG `FFD8`, PNG `89504E47`, ZIP-based docx `504B0304`). Reject on
mismatch.
- Store OUTSIDE any web-served directory (`/opt/dre-portal/data/uploads`), uuid-named to prevent
path traversal; never trust `original_name` for the path. Sanitize `original_name` for display.
- Serve only via the authenticated download endpoint with `Content-Disposition: attachment` and a
safe `Content-Type` (or `application/octet-stream`) to prevent inline execution.
- Per-claim document count cap (e.g. 50) to prevent abuse.
**AuthZ / data isolation:** every client claim query filters by the session's `client_id`. Accessing
another client's `claim_number` returns `404` (not `403`) to avoid confirming existence. Staff key
compared with `hmac.compare_digest`.
**Transport & secrets:** HTTPS enforced by Caddy (app only on localhost). Secrets only from `.env`;
never logged. Redact tokens/keys from logs. Access logs must not contain the `?token=` query value
(verify uses POST).
**Other edge cases:**
- Duplicate email at intake → reuse client, still create new claim (a client can have many claims).
- Concurrent number generation → atomic `UPDATE number_sequences SET last_value = last_value + 1 ...
RETURNING last_value` inside the same transaction as the insert; retry on the (rare) SQLite busy.
- Year rollover → sequence keyed by `(prefix, year)`; new year starts at 0001 automatically.
- Email relay down → intake/messages still succeed (email is best-effort); failure logged + surfaced
in `audit_log` as a `note` action so staff can follow up.
- Clock/expiry → all comparisons in UTC; expired tokens/sessions rejected and lazily pruned.
- Empty portal (new client, no claims) → endpoints return empty arrays; frontend shows empty state.
---
## 7. Open Decisions for the Conductor
| # | Decision | Spec default (build this unless overridden) |
|---|----------|---------------------------------------------|
| 1 | Extra statuses (`UNDER_REVIEW`,`REJECTED`,`TIER_2_5`) diverge from TwentyCRM enums. Keep richer local enum? | **Yes** — keep richer enum; sync layer maps down later. |
| 2 | Max claim amount cap. | **$1,000,000** (100_000_000 cents); larger → validation error. |
| 3 | Should intake fail if the team-notification email fails to send? | **No** — email is best-effort; request still returns 201. |
| 4 | Session transport: Bearer token in localStorage vs HttpOnly cookie. | **Bearer in localStorage** (simpler; XSS mitigated by textContent rendering). |
| 5 | Does the client see debtor contact/address in claim detail? | **No** — client sees debtor name + type only. |
| 6 | Are staff-uploaded documents visible to the client? | **No** — staff uploads internal-only by default. |
| 7 | Keep client dashboard/claim pages in public docroot (API-gated) or move behind a path? | **Keep in public docroot**, gate by API session. |
| 8 | Deploy model: run FastAPI directly from the git repo clone at `/opt/dre-portal`, or copy `app/` from `/root/projects/dre`? | **Clone repo at `/opt/dre-portal`**; `data/` + `.env` gitignored. |
| 9 | Service user: dedicated `dre-portal` user vs reuse ops-portal user. | **Reuse ops-portal service-user pattern.** |
**Also flag to conductor (informational, not blocking):**
- TwentyCRM Payment→Claim and CaseNote→Claim relations are still missing (per current state). The
future sync layer will need them; not required for this backend.
- Turnstile secret not yet provisioned — intake ships with captcha check *conditional* on the env var,
so it works with or without it.
- LPOA/DocuSeal, Stripe, LetterStream, AI scoring are all explicitly deferred (fast-follow).
---
## 8. Handoff Notes
- **GLM-5.2 (backend):** implement §1 schema verbatim, §2 endpoints, §3 auth, §5 deploy, §6 security.
No ORM required — stdlib `sqlite3` with parameterized queries + Pydantic for validation. Keep raw
tokens out of the DB and logs. Reuse `~/.hermes/.env` `DRE_EMAIL_*` creds for SMTP via the
germainebrown.com relay.
- **Sonnet 5 (frontend):** wire the pages per §4. Every data call sends `Authorization: Bearer`;
render all user/staff text via `textContent`. Real `<input type=file>` dropzone (see platform
pitfall). `chmod 644` any new HTML in the webroots. Keep the D|R|E logo, nav, and theme
conventions from the platform skill.
- **Both:** the internal dashboards read from `/api/staff/*` with the `X-DRE-Staff-Key` header
(still behind Cloudflare Access). Status changes via `PATCH /api/staff/claims/{n}` are the
write-back that surfaces in the client portal.
Reference in New Issue View Git Blame Copy Permalink