Aegis 365 -- Data Model

Aegis 365 organises all state across three storage tiers:

PostgreSQL equivalents: UUID instead of BIGINT identity, jsonb instead of NVARCHAR(MAX), BYTEA instead of VARBINARY, TIMESTAMPTZ instead of DATETIME2.

Core Tables

Sessions -- L0 Context Store

CREATE TABLE Sessions (
    SessionId        BIGINT IDENTITY(1,1) PRIMARY KEY,
    UserId           UNIQUEIDENTIFIER NOT NULL,            -- Azure AD objectId
    AgentId          BIGINT NULL,                          -- agentic parent/child tracking
    ParentAgentId    BIGINT NULL,                          -- Mosaic chain
    SessionState     NVARCHAR(MAX) NOT NULL,               -- JSON: action_history, inference_tiles, risk_score
    RiskScore        DECIMAL(5,2) NOT NULL DEFAULT 0,
    CreatedUtc       DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    ExpiresUtc       DATETIME2 NOT NULL,                   -- Security-Aware TTL (L0 classification-driven)
    RedisKeyHash     VARCHAR(128) NOT NULL,                -- Tier 1 lookup
    INDEX IX_Sessions_User (UserId),
    INDEX IX_Sessions_Expiry (ExpiresUtc)
);

Prompts -- Request/Response Log

CREATE TABLE Prompts (
    PromptId                       BIGINT IDENTITY(1,1) PRIMARY KEY,
    SessionId                      BIGINT NOT NULL FOREIGN KEY REFERENCES Sessions(SessionId),
    L0_BehavioralApprovalStatus    INT NOT NULL,           -- 0=Pending,1=Approved,2=Blocked,3=RequiresHITL
    L1_IntentConfidenceScore       DECIMAL(5,2) NOT NULL,  -- 0-100; >80 = block
    L2_SensitivityClassificationId INT NOT NULL FOREIGN KEY REFERENCES SensitivityClassifications(ClassificationId),
    L3_AnonymizedPrompt            NVARCHAR(MAX) NULL,
    L4_MaskingApplied              BIT NOT NULL DEFAULT 1,
    L5_RoutedRegion                VARCHAR(50) NULL,       -- e.g. Azure Germany Central
    L6_ProofHash                   VARCHAR(128) NULL,      -- SHA256 of ZKP chain
    L7_CacheHit                    BIT NOT NULL DEFAULT 0,
    ResponseHash                   VARCHAR(128) NULL,      -- SHA256 of LLM response
    TimestampUtc                   DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    INDEX IX_Prompts_Session (SessionId),
    INDEX IX_Prompts_Time (TimestampUtc)
);

SensitivityClassifications

CREATE TABLE SensitivityClassifications (
    ClassificationId   INT PRIMARY KEY,        -- 1=Low,2=Medium,3=High,4=Critical
    Name               VARCHAR(50) NOT NULL,
    AnonymizationLevel INT NOT NULL,           -- 1=Redaction,2=Tokenization,3=SDG
    DefaultApprover    VARCHAR(500) NULL,
    TTLMinutes         INT NOT NULL
);

SecureStateMap -- L3/L4 Shared Token-to-Real Mapping

CREATE TABLE SecureStateMap (
    MapId             BIGINT IDENTITY(1,1) PRIMARY KEY,
    SessionId         BIGINT NOT NULL FOREIGN KEY REFERENCES Sessions(SessionId),
    TokenValue        VARCHAR(128) NOT NULL,
    RealValueHash     VARBINARY(256) NOT NULL, -- HMAC of original (never plaintext)
    EncryptedRealValue VARBINARY(MAX) NOT NULL,-- ChaCha20-Poly1305; key in HSM
    CreatedUtc        DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    ExpiresUtc        DATETIME2 NOT NULL,
    AccessLog         NVARCHAR(MAX) NULL,
    CONSTRAINT UQ_Token_Session UNIQUE (SessionId, TokenValue)
);

AuditLog -- L6 Immutable Proof Store

CREATE TABLE AuditLog (
    AuditId        BIGINT IDENTITY(1,1) PRIMARY KEY,
    SessionId      BIGINT NULL,
    EventType      VARCHAR(50) NOT NULL,
    EventPayload   NVARCHAR(MAX) NOT NULL,
    ProofHash      VARCHAR(256) NOT NULL,
    PrevProofHash  VARCHAR(256) NULL,
    ZKProofWitness VARBINARY(MAX) NULL,
    RecordedUtc    DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    ArchiveBlobRef VARCHAR(MAX) NULL,
    INDEX IX_Audit_Session (SessionId),
    INDEX IX_Audit_Type (EventType)
);

Users

CREATE TABLE Users (
    UserId         UNIQUEIDENTIFIER PRIMARY KEY,
    Email          VARCHAR(256) NOT NULL UNIQUE,
    DisplayName    NVARCHAR(256) NOT NULL,
    RoleId         INT NOT NULL FOREIGN KEY REFERENCES Roles(RoleId),
    DepartmentId   UNIQUEIDENTIFIER NULL,
    ClearanceLevel INT NOT NULL DEFAULT 0,
    IsActive       BIT NOT NULL DEFAULT 1,
    LastSyncedUtc  DATETIME2 NOT NULL
);

Roles -- L0 Action Authorization

CREATE TABLE Roles (
    RoleId            INT PRIMARY KEY,
    RoleName          VARCHAR(100) NOT NULL,
    ActionPermissions NVARCHAR(MAX) NOT NULL,
    CanViewAuditLog   BIT NOT NULL DEFAULT 0,
    CanApproveHITL    BIT NOT NULL DEFAULT 0,
    CanModifyPolicy   BIT NOT NULL DEFAULT 0
);

Policies

CREATE TABLE Policies (
    PolicyId    UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    PolicyType  VARCHAR(50) NOT NULL,
    Name        VARCHAR(256) NOT NULL,
    Rules       NVARCHAR(MAX) NOT NULL,
    IsImmutable BIT NOT NULL DEFAULT 0,
    CreatedUtc  DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    CreatedBy   UNIQUEIDENTIFIER NOT NULL FOREIGN KEY REFERENCES Users(UserId),
    ApprovedBy  UNIQUEIDENTIFIER NULL FOREIGN KEY REFERENCES Users(UserId)
);

PrivateSemanticMap -- L2 Tenant-Specific Codename Registry

CREATE TABLE PrivateSemanticMap (
    MapId           UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    TenantId        UNIQUEIDENTIFIER NOT NULL,
    Term            VARCHAR(256) NOT NULL,
    SemanticVariants NVARCHAR(MAX) NOT NULL,
    SensitivityLevel INT NOT NULL,
    CreatedUtc      DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    RetiredUtc      DATETIME2 NULL
);

Redis Key Schema (Tier 1)

Security-Aware TTL