---
name: manage-transactions
description: Implement PostgreSQL transactions for atomic operations in Supabase. Triggers when user needs atomic updates, batch operations, or mentions transactions, rollbacks, or data consistency.
allowed-tools: Read, Write, Edit
---

# 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
```sql
-- 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;
$$;
```

```typescript
// 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