Algis DumbrisandClaude Opus 4.6
b6fc298595
Two features: 1. SQL query action for agents via execute MCP tool — read-only, curated views, LIMIT/timeout, SELECT-only validation 2. Split read/write SQLite connection pools — writeDB (1 conn) + readDB (8 conns) to eliminate SQLITE_BUSY Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
110 lines
6.4 KiB
Markdown
110 lines
6.4 KiB
Markdown
# Feature Specification: SQL Query Interface + Split Connection Pools
|
|
|
|
**Feature Branch**: `015-sql-query-split-pools`
|
|
**Created**: 2026-03-26
|
|
**Status**: Draft
|
|
**Input**: Architecture research from reactive agent triggering session
|
|
|
|
## Assumptions
|
|
|
|
- SQL query interface is exposed as a `query` action via the existing `execute` MCP tool, not a new top-level MCP tool
|
|
- Queries are read-only (enforced via `PRAGMA query_only=ON` on a dedicated connection)
|
|
- Agents query curated SQL views (not raw tables) that bake in per-agent access control
|
|
- Views: `my_messages`, `my_channels`, `channel_messages` — parameterized by the authenticated agent's name
|
|
- Results are automatically limited to 100 rows; agent can specify lower limit
|
|
- Query timeout: 5 seconds max
|
|
- Only SELECT statements allowed (validated before execution); WITH (CTEs) permitted
|
|
- Split connection pools: writeDB (MaxOpenConns=1) for all INSERT/UPDATE/DELETE, readDB (MaxOpenConns=8) for all SELECT
|
|
- Both pools share the same SQLite file with WAL mode
|
|
- The read pool uses `PRAGMA query_only=ON` for safety
|
|
- No schema changes needed — this is a runtime architecture change
|
|
- Agent SQL queries use the read pool
|
|
|
|
## User Scenarios & Testing *(mandatory)*
|
|
|
|
### User Story 1 - Agent Queries Messages via SQL (Priority: P1)
|
|
|
|
An agent connected via MCP uses the `execute` tool to run a SQL query against its accessible messages. For example: "Show me all messages in #news-mcpproxy from the last 3 days with priority >= 7".
|
|
|
|
**Why this priority**: Removes the expressiveness ceiling — agents can compose arbitrary queries instead of being limited to fixed API endpoints.
|
|
|
|
**Independent Test**: Agent calls `execute` with `call('query', {sql: "SELECT * FROM my_messages WHERE channel_name = 'news-mcpproxy' AND priority >= 7 ORDER BY created_at DESC LIMIT 5"})` and gets results.
|
|
|
|
**Acceptance Scenarios**:
|
|
|
|
1. **Given** an authenticated agent, **When** it calls `query` with a valid SELECT, **Then** it receives JSON results with column names and rows.
|
|
2. **Given** an agent, **When** it runs a query referencing `my_messages`, **Then** it only sees messages it has access to (own DMs + joined channels).
|
|
3. **Given** an agent, **When** it runs `INSERT INTO messages ...`, **Then** the query is rejected with "only SELECT statements allowed".
|
|
4. **Given** an agent, **When** it runs a query without LIMIT, **Then** results are automatically capped at 100 rows.
|
|
5. **Given** an agent, **When** it runs a slow query (> 5s), **Then** the query is cancelled and an error is returned.
|
|
|
|
---
|
|
|
|
### User Story 2 - Split Read/Write Connection Pools (Priority: P1)
|
|
|
|
SynapBus uses separate connection pools for reads and writes to eliminate SQLITE_BUSY errors under concurrent agent load.
|
|
|
|
**Why this priority**: Directly fixes the SQLITE_BUSY errors observed during reactive agent runs.
|
|
|
|
**Independent Test**: Run concurrent read and write operations; verify no SQLITE_BUSY errors and writes serialize correctly.
|
|
|
|
**Acceptance Scenarios**:
|
|
|
|
1. **Given** concurrent agents sending messages, **When** writes happen simultaneously, **Then** they serialize through the single-writer pool without SQLITE_BUSY.
|
|
2. **Given** a write in progress, **When** a read query arrives, **Then** the read executes immediately on the read pool (WAL mode).
|
|
3. **Given** the read pool, **When** any write operation is attempted, **Then** it fails (query_only=ON enforcement).
|
|
|
|
---
|
|
|
|
### User Story 3 - Agent Queries Channel Messages (Priority: P2)
|
|
|
|
An agent queries messages from a specific channel with rich filtering — date ranges, keywords, reactions, workflow states.
|
|
|
|
**Why this priority**: Enables the social-commenter to query #opportunities channel structured data via SQL.
|
|
|
|
**Acceptance Scenarios**:
|
|
|
|
1. **Given** an agent that has joined #opportunities, **When** it queries `SELECT * FROM channel_messages WHERE channel_name = 'opportunities' AND created_at > datetime('now', '-3 days')`, **Then** it sees messages from that channel.
|
|
2. **Given** an agent that has NOT joined a private channel, **When** it queries that channel's messages, **Then** no results are returned.
|
|
|
|
---
|
|
|
|
### Edge Cases
|
|
|
|
- Query with syntax error returns a clear error message, not a crash
|
|
- Query referencing non-existent view returns "no such table" error
|
|
- Empty result set returns empty array, not null
|
|
- Very large result (>100 rows) is truncated with a warning
|
|
- Concurrent SQL queries from multiple agents don't interfere
|
|
|
|
## Requirements *(mandatory)*
|
|
|
|
### Functional Requirements
|
|
|
|
- **FR-001**: System MUST provide a `query` action callable via the `execute` MCP tool that accepts a SQL string and returns results as JSON.
|
|
- **FR-002**: System MUST enforce read-only execution — no INSERT, UPDATE, DELETE, DROP, ALTER, or PRAGMA statements allowed.
|
|
- **FR-003**: System MUST expose curated views (`my_messages`, `my_channels`, `channel_messages`) that enforce per-agent access control.
|
|
- **FR-004**: System MUST automatically limit query results to 100 rows (or fewer if agent specifies).
|
|
- **FR-005**: System MUST cancel queries that exceed 5 seconds.
|
|
- **FR-006**: System MUST use a separate read-only connection pool (MaxOpenConns=8) for all SELECT operations.
|
|
- **FR-007**: System MUST use a single-writer connection pool (MaxOpenConns=1) for all write operations.
|
|
- **FR-008**: System MUST configure `PRAGMA query_only=ON` on the read pool connections.
|
|
- **FR-009**: System MUST validate SQL statements before execution — only SELECT and WITH (CTE) prefixes allowed.
|
|
- **FR-010**: System MUST return query results as `{columns: [...], rows: [[...], ...], row_count: N, truncated: bool}`.
|
|
|
|
### Key Entities
|
|
|
|
- **Read Pool**: SQLite connection pool with MaxOpenConns=8, query_only=ON, for all SELECT operations including agent SQL queries.
|
|
- **Write Pool**: SQLite connection pool with MaxOpenConns=1, for all INSERT/UPDATE/DELETE operations.
|
|
- **Agent Views**: SQL views parameterized by agent name that enforce access control.
|
|
|
|
## Success Criteria *(mandatory)*
|
|
|
|
### Measurable Outcomes
|
|
|
|
- **SC-001**: Agents can execute arbitrary SELECT queries against curated views and receive structured JSON results within 5 seconds.
|
|
- **SC-002**: No SQLITE_BUSY errors under concurrent 4-agent workload (verified by running all 4 reactive agents simultaneously).
|
|
- **SC-003**: Write operations on the read pool are rejected at the SQLite engine level.
|
|
- **SC-004**: Query results are limited to 100 rows maximum.
|
|
- **SC-005**: All 28+ existing test packages continue to pass with the split pool architecture.
|