# Supabase Migration Files for User Credits and Billing in awesome-gpt-image-2

> Discover Supabase migration files for user credits and billing in awesome-gpt-image-2. Learn how tables for transactions, reservations, and Stripe payments are created.

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

---

**The awesome-gpt-image-2 repository stores all credit and billing schema definitions in two sequential Supabase migration files located in `supabase/migrations/`, which create tables for credit transactions, generation reservations, membership plans, and Stripe payment processing.**

This article examines the complete database architecture for the credit and billing system implemented in the **awesome-gpt-image-2** project. According to the source code, the entire infrastructure relies on two SQL migration files that establish Row-Level Security (RLS) policies, PL/pgSQL functions for atomic credit operations, and Stripe integration fields.

## Core Credits Migration (202605090001_user_credits.sql)

The first migration file, [`202605090001_user_credits.sql`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/202605090001_user_credits.sql), establishes the foundational credit economy. It creates three interconnected tables and four critical database functions that handle atomic credit operations.

### Database Schema

The migration defines the following core tables:

- **`public.profiles`** – Extends the auth user with `credit_balance` (integer), `free_generations_used` (integer), and `role` (text) fields to track available resources and user permissions.
- **`public.credit_transactions`** – An immutable ledger logging every credit event with fields for `user_id`, `amount`, `type` (grant, purchase, generation, refund, adjustment), `source`, `reference_id`, and `metadata` JSONB.
- **`public.generation_reservations`** – A temporary holding table for pending generation requests, tracking `case_id`, `prompt`, `credit_amount`, `used_free_generation` boolean, and reservation `status`.

### Credit Management Functions

The file implements atomic PL/pgSQL functions to prevent race conditions during credit operations:

**`grant_user_credits`** – Adds credits to a user's balance and logs the transaction. Accepts parameters for user ID, amount, type, source, reference ID, and metadata.

**`reserve_generation_usage`** – Atomically checks for available free generations or credits, creates a reservation record, and decrements the appropriate balance. This prevents double-spending during high-concurrency generation requests.

**`complete_generation_reservation`** – Marks a reservation as succeeded when generation finishes successfully, finalizing the credit consumption.

**`release_generation_reservation`** – Handles failure scenarios by restoring credits or free generation counts to the user's profile based on the original reservation type.

## Membership and Billing Migration (20260509090000_membership_billing.sql)

The second migration file, [`20260509090000_membership_billing.sql`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/20260509090000_membership_billing.sql), extends the core credit system with recurring subscriptions, one-time purchases, and Stripe payment processing capabilities.

### Stripe Integration and User Profiles

This migration modifies the existing `profiles` table to support external billing:

- Adds `stripe_customer_id` (text) to link Supabase users with Stripe customer objects.
- Maintains the existing credit balance fields while adding relational constraints to the new billing tables.

### Subscription and Payment Tables

The file creates four additional tables to manage commercial operations:

- **`public.membership_plans`** – Defines recurring subscription tiers with `name_en`, `monthly_credits`, `amount_cents`, and `active` status flags.
- **`public.credit_packs`** – One-time purchase options for credit bundles, distinct from recurring memberships.
- **`public.user_memberships`** – Links users to active subscriptions, tracking `current_period_start`, `current_period_end`, and `cancel_at_period_end` booleans.
- **`public.payment_orders`** – Stores checkout session data including `stripe_session_id`, `amount_cents`, `currency`, and `status` (pending, completed, failed).

Both migrations implement comprehensive **Row-Level Security (RLS) policies** ensuring users can only view and modify their own credit data, while allowing anonymous read access to active membership plans for public pricing pages.

## Implementing Credit Workflows

The following SQL examples demonstrate how to interact with the migration-defined schema using the created functions.

### Granting Credits to a User

Use the `grant_user_credits` function to programmatically add credits:

```sql
SELECT * FROM public.grant_user_credits(
    p_user_id := 'c6a5b5d2-e3f1-4d7a-9b2c-a7f9e2d5b123'::uuid,
    p_amount  := 50,
    p_type    := 'grant',
    p_source  := 'admin_adjustment',
    p_reference_id := NULL,
    p_metadata := '{"reason":"welcome_bonus"}'::jsonb
);

```

This atomically increments the user's `credit_balance` in `profiles` and appends a record to `credit_transactions` with type **grant**.

### Reserving Generation Capacity

Before initiating an AI generation, reserve resources using:

```sql
SELECT *
FROM public.reserve_generation_usage(
    p_user_id := 'c6a5b5d2-e3f1-4d7a-9b2c-a7f9e2d5b123'::uuid,
    p_case_id := 42,
    p_prompt  := 'A futuristic city at sunset'
);

```

The function returns a result set containing:
- `reservation_id` (uuid) – Unique identifier for this hold
- `used_free_generation` (boolean) – Whether a free generation was consumed
- `credit_amount` (integer) – Credits reserved (0 if free generation used)
- `free_generations_used` (integer) – Updated count of consumed free generations
- `credit_balance` (integer) – Remaining credit balance after reservation

### Completing Successful Generations

Upon successful image generation, finalize the reservation:

```sql
SELECT public.complete_generation_reservation(
    p_reservation_id := 'e1d2c3b4-a5f6-7d8e-9b0c-d1e2f3a4b5c6'::uuid
);

```

This updates the `generation_reservations` record status to **succeeded** and sets `completed_at` to the current timestamp.

### Handling Generation Failures

If generation fails, release the reservation to refund resources:

```sql
SELECT public.release_generation_reservation(
    p_reservation_id := 'e1d2c3b4-a5f6-7d8e-9b0c-d1e2f3a4b5c6'::uuid,
    p_error_code := 'GENERATION_TIMEOUT'
);

```

The function automatically restores credits to `profiles.credit_balance` or decrements `free_generations_used` based on the original reservation parameters, ensuring users are not charged for failed operations.

## Querying Billing Data

The migrations enable direct client-side queries for public billing information while restricting sensitive data. To fetch active membership plans for a pricing page:

```sql
SELECT id, name_en, monthly_credits, amount_cents
FROM public.membership_plans
WHERE active = true
ORDER BY sort_order;

```

The RLS policy defined in [`20260509090000_membership_billing.sql`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/20260509090000_membership_billing.sql) explicitly allows anonymous and authenticated clients to read active membership plans, enabling frontend applications to display pricing without authentication.

## Summary

- **awesome-gpt-image-2** implements its entire credit and billing infrastructure through two Supabase migrations: [`202605090001_user_credits.sql`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/202605090001_user_credits.sql) and [`20260509090000_membership_billing.sql`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/20260509090000_membership_billing.sql).
- The core migration creates atomic PL/pgSQL functions (`reserve_generation_usage`, `complete_generation_reservation`, `release_generation_reservation`) that prevent race conditions during credit consumption.
- The billing migration adds Stripe integration fields, membership subscriptions, and payment order tracking to support commercial operations.
- Row-Level Security policies ensure users can only access their own credit data while allowing public read access to pricing information.
- All credit operations are logged immutably in the `credit_transactions` table for audit and analytics purposes.

## Frequently Asked Questions

### What tables are created by the user credits migration?

The [`202605090001_user_credits.sql`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/202605090001_user_credits.sql) migration creates three primary tables: `public.profiles` (storing credit balances and free generation counts), `public.credit_transactions` (immutable ledger of all credit events), and `public.generation_reservations` (temporary holds during active generation requests). It also creates four PL/pgSQL functions to manage atomic credit operations.

### How does the system prevent users from spending the same credit twice?

The system uses the `reserve_generation_usage` function to atomically check available credits and create a reservation before generation begins. This reserves the resource immediately, preventing concurrent requests from accessing the same credit. The reservation is only finalized via `complete_generation_reservation` on success or released via `release_generation_reservation` on failure.

### Can anonymous users view membership plans without logging in?

Yes, the [`20260509090000_membership_billing.sql`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/20260509090000_membership_billing.sql) migration includes a Row-Level Security policy that explicitly grants read access to `public.membership_plans` for both anonymous and authenticated roles. This allows frontend applications to display pricing information to potential customers before they create accounts.

### What happens when a generation fails after reserving a credit?

When a generation fails, calling `release_generation_reservation` with the reservation ID automatically inspects whether the original reservation consumed a free generation or a paid credit. The function then either restores the credit to the user's balance or decrements the `free_generations_used` counter, ensuring users are never charged for unsuccessful operations.