Supabase Database Schema for Pokémon Card Collections: Cards, Quantities, and Trade Associations

The Supabase backend in marcelpanse/tcg-pocket-collection-tracker implements a relational schema centered on a collection table that tracks per-user card quantities, an account table for caching metadata, and normalized trades and trade_items tables to manage peer-to-peer exchange workflows.

The tcg-pocket-collection-tracker application uses Supabase as its persistence layer to store Pokémon TCG Pocket card ownership and facilitate trading between users. This article examines the actual database schema implemented in the repository, detailing how card quantities, user associations, and trade states are modeled based on the TypeScript service implementations and edge functions.

Core Collection Tables

The Collection Table

At the heart of the schema is the collection table, which maintains a row for every unique card owned by a user. Each record uses internal_id as an auto-generated primary key and links to auth.users.id via the user_id foreign key. The table stores the specific card_id referencing the static card catalogue and tracks quantity through the amount_owned integer column.

Notably, the table includes a collection JSONB column containing an array of card IDs. According to frontend/src/services/collection/collectionService.ts, the frontend leverages this array for rapid membership testing—determining whether a specific card ID exists in a user's set without parsing full relational rows.

User Account Metadata

Complementing the collection data is the account table, keyed by id with a foreign key relationship to auth.users.id. This table stores per-user metadata, specifically the collection_last_updated timestamp.

As implemented in frontend/src/services/collection/collectionService.ts, this column acts as a cache invalidation signal. The service reads this timestamp to decide whether client-side collection data is stale, updating it after every mutation to ensure synchronization across sessions.

Static Card Reference

The cards table serves as the immutable reference catalogue for all Pokémon TCG Pocket cards. It contains static attributes such as card_id (the official Pokémon TCG identifier), name, expansion_code, and rarity. While the repository does not contain the DDL for this table, the collection table references it through foreign key constraints on card_id, and the frontend types in frontend/src/types/index.ts mirror this structure.

Trade Management Schema

The Trades Table

Trade proposals between users reside in the trades table. This table uses an auto-generated id primary key and establishes relationships between participants through sender_id and receiver_id, both foreign keys to auth.users.id.

The workflow state is managed through a status enum column supporting four values: offered, accepted, declined, and finished. The created_at and updated_at timestamp columns track the lifecycle of each proposal, allowing the system to sort and filter trades by recency.

Trade Items Junction

Individual cards within a trade are stored in the trade_items table, which implements a composite primary key of (trade_id, card_id). This design links to the parent trade via trade_id and references the specific card via card_id.

The table captures exchange quantities through the quantity integer column and distinguishes flow direction using the direction enum. Valid values are offered (cards the sender provides) or requested (cards sought from the receiver), enabling the system to calculate net exchanges during trade finalization.

Querying the Schema in Practice

Fetching and Mutating Collections

The frontend/src/services/collection/collectionService.ts file interacts directly with the collection table. The service performs atomic upsert operations to synchronize local state with the server and deletes specific entries when users remove cards from their inventory.

// Upsert collection rows from local state
const collectionResult = await supabase.from('collection').upsert(collectionRows)

// Delete specific card ownership record
const { error: collectionError } = await supabase.from('collection').delete().eq('card_id', cardId)

After mutations, the service updates the collection_last_updated timestamp in the account table to mark the cache as fresh.

Aggregating Collection Statistics

The edge function in supabase/functions/stats-tracker/index.ts performs server-side aggregation across the entire collection table to compute global ownership totals. The function executes raw SQL to sum the amount_owned column efficiently at the database level:

SELECT SUM(amount_owned) AS count FROM collection;

Discovering Trade Partners

The supabase/functions/get-trading-partners/index.ts edge function queries the intersection of collection and account tables to identify active traders. It filters users based on the collection_last_updated timestamp to surface only those with recently modified inventories, optimizing the matching algorithm:

// Fragment from get-trading-partners/index.ts
AND collection_last_updated IS NOT NULL
ORDER BY collection_last_updated DESC

Practical Implementation Examples

The following TypeScript examples demonstrate typical CRUD patterns against this schema, as inferred from the service implementations.

Retrieving a user's mapped collection for frontend state management:

import { supabase } from '@/lib/supabase'

export const getCollection = async (email: string) => {
  const { data, error } = await supabase
    .from('collection')
    .select('card_id, amount_owned')
    .eq('user_id', email)
  
  if (error) throw error
  
  return new Map(data.map(row => [row.card_id, row.amount_owned]))
}

Creating a trade offer with directional items:

export const createTrade = async (
  senderId: string, 
  receiverId: string, 
  offered: {cardId: number, qty: number}[]
) => {
  const { data: trade, error } = await supabase
    .from('trades')
    .insert({ sender_id: senderId, receiver_id: receiverId, status: 'offered' })
    .single()
  
  if (error) throw error

  const tradeItems = offered.map(o => ({
    trade_id: trade.id,
    card_id: o.cardId,
    quantity: o.qty,
    direction: 'offered'
  }))
  
  const { error: itemsErr } = await supabase.from('trade_items').insert(tradeItems)
  if (itemsErr) throw itemsErr
  
  return trade
}

Summary

• The collection table stores individual user card ownership with amount_owned quantities and a JSONB collection array for fast client-side lookups.
• The account table manages per-user metadata including the collection_last_updated timestamp for cache synchronization and trade partner discovery.
• Trade workflows rely on the trades table for proposal state management and the trade_items junction table to track specific cards, quantities, and directionality between sender and receiver.
• Edge functions in supabase/functions/ perform aggregations and partner matching directly against these tables using SQL and Supabase client libraries.
• Foreign key relationships consistently reference auth.users.id for user identity, ensuring tight integration with Supabase Auth.

Frequently Asked Questions

How does the collection table handle multiple copies of the same card?

The collection table uses the amount_owned integer column to track quantities. Rather than creating duplicate rows for each copy, the application increments this value within the single row identified by the composite relationship between user_id and card_id. This approach minimizes table bloat and simplifies aggregation queries.

What is the purpose of the JSONB collection column in the collection table?

The collection JSONB column stores an array of card IDs owned by the user. According to the source analysis of collectionService.ts, this denormalized structure allows the frontend to perform rapid membership tests—checking whether a specific card exists in a user's set—without querying individual rows or parsing complex relational data.

How are trade statuses managed in the database?

The trades table includes a status enum column that restricts values to offered, accepted, declined, or finished. This state machine approach allows the application to track a trade proposal from initial creation through completion, with the updated_at timestamp recording the exact moment of the last state transition.

Where is the actual database DDL defined for these tables?

The repository does not contain migration files or SQL DDL scripts. As noted in the source analysis, the tables are created through the Supabase UI or external migration scripts not stored in the repository. The schema structure is inferred from the TypeScript type definitions in frontend/src/types/index.ts and the query patterns observed in collectionService.ts and the Supabase edge functions.

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