Subscription Schema

Audience: backend developers Scope: packages/db/src/schema/subscription.ts

Enum Strategy

The schema uses TypeScript enums mapped to varchar columns with .$type<>().

That means:

• code gets enum typing
• the database does not enforce enum values natively

Current enums:

• SubscriptionStatus
• SubscriptionHistoryAction
• AccessGrantType
• AccessHistoryChangeType

Table Groups

The schema is organized into three groups.

Catalog Tables

• subscription_module
• subscription_tier
• subscription_plan
• subscription_plan_price
• subscription_plan_feature

Lifecycle / Runtime Tables

• subscription
• user_trial
• user_access

History Tables

• subscription_history
• user_access_history

Catalog Tables

subscription_module

Represents a subscribable product area.

Key fields:

• id uuid
• name unique
• slug unique
• description
• icon
• isActive

subscription_tier

Represents a tier within a module.

Key fields:

• moduleId
• name
• slug
• displayOrder
• parentTierId
• isActive

Important rule:

• tier slug uniqueness is scoped to a module

subscription_plan

Represents the core plan attached to a tier.

Key fields:

• tierId
• name
• description
• trialDays
• trialRequiresCc
• isActive
• displayOrder

Important rule:

• tierId is unique, so each tier has exactly one plan

subscription_plan_price

Represents purchasable durations and prices for a plan.

Key fields:

• planId
• label
• durationDays
• mrpNpr
• spNpr
• isBase
• isFeatured
• isActive
• displayOrder

Important rules:

• unique on (planId, durationDays)
• check constraint enforces spNpr <= mrpNpr

subscription_plan_feature

Represents plan features used by the feature entitlement layer and UI.

Key fields:

• planId
• moduleId
• featureKey
• featureText
• featureDetails
• value
• icon
• displayOrder
• enabled

Important rule:

• unique on (planId, featureKey)

Runtime Tables

subscription

Represents a user's subscription lifecycle for a module.

Key fields:

• userId
• moduleId
• planId
• planPriceId
• status
• startDate
• endDate
• cancelledAt
• cancelsAt
• priceSnapshotNpr

Important observations:

• planId is required in the current schema
• planPriceId is optional
• status can be pending_payment, active, cancelled, expired, or trial
• indexes exist on userId, status, endDate, and moduleId

user_trial

Tracks one-time trial consumption per user and module.

Key fields:

• userId
• moduleId
• subscriptionId
• startedAt
• convertedAt

Important rule:

• unique on (userId, moduleId)

user_access

Represents the current access decision source of truth.

Key fields:

• userId
• moduleId
• planId
• grantType
• grantedBy
• subscriptionId
• expiresAt
• revokedAt

This table is what AccessService and FeatureService read.

History Tables

subscription_history

Audit log for lifecycle changes.

Key fields:

• subscriptionId
• userId
• action
• performedBy
• details
• createdAt

user_access_history

Audit log for entitlement changes.

Key fields:

• userAccessId
• userId
• moduleId
• planId
• grantType
• changeType
• changedBy
• previousExpiresAt
• newExpiresAt
• note
• createdAt

Current Relations

Important relation paths:

• module -> tiers
• tier -> plan
• plan -> prices
• plan -> features
• subscription -> module / plan / planPrice / history
• user_access -> module / plan / subscription / history
• user_trial -> module / subscription

Important Modeling Decisions

Lifecycle and Entitlement Are Separate

This is the most important design choice.

• subscription stores lifecycle and reporting state
• user_access stores current entitlement

That is why cancellation, trial, paid activation, and admin grant can all share one authorization model.

No DB-Level Invariant for Some Business Rules

Some business rules are enforced in service code rather than the database:

• one current access row per userId + moduleId
• consistency between subscription.moduleId and the module implied by planId
• start/end date ordering

The service layer assumes those rules hold.