Transaction Management Skill

Implement atomic database transactions for Supabase operations requiring consistency.

Purpose

Implement PostgreSQL transactions using Supabase RPC functions to ensure atomic, consistent operations across multiple tables.

When to Use

• Multiple related inserts/updates that must succeed together
• Complex business logic requiring atomicity
• Batch operations needing rollback on failure
• User mentions transactions, atomic operations, or rollbacks
• Operations requiring strong consistency guarantees

Instructions

1. Identify Transaction Boundary

   • Determine which operations must be atomic
   • Identify dependent operations
   • Plan rollback scenarios

2. Create Database Function

   • Write PL/pgSQL function with transaction logic
   • Include error handling with RAISE
   • Return appropriate result type
   • Add security definer if needed

3. Implement Client Code

   • Call RPC function from client
   • Handle function errors
   • Provide clear error messages

4. Test Scenarios

   • Test success case
   • Test rollback on error
   • Verify data consistency

Examples

Transfer Credits Between Users

-- Database function
CREATE OR REPLACE FUNCTION transfer_credits(
  from_user_id UUID,
  to_user_id UUID,
  amount INTEGER
)
RETURNS JSONB
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  from_balance INTEGER;
BEGIN
  -- Start transaction (implicit in function)

  -- Lock and check sender balance
  SELECT credits INTO from_balance
  FROM user_credits
  WHERE user_id = from_user_id
  FOR UPDATE;

  IF from_balance < amount THEN
    RAISE EXCEPTION 'Insufficient credits';
  END IF;

  -- Deduct from sender
  UPDATE user_credits
  SET credits = credits - amount
  WHERE user_id = from_user_id;

  -- Add to recipient
  UPDATE user_credits
  SET credits = credits + amount
  WHERE user_id = to_user_id;

  -- Log transaction
  INSERT INTO credit_transactions (from_user, to_user, amount)
  VALUES (from_user_id, to_user_id, amount);

  RETURN jsonb_build_object('success', true, 'amount', amount);
EXCEPTION
  WHEN OTHERS THEN
    -- Rollback happens automatically
    RAISE EXCEPTION 'Transfer failed: %', SQLERRM;
END;
$$;

// Client code
export async function transferCredits(
  fromUserId: string,
  toUserId: string,
  amount: number
) {
  const { data, error } = await supabase.rpc('transfer_credits', {
    from_user_id: fromUserId,
    to_user_id: toUserId,
    amount
  })

  if (error) {
    throw new Error(`Transfer failed: ${error.message}`)
  }

  return data
}

Output Format

Provide:

1. Complete PL/pgSQL function with transaction logic
2. Client-side RPC call implementation
3. Error handling code
4. Usage examples