03_billing.sql

-- ==========================================================================
-- BillingDB - SaaS subscription billing (Stripe-style object graph)
-- Accounts -> Subscriptions (Plans) -> Invoices -> InvoiceLineItems, with a
-- large append-only UsageEvents fact. All tables have an IDENTITY PK +
-- Change Tracking. Data loaded by loader/.
-- ==========================================================================

IF DB_ID('BillingDB') IS NOT NULL
BEGIN
    ALTER DATABASE BillingDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE BillingDB;
END
GO

CREATE DATABASE BillingDB;
GO

USE BillingDB;
GO

-- SIMPLE recovery keeps the transaction log small during large bulk loads.
ALTER DATABASE BillingDB SET RECOVERY SIMPLE;
GO

ALTER DATABASE BillingDB SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
GO

-- --------------------------------------------------------------------------
-- Dimensions
-- --------------------------------------------------------------------------

CREATE TABLE Accounts (
    AccountID   INT IDENTITY(1,1) PRIMARY KEY,
    AccountUuid UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() CONSTRAINT UQ_Accounts_Uuid UNIQUE,
    CompanyName NVARCHAR(200) NOT NULL,
    Status      NVARCHAR(20)  NOT NULL DEFAULT 'trialing',
    CreatedAt   DATETIME2(3)  NOT NULL DEFAULT SYSUTCDATETIME(),
    UpdatedAt   DATETIME2(3)  NOT NULL DEFAULT SYSUTCDATETIME(),
    IsDeleted   BIT           NOT NULL DEFAULT 0
);
GO

CREATE TABLE Users (
    UserID    INT IDENTITY(1,1) PRIMARY KEY,
    AccountID INT           NOT NULL,
    Email     NVARCHAR(256) NOT NULL CONSTRAINT UQ_Users_Email UNIQUE,
    Role      NVARCHAR(20)  NOT NULL DEFAULT 'member',
    CreatedAt DATETIME2(3)  NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT FK_Users_Account FOREIGN KEY (AccountID) REFERENCES Accounts(AccountID)
);
GO

CREATE TABLE Plans (
    PlanID          INT IDENTITY(1,1) PRIMARY KEY,
    PlanCode        NVARCHAR(40)  NOT NULL CONSTRAINT UQ_Plans_Code UNIQUE,
    Name            NVARCHAR(100) NOT NULL,
    BillingInterval NVARCHAR(10)  NOT NULL DEFAULT 'month',
    MonthlyPrice    DECIMAL(18,2) NOT NULL,
    CurrencyCode    CHAR(3)       NOT NULL DEFAULT 'USD',
    IsActive        BIT           NOT NULL DEFAULT 1,
    CreatedAt       DATETIME2(3)  NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

CREATE TABLE Subscriptions (
    SubscriptionID     INT IDENTITY(1,1) PRIMARY KEY,
    AccountID          INT NOT NULL,
    PlanID             INT NOT NULL,
    Status             NVARCHAR(20) NOT NULL DEFAULT 'active',
    CurrentPeriodStart DATETIME2(3) NOT NULL,
    CurrentPeriodEnd   DATETIME2(3) NOT NULL,
    CancelAt           DATETIME2(3) NULL,
    CreatedAt          DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
    UpdatedAt          DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT FK_Subscriptions_Account FOREIGN KEY (AccountID) REFERENCES Accounts(AccountID),
    CONSTRAINT FK_Subscriptions_Plan    FOREIGN KEY (PlanID)    REFERENCES Plans(PlanID)
);
GO

-- --------------------------------------------------------------------------
-- Facts
-- --------------------------------------------------------------------------

CREATE TABLE Invoices (
    InvoiceID      BIGINT IDENTITY(1,1) PRIMARY KEY,
    InvoiceNumber  NVARCHAR(30)  NOT NULL CONSTRAINT UQ_Invoices_Number UNIQUE,
    AccountID      INT           NOT NULL,
    SubscriptionID INT NULL,
    Status         NVARCHAR(20)  NOT NULL DEFAULT 'draft',
    Subtotal       DECIMAL(18,2),
    TaxAmount      DECIMAL(18,2),
    Total          DECIMAL(18,2),
    CurrencyCode   CHAR(3)       NOT NULL DEFAULT 'USD',
    IssuedAt       DATETIME2(3),
    DueAt          DATETIME2(3),
    CreatedAt      DATETIME2(3)  NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT FK_Invoices_Account      FOREIGN KEY (AccountID)      REFERENCES Accounts(AccountID),
    CONSTRAINT FK_Invoices_Subscription FOREIGN KEY (SubscriptionID) REFERENCES Subscriptions(SubscriptionID)
);
GO

CREATE TABLE InvoiceLineItems (
    InvoiceLineID BIGINT IDENTITY(1,1) PRIMARY KEY,
    InvoiceID     BIGINT        NOT NULL,
    PlanID        INT NULL,
    Description   NVARCHAR(200),
    Quantity      INT,
    UnitPrice     DECIMAL(18,2),
    Amount        DECIMAL(18,2),
    PeriodStart   DATETIME2(3),
    PeriodEnd     DATETIME2(3),
    CONSTRAINT FK_InvoiceLines_Invoice FOREIGN KEY (InvoiceID) REFERENCES Invoices(InvoiceID),
    CONSTRAINT FK_InvoiceLines_Plan    FOREIGN KEY (PlanID)    REFERENCES Plans(PlanID)
);
GO

CREATE TABLE UsageEvents (
    UsageEventID   BIGINT IDENTITY(1,1) PRIMARY KEY,
    SubscriptionID INT           NOT NULL,
    EventName      NVARCHAR(60)  NOT NULL,
    Quantity       DECIMAL(18,4) NOT NULL,
    OccurredAt     DATETIME2(3)  NOT NULL,
    CreatedAt      DATETIME2(3)  NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT FK_Usage_Subscription FOREIGN KEY (SubscriptionID) REFERENCES Subscriptions(SubscriptionID)
);
GO

CREATE TABLE Payments (
    PaymentID   BIGINT IDENTITY(1,1) PRIMARY KEY,
    InvoiceID   BIGINT        NOT NULL,
    Amount      DECIMAL(18,2) NOT NULL,
    Status      NVARCHAR(20)  NOT NULL DEFAULT 'succeeded',
    Method      NVARCHAR(20),
    ProcessedAt DATETIME2(3),
    CreatedAt   DATETIME2(3)  NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT FK_BillPayments_Invoice FOREIGN KEY (InvoiceID) REFERENCES Invoices(InvoiceID)
);
GO

-- --------------------------------------------------------------------------
-- Secondary indexes
-- --------------------------------------------------------------------------
CREATE INDEX IX_Users_Account          ON Users(AccountID);
CREATE INDEX IX_Subscriptions_Account  ON Subscriptions(AccountID);
CREATE INDEX IX_Subscriptions_Plan     ON Subscriptions(PlanID);
CREATE INDEX IX_Invoices_Account       ON Invoices(AccountID);
CREATE INDEX IX_Invoices_Subscription  ON Invoices(SubscriptionID);
CREATE INDEX IX_Invoices_Issued        ON Invoices(IssuedAt);
CREATE INDEX IX_InvoiceLines_Invoice   ON InvoiceLineItems(InvoiceID);
CREATE INDEX IX_Usage_SubOccurred      ON UsageEvents(SubscriptionID, OccurredAt);
CREATE INDEX IX_BillPayments_Invoice   ON Payments(InvoiceID);
GO

-- --------------------------------------------------------------------------
-- Enable Change Tracking on every table.
-- --------------------------------------------------------------------------
ALTER TABLE Accounts         ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE Users            ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE Plans            ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE Subscriptions    ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE Invoices         ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE InvoiceLineItems ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE UsageEvents      ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE Payments         ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
GO

PRINT 'BillingDB schema created.';
GO