9 min. read

March 14, 2021

shit happens

The One Time I Deleted All of My Company's Social Security Numbers. 

In the world of healthcare in the United States, most things used to identify you as a patient are based on two unique factors. Your social security number (SSN) and your date of birth. 

Patrick Zawadzki

Patrick Zawadzki, Fullstack Software Engineer

Would you like some SALT with that?

A few years back, I accepted a full-time role at a healthcare tech company after a year-long internship. I was confident in my new role, I knew the people, I knew the technology, and I knew the systems. I also, unfortunately, had new graduate overconfidence which gave me an insatiable desire to prove myself and learn as much as possible. 

While the desire to learn constantly and the curiosity to do so is crucial in software development, overconfidence, on the other hand, can make you miss painfully obvious details. 

The healthcare company was growing so there were always new requests and ideas flowing in, this caused an ever-growing backlog of tech debt, and there where any signs of it slowing down.  One of the items in the backlog was an additional part of our patient create and update system that sanitized and validated any SSNs.

The task sounded simple and seemed like a great way to get better with coding regular expressions — and I’d also be chipping away at the huge backlog. For someone who wanted to prove themselves as an effective, independently operating team member, it seemed impossible to pass up.

Before I began to tackle the backlog item, I learned how the SSNs were defined. I did this to make sure I could properly write a function that would read the SSN and sanitize it. This was my first mistake! In hindsight, I should have searched for a tested library to do this for me. 

A problem like this has probably been solved a plethora of times before. I shouldn’t need a custom solution but being a fresh graduate I wanted to put my skills to the test.

I began implementing my approach and quickly came up with a solution. I had created a function that could be used to validate anywhere in the application. One place being in the patient controller which handled patient data creation and updates, and another being in a “migration” controller we used to run one-time code changes to our databases. After I had tested the regular expression locally and felt confident in the function I’d created, I pushed the code review. 

Team lead: “Did you test it?”  Me: “Yup” 

The code was quickly approved and on its way to production! We ran the migration, nothing exploded and the request was complete. I moved onto my next item and began to let this one leave my mind. 

A few days later, we got a customer support ticket wondering why a patient's social security number wasn’t appearing. (At the time our system allowed a patient to only be entered into the system with their date of birth.)This at first seemed like a training issue and then quickly was uncovered as an application issue. Turns out that I hadn’t tested the actual JavaScript application of the regex, just the expression itself...big mistake. 

Since our data was all patient-specific, HIPAA compliance and encryption are a backbone of a software application and are considered a top priority. All of our social security numbers were treated the same as passwords and used a unique salt (cryptography) for storing the text in our database. That way, even if our database was compromised there was an extra layer of security on the patient. My regex was correctly designed to check an SSN but did not unencrypt the number before checking it. Therefore instead of seeing XXX-XX-XXXX for an SSN it saw a random set of letters and numbers and considered it invalid. 

This is the part of my career where I learned the importance of unit testing and knowing your application infrastructure. I still struggle with unit testing everything today, sometimes choosing the speed of completing a request over the quality of a request. However, this story is a stark reminder of why speed isn’t always important. People say unit testing is important, but it’s truly one of those things that you don’t understand the value of until it bites you. Kind of like wearing a seatbelt in a car, it can feel like you really don’t need it until you need it. 

After the migration had run and the code had been completed, the code I wrote deleted every SSN from our database. The embarrassment I felt when I looked at the other engineers, and the CTO, and told them I had just written code that deleted all of our SSNs in our application still feels fresh even 5 years later. For any company, even more so one that is trying to establish itself, this is a large problem. Somewhat fortunately, SSNs don't change often so that meant we could use a backup from our database as a starting point.

The good news was that the data was there and only a day old! The bad news — the backup was all of our data, so a simple rollback was not possible as it would delete everything else that had happened in the last 24 hours. The CTO, who was our lead engineer at the time, told me I’d have to figure out a way to pull all of the numbers out of the backup and reinsert them into our system in production. 

Being a healthcare database, HIPAA compliance was needed and meant there was unique encryption systems setup on production that did not exist in our lower environments. He helped me understand how to pull the backup locally and how to get access to the data. Once I was able to run the database locally, I finally got all of the SSNs extracted with their appropriate patient IDs and created a sizable database update statement to run in production via our migration controller. This took me a day or so to complete, and I didn’t sleep much in that time over the thought of losing my job and negatively affecting my reputation. 

The issue in the code was updated and the migration was all ready for code review. 

“Did you test it?” asked my manager raising an eyebrow.  “Yup — but actually this time.”

The code was merged in, migration ran and suddenly our patients had their data back without affecting the rest of the database. The relief I felt from seeing the masked SSNs appear on the front end again also still feels fresh.

As horrible of a memory this still is for me, and how tarnished my career feels after having done it, it was one of the single greatest experiences I have had. It’s true, people learn more from their mistakes than from their successes. I learned the value of quality over speed, not reinventing the wheel, and the importance of testing. 

Software touches so much of what we do in the world today and controls the systems crucial to our finances, well being, and lives. Sometimes a little unit test might be more important than you think.