seubert-claims/ondeck/docs/shape-historical-import.md

SHAPE Historical Data Import

Script: scripts/import-shape-historical.ts Last reviewed: April 2026

---

For the Team

What this script does

This script imports historical SHAPE task completion data from the team's SharePoint Excel files into Horizon. It is a one-time migration tool — not part of the ongoing sync.

Each team member has a folder on SharePoint at:

Claims/SHAPE Accounts/{MEMBER_FOLDER}/{Client}.xlsx

The script reads every client Excel file for each advocate, matches it to a client in Horizon, and records whether each SHAPE task was completed, marked N/A, or left blank.

Team member folders

SharePoint Folder	Advocate
CHRIS	Christine Gove
DAWN	Dawn Boland
Jeanne	Jeanne Strong
LUKE	Luke Billman
MIMI	Mimi Rawlings

Running the script

Always do a dry run first. The dry run reads SharePoint and the database but writes nothing.

# Dry run (safe — no changes made)
npx tsx scripts/import-shape-historical.ts

# Execute (writes to database)
npx tsx scripts/import-shape-historical.ts --execute

# Save report to a custom location
npx tsx scripts/import-shape-historical.ts --output=/home/user/import-report.txt

The report is always written to /tmp/shape-import-report.txt (or your --output path). Check it before running --execute.

What the report tells you

After running, the report shows:

Stat	Meaning
Files processed	Excel files successfully downloaded and parsed
Clients matched	Clients in the Excel that were found in Horizon
Clients unmatched	Clients that could not be matched — need manual review
Fuzzy matches	Clients matched by approximate name — review for correctness
Tasks updated	Existing tasks whose status was set to Completed or N/A
Tasks assigned	Tasks that had the advocate linked to them
Tasks created	Tasks from the Excel that didn't exist in Horizon yet
Duplicates deleted	Extra copies of the same task removed from the DB
Ad-hoc created	"Additional Services" rows and unmatched tasks saved as free-form tasks
Advocates assigned	Clients that had their Claims Advocate set from this import

What gets skipped

The script will not import a row if:

• The client name in the Excel cannot be matched to any Horizon client (logged as "unmatched")
• The file is named archive*, contains template, master workbook, or starts with all shape — these are intentionally excluded

Nothing else is filtered. Every task row in every matched client file is processed.

Does deduplication remove tasks from Excel?

No. Deduplication only removes extra copies of the same task that already exist in the database (e.g. from a previous partial run of the script). It does not skip or discard any rows from the Excel files.

Specifically: if the database already has two identical tasks for the same client and template within ±5 days of the same due date, the older one is kept and the newer duplicate is deleted. The Excel row is still processed normally against the surviving task.

What to do with unmatched clients

After running the dry run, check the "Unmatched Clients" section of the report. For each one:

1. Check if the client exists in Horizon under a different spelling
2. If yes — either rename the client in Horizon to match, or rename the Excel file
3. If no — create the client in Horizon first, then re-run the import

---

Technical Reference

Architecture overview

SharePoint (Graph API)
    └── Claims/SHAPE Accounts/
            ├── CHRIS/   → Christine Gove (userId: cmkm36yc90021y7vb9783nitl)
            ├── DAWN/    → Dawn Boland
            ├── Jeanne/  → Jeanne Strong
            ├── LUKE/    → Luke Billman
            └── MIMI/    → Mimi Rawlings

Each .xlsx file:
    ├── One or more client blocks per sheet
    │       ├── Client Name
    │       ├── Effective Date (Excel serial number)
    │       └── Task rows: [Task Name | Days After Renewal | Date Completed | Notes]
    └── "Additional Services Provided" section (ad-hoc tasks)

Processing pipeline

1. fixTemplates()          Fix known template name/offset discrepancies in DB
2. loadDbState()           Load SHAPE/Shape2 designations, templates, and clients
3. discoverFiles()         List Excel files from SharePoint via Graph API
4. processFile() × N       For each file:
     └── parseExcelFile()       Parse XLSX without external libraries (custom ZIP+XML)
           └── parseExcelBlocks()   Extract client blocks and task rows
     └── matchClient()          Match Excel client name → Horizon client
     └── processTaskRow() × N   For each task row:
           └── findMatchingTasks()  Look up existing task in DB (±5-day window)
           └── deduplication        Delete extras if >1 match
           └── update or create     Set status, completedAt, assign advocate
     └── createAdHocTask() × N  For "Additional Services" rows
5. printReport()           Write summary to stdout and output file

XLSX parsing

The script parses .xlsx files without any npm dependencies. .xlsx is a ZIP archive containing XML files. The parser:

1. Finds the End of Central Directory record to locate the ZIP central directory
2. Reads each file entry (stored or deflate-compressed via Node's zlib.inflateRawSync)
3. Parses xl/sharedstrings.xml to build a string table
4. Identifies the target sheet — prefers SHAPE2 > SHAPE > CLIENT tab names, falls back to sheet1
5. Parses the sheet XML into a (row, col) → string grid
6. Scans for Client Name: anchors and reads blocks downward

Client name matching (3 tiers)

1. Exact normalized match
   - Strip "the", "Inc", "LLC", "Corp", etc., lowercase, collapse whitespace
   - Direct map lookup

2. Contains match
   - One normalized name contains the other as a substring

3. Jaccard token similarity ≥ 0.65
   - Tokenize both names, compute intersection/union ratio
   - Only used if tiers 1 and 2 fail

Fuzzy matches (tiers 2–3) are listed in the report for manual review.

Template matching (3 tiers)

1. Exact normalized name (after alias substitution)
2. Contains match on normalized name
3. Days-offset fallback: approxOffset = -(365 - daysAfterRenewal), ±15-day window

Known aliases handled (TEMPLATE_NAME_ALIASES):

• request 125 day loss runs → request 120 day loss runs
• request 89 day loss runs → request 90 day loss runs
• claim review (six month) / claims review → claim review

Deduplication logic

findMatchingTasks(clientId, templateId, dueDate):

• Queries for tasks matching clientId + templateId with dueDate ±5 days
• Returns the oldest match as the canonical task
• Returns all additional matches as duplicateIds → these are hard-deleted

This only cleans up DB duplicates from previous partial runs. It never suppresses Excel rows.

Date completed interpretation

Excel cell value	Result
Empty / ?? / ?	NOT_STARTED
n/a / na	NA (with standard naReason)
Numeric Excel serial (40000–60000)	COMPLETED, date converted
Anything else	NOT_STARTED

Task creation rules

Situation	Action
Task exists in DB, status = NOT_STARTED, Excel says completed/NA	Update status, completedAt, assign advocate
Task exists in DB, already completed	Assign advocate only (never overwrite completion)
Task does not exist, Excel says completed/NA	Create task with completed status, assign advocate
Task does not exist, Excel says NOT_STARTED	Create task as NOT_STARTED, assign advocate
Template not matched, Excel says completed/NA	Create ad-hoc task: [Unmatched Task] {row name}
Template not matched, NOT_STARTED	Skip (no DB write)

Advocate assignment

• Advocate is set at the client level (claimsAdvocateId) only if the client has none yet
• Existing advocate assignments are never overwritten
• Advocate is also linked at the task level via TaskAssignment upsert

Template fixes run on each execution

Even in dry-run mode, these are applied to the DB on --execute:

• Request 125 day loss runs (offset -125) → renamed to Request 120 day loss runs, offset -120
• Templates with offset -89 → corrected to -90, names updated accordingly
• Missing Claim Review at -185 days (Shape designation) → created if absent

Environment variables required

DATABASE_URL          PostgreSQL connection string
AZURE_AD_TENANT_ID    Microsoft Entra tenant ID
AZURE_AD_CLIENT_ID    App registration client ID
AZURE_AD_CLIENT_SECRET App registration client secret

The Azure app registration needs Files.Read.All (or Sites.Read.All) on Microsoft Graph.

Re-running safely

The script is fully idempotent on --execute:

• Tasks are updated, not duplicated (±5-day deduplication window)
• Advocate assignments use upsert
• Ad-hoc tasks check for existing (clientId, title, isAdHoc=true) before creating
• Template fixes use updateMany with specific where conditions

Re-running after adding new clients or fixing name mismatches is safe.