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

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:

1. Platform Level: The platform admin assigns a master pool of merchants to the organization.
2. 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.

---

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.

---

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
);

Whenever an order is processed:

1. We calculate the discount using the active program state.
2. We record the calculation details and save a stringified snapshot of the parameters (the percentage, cap, limits, and type) directly inside the JSONB column.
3. 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:

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