Data Fetching & Caching

4.1 useFetchTableData

Location: src/components/tables/common/useFetchTableData.tsx

Purpose: Custom hook for fetching table data with caching, filtering, sorting, and pagination.

Signature:

function useFetchTableData({
  config,
  campaignId,
  tableState,
  dependencies = []
}: UseTableDataOptions)

Parameters:

• config - Table configuration object
  • tableName - Supabase table name
  • selectQuery - Custom select query string
  • dataProcessor - Optional function to process fetched data
  • defaultSorting - Default sorting state
• campaignId - Optional campaign ID for filtering
• tableState - Initial table state (filters, sorting, pagination)
• dependencies - Additional dependencies for refetch

Returns:

{
  // Data
  data: any[];
  isLoading: boolean;
  isError: boolean;
  count: number;
  refetch: () => Promise<void>;
  
  // State
  columnFilters: ColumnFiltersState;
  sorting: SortingState;
  pagination: PaginationState;
  advancedFilters: FilterItem[];
  dateFilters: DateFilterItem;
  
  // Setters
  setColumnFilters: (filters: ColumnFiltersState) => void;
  setSorting: (sorting: SortingState) => void;
  setPagination: (pagination: PaginationState) => void;
  setAdvancedFilters: (filters: FilterItem[]) => void;
  setDateFilters: (filters: DateFilterItem) => void;
}

Caching Logic:

1. Cache Key Generation:

   • Uses generateCacheKey() with all filter/sort/pagination state
   • Memoized values prevent unnecessary key changes

2. Cache Check:

   • Checks if cache exists for current key
   • Returns cached data if available and force !== true
   • Skips API call if cache hit

3. Data Fetching:

   • Builds Supabase query with all filters
   • Applies column filters, date filters, advanced filters
   • Applies sorting and pagination
   • Executes query with count

4. Data Processing:

   • Applies dataProcessor if provided
   • Stores processed data in cache
   • Updates state with processed data

Memoization:

• All filter/sort/pagination states are memoized
• Prevents unnecessary re-renders and refetches
• Dependencies array allows external triggers

Example:

const {
  data,
  isLoading,
  refetch,
  setColumnFilters
} = useFetchTableData({
  config: {
    tableName: "signatures",
    selectQuery: "*, circulator(*), petition(*)",
    dataProcessor: (data) => data.map(transformSignature)
  },
  campaignId: "campaign-123",
  tableState: initialTableState
});

---

4.2 generateCacheKey

Location: src/components/tables/common/generateCacheKey.ts

Purpose: Generates a unique cache key from table state for caching fetched data.

Signature:

function generateCacheKey(
  columnFilters?: ColumnFiltersState,
  advancedFilters?: FilterItem[],
  sorting?: SortingState,
  pagination?: PaginationState,
  dateFilters?: DateFilterItem
): string

Parameters:

• All optional table state parameters

Returns: JSON string representing sorted state

Logic:

1. Sort Filters:

   • Column filters sorted by id (alphabetically)
   • Advanced filters sorted by column (alphabetically)
   • Sorting sorted by id (alphabetically)

2. Remove IDs:

   • Advanced filters have id field removed (temporary UI state)
   • Only includes filter criteria, not UI identifiers

3. Stringify:

   • Converts sorted state to JSON string
   • Ensures consistent key generation

Example:

generateCacheKey(
  [{ id: "name", value: "John" }],
  [{ column: "status", operator: "eq", value: "active" }],
  [{ id: "created_at", desc: true }],
  { pageIndex: 0, pageSize: 20 }
);
// Returns: '{"filters":[...],"advancedFilters":[...],...}'

Notes:

• Same state always produces same key
• Order-independent (due to sorting)
• ID-independent (removes temporary IDs)

---

4.3 parseTableStateFromServerSearchParams

Location: src/components/tables/common/parseTableStateFromURL.ts

Purpose: Parses table state from URL search parameters (server-side).

Signature:

function parseTableStateFromServerSearchParams(
  searchParams: { [key: string]: string | string[] | undefined }
): TableState

Parameters:

• searchParams - Next.js search params object

Returns: TableState object with defaults

Logic:

1. Extract Parameter:

   • Gets tableState from search params
   • Returns defaults if missing or array

2. Decode:

   • URL decodes the parameter value
   • Handles encoded JSON strings

3. Parse:

   • Parses JSON string to object
   • Validates structure

4. Apply Defaults:

   • Merges with default values
   • Ensures all required fields exist

Default State:

{
  advancedFilters: [],
  columnFilters: [],
  pagination: { pageIndex: 0, pageSize: 20 },
  sorting: [],
  dateFilters: { columnId: "", from: "", to: "" }
}

Error Handling:

• Returns defaults on parse error
• Logs error to console
• Never throws (graceful degradation)

Usage:

// In page component
const searchParams = await props.searchParams;
const tableState = parseTableStateFromServerSearchParams(searchParams);

---

4.4 get_unique_values (PostgreSQL function)

Purpose: Server-side helper to return unique, non-empty values from any table column for use in filters or dropdowns.

Function (create in Postgres / Supabase):

CREATE OR REPLACE FUNCTION public.get_unique_values(
  table_name text,
  column_name text
) RETURNS text[] LANGUAGE plpgsql AS $$
DECLARE
  result text[];
  q text;
BEGIN
  q := format(
    'SELECT array_agg(distinct %I ORDER BY %I) FROM (SELECT %I FROM %I WHERE %I IS NOT NULL AND trim(%I) <> '''') t',
    column_name, column_name, column_name, table_name, column_name, column_name
  );
  EXECUTE q INTO result;
  RETURN COALESCE(result, ARRAY[]::text[]);
END;
$$;

Key points:

• Dynamic: accepts any table and column names.
• Skips NULL and empty (after trim) values.
• Returns a sorted array of unique values.
• Uses format(...%I...) to quote identifiers safely.

Usage in Supabase SQL editor:

-- Get unique counties
SELECT get_unique_values('signatures', 'county');

-- Get unique cities
SELECT get_unique_values('signatures', 'city');

Call from JavaScript (Supabase client):

// Without filter
const { data } = await supabase.rpc('get_unique_values', {
  table_name: 'signatures',
  column_name: 'county'
});

// With filter
const { data } = await supabase.rpc('get_unique_values', {
  table_name: 'signatures',
  column_name: 'city',
  filter_column: 'county',
  filter_value: 'Los Angeles'
});// array of strings

Notes:

• The RPC returns an array (text[]). Convert or map as needed in the client.
• Use this function to populate filter dropdowns or autocomplete lists without extra client-side deduplication.