RunControlService — 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.Runs

Owner: Run · Retention: 1 year by default, tenant-configurable (D31)

CREATE TABLE [dbo].[Runs] (
    [RunId] uniqueidentifier NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [ProjectId] nvarchar(64) NOT NULL,
    [EnvironmentId] nvarchar(64) NULL,
    [IdempotencyKey] nvarchar(128) NOT NULL,
    [WorkflowId] nvarchar(128) NOT NULL,
    [ReleaseId] nvarchar(128) NOT NULL,
    [PolicyRevision] nvarchar(64) NOT NULL,
    [ModelProfileId] nvarchar(64) NOT NULL,
    [State] int NOT NULL,
    [InputHash] nvarchar(128) NOT NULL,
    [SnapshotId] uniqueidentifier NOT NULL,
    [BudgetReservationId] uniqueidentifier NULL,
    [RequestedBy] nvarchar(256) NOT NULL,
    [Version] rowversion NOT NULL,
    [CreatedAt] datetimeoffset NOT NULL,
    [UpdatedAt] datetimeoffset NOT NULL,
    CONSTRAINT [PK_Runs] PRIMARY KEY ([RunId])
);
CREATE UNIQUE INDEX [UX_Runs_Tenant_IdempotencyKey] ON [dbo].[Runs] ([TenantId], [IdempotencyKey]);
CREATE INDEX [IX_Runs_Tenant_Project_State] ON [dbo].[Runs] ([TenantId], [ProjectId], [State]);

Column	Type	Null	Key	Description
RunId	uniqueidentifier	no	PK
TenantId	nvarchar(64)	no
ProjectId	nvarchar(64)	no
EnvironmentId	nvarchar(64)	yes
IdempotencyKey	nvarchar(128)	no
WorkflowId	nvarchar(128)	no
ReleaseId	nvarchar(128)	no
PolicyRevision	nvarchar(64)	no
ModelProfileId	nvarchar(64)	no
State	int	no
InputHash	nvarchar(128)	no
SnapshotId	uniqueidentifier	no
BudgetReservationId	uniqueidentifier	yes
RequestedBy	nvarchar(256)	no
Version	rowversion	no
CreatedAt	datetimeoffset	no
UpdatedAt	datetimeoffset	no

dbo.CancellationIntents

Owner: CancellationIntent

CREATE TABLE [dbo].[CancellationIntents] (
    [RunId] uniqueidentifier NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [RequestedBy] nvarchar(256) NOT NULL,
    [Reason] nvarchar(1024) NULL,
    [RequestedAt] datetimeoffset NOT NULL,
    CONSTRAINT [PK_CancellationIntents] PRIMARY KEY ([RunId])
);
ALTER TABLE [dbo].[CancellationIntents] ADD CONSTRAINT [FK_CancellationIntents_Runs_RunId] FOREIGN KEY ([RunId]) REFERENCES [dbo].[Runs] ([RunId]);

Column	Type	Null	Key	Description
RunId	uniqueidentifier	no	PK FK → Runs.RunId
TenantId	nvarchar(64)	no
RequestedBy	nvarchar(256)	no
Reason	nvarchar(1024)	yes
RequestedAt	datetimeoffset	no

dbo.RecoveryJournal

Owner: RecoveryJournalEntry

CREATE TABLE [dbo].[RecoveryJournal] (
    [RunId] uniqueidentifier NOT NULL,
    [Sequence] bigint NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [FromState] int NULL,
    [ToState] int NOT NULL,
    [Cause] nvarchar(256) NOT NULL,
    [OccurredAt] datetimeoffset NOT NULL,
    CONSTRAINT [PK_RecoveryJournal] PRIMARY KEY ([RunId], [Sequence])
);
ALTER TABLE [dbo].[RecoveryJournal] ADD CONSTRAINT [FK_RecoveryJournal_Runs_RunId] FOREIGN KEY ([RunId]) REFERENCES [dbo].[Runs] ([RunId]);
-- Append-only: UPDATE and DELETE are denied to the service principal; restore order places this table first (D30).

Column	Type	Null	Key	Description
RunId	uniqueidentifier	no	PK FK → Runs.RunId
Sequence	bigint	no	PK
TenantId	nvarchar(64)	no
FromState	int	yes
ToState	int	no
Cause	nvarchar(256)	no
OccurredAt	datetimeoffset	no

dbo.Steps

Owner: Step

CREATE TABLE [dbo].[Steps] (
    [StepId] uniqueidentifier NOT NULL,
    [RunId] uniqueidentifier NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [Ordinal] int NOT NULL,
    [AgentId] nvarchar(128) NOT NULL,
    [SkillId] nvarchar(128) NULL,
    [State] int NOT NULL,
    [MaxAttempts] int NOT NULL DEFAULT (3),
    [Gate] nvarchar(32) NULL,
    CONSTRAINT [PK_Steps] PRIMARY KEY ([StepId])
);
ALTER TABLE [dbo].[Steps] ADD CONSTRAINT [FK_Steps_Runs_RunId] FOREIGN KEY ([RunId]) REFERENCES [dbo].[Runs] ([RunId]);
CREATE UNIQUE INDEX [UX_Steps_Run_Ordinal] ON [dbo].[Steps] ([RunId], [Ordinal]);

Column	Type	Null	Key	Description
StepId	uniqueidentifier	no	PK
RunId	uniqueidentifier	no	FK → Runs.RunId
TenantId	nvarchar(64)	no
Ordinal	int	no
AgentId	nvarchar(128)	no
SkillId	nvarchar(128)	yes
State	int	no
MaxAttempts	int	no
Gate	nvarchar(32)	yes

dbo.Attempts

Owner: Attempt

CREATE TABLE [dbo].[Attempts] (
    [AttemptId] uniqueidentifier NOT NULL,
    [StepId] uniqueidentifier NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [Number] int NOT NULL,
    [LeaseId] uniqueidentifier NULL,
    [Outcome] int NULL,
    [OutcomeHash] nvarchar(128) NULL,
    [FailureClass] nvarchar(32) NULL,
    [DispatchedAt] datetimeoffset NOT NULL,
    [SettledAt] datetimeoffset NULL,
    CONSTRAINT [PK_Attempts] PRIMARY KEY ([AttemptId])
);
ALTER TABLE [dbo].[Attempts] ADD CONSTRAINT [FK_Attempts_Steps_StepId] FOREIGN KEY ([StepId]) REFERENCES [dbo].[Steps] ([StepId]);
CREATE UNIQUE INDEX [UX_Attempts_Step_Number] ON [dbo].[Attempts] ([StepId], [Number]);
CREATE INDEX [IX_Attempts_Open] ON [dbo].[Attempts] ([TenantId], [SettledAt]) WHERE SettledAt IS NULL;

Column	Type	Null	Key	Description
AttemptId	uniqueidentifier	no	PK
StepId	uniqueidentifier	no	FK → Steps.StepId
TenantId	nvarchar(64)	no
Number	int	no
LeaseId	uniqueidentifier	yes
Outcome	int	yes
OutcomeHash	nvarchar(128)	yes
FailureClass	nvarchar(32)	yes
DispatchedAt	datetimeoffset	no
SettledAt	datetimeoffset	yes

dbo.Leases

Owner: Lease

CREATE TABLE [dbo].[Leases] (
    [LeaseId] uniqueidentifier NOT NULL,
    [AttemptId] uniqueidentifier NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [Holder] nvarchar(256) NOT NULL,
    [FencingToken] bigint NOT NULL,
    [ExpiresAt] datetimeoffset NOT NULL,
    CONSTRAINT [PK_Leases] PRIMARY KEY ([LeaseId])
);
ALTER TABLE [dbo].[Leases] ADD CONSTRAINT [FK_Leases_Attempts_AttemptId] FOREIGN KEY ([AttemptId]) REFERENCES [dbo].[Attempts] ([AttemptId]);
CREATE UNIQUE INDEX [UX_Leases_Attempt] ON [dbo].[Leases] ([AttemptId]);
CREATE INDEX [IX_Leases_ExpiresAt] ON [dbo].[Leases] ([ExpiresAt]);

Column	Type	Null	Key	Description
LeaseId	uniqueidentifier	no	PK
AttemptId	uniqueidentifier	no	FK → Attempts.AttemptId
TenantId	nvarchar(64)	no
Holder	nvarchar(256)	no
FencingToken	bigint	no
ExpiresAt	datetimeoffset	no

dbo.ApprovalRequests

Owner: ApprovalRequest

CREATE TABLE [dbo].[ApprovalRequests] (
    [ApprovalId] uniqueidentifier NOT NULL,
    [RunId] uniqueidentifier NOT NULL,
    [StepId] uniqueidentifier NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [ActionClass] nvarchar(32) NOT NULL,
    [ProposalHash] nvarchar(128) NOT NULL,
    [PolicyRevision] nvarchar(64) NOT NULL,
    [ApproverRule] nvarchar(256) NOT NULL,
    [ExpiresAt] datetimeoffset NOT NULL,
    [State] int NOT NULL,
    [DecidedBy] nvarchar(256) NULL,
    [DecidedAt] datetimeoffset NULL,
    [Reason] nvarchar(2048) NULL,
    [Version] rowversion NOT NULL,
    CONSTRAINT [PK_ApprovalRequests] PRIMARY KEY ([ApprovalId])
);
ALTER TABLE [dbo].[ApprovalRequests] ADD CONSTRAINT [FK_ApprovalRequests_Runs_RunId] FOREIGN KEY ([RunId]) REFERENCES [dbo].[Runs] ([RunId]);
ALTER TABLE [dbo].[ApprovalRequests] ADD CONSTRAINT [FK_ApprovalRequests_Steps_StepId] FOREIGN KEY ([StepId]) REFERENCES [dbo].[Steps] ([StepId]);
CREATE INDEX [IX_Approvals_Tenant_State_Expires] ON [dbo].[ApprovalRequests] ([TenantId], [State], [ExpiresAt]);
CREATE INDEX [IX_Approvals_Run] ON [dbo].[ApprovalRequests] ([RunId]);

Column	Type	Null	Key	Description
ApprovalId	uniqueidentifier	no	PK
RunId	uniqueidentifier	no	FK → Runs.RunId
StepId	uniqueidentifier	no	FK → Steps.StepId
TenantId	nvarchar(64)	no
ActionClass	nvarchar(32)	no
ProposalHash	nvarchar(128)	no
PolicyRevision	nvarchar(64)	no
ApproverRule	nvarchar(256)	no
ExpiresAt	datetimeoffset	no
State	int	no
DecidedBy	nvarchar(256)	yes
DecidedAt	datetimeoffset	yes
Reason	nvarchar(2048)	yes
Version	rowversion	no