---
name: 0xjitsu/supabase-schema
source: https://app.decimal.ai/s/0xjitsu-supabase-schema@1/SKILL.md
source_sha256: 41e010dfa59d
---

# Supabase Schema Skill

Natural language to production-ready Supabase migration pipeline.

## When to Trigger

Activate this skill when the user:
- Asks to design a database schema or data model
- Wants to create tables or set up Supabase
- Requests a migration file or SQL generation
- Describes entities and relationships in plain English

## Input

A natural language description of the data model. Examples:
- "I need a users table with auth, a posts table with comments, and a media uploads table"
- "Set up a multi-tenant SaaS schema with organizations, members, and billing"
- "Create a simple blog with categories, tags, and full-text search"

## Output Pipeline

### Step 1: Parse Requirements into Entity-Relationship Model

Read the user's description and extract:
- **Entities** (tables) with their attributes
- **Relationships** (one-to-one, one-to-many, many-to-many)
- **Constraints** (unique, not null, check)
- **Access patterns** (who reads/writes what)

Present a summary table before generating SQL:

| Entity | Key Attributes | Relationships |
|--------|---------------|---------------|
| users | email, name, avatar_url | has_many posts, has_many comments |
| posts | title, body, status | belongs_to user, has_many comments |

### Step 2: Generate CREATE TABLE Statements

Generate SQL with appropriate Postgres types:

```sql
-- Enable required extensions
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

CREATE TABLE public.users (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  email text UNIQUE NOT NULL,
  full_name text,
  avatar_url text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
```

**Type selection rules:**
- Primary keys: `uuid` with `gen_random_uuid()` default — never serial/bigserial
- Timestamps: `timestamptz` — never `timestamp` without timezone
- Free text: `text` — never `varchar` unless a hard length limit is required
- Structured data: `jsonb` — never `json`
- Enums: prefer `CHECK` constraints over Postgres `ENUM` types (easier to migrate)
- Money: `numeric(12,2)` or `bigint` (cents) — never `float`/`double`
- Status fields: `text` with `CHECK (status IN ('draft', 'published', 'archived'))`

### Step 3: Generate RLS Policies

Enable RLS on every table and generate default policies:

```sql
ALTER TABLE public.posts ENABLE ROW LEVEL SECURITY;

-- Users can read all published posts
CREATE POLICY "Public posts are viewable by everyone"
  ON public.posts FOR SELECT
  USING (status = 'published');

-- Users can CRUD their own posts
CREATE POLICY "Users can manage their own posts"
  ON public.posts FOR ALL
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);
```

**Default RLS rules:**
- Users can only read/write their own rows unless explicitly public
- Service role bypasses RLS (for server-side operations)
- Anonymous access is denied by default
- Admin roles get separate, broader policies

### Step 4: Generate Indexes

```sql
-- Foreign key indexes (Postgres does NOT auto-index FK columns)
CREATE INDEX idx_posts_user_id ON public.posts (user_id);
CREATE INDEX idx_comments_post_id ON public.comments (post_id);

-- Common query pattern indexes
CREATE INDEX idx_posts_status ON public.posts (status) WHERE status = 'published';
CREATE INDEX idx_posts_created_at ON public.posts (created_at DESC);
```

**Index rules:**
- Always index foreign key columns
- Add partial indexes for status filters
- Add `DESC` indexes for timestamp ordering
- Consider GIN indexes for `jsonb` and full-text search columns
- Do not over-index — each index slows writes

### Step 5: Generate Updated-At Trigger

```sql
CREATE OR REPLACE FUNCTION public.handle_updated_at()
RETURNS trigger AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply to all tables with updated_at
CREATE TRIGGER set_updated_at
  BEFORE UPDATE ON public.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_updated_at();
```

### Step 6: Generate TypeScript Types

Use Supabase MCP to generate types:
- Tool: `generate_typescript_types` with the project ID
- Output the types to `src/types/database.ts`

### Step 7: Optionally Apply Migration

**Safety protocol — ALWAYS follow this sequence:**
1. Show the complete SQL to the user
2. Ask for explicit confirmation before applying
3. Use Supabase MCP `apply_migration` with a descriptive name
4. Verify by running `list_tables` after application

Never auto-apply without confirmation. This is a hard rule.

## Best Practices Checklist

Before presenting the migration, verify:

- [ ] Every table has `id uuid PRIMARY KEY DEFAULT gen_random_uuid()`
- [ ] Every table has `created_at timestamptz NOT NULL DEFAULT now()`
- [ ] Every table has `updated_at timestamptz NOT NULL DEFAULT now()`
- [ ] RLS is enabled on every table
- [ ] At least one RLS policy exists per table
- [ ] All foreign keys have corresponding indexes
- [ ] `ON DELETE` behavior is specified (`CASCADE` or `SET NULL`)
- [ ] Status/enum columns use `CHECK` constraints
- [ ] The `handle_updated_at` trigger is applied to all tables
- [ ] No hardcoded user IDs or secrets in the SQL

## Example Interaction

**User:** "I need a schema for a task management app with projects, tasks, and team members"

**Agent output:**
1. Entity-relationship summary table
2. Full SQL migration (extensions, tables, RLS, indexes, triggers)
3. Confirmation prompt: "Ready to apply this migration? (yes/no)"
4. On confirmation: apply via MCP and generate TypeScript types