# QuitSure Quiz Funnel (Quira) — Enterprise Code Audit

**Date:** 2026-07-22
**Scope reviewed:** `app.py` (1,010 LOC), `db.py` (803 LOC), `templates/index.html` (5,981 LOC), `static/facts.json`, `.env` / `.env.example`, `.gitignore`, `requirements.txt`, tracked docs, and git history.

---

## Executive Summary

**Overall health: FAIR — a functional, thoughtfully-built prototype that is not production-safe.**

The application *works*, the product logic is smart (deterministic server-side scoring, a strong LLM-fallback chain, no SQL injection), and it is unusually well-documented for a prototype. But it is dragged down hard by a **live credential leak**, **no authentication on any endpoint**, **three prompt-injection-to-XSS sinks with no CSP**, an **insecure OTP scheme**, and **synchronous DB/email I/O blocking the async event loop**. These are not theoretical — several are directly exploitable by an anonymous visitor against your user/PII database and your users' inboxes. Fix the ~6 top items and this jumps from a D to a solid B‑.

---

## Scorecard

| Category | Score (/100) | One-line justification |
|---|---|---|
| Project Structure | 55 | Sensible 2-file backend split; no packages/config/service layers; monolithic 5,981-line frontend |
| Code Quality | 52 | Good docstrings, but heavy duplication, 400+ line functions, dead code, magic numbers |
| Python Best Practices | 48 | No type hints on handlers, no Pydantic, `print` over `logging`, **sync I/O in async**, local re-imports |
| Architecture | 55 | Layered-ish; business logic + giant prompts inline in handlers; no DI/config module |
| **Security** | **26** | Leaked live creds, no auth, 3× XSS, OTP backdoor + brute-forceable, XFF-spoof rate bypass |
| Performance | 44 | Blocking event loop, no pooling, connection-per-call, unbounded in-memory leaks, execute-in-loop |
| Environment Variables | 55 | Mostly clean; `ENV` unused, `STRIPE_URL` missing from template, hardcoded URL default |
| Dependency Management | 40 | **Zero version pins**; rolled own `.env` parser instead of `python-dotenv`; no lockfile |
| Error Handling | 40 | Broad `except Exception` → `print` → swallow; silent failures reported as success; no rollback |
| Logging | 30 | `print()` ×35, no levels, **PII + OTP logged in plaintext**, no correlation IDs |
| API Design | 45 | RPC-style POSTs OK; no Pydantic/OpenAPI models, inconsistent shapes, `200` for errors, no versioning |
| Database | 55 | **Parameterized (no SQLi)** + utf8mb4, but no pooling, sync-in-async, execute-in-loop, no migrations |
| Testing | 5 | No tests, no framework, no CI. Zero coverage |
| Documentation | 65 | Strong docs & docstrings — but now inaccurate vs code, and one doc leaked the secret |
| DevOps | 30 | No Docker/CI/CD, manual uvicorn, plaintext-file secrets, `ENV` separation not implemented |
| Git Hygiene | 38 | Good `.gitignore`, `.env` never committed — but a live secret is in a tracked doc + history |
| Maintainability | 50 | Small enough to grok; duplication, global-scope frontend, 3 hand-synced arrays, no tests raise risk |
| Production Readiness | 28 | Not ready: leaked creds, no auth, XSS, blocking loop, unbounded memory, no monitoring/tests |

### Overall Score: **41 / 100 — Grade D**

*Weighted* (Security 15%, Production Readiness 10%, Code Quality 8%, Python 7%, Performance 7%, Architecture 6%, Error Handling 6%, Testing 6%, Structure/DB 5% each, API/Deps/Logging/Docs 4% each, Env 3%, DevOps 3%, Git 2%, Maintainability 1%). The score is depressed almost entirely by the security and production-readiness axes; the engineering fundamentals underneath them are C+/B‑ grade.

---

## Findings

### 🔴 CRITICAL

**C1 — Live database + PII credentials are recoverable from the repository**
- **Files/lines:** `IMPLEMENTATION_SPEC.md:506,509,516,517` (current tree: real RDS host + username in plaintext); password in git history at commit `db81423` (`IMPLEMENTATION_SPEC.md:436,443`), only cosmetically changed to `xxxxx` in `2cee580`.
- **Why it matters:** Host (`qs-test-dec2025…rds.amazonaws.com`) + user (`qs-neel`) are live in the tree *today*; the matching password sits in history and **still matches your `.env`**. The **same** credentials serve both the main DB and the `quitsureUsers` PII DB (`db.py:20-41` + `.env`), so one leak = full read/write to user emails.
- **Fix:** ① Rotate the RDS password now. ② Replace host/username in the spec with placeholders. ③ `git filter-repo`/BFG to purge history, force-push, re-clone. ④ Give the PII DB **separate** credentials. ⑤ Verify the RDS security group isn't internet-open.

**C2 — Prompt-injection → stored/reflected XSS (3 sinks), and no CSP to contain it**
- **Files/lines:** `index.html:3599` (`addLetterCard` renders `/api/personal-letter` raw, via `5641`); `index.html:5868` (`botType(data.reply)` renders `/api/ask-quincy` raw, input is the user's typed question at `5861`); `index.html:5625` (`/api/classify-type` explanation rendered raw, `5563`). Render primitive `addBotMsg` does `row.innerHTML = …` with no escaping (`index.html:3354`). No CSP meta in `<head>` (`index.html:4-11`). Backend compounds it: `/api/chat` does **not** strip HTML from the reply (contrast `app.py:431` in `ask-quincy`).
- **Why it matters:** These endpoints echo LLM output — steered by user free-text (`name`, `future_vision`, typed questions) — straight into `innerHTML` on the same page that captures email/PII and routes to payment. A model coaxed into emitting `<img src=x onerror=…>` executes script; with no CSP there is zero backstop.
- **Fix:** Route **all four** LLM sinks (letter, ask-quincy, classify explanation, `askCoach`) through one helper that HTML-escapes first, then re-introduces only an allowlist (`<strong>`, `<em>`). Add a strict CSP `<meta>`/header. Also strip HTML server-side in `/api/chat` as defense-in-depth. This one change closes findings 1–5 from the frontend pass.

### 🟠 HIGH

**H1 — No authentication/authorization on any endpoint → real abuse vectors**
- **Files/lines:** every route in `app.py` (`/api/save-email` `:855`, `/api/chat` `:142`, `/api/ask-quincy` `:293`, `/api/personal-letter` `:534`, `/api/classify-type` `:436`). No auth dependency anywhere.
- **Why it matters:** Anonymous callers can (a) **email-bomb** arbitrary QuitSure users — `save-email` with an *existing* email triggers a Postmark OTP send (`app.py:885` → `db.py:664`), burning Postmark cost and spamming inboxes; (b) create junk rows across 6 user tables + PII DB (`db.py:311`); (c) use your Gemini key as a **free LLM proxy** (quota/cost drain). Becomes Critical once pointed at prod.
- **Fix:** Require a signed session/HMAC from the served page; per-session (not just per-IP) limits after email capture; CAPTCHA before the OTP-send path; cap Gemini spend.

**H2 — Synchronous DB + Postmark I/O blocks the async event loop**
- **Files/lines:** async handlers call **sync** `pymysql` — `app.py:845, 882, 919, 984-985` → `db.py` (`get_db` `:46`); OTP email uses blocking `httpx.post` (`db.py:781`), not `AsyncClient`.
- **Why it matters:** Each blocking DB/email call freezes *all* concurrent requests on that uvicorn worker for its full duration. Combined with 25s LLM timeouts (`app.py:194`), a handful of users can starve a worker. (The LLM calls themselves are correctly async — only DB/Postmark block.)
- **Fix:** `await run_in_threadpool(...)` for the sync calls, or switch to an async driver (`aiomysql`/`asyncmy`) + `httpx.AsyncClient` for Postmark.

**H3 — Insecure OTP scheme (backdoor + no expiry + brute-forceable + weak RNG)**
- **Files/lines:** hardcoded backdoor `091081` for `ram@quitsure.app` shipped in code (`db.py:659`); `verify_otp` **never checks time** — `WHERE bUsed=0 AND bExpired=0` with no `dDateCreated` window (`db.py:710-718`), so the "expired" message is a lie and codes are valid forever; `/api/verify-otp` (`app.py:927`) has **no rate limiting** (the limiter guards only chat/ask-quincy), so a 6-digit code is brute-forceable; generated with non-CSPRNG `random.randint` (`db.py:661`).
- **Why it matters:** OTP is the *only* gate protecting an existing user's record from being overwritten (`update_existing_user`, `db.py:457`). Unexpiring + unthrottled + 10⁶ space = defeatable; the hardcoded code is a literal backdoor now published in the repo.
- **Fix:** Remove the hardcoded code (or gate behind an `ENV=test` check); add a 10-min expiry checked in SQL; limit to ~5 attempts then lock; rate-limit the verify endpoint; use `secrets.randbelow`.

**H4 — `X-Forwarded-For` blindly trusted → rate-limit bypass & log spoofing**
- **Files/lines:** `app.py:46-48` returns the first XFF value with no trusted-proxy allowlist.
- **Why it matters:** Any client sets `X-Forwarded-For: <random>` to get a fresh rate-limit bucket every request, nullifying the limiter, and to poison IP logs.
- **Fix:** Only honor XFF from known proxy IPs (`uvicorn --forwarded-allow-ips`), else use `request.client.host`.

**H5 — Silent failure: errors swallowed and reported to the client as success**
- **Files/lines:** ~27 `except Exception` blocks that just `print` and return (`app.py`, `db.py`); e.g. `create_user` returns `None` on failure (`db.py:453`) but `save-email` still returns `{"ok": True, "userId": None}` (`app.py:924`). No `rollback` anywhere.
- **Why it matters:** Lead/PII writes can fail while the funnel reports success — silent data loss with no signal. Cross-DB writes leave partial state.
- **Fix:** Distinguish "no DB configured" from "DB error"; return 5xx on real failures; add `conn.rollback()`; alert on the error path.

### 🟡 MEDIUM

- **M1 — Unbounded in-memory stores (memory leak + per-worker inconsistency).** `_rate_limit`, `_clickstream`, `_email_captures` are never evicted (`app.py:38,790,791`); `_clickstream` retains every event of every session forever. Multi-worker: state is per-worker (the code already works around this for `_email_captures` via a DB fallback at `app.py:794`). → Move to Redis with TTLs.
- **M2 — Prompt-injection "defense" is a bypassable regex** (`app.py:318`) that only strips "ignore/forget/disregard … instructions." Gives false confidence; real control is output-side escaping (C2).
- **M3 — Dependencies fully unpinned** (`requirements.txt` lists 5 bare names). Non-reproducible builds, supply-chain exposure. → Pin exact versions, add `pyproject.toml` + lockfile.
- **M4 — No request validation.** Raw `request.json()` + `.get()` everywhere; Pydantic (FastAPI's core feature) unused → no schema validation, no OpenAPI, inconsistent responses (`{"ok":…}` vs `{"status":"ok"}` vs `{"reply":…}`), and business failures return `200`.
- **M5 — PII/OTP in plaintext logs.** Full email at `app.py:920`; OTP printed at `db.py:670,777`. (`create_user` masks email at `db.py:449` — inconsistent.) → Never log secrets/PII.
- **M6 — DB efficiency.** No pooling (new pymysql connection per call, `db.py:46`); clickstream inserts run `execute()` in a loop (`db.py:267`) instead of `executemany`. → Pool + batch.
- **M7 — Dead & divergent code/docs.** `/api/profile` always sets `program = "six_day"` (`app.py:757`), so the entire six-week routing in `CLAUDE.md` and `facts.json:117` is unreachable; `CLAUDE.md`'s `route_to_16_week` thresholds and the `severity` reveal don't exist in code. Frontend has write-only `localStorage` (`index.html:5963`, never read) and no-op removed STEPS stubs (`index.html:4506,4513,4582,4657`).
- **M8 — Client-trusted values.** `savings` is computed client-side (`index.html:3810`) and POSTed up; `?region=` override (`index.html:3008`) flips currency/pricing/Stripe-vs-Razorpay routing (`5932`) and is live in prod. → Server must recompute, never trust client `profile`/`savings`; gate `?region=` behind a test flag.
- **M9 — Env drift.** `SUBSCRIPTION_URL` relies on a hardcoded base64 default in code (`app.py:136`) and is absent from `.env`; `STRIPE_URL` used (`app.py:138`) but missing from `.env.example`; `ENV` declared but never read.

### 🟢 LOW

- **L1 — Accessibility.** Pinch-zoom disabled (`index.html:6`); dynamically-created inputs have no label/`aria-label` (`index.html:4065,4123,4183,5420`); progressbar missing `aria-valuemin/max` (`2965`).
- **L2 — Duplication.** Gemini call boilerplate repeated ×6 (`app.py`); `why_labels`/`fear_labels` dicts duplicated (`app.py:449/549`); `create_user`↔`update_existing_user` column mapping copy-pasted (`db.py`); rate-limit block duplicated (`app.py:146,299`). → Extract `call_gemini()` + shared mappers.
- **L3 — Import hygiene.** Local re-imports of `json`/`httpx` inside functions (`app.py:523`, `db.py:363,583,770`); unused `hashlib` import in `app.py:7`.
- **L4 — Frontend robustness.** No global `unhandledrejection`/`window.onerror`; `nextStep` wraps an async step in a sync try/catch and, on error, re-invokes the *same* failing step (`index.html:5943-5969`).
- **L5 — Ops defaults.** `__main__` runs `reload=True`, `host="0.0.0.0"` (`app.py:1008`); app serves HTTP (HTTPS assumed at proxy — documented).

---

## Environment Variables

| Variable | Used? | File | Recommendation |
|---|---|---|---|
| `GEMINI_API_KEY` | ✅ | `app.py:33` | Keep; move to a secrets manager |
| `SUBSCRIPTION_URL` | ✅ | `app.py:135` | Remove hardcoded default; **set it in `.env`** (currently absent → silently uses code default) |
| `STRIPE_URL` | ✅ | `app.py:138` | **Add to `.env.example`** (used in code, missing from template) |
| `DB_HOST/PORT/DATABASE/USERNAME/PASSWORD` | ✅ | `db.py:20-26` | **Rotate password (C1)**; secrets manager |
| `DB_QSUSERINFO_*` | ✅ | `db.py:32-38` | Use **separate** creds from the main DB (currently identical) |
| `POSTMARK_TOKEN/FROM/OTP_TEMPLATE` | ✅ | `db.py:772-774` | Keep; secrets manager |
| `ENV` | ❌ | `.env`, `.env.example` only | **Never read in code** — implement env-gating or remove |

---

## Refactoring Roadmap

### Immediate (today)
- **Rotate the leaked DB password; scrub host/username from the spec; purge git history.** (Rotate: *Small*; scrub: *Small*; history purge + re-clone: *Medium*) — **C1**
- **Add a CSP `<meta>` and route all LLM output through one escape-then-allowlist sanitizer.** (*Small–Medium*) — **C2**
- **Remove/ENV-gate the hardcoded OTP `091081`.** (*Small*) — **H3**

### Short-term (this week)
- Add endpoint auth (signed session/HMAC) + CAPTCHA on the OTP-send path; per-session limits. (*Large*) — **H1**
- Move blocking DB + Postmark calls off the event loop (`run_in_threadpool` or async drivers). (*Medium*) — **H2**
- OTP: SQL expiry + attempt cap + rate-limit verify + `secrets`. (*Medium*) — **H3**
- Fix XFF trust; pin dependencies + add `pyproject.toml`. (*Small* each) — **H4/M3**
- Replace `print` with `logging`; stop logging PII/OTP. (*Medium*) — **M5/Logging**

### Medium-term (this month)
- Pydantic request models + consistent responses; DB connection pool + `executemany`; Redis for rate-limit/clickstream. (*Large*) — **M1/M4/M6**
- Add `pytest` for `/api/profile` scoring, OTP flow, and `db.py` mappers; wire GitHub Actions CI. (*Large*) — **Testing**
- Extract `call_gemini()` + prompt files; dedupe create/update mapping; split JS/CSS out of `index.html`. (*Large*) — **Code Quality**

### Long-term
- Reconcile docs with code (remove dead six-week routing or implement it); secrets manager (AWS Secrets Manager/Vault); observability (structured logs, request IDs, metrics, health check); A/B infra; session resume. (*Major*)

---

## Positive Observations

- **No SQL injection** — every query is parameterized (`db.py` throughout), with `utf8mb4`.
- **Deterministic, auditable server-side scoring** (`app.py:635`) — the right call for user segmentation; keeps LLM nondeterminism out of routing.
- **Excellent LLM-fallback chain** — every LLM call has a scripted fallback (`index.html:3307,5822`), so users never see a broken experience. This is the strongest part of the codebase.
- **User free-text *is* escaped** at most direct-interpolation points (`escapeHtml`, `index.html:3315,3374,4500,5746`).
- **`.env` correctly gitignored and never committed** (the leak came only via a doc).
- **CTA redirect target is fetched server-side** (`index.html:5929`) — no open-redirect.
- **Strong documentation & docstrings** (`CLAUDE.md`, `DEPLOYMENT.md`, `PRODUCTION_PLAN.md`, `IMPLEMENTATION_SPEC.md`) and clear, descriptive commit messages.

---

## Final Verdict

- **Production-ready?** No. The credential leak, absence of auth, XSS trio, insecure OTP, and blocking event loop each independently block a safe launch — especially for a funnel handling email/PII and routing to payment.
- **Can it scale?** Not as written. Blocking sync I/O in async handlers, connection-per-call with no pool, and unbounded per-worker memory will fail under real ad traffic. All fixable with well-understood changes.
- **Is it secure?** No — this is the weakest axis (26/100). But there's no SQLi and secrets aren't shipped to the browser; the gaps are concentrated and closeable.
- **Is it maintainable?** Moderately. It's small and documented, but zero tests, heavy duplication, an all-global 5,981-line frontend, and doc/code drift make change risky.

### Top 10 improvements, by impact
1. Rotate DB creds + purge history + scrub the spec (**C1**).
2. Fix the 3 XSS sinks + add CSP via one shared sanitizer (**C2**).
3. Authenticate endpoints; protect the OTP-send/LLM-proxy abuse paths (**H1**).
4. Move DB/Postmark I/O off the async event loop (**H2**).
5. Harden OTP: remove backdoor, add expiry + attempt/rate limits + CSPRNG (**H3**).
6. Stop trusting `X-Forwarded-For`; fix rate-limit bypass (**H4**).
7. Replace silent `except/print` with real error handling + rollback + honest status codes (**H5**).
8. Introduce Pydantic models, pin dependencies, add `logging` (no PII/OTP) (**M3/M4/M5**).
9. Add a test suite (profile scoring, OTP, DB mappers) + CI (**Testing**).
10. Reconcile docs vs code and remove dead code (six-week routing, write-only localStorage, no-op steps) (**M7**).

*Two categories were in scope but minimally applicable and thus lightly weighted: **DevOps** (no containers/CI exist yet — scored on what's absent) and **API versioning** (single internal RPC surface — no external consumers to version for yet).*