GhostiFi — The complete MVP database design and pseudocode

Overview

In this post, I’ve written out the entire database schema and every single function of the GhostiFi MVP in plain English/“pseudo code”. It describes exactly how I plan to be able to make my sketch of the product interface come to life as a real application.

GhostiFi is the second SaaS business I am working on developing. I launched my first SaaS in May 2018 and grew it from $0 to $2,000 MRR providing UniFi cloud hosting for over 100 customers.

By writing all of this down, it forced me to think through how each step of the application will work, and several times I realized that I had forgotten about something and had to go back and make design changes to one or several components of the system. That is much easier to do now in the design phase rather than having to rewrite real code later on.

When I programmed my first SaaS, HostiFi, I didn’t spend very much time designing or planning how it would all work. I would basically write snippets of code, get them working, and then continuously integrate them to create the larger application. I was making up the DB schema on the fly, adding new columns whenever I thought of a new feature, and my small snippets of code grew to thousands of lines of quick hacks without utilizing hardly any classes or even functions.

As that code base continued to grow, and I expanded it from building UniFi controllers, to also UNMS, UCRM, and UniFi Video servers, a lot of code was being copied and pasted in different places. If I had designed it properly, I should have had a server class, and a sub class for each type of server. There are many ways it could be rewritten to be more modular and easier to manage, but for now it’s kind of a mess that I have to be very careful with when working on.

When you don’t spend time designing and planning, code gets hard to manage as it grows, and quick hacks here and there become technical debt later on. Technical debt slows you down when troubleshooting, and trying to add new functionality.

Being an amateur programmer, I did the best I could when building HostiFi, and since then I’ve learned quite a bit. So now, with GhostiFi, I’ve sat down and hashed out my first real application design session with myself, and I’ve planned everything out from start to finish.

Having the big picture in mind from the start really helps to be able to spot patterns, group code together in classes and functions, and create modular components which can easily be extended in the future.

The DB Schema

The database will have many tables already created by WordPress and various plugins, however, the only tables that will be relevant to the custom parts of this application are going to be “wp_edd_subscriptions” (where Easy Digital Downloads plugin which stores recurring subscription information and syncs with Stripe), and our new table called “server”.

Server Table

id (int)
customer_id (int) — This ties this server to the Easy Digital Downloads customer
product_id (int) — This is the type of server. Right now there is only one, but in the future there might be different types, for example, a more expensive one with additional features
wp_edd_sub_id (int) — This ties the server to the Easy Digital Downloads subscription
server_ip (string)
user_email (email address) — The customer email address
server_name (g0xx.ghostifi.net)
root_password (sASFxagergJrtyt) — Root password for the customer if they want it, in the future I want to use key only SSH authentication where the user uploads their public key and the server is built with it
bandwidth_this_month (bandwidth used in Mb) — Keep track of how much bandwidth each customer uses between different server rebuilds, 1TB limit
bandwidth_limit_this_month (Mb int) — 1TB currently, in the future might add more products with higher limits
rebuild_schedule (None/daily/weekly/monthly) — Rebuild frequency. Starting with only 4 options but might add more later
current_location (NYC/Paris/London etc)
rebuild_schedule_location (Random/NYC/Paris/London etc)
status (Installing, Rebuilding, Running)
ovpn_file (g0xxx.ovpn)
rebuild_now_location (Random/NYC/Paris/London etc)
rebuild_now_status (0 or 1) — 0 means a rebuild_now has not been requested, 1 means it has

The Python Backend

server_check.py

Runs once every minute on a cron job.

It creates a file when it starts running and deletes it when it finishes — it checks for the existence of that file before starting in order to determine whether it should run or not (this prevents it from running more than one instance at a time).

First, it checks for the following conditions by comparing the “wp_eddd_subscriptions” table with the “server” table:

1. If it finds an active sub with no server built, it adds it to a list to be built (servers_to_build.append(server))
2. If it finds a subscription that has been cancelled, but the server still exists, it adds it to a list to be destroyed (servers_to_destroy.append(server))
It also does an SQL query to find servers with a value of rebuild_now_status = 1, if found, it adds them to a list to be rebuilt (servers_to_rebuild.append(server))

Then the script will do something like this (pseudo code):

for server in servers_to_build:
thread(server.create())

for server in servers_to_destroy:
thread(server.destroy())
for server in servers_to_rebuild:
thread(server.rebuild())

Notice that a new thread is spawned for each. That will speed things up when, for example, it has 10 rebuilds to do at the same time. If each rebuild takes 5 minutes and threading was not used, it could take up to 50 minutes for the last server to be rebuilt. Threading makes it possible to do them all concurrently, so it should only take 5 minutes to rebuild 10 servers.

The main script for the entire application is the “server_check.py” script will look something like this (pseudo code):

server_check.py
# Custom Python class called server which I will create methods “create”, “destroy”, and "rebuild_now" for
server = server(server_name, username)
# Create a server object with a server_name (g0xxx.ghostifi.net for example, and customer’s username)

server.create()
# Create a record in the database for the server and set status to Installing, this way the PHP frontend will have something to show the user while the rest of this setup completes
# Create Vultr server in NYC (by default, user can migrate later)
# create DNS entry for server on CloudFlare
# install OpenVPN
# copy ovpn file back to a location on the ghostifi.net server
# modify SSH to allow root login with password
# create a snapshot of Vultr server (stored on Vultr) to use later for future rebuilds
# return root password to database, ovpn file name, server_name, server_ip, current_location, and set status to Running
server.destroy()
# Delete the server from Vultr
# delete the ovpn file
# delete the snapshot on Vultr
# delete the CloudFlare DNS record for the server
server.rebuild_now(rebuild_now_location)
# Update database record for this server, set status to Rebuilding, this way the PHP frontend will have something to show the user while this rebuild is in progress
# Update the bandwidth in database (bandwidth_this_month += bandwidth)
# Delete the current server from Vultr
# Create a new server from the snapshot of the old server, with the location of rebuild_now_location
# Update root password, server_ip, current_location, rebuild_now_status = 0, and set status to Running in database record for this server (server table)

rebuild_daily.py

Runs once every day at midnight EST on a cron job.

rebuild_daily.py
# Rebuilds all servers that are set to "rebuild_schedule = daily"
# Follows the same process as server.rebuild_now() but uses rebuild_schedule_location instead of rebuild_now_location

rebuild_weekly.py

Runs once every week at midnight EST on a cron job.

rebuild_weekly.py
# Rebuilds all servers that are set to rebuild_schedule = weekly
# Follows the same process as server.rebuild_now() but uses rebuild_schedule_location

rebuild_monthly.py

Runs once every month at midnight EST on a cron job.

rebuild_monthly.py
# Rebuilds all servers that are set to rebuild_schedule = monthly
# Follows the same process as server.rebuild_now() but uses rebuild_schedule_location

reset_bandwidth_monthly.py

Runs once every month at midnight EST on a cron job.

reset_bandwidth.py
# Resets all bandwidth_this_month values to 0

The PHP/HTML/CSS/Javascript Frontend

dashboard.php

The only custom coded part of the frontend is dashboard.php, which is the user dashboard. The rest of the pages on the website (homepage, checkout, subscriptions, invoices, account settings) are all handled by WordPress, the Startuply theme, and Easy Digital Downloads’ plugins, which is awesome, that saves me a TON of work.

On dashboard.php, I will need the following custom code (this is pseudo code, and Python-ish looking instead of PHP syntax just because it was easier for me to write that way):

You’ll notice there is a kind of “mini API” that I built into the same page, it has 3 simple endpoints and returns a JSON response for each (only if it detects an AJAX request was made). It was easier for me to build that into the page rather than creating a separate page for it and having to learn how to integrate the WordPress stuff.

if request is ajax:
# return json response
# get username of logged in user (from WordPress session info)
# Processes POST Ajax requests
    if POST request_type=rebuild_now:        
# Update server database where server=server and username=username set rebuild_location=rebuild_location and rebuild_now=1
    else if POST request_type=update_schedule:
# Update server database where server=server and username=username set rebuild_schedule=rebuild_schedule, rebuild_location=rebuild_location
    else if GET request request_type=servers:
# select server infos from server table where username=username
else:
# return normal dashboard UI
# get username of customer who is logged in by asking WordPress about the current session info
# query server table to find if a server row exists
# do ajax request with GET and request_type=servers to update servers list and statuses every 15 seconds

if a server row exists:

# echo a heading of “VPN Servers”
# echo an HTML table with the server info (see sketch of UI) for each server
# Create a “Rebuild” button which triggers an Ajax POST request to dashboard.php which includes: request_type=rebuild_now, rebuild_now_location,server
# Use a “Toast”/pop-up which lets the user know that the request was successful
        # echo a heading of “Rebuild Scheduler”
# echo a table based off of the information gathered from the server row (server_name, rebuild_schedule_location, and rebuild_schedule) for each server
# Create a “Save” button which triggers an Ajax POST request to dashboard.php which includes: request_type=update_schedule, rebuild_schedule_location, rebuild_schedule, server
# Use a “Toast”/pop-up which lets the user know that request was successful

else:
# echo “You haven’t subscribed to any plans. Purchase The VPS VPN so that we can build it for you!”

Closing thoughts

I hope this was helpful for anyone who is an aspiring programmer or entrepreneur to see my thought process in how I went about turning a sketched design of a user interface for my product idea into a step-by-step process that any programmer might be able to understand and turn into code.

If you have any feedback on how I could improve this design please let me know in the comments section!

I am also looking for feedback on the concept itself, as well as beta testers. Please sign up for the newsletter at https://ghostifi.net if you are interested.

If you want to know when I release another post about building GhostiFi, you can follow me on Twitter: @_rchase_ or Medium: Reilly Chase