Interaction logging to Postgres

forgejo_admin commented

2026-03-29 20:12:16 +00:00

Owner

Type

Feature

Lineage

Standalone — operational observability for improving Nemo over time. Blocked by #21 (Postgres DB + NetworkPolicy infra).

Repo

forgejo_admin/westside-ai-assistant

User Story

As Lucas (platform operator)
I want every Nemo interaction logged to a database
So that I can analyze what Marcus asks, how Nemo responds, track token costs, and identify when tools are ready to graduate from read-only to read-write

Context

Every GroupMe message that Nemo processes gets logged: who asked, what they asked, what tools were called, what was returned, how many tokens were used. This is the training data for improving the system. Uses the existing CNPG Postgres cluster (pal-e-postgres). Database nemo and user nemo provisioned by #21. NetworkPolicy for postgres access provisioned by #21.

File Targets

Files the agent should create:

app/logging_db.py — async database connection (asyncpg), log_interaction() function
migrations/001_interaction_log.sql — CREATE TABLE interaction_log (id, timestamp, group_id, sender_name, message, bot_response, tools_called JSONB, tool_results JSONB, model, input_tokens, output_tokens, duration_ms)

Files the agent should modify:

app/ai.py — after processing each message, call log_interaction() with all fields. Token counts from Anthropic response usage.input_tokens and usage.output_tokens. For multi-turn tool_use loops, sum tokens across all API calls in the interaction.
app/config.py — add database_url: str = "" setting
requirements.txt — add asyncpg

Files the agent should NOT touch:

app/basketball.py, app/groupme.py, app/confirmation.py

Acceptance Criteria

migrations/ directory created with 001_interaction_log.sql
interaction_log table schema includes all fields: id, timestamp, group_id, sender_name, message, bot_response, tools_called (JSONB), tool_results (JSONB), model, input_tokens, output_tokens, duration_ms
Every processed message logged with all fields populated
Token counts summed across all Anthropic API calls in a multi-turn tool_use loop
Logging failure does NOT crash the bot — log errors to stdout, continue serving
DATABASE_URL configurable via env var, empty string disables logging gracefully

Test Expectations

Unit test: log_interaction writes correct fields (mock asyncpg)
Unit test: logging failure is caught and doesn't propagate
Unit test: ai.py calls log_interaction after each response
Unit test: empty DATABASE_URL skips logging without error
Run command: pytest tests/ -v

Constraints

Use asyncpg (not SQLAlchemy — keep it simple, one table)
Logging is fire-and-forget — never block the response to Marcus
Token counts come from the Anthropic API response (usage.input_tokens, usage.output_tokens)
#21 must be completed first (database + NetworkPolicy + k8s secret)

Checklist

PR opened
Tests pass
No unrelated changes

project-westside-ai-assistant — parent project
Blocked by: forgejo_admin/westside-ai-assistant#21

### Type Feature ### Lineage Standalone — operational observability for improving Nemo over time. **Blocked by #21** (Postgres DB + NetworkPolicy infra). ### Repo `forgejo_admin/westside-ai-assistant` ### User Story As Lucas (platform operator) I want every Nemo interaction logged to a database So that I can analyze what Marcus asks, how Nemo responds, track token costs, and identify when tools are ready to graduate from read-only to read-write ### Context Every GroupMe message that Nemo processes gets logged: who asked, what they asked, what tools were called, what was returned, how many tokens were used. This is the training data for improving the system. Uses the existing CNPG Postgres cluster (pal-e-postgres). Database `nemo` and user `nemo` provisioned by #21. NetworkPolicy for postgres access provisioned by #21. ### File Targets Files the agent should create: - `app/logging_db.py` — async database connection (asyncpg), `log_interaction()` function - `migrations/001_interaction_log.sql` — CREATE TABLE interaction_log (id, timestamp, group_id, sender_name, message, bot_response, tools_called JSONB, tool_results JSONB, model, input_tokens, output_tokens, duration_ms) Files the agent should modify: - `app/ai.py` — after processing each message, call `log_interaction()` with all fields. Token counts from Anthropic response `usage.input_tokens` and `usage.output_tokens`. For multi-turn tool_use loops, sum tokens across all API calls in the interaction. - `app/config.py` — add `database_url: str = ""` setting - `requirements.txt` — add `asyncpg` Files the agent should NOT touch: - `app/basketball.py`, `app/groupme.py`, `app/confirmation.py` ### Acceptance Criteria - [ ] `migrations/` directory created with `001_interaction_log.sql` - [ ] interaction_log table schema includes all fields: id, timestamp, group_id, sender_name, message, bot_response, tools_called (JSONB), tool_results (JSONB), model, input_tokens, output_tokens, duration_ms - [ ] Every processed message logged with all fields populated - [ ] Token counts summed across all Anthropic API calls in a multi-turn tool_use loop - [ ] Logging failure does NOT crash the bot — log errors to stdout, continue serving - [ ] `DATABASE_URL` configurable via env var, empty string disables logging gracefully ### Test Expectations - [ ] Unit test: log_interaction writes correct fields (mock asyncpg) - [ ] Unit test: logging failure is caught and doesn't propagate - [ ] Unit test: ai.py calls log_interaction after each response - [ ] Unit test: empty DATABASE_URL skips logging without error - Run command: `pytest tests/ -v` ### Constraints - Use asyncpg (not SQLAlchemy — keep it simple, one table) - Logging is fire-and-forget — never block the response to Marcus - Token counts come from the Anthropic API response (`usage.input_tokens`, `usage.output_tokens`) - #21 must be completed first (database + NetworkPolicy + k8s secret) ### Checklist - [ ] PR opened - [ ] Tests pass - [ ] No unrelated changes ### Related - `project-westside-ai-assistant` — parent project - Blocked by: forgejo_admin/westside-ai-assistant#21

forgejo_admin commented

2026-03-29 22:35:28 +00:00

Author

Owner

Scope Review: NEEDS_REFINEMENT (re-review)

Review note: review-684-2026-03-28-v2

Re-review of board item #684. Previous review (review-684-2026-03-28) returned NEEDS_REFINEMENT with 4 recommendations. None have been addressed.

Outstanding issues:

[SCOPE] No infra prerequisite ticket exists for DB creation, NetworkPolicy, or k8s secret. This blocks #19 — the app code cannot connect to Postgres without this work. File on pal-e-services or pal-e-platform.
[BODY] migrations/ directory does not exist — ticket should note it must be created.
[BODY] Token counting needs clarification — tool_use loop makes multiple Anthropic API calls, total tokens should be summed.
[BODY] Add explicit "Blocked by: infra ticket" dependency reference in Constraints.

## Scope Review: NEEDS_REFINEMENT (re-review) Review note: `review-684-2026-03-28-v2` Re-review of board item #684. Previous review (`review-684-2026-03-28`) returned NEEDS_REFINEMENT with 4 recommendations. **None have been addressed.** Outstanding issues: - `[SCOPE]` No infra prerequisite ticket exists for DB creation, NetworkPolicy, or k8s secret. This blocks #19 — the app code cannot connect to Postgres without this work. File on pal-e-services or pal-e-platform. - `[BODY]` `migrations/` directory does not exist — ticket should note it must be created. - `[BODY]` Token counting needs clarification — tool_use loop makes multiple Anthropic API calls, total tokens should be summed. - `[BODY]` Add explicit "Blocked by: infra ticket" dependency reference in Constraints.