Case studies

Problem

Member records lived in a collection of Excel files and annual dues were chased by hand.

The off-the-shelf membership platforms were rigid: none of them worked quite the way the client did, or could be styled to look like their own. They also all wanted a share of every subscription, on top of the card processing fees.

What I did

Moved the records into a PostgreSQL database and built a portal where members pay through Stripe, either as a one-off payment or an automatic yearly subscription.

Admins can record bank transfers and standing orders, with each member's standing order date on file to make checking the accounts easier.

Members get renewal reminders, then follow-ups at 30, 60 and 90 days overdue, and we tested every reminder by simulating future dates before going live.

Result

A bespoke system that works exactly the way the client wanted and looks like part of their own site, for a one-off cost of a few thousand pounds.

With no platform taking its share, the only fee on each payment is Stripe's card processing charge.

Problem

The client's records lived in a Microsoft Access database. They needed a way to plot the items on a map for historical and documentary purposes, and for people to find exactly the ones they're interested in, by any combination of properties and by location, without needing to know SQL or specialist mapping software.

What I did

Built the application from scratch in PHP with the Slim 4 framework, using PostgreSQL with PostGIS for the geographic data and Meilisearch for fast text search.

At first, the client exported their Access data to Excel and uploaded the spreadsheets, and could download the data as spreadsheets again to edit it. Much of the work at this stage went into validating the data, and working out the rules and relationships hidden in it.

Once all the data lived in the new system, I built a CMS for entering it directly. It makes invalid entries impossible, such as a reference to a record that doesn't exist, or a value that doesn't make sense given another field. Different users have different permissions, so a junior can enter data that a more senior colleague then reviews and publishes.

Simple properties can be filtered with sliders. For anything more involved, an advanced search builder lets users create rules as complicated as they like, with groups of conditions joined by "and" and "or", all without writing a query.

Users can draw an area on the map to only see results inside it, and switch between base maps to whichever suits the job and looks best.

Once they've narrowed down the items they want, one click downloads a report of all of them, as either a web page or a PDF.

Result

Questions that would otherwise need a developer to write a database query can be answered by the people asking them, and turned straight into a report.

The data is more trustworthy than it's ever been: mistakes are stopped at the point of entry, and every new record is checked by someone senior before it's published.

The project is ongoing, and I continue to develop it as the client's needs grow.

Problem

Every release meant uploading files to a shared cPanel server by hand, running checks, sometimes tweaking the database, then running a script. Scheduled jobs had to be set up by hand too. It took hours and often went wrong.

What I did

Packaged the software with Docker, so that when the developers tag a release, GitLab automatically builds it into an image ready to deploy.

Each of the client's customers now has their own virtual private server (AWS EC2 or similar), and deploying a release is just a case of picking a version and a server in Rundeck. Rundeck gets its list of servers from a small API I built, rather than a hand-edited file, so the list stays accurate as new customers are added. It then pulls the image from GitLab's container registry and the customer's configuration from OpenBao, where it's stored securely and versioned, and deploys them.

The work is ongoing: I mainly set up servers for new customers as they sign up, and help the client's developers with the PHP code when they need it.

Result

Releases take seconds, and the manual steps that used to go wrong are gone. Each of the client's customers runs a version set up and styled for them, and only gets changes when they ask for them.

Setting up a new customer is now quick, so the only limit on growth is winning new business.

When the critical php2shell vulnerability was announced, rebuilding the images in GitLab's registry took only a few minutes. Each customer then took a few minutes more: look up the release they were on, and redeploy the rebuilt image to their server with Rundeck. All of the client's servers were redeployed within two hours.