Every new column meets old data: five lessons from a live IoT platform

The short answer

On a platform with years of data, every schema change is really two changes: what new records look like, and what happens to every existing one. The mistakes below all came from thinking about the first half and forgetting the second.

They come from a cloud platform that runs connected smart lockers in UK workplaces and talks to each lock over MQTT. Over eight months our engineers added a lot of fields to it. Here’s what we learned, including what we got wrong.

Why is a default on a new column risky?

We added an account status, live or not live, so new sites could be installed and tested before customers started using them. The column defaulted to “not live”, which was right for new accounts. It was also applied to every existing account, which quietly switched every current customer to “not live”. A data migration the next day set existing accounts back to live.

When you add a column with a default, decide separately what existing rows should hold, and set it in the same release.

Why does a new required field break old records?

We added a model name to each lock, checked against a list of known models, and made it required. No existing lock had a model recorded, so from that moment any update to an existing lock failed validation, including routine changes that had nothing to do with the model. Four days later the field became optional on edit, and required where new locks enter the system: the import file installers use.

Require a new field where new data comes in. Fill in existing data separately, and only then tighten the rule for everyone.

Where should device-specific behaviour live?

How a lock behaved (which protocol options it used, whether it received the full list of users or only the changes, its light and sound settings) was decided by which brand of account it belonged to. That works until one customer wants two kinds of lock on the same site. We moved the setting onto each lock as a lock type, so one account can mix hardware and each lock gets the configuration it needs.

The general rule: put a setting on the thing that actually behaves differently. A customer-level setting that’s really a device-level setting will eventually force a customer to choose.

How do you handle devices on different firmware?

Newer lock firmware reported the battery as a voltage instead of an estimated percentage, while older locks kept sending percentages. Two rules kept the data honest:

1. Only save what was sent. When a lock checks in, only the fields present in its message are updated. An older lock that doesn’t report a voltage can’t blank out a voltage that’s already stored.
2. Fall back gracefully. Screens show the voltage when there is one, the old percentage when there isn’t, and “unknown” only when neither exists.

Voltages are grouped into four bands, from critical to good, which drive a daily email to the people who manage the locks. Agreeing the thresholds with the people who’ll act on the alerts matters more than the code.

Which data types and constraints should you choose?

1. A number isn’t always a number. We stored four-digit PINs as integers, so a PIN of 0042 became 42 and failed the four-digit check. It was a text column eleven days later. PINs, phone numbers and postcodes are text that happens to contain digits.
2. Uniqueness belongs in the database. Device MAC addresses had to be unique so the same device couldn’t be registered twice. A check in application code catches most duplicates, but two registrations at the same moment can both pass it. Back it with a unique index, which is the only check that always holds.