One Active Subscription per User
Hard · Indexes
A partial index only covers the rows that match its WHERE clause. A unique partial index enforces uniqueness among those rows only: CREATE UNIQUE INDEX name ON t (col) WHERE condition;.
Each user may have many old subscriptions, but at most one active one. A bug let some users get several active subscriptions.
1. Fix the data: for each user, keep only their most recent active subscription active (latest started_on; if tied, the highest id) and set active = false on their other active ones.
2. Create a unique partial index one_active_subscription so a user can never have two active subscriptions again, while inactive ones stay unlimited.
The check lists the subscriptions and then tries to add one active and one inactive subscription for every user.