# Lead Data Cleaner

A self-hosted dashboard for cleaning lead-list CSVs at scale: dedupe, split
multi-value email/phone fields into individual rows, block military/defense
and educational domains, and browse/download results by platform category
(WordPress, Shopify, Wix, etc.).

Tested against real Shopify-domain-export data (1,765 rows → 1,913 clean
rows after explosion + dedupe).

## Architecture

```
┌────────────┐   upload CSV    ┌──────────────┐   background   ┌─────────────┐
│  React UI  │ ───────────────▶│ FastAPI      │───── thread ──▶│ cleaning.py │
│ (Vite)     │◀─── poll/query ─│ (main.py)    │                │  (Polars)   │
└────────────┘                 └──────┬───────┘                └──────┬──────┘
                                       │                               │
                                       ▼                               ▼
                                 job status.json              clean.parquet
                                 (per job UUID dir)            rejected.parquet
                                       ▲
                                       │ paginate / filter / search
                                       │ (DuckDB queries over Parquet —
                                       │  no full-file load into memory)
                                       ▼
                                 GET /data, /download
```

**Why this stack handles millions of rows:**
- Polars does the actual row explosion/dedupe/filtering, and reads CSVs far
  faster than pandas.
- Cleaned output is stored as **Parquet**, a columnar format DuckDB can filter,
  search, and paginate over without loading the whole file into RAM.
- Upload returns immediately; cleaning runs in a background thread so the UI
  polls for status instead of holding a long HTTP request open.
- CSV downloads are streamed via DuckDB's `COPY ... TO` directly to disk, not
  buffered in Python memory.

**Where this MVP's scaling stops and what to do past that point:**
- Background jobs run as a Python thread in-process. Fine for a single
  server; for true multi-worker horizontal scaling, swap in Celery/RQ/Arq
  with Redis, using the same `jobs.py` interface.
- Job status lives in a JSON file per job. For multi-instance deployments,
  move this to Postgres or Redis so all instances see the same state.
- If a single CSV upload will regularly exceed ~10-20M rows, consider
  chunked/streaming ingestion into the pipeline rather than `pl.read_csv`
  loading it in one shot.

## What the cleaner does

1. **Bulk upload, merged.** Select or drag multiple CSVs at once — they're
   combined into a single dataset before cleaning (columns that differ
   between files are unioned; a file missing a column just gets nulls
   there), so duplicates get caught *across* files too, not just within
   each one.
2. **Explode multi-value fields.** If `emails` or `phones` contain several
   values (any separator — `:`, `;`, `,`, `|`), each gets extracted via
   pattern matching (not a fixed-delimiter split) and turned into its own
   row. Emails and phones are paired by position rather than cross-joined,
   so 2 emails × 2 phones becomes 2 rows, not 4.
3. **Drop exact duplicate rows**, then **dedupe by email** (configurable:
   email / domain / off), keeping the first occurrence.
4. **Block military/defense domains** — `.mil` (any TLD variant) plus an
   editable list at `backend/blocklists/military_domains.txt`.
5. **Block educational domains** — `.edu`, and international academic
   patterns like `.ac.uk`, `.edu.au`, `.ac.in`.
6. Every rejected row is kept in a separate `rejected` dataset with a reason
   column — nothing is silently deleted, so you can audit the filter.
7. Rows are grouped by whatever column looks like a platform/category field
   (`platform` or `category`) for the dashboard's filter and per-category
   download.
8. **Choose columns at download time** — pick a subset of columns in a
   dialog before exporting, independent of what's shown on screen.
9. **Filter by email presence** — "All rows" / "Has email" / "Missing
   email" in the toolbar, applied to both the table view and the download
   (so you can pull just the leads that need enrichment, or just the ones
   ready for outreach).

## Background processing & job history

- Uploads process in the background — the UI polls status instead of
  holding the request open, so a large file doesn't time out a browser tab.
- **Bounded concurrency**: only `MAX_CONCURRENT_JOBS` (default 3) cleaning
  jobs run at once. If you bulk-upload several batches back to back, extras
  wait with status `pending` instead of piling onto the CPU/RAM all at
  once — visible in the job history screen as "Queued".
- **Job history** (top bar → "Job history") lists every upload — files
  included, who uploaded it, status, final row count, and when — for every
  user, since this is meant as a shared team dashboard. From there you can
  jump back into a finished job's results or delete old ones.

## Running locally (development)

```bash
# Backend
cd backend
pip install -r requirements.txt
uvicorn main:app --reload --port 8000

# Frontend (separate terminal)
cd frontend
npm install
VITE_BACKEND_URL=http://localhost:8000 npm run dev
```

Open http://localhost:5173.

## Running with Docker (recommended for anything beyond your own laptop)

```bash
# optional: set an API key that both containers will require/send
export API_KEY=some-long-random-string

docker compose up --build
```

Open http://localhost:8080.

## Deploying somewhere public

This repo is two containers (`backend`, `frontend`) wired together by
`docker-compose.yml`. Any host that runs Docker Compose works:

- **Railway / Render / Fly.io** — point them at this repo; they each support
  docker-compose-style multi-service deploys (check current docs, this
  changes fairly often).
- **Your own VPS** — `git clone`, set `API_KEY`, `docker compose up -d`,
  put nginx/Caddy in front for HTTPS.

Either way, **set `API_KEY`** before exposing this publicly (see Security
below) and **set `ALLOWED_ORIGINS`** in `docker-compose.yml` to your real
frontend domain instead of `localhost`.

## Security notes

- **Login is required by default**, with multi-user support and two roles:
  `admin` (can manage users, clean data) and `member` (can clean/browse
  data, no user management). The first person to open the dashboard sets up
  the initial admin account (one-time setup screen); every launch after
  that hits a login screen. Sessions are server-side (revocable on logout)
  via an httpOnly cookie — not a token sitting in browser storage.
- User accounts live in SQLite (`backend/data/app.db`), not a flat file —
  proper transactions, easy to add/remove/promote users from the "Manage
  users" screen (admin-only) without touching the server.
- Built-in guard rails: an admin can't delete their own account while
  logged in as it, and the last remaining admin can't be deleted or
  demoted — so you can't accidentally lock everyone out.
- Set `COOKIE_SECURE=true` once you're serving over HTTPS (default `false`
  so local HTTP dev still works — cookies with `Secure` won't be sent over
  plain HTTP).
- `API_KEY` is optional and **separate from login** — it's for scripts/CI
  that need to hit the API without an interactive login (treated as an
  implicit admin for job endpoints only; it cannot manage users).
- Uploads are capped at `MAX_UPLOAD_MB` (default 1024MB) and rate-limited
  (`UPLOAD_RATE_LIMIT`, default 10/minute per IP); login/setup are separately
  rate-limited (`LOGIN_RATE_LIMIT`, default 10/minute) against brute-forcing.
- Each upload gets its own server-generated UUID directory — job IDs are
  never built from user input, so there's no path-traversal surface there.
- CSV downloads are sanitized against formula injection (cells starting with
  `=`, `+`, `-`, `@` get neutralized so Excel/Sheets won't execute them).
- **Job history is shared across all logged-in users** (any member can see
  who uploaded what and browse/download/delete any job) — this matches a
  shared team-dashboard model. If you need per-user privacy instead, that's
  a larger change (scoping `/api/jobs` by `created_by`); ask if you want it.
- **Nothing auto-deletes uploaded data.** Run `backend/cleanup_old_jobs.py`
  on a schedule (cron/systemd timer) if you don't want lead lists sitting on
  disk indefinitely:
  ```bash
  docker compose exec backend python cleanup_old_jobs.py --ttl-hours 72
  ```

## User management

- Admins get a "Manage users" link in the dashboard's top bar. From there:
  add a user (email + temporary password + role), change anyone's role,
  reset anyone's password, or remove a user.
- There's no self-service "forgot password" flow — if a member forgets
  their password, an admin resets it from that screen. If the *only* admin
  forgets theirs, recovery means stopping the backend and deleting
  `backend/data/app.db` (wipes all accounts *and* requires going through
  setup again — it does not touch cleaned lead data, which lives in
  separate per-job Parquet files).

## Customizing

- **Add defense-sector domains:** edit `backend/blocklists/military_domains.txt`,
  one domain per line.
- **Add academic TLD patterns:** edit `EDU_PATTERNS` in `backend/cleaning.py`.
- **Change what counts as a duplicate:** the `dedupe_key` option (`email` /
  `domain` / `row`) is exposed in the upload form already.
- **Different category column name:** auto-detected as `platform` or
  `category`; pass `category_column=` explicitly to `clean_leads()` if your
  export uses something else.

## Project layout

```
backend/
  main.py              FastAPI app — upload, status, query, download endpoints
  cleaning.py           Core Polars cleaning pipeline (unit-testable standalone)
  jobs.py               Per-job filesystem storage + status tracking
  cleanup_old_jobs.py   TTL-based disk cleanup, run via cron
  blocklists/
    military_domains.txt
frontend/
  src/App.jsx            Upload → processing → results dashboard
  src/api.js              Thin fetch client
docker-compose.yml
```