What schema changes are needed to add a Pro subscription tier?

WHAT THIS TESTS: Whether you normalize subscription metadata into a dedicated plans table or hardcode tier logic into the users and subscriptions tables. The core concern is relational design for SaaS billing, data integrity through foreign keys, and safe migration patterns for production data.

A GOOD ANSWER COVERS: First, state that the users table should not change because subscription state belongs to a billing entity, not the identity entity. Second, describe creating or extending a plans table with columns like id, name, price_monthly_cents, price_yearly_cents, trial_days, features as JSON, and is_active. Third, explain that the subscriptions table needs a plan_id foreign key referencing plans, plus status enums such as trialing, active, past_due, canceled, and paused, along with billing period timestamps. Fourth, mention a backfill migration that maps existing subscriptions to the correct plan rows without downtime, and emphasize storing all monetary values as integer cents to eliminate floating-point precision bugs.

COMMON WRONG ANSWERS: Adding a tier string or enum column directly to the users table, which couples identity to billing and breaks when a user has multiple subscriptions. Duplicating price columns on the subscriptions table, which creates update anomalies when pricing changes. Proposing to drop and recreate tables, or omitting a migration strategy for existing rows. Storing prices as floats or decimals without explaining the integer-cents pattern. Hardcoding Pro features in application code rather than modeling them in the schema.

LIKELY FOLLOW-UPS: How would you handle a user downgrading from Pro to Free mid-cycle? How would you support per-seat pricing or usage-based metering on top of tiered plans? What indexes would you add to keep the billing dashboard fast at high volume? How would you ensure tenant isolation if this is a multi-tenant SaaS?

ONE CONCRETE EXAMPLE: Imagine you currently have a subscriptions table with columns id, user_id, status, and price. You would create a plans table with id, name, price_monthly_cents, and is_active. Then add plan_id to subscriptions as a nullable foreign key, backfill it by joining on the old price values to map each subscription to its plan, mark the column not null, and finally drop the old price column from subscriptions. The new Pro tier is simply a new row in plans with name Pro and price_monthly_cents set to 2999, requiring zero application deploys to recognize the tier.

Source: erflow.io