SQL Schema — SQL Server Plugin

Schema Overview

Table	Stores	Primary Consumer
`Octopus_Agents`	Agent definitions and configuration	`IAgentStore`
`Octopus_Episodes`	Conversation session metadata	`IEpisodicMemoryStore`
`Octopus_EpisodeMessages`	Individual messages within episodes	`IEpisodicMemoryStore`
`Octopus_Procedures`	Skill sequences for procedural memory	`IProceduralMemoryStore`
`Octopus_AIFunctions`	JavaScript AI functions	`IAIFunctionStore`
`Octopus_Areas`	Agent area definitions and memberships	`IAreaStore`

Octopus_Agents

CREATE TABLE Octopus_Agents (
    Id                   UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
    TenantId             NVARCHAR(100)    NOT NULL,
    Name                 NVARCHAR(200)    NOT NULL,
    DisplayName          NVARCHAR(200)    NOT NULL,
    SystemPrompt         NVARCHAR(MAX)    NOT NULL,
    LLMModel             NVARCHAR(100)    NOT NULL,
    LLMCredentialId      INT              NOT NULL,
    MaxTokensPerTurn     INT              NOT NULL DEFAULT 4096,
    Temperature          FLOAT            NOT NULL DEFAULT 0.7,

    -- Episodic memory settings
    EpisodicEnabled      BIT              NOT NULL DEFAULT 1,
    EpisodicMaxMessages  INT              NOT NULL DEFAULT 20,
    EpisodicPruning      NVARCHAR(50)     NOT NULL DEFAULT 'FIFO',

    -- Semantic memory settings
    SemanticEnabled      BIT              NOT NULL DEFAULT 0,
    SemanticTopK         INT              NOT NULL DEFAULT 5,
    SemanticThreshold    FLOAT            NOT NULL DEFAULT 0.7,

    -- Procedural memory settings
    ProceduralEnabled    BIT              NOT NULL DEFAULT 0,

    IsActive             BIT              NOT NULL DEFAULT 1,
    CreatedAt            DATETIMEOFFSET   NOT NULL DEFAULT SYSDATETIMEOFFSET(),
    UpdatedAt            DATETIMEOFFSET   NOT NULL DEFAULT SYSDATETIMEOFFSET(),

    CONSTRAINT UQ_Octopus_Agents_Tenant_Name UNIQUE (TenantId, Name)
);

CREATE INDEX IX_Octopus_Agents_TenantId ON Octopus_Agents (TenantId);
CREATE INDEX IX_Octopus_Agents_Active    ON Octopus_Agents (TenantId, IsActive);

Octopus_Episodes and Octopus_EpisodeMessages

CREATE TABLE Octopus_Episodes (
    Id           UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
    TenantId     NVARCHAR(100)    NOT NULL,
    AgentId      UNIQUEIDENTIFIER NOT NULL,
    UserId       NVARCHAR(200)    NOT NULL,
    SessionId    NVARCHAR(200)    NOT NULL,
    Summary      NVARCHAR(MAX)    NULL,
    StartedAt    DATETIMEOFFSET   NOT NULL DEFAULT SYSDATETIMEOFFSET(),
    LastActiveAt DATETIMEOFFSET   NOT NULL DEFAULT SYSDATETIMEOFFSET(),
    IsActive     BIT              NOT NULL DEFAULT 1,

    CONSTRAINT FK_Episodes_Agent FOREIGN KEY (AgentId)
        REFERENCES Octopus_Agents(Id) ON DELETE CASCADE
);

CREATE INDEX IX_Octopus_Episodes_Tenant_User
    ON Octopus_Episodes (TenantId, UserId, LastActiveAt DESC);
CREATE INDEX IX_Octopus_Episodes_Session
    ON Octopus_Episodes (TenantId, SessionId);

-- ──────────────────────────────────────────────────────────────────────
CREATE TABLE Octopus_EpisodeMessages (
    Id           BIGINT           NOT NULL IDENTITY PRIMARY KEY,
    TenantId     NVARCHAR(100)    NOT NULL,
    EpisodeId    UNIQUEIDENTIFIER NOT NULL,
    Role         NVARCHAR(20)     NOT NULL,  -- user | assistant | system | tool
    Content      NVARCHAR(MAX)    NOT NULL,
    TokenCount   INT              NOT NULL DEFAULT 0,
    ToolCallId   NVARCHAR(200)    NULL,
    ToolName     NVARCHAR(200)    NULL,
    CreatedAt    DATETIMEOFFSET   NOT NULL DEFAULT SYSDATETIMEOFFSET(),

    CONSTRAINT FK_Messages_Episode FOREIGN KEY (EpisodeId)
        REFERENCES Octopus_Episodes(Id) ON DELETE CASCADE
);

CREATE INDEX IX_Octopus_EpisodeMessages_Episode
    ON Octopus_EpisodeMessages (EpisodeId, CreatedAt ASC);
CREATE INDEX IX_Octopus_EpisodeMessages_Tenant
    ON Octopus_EpisodeMessages (TenantId, CreatedAt DESC);

Octopus_Procedures

CREATE TABLE Octopus_Procedures (
    Id              UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
    TenantId        NVARCHAR(100)    NOT NULL,
    Name            NVARCHAR(200)    NOT NULL,
    Description     NVARCHAR(500)    NOT NULL,
    TriggerPhrases  NVARCHAR(MAX)    NOT NULL,  -- JSON array of strings
    Steps           NVARCHAR(MAX)    NOT NULL,  -- JSON array of step objects
    Priority        INT              NOT NULL DEFAULT 0,
    IsActive        BIT              NOT NULL DEFAULT 1,
    CreatedAt       DATETIMEOFFSET   NOT NULL DEFAULT SYSDATETIMEOFFSET(),
    UpdatedAt       DATETIMEOFFSET   NOT NULL DEFAULT SYSDATETIMEOFFSET(),

    CONSTRAINT UQ_Octopus_Procedures_Tenant_Name UNIQUE (TenantId, Name)
);

CREATE INDEX IX_Octopus_Procedures_TenantActive
    ON Octopus_Procedures (TenantId, IsActive, Priority DESC);

Octopus_AIFunctions

CREATE TABLE Octopus_AIFunctions (
    Id           UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
    TenantId     NVARCHAR(100)    NOT NULL,
    Name         NVARCHAR(200)    NOT NULL,
    Description  NVARCHAR(500)    NOT NULL,
    Code         NVARCHAR(MAX)    NOT NULL,  -- JavaScript source
    InputSchema  NVARCHAR(MAX)    NULL,      -- JSON Schema for LLM tool registration
    IsActive     BIT              NOT NULL DEFAULT 1,
    CreatedAt    DATETIMEOFFSET   NOT NULL DEFAULT SYSDATETIMEOFFSET(),
    UpdatedAt    DATETIMEOFFSET   NOT NULL DEFAULT SYSDATETIMEOFFSET(),
    CreatedBy    NVARCHAR(200)    NULL,
    UpdatedBy    NVARCHAR(200)    NULL,

    CONSTRAINT UQ_Octopus_AIFunctions_Tenant_Name UNIQUE (TenantId, Name)
);

CREATE INDEX IX_Octopus_AIFunctions_TenantActive
    ON Octopus_AIFunctions (TenantId, IsActive);

Never alter these tables directly. All schema changes are managed by EF Core migrations. Direct ALTER TABLE commands will cause migration conflicts and may break the application on next startup.

← Connection Configuration Next: Running Migrations →