# RFB List Processor

**Status:** <mark style="color:$primary;">In Development</mark>

**Repo:** [github.com/themailworks/rfb-processor](https://github.com/themailworks/rfb-processor/)

## What It Does

The RFB List Processor is a browser-based tool that automates the preparation of Regional Food Bank mailing lists. It replaces a manual three-stage workflow that previously required two people, three hand-offs, and 60 to 90 minutes per campaign. An employee drops in the raw files, enters the event date, and downloads Portal 1-ready CSVs in about a minute. No Excel, no scripts, no hand-off.

***

## The Problem It Solves

The original process had three stages passed between two employees:

**Stage 0 (Prep).** Employee 1 received the raw list files from the Regional Food Bank via Asana, inspected them, separated multi-branch files into one file per branch, and renamed everything to the internal naming convention before passing them along.

**Stage 1 (Scanline Generation).** Employee 2 opened a C# script in LINQPad on a local machine, pointed it at each file, and ran it to pull sequential IDs from the Mailworks SQL Server and generate scanline barcodes for every record. The output CSV was handed back to Employee 1.

**Stage 2 (Portal Formatting).** Employee 1 opened each scanline CSV in Excel and spent roughly 25 minutes per file manually restructuring columns, splitting name and address fields, performing "surgery" on long organization names, adding reserve and campaign fields, normalizing ZIPs, and saving the final CSV for Portal 1 upload.

The app collapses all three stages into one self-service flow in the browser.

***

## How It Works

The interface is a three-card flow: **Drop, Confirm, Process.**

### Card 1: Drop

The employee drags in the raw files (house lists, acquisition lists) and enters the event date. The app accepts multiple files at once, handles XLSX and CSV, and immediately rejects any file that already contains scanline or KeyCodeListId columns so nothing gets double-processed.

### Card 2: Confirm

The app detects each file's type (house vs. acquisition vs. volunteer-style acquisition), splits multi-branch files into separate jobs, and suggests filename tags. The employee adds the Creative name and confirms the event date. Each job name updates live to preview the exact filename that will be downloaded.

### Card 3: Process

With one click, the app runs three stages for every job:

**Step 0 (Normalization).** `src/normalizer.ts` detects the source type, maps columns to a canonical layout, and splits multi-branch files into one output per branch. Files that look already-processed (two or more of `scanline`, `KeyCodeListId`, `Reserve17/18/19` present) are refused up front.

**Step 1 (Scanline Generation).** `src/processor.ts` produces 26-character scanline barcodes with Luhn check digits (verified byte-for-byte against the original C# output), reserves sequential IDs from the database, and silently auto-fixes common data issues:

* **ZIPs:** pads leading zeros (`1234` becomes `01234`), inserts the missing dash on 9-digit mashed ZIP+4 codes (`121382703` becomes `12138-2703`), strips stray non-digit characters.
* **Duplicates:** rows with the same Constituent ID are deduplicated, keeping the first occurrence.
* **Undeliverable rows:** rows missing an address, ZIP, or city/state are dropped.

**Step 2 (Portal Formatting).** `src/portalFormatter.ts` transforms the Step 1 output into the 21-column Portal 1 format:

* Restructures columns, splits CityState into separate fields, strips ZIPs to 5 digits.
* **Organization names:** `FirstName` becomes "Our Friends at," `LastName` becomes the org name. For names longer than Portal 1's 50-character limit, the app performs the same "surgery" a manager would do by hand, splitting the name at a natural break (`-`, `,` , `(dba`) and prepending "Our Friends at " to the first half, leaving the second half in `LastName`.
* **Individual names:** `PrimaryAddressee` is split on the first space into FirstName / LastName. " and " becomes " & " only when keeping "and" would push LastName past the 50-character limit.
* Populates reserve fields (KeyCodeListId, sequential row number, ScanLine, PrimarySalutation).
* Adds campaign fields (Store, Creative, EventDate).

Downloads land as a per-job card. The employee can download each job individually or grab the whole set as a single ZIP. Validation warnings and errors expand inline only when something truly needs attention. Auto-fixes happen silently.

***

## Tech Stack

| Component    | Technology                                                                                   |
| ------------ | -------------------------------------------------------------------------------------------- |
| **Backend**  | Node 22, Fastify 5, TypeScript, better-sqlite3, mssql                                        |
| **Frontend** | Vanilla HTML/CSS/JS with Inter (stand-in for Proxima Nova)                                   |
| **Branding** | Mailworks dark navy (#071E30), indicia pink (#E81F76), turquoise (#44BDA9), yellow (#F5C542) |
| **Storage**  | SQLite (better-sqlite3, WAL mode) for session state; MS SQL Server for sequence reservation  |

***

## What Happens Behind the Scenes

The app is a Fastify server exposing a small endpoint set. A typical campaign moves through them in order:

* **`POST /api/step0`** accepts a multipart upload of the raw files. Runs Step 0 normalization, detects source type, analyzes branch columns, and writes a new session plus one job per output file into SQLite. Returns suggested filenames and auto-split decisions to the browser.
* **`GET /api/sessions/:id`** rehydrates the Confirm card from the session, so an employee can close the tab and come back without losing progress.
* **`POST /api/process`** runs Step 1 (scanline) and Step 2 (Portal formatting) for every job in the session using the employee's Creative/event date inputs. Persists the final CSV bytes per job.
* **`GET /api/download/:jobId`** streams one job's final CSV.
* **`GET /api/download_all/:sessionId`** streams all final CSVs in a single ZIP.

Session and job state live in a local SQLite database (`rfb_sessions.db`). Pending sessions older than an hour are purged on boot. Uploads are capped at 100 MB per request and 10 files at a time.

***

## Database Dependency

Every processed file requires a SQL Server connection. The Mailworks `MailWorksInventory.dbo.GetNextIntelligentBarCode` stored procedure is called on two serial names:

* **`FoodBankDonorId`** is reserved once per file to assemble the `KeyCodeListId` (`yy` + ISO-week of the in-home date + the reserved list ID, left-padded to 8 characters). Both house lists and acquisition lists need this.
* **`FoodBankAcquisitionId`** is reserved as a single block sized to the file's row count, used to populate `Reserve18` on acquisition lists. House lists skip this step because their individual IDs come from the upload's Constituent ID column.

The `/api/process` endpoint returns `503 Service Unavailable` if neither `MAILWORKS_CONN_STRING` nor `MOCK_DB` is set. A status pill in the header reflects connection state: turquoise when connected, yellow when offline. For local development, `MOCK_DB=1` swaps the stored procedure for an in-memory counter.

***

## In-Home Date Derivation

The employee enters the event date. The app derives the in-home date as the **first Wednesday strictly after** the event (if the event itself is a Wednesday, the app jumps forward a full week). That in-home date drives two things:

* The `MMDD` tag baked into the output filename.
* The ISO-week component of the `KeyCodeListId` preamble.

***

## Project Files

| File                     | Purpose                                                            |
| ------------------------ | ------------------------------------------------------------------ |
| `src/server.ts`          | Fastify app: session, upload, process, and download endpoints      |
| `src/normalizer.ts`      | Step 0: source detection, canonicalization, multi-branch splitting |
| `src/processor.ts`       | Step 1: scanline generation, auto-fixes, validation                |
| `src/portalFormatter.ts` | Step 2: Portal 1 formatting, column restructuring, name transforms |
| `src/scanline.ts`        | Luhn check digit calculation and scanline assembly                 |
| `src/sequenceDb.ts`      | SQL Server sequence reservation (`GetNextIntelligentBarCode`)      |
| `src/store.ts`           | Per-session state persistence (SQLite, WAL mode)                   |
| `src/fileIo.ts`          | XLSX / CSV parsing and writing                                     |
| `src/ihDate.ts`          | In-home date derivation and ISO-week key-code preamble             |
| `static/index.html`      | Frontend UI                                                        |
| `tests/unit.test.ts`     | Unit tests covering scanlines, auto-fixes, formatters, and helpers |

***

## Key Design Decisions

* **Silent auto-fixes:** ZIP normalization, deduping, undeliverable-row removal, and long org-name surgery happen invisibly. The UI only surfaces a pill or disclosure when there is an actual error that needs attention.
* **Long org-name surgery matches the human convention:** Rather than truncate an overlong organization name, the app splits it exactly where a manager would by hand, at a dash, comma, or "dba," and uses FirstName as overflow space (`Our Friends at <first-half>`).
* **Ampersand replacement is length-based:** " and " to " & " only fires for individual records where keeping "and" would push the LastName past the 50-character limit. Short individual names and organization names are left alone.
* **Deterministic filenames:** Output filenames are assembled from fixed parts (branch, kind, in-home date) with no user override. This keeps downstream naming conventions consistent across campaigns.
* **Campaign fields are entered once per job:** Creative name and event date appear on the Confirm card; the filename preview updates live as they change.
* **No manual sequence numbers:** House lists derive individual IDs from the upload; acquisition lists pull a block from the database automatically. The KeyCodeListId is always reserved from the database. The employee never enters a sequence number.
* **Already-processed files are rejected up front:** If an upload already contains scanline or KeyCodeListId columns, the app refuses it with a clear message so no one accidentally double-processes a file.
* **Sessions survive a tab close:** Session and job state live in SQLite with a 1-hour TTL on pending sessions, so closing the browser mid-campaign does not lose work.

***

## What's Not Done Yet

* **Production SQL Server wiring:** Requires the final connection string and a host on the office network. The app runs with `MOCK_DB=1` locally for development.
* **Asana integration:** Campaign fields (Creative, EventDate) are entered manually. If added as custom fields on the Asana task, the app could pull them automatically.
* **Adobe Fonts:** The UI uses Inter as a stand-in until the Typekit project ID is available for Proxima Nova.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://knowledge.themailworks.com/apps/rfb-list-processor.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.