Proposal: Database file naming convention + lifecycle management for ~/.hermes/*.db

Problem

~/.hermes/ currently accumulates SQLite database files with inconsistent naming and no automated cleanup:

Additionally, exclusion lists for hermes_state.db are hardcoded in three separate places (profiles.py, profile_distribution.py, and backup tests) — any future rename or deprecation requires finding and updating all of them.

Proposal

1. Centralized database file registry

Introduce a REGISTERED_DB_FILES dict in hermes_state.py (or hermes_constants.py) as the single source of truth for all database files in ~/.hermes/:

REGISTERED_DB_FILES = {
    "state.db": {
        "purpose": "session metadata + message history + FTS5 full-text index",
        "module": "hermes_state.SessionDB",
        "legacy_names": ["hermes_state.db"],
        "auto_cleanup_legacy": True,
    },
    "response_store.db": {
        "purpose": "OpenAI Responses API LRU cache",
        "module": "gateway.platforms.api_server.ResponseStore",
        "legacy_names": [],
        "auto_cleanup_legacy": False,
    },
    "kanban.db": {
        "purpose": "Kanban multi-agent work queue",
        "module": "plugins.kanban",
        "legacy_names": [],
        "auto_cleanup_legacy": False,
    },
}

Benefits:

• One place to add/remove/rename database files
• Backup/export/profile-distribution exclusion lists derive from this instead of being hardcoded in 3+ files
• Clear lineage: state.db → hermes_state.db (legacy), making the deprecation chain explicit

2. Startup-time cleanup of legacy files

In SessionDB.__init__(), add a lightweight check:

# Auto-remove legacy database files (only empty/stale dbs)
for name, info in REGISTERED_DB_FILES.items():
    if info.get("auto_cleanup_legacy") and info.get("legacy_names"):
        for legacy in info["legacy_names"]:
            p = hermes_home / legacy
            if p.exists():
                p.unlink()
                logger.warning("Removed legacy database: %s", p)

3. Startup-time scan for unregistered .db files

After cleanup, scan ~/.hermes/ for any .db files not in the registry and warn:

registered = set(REGISTERED_DB_FILES.keys())
for info in REGISTERED_DB_FILES.values():
    registered.update(info.get("legacy_names", []))
for f in hermes_home.glob("*.db"):
    if f.name not in registered:
        logger.warning(
            "Unregistered database file: %s (%s). "
            "Add it to REGISTERED_DB_FILES in hermes_state.py to suppress this warning.",
            f.name, f.stat().st_size,
        )

This prevents silent bloat from future upgrades or plugin additions — new .db files appear in logs immediately.

4. Naming convention (long-term)

Eventual rename target:

~/.hermes/
├── sessions.db         ← state.db (session store)
├── response_cache.db   ← response_store.db (API cache)
├── kanban.db           (no change)
└── legacy/             ← migrated old files (not silently deleted)

Migration script: old file → copy to legacy/ → create new name as symlink or hardlink → next release drops symlink.

5. Consolidate exclusion lists

Currently hermes_state.db is excluded in:

• hermes_cli/profiles.py
• hermes_cli/profile_distribution.py
• tests/ (backup test)

These should all derive from the registry instead of duplicating the filename.

Implementation

Changes are confined to hermes_state.py (~30 lines added) and optionally gateway/platforms/api_server.py (LRU cleanup). No schema changes to existing databases.

• REGISTERED_DB_FILES dict — ~20 lines
• _auto_cleanup_legacy() + _warn_unregistered_db_files() — ~30 lines total
• Two lines inserted in SessionDB.__init__() to call both helpers

All changes are backward-compatible: database files are never renamed automatically, legacy cleanup only touches files explicitly marked as such, and the scan is warn-only.

A local prototype has been tested and verified: hermes_state.db (0-byte legacy ghost) was successfully auto-cleaned, and a test _test_unregistered.db triggered the expected warning.