14.07 Consolidated Financial Reporting

Overview: Script for Creating and Maintaining the quickbooks_consolidated_profit_and_loss View

Purpose:

This Python script is designed to ensure that the profit_and_loss view in the quickbooks_consolidated schema of the PostgreSQL database is created and properly maintained. The script checks if the view exists every 5 minutes, and if it does not exist, the script creates the view and grants the metabase user read access to it.

Functionality:

1. Connection to PostgreSQL:

   • The script connects to the PostgreSQL database using credentials stored in a .env file. The credentials include the database host, name, user, password, and port.

2. Checking for the View:

   • The script checks whether the profit_and_loss view exists in the quickbooks_consolidated schema by querying the information_schema.

3. Creating the View (if it doesn't exist):

   • If the view does not exist, the script executes a SQL query to create it. The view is composed of a union of several profit_and_loss tables from different schemas, excluding specific account classes.

4. Granting Permissions to metabase:

   • After the view is created, the script grants SELECT permission on the view to the metabase user to ensure that Metabase can access the view for reporting purposes.

5. Error Logging:

   • The script uses Python’s built-in logging module to log any errors that occur during execution. Errors are recorded in a log file located in a logs directory.

   • The RotatingFileHandler is used to limit the size of the log file to 1MB, with only one backup kept to prevent excessive log file accumulation.

Setup on the Mac Mini:

1. Virtual Environment:

   • A virtual environment is set up in the project directory (/Users/joshuaholt/hermes/projects/07-consolidated-financial-reporting) to isolate dependencies. The virtual environment contains all necessary Python packages, which were installed using pip3.

2. Shell Script:

   • A shell script (run_script.sh) is created to activate the virtual environment and run the main Python script (main.py).

   • The shell script includes the following commands:

     #!/bin/bash
     source /Users/joshuaholt/hermes/projects/07-consolidated-financial-reporting/venv/bin/activate
     python /Users/joshuaholt/hermes/projects/07-consolidated-financial-reporting/main.py

   • The script is marked as executable with the chmod +x command.

3. Cron Job:

   • A cron job is set up to run the shell script every 5 minutes. The cron job is added to the crontab using the following command:

     */5 * * * * /Users/joshuaholt/hermes/projects/07-consolidated-financial-reporting/run_script.sh >> /Users/joshuaholt/hermes/projects/07-consolidated-financial-reporting/logs/cron_log.log 2>&1

   • This command ensures that the script runs every 5 minutes and logs the output (both stdout and stderr) to cron_log.log in the logs directory.

Logs:

• Error Log:

  • The script writes error messages to logs/error.log. The log file is managed by RotatingFileHandler, which ensures that the log file doesn’t grow too large. Only errors are logged, which helps in focusing on critical issues without cluttering the logs with non-essential information.

• Cron Job Log:

  • The cron job’s output (standard output and errors) is redirected to logs/cron_log.log. This file logs every run of the script, capturing any messages or errors that occur during the execution of the cron job.

How to Test and Monitor:

1. Testing:

   • Drop the profit_and_loss view manually and wait for the cron job to trigger the script. After 5 minutes, check if the view has been recreated. You can also verify that the metabase user has access to the view.

   • Check logs/cron_log.log and logs/error.log for any messages or errors during the execution.

2. Monitoring:

   • Regularly check the cron_log.log and error.log files to ensure the script is running smoothly.

   • If any issues arise, investigate the error logs first to understand what might be causing the problem.

Troubleshooting:

• Too Many Database Connections:

  • Ensure that the script properly closes the database connection after each run to prevent connection leaks. The connection.close() method in the script ensures that the session is fully disconnected.

• Permission Issues:

  • If the metabase user encounters permission issues, verify that the GRANT SELECT command is executed after the view is created.