# SkillSlap Migration Writer

> **Purpose:** Generate a complete, production-ready Supabase migration SQL file following SkillSlap's exact conventions. No hand-editing the output — it should be paste-and-push quality.

---

## Invocation

```
/migration <feature-description>
```

**Examples:**
- `/migration Add a skill_reports table so users can flag inappropriate skills`
- `/migration Add a focus_pool_history table tracking daily focus_pool snapshots per skill`
- `/migration Add a skill_collections table for users to curate skill lists`

---

## SkillSlap Migration Conventions

### File naming
`YYYYMMDDHHMMSS_snake_case_description.sql`
Use the current timestamp. Keep description to 3–5 words max.

### Required header
```sql
-- Migration: [description]
-- Created: [date]
-- Purpose: [one sentence]
```

### Idempotency — always guard with IF NOT EXISTS
```sql
-- Tables
CREATE TABLE IF NOT EXISTS public.[table] (...);

-- Columns
ALTER TABLE public.[table]
  ADD COLUMN IF NOT EXISTS [col] [type];

-- Indexes
CREATE INDEX IF NOT EXISTS idx_[table]_[col] ON public.[table]([col]);
```

### Column conventions
```sql
id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
created_at  timestamptz NOT NULL DEFAULT now(),
updated_at  timestamptz NOT NULL DEFAULT now()
```

Always add an `updated_at` trigger:
```sql
CREATE TRIGGER [table]_updated_at
  BEFORE UPDATE ON public.[table]
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
-- Note: update_updated_at_column() already exists in SkillSlap schema
```

### Foreign keys
```sql
skill_id    uuid NOT NULL REFERENCES public.skills(id) ON DELETE CASCADE,
user_id     uuid NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
```

Always use `ON DELETE CASCADE` for child records unless there's a specific reason for `SET NULL` or `RESTRICT`.

### RLS — every user-data table must have it
```sql
ALTER TABLE public.[table] ENABLE ROW LEVEL SECURITY;

-- SELECT: authenticated users see their own rows
CREATE POLICY "[table]_select_own" ON public.[table]
  FOR SELECT TO authenticated
  USING (auth.uid() = user_id);

-- SELECT: public data (active status)
CREATE POLICY "[table]_select_public" ON public.[table]
  FOR SELECT USING (status = 'active');

-- INSERT: user can only insert their own rows
CREATE POLICY "[table]_insert_own" ON public.[table]
  FOR INSERT TO authenticated
  WITH CHECK (auth.uid() = user_id);

-- UPDATE: user can only update their own rows
CREATE POLICY "[table]_update_own" ON public.[table]
  FOR UPDATE TO authenticated
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

-- DELETE: user can only delete their own rows
CREATE POLICY "[table]_delete_own" ON public.[table]
  FOR DELETE TO authenticated
  USING (auth.uid() = user_id);

-- Service role bypass (only if needed for server-side writes)
-- Document WHY this is needed
CREATE POLICY "[table]_service_role" ON public.[table]
  USING (auth.role() = 'service_role');
```

### Enum types
```sql
CREATE TYPE public.[name]_status AS ENUM ('pending', 'active', 'archived');
-- Add IF NOT EXISTS guard using a named block for types
DO $block$ BEGIN
  CREATE TYPE public.[name]_status AS ENUM ('pending', 'active', 'archived');
EXCEPTION WHEN duplicate_object THEN NULL;
END $block$;
```

### Indexes — add for every foreign key and common query pattern
```sql
CREATE INDEX IF NOT EXISTS idx_[table]_user_id ON public.[table](user_id);
CREATE INDEX IF NOT EXISTS idx_[table]_skill_id ON public.[table](skill_id);
CREATE INDEX IF NOT EXISTS idx_[table]_created_at ON public.[table](created_at DESC);
-- Partial index for active rows only (common pattern)
CREATE INDEX IF NOT EXISTS idx_[table]_active ON public.[table](created_at DESC)
  WHERE status = 'active';
```

### GRANTs
```sql
GRANT SELECT, INSERT, UPDATE, DELETE ON public.[table] TO authenticated;
GRANT SELECT ON public.[table] TO anon;  -- only if table has public data
```

---

## Output Format

1. **Complete migration file** — ready to save as `supabase/migrations/[timestamp]_[name].sql`
2. **Down migration** — save as `supabase/migrations/down/[same-filename].sql`
3. **Summary** — bullet list of what the migration does (for commit message and PR)

### Down migration pattern
```sql
-- Down: [description]
DROP TABLE IF EXISTS public.[table] CASCADE;
DROP TYPE IF EXISTS public.[type];
```

---

## Checklist Before Outputting

- [ ] All tables have `id uuid PRIMARY KEY DEFAULT gen_random_uuid()`
- [ ] All tables have `created_at` + `updated_at` with trigger
- [ ] All foreign keys have `ON DELETE CASCADE` or explicit reason for alternative
- [ ] RLS enabled and all CRUD policies present for tables with user data
- [ ] Indexes on all foreign keys
- [ ] Idempotent (IF NOT EXISTS on all DDL)
- [ ] Down migration included
- [ ] GRANTs match data sensitivity (no anon grant on private tables)