Plans and entitlements database schema for upgrades and add-ons

Why plans and features get messy fast

Plans look simple on a pricing page: Basic, Pro, Enterprise. The mess starts the moment you try to turn those names into real access rules inside your app.

Hardcoding feature checks (like if plan = Pro then allow X) works for the first version. Then pricing changes. A feature moves from Pro to Basic, a new add-on appears, or a sales deal includes a custom bundle. Suddenly you have the same rule copied across APIs, UI, mobile apps, and background jobs. You change one place and forget another. Users notice.

A second problem is time. Subscriptions are not a static label; they change mid-cycle. Someone upgrades today, downgrades next month, pauses, or cancels with remaining paid time. If your database only stores “current plan”, you lose the timeline and you cannot answer basic questions later: What did they have access to last Tuesday? Why did support approve a refund?

Add-ons make it worse because they cut across plans. An add-on might unlock extra seats, remove a limit, or enable a specific feature. People can buy it on any plan, remove it later, or keep it after downgrading. If the rule is embedded in code, you end up with a growing pile of special cases.

Here are the situations that usually break naive designs:

• Upgrade mid-cycle: access should change immediately, billing proration may follow different rules.
• Downgrade scheduled: access may stay “higher” until the paid period ends.
• Grandfathering: older customers keep a feature that new customers do not get.
• Custom deals: one account gets Feature A but not Feature B, even though they share the same plan name.
• Audit needs: support, finance, or compliance asks “what exactly was enabled and when?”

The goal is simple: a flexible access control model that changes with pricing, without rewriting business logic each time. You want one place to ask “can they do this?” and a database trail that explains the answer.

By the end of this article, you will have a schema pattern you can copy: plans and add-ons become inputs, and entitlements become the single source of truth for feature access. That same approach also fits no-code builders like AppMaster, because you can keep the rules in data and query them consistently from your backend, web app, and mobile apps.

Key terms: plan, add-on, entitlement, and access

A lot of subscription problems start as a vocabulary problem. If everyone uses the same word to mean different things, your schema turns into special cases.

Here are the terms worth keeping separate in a plans and entitlements database schema:

• Plan: The default bundle someone gets when they subscribe (for example, Basic or Pro). A plan usually sets the baseline limits and included features.
• Add-on: An optional purchase that changes the baseline (for example, “extra seats” or “advanced reporting”). Add-ons should be attachable and removable without changing the plan.
• Entitlement: The final, computed “what they have right now,” after combining plan + add-ons + overrides. This is what your app should query.
• Permission (or capability): A specific action someone can take (for example, “export data” or “manage billing”). Permissions often depend on role plus entitlements.
• Access: The real-world outcome when the app enforces rules (the screen shows or hides a feature, an API call is allowed or blocked, a limit is applied).

Feature flags are related but different. A feature flag is typically a product switch you control (rollouts, experiments, turning a feature off during an incident). An entitlement is customer-specific access based on what they pay for or what you granted. Use flags when you want to change behavior for groups without touching billing. Use entitlements when access must match a subscription, invoice, or contract.

Scope is another source of confusion. Keep these ideas clear:

• User: One person. Good for roles (admin vs member) and personal limits.
• Account (customer): The paying entity. Good for billing info and subscription ownership.
• Workspace (project/team): Where work happens. Many products apply entitlements here (seats, storage, enabled modules).

Time matters because access changes. Model it directly:

• Start and end: An entitlement can be active only within a window (trial, promo, annual contract).
• Scheduled change: Upgrades can start now; downgrades often start at the next renewal.
• Grace and cancelation: You may allow limited access after payment fails, but only until a clear end date.

Example: A company is on Pro, adds “Advanced Reporting” mid-month, then schedules a downgrade to Basic next cycle. The plan changes later, the add-on starts now, and the entitlement layer stays the single place to ask: “Can this workspace use advanced reports today?”

A simple core schema for plans and features

A good plans and entitlements database schema starts small: separate what you sell (plans and add-ons) from what people can do (features). If you keep those two ideas clean, upgrades and new add-ons become data changes, not rewrites.

Here is a practical core set of tables that works for most subscription products:

• products: the sellable thing (Base plan, Team plan, Extra seats add-on, Priority support add-on).
• plans: optional, if you want plans to be a special type of product with plan-only fields (billing interval, public display order). Many teams simply store plans inside products and use a product_type column.
• features: the catalog of capabilities (API access, max projects, export, SSO, SMS credits).
• product_features (or plan_features if you split plans): a join table that says which features come with which product, usually with a value.

That join table is where most of the power lives. Features are rarely just on or off. A plan might include max_projects = 10, while an add-on might add +5. So product_features should support at least:

• feature_value (number, text, JSON, or separate columns)
• value_type (boolean, integer, enum, json)
• grant_mode (replace vs add), so an add-on can “add 5 seats” instead of overwriting a base limit

Model add-ons as products too. The only difference is how they are purchased. A base plan product is “one at a time”, while an add-on product might allow quantity. But both map to features the same way. This avoids special cases like “if add-on X then enable feature Y” scattered through your code.

Features should be data, not code constants. If you hardcode feature checks in multiple services, you will eventually ship mismatches (web says yes, mobile says no, backend disagrees). When features live in the database, the app can ask one consistent question and you can roll out changes by editing rows.

Naming matters more than people expect. Use stable identifiers that never change, even if the marketing name does:

• feature_key like max_projects, sso, priority_support
• product_code like plan_starter_monthly, addon_extra_seats

Keep display labels separate (feature_name, product_name). If you are using AppMaster’s Data Designer with PostgreSQL, treating these keys as unique fields pays off immediately: you can regenerate safely while keeping integrations and reporting stable.

The entitlement layer: one place to ask “can they?”

Most subscription systems go sideways when “what they bought” is stored in one place, but “what they can do” is calculated in five different code paths. The fix is an entitlement layer: a single table (or view) that represents effective access for a subject at a point in time.

If you’re aiming for a plans and entitlements database schema that survives upgrades, downgrades, trials, and one-off grants, this layer is the part that makes everything predictable.

A practical entitlements table

Think of each row as one claim: “this subject has access to this feature with this value, from this time to that time, from this source.” A common shape looks like this:

• subject_type (e.g., "account", "user", "org") and subject_id
• feature_id
• value (the effective value for that feature)
• source (where it came from: "direct", "plan", "addon", "default")
• starts_at and ends_at (nullable ends_at for ongoing access)

You can implement value in a few ways: a single text/JSON column plus a value_type, or separate columns like value_bool, value_int, value_text. Keep it boring and query-friendly.

Value types that cover most products

Features are not always on/off. These value types usually cover real billing and access control needs:

• Boolean: enabled/disabled ("can_export" = true)
• Quota number: a limit ("seats" = 10, "api_calls" = 100000)
• Tier level: a rank ("support_tier" = 2)
• String: a mode or variant ("data_retention" = "90_days")

Precedence: how conflicts get resolved

Conflicts are normal. A user might be on a plan that allows 5 seats, buy an add-on for 10 more, and also get a manual grant from support.

Set a clear rule and stick to it everywhere:

1. Direct grant overrides plan
2. Then add-ons
3. Then defaults

A simple approach is to store all candidate rows (plan-derived, add-on-derived, direct) and compute a final “winner” per subject_id + feature_id by sorting on source precedence, then newest starts_at.

Here’s a concrete scenario: a customer downgrades their plan today, but they already paid for an add-on that lasts until the end of the month. With starts_at/ends_at on entitlements, the downgrade takes effect immediately for plan-based features, while the add-on feature stays active until its ends_at. Your app can answer “can they?” with one query instead of special-case logic.

Subscriptions, items, and time-bounded access

Your plan catalog (plans, add-ons, features) is the “what.” Subscriptions are the “who has what, and when.” If you keep these separate, upgrades and cancellations stop being scary.

A practical pattern is: one subscription per account, and many subscription items under it (one for the base plan, plus zero or more add-ons). In a plans and entitlements database schema, this gives you a clean place to record changes over time without rewriting access rules.

Core tables to model the purchase timeline

You can keep it simple with two tables that are easy to query:

• subscriptions: id, account_id, status (active, trialing, canceled, past_due), started_at, current_period_start, current_period_end, canceled_at (nullable)
• subscription_items: id, subscription_id, item_type (plan, addon), plan_id/addon_id, quantity, started_at, ends_at (nullable), source (stripe, manual, promo)

A common detail: store each item with its own dates. That way you can grant an add-on for only 30 days, or let a plan run until the end of a paid period even if the customer cancels renewal.

Keep proration and billing out of access logic

Proration, invoices, and payment retries are billing problems. Feature access is an entitlement problem. Do not try to “calculate access” from invoice lines.

Instead, let billing events update subscription records (for example, extend current_period_end, create a new subscription_item row, or set ends_at). Your app then answers access questions from the subscription timeline (and later, from the entitlement layer), not from billing math.

Scheduled changes without surprises

Upgrades and downgrades often take effect at a specific moment:

• Add pending_plan_id and change_at on subscriptions for a single scheduled plan change.
• Or use a subscription_changes table (subscription_id, effective_at, from_plan_id, to_plan_id, reason) if you need history and multiple future changes.

This prevents hardcoding rules like “downgrades happen at period end” into random parts of your code. The schedule is data.

Where trials fit

Trials are just time-bounded access with a different source. Two clean options:

• Treat trial as a subscription status (trialing) with trial_start/trial_end dates.
• Or create trial-granted items/entitlements with started_at/ends_at and source = trial.

If you are building this in AppMaster, these tables map neatly to the Data Designer in PostgreSQL, and the dates make it straightforward to query “what’s active right now” without special cases.

Step by step: implement the pattern

A good plans and entitlements database schema starts with one promise: feature logic lives in data, not scattered across code paths. Your app should ask one question - “what are the effective entitlements right now?” - and get a clear answer.

1) Define features with stable keys

Create a feature table with a stable, human-readable key you will never rename (even if the UI label changes). Good keys look like export_csv, api_calls_per_month, or seats.

Add a type so the system knows how to treat the value: boolean (on/off) vs numeric (limits/quotas). Keep it boring and consistent.

2) Map plans and add-ons to entitlements

Now you need two sources of truth: what a plan includes, and what each add-on grants.

A simple, practical sequence is:

• Put all features in one feature table with stable keys and a value type.
• Create plan and plan_entitlement where each row grants a feature value (for example seats = 5, export_csv = true).
• Create addon and addon_entitlement that grant extra values (for example seats + 10, api_calls_per_month + 50000, or priority_support = true).
• Decide how to combine values: booleans usually use OR, numeric limits often use MAX (higher wins), and seat-like quantities often use SUM.
• Record when entitlements start and end so upgrades, cancellations, and proration do not break access checks.

If you are building this in AppMaster, you can model these tables in the Data Designer (PostgreSQL) and keep the combine rules as a small “policy” table or enum used by your Business Process logic.

3) Produce “effective entitlements”

You have two options: compute on read (query and merge every time) or generate a cached snapshot when something changes (plan change, add-on purchase, renewal, cancellation). For most apps, snapshots are simpler to reason about and faster under load.

A common approach is an account_entitlement table that stores the final result per feature, plus valid_from and valid_to.

4) Enforce access with one check

Do not spread rules across screens, endpoints, and background jobs. Put one function in app code that reads effective entitlements and decides.

can(account_id, feature_key, needed_value=1):
  ent = get_effective_entitlement(account_id, feature_key, now)
  if ent.type == "bool": return ent.value == true
  if ent.type == "number": return ent.value >= needed_value

Once everything calls can(...), upgrades and add-ons become data updates, not a rewrite.

Example scenario: upgrade plus add-on without surprises

A 6 person support team is on the Starter plan. Starter includes 3 agent seats and 1,000 SMS messages per month. Mid-month they grow to 6 agents and want an extra 5,000 SMS pack. You want this to work without special case code like “if plan = Pro then…”.

Day 1: they start on Starter

You create a subscription for the account with a billing period (for example, Jan 1 to Jan 31). Then you add one subscription_item for the plan.

At checkout (or via a nightly job), you write entitlement grants for that period:

• entitlement_grant: agent_seats, value 3, start Jan 1, end Jan 31
• entitlement_grant: sms_messages, value 1000, start Jan 1, end Jan 31

Your app never asks “what plan are they on?” It asks “what is their effective entitlement right now?” and gets seats = 3, SMS = 1000.

Day 15: upgrade to Pro, same day add SMS pack

On Jan 15 they upgrade to Pro (includes 10 agent seats and 2,000 SMS). You do not edit old grants. You add new records:

• Close the old plan item: set subscription_item (Starter) end to Jan 15
• Create a new plan item: subscription_item (Pro) start Jan 15, end Jan 31
• Add a new add-on item: subscription_item (SMS Pack 5000) start Jan 15, end Jan 31

Then grants for the same period are appended:

• entitlement_grant: agent_seats, value 10, start Jan 15, end Jan 31
• entitlement_grant: sms_messages, value 2000, start Jan 15, end Jan 31
• entitlement_grant: sms_messages, value 5000, start Jan 15, end Jan 31

What happens immediately on Jan 15?

• Seats: effective seats becomes 10 (you choose a rule like “take the max for seats”). They can add 3 more agents that day.
• SMS: effective SMS becomes 7,000 for the rest of the period (you choose “sum additive grants” for message packs).

No existing usage needs to be “moved.” Your usage table keeps counting messages sent; the entitlement check simply compares usage this period against the current effective limit.

Day 25: schedule a downgrade, keep access until period end

On Jan 25 they schedule a downgrade back to Starter starting Feb 1. You do not touch Jan grants. You create future items (or future grants) for the next period:

• subscription_item (Starter) start Feb 1, end Feb 28
• No SMS pack item starting Feb 1

Result: they keep Pro seats and the SMS pack through Jan 31. On Feb 1, their effective seats drops to 3 and SMS resets to Starter limits for the new period. This is easy to reason about, and it maps cleanly to a no-code workflow in AppMaster: changing dates creates new rows, and the entitlement query stays the same.

Common mistakes and traps

Most subscription bugs are not billing bugs. They are access bugs caused by logic spread across the product. The fastest way to break a plans and entitlements database schema is to answer “can they use this?” in five different places.

A classic failure is hardcoding rules in the UI, the API, and background jobs separately. The UI hides a button, the API forgets to block the endpoint, and a nightly job still runs because it checks something else. You end up with confusing “it works sometimes” reports that are hard to reproduce.

Another trap is using plan_id checks instead of feature checks. It feels simple at first (Plan A can export, Plan B cannot), but it falls apart the moment you add an add-on, a grandfathered customer, a free trial, or an enterprise exception. If you ever say “if plan is Pro then allow…”, you are building a maze you will have to maintain forever.

Time and cancellation edge cases

Access also gets “stuck” when you store only a boolean like has_export = true and never attach dates. Cancellations, refunds, chargebacks, and mid-cycle downgrades all need time bounds. Without starts_at and ends_at, you will accidentally grant permanent access, or you will take access away too early.

A simple check to avoid this is:

• Every entitlement grant must have a source (plan, add-on, manual override) and a time range.
• Every access decision should use “now between start and end” (with clear rules for null end dates).
• Background jobs should re-check entitlements at run time, not assume yesterday’s state.

Don’t mix billing and access

Teams also get into trouble by mixing billing records and access rules in the same table. Billing needs invoices, taxes, proration, provider IDs, and retry states. Access needs clear feature keys and time windows. When they are tangled, a billing migration becomes a product outage.

Finally, many systems skip an audit trail. When a user asks “why can I export?”, you need an answer like: “Enabled by Add-on X from 2026-01-01 to 2026-02-01” or “Granted manually by support, ticket 1842.” Without that, support and engineering guess.

If you build this in AppMaster, keep the audit fields in your Data Designer model and make the “can they?” check a single Business Process used by web, mobile, and scheduled flows.

Quick checklist before you ship

Before you ship your plans and entitlements database schema, do one last pass with real questions, not theory. The goal is that access is explainable, testable, and change-friendly.

Reality check questions

Pick one user and one feature, then try to explain the result like you would to support or finance. If you can only answer “they’re on Pro” (or worse, “the code says so”), you will feel pain the first time someone upgrades mid-cycle or gets a one-off deal.

Use this quick checklist:

• Can you answer “why does this user have access?” using data alone (subscription items, add-ons, overrides, and time windows), without reading application code.
• Are all access checks based on stable feature keys (like feature.export_csv) rather than plan names (like “Starter” or “Business”). Plan names change; feature keys should not.
• Do entitlements have clear start and end times, including trials, grace periods, and scheduled cancellations. If time is missing, downgrades become arguments.
• Can you grant or remove access for one customer using an override record, without branching your logic. This is how you handle “give them 10 extra seats this month” without custom code.
• Can you test an upgrade and a downgrade using a few sample rows and get predictable results. If you need a complex script to simulate it, your model is too implicit.

A practical test: create three users (new, upgraded mid-month, canceled) and one add-on (like “extra seats” or “advanced reports”). Then run your access query for each. If results are obvious and explainable, you’re ready.

If you’re building this in a tool like AppMaster, keep the same rule: make one place (one query or one Business Process) responsible for “can they?” so every web and mobile screen uses the same answer.

Next steps: make upgrades easy to maintain

The best way to keep upgrades sane is to start smaller than you think. Pick a handful of features that actually drive value (5-10 is plenty) and build one entitlement query or function that answers a single question: “Can this account do X right now?” If you can’t answer that in one place, upgrades will always feel risky.

Once that one check works, treat upgrade paths like product behavior, not just billing behavior. The fastest way to catch weird edge cases is to write a small set of access tests based on real customer moves.

Here are practical next steps that tend to pay off immediately:

• Define a minimal feature catalog and map each plan to a clear set of entitlements.
• Add add-ons as separate “items” that grant or extend entitlements, instead of baking them into plan rules.
• Write 5-10 access tests for common paths (upgrade mid-cycle, downgrade at renewal, add-on added then removed, trial to paid, grace period).
• Make pricing changes data-only: update plan rows, feature mappings, and entitlement grants, not application code.
• Set a habit: every new plan or add-on must come with at least one new test that proves access behaves as expected.

If you’re using a no-code backend, you can still model this pattern cleanly. In AppMaster, the Data Designer is a good fit for building the core tables (plans, features, subscriptions, subscription items, entitlements). Then the Business Process Editor can hold the access decision flow (load active entitlements, apply time windows, return allow/deny) so you’re not hand-coding scattered checks across endpoints.

The payoff shows up the next time pricing changes. Instead of rewriting rules, you edit data: a feature moves from “Pro” to an add-on, an entitlement duration changes, or a legacy plan keeps its old grants. Your access logic stays stable, and upgrades become a controlled update, not a code sprint.

If you want to validate your schema quickly, try modeling one upgrade plus one add-on end to end, then run those access tests before you add anything else.