How Supabase Credit Grant RPC Works in billing.js: Atomic Credit Management

The grantCredits helper in api/_lib/billing.js invokes the grant_user_credits Supabase RPC to atomically update user credit balances, validate transactions, and log entries to credit_transactions while preventing race conditions through database-level row locking.

The freestylefly/awesome-gpt-image-2 repository implements a production-grade credit system using Supabase's Remote Procedure Call (RPC) capabilities. The billing.js module serves as the primary interface for credit operations, delegating atomic transactions to a specialized PL/pgSQL function that ensures data consistency and auditability.

The grant_user_credits RPC Database Function

Defined in supabase/migrations/20260509090000_membership_billing.sql, the grant_user_credits function handles all credit modifications within a single database transaction. This approach eliminates race conditions and ensures that every credit change is properly recorded.

Atomic Transaction Flow

When invoked, the function executes five critical operations sequentially:

1. Row Locking: Loads the user's profile from the profiles table using SELECT ... FOR UPDATE, creating an exclusive lock that prevents concurrent modifications.

2. Validation: Checks for profile existence (raising PROFILE_NOT_FOUND if missing), verifies sufficient balance for deductions (raising CREDITS_INSUFFICIENT if negative), and exits early for zero-amount balance queries.

3. Balance Update: Modifies the credit_balance field in the profiles table to reflect the new total.

4. Audit Logging: Inserts a record into credit_transactions containing the amount, transaction type, source, reference ID, and JSON-encoded metadata.

5. Return Value: Outputs the user's new credit balance to the calling application.

Race Condition Protection

The function declares SECURITY DEFINER and operates with elevated privileges within the public search path. Combined with the FOR UPDATE row lock, this ensures that concurrent RPC calls serialize on the user record, making the entire operation thread-safe against simultaneous credit modifications.

The grantCredits JavaScript Wrapper

Located at api/_lib/billing.js, the grantCredits function provides a clean interface to the underlying RPC:

export async function grantCredits(client, { userId, amount, type, source, referenceId = null, metadata = {} }) {
  const { data, error } = await client.rpc('grant_user_credits', {
    p_user_id: userId,
    p_amount: amount,
    p_type: type,
    p_source: source,
    p_reference_id: referenceId,
    p_metadata: metadata
  });

  if (error) throw error;
  return Array.isArray(data) ? data[0] : data;   // → new credit_balance
}

Parameter Mapping: The destructured arguments map directly to PL/pgSQL parameters (p_user_id, p_amount, p_type, p_source, p_reference_id, p_metadata).

Client Usage: The function accepts a Supabase client instance (typically from getSupabaseAdminClient in api/_lib/supabase.js) and returns the updated balance as a numeric value.

Error Handling: Database-raised exceptions (like PROFILE_NOT_FOUND or CREDITS_INSUFFICIENT) propagate through Supabase's error object and throw immediately, allowing upstream catch blocks to handle specific failure modes.

Practical Implementation Examples

Granting Promotional Credits

Award fixed bonus credits after user onboarding:

import { getSupabaseAdminClient } from '../_lib/supabase.js';
import { grantCredits } from '../_lib/billing.js';

async function awardPromo(userId) {
  const client = getSupabaseAdminClient();

  const newBalance = await grantCredits(client, {
    userId,
    amount: 100,
    type: 'promo',
    source: 'welcome_bonus',
    metadata: { campaign: 'summer_2026' }
  });

  console.log(`User ${userId} now has ${newBalance} credits`);
}

Deducting Credits for Usage

Process consumption by passing negative amounts:

await grantCredits(client, {
  userId,
  amount: -1,
  type: 'usage',
  source: 'generation',
  referenceId: generationId,
  metadata: { caseId: caseId }
});

Negative amount values trigger the validation logic to verify sufficient balance before committing the deduction.

Membership Credit Grants

Stripe webhook handlers utilize grantMembershipCredits (which internally calls grantCredits) to provision subscription benefits:

import { grantMembershipCredits } from '../_lib/billing.js';

await grantMembershipCredits(client, {
  userId,
  planId: 'creator',
  source: 'stripe_membership_checkout',
  orderId: order.id,
  metadata: { stripeSessionId: session.id }
});

This pattern appears in api/billing/webhook.js when processing successful subscription payments.

Key Files and Architecture

The credit grant mechanism spans several critical components:

• api/_lib/billing.js – Business logic layer containing grantCredits and grantMembershipCredits wrappers.

• supabase/migrations/20260509090000_membership_billing.sql – Database schema defining the grant_user_credits PL/pgSQL function with atomic transaction guarantees.

• api/billing/webhook.js – Integration point demonstrating RPC usage within Stripe webhook handlers.

• api/_lib/supabase.js – Client factory providing getSupabaseAdminClient for authenticated RPC invocations.

Together, these files implement a safe, auditable credit system where JavaScript orchestrates business rules while PostgreSQL handles data integrity through the Supabase RPC interface.

Summary

• Atomic Transactions: The grant_user_credits RPC function executes credit updates, validation, and audit logging within a single database transaction.

• Race Condition Safety: FOR UPDATE row locking and SECURITY DEFINER execution context prevent concurrent modification issues.

• JavaScript Interface: The grantCredits helper in billing.js maps parameters to the RPC and returns the new balance or throws specific database errors.

• Flexible Usage: Support both credit grants (positive amounts) and deductions (negative amounts) with full metadata and reference tracking.

• Integration Ready: Higher-level functions like grantMembershipCredits leverage the RPC for Stripe subscription workflows.

Frequently Asked Questions

What is the grant_user_credits RPC function?

The grant_user_credits RPC is a PL/pgSQL function stored in the Supabase database that atomically updates user credit balances, validates transaction rules, and writes audit records to the credit_transactions table. It operates as a SECURITY DEFINER function to ensure consistent privilege escalation during execution.

How does the RPC prevent race conditions when updating credits?

The function uses SELECT ... FOR UPDATE to acquire an exclusive row lock on the user's profile before reading or modifying the credit_balance. This database-level locking serializes concurrent requests, ensuring that simultaneous credit grants or deductions calculate balances based on the most recent committed state.

What parameters does the grantCredits helper require?

The helper requires a Supabase client instance and an options object containing userId, amount (positive for grants, negative for deductions), type (transaction category), source (origin identifier), plus optional referenceId and metadata. These map directly to the RPC's p_-prefixed parameters.

How are credits deducted for image generation usage?

The system deducts credits by calling grantCredits with a negative amount (typically -1), specifying type: 'usage' and source: 'generation'. While reserve_generation_usage often handles this internally, direct RPC calls with negative amounts trigger the validation logic that checks for CREDITS_INSUFFICIENT before permitting the deduction.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}