Epic: REST API and UI Pagination for Large-Scale Multi-Tenant Deployments

[crivetimihai]

🔌 Epic: REST API and UI Pagination for Large-Scale Multi-Tenant Deployments

Goal

Enable efficient data retrieval and display for large-scale multi-tenant MCP Gateway deployments with hundreds of thousands of users, millions of resources, and tens of thousands of MCP servers. Implement comprehensive pagination across all REST API endpoints and Admin UI components to ensure the system can scale to:

• 160,000+ users (with ~500,000 teams)
• Millions of prompts and resources
• Tens of thousands of MCP servers (gateways)
• Millions of tools from federated gateways
• Hundreds of thousands of API tokens

This feature ensures the gateway remains performant and responsive under extreme scale by implementing cursor-based and offset-based pagination with configurable page sizes.

Why Now?

Current implementation loads all data at once, which causes:

1. Database performance degradation with large result sets
2. Memory exhaustion on gateway instances
3. Slow API response times (10+ seconds for large lists)
4. Poor UI experience with unresponsive pages
5. Network bandwidth waste transferring unnecessary data

By implementing pagination now, we:

1. Future-proof the platform for enterprise-scale deployments
2. Improve response times by 95%+ for large datasets
3. Reduce memory footprint by loading data incrementally
4. Enable better UX with fast, incremental data loading
5. Align with industry standards (REST API best practices)

---

📖 User Stories

US-1: API Developer - Paginated Tool Retrieval

As an API Developer
I want to retrieve tools in paginated batches
So that I can efficiently process thousands of tools without memory issues

Acceptance Criteria:

Given a deployment with 50,000 registered tools
When I call GET /admin/tools?page=1&per_page=100
Then the gateway should:
  - Return exactly 100 tools (or fewer on last page)
  - Include pagination metadata: total_count, page, per_page, total_pages
  - Include navigation links: next_page, prev_page, first_page, last_page
  - Execute the query in < 200ms
  - Use database query limits (LIMIT/OFFSET or cursor-based)
  - Support filtering with pagination (e.g., ?team_id=xyz&page=2)

Technical Requirements:

• Offset-based pagination for simple use cases
• Cursor-based pagination for large datasets to avoid performance degradation
• Configurable default page size via environment variable (default: 50)
• Maximum page size limit (default: 500)
• Consistent pagination schema across all endpoints

US-2: Frontend Developer - UI Table Pagination

As a Frontend Developer
I want paginated data displayed in the Admin UI tables
So that users can browse large datasets without browser freezing

Acceptance Criteria:

Given the Admin UI tools page with 10,000 tools
When I navigate to /admin#tools
Then the UI should:
  - Display pagination controls (Previous, 1, 2, 3, ..., Next)
  - Show current page, total pages, and total items
  - Load only the current page data via HTMX
  - Support page size selection (25, 50, 100, 200)
  - Preserve filters/search when changing pages
  - Update URL with page parameter (?page=3)
  - Show loading state during page transitions
  - Remember page size preference in localStorage

Technical Requirements:

• HTMX-based partial page updates
• Alpine.js for pagination controls
• Debounced search with pagination reset
• Accessible keyboard navigation
• Mobile-responsive pagination controls

US-3: Platform Admin - Configurable Pagination Settings

As a Platform Administrator
I want to configure pagination defaults globally
So that I can optimize performance for my specific deployment scale

Acceptance Criteria:

Given the .env configuration file
When I set pagination environment variables:
  - PAGINATION_DEFAULT_PAGE_SIZE=100
  - PAGINATION_MAX_PAGE_SIZE=1000
  - PAGINATION_CURSOR_THRESHOLD=10000
Then the gateway should:
  - Use 100 items per page by default
  - Reject requests for >1000 items per page
  - Switch to cursor-based pagination for result sets >10,000
  - Log pagination configuration at startup
  - Validate configuration values (positive integers)

Technical Requirements:

• Environment variable configuration in config.py
• Runtime validation of pagination settings
• Documentation in .env.example
• Startup logging of pagination config

US-4: Database Admin - Efficient Query Performance

As a Database Administrator
I want pagination queries to use proper indexes
So that the database can handle millions of records efficiently

Acceptance Criteria:

Given a database with 1M+ tools and proper indexes
When executing paginated queries
Then the database should:
  - Use indexed columns for sorting (created_at, id)
  - Execute offset queries in <50ms for first 10,000 records
  - Use cursor-based pagination for result sets >10,000
  - Avoid full table scans (verified by EXPLAIN ANALYZE)
  - Support composite indexes for filtered pagination

Technical Requirements:

• Database indexes on: (team_id, created_at), (created_at, id)
• Alembic migration for new indexes
• Query optimization for each paginated endpoint
• Cursor-based pagination uses keyset pagination (WHERE id > cursor)
• Support for PostgreSQL, SQLite, and MySQL

US-5: API Consumer - Standardized Pagination Format

As an API Consumer
I want consistent pagination format across all endpoints
So that I can write reusable client libraries

Acceptance Criteria:

Given any paginated API endpoint
When I request data with pagination parameters
Then the response should follow this schema:
{
  "data": [...],  // Array of items
  "pagination": {
    "page": 1,
    "per_page": 50,
    "total_items": 1250,
    "total_pages": 25,
    "has_next": true,
    "has_prev": false,
    "next_cursor": "eyJpZCI6MTAwfQ==",  // Optional for cursor-based
    "prev_cursor": null
  },
  "links": {
    "self": "/admin/tools?page=1&per_page=50",
    "first": "/admin/tools?page=1&per_page=50",
    "last": "/admin/tools?page=25&per_page=50",
    "next": "/admin/tools?page=2&per_page=50",
    "prev": null
  }
}

Technical Requirements:

• Pydantic schema for PaginatedResponse[T]
• Generic pagination wrapper
• Automatic link generation
• Cursor encoding/decoding utilities

---

📋 Detailed Requirements

1. API Endpoints Requiring Pagination

Core MCP Resources

Endpoint	Current Return Type	Expected Scale	Priority
GET /admin/tools	List[ToolRead]	1M+ tools	P0
GET /admin/resources	List[ResourceRead]	1M+ resources	P0
GET /admin/prompts	List[PromptRead]	1M+ prompts	P0
GET /admin/servers	List[ServerRead]	50K+ servers	P0
GET /admin/gateways	List[GatewayRead]	10K+ gateways	P0

User Management

Endpoint	Current Return Type	Expected Scale	Priority
GET /email-auth/admin/users	UserListResponse	160K+ users	P0
GET /email-auth/admin/events	list[AuthEventResponse]	10M+ events	P1
GET /sso/pending-approvals	List[PendingUserApprovalResponse]	10K+ pending	P1

Team Management

Endpoint	Current Return Type	Expected Scale	Priority
GET /teams	TeamListResponse	500K+ teams	P0
GET /teams/{team_id}/members	List[TeamMemberResponse]	100+ per team	P0
GET /teams/{team_id}/invitations	List[TeamInvitationResponse]	1K+ per team	P1
GET /teams/{team_id}/join-requests	List[TeamJoinRequestResponse]	1K+ per team	P1
GET /teams/discover	List[TeamDiscoveryResponse]	500K+ teams	P1

API Token Management

Endpoint	Current Return Type	Expected Scale	Priority
GET /api-tokens	TokenListResponse	500K+ tokens	P0
GET /api-tokens/admin/all	TokenListResponse	500K+ tokens	P0
GET /api-tokens/teams/{team_id}	TokenListResponse	10K+ per team	P1

RBAC & Permissions

Endpoint	Current Return Type	Expected Scale	Priority
GET /rbac/roles	List[RoleResponse]	10K+ roles	P1
GET /rbac/users/{user_email}/roles	List[UserRoleResponse]	100+ per user	P1
GET /rbac/permissions/user/{user_email}	List[str]	1K+ per user	P1
GET /rbac/my/roles	List[UserRoleResponse]	100+ per user	P1

SSO & Providers

Endpoint	Current Return Type	Expected Scale	Priority
GET /sso/providers	List[SSOProviderResponse]	100+ providers	P2
GET /sso/admin/providers	List[Dict]	100+ providers	P2

A2A Agents

Endpoint	Current Return Type	Expected Scale	Priority
GET /admin/a2a	HTML/JSON List	10K+ agents	P1

MCP Registry & Plugins

Endpoint	Current Return Type	Expected Scale	Priority
GET /admin/mcp-registry/servers	CatalogListResponse	1K+ servers	P1
GET /admin/plugins	PluginListResponse	500+ plugins	P2
GET /admin/tags	List[Dict[str, Any]]	10K+ tags	P1

Audit & Logs

Endpoint	Current Return Type	Expected Scale	Priority
GET /admin/logs	HTML	100M+ log lines	P1
GET /admin/import/status	Dict[str, Any]	10K+ imports	P2

2. Database Models Requiring Indexed Pagination

Core Tables:

• Tool (mcpgateway.db:1531) - Expected: 1M+ rows
• Resource (mcpgateway.db:1818) - Expected: 1M+ rows
• Prompt (mcpgateway.db:2048) - Expected: 1M+ rows
• Server (mcpgateway.db:2239) - Expected: 50K+ rows
• Gateway (mcpgateway.db:2409) - Expected: 10K+ rows
• A2AAgent (mcpgateway.db:2527) - Expected: 10K+ rows

User Management Tables:

• EmailUser (mcpgateway.db:418) - Expected: 160K+ rows
• EmailAuthEvent (mcpgateway.db:663) - Expected: 10M+ rows
• PendingUserApproval (mcpgateway.db:1240) - Expected: 10K+ rows

Team Management Tables:

• EmailTeam (mcpgateway.db:799) - Expected: 500K+ rows
• EmailTeamMember (mcpgateway.db:914) - Expected: 10M+ rows
• EmailTeamMemberHistory (mcpgateway.db:973) - Expected: 50M+ rows
• EmailTeamInvitation (mcpgateway.db:1047) - Expected: 1M+ rows
• EmailTeamJoinRequest (mcpgateway.db:1159) - Expected: 1M+ rows

Token & Security Tables:

• EmailApiToken (mcpgateway.db:2772) - Expected: 500K+ rows
• TokenUsageLog (mcpgateway.db:2916) - Expected: 100M+ rows
• TokenRevocation (mcpgateway.db:2981) - Expected: 50K+ rows
• OAuthToken (mcpgateway.db:2687) - Expected: 100K+ rows

RBAC Tables:

• Role (mcpgateway.db:218) - Expected: 10K+ rows
• UserRole (mcpgateway.db:260) - Expected: 1M+ rows
• PermissionAuditLog (mcpgateway.db:294) - Expected: 1B+ rows

SSO Tables:

• SSOProvider (mcpgateway.db:3015) - Expected: 100+ rows
• SSOAuthSession (mcpgateway.db:3091) - Expected: 1M+ rows

Metrics Tables:

• ToolMetric (mcpgateway.db:1397) - Expected: 100M+ rows
• ResourceMetric (mcpgateway.db:1425) - Expected: 10M+ rows
• ServerMetric (mcpgateway.db:1451) - Expected: 1M+ rows
• PromptMetric (mcpgateway.db:1477) - Expected: 10M+ rows
• A2AAgentMetric (mcpgateway.db:1503) - Expected: 10M+ rows

3. UI Components Requiring Pagination

Admin Dashboard (/admin)

• Tools table
• Resources table
• Prompts table
• Virtual servers table
• Gateway peers table
• A2A agents table
• Tags list
• Plugins list
• MCP Registry catalog

User Management UI

• Users list
• Auth events log
• Pending approvals queue

Team Management UI

• Teams list
• Team members list
• Team invitations list
• Join requests list
• Team discovery browse

Token Management UI

• API tokens list (user view)
• API tokens list (admin view)
• Token usage logs

Audit & Logs UI

• Application logs viewer
• Permission audit logs
• Import status history

4. Pagination Implementation Strategy

4.1 Offset-Based Pagination (Default)

Use for: Result sets <10,000 records, simple use cases

# Query pattern
query = db.query(Tool).filter(Tool.team_id == team_id)
total = query.count()
items = query.order_by(Tool.created_at.desc()).offset(offset).limit(limit).all()

Pros:

• Simple to implement
• Supports jumping to arbitrary pages
• Familiar to users (page 1, 2, 3...)

Cons:

• Performance degrades for large offsets (>10K)
• Inefficient for very large tables
• Inconsistent results during data mutations

4.2 Cursor-Based Pagination (Advanced)

Use for: Result sets >10,000 records, high-volume tables

# Query pattern (forward)
query = db.query(Tool).filter(Tool.created_at < cursor_timestamp, Tool.id < cursor_id)
items = query.order_by(Tool.created_at.desc(), Tool.id.desc()).limit(limit).all()

# Cursor encoding
cursor = base64.urlsafe_b64encode(json.dumps({"id": item.id, "created_at": item.created_at.isoformat()}).encode())

Pros:

• Consistent performance regardless of position
• Handles large datasets efficiently
• Stable results during mutations

Cons:

• Cannot jump to arbitrary pages
• More complex to implement
• Requires composite sorting key

4.3 Hybrid Approach (Recommended)

# Automatic selection based on result set size
if total_count > PAGINATION_CURSOR_THRESHOLD:
    # Use cursor-based pagination
    return cursor_paginate(query, cursor, limit)
else:
    # Use offset-based pagination
    return offset_paginate(query, page, limit)

5. Pagination Schema Design

5.1 Pydantic Models

from typing import Generic, TypeVar, Optional, List
from pydantic import BaseModel, Field

T = TypeVar('T')

class PaginationMeta(BaseModel):
    """Pagination metadata."""
    page: int = Field(..., description="Current page number (1-indexed)")
    per_page: int = Field(..., description="Items per page")
    total_items: int = Field(..., description="Total number of items")
    total_pages: int = Field(..., description="Total number of pages")
    has_next: bool = Field(..., description="Whether there is a next page")
    has_prev: bool = Field(..., description="Whether there is a previous page")
    next_cursor: Optional[str] = Field(None, description="Cursor for next page (cursor-based only)")
    prev_cursor: Optional[str] = Field(None, description="Cursor for previous page (cursor-based only)")

class PaginationLinks(BaseModel):
    """Pagination navigation links."""
    self: str = Field(..., description="Current page URL")
    first: str = Field(..., description="First page URL")
    last: str = Field(..., description="Last page URL")
    next: Optional[str] = Field(None, description="Next page URL")
    prev: Optional[str] = Field(None, description="Previous page URL")

class PaginatedResponse(BaseModel, Generic[T]):
    """Generic paginated response wrapper."""
    data: List[T] = Field(..., description="List of items")
    pagination: PaginationMeta = Field(..., description="Pagination metadata")
    links: PaginationLinks = Field(..., description="Navigation links")

# Usage examples
PaginatedToolResponse = PaginatedResponse[ToolRead]
PaginatedUserResponse = PaginatedResponse[EmailUserResponse]
PaginatedTeamResponse = PaginatedResponse[TeamResponse]

5.2 Query Parameters

from typing import Optional
from fastapi import Query

class PaginationParams(BaseModel):
    """Common pagination query parameters."""
    page: int = Query(1, ge=1, description="Page number (1-indexed)")
    per_page: int = Query(50, ge=1, le=500, description="Items per page (max 500)")
    cursor: Optional[str] = Query(None, description="Cursor for cursor-based pagination")
    sort_by: Optional[str] = Query("created_at", description="Sort field")
    sort_order: Optional[str] = Query("desc", regex="^(asc|desc)$", description="Sort order")

6. Environment Variables

Add to .env.example:

#####################################
# Pagination Configuration
#####################################

# Default number of items per page for paginated endpoints
# Applies to: tools, resources, prompts, servers, gateways, users, teams, tokens, etc.
# Default: 50
PAGINATION_DEFAULT_PAGE_SIZE=50

# Maximum allowed items per page (prevents abuse)
# Default: 500
PAGINATION_MAX_PAGE_SIZE=500

# Minimum items per page
# Default: 1
PAGINATION_MIN_PAGE_SIZE=1

# Threshold for switching from offset to cursor-based pagination
# When result set exceeds this count, use cursor-based pagination for performance
# Default: 10000
PAGINATION_CURSOR_THRESHOLD=10000

# Enable cursor-based pagination globally
# Options: true (default), false
# When false, only offset-based pagination is used
PAGINATION_CURSOR_ENABLED=true

# Default sort field for paginated queries
# Default: created_at
PAGINATION_DEFAULT_SORT_FIELD=created_at

# Default sort order for paginated queries
# Options: asc, desc (default)
PAGINATION_DEFAULT_SORT_ORDER=desc

# Maximum offset allowed for offset-based pagination (prevents abuse)
# Default: 100000 (100K records)
PAGINATION_MAX_OFFSET=100000

# Cache pagination counts for performance (seconds)
# Set to 0 to disable caching
# Default: 300 (5 minutes)
PAGINATION_COUNT_CACHE_TTL=300

# Enable pagination links in API responses
# Options: true (default), false
PAGINATION_INCLUDE_LINKS=true

# Base URL for pagination links (defaults to request URL)
# PAGINATION_BASE_URL=https://api.example.com

7. Database Indexes Required

# Alembic migration: add_pagination_indexes.py

def upgrade():
    # Tools table
    op.create_index('ix_tools_created_at_id', 'tools', ['created_at', 'id'], unique=False)
    op.create_index('ix_tools_team_id_created_at', 'tools', ['team_id', 'created_at'], unique=False)
    
    # Resources table
    op.create_index('ix_resources_created_at_id', 'resources', ['created_at', 'uri'], unique=False)
    op.create_index('ix_resources_team_id_created_at', 'resources', ['team_id', 'created_at'], unique=False)
    
    # Prompts table
    op.create_index('ix_prompts_created_at_name', 'prompts', ['created_at', 'name'], unique=False)
    op.create_index('ix_prompts_team_id_created_at', 'prompts', ['team_id', 'created_at'], unique=False)
    
    # Servers table
    op.create_index('ix_servers_created_at_id', 'servers', ['created_at', 'id'], unique=False)
    op.create_index('ix_servers_team_id_created_at', 'servers', ['team_id', 'created_at'], unique=False)
    
    # Gateways table
    op.create_index('ix_gateways_created_at_id', 'gateways', ['created_at', 'id'], unique=False)
    op.create_index('ix_gateways_team_id_created_at', 'gateways', ['team_id', 'created_at'], unique=False)
    
    # Users table
    op.create_index('ix_email_users_created_at_email', 'email_users', ['created_at', 'email'], unique=False)
    
    # Teams table
    op.create_index('ix_email_teams_created_at_id', 'email_teams', ['created_at', 'id'], unique=False)
    
    # API Tokens table
    op.create_index('ix_email_api_tokens_created_at_id', 'email_api_tokens', ['created_at', 'id'], unique=False)
    op.create_index('ix_email_api_tokens_owner_created_at', 'email_api_tokens', ['owner_email', 'created_at'], unique=False)
    
    # Auth Events table
    op.create_index('ix_email_auth_events_timestamp_id', 'email_auth_events', ['timestamp', 'id'], unique=False)
    op.create_index('ix_email_auth_events_user_timestamp', 'email_auth_events', ['email', 'timestamp'], unique=False)

8. UI/UX Requirements

8.1 Pagination Controls

• First, Previous, Page Numbers (1, 2, 3, ...), Next, Last buttons
• Current page indicator (highlighted)
• "Showing X-Y of Z items" text
• Page size dropdown (25, 50, 100, 200)
• Jump to page input (for offset-based only)

8.2 Accessibility

• Keyboard navigation (Tab, Enter, Arrow keys)
• ARIA labels for screen readers
• Focus management
• Loading states with aria-busy

8.3 Performance

• HTMX partial page updates (no full refresh)
• Optimistic UI updates
• Debounced search/filter
• Loading skeletons
• Virtual scrolling for very large lists (optional)

9. API Response Examples

9.1 Offset-Based Pagination

GET /admin/tools?page=2&per_page=50&team_id=team-123&sort_by=name&sort_order=asc

HTTP/1.1 200 OK
Content-Type: application/json

{
  "data": [
    {
      "id": "tool-100",
      "name": "echo",
      "description": "Echo text",
      "url": "https://mcp.example.com/echo",
      "created_at": "2025-01-15T10:30:00Z",
      "team_id": "team-123"
    },
    // ... 49 more tools
  ],
  "pagination": {
    "page": 2,
    "per_page": 50,
    "total_items": 1250,
    "total_pages": 25,
    "has_next": true,
    "has_prev": true,
    "next_cursor": null,
    "prev_cursor": null
  },
  "links": {
    "self": "/admin/tools?page=2&per_page=50&team_id=team-123&sort_by=name&sort_order=asc",
    "first": "/admin/tools?page=1&per_page=50&team_id=team-123&sort_by=name&sort_order=asc",
    "last": "/admin/tools?page=25&per_page=50&team_id=team-123&sort_by=name&sort_order=asc",
    "next": "/admin/tools?page=3&per_page=50&team_id=team-123&sort_by=name&sort_order=asc",
    "prev": "/admin/tools?page=1&per_page=50&team_id=team-123&sort_by=name&sort_order=asc"
  }
}

9.2 Cursor-Based Pagination

GET /admin/tools?cursor=eyJpZCI6InRvb2wtMTAwIiwiY3JlYXRlZF9hdCI6IjIwMjUtMDEtMTVUMTA6MzA6MDBaIn0&per_page=50

HTTP/1.1 200 OK
Content-Type: application/json

{
  "data": [
    // ... 50 tools
  ],
  "pagination": {
    "page": null,  // Not applicable for cursor-based
    "per_page": 50,
    "total_items": 125000,  // May be approximate
    "total_pages": null,  // Not applicable for cursor-based
    "has_next": true,
    "has_prev": true,
    "next_cursor": "eyJpZCI6InRvb2wtMTUwIiwiY3JlYXRlZF9hdCI6IjIwMjUtMDEtMTVUMDk6MzA6MDBaIn0",
    "prev_cursor": "eyJpZCI6InRvb2wtNTAiLCJjcmVhdGVkX2F0IjoiMjAyNS0wMS0xNVQxMTozMDowMFoifQ"
  },
  "links": {
    "self": "/admin/tools?cursor=eyJpZCI6InRvb2wtMTAwIiwiY3JlYXRlZF9hdCI6IjIwMjUtMDEtMTVUMTA6MzA6MDBaIn0&per_page=50",
    "first": "/admin/tools?per_page=50",
    "last": null,  // Not applicable for cursor-based
    "next": "/admin/tools?cursor=eyJpZCI6InRvb2wtMTUwIiwiY3JlYXRlZF9hdCI6IjIwMjUtMDEtMTVUMDk6MzA6MDBaIn0&per_page=50",
    "prev": "/admin/tools?cursor=eyJpZCI6InRvb2wtNTAiLCJjcmVhdGVkX2F0IjoiMjAyNS0wMS0xNVQxMTozMDowMFoifQ&per_page=50"
  }
}

10. Testing Requirements

10.1 Unit Tests

• Pagination parameter validation
• Cursor encoding/decoding
• Link generation
• Edge cases (empty results, single page, invalid cursors)

10.2 Integration Tests

• End-to-end pagination flows
• Filter + pagination combinations
• Sort + pagination
• Performance benchmarks (query execution time)

10.3 Load Tests

• 1M+ records pagination performance
• Concurrent pagination requests
• Memory usage under load
• Database connection pool behavior

10.4 UI Tests

• Playwright tests for pagination controls
• Keyboard navigation
• Page size changes
• Search + pagination

11. Performance Targets

Metric	Target	Measurement
API response time (first page)	<100ms	p95
API response time (page 100)	<200ms	p95
API response time (cursor-based)	<150ms	p95
Database query time	<50ms	p95
UI pagination control render	<50ms	p95
Full page load with pagination	<500ms	p95
Memory per request	<50MB	p95

12. Implementation Phases

Phase 1: Foundation

• Add pagination configuration to config.py
• Create Pydantic pagination schemas
• Implement pagination utility functions
• Create database indexes migration
• Update .env.example documentation

Phase 2: Core APIs

• Implement pagination for Tools API
• Implement pagination for Resources API
• Implement pagination for Prompts API
• Implement pagination for Servers API
• Implement pagination for Gateways API
• Implement pagination for A2A Agents API

Phase 3: User Management APIs

• Implement pagination for Users API
• Implement pagination for Auth Events API
• Implement pagination for Pending Approvals API

Phase 4: Team Management APIs

• Implement pagination for Teams API
• Implement pagination for Team Members API
• Implement pagination for Team Invitations API
• Implement pagination for Join Requests API
• Implement pagination for Team Discovery API

Phase 5: Token & RBAC APIs

• Implement pagination for API Tokens API
• Implement pagination for Roles API
• Implement pagination for User Roles API
• Implement pagination for Permissions API

Phase 6: UI Integration

• Create HTMX pagination partial templates
• Implement Alpine.js pagination controls
• Add pagination to Tools UI
• Add pagination to Resources UI
• Add pagination to Prompts UI
• Add pagination to Servers UI
• Add pagination to Gateways UI
• Add pagination to Users UI
• Add pagination to Teams UI
• Add pagination to Tokens UI

Phase 7: Testing & Optimization

• Write unit tests for all pagination functions
• Write integration tests for all paginated endpoints
• Performance benchmarking
• Query optimization
• Load testing

Phase 8: Documentation

• API documentation updates
• Admin guide for pagination configuration
• Developer guide for adding pagination
• Performance tuning guide

13. Success Metrics

• All P0 endpoints support pagination
• API response times <200ms for paginated requests (p95)
• Database queries use indexes (verified by EXPLAIN)
• UI supports 1M+ items without performance degradation
• Zero N+1 query issues
• Test coverage >90% for pagination code
• Documentation complete and reviewed

14. Non-Goals (Out of Scope)

• GraphQL pagination (different pattern)
• Infinite scroll UI (use pagination controls instead)
• Real-time pagination updates (use polling/SSE separately)
• Pagination for binary data endpoints
• Pagination for SSE/WebSocket streams

15. References

• REST API Pagination Best Practices
• Cursor vs Offset Pagination
• SQLAlchemy Pagination
• FastAPI Pagination Libraries

---

📊 Impact Assessment

Before Pagination

• Tools list: 10-30s load time for 10K+ tools
• Memory usage: 500MB+ for large result sets
• Database CPU: 80%+ during list operations
• User experience: Frozen browser, timeouts

After Pagination

• Tools list: <200ms load time for any page
• Memory usage: <50MB per request
• Database CPU: <20% during list operations
• User experience: Instant navigation, smooth scrolling

Scale Projections

Deployment Scale	Tools Count	Users	Response Time	Memory
Small	<1K	<100	50ms	10MB
Medium	1K-10K	100-1K	100ms	20MB
Large	10K-100K	1K-10K	150ms	30MB
Enterprise	100K-1M	10K-100K	200ms	40MB
Massive	1M+	100K+	250ms	50MB

---

🎯 Acceptance Criteria

• All P0 endpoints implement pagination
• All P1 endpoints implement pagination
• Database indexes created for all paginated tables
• UI pagination controls implemented for all list views
• Configuration options documented in .env.example
• API documentation updated with pagination examples
• Unit test coverage >90%
• Integration tests for all paginated endpoints
• Performance benchmarks meet targets
• Load testing validates scale targets
• Admin guide includes pagination tuning
• Developer guide includes pagination patterns

---

🔗 Related Issues

• Depends on: Database performance optimization (#TBD)
• Blocks: Federation scalability (#TBD)
• Related to: Caching strategy (#TBD)

---

🏷️ Labels

epic, p0, api, ui, performance, scalability, database

[crivetimihai]

REST API and UI Pagination Implementation Checklist

Based on Issue #1224

Overview

Enable efficient data retrieval and display for large-scale multi-tenant MCP Gateway deployments with:

• 160,000+ users (with ~500,000 teams)
• Millions of prompts and resources
• Tens of thousands of MCP servers (gateways)
• Millions of tools from federated gateways
• Hundreds of thousands of API tokens

Phase 1: Foundation ✅

Configuration & Schema

• Add pagination configuration to mcpgateway/config.py

  • PAGINATION_DEFAULT_PAGE_SIZE (default: 50)
  • PAGINATION_MAX_PAGE_SIZE (default: 500)
  • PAGINATION_MIN_PAGE_SIZE (default: 1)
  • PAGINATION_CURSOR_THRESHOLD (default: 10000)
  • PAGINATION_CURSOR_ENABLED (default: true)
  • PAGINATION_DEFAULT_SORT_FIELD (default: created_at)
  • PAGINATION_DEFAULT_SORT_ORDER (default: desc)
  • PAGINATION_MAX_OFFSET (default: 100000)
  • PAGINATION_COUNT_CACHE_TTL (default: 300)
  • PAGINATION_INCLUDE_LINKS (default: true)

• Create Pydantic pagination schemas in mcpgateway/schemas.py

  • PaginationMeta schema
  • PaginationLinks schema
  • PaginatedResponse[T] generic schema
  • PaginationParams query parameters

• Implement pagination utility functions in mcpgateway/utils/pagination.py

  • offset_paginate() - offset-based pagination
  • cursor_paginate() - cursor-based pagination
  • encode_cursor() - cursor encoding
  • decode_cursor() - cursor decoding
  • generate_pagination_links() - link generation
  • paginate_query() - automatic selection (offset vs cursor)

• Create database indexes migration

  • Create Alembic migration: alembic/versions/add_pagination_indexes.py
  • Add indexes for Tools table
  • Add indexes for Resources table
  • Add indexes for Prompts table
  • Add indexes for Servers table
  • Add indexes for Gateways table
  • Add indexes for Users table
  • Add indexes for Teams table
  • Add indexes for API Tokens table
  • Add indexes for Auth Events table
  • Add indexes for Team Members table
  • Add indexes for Team Invitations table
  • Add indexes for Join Requests table
  • Add indexes for A2A Agents table
  • Add indexes for Roles table
  • Add indexes for UserRoles table

• Update .env.example documentation

  • Add pagination configuration section
  • Document all pagination environment variables
  • Add usage examples and recommendations

Phase 2: Core APIs (P0) 🔧

MCP Resources

• Tools API (GET /admin/tools)

  • Add pagination parameters to endpoint
  • Implement offset-based pagination
  • Implement cursor-based pagination for >10K results
  • Support filtering with pagination (team_id, tags, etc.)
  • Generate pagination links
  • Add unit tests
  • Add integration tests
  • Performance benchmark (<200ms p95)

• Resources API (GET /admin/resources)

  • Add pagination parameters to endpoint
  • Implement offset-based pagination
  • Implement cursor-based pagination for >10K results
  • Support filtering with pagination
  • Generate pagination links
  • Add unit tests
  • Add integration tests
  • Performance benchmark

• Prompts API (GET /admin/prompts)

  • Add pagination parameters to endpoint
  • Implement offset-based pagination
  • Implement cursor-based pagination for >10K results
  • Support filtering with pagination
  • Generate pagination links
  • Add unit tests
  • Add integration tests
  • Performance benchmark

• Servers API (GET /admin/servers)

  • Add pagination parameters to endpoint
  • Implement offset-based pagination
  • Implement cursor-based pagination for >10K results
  • Support filtering with pagination
  • Generate pagination links
  • Add unit tests
  • Add integration tests
  • Performance benchmark

• Gateways API (GET /admin/gateways)

  • Add pagination parameters to endpoint
  • Implement offset-based pagination
  • Implement cursor-based pagination for >10K results
  • Support filtering with pagination
  • Generate pagination links
  • Add unit tests
  • Add integration tests
  • Performance benchmark

• A2A Agents API (GET /admin/a2a)

  • Add pagination parameters to endpoint
  • Implement offset-based pagination
  • Implement cursor-based pagination for >10K results
  • Support filtering with pagination
  • Generate pagination links
  • Add unit tests
  • Add integration tests
  • Performance benchmark

Phase 3: User Management APIs (P0) 👥

• Users API (GET /email-auth/admin/users)

  • Add pagination parameters to endpoint
  • Implement offset-based pagination
  • Implement cursor-based pagination for >10K results
  • Support filtering (status, role, etc.)
  • Generate pagination links
  • Add unit tests
  • Add integration tests
  • Performance benchmark

• Auth Events API (GET /email-auth/admin/events) (P1)

  • Add pagination parameters to endpoint
  • Implement cursor-based pagination (large dataset expected)
  • Support filtering (user, event_type, date range)
  • Generate pagination links
  • Add unit tests
  • Add integration tests
  • Performance benchmark

• Pending Approvals API (GET /sso/pending-approvals) (P1)

  • Add pagination parameters to endpoint
  • Implement offset-based pagination
  • Support filtering with pagination
  • Generate pagination links
  • Add unit tests
  • Add integration tests
  • Performance benchmark

Phase 4: Team Management APIs (P0) 👥

• Teams API (GET /teams)

  • Add pagination parameters to endpoint
  • Implement offset-based pagination
  • Implement cursor-based pagination for >10K results
  • Support filtering (owner, name, visibility)
  • Generate pagination links
  • Add unit tests
  • Add integration tests
  • Performance benchmark

• Team Members API (GET /teams/{team_id}/members)

  • Add pagination parameters to endpoint
  • Implement offset-based pagination
  • Support filtering (role, status)
  • Generate pagination links
  • Add unit tests
  • Add integration tests
  • Performance benchmark

• Team Invitations API (GET /teams/{team_id}/invitations) (P1)

  • Add pagination parameters to endpoint
  • Implement offset-based pagination
  • Support filtering (status, invited_by)
  • Generate pagination links
  • Add unit tests
  • Add integration tests
  • Performance benchmark

• Join Requests API (GET /teams/{team_id}/join-requests) (P1)

  • Add pagination parameters to endpoint
  • Implement offset-based pagination
  • Support filtering (status)
  • Generate pagination links
  • Add unit tests
  • Add integration tests
  • Performance benchmark

• Team Discovery API (GET /teams/discover) (P1)

  • Add pagination parameters to endpoint
  • Implement offset-based pagination
  • Implement cursor-based pagination for >10K results
  • Support filtering (visibility, tags)
  • Generate pagination links
  • Add unit tests
  • Add integration tests
  • Performance benchmark

Phase 5: Token & RBAC APIs (P0/P1) 🔑

API Tokens

• User Tokens API (GET /api-tokens)

  • Add pagination parameters to endpoint
  • Implement offset-based pagination
  • Support filtering (status, scope)
  • Generate pagination links
  • Add unit tests
  • Add integration tests
  • Performance benchmark

• Admin Tokens API (GET /api-tokens/admin/all)

  • Add pagination parameters to endpoint
  • Implement offset-based pagination
  • Implement cursor-based pagination for >10K results
  • Support filtering (owner, team, status)
  • Generate pagination links
  • Add unit tests
  • Add integration tests
  • Performance benchmark

• Team Tokens API (GET /api-tokens/teams/{team_id}) (P1)

  • Add pagination parameters to endpoint
  • Implement offset-based pagination
  • Support filtering with pagination
  • Generate pagination links
  • Add unit tests
  • Add integration tests
  • Performance benchmark

RBAC

• Roles API (GET /rbac/roles) (P1)

  • Add pagination parameters to endpoint
  • Implement offset-based pagination
  • Support filtering with pagination
  • Generate pagination links
  • Add unit tests
  • Add integration tests
  • Performance benchmark

• User Roles API (GET /rbac/users/{user_email}/roles) (P1)

  • Add pagination parameters to endpoint
  • Implement offset-based pagination
  • Generate pagination links
  • Add unit tests
  • Add integration tests
  • Performance benchmark

• User Permissions API (GET /rbac/permissions/user/{user_email}) (P1)

  • Add pagination parameters to endpoint
  • Implement offset-based pagination
  • Generate pagination links
  • Add unit tests
  • Add integration tests
  • Performance benchmark

• My Roles API (GET /rbac/my/roles) (P1)

  • Add pagination parameters to endpoint
  • Implement offset-based pagination
  • Generate pagination links
  • Add unit tests
  • Add integration tests
  • Performance benchmark

Phase 6: Additional APIs (P1/P2) 🔧

SSO & Providers

• SSO Providers API (GET /sso/providers) (P2)

  • Add pagination parameters to endpoint
  • Implement offset-based pagination
  • Support filtering with pagination
  • Generate pagination links
  • Add unit tests
  • Add integration tests

• Admin Providers API (GET /sso/admin/providers) (P2)

  • Add pagination parameters to endpoint
  • Implement offset-based pagination
  • Support filtering with pagination
  • Generate pagination links
  • Add unit tests
  • Add integration tests

MCP Registry & Plugins

• MCP Registry Servers API (GET /admin/mcp-registry/servers) (P1)

  • Add pagination parameters to endpoint
  • Implement offset-based pagination
  • Support filtering with pagination
  • Generate pagination links
  • Add unit tests
  • Add integration tests

• Plugins API (GET /admin/plugins) (P2)

  • Add pagination parameters to endpoint
  • Implement offset-based pagination
  • Generate pagination links
  • Add unit tests
  • Add integration tests

• Tags API (GET /admin/tags) (P1)

  • Add pagination parameters to endpoint
  • Implement offset-based pagination
  • Support filtering with pagination
  • Generate pagination links
  • Add unit tests
  • Add integration tests

Audit & Logs

• Logs API (GET /admin/logs) (P1)

  • Add pagination parameters to endpoint
  • Implement cursor-based pagination (large dataset)
  • Support filtering (level, timestamp, component)
  • Generate pagination links
  • Add unit tests
  • Add integration tests

• Import Status API (GET /admin/import/status) (P2)

  • Add pagination parameters to endpoint
  • Implement offset-based pagination
  • Support filtering with pagination
  • Generate pagination links
  • Add unit tests
  • Add integration tests

Phase 7: UI Integration 🎨

UI Component Development

• Create HTMX pagination partial templates

  • mcpgateway/templates/partials/pagination_controls.html
  • Support for offset-based pagination controls
  • Support for cursor-based pagination controls
  • Page size selector dropdown
  • "Showing X-Y of Z items" indicator
  • Keyboard navigation support
  • ARIA labels for accessibility

• Implement Alpine.js pagination state management

  • Create pagination.js Alpine.js component
  • Handle page changes
  • Handle page size changes
  • Preserve filters/search during pagination
  • Update URL with page parameters
  • localStorage for page size preference

Admin UI Pages

• Tools UI (/admin#tools)

  • Integrate pagination controls
  • HTMX partial page updates
  • Loading states
  • Preserve search/filters
  • Test with 10K+ tools

• Resources UI (/admin#resources)

  • Integrate pagination controls
  • HTMX partial page updates
  • Loading states
  • Preserve search/filters
  • Test with large datasets

• Prompts UI (/admin#prompts)

  • Integrate pagination controls
  • HTMX partial page updates
  • Loading states
  • Preserve search/filters
  • Test with large datasets

• Servers UI (/admin#servers)

  • Integrate pagination controls
  • HTMX partial page updates
  • Loading states
  • Preserve search/filters
  • Test with large datasets

• Gateways UI (/admin#gateways)

  • Integrate pagination controls
  • HTMX partial page updates
  • Loading states
  • Preserve search/filters
  • Test with large datasets

• A2A Agents UI (/admin#a2a)

  • Integrate pagination controls
  • HTMX partial page updates
  • Loading states
  • Preserve search/filters
  • Test with large datasets

• Users UI (/admin#users)

  • Integrate pagination controls
  • HTMX partial page updates
  • Loading states
  • Preserve search/filters
  • Test with 160K+ users

• Teams UI (/admin#teams)

  • Integrate pagination controls
  • HTMX partial page updates
  • Loading states
  • Preserve search/filters
  • Test with 500K+ teams

• API Tokens UI (/admin#tokens)

  • Integrate pagination controls
  • HTMX partial page updates
  • Loading states
  • Preserve search/filters
  • Test with large datasets

• Logs UI (/admin#logs)

  • Integrate pagination controls
  • HTMX partial page updates
  • Loading states
  • Preserve search/filters
  • Test with large datasets

• Tags UI (/admin#tags)

  • Integrate pagination controls
  • HTMX partial page updates
  • Loading states
  • Preserve search/filters

• Plugins UI (/admin#plugins)

  • Integrate pagination controls
  • HTMX partial page updates
  • Loading states
  • Preserve search/filters

• MCP Registry UI (/admin#mcp-registry)

  • Integrate pagination controls
  • HTMX partial page updates
  • Loading states
  • Preserve search/filters

Phase 8: Testing & Optimization 🧪

Unit Tests

• Test pagination parameter validation

  • Valid page numbers
  • Invalid page numbers (negative, zero)
  • Valid page sizes
  • Invalid page sizes (too large, too small)
  • Cursor encoding/decoding
  • Invalid cursors

• Test pagination utility functions

  • offset_paginate() with various inputs
  • cursor_paginate() with various inputs
  • paginate_query() automatic selection
  • Link generation
  • Edge cases (empty results, single page, last page)

• Test pagination schema validation

  • PaginationMeta validation
  • PaginationLinks validation
  • PaginatedResponse[T] generic validation

Integration Tests

• Test end-to-end pagination flows

  • Tools pagination with filters
  • Resources pagination with filters
  • Prompts pagination with filters
  • Servers pagination with filters
  • Users pagination with filters
  • Teams pagination with filters
  • Tokens pagination with filters

• Test pagination + filtering combinations

  • Multiple filters with pagination
  • Search with pagination
  • Sort with pagination

• Test pagination edge cases

  • No results
  • Single result
  • Exactly one page
  • Last page partial results
  • Invalid page numbers
  • Invalid cursors

Performance Testing

• Benchmark pagination queries

  • First page (page=1) performance
  • Middle page (page=100) performance
  • Last page performance
  • Cursor-based pagination performance
  • Query with filters performance
  • Query with sorting performance

• Load testing

  • 1M+ records pagination
  • Concurrent pagination requests
  • Memory usage under load
  • Database connection pool behavior
  • Cache effectiveness

• Query optimization

  • Verify index usage (EXPLAIN ANALYZE)
  • Identify N+1 queries
  • Optimize slow queries
  • Add missing indexes if needed

UI Testing

• Playwright tests for pagination controls

  • Click next/previous buttons
  • Jump to specific page
  • Change page size
  • Keyboard navigation
  • Mobile responsive behavior

• UI accessibility tests

  • Screen reader compatibility
  • Keyboard navigation
  • ARIA labels
  • Focus management
  • Loading states with aria-busy

• UI performance tests

  • Page transition speed
  • Loading state display
  • Smooth scrolling
  • No layout shifts

Phase 9: Documentation 📚

API Documentation

• Update OpenAPI/Swagger specs

  • Document pagination parameters
  • Document pagination response schema
  • Add examples for offset-based pagination
  • Add examples for cursor-based pagination
  • Document error responses

• Update REST API guide

  • Explain pagination concepts
  • Provide usage examples
  • Explain offset vs cursor pagination
  • Document best practices

Admin Documentation

• Admin guide for pagination configuration

  • Explain environment variables
  • Provide tuning recommendations
  • Document performance characteristics
  • Troubleshooting guide

• User guide for UI pagination

  • How to navigate paginated lists
  • How to change page size
  • How to search with pagination
  • Keyboard shortcuts

Developer Documentation

• Developer guide for adding pagination

  • Step-by-step tutorial
  • Code examples
  • Best practices
  • Common pitfalls

• Performance tuning guide

  • Database optimization
  • Index recommendations
  • Query optimization
  • Caching strategies
  • Monitoring and metrics

Success Criteria 🎯

Performance Targets

• API response times <200ms for paginated requests (p95)
• First page load <100ms (p95)
• Database queries use indexes (verified by EXPLAIN)
• UI supports 1M+ items without performance degradation
• Zero N+1 query issues
• Memory per request <50MB (p95)

Coverage Targets

• All P0 endpoints support pagination
• All P1 endpoints support pagination
• Test coverage >90% for pagination code
• UI pagination implemented for all list views

Quality Targets

• Documentation complete and reviewed
• All integration tests passing
• Load tests validate scale targets (1M+ records)
• Accessibility tests passing (WCAG 2.1 AA)
• Performance benchmarks meet targets

Notes & Considerations 📝

Implementation Strategy

• Use hybrid approach: offset-based for <10K results, cursor-based for >10K
• Implement generic PaginatedResponse[T] to ensure consistency
• Add database indexes before implementing pagination queries
• Use Redis caching for total counts (TTL: 5 minutes)
• Consider approximate counts for very large datasets (>1M)

Database Considerations

• Create composite indexes for (team_id, created_at) on all main tables
• Use (created_at, id) for cursor-based pagination
• Test index performance with EXPLAIN ANALYZE
• Monitor index size and maintenance overhead

UI/UX Considerations

• Always show total count when possible
• Use loading skeletons during page transitions
• Preserve search/filter state across pages
• Remember page size preference in localStorage
• Provide keyboard shortcuts (J/K for prev/next)

Performance Considerations

• Cache total counts for 5 minutes (configurable)
• Use approximate counts for >1M records
• Consider virtual scrolling for very large lists
• Monitor query performance with slow query log
• Set connection pool size appropriately

Security Considerations

• Validate pagination parameters to prevent abuse
• Enforce maximum page size limit
• Implement rate limiting for pagination endpoints
• Audit access to paginated sensitive data
• Log suspicious pagination patterns

Related Issues

• Issue Epic: REST API and UI Pagination for Large-Scale Multi-Tenant Deployments #1224 (this epic)
• Database performance optimization (TBD)
• Federation scalability (TBD)
• Caching strategy (TBD)