Spotter Agent — Data Model

All Spotter tables live in libs/db/src/lib/schema/agent.ts and libs/db/src/lib/schema/ai-cache.ts.

`ai_conversations`

Column	Type	Notes
`id`	`uuid` PK
`organization_id`	`uuid` FK → `organizations.id` (cascade)	Scope.
`user_id`	`uuid` FK → `users.id` (cascade)	Conversation owner.
`membership_id`	`uuid` FK → `memberships.id` (set null)	Snapshots the membership at start.
`title`	`varchar(255)` nullable	Haiku-generated from first user message.
`pinned`	`boolean` default false	UX affordance.
`archived_at`	`timestamp tz` nullable	Soft archive.
`summary`	`text` nullable	Compaction summary text.
`summary_embedding`	`vector(1024)`	Voyage embedding of `summary`. HNSW index.
`last_message_at`	`timestamp tz`	Used for sort.
`total_input_tokens`	`int`	Lifetime tokens.
`total_output_tokens`	`int`
`total_cache_read_tokens`	`int`
`total_cache_creation_tokens`	`int`
`cost_usd_micros`	`bigint`	Lifetime cost.
`model`	`varchar(64)`	DEFAULT_MODEL at creation.
`created_at`, `updated_at`	`timestamp tz`

Indexes:

(organization_id, user_id, last_message_at DESC) — list query.
HNSW on summary_embedding (vector_cosine_ops).

`ai_messages`

Column	Type	Notes
`id`	`uuid` PK
`conversation_id`	`uuid` FK (cascade)
`role`	enum `aiMessageRole`	`user`, `assistant`, `tool_result`, `system_note`.
`content`	`jsonb`	Anthropic content block array, verbatim. Allows lossless replay.
`preview`	`text` nullable	First 200 chars for the list panel.
`page_context`	`jsonb` nullable	`{ pathname, selection }` snapshot. Also used by compaction to record `summarizedThroughMessageId`.
`stop_reason`	`varchar(32)` nullable	`end_turn`, `tool_use`, `max_tokens`, …
`input_tokens` / `output_tokens` / `cache_read_tokens` / `cache_creation_tokens`	`int` nullable	Per-turn usage from Anthropic.
`model`	`varchar(64)` nullable
`created_at`	`timestamp tz`

Index: (conversation_id, created_at) for replay.

Why `content` is `jsonb`

Anthropic’s content blocks (text, tool_use, tool_result, thinking) are stored byte-for-byte. The orchestrator re-feeds them on resume so token counting, citation positions, and tool_use ids stay stable across turns.

`ai_tool_executions`

Column	Type	Notes
`id`	`uuid` PK
`conversation_id`	`uuid` FK (cascade)
`message_id`	`uuid` FK → `ai_messages.id` (cascade)	The assistant message that emitted the tool_use.
`tool_use_id`	`varchar(64)`	Anthropic’s tool_use id.
`router`	`varchar(64)`	e.g. `workouts`.
`action`	`varchar(64)`	e.g. `create`.
`input`	`jsonb`	Validated `args` payload.
`output`	`jsonb` nullable	Set on succeeded.
`status`	enum `aiToolStatus`	`pending`, `running`, `succeeded`, `failed`, `rejected_by_user`.
`error_code` / `error_message`	`varchar(64)` / `text` nullable	On failure / rejection.
`duration_ms`	`int` nullable	Wall-clock per `ToolRunner.execute`.
`resource_id`	`varchar(255)` nullable	Extracted from `output.id` when present.
`audit_log_id`	`uuid` FK → `audit_logs.id` nullable	Linked when write tool emits an audit row.
`created_at`	`timestamp tz`

Indexes:

UNIQUE (conversation_id, tool_use_id) — dedupe / lookup.
(resource_id) — find tool runs by resource.
(conversation_id, created_at) — replay order.

`ai_usage_daily`

Column	Type	Notes
`id`	`uuid` PK
`organization_id`	`uuid` FK (cascade)
`user_id`	`uuid` FK (cascade)
`day`	`date`	UTC day bucket.
`messages`	`int`	Turn count.
`input_tokens` / `output_tokens` / `cache_read_tokens` / `cache_creation_tokens`	`int`
`cost_usd_micros`	`bigint`

UNIQUE (organization_id, user_id, day).

AgentCostTracker.recordTurn upserts via SQL increments to avoid lost updates on concurrent turns.

`ai_cache` (`ai-cache.ts`)

Column	Type	Notes
`id`	`uuid` PK
`model`	`varchar(100)`	e.g. `voyage-multilingual-2`.
`input_hash`	`varchar(64)`	SHA hash of input.
`operation`	`varchar(50)`	`embedding` or `enrichment`.
`result`	`text`	JSON-serialized result.
`created_at`	`timestamp tz`
`expires_at`	`timestamp tz` nullable	Optional TTL.

Index: (model, input_hash) — lookup.

Used by AiCacheService to memoize Voyage embeddings and exercise enrichment (the LLM-generated movement metadata). Not the same as Anthropic’s prompt cache — that’s transient per-request cache_control markers.

Audit linkage

Every write tool that defines meta.audit writes to audit_logs post-success with metadata.agent: true.
ai_tool_executions.audit_log_id points at the audit row so admin views can pivot.

Retention posture

No automatic deletion. ai_messages and ai_tool_executions grow with usage.
AgentStorageSnapshotService emits a daily agent.storage.snapshot event so growth is monitored.
A retention policy is on the backlog; cold rows beyond N days could move to a _archive table or be dropped after summarization.

Spotter Agent — Data Model

ai_conversations

ai_messages

Why content is jsonb

ai_tool_executions

ai_usage_daily

ai_cache (ai-cache.ts)