AML Case Desk: Mini Case-Management App - SQL Server & Streamlit -

A compact, realistic AML case desk to demonstrate queues, case actions, QA sampling, audit exports, and SAR summaries. Built for interview-readiness and quick demos.

Features

• Triage view with filters (owner, risk, search, include closed)
• Case page with summary + full timeline (immutable, hash-chained)
• Actions: assign owner, add note, set disposition (SAR/No SAR/Escalate)
• QA tab: deterministic 10% sample of closed cases, record pass/fail
• Exports: per-case Audit CSV + SAR summary (Markdown)
• Synthetic dataset (users, cases, events) seeded via Faker → CSV → SQL Server

---

Architecture

Streamlit UI (triage | case | qa | exports) ↕ pyodbc SQL Server (DB: aml_cases; tables: users, cases, case_events) ↘ exports (CSV/MD) Filesystem (audit.csv, case_*.md)

Audit integrity: every case_events row stores event_hash = sha256(prev_hash || case_id || ts || user || action || notes || meta). Changing any event breaks the chain.

QA sampling: deterministic rule ABS(CHECKSUM(case_id)) % 10 = 0 selects ~10% of closed cases for review.

---

Data Model -SQL Server-

• users(user_id PK, name, role CHECK IN ('Analyst','QA','Manager'), active BIT)
• cases(case_id PK, ..., status CHECK IN ('New','In Review','Closed'), owner FK→users, disposition CHECK IN ('','No SAR','SAR','Escalate'), qa_* fields)
• case_events(event_id PK, case_id FK→cases, ts, user, action, notes, meta, prev_hash, event_hash)
• Indices: cases(status), cases(owner), case_events(case_id, ts)

---

Quickstart

0) Prerequisites

• Python 3.11+
• SQL Server (local ok) + SSMS
• ODBC Driver 17 for SQL Server (Windows usually already installed)
• pip install -r requirements.txt

1) Create the database

Open SSMS and run:

CREATE DATABASE aml_cases;
GO
USE aml_cases;
GO
-- create the three tables; use your schema script (as built in the notebook)
-- if you don't have it handy, import the CSVs with the SSMS Import Wizard and fix types,
-- or create the tables and use BULK INSERT (recommended).

2) Seed data -CSV -- SQL Server-

Use SSMS Import Wizard or BULK INSERT (recommended to keep schema constraints):

BULK INSERT users
FROM 'C:\\Temp\\users.csv'
WITH (FIRSTROW=2, FIELDTERMINATOR=',', ROWTERMINATOR='0x0a', CODEPAGE='65001');

BULK INSERT cases
FROM 'C:\\Temp\\cases.csv'
WITH (FIRSTROW=2, FIELDTERMINATOR=',', ROWTERMINATOR='0x0a', CODEPAGE='65001');

BULK INSERT case_events
FROM 'C:\\Temp\\case_events.csv'
WITH (FIRSTROW=2, FIELDTERMINATOR=',', ROWTERMINATOR='0x0a', CODEPAGE='65001');

3) Configure & run the app

Open app/streamlit_app.py and confirm the connection string:

Server=localhost\\SQLEXPRESS; Database=aml_cases; Trusted_Connection=yes;
Run:

# mac/linux
./run.sh
# windows powershell
./run.ps1


Your browser opens at http://localhost:8501.

How to use the app
Triage

Filter by owner, risk, search; toggle “include closed”; control row limit.

Copy a case_id and jump to Case.

Case

Enter case_id. See summary + timeline.

Actions:

Assign owner → emits assign event

Add note → emits note event

Set disposition (No SAR/SAR/Escalate) → updates status + emits disposition event

QA

Deterministic 10% sample of Closed cases awaiting QA.

Record pass/fail with notes → emits qa_pass or qa_fail event.

Exports

Audit CSV: full event trail with hash chain.

SAR Summary (MD): one-pager with header, notes, QA outcome.

Screenshots / GIF

screenshot