Migrating a publisher and $5.5M of sales history

The problem

A publisher was moving onto Perfect Bound. Two things had to come across: their catalog metadata, which arrived as CSV, and their royalty history, which lived in the publisher’s previous royalty system, MetaComet. The existing royalty importer would have created a second copy of a catalog that was already on the platform.

What I built

The catalog came first, in October 2025. I wrote a CSV import adapter for their metadata. It handles two CSV shapes, author names written “Last, First”, free-text trim sizes (“W x L x D”), subject descriptions mapped to BISAC codes, and Google Drive share links converted into direct image URLs for cover import. I built a similar adapter for a second publisher.

I fixed three bugs in the ONIX parser in one refactor. Prices were overwritten instead of accumulated. Pack quantity was unreachable whenever a product had more than one supply detail. The audience loop always read the first entry instead of the current one. Page count also no longer depends on the order of the extent entries.

The royalty history followed, from July to October 2026. I built a “match existing” mode that imports agreements, statement balances, life-to-date sales and sales history onto the catalog already on the platform. Titles are matched by ISBN. Authors are matched by a fuzzy name key that tolerates differences in order, punctuation, accents and middle names. Missing authors can optionally be created as royalties-only contributors.Chose:Match onto the existing catalog. Rejected:The existing royalty importer, which would have duplicated it.

Balances come in as prior-balance ledger entries, and rate rows are scoped to the print formats each title actually has. Some imported discount settings turned out to be inert; that fix is in the royalty statements chapter.

Reconciliation is built in. Skipped rows go to an annotated CSV, with counters for each step, and reserve balances this mode skips are reported. Along the way I fixed decimal values arriving as strings, open transactions leaked on skipped rows, and blank keys that caused cross-joins on malformed rows.Chose:Nothing is silently dropped.

A repair backfill writes per-step calculation methods onto agreements imported before that field existed. It never overwrites an existing value.Chose:Report conflicting source terms instead of guessing, so a person decides which one is right.

Imports had also been creating duplicate contributor rows, and 1,109 already existed. I moved the choice between title-level and edition-level authors into one pure function, shared by all seven import adapters, so new imports stop adding duplicates. Promoted-role selection is now deterministic: Author, then the most-used role, then alphabetical.Chose:One pure function for all seven adapters, so they can’t drift apart.

Outcome

The royalty import ran from July 8 to 29, 2026. MetaComet held 451 projects, one per book. Of those, 376 (83%) matched a title in the catalog, and the other 75 went to the reconciliation CSV: 74 had no matching ISBN and 1 was malformed. Several projects mapped to the same title, so the matches cover 336 titles. Perfect Bound creates one royalty agreement per author per title, which came to 385 agreements across those 336 titles and 228 authors. Of the authors, 99 were created as royalties-only contributors because their title matched but they weren’t on the platform yet. The other 129 or so matched existing records.

The import carried over 80 opening statement balances (−$73,458.43), 342 life-to-date sales totals ($8.08M) and 135,005 rows of sales history from May 2021 to October 2025 ($5.53M). As the import spec required, 2,889 DVD and streaming sales rows ($198K) were left out. The reconciliation CSV also received 48 statement-balance rows, totalling −$9,389.81 across 34 people. Imports no longer create new duplicate contributor rows, and they give the same result whatever the row order.

Stack

• TypeScript
• Node.js
• fast-xml-parser
• MySQL
• Amazon S3