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

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, 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, 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:

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:

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:

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:

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:

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 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 and 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 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 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.

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