How SQLite Backup and Restore Works in TREK: Complete Technical Guide

TREK implements SQLite backup and restore by packaging the live travel.db database into a compressed ZIP archive alongside encryption keys and user uploads, then validates and atomically swaps the database file during restoration.

All persistent data in TREK resides in a single SQLite file located at data/travel.db. The backup subsystem, implemented in server/src/services/backupService.ts and exposed via server/src/nest/backup/backup.controller.ts, handles atomic archiving, integrity verification, and safe restoration to prevent data corruption.

Creating a Backup (SQLite to ZIP)

The backup creation process ensures database consistency before archiving and bundles all necessary assets for a complete restoration.

WAL Checkpoint for Consistency

Before archiving, the service forces SQLite to flush its write-ahead log to ensure the on-disk file is fully consistent:

try { db.exec('PRAGMA wal_checkpoint(TRUNCATE)'); } catch (e) {}

This pragma truncates the WAL file and persists all pending transactions to travel.db before the file is copied into the archive.

Archive Assembly

The service creates a ZIP archive with maximum compression (zlib:{level:9}) and sequentially adds:

1. The database file – data/travel.db is added as travel.db in the archive root
2. Encryption key – If present, .encryption_key is bundled to preserve encrypted secrets
3. User uploads – All files under uploads/ are included, while caches and existing backups are explicitly excluded to prevent archive bloat

The resulting file is written to data/backups/ with a timestamped filename pattern (backup-*.zip or auto-backup-*.zip), and the API returns a BackupInfo object containing the filename, size, and creation timestamp.

Restoring a Backup (ZIP to SQLite)

Restoration performs multi-layer validation before atomically replacing the live database to prevent corruption from invalid archives.

Size Validation and Extraction

The service first calculates the total uncompressed size of all ZIP entries using unzipper. If the sum exceeds MAX_BACKUP_DECOMPRESSED_SIZE (5 GB), the operation aborts immediately to prevent disk exhaustion. Valid archives are extracted to a temporary directory (data/restore-<timestamp>).

Database Integrity Verification

Before accepting the backup, TREK performs three validation checks in backupService.ts:

1. Presence check – The archive must contain travel.db at the root level
2. Integrity check – The file is opened read-only with better-sqlite3 and PRAGMA integrity_check is executed; any result other than ok causes immediate failure
3. Schema validation – The service verifies the presence of core TREK tables (users, trips, trip_members, places, days) to ensure the file is a valid TREK database and not an arbitrary SQLite file

Atomic Database Swap

Once validated, the restoration proceeds atomically:

• The existing travel.db and its -wal/-shm sidecar files are deleted
• The validated travel.db from the archive is copied into data/
• If the archive contains .encryption_key, it overwrites the server's key file
• The uploads/ directory is replaced entirely: existing files are deleted and archive contents are copied

Post-Restore Initialization

After the file swap, the global database connection is reinitialized via reinitialize(), and the in-process permissions cache is cleared to reflect the new database state. The temporary extraction directory is removed, and the API returns { success: true }.

API Usage Examples

Creating and Downloading Backups

Create a backup via the admin API:

curl -X POST -H "Authorization: Bearer <admin-jwt>" \
     https://your-trek.example.com/api/backup/create

Download the resulting archive:

curl -OJ -H "Authorization: Bearer <admin-jwt>" \
     https://your-trek.example.com/api/backup/download/backup-2024-10-22T14-30-00.zip

Restoring from Backup

Restore a server-side backup file:

curl -X POST -H "Authorization: Bearer <admin-jwt>" \
     https://your-trek.example.com/api/backup/restore/backup-2024-10-22T14-30-00.zip

Upload and restore an external backup file:

curl -X POST -H "Authorization: Bearer <admin-jwt>" \
     -F "backup=@/path/to/backup.zip" \
     https://your-trek.example.com/api/backup/upload-restore

Programmatic Usage

Interact with the backup service directly in TypeScript:

import * as backup from './services/backupService';

// Create backup programmatically
const info = await backup.createBackup();
console.log('Created:', info.filename, info.sizeText);

// Restore from file path
const result = await backup.restoreFromZip('/tmp/backup.zip');
if (!result.success) {
  console.error('Restore failed:', result.error);
}

Rate Limiting and Security Controls

The backup controller enforces rate limiting via BackupService.checkRateLimit, tracking requests per IP in a rolling one-hour window (BACKUP_RATE_WINDOW).

Filename validation restricts operations to backup-*.zip or auto-backup-*.zip patterns via isValidBackupFilename. Upload size is capped by the BACKUP_UPLOAD_LIMIT_MB environment variable (defaulting to 500 MiB) to prevent resource exhaustion.

Summary

• Atomic archiving – TREK uses PRAGMA wal_checkpoint(TRUNCATE) to ensure SQLite consistency before bundling travel.db, encryption keys, and uploads into a compressed ZIP
• Validation layers – Restoration enforces 5 GB size limits, SQLite integrity checks, and schema validation against required tables (users, trips, trip_members, places, days)
• Safe swapping – The live database is replaced atomically after extraction to a temporary directory, with automatic connection reinitialization and permissions cache clearing
• Rate protection – Built-in rate limiting and filename validation prevent abuse of backup endpoints

Frequently Asked Questions

How does TREK ensure the SQLite database is not corrupted during backup?

TREK executes PRAGMA wal_checkpoint(TRUNCATE) immediately before archiving, forcing SQLite to persist all pending write-ahead log entries to disk and truncate the WAL file. This ensures the travel.db file on disk is in a consistent state before being copied into the ZIP archive, preventing backup corruption from uncommitted transactions.

What is the maximum size for backup uploads and extractions?

Uploads are limited by the BACKUP_UPLOAD_LIMIT_MB environment variable (default 500 MB). During restoration, the service calculates the total uncompressed size of all ZIP entries and aborts if the total exceeds MAX_BACKUP_DECOMPRESSED_SIZE (5 GB), protecting the server from ZIP bomb attacks and disk space exhaustion.

Why does TREK include the encryption key in the backup archive?

The .encryption_key file is bundled with the database when present because TREK uses it to encrypt sensitive data stored in SQLite. Without this key, the encrypted values in travel.db would be unrecoverable even if the database file were restored successfully. The key is only included when using file-based encryption key storage.

What happens if a restore operation fails midway through?

If validation fails (integrity check, missing tables, or size limits), the temporary extraction directory is removed and the live database remains untouched. If the database swap fails after validation, the service attempts to reinitialize the connection; if that fails, it returns an error indicating that a manual server restart is required to recover the database state.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}