How the Membership Billing System Tracks User Credits in awesome‑gpt‑image‑2

The membership billing system tracks user credits through a PostgreSQL transaction ledger, Supabase RPC functions, and server‑side helpers that synchronize Stripe subscriptions with monthly credit grants while enforcing idempotency.

The freestylefly/awesome‑gpt‑image‑2 repository implements a robust credit tracking architecture that connects Stripe billing cycles to a Supabase backend. This system ensures users receive exactly the number of credits defined in their membership_plans each billing period, records every transaction immutably, and exposes real‑time balances through secure API endpoints. Understanding how this membership billing system tracks user credits is essential for maintaining data integrity and debugging billing discrepancies.

Database Schema for Credit Accounting

The foundation of the credit system rests on four interconnected PostgreSQL tables managed through Supabase.

membership_plans defines the credit quota for each subscription tier via the monthly_credits column. When the application reads plan data, the formatPlan function in api/_lib/billing.js (lines 64‑78) normalizes this value alongside pricing and feature flags.

user_memberships records active subscriptions, storing plan_id, status, current_period_start, current_period_end, and cancel_at_period_end. The upsertMembershipFromSubscription function (lines 54‑71 in the same file) populates this table whenever Stripe sends a subscription update webhook.

credit_transactions serves as the immutable ledger. Every credit grant or consumption creates a row here with metadata linking it to specific subscription periods.

profiles maintains the denormalized credit_balance column, updated atomically by the database to ensure fast read access.

Subscription Synchronization and Monthly Grants

When Stripe notifies the application of a new billing period, the system executes a coordinated credit grant operation through the grantMembershipCredits helper.

First, the function retrieves the plan definition to determine the monthly_credits value. It then checks credit_transactions via hasTransactionWithMetadata (lines 81‑92 in api/_lib/billing.js) to verify whether credits were already granted for the current subscription period. This idempotency check prevents duplicate allocations if webhooks fire multiple times.

If no prior transaction exists, grantCredits (lines 94‑106) invokes the Supabase RPC function grant_user_credits. This PostgreSQL function performs two atomic operations:

1. Inserts a row into credit_transactions recording the amount, source (stripe_subscription), and metadata including the subscription ID
2. Increments the credit_balance column in the profiles table for the specified user

Profile Aggregation and API Exposure

Client applications never query the transaction ledger directly. Instead, they consume a normalized profile object constructed by helpers in api/_lib/supabase.js.

The normalizeProfile function (referenced within getProfileById and ensureProfileForUser at lines 95‑107) merges data from multiple sources:

• creditBalance extracted from profiles.credit_balance
• membership object joined from user_memberships and membership_plans

The /api/me endpoint leverages this aggregation to return the current state:

// api/me.js – Returns authenticated user's credit status
import { getAuthContext } from '@/api/_lib/supabase';

export async function GET(req) {
  const { profile, error } = await getAuthContext(req);
  if (error) return new Response(JSON.stringify({ error }), { status: 401 });

  return new Response(
    JSON.stringify({
      creditBalance: profile.creditBalance,
      membership: profile.membership
    })
  );
}

Admin Metrics and Consumption Tracking

Administrators monitor system health through aggregated queries defined in api/admin/metrics.js (lines 80‑101). This module reads the denormalized membership_credits column from profiles and calculates credits_consumed by aggregating transaction amounts from credit_transactions.

This dual approach—fast read‑time balances in profiles and audit‑capable history in credit_transactions—allows the dashboard to display real‑time usage without expensive JOIN operations on the transaction table.

Handling Stripe Events in Production

Integration with Stripe webhooks typically follows this pattern in the codebase:

// Example handler for Stripe subscription events
import { grantMembershipCredits } from '@/api/_lib/billing';
import { getSupabaseAdminClient } from '@/api/_lib/supabase';

async function handleSubscriptionUpdate(event) {
  const client = getSupabaseAdminClient();
  const userId = event.metadata.userId;
  const planId = event.metadata.planId;
  
  await grantMembershipCredits(client, {
    userId,
    planId,
    source: 'stripe_subscription',
    metadata: { subscriptionId: event.id }
  });
}

The source and metadata parameters create a traceable audit trail, linking every credit balance change to a specific Stripe subscription event.

Summary

• membership_plans.monthly_credits defines the quota allocated during each billing period.
• user_memberships stores the active subscription state synchronized from Stripe via upsertMembershipFromSubscription.
• grantMembershipCredits enforces idempotency by checking credit_transactions before invoking the RPC.
• grant_user_credits (PostgreSQL RPC) atomically writes transaction records and updates profiles.credit_balance.
• Profile helpers in api/_lib/supabase.js denormalize data for fast frontend consumption.
• Admin endpoints in api/admin/metrics.js aggregate consumption data from the transaction ledger.

Frequently Asked Questions

How does the system prevent duplicate credit grants?

The hasTransactionWithMetadata function queries credit_transactions to verify whether a grant already exists for the specific subscription period before calling grantCredits. This check, implemented in api/_lib/billing.js (lines 81‑92), ensures idempotency even if Stripe sends multiple webhook events for the same billing cycle.

What database tables store credit information?

The system uses four primary tables: membership_plans (defines monthly quotas), user_memberships (tracks active subscriptions), credit_transactions (immutable ledger of all changes), and profiles (denormalized current balance). The grant_user_credits RPC function coordinates writes between the transaction table and the profile table.

How is the credit balance exposed to the frontend?

The /api/me endpoint returns a normalized profile object containing creditBalance and membership details. These values are assembled by the normalizeProfile helper in api/_lib/supabase.js, which joins the profiles table with user_memberships to provide a unified view without requiring client‑side joins.

Where is the credit transaction history stored?

All credit movements are recorded in the credit_transactions table with metadata including the source (e.g., stripe_subscription) and reference IDs. Admin dashboards in api/admin/metrics.js query this table to calculate credits_consumed while using the membership_credits column from profiles for quick balance checks.

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