CLAUDE.md

Supabase Development Guidelines for Claude Code

Project: Dataprint Web Application Database: PostgreSQL via Supabase Framework: Next.js 15 with App Router Primary Principle: CLI-First, Clean Code, Type-Safe Development

---

🚨 CRITICAL RULES - NEVER VIOLATE THESE

1. NEVER make manual changes to the database - Always use Supabase CLI
2. ALWAYS test locally before deploying - Use supabase test db
3. ALWAYS generate TypeScript types after schema changes
4. NEVER skip migrations - Even for "small" changes
5. ALWAYS use descriptive migration names - Not "update" or "fix"
6. ALWAYS write RLS policies for new tables
7. ALWAYS include proper constraints and indexes

---

📋 Required Development Workflow

For ALL Database Changes:

# 1. Create a descriptive migration
supabase migration new add_user_authentication_system

# 2. Write the migration with proper structure (see templates below)

# 3. Test locally
supabase db reset
supabase test db

# 4. Generate TypeScript types
supabase gen types typescript --local > types/database.types.ts

# 5. Deploy to remote (only after local testing passes)
supabase db push

# 6. Generate types for remote
supabase gen types typescript --project-id iamkiyhvukvncjebzhcy > types/database.types.ts

---

🏗️ Clean Code Naming Conventions

Table Names

• ✅ USE: Singular, descriptive nouns
  • user, organization, project, pursuit, document
• ❌ AVOID: Plural, abbreviations, generic names
  • users, orgs, proj, data, items

Column Names

• ✅ USE: Snake_case, descriptive, unambiguous
  • clerk_user_id, created_at, primary_email_verified
• ❌ AVOID: Abbreviations, ambiguous names
  • usr_id, ts, verified, active

Migration Names

• ✅ USE: Verb_noun pattern, highly descriptive
  • add_user_authentication_system
  • create_pursuit_collaboration_workflow
  • add_rls_policies_for_organizations
  • add_full_text_search_to_projects
• ❌ AVOID: Generic, vague, or version-like names
  • update, fix, v2, changes, alter_table

Function Names

• ✅ USE: Verb_noun, clear purpose
  • sync_clerk_user_to_database
  • calculate_project_budget_total
  • validate_pursuit_requirements
• ❌ AVOID: Vague or generic
  • process, handler, update, sync

---

📝 Migration Templates

Template 1: Creating a New Table

-- Migration: create_collaboration_request_system
-- Description: Add tables to manage collaboration requests between organizations
-- Author: Claude Code
-- Date: 2026-02-13

-- Create the collaboration_requests table
CREATE TABLE IF NOT EXISTS collaboration_request (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Foreign keys with proper naming
  requesting_organization_id text NOT NULL REFERENCES organization(clerk_organization_id) ON DELETE CASCADE,
  target_organization_id text NOT NULL REFERENCES organization(clerk_organization_id) ON DELETE CASCADE,
  pursuit_id text REFERENCES pursuit(id) ON DELETE CASCADE,

  -- Status tracking
  status text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'accepted', 'declined', 'cancelled')),

  -- Request details
  message text,
  requested_capabilities text[],

  -- Audit fields
  created_by text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  responded_at timestamptz,

  -- Constraints
  CONSTRAINT different_organizations CHECK (requesting_organization_id != target_organization_id)
);

-- Create indexes for common queries
CREATE INDEX idx_collaboration_request_requesting_org ON collaboration_request(requesting_organization_id);
CREATE INDEX idx_collaboration_request_target_org ON collaboration_request(target_organization_id);
CREATE INDEX idx_collaboration_request_status ON collaboration_request(status);
CREATE INDEX idx_collaboration_request_pursuit ON collaboration_request(pursuit_id) WHERE pursuit_id IS NOT NULL;

-- Enable Row Level Security
ALTER TABLE collaboration_request ENABLE ROW LEVEL SECURITY;

-- RLS Policy: Users can view requests for their organizations
CREATE POLICY "Users can view collaboration requests for their organizations"
  ON collaboration_request
  FOR SELECT
  USING (
    requesting_organization_id IN (
      SELECT clerk_organization_id
      FROM organization_membership
      WHERE clerk_user_id = auth.uid()
    )
    OR target_organization_id IN (
      SELECT clerk_organization_id
      FROM organization_membership
      WHERE clerk_user_id = auth.uid()
    )
  );

-- RLS Policy: Users can create requests from their organizations
CREATE POLICY "Users can create collaboration requests from their organizations"
  ON collaboration_request
  FOR INSERT
  WITH CHECK (
    requesting_organization_id IN (
      SELECT clerk_organization_id
      FROM organization_membership
      WHERE clerk_user_id = auth.uid()
    )
  );

-- RLS Policy: Target organization admins can update request status
CREATE POLICY "Target organization admins can respond to requests"
  ON collaboration_request
  FOR UPDATE
  USING (
    target_organization_id IN (
      SELECT clerk_organization_id
      FROM organization_membership
      WHERE clerk_user_id = auth.uid()
      AND role = 'admin'
    )
  );

-- Add helpful comment
COMMENT ON TABLE collaboration_request IS 'Manages collaboration requests between organizations for pursuits';
COMMENT ON COLUMN collaboration_request.status IS 'Current state: pending (awaiting response), accepted, declined, cancelled (by requester)';

Template 2: Adding Columns to Existing Table

-- Migration: add_microsoft_teams_integration_to_pursuit
-- Description: Add Teams chat and channel tracking to pursuits
-- Author: Claude Code
-- Date: 2026-02-13

-- Add new columns with proper types and constraints
ALTER TABLE pursuit
  ADD COLUMN IF NOT EXISTS teams_chat_id text,
  ADD COLUMN IF NOT EXISTS teams_channel_id text,
  ADD COLUMN IF NOT EXISTS teams_integration_enabled boolean NOT NULL DEFAULT false,
  ADD COLUMN IF NOT EXISTS last_teams_sync_at timestamptz;

-- Add index for Teams lookups
CREATE INDEX IF NOT EXISTS idx_pursuit_teams_chat
  ON pursuit(teams_chat_id)
  WHERE teams_chat_id IS NOT NULL;

-- Add comments for documentation
COMMENT ON COLUMN pursuit.teams_chat_id IS 'Microsoft Teams group chat ID for pursuit collaboration';
COMMENT ON COLUMN pursuit.teams_channel_id IS 'Microsoft Teams channel ID for pursuit-specific discussions';
COMMENT ON COLUMN pursuit.teams_integration_enabled IS 'Whether Microsoft Teams integration is active for this pursuit';

Template 3: Creating Database Functions

-- Migration: add_full_text_search_for_projects
-- Description: Add full-text search capability across project fields
-- Author: Claude Code
-- Date: 2026-02-13

-- Create a function to generate search vector
CREATE OR REPLACE FUNCTION generate_project_search_vector(project_record project)
RETURNS tsvector
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
  RETURN
    setweight(to_tsvector('english', coalesce(project_record.name, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(project_record.description, '')), 'B') ||
    setweight(to_tsvector('english', coalesce(project_record.client, '')), 'B') ||
    setweight(to_tsvector('english', coalesce(project_record.location, '')), 'C') ||
    setweight(to_tsvector('english', coalesce(array_to_string(project_record.services_provided, ' '), '')), 'C');
END;
$$;

-- Add search vector column
ALTER TABLE project
  ADD COLUMN IF NOT EXISTS search_vector tsvector;

-- Create index for fast searching
CREATE INDEX IF NOT EXISTS idx_project_search_vector
  ON project
  USING GIN(search_vector);

-- Create trigger to keep search vector updated
CREATE OR REPLACE FUNCTION update_project_search_vector()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.search_vector := generate_project_search_vector(NEW);
  RETURN NEW;
END;
$$;

CREATE TRIGGER trigger_update_project_search_vector
  BEFORE INSERT OR UPDATE ON project
  FOR EACH ROW
  EXECUTE FUNCTION update_project_search_vector();

-- Backfill existing records
UPDATE project SET search_vector = generate_project_search_vector(project.*);

-- Add helpful comment
COMMENT ON COLUMN project.search_vector IS 'Full-text search vector for efficient project searching';
COMMENT ON FUNCTION generate_project_search_vector IS 'Generates weighted search vector from project fields';

---

🔧 Edge Function Templates

Template: Clean, Testable Edge Function

// supabase/functions/sync-clerk-webhook/index.ts

import { serve } from 'https://deno.land/std@0.168.0/http/server.ts'
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2'

// ============================================================================
// TYPES - Define all types at the top
// ============================================================================

interface ClerkWebhookEvent {
  type: string
  data: {
    id: string
    email_addresses?: Array<{ email_address: string }>
    first_name?: string
    last_name?: string
  }
}

interface SupabaseUserRecord {
  id: string
  email: string | null
  first_name: string | null
  last_name: string | null
  updated_at: string
}

interface WebhookResponse {
  success: boolean
  message: string
  error?: string
}

// ============================================================================
// PURE FUNCTIONS - Testable business logic
// ============================================================================

/**
 * Validates that the webhook event has required fields
 */
export function validateWebhookEvent(event: unknown): event is ClerkWebhookEvent {
  if (!event || typeof event !== 'object') return false

  const evt = event as Partial<ClerkWebhookEvent>
  return !!(evt.type && evt.data?.id)
}

/**
 * Transforms Clerk user data to Supabase user record
 */
export function transformClerkUserToSupabase(clerkData: ClerkWebhookEvent['data']): SupabaseUserRecord {
  return {
    id: clerkData.id,
    email: clerkData.email_addresses?.[0]?.email_address ?? null,
    first_name: clerkData.first_name ?? null,
    last_name: clerkData.last_name ?? null,
    updated_at: new Date().toISOString(),
  }
}

/**
 * Determines what action to take based on webhook event type
 */
export function getActionFromEventType(eventType: string): 'upsert' | 'delete' | 'ignore' {
  if (eventType === 'user.created' || eventType === 'user.updated') return 'upsert'
  if (eventType === 'user.deleted') return 'delete'
  return 'ignore'
}

// ============================================================================
// MAIN HANDLER - Keep thin, delegate to pure functions
// ============================================================================

serve(async (req) => {
  // CORS headers
  if (req.method === 'OPTIONS') {
    return new Response('ok', {
      headers: {
        'Access-Control-Allow-Origin': '*',
        'Access-Control-Allow-Methods': 'POST, OPTIONS',
        'Access-Control-Allow-Headers': 'authorization, x-client-info, apikey, content-type',
      },
    })
  }

  try {
    // 1. Parse request
    const event = await req.json()

    // 2. Validate
    if (!validateWebhookEvent(event)) {
      return new Response(
        JSON.stringify({ success: false, error: 'Invalid webhook payload' }),
        { status: 400, headers: { 'Content-Type': 'application/json' } }
      )
    }

    // 3. Determine action
    const action = getActionFromEventType(event.type)

    if (action === 'ignore') {
      return new Response(
        JSON.stringify({ success: true, message: 'Event type ignored' }),
        { status: 200, headers: { 'Content-Type': 'application/json' } }
      )
    }

    // 4. Initialize Supabase client
    const supabase = createClient(
      Deno.env.get('SUPABASE_URL') ?? '',
      Deno.env.get('SUPABASE_SERVICE_ROLE_KEY') ?? ''
    )

    // 5. Perform action
    if (action === 'upsert') {
      const userData = transformClerkUserToSupabase(event.data)
      const { error } = await supabase.from('users').upsert(userData)

      if (error) throw error
    } else if (action === 'delete') {
      const { error } = await supabase
        .from('users')
        .delete()
        .eq('id', event.data.id)

      if (error) throw error
    }

    return new Response(
      JSON.stringify({ success: true, message: 'Webhook processed' }),
      { status: 200, headers: { 'Content-Type': 'application/json' } }
    )

  } catch (error) {
    console.error('Webhook processing error:', error)
    return new Response(
      JSON.stringify({
        success: false,
        error: error instanceof Error ? error.message : 'Unknown error'
      }),
      { status: 500, headers: { 'Content-Type': 'application/json' } }
    )
  }
})

Template: Edge Function Tests

// supabase/functions/sync-clerk-webhook/index.test.ts

import { assertEquals } from 'https://deno.land/std@0.168.0/testing/asserts.ts'
import {
  validateWebhookEvent,
  transformClerkUserToSupabase,
  getActionFromEventType,
} from './index.ts'

Deno.test('validateWebhookEvent - valid event', () => {
  const event = {
    type: 'user.created',
    data: { id: 'user_123' },
  }

  assertEquals(validateWebhookEvent(event), true)
})

Deno.test('validateWebhookEvent - invalid event', () => {
  assertEquals(validateWebhookEvent(null), false)
  assertEquals(validateWebhookEvent({}), false)
  assertEquals(validateWebhookEvent({ type: 'test' }), false)
})

Deno.test('transformClerkUserToSupabase - complete data', () => {
  const clerkData = {
    id: 'user_123',
    email_addresses: [{ email_address: 'test@example.com' }],
    first_name: 'John',
    last_name: 'Doe',
  }

  const result = transformClerkUserToSupabase(clerkData)

  assertEquals(result.id, 'user_123')
  assertEquals(result.email, 'test@example.com')
  assertEquals(result.first_name, 'John')
  assertEquals(result.last_name, 'Doe')
})

Deno.test('getActionFromEventType', () => {
  assertEquals(getActionFromEventType('user.created'), 'upsert')
  assertEquals(getActionFromEventType('user.updated'), 'upsert')
  assertEquals(getActionFromEventType('user.deleted'), 'delete')
  assertEquals(getActionFromEventType('unknown'), 'ignore')
})

---

🧪 Testing Requirements

Before Every Deploy

# 1. Reset local database to clean state
supabase db reset

# 2. Run all database tests
supabase test db

# 3. Test Edge Functions locally
supabase functions serve sync-clerk-webhook

# 4. Run Edge Function tests
deno test --allow-all supabase/functions/sync-clerk-webhook/index.test.ts

Test Coverage Requirements

• ✅ Database migrations: Must include test data in seed.sql
• ✅ Edge Functions: Must have unit tests for pure functions
• ✅ RLS Policies: Must test with different user roles
• ✅ Constraints: Must test edge cases (null values, duplicates, etc.)

---

🎯 Type Safety Requirements

Always Generate Types After Schema Changes

# For local development
supabase gen types typescript --local > types/database.types.ts

# For production
supabase gen types typescript --project-id iamkiyhvukvncjebzhcy > types/database.types.ts

Use Generated Types in Code

import { Database } from '@/types/database.types'

type User = Database['public']['Tables']['user']['Row']
type UserInsert = Database['public']['Tables']['user']['Insert']
type UserUpdate = Database['public']['Tables']['user']['Update']

// ✅ Type-safe queries
const { data } = await supabase
  .from('user')
  .select('*')
  .returns<User[]>()

---

❌ Anti-Patterns to AVOID

1. Manual Database Changes

❌ NEVER: "Let me just quickly add a column in the Supabase dashboard"
✅ ALWAYS: Create a migration with proper structure

2. Generic Migration Names

❌ NEVER: "update.sql", "changes.sql", "fix.sql"
✅ ALWAYS: "add_user_authentication_system.sql"

3. Skipping Tests

❌ NEVER: "This is a small change, no need to test"
✅ ALWAYS: Run supabase test db before deploying

4. Missing RLS Policies

❌ NEVER: Create tables without RLS policies
✅ ALWAYS: Enable RLS and add appropriate policies

5. Missing Indexes

❌ NEVER: Forget indexes on foreign keys and commonly queried columns
✅ ALWAYS: Add indexes for joins, filters, and lookups

6. Unclear Column Names

❌ NEVER: usr_id, ts, data, value, active
✅ ALWAYS: clerk_user_id, created_at, request_payload, status

7. Missing Constraints

❌ NEVER: Allow invalid data through missing CHECKs
✅ ALWAYS: Add CHECK constraints for enums and business rules

8. Deploying Without Type Generation

❌ NEVER: Deploy schema changes without regenerating types
✅ ALWAYS: Generate and commit types after schema changes

---

📚 Quick Reference Commands

# Create new migration
supabase migration new <descriptive_name>

# Reset local database
supabase db reset

# Run tests
supabase test db

# Generate types (local)
supabase gen types typescript --local > types/database.types.ts

# Generate types (remote)
supabase gen types typescript --project-id iamkiyhvukvncjebzhcy > types/database.types.ts

# Deploy migration
supabase db push

# Check migration status
supabase migration list

# Create new Edge Function
supabase functions new <function-name>

# Test Edge Function
deno test --allow-all supabase/functions/<function-name>/index.test.ts

# Deploy Edge Function
supabase functions deploy <function-name>

# View Edge Function logs
supabase functions logs <function-name>

---

🎓 Example Workflow: Adding a New Feature

Let's say we need to add a "project favoriting" feature:

Step 1: Create Migration

supabase migration new create_project_favorites_system

Step 2: Write Migration

-- Migration: create_project_favorites_system
-- Description: Allow users to favorite projects for quick access

CREATE TABLE IF NOT EXISTS project_favorite (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  clerk_user_id text NOT NULL,
  project_id text NOT NULL REFERENCES project(id) ON DELETE CASCADE,
  created_at timestamptz NOT NULL DEFAULT now(),

  -- Prevent duplicate favorites
  UNIQUE(clerk_user_id, project_id)
);

CREATE INDEX idx_project_favorite_user ON project_favorite(clerk_user_id);
CREATE INDEX idx_project_favorite_project ON project_favorite(project_id);

ALTER TABLE project_favorite ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can manage their own favorites"
  ON project_favorite
  FOR ALL
  USING (clerk_user_id = auth.uid())
  WITH CHECK (clerk_user_id = auth.uid());

Step 3: Test Locally

supabase db reset
supabase test db

Step 4: Generate Types

supabase gen types typescript --local > types/database.types.ts

Step 5: Deploy

supabase db push
supabase gen types typescript --project-id iamkiyhvukvncjebzhcy > types/database.types.ts

Step 6: Use in Code

import { Database } from '@/types/database.types'

type ProjectFavorite = Database['public']['Tables']['project_favorite']['Row']

// Add favorite
await supabase.from('project_favorite').insert({
  clerk_user_id: user.id,
  project_id: projectId,
})

// Get user's favorites
const { data } = await supabase
  .from('project_favorite')
  .select('*, project(*)')
  .eq('clerk_user_id', user.id)

---

🔐 Security Checklist

Before deploying ANY migration:

• RLS is enabled on all new tables
• Appropriate RLS policies are in place
• Foreign key constraints use proper ON DELETE actions
• CHECK constraints prevent invalid states
• Sensitive columns are NOT exposed to anon role
• Indexes are added for all foreign keys
• Service role operations are logged
• Edge Functions validate input data
• Edge Functions handle errors gracefully

---

📖 Additional Resources

• Supabase CLI Reference
• PostgreSQL Naming Conventions
• RLS Policy Examples
• Edge Functions Guide

---

Remember: Clean code is not about being clever—it's about being clear, maintainable, and correct. Always prioritize clarity and safety over speed.