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

> Learn to set up a Supabase credits system for AI image generation. Implement database functions, RLS, and API integration for efficient credit management and user quotas.

- Repository: [苍何/awesome-gpt-image-2](https://github.com/freestylefly/awesome-gpt-image-2)
- Tags: how-to-guide
- Published: 2026-09-13

---

**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`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/supabase/migrations/202605090001_user_credits.sql) and the client configuration in [`src/supabaseClient.js`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/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`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/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`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/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`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/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.

```javascript
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`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/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.

```javascript
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`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/api/admin/credits/adjust.js), which utilizes a service-role client from [`api/_lib/supabase.js`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/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".

```javascript
// 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`:

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

```

**Failed generation** triggers `release_generation_reservation`:

```javascript
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`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/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`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/src/supabaseClient.js) file automatically loads these values and exports a configured client instance plus an `isSupabaseConfigured` boolean for validation checks.