Database Schema — Octopus Database

Schema at a Glance

The Octopus database schema is intentionally minimal. Four tables cover everything the framework needs: agent registry, session tracking, episodic memory, and procedural memory. The schema is managed via EF Core Code-First migrations, ensuring that deployments are reproducible and upgrades are tracked.

-- Entity Relationship Summary
Octopus_Agents (AgentId PK)
    └── Octopus_Conversations (AgentId FK)
            └── Octopus_Episodes (SessionId FK)
    └── Octopus_Procedures (AgentId FK)

Octopus_Agents

Column	Type	Nullable	Description
`AgentId`	uniqueidentifier	No	Primary key — GUID assigned at agent registration
`TenantId`	int	No	Multi-tenancy discriminator — required on all queries
`Name`	nvarchar(200)	No	Human-readable agent name, unique per tenant
`SystemPrompt`	nvarchar(max)	Yes	The base system prompt injected at the start of every conversation
`PluginRefs`	nvarchar(max)	Yes	JSON array of plugin identifiers the agent has access to
`Config`	nvarchar(max)	Yes	JSON blob for model settings: temperature, maxTokens, modelId, etc.
`Version`	int	No	Schema version of the agent definition — incremented on update
`Status`	nvarchar(50)	No	Active, Inactive, Draft, Deprecated
`CreatedAt`	datetime2	No	UTC creation timestamp
`UpdatedAt`	datetime2	No	UTC last-modified timestamp

Octopus_Conversations

Column	Type	Nullable	Description
`SessionId`	uniqueidentifier	No	Primary key — GUID created when a new chat session starts
`AgentId`	uniqueidentifier	No	FK → Octopus_Agents.AgentId
`TenantId`	int	No	Multi-tenancy discriminator
`UserId`	nvarchar(200)	Yes	Identity of the user who initiated the conversation
`StartedAt`	datetime2	No	UTC timestamp when the session was opened
`EndedAt`	datetime2	Yes	UTC timestamp when the session was closed (null = still active)
`Status`	nvarchar(50)	No	Active, Ended, TimedOut, Error
`Metadata`	nvarchar(max)	Yes	JSON blob for caller-supplied context (channel, referrer, custom tags)

Octopus_Episodes

Column	Type	Nullable	Description
`EpisodeId`	uniqueidentifier	No	Primary key
`SessionId`	uniqueidentifier	No	FK → Octopus_Conversations.SessionId
`TenantId`	int	No	Multi-tenancy discriminator
`Role`	nvarchar(50)	No	user, assistant, system, tool
`Messages`	varbinary(max)	No	Compressed JSON array of message objects in this episode
`ToolCalls`	varbinary(max)	Yes	Compressed JSON of tool/function call records, if any
`EmbeddingRef`	nvarchar(500)	Yes	ID pointing to the vector store entry for this episode
`Timestamp`	datetime2	No	UTC creation time of this episode turn
`TokenCount`	int	Yes	Approximate token count for this episode (for context window management)

Octopus_Procedures

Column	Type	Nullable	Description
`ProcedureId`	uniqueidentifier	No	Primary key
`AgentId`	uniqueidentifier	No	FK → Octopus_Agents.AgentId — the agent this procedure belongs to
`TenantId`	int	No	Multi-tenancy discriminator
`Name`	nvarchar(300)	No	Unique name for this procedure within the agent+tenant scope
`Steps`	nvarchar(max)	No	JSON array of procedure steps with type, action, params
`ActivatedAt`	datetime2	Yes	When this procedure was last activated/executed
`CreatedAt`	datetime2	No	UTC creation timestamp

Indexes

The following indexes are created by the EF Core migrations to support common query patterns:

Table	Index Columns	Purpose
Octopus_Agents	(TenantId, Status)	Filter active agents per tenant
Octopus_Agents	(TenantId, Name) UNIQUE	Enforce unique agent names within a tenant
Octopus_Conversations	(TenantId, AgentId, Status)	Find active sessions for an agent
Octopus_Conversations	(TenantId, UserId)	Look up all sessions for a user
Octopus_Episodes	(TenantId, SessionId, Timestamp DESC)	Retrieve episodes in reverse-chronological order (most recent first)
Octopus_Episodes	(TenantId, EmbeddingRef)	Retrieve SQL episode by vector store ID after semantic search
Octopus_Procedures	(TenantId, AgentId, Name) UNIQUE	Enforce unique procedure names per agent per tenant

EF Core Migrations

The schema is managed via EF Core Code-First migrations located at Octopus/Plugins/BuiltIn/SqlServerStorage/Migrations/. Never modify the schema manually. Apply changes by running dotnet ef migrations add <MigrationName> and dotnet ef database update.

JSON Column Conventions

Several columns store structured data as JSON. Here are the conventions followed throughout the schema:

Compressed JSON (varbinary(max)): Used for high-volume columns like Messages and ToolCalls in Octopus_Episodes. Data is compressed with GZip before storage and decompressed on read by the EF Core value converter.
Plain JSON (nvarchar(max)): Used for smaller, less frequent documents like Config, PluginRefs, and Steps. Can be queried directly with OPENJSON() in SQL.

-- Example: Query plugin refs using OPENJSON
SELECT a.AgentId, a.Name, p.value AS PluginRef
FROM Octopus_Agents a
CROSS APPLY OPENJSON(a.PluginRefs) AS p
WHERE a.TenantId = 42
  AND a.Status = 'Active';

← Overview Next: Octopus_Agents Table →