Octopus_Conversations Table
The conversation session table — each row represents a single chat session between a user and an Octopus agent. It is the parent record for all episode (turn) data in Octopus_Episodes.
Role in the Data Model
A conversation in Octopus is a bounded interaction session. When a user opens a chat window and sends messages, all those message turns belong to a single conversation. The Octopus_Conversations table acts as the container record — it holds session identity, timing, status, and the link to the agent that handled it.
Every row in Octopus_Episodes is a child of a row in Octopus_Conversations via the SessionId foreign key. To retrieve a full conversation history, you query episodes filtered by SessionId.
Column Reference
| Column | Type | Constraints | Description |
|---|---|---|---|
SessionId | uniqueidentifier | PK, NOT NULL | GUID primary key. Returned to the client on session creation and used in all subsequent episode writes. |
AgentId | uniqueidentifier | FK, NOT NULL | References the agent that handled this conversation. Indexed for agent-level session reporting. |
TenantId | int | NOT NULL, IX | Multi-tenancy discriminator. Always in the WHERE clause. |
UserId | nvarchar(200) | NULL | The identity of the initiating user. Can be an internal user GUID, an email, or an anonymous session token. Null for system-initiated conversations. |
StartedAt | datetime2 | NOT NULL | UTC timestamp when the session was created. Used for duration calculations and time-based filtering. |
EndedAt | datetime2 | NULL | UTC timestamp when the session was explicitly closed. Null means the session is still active (or abandoned). |
Status | nvarchar(50) | NOT NULL | Session lifecycle status. See status values below. |
Metadata | nvarchar(max) | NULL | Caller-supplied JSON context attached at session creation. Useful for tracking channel, UI context, referrer, campaign ID, etc. |
Session Status Values
| Status | Meaning | EndedAt |
|---|---|---|
Active | Session is open and accepting new messages | NULL |
Ended | Session was explicitly closed by the user or application | Set |
TimedOut | Session exceeded the inactivity timeout configured for the agent | Set (by retention job) |
Error | Session ended due to an unrecoverable error in the agent pipeline | Set |
Metadata JSON Pattern
The Metadata column accepts any valid JSON object. Common patterns used by BizFirstGO applications:
{
"channel": "web-chat",
"referrer": "pricing-page",
"campaignId": "Q2-2026-trial",
"uiSessionId": "abc123",
"clientIp": "203.0.113.5",
"customTags": ["vip", "trial"]
}This metadata is never interpreted by the Octopus framework itself — it is purely for application-layer reporting and filtering.
Session Lifecycle Flow
Session Creation
A new row is inserted with Status = 'Active', StartedAt = UTC_NOW(), and EndedAt = NULL. The generated SessionId is returned to the caller.
Message Turns
Each user message and agent response creates episode rows in Octopus_Episodes linked to this SessionId. The conversation row itself is not updated during messaging.
Session Close
When the user ends the session (or the timeout job fires), Status is updated and EndedAt is set. No further episodes can be written to a closed session.
Common Queries
-- All active sessions for an agent
SELECT SessionId, UserId, StartedAt,
DATEDIFF(MINUTE, StartedAt, GETUTCDATE()) AS DurationMinutes
FROM Octopus_Conversations
WHERE TenantId = @tenantId
AND AgentId = @agentId
AND Status = 'Active'
ORDER BY StartedAt DESC;
-- Session history for a specific user
SELECT SessionId, AgentId, StartedAt, EndedAt, Status
FROM Octopus_Conversations
WHERE TenantId = @tenantId
AND UserId = @userId
ORDER BY StartedAt DESC;
-- Average session duration per agent (last 30 days)
SELECT AgentId,
COUNT(*) AS SessionCount,
AVG(DATEDIFF(SECOND, StartedAt, EndedAt)) AS AvgDurationSecs
FROM Octopus_Conversations
WHERE TenantId = @tenantId
AND Status = 'Ended'
AND StartedAt >= DATEADD(DAY, -30, GETUTCDATE())
GROUP BY AgentId;Sessions where the user closed the browser without explicitly ending the conversation will remain Active with a null EndedAt indefinitely unless the MemoryRetentionJob times them out. Configure the SessionInactivityTimeoutMinutes setting in your Octopus agent config to control how long inactive sessions are kept open before being automatically set to TimedOut.