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)
• 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
    }

    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.

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.
• Exclusions are not stored in SQLite; they are written to text files under ~/.borgboi/data/exclusions/.

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;