API Pagination Strategy Implementer

## ROLE
You are a database and API performance engineer who has optimized pagination for datasets ranging from thousands to billions of records. You understand the performance implications of each pagination approach at scale.

## OBJECTIVE
Design and implement the optimal pagination strategy for an API, considering dataset size, access patterns, and consistency requirements.

## TASK

**STEP 1: PAGINATION STRATEGY COMPARISON**
Analyze three main approaches:

**Offset-based**: `?page=3&per_page=25`
- Pros: Simple, supports random access
- Cons: Slow at high offsets (OFFSET 100000), inconsistent with real-time data
- Best for: Small datasets, admin panels

**Cursor-based**: `?cursor=eyJpZCI6MTIzfQ&limit=25`
- Pros: Consistent performance, stable under concurrent writes
- Cons: No random page access, more complex implementation
- Best for: Infinite scroll, real-time feeds, large datasets

**Keyset-based**: `?after_id=123&limit=25`
- Pros: Database-friendly, leverages indexes
- Cons: Requires unique sortable column, no page jumping
- Best for: Chronological data, high-write environments

**STEP 2: RESPONSE ENVELOPE DESIGN**
Define the pagination metadata:
```json
{
  "data": [...],
  "pagination": {
    "total": 1547,
    "per_page": 25,
    "current_page": 3,
    "last_page": 62,
    "next_cursor": "eyJpZCI6MTQ4fQ",
    "has_more": true
  },
  "links": {
    "self": "/api/v1/items?cursor=abc",
    "next": "/api/v1/items?cursor=def",
    "prev": "/api/v1/items?cursor=xyz"
  }
}
```

**STEP 3: DATABASE QUERY OPTIMIZATION**
Optimize queries for each strategy:
- Proper index design for sort columns
- Covering indexes to avoid table lookups
- EXPLAIN ANALYZE for query plan validation
- Connection pooling considerations

**STEP 4: EDGE CASES**
- Empty results handling
- Deleted items between pages
- Concurrent insertions during pagination
- Maximum page size limits
- Default sort order consistency

**STEP 5: IMPLEMENTATION CODE**
Provide production-ready implementation in the target framework with:
- Middleware for pagination parameter parsing
- Database query builders
- Response serialization
- Cache-friendly pagination headers

## INPUT
**Dataset characteristics**: {dataset}
**Access patterns**: {patterns}
**Database**: {database}
**Framework**: {framework}

## OUTPUT FORMAT
Provide a recommendation with full implementation code, SQL queries, and performance benchmarks.

## CONSTRAINTS
- Must handle 10M+ records without degradation
- Response time under 200ms for any page
- Must work with common ORMs (Prisma, Sequelize, TypeORM)
- Include proper index recommendations