How to Set Up a Supabase Credits System for awesome-gpt-image-2: Database Functions, RLS, and API Integration

To set up a Supabase credits system for AI image generation, you implement PostgreSQL tables for user profiles and transactions, row-level security policies, and atomic database functions that handle credit consumption, free generation quotas, and automatic refunds when operations fail.

Setting up a Supabase credits system requires architecting database tables that track balances, transactions, and pending operations while enforcing security through row-level security (RLS). The awesome-gpt-image-2 project demonstrates this implementation using PostgreSQL functions to handle free generation quotas, credit consumption, and automatic refunds. By following the schema defined in supabase/migrations/202605090001_user_credits.sql and the client configuration in src/supabaseClient.js, developers can deploy a tamper-proof credit economy for AI services.

Database Schema Architecture

The profiles Table

Located in supabase/migrations/202605090001_user_credits.sql, the profiles table stores the core user state. The credit_balance column tracks purchasable credits available for image generation, while free_generations_used counts consumed complimentary generations. Each row represents a single user's current financial and quota state within the system.

The credit_transactions Table

The credit_transactions table provides an immutable ledger of every credit modification. It records grants, purchases, generation consumption, refunds, and manual adjustments. This audit trail enables administrators to trace the complete history of any credit balance change while supporting data analysis for business dashboards.

The generation_reservations Table

Acting as a temporary holding pattern for active requests, the generation_reservations table stores case IDs, prompts, consumption amounts, and status values (pending, succeeded, failed). This table bridges the gap between credit deduction and successful image delivery, ensuring that credits are only permanently consumed after confirmed generation completion.

Row-Level Security Implementation

RLS policies defined in the migration file restrict data access so users can only interact with their own rows. The policies named "Users can read own …" apply to each table, preventing unauthorized balance queries or transaction history access. Combined with PostgreSQL's security model, these policies guarantee that credit operations cannot be tampered with from the client side.

Core Database Functions

reserve_generation_usage

The reserve_generation_usage function serves as the primary entry point for consumption attempts. When called via RPC from api/generate-image.js, it performs the following atomic operations:

1. Locks the caller's profile row to prevent race conditions
2. Checks if the user has remaining free generations; if so, increments free_generations_used and creates a reservation with used_free_generation = true
3. If no free generation remains, verifies credit_balance >= 1, deducts one credit, creates a reservation, and logs a "generation" transaction in credit_transactions
4. Throws a CREDITS_REQUIRED error if neither condition is met

This function returns the reservation ID, generation type used, and current balance state.

complete_generation_reservation and release_generation_reservation

Once the image generation pipeline finishes successfully, the worker calls complete_generation_reservation with the p_reservation_id parameter. This function updates the reservation status to succeeded and sets the completed_at timestamp, finalizing the credit consumption.

When generation fails, the release_generation_reservation function handles automatic rollback. If the reservation consumed a free generation, the function decrements free_generations_used to restore the quota. If a credit was spent, the function refunds the credit_balance and creates a "refund" transaction entry in credit_transactions, maintaining accurate financial records while preserving user funds.

Client Configuration and API Layer

Supabase Client Initialization

The front-end and API routes rely on src/supabaseClient.js to instantiate the Supabase client. This wrapper loads the VITE_SUPABASE_URL and VITE_SUPABASE_ANON_KEY environment variables, exporting both a standard supabase instance for user operations and configuration checks.

import { supabase, isSupabaseConfigured } from '@/supabaseClient';

if (!isSupabaseConfigured) {
  console.error('Supabase isn’t configured – check VITE_SUPABASE_URL and VITE_SUPABASE_ANON_KEY.');
}

Image Generation Endpoint

The api/generate-image.js route orchestrates the credit reservation and generation trigger. It invokes client.rpc('reserve_generation_usage', { p_user_id, p_case_id, p_prompt }) to atomically reserve credits before processing the image request. If the RPC returns a CREDITS_REQUIRED error, the endpoint immediately returns an insufficient credits response to the client.

async function reserveGeneration(caseId, prompt) {
  const { data, error } = await supabase.rpc(
    'reserve_generation_usage',
    {
      p_user_id: supabase.auth.user().id,
      p_case_id: caseId,
      p_prompt: prompt,
    }
  );

  if (error) {
    if (error.message.includes('CREDITS_REQUIRED')) {
      alert('You need more credits to generate images.');
    } else {
      console.error(error);
    }
    return null;
  }

  // data = { reservation_id, used_free_generation, credit_amount, free_generations_used, credit_balance }
  return data;
}

Admin Credit Management

Administrators can adjust user balances through api/admin/credits/adjust.js, which utilizes a service-role client from api/_lib/supabase.js. This endpoint bypasses RLS to directly modify profiles.credit_balance and append corresponding entries to credit_transactions with types "grant" or "adjustment".

// POST /api/admin/credits/adjust
// Body: { userId: "...", amount: 10, reason: "Promotional grant" }

import { supabaseAdmin } from '@/supabaseClient'; // admin-role client

export default async function handler(req, res) {
  const { userId, amount, reason } = req.body;

  // Update balance
  await supabaseAdmin
    .from('profiles')
    .update({ credit_balance: supabaseAdmin.raw('credit_balance + ?', [amount]) })
    .eq('id', userId);

  // Log transaction
  await supabaseAdmin.from('credit_transactions').insert({
    user_id: userId,
    amount,
    type: amount > 0 ? 'grant' : 'adjustment',
    source: 'admin_adjust',
    metadata: { reason },
  });

  res.status(200).json({ ok: true });
}

Worker Completion Flow

After the background generation service completes or fails, it interacts with the reservation system to finalize state.

Successful completion uses complete_generation_reservation:

// In the worker after the image is generated
await supabase.rpc('complete_generation_reservation', {
  p_reservation_id: reservationId,
});

Failed generation triggers release_generation_reservation:

await supabase.rpc('release_generation_reservation', {
  p_reservation_id: reservationId,
  p_error_code: 'IMAGE_RENDER_TIMEOUT',
});

Summary

• Atomic transactions in PostgreSQL functions prevent race conditions when deducting credits or consuming free generations
• Three core tables (profiles, credit_transactions, generation_reservations) track balances, audit trails, and pending operations
• Row-level security policies ensure users can only access their own credit data
• Automatic rollback through release_generation_reservation refunds credits or restores free generations when image generation fails
• Service-role clients in api/_lib/supabase.js enable privileged admin operations while maintaining security boundaries

Frequently Asked Questions

How does the system handle concurrent generation requests?

The reserve_generation_usage function employs row-level locking when selecting the user's profile, ensuring that simultaneous requests from the same user cannot double-spend credits or free generations. PostgreSQL's atomic transaction guarantees that only one reservation succeeds per available credit.

What happens if an image fails to generate after credits are deducted?

The system uses the release_generation_reservation function to automatically refund consumed resources. If a credit was spent, the function increments credit_balance and creates a "refund" transaction; if a free generation was used, it decrements free_generations_used to restore the user's quota.

Can users manipulate their credit balances through the front-end?

No. All credit modifications occur inside server-side database functions protected by RLS policies. The credit_transactions table is append-only from the user's perspective, and balance updates happen exclusively through the reserve_generation_usage, release_generation_reservation, and admin adjustment functions, preventing client-side tampering.

How do I configure the Supabase client for local development?

Set the VITE_SUPABASE_URL and VITE_SUPABASE_ANON_KEY environment variables in your .env file. The src/supabaseClient.js file automatically loads these values and exports a configured client instance plus an isSupabaseConfigured boolean for validation checks.

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