Database Migrations
Relevant source files
The following files were used as context for generating this wiki page:
Introduction
The database migration and storage strategy for the AI Anomaly Detection system is centered around a portable and queryable SQLite architecture. This system manages the persistence of interaction logs, multi-agent analysis records, risk scores, RAG-grounded explanations, and human feedback. The primary purpose of the database is to provide a structured historical record of LLM medical outputs and their subsequent safety evaluations.
The architecture supports a multi-stage processing pipeline where data flows from initial ingestion through automated scoring to human review. While SQLite serves as the primary relational store, the system also utilizes an optional append-only JSONL mirror for auditing and export purposes.
Sources: AI Service/plan.md:92-96, AI Service/watchtower/rag_system.py:12-14
Data Schema Architecture
The database is structured into several core tables that represent the lifecycle of a medical AI interaction and its analysis. These tables are designed to support complex queries for the Watchtower UI and downstream machine learning tasks.
Core Relational Tables
The system utilizes five primary tables to manage the anomaly detection lifecycle:
| Table Name | Description | Key Fields |
|---|---|---|
interaction_logs | Stores original user prompts and AI responses. | id, prompt, response, model_name, timestamp |
analysis_records | Contains feature extraction results and safety flags. | interaction_id, topics, risk_context_flags |
scoring_records | Holds numerical risk scores and anomaly detection flags. | interaction_id, scores, overall_anomaly_score |
explanations | Stores RAG-generated rationales and citations. | interaction_id, risk_type, explanation, citations |
feedback | Records expert human verification and corrections. | interaction_id, human_label, corrected_response |
Sources: AI Service/plan.md:12-68, AI Service/plan.md:94-96
RAG Chunk Storage
In addition to interaction data, the system manages a corpus of medical safety guidelines via the rag_chunks table. This table supports the Explainability Layer by storing vectorized or searchable text segments used for grounding AI safety critiques.
The diagram above illustrates the relational flow from a raw interaction log through the various stages of analysis, scoring, and explanation, ending with a human feedback loop.
Sources: AI Service/plan.md:94-96, AI Service/watchtower/rag_system.py:20-33
Data Ingestion and Normalization
Data enters the database through the LogAgent. This component is responsible for normalizing incoming interaction logs from various sources (UI, API, or files) and persisting them to both SQLite and the optional JSONL audit mirror.
LogAgent Responsibilities
- Normalization: Standardizes timestamps (ISO-8601), languages, and default metadata.
- Persistence: Executes the initial write to
interaction_logs. - Access: Provides batched loading and streaming capabilities for downstream agents like the
MisbehaviorAnalysisAgent.
Sources: AI Service/plan.md:99-120
Multi-Stage Processing Flow
As interactions are processed, the database is updated sequentially. Each stage of the WatchtowerOrchestrator pipeline writes its results to the corresponding table, linked by a unique interaction_id.
This flowchart details how the system transitions through different analytical states, with each state persisting data back to the central SQLite database.
Sources: AI Service/plan.md:122-130, AI Service/plan.md:169-173, AI Service/plan.md:231-236
Querying and API Integration
The system provides enhanced API endpoints for querying these database records, specifically for administrative and analytical purposes. These endpoints allow for filtering by anomaly score, risk category, and time period.
Administrative Endpoints
GET /api/admin/interactions/anomalies/all: Retrieves a list of flagged interactions above a specific score threshold.GET /api/admin/analytics/anomaly-breakdown: Provides statistical summaries of detected risks over time.GET /api/admin/interactions/{id}/detailed: Performs a join-like retrieval of logs, analysis, scores, and explanations for a single ID.
Sources: backend/test_enhanced_endpoints.py:61-125, frontend/src/pages/admin/InteractionDetail.jsx:21-65
Conclusion
The database migration and management system for the AI Anomaly Detection Watchtower provides a robust foundation for monitoring medical LLM behavior. By utilizing a structured SQLite schema and a multi-stage write pipeline, it ensures that every step of the safety analysis—from raw log ingestion to RAG-backed explanation and human correction—is fully audited and accessible for both real-time monitoring and future machine learning training.
Sources: AI Service/plan.md:255-265, backend/README.md