Add read-only SQL tool with Postgres access to basketball + contracts DBs

forgejo_admin commented

2026-03-31 01:07:58 +00:00

Owner

Type

Feature

Lineage

Standalone — evolved from api_get (#23). SQL access is more powerful than API endpoints for arbitrary data questions.

Repo

forgejo_admin/westside-ai-assistant

User Story

As Marcus (admin)
I want to ask Nemo any question about program data and get an answer
So that I don't need custom API endpoints for every possible query

Context

The generic api_get tool (#23) gives Nemo access to basketball-api REST endpoints. But contract data lives in westside-contracts (separate Postgres DB, no REST API). And many questions require JOINs or aggregations that no single endpoint serves. A read-only SQL tool lets Claude generate queries from natural language against the actual database schema.

Safety: a dedicated Postgres role with SELECT-only permissions, statement_timeout, and REVOKE on sensitive columns (password hashes, tokens, secrets). Claude writes the SQL, Nemo executes it read-only.

File Targets

Files to create:

app/database.py — async Postgres client (asyncpg or psycopg), read-only connection, statement_timeout enforcement
New tool definition in app/ai.py — db_query tool with read metadata

Files to modify:

app/ai.py — add db_query tool to TOOLS list, add schema description to SYSTEM_PROMPT
app/config.py — add DATABASE_URL env vars (basketball DB + contracts DB)

Files NOT to touch:

app/confirmation.py — no write confirmation needed (queries are read-only at DB level)
Write tools — unchanged

Infrastructure:

Create read-only Postgres role on CNPG cluster with SELECT only
REVOKE SELECT on sensitive columns/tables
SET statement_timeout = 5s on the role
Add connection string(s) to k8s secrets

Acceptance Criteria

db_query tool executes SELECT queries against basketball-api database
db_query tool executes SELECT queries against westside-contracts database
INSERT/UPDATE/DELETE/DROP are rejected at Postgres role level (not just prompt level)
statement_timeout kills queries over 5 seconds
Sensitive tables/columns are not queryable
System prompt includes schema index (table names, columns, relationships)
"How many contracts are signed?" returns correct count
"Which players haven't ordered jerseys?" works as a cross-table query

Test Expectations

Unit test: read-only enforcement (attempt INSERT, verify rejection)
Unit test: statement_timeout enforcement
Integration test: end-to-end GroupMe message → SQL → response
Run command: pytest tests/ -k test_db_query

Constraints

Read-only Postgres role. No application-level query filtering — trust the DB role.
Schema in system prompt, not discovered at runtime
asyncpg or psycopg — match whatever basketball-api uses if possible
Connection strings as k8s secrets, not hardcoded

Checklist

PR opened
Tests pass
No unrelated changes

project-westside-basketball
westside-ai-assistant #23 (api_get — predecessor)

### Type Feature ### Lineage Standalone — evolved from api_get (#23). SQL access is more powerful than API endpoints for arbitrary data questions. ### Repo `forgejo_admin/westside-ai-assistant` ### User Story As Marcus (admin) I want to ask Nemo any question about program data and get an answer So that I don't need custom API endpoints for every possible query ### Context The generic api_get tool (#23) gives Nemo access to basketball-api REST endpoints. But contract data lives in westside-contracts (separate Postgres DB, no REST API). And many questions require JOINs or aggregations that no single endpoint serves. A read-only SQL tool lets Claude generate queries from natural language against the actual database schema. Safety: a dedicated Postgres role with SELECT-only permissions, statement_timeout, and REVOKE on sensitive columns (password hashes, tokens, secrets). Claude writes the SQL, Nemo executes it read-only. ### File Targets Files to create: - `app/database.py` — async Postgres client (asyncpg or psycopg), read-only connection, statement_timeout enforcement - New tool definition in `app/ai.py` — `db_query` tool with read metadata Files to modify: - `app/ai.py` — add `db_query` tool to TOOLS list, add schema description to SYSTEM_PROMPT - `app/config.py` — add DATABASE_URL env vars (basketball DB + contracts DB) Files NOT to touch: - `app/confirmation.py` — no write confirmation needed (queries are read-only at DB level) - Write tools — unchanged Infrastructure: - Create read-only Postgres role on CNPG cluster with SELECT only - REVOKE SELECT on sensitive columns/tables - SET statement_timeout = 5s on the role - Add connection string(s) to k8s secrets ### Acceptance Criteria - [ ] `db_query` tool executes SELECT queries against basketball-api database - [ ] `db_query` tool executes SELECT queries against westside-contracts database - [ ] INSERT/UPDATE/DELETE/DROP are rejected at Postgres role level (not just prompt level) - [ ] statement_timeout kills queries over 5 seconds - [ ] Sensitive tables/columns are not queryable - [ ] System prompt includes schema index (table names, columns, relationships) - [ ] "How many contracts are signed?" returns correct count - [ ] "Which players haven't ordered jerseys?" works as a cross-table query ### Test Expectations - [ ] Unit test: read-only enforcement (attempt INSERT, verify rejection) - [ ] Unit test: statement_timeout enforcement - [ ] Integration test: end-to-end GroupMe message → SQL → response - Run command: `pytest tests/ -k test_db_query` ### Constraints - Read-only Postgres role. No application-level query filtering — trust the DB role. - Schema in system prompt, not discovered at runtime - asyncpg or psycopg — match whatever basketball-api uses if possible - Connection strings as k8s secrets, not hardcoded ### Checklist - [ ] PR opened - [ ] Tests pass - [ ] No unrelated changes ### Related - `project-westside-basketball` - westside-ai-assistant #23 (api_get — predecessor)

forgejo_admin referenced this issue

2026-03-31 01:08:06 +00:00

Upgrade Nemo model from Sonnet 4 to Sonnet 4.6 #28

forgejo_admin commented

2026-03-31 01:14:02 +00:00

Author

Owner

Scope Review: NEEDS_REFINEMENT

Review note: review-729-2026-03-30

Well-structured feature issue with all template sections present, but has cross-repo scope that exceeds the single-ticket threshold.

Issues found:

Story mismatch: story:WS-S5 maps to CNPG infra (superadmin) or dashboard stats (admin), not AI data queries. Needs a new story ID.
Missing arch note: No arch-westside-ai-assistant note exists in pal-e-docs.
Cross-repo work undocumented: Infra (Postgres role, network policies, k8s secrets) spans pal-e-platform + pal-e-services + pal-e-deployments but is bundled into this single-repo ticket.
Missing file targets: requirements.txt (asyncpg dependency) and pal-e-deployments/.../deployment-patch.yaml (DATABASE_URL env vars) not listed.
8 AC across 3+ repos: Exceeds 5-minute rule and three-thing limit.

Action: Route to skill-decompose-ticket for sub-board creation (5 suggested sub-tickets across infra, deploy, and app work streams).

## Scope Review: NEEDS_REFINEMENT Review note: `review-729-2026-03-30` Well-structured feature issue with all template sections present, but has cross-repo scope that exceeds the single-ticket threshold. **Issues found:** - **Story mismatch**: `story:WS-S5` maps to CNPG infra (superadmin) or dashboard stats (admin), not AI data queries. Needs a new story ID. - **Missing arch note**: No `arch-westside-ai-assistant` note exists in pal-e-docs. - **Cross-repo work undocumented**: Infra (Postgres role, network policies, k8s secrets) spans pal-e-platform + pal-e-services + pal-e-deployments but is bundled into this single-repo ticket. - **Missing file targets**: `requirements.txt` (asyncpg dependency) and `pal-e-deployments/.../deployment-patch.yaml` (DATABASE_URL env vars) not listed. - **8 AC across 3+ repos**: Exceeds 5-minute rule and three-thing limit. **Action**: Route to `skill-decompose-ticket` for sub-board creation (5 suggested sub-tickets across infra, deploy, and app work streams).

forgejo_admin referenced this issue

2026-04-03 17:38:04 +00:00

Replace Anthropic SDK with Ollama and add purpose-built read-only tools #29

forgejo_admin closed this issue

2026-04-03 17:38:46 +00:00