Querying the Octopus Database

Agent Activity Queries

Active Agent Summary

-- Count active agents and their total conversations (all time)
SELECT
    a.AgentId,
    a.Name,
    a.Status,
    COUNT(c.SessionId) AS TotalSessions,
    MAX(c.StartedAt)   AS LastSessionAt
FROM Octopus_Agents a
LEFT JOIN Octopus_Conversations c
    ON c.AgentId = a.AgentId AND c.TenantId = a.TenantId
WHERE a.TenantId = @tenantId
GROUP BY a.AgentId, a.Name, a.Status
ORDER BY TotalSessions DESC;

Agent Usage in the Last 7 Days

SELECT
    a.Name AS AgentName,
    COUNT(DISTINCT c.SessionId) AS Sessions,
    COUNT(e.EpisodeId)          AS TotalEpisodes,
    SUM(e.TokenCount)           AS TotalTokens
FROM Octopus_Agents a
JOIN Octopus_Conversations c
    ON c.AgentId = a.AgentId AND c.TenantId = a.TenantId
JOIN Octopus_Episodes e
    ON e.SessionId = c.SessionId AND e.TenantId = a.TenantId
WHERE a.TenantId = @tenantId
  AND c.StartedAt >= DATEADD(DAY, -7, GETUTCDATE())
GROUP BY a.Name
ORDER BY Sessions DESC;

Conversation History Queries

Full Conversation Transcript

-- Retrieve all episodes for a conversation, ordered chronologically
SELECT
    e.EpisodeId,
    e.Role,
    e.Timestamp,
    e.TokenCount
    -- Note: e.Messages is varbinary (compressed JSON) — decompress in application
FROM Octopus_Episodes e
WHERE e.TenantId = @tenantId
  AND e.SessionId = @sessionId
ORDER BY e.Timestamp ASC;

Recent Conversations for a User

SELECT TOP 20
    c.SessionId,
    a.Name AS AgentName,
    c.StartedAt,
    c.EndedAt,
    c.Status,
    COUNT(e.EpisodeId) AS TurnCount
FROM Octopus_Conversations c
JOIN Octopus_Agents a
    ON a.AgentId = c.AgentId AND a.TenantId = c.TenantId
LEFT JOIN Octopus_Episodes e
    ON e.SessionId = c.SessionId AND e.TenantId = c.TenantId
WHERE c.TenantId = @tenantId
  AND c.UserId = @userId
GROUP BY c.SessionId, a.Name, c.StartedAt, c.EndedAt, c.Status
ORDER BY c.StartedAt DESC;

Episode Recall Queries

Look Up Episodes by EmbeddingRef (After Vector Search)

-- After a vector similarity search returns a list of EmbeddingRef IDs,
-- retrieve the corresponding SQL episode records
SELECT
    e.EpisodeId,
    e.SessionId,
    e.Role,
    e.Timestamp,
    e.EmbeddingRef
    -- Decompress e.Messages in application layer
FROM Octopus_Episodes e
WHERE e.TenantId = @tenantId
  AND e.EmbeddingRef IN (
      'vec_id_001', 'vec_id_002', 'vec_id_003'
      -- Replace with actual IDs returned by vector store query
  )
ORDER BY e.Timestamp DESC;

Episodes With Tool Calls (Audit)

-- Find all episodes that included tool calls (non-null ToolCalls column)
SELECT
    e.EpisodeId,
    e.SessionId,
    e.Timestamp,
    c.UserId,
    a.Name AS AgentName
FROM Octopus_Episodes e
JOIN Octopus_Conversations c
    ON c.SessionId = e.SessionId AND c.TenantId = e.TenantId
JOIN Octopus_Agents a
    ON a.AgentId = c.AgentId AND a.TenantId = c.TenantId
WHERE e.TenantId = @tenantId
  AND e.ToolCalls IS NOT NULL
  AND e.Timestamp >= DATEADD(DAY, -7, GETUTCDATE())
ORDER BY e.Timestamp DESC;

Procedure Usage Queries

Most-Used Procedures (Last 30 Days)

SELECT
    p.Name,
    p.AgentId,
    a.Name AS AgentName,
    p.ActivatedAt,
    p.CreatedAt
FROM Octopus_Procedures p
JOIN Octopus_Agents a
    ON a.AgentId = p.AgentId AND a.TenantId = p.TenantId
WHERE p.TenantId = @tenantId
  AND p.ActivatedAt >= DATEADD(DAY, -30, GETUTCDATE())
ORDER BY p.ActivatedAt DESC;

Procedure Count Per Agent

SELECT
    a.Name AS AgentName,
    COUNT(p.ProcedureId) AS ProcedureCount
FROM Octopus_Agents a
LEFT JOIN Octopus_Procedures p
    ON p.AgentId = a.AgentId AND p.TenantId = a.TenantId
WHERE a.TenantId = @tenantId
  AND a.Status = 'Active'
GROUP BY a.Name
ORDER BY ProcedureCount DESC;

Operational Dashboards

Daily Episode Volume (Last 14 Days)

SELECT
    CAST(Timestamp AS DATE) AS EpisodeDate,
    COUNT(*) AS EpisodeCount,
    SUM(TokenCount) AS TotalTokens,
    COUNT(DISTINCT SessionId) AS UniqueSessions
FROM Octopus_Episodes
WHERE TenantId = @tenantId
  AND Timestamp >= DATEADD(DAY, -14, GETUTCDATE())
GROUP BY CAST(Timestamp AS DATE)
ORDER BY EpisodeDate DESC;

Error Sessions Summary

SELECT
    a.Name AS AgentName,
    COUNT(*) AS ErrorSessions,
    MIN(c.StartedAt) AS EarliestError,
    MAX(c.StartedAt) AS LatestError
FROM Octopus_Conversations c
JOIN Octopus_Agents a
    ON a.AgentId = c.AgentId AND a.TenantId = c.TenantId
WHERE c.TenantId = @tenantId
  AND c.Status = 'Error'
  AND c.StartedAt >= DATEADD(DAY, -7, GETUTCDATE())
GROUP BY a.Name
ORDER BY ErrorSessions DESC;