Purpose / Overview
There are several saltwater disposal sites in North Dakota spread around the Bakken Formation in which the ticket information from the truck drivers must be collected and archived. The information includes the driver’s name, company, truck number, water type, BBLs to be disposed, etc. The ticket database should be accessible via the company’s VPN as a web application. Various types of reports will be generated by “slicing and dicing” the database parameters. The results of these reports can be exported to a CSV file. The database has a robust backup strategy in which partial backups occur every 24 hours and full backups once per week. The backups are saved on the local main device as well as copied to a remote backup device. The most recent five full backups are automatically maintained on both devices.
Implementation
The system consists of the following main components:
- Modbus server to receive tickets over the network
- Database to store tickets (data mart)
- Web application as a frontend to the database
- Web server to serve the web app over the VPN
- Database backup system with remote device for backup duplication
- Interface to company and driver authorization database residing on a PLC
At each site there is a touchscreen for drivers to enter their information. The touchscreen is connected to a PLC connected to the VPN that sends the ticket data to the main database device (a Raspberry Pi) using Modbus over TCP. A modbus server is listening for incoming tickets on the Pi. The Java version of the EasyModbusTCP library was used to implement the modbus server. When a ticket is received, it is inserted into a data mart database, which uses a star schema configuration. The DBMS is MariaDB. A simple PHP web application is served by Apache web server over the VPN for employees to access the database. The web application provides a simple form to set constraints and filters on the returned result set. The results are rendered in a table and there is an option to export to a CSV file. The database is backed up using mariabackup incremental backups Monday through Saturday, and a full backup every Sunday. I have these set up using cron jobs and custom shell scripts. Every backup is pushed to a remote Raspberry Pi for greater redundancy. On the first Saturday of each month, a full database recovery is run on the backup Pi and tested against the main database for data integrity. There is a separate PLC which contains all of the authorized companies and drivers. This authorization system was already in place and the interface was provided via the on-site touchscreens. I’ve also added a web dashboard to interface with this system. For that I used PHP to execute shell commands that would invoke a modbus client instance using the same library (EasyModbusTCP) that would send the appropriate modbus messages needed for the database PLC.
