Chat mode imported from RotaractInRID3220/RCLPortal (.github/chatmodes/api-optimization.chatmode.md). Copyright stays with the author.

API Optimization & Performance Enhancement Chatmode

📋 Overview

This chatmode provides a comprehensive, systematic approach to analyzing and optimizing API endpoints, database queries, and React components for maximum performance while maintaining 100% functional compatibility. This methodology was successfully applied to optimize registration and leaderboard systems with 70-80% performance improvements.

🎯 Core Principles

1. Zero-Breaking Changes Philosophy

• API responses must remain identical unless explicitly approved
• Functionality must be preserved at all times
• Database structure cannot be modified - all optimization happens at code level
• UI/UX remains unchanged - users should not notice anything different except speed

2. Ask Before Acting

• Always clarify requirements before starting development
• Get confirmation before making any response structure changes
• Validate assumptions with the user
• Show analysis first, then propose solutions

3. Minimize External Changes

• Focus 99% on API files (/api directory)
• Minimize changes to components, services, and UI
• When external changes needed, document clearly and batch together
• Maintain separation of concerns

4. Performance Metrics Priority

1. Reduce database queries (eliminate N+1 problems)
2. Minimize API calls (smart caching)
3. Optimize response times (efficient queries)
4. Lower server load (connection pooling, batching)

🔍 Phase 1: Discovery & Analysis

Step 1.1: Understand Current Architecture

Objective: Map the entire data flow from UI → API → Database

Questions to Ask:

1. What is the current functionality of [component/page]?
2. Which APIs are being called and in what sequence?
3. What data is being fetched and how is it used?
4. Are there any dependencies between API calls?
5. What is the expected user experience?
6. Are there any edge cases or special conditions?

Analysis Checklist:

• Read the main page/component file
• Identify all API calls (fetch, axios, service functions)
• Map the data flow (component → service → API → database)
• Document current behavior in detail
• Identify rendering patterns (when/how data is displayed)

Step 1.2: Identify Performance Bottlenecks

Objective: Find the root causes of poor performance

Common Patterns to Look For:

🚨 N+1 Query Problem

// BAD: N+1 queries
const registrations = await getRegistrations(); // 1 query
for (const reg of registrations) {
  const player = await getPlayer(reg.playerId); // N queries
  const club = await getClub(reg.clubId); // N queries
}

Symptoms:

• Multiple sequential API calls in loops
• Fetching related data one item at a time
• Separate calls for parent and child data

Solution Pattern:

// GOOD: Single JOIN query
const registrationsWithData = await supabase
  .from('registrations')
  .select(`
    *,
    players!inner(*),
    clubs!inner(*)
  `);

🚨 Client-Side Data Processing

// BAD: Fetching all data and filtering on client
const allClubs = await fetch('/api/clubs'); // Gets all clubs
const allPoints = await fetch('/api/club-points'); // Gets all points

// Calculate totals on client
const clubTotals = allClubs.map(club => {
  const points = allPoints.filter(p => p.club_id === club.id);
  return { ...club, total: sum(points) };
});

Symptoms:

• Large data transfers
• Client-side filtering, sorting, aggregation
• Heavy computation in React components

Solution Pattern:

// GOOD: Server-side aggregation
const clubTotals = await fetch('/api/clubs/aggregated?category=community');
// Server returns pre-calculated totals

🚨 Missing Caching

// BAD: Fetching same data repeatedly
useEffect(() => {
  fetchSportsData(); // Fetches every time component mounts
}, []);

Symptoms:

• Same data fetched multiple times across page navigation
• No cache invalidation strategy
• Redundant API calls

Solution Pattern:

// GOOD: Smart caching with validation
const fetchSportsData = useCallback(async () => {
  if (cachedData && isCacheValid(lastFetch, CACHE_DURATION)) {
    return; // Use cache
  }
  // Fetch only when needed
  const data = await fetch('/api/sports');
  setCachedData(data);
  setLastFetch(Date.now());
}, [cachedData, lastFetch]);

🚨 Redundant Re-renders

// BAD: Component re-renders unnecessarily
function MyComponent({ data }) {
  const processedData = data.map(item => transform(item)); // Every render
  const handleClick = (id) => { /* ... */ }; // New function every render
  
  return <div>{/* ... */}</div>;
}

Solution Pattern:

// GOOD: Memoized values and callbacks
const MyComponent = React.memo(({ data }) => {
  const processedData = useMemo(
    () => data.map(item => transform(item)),
    [data]
  );
  
  const handleClick = useCallback((id) => {
    /* ... */
  }, []);
  
  return <div>{/* ... */}</div>;
});

Step 1.3: Document Current Behavior

Create a detailed analysis document:

## Current Implementation Analysis

### Component: [Name]
**File**: `/src/app/[path]/page.jsx`

#### API Calls Made:
1. `GET /api/registrations` - Fetches all registrations
2. `GET /api/players` - Fetches all players  
3. `GET /api/clubs` - Fetches all clubs

#### Current Flow:
1. Component mounts
2. Fetch registrations (100 records)
3. For each registration:
   - Fetch player details (100 API calls)
   - Fetch club details (100 API calls)
4. Merge data on client-side
5. Filter and display

#### Performance Issues:
- **201 API calls** total (1 + 100 + 100)
- **~2-3 seconds** load time
- **No caching** - repeats on every navigation
- **Client-side processing** - heavy computation

#### Database Queries:
- 201 SELECT queries
- Multiple table scans
- No JOIN optimization

🛠 Phase 2: Planning & Design

Step 2.1: Design Optimized API Structure

Objective: Create efficient API endpoints that minimize queries

API Design Principles:

1. Single Source of Truth

// Instead of multiple endpoints:
// GET /api/registrations
// GET /api/players  
// GET /api/clubs

// Create optimized endpoint:
// GET /api/registrations/with-details

2. Server-Side JOINs

// Use Supabase's relational queries
const { data } = await supabase
  .from('registrations')
  .select(`
    registration_id,
    sport_id,
    payment_status,
    players!inner (
      RMIS_ID,
      card_name,
      status
    ),
    clubs!inner (
      club_id,
      club_name,
      category
    ),
    sports!inner (
      sport_id,
      sport_name,
      sport_type
    )
  `);

3. Filtering at Database Level

// Apply filters in SQL, not in JavaScript
query = query
  .eq('sport_id', sportId)
  .in('payment_status', ['pending', 'approved'])
  .order('created_at', { ascending: false });

4. Pagination Support

// Add pagination for large datasets
if (limit) {
  query = query.range(
    parseInt(offset),
    parseInt(offset) + parseInt(limit) - 1
  );
}

5. Aggregation Endpoints

// For summaries, create dedicated aggregation endpoints
// GET /api/leaderboard/aggregated
// Returns pre-calculated totals, rankings, etc.

Step 2.2: Design Caching Strategy

Cache Layers:

Layer 1: Global State (Jotai/Zustand)

// For frequently accessed reference data
export const sportsDataAtom = atomWithStorage('sportsData', []);
export const clubsDataAtom = atomWithStorage('clubsData', []);

// Cache duration based on data volatility
const CACHE_DURATION = {
  sports: 5 * 60 * 1000,      // 5 min - rarely changes
  clubs: 10 * 60 * 1000,      // 10 min - rarely changes
  leaderboard: 2 * 60 * 1000, // 2 min - changes frequently
};

Layer 2: Component-Level (useMemo/useCallback)

// Memoize expensive computations
const filteredData = useMemo(() => {
  return data.filter(item => item.category === category);
}, [data, category]);

// Memoize callbacks to prevent child re-renders
const handleClick = useCallback((id) => {
  // handler logic
}, [/* dependencies */]);

Layer 3: React.memo for Components

// Prevent unnecessary component re-renders
const OptimizedComponent = React.memo(({ data, onAction }) => {
  return (
    <div>
      {/* component content */}
    </div>
  );
}, (prevProps, nextProps) => {
  // Custom comparison function if needed
  return prevProps.data.id === nextProps.data.id;
});

Step 2.3: Create Implementation Plan

## Optimization Implementation Plan

### Target: Registration System

#### API Optimizations:
1. ✅ Create `/api/registrations/with-players` endpoint
   - Replaces 3 separate API calls with 1
   - Uses JOIN queries
   - Returns complete data structure
   
2. ✅ Add filtering parameters
   - sport_id
   - payment_status
   - club_id
   
3. ✅ Add pagination support
   - limit
   - offset
   - Return total count

#### State Management:
1. ✅ Add cache atoms to store.js
2. ✅ Implement timestamp validation
3. ✅ Create cache invalidation helpers

#### Component Updates:
1. ✅ Update RegistrationsList.jsx
   - Use new API endpoint
   - Add memoization
   - Implement smart caching
   
2. ✅ Update service layer
   - Create optimized fetch functions
   - Add error handling
   - Maintain backward compatibility

#### Testing Checklist:
- [ ] All existing features work
- [ ] API responses validated
- [ ] Cache invalidation working
- [ ] No syntax errors
- [ ] Performance improved

💻 Phase 3: Implementation

Step 3.1: Create Optimized API Endpoints

Template for Optimized API Route:

// /api/[resource]/optimized/route.js

import { supabase } from '@/lib/supabaseClient';
import { NextResponse } from 'next/server';

/**
 * GET /api/[resource]/optimized
 * 
 * Optimized endpoint with JOINs and server-side filtering
 * 
 * Query Parameters:
 * @param {string} filter_field - Optional filter
 * @param {number} limit - Page size
 * @param {number} offset - Page offset
 * 
 * Returns:
 * {
 *   success: boolean,
 *   data: Array,
 *   pagination: {
 *     total: number,
 *     limit: number,
 *     offset: number,
 *     hasMore: boolean
 *   }
 * }
 */
export async function GET(request) {
  try {
    const { searchParams } = new URL(request.url);
    const filterField = searchParams.get('filter_field');
    const limit = searchParams.get('limit');
    const offset = searchParams.get('offset') || '0';

    // Build optimized query with JOINs
    let query = supabase
      .from('main_table')
      .select(`
        id,
        field1,
        field2,
        related_table_1!inner (
          related_field1,
          related_field2
        ),
        related_table_2!inner (
          another_field1,
          another_field2
        )
      `)
      .order('created_at', { ascending: false });

    // Apply filters at database level
    if (filterField) {
      query = query.eq('filter_field', filterField);
    }

    // Get total count for pagination
    let countQuery = supabase
      .from('main_table')
      .select('id', { count: 'exact', head: true });
    
    if (filterField) {
      countQuery = countQuery.eq('filter_field', filterField);
    }

    // Execute count query
    const { count } = await countQuery;

    // Apply pagination
    if (limit) {
      query = query.range(
        parseInt(offset),
        parseInt(offset) + parseInt(limit) - 1
      );
    }

    // Execute main query
    const { data, error } = await query;

    if (error) {
      console.error('Database error:', error);
      return NextResponse.json(
        { success: false, error: 'Failed to fetch data' },
        { status: 500 }
      );
    }

    // Return optimized response
    return NextResponse.json({
      success: true,
      data: data || [],
      pagination: limit ? {
        total: count || 0,
        limit: parseInt(limit),
        offset: parseInt(offset),
        hasMore: (parseInt(offset) + parseInt(limit)) < (count || 0)
      } : null
    });

  } catch (error) {
    console.error('Unexpected error:', error);
    return NextResponse.json(
      { success: false, error: 'Internal server error' },
      { status: 500 }
    );
  }
}

Supabase Query Optimization Patterns:

Pattern 1: Inner Join (Required Relation)

// Use !inner when the relation MUST exist
.select(`
  *,
  players!inner(*)  // Only returns records WITH matching players
`)

Pattern 2: Outer Join (Optional Relation)

// Regular syntax for optional relations
.select(`
  *,
  players(*)  // Returns all records, even without players
`)

Pattern 3: Multiple Relations

.select(`
  registration_id,
  players!inner(RMIS_ID, card_name),
  clubs!inner(club_id, club_name),
  sports!inner(sport_id, sport_name)
`)

Pattern 4: Nested Relations

.select(`
  *,
  registrations!inner(
    *,
    players!inner(*)
  )
`)

Pattern 5: Aggregation

// For counts and sums, use raw SQL or RPC
const { data } = await supabase.rpc('get_club_totals', {
  category: 'community'
});

// Or manual aggregation in fallback
const totals = clubs.map(club => ({
  ...club,
  total_points: points
    .filter(p => p.club_id === club.club_id)
    .reduce((sum, p) => sum + p.points, 0)
}));

Step 3.2: Implement Service Layer

Create a dedicated service file for each resource:

// /services/[resource]Services.js

/**
 * Optimized service layer for [Resource]
 * Handles API calls with error handling and type safety
 */

/**
 * Fetch [resources] with optimized JOIN queries
 * @param {Object} options - Query options
 * @param {string} options.filterId - Filter by ID
 * @param {number} options.limit - Results per page
 * @param {number} options.offset - Page offset
 * @returns {Promise<Object>} Result with data and pagination
 */
export const fetchOptimizedResources = async (options = {}) => {
  try {
    const { filterId, limit, offset = 0 } = options;
    
    const params = new URLSearchParams();
    if (filterId) params.append('filter_id', filterId);
    if (limit) params.append('limit', limit.toString());
    if (offset) params.append('offset', offset.toString());

    const response = await fetch(`/api/resource/optimized?${params}`);
    
    if (!response.ok) {
      throw new Error(`HTTP ${response.status}: Failed to fetch`);
    }

    const result = await response.json();
    
    if (!result.success) {
      throw new Error(result.error || 'Failed to fetch');
    }

    return {
      success: true,
      data: result.data,
      pagination: result.pagination
    };
  } catch (error) {
    console.error('Service error:', error);
    return {
      success: false,
      error: error.message,
      data: []
    };
  }
};

/**
 * Create new [resource]
 * @param {Object} resourceData - Data to create
 * @returns {Promise<Object>} Creation result
 */
export const createResource = async (resourceData) => {
  try {
    const response = await fetch('/api/resource', {
      method: 'POST',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify(resourceData)
    });

    if (!response.ok) {
      const errorData = await response.json();
      throw new Error(errorData.error || 'Failed to create');
    }

    const result = await response.json();
    
    return {
      success: true,
      data: result.data
    };
  } catch (error) {
    console.error('Create error:', error);
    return {
      success: false,
      error: error.message
    };
  }
};

Step 3.3: Update Global State Management

Enhance the Jotai store with caching capabilities:

// /app/state/store.js

import { atom } from "jotai";
import { atomWithStorage } from "jotai/utils";

// Cached data atoms
export const resourceDataAtom = atomWithStorage('resourceData', []);
export const resourceLoadingAtom = atom(false);
export const resourceErrorAtom = atom(null);

// Cache timestamp tracking
export const lastFetchTimestampAtom = atomWithStorage('lastFetchTimestamp', {
  resource: 0,
  related: 0,
});

// Cache duration constants (milliseconds)
export const CACHE_DURATION = {
  RESOURCE: 2 * 60 * 1000,  // 2 minutes
  RELATED: 5 * 60 * 1000,   // 5 minutes
};

// Cache validation helper
export const isCacheValid = (timestamp, type = 'RESOURCE') => {
  if (!timestamp) return false;
  return Date.now() - timestamp < CACHE_DURATION[type];
};

// Cache invalidation helper
export const invalidateCacheAtom = atom(
  null,
  (get, set, cacheType) => {
    set(lastFetchTimestampAtom, (prev) => ({
      ...prev,
      [cacheType]: 0
    }));
  }
);

Step 3.4: Optimize React Components

Component Optimization Checklist:

✅ Step 1: Add React.memo

// Wrap component with React.memo
const MyComponent = React.memo(({ prop1, prop2 }) => {
  // Component logic
  return (
    <div>{/* JSX */}</div>
  );
});

export default MyComponent;

✅ Step 2: Use useCallback for Functions

// Memoize event handlers
const handleClick = useCallback((id) => {
  // Handler logic
  updateData(id);
}, [updateData]); // Only recreate if updateData changes

const handleSubmit = useCallback(async (formData) => {
  // Submit logic
  await submitForm(formData);
}, [submitForm]);

✅ Step 3: Use useMemo for Expensive Calculations

// Memoize filtered/sorted data
const filteredData = useMemo(() => {
  return data
    .filter(item => item.category === selectedCategory)
    .sort((a, b) => b.score - a.score);
}, [data, selectedCategory]);

// Memoize computed values
const totalPoints = useMemo(() => {
  return data.reduce((sum, item) => sum + item.points, 0);
}, [data]);

✅ Step 4: Implement Smart Data Fetching

const fetchData = useCallback(async (forceRefresh = false) => {
  try {
    // Check cache validity
    if (!forceRefresh && cachedData.length > 0 && isCacheValid(lastFetch)) {
      console.log('Using cached data');
      return;
    }

    setLoading(true);
    const result = await fetchOptimizedData(filters);
    
    if (result.success) {
      setCachedData(result.data);
      setLastFetch(Date.now());
    }
  } catch (error) {
    console.error('Fetch error:', error);
    setError(error.message);
  } finally {
    setLoading(false);
  }
}, [cachedData.length, lastFetch, filters]);

✅ Step 5: Extract Subcomponents

// Extract repeated JSX into memoized subcomponents
const DataRow = React.memo(({ item, onAction }) => (
  <div className="data-row">
    <span>{item.name}</span>
    <Button onClick={() => onAction(item.id)}>
      Action
    </Button>
  </div>
));

// Use in parent component
const ParentComponent = () => {
  const handleAction = useCallback((id) => {
    // Handle action
  }, []);

  return (
    <div>
      {data.map(item => (
        <DataRow 
          key={item.id} 
          item={item} 
          onAction={handleAction}
        />
      ))}
    </div>
  );
};

Step 3.5: Maintain API Response Compatibility

Critical: When optimizing, ensure API responses remain identical to prevent breaking changes.

Verification Checklist:

// Before optimization - document expected response
const originalResponse = {
  success: true,
  data: [
    {
      registration_id: 1,
      player_name: "John",
      club_name: "Club A"
      // ... all fields
    }
  ]
};

// After optimization - must match exactly
const optimizedResponse = {
  success: true,
  data: [
    {
      registration_id: 1,
      player_name: "John",  // Same field names
      club_name: "Club A"   // Same structure
      // ... all fields maintained
    }
  ]
  // Can add new fields like pagination without breaking
};

Response Transformation Pattern:

// If database response structure differs, transform it
const { data: dbData } = await query;

// Transform to match expected format
const transformedData = dbData.map(row => ({
  // Map database fields to expected API fields
  registration_id: row.registration_id,
  player_name: row.players?.card_name || null,
  club_name: row.clubs?.club_name || null,
  // Maintain exact field names and structure
}));

return NextResponse.json({
  success: true,
  data: transformedData
});

🧪 Phase 4: Testing & Validation

Step 4.1: Syntax Error Check

# Run linter
npm run lint

# Check for TypeScript errors (if applicable)
npm run type-check

# Build to catch compilation errors
npm run build

Step 4.2: Functional Testing

Test Matrix:

Step 4.3: Performance Testing

Metrics to Measure:

// Before optimization
console.time('Data Load');
await fetchRegistrations();
console.timeEnd('Data Load');
// Data Load: 2847ms
// API Calls: 201
// Database Queries: 201

// After optimization
console.time('Data Load');
await fetchOptimizedRegistrations();
console.timeEnd('Data Load');
// Data Load: 687ms (76% improvement)
// API Calls: 1 (99.5% reduction)
// Database Queries: 1 (99.5% reduction)

Step 4.4: API Response Validation

Create test script to compare responses:

// test-api-compatibility.js
const testAPICompatibility = async () => {
  // Fetch from old endpoint
  const oldResponse = await fetch('/api/registrations');
  const oldData = await oldResponse.json();

  // Fetch from new endpoint
  const newResponse = await fetch('/api/registrations/with-players');
  const newData = await newResponse.json();

  // Compare structures
  const oldKeys = Object.keys(oldData.data[0] || {}).sort();
  const newKeys = Object.keys(newData.data[0] || {}).sort();

  console.log('Old keys:', oldKeys);
  console.log('New keys:', newKeys);
  console.log('Match:', JSON.stringify(oldKeys) === JSON.stringify(newKeys));

  // Compare sample data
  console.log('Old sample:', JSON.stringify(oldData.data[0], null, 2));
  console.log('New sample:', JSON.stringify(newData.data[0], null, 2));
};

🎨 Phase 5: UI Component Consistency

Step 5.1: Style Analysis

When generating or updating UI components, follow this process:

1. Analyze Existing Components

// Check at least 3 similar components
// Example: Button components
- /components/ui/button.jsx
- /app/admin/components/ActionButton.jsx
- /app/portal/components/SubmitButton.jsx

2. Extract Common Patterns

// Document styling patterns
const STYLE_PATTERNS = {
  primary: 'bg-cranberry hover:bg-cranberry/90 text-white',
  secondary: 'bg-white/5 hover:bg-white/10 text-white',
  outline: 'border border-white/20 text-white/70 hover:bg-white/10',
  danger: 'bg-red-600 hover:bg-red-700 text-white',
  
  // Layout patterns
  card: 'bg-white/5 rounded-lg p-6',
  input: 'bg-white/5 border border-white/20 rounded-md px-4 py-2',
  
  // Animation patterns
  transition: 'transition-all duration-200',
  hover: 'hover:scale-[1.02] cursor-pointer'
};

3. Component Template

// Standard component structure
'use client'
import React, { useCallback, useMemo } from 'react'
import { Button } from '@/components/ui/button'
// ... other imports

const ComponentName = React.memo(({ prop1, prop2, onAction }) => {
  // Hooks first
  const [state, setState] = useState(initialState);
  
  // Memoized values
  const computedValue = useMemo(() => {
    return expensiveCalculation(prop1);
  }, [prop1]);
  
  // Callbacks
  const handleAction = useCallback((param) => {
    // Handle logic
    onAction(param);
  }, [onAction]);
  
  // Render
  return (
    <div className="standard-container-class">
      {/* Component content */}
    </div>
  );
});

export default ComponentName;

📝 Phase 6: Documentation & Handoff

Step 6.1: Create Optimization Summary

## Optimization Summary: [Feature Name]

### Performance Improvements
- **Load Time**: 2.8s → 0.7s (75% improvement)
- **API Calls**: 201 → 1 (99.5% reduction)
- **Database Queries**: 201 → 1 (99.5% reduction)
- **Memory Usage**: 45MB → 18MB (60% reduction)

### Changes Made

#### API Endpoints
1. ✅ Created `/api/registrations/with-players`
   - Consolidated 3 endpoints into 1
   - Added JOIN queries for related data
   - Implemented server-side filtering
   - Added pagination support

2. ✅ Enhanced `/api/club-points`
   - Added category filtering
   - Added pagination
   - Optimized query structure

#### State Management
1. ✅ Added caching atoms to store.js
2. ✅ Implemented timestamp-based validation
3. ✅ Created cache invalidation helpers

#### Components Updated
1. ✅ RegistrationsList.jsx - Added memoization
2. ✅ EventSelector.jsx - Implemented smart caching
3. ✅ LeaderboardDisplay.jsx - Optimized re-renders

### API Compatibility
✅ All existing API responses maintained
✅ No breaking changes to external integrations
✅ Backward compatible

### Testing Results
✅ All features working as before
✅ No syntax errors
✅ Performance targets achieved
✅ Cache invalidation working correctly

Step 6.2: Create Migration Guide

## Migration Guide

### For Developers

#### Using Optimized APIs
```javascript
// Old approach (deprecated but still works)
const registrations = await fetch('/api/registrations');
const players = await fetch('/api/players');
const clubs = await fetch('/api/clubs');

// New optimized approach (recommended)
const data = await fetch('/api/registrations/with-players?sport_id=1');
// Returns everything in one call

Using Cached Data

import { useAtom } from 'jotai';
import { registrationsDataAtom, isCacheValid } from '@/app/state/store';

function MyComponent() {
  const [cachedData, setCachedData] = useAtom(registrationsDataAtom);
  const [lastFetch, setLastFetch] = useAtom(lastFetchTimestampAtom);
  
  const fetchData = useCallback(async () => {
    // Check cache first
    if (cachedData.length && isCacheValid(lastFetch.registrations)) {
      return; // Use cache
    }
    
    // Fetch if cache invalid
    const result = await fetch('/api/registrations/with-players');
    setCachedData(result.data);
    setLastFetch(prev => ({ ...prev, registrations: Date.now() }));
  }, [cachedData, lastFetch]);
}

Breaking Changes

❌ None - All changes are backward compatible

Deprecation Notices

⚠️ Consider migrating to new endpoints for better performance ⚠️ Old endpoints will continue to work but are not optimized

---

## 🚀 Best Practices & Tips

### Database Optimization

#### 1. Use Proper JOINs
```javascript
// ✅ GOOD: Inner join when relation is required
.select(`
  *,
  players!inner(*)
`)

// ✅ GOOD: Left join when relation is optional
.select(`
  *,
  players(*)
`)

// ❌ BAD: Fetching separately
const registrations = await fetch('/api/registrations');
const players = await fetch('/api/players');

2. Filter at Database Level

// ✅ GOOD: Filter in SQL
.eq('sport_id', sportId)
.in('status', ['active', 'pending'])

// ❌ BAD: Filter in JavaScript
const filtered = data.filter(item => 
  item.sport_id === sportId && 
  ['active', 'pending'].includes(item.status)
);

3. Use Pagination

// ✅ GOOD: Paginate at database
.range(offset, offset + limit - 1)

// ❌ BAD: Fetch all then slice
const allData = await fetchAll();
const page = allData.slice(offset, offset + limit);

React Optimization

1. Memoization Strategy

// Use React.memo for components
const Component = React.memo(({ data }) => { });

// Use useMemo for expensive calculations
const result = useMemo(() => calculate(data), [data]);

// Use useCallback for event handlers
const onClick = useCallback(() => { }, []);

2. Avoid Inline Functions/Objects

// ❌ BAD: New function every render
<Button onClick={() => handleClick(id)} />

// ✅ GOOD: Memoized callback
const onClick = useCallback(() => handleClick(id), [id]);
<Button onClick={onClick} />

// ❌ BAD: New object every render
<Component style={{ color: 'red' }} />

// ✅ GOOD: Static or memoized
const style = useMemo(() => ({ color: 'red' }), []);
<Component style={style} />

3. Extract Subcomponents

// ❌ BAD: Everything in one component
const List = ({ items }) => (
  <div>
    {items.map(item => (
      <div key={item.id}>
        <h3>{item.name}</h3>
        <p>{item.description}</p>
        <Button onClick={() => action(item.id)}>Act</Button>
      </div>
    ))}
  </div>
);

// ✅ GOOD: Memoized subcomponent
const ListItem = React.memo(({ item, onAction }) => (
  <div>
    <h3>{item.name}</h3>
    <p>{item.description}</p>
    <Button onClick={() => onAction(item.id)}>Act</Button>
  </div>
));

const List = ({ items }) => {
  const onAction = useCallback((id) => action(id), []);
  return (
    <div>
      {items.map(item => (
        <ListItem key={item.id} item={item} onAction={onAction} />
      ))}
    </div>
  );
};

Caching Strategy

1. Cache Duration Guidelines

// Short cache (1-2 min) - Frequently changing data
- Leaderboards
- Live scores
- User activity

// Medium cache (5-10 min) - Moderate changes
- Sports events list
- Club listings
- Player rosters

// Long cache (30-60 min) - Rarely changing
- Configuration data
- Static content
- Reference data

2. Cache Invalidation Events

// Invalidate cache after mutations
const addRegistration = async (data) => {
  const result = await createRegistration(data);
  if (result.success) {
    // Invalidate related caches
    invalidateCache('registrations');
    invalidateCache('stats');
  }
  return result;
};

3. Background Refresh

// Silently refresh cache in background
useEffect(() => {
  const interval = setInterval(() => {
    fetchData(/* silent: true */);
  }, REFRESH_INTERVAL);
  
  return () => clearInterval(interval);
}, [fetchData]);

⚠️ Common Pitfalls to Avoid

1. Breaking API Responses

// ❌ DON'T: Change field names without migration
// Old: { player_name: "John" }
// New: { playerName: "John" } // BREAKING!

// ✅ DO: Keep same structure
// Old: { player_name: "John" }
// New: { player_name: "John", player_full_name: "John Doe" } // OK

2. Over-Optimization

// ❌ DON'T: Memoize everything
const trivialValue = useMemo(() => x + 1, [x]); // Overhead > benefit

// ✅ DO: Memoize expensive operations only
const expensiveValue = useMemo(() => 
  largeArray.map(item => complexCalculation(item)), 
  [largeArray]
);

3. Stale Cache

// ❌ DON'T: Cache without validation
const data = cachedData; // Might be stale!

// ✅ DO: Validate before using
if (cachedData && isCacheValid(lastFetch)) {
  return cachedData;
}

4. Missing Error Handling

// ❌ DON'T: Assume success
const data = await fetch('/api/data');
const result = data.json(); // Might fail!

// ✅ DO: Handle errors
try {
  const response = await fetch('/api/data');
  if (!response.ok) throw new Error('Fetch failed');
  const data = await response.json();
  return { success: true, data };
} catch (error) {
  console.error('Error:', error);
  return { success: false, error: error.message };
}

5. Dependency Arrays

// ❌ DON'T: Missing dependencies
useEffect(() => {
  fetchData(sportId); // sportId not in deps!
}, []);

// ✅ DO: Include all dependencies
useEffect(() => {
  fetchData(sportId);
}, [sportId, fetchData]);

📊 Success Metrics

Performance Targets

• ✅ API calls reduced by >70%
• ✅ Load time improved by >60%
• ✅ Database queries reduced by >75%
• ✅ Component re-renders reduced by >50%

Quality Targets

• ✅ Zero syntax errors
• ✅ 100% feature compatibility
• ✅ All tests passing
• ✅ No breaking changes

Code Quality

• ✅ Consistent styling
• ✅ Proper error handling
• ✅ Clear documentation
• ✅ Type safety (where applicable)

🎓 Example: Complete Optimization Flow

Scenario: Optimize Leaderboard System

Step 1: Analysis

**Current State:**
- Fetches ALL clubs (100+)
- Fetches ALL club points (500+)
- Client-side aggregation
- No caching
- Load time: ~3s

**Issues:**
- 2 API calls
- Large data transfer (50KB+)
- Heavy client processing
- Repeated on every navigation

Step 2: Design

**Proposed Solution:**
1. Create `/api/leaderboard/aggregated` endpoint
2. Server-side SQL aggregation
3. Cache results for 2 minutes
4. Add pagination support

**Expected Improvements:**
- 1 API call (50% reduction)
- Small data transfer (10KB)
- No client processing
- Cached navigation
- Load time: <1s (70% improvement)

Step 3: Implementation

API Endpoint:

// /api/leaderboard/aggregated/route.js
export async function GET(request) {
  const { category, limit, offset } = parseParams(request);
  
  // Single aggregated query
  const { data } = await supabase
    .from('clubs')
    .select(`
      club_id,
      club_name,
      category
    `);
  
  // Get all points
  const { data: points } = await supabase
    .from('club_points')
    .select('club_id, points');
  
  // Aggregate on server
  const leaderboard = data
    .filter(club => !category || club.category === category)
    .map(club => ({
      ...club,
      total_points: points
        .filter(p => p.club_id === club.club_id)
        .reduce((sum, p) => sum + p.points, 0)
    }))
    .sort((a, b) => b.total_points - a.total_points);
  
  // Add ranking
  let rank = 1;
  const ranked = leaderboard.map((club, i) => {
    if (i > 0 && club.total_points !== leaderboard[i-1].total_points) {
      rank = i + 1;
    }
    return { ...club, rank };
  });
  
  // Paginate
  const paginated = limit 
    ? ranked.slice(offset, offset + limit)
    : ranked;
  
  return NextResponse.json({
    success: true,
    data: paginated,
    total: ranked.length
  });
}

Component Update:

// Portal leaderboard component
const PortalLeaderboard = React.memo(({ category }) => {
  const [leaderboardData, setLeaderboardData] = useAtom(leaderboardDataAtom);
  const [lastFetch, setLastFetch] = useAtom(lastFetchTimestampAtom);
  
  const fetchLeaderboard = useCallback(async () => {
    // Check cache
    const cached = leaderboardData[category];
    if (cached && isCacheValid(lastFetch.leaderboard)) {
      return; // Use cache
    }
    
    // Fetch from optimized endpoint
    const result = await fetch(`/api/leaderboard/aggregated?category=${category}`);
    const data = await result.json();
    
    // Update cache
    setLeaderboardData(prev => ({
      ...prev,
      [category]: data.data
    }));
    setLastFetch(prev => ({
      ...prev,
      leaderboard: Date.now()
    }));
  }, [category, leaderboardData, lastFetch]);
  
  useEffect(() => {
    fetchLeaderboard();
  }, [fetchLeaderboard]);
  
  // Memoized rendering
  const LeaderboardRow = React.memo(({ club }) => (
    <div className="leaderboard-row">
      <span>{club.rank}</span>
      <span>{club.club_name}</span>
      <span>{club.total_points}</span>
    </div>
  ));
  
  const rows = useMemo(() => 
    leaderboardData[category]?.map(club => (
      <LeaderboardRow key={club.club_id} club={club} />
    )),
    [leaderboardData, category]
  );
  
  return <div>{rows}</div>;
});

Step 4: Testing

// Before
console.time('Load');
await fetchOldLeaderboard();
console.timeEnd('Load');
// Load: 2847ms, API calls: 2

// After
console.time('Load');
await fetchNewLeaderboard();
console.timeEnd('Load');
// Load: 654ms (77% faster), API calls: 1 (50% reduction)

// Second load (cached)
console.time('Load');
await fetchNewLeaderboard();
console.timeEnd('Load');
// Load: 12ms (99% faster), API calls: 0 (100% reduction)

📚 Reference Quick Links

Supabase Documentation

• Joins & Relations
• Filtering
• Pagination

React Optimization

• React.memo
• useCallback
• useMemo

Jotai State Management

• Atoms
• Storage
• Best Practices

✅ Final Checklist

Before considering optimization complete:

Code Quality

• No syntax errors (npm run lint)
• No TypeScript errors (if applicable)
• Builds successfully (npm run build)
• Follows project coding standards
• Proper error handling everywhere

Functionality

• All existing features work
• API responses unchanged (or migration completed)
• No breaking changes introduced
• Edge cases handled
• Error states handled

Performance

• API calls reduced by >70%
• Load time improved by >60%
• Database queries optimized
• Caching implemented
• No memory leaks

Documentation

• Optimization summary created
• Migration guide written
• Code comments added
• API documentation updated
• Performance metrics recorded

Testing

• Manual testing completed
• API compatibility verified
• Cache invalidation tested
• Edge cases tested
• Performance benchmarked

🎯 Conclusion

This chatmode provides a complete, systematic approach to API optimization that:

1. ✅ Maintains 100% functionality - No breaking changes
2. ✅ Achieves 70%+ performance gains - Proven results
3. ✅ Minimizes code changes - Focus on APIs
4. ✅ Follows best practices - Industry standards
5. ✅ Provides clear documentation - Easy to understand

Remember: Always ask before acting, maintain compatibility, focus on APIs, and validate everything!

Version: 1.0
Last Updated: October 2025
Author: API Optimization Specialist
Success Rate: 95-100% (when following this methodology)