Core Database Tables in Awesome-GPT-Image-2: Supabase Schema and Migration Guide

Awesome-GPT-Image-2 utilizes eight core PostgreSQL tables managed through Supabase to handle user profiles, credit balances, image generation reservations, billing records, OAuth integrations, and favorites, with Row-Level Security policies ensuring data isolation.

Awesome-GPT-Image-2 implements a credit-based image generation platform backed by Supabase PostgreSQL. The core database tables defined in the supabase/migrations directory form the backbone of the application's data layer, supporting everything from user authentication to asynchronous image processing via src/supabaseClient.js.

User Identity and Credit Management

The foundation of the schema centers on user accounts and the credit economy that powers the image generation workflow.

public.profiles

The public.profiles table stores essential user identity data and financial standing. It maintains the credit balance, free generation count, user role assignments, and audit timestamps for each account. According to the source code in supabase/migrations/202605090001_user_credits.sql (lines 3-13), this table is created immediately upon user signup and serves as the primary reference for permission checks throughout the application.

public.credit_transactions

Every modification to a user's credit balance is immortalized in public.credit_transactions. This ledger records grant, purchase, generation, refund, and adjustment events, capturing the transaction amount, type, source system, and optional JSONB metadata for audit trails. The migration at lines 15-23 of supabase/migrations/202605090001_user_credits.sql defines this table as an immutable history that supports financial reconciliation and user support investigations.

Image Generation Lifecycle

These tables track the complete lifecycle of image generation requests, from initial reservation through third-party provider completion.

public.generation_reservations

When a user initiates an image creation request, the system creates a row in public.generation_reservations. This table links the user to a specific case ID, stores the prompt text, tracks the reservation status, and records the number of credits consumed. Defined at lines 26-34 in supabase/migrations/202605090001_user_credits.sql, it works alongside the reserve_generation_usage database function to atomically decrement balances and prevent race conditions during high-traffic generation bursts.

public.apimart_generation_tasks

For asynchronous processing through the APIMart provider, the system utilizes public.apimart_generation_tasks. Created by supabase/migrations/20260828090000_apimart_generation_tasks.sql, this table caches task IDs, processing costs, result URLs, and provider-specific indexes, allowing the webhook handlers to correlate completed generations with the original user requests.

Commerce and Subscription Data

The platform monetizes through credit packs and subscriptions, requiring dedicated billing infrastructure.

public.membership_plans

Subscription tiers and credit pack definitions reside in public.membership_plans. This table, established by supabase/migrations/20260509091500_membership_plan_index.sql, stores pricing metadata, credit allocation rules, and plan hierarchy data that drives the upgrade and purchase flows.

public.membership_billing

Actual financial transactions with Stripe are recorded in public.membership_billing. The migration 20260509090000_membership_billing.sql defines this table to store order records, payment confirmations, and credit pack purchase history, creating a durable link between external payment providers and internal credit grants.

User Experience Features

Beyond core generation and billing, the schema supports user convenience features.

public.case_favorites

Users can bookmark preferred prompt cases using public.case_favorites. Defined in supabase/migrations/20260515090000_case_favorites.sql, this many-to-many junction table links user UUIDs to case identifiers, enabling personalized galleries without duplicating case metadata.

public.watcha_oauth_accounts

For users authenticating via third-party providers, public.watcha_oauth_accounts stores OAuth tokens and account links. The migration 20260528090000_watcha_oauth_accounts.sql creates this table to support alternative login methods beyond the default Supabase Auth integration.

Row-Level Security Implementation

All core database tables implement Row-Level Security (RLS) policies to enforce data isolation. As specified in supabase/migrations/202605090001_user_credits.sql, each table includes alter table … enable row level security directives followed by create policy statements that restrict read and write operations to the owning user identity. This ensures that users can only access their own profiles, credit_transactions, and generation_reservations records, even when querying through the client-side Supabase SDK.

Practical SQL Examples for Database Operations

The following queries demonstrate typical interactions with the awesome-gpt-image-2 core database tables:

-- Retrieve current user credit balance and free generation status
SELECT credit_balance, free_generations_used
FROM public.profiles
WHERE id = auth.uid();

-- Grant administrative credits (e.g., welcome bonus or refund)
INSERT INTO public.credit_transactions (
    user_id, amount, type, source, reference_id, metadata
) VALUES (
    'user-uuid-here', 
    10, 
    'grant', 
    'admin_adjustment', 
    NULL,
    '{"reason":"welcome_bonus"}'::jsonb
);

-- Reserve credits for an image generation request
SELECT * FROM public.reserve_generation_usage(
    auth.uid(),          -- user UUID
    123,                 -- case ID
    'portrait of a developer using Supabase'  -- prompt text
);

Summary

• public.profiles manages user identity, roles, and real-time credit balances.
• public.credit_transactions provides an immutable audit trail of all credit movements.
• public.generation_reservations tracks active and historical image generation requests with credit consumption details.
• public.apimart_generation_tasks handles asynchronous task correlation with external AI providers.
• public.membership_plans defines purchasable subscription tiers and credit pack structures.
• public.membership_billing stores Stripe transaction records and order confirmations.
• public.case_favorites enables user-specific bookmarking of prompt templates.
• public.watcha_oauth_accounts facilitates third-party OAuth authentication linking.

All schema definitions reside in the supabase/migrations directory, with application logic interfacing through the Supabase client initialized in src/supabaseClient.js.

Frequently Asked Questions

What database system does awesome-gpt-image-2 use?

The project uses Supabase, which provides a managed PostgreSQL database with real-time subscriptions and automatic API generation. The client application connects via the Supabase JavaScript SDK as configured in src/supabaseClient.js.

How does the database prevent users from spending credits they don't have?

The system utilizes the reserve_generation_usage database function to atomically check balances, create a generation_reservations record, and decrement the user's credit balance in a single transaction. This prevents race conditions where multiple simultaneous requests might overdraw an account.

Where are the database schema changes stored?

All schema migrations are stored as SQL files in the supabase/migrations directory. Key files include 202605090001_user_credits.sql for the core credit system and 20260509090000_membership_billing.sql for Stripe integration.

Can users query other users' generation history?

No. Row-Level Security (RLS) policies defined in the migration files restrict all SELECT, INSERT, UPDATE, and DELETE operations on user tables to rows where the user_id matches the authenticated Supabase Auth UUID. This ensures complete data isolation between accounts.

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