ProjectService — 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.Projects

Owner: Project

CREATE TABLE [dbo].[Projects] (
    [ProjectId] uniqueidentifier NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [Key] nvarchar(32) NOT NULL,
    [Name] nvarchar(256) NOT NULL,
    [State] int NOT NULL,
    [Region] nvarchar(32) NOT NULL,
    [PolicyOverrideRevision] nvarchar(64) NULL,
    [RetentionDays] int NOT NULL DEFAULT (365),
    [BudgetId] uniqueidentifier NULL,
    [Version] rowversion NOT NULL,
    [CreatedAt] datetimeoffset NOT NULL,
    CONSTRAINT [PK_Projects] PRIMARY KEY ([ProjectId])
);
CREATE UNIQUE INDEX [UX_Projects_Tenant_Key] ON [dbo].[Projects] ([TenantId], [Key]);

Column	Type	Null	Key	Description
ProjectId	uniqueidentifier	no	PK
TenantId	nvarchar(64)	no
Key	nvarchar(32)	no
Name	nvarchar(256)	no
State	int	no
Region	nvarchar(32)	no
PolicyOverrideRevision	nvarchar(64)	yes
RetentionDays	int	no
BudgetId	uniqueidentifier	yes
Version	rowversion	no
CreatedAt	datetimeoffset	no

dbo.Memberships

Owner: Membership

CREATE TABLE [dbo].[Memberships] (
    [ProjectId] uniqueidentifier NOT NULL,
    [SubjectId] nvarchar(256) NOT NULL,
    [Role] int NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [GrantedBy] nvarchar(256) NOT NULL,
    [GrantedAt] datetimeoffset NOT NULL,
    CONSTRAINT [PK_Memberships] PRIMARY KEY ([ProjectId], [SubjectId], [Role])
);
ALTER TABLE [dbo].[Memberships] ADD CONSTRAINT [FK_Memberships_Projects_ProjectId] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[Projects] ([ProjectId]);

Column	Type	Null	Key	Description
ProjectId	uniqueidentifier	no	PK FK → Projects.ProjectId
SubjectId	nvarchar(256)	no	PK
Role	int	no	PK
TenantId	nvarchar(64)	no
GrantedBy	nvarchar(256)	no
GrantedAt	datetimeoffset	no

dbo.Delegations

Owner: Delegation

CREATE TABLE [dbo].[Delegations] (
    [DelegationId] uniqueidentifier NOT NULL,
    [ProjectId] uniqueidentifier NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [GrantorId] nvarchar(256) NOT NULL,
    [GranteeId] nvarchar(256) NOT NULL,
    [Role] int NOT NULL,
    [ExpiresAt] datetimeoffset NOT NULL,
    [Reason] nvarchar(1024) NOT NULL,
    [RevokedAt] datetimeoffset NULL,
    CONSTRAINT [PK_Delegations] PRIMARY KEY ([DelegationId])
);
ALTER TABLE [dbo].[Delegations] ADD CONSTRAINT [FK_Delegations_Projects_ProjectId] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[Projects] ([ProjectId]);
CREATE INDEX [IX_Delegations_Grantee_Active] ON [dbo].[Delegations] ([TenantId], [GranteeId], [ExpiresAt]) WHERE RevokedAt IS NULL;

Column	Type	Null	Key	Description
DelegationId	uniqueidentifier	no	PK
ProjectId	uniqueidentifier	no	FK → Projects.ProjectId
TenantId	nvarchar(64)	no
GrantorId	nvarchar(256)	no
GranteeId	nvarchar(256)	no
Role	int	no
ExpiresAt	datetimeoffset	no
Reason	nvarchar(1024)	no
RevokedAt	datetimeoffset	yes

dbo.SourceScopes

Owner: SourceScopeEntry

CREATE TABLE [dbo].[SourceScopes] (
    [ProjectId] uniqueidentifier NOT NULL,
    [Kind] nvarchar(32) NOT NULL,
    [Reference] nvarchar(512) NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [ConnectionId] uniqueidentifier NULL,
    CONSTRAINT [PK_SourceScopes] PRIMARY KEY ([ProjectId], [Kind], [Reference])
);
ALTER TABLE [dbo].[SourceScopes] ADD CONSTRAINT [FK_SourceScopes_Projects_ProjectId] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[Projects] ([ProjectId]);

Column	Type	Null	Key	Description
ProjectId	uniqueidentifier	no	PK FK → Projects.ProjectId
Kind	nvarchar(32)	no	PK
Reference	nvarchar(512)	no	PK
TenantId	nvarchar(64)	no
ConnectionId	uniqueidentifier	yes