LicenseService — 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.PricingPlans

Owner: PricingPlan

CREATE TABLE [dbo].[PricingPlans] (
    [PricingId] uniqueidentifier NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [ListingId] uniqueidentifier NOT NULL,
    [Model] int NOT NULL,
    [AmountUsd] decimal(18,4) NULL,
    [IncludedEditionsJson] nvarchar(256) NOT NULL,
    [MeteringDimension] nvarchar(32) NULL,
    [TrialDays] int NULL,
    [RevenueSharePct] decimal(5,2) NOT NULL,
    [Active] bit NOT NULL DEFAULT (1),
    CONSTRAINT [PK_PricingPlans] PRIMARY KEY ([PricingId])
);
CREATE INDEX [IX_Pricing_Listing] ON [dbo].[PricingPlans] ([ListingId], [Active]);

Column	Type	Null	Key	Description
PricingId	uniqueidentifier	no	PK
TenantId	nvarchar(64)	no
ListingId	uniqueidentifier	no
Model	int	no
AmountUsd	decimal(18,4)	yes
IncludedEditionsJson	nvarchar(256)	no
MeteringDimension	nvarchar(32)	yes
TrialDays	int	yes
RevenueSharePct	decimal(5,2)	no
Active	bit	no

dbo.Entitlements

Owner: Entitlement · Retention: 7 years

CREATE TABLE [dbo].[Entitlements] (
    [EntitlementId] uniqueidentifier NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [ListingId] uniqueidentifier NOT NULL,
    [PricingId] uniqueidentifier NOT NULL,
    [SubscriptionId] uniqueidentifier NOT NULL,
    [Seats] int NULL,
    [State] int NOT NULL,
    [ValidFrom] datetimeoffset NOT NULL,
    [ValidTo] datetimeoffset NULL,
    [LicenseTermsRef] nvarchar(512) NOT NULL,
    [AcquiredBy] nvarchar(256) NOT NULL,
    CONSTRAINT [PK_Entitlements] PRIMARY KEY ([EntitlementId])
);
ALTER TABLE [dbo].[Entitlements] ADD CONSTRAINT [FK_Entitlements_PricingPlans_PricingId] FOREIGN KEY ([PricingId]) REFERENCES [dbo].[PricingPlans] ([PricingId]);
CREATE UNIQUE INDEX [UX_Entitlements_Tenant_Listing] ON [dbo].[Entitlements] ([TenantId], [ListingId]);

Column	Type	Null	Key	Description
EntitlementId	uniqueidentifier	no	PK
TenantId	nvarchar(64)	no
ListingId	uniqueidentifier	no
PricingId	uniqueidentifier	no	FK → PricingPlans.PricingId
SubscriptionId	uniqueidentifier	no
Seats	int	yes
State	int	no
ValidFrom	datetimeoffset	no
ValidTo	datetimeoffset	yes
LicenseTermsRef	nvarchar(512)	no
AcquiredBy	nvarchar(256)	no

dbo.Publishers

Owner: Publisher

CREATE TABLE [dbo].[Publishers] (
    [PublisherId] uniqueidentifier NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [Name] nvarchar(128) NOT NULL,
    [State] int NOT NULL,
    [AgreementRef] nvarchar(512) NOT NULL,
    [PayoutRef] nvarchar(256) NULL,
    [RatingAverage] decimal(3,2) NULL,
    [CertifiedAt] datetimeoffset NULL,
    CONSTRAINT [PK_Publishers] PRIMARY KEY ([PublisherId])
);

Column	Type	Null	Key	Description
PublisherId	uniqueidentifier	no	PK
TenantId	nvarchar(64)	no
Name	nvarchar(128)	no
State	int	no
AgreementRef	nvarchar(512)	no
PayoutRef	nvarchar(256)	yes
RatingAverage	decimal(3,2)	yes
CertifiedAt	datetimeoffset	yes

dbo.Reviews

Owner: Publisher

CREATE TABLE [dbo].[Reviews] (
    [ListingId] uniqueidentifier NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [PublisherId] uniqueidentifier NOT NULL,
    [Rating] int NOT NULL,
    [TextRef] nvarchar(512) NULL,
    [ReviewedAt] datetimeoffset NOT NULL,
    CONSTRAINT [PK_Reviews] PRIMARY KEY ([ListingId], [TenantId])
);
ALTER TABLE [dbo].[Reviews] ADD CONSTRAINT [FK_Reviews_Publishers_PublisherId] FOREIGN KEY ([PublisherId]) REFERENCES [dbo].[Publishers] ([PublisherId]);

Column	Type	Null	Key	Description
ListingId	uniqueidentifier	no	PK
TenantId	nvarchar(64)	no	PK
PublisherId	uniqueidentifier	no	FK → Publishers.PublisherId
Rating	int	no
TextRef	nvarchar(512)	yes
ReviewedAt	datetimeoffset	no