Wormhole

Wormhole was the tool that moved World Wide Web Hosting’s acquired brands off their legacy billing systems and into Helix, the in-house platform that replaced them. Three brands — Bluefur, AptHost, and Kahuna Host — each running its own WHMCS installation, each with its own products, currencies, servers, and ten years of accumulated data entry. Roughly ten thousand customers in total. All of them moved, and none of them had to do anything about it beyond a maintenance window and, for some, a form.

The first commit lands on January 15, 2013 and says hi there. The last lands on July 6, 2015 and bumps Rails to 4.1.12. Two and a half years between them, a team of ten, and three brands moved — each in a single announced weekend.

Migrating a billing system is not a hard engineering problem in the way that building one is. There is no clever architecture to find. What there is instead is a very long list of ways to be quietly wrong, and a failure mode where you find out about each of them from a customer. Everything below is about narrowing that list.

The thing that makes it different

An application migration can be rolled back. You restore the database, you point DNS at the old thing, and the afternoon is a write-off but nothing is permanently different.

A billing migration cannot, because the moment the new system starts charging cards, the outside world has changed. There is a transaction at a payment processor, a line on a bank statement, and a customer who has an opinion about it. Double-charge ten thousand people and you do not have a bug, you have a refund run, a chargeback wave, and a conversation with your merchant bank.

So the design constraint was not “make the import fast” or “make the import elegant.” It was: at every point, be able to say exactly what moved, where it came from, and what it became — and be able to stop.

One JSON file per record

Wormhole ran in two halves that never spoke to each other.

Export ran on the legacy billing server, connected to its MySQL database, and wrote one JSON file per record: accounts/8412.json, products/17.json, domains/993.json. A record’s children came with it — an account’s file carried its contacts, services, domain services, invoices, notes, balance entries, and credit card, already assembled.

Import ran on the Helix server, read those files, and created Postgres records through the HelixCore Rails engine — the same models, validations, and callbacks the live application used. Not raw SQL. The import went in the front door.

The transfer between them was files. Early on those files were GPG symmetric-encrypted before leaving the legacy box and decrypted on arrival; that came out in July 2014 once the transfer path itself was secured and the encryption was buying obscurity rather than security.

A file per record is an obviously inefficient way to move ten thousand accounts, and it is the single best decision in the tool. Everything good downstream is a consequence of it.

You can look at one. There is a 12-line script in contrib/jj whose entire job is to pretty- print a record so a human can read it. When an import failed, the failure was one file, on disk, with a number in its name, and you could open it.

You can re-run one. --ids, --ids-file, --limit, and --regenerate all exist because “export account 8412 again” is a thing you need constantly and should never require re-running a batch.

You can resume. --continue diffs the source table against the filenames already on disk and exports only what’s missing. An export interrupted at hour six restarts at hour six.

And you can tell what’s done, because successful imports move. Each file is imported inside a database transaction; on success it is renamed into an imported/ subdirectory alongside its siblings. The remaining files in the directory are, by definition, the work left. There is no progress table to consult and nothing to get out of sync with the filesystem, because the filesystem is the progress table.

The ledger

Every record Wormhole created wrote a WormholeLog row: the Helix model name, the new Helix ID, and the legacy ID it came from. It is an after_create hook on a concern mixed into the Helix models being imported, so it is not something an import handler can forget to do.

That table did more work than anything else in the project.

It made imports idempotent in the way that matters. Handlers resolve foreign keys by asking the log — “what is Helix’s ID for WHMCS service 4471?” — so an account imported after its products already exists in a graph, not as an orphan.

It made the migration auditable in both directions. Helix’s admin surfaced a record’s legacy ID and, through a legacy_billing_url on the brand, linked straight back to the record in the old system. Running the other way, the account closer wrote a comment into the legacy system listing every child record it had moved with a link to its new home in Helix. Support staff working a ticket during the transition could get from either side to the other in one click.

And it caught a bug that would have been very expensive. When the second WHMCS brand arrived in 2014, legacy IDs stopped being unique — Bluefur’s client 1 and AptHost’s client 1 are different people who share a primary key. On October 1, 2014, WormholeLog grew a brand_id and every lookup got scoped by it. That is a one-line schema change and a category of cross-brand data leak that never happened.

What we refused to carry

The most consequential decisions in the project were exclusions, and most of them are one method or one line of YAML.

Accounts with pending services were skipped entirely, and the exclusion was logged with the account’s active and suspended service counts so someone could look at it. An account mid-provision is a distributed transaction in flight across two systems; there is no correct way to move one, so you move it tomorrow instead.

Only active clients exported. Clients with unpaid invoices were skipped for a period. Old invoices did not come across; only recent ones, and those arrived with a due date set so Helix would pick them up in its normal billing run rather than needing a special case. Products and addons imported inactive, so nothing in the migrated catalog was sellable until a human turned it on.

Per-brand exclusions lived in config/brands.yml with the reason written next to them:

exclude_product_ids:
  - 94  # Custom Plan - Custom Packages
  - 137 # APT-DEDI 2 (Hidden) - Dedicated Servers
  - 104 # Power1000 - Virtual Private Servers (no active services)

AptHost got a raw SQL fragment in its config to drop one-time SSL services older than a year — services whose nextduedate was 0000-00-00, which is MySQL for “this was never going to renew and nobody cleaned it up.”

None of this is interesting code. All of it is the difference between a migration that lands ten thousand accounts and a migration that lands ten thousand accounts plus four thousand dead rows that support will be explaining for a year.

Encoding, which took fifteen months

This is the part that actually fought back.

WHMCS is PHP, and old WHMCS installations are the classic PHP-on-MySQL encoding situation: columns declared latin1, a latin1 client connection, and UTF-8 bytes written straight through it. The bytes in the column are UTF-8. The database is convinced they are Latin-1. Nothing in the stack ever converts, so PHP round-trips them perfectly and the application never notices it is sitting on a lie.

Ruby notices immediately. mysql2 tags every string with the connection’s encoding, so a customer named Nuñez arrives labelled ISO-8859-1 or ASCII-8BIT while holding UTF-8 bytes, and from there every comparison, every JSON encode, and every Postgres insert is wrong in a different way.

The fix landed September 3, 2014 as a force_encoding class attribute — off by default, turned on for the three models that carry human-entered text — and an override on read_attribute:

def read_attribute(name)
  out = super

  if force_encoding && out.respond_to?(:force_encoding)
    CGI.unescapeHTML out.force_encoding("UTF-8")
  else
    out
  end
end

Two separate problems in one line. force_encoding relabels bytes without touching them, which is correct here precisely because the bytes were already UTF-8 and only the label was wrong — encode would have transcoded perfectly good data into mojibake. And CGI.unescapeHTML, because WHMCS also HTML-escapes on write, so an apostrophe in a surname is sitting in the column as '. A migration that carried those across would have put literal HTML entities in ten thousand customers’ names, in every invoice and every email, and nobody would have caught it before a customer did.

Then the exceptions arrived, one production surprise at a time.

October 2, 2014. cardnum and expdate are not text. WHMCS stores them via MySQL’s AES_ENCRYPT, so they are ciphertext, and relabelling ciphertext as UTF-8 produces “invalid byte sequence” the moment anything looks at it. Those two columns got skipped by name.

October 30, 2014. Service passwords turned out to be encrypted too — with WHMCS’s own scheme this time, not AES — which meant they had been exporting as ciphertext and importing as a customer’s cPanel password. Fixed by decrypting on read.

November 5, 2014. ENV['LANG'] = 'en_US.UTF-8', set at library load. Ruby takes its default external encoding from the locale, so until that line existed the export produced different bytes depending on whose shell it ran in.

December 3, 2014. The export files themselves got pinned: File.open(path, "w:ISO-8859-1"). Same day the Kahuna Host config was added.

That last one is an empirical fix and should be read as one. It was arrived at by exporting, looking at the bytes, and changing the mode string until the round trip held — not from a theory about the pipeline. It worked, it shipped, and it was the right call under a cutover date. It also has a tail, which is in the last section.

The pattern across all fifteen months is worth naming. Every one of these was found by running the real export against the real database and reading the output, and not one of them would have been caught by a test suite built on data we generated ourselves. Encoding bugs do not live in your code. They live in the twelve years of other people’s software that wrote the rows.

Free text, ten years of it

A billing database that has been running since 2003 has a column called country, and it is a text field, and people have typed into it.

config/country-map.yml is 430 lines of what they typed. usa, us united states, estados unidos, united states of america, and a bare : united states with a leading colon somebody pasted in. united kindom, great britain, england, scotland, wales, wales eu. austrailia. And, mapped to US: california.

The mapper tries the value as an ISO code, then as a country name, then against that file, and falls through to returning the original string if all three miss. States get the same treatment — matched against the country’s state list by full name or abbreviation, case- and period-insensitive, so Fla., florida, and FL all land on FL.

Around it, a long tail of the same kind of work: zip codes upcased and stripped, domain names downcased and stripped of spaces, 0000-00-00 dates handled, prices of -1.00 skipped, <br> tags pulled out of product descriptions, (Legacy) appended to product names so imported catalog entries didn’t collide with real ones, and float arithmetic replaced with integer cents.

The governing rule is in a comment on the phone number method:

If the number is not valid, we return a blank string. We’ll ask the user to supply this when they sign into Backstage for the first time.

Don’t guess, don’t drop the customer, don’t fabricate a value that looks real. Carry the gap forward as a gap, and put it in front of the one person who actually knows the answer. That principle is what the next section is mostly about.

The gaps you only find by migrating

Helix was written in 2011 for customers who signed up through Helix. Every field it required, it required because its own signup form collected it. Pointing a decade of someone else’s data at it found the seams fast, and closing them meant work in Helix itself rather than fudging it in the importer.

Addresses. Helix validated country, state, postcode format, and phone number against a real country table. WHMCS validated approximately nothing. A meaningful number of legacy accounts simply could not be saved. On March 5, 2015, legacy_address was added as a boolean on accounts and customers, suspending address validation for records carrying it; the importer sets it only when the record is genuinely invalid. The same day, the customer portal learned to require an address update on next login for those accounts, clear the flag when the form is submitted, and block affiliate payouts until it is. Bad data came across as bad data, flagged, with a path to being fixed by the person who knows their own address.

Credit cards. Helix required a CVV on every stored card. No legacy system had one, because storing CVVs is against the card network rules — the old systems were right and Helix’s assumption was wrong for this case. CreditCard.ignore_cvv! was added in July 2014 for import runs, with require_cvv! alongside it to put the guard back.

Cards then got their own path entirely. In March 2015 card import moved to a dedicated Sidekiq queue: Wormhole AES-encrypts the number, hands it to CreditCardImportWorker, and the worker decrypts and stores it. The card number is never a plain string in the import process, never in a log line, and never in the JSON export beyond its own encrypted field.

Email. Every import path sets skip_account_setup_email. Ten thousand welcome emails is not a migration, it is an incident. What did go out was written on purpose: a separate import notification template, added December 2013, sent deliberately from an is_imported concern.

Permissions. WHMCS’s contact permission model didn’t map cleanly onto Helix’s. Rather than picking a mapping and hoping, Helix got Account#imported? and the customer portal got a modal that walked imported customers through reviewing their contacts’ permissions on first login.

Every one of these is the same shape: the migration found a place where the new system had assumed something the old world didn’t guarantee, and the fix went into the new system rather than into a workaround in the importer. A migration is an audit of your own model’s assumptions, and it is a much cheaper one than the alternative.

Testing against a database you don’t own

You cannot spin up a WHMCS instance in CI, and you should not be running your test suite against a production billing database.

So the schemas came with. db/whmcs_structure.sql is 94 tables and db/synco_structure.sql is 104, both checked into the repository, both loaded into a scratch MySQL database at test time. On top of them sit FactoryGirl factories for the legacy tables — tblclients, tblhosting, tblinvoices — so a spec could build a WHMCS client with three services and two contacts and assert on the JSON that came out the other end.

That is why the test suite is nearly as large as the library it tests, and why encoding fixes could ship with a regression test rather than a hope. The decryptor spec runs against a real encrypted string lifted from a WHMCS test client and asserts it decrypts to Super Duper Secret. When the WHMCS encryption key was misconfigured, the failure mode was silent garbage; a July 2014 commit made it raise instead, with specs for both the blank key and the default key.

Travis ran the whole thing on every pull request, which is how two and a half years of changes landed in a tool that had one job and no room to be wrong at it.

Three brands, three windows

Each brand moved as a single announced maintenance window rather than a rolling batch — one weekend, billing frozen on both sides, the export and import run end to end, then reconcile, then open.

The lead-up is visible in the commit history. Bluefur, Helix brand 2, is seeded into Helix on August 23, 2013; its customer-facing theme lands in November; the brand ships across all three Helix applications on December 10. AptHost, brand 5, is created August 26, 2014, with its product-type mapping, locations, and theme landing between September 8 and 15. Kahuna Host, brand 6, gets its Wormhole config on December 3, 2014 and its Helix seeds on December 19.

What made the windows boring was the rehearsal. Each brand’s migration was run start to finish into a staging Helix, repeatedly, until it came out clean — there is a regenerate-whmcs script in the repo whose whole job is to wipe the Helix database, re-run migrations, and re-export and re-import every model, because that loop got run enough times to be worth scripting. The output was reconciled against the legacy system on the numbers that matter: account counts, active services, balances, recurring revenue. Then billing and support staff worked through sampled accounts by hand, comparing Helix against the legacy record, because totals can reconcile while individual accounts are wrong in ways only somebody who reads accounts all day will spot.

The dry run is the entire product. Everything the tool does — resumable exports, per-record files, per-record re-runs, a full-wipe rebuild script, an import that goes through real model validations — exists so that running the migration again costs an afternoon instead of a negotiation. If a migration is expensive to rehearse, it will be rehearsed once, and the window is where you will discover what you missed.

Nobody had to apologize to a customer about a charge. That is the whole claim, and it is sourced entirely in exclusion rules, dry runs, and a reconciliation spreadsheet.

The fourth one

The technique got tested again in 2015, harder.

Site5 ran on Synco, an entirely different in-house billing system: its own product model, affiliates, payouts, PayPal payout files, referral credits, audit events, and a comment system. Rubem Nakamura and I spent most of February through June 2015 building the Synco half — a second set of models and a second export handler — while the shared machinery generalized underneath. WormholeLog grew a synco_id. Import.whmcs? and Import.synco? replaced hardcoded assumptions. Both handlers collapsed into a common BaseHandler. An account closer was written to walk the imported accounts, mark their services deprovisioned, set do_not_charge, and leave a comment linking each record to its new Helix home.

It got as far as a working end-to-end pipeline and never ran in production. The company was acquired, priorities moved, and Site5’s customers stayed where they were.

Two things came out of it anyway. The tool stopped being a WHMCS importer and became a migration framework with two drivers, which is the shape it should have had from the start. And the Synco work is where the collaboration actually shows — nearly everything Rubem wrote on the project lands in those five months, working the export side while I worked the import side against a shared JSON contract that neither of us had to renegotiate.

What we’d do differently

The Latin-1 write got promoted by accident. On April 28, 2015, the two export handlers were refactored into a shared BaseHandler, and the file-write moved up with them. Before that refactor, WHMCS exports were written w:ISO-8859-1 and Synco exports were written w. After it, everything was written Latin-1 — including exports from a system that never had the problem the flag was fixing. Nobody decided that. A refactor decided it, silently, because the workaround had been written as a plain mode string with no comment saying which database it was for and why. Empirical fixes need a note attached explaining that they are empirical, otherwise the next person to touch the file will read them as intent.

Reconciliation should have been in the tool. Counts, balances, and revenue totals were compared by hand, out of band, and the comparison is not in the repository. Everything needed to automate it was already there — WormholeLog knows exactly which legacy record became which Helix record — and a wormhole verify command producing a reconciliation report per brand would have been maybe two days of work against a task that was repeated for every dry run of every brand. The one part of the process a person had to be careful about is the part we never made a machine do.

Exclusions belonged in one place. They ended up spread across default scopes on model classes, an exclude? method, a block passed into the export handler, and raw SQL fragments in the brand YAML. Each one was reasonable where it was added. Collectively, “what is not moving, and why” was a question you answered by reading four files, and it is the single question a migration gets asked most.

What held up is the boring half. One file per record, so any failure was one file you could open. An audit log written by a callback, so nothing could forget to record where it came from. Imports through real model validations rather than raw SQL, so the new system’s rules were enforced on arrival instead of discovered later. Bad data carried forward as flagged bad data with a form in front of the customer, rather than guessed at. And a rehearsal loop cheap enough that nobody had to argue about whether to run it again.

None of that is clever. Migrations are not won by cleverness. They are won by being able to answer, at any moment, exactly what moved and exactly what didn’t — and by having run the whole thing enough times that the window is the least interesting part of the weekend.