Database Schema & Logging

Relevant source files

The following files were used as context for generating this wiki page:

• README.md
• orchestrator.py
• app.py
• memory_utils.py
• reset_demo.py

The BrightPath AI Operations system utilizes a dual-layered persistence strategy consisting of a relational SQLite database for structured events and tasks, and a file-based memory system for persistent agent context. The primary goal of the database and logging infrastructure is to ensure every inbound message, agent decision, and manual intervention is audited, traceable, and available for administrative review through the Streamlit UI or terminal orchestrator.

Sources: README.md:1-20, app.py:270-300

SQLite Database Architecture

The system uses a local SQLite database, typically named brightpath.db, which is automatically initialized upon the first run of the application. The schema is designed to support the three-agent pipeline by capturing inputs and outputs at each stage, while also maintaining a decoupled task list and a log of CRM interactions.

Sources: orchestrator.py:38-44, app.py:255-265

Entity Relationship Diagram

The following diagram illustrates the structure of the three main tables used for operational logging and task management.

Table Definitions

Table	Column	Type	Description
events	id	INTEGER	Primary key, auto-incrementing.
	timestamp	TEXT	ISO format timestamp of the pipeline run.
	message	TEXT	The raw inbound message processed.
	category	TEXT	Classification (e.g., sales, customer_service).
	triage_out	TEXT	JSON string of the Triage Agent's complete output.
	agent_out	TEXT	JSON string of the Specialist Agent's results.
	briefing	TEXT	JSON string of the Manager Briefing Agent's output.
tasks	id	INTEGER	Primary key, auto-incrementing.
	created	TEXT	Creation timestamp.
	task	TEXT	Description of the action required.
	priority	TEXT	Urgency level (high, medium, low).
	status	TEXT	Defaults to 'open'.
	source	TEXT	Originating interface (e.g., 'streamlit', 'orchestrator').
crm_log	id	INTEGER	Primary key, auto-incrementing.
	timestamp	TEXT	Timestamp of the update.
	contact	TEXT	The customer or company name.
	action	TEXT	Type of update (e.g., 'updated', 'contacted').

Sources: app.py:258-274, orchestrator.py:48-77

Logging Pipeline Flow

Logging occurs at the end of the process_message (orchestrator) or run_pipeline (app) functions. Data is committed only after the specialist agents have completed their respective tasks to ensure the full context of the decision-making process is captured.

Implementation Details

The log_event function serves as the primary sink for the pipeline. It serializes the complex JSON objects returned by the Claude LLM into text fields for storage in the events table.

def log_event(message, triage, agent_result, briefing):
    conn = sqlite3.connect(DB, timeout=10)
    c = conn.cursor()
    c.execute("""INSERT INTO events
        (timestamp,message,category,urgency,sentiment,triage_out,agent_out,briefing)
        VALUES (?,?,?,?,?,?,?,?)""",
        (datetime.now().isoformat(), message,
         triage.get("category"), triage.get("urgency"), triage.get("sentiment"),
         json.dumps(triage), json.dumps(agent_result), json.dumps(briefing)))
    conn.commit(); conn.close()

Sources: app.py:277-286, orchestrator.py:80-97

File-Based Memory System

In addition to the SQLite database, the system maintains "Live Memory Files" in Markdown format. These files are read by agents on every run to provide long-term context that exceeds the scope of individual database events.

Memory Update Logic

Updates to memory files are managed by memory_utils.py. The system appends specific events to logs or updates existing sections (like customer history) using string manipulation and pattern matching.

• Decision Log: Captures the "Reasoning" behind specific agent actions for transparency.
• Customer History: Appends updates under specific customer H2 headings.
• Open Tasks: Syncs high-level tasks to open_tasks.md in addition to the database.

Sources: memory_utils.py:12-65, README.md:120-135

Data Management and Reset

For demonstration and testing purposes, the system includes a reset_demo.py utility. This script performs two primary actions:

1. Database Cleansing: Executes DELETE commands on the events, tasks, and crm_log tables and resets auto-increment sequences.
2. Memory Restoration: Overwrites the current .md files in the memory/ directory with clean versions from a memory_backup/ folder.

Sources: reset_demo.py:20-55

Summary

The BrightPath logging system provides a robust audit trail by combining structured SQLite data for performance and reporting with human-readable Markdown files for agent context. This hybrid approach allows the Streamlit dashboard to display real-time metrics and historical event logs while ensuring that the multi-agent pipeline remains informed of past interactions across sessions.

Sources: app.py:530-560, README.md:137-145