Switchboard — an AI data-migration copilot

Legacy export in → clean Ledgerline import out, with a consultant in the loop.

The business problem

Every B2B software implementation starts the same way: the customer hands over an export from "the old system" — a Salesforce dump with Subscription_Status__c columns, a mainframe extract with fields named STS and MRR_CENTS, or a spreadsheet a sales team kept by hand with $1,250/mo in the money column and three different date formats. Implementation teams burn days to weeks per onboarding turning that file into the new product's schema: eyeballing columns, writing one-off scripts, emailing the customer "what does SUSP mean?", and re-running the import until it stops erroring.

Commercial import tools automate the easy 30% — fuzzy field matching with a confidence score. The expensive 70% is the consulting around it: asking the right clarifying questions, deciding what to do with bad rows, and producing a handoff document the customer's exec sponsor will actually sign off on. Switchboard automates that layer.

What it does

Switchboard migrates messy legacy exports into Ledgerline, a (fictional) subscription-revenue CRM with a 15-field account schema. The flow mirrors how a senior implementation consultant works an onboarding:

1. Intake — tolerant parsing. Sniffs encodings (UTF-8 / latin-1 / cp1250), detects delimiters (, ; | tab) by per-line consistency scoring, finds the real header under title banners, and drops blank rows/columns. Every repair is surfaced as a parser warning.
2. Routing — Claude proposes a mapping for every column with a confidence score and its reasoning, based on observed values (formats, cardinality, uniqueness) — not string similarity. CNTCT_NM containing "CHEN, OMAR" routes to the name fields with a split transform; MRR_CENTS containing 149900 routes to mrr_usd via cents→dollars. The mapping renders as a wiring diagram with green/amber/red confidence lamps, and any route can be manually overridden.
3. Interlock — the differentiator. Low-confidence and ambiguous mappings become the clarifying questions a consultant would ask, citing real values and row counts: "'STS' contains 'ACT' (21 rows), 'CLSD' (10), 'SUSP' (5) and 'PND' (5)... should 'SUSP' import as paused, or do these codes mean something else in your system?" Every answer option carries a machine-actionable effect that updates the mapping deterministically.
4. Inspection — a deterministic pandas audit runs against the mapped data: missing required fields, malformed emails, duplicate keys, enum violations, unparseable dates, renewal-before-signup, negative MRR, invalid seat counts. Claude explains each issue class in plain business language and recommends a resolution policy (auto-fix / quarantine / migrate-as-is / drop duplicates); the operator decides.
5. Dispatch — the deliverable: an executive summary with headline numbers, the final mapping table, every operator decision recorded verbatim, per-issue resolutions, and a Ledgerline-specific rollback plan — written like a consultant's handoff doc, not a log file. Downloadable as migrated.csv, quarantine.csv, and report.md.

Measured on the bundled samples: a 14-column mainframe dump (41 rows, cp1250, pipe-delimited, banner rows) was mapped, clarified with 4 consultant questions, audited across 6 defect classes, and dispatched — 36 rows migrated, 4 quarantined with reasons, 1 duplicate dropped — in under 3 minutes end to end.

Design principle: the AI proposes, deterministic code executes

Nothing the model says touches the data directly.

• Structured outputs everywhere. Every Claude call uses client.messages.parse() (model: claude-opus-4-8) with a Pydantic schema — mapping proposals, clarifying questions, audit assessments, and the report are all validated before they reach the UI. Grammar limits (no list-length or numeric constraints) are handled with field_validator clamps and explicit quantity rules in field descriptions.
• A vetted transform catalog. The model selects transform names (split_full_name, parse_currency, cents_to_dollars, map_values, ...); only audited Python executes them. The AI never writes executable logic.
• Deterministic audit. Every validation rule is plain pandas. The AI explains findings and recommends policies; it never decides what counts as a violation.
• Split calls, not big ones. Column mapping runs as parallel chunked calls (ThreadPoolExecutor, ≤8 columns each) to keep each structured-output grammar small; question generation runs sequentially after mapping because it needs the full picture. Same pattern for audit explanations.

Architecture

frontend/  React 18 + TypeScript + Vite, hand-rolled CSS (no frameworks)
           └─ mid-century engineering-schematic UI: routing diagram with SVG
              wires + confidence lamps, interlock cards, defect register
backend/   FastAPI + pandas
           ├─ parsing.py    tolerant ingestion (encoding/delimiter/header heuristics)
           ├─ ai.py         all Claude calls — messages.parse() + Pydantic only
           ├─ transforms.py deterministic transform catalog
           ├─ audit.py      pandas validation rules + policy execution
           └─ target_schema.py  the Ledgerline contract (drives prompts + audit)
sample_data/  3 committed demo exports (generator included) — the demo runs
              fully offline except the Anthropic API calls

Run it

# backend
cd backend
python3 -m venv .venv && .venv/bin/pip install -r requirements.txt
cp .env.example .env   # add your ANTHROPIC_API_KEY
.venv/bin/uvicorn app.main:app --port 8000

# frontend (second terminal)
cd frontend
npm install
npm run dev            # http://localhost:5173

Click one of the three bundled specimens on the intake panel:

Sample	Legacy flavor	What it exercises
vantage_crm_export.csv	Old Salesforce-style CRM	Verbose API names, ISO timestamps, exact + keyed duplicates, negative MRR
SYSDUMP_ACCT_0347.csv	Homegrown mainframe dump	cp1250/latin-1, pipe delimiter, banner rows, STS/PLN_CD code mapping, cents, YYYYMMDD dates, LAST, FIRST names
renewals_tracker_2024.xlsx	Hand-kept spreadsheet	Header under title rows, Name <email> merged column, four date formats, $1,250/mo money, emoji statuses, missing IDs

Why these decisions

• Fictional target product — a real integration would date the project and add credentials; a well-specified fake schema demonstrates the same skill and demos offline.
• In-memory sessions — the deliverable is the workflow, not a persistence layer.
• Clarifying questions over silent guesses — a wrong enum mapping (SUSP → churned when it meant paused) corrupts every downstream revenue report. The cost of one question is seconds; the cost of a silent wrong guess is a support escalation. The question/answer log also becomes the audit trail in the final report.
• Quarantine over rejection — implementation teams never get to say "your file failed." Bad rows ship in a review file with named reasons, and the migration proceeds.

---

Companion project: ColdOpen — the pre-sales twin (AI demo engine). Switchboard is the post-sales half of the same implementation-consultant toolkit.