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

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, 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 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 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 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 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 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 – Aggregates usage data across users and provides an admin-forced credit deduction RPC for super-admin actions.
• 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 – Adds chart metadata tables for dashboard visualizations of subscription and credit consumption trends.

OAuth and Community Features

• 20260528090000_watcha_oauth_accounts.sql – Maps external OAuth provider identifiers for Watcha single-sign-on integration.
• 20260722090000_paid_community.sql – Establishes schema for QR-code-based community access and Alipay onboarding flows.
• 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 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:

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

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:

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

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 enforce RLS for multi-tenant security.
• Billing infrastructure in 20260509090000_membership_billing.sql supports both subscription memberships and one-time credit packs.
• Alipay integration in 20260721090000_alipay_webpay.sql adds webhook handling and idempotent stored procedures for order completion and refunds.
• APIMart tracking in 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, 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 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 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.

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