Feature request: Implement Automated Log Cleanup with Cron Job in Docker

[berntpopp]

Description:

We need to implement a mechanism to automatically delete old log entries from the database to ensure efficient storage management and performance. This task will involve creating a cron job that runs an R script to clean up the logs. The cron job will be integrated into our existing Docker-based infrastructure.

Steps to Implement:

1. Create R Script for Log Cleanup:

   • Write an R script (delete_old_logs.R) that connects to the MySQL database and deletes logs older than a specified period (e.g., 30 days).
   • Ensure the script is parameterized to allow flexibility in defining the log retention period.

2. Dockerfile for R Script:

   • Create a Dockerfile that sets up an environment to run the R script.
   • The Dockerfile should install R, necessary packages, and set up a cron job to execute the script at a specified interval (e.g., daily).

3. Integrate with Docker Compose:

   • Update the docker-compose.yml file to include a new service for the log cleanup cron job.
   • Ensure the new service has access to the necessary environment variables and volumes.

Detailed Implementation Steps:

1. R Script (delete_old_logs.R):

   #!/usr/bin/env Rscript
   library(DBI)
   library(RMySQL)
   
   # Database connection
   con <- dbConnect(RMySQL::MySQL(), 
                    dbname = Sys.getenv('MYSQL_DATABASE'),
                    host = 'mysql', 
                    user = Sys.getenv('MYSQL_USER'), 
                    password = Sys.getenv('MYSQL_PASSWORD'))
   
   # Define log retention period (e.g., 30 days)
   retention_period <- 30
   query <- paste0("DELETE FROM logs WHERE timestamp < NOW() - INTERVAL ", retention_period, " DAY")
   
   # Execute query
   dbExecute(con, query)
   
   # Close connection
   dbDisconnect(con)

2. Dockerfile for Log Cleanup Service:

      FROM r-base:latest
      
      # Install necessary R packages
      RUN R -e "install.packages(c('DBI', 'RMySQL'), repos='http://cran.us.r-project.org')"
      
      # Copy the R script into the container
      COPY delete_old_logs.R /scripts/delete_old_logs.R
      
      # Set up the cron job
      RUN apt-get update && apt-get install -y cron
      
      # Add the cron job
      RUN echo "0 3 * * * Rscript /scripts/delete_old_logs.R" >> /etc/crontab
      
      # Start the cron service
      CMD cron -f
      Integrate with Docker Compose:

3. Update the docker-compose.yml file to include a new service for the log cleanup cron job.
   Ensure the new service has access to the necessary environment variables and volumes.