What Is the Purpose of the credit_transactions Table in awesome-gpt-image-2?

The credit_transactions table serves as the immutable audit ledger for all credit-related events in the awesome-gpt-image-2 application, recording every balance change resulting from purchases, image generation consumption, refunds, and administrative adjustments.

The credit_transactions table sits at the heart of the billing system in the freestylefly/awesome-gpt-image-2 repository, a platform for AI-powered image generation. Every modification to a user's credit balance—whether adding funds through purchases or consuming credits for generation tasks—creates a permanent, queryable record in this ledger, ensuring full transparency and accountability.

Schema Design and Core Purpose

The table is defined in supabase/migrations/202605090001_user_credits.sql (lines 15-24) as an append-only ledger designed for financial auditability. Rather than updating a single balance field, the system records every delta as a new row, creating a complete history of a user's financial activity.

Key columns include:

• id: UUID primary key uniquely identifying each transaction
• user_id: Foreign key referencing auth.users(id), linking transactions to specific accounts
• amount: Integer value where positive numbers add credits and negative numbers deduct them
• type: Enum categorizing the transaction reason (grant, purchase, generation, refund, adjustment)
• source: Optional text describing the human-readable origin (e.g., "case_generation_test")
• reference_id: Optional UUID pointing to related objects like generation reservations
• metadata: JSONB field storing arbitrary additional data such as related case IDs
• created_at: Timestamp providing chronological ordering for the audit trail

How Credit Events Are Recorded

The table integrates directly with database functions that automate billing workflows during the image generation lifecycle.

Generation Consumption with reserve_generation_usage

When a user initiates an image generation, the reserve_generation_usage function (defined at lines 48-63 of supabase/migrations/202605090001_user_credits.sql) automatically inserts a row with type = 'generation' and a negative amount value. This immediately deducts the required credits from the user's available balance while creating an immutable record of the charge.

Processing Refunds via release_generation_reservation

If a generation fails to complete successfully, the release_generation_reservation function (lines 22-38) inserts a compensating transaction with type = 'refund'. This restores the previously deducted credits to the user's balance, maintaining ledger integrity without deleting or modifying the original consumption record.

Administrative Adjustments

Support teams can manually credit user accounts by inserting rows with type = 'adjustment'. The optional source and metadata fields document the reason for the intervention, creating an audit trail for all manual balance modifications.

Security and Access Control

Row-Level Security (RLS) policies defined at lines 54-57 of supabase/migrations/202605090001_user_credits.sql enforce strict data isolation. The policy restricts SELECT operations to rows where the user_id column matches the authenticated user's ID, ensuring users can only view their own financial history while preventing unauthorized access to other accounts' transaction data.

Querying Transaction Data

Retrieving User History in the API

The application exposes recent generation activity through the GET /api/me endpoint implemented in api/me.js (lines 30-39). This endpoint queries the credit_transactions table for the authenticated user's last 20 generation-type transactions, returning them to populate the user interface's credit history view.

Calculating Real-Time Balances

Instead of maintaining a cached balance field, the system can calculate the current credit total dynamically by summing the ledger:

SELECT COALESCE(SUM(amount), 0) AS credit_balance
FROM public.credit_transactions
WHERE user_id = '<user-uuid>';

Fetching Records via Supabase Client

To retrieve the last 10 transactions for display in a user dashboard:

const { data, error } = await supabase
  .from('credit_transactions')
  .select('id, amount, type, source, metadata, created_at')
  .eq('user_id', USER_ID)
  .order('created_at', { ascending: false })
  .limit(10);

Inserting Manual Adjustments

Administrators can provision credits directly via SQL:

INSERT INTO public.credit_transactions
  (user_id, amount, type, source, reference_id, metadata)
VALUES
  ('<user-uuid>', 50, 'adjustment', 'admin_bonus', NULL, '{"reason":"promo"}');

Integration with Billing Infrastructure

The api/_lib/billing.js file centralizes all Supabase queries related to the credit_transactions table, abstracting database access patterns for various billing endpoints. This consolidation ensures consistent transaction creation logic across the application, from the generation reservation system to administrative credit tools.

Summary

• The credit_transactions table acts as an immutable financial ledger, recording every credit balance change as an individual row rather than updating a cached total
• The schema supports five transaction types (grant, purchase, generation, refund, adjustment) with signed integers indicating credit direction (positive for additions, negative for consumption)
• Database functions reserve_generation_usage and release_generation_reservation automate billing workflows during the image generation lifecycle
• Row-Level Security policies ensure users can only access their own transaction records, protecting financial privacy
• The /api/me endpoint and api/_lib/billing.js module provide standardized access patterns for querying and creating transaction records

Frequently Asked Questions

What transaction types does the credit_transactions table support?

The table uses a PostgreSQL enum with five values: grant (promotional or free credits), purchase (credits bought by the user), generation (credits consumed for image generation), refund (credits returned due to failures), and adjustment (manual administrative changes).

How does the application prevent users from viewing other users' credit history?

Row-Level Security (RLS) policies defined in supabase/migrations/202605090001_user_credits.sql (lines 54-57) restrict all SELECT queries to rows where the user_id column matches the authenticated user's UUID, ensuring complete isolation of financial data between accounts.

Can administrators manually add or remove credits from user accounts?

Yes, administrators can insert rows directly with type = 'adjustment'. The metadata JSONB field stores the justification for the change (such as {"reason":"promo"} or {"reason":"billing_correction"}), creating a permanent audit trail for all manual interventions.

How does the system calculate a user's current credit balance?

The application computes the real-time balance by executing COALESCE(SUM(amount), 0) across all rows matching the user's ID. This treats positive values as deposits and negative values as charges, deriving the current total from the complete transaction history rather than maintaining a separate balance column that could drift out of sync.

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