Procedure Storage

SQL Schema

CREATE TABLE Octopus_Procedures (
    Id              UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT NEWSEQUENTIALID(),
    Name            NVARCHAR(200)    NOT NULL,
    Description     NVARCHAR(1000)   NULL,
    TriggerPattern  NVARCHAR(500)    NOT NULL,  -- pattern for matching
    StepsJson       NVARCHAR(MAX)    NOT NULL,  -- JSON array of ProcedureStep
    AgentId         UNIQUEIDENTIFIER NULL,       -- NULL = shared across all agents in tenant
    TenantId        NVARCHAR(100)    NOT NULL,
    IsApproved      BIT              NOT NULL DEFAULT 0,
    IsShared        BIT              NOT NULL DEFAULT 0,
    IsActive        BIT              NOT NULL DEFAULT 1,
    CreatedBy       NVARCHAR(100)    NOT NULL,  -- 'admin' or 'agent'
    ApprovedBy      NVARCHAR(200)    NULL,
    ApprovedAt      DATETIMEOFFSET   NULL,
    CreatedAt       DATETIMEOFFSET   NOT NULL DEFAULT SYSUTCDATETIME(),
    UpdatedAt       DATETIMEOFFSET   NOT NULL DEFAULT SYSUTCDATETIME()
);

CREATE INDEX IX_Procedures_Agent ON Octopus_Procedures (TenantId, AgentId, IsApproved, IsActive);

Procedure Step JSON Schema

// StepsJson is an array of ProcedureStep objects:
[
  {
    "order": 1,
    "instruction": "Collect vendor name and contact from the user.",
    "toolName": null,
    "toolArgumentTemplate": null,
    "condition": null,
    "isOptional": false
  },
  {
    "order": 2,
    "instruction": "Look up the vendor in the system.",
    "toolName": "vendor_lookup",
    "toolArgumentTemplate": "{\"name\": \"{{vendor_name}}\"}",
    "condition": null,
    "isOptional": false
  },
  {
    "order": 3,
    "instruction": "Confirm creation to the user.",
    "toolName": "vendor_create",
    "toolArgumentTemplate": "{\"name\":\"{{vendor_name}}\",\"contact\":\"{{contact}}\"}",
    "condition": "vendor_lookup.result == null",
    "isOptional": false
  }
]

EF Core Mapping

modelBuilder.Entity<ProcedureEntity>(e =>
{
    e.ToTable("Octopus_Procedures");
    e.HasKey(x => x.Id);
    e.Property(x => x.TenantId).IsRequired().HasMaxLength(100);
    e.Property(x => x.Name).IsRequired().HasMaxLength(200);
    e.Property(x => x.TriggerPattern).IsRequired().HasMaxLength(500);
    e.Property(x => x.StepsJson).HasColumnType("nvarchar(max)");
    // Global query filter: only approved, active procedures are visible by default
    e.HasQueryFilter(x => x.IsApproved && x.IsActive && x.TenantId == _tenantId);
});