PostgreSQL Migrations for Subscription Billing and Transition Auditing

Good to see the lifecycle model reach the database layer. In the previous lesson, you defined a provider-neutral subscription state machine and kept cancellation scheduling separate from lifecycle status. Now you will make the most important parts of that model durable: a PostgreSQL schema that rejects structurally invalid billing data even if a script, admin tool, or future code path bypasses your NestJS services.

The goal is not to force every business rule into SQL. The database should enforce row-local facts, identity, referential integrity, and uniqueness; the domain service should enforce transition permissions and rules involving several rows or external provider facts. By the end, you will have a TypeORM migration for subscription records, provider customer and subscription references, billing periods, and an append-only transition audit.

---

1. Decide what the database owns

A subscription module has two kinds of data:

1. Current aggregate state, queried frequently by the entitlement evaluator and customer UI.
2. Financial and lifecycle history, needed for support, reconciliation, renewals, and internal administration.

For this course, use these tables:

Table	Purpose
billing_provider_customers	Maps a billable owner to a customer reference at a billing provider.
billing_subscriptions	Stores current local lifecycle state, selected immutable prices, provider subscription identity, and cancellation context.
billing_subscription_periods	Stores each non-overlapping billed or trial period. One row is marked current.
billing_subscription_transitions	An append-only record of every accepted lifecycle transition, including valid self-transitions such as a successful renewal.

The relationships are deliberate:

• A subscription references immutable plan and price versions created in the previous lesson.
• A billing period and a transition audit row belong to one subscription.
• A provider customer reference uses the stable owner_type and owner_id integration boundary rather than a foreign key to a specific users or organizations table.

That last choice is essential for a reusable module. PostgreSQL cannot create a conventional foreign key to “either users or organizations,” and it should not know the host application’s ownership model. Your application’s host-integration service verifies that an owner exists and that an actor may administer it.

Before writing the migration, review PostgreSQL’s distinction between row checks, uniqueness, and foreign keys.

Constraints are not a replacement for the state machine

The database can enforce facts such as:

• a subscription status is one of the defined local statuses;
• seat quantity cannot be negative;
• a billing period has a start before its end;
• a transition record points to a real subscription;
• a provider’s subscription reference cannot map to two local subscriptions;
• only one current billing period exists per subscription.

The database cannot safely enforce these rules using ordinary CHECK constraints:

• whether a new transition is allowed from the prior state;
• whether a transition audit’s from_status equals the actual previous subscription status;
• whether a subscription has a current period in another table;
• whether an external owner ID actually exists;
• whether the current time has passed a trial or grace deadline.

Those require the state-machine service, database transactions, and later, row-level locking. PostgreSQL explicitly warns against writing CHECK constraints that depend on other rows or tables.

---

2. Model provider references without coupling to one provider

Provider-neutral does not mean provider-identity-free. You need durable external references to support webhooks, API calls, retries, reconciliation, and support investigations.

There are two different identities:

Identity	Stored where	Why
Provider customer reference	billing_provider_customers	One provider customer commonly represents an owner across multiple subscriptions.
Provider subscription reference	billing_subscriptions	Maps one provider-managed recurring agreement to one local subscription.

For example, an organization may be represented as:

owner_type: organization
owner_id:   org_8f4...
provider:   stripe
customer:   cus_123...
subscription: sub_456...

The provider_name is a stable adapter key such as stripe, paddle, or fake. Do not use a display name that marketing or configuration code can change.

A provider subscription reference must be globally unique within the provider. Otherwise, a duplicated webhook, a migration defect, or a mistaken manual repair could cause the same external subscription to activate two local owners.

---

3. Use an explicit migration, not synchronize

For production billing data, keep TypeORM synchronize: false and commit reviewed migrations. Automatic synchronization is convenient during early experimentation, but it does not give you an auditable, reproducible schema history or a safe path for nontrivial PostgreSQL features such as partial unique indexes and exclusion constraints.

This TypeORM migration uses queryRunner.query() with PostgreSQL DDL rather than only Table objects. That is intentional:

• TypeORM’s table API is useful for ordinary tables and foreign keys.
• Raw SQL makes PostgreSQL-specific constraints visible and reviewable.
• A partial unique index, such as “one open subscription per owner,” cannot be expressed as a standard UNIQUE constraint.
• The range exclusion constraint for billing periods is also PostgreSQL-specific.

If you want a short implementation refresher before coding, watch the migration anatomy and QueryRunner portions of the curated video.

Assumptions from the earlier plan-and-price migration

The migration below assumes your prior migration created these immutable catalog tables:

• billing_plan_versions(id)
• billing_price_versions(id)

Rename these foreign-key targets if your actual names differ. The important design point is that a subscription references version rows, not a mutable plan slug or price amount.

This migration also assumes your NestJS application generates UUIDs before inserts. That avoids silently relying on a particular PostgreSQL UUID extension. If your project already has a standard UUID database default, use it consistently instead.

---

4. Implement the PostgreSQL migration

Create a descriptively named migration, for example:

CreateBillingSubscriptionLedger

Then adapt the following migration to your project’s migration naming convention.

import { MigrationInterface, QueryRunner } from 'typeorm';

export class CreateBillingSubscriptionLedger1710000000000
  implements MigrationInterface
{
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`
      CREATE EXTENSION IF NOT EXISTS btree_gist;
    `);

    await queryRunner.query(`
      CREATE TABLE billing_provider_customers (
        id UUID PRIMARY KEY,

        owner_type VARCHAR(64) NOT NULL,
        owner_id TEXT NOT NULL,

        provider_name VARCHAR(64) NOT NULL,
        provider_customer_ref TEXT NOT NULL,

        created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

        CONSTRAINT ck_provider_customers_owner_type_nonempty
          CHECK (owner_type <> ''),

        CONSTRAINT ck_provider_customers_owner_id_nonempty
          CHECK (owner_id <> ''),

        CONSTRAINT ck_provider_customers_provider_name_nonempty
          CHECK (provider_name <> ''),

        CONSTRAINT ck_provider_customers_reference_nonempty
          CHECK (provider_customer_ref <> ''),

        CONSTRAINT uq_provider_customers_owner_provider
          UNIQUE (owner_type, owner_id, provider_name),

        CONSTRAINT uq_provider_customers_provider_reference
          UNIQUE (provider_name, provider_customer_ref)
      );
    `);

    await queryRunner.query(`
      CREATE TABLE billing_subscriptions (
        id UUID PRIMARY KEY,

        owner_type VARCHAR(64) NOT NULL,
        owner_id TEXT NOT NULL,

        plan_version_id UUID NOT NULL,
        fixed_price_version_id UUID NULL,
        per_seat_price_version_id UUID NULL,

        currency CHAR(3) NOT NULL,
        seat_quantity INTEGER NOT NULL DEFAULT 0,

        status VARCHAR(32) NOT NULL,

        provider_name VARCHAR(64) NULL,
        provider_subscription_ref TEXT NULL,
        provider_version BIGINT NULL,

        trial_ends_at TIMESTAMPTZ NULL,

        cancellation_requested_at TIMESTAMPTZ NULL,
        cancel_at_period_end BOOLEAN NOT NULL DEFAULT FALSE,

        ended_at TIMESTAMPTZ NULL,

        created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

        CONSTRAINT fk_subscriptions_plan_version
          FOREIGN KEY (plan_version_id)
          REFERENCES billing_plan_versions (id)
          ON DELETE RESTRICT,

        CONSTRAINT fk_subscriptions_fixed_price_version
          FOREIGN KEY (fixed_price_version_id)
          REFERENCES billing_price_versions (id)
          ON DELETE RESTRICT,

        CONSTRAINT fk_subscriptions_per_seat_price_version
          FOREIGN KEY (per_seat_price_version_id)
          REFERENCES billing_price_versions (id)
          ON DELETE RESTRICT,

        CONSTRAINT ck_subscriptions_owner_type_nonempty
          CHECK (owner_type <> ''),

        CONSTRAINT ck_subscriptions_owner_id_nonempty
          CHECK (owner_id <> ''),

        CONSTRAINT ck_subscriptions_price_selection
          CHECK (
            fixed_price_version_id IS NOT NULL
            OR per_seat_price_version_id IS NOT NULL
          ),

        CONSTRAINT ck_subscriptions_currency_format
          CHECK (currency ~ '^[A-Z]{3}$'),

        CONSTRAINT ck_subscriptions_seat_quantity
          CHECK (seat_quantity >= 0),

        CONSTRAINT ck_subscriptions_status
          CHECK (
            status IN (
              'pending_activation',
              'trialing',
              'active',
              'past_due',
              'suspended',
              'cancelled',
              'expired'
            )
          ),

        CONSTRAINT ck_subscriptions_provider_reference_pair
          CHECK (
            (
              provider_name IS NULL
              AND provider_subscription_ref IS NULL
            )
            OR
            (
              provider_name IS NOT NULL
              AND provider_subscription_ref IS NOT NULL
            )
          ),

        CONSTRAINT ck_subscriptions_provider_version
          CHECK (
            provider_version IS NULL
            OR provider_version >= 0
          ),

        CONSTRAINT ck_subscriptions_established_state_provider_ref
          CHECK (
            status NOT IN ('active', 'past_due', 'suspended')
            OR (
              provider_name IS NOT NULL
              AND provider_subscription_ref IS NOT NULL
            )
          ),

        CONSTRAINT ck_subscriptions_trial_end
          CHECK (
            status <> 'trialing'
            OR trial_ends_at IS NOT NULL
          ),

        CONSTRAINT ck_subscriptions_terminal_end
          CHECK (
            (
              status IN ('cancelled', 'expired')
              AND ended_at IS NOT NULL
            )
            OR
            (
              status NOT IN ('cancelled', 'expired')
              AND ended_at IS NULL
            )
          ),

        CONSTRAINT ck_subscriptions_cancel_request
          CHECK (
            cancel_at_period_end = FALSE
            OR cancellation_requested_at IS NOT NULL
          ),

        CONSTRAINT ck_subscriptions_trial_after_creation
          CHECK (
            trial_ends_at IS NULL
            OR trial_ends_at >= created_at
          ),

        CONSTRAINT ck_subscriptions_ended_after_creation
          CHECK (
            ended_at IS NULL
            OR ended_at >= created_at
          )
      );
    `);

    await queryRunner.query(`
      CREATE UNIQUE INDEX uq_subscriptions_provider_subscription
      ON billing_subscriptions (
        provider_name,
        provider_subscription_ref
      )
      WHERE provider_name IS NOT NULL
        AND provider_subscription_ref IS NOT NULL;
    `);

    await queryRunner.query(`
      CREATE UNIQUE INDEX uq_subscriptions_one_open_subscription_per_owner
      ON billing_subscriptions (owner_type, owner_id)
      WHERE status IN (
        'pending_activation',
        'trialing',
        'active',
        'past_due',
        'suspended'
      );
    `);

    await queryRunner.query(`
      CREATE INDEX ix_subscriptions_owner_status
      ON billing_subscriptions (owner_type, owner_id, status);
    `);

    await queryRunner.query(`
      CREATE TABLE billing_subscription_periods (
        id UUID PRIMARY KEY,

        subscription_id UUID NOT NULL,

        period_start TIMESTAMPTZ NOT NULL,
        period_end TIMESTAMPTZ NOT NULL,

        period_kind VARCHAR(16) NOT NULL,
        is_current BOOLEAN NOT NULL DEFAULT FALSE,

        provider_name VARCHAR(64) NULL,
        provider_invoice_ref TEXT NULL,

        created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

        CONSTRAINT fk_subscription_periods_subscription
          FOREIGN KEY (subscription_id)
          REFERENCES billing_subscriptions (id)
          ON DELETE RESTRICT,

        CONSTRAINT ck_subscription_periods_range
          CHECK (period_start < period_end),

        CONSTRAINT ck_subscription_periods_kind
          CHECK (period_kind IN ('trial', 'paid')),

        CONSTRAINT ck_subscription_periods_invoice_reference_pair
          CHECK (
            (
              provider_name IS NULL
              AND provider_invoice_ref IS NULL
            )
            OR
            (
              provider_name IS NOT NULL
              AND provider_invoice_ref IS NOT NULL
            )
          ),

        CONSTRAINT uq_subscription_periods_start
          UNIQUE (subscription_id, period_start),

        CONSTRAINT ex_subscription_periods_no_overlap
          EXCLUDE USING gist (
            subscription_id WITH =,
            tstzrange(period_start, period_end, '[)') WITH &&
          )
      );
    `);

    await queryRunner.query(`
      CREATE UNIQUE INDEX uq_subscription_periods_current
      ON billing_subscription_periods (subscription_id)
      WHERE is_current = TRUE;
    `);

    await queryRunner.query(`
      CREATE UNIQUE INDEX uq_subscription_periods_provider_invoice
      ON billing_subscription_periods (
        provider_name,
        provider_invoice_ref
      )
      WHERE provider_name IS NOT NULL
        AND provider_invoice_ref IS NOT NULL;
    `);

    await queryRunner.query(`
      CREATE INDEX ix_subscription_periods_subscription_end
      ON billing_subscription_periods (
        subscription_id,
        period_end DESC
      );
    `);

    await queryRunner.query(`
      CREATE TABLE billing_subscription_transitions (
        id UUID PRIMARY KEY,

        subscription_id UUID NOT NULL,
        sequence_number INTEGER NOT NULL,

        from_status VARCHAR(32) NULL,
        to_status VARCHAR(32) NOT NULL,

        event_type VARCHAR(128) NOT NULL,
        source VARCHAR(24) NOT NULL,

        provider_name VARCHAR(64) NULL,
        provider_event_id TEXT NULL,

        actor_type VARCHAR(64) NULL,
        actor_id TEXT NULL,

        reason_code VARCHAR(128) NULL,
        metadata JSONB NOT NULL DEFAULT '{}'::jsonb,

        occurred_at TIMESTAMPTZ NOT NULL,
        recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

        CONSTRAINT fk_subscription_transitions_subscription
          FOREIGN KEY (subscription_id)
          REFERENCES billing_subscriptions (id)
          ON DELETE RESTRICT,

        CONSTRAINT ck_subscription_transitions_sequence
          CHECK (sequence_number > 0),

        CONSTRAINT ck_subscription_transitions_from_status
          CHECK (
            from_status IS NULL
            OR from_status IN (
              'pending_activation',
              'trialing',
              'active',
              'past_due',
              'suspended',
              'cancelled',
              'expired'
            )
          ),

        CONSTRAINT ck_subscription_transitions_to_status
          CHECK (
            to_status IN (
              'pending_activation',
              'trialing',
              'active',
              'past_due',
              'suspended',
              'cancelled',
              'expired'
            )
          ),

        CONSTRAINT ck_subscription_transitions_source
          CHECK (
            source IN (
              'command',
              'provider_event',
              'policy_job',
              'reconciliation'
            )
          ),

        CONSTRAINT ck_subscription_transitions_provider_event_pair
          CHECK (
            (
              provider_name IS NULL
              AND provider_event_id IS NULL
            )
            OR
            (
              provider_name IS NOT NULL
              AND provider_event_id IS NOT NULL
            )
          ),

        CONSTRAINT ck_subscription_transitions_provider_source
          CHECK (
            source <> 'provider_event'
            OR provider_event_id IS NOT NULL
          ),

        CONSTRAINT ck_subscription_transitions_actor_pair
          CHECK (
            (
              actor_type IS NULL
              AND actor_id IS NULL
            )
            OR
            (
              actor_type IS NOT NULL
              AND actor_id IS NOT NULL
            )
          ),

        CONSTRAINT uq_subscription_transitions_sequence
          UNIQUE (subscription_id, sequence_number)
      );
    `);

    await queryRunner.query(`
      CREATE UNIQUE INDEX uq_subscription_transitions_provider_event
      ON billing_subscription_transitions (
        provider_name,
        provider_event_id
      )
      WHERE provider_name IS NOT NULL
        AND provider_event_id IS NOT NULL;
    `);

    await queryRunner.query(`
      CREATE INDEX ix_subscription_transitions_subscription_occurred
      ON billing_subscription_transitions (
        subscription_id,
        occurred_at DESC
      );
    `);
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`
      DROP TABLE billing_subscription_transitions;
    `);

    await queryRunner.query(`
      DROP TABLE billing_subscription_periods;
    `);

    await queryRunner.query(`
      DROP TABLE billing_subscriptions;
    `);

    await queryRunner.query(`
      DROP TABLE billing_provider_customers;
    `);

    // Do not drop btree_gist here. It may be shared by other migrations.
  }
}

---

5. Understand the constraints before relying on them

Immutable catalog references use RESTRICT

The plan and prices that a subscription selected are part of financial history. This is why the foreign keys use ON DELETE RESTRICT.

A retired offering should remain in the catalog with an availability flag set to false. It should not be deleted merely because it is no longer purchasable. If it were deleted, you would lose the ability to explain an old invoice or reconstruct a subscription’s historical terms.

The pair checks prevent half-written provider identities

This constraint:

CHECK (
  (provider_name IS NULL AND provider_subscription_ref IS NULL)
  OR
  (provider_name IS NOT NULL AND provider_subscription_ref IS NOT NULL)
)

prevents records such as:

provider_name: stripe
provider_subscription_ref: null

A pending_activation subscription may legitimately have neither value before the provider has created a recurring agreement. But an active, past_due, or suspended subscription must have both.

This mirrors the lifecycle invariant from the prior lesson at the persistence boundary.

Partial unique indexes encode lifecycle intent

PostgreSQL cannot express “one nonterminal row per owner” as an ordinary UNIQUE constraint because the restriction applies only to some statuses. This partial unique index does exactly that:

CREATE UNIQUE INDEX uq_subscriptions_one_open_subscription_per_owner
ON billing_subscriptions (owner_type, owner_id)
WHERE status IN (
  'pending_activation',
  'trialing',
  'active',
  'past_due',
  'suspended'
);

It permits historical cancelled and expired records while preventing accidental duplicate checkouts or two concurrent active subscriptions for the same owner.

This design assumes one subscription entitlement bundle per owner. If your product eventually needs independent subscriptions for distinct products, add a non-null subscription_scope or product_key column and include it in this index:

(owner_type, owner_id, subscription_scope)

Do this only when the domain truly needs it. Do not weaken the uniqueness rule prematurely.

Billing-period ranges should not overlap

A subscription has many periods over time, but its periods should not overlap. PostgreSQL’s range exclusion constraint enforces that at the database level:

EXCLUDE USING gist (
  subscription_id WITH =,
  tstzrange(period_start, period_end, '[)') WITH &&
)

The range uses [) semantics:

• the start is included;
• the end is excluded.

Therefore, these adjacent periods are valid:

Period	Range
April	April 1 through May 1
May	May 1 through June 1

But a second row from April 15 through May 15 conflicts with the first.

btree_gist enables equality comparisons for UUID values in this GiST index. In some managed PostgreSQL environments, extension creation requires a privileged deployment role. If that applies, coordinate the extension installation with your DBA or platform migration process rather than silently dropping the overlap protection.

Keep one row marked current

is_current is a query optimization and a clear operational marker. It allows the entitlement evaluator and subscription-summary endpoint to retrieve the present period without inferring it from dates.

The partial unique index ensures at most one current period:

CREATE UNIQUE INDEX uq_subscription_periods_current
ON billing_subscription_periods (subscription_id)
WHERE is_current = TRUE;

When a renewal is confirmed, update the old current period to false and insert the new one within the same transaction that updates subscription state and writes the transition audit. Later lessons will add row-level locking around that operation.

A transition audit is an event ledger, not a copied subscription table

The billing_subscription_transitions table records a domain event:

from_status: active
to_status: active
event_type: provider.billing_period_renewed
source: provider_event

The status is unchanged, but the renewal is commercially significant. Therefore, do not add a constraint requiring from_status <> to_status.

The table uses:

• sequence_number for one ordered local transition stream per subscription;
• a unique provider event index for a first persistence-level defense against duplicate provider deliveries;
• metadata for bounded, non-authoritative diagnostic details;
• machine-readable event_type, source, and reason_code fields instead of relying on prose notes.

Avoid placing raw payment details, full provider payloads, card data, or unnecessary personal information in metadata. The webhook inbox introduced later is the appropriate durable location for normalized event payloads and processing details.

---

6. Run and verify the migration as a database contract

After adding the migration:

1. Run it against an empty local PostgreSQL database.
2. Inspect the generated schema using your database client or psql.
3. Run it in a disposable integration-test database, not only against a development database that may already contain drift.
4. Verify that down() works only in local or ephemeral environments. In production, prefer a new corrective migration over rolling back billing history.

Your integration tests should assert PostgreSQL error codes rather than only testing application validation.

Scenario	Expected SQLSTATE	Constraint type
Negative seat_quantity	23514	Check violation
Duplicate provider subscription reference	23505	Unique violation
Period references a nonexistent subscription	23503	Foreign-key violation
Two open subscriptions for one owner	23505	Partial unique-index violation
Overlapping billing periods	23P01	Exclusion violation

A useful migration-level test strategy is to insert a minimal valid plan version and price version fixture, then progressively attempt invalid subscription rows. Keep the tests close to the migration behavior: they verify that database protections remain intact when future entity refactors change application code.

One operational caution: updated_at DEFAULT CURRENT_TIMESTAMP only sets a value on insertion. It does not refresh itself on later updates. For now, set updated_at in your application service. If multiple services or direct SQL writers will update subscriptions, introduce a separate trigger migration later and test it explicitly.

---

Implementation checkpoint

Before proceeding, confirm that your migration provides all of the following:

• stable polymorphic owner references with no host-domain foreign key;
• immutable foreign keys from subscriptions to plan and price versions;
• provider customer uniqueness per provider and owner;
• provider subscription-reference uniqueness per provider;
• checks for valid statuses, provider reference pairs, nonnegative seat quantity, lifecycle context, and valid timestamps;
• a partial unique index preventing duplicate open subscriptions for an owner;
• billing periods with valid ranges, one current period, and no overlaps;
• an append-only transition ledger with ordered sequence numbers and provider-event deduplication;
• RESTRICT deletion behavior for financial history;
• a reversible down() implementation that drops dependent tables first.

---

Key takeaways

A robust billing schema makes invalid states difficult to store, not merely easy to avoid in a controller. Use CHECK constraints for row-local invariants, foreign keys for durable internal relationships, unique constraints and indexes for identity, and PostgreSQL exclusion constraints for non-overlapping time ranges.

Keep the owner relationship polymorphic and outside foreign-key enforcement so the subscription module stays reusable across individual and organization owners. Preserve plan, price, period, and transition history with restrictive deletion rules. Finally, treat transition auditing as an append-only domain-event ledger, including self-transitions such as renewals.

Next, you will define the provider-neutral billing contract: the interface for provider customers, checkout, quotes, subscription changes, cancellation, invoices, capability flags, idempotency keys, and normalized errors.