CostUsageService — 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.Budgets

Owner: Budget

CREATE TABLE [dbo].[Budgets] (
    [BudgetId] uniqueidentifier NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [ProjectId] uniqueidentifier NULL,
    [Period] nvarchar(16) NOT NULL,
    [LimitUsd] decimal(18,4) NOT NULL,
    [CommittedUsd] decimal(18,4) NOT NULL DEFAULT (0),
    [ApprovalThresholdUsd] decimal(18,4) NOT NULL,
    [Version] rowversion NOT NULL,
    CONSTRAINT [PK_Budgets] PRIMARY KEY ([BudgetId])
);
CREATE UNIQUE INDEX [UX_Budgets_Tenant_Project_Period] ON [dbo].[Budgets] ([TenantId], [ProjectId], [Period]);

Column	Type	Null	Key	Description
BudgetId	uniqueidentifier	no	PK
TenantId	nvarchar(64)	no
ProjectId	uniqueidentifier	yes
Period	nvarchar(16)	no
LimitUsd	decimal(18,4)	no
CommittedUsd	decimal(18,4)	no
ApprovalThresholdUsd	decimal(18,4)	no
Version	rowversion	no

dbo.Reservations

Owner: Reservation

CREATE TABLE [dbo].[Reservations] (
    [ReservationId] uniqueidentifier NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [ProjectId] uniqueidentifier NOT NULL,
    [RunId] uniqueidentifier NOT NULL,
    [BudgetId] uniqueidentifier NOT NULL,
    [EstimatedUsd] decimal(18,4) NOT NULL,
    [SettledUsd] decimal(18,4) NULL,
    [State] int NOT NULL,
    [ApprovalId] uniqueidentifier NULL,
    [ExpiresAt] datetimeoffset NOT NULL,
    CONSTRAINT [PK_Reservations] PRIMARY KEY ([ReservationId])
);
ALTER TABLE [dbo].[Reservations] ADD CONSTRAINT [FK_Reservations_Budgets_BudgetId] FOREIGN KEY ([BudgetId]) REFERENCES [dbo].[Budgets] ([BudgetId]);
CREATE UNIQUE INDEX [UX_Reservations_Run] ON [dbo].[Reservations] ([RunId]);

Column	Type	Null	Key	Description
ReservationId	uniqueidentifier	no	PK
TenantId	nvarchar(64)	no
ProjectId	uniqueidentifier	no
RunId	uniqueidentifier	no
BudgetId	uniqueidentifier	no	FK → Budgets.BudgetId
EstimatedUsd	decimal(18,4)	no
SettledUsd	decimal(18,4)	yes
State	int	no
ApprovalId	uniqueidentifier	yes
ExpiresAt	datetimeoffset	no

dbo.UsageRecords

Owner: UsageRecord · Retention: 7 years (commercial evidence, D31)

CREATE TABLE [dbo].[UsageRecords] (
    [UsageId] uniqueidentifier NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [ProjectId] uniqueidentifier NOT NULL,
    [RunId] uniqueidentifier NULL,
    [AttemptId] uniqueidentifier NULL,
    [Meter] int NOT NULL,
    [DimensionsJson] nvarchar(2000) NOT NULL,
    [Quantity] decimal(18,6) NOT NULL,
    [Status] int NOT NULL,
    [PricingRevision] nvarchar(32) NOT NULL,
    [EstimatedCostUsd] decimal(18,6) NULL,
    [ConfirmedCostUsd] decimal(18,6) NULL,
    [SourceReceipt] nvarchar(128) NULL,
    [OccurredAt] datetimeoffset NOT NULL,
    CONSTRAINT [PK_UsageRecords] PRIMARY KEY ([UsageId])
);
CREATE UNIQUE INDEX [UX_Usage_Meter_Receipt] ON [dbo].[UsageRecords] ([Meter], [SourceReceipt]) WHERE SourceReceipt IS NOT NULL;
CREATE INDEX [IX_Usage_Tenant_Project_OccurredAt] ON [dbo].[UsageRecords] ([TenantId], [ProjectId], [OccurredAt]);

Column	Type	Null	Key	Description
UsageId	uniqueidentifier	no	PK
TenantId	nvarchar(64)	no
ProjectId	uniqueidentifier	no
RunId	uniqueidentifier	yes
AttemptId	uniqueidentifier	yes
Meter	int	no
DimensionsJson	nvarchar(2000)	no
Quantity	decimal(18,6)	no
Status	int	no
PricingRevision	nvarchar(32)	no
EstimatedCostUsd	decimal(18,6)	yes
ConfirmedCostUsd	decimal(18,6)	yes
SourceReceipt	nvarchar(128)	yes
OccurredAt	datetimeoffset	no

dbo.PricingRevisions

Owner: PricingRevision

CREATE TABLE [dbo].[PricingRevisions] (
    [Revision] nvarchar(32) NOT NULL,
    [Meter] int NOT NULL,
    [DimensionKey] nvarchar(256) NOT NULL,
    [UnitPriceUsd] decimal(18,8) NOT NULL,
    [ValidFrom] datetimeoffset NOT NULL,
    [ValidTo] datetimeoffset NULL,
    CONSTRAINT [PK_PricingRevisions] PRIMARY KEY ([Revision], [Meter], [DimensionKey])
);

Column	Type	Null	Key	Description
Revision	nvarchar(32)	no	PK
Meter	int	no	PK
DimensionKey	nvarchar(256)	no	PK
UnitPriceUsd	decimal(18,8)	no
ValidFrom	datetimeoffset	no
ValidTo	datetimeoffset	yes