Add database management API and automatic migration backups

[wpfleger96]

Summary

This PR adds a database management API that enables users to backup, restore, and monitor their session database, along with automatic safety backups before schema migrations. This protects users from data loss during upgrades and provides easy recovery options if migrations fail.

Users previously had no visibility into their database state and no easy way to recover from failed migrations or accidental data loss. The database file location was opaque, backups required manual file copying, and schema migrations happened silently without safety nets. This created risk during upgrades and made troubleshooting difficult.

Implementation adds 6 new CLI commands and comprehensive backup infrastructure:

• goose db status - shows database location, size, schema version, session/message counts, and backup information
• goose db backup [--output <NAME>] - creates validated manual backups with optional custom output paths
• goose db restore <FILENAME|PATH> [--force] - restores from backups using either filename (e.g., backup_20251030_202327.db) or full path, with confirmation prompts and safety backups
• goose db list-backups [--format <json|table>] - displays all available backups with size and age in table or JSON format
• goose db delete-backup [FILES...] [--all] [--cleanup] [--force] - removes backup files (supports multi-file deletion, --all flag, and --cleanup for orphaned SQLite wal/shm auxiliary files)
• goose db path - prints the database file location for scripting

Implementation details:

• Modified SessionManager::run_migrations() to automatically create validated backups before applying schema migrations using format pre_migration_v{old}_to_v{new}_{timestamp}.db
  • Automatic backups are best-effort and will allow migrations to proceed if backup fails (e.g. in case of filesystem error)
• Added backup validation with file size verification and SQLite integrity checks (PRAGMA quick_check)
• Created backup directory management in Paths::backup_dir() using platform-specific data directories
• Added REST API endpoints (GET /database/status, GET /database/backups, POST /database/backup, POST /database/restore, DELETE /database/backups/delete) for desktop app integration with full OpenAPI spec generation
  • API endpoints include operation locking (409 Conflict response) to prevent concurrent database operations

Type of Change

• [x] Feature
• [ ] Bug fix
• [ ] Refactor / Code quality
• [ ] Performance improvement
• [ ] Documentation
• [ ] Tests
• [ ] Security fix
• [ ] Build / Release
• [ ] Other (specify below)

Testing

Manually tested, and also added new test test_backup_restore_round_trip

[github-actions[bot]]

PR Preview Action v1.6.0 :---: Preview removed because the pull request was closed. 2025-11-06 21:54 UTC

[alexhancock]

I appreciate all the work here! But I am not sure adding functionality like this to goose's command line tool feels right. It's a lot of overhead for goose to have six commands related to this added, and we also don't want to maintain a comprehensive db management layer in goose I don't think.

I made a small change recently to show the user the location of the sessions.db via the goose info command, and then I think for someone who wants to manually manage the db, they could just use https://sqlite.org/cli.html

What do you think?

[wpfleger96]

yeah that's fair!

the reason behind this was to have goose automatically backup the session DB before migrations are applied, and to have the ability to restore if necessary. I've opened 2 PRs (#5279 and #5419) recently that added DB migrations and ended up having a few issues locally when switching from main goose, my feature branch goose, and installed version of goose. I probably should have manually backed up before testing locally, but it would have been nice for goose to do it for me

maybe this is over engineered and automated pre-migration backups are more trouble than they're worth. instead I direct this effort toward simplifying and improving my goose-db-helper script for my use case, and anything more complicated than that can be done manually with sqlite3 CLI like you said. what do you think @alexhancock ?

[alexhancock]

@wpfleger96 yes, i think something like an external script which has goose specific migration utils could be good for powerusers. and then standard sqlite3 cli for anything where user wants to take full control.

so good to close this out then and make a new pr with a script if needed?

[wpfleger96]

@alexhancock sounds good! just to clarify - the script already exists and this was my attempt at replacing it by building the functionality into goose