One hundred and forty-three tables
This is part 3 of Turning Off The LAMP - the story of our journey from an monolithic, in-house -built, PHP "ERP" to a serverless FaaS e-commerce platform.
You can read the previous part here.
After the team was given the task to re-implement the API, they were also told that they are strictly forbidden to do any cargo-cult coding. I think I even said that when in doubt, do NOT look at the PHP code, albeit half-jokingly at first. Why? Because the legacy database had 143 tables. In a database that's not even half-assedly normalized, as mentioned before. Actually I'm pretty sure it was violating all 4 rules of first normal form. Given this, the point of these instructions was to have people think for a moment where the data was most easily accessed and where should the canonical version be saved when doing the ORM bindings for it.
Alternatively, we could have redesigned (or just designed, sans re-, as the database looked like it had just happened) the database and rebuilt the API on top of that and migrate the data, right? Nope. Not back then. It really was not an option at that time. You have to remember that this was back when we were still months from hiring an in-house front-end developer - and the system was live and running. In effect this meant that we were pretty much stuck with the old server side PHP system for the back-office functionality - such as processing orders - for some time...
Spring and summer passed and the reimplementation API was almost complete even though the developers had to do more firefighting and perform CPR on the old system than I cared for. Meanwhile, we had finally hired a senior in-house front-end developer who started working on rewriting the apps in Ionic - with the vision that the same codebase would eventually replace the almost single page app of a web shop, but more of that in another post. Like I said in the previous post, there was no single climatic switch-over event as new endpoints were simply deployed to Lambda and the CloudFront origin & behaviour were added so we just noticed that the old API server was getting less and less traffic, until at some point we noticed that in the last few days the only calls were made to reset a forgotten password.
<Cue: record scratch>
The team was still implementing some API endpoints. Endpoints that no-one had ever used, turns out. Since we already had decided earlier that everything must be rewritten - as there was going to be a new "v 2.0" API - this was actually happy news. It was one of those moments when everyone could just stop whatever they were doing and close a ton of issues "won't fix" by simply marking all not-yet-reimplemented API endpoints "DEPRECATED" in the documentation.
Since we now had implemented all the relevant parts of the API, it was time to take out the trash. I took our latest automated production database backup - anonymized one, mind you, GDPR was after all less than year away now - spun it up on my laptop, opened DataGrip and went medieval on it. I dropped all tables not referenced by the ORM and I knew weren't needed by essential parts of the old back-office software*. Tables with one row? Dropped. I also dropped all columns with only NULLs in them. Columns with only a single distinct value: dropped - these could be easily replaced in ORM with a literal. I think I also dropped some tables that I just plain hated or thought they were stupid. I soon had a SQL script with 207 DROP statements, out of which 84 were dropping tables. Most importantly, running this didn't seem to break anything relevant. I ran it on staging immediately.
I had to tell the others a few days later as no-one had noticed anything odd.
* Have to give a bit of credit here: Luckily for us, yii 1.1 didn't give jack shit about missing tables or columns unless you actually tried to access them.
PS: If you were paying attention and are now asking "but what about those API calls to reset a forgotten password?" - I'll answer that in the next post.