Skip to content

Scheduled Tasks

There are two scheduled jobs running in production, both implemented as Docker services (no host-level cron).

1. SQLite Database Backup

What it does

Every night at 2 AM (Asia/Kuala_Lumpur / UTC+8) the sqlite-backup container:

  1. Runs an initial backup on container start (so you always have a fresh backup after a deploy)
  2. Calculates time until next 2 AM and sleeps
  3. At 2 AM: performs a safe backup using SQLite's .backup command (WAL-safe)
  4. Verifies integrity with PRAGMA integrity_check
  5. Compresses with gzip -9
  6. Uploads to Linode Object Storage: s3://<LINODE_BUCKET>/sqlite-backups/daily/
  7. Prunes old daily backups, keeping the most recent N (default: 20, controlled by BACKUP_RETENTION_DAILY)
  8. On Sundays: copies the daily backup to s3://<LINODE_BUCKET>/sqlite-backups/weekly/ with a week-number filename

Relevant files

File Purpose
scripts/backup-cron.sh Container entrypoint; sleep-loop scheduler
scripts/sqlite-backup.sh The actual backup logic
scripts/Dockerfile.backup Alpine 3.19 image; installs sqlite, s3cmd, tzdata

Configuration (from .env)

Variable Default Purpose
LINODE_ACCESS_KEY — Linode Object Storage access key
LINODE_SECRET_KEY — Linode Object Storage secret key
LINODE_BUCKET zygy-backups-service Target bucket name
LINODE_REGION sg-sin-1 Region (sg-sin-1.linodeobjects.com)
BACKUP_RETENTION_DAILY 20 Number of daily backups to keep

Storage paths in Linode Object Storage

s3://<LINODE_BUCKET>/sqlite-backups/daily/    ← rolling 20 daily backups
s3://<LINODE_BUCKET>/sqlite-backups/weekly/   ← one per week (Sundays)

Checking backup status

ssh zygy@172.237.81.37
docker-compose logs sqlite-backup

Look for lines like: 

[2025-11-30 02:00:01] Backup completed successfully
[2025-11-30 02:00:01] Backup uploaded to Object Storage

2. Restoring from Backup

Use the scripts/restore-backup.sh script. It must be run on the VPS (not inside a container) because it calls docker-compose.

ssh zygy@172.237.81.37
cd $PROJECT_PATH

# List available backups (requires s3cmd and Linode credentials in env)
export LINODE_ACCESS_KEY=...
export LINODE_SECRET_KEY=...
export LINODE_REGION=sg-sin-1
export LINODE_BUCKET=zygy-backups-service

s3cmd --access_key="$LINODE_ACCESS_KEY" \
      --secret_key="$LINODE_SECRET_KEY" \
      --host="$LINODE_REGION.linodeobjects.com" \
      --host-bucket="%(bucket)s.$LINODE_REGION.linodeobjects.com" \
      ls s3://$LINODE_BUCKET/sqlite-backups/daily/

# Restore a specific backup
./scripts/restore-backup.sh csv_data_2025-11-30_020000.db.gz daily

What the restore script does

  1. Stops backend-mcpserver and backend-vectorindexing
  2. Downloads the .gz file from Linode Object Storage to /tmp/sqlite_restore/
  3. Decompresses it
  4. Renames the current csv_data.db to csv_data.db.backup-<timestamp> (safety copy)
  5. Moves the restored file to /mnt/blockstorage/zygy-data/csv_data.db
  6. Sets owner 1000:1000 and permissions 644
  7. Restarts the stopped services

Requires Linode env vars

The restore script expects LINODE_ACCESS_KEY, LINODE_SECRET_KEY, LINODE_REGION, and LINODE_BUCKET to be set in the environment. Export them before running.

3. Daily Summary Email

The backend-dailysummary service generates and emails a daily business summary.

Item Value
Schedule 8 AM Asia/Kuala_Lumpur (SUMMARY_SCHEDULE_HOUR=8 in .env)
Delivery Email via SMTP
SMTP host smtppro13.mschosting.com:465 (Exabytes hosting)
Public URL daily.zygy.com

To check if summaries are being sent:

docker-compose logs backend-dailysummary