Subscriptions

State Machine

                    ┌──────────┐
                    │ pending  │
                    └────┬─────┘
                         │ checkout completed
                         ▼
                ┌────────────────┐
           ┌────│  trial / active │────┐
           │    └───────┬────────┘    │
           │            │             │
           │   payment failure        │ cancel
           │            │             │
           ▼            ▼             ▼
    ┌──────────┐ ┌──────────┐ ┌──────────┐
    │ past_due │ │ renewed  │ │cancelled │
    └────┬─────┘ │ (back to │ └────┬─────┘
         │       │  active)  │      │ expires
         │       └──────────┘      ▼
         ▼                    ┌──────────┐
    ┌──────────┐             │ expired  │
    │ expired  │             └──────────┘
    └──────────┘

All state transitions are driven by payment gateway webhooks.

Subscription Table

Column	Type	Description
id	text PK
app_id	text FK	Application this subscription belongs to
owner_type	text	user or org (B2B readiness)
owner_id	text	User or org ID
product_id	text FK
sku	text	Denormalized SKU for query convenience
status	text	pending, trial, active, past_due, cancelled, expired
gateway	text	lemonsqueezy or apple_iap
gateway_ref	text?	Gateway subscription ID
started_at	timestamptz?
expires_at	timestamptz?
quantity	integer	Seat count (1 for individual)
lock_version	integer	Optimistic lock for concurrent webhook handling

Polymorphic Owner

The owner_type / owner_id pattern supports both individual and B2B subscriptions:

• Individual: owner_type='user', owner_id=user.id
• Organization: owner_type='org', owner_id=organization.id

This allows a zero-schema-change path from Phase 3 (personal subscriptions) to Phase 4 (B2B team subscriptions).

Active Subscription Uniqueness

CREATE UNIQUE INDEX uniq_sub_active
  ON subscription(app_id, owner_type, owner_id, sku)
  WHERE status IN ('trial', 'active', 'pending');

This partial index ensures an owner can have only one active subscription per SKU per application. Expired and cancelled subscriptions do not count against this limit, allowing users to re-subscribe after cancellation.