A Type Migration Across Three Services: VARCHAR to BIGINT at Scale

A block height stored as text instead of a number sounds like a small, easily-forgiven decision, and for a long time it is. The chain's own RPC layer returns numeric values like block height as strings in the first place, because some of those numbers exceed what can be safely represented as a standard number in JavaScript, so storing them as text on the way in feels like the path of least resistance. The cost of that decision doesn't show up until someone runs a range query and Postgres compares those "numbers" alphabetically instead of numerically, silently returning wrong results. Fixing that meant migrating the column's actual type from text to a proper integer at its source, the indexer that originally writes it, and then getting every downstream reader of that same column to agree with the new type. The migration itself turned out to be the least risky part of the whole change.

Why the column type was the easy part

Changing a column's underlying type in Postgres is a well-understood operation. The genuine risk in a change like this isn't the ALTER TABLE itself, it's every piece of application code downstream that was written against the old type's behavior without ever stating that assumption explicitly. A function that concatenates a block height into a string for logging, a comparison that relies on lexicographic ordering without realizing it, a serialization layer that formats a string differently than it formats a number, none of these show up as compile errors. They show up as runtime bugs, in production, only when that specific code path executes against the newly-typed column for the first time.

That's exactly what happened here. The migration itself landed cleanly across the backend service and the socket server, both of which read from the same underlying column. Two follow-up hotfixes were needed shortly after, both in functions that hadn't been touched by the migration directly but that broke because they'd been implicitly relying on the column's old string type when computing values like the latest block height and network-wide statistics. Neither hotfix was a large fix. Both were necessary, and both were the direct, predictable cost of a type change rippling through code that had never explicitly declared its assumption about that type in the first place.

What coordinating across services actually requires

The reason this qualifies as a coordination problem, not just a database migration, is that three services, the indexer writing the column, and the backend and the socket server reading it, are all deployed independently, on their own release cycles, each with its own copy of code that depends on this column's type. A schema change made once, in one place, has to be correctly consumed by every service reading that data, and there's no compiler shared across service boundaries to catch a mismatch between what the database now returns and what a given service's code still expects. The two hotfixes here are exactly what "coordination worked, but wasn't perfect on the first attempt" looks like in practice: the underlying migration itself never had to be rolled back or redone, but the code on the consuming side needed a second pass once the new type actually reached it in production.

What this demonstrates

The lesson here isn't "test your migrations more," though that's true of any migration. It's that a type change at the schema level is never really contained to the schema. It's a contract change with every piece of code, across every service, that reads that column, and the services most likely to break are the ones that were never explicitly written against a documented type contract in the first place, because they were written back when the column's actual type was assumed rather than verified. Catching both breakages quickly, shipping targeted hotfixes rather than a broader rollback, and confirming the underlying migration itself held throughout is the realistic version of what "coordinating a schema change across services" looks like when it goes well: not zero surprises, but small, contained ones, caught and fixed fast.