# Supabase

> Supabase Database & Auth

- Skill: `cooler09/supabase` (Agent Skill)
- Install (CLI): `npx skillmds@latest add cooler09/supabase`
- Raw SKILL.md: https://api.skillmd.com/api/skills/cooler09/supabase/raw
- Safety review: pending (external: skill-scanner PASS, skillspector PASS)
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: cooler09 (https://skillmd.com/u/cooler09)
- Updated: 2026-09-22
- Page: https://skillmd.com/skills/cooler09/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

```bash
# 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.

| Script | Purpose |
|--------|---------|
| `tools/supabase/migrate.sh` | Run SQL migration files |
| `tools/supabase/query.sh` | Execute arbitrary SQL queries |
| `tools/supabase/schema.sh` | Inspect tables, columns, and indexes |
| `tools/supabase/seed.sh` | Seed with sample Slay the Spire card data |
| `tools/supabase/status.sh` | Check project and database health |

### Run Migrations

```bash
# 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

```bash
# 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

```bash
# 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

```bash
# 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

| Table | Purpose |
|-------|---------|
| `users` | User profiles (linked to Supabase Auth) |
| `decks` | User-created deck definitions |
| `cards` | Card catalog (all Slay the Spire cards) |
| `deck_cards` | Many-to-many: which cards are in which decks |
| `deck_likes` | User favorites/likes on decks |
| `comments` | User comments on decks |

### 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

| Variable | Used By | Description |
|----------|---------|-------------|
| `VITE_SUPABASE_URL` | Frontend | Project URL (safe for browser) |
| `VITE_SUPABASE_ANON_KEY` | Frontend | Public/anon key (safe for browser) |
| `SUPABASE_URL` | Backend | Project URL |
| `SUPABASE_ANON_KEY` | Backend | Public/anon key |
| `SUPABASE_SERVICE_ROLE_KEY` | Backend | Secret admin key — **never expose** |
| `DATABASE_URL` | Tools/psql | Direct PostgreSQL connection string |
| `SUPABASE_PROJECT_REF` | Tools/CLI | Project reference ID (from dashboard URL) |

## Connecting from Code

### Frontend (React)

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

```javascript
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

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

### Resetting a Table

```bash
./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