Modeling credits, plans, and entitlements in your database

The data model of a credits system determines what you can express—plans, grants, expirations, overrides—and how hard it is to add new behaviors later.

Get it wrong and you're stuck with schema churn, messy migrations, and logic that's scattered across the codebase. Get it right and you have a clear, extensible foundation for balance, history, and reporting.

This article walks through common schema choices—from "credits on user" to "ledger of grants and usages" to "plans plus overrides"—and how they fit together so you can support upgrades, top-ups, promos, and manual adjustments without constant migration pain.

The single-balance trap

The minimal model is one number per account: balance or credits_remaining. You increment on grant, decrement on use.

The problem: you can't tell where the balance came from (plan renewal? promo? support grant?) or when it expires.

This means you can't implement:

• "Use oldest credits first"
• "Expire promo credits after 30 days"
• "Report how many credits we gave away this quarter"

You also can't support multiple grant types (plan allowance vs. one-off top-up) with different rules. As soon as you need any of that, you're forced to add tables and backfill.

The single balance is only acceptable for the most trivial use case. For anything production-ready, you need at least a ledger (who gave what, when) and ideally a plan and grant layer on top.

Ledger: grants and consumptions

A ledger records every change to balance. Each row is a grant (credit) or a consumption (debit), with:

• Amount
• Timestamp
• Account
• Context explaining why (e.g., grant_type: "plan_renewal," "promo," "support_adjustment")

Balance is then derived: sum of grants minus sum of consumptions.

Why a ledger matters

This approach gives you:

• Full history of all balance changes
• Auditability for compliance and debugging
• Policy flexibility like "expire grants with expiry_date in the past"

The ledger is append-only: you never update or delete rows, only insert. This keeps history intact and simplifies debugging and compliance.

Grants with metadata

Grants are the "credit" side of the ledger. Each grant can have:

• Account (or subscription) it belongs to.
• Amount (how many credits).
• Grant type (plan_renewal, promo, support, purchase, etc.).
• Effective and expiry (optional): valid from X until Y (e.g. "this month's allowance," "promo expires in 30 days").
• Reference (optional): link to plan period, invoice, promo campaign, or support ticket.

Consumption (debit) rows reference the account and amount, typically with an idempotency key to prevent double-counting. Optionally, they can reference "which grant was consumed" if you implement "use oldest first" logic.

The key idea: balance is computed from the ledger, and the ledger carries enough metadata to support reporting, expiration, and consumption policies (e.g., use promo before plan credits).

Plans and allowances

Plans define what an account gets (e.g. 10,000 credits per month). The assignment is "account A is on plan P from time X to time Y." The allowance is granted at the start of each period (by a renewal job that inserts a ledger row: grant_type=plan_renewal, amount=10000, reference=period_id). So you need:

• Plan definitions (or versioned definitions): name, allowance, period length, etc.
• Plan assignment (or subscription): account_id, plan_id, period_start, period_end, next_renewal_at.
• Ledger rows for each period's allowance, created by the renewal job.

That way you can add new plans or change allowances without changing the ledger schema; you just add new plan definitions and point assignments at them. Grandfathering is "old assignments keep pointing at old plan version."

Top-ups, promos, and manual adjustments

Beyond plan allowance, you'll have one-off grants:

• Customer bought a credit pack
• Support gave 500 credits as goodwill
• A promo campaign added 1,000 credits

These are just more ledger rows with different grant_type values, plus optional expiry and reference fields. The same balance computation applies: sum credits, sum debits, respect expiry dates.

Keep adjustments in the ledger

Manual adjustments (support grant/revoke) should also go through the ledger so you have an audit trail and consistent balance logic.

The schema stays simple: one ledger table (or separate grants + consumptions tables), with type and metadata. No need for a separate "adjustments" table; an adjustment is simply a grant or revoke with type=support_adjustment and a reason field.

Upgrades, downgrades, and proration

When an account changes plan mid-period, you face two concerns:

1. Credits policy

• Do they keep the current allowance until period end, or get the new allowance immediately?
• Do you revoke unused old allowance?
• How do you handle proration?

2. Data model

You need to record "as of time T, plan changed from A to B" so that renewal and reporting know what to do next.

This usually means:

• Updating the plan assignment (new plan_id, new period boundaries)
• Possibly inserting ledger rows (e.g., revoke remaining old allowance, grant prorated new allowance)

The ledger and assignment tables are enough. The complexity is in the business rules (when to revoke, when to grant, how to prorate), not in adding new tables.

Summary and recommendations

A robust schema for credits and plans includes:

• A ledger (grants and consumptions) so balance is derived and full history is available
• Grant metadata (type, expiry, reference) so you can implement expiration, reporting, and consumption order
• Plan definitions and assignments so allowances are created by a renewal process that writes ledger rows
• One-off and manual grants as first-class ledger rows with type and reason, not ad-hoc columns or separate adjustment tables

Keep it consistent

Keeping the model consistent reduces schema churn and keeps the door open for new grant types and policies without migrations. Everything that changes balance should go through the ledger.

If you're building this yourself, start with the ledger and plan layer and avoid the single-balance shortcut. If you're evaluating a credits API, look for a model that exposes clear concepts (accounts, grants, consumptions, plans) and lets you extend with custom grant types or metadata without fighting the schema.

credits.dev is built on a ledger and plan model, giving you balance, history, and flexibility without designing the schema from scratch.