PipelineExecutionService — 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.ChangeProposals

Owner: ChangeProposal · Retention: 1 year with the run

CREATE TABLE [dbo].[ChangeProposals] (
    [ProposalId] uniqueidentifier NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [ProjectId] nvarchar(64) NOT NULL,
    [RunId] uniqueidentifier NOT NULL,
    [AttemptId] uniqueidentifier NOT NULL,
    [AgentReleaseId] nvarchar(256) NOT NULL,
    [ChangeSetRef] nvarchar(512) NOT NULL,
    [System] int NOT NULL,
    [Repository] nvarchar(256) NOT NULL,
    [Branch] nvarchar(256) NOT NULL,
    [PullRequestRef] nvarchar(256) NULL,
    [State] int NOT NULL,
    [ReviewersJson] nvarchar(2000) NULL,
    [MergedCommit] nvarchar(64) NULL,
    [UpdatedAt] datetimeoffset NOT NULL,
    CONSTRAINT [PK_ChangeProposals] PRIMARY KEY ([ProposalId])
);
CREATE INDEX [IX_Proposals_Run] ON [dbo].[ChangeProposals] ([RunId]);
CREATE INDEX [IX_Proposals_Repo_State] ON [dbo].[ChangeProposals] ([TenantId], [Repository], [State]);

Column	Type	Null	Key	Description
ProposalId	uniqueidentifier	no	PK
TenantId	nvarchar(64)	no
ProjectId	nvarchar(64)	no
RunId	uniqueidentifier	no
AttemptId	uniqueidentifier	no
AgentReleaseId	nvarchar(256)	no
ChangeSetRef	nvarchar(512)	no
System	int	no
Repository	nvarchar(256)	no
Branch	nvarchar(256)	no
PullRequestRef	nvarchar(256)	yes
State	int	no
ReviewersJson	nvarchar(2000)	yes
MergedCommit	nvarchar(64)	yes
UpdatedAt	datetimeoffset	no

dbo.ProposalCommits

Owner: ChangeProposal

CREATE TABLE [dbo].[ProposalCommits] (
    [ProposalId] uniqueidentifier NOT NULL,
    [Commit] nvarchar(64) NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [AuthoredAt] datetimeoffset NOT NULL,
    [TrailerJson] nvarchar(512) NOT NULL,
    CONSTRAINT [PK_ProposalCommits] PRIMARY KEY ([ProposalId], [Commit])
);
ALTER TABLE [dbo].[ProposalCommits] ADD CONSTRAINT [FK_ProposalCommits_ChangeProposals_ProposalId] FOREIGN KEY ([ProposalId]) REFERENCES [dbo].[ChangeProposals] ([ProposalId]);

Column	Type	Null	Key	Description
ProposalId	uniqueidentifier	no	PK FK → ChangeProposals.ProposalId
Commit	nvarchar(64)	no	PK
TenantId	nvarchar(64)	no
AuthoredAt	datetimeoffset	no
TrailerJson	nvarchar(512)	no		run id, change-set hash, agent release

dbo.PipelineRuns

Owner: PipelineRun · Retention: 1 year

CREATE TABLE [dbo].[PipelineRuns] (
    [PipelineRunId] uniqueidentifier NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [ProjectId] nvarchar(64) NOT NULL,
    [System] int NOT NULL,
    [ExternalRunRef] nvarchar(256) NOT NULL,
    [DefinitionRef] nvarchar(256) NOT NULL,
    [ProposalId] uniqueidentifier NULL,
    [Commit] nvarchar(64) NOT NULL,
    [State] int NOT NULL,
    [StagesJson] nvarchar(max) NOT NULL,
    [StartedAt] datetimeoffset NOT NULL,
    [CompletedAt] datetimeoffset NULL,
    CONSTRAINT [PK_PipelineRuns] PRIMARY KEY ([PipelineRunId])
);
ALTER TABLE [dbo].[PipelineRuns] ADD CONSTRAINT [FK_PipelineRuns_ChangeProposals_ProposalId] FOREIGN KEY ([ProposalId]) REFERENCES [dbo].[ChangeProposals] ([ProposalId]);
CREATE UNIQUE INDEX [UX_PipelineRuns_External] ON [dbo].[PipelineRuns] ([TenantId], [System], [ExternalRunRef]);
CREATE INDEX [IX_PipelineRuns_Commit] ON [dbo].[PipelineRuns] ([Commit]);

Column	Type	Null	Key	Description
PipelineRunId	uniqueidentifier	no	PK
TenantId	nvarchar(64)	no
ProjectId	nvarchar(64)	no
System	int	no
ExternalRunRef	nvarchar(256)	no
DefinitionRef	nvarchar(256)	no
ProposalId	uniqueidentifier	yes	FK → ChangeProposals.ProposalId
Commit	nvarchar(64)	no
State	int	no
StagesJson	nvarchar(max)	no
StartedAt	datetimeoffset	no
CompletedAt	datetimeoffset	yes

dbo.PipelineResults

Owner: PipelineResult

CREATE TABLE [dbo].[PipelineResults] (
    [PipelineRunId] uniqueidentifier NOT NULL,
    [Kind] int NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [Passed] bit NOT NULL,
    [SummaryJson] nvarchar(2000) NOT NULL,
    [ArtifactRef] nvarchar(512) NULL,
    CONSTRAINT [PK_PipelineResults] PRIMARY KEY ([PipelineRunId], [Kind])
);
ALTER TABLE [dbo].[PipelineResults] ADD CONSTRAINT [FK_PipelineResults_PipelineRuns_PipelineRunId] FOREIGN KEY ([PipelineRunId]) REFERENCES [dbo].[PipelineRuns] ([PipelineRunId]);

Column	Type	Null	Key	Description
PipelineRunId	uniqueidentifier	no	PK FK → PipelineRuns.PipelineRunId
Kind	int	no	PK
TenantId	nvarchar(64)	no
Passed	bit	no
SummaryJson	nvarchar(2000)	no
ArtifactRef	nvarchar(512)	yes