SQLite Database¶

BorgBoi uses SQLite for local metadata and cache storage. The database file is:

• ~/.borgboi/.database/borgboi.db

If BORGBOI_HOME is set, the path becomes:

• $BORGBOI_HOME/.borgboi/.database/borgboi.db

What SQLite Stores¶

SQLite stores:

• Repository metadata (repositories)
• Cached S3 statistics (s3_stats_cache)
• Daily backup stage timing history (backup_stage_timings)
• Storage schema version (schema_version)

It does not store the Borg repository contents themselves. Actual backup archives remain in Borg repositories on disk (and optionally S3).

Database Lifecycle¶

flowchart TD
    A[Start SQLiteStorage] --> B{DB file exists?}
    B -- Yes --> C[Open DB engine]
    B -- No --> D[Check legacy JSON data dirs]
    D --> E{Legacy data found?}
    E -- No --> F[Create empty SQLite DB]
    E -- Yes --> G[Create SQLite DB]
    G --> H[Migrate legacy repository JSON]
    G --> I[Migrate legacy S3 stats cache JSON]
    H --> C
    I --> C
    F --> C
    C --> J[Ensure tables exist]
    J --> K[Ensure schema_version row exists]
    K --> L[Use DB for reads and writes]

Schema Overview¶

erDiagram
    REPOSITORIES {
        INTEGER id PK
        STRING name UK
        STRING path
        STRING backup_target
        STRING hostname
        STRING os_platform
        DATETIME last_backup
        TEXT metadata_json
        INTEGER retention_keep_daily
        INTEGER retention_keep_weekly
        INTEGER retention_keep_monthly
        INTEGER retention_keep_yearly
        DATETIME last_s3_sync
        DATETIME created_at
        STRING passphrase
        STRING passphrase_file_path
        BOOLEAN passphrase_migrated
        DATETIME updated_at
    }

    S3_STATS_CACHE {
        INTEGER id PK
        STRING repo_name UK
        BIGINT total_size_bytes
        INTEGER object_count
        DATETIME last_modified
        DATETIME cached_at
    }

    BACKUP_STAGE_TIMINGS {
        INTEGER id PK
        STRING repo_name
        STRING stage_id
        BOOLEAN sync_enabled
        BIGINT duration_ms
        BOOLEAN succeeded
        DATETIME completed_at
    }

    SCHEMA_VERSION {
        INTEGER id PK
        INTEGER version
        DATETIME applied_at
    }

    REPOSITORIES ||--o| S3_STATS_CACHE : "repo name (app-level)"

Table Details¶

repositories¶

Primary table for BorgBoi repository metadata.

Additional unique constraint:

• UNIQUE(path, hostname) via uq_repo_path_hostname

This allows the same path string to exist on different hosts while preventing duplicates on the same host.

s3_stats_cache¶

Caches S3 statistics for each repository to reduce repeated API calls.

schema_version¶

Tracks storage schema version.

Current initialization behavior inserts version 1 if no row exists.

backup_stage_timings¶

Stores observed daily backup stage durations for the TUI so stage progress can be estimated more accurately over time.

This table is used by the daily backup screen to predict duration-weighted progress for the distinct backup stages (create, prune, compact, and optional sync). Predictions prefer recent successful samples for the current repository, then recent successful samples across all repositories, then built-in defaults.

See TUI for the user-facing behavior in the daily backup screen.

Operational Notes¶

• SQLite is opened with PRAGMA journal_mode=WAL to improve concurrent read behavior.
• Tables are created through SQLAlchemy metadata (create_all) during initialization.
• Deleting a repository also deletes its s3_stats_cache row in application logic.
• backup_stage_timings is append-only timing history; rows are recorded when daily backup stages complete successfully.
• Exclusions are not stored in SQLite; they are written to text files under ~/.borgboi/ (e.g. ~/.borgboi/{repo-name}_excludes.txt for repo-specific patterns and ~/.borgboi/excludes.txt for the shared default).

Common Introspection Queries¶

-- List tables
SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name;

-- Show repository rows
SELECT id, name, path, hostname, last_backup, updated_at
FROM repositories
ORDER BY name;

-- Show cached S3 stats
SELECT repo_name, total_size_bytes, object_count, cached_at
FROM s3_stats_cache
ORDER BY cached_at DESC;

-- Show schema version row
SELECT id, version, applied_at
FROM schema_version
ORDER BY id DESC;

-- Show recent daily backup stage timings
SELECT repo_name, stage_id, sync_enabled, duration_ms, succeeded, completed_at
FROM backup_stage_timings
ORDER BY completed_at DESC;