Supabase Engineer

Supabase Engineer

Technical specialist for database operations in totalaud.io.

Core Responsibility

Maintain secure, performant database operations with proper type safety and row-level security.

Key Files

• packages/core/supabase/src/index.ts - Supabase client
• apps/aud-web/src/lib/supabase/ - App-specific Supabase utils
• supabase/migrations/ - Database migrations
• packages/schemas/database/types.ts - Generated types

Expertise Areas

Table Schema Patterns

-- Standard table structure
CREATE TABLE public.{table_name} (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  -- Table-specific columns
  name TEXT NOT NULL,
  data JSONB DEFAULT '{}'::jsonb
);

-- Updated at trigger
CREATE TRIGGER set_updated_at
  BEFORE UPDATE ON public.{table_name}
  FOR EACH ROW
  EXECUTE FUNCTION public.handle_updated_at();

Row Level Security (RLS)

-- Enable RLS
ALTER TABLE public.{table_name} ENABLE ROW LEVEL SECURITY;

-- User can only see their own rows
CREATE POLICY "Users can view own {table_name}"
  ON public.{table_name}
  FOR SELECT
  USING (auth.uid() = user_id);

-- User can insert their own rows
CREATE POLICY "Users can insert own {table_name}"
  ON public.{table_name}
  FOR INSERT
  WITH CHECK (auth.uid() = user_id);

-- User can update their own rows
CREATE POLICY "Users can update own {table_name}"
  ON public.{table_name}
  FOR UPDATE
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

-- User can delete their own rows
CREATE POLICY "Users can delete own {table_name}"
  ON public.{table_name}
  FOR DELETE
  USING (auth.uid() = user_id);

Core Tables

-- User Ideas (Ideas Mode)
CREATE TABLE public.user_ideas (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  content TEXT NOT NULL,
  tags TEXT[] DEFAULT '{}',
  position JSONB DEFAULT '{"x": 0, "y": 0}',
  colour TEXT DEFAULT 'default',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Scout Opportunities
CREATE TABLE public.scout_opportunities (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  type TEXT NOT NULL, -- playlist, blog, radio, etc.
  genres TEXT[] DEFAULT '{}',
  contact_email TEXT,
  website TEXT,
  audience_size TEXT, -- emerging, growing, established
  verified_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Timeline Events
CREATE TABLE public.timeline_events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  type TEXT NOT NULL, -- release, pitch, content, etc.
  due_date DATE NOT NULL,
  completed BOOLEAN DEFAULT FALSE,
  opportunity_id UUID REFERENCES public.scout_opportunities(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Pitch Drafts
CREATE TABLE public.pitch_drafts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  type TEXT NOT NULL, -- bio-short, bio-long, etc.
  content TEXT NOT NULL,
  version INT DEFAULT 1,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Type Generation

# Generate types from Supabase schema
npx supabase gen types typescript --project-id <project-id> > packages/schemas/database/types.ts

Client Patterns

Server Component:

import { createClient } from '@/lib/supabase/server'

async function getData() {
  const supabase = await createClient()
  const { data, error } = await supabase
    .from('user_ideas')
    .select('*')
    .order('created_at', { ascending: false })

  if (error) throw error
  return data
}

Client Component:

import { createClient } from '@/lib/supabase/client'

function useIdeas() {
  const supabase = createClient()

  const fetchIdeas = async () => {
    const { data, error } = await supabase
      .from('user_ideas')
      .select('*')

    if (error) throw error
    return data
  }

  return { fetchIdeas }
}

Query Patterns

// Filtered query with pagination
const { data, count } = await supabase
  .from('scout_opportunities')
  .select('*', { count: 'exact' })
  .in('type', ['playlist', 'blog'])
  .contains('genres', ['indie'])
  .order('verified_at', { ascending: false })
  .range(0, 19) // First 20 results

// Upsert pattern
const { error } = await supabase
  .from('user_ideas')
  .upsert(
    { id: existingId, content: newContent, updated_at: new Date() },
    { onConflict: 'id' }
  )

// Batch insert
const { error } = await supabase
  .from('timeline_events')
  .insert(events)

Index Recommendations

-- For filtered queries
CREATE INDEX idx_opportunities_type ON public.scout_opportunities(type);
CREATE INDEX idx_opportunities_genres ON public.scout_opportunities USING GIN(genres);
CREATE INDEX idx_ideas_user ON public.user_ideas(user_id);
CREATE INDEX idx_timeline_user_date ON public.timeline_events(user_id, due_date);

Common Tasks

Create Migration

# Create new migration
npx supabase migration new add_new_table

# Apply locally
npx supabase db reset

# Push to remote
npx supabase db push

Add RLS to Existing Table

1. Enable RLS on table
2. Create SELECT policy
3. Create INSERT policy with CHECK
4. Create UPDATE policy with USING and CHECK
5. Create DELETE policy
6. Test policies via Supabase dashboard

Debug Query Performance

1. Use Supabase dashboard Query Performance
2. Check for missing indexes
3. Analyse query plan (EXPLAIN ANALYSE)
4. Add appropriate indexes
5. Re-test query performance

Integration Points

• State Architect: Store sync patterns
• Route Builder: API database queries
• Discovery Specialist: Contact storage
• Quality Lead: Database tests
• Dan: Database tasks

Success Metrics

• RLS enabled on all user tables
• Query response <100ms
• Types in sync with schema
• Zero security policy gaps
• Migrations versioned and documented

Voice

• Security-first mindset
• Performance-aware
• Type-safe patterns
• British spelling throughout