ADR-0023: Persist tool_result snapshots for recall

Status: Accepted
Date: 2026-05-20
Decider: Seth Shoultes (with ai-engineer cost-discipline review)
Author: Seth Shoultes

Context

The chat service today persists the final assistant text for each turn but discards the raw tool_result JSON returned by query_kpi_trend, query_site_data, and update_customer_brain once the turn closes. Within a turn the LLM sees the tool output; across turns it’s gone.

Three weeks later, when a user asks “what were those membership plans you showed me?”, the LLM has three options, all bad:

1. Re-query (the default). The original assistant message has rolled off the MAX_HISTORY_TURNS = 10 window. Even if it hadn’t, live data may have changed since — so the model returns current truth, not the historical snapshot the user is asking about.
2. Read from MEMORY (ADR 0016). Only works if the LLM happened to call update_customer_brain(target="memory", ...) during the original session. Not automatic.
3. Read from session wrap-up (ADR 0019). Wrap-ups are five-field distillations, not raw data.

The product premise — “an advisor that remembers what we discussed and can compare state over time” — depends on the system being able to recall snapshots, not just current truth. Today it cannot. This ADR adds that capability.

A related concern surfaced in PR #85 (on-demand site data tool). The user asked: “is the data that’s returned in the query saved somewhere for later reference?” — and the honest answer was “no.” This ADR closes that gap.

Decision

MemberIntel adds per-tool-call snapshot rows to a new message_tool_results table, written every time the chat service materializes a tool_result content block.

Schema

class MessageToolResult(Base):
    __tablename__ = "message_tool_results"

    id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    # Denormalized from conversation_id → conversations.user_id. Matches the
    # observed codebase convention for per-user OLTP tables (SessionLog,
    # UsageCounter, Conversation, Message, Site all carry user_id directly;
    # the pgvector-backed global brain is the only place that uses the
    # abstract tenant_id from ADR 0003). Lets GDPR / right-to-be-forgotten
    # deletes hit this table by a single indexed key without joining.
    user_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("users.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    message_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("messages.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    conversation_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("conversations.id", ondelete="CASCADE"),
        nullable=False,
    )
    tool_name: Mapped[str] = mapped_column(String(128), nullable=False)
    tool_input: Mapped[dict[str, Any]] = mapped_column(JSONB, nullable=False)
    tool_output: Mapped[dict[str, Any]] = mapped_column(JSONB, nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, default=lambda: datetime.now(UTC)
    )

    __table_args__ = (
        Index("ix_message_tool_results_conversation_recency", "conversation_id", "created_at"),
    )

Write site is the existing tool-dispatch loop in src/memberintel/api/chat/service.py (non-streaming) and src/memberintel/api/chat/sse.py (streaming) — one row per (tool_use_id → tool_result) pair, attached to the assistant Message that incorporated the tool call.

Recall — Option A (this ADR)

When loading conversation history, also load the K most-recent MessageToolResult rows for the conversation (default K = 5, hard budget 300 input tokens for the entire block) and inject a summarized “Previously fetched data” block into the system prompt:

Previously fetched (this conversation, snapshots — not live data):
- 2026-05-12 query_site_data(target="recent_members"): 8 members, last registered 2026-05-10
- 2026-05-12 query_kpi_trend(metric="active_members", granularity="week"): 7-week trend, +12%
- 2026-05-08 query_site_data(target="memberships"): 5 plans, Starter Pack 23 active

IMPORTANT: when referencing a row above, always prefix with "as of <date>".
If the user is asking about CURRENT state, call the tool fresh — do not
present these snapshots as live data.

The block is summarized, not pasted verbatim. Summarization is a deterministic Python function (src/memberintel/api/chat/snapshot_summary.py) — no LLM hop, no network call. The summarizer picks 1–2 signal fields per tool_name (e.g., for query_site_data(target="recent_members") it counts rows and reads the max registered_at). Full JSON stays in the table for explicit recall later if needed.

Both bounds (K and the 300-token budget) live as module-level constants in snapshot_summary.py. The token budget wins when K=5 condensed lines would exceed it; rows past the budget are dropped (oldest first).

When the LLM sees this block, it can reference historical snapshots (“as of May 12 you had 23 active members on Starter Pack”) without calling a tool. If the user explicitly asks for current truth, the LLM still calls the tool fresh.

Recall — Option B (deferred)

A recall_prior_tool_result(tool_name?, since_days?) LLM tool that returns the full JSON of a prior snapshot. Not built in this ADR. Option A first; promote to B if Option A misses cases real users hit.

Retention

• Default: keep all rows for 180 days from created_at, then a periodic job deletes.
• Per-conversation cap: keep at most 200 rows per conversation; older rows hard-deleted on insert (LRU on created_at).
• CASCADE: rows go away when their parent Message or Conversation is deleted (already enforced by FK).

Both bounds are conservative starting points. They can tighten without ADR sign-off; loosening or removing them needs a follow-up.

Sensitive data + GDPR / right-to-be-forgotten

Tool outputs contain real member data (emails, transaction amounts, plan names). These are already persisted elsewhere — members, transactions, etc. — so the marginal privacy exposure of this table is zero net new categories.

GDPR deletion is wired day one, not deferred: the user_id column declares ondelete="CASCADE" against users.id, so a user-record delete drops every row in message_tool_results for that user in a single SQL statement. No application-level sweep needed. When a formal delete_user_data() routine lands, it has nothing extra to do here — the cascade handles it.

Tenant scoping

This codebase’s observed convention — not formalized in any ADR — is that per-user OLTP tables carry user_id directly (Sites, Conversations, Messages, UsageCounters, SessionLogs). The abstract tenant_id partition key from ADR 0003 applies to the pgvector store (the global brain), not to per-user relational tables. message_tool_results follows the OLTP precedent: a denormalized user_id column with ondelete="CASCADE". This avoids a future backfill migration if/when per-user RLS or query patterns require single-key access, and matches how every neighboring table in db/models.py is shaped.

Evals (release gate)

Phase 1 does not ship without these four evals passing. The recall path’s failure mode is hallucination (“LLM presents an old snapshot as current truth”) — Risk #7 in SPEC §14 — and the citation-discipline requirement from SPEC §8.4 applies directly.

1. As-of-date adherence. Golden set: injection block contains a 7-day-old query_site_data snapshot. User asks “how many active members do I have?” Pass if model either says “as of ” + the historical number OR calls the tool for fresh data. Fail if model presents the snapshot as current with no date prefix.
2. Recall vs. re-query routing. Two paired prompts in the same conversation: “what plans did you show me?” (should recall) vs. “how many active members right now?” (should call tool). Both must route correctly.
3. Citation discipline. When recalling, the model must cite the original created_at and tool name from the injection block. Eval checks for the date string and query_* name in the response.
4. Cross-tenant leak. Synthetic two-user fixture; the injection block for user A must never contain a row from user B’s conversation, even if the join is malformed. Tested by querying the helper directly and asserting WHERE user_id = ? is in the SQL.

The exact system-prompt copy committed to in the “Recall — Option A” section above is what the as-of-date eval asserts against. Changing that copy requires re-running the eval.

Observability

Three counters/gauges added with Phase 1 (exported via the existing logging pipeline; promoted to dashboards in a follow-up):

• tool_results_persisted_total{tool_name} — counter, incremented on write.
• tool_results_injection_tokens — gauge, set per turn to the actual token count of the injection block.
• tool_results_retention_deletes_total — counter, incremented by the retention sweep job.

Without these we can’t audit the cost estimate above or detect storage drift before it bites.

Consequences

Positive:

• Closes the “what did we discuss” gap that’s the most concrete differentiator over generic chat tools.
• Makes query_site_data answers stickier — fewer re-queries per multi-turn thread on the same topic, which trims tool round-trips and token cost.
• Unblocks #87 (filters on query_site_data) — composable filters only feel composable if prior results are remembered.
• Audit trail of what the model fetched, useful for debugging “why did it say X.”

Negative / costs:

• Storage growth scales with tool-call volume. At ~1 KB per tool_output row × ~5 tool calls per active session × ~30 sessions/user/month, this is ~150 KB/user/month or ~2 MB/user/year — negligible at current scale, worth re-checking at 10k users.
• Larger system prompt = more input tokens per turn. K=5 condensed lines at ~25–35 tokens each lands at ~150–200 tokens; the 300-token budget cap bounds the worst case. At $3/MTok Sonnet input that’s ~$0.0006/turn × ~30 turns/user/mo = ~$0.018/user/mo — comfortably inside the SPEC §5.5 Pro envelope. Free/Haiku is cheaper still. Net positive when the recalled line saves a multi-hundred-token tool round-trip.
• Risk of stale-data confusion if the LLM presents an old snapshot as current. Mitigated by explicit timestamping in the prompt block (“as of ”) and a system-prompt instruction.

Mitigations:

• Retention bounds (180 days + 200/conversation) keep growth predictable.
• Prompt block always prefixes each line with the created_at date so the model can disambiguate snapshot vs. current.
• Sensitive-data deletion path documented and added to whatever right-to-be-forgotten work lands later.

Alternatives considered

Status quo — re-query every turn

Cheapest in storage and code. Rejected for the reason the issue exists: the model can’t honor “what we discussed” because the only thing persisted is the assistant’s final text, and that’s been compressed past the raw data point. Customer-felt as “you don’t remember what you just told me.”

Persist into MEMORY (ADR 0016) instead of a new table

The LLM already has a tool that writes durable insights. Why not have it write tool_result summaries into MEMORY on its own?

Rejected because MEMORY is opportunistic by design — the LLM decides what’s worth remembering. Tool results should be persisted deterministically so recall doesn’t depend on a model judgment call. Mixing them into MEMORY also blows past the per-tier MEMORY size caps from ADR 0018 and pollutes vector-retrieval results with what should be structured records.

Persist into wrap-up logs (ADR 0019) instead of a new table

Wrap-ups capture five fields at session end. They could include a “data fetched” field.

Rejected because wrap-ups are session-end artifacts and summarized. The first-turn drill-down case (“show me X” → “now drill into the Q4 cohort”) needs recall within a session, before the wrap-up exists. And the wrap-up’s natural form is narrative summary, not structured tool_input / tool_output.

Bigger messages.content JSONB blob

Add tool_results as a JSONB field on messages. One fewer table.

Rejected on three grounds: queries that need recency-ordered tool results across many messages get awkward (have to unnest JSONB on every row); the FK + index pattern in the chosen schema is the boring, well-understood one; and Message.content should remain rendered conversation text, not a junk drawer.

Recall-intent gating (Phase 1.5 follow-up, not now)

Only inject the “Previously fetched” block when the user’s message looks like recall (“last time”, “you showed me”, “previously”, “what did”, etc. — cheap regex). Drops the ~200-token tax on the ~80% of turns that don’t need it.

Worth doing soon but not now. Without real usage data we’ll guess the regex wrong and either over-inject (no savings) or under-inject (silently regress the recall experience). Phase 1 ships with always-inject; once we have observability counters for tool_results_injection_tokens and a sample of real recall queries, we add gating as a Phase 1.5 PR with the eval scenarios above re-asserted.