• Gert Nelen

Writing the back-end of AquaPI

This post is going to cover the programs running in the background to keep my aquarium 'fully automated', the challenges I encountered and the solutions I've built.

Let's start with the data structure behind.

I use mysql as a db for storing and reading values so the back-end and front-end programs can communicate clearly. I will use the local file system for logging.

Data structure and content of aquapi Database

The GPIO table only has a functional role. I use it to map which device is on which GPIO pin, so when I run (back- or frontend) programs I'm always speaking the correct language to the correct device. The numbering I use is BCM and for pins that don't need adressing (but which I want to document), I do 40+the PIN number (so I can keep track of what pins have been used). When I have to work on the breadboard from now on, since it's mounted in the cabinet, I will be working in a very small space (the closet), hanging upside-down. Knowing exactly which cable goes where is primordial for limiting frustrations :-) ...

The most important table is sensordata. It will be updated and read frequently from different sources, so I kept the data footprint very low. Here all readings will be collected. One python script will be updating the table every 2 minutes with the electronics sensor data (needed for alerting when water gets near electronics or temperature raises quickly or too high; pointing out a component failure) and another will interface with USB and read in the aquarium test values (collected from a senEye device). A senAlert field will be set to 'true' when the USB reading timed out or failed for some reason, which alerts a python supervisor to restart the collection process. I also added an error counter to keep track of problems with the USB subsystem of the raspberry. The alert field will be set to 1 when any of the parameter checks are out of bound. An alerting program is ran to send notifies with the relating data (still under developement). This data is also used to provide information to the LED display I have attached to the system. It will cycle through all 'wet' values and show a scrolling report containing all sensor data every 2 minutes. A clean-up program to delete old data is also in the writing, but I'm still struggling with the correct SQL syntax. Notice I also have a depth value in there. I wanted to have water depth measurement, but since I can't find the correct electronics device which is water-proof, this piece is on hold for now.

To tackle the lighting problem I use 3 tables. Table lighting contains the current setting of the automatic day/night cycle. Table lightingTiming is checked and updates table lighting once every minute (if update is provided). There 's a boolean in here to stop the automatic cycle when lighting is set to 'manual'. This provides the ability to set a certain lighting Mood or manual light configuration to the aquarium and stop the automatic update (day/night) cycle.

I also wrote a front-end component allowing you to set save the manual lighting settings and name them as a mood. You can then just select the saved setting from a list. Some preset functions will also be created later on, like 'lightning', 'clouds', 'aurora borealis', ;-) .... I'm also thinking of adding those randomly to the day/night cycle a couple of times a day.

There are 5 LED strips in the aquarium. 1.60 m of cold white (3000k), providing daylight spectrum; 60 cm of warm white (2400k), providing sun radiation -like light; 60 cm of night blue, for simulating night (but still seein fishez...) and finally 1 meter of RGB (for fish disco ;-) ). Every color has it's own channel of control on the PI with power settings from 1 to 255.

But I'm deviating. The back-end of the lighting system consists of a python class which is instantiated and threaded and runs each color independently from one another. This makes it possible to manipulate the process without interfering with any other controls. You can pause, stop, push new data (target light value [1-255], speed; instructs the object to increase/decrease the lighting to the new value, using speed (in seconds) intervals (controlling the speed at which that color changes)), get status of the process. The controller process continuously monitors the lighting table, if a new value comes in for a certain color, it will instruct the controller to change value and speed for that color. Another script is running every minute to check if any changes are planned at that time, and updates the lighting table accordingly. When the boolean cycle is set to 0, the cycle stops, but the timing will still run on and update the current values, for when the cycle must resume. On a resume/restart of the lighting controller it will pick the last lighting settings from the database and set it directly (regardless of speed) and then further run it's course.

I will also implement a feature that will restart the cycle when it stays of for too long (for when someone forgets to resume the cycle).


The message you send from this webpage is unencrypted. If you want full discretion please use public PGP key to encrypt. Mail address is shown below - just replace 'at' with @ (this is done to prevent bot scanning)

Gert Nelen

ICT Security Specialist


+32 474 86 19 44



gert.nelen at g3rt.be 

  • LinkedIn Social Icon
  • Facebook Social Icon
  • Google+ Social Icon