Insights Database Schema Reference

AI Fusion stores session-level Observation data in the insights database — a relational database that dashboards, BI tools, reporting systems and data pipelines can query directly. This article describes its tables and their relationships, and gives example queries for common reports.

The insights database holds one row per session plus aggregates. The detailed per-turn data (messages, executions, tool calls, token usage per call) is stored in the aifusion Data Product (LU), one instance per session; it is described at the end of this article.

Connecting to the Insights Database

The database is set by three shared globals:

Use SQLite for development in Studio and PostgreSQL for QA and production, where concurrent writes and large volumes matter and where BI tools can connect directly. See Installation and Setup.

The tables are created by the deploy flow of the aifusion LU on every deploy (CREATE TABLE IF NOT EXISTS), so a new table reaches existing environments automatically. Column changes to ACTVSESSION on environments that predate them (see below) are applied by the same flow.

Observation Tables

ACTVSESSION

The central monitoring table: one row per conversation session.

Notes:

• The token, turn, response-time and summary columns are NULL until the session is analyzed. They reflect the conversation as of its last analysis.
• The 2.x customer_id column is no longer created or used. A session's LU instances are in the INSTANCES table.

INSTANCES

The LU instances a session ran against. A session can have zero, one or several rows.

INSIGHTS

One row per tag per session. Each analyzed session has several rows: one per single-value signal, one per selected value of a multi-select signal (such as Topic), and the feedback counters.

Tags present for every analyzed session:

Additional tag_name values come from each agent's Observation/signals.yaml — for example Urgency Level, Follow-up or Customer Lifetime Value Risk for the banking sample agent. See Configuring Observation.

Note: tag_name / tag_value is a key-value structure. To produce one row per session for a BI tool, pivot it (see the example queries below).

invoke_observation

Telemetry for every LLM and embedding call — from agents as well as from the LLM actors in Broadway flows. Use it for performance profiling and cost attribution at the flow level. It has no agent or session column, so it cannot be filtered by agent.

model_token_usage

Cumulative token consumption per LLM interface and model, updated after each call. Use it for cost reporting by model.

Other Tables in the Insights Database

The same database also holds tables used by other AI Fusion processes. You normally do not query them for Observation reporting.

Table Relationships

ACTVSESSION (session_id)
    ├── INSIGHTS (session_id)            signals and feedback counters, one row per tag
    ├── INSTANCES (session_id)           LU instances the session ran against
    └── aifusion LU instance (IID = session_id)
                                         turns, messages, executions, tool calls, usage, feedback

invoke_observation                       per-call LLM telemetry (correlate by timestamp)
model_token_usage                        cumulative totals per interface + model

Example Queries

The queries below are written for SQLite (the default). For PostgreSQL, replace date expressions such as DATE('now', '-30 days') with (now() - INTERVAL '30 days')::date, and GROUP_CONCAT with string_agg. Prefix table names with your schema when it is not the default one. The overall dashboard widgets in the widgets MTable of the aifusion LU are further examples.

Daily resolution rate per agent (last 30 days)

SELECT
    DATE(a.last_modified_date)   AS day,
    a.agent_name,
    COUNT(DISTINCT a.session_id) AS analyzed_sessions,
    ROUND(
        100.0
        * COUNT(DISTINCT CASE WHEN i.tag_value = 'Resolved' THEN a.session_id END)
        / NULLIF(COUNT(DISTINCT a.session_id), 0)
    , 1)                         AS resolution_rate_pct
FROM ACTVSESSION a
JOIN INSIGHTS i
  ON i.session_id = a.session_id
 AND i.tag_name   = 'Resolution Status'
WHERE a.last_modified_date >= DATE('now', '-30 days')
GROUP BY DATE(a.last_modified_date), a.agent_name
ORDER BY day, a.agent_name;

Sentiment breakdown by agent

SELECT
    a.agent_name,
    a.data_product,
    i.tag_value  AS sentiment,
    COUNT(*)     AS session_count
FROM ACTVSESSION a
JOIN INSIGHTS i
  ON i.session_id = a.session_id
 AND i.tag_name   = 'Sentiment'
WHERE a.status = 'CLOSED'
GROUP BY a.agent_name, a.data_product, i.tag_value
ORDER BY a.agent_name, session_count DESC;

All conversations of one customer

Find every session that ran against a given LU instance, across all agents:

SELECT
    a.session_id,
    a.agent_name,
    a.creation_date,
    a.submitted_by,
    a.chat_summary
FROM ACTVSESSION a
JOIN INSTANCES n
  ON n.session_id = a.session_id
WHERE n.lu_name = 'customer_bank'
  AND n.iid     = '1001'
ORDER BY a.creation_date DESC;

Session detail with key signals (pivot)

One row per session, with the main tags as columns:

SELECT
    a.session_id,
    a.agent_name,
    a.data_product,
    (SELECT GROUP_CONCAT(n.lu_name || '/' || n.iid, ', ')
       FROM INSTANCES n WHERE n.session_id = a.session_id)                      AS instances,
    a.creation_date,
    a.conversation_pairs,
    a.avg_response_time,
    a.total_tokens,
    a.submitted_by,
    MAX(CASE WHEN i.tag_name = 'Resolution Status' THEN i.tag_value END)       AS resolution_status,
    MAX(CASE WHEN i.tag_name = 'Sentiment'         THEN i.tag_value END)       AS sentiment,
    GROUP_CONCAT(CASE WHEN i.tag_name = 'Topic'    THEN i.tag_value END, ', ') AS topics,
    MAX(CASE WHEN i.tag_name = 'Positive Feedback' THEN i.tag_value END)       AS positive_feedback,
    MAX(CASE WHEN i.tag_name = 'Negative Feedback' THEN i.tag_value END)       AS negative_feedback
FROM ACTVSESSION a
LEFT JOIN INSIGHTS i ON i.session_id = a.session_id
WHERE a.status = 'CLOSED'
GROUP BY a.session_id, a.agent_name, a.data_product, a.creation_date,
         a.conversation_pairs, a.avg_response_time, a.total_tokens, a.submitted_by
ORDER BY a.creation_date DESC;

Token cost by model

SELECT
    interface,
    model,
    total_tokens,
    input_tokens,
    output_tokens,
    cache_read_tokens,
    last_update
FROM model_token_usage
ORDER BY total_tokens DESC;

Response time and token usage by agent (last 30 days)

SELECT
    agent_name,
    COUNT(session_id)                AS sessions,
    ROUND(AVG(avg_response_time), 1) AS avg_response_sec,
    ROUND(AVG(total_tokens), 0)      AS avg_tokens_per_session,
    SUM(total_tokens)                AS total_tokens
FROM ACTVSESSION
WHERE last_modified_date >= DATE('now', '-30 days')
  AND status = 'CLOSED'
GROUP BY agent_name
ORDER BY total_tokens DESC;

Per-Session Tables in the aifusion LU

The detailed record of each session is stored in the aifusion LU, in one instance per session (IID = session_id). These tables are not in the insights database and are not queried by direct SQL against it; they are read through Fabric — by the conversation review, the trace panel and the analysis process.

The cleanup process deletes the session's aifusion LU instance together with its insights rows when the session passes the retention period. See Configuring Observation.

Next article: Observation Best Practices