CRUD apps. They’re, I gather, 90% or more of what is built, because it turns out users mostly want to be able to save information, retrieve it, change it, and delete it. My work sponsored a bootcamp for JS + PHP, so I attended. After, I felt that I needed some project to practice on, and one need jumped out to me: employee ranking and rating. My employer uses stacked ranking, for better or worse, and the method being used for tracking this Excel, the obvious choice for databases. The first quarter that I was involved with ranking, the pain of trying to merge together everyone’s inputs was painful. I first made a VBA macro that did most of it for me, but then realized a far better solution was a web app.

The app itself is unremarkable, built using vanilla JS, jQuery (I didn’t say the bootcamp was modern…), Bootstrap, some assorted stuff for animations and draggable elements, PHP, Postgres, ag-Grid, and Plotly. I first used Tabulator in place of ag-Grid (actually, I’m still using Tabulator for a couple of parts), but then we got an enterprise license for ag-Grid, and it had some better features, so I switched.

The main requirements were as follows:

  • Be able to assign numerical and alphabetical ratings to employees.
  • Be able to provide a numerical ranking for each employee, relative to their team.
  • Be able to provide a written writeup for employees, based on MBOs.
  • Provide separate listings for each quarter.
  • Be able to recall and graph information, with reasonable filtering capabilities - if an average user can do it in Excel, provide it.

That’s pretty much it. I got db access to an employee listing, cloned it, and created another one for storing the actual rankings and ratings for each employee. I first used PDO (sanitizing inputs is important, after all), but later changed to vanilla pg_ prepared statements.

My challenges were mostly translating what I expected to occur in the UX to code. For example, my app has three main pages - an index that displays a selected team’s employees, a CRUD page given a selected employee, and a summary page using ag-Grid. As a user, if you select a team, then go to edit an entry for someone on the team, you expect that same team to be selected when you go back. Turns out that doesn’t happen automatically, depending on how you’ve coded it.

I also found through later self-reviews that I would often pick a brute-force method, and not think to change it once it was working. For example, the create page, at the specific request of my manager, had nine checkboxes for various attributes. My first approach was to get the value for each checkbox from the SQL query, then iterate over every checkbox, changing the value accordingly. This was a large chunk of heavily repeated code. Later, I changed that to Object.keys().forEach, with a Map. What was once > 50 lines of code became 9, including comments.

Additionally, I found myself struggling to create enough tests to hopefully cover any malformed input. While I, a sane user, wouldn’t do more than insert a typo in a field, what if someone inputted a negative number where the app was expecting a positive one? What about malicious users? Where should the balance be struck with openness and siloing, usability and security? In the end, I went for capturing obvious typos and outright malfeasance, but allowing full control by any supervisor in the department. There is security via an API from another department that returns a token based on a valid user/pass set in another, company-wide app, so I took that, and then added whitelisting.

As an example to the above, while writing this post, I was playing around with the app, and realized it was no longer graphing on the summary page. Chrome’s console showed this statement returning a TypeError for nulls:

let unSuccCount = newDataArray.filter(function(newDataObj) {
                        return newDataObj.overall_rating.indexOf('Unsuccessful') > -1;
                });

I deduced that up until this point, I had been manually populating boilerplate db entries for everyone via a SQL INSERT, with many default values. Now that the app was generating its own boilerplate on demand, but leaving the overall rating (and some other fields) null, this broke. My solution was to do a simple null check before the return. Another option had I caught this earlier would have been to make the app default ratings to ‘Successful,’ but at this point with user data entered, I felt it would have been difficult to update only those which were not yet entered. Additionally, ag-Grid handles nulls well, with a blank, which is a good visual indicator that you’re missing data from a record.

Things I learned from this project:

  • Life is much easier when you don’t have to develop for IE, but if you must, polyfill.io is amazing.
  • Spending hours fighting cryptic error messages in PHP only to discover a missing semi-colon is apparently a rite of passage.
  • If you don’t cast it to a string, Postgres will interpret the checked property of an unchecked checkbox (false) as unknown (null), since it expects FALSE. This will cause all manner of frustration until you bother to lookup the documentation for Postgres’ boolean type, where it says precisely that.
  • Getting the SQL query right is easier than trying to massage the data later.
  • Fixing all known bugs doesn’t mean your code is bug-free.

Unfortunately, the entire codebase is not available for perusal thanks to security policies, but I’m happy to answer any questions about design, specific issues, and the like.