Impact Dashboard

Functionalities Overview and API Points

This project has 2 main functionalities: serving the main backend for on-request data and managing scheduled tasks in order to aggregate data on regular time basis.

The Impact Dashboard Backend is a Django-based API microservice that is connected to the main Coldtivate backend. It provides functionalities for data aggregation, processing, and querying for cold storage monitoring. The API serves precomputed metrics stored in PostgreSQL tables and exposes them through structured endpoints.

This project also contains a python file (indicator_reporter.py) which is executed through cron jobs in order to fill in some statistical data on certain time periods. The cron jobs are triggered inside of a separate docker container. This handles the routine tasks that aggregate the data and update the appropriate tables.

API Endpoints

Cloning Git Repository

To begin development, clone the repository (this is the setup if you have connected Github with SSH keys, otherwise you will have to use an alternative git clone argument):

 git clone [email protected]:YourVirtualColdChainAssistant/Impact-Dashboard-Backend.git
 cd impact-dashboard-backend

Ensure you have the correct branch checked out:

git status
git checkout main

Recommended IDE Configuration

• PyCharm Professional (supports Django)
• VSCode (requires Django and Python extensions)

Database Setup

Please check the setup of the main backend API where it is described how to set up the database, however read through the documentation below to ensure all the required tables and views exist.

This project is dependable on tables and views to exist in the Database.

Intermediate tables

The “intermediate” tables were created specifically for the impact dashboard backend. They store precomputed metrics (computed by indicator_reporter.py) which are then served to the front-end of the Coldtivate app via the API endpoints. There are 3 intermediate tables and each one of them stores metrics for different screens of the Impact Dashboard. The tables (or called intermediate tables) used are:

• company_metrics - This table stores a single row of data for each company in the database. This table is updated daily by the indicator_reporter.py --view company python script, which is triggered in the daily-at-midnight.sh cron job script.

• cooling_unit_metrics - This intermediate table stores the daily utilization indicators for cooling units in the Coldtivate DB as a time series. The metrics can be sliced along the cooling units and time dimensions to serve the aggregated results to the front-end. This table serves the Aggregated and Comparison screens of the dashboard enabling the functionality of the cooling unit and calendar selectors on the front-end. The table also serves the ‘impact’ section of the Company screen specifically for the CO2 metrics. The table is updated daily by the indicator_reporter.py --view aggregated-comparison python script triggered in the daily-at-midnight.sh cron job script.

• impact_metrics - The impact_metrics stores all impact related metrics such as the farmer revenue evolution and food loss evolution. The data on the table is extracted from the user baseline and post-checkout surveys. Each row in the table corresponds to impact metrics for a single crop belonging to a farmer in a single cooling unit (i.e the index is farmer-coolingunit-crop). A user needs to have successfully completed a baseline survey and at least one post-checkout survey for a crop to have an entry in this table. The table is updated at monthly intervals by the indicator_reporter.py --view impact_metrics python script triggered in the impact-metrics.sh cron job script.

Required Views (in DB)

The views required are located in the views folder. These sql scripts need to be executed (only if the views don't exist already - please check database before that). The scripts and respectable views names are: - analytics_crate_movements.sql - create_relevant_checkins_view.sql

Local Deployment in Docker

To run the API within Docker:

1. Set up the .env file:

DB_NAME=impact_db
DB_USERNAME=user
DB_PASSWORD=pass
DB_HOST=db # or for docker sometimes it is better to use host.docker.internal
DB_PORT=5432

docker-compose up --build

http://localhost:8000

Alternative Local Setup

1. Setup the URL resolving to be using the localhost

echo "127.0.0.1 api.coldtivate.localhost air.coldtivate.localhost farmer.coldtivate.localhost impact.coldtivate.localhost" | sudo tee /etc/hosts
cat /etc/hosts # confirm it was added.

2. Update the code to include the DB credentials

The code is not written in the most optimal way for now, therefore you would have to search through it to see where do you have to set up the environment variables in order to connect to the database. The easiest way to do this is do a global search in your IDE for some of the key values, e.g. DB_HOST. If you do this you will find that the values need to be updated in the - DataSlicer.py - utils.py

3. Install Python Dependencies

Create a virtual environment and install dependencies:

python3 -m venv venv
source venv/bin/activate
pip install -r requirements.txt

4. Run the project

python3 main.py runserver

Project Structure - Directories and Files

Graphical Representation of Deployed Architecture

graph TD

%% Define server with Docker containers
subgraph Server [Server Node]
    A1["API Backend Container"]
    A2["Scheduler Container containing Cron Jobs"]
end

subgraph Database [Database]
    B1["Views"]
    B2["Intermediate Tables"]
end

%% Other components
C["Main API"]


%% Connections
A1 <--> C
A1 <--> Database
C <--> Database
A2 --> Database

Schedulers, Cron Jobs and ENV Variables

The cron jobs are deployed inside of docker containers. In order to check the list of containers you will have to run

docker ps

docker exec -it coldtivate-staging-impact_dashboard_scheduler-1 /bin/bash

The cron jobs definitions can be found at the location inside of the docker container at the location:

/etc/cron.d/

Deployment debugging scenario

Let's say you have already set up the whole project and it is in the cloud. If you have any errors for the metrics not being generated you need to find the error caused and check if the cron jobs are being triggered correctly. In the debugging scenario below you can learn how to do that.

1. SSH into the server.

2. Then list the docker container:

docker ps -a

Once you've identified the container, open an interactive bash session:

docker exec -it coldtivate-staging-impact_dashboard_scheduler-1 /bin/bash

4. Check Cron Jobs:

Navigate to the cron jobs definition folder and print the content of the crontab file:

cd /etc/cron.d
cat crontab

SHELL=/bin/bash
0 0 * * * root /etc/cron.d/scripts/daily-at-midnight.sh > /proc/1/fd/1 2>/proc/1/fd/2 # Daily at midnight
0 1 1 * * root /etc/cron.d/scripts/impact-metrics.sh > /proc/1/fd/1 2>/proc/1/fd/2 # First day every month

5. Check Scripts Content:

Navigate to the location of the scripts and first check the content for the script executed on daily basis:

cd /etc/cron.d/scripts
cat daily-at-midnight.sh

#!/bin/bash
cd /scheduler

# Export environment variables
export $(cat /proc/1/environ | tr '\0' '\n' | sed 's/^\([^=]*\)=\(.*\)$/\1="\2"/')

# Run Python scripts
python3 /scheduler/indicator_reporter.py --view aggregated-comparison
python3 /scheduler/indicator_reporter.py --view company

Now we check the monthly executed script content:

cat impact-metrics.sh

#!/bin/bash
cd /scheduler

# Export environment variables
export $(cat /proc/1/environ | tr '\0' '\n' | sed 's/^\([^=]*\)=\(.*\)$/\1="\2"/')

# Run Python script
python3 /scheduler/indicator_reporter.py --view impact_metrics

6. Create your own test script

The test script will be triggered by the cron job itself so you have complete context (same permissions same user) as when the scripts are executed.

cd /etc/cron.d/scripts

We need to set up a test cron script inside the script directory. Create the script file naming it testEnvVars.sh. The script should have the following permission and ownership only when listed with ls -la command (ignore the timestamp below):

-rw-r--r-- 1 root root 374 Nov 11 19:02 testEnvVars.sh

chmod +x /etc/cron.d/scripts/testEnvVars.sh

The script that we are creating will print out the environment variables. It will look like this:

#!/bin/bash

cd /scheduler

# Debug message to check execution
echo "Loading environment variables from /proc/1/environ"

# Read, parse, and export variables using sed and eval
eval $(cat /proc/1/environ | tr '\0' '\n' | sed 's/^\([^=]*\)=\(.*\)$/export \1="\2"/')

# Print environment variables to verify they are loaded
echo "Environment variables after loading:"
printenv

7. Call the script from the cron job

cd /etc/cron.d/scripts

Backup Crontab File:

cp crontab crontabBackup
ls

Add Test Script to Cron:

echo "* * * * * root /etc/cron.d/scripts/testEnvVars.sh > /proc/1/fd/1 2>/proc/1/fd/2" >> /etc/cron.d/crontab

Confirm addition:

cat crontab

8. Debugging Cron Output:

In another terminal, SSH again and start logging container outputs, this will print you what is happening:

docker logs -f coldtivate-staging-impact_dashboard_scheduler-1

9. Debugging finished

When you want to revert back to the original crontab and stop running the other script you will just delete the crontab file and rename the backup file back:

mv crontabBackup crontab