PolicyEngineService — 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.PolicyBundles

Owner: PolicyBundle

CREATE TABLE [dbo].[PolicyBundles] (
    [Revision] nvarchar(64) NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [Scope] int NOT NULL,
    [ScopeId] nvarchar(64) NOT NULL,
    [ReleaseId] nvarchar(256) NOT NULL,
    [ParentRevision] nvarchar(64) NULL,
    [RulesRef] nvarchar(512) NOT NULL,
    [RulesHash] nvarchar(128) NOT NULL,
    [DomainsJson] nvarchar(512) NOT NULL,
    [Active] bit NOT NULL DEFAULT (0),
    [ActivatedAt] datetimeoffset NULL,
    CONSTRAINT [PK_PolicyBundles] PRIMARY KEY ([Revision])
);
CREATE INDEX [IX_PolicyBundles_Scope_Active] ON [dbo].[PolicyBundles] ([TenantId], [Scope], [ScopeId], [Active]);

Column	Type	Null	Key	Description
Revision	nvarchar(64)	no	PK
TenantId	nvarchar(64)	no
Scope	int	no
ScopeId	nvarchar(64)	no
ReleaseId	nvarchar(256)	no
ParentRevision	nvarchar(64)	yes
RulesRef	nvarchar(512)	no
RulesHash	nvarchar(128)	no
DomainsJson	nvarchar(512)	no
Active	bit	no
ActivatedAt	datetimeoffset	yes

dbo.PolicyRules

Owner: PolicyRule

CREATE TABLE [dbo].[PolicyRules] (
    [Revision] nvarchar(64) NOT NULL,
    [RuleId] nvarchar(128) NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [Domain] int NOT NULL,
    [MatchJson] nvarchar(max) NOT NULL,
    [Effect] int NOT NULL,
    [ObligationsJson] nvarchar(2000) NULL,
    [Priority] int NOT NULL,
    CONSTRAINT [PK_PolicyRules] PRIMARY KEY ([Revision], [RuleId])
);
ALTER TABLE [dbo].[PolicyRules] ADD CONSTRAINT [FK_PolicyRules_PolicyBundles_Revision] FOREIGN KEY ([Revision]) REFERENCES [dbo].[PolicyBundles] ([Revision]);

Column	Type	Null	Key	Description
Revision	nvarchar(64)	no	PK FK → PolicyBundles.Revision
RuleId	nvarchar(128)	no	PK
TenantId	nvarchar(64)	no
Domain	int	no
MatchJson	nvarchar(max)	no
Effect	int	no
ObligationsJson	nvarchar(2000)	yes
Priority	int	no

dbo.PolicyDecisions

Owner: PolicyDecision · Retention: 7 years as audit evidence (D31)

CREATE TABLE [dbo].[PolicyDecisions] (
    [DecisionId] uniqueidentifier NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [ProjectId] nvarchar(64) NULL,
    [EnvironmentId] nvarchar(64) NULL,
    [RunId] uniqueidentifier NULL,
    [SubjectJson] nvarchar(2000) NOT NULL,
    [Domain] int NOT NULL,
    [Action] nvarchar(256) NOT NULL,
    [ActionClass] nvarchar(16) NOT NULL,
    [ResourceJson] nvarchar(2000) NOT NULL,
    [InputsHash] nvarchar(128) NULL,
    [PolicyRevision] nvarchar(64) NOT NULL,
    [Effect] int NOT NULL,
    [ObligationsJson] nvarchar(2000) NULL,
    [MatchedRulesJson] nvarchar(2000) NOT NULL,
    [ReasonsJson] nvarchar(2000) NOT NULL,
    [EvaluatedAt] datetimeoffset NOT NULL,
    CONSTRAINT [PK_PolicyDecisions] PRIMARY KEY ([DecisionId])
);
CREATE INDEX [IX_Decisions_Run] ON [dbo].[PolicyDecisions] ([RunId]);
CREATE INDEX [IX_Decisions_Tenant_EvaluatedAt] ON [dbo].[PolicyDecisions] ([TenantId], [EvaluatedAt]);

Column	Type	Null	Key	Description
DecisionId	uniqueidentifier	no	PK
TenantId	nvarchar(64)	no
ProjectId	nvarchar(64)	yes
EnvironmentId	nvarchar(64)	yes
RunId	uniqueidentifier	yes
SubjectJson	nvarchar(2000)	no
Domain	int	no
Action	nvarchar(256)	no
ActionClass	nvarchar(16)	no
ResourceJson	nvarchar(2000)	no
InputsHash	nvarchar(128)	yes
PolicyRevision	nvarchar(64)	no
Effect	int	no
ObligationsJson	nvarchar(2000)	yes
MatchedRulesJson	nvarchar(2000)	no
ReasonsJson	nvarchar(2000)	no
EvaluatedAt	datetimeoffset	no