Legacy documentation — AI Fusion 1.x / 2.x. This article describes AI Fusion versions 1.x and 2.x. For AI Fusion 3, see the current GenAI Data Fusion documentation.

Assurance Database Schema Reference

AI Fusion stores observation data in the Assurance database - a relational database that BI tools, reporting systems, and data pipelines can query directly. This article describes the table schemas, their relationships, and provides example queries for common reporting use cases.

The Assurance database holds session-level and aggregate data. Detailed per-turn data (individual messages, tool calls, execution traces) is stored in Fabric LU storage and is accessed via the Fabric AI Fusion REST API, not by direct SQL - this is described separately at the end of this article.

Assurance DB Tables

ACTVSESSION

This is the central table for monitoring, holding one row per conversation session.

Notes:

• chat_summary, suggestion, and the *_tokens aggregates are populated only after RunAnalyze.flow or runBatchChatAnalyzePipeline.flow has processed the session. Unanalyzed sessions have status = 'OPEN' and NULL values for these columns.
• customer_id maps to the IID of the relevant Logical Unit (e.g., the Customer LU).

INSIGHTS

Contains one row per insight tag per session. Each analyzed session produces multiple rows, one for each signal the LLM extracted.

Default signals (defined in the chat_signals.csv MTable, configurable per deployment):

Note: Since tag_name / tag_value is a key-value structure, pivot queries are needed to produce wide-format rows for BI tools. See the example queries below.

INVOKE_OBSERVATION

Telemetry for every LLM AI invocation. Use this table for performance profiling and LLM cost attribution at the flow level.

Note: This table mentions Broadway flow and actor names. Naming under agents and tools exist in the AIFusion LU tables.

MODEL_TOKEN_USAGE

Aggregation of token consumption, grouped by LLM interface and model. Updated continuously. Use for cost reporting and model usage trend dashboards.

Table Relationships

ACTVSESSION (session_id)
    └── INSIGHTS (session_id)         - LLM-assigned tags, one row per tag per session

INVOKE_OBSERVATION                    - per-invocation telemetry (correlate via timestamp)
MODEL_TOKEN_USAGE                     - cumulative totals by interface + model

Example Queries

The following queries target the Assurance DB and are compatible with both SQLite and PostgreSQL unless noted.

More examples can be found in the Observation Dashboard default widgets queries.

Daily resolution rate (last 30 days)

SELECT
    DATE(a.creation_date)        AS day,
    COUNT(DISTINCT a.session_id) AS total_sessions,
    COUNT(DISTINCT CASE WHEN i.tag_value = 'Resolved'
          THEN a.session_id END) AS resolved,
    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
LEFT JOIN INSIGHTS i
       ON a.session_id = i.session_id
      AND i.tag_name   = 'Resolution Status'
WHERE a.creation_date >= DATE('now', '-30 days')
  AND a.status         = 'CLOSED'
GROUP BY DATE(a.creation_date)
ORDER BY day;

Sentiment breakdown by application

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

Session detail with key signals (pivot)

Produces one row per session with the most-used tags as columns.

SELECT
    a.session_id,
    a.app_id,
    a.customer_id,
    a.creation_date,
    a.conversation_pairs,
    a.avg_response_time,
    a.total_tokens,
    a.submitted_by,
    MAX(CASE WHEN i.tag_name = 'Sentiment'                     THEN i.tag_value END) AS sentiment,
    MAX(CASE WHEN i.tag_name = 'Resolution Status'             THEN i.tag_value END) AS resolution_status,
    MAX(CASE WHEN i.tag_name = 'Topic'                         THEN i.tag_value END) AS topic,
    MAX(CASE WHEN i.tag_name = 'Urgency Level'                 THEN i.tag_value END) AS urgency_level,
    MAX(CASE WHEN i.tag_name = 'Customer Lifetime Value Risk'  THEN i.tag_value END) AS clv_risk
FROM ACTVSESSION a
LEFT JOIN INSIGHTS i ON a.session_id = i.session_id
WHERE a.status = 'CLOSED'
GROUP BY a.session_id, a.app_id, a.customer_id, 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;

Average response time and token usage by application (last 30 days)

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

Fabric LU Tables

The following tables are stored inside the Fabric LU - one LUI per session (not accessible via direct SQL).