EnvironmentPromotionService — 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.Promotions

Owner: Promotion · Retention: lifetime of the product plus 7 years for evidence

CREATE TABLE [dbo].[Promotions] (
    [PromotionId] uniqueidentifier NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [ProjectId] nvarchar(64) NOT NULL,
    [ReleaseId] uniqueidentifier NOT NULL,
    [EnvironmentId] nvarchar(64) NOT NULL,
    [EnvironmentClass] nvarchar(16) NOT NULL,
    [Strategy] int NOT NULL,
    [ApprovalId] uniqueidentifier NULL,
    [RequestedBy] nvarchar(256) NOT NULL,
    [RunId] uniqueidentifier NULL,
    [DesiredStateRef] nvarchar(512) NULL,
    [VerificationJson] nvarchar(max) NULL,
    [PreviousReleaseId] uniqueidentifier NULL,
    [State] int NOT NULL,
    [RequestedAt] datetimeoffset NOT NULL,
    [CompletedAt] datetimeoffset NULL,
    CONSTRAINT [PK_Promotions] PRIMARY KEY ([PromotionId])
);
CREATE INDEX [IX_Promotions_Env_State] ON [dbo].[Promotions] ([TenantId], [EnvironmentId], [State]);
CREATE INDEX [IX_Promotions_Release] ON [dbo].[Promotions] ([ReleaseId]);

Column	Type	Null	Key	Description
PromotionId	uniqueidentifier	no	PK
TenantId	nvarchar(64)	no
ProjectId	nvarchar(64)	no
ReleaseId	uniqueidentifier	no
EnvironmentId	nvarchar(64)	no
EnvironmentClass	nvarchar(16)	no
Strategy	int	no
ApprovalId	uniqueidentifier	yes
RequestedBy	nvarchar(256)	no
RunId	uniqueidentifier	yes
DesiredStateRef	nvarchar(512)	yes
VerificationJson	nvarchar(max)	yes
PreviousReleaseId	uniqueidentifier	yes
State	int	no
RequestedAt	datetimeoffset	no
CompletedAt	datetimeoffset	yes

dbo.PromotionGates

Owner: Promotion

CREATE TABLE [dbo].[PromotionGates] (
    [PromotionId] uniqueidentifier NOT NULL,
    [GateKind] int NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [Passed] bit NOT NULL,
    [EvidenceRef] nvarchar(512) NULL,
    [EvaluatedAt] datetimeoffset NOT NULL,
    CONSTRAINT [PK_PromotionGates] PRIMARY KEY ([PromotionId], [GateKind])
);
ALTER TABLE [dbo].[PromotionGates] ADD CONSTRAINT [FK_PromotionGates_Promotions_PromotionId] FOREIGN KEY ([PromotionId]) REFERENCES [dbo].[Promotions] ([PromotionId]);

Column	Type	Null	Key	Description
PromotionId	uniqueidentifier	no	PK FK → Promotions.PromotionId
GateKind	int	no	PK
TenantId	nvarchar(64)	no
Passed	bit	no
EvidenceRef	nvarchar(512)	yes
EvaluatedAt	datetimeoffset	no