Supabase

Supabase Database & Auth

Overview

This skill covers managing the Supabase PostgreSQL database for the Slay the Spire Deck Builder. Supabase provides the database, authentication, and auto-generated REST APIs on the free tier.

CLI Setup

# Install Supabase CLI
brew install supabase/tap/supabase

# Login
supabase login

# Link to remote project (get ref from Supabase dashboard URL)
supabase link --project-ref <your-project-ref>

Available Tools

All tools are in tools/supabase/. Run from the copilot-skills root.

Run Migrations

# List available migrations
./tools/supabase/migrate.sh

# Dry run (preview SQL without executing)
./tools/supabase/migrate.sh initial-schema.sql --dry-run

# Execute migration
./tools/supabase/migrate.sh initial-schema.sql --project-ref abc123

# Via direct DB connection
./tools/supabase/migrate.sh initial-schema.sql --db-url postgresql://...

Query the Database

# Inline SQL
./tools/supabase/query.sh "SELECT count(*) FROM cards"

# From a file
./tools/supabase/query.sh --file path/to/query.sql

# Piped from stdin
echo "SELECT * FROM decks LIMIT 5" | ./tools/supabase/query.sh --stdin

Inspect Schema

# List all tables with sizes
./tools/supabase/schema.sh

# Describe a specific table
./tools/supabase/schema.sh --table cards

# Dump full schema to file
./tools/supabase/schema.sh --dump > schema_backup.sql

Seed Data

# Seed Ironclad and Silent cards
./tools/supabase/seed.sh --project-ref abc123

# If no CLI available, it prints the SQL for manual use
./tools/supabase/seed.sh

Database Schema

Current schema lives in database/migrations/initial-schema.sql.

Tables

Key Relationships

users 1──* decks 1──* deck_cards *──1 cards
users 1──* deck_likes *──1 decks
users 1──* comments *──1 decks

Row Level Security (RLS)

RLS is enabled on users, decks, and comments:

• Anyone can read public decks
• Users can only modify their own data
• Policies use auth.uid() to match the authenticated user

Environment Variables

Connecting from Code

Frontend (React)

import { createClient } from '@supabase/supabase-js'

const supabase = createClient(
  import.meta.env.VITE_SUPABASE_URL,
  import.meta.env.VITE_SUPABASE_ANON_KEY
)

// Query with RLS (user context)
const { data, error } = await supabase
  .from('decks')
  .select('*, cards:deck_cards(card:cards(*))')
  .eq('is_public', true)

Backend (Express)

import { createClient } from '@supabase/supabase-js'

// Service role bypasses RLS — use for admin operations only
const supabase = createClient(
  process.env.SUPABASE_URL,
  process.env.SUPABASE_SERVICE_ROLE_KEY
)

Common Tasks

Adding a New Migration

1. Create file: database/migrations/migration_YYYYMMDD_description.sql
2. Test with dry run: ./tools/supabase/migrate.sh migration_file.sql --dry-run
3. Run locally or via dashboard SQL editor
4. Commit the migration file

Checking Table Data

./tools/supabase/query.sh "SELECT table_name, n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC"

Resetting a Table

./tools/supabase/query.sh "TRUNCATE cards CASCADE"
./tools/supabase/seed.sh

Free Tier Limits

• Database: 500 MB storage
• Auth: 50,000 monthly active users
• API requests: unlimited
• Realtime: 200 concurrent connections
• File storage: 1 GB
• Pausing: Projects pause after 1 week of inactivity (reactivate in dashboard)
• Projects: max 2 active free projects