Mapping the LEAP exports into the import boundary schemas

Where the exports come from

The team follows Clio's LEAP migration recipe for extraction: six report exports run from inside LEAP by a firm admin. The two staged here are the first two, run by George on 2026-08-14:

The four pending exports are all money-side, which the boundary catalogue defers deliberately. They should still be exported and archived while access is easy; they become reconciliation baselines, and the balances are snapshots at export time, not ledgers.

Two things the recipe does not do, so they stay open regardless of it: it never links contacts to matters, and it has no documents step.

What differs from the v2 run

The v2 mapping read a database dump: real primary keys, foreign keys, timestamps, soft-delete flags. These LEAP files are display-layer reports: no ids of any kind (not one GUID in either file), dates as dd/mm/yyyy strings, one of them a formatted report with a title block, 96 staff group-subheader rows interleaved with the data, and a totals footer. The transform's first stage exists purely to undo that formatting. Everything downstream is the same machinery as v2: the same boundary schemas, the same sort rules, the same verifier, byte-identical output on every run.

What one run produced

The run covers Woodstock's full book: 27,198 matters against 27,005 in the warehouse, statuses including Archived and Complete, instruction dates 2014 to the export day.

The verifier reports all 27,198 matters as having no client-side participant. That is expected here, more so than in v2: LEAP clients are not platform users, so they land as matter contacts, not participants.

The mapping, source by source

External refs are the source's own identifier where one exists. LEAP's reports carry none for contacts, which is the root of two of the three gaps below.

Matter List → matter

Matter No comes in three formats: 69% modern 99/99999, 30% 99/9999/AA where the suffix is staff initials (verified against the staff columns on the same rows, correcting an earlier belief that these were client surnames), and 191 legacy AAA999/999 references whose prefix genuinely is a client-surname fragment.

Matter List staff columns → identity + matter_participant

The export identifies staff only by initials. One identity stub is staged per distinct value (302), with external_ref staff:<initials> and the initials as the name; real names join when the firm's staff directory arrives. Participants: Staff Resp. becomes the lead lawyer, Staff Act. and Staff Assist. become non-lead lawyers, duplicates on the same row deduplicated, the placeholder "Unassigned" value skipped. The Credit column is the introducing fee earner, which has no boundary shape: no participant row is emitted (27,009 counted), though the identity stub survives.

Client Details → contact, contact_method, contact_address

Every contact is staged as an INDIVIDUAL because the export has no individual-versus-company discriminator. Clio's recipe says to select all card types when exporting; whether this file includes companies needs George's confirmation.

The join → matter_contact

The two files share no key, so the only bridge is the client's name, normalised (lowercase, alphanumeric only). A link is emitted only when the normalised name maps to exactly one contact row:

65% of matters get a client link. The remaining 35% is not a mapping problem to solve with cleverer fuzzy matching; it is the missing-client-id problem wearing a costume.

The status collapse, draft

The collapse answers one narrow question: which v3 lifecycle state a matter is in. It deliberately does not try to carry what LEAP's statuses mostly are, which is practice-area progress ("Sent To Land Registry" is a conveyancing milestone, not a lifecycle state). The proposal is to keep both meanings:

• matter_status collapses to v3's four lifecycle states, per the table below.
• The original LEAP status is preserved on each matter as a firm-scoped custom field (working name "Internal Status"; the business picks the real one). v3 already has the machinery (firm-scoped field definitions, applied at matter creation), and the boundary catalogue deferred custom fields "until the first source where they carry the data". LEAP is that source, so this asks for that schema now. Matter Type (97 values, currently dropped) is the same shape and should ride along.

One field or one per practice area? The data answers this. Cross-tabbing Status against Matter Type across the full book: the 8 lifecycle statuses (In Progress, Archived, Complete, Not Proceeding and variants) span every practice area and cover 95% of matters, while the 50 milestone statuses are practice-area vocabulary in practice, with 39 of 50 appearing in exactly one area family (Contract Pack Sent, Exchanged and Searches Complete are purely conveyancing; Trial, Letter Before Action Sent and Judgement Date purely litigation). The vocabularies never collide, so a single "Internal Status" field is the proposed import shape; whether v3 later offers per-matter-type dropdown vocabularies is a product choice this import does not preempt. Educated guess from the export's own co-occurrence; the business validates both the field name and the single-field choice. If API access lands, LEAP's schema service (per-matter-type field definitions and critical-date schemes) answers the fields-to-practice-areas question authoritatively.

Preserving the label also lowers the stakes of every contentious row below: a matter collapsed to the wrong lifecycle state still shows its true LEAP status. Until the custom-field schema exists the transform cannot stage the labels, but nothing is lost: the transform is deterministic, so one re-run stages them the day the schema lands.

Collapse rule: only unambiguously terminal states close a matter; Not Proceeding cancels; every workflow milestone stays ACTIVE, so nothing closes without the firm's say-so. LEAP's ?? (X) display variants collapse like their base status.

Totals: COMPLETED 16,536 · ACTIVE 9,864 · CANCELLED 798 (OPENING unused).

The rows worth arguing about, all kept ACTIVE in the draft: the Registered family (registration finished, arguably COMPLETED), Will Executed (the deliverable exists), and Limitation Period Expired / Mortgage Expired (arguably dead, so CANCELLED). The firm decides; the draft only proposes.

Exclusions and fix-ups, the honest ledger

Every dropped value is counted with a reason; nothing is silently skipped. The largest classes:

Fix-ups: completed_at set to instruction date (16,536), single-token names kept as first name only (86), unambiguous two-number phone cells split (4).

In coverage-loop terms: 100% of both files' source rows are resolved: 27,198 of 27,300 physical Matter List rows mapped and 102 excluded as report furniture; 26,027 of 26,028 Client Details rows mapped and 1 header excluded. This is the upstream number for these two exports; what LEAP holds beyond them (documents, money, key dates) is a different denominator that grows as more exports arrive.

Decisions needing sign-off

1. The status collapse table, above, together with the custom-field approach that preserves the LEAP label. The contentious rows are listed; preserving the label makes them cheap to get slightly wrong and cheap to revisit.
2. Contact references are unstable. cd:<row index> breaks on any re-export. The fix is an export carrying LEAP's internal client id; until then, no real import run should use these contacts.
3. completed_at is fabricated for 16,536 matters. The export has no completion date and the boundary requires one for COMPLETED matters. Needs either the firm's acceptance or, better, an export with completion dates.
4. All contacts staged as individuals. No company discriminator exists in the export.
5. Identity stubs carry initials as names. The identity contract has no free-text field, so staff: in the external ref is the only stub marker. The firm's staff directory turns these into people.
6. The introducer relation is dropped. Credit initials keep their identity stub; the relation itself has no boundary shape and is counted, not staged.
7. Custom-field candidates, pending the schema: the original LEAP status label and Matter Type (97 values) are not staged yet but are earmarked for the custom-field boundary schema this page asks for; a deterministic re-run stages them once it exists. Dropped outright: marketing consent (99.8% "No"), Home/Work/Fax phones, PO Box and DX columns. DOB and Short Name survive only inside contact.notes.

What to ask LEAP for next

All three gaps have the same shape: they are export problems, not mapping problems.

1. An export carrying LEAP's internal client id (or a matters-per-client report). Fixes the unstable contact refs and replaces name-matching for the 35% of matters without a client link.
2. Completion dates, so 16,536 matters stop carrying a fabricated one.
3. Confirmation of card types in the Client Detail export (people only, or companies too), and the staff directory to give the 302 identity stubs real names.

And one ask that is ours, not LEAP's: a matter custom-field boundary schema in the catalogue, so the LEAP status labels and Matter Type can be staged. The catalogue deferred custom fields until a source needed them; that source has arrived.

Files and how to run it

The transform lives in this doc's folder, in the style of the v2 transform: transform/parse.py (stage 1: undo the report formatting) and transform/transform-leap.mjs (stage 2: mapping, validation and sorted writes, importing boundary-schemas.mjs from the v2 transform so there is exactly one copy of the contracts). Same inputs, byte-identical outputs, proven by manifest comparison across independent runs.

uv run --with openpyxl python transform/parse.py <dir-with-xlsx> <intermediate-dir>
node transform/transform-leap.mjs <intermediate-dir> <out-dir>
node ../v2-boundary-mapping-2026-08-12/transform/verify.mjs <out-dir>

The xlsx inputs and every output file contain real client data and live only outside the repositories. This page and the transform code carry none of it.