ecw-charge-validation/docs/superpowers/specs/2026-10-08-results-db-and-web-page-design.md

Charge Summary in SQL Server + results web page — design

Date: 2026-10-08. Status: design approved in chat (operator, 2026-10-08), spec under review.

Goal

Every run's Charge Summary rows are also stored in SQL Server, and a simple local web page (FastAPI) shows them. This is the basic version; later it is folded into the full portal.

Decisions (operator, 2026-10-08)

• Server MECARE331\MECARE331 (SQL Server 2022, on this machine), database Charge_Validation (created by the operator).
• The table follows the team's Charge Summary File Format (\\mecare2\Production\Salud-eCW RPA\Charge Summary File Format.xlsx) — the same 24 columns as core/charge_summary.HEADERS, plus run columns and a details JSON. PATIENT, Account Number and Guarantor Name are stored, as in the Excel file.
• FastAPI for the backend. All needed packages are already installed (fastapi 0.141, uvicorn, jinja2, httpx, pyodbc, ODBC Driver 18); nothing new is added to requirements.txt for the bot itself.

Table

tools/sql/create_tables.sql, run by the operator in SSMS (the bot never creates or alters tables):

USE Charge_Validation;
CREATE TABLE dbo.ChargeSummary (
    id             INT IDENTITY(1,1) PRIMARY KEY,
    run_at         DATETIME2      NOT NULL,
    run_mode       NVARCHAR(20)   NOT NULL,  -- VERIFY / LIVE-WRITE / TEST
    s_no           INT            NULL,
    claim_no       NVARCHAR(20)   NOT NULL,
    service_date   DATE           NULL,
    pvdr           NVARCHAR(50)   NULL,
    patient        NVARCHAR(200)  NULL,
    payer          NVARCHAR(200)  NULL,
    group_name     NVARCHAR(100)  NULL,
    status         NVARCHAR(100)  NULL,
    charges        DECIMAL(12,2)  NULL,
    pmts_adjs      DECIMAL(12,2)  NULL,
    pmts_refunds   DECIMAL(12,2)  NULL,
    adjustment     DECIMAL(12,2)  NULL,
    withheld       DECIMAL(12,2)  NULL,
    balance        DECIMAL(12,2)  NULL,
    visit_type     NVARCHAR(100)  NULL,
    account_number NVARCHAR(50)   NULL,
    provider_name  NVARCHAR(200)  NULL,
    guarantor_name NVARCHAR(200)  NULL,
    received_date  DATE           NULL,
    assigned_to    NVARCHAR(100)  NULL,
    completed_on   DATE           NULL,
    comments       NVARCHAR(MAX)  NULL,
    current_status NVARCHAR(200)  NULL,
    category       NVARCHAR(20)   NULL,
    details_json   NVARCHAR(MAX)  NULL
);
CREATE INDEX IX_ChargeSummary_run_at   ON dbo.ChargeSummary (run_at);
CREATE INDEX IX_ChargeSummary_claim_no ON dbo.ChargeSummary (claim_no);

Column map (Excel → DB) lives in one place, results_db.COLUMNS, built from charge_summary.HEADERS so the two cannot drift: S.No→s_no, CLAIMS#→claim_no, SERVICE DATE→service_date, PVDR→pvdr, PATIENT→patient, PAYER→payer, Group Name→group_name, STATUS→status, CHARGES→charges, PMTS/ADJS→pmts_adjs, Pmts/Refunds→pmts_refunds, Adjustment→adjustment, WithHeld→withheld, Balance→balance, Visit Type→visit_type, Account Number→account_number, Provider Name→provider_name, Guarantor Name→guarantor_name, Recevied date→received_date, "Assigned To "→assigned_to, Completed On→completed_on, Comments→comments, Current Status→current_status, Category→category.

Value conversion: money columns strip $, , and spaces → Decimal; dates accept mm/dd/yyyy (and an Excel date/datetime) → date; blank or unparseable → NULL (the raw text is not lost: the Excel file still has it).

Bot side — src/ecw_bot/core/results_db.py

• Settings (settings.py, from .env):
  • RESULTS_DB (bool, default off): write rows to the DB.
  • RESULTS_DB_CONN (string): ODBC connection string. Default: DRIVER={ODBC Driver 18 for SQL Server};SERVER=MECARE331\MECARE331;DATABASE=Charge_Validation;Trusted_Connection=yes;TrustServerCertificate=yes; (Windows login, no password). A SQL login's password, if ever used, is put in .env by the operator only.
• to_record(row: dict, *, run_at, run_mode, details) -> dict: one Charge Summary row (header→value) to DB column→value, converted.
• insert_rows(conn, records) -> int: one parameterized INSERT per row (executemany), one commit; returns rows written. Never builds SQL from values.
• save_summary(sheet, details, run_at, run_mode, connect=pyodbc.connect) -> int | None: sheet is the header+rows list charge_summary.build_rows already returns; details maps claim no → details dict. Opens the connection, inserts, closes. Any exception is caught: one [RESULTS-DB] Could not save ... log line, returns None. The run, the Excel summary and the claims are never affected.
• details_json per claim: {"fired", "changed", "review", "failed", "comments", "saved", "changes": {condition: [change, ...]}, "dental": {"high_risk", "sliding_fee"}} — taken from the claim's data["_conditions"] (the flow adds changes there) and Dental results.

Flow changes

• charge_summary.write() is split: charge_summary.build(report_path, outcomes, assigned_to) -> sheet (read + build_rows), and write() saves that sheet as today. The flow builds once and hands the same sheet to Excel and to the DB, so both always hold the same rows.
• _read_claim_form adds "changes": {r.condition_name: r.changes} to data["_conditions"]; the claim loop keeps details[claim_id] next to outcomes[claim_id].
• _write_charge_summary calls results_db.save_summary(...) after the Excel write when RESULTS_DB is on, and logs [RESULTS-DB] N row(s) saved.
• run_mode: TEST when TEST_CLAIM_IDS_FILE is set, else LIVE-WRITE when SAVE_CORRECTIONS is on, else VERIFY — the same words as the [MODE] banner.

Web page — web/

• web/app.py (FastAPI) and web/templates/index.html (Jinja2), plain HTML/CSS, no external scripts or CDNs.
• Read-only: only SELECT statements, parameterized.
• GET / — page:
  • run picker (distinct run_at, newest first; default latest run),
  • Category filter (All / Pass / Fail / Review) and Claim# search,
  • counts per Category for the selected run (Pass / Fail / Review tiles),
  • the table in Charge Summary column order, Category coloured,
  • each row expands to show details_json (conditions fired, before/after changes, review reasons).
• GET /api/runs — runs with counts. GET /api/results?run_at=&category=&claim_no= — rows as JSON (for the later portal).
• Start: python -m uvicorn web.app:app --host 127.0.0.1 --port 8000. Bound to 127.0.0.1 only: the page shows PHI and is not reachable from the network. A run_web.bat helper does the same.
• The DB connection string is the same RESULTS_DB_CONN. If the DB cannot be reached the page shows a plain error message, not a stack trace.

Error handling

• DB unreachable / permission denied / table missing during a run → one log line, run continues, Excel written.
• A value that does not convert → NULL for that column, row still saved.
• Web page: DB errors → HTTP 503 page "Results database not reachable".

Testing (no real DB)

• tests/test_results_db.py: header→column map covers all 24 HEADERS; money/date conversion (incl. $1,234.50, blank, junk); to_record output; insert_rows sends one parameterized statement with values in column order (fake connection records calls); save_summary swallows a connect error and returns None; details_json shape.
• tests/test_charge_summary.py: build() + write() split keeps today's output.
• tests/test_web_app.py: FastAPI TestClient with a fake repository — page renders rows and Category counts, filters work, /api/results JSON shape, DB error → 503.
• Full suite stays green. Live: operator runs create_tables.sql, sets RESULTS_DB=1, one test run, then opens the page.

Out of scope (later portal)

Login/roles, editing rows, charts beyond the Category tiles, scheduling, AI features, hosting on the network.