Episode Storage — Episodic Memory

SQL Schema — Octopus_Episodes

CREATE TABLE Octopus_Episodes (
    Id               UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT NEWSEQUENTIALID(),
    SessionId        UNIQUEIDENTIFIER NOT NULL,
    AgentId          UNIQUEIDENTIFIER NOT NULL,
    UserId           UNIQUEIDENTIFIER NOT NULL,
    TenantId         NVARCHAR(100)    NOT NULL,
    MessagesJson     NVARCHAR(MAX)    NOT NULL,     -- JSON array
    ToolCallsJson    NVARCHAR(MAX)    NULL,          -- JSON array
    Summary          NVARCHAR(2000)   NULL,
    SummaryEmbedding VARBINARY(MAX)   NULL,          -- float[] binary
    StartedAt        DATETIMEOFFSET   NOT NULL,
    EndedAt          DATETIMEOFFSET   NULL,
    EndReason        NVARCHAR(50)     NULL,          -- 'UserClosed','Timeout','AgentClosed'
    MessageCount     INT              NOT NULL DEFAULT 0,
    TotalInputTokens INT              NOT NULL DEFAULT 0,
    TotalOutputTokens INT             NOT NULL DEFAULT 0,
    IsArchived       BIT              NOT NULL DEFAULT 0,
    CreatedAt        DATETIMEOFFSET   NOT NULL DEFAULT SYSUTCDATETIME()
);

CREATE INDEX IX_Episodes_User_Agent ON Octopus_Episodes (TenantId, UserId, AgentId, StartedAt DESC);
CREATE INDEX IX_Episodes_Session    ON Octopus_Episodes (SessionId, TenantId);

EpisodicMemoryService — StoreAsync

public class EpisodicMemoryService : IEpisodicMemoryStore
{
    public async Task StoreAsync(Episode episode, CancellationToken ct = default)
    {
        var entity = new EpisodeEntity
        {
            Id = episode.Id,
            SessionId = episode.SessionId,
            AgentId = episode.AgentId,
            UserId = episode.UserId,
            TenantId = episode.TenantId,
            MessagesJson = JsonSerializer.Serialize(episode.Messages),
            ToolCallsJson = JsonSerializer.Serialize(episode.ToolCalls),
            Summary = episode.Summary,
            SummaryEmbedding = episode.SummaryEmbedding != null
                ? SerializeEmbedding(episode.SummaryEmbedding)
                : null,
            StartedAt = episode.StartedAt,
            EndedAt = episode.EndedAt,
            EndReason = episode.EndReason.ToString(),
            MessageCount = episode.MessageCount,
            TotalInputTokens = episode.TotalInputTokens,
            TotalOutputTokens = episode.TotalOutputTokens
        };

        _db.Episodes.Add(entity);
        await _db.SaveChangesAsync(ct);
    }
}

Performance Considerations

Concern	Recommendation
MessagesJson size	For very long conversations, consider storing messages in a separate table to avoid row bloat
Embedding storage	SummaryEmbedding as VARBINARY is adequate for <1M episodes; at scale, offload embeddings to Qdrant
Query performance	The composite index on (TenantId, UserId, AgentId, StartedAt) covers all common recall queries
Archival	Set `IsArchived = 1` for episodes older than TTL; exclude from live queries with `WHERE IsArchived = 0`

EF Core Configuration

// OctopusDbContext episode mapping
modelBuilder.Entity<EpisodeEntity>(e =>
{
    e.ToTable("Octopus_Episodes");
    e.HasKey(x => x.Id);
    e.Property(x => x.TenantId).IsRequired().HasMaxLength(100);
    e.HasQueryFilter(x => x.TenantId == _tenantContext.TenantId); // global tenant filter
    e.Property(x => x.MessagesJson).HasColumnType("nvarchar(max)");
    e.Property(x => x.SummaryEmbedding).HasColumnType("varbinary(max)");
});

← Episode Structure Next: Recalling Past Episodes →