awesome-gpt-image-2 Database and Schema Management: Supabase PostgreSQL Implementation

awesome-gpt-image-2 uses Supabase (PostgreSQL) as its underlying database engine and manages schema evolution through version-controlled SQL migration files located in the supabase/migrations directory.

The open-source project awesome-gpt-image-2 leverages a hosted PostgreSQL service to persist user data, billing records, and image generation tasks. According to the source code, the database layer is built entirely on Supabase, with schema definitions, security policies, and stored procedures maintained as incremental migration scripts.

Database Engine and Architecture

The project relies on PostgreSQL as the core database engine, accessed through the Supabase platform. This provides a fully managed relational database with built-in authentication, real-time capabilities, and row-level security (RLS).

All persistent data—including user profiles, credit transactions, membership billing, and generation task queues—is stored within this single PostgreSQL instance. The architecture separates client access into two distinct layers: an anonymous browser client for user-facing operations and a privileged server-side admin client for system-level tasks.

Schema Management Strategy

Schema evolution in awesome-gpt-image-2 follows a migration-based approach. Each database change is captured as a SQL file in the supabase/migrations/ directory, named with timestamps to ensure ordered execution (e.g., 202605090001_user_credits.sql).

This approach provides several advantages:

• Version Control: All schema definitions are tracked in Git alongside application code
• Reproducibility: New environments can be provisioned by running migrations sequentially
• Rollback Capability: Changes are isolated per file, allowing for targeted troubleshooting

The migrations define not only tables and indexes but also complex security policies, PostgreSQL functions, and triggers that enforce business logic at the database level.

Key Schema Components and Security

The database schema implements strict security through Row-Level Security (RLS) policies defined directly within migration files. These policies restrict data access based on the authenticated user's ID, ensuring users can only read their own profiles and transaction histories.

Critical stored procedures, such as reserve_generation_usage, handle credit deduction and generation reservations atomically. This function, defined in supabase/migrations/202605090001_user_credits.sql, is granted exclusively to the service_role to prevent unauthorized modifications.

The schema includes specialized tables for:

• User Management: Profiles and authentication metadata
• Credit Systems: Transaction logs and usage reservations
• Billing Integration: Membership plans, Alipay payments, and invoice tracking
• Task Queue: APIMart generation task tracking for async processing

Database Access Layer Implementation

The project implements a dual-client architecture to interact with the awesome-gpt-image-2 database, separating public browser access from privileged server operations.

Browser-Side Client Configuration

The browser client initializes Supabase with anonymous key authentication, handling session persistence and automatic token refresh for authenticated users.

import { createClient } from '@supabase/supabase-js';

const supabaseUrl = import.meta.env.VITE_SUPABASE_URL;
const supabaseAnonKey = import.meta.env.VITE_SUPABASE_ANON_KEY;

export const supabase = supabaseUrl && supabaseAnonKey
  ? createClient(supabaseUrl, supabaseAnonKey, {
      auth: {
        autoRefreshToken: true,
        detectSessionInUrl: true,
        persistSession: true,
      },
    })
  : null;

Source: [src/supabaseClient.js](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/src/supabaseClient.js)

Server-Side Admin Client

Serverless API routes utilize a separate admin client configured with the service role key. This client bypasses RLS policies to perform administrative tasks like crediting user accounts or querying aggregate statistics.

import { createClient } from '@supabase/supabase-js';

let adminClient;

export function getSupabaseAdminClient() {
  const url = process.env.SUPABASE_URL;
  const serviceRoleKey = process.env.SUPABASE_SERVICE_ROLE_KEY;
  if (!url || !serviceRoleKey) return null;

  if (!adminClient) {
    adminClient = createClient(url, serviceRoleKey, {
      auth: { autoRefreshToken: false, persistSession: false },
    });
  }
  return adminClient;
}

Source: [api/_lib/supabase.js](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/api/_lib/supabase.js)

Migration Files and Schema Evolution

The supabase/migrations/ directory contains timestamped SQL files that progressively build the database schema. Key migration files include:

• 202605090001_user_credits.sql: Creates the foundational tables including profiles, credit_transactions, and generation_reservations, along with the reserve_generation_usage function and initial RLS policies
• 20260509090000_membership_billing.sql: Adds membership tier management and recurring billing infrastructure
• 20260721090000_alipay_webpay.sql: Implements payment processing tables for Alipay integration
• 20260828090000_apimart_generation_tasks.sql: Establishes task queue tables for managing asynchronous image generation workloads

When deploying the awesome-gpt-image-2 database, these migrations execute sequentially to establish the complete schema, indexes, and security policies required for production operation.

Executing Database Operations

Application code interacts with the schema primarily through Supabase's RPC (Remote Procedure Call) interface for complex transactions. The reserve_generation_usage function encapsulates the logic for checking user credits, deducting balances, and creating generation reservations in a single atomic operation.

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

This pattern ensures data consistency by executing business logic within the database layer rather than the application tier, reducing race conditions in credit management workflows.

Summary

• awesome-gpt-image-2 uses Supabase PostgreSQL as its sole database engine for all persistent storage
• Schema changes are managed through timestamped SQL migrations in supabase/migrations/, enabling version-controlled, incremental database evolution
• Row-Level Security policies defined in migrations enforce data access restrictions at the database level
• The architecture separates concerns with a browser client (src/supabaseClient.js) for user operations and a server admin client (api/_lib/supabase.js) for privileged tasks
• Complex business logic, such as credit reservation, is implemented as PostgreSQL stored procedures to ensure transactional integrity

Frequently Asked Questions

What database engine powers awesome-gpt-image-2?

awesome-gpt-image-2 runs on PostgreSQL through the Supabase managed service. The source code confirms this through the migration files in supabase/migrations/, which use standard PostgreSQL DDL (Data Definition Language) for table creation, index management, and function definitions. The project utilizes PostgreSQL-specific features including stored procedures, triggers, and row-level security policies.

How does awesome-gpt-image-2 handle database schema changes?

Schema modifications are implemented as incremental SQL migrations stored in the supabase/migrations/ directory. Each migration file is prefixed with a timestamp (e.g., 202605090001_user_credits.sql) to ensure execution order. When deploying or updating the application, these migrations run sequentially to evolve the database structure without data loss. This approach maintains schema history in version control alongside application code.

Where are the Supabase connection credentials configured?

Connection settings use environment variables. The browser client in src/supabaseClient.js reads VITE_SUPABASE_URL and VITE_SUPABASE_ANON_KEY for public authentication. The server-side admin client in api/_lib/supabase.js uses SUPABASE_URL and SUPABASE_SERVICE_ROLE_KEY for privileged database access. This separation ensures anonymous users cannot access administrative database functions while allowing serverless functions to bypass row-level security when necessary.

What security measures protect data in the awesome-gpt-image-2 database?

Security relies on Row-Level Security (RLS) policies embedded directly in migration files, which restrict queries to rows where the user's ID matches the authenticated session. Additionally, sensitive operations use a service_role key that bypasses RLS, accessible only via the server-side admin client. Stored procedures like reserve_generation_usage are explicitly granted to specific roles, preventing direct table manipulation by client applications and ensuring credit calculations occur within secure database transactions.

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