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

> Explore the core database tables in Awesome-GPT-Image-2 using Supabase. Understand user profiles, credits, reservations, billing, and more with this schema and migration guide.

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

---

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

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

```

```sql
-- 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
);

```

```sql
-- 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`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/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`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/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`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/202605090001_user_credits.sql) for the core credit system and [`20260509090000_membership_billing.sql`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/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.