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

> Discover how TREK backs up and restores SQLite databases by creating compressed archives, ensuring data integrity and seamless recovery. Learn the technical details.

- Repository: [Maurice/TREK](https://github.com/mauriceboe/TREK)
- Tags: internals
- Published: 2026-07-09

---

**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`](https://github.com/mauriceboe/TREK/blob/main/server/src/services/backupService.ts) and exposed via [`server/src/nest/backup/backup.controller.ts`](https://github.com/mauriceboe/TREK/blob/main/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:

```typescript
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`](https://github.com/mauriceboe/TREK/blob/main/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:

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

```

Download the resulting archive:

```bash
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:

```bash
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:

```bash
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:

```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.