lhumina_code/home
21
0
Fork 0
Code Issues 10 Pull requests Projects Releases Packages Wiki Activity Actions

hero_agent: migrate audit_log, usage_log, and memories from SQLite to OSIS #91

New issue
Closed
opened 2026-03-25 21:57:50 +00:00 by mik-tf · 1 comment
mik-tf commented 2026-03-25 21:57:50 +00:00
Owner
Copy link

Context

Follow-up to #45 (AI conversations → OSIS).

After #45, hero_agent still uses SQLite (db.rs) for three data domains. This issue tracks migrating them to OSIS so SQLite and rusqlite can be removed entirely.

What remains in SQLite after #45

1. Audit Log (audit_log table)

  • Purpose: tracks tool executions (tool_name, arguments, result_preview, success, duration_ms, user_sid)
  • Schema needed: ai/audit.oschema or extend ai/agent.oschema
  • Fields: tool_name, arguments (JSON string), result_preview, success (bool), duration_ms, user_sid, created_at
  • Volume: high — every tool call creates an entry
  • Queries: list recent, filter by tool name, search in arguments/results

2. Usage Log (usage_log table)

  • Purpose: tracks LLM token consumption per model (prompt_tokens, completion_tokens, total_tokens)
  • Schema needed: ai/usage.oschema or extend ai/agent.oschema
  • Fields: model, prompt_tokens, completion_tokens, total_tokens, user_sid, created_at
  • Volume: moderate — one entry per LLM API call
  • Queries: aggregate by model, daily totals, per-user breakdown

3. Memories (memories table)

  • Purpose: agent memory storage (category, key, value, confidence, access_count)
  • Schema needed: ai/memory.oschema or extend ai/agent.oschema
  • Fields: user_sid, category (enum: fact/preference/context/skill), key, value, source, confidence (f64), access_count, created_at, updated_at
  • Volume: low — grows slowly as agent learns about user
  • Queries: by user+category, by key, ordered by confidence/access_count

Implementation Steps

Phase 1: OSIS Schemas

  • Create schemas for audit, usage, and memory types
  • Run codegen → types + client + server traits

Phase 2: Backend Migration

  • Create audit_osis.rs, usage_osis.rs, memory_osis.rs (or unified module)
  • Wire into agent.rs and routes.rs
  • Maintain same REST API surface (/api/audit, /api/usage, /api/memories, /api/stats)

Phase 3: Data Migration

  • One-time script: SQLite → OSIS for all three tables
  • Verify data integrity

Phase 4: Remove SQLite

  • Delete db.rs entirely
  • Remove rusqlite from Cargo.toml
  • Remove SQLite data file creation from startup
  • Update Docker setup (no more SQLite volume mount needed)

Testing

  • Smoke: /api/audit, /api/usage, /api/memories, /api/stats endpoints
  • Integration: create audit entry → query → verify
  • E2E: dashboard stats render correctly

Related

  • #45 — conversations → OSIS (prerequisite)

Signed-off-by: mik-tf

## Context Follow-up to https://forge.ourworld.tf/lhumina_code/home/issues/45 (AI conversations → OSIS). After #45, hero_agent still uses SQLite (`db.rs`) for three data domains. This issue tracks migrating them to OSIS so SQLite and `rusqlite` can be removed entirely. ## What remains in SQLite after #45 ### 1. Audit Log (`audit_log` table) - **Purpose**: tracks tool executions (tool_name, arguments, result_preview, success, duration_ms, user_sid) - **Schema needed**: `ai/audit.oschema` or extend `ai/agent.oschema` - **Fields**: tool_name, arguments (JSON string), result_preview, success (bool), duration_ms, user_sid, created_at - **Volume**: high — every tool call creates an entry - **Queries**: list recent, filter by tool name, search in arguments/results ### 2. Usage Log (`usage_log` table) - **Purpose**: tracks LLM token consumption per model (prompt_tokens, completion_tokens, total_tokens) - **Schema needed**: `ai/usage.oschema` or extend `ai/agent.oschema` - **Fields**: model, prompt_tokens, completion_tokens, total_tokens, user_sid, created_at - **Volume**: moderate — one entry per LLM API call - **Queries**: aggregate by model, daily totals, per-user breakdown ### 3. Memories (`memories` table) - **Purpose**: agent memory storage (category, key, value, confidence, access_count) - **Schema needed**: `ai/memory.oschema` or extend `ai/agent.oschema` - **Fields**: user_sid, category (enum: fact/preference/context/skill), key, value, source, confidence (f64), access_count, created_at, updated_at - **Volume**: low — grows slowly as agent learns about user - **Queries**: by user+category, by key, ordered by confidence/access_count ## Implementation Steps ### Phase 1: OSIS Schemas - [ ] Create schemas for audit, usage, and memory types - [ ] Run codegen → types + client + server traits ### Phase 2: Backend Migration - [ ] Create `audit_osis.rs`, `usage_osis.rs`, `memory_osis.rs` (or unified module) - [ ] Wire into agent.rs and routes.rs - [ ] Maintain same REST API surface (`/api/audit`, `/api/usage`, `/api/memories`, `/api/stats`) ### Phase 3: Data Migration - [ ] One-time script: SQLite → OSIS for all three tables - [ ] Verify data integrity ### Phase 4: Remove SQLite - [ ] Delete `db.rs` entirely - [ ] Remove `rusqlite` from Cargo.toml - [ ] Remove SQLite data file creation from startup - [ ] Update Docker setup (no more SQLite volume mount needed) ### Testing - Smoke: `/api/audit`, `/api/usage`, `/api/memories`, `/api/stats` endpoints - Integration: create audit entry → query → verify - E2E: dashboard stats render correctly ## Related - https://forge.ourworld.tf/lhumina_code/home/issues/45 — conversations → OSIS (prerequisite) Signed-off-by: mik-tf
mik-tf commented 2026-03-25 22:26:48 +00:00
Author
Owner
Copy link

Completed — all SQLite tables migrated to OSIS

This was implemented as part of #45 in a single pass. All 5 SQLite tables (conversations, messages, audit_log, usage_log, memories) are now stored in OSIS via the ai domain schema.

Schema types added

  • AgentAuditEntry — tool execution audit trail
  • AgentUsageEntry — LLM token consumption tracking
  • AgentMemory — user facts, preferences, skills, context

(Plus AgentConversation and AgentMessage from #45)

SQLite fully removed

  • db.rs deleted (711 lines)
  • conversation.rs deleted (90 lines)
  • rusqlite dependency removed from workspace
  • No more hero_agent.db file at runtime

Signed-off-by: mik-tf

## Completed — all SQLite tables migrated to OSIS This was implemented as part of https://forge.ourworld.tf/lhumina_code/home/issues/45 in a single pass. All 5 SQLite tables (conversations, messages, audit_log, usage_log, memories) are now stored in OSIS via the `ai` domain schema. ### Schema types added - `AgentAuditEntry` — tool execution audit trail - `AgentUsageEntry` — LLM token consumption tracking - `AgentMemory` — user facts, preferences, skills, context (Plus `AgentConversation` and `AgentMessage` from #45) ### SQLite fully removed - `db.rs` deleted (711 lines) - `conversation.rs` deleted (90 lines) - `rusqlite` dependency removed from workspace - No more `hero_agent.db` file at runtime Signed-off-by: mik-tf
Sign in to join this conversation.
No Branch/Tag specified
Branches Tags
development
main
No results found.
Labels
Clear labels
No items
No labels
Milestone
Clear milestone
No items
No milestone
Projects
Clear projects
No items
No project
Assignees
Clear assignees
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference
lhumina_code/home#91
No description provided.