RetentionResidencyService — 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.RetentionRules

Owner: RetentionRule

CREATE TABLE [dbo].[RetentionRules] (
    [RuleId] uniqueidentifier NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [StoreClass] nvarchar(32) NOT NULL,
    [Classification] nvarchar(16) NOT NULL,
    [RetentionDays] int NOT NULL,
    [ExpiryAction] nvarchar(16) NOT NULL,
    [LegalHoldExempt] bit NOT NULL DEFAULT (0),
    [Active] bit NOT NULL DEFAULT (1),
    CONSTRAINT [PK_RetentionRules] PRIMARY KEY ([RuleId])
);
CREATE UNIQUE INDEX [UX_Retention_Scope] ON [dbo].[RetentionRules] ([TenantId], [StoreClass], [Classification]);

Column	Type	Null	Key	Description
RuleId	uniqueidentifier	no	PK
TenantId	nvarchar(64)	no
StoreClass	nvarchar(32)	no
Classification	nvarchar(16)	no
RetentionDays	int	no
ExpiryAction	nvarchar(16)	no
LegalHoldExempt	bit	no
Active	bit	no

dbo.ResidencyRules

Owner: ResidencyRule

CREATE TABLE [dbo].[ResidencyRules] (
    [TenantId] nvarchar(64) NOT NULL,
    [Region] nvarchar(32) NOT NULL,
    [AllowedRegionsJson] nvarchar(256) NOT NULL,
    [AllowedEnginesJson] nvarchar(1024) NOT NULL,
    [ModelDataResidency] nvarchar(16) NOT NULL,
    [ChangedAt] datetimeoffset NOT NULL,
    CONSTRAINT [PK_ResidencyRules] PRIMARY KEY ([TenantId])
);

Column	Type	Null	Key	Description
TenantId	nvarchar(64)	no	PK
Region	nvarchar(32)	no
AllowedRegionsJson	nvarchar(256)	no
AllowedEnginesJson	nvarchar(1024)	no
ModelDataResidency	nvarchar(16)	no
ChangedAt	datetimeoffset	no

dbo.GovernanceOrders

Owner: GovernanceOrder · Retention: 7 years (audit of deletions)

CREATE TABLE [dbo].[GovernanceOrders] (
    [OrderId] uniqueidentifier NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [Kind] int NOT NULL,
    [ScopeJson] nvarchar(2000) NOT NULL,
    [IssuedBy] nvarchar(256) NOT NULL,
    [ApprovalId] uniqueidentifier NULL,
    [State] int NOT NULL,
    [DueAt] datetimeoffset NOT NULL,
    [CompletedAt] datetimeoffset NULL,
    [ExportRef] nvarchar(512) NULL,
    CONSTRAINT [PK_GovernanceOrders] PRIMARY KEY ([OrderId])
);
CREATE INDEX [IX_Orders_Tenant_State] ON [dbo].[GovernanceOrders] ([TenantId], [State], [DueAt]);

Column	Type	Null	Key	Description
OrderId	uniqueidentifier	no	PK
TenantId	nvarchar(64)	no
Kind	int	no
ScopeJson	nvarchar(2000)	no
IssuedBy	nvarchar(256)	no
ApprovalId	uniqueidentifier	yes
State	int	no
DueAt	datetimeoffset	no
CompletedAt	datetimeoffset	yes
ExportRef	nvarchar(512)	yes

dbo.GovernanceOrderTasks

Owner: GovernanceOrder

CREATE TABLE [dbo].[GovernanceOrderTasks] (
    [OrderId] uniqueidentifier NOT NULL,
    [OwnerService] nvarchar(96) NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [State] nvarchar(16) NOT NULL,
    [ReceiptRef] nvarchar(512) NULL,
    [AcknowledgedAt] datetimeoffset NULL,
    CONSTRAINT [PK_GovernanceOrderTasks] PRIMARY KEY ([OrderId], [OwnerService])
);
ALTER TABLE [dbo].[GovernanceOrderTasks] ADD CONSTRAINT [FK_GovernanceOrderTasks_GovernanceOrders_OrderId] FOREIGN KEY ([OrderId]) REFERENCES [dbo].[GovernanceOrders] ([OrderId]);

Column	Type	Null	Key	Description
OrderId	uniqueidentifier	no	PK FK → GovernanceOrders.OrderId
OwnerService	nvarchar(96)	no	PK
TenantId	nvarchar(64)	no
State	nvarchar(16)	no
ReceiptRef	nvarchar(512)	yes
AcknowledgedAt	datetimeoffset	yes