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

> Explore the Supabase database schema for Pokémon card collections. Learn how to manage cards, quantities, and trade associations using the relational design in tcg-pocket-collection-tracker.

- Repository: [Marcel Panse/tcg-pocket-collection-tracker](https://github.com/marcelpanse/tcg-pocket-collection-tracker)
- Tags: architecture
- Published: 2026-03-06

---

**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`](https://github.com/marcelpanse/tcg-pocket-collection-tracker/blob/main/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`](https://github.com/marcelpanse/tcg-pocket-collection-tracker/blob/main/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`](https://github.com/marcelpanse/tcg-pocket-collection-tracker/blob/main/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`](https://github.com/marcelpanse/tcg-pocket-collection-tracker/blob/main/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.

```typescript
// 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`](https://github.com/marcelpanse/tcg-pocket-collection-tracker/blob/main/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:

```sql
SELECT SUM(amount_owned) AS count FROM collection;

```

### Discovering Trade Partners

The [`supabase/functions/get-trading-partners/index.ts`](https://github.com/marcelpanse/tcg-pocket-collection-tracker/blob/main/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:

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

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

```typescript
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`](https://github.com/marcelpanse/tcg-pocket-collection-tracker/blob/main/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`](https://github.com/marcelpanse/tcg-pocket-collection-tracker/blob/main/frontend/src/types/index.ts) and the query patterns observed in [`collectionService.ts`](https://github.com/marcelpanse/tcg-pocket-collection-tracker/blob/main/collectionService.ts) and the Supabase edge functions.