Database Schema

ProductClank Arena uses Supabase (PostgreSQL) for data storage. This document outlines the key tables and relationships.

Core Tables

Products

products
├── id (uuid, primary key)
├── name (text)
├── description (text)
├── image_url (text)
├── website_url (text)
├── category (text)
├── tags (text[])
├── creator_id (uuid, foreign key → users)
├── believer_count (integer)
├── created_at (timestamp)
└── updated_at (timestamp)

Users

users
├── id (uuid, primary key)
├── farcaster_id (bigint, unique)
├── username (text)
├── display_name (text)
├── avatar_url (text)
├── wallet_address (text)
├── created_at (timestamp)
└── updated_at (timestamp)

Campaigns

campaigns
├── id (uuid, primary key)
├── product_id (uuid, foreign key → products)
├── creator_id (uuid, foreign key → users)
├── reward_amount (numeric)
├── reward_token (text)
├── start_date (timestamp)
├── end_date (timestamp)
├── status (text: 'draft' | 'active' | 'completed')
├── distribution_method (text)
├── created_at (timestamp)
└── updated_at (timestamp)

Auctions

auctions
├── id (uuid, primary key)
├── token_id (bigint, unique)
├── start_time (timestamp)
├── end_time (timestamp)
├── highest_bid (numeric)
├── highest_bidder (text)
├── is_settled (boolean)
├── winning_campaign_id (uuid, foreign key → campaigns)
├── tx_hash (text)
├── created_at (timestamp)
└── updated_at (timestamp)

Bids

bids
├── id (uuid, primary key)
├── auction_id (uuid, foreign key → auctions)
├── bidder_address (text)
├── bidder_id (uuid, foreign key → users)
├── amount (numeric)
├── campaign_id (uuid, foreign key → campaigns)
├── tx_hash (text)
├── created_at (timestamp)
└── is_refunded (boolean)

Believers (Product Votes)

product_believers
├── id (uuid, primary key)
├── product_id (uuid, foreign key → products)
├── user_id (uuid, foreign key → users)
├── created_at (timestamp)
└── UNIQUE(product_id, user_id)

Campaign Actions

campaign_actions
├── id (uuid, primary key)
├── campaign_id (uuid, foreign key → campaigns)
├── action_type (text)
├── action_data (jsonb)
├── reward_amount (numeric)
└── created_at (timestamp)

Action Submissions

action_submissions
├── id (uuid, primary key)
├── campaign_id (uuid, foreign key → campaigns)
├── action_id (uuid, foreign key → campaign_actions)
├── user_id (uuid, foreign key → users)
├── proof_data (jsonb)
├── status (text: 'pending' | 'approved' | 'rejected')
├── reviewed_at (timestamp)
├── created_at (timestamp)
└── updated_at (timestamp)

Leaderboard Tables

User Scores

user_scores
├── id (uuid, primary key)
├── user_id (uuid, foreign key → users)
├── builder_points (numeric)
├── believer_points (numeric)
├── combined_points (numeric)
├── last_updated (timestamp)
└── UNIQUE(user_id)

Leaderboard Snapshots

leaderboard_snapshots
├── id (uuid, primary key)
├── snapshot_date (date)
├── user_id (uuid, foreign key → users)
├── rank (integer)
├── builder_points (numeric)
├── believer_points (numeric)
├── combined_points (numeric)
└── created_at (timestamp)

Key Relationships

• Products → Users: Many-to-one (creator)
• Products → Believers: Many-to-many (users)
• Campaigns → Products: Many-to-one
• Campaigns → Users: Many-to-one (creator)
• Auctions → Campaigns: One-to-one (winning campaign)
• Bids → Auctions: Many-to-one
• Bids → Users: Many-to-one (bidder)
• Action Submissions → Campaigns: Many-to-one
• Action Submissions → Users: Many-to-one

Indexes

Key indexes for performance:

• Products: `creator_id`, `category`, `created_at`
• Believers: `product_id`, `user_id` (composite unique)
• Campaigns: `product_id`, `status`, `end_date`
• Auctions: `token_id`, `is_settled`, `end_time`
• Bids: `auction_id`, `bidder_address`, `created_at`
• User Scores: `user_id`, `combined_points`

Database Functions

Upsert Auction

CREATE OR REPLACE FUNCTION upsert_auction(
  p_token_id BIGINT,
  p_start_time TIMESTAMP,
  p_end_time TIMESTAMP
) RETURNS uuid AS $$
-- Atomic auction upsert to prevent race conditions
$$ LANGUAGE plpgsql;

Get Auction Participants

CREATE OR REPLACE FUNCTION get_auction_participants(
  p_auction_id UUID
) RETURNS TABLE(...) AS $$
-- Returns all participants for an auction
$$ LANGUAGE plpgsql;