> For the complete documentation index, see [llms.txt](https://docs.biglawinvestor.com/knowledge-base/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.biglawinvestor.com/knowledge-base/company/hermes/14.07-consolidated-financial-reporting.md).

# 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:

     ```bash
     #!/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:

     ```bash
     */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.