Data Type Pitfalls: Lessons Learned Dealing With Fiat and Crypto

I have worked in fintech for a number of years now. Each system had its pros and cons, legacy, and tech debt (tech opportunities). Modelling data was a constant and becoming more crucial as micro services became mainstream. At the beginning of my career in the CRUD world, I usually maxed out the field, varchar(max), yes I was naive. Storage was not a problem, data types were “free” to be used without the rigours of system performance. We used cache for everything and usually cache was a complete store of the database, sometimes a bit more. Need more RAM for the cache, send a request to IT (devops, SRE, sys admins) to add more. They ask why, simply say manager X wants this page to load in seconds instead of a minute.

Today there is a recurrent pattern on data types especially in the financial world. The importance of data types and the selection became increasingly apparent as I continued to learn. As systems I worked on scaled, managing cost became crucial. I am not just referring to infrastructure costs, cost of execution increased as well as I will explain further. That recurrent pattern is how much should we provision, back of the envelope maths helps to some extent. When your company scales from 10 TPS to 1000 TPS you start to hit some bottlenecks.

The crypto landscape evolves rapidly, early on a company I worked at made the assumption that char(3) would be great to store the short codes for the crypto currencies. Besides, BTC was the only currency ever we were going to list, it is the king of crypto and most likely to succeed. Forward a few years along and the company targeted increasing revenue opening to other cryptos and stable coins. You guessed it char(3) needed to be char(4), it was changed to varchar(30). This required a large migration of data and updating all services across, shared libraries and a lot of convincing to stakeholders that we could not simply add a new field in an hour.

The point is that systems change and adapt. This is not unique to crypto. When I worked at a back office asset manager we encountered the same issues in various third party systems. You would log a request to extend a field and then wait for what felt like an eternity. The reply would eventually come back saying “we are working on it”. We would sarcastically ask why is it taking so long and how hard can it be. These systems often had many clients and had grown over years into giant monoliths. Making what looks like a small change could impact multiple clients at once. I have sympathy for those engineers because I learnt the hard way how even the smallest data type change can ripple through an entire system.

Recently a rather well established card acquirer capped their email address to 40 characters, we hit an error each time we went over, requiring manual intervention to remedy the transaction. The acquirer could not change the field and suggested we truncate the field. The card acquirer received the field through another api call for KYC so had what they needed. They could not make the field optional. The biggest debate was how to truncate, do we exclude the domain, leave the domain or remove characters randomly.

Another example and most likely common occurrence is decimals particularly when the precision runs out. I have encountered this issue a number of times usually we start off in Postgres as a Decimal type and at the time of design we have enough decimals for the currency we need to store. As an example fiat currencies require accuracy, with no rounding issues. Usually there is a single fiat currency that a company will support (USD, EUR, GBP) which looking at the current exchange rate the precision is closely matched usually decimal (10,2) should be sufficient. Ten digits in total and 2 decimals allowing for 99 million in currency which when the company is a start up seems realistic. After all how many transactions would be 99 million dollars.

Now from my experience introducing a new currency like Indonesian Rupiah complicates things. The rupiah does not have cents, although it can be represented there is no denomination for the rupiah below 50 and the representation ranges from 50 to 100 000. Now when converting that to and from crypto introduces several challenges, the precision before has to increase especially expressing large amounts. This means we need to increase the precision, 10,2 is not enough. Now if you are working in a high scale environment usually the value is stored in multiple services, backend and frontend.

We could use a library. the downfall is that if one service does not update the latest version you have a type mismatch and then the inevitable happens you lose precision, leaving “dust” scattered across the services and ultimately your customer is asking show me the money.

So what’s the solution, it’s not an easy one. You can always anticipate the future and ensure enough precision early on. As I explained that is a rarity, but if you did hopefully your CTO is buying you lunch. Solutions that my teams and I discovered varied.

I mentioned crypto and fiat currencies. Each has unique properties and crypto generally has a much larger numerical representation and more demanding precision requirements than traditional fiat. In my experience separating the two is easier because you can then scale precision independently. When everything is stored in a single field you end up constrained by the strictest currency. That is usually the crypto side which forces you to overprovision precision for fiat and this increases storage, validation complexity and the risk of inconsistencies across services.

Another approach that I have seen work is splitting the stored value into two fields. One field represents the integer portion and another represents the scale or fractional portion. When you combine the two you always get the correct value regardless of currency. This avoids precision loss during conversions and helps make the rules for fiat and crypto explicit. It also makes migrations easier because you can expand the integer or fractional field independently without impacting existing data.

If you are using a library or package to represent the types, make sure there is the ability to warn engineers at compile time that they are using an outdated library. I have been in a number of incidents where at runtime we discovered that a service was using an outdated version. A git hook will go a long way from having discrepancies in versioning.

There is a fine line with designing now and having changes in the future. The inevitable will happen you will have to change types at some point, requirements and business strategy evolve. Make that migration easy by ensuring from the onset you have migration tooling to enable rollback if needed – flyway, golang-migrate, EF (database schema migration) should be adopted very early on as a prerequisite – and incorporated in your continuous integration process.

As systems grow, it becomes clear that data modelling choices made early on have long and expensive consequences. It is never just about a field type or a length change, it is about the behaviour of your entire system under real conditions, across multiple services, currencies, and versions. Thinking carefully about these choices upfront saves frustration later, but it also makes you appreciate the real complexity behind something as simple sounding as “just change the type.”