supabase-usage

# Supabase Database Patterns

Patterns for working with Supabase databases including Auth, Row Level Security, table relationships, and query best practices.

## Overview

- **MCP Tools**: Query and explore database structure
- **Authentication**: User management, sessions, auth tables
- **Row Level Security**: Policy patterns for data access control
- **Table Relationships**: Foreign keys, joins, nested queries
- **Query Patterns**: Filtering, pagination, performance

## MCP Tools

Available tools for database exploration:

- `mcp__supabase__list_tables` - List all tables in the database
- `mcp__supabase__get_table_schema` - Get schema for a specific table
- `mcp__supabase__execute_sql` - Run read-only SQL queries

**Workflow:**

1. Start with `list_tables` to understand database structure
2. Use `get_table_schema` to inspect columns and types
3. Use `execute_sql` for custom queries (read-only)

---

## Best Practices

### DO

- ✓ Enable RLS on all public tables
- ✓ Use `(select auth.uid())` in RLS policies for performance
- ✓ Add indexes on RLS-checked columns
- ✓ Specify roles with `TO authenticated` in policies
- ✓ Use `on delete cascade` for foreign keys to auth.users
- ✓ Use cursor-based pagination for large datasets
- ✓ Select only needed columns: `.select('id, name')` not `.select('*')`

### DON'T

- ✗ Store sensitive data without RLS
- ✗ Use `auth.uid()` directly in policies (use `(select auth.uid())`)
- ✗ Create policies without specifying roles
- ✗ Forget indexes on frequently filtered columns
- ✗ Use offset pagination for deep pages (>1000 rows)
- ✗ Expose auth.users directly via API (use public profiles table)

---

## Quick Reference

### Common Filters

| Filter           | JavaScript               | Python                   |
| ---------------- | ------------------------ | ------------------------ |
| Equals           | `.eq('col', val)`        | `.eq("col", val)`        |
| Not equals       | `.neq('col', val)`       | `.neq("col", val)`       |
| G