[P2] Performance: Add pagination to list queries

[Pamacea]

Priority

P2 - Medium Priority

Category

Performance

Description

List queries don't have pagination, causing performance issues as datasets grow. All list endpoints should support pagination.

Affected Queries

Characters

// Current - Fetches ALL characters
export async function getCharactersByWorld(worldId: string) {
  return await prisma.character.findMany({
    where: { gameWorldId: worldId },
  });
}

Lore Entries

// Current - No pagination
export async function getLoreEntriesByWorld(gameWorldId: string) {
  return await prisma.loreEntry.findMany({
    where: { gameWorldId },
  });
}

Implementation

1. Add Pagination Parameters

// lib/pagination.ts
export interface PaginationParams {
  page?: number;
  pageSize?: number;
  cursor?: string;
}

export interface PaginatedResult<T> {
  items: T[];
  pagination: {
    page: number;
    pageSize: number;
    totalCount: number;
    totalPages: number;
    hasNext: boolean;
    hasPrevious: boolean;
  };
}

2. Update List Queries

// Offset-based pagination
export async function getCharactersByWorld(
  worldId: string,
  pagination: PaginationParams = {}
): Promise<PaginatedResult<Character>> {
  const page = pagination.page || 1;
  const pageSize = Math.min(pagination.pageSize || 50, 100);
  const skip = (page - 1) * pageSize;

  const [items, totalCount] = await Promise.all([
    prisma.character.findMany({
      where: { gameWorldId: worldId },
      skip,
      take: pageSize,
      orderBy: { order: 'asc' },
    }),
    prisma.character.count({
      where: { gameWorldId: worldId },
    }),
  ]);

  return {
    items,
    pagination: {
      page,
      pageSize,
      totalCount,
      totalPages: Math.ceil(totalCount / pageSize),
      hasNext: page * pageSize < totalCount,
      hasPrevious: page > 1,
    },
  };
}

3. Client-Side Integration

// Hooks for paginated queries
export function useCharacters(worldId: string, page: number) {
  return useQuery({
    queryKey: ['characters', worldId, page],
    queryFn: () => getCharactersByWorld(worldId, { page }),
  });
}

Success Criteria

• All list queries support pagination
• Default page size: 50
• Maximum page size: 100
• Pagination metadata returned to client
• UI implements pagination controls

Co-Authored-By: Claude Opus 4.6 noreply@anthropic.com