Designing a Multi-Tenant Subsidy Engine with Concurrency-Safe Budget Tracking

How to architect a B2B meal allowance and subsidy calculation system in Node.js and PostgreSQL. We walk through schema mapping, resolving discount conflicts using the Maximum Rule, and preventing budget depletion race conditions.

TL;DR — A corporate subsidy engine is three separate problems that people try to solve as one. Isolation: which merchants can this company’s employees even see — enforced with composite foreign keys so an invalid pairing is unrepresentable, not merely unchecked. Conflict resolution: when two subsidy programs both apply, stacking them silently gives away money, so pick a deterministic rule (I use the Maximum Rule — best single benefit wins, never the sum). Concurrency: a budget checked in application code and deducted a moment later will go negative under parallel checkouts; do the check and the deduction in one atomic SQL UPDATE with the bound in the WHERE clause. Then write an immutable ledger entry that snapshots the program parameters, so a report run next year survives an admin editing the program today.

Expanding a single-tenant business application to support B2B corporate models brings massive complexity to your data access layer. You are no longer just handling independent user checkouts; you are now enforcing complex corporate compliance rules, isolating merchant visibility, managing shared program budgets, and tracking subsidy ledgers.

As a backend engineer, I recently architected a B2B corporate allowance engine. Below is a deep dive into the core engineering decisions, database design, conflict resolution algorithms, and concurrency patterns required to build a high-integrity multi-tenant corporate wallet.

1. Multi-Tenant Relational Isolation

In a multi-tenant B2B platform, security starts at the database schema. Corporate organizations contract with the platform to allow their employees to buy meals from a selected pool of merchants.

To enforce this, we designed a two-tiered outlet mapping structure:

Platform Level: The platform admin assigns a master pool of merchants to the organization.
Office Level: The corporate admin maps specific merchants from that pool to individual physical offices.

This is enforced at the database level using composite foreign keys:

-- 1. Master Pool assigned to the organization
CREATE TABLE corporate_org_outlets (
    organization_id UUID NOT NULL,
    outlet_id UUID NOT NULL,
    assigned_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (organization_id, outlet_id)
);

-- 2. Office level assignment mapping to specific offices
CREATE TABLE corporate_office_outlets (
    office_outlet_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    office_id UUID NOT NULL,
    organization_id UUID NOT NULL,
    outlet_id UUID NOT NULL,
    is_visible_in_app BOOLEAN DEFAULT TRUE,
    
    -- Enforce that the office outlet MUST exist in the organization's master pool
    FOREIGN KEY (organization_id, outlet_id) 
        REFERENCES corporate_org_outlets (organization_id, outlet_id)
        ON DELETE CASCADE
);

The Architectural Benefit

By referencing a composite key (organization_id, outlet_id) rather than just a single outlet_id, PostgreSQL prevents a corporate admin from accidentally (or maliciously) displaying an unauthorized merchant to their employees. Data integrity is guaranteed at the database engine level, minimizing reliance on application-layer checks.

The alternative route to the same guarantee is row-level security, which enforces tenant scoping through policies on every query rather than through the shape of the keys. I chose composite keys because they make the invalid pairing unrepresentable rather than merely filtered — but if your tenant boundary is a single column on every table, RLS is less schema work for the same protection.

2. Resolving Subsidy Conflicts: The “Maximum Rule”

When an employee checks out, multiple subsidy programs might apply to their order. For example:

A Corporate Allowance (e.g., 50% subsidy up to $10, eligible for the entire office).
A Merchant-Specific Subsidy (e.g., a flat $5 discount sponsored for a specific team).

Allowing these programs to stack cumulatively (e.g., applying both) leads to extreme financial leakage where orders can become free or negative-cost.

To solve this, we implemented the Maximum Rule: evaluate all eligible programs, calculate the absolute discount for each independently, and apply only the program that grants the user the highest discount.

Here is a simplified version of the logic executing in the order validation lifecycle:

interface SubsidyProgram {
  programId: string;
  type: 'PERCENTAGE' | 'FIXED' | 'PERCENTAGE_CAPPED';
  value: number;
  maxCap?: number;
}

function calculateDiscount(orderAmount: number, program: SubsidyProgram): number {
  switch (program.type) {
    case 'PERCENTAGE':
      return orderAmount * (program.value / 100);
      
    case 'PERCENTAGE_CAPPED':
      const percentageDiscount = orderAmount * (program.value / 100);
      return Math.min(percentageDiscount, program.maxCap || 0);
      
    case 'FIXED':
      return Math.min(program.value, orderAmount);
      
    default:
      return 0;
  }
}

function getBestEligibleSubsidy(
  orderAmount: number, 
  eligiblePrograms: SubsidyProgram[]
): { programId: string; discountAmount: number } | null {
  if (eligiblePrograms.length === 0) return null;

  return eligiblePrograms.reduce((best, current) => {
    const currentDiscount = calculateDiscount(orderAmount, current);
    
    if (!best || currentDiscount > best.discountAmount) {
      return { programId: current.programId, discountAmount: currentDiscount };
    }
    return best;
  }, null as { programId: string; discountAmount: number } | null);
}

Why Reduce?

The Array.prototype.reduce pattern executes in linear O(N) time complexity. Because the list of eligible programs for a single employee is typically small (under 10), this keeps calculation overhead virtually non-existent while guaranteeing deterministic, leak-proof checkouts.

3. Concurrency-Safe Budget Exhaustion

In corporate subsidy programs, organizations set strict limits (e.g., a total monthly budget of $5,000). Once the budget is exhausted, the program must immediately disable itself, and employees must pay full price.

In a high-concurrency environment where dozens of users check out at the exact same second, a standard Select-then-Update application-layer loop is highly vulnerable to race conditions:

1. User A Reads: budget_used = 4995. Limit = 5000. (Eligible!)
2. User B Reads: budget_used = 4995. Limit = 5000. (Eligible!)
3. User A Updates: budget_used = 4995 + 10 = 5005. (Overdraft!)
4. User B Updates: budget_used = 5005 + 10 = 5015. (Double Overdraft!)

The Solution: Database-Level Atomic Updates

Instead of validating budget state inside Node.js, we offloaded the validation to an atomic transaction block with conditional update locks in PostgreSQL.

We write a single, atomic SQL statement that increments the budget and checks the boundary condition in one step:

-- Atomic Check-and-Apply Query
UPDATE corporate_subsidies 
SET budget_used = budget_used + $1
WHERE subsidy_id = $2 
  AND is_active = TRUE
  AND (budget_used + $1) <= total_budget
RETURNING subsidy_id, budget_used;

How the Application Handles It

If the update affects exactly 0 rows, the database tells our application that the budget limit has been reached or the program is inactive. Node.js can catch this state and fail the transaction gracefully, prompting the user that the subsidy is no longer available:

async function applySubsidyTransaction(client, subsidyId, discountAmount) {
  const query = `
    UPDATE corporate_subsidies 
    SET budget_used = budget_used + $1
    WHERE subsidy_id = $2 
      AND is_active = TRUE
      AND (budget_used + $1) <= total_budget
    RETURNING subsidy_id, budget_used;
  `;

  const result = await client.query(query, [discountAmount, subsidyId]);

  if (result.rowCount === 0) {
    throw new Error('SUBSIDY_BUDGET_EXHAUSTED');
  }

  return result.rows[0];
}

By leveraging PostgreSQL’s row locks on update execution, database transactions are serialized cleanly. We completely eliminate race conditions and budget overruns without introducing the latency or complexity of distributed lock managers like Redis Redlock.

Worth being precise about why this works, because it is not the isolation level doing it. Under Read Committed — Postgres’s default — a second UPDATE targeting the same row blocks until the first commits, then re-evaluates its WHERE clause against the new value. That re-evaluation is the whole trick: the bound is checked against committed state, not against the state the transaction read a moment earlier. A SELECT followed by an UPDATE gets no such re-check, which is exactly why the read-then-write version leaks.

4. Audit Trail Integrity: The Subsidy Ledger

Subsidies are financial transactions. In corporate systems, admins can edit or delete subsidy programs at any time. If you only store references to program IDs, generating historical GST reports, settlement invoices, or user ledgers will yield incorrect values if program parameters have changed.

To solve this, we built a Subsidy Ledger containing full denormalized snapshots:

CREATE TABLE corporate_subsidy_ledger (
    ledger_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id UUID NOT NULL REFERENCES orders(order_id),
    employee_id UUID NOT NULL,
    subsidy_program_id UUID NOT NULL,
    applied_amount NUMERIC(10, 2) NOT NULL,
    
    -- Snapshot data for historical reporting
    program_snapshot JSONB NOT NULL, 
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

Two column choices in there are load-bearing. applied_amount is NUMERIC, not a float — money in a binary floating-point type accumulates rounding error that shows up as a settlement report that is off by a few paise and cannot be explained. And the snapshot is JSONB rather than a text blob, so a later report can query into the historical parameters instead of parsing them.

Whenever an order is processed:

We calculate the discount using the active program state.
We record the calculation details and save a stringified snapshot of the parameters (the percentage, cap, limits, and type) directly inside the JSONB column.
This creates a permanent, immutable ledger entry. Even if an admin updates the program’s parameters or deletes the program entirely next month, historical reports will remain 100% accurate and audit-safe.

Summary of Architectural Best Practices

When building financial or B2B entitlement layers:

Push Integrity to the Database: Enforce relational pools via composite foreign keys and composite constraints.
De-stack Early: Implement a deterministic resolution algorithm like the Maximum Rule to avoid cumulative discounts.
Enforce Atomic Bounds: Never validate business thresholds in application code. Let SQL updates handle calculations and assertions simultaneously.
Log Immutable State: Store point-in-time configuration snapshots alongside transactional tables to maintain audit compliance.

What actually broke in production

The atomic deduction was right; the refund path was not. When an order was cancelled we credited the budget back — but the credit had no idempotency key, and a client retrying a cancellation could credit twice. Nobody noticed for a while because it only ever moved money in the customer’s favour, which is exactly the kind of bug that survives. The fix was the same one I use for payment webhooks: a unique constraint on the reversal reference, so the second credit fails at the database rather than being trusted at the application.

What I’d still improve

The Maximum Rule is deterministic and occasionally feels unfair to an employee who qualifies for two genuinely different benefits. A more expressive policy — additive within a category, maximum across categories — is the obvious next version, and it is also a rules engine, which is a considerably larger thing to own.

FAQ

How do you stop two subsidy programs from stacking? Pick a deterministic resolution rule and apply it in one place. I use the Maximum Rule: calculate each eligible program’s discount independently, then apply only the best single one. Stacking cumulatively is how orders end up free, and “whichever the code happened to evaluate last” is not a rule.

Why check a budget limit in SQL instead of application code? Because a limit checked in application code is checked against a value that may already be stale. Two concurrent checkouts both read the same remaining budget, both pass, and both deduct. Putting the bound in the WHERE clause of the UPDATE makes the check and the deduction one atomic operation.

What happens when the atomic UPDATE affects zero rows? Zero rows is the signal that the program is exhausted or inactive — the database has refused the deduction. The application reads rowCount === 0 and fails the checkout gracefully rather than inspecting the budget separately, which would reintroduce the race you just removed.

Do you need Redis or a distributed lock for budget limits? No, not if the budget lives in one row of one database. A bounded UPDATE already serialises on that row. Distributed lock managers earn their complexity when the contended resource spans systems; here they add a network hop and a new failure mode for nothing.

How do you enforce multi-tenant merchant visibility at the database level? Reference a composite key rather than a single id. Pointing the office-level mapping at (organization_id, outlet_id) means an office can only ever map a merchant already in its organization’s pool — the invalid pairing cannot be written, so no application check can forget to run.

Why snapshot subsidy program parameters into the ledger? Because admins edit programs and reports are run later. If the ledger only stores a program id, a percentage changed today silently rewrites what last quarter’s settlement report says happened. A denormalised snapshot at transaction time keeps historical reporting accurate regardless of what changes afterwards.

The one idea to take away

Put the constraint where it cannot be skipped. Every rule in this system that lives in application code is a rule some future endpoint will forget to apply. Composite foreign keys, CHECK constraints and bounded UPDATE statements are not micro-optimisations — they are the difference between a rule and a suggestion.