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

> Discover what the generation_reservations table stores including user ID, prompt, status, credits, and timestamps for efficient quota management and audit trails.

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

---

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

```javascript
// 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:

```javascript
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:

```javascript
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:

```javascript
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`](https://github.com/freestylefly/awesome-gpt-image-2/blob/main/api/_lib/generation.js) before any external AI service call is made.