# Supabase Migrations for awesome-gpt-image-2: Complete Database Schema Guide

> Explore Supabase migrations for awesome-gpt-image-2. This guide details twelve version-controlled SQL migrations defining tables, RLS policies, and stored procedures for billing and image generation tracking in Supabase Postgres.

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

---

**The awesome-gpt-image-2 project stores user profiles, credit balances, and payment data in Supabase Postgres, using twelve version-controlled SQL migrations located in `supabase/migrations/` to define tables, row-level security policies, and stored procedures for billing and image generation tracking.**

The repository `freestylefly/awesome-gpt-image-2` relies on a robust Supabase backend to manage its credit-based billing system, membership subscriptions, and APIMart integration. The database schema is built through a series of incremental migrations that create tables for user credits, generation tasks, Alipay payments, and admin analytics. Understanding these Supabase migrations for awesome-gpt-image-2 is essential for deploying the application or extending its financial infrastructure.

## Core Credit and User Schema

### User Profiles and Credit Tables

The foundation is laid in [`202605090001_user_credits.sql`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/202605090001_user_credits.sql), which creates three essential tables with **Row-Level Security (RLS)** policies.

- **`profiles`** – Stores user UUID, email, avatar, role, credit balance, free-generation counter, and timestamps. The RLS policy ensures users can only read their own row.
- **`credit_transactions`** – Audits every credit change (grant, purchase, generation, refund) with a JSONB `metadata` column for extensibility.
- **`generation_reservations`** – Tracks pending generation requests, linking them to users, cases, and prompts while recording whether a free generation was consumed.

This migration also installs critical database functions:

- `set_updated_at()` – Trigger function to auto-update timestamps.
- `reserve_generation_usage()` – Atomically reserves a generation slot by deducting a free generation or a credit.
- `complete_generation_reservation()` and `release_generation_reservation()` – Mark success or failure, automatically reverting credits if the generation fails.

All functions are explicitly granted to the Supabase `service_role` and revoked from `public`/`anon` users, ensuring only server-side backend code can invoke them.

### Billing and Membership Infrastructure

The file [`20260509090000_membership_billing.sql`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/20260509090000_membership_billing.sql) expands the schema to support subscription billing and one-off purchases:

- **`membership_plans`** – Defines recurring plans (Starter, Creator, Studio) with monthly credit allotments, pricing in cents, and currency.
- **`credit_packs`** – One-time credit bundles purchasable via Stripe or Alipay.
- **`user_memberships`** – Links users to plans, storing Stripe subscription IDs, billing period dates, and status.
- **`payment_orders`** – Generic order table tracking product type (credit pack vs. membership), Stripe session IDs, payment status, and metadata.

RLS policies restrict visibility to order owners, while indexes optimize lookups by user ID, status, and Stripe session ID. This migration also introduces `grant_user_credits()`, an RPC function allowing admin-level credit adjustments.

## Payment Processing and Financial Safety

### Alipay Web Payment Integration

For Chinese market support, [`20260721090000_alipay_webpay.sql`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/20260721090000_alipay_webpay.sql) extends the billing schema:

- Extends **`credit_packs`** with `alipay_amount_cents` for local pricing.
- Extends **`payment_orders`** with `payment_provider`, `provider_trade_no`, and `provider_refund_request_no` fields.
- Creates **`alipay_notify_events`** to persist webhook notifications from Alipay for audit trails.

Three PL/pgSQL stored procedures ensure transactional safety:

- `complete_alipay_credit_pack_order` – Validates Alipay callbacks, atomically credits the user, and records the transaction in `credit_transactions`.
- `prepare_alipay_credit_pack_refund` – Reserves credits for refund and updates order status to `refund_pending`.
- `finalize_alipay_credit_pack_refund` – Marks orders as `refunded` after Alipay confirms completion.

These functions are restricted to the `service_role`, preventing client-side manipulation of financial records.

### Generation Usage Fixes

The migration [`20260509061039_fix_generation_usage_ambiguous_columns.sql`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/20260509061039_fix_generation_usage_ambiguous_columns.sql) resolves column name conflicts in `generation_reservations` that emerged after adding `usage_source` and `generation_cost` fields, ensuring queries remain unambiguous when joining with `credit_transactions`.

## API and Generation Task Tracking

### APIMart Task Management

The file [`20260828090000_apimart_generation_tasks.sql`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/20260828090000_apimart_generation_tasks.sql) creates **`apimart_generation_tasks`** to track external API provider jobs:

- Stores provider-specific task IDs (OpenAI, Stability AI).
- Records actual USD cost per generation.
- Tracks expiration timestamps for result URLs.
- Indexes provider references for rapid lookup.

Helper functions in [`api/_lib/generation.js`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/api/_lib/generation.js) reference this table to persist task metadata and correlate it with user reservations.

## Administrative and Social Features

### Google Account Center and Metrics

Three migrations support admin oversight and analytics:

- **[`20260512090000_google_account_center.sql`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/20260512090000_google_account_center.sql)** – Aggregates usage data across users and provides an admin-forced credit deduction RPC for super-admin actions.
- **[`20260512143000_pricing_admin_metrics.sql`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/20260512143000_pricing_admin_metrics.sql)** – Updates credit-to-price mappings (e.g., $5 per 300 credits) and creates tables for revenue tracking.
- **[`20260513095141_admin_metrics_charts.sql`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/20260513095141_admin_metrics_charts.sql)** – Adds chart metadata tables for dashboard visualizations of subscription and credit consumption trends.

### OAuth and Community Features

- **[`20260528090000_watcha_oauth_accounts.sql`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/20260528090000_watcha_oauth_accounts.sql)** – Maps external OAuth provider identifiers for Watcha single-sign-on integration.
- **[`20260722090000_paid_community.sql`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/20260722090000_paid_community.sql)** – Establishes schema for QR-code-based community access and Alipay onboarding flows.
- **[`20260515090000_case_favorites.sql`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/20260515090000_case_favorites.sql)** – Creates a `case_favorites` table with RLS protection, allowing users to bookmark image generation cases.

## Performance Optimization

### Database Indexing

The migration [`20260509091500_membership_plan_index.sql`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/20260509091500_membership_plan_index.sql) adds a searchable index on active membership plans, ensuring the UI can quickly filter available subscription tiers without full table scans.

## Applying the Migrations with Supabase CLI

To deploy this schema to your Supabase project:

```bash

# Install Supabase CLI globally

npm install -g supabase

# Link to your project (replace <PROJECT_REF>)

supabase link --project-ref <PROJECT_REF>

# Execute all migrations in chronological order

supabase db push

```

This command runs every `.sql` file in `supabase/migrations/` sequentially, creating tables, indexes, policies, and stored procedures.

### Invoking Stored Procedures from Application Code

Reserve a generation atomically:

```js
import { supabase } from './src/supabaseClient.js';

async function reserveGeneration(userId, caseId, prompt) {
  const { data, error } = await supabase.rpc('reserve_generation_usage', {
    p_user_id: userId,
    p_case_id: caseId,
    p_prompt: prompt,
  });

  if (error) throw error;
  return data; // Returns { reservation_id, used_free_generation, credit_amount }
}

```

Complete an Alipay order securely:

```js
async function finalizeAlipayPayment(orderId, tradeNo, amount, notifyId, payload) {
  const { data, error } = await supabase.rpc('complete_alipay_credit_pack_order', {
    p_order_id: orderId,
    p_trade_no: tradeNo,
    p_paid_amount_cents: amount,
    p_notify_id: notifyId,
    p_notify_payload: payload,
  });

  if (error) throw error;
  return data.credit_balance;
}

```

Grant credits manually (admin only):

```js
async function adminGrantCredits(userId, amount) {
  const { data, error } = await supabase.rpc('grant_user_credits', {
    p_user_id: userId,
    p_amount: amount,
    p_type: 'grant',
    p_source: 'admin',
  });

  if (error) throw error;
  return data.credit_balance;
}

```

## Summary

- The **Supabase migrations for awesome-gpt-image-2** are located in `supabase/migrations/` and define a production-ready Postgres schema.
- **Core tables** (`profiles`, `credit_transactions`, `generation_reservations`) in [`202605090001_user_credits.sql`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/202605090001_user_credits.sql) enforce RLS for multi-tenant security.
- **Billing infrastructure** in [`20260509090000_membership_billing.sql`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/20260509090000_membership_billing.sql) supports both subscription memberships and one-time credit packs.
- **Alipay integration** in [`20260721090000_alipay_webpay.sql`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/20260721090000_alipay_webpay.sql) adds webhook handling and idempotent stored procedures for order completion and refunds.
- **APIMart tracking** in [`20260828090000_apimart_generation_tasks.sql`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/20260828090000_apimart_generation_tasks.sql) correlates external generation costs with user reservations.
- All financial functions are restricted to the `service_role`, ensuring only backend APIs can execute credit mutations.

## Frequently Asked Questions

### How do I apply the Supabase migrations for awesome-gpt-image-2 to a new project?

Link your Supabase project using the CLI with `supabase link --project-ref <PROJECT_REF>`, then run `supabase db push`. The CLI automatically executes all SQL files in `supabase/migrations/` in chronological order, creating tables, indexes, and stored procedures.

### What is the purpose of the `reserve_generation_usage` function?

The `reserve_generation_usage` function, defined in [`202605090001_user_credits.sql`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/202605090001_user_credits.sql), atomically checks a user's credit balance or free generation quota, deducts the appropriate amount, and creates a reservation record. This prevents race conditions where multiple simultaneous requests might overdraw a user's account.

### Which migration handles Alipay payment refunds?

Refund logic is implemented in [`20260721090000_alipay_webpay.sql`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/20260721090000_alipay_webpay.sql) via two functions: `prepare_alipay_credit_pack_refund` reserves credits and sets the order status to `refund_pending`, while `finalize_alipay_credit_pack_refund` confirms the refund after Alipay's webhook notification, ensuring transactional consistency.

### Can I query the APIMart generation costs directly from the database?

Yes. The `apimart_generation_tasks` table created by [`20260828090000_apimart_generation_tasks.sql`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/20260828090000_apimart_generation_tasks.sql) stores actual USD costs per generation task. You can join this table with `generation_reservations` on task ID to analyze per-user generation expenses and provider efficiency.