SecretBrokerService — 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.SecretReferences

Owner: SecretReference

CREATE TABLE [dbo].[SecretReferences] (
    [ReferenceId] nvarchar(128) NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [Kind] int NOT NULL,
    [VaultRef] nvarchar(512) NOT NULL,
    [OwnerKind] nvarchar(32) NOT NULL,
    [OwnerId] nvarchar(256) NOT NULL,
    [AllowedPurposesJson] nvarchar(512) NOT NULL,
    [MaxTtlSeconds] int NOT NULL,
    [RotationDays] int NOT NULL,
    [LastRotatedAt] datetimeoffset NULL,
    [Revoked] bit NOT NULL DEFAULT (0),
    CONSTRAINT [PK_SecretReferences] PRIMARY KEY ([ReferenceId])
);
CREATE INDEX [IX_SecretRefs_Owner] ON [dbo].[SecretReferences] ([TenantId], [OwnerKind], [OwnerId]);

Column	Type	Null	Key	Description
ReferenceId	nvarchar(128)	no	PK
TenantId	nvarchar(64)	no
Kind	int	no
VaultRef	nvarchar(512)	no
OwnerKind	nvarchar(32)	no
OwnerId	nvarchar(256)	no
AllowedPurposesJson	nvarchar(512)	no
MaxTtlSeconds	int	no
RotationDays	int	no
LastRotatedAt	datetimeoffset	yes
Revoked	bit	no

dbo.RotationReceipts

Owner: SecretReference

CREATE TABLE [dbo].[RotationReceipts] (
    [ReceiptId] uniqueidentifier NOT NULL,
    [ReferenceId] nvarchar(128) NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [RotatedAt] datetimeoffset NOT NULL,
    [RotatedBy] nvarchar(256) NOT NULL,
    [PreviousVersionRef] nvarchar(512) NOT NULL,
    CONSTRAINT [PK_RotationReceipts] PRIMARY KEY ([ReceiptId])
);
ALTER TABLE [dbo].[RotationReceipts] ADD CONSTRAINT [FK_RotationReceipts_SecretReferences_ReferenceId] FOREIGN KEY ([ReferenceId]) REFERENCES [dbo].[SecretReferences] ([ReferenceId]);

Column	Type	Null	Key	Description
ReceiptId	uniqueidentifier	no	PK
ReferenceId	nvarchar(128)	no	FK → SecretReferences.ReferenceId
TenantId	nvarchar(64)	no
RotatedAt	datetimeoffset	no
RotatedBy	nvarchar(256)	no
PreviousVersionRef	nvarchar(512)	no

dbo.Issuances

Owner: Issuance · Retention: 7 years as audit evidence (issuance metadata only)

CREATE TABLE [dbo].[Issuances] (
    [IssuanceId] uniqueidentifier NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [ReferenceId] nvarchar(128) NOT NULL,
    [SubjectJson] nvarchar(2000) NOT NULL,
    [Purpose] nvarchar(32) NOT NULL,
    [ScopeJson] nvarchar(2000) NOT NULL,
    [PolicyDecisionId] uniqueidentifier NOT NULL,
    [State] int NOT NULL,
    [IssuedAt] datetimeoffset NOT NULL,
    [ExpiresAt] datetimeoffset NOT NULL,
    [RevokedAt] datetimeoffset NULL,
    CONSTRAINT [PK_Issuances] PRIMARY KEY ([IssuanceId])
);
ALTER TABLE [dbo].[Issuances] ADD CONSTRAINT [FK_Issuances_SecretReferences_ReferenceId] FOREIGN KEY ([ReferenceId]) REFERENCES [dbo].[SecretReferences] ([ReferenceId]);
CREATE INDEX [IX_Issuances_Reference_State] ON [dbo].[Issuances] ([ReferenceId], [State]);
CREATE INDEX [IX_Issuances_Expiry] ON [dbo].[Issuances] ([State], [ExpiresAt]);

Column	Type	Null	Key	Description
IssuanceId	uniqueidentifier	no	PK
TenantId	nvarchar(64)	no
ReferenceId	nvarchar(128)	no	FK → SecretReferences.ReferenceId
SubjectJson	nvarchar(2000)	no
Purpose	nvarchar(32)	no
ScopeJson	nvarchar(2000)	no
PolicyDecisionId	uniqueidentifier	no
State	int	no
IssuedAt	datetimeoffset	no
ExpiresAt	datetimeoffset	no
RevokedAt	datetimeoffset	yes