[BUG] Direct SQLite Bypass in Admin API, CLI, and MCP

[sandy4242]

Describe the Bug

The Admin API endpoints, CLI query tools hp escalations/hp session, and the MCP tool list_recent_escalations bypass the active swappable storage backend (Postgres/Redis) and query a local SQLite database directly using sqlite3.connect().

To Reproduce

Steps to reproduce the behaviour:

1. Configure humane_proxy.yaml to use a non-default storage backend:

   storage:
     backend: "redis"
     redis:
       url: "redis://localhost:6379/0"

2. Log an escalation .
3. The escalation is correctly logged to Redis.
4. Run the query CLI tool: hp escalations (or query GET /admin/escalations).
5. Observe that the returned list is empty because the CLI/API is querying a local SQLite escalations.db file instead of the configured Redis store.

Expected Behaviour

The Admin API, CLI, and MCP server should query the active storage backend using the swappable storage factory (get_store()) instead of opening direct, hardcoded SQLite connections.

Actual Behaviour

The API (admin.py), CLI (cli.py), and MCP Server (mcp_server.py) import sqlite3 and call sqlite3.connect() directly to retrieve escalations, calculate statistics, and delete session logs.

Environment

• OS: (e.g. Ubuntu 22.04, Windows 11, macOS 14)
• Python version: (e.g. 3.11.5)
• HumaneProxy version: (run humane-proxy version)
• Install extras: (e.g. [ml], [mcp], [all], or none)

[aarushlohit]

/claim

I'd like to work on this bug.

Plan:

• Audit the storage abstraction layer and active backend selection flow.
• Trace Admin API, CLI, and MCP query paths for escalation/session retrieval.
• Verify where direct SQLite access bypasses the configured storage backend.
• Reproduce the inconsistency using a non-SQLite backend configuration.
• Refactor affected query paths to use the shared storage factory (get_store()).
• Preserve existing API, CLI, and MCP behavior while ensuring backend consistency.
• Validate Redis/Postgres-backed escalation retrieval before submitting the PR.
  #GSSOC26

[sandy4242]

@Vishisht16 my approach,
The Solution

• Leverage the Storage Factory: Replace direct sqlite3 imports and connection calls with the package's existing get_store() factory function. This function automatically determines whether the user is using SQLite, PostgreSQL, or Redis, and returns the appropriate storage handler.
• Standardize Data Retrieval: Update the Admin API, CLI, and MCP server tools to interact with the database using the standard methods defined in the storage interface:
• For Listing Logs: Call the active store's .query() and .count() methods to retrieve paginated records and count items.
• For Detail Views: Use .get_by_id() to fetch a single escalation record.
• For Erasing Data: Use .delete_session() to ensure session data is removed from the active database.
• Handle Advanced Analytics Gracefully: The Admin API's /stats endpoint requests complex metrics (such as hourly breakdowns, historical day-by-day counts, and top sessions) that are not natively supported by all storage interfaces (like Redis). The solution is to:
• Inspect the type of the active store at runtime.
• If it is a SQL-based store (SQLite or PostgreSQL), execute the advanced queries using the active store's connection details.
• If it is a key-value store (like Redis), calculate the metrics gracefully in-memory or return a subset of statistics to prevent full-scan performance degradation.
• Refactor CSV Export: Update the export endpoint to retrieve records via .query() from the active store, and stream the formatted rows using Python's standard csv library rather than relying on a direct SQLite cursor fetch.

[aarushlohit]

can i work

[aarushlohit]

please add gssoc:approved label and critical label too

[Vishisht16]

@sandy4242 good catch but the approach is somewhat vague. Assigning it to you but take extra care on the /admin/stats endpoint and also check #5 since that could affect this issue too.

[rashi-gupta-08]

hello @Vishisht16
I'm Rashi Gupta, GSSoC 2026 contributor (AI/Agents + Open Source tracks). I've read the full thread, every piece of your feedback, CONTRIBUTING.md, and the codebase carefully.
please assign this issue to me

[Vishisht16]

@rashi-gupta-08 assigned to you
Please work on this issue once #42 is reviewed and merged.