FleetInventoryService — 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.OperatedProducts

Owner: OperatedProduct

CREATE TABLE [dbo].[OperatedProducts] (
    [ProductId] nvarchar(128) NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [ProjectId] nvarchar(64) NOT NULL,
    [Name] nvarchar(128) NOT NULL,
    [GeneratedByJson] nvarchar(2000) NOT NULL,
    [CurrentReleaseId] uniqueidentifier NULL,
    [Edition] int NOT NULL,
    [GatewayRef] nvarchar(256) NOT NULL,
    [InstanceCount] int NOT NULL DEFAULT (0),
    [OnboardedAt] datetimeoffset NOT NULL,
    CONSTRAINT [PK_OperatedProducts] PRIMARY KEY ([ProductId])
);
CREATE INDEX [IX_Products_Tenant_Edition] ON [dbo].[OperatedProducts] ([TenantId], [Edition]);

Column	Type	Null	Key	Description
ProductId	nvarchar(128)	no	PK
TenantId	nvarchar(64)	no
ProjectId	nvarchar(64)	no
Name	nvarchar(128)	no
GeneratedByJson	nvarchar(2000)	no
CurrentReleaseId	uniqueidentifier	yes
Edition	int	no
GatewayRef	nvarchar(256)	no
InstanceCount	int	no
OnboardedAt	datetimeoffset	no

dbo.ProductInstances

Owner: ProductInstance

CREATE TABLE [dbo].[ProductInstances] (
    [InstanceId] uniqueidentifier NOT NULL,
    [ProductId] nvarchar(128) NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [CustomerTenantId] nvarchar(64) NOT NULL,
    [EnvironmentId] nvarchar(64) NOT NULL,
    [Region] nvarchar(32) NOT NULL,
    [ReleaseId] uniqueidentifier NOT NULL,
    [State] int NOT NULL,
    [Health] nvarchar(16) NOT NULL,
    [LastObservedAt] datetimeoffset NOT NULL,
    CONSTRAINT [PK_ProductInstances] PRIMARY KEY ([InstanceId])
);
ALTER TABLE [dbo].[ProductInstances] ADD CONSTRAINT [FK_ProductInstances_OperatedProducts_ProductId] FOREIGN KEY ([ProductId]) REFERENCES [dbo].[OperatedProducts] ([ProductId]);
CREATE UNIQUE INDEX [UX_Instances_Env] ON [dbo].[ProductInstances] ([ProductId], [CustomerTenantId], [EnvironmentId]);
CREATE INDEX [IX_Instances_State] ON [dbo].[ProductInstances] ([TenantId], [State]);
CREATE INDEX [IX_Instances_Release] ON [dbo].[ProductInstances] ([ReleaseId]);

Column	Type	Null	Key	Description
InstanceId	uniqueidentifier	no	PK
ProductId	nvarchar(128)	no	FK → OperatedProducts.ProductId
TenantId	nvarchar(64)	no
CustomerTenantId	nvarchar(64)	no
EnvironmentId	nvarchar(64)	no
Region	nvarchar(32)	no
ReleaseId	uniqueidentifier	no
State	int	no
Health	nvarchar(16)	no
LastObservedAt	datetimeoffset	no

dbo.ProductObjectives

Owner: ProductObjective

CREATE TABLE [dbo].[ProductObjectives] (
    [ObjectiveId] uniqueidentifier NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [ProductId] nvarchar(128) NOT NULL,
    [Kind] nvarchar(32) NOT NULL,
    [Target] decimal(12,6) NOT NULL,
    [Window] nvarchar(16) NOT NULL,
    [SloId] uniqueidentifier NULL,
    [EditionFloor] int NOT NULL,
    CONSTRAINT [PK_ProductObjectives] PRIMARY KEY ([ObjectiveId])
);
ALTER TABLE [dbo].[ProductObjectives] ADD CONSTRAINT [FK_ProductObjectives_OperatedProducts_ProductId] FOREIGN KEY ([ProductId]) REFERENCES [dbo].[OperatedProducts] ([ProductId]);
CREATE UNIQUE INDEX [UX_Objectives_Product_Kind] ON [dbo].[ProductObjectives] ([ProductId], [Kind]);

Column	Type	Null	Key	Description
ObjectiveId	uniqueidentifier	no	PK
TenantId	nvarchar(64)	no
ProductId	nvarchar(128)	no	FK → OperatedProducts.ProductId
Kind	nvarchar(32)	no
Target	decimal(12,6)	no
Window	nvarchar(16)	no
SloId	uniqueidentifier	yes
EditionFloor	int	no