Skip to content

MemoryPromotionService — DDL appendix

Generated from the architecture model

This page is rendered from model/**/*.yaml by tools/docs_model during mkdocs build. Edit the YAML, not this page; hand-written design lives on the linked service, platform and plane pages.

Dialect shown: SQL Server (nvarchar, uniqueidentifier, datetimeoffset). Migrations are authored as FluentMigrator classes in the service's DatabaseModel.Migrations project; the TenantId column and the NHibernate tenant filter follow canonical ADR-0100 in SharedDb mode.

dbo.MemoryItems

Owner: MemoryItem · Retention: project retention (D31); Shared memories follow tenant consent

CREATE TABLE [dbo].[MemoryItems] (
    [MemoryId] uniqueidentifier NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [ProjectId] nvarchar(64) NULL,
    [Scope] int NOT NULL,
    [Class] int NOT NULL,
    [State] int NOT NULL,
    [SummaryRef] nvarchar(512) NOT NULL,
    [ContentHash] nvarchar(128) NOT NULL,
    [OriginRunId] uniqueidentifier NOT NULL,
    [OriginAttemptId] uniqueidentifier NULL,
    [EvidenceRefsJson] nvarchar(max) NOT NULL,
    [Confidence] decimal(5,4) NOT NULL,
    [TrustTier] nvarchar(24) NOT NULL,
    [ReviewApprovalId] uniqueidentifier NULL,
    [Reinforcements] int NOT NULL DEFAULT (0),
    [LastUsedAt] datetimeoffset NULL,
    [ExpiresAt] datetimeoffset NULL,
    [PromotedAt] datetimeoffset NULL,
    [Version] rowversion NOT NULL,
    CONSTRAINT [PK_MemoryItems] PRIMARY KEY ([MemoryId])
);
CREATE INDEX [IX_Memory_Scope_State] ON [dbo].[MemoryItems] ([TenantId], [ProjectId], [Scope], [State]);
CREATE INDEX [IX_Memory_Expiry] ON [dbo].[MemoryItems] ([State], [ExpiresAt]);
Column Type Null Key Description
MemoryId uniqueidentifier no PK
TenantId nvarchar(64) no
ProjectId nvarchar(64) yes
Scope int no
Class int no
State int no
SummaryRef nvarchar(512) no
ContentHash nvarchar(128) no
OriginRunId uniqueidentifier no
OriginAttemptId uniqueidentifier yes
EvidenceRefsJson nvarchar(max) no
Confidence decimal(5,4) no
TrustTier nvarchar(24) no
ReviewApprovalId uniqueidentifier yes
Reinforcements int no
LastUsedAt datetimeoffset yes
ExpiresAt datetimeoffset yes
PromotedAt datetimeoffset yes
Version rowversion no

dbo.ReviewDecisions

Owner: ReviewDecision

CREATE TABLE [dbo].[ReviewDecisions] (
    [DecisionId] uniqueidentifier NOT NULL,
    [MemoryId] uniqueidentifier NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [ApprovalId] uniqueidentifier NOT NULL,
    [ContentHash] nvarchar(128) NOT NULL,
    [DecidedBy] nvarchar(256) NOT NULL,
    [DecidedAt] datetimeoffset NOT NULL,
    [Outcome] nvarchar(24) NOT NULL,
    [Reason] nvarchar(2000) NULL,
    CONSTRAINT [PK_ReviewDecisions] PRIMARY KEY ([DecisionId])
);
ALTER TABLE [dbo].[ReviewDecisions] ADD CONSTRAINT [FK_ReviewDecisions_MemoryItems_MemoryId] FOREIGN KEY ([MemoryId]) REFERENCES [dbo].[MemoryItems] ([MemoryId]);
Column Type Null Key Description
DecisionId uniqueidentifier no PK
MemoryId uniqueidentifier no FK → MemoryItems.MemoryId
TenantId nvarchar(64) no
ApprovalId uniqueidentifier no
ContentHash nvarchar(128) no
DecidedBy nvarchar(256) no
DecidedAt datetimeoffset no
Outcome nvarchar(24) no
Reason nvarchar(2000) yes

Owner: MemoryItem

CREATE TABLE [dbo].[ConsentRecords] (
    [TenantId] nvarchar(64) NOT NULL,
    [ConsentKind] nvarchar(32) NOT NULL,
    [GrantedBy] nvarchar(256) NOT NULL,
    [GrantedAt] datetimeoffset NOT NULL,
    [RevokedAt] datetimeoffset NULL,
    CONSTRAINT [PK_ConsentRecords] PRIMARY KEY ([TenantId], [ConsentKind])
);
Column Type Null Key Description
TenantId nvarchar(64) no PK
ConsentKind nvarchar(32) no PK shared-memory
GrantedBy nvarchar(256) no
GrantedAt datetimeoffset no
RevokedAt datetimeoffset yes