What Information Is Stored in the generation_reservations Table? Schema and Usage Guide

The generation_reservations table in the freestylefly/awesome-gpt-image-2 repository tracks every AI generation request with columns for user identity, prompt text, processing status, credit consumption, and lifecycle timestamps, enabling atomic quota management and precise audit trails.

The generation_reservations table serves as the authoritative ledger for image generation jobs in the open-source freestylefly/awesome-gpt-image-2 project. Defined in the Supabase PostgreSQL migration layer and accessed by the Node.js backend via stored procedures, it captures the complete lifecycle of a generation request—from initial reservation through success or failure.

Database Schema and Column Definitions

The table schema is defined in [supabase/migrations/202605090001_user_credits.sql](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/supabase/migrations/202605090001_user_credits.sql#L26-L38).

• id (uuid, PK): Unique identifier generated via gen_random_uuid(). Serves as the primary key for all downstream operations.
• user_id (uuid, FK): References auth.users(id). Enforces ownership and drives Row-Level Security (RLS) policies that isolate user data.
• case_id (integer): Categorizes the generation request type or specific workflow case identifier.
• prompt (text): Stores the complete text prompt submitted by the user for image generation.
• status (text): Tracks lifecycle state with values pending, succeeded, or failed. Defaults to pending upon insertion.
• used_free_generation (boolean): Flags whether the request consumed the user's daily free generation quota. Defaults to false.
• credit_amount (integer): Records credits deducted from the user's balance (remains 0 for free generations). Defaults to 0 with a non-negative constraint.
• error_code (text, nullable): Contains machine-readable error identifiers (e.g., TIMEOUT) when status is failed.
• created_at (timestamptz): Timestamp of reservation creation, defaults to now().
• completed_at (timestamptz, nullable): Populated when the generation finishes, regardless of whether the result succeeded or failed.

Indexes and Query Performance

The migration creates B-tree indexes to optimize the most common access patterns encountered in api/_lib/generation.js.

• idx_generation_reservations_user_id_created_at: Composite index on (user_id, created_at) for fast chronological listing of a user's history.
• idx_generation_reservations_status: Index on status for efficient polling of pending jobs by background workers.
• idx_generation_reservations_provider_provider_task_id (added in later migrations): Composite index on (provider, provider_task_id) for webhook correlation and idempotency checks.

Application Integration and Stored Procedures

The backend does not insert rows directly into generation_reservations via the client library. Instead, the application relies on atomic PostgreSQL stored procedures to prevent race conditions in credit accounting.

Reserving a Generation Slot

The public.reserve_generation_usage stored procedure creates a reservation and deducts credits or free quota atomically. The backend calls this via RPC before invoking external AI services:

// File: api/_lib/generation.js
const { data: reservationId, error } = await supabase
  .rpc('reserve_generation_usage', {
    p_user_id: userId,
    p_case_id: caseId,
    p_prompt: promptText,
  });

if (error) throw new Error('Reservation failed: ' + error.message);
// reservationId contains the new UUID

Completing a Successful Generation

After the AI service returns the image, the system marks the reservation as succeeded using the complete_generation_reservation procedure:

await supabase.rpc('complete_generation_reservation', {
  p_reservation_id: reservationId,
});

This updates the status column to succeeded and sets completed_at to the current timestamp.

Handling Failures and Refunds

If the generation fails or times out, release_generation_reservation restores credits or free quota while recording the failure:

await supabase.rpc('release_generation_reservation', {
  p_reservation_id: reservationId,
  p_error_code: 'TIMEOUT',
});

This sets status to failed, records the error_code, and updates completed_at.

Querying User History

To display a user's generation history in the dashboard, the application queries the table directly with RLS enforcement:

const { data, error } = await supabase
  .from('generation_reservations')
  .select('id, prompt, status, credit_amount, created_at, completed_at')
  .eq('user_id', userId)
  .order('created_at', { ascending: false })
  .limit(50);

Summary

• The generation_reservations table acts as the central ledger for all AI generation requests in freestylefly/awesome-gpt-image-2.
• It stores user identification, prompt text, processing status, credit/free-quota flags, and timestamps for complete lifecycle tracking.
• Indexes on user_id, status, and provider_task_id ensure efficient querying and webhook handling.
• All writes occur through stored procedures (reserve_generation_usage, complete_generation_reservation, release_generation_reservation) to maintain atomic credit accounting and prevent double-spending.
• Row-Level Security policies enforce that users can only read their own reservation records, ensuring data isolation.

Frequently Asked Questions

What is the primary key of the generation_reservations table?

The primary key is the id column, defined as a uuid with a default value of gen_random_uuid(). This unique identifier is returned by the reserve_generation_usage stored procedure and is used to reference the reservation in subsequent completion or release calls.

How does the system track whether a generation used free quota?

The used_free_generation boolean column indicates whether a request consumed the user's daily free generation allocation. When true, the credit_amount column remains 0. The reserve_generation_usage procedure atomically checks remaining free quota and sets this flag accordingly before deducting credits only if the quota is exhausted.

What happens to the completed_at timestamp when a generation fails?

The completed_at column is populated with the current timestamp whenever a reservation reaches a terminal state, regardless of whether the status is succeeded or failed. This allows the system to calculate processing duration and clean up stale pending reservations.

Which stored procedure creates a new generation_reservations row?

The public.reserve_generation_usage stored procedure inserts new rows. It accepts p_user_id, p_case_id, and p_prompt as parameters, validates available credits or free quota, and returns the new id of the created reservation. This procedure is invoked from api/_lib/generation.js before any external AI service call is made.

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