One of the products we work on has a fully encrypted chat built-in. The other day as part of a larger deployment we pushed out a mini-feature that shows how long it has been since the last message was sent in the chat.

The Problem

Everything worked perfectly in our alpha and beta environments but in production, all of the previous chats times were somehow 17 years in the future, which is not ideal. I took a look at where we were setting the last message time to find roughly the following:

const chatUpdate = {
  lastMessageTime: moment().valueOf()
}

This should be setting our lastMessageTime time to the current time as a unix timestamp. So you would expect the value saved to be something along the lines of this 1614699105, however, 2147483647 was getting saved which works out as Tue Jan 19 2038 when Tue Mar 02 2021 is expected. My first thought, seeing as this works fine in our other environments was that our production server's date-time somehow got changed. I sshd into a couple of our instances to check but they were perfectly fine.

At this point I realized that all the new chats were getting set to 2147483647, so something was limiting how high that number could go and it clicked. Our database schema must be wrong. It turned out that in our production database the type had been accidentally set to INT(11) rather than BIGINT(20) (we store the UNIX timestamp in milliseconds).

The Solution

Fundamentally how we are currently doing database schema changes is subpar for this project. We write a script to make changes and manually run it against each database completely separate from the rest of our CI process. Being a small team makes this setup acceptable so we have never really needed to focus on changing it, however, this manual step is how the issue came about and it's high time we automate it. After doing some quick reading I think the best approach will be to use the likes of (umzug)[https://github.com/sequelize/umzug] for updates.

Recommended Reading

https://betterprogramming.pub/evolving-a-database-schema-10b7f4094d14

Database Migration Resources (NodeJS)

# Databases

Is Aurora Serverless worth moving to?

Time Travelling Chat Messages