api-pagination-filtering-sorting

Files (4)

• SKILL.md
• query-params-conventions.md · references/
• repo-evidence.md · references/
• response-metadata.md · references/

SKILL.md (first lines)

Standardize HTTP query parameter conventions and response metadata for paginated, filtered, and sorted list endpoints.

## When to use

- Designing a new list endpoint with pagination, filtering, or sorting
- Implementing multi-value or hierarchical filters (e.g., segment, family, class filters)
- Building repository/query builder layers that translate query params to SQL
- Adding search across multiple fields to a list endpoint
- Standardizing response metadata across paginated endpoints
- Reviewing endpoint consistency for pagination patterns
- Adding sort_by/order_by capabilities to existing endpoints
- Implementing cursor-based pagination for large datasets (complement to offset-based)

## Core conventions

1. **Pagination styles**: Two common patterns emerge—(a) `page` (1-indexed) + `page_size` with `total_pages` in response; (b) `limit` + `offset` (0-indexed) with `has_next` boolean. Pattern (a) is friendlier for UI pagination controls; pattern (b) is more SQL-native. Choose one per service; mixing within a service confuses clients. `page_size` or `limit` should have a `ge=1, le=<MAX>` constraint (common max: 100 or 1000).

2. **Offset calculation**: When using `page`/`page_size`, calculate `offset = (page - 1) * page_size` in the route handler or service layer before passing to repository. When using `limit`/`offset`, pass directly. Both approaches translate to `LIMIT {limit} OFFSET {offset}` in SQL.

3. **Response pagination metadata**: Standardize on a `PaginationInfo` or inline fields embedded in the data response. Common fields: `page` (current page, 1-indexed), `page_size` or `limit` (records per page), `total` or `total_records` (total count across all pages), `total_pages` (ceiling division: `(total + page_size - 1) // page_size`), `has_next` (boolean: `(offset + limit) < total` or `page < total_pages`), optional `has_prev` (boolean: `page > 1` or `offset > 0`). Return metadata alongside `data` list in the response envelope.

4. **Multi-value filters via FastAPI `Query`**: For filters that accept multiple values (e.g., `status`, `store_ids`, `segment_codes`), use `Optional[List[str]] = Query(None, description="...")`. The client sends `?status=pending&status=approved` or `?status[]=pending&status[]=approved` (FastAPI accepts both). In the query builder, translate to SQL `IN` clause or `ANY(ARRAY[...])`.

5. **Comma-separated multi-value filters**: Some endpoints accept comma-separated values in a single param (e.g., `?items=ITEM-001,ITEM-002,ITEM-003`). Use `Optional[str] = Query(None)` and split by comma in the service or repository layer before building SQL. This pattern is common for legacy APIs or when the client cannot send array query params.

6. **Hierarchical filters**: Product or location hierarchies (e.g., segment → family → class → brick; zone → format → state → region → city → store) use separate query params for each level. Each is optional; when provided, it narrows results to that level. Example: `?segment=BEVERAGES&family=SOFT_DRINKS&class=COLA`. Query builder applies `WHERE` clauses for each non-null filter. Some services support both singular and plural param names (`?store=...` and `?stores=...`); prefer plural for multi-value, singular for single value.

7. **Sorting via `sort_by` + `sort_order` or `order_by` + `direction`**: Two naming conventions—`sort_by` (field name) + `sort_order` (asc/desc) or `order_by` + `direction`. Choose one per service. Use FastAPI `Query` with `regex="^(ASC|DESC)$"` or `regex="^(asc|desc)$"` for order validation. Map user-facing sort field names (e.g., `doh`, `stock`, `sales`) to actual column names in query builder via a `sort_mapping` dict. Default sort is usually `created_at DESC` or a domain-specific default (e.g., `total_actual_qty DESC` for forecast data).

8. **QueryBuilder pattern**: Encapsulate SQL construction in a `QueryBuilder` class. It receives filters, pagination params, and sort params; returns `(query, params)` tuple with parameterized placeholders (`$1, $2, ...` for PostgreSQL, `?` for others). The builder resets params per query via `_reset_params()` and adds params via `_add_param(value)` returning a placeholder. Separate methods for data query (`build_data_query`) and count query (`build_count_query`) sharing the same filter logic.

9. **Repository layer pagination**: The repository fetches two results in parallel—`(records_data, total_count)`—via `asyncio.gather` or sequential calls. It executes the count query to get `total_count`, then executes the data query with `LIMIT/OFFSET`. Service layer constructs the response object with data + pagination metadata. For download endpoints (no pagination), omit `LIMIT/OFFSET` from the query.

10. **Search across multiple fields**: A single `search` query param applies a case-insensitive substring match across multiple columns. In SQL, use `WHERE (LOWER(field1) LIKE LOWER(:search) OR LOWER(field2) LIKE LOWER(:search) OR ...)` with param `%{search}%`. Some implementations use `ILIKE` (PostgreSQL) or full-text search. Common searchable fields: item codes, descriptions, store IDs, order IDs.

11. **Pydantic models for query params**: Define a `QueryParams` Pydantic model (e.g., `InventoryQueryParams`, `ForecastQuery`) to encapsulate all filters, pagination, sorting, and search params. This separates route handler param extraction from business logic. The model can provide helper methods like `@property def limit(self)` to derive limit from `page_size` or compute `offset` from `page` and `page_size`.

12. **FastAPI route handler**: Extract query params via `Query()` for primitive types or depend on Pydantic model. Calculate `offset = (page - 1) * page_size` if needed. Call service layer with `query_params` object or individual params. Return response via `ResponseData.ok(data=..., pagination=...)` or a typed response model wrapping data + metadata.

13. **Default and max constraints**: Pagination params should have defaults and constraints: `page: int = Query(1, ge=1)`, `page_size: int = Query(20, ge=1, le=100)`, `limit: int = Query(20, ge=1, le=1000)`, `offset: int = Query(0, ge=0)`. Document max values in endpoint docstring. Constants like `DEFAULT_PAGE_SIZE = 20`, `MAX_PAGE_SIZE = 1000` centralize these values.

14. **RBAC / hierarchy access control**: Some endpoints filter results by user's hierarchy access (e.g., user can only see data for their assigned stores or segments). This is modeled as `List[HierarchyFilter]` derived from user context (x-user-data JWT). The query builder applies RBAC filters as additional `WHERE` clauses (e.g., `WHERE (segment = ANY(:allowed_segments) OR :allowed_segments = '{all}')`). Cross-reference `python-dao-and-database` for DAO-level RLS patterns.

15. **Caching paginated responses**: For expensive queries, cache results by a key derived from `(query_params, hierarchy_filters, org_id, run_id)`. Serialize the Pydantic response model to JSON for cache storage. Cache TTL is typically 1 hour. Invalidate cache on data writes. Use Redis or in-memory cache. Avoid caching every page individually; instead cache the full result set or the first page and accept cache misses for deeper pages.

16. **Cursor/keyset pagination — and *why* it beats offset for large or mutating sets**: offset pagination (`LIMIT/OFFSET`) has two failure modes the conventions above don't fix. **Correctness:** when rows are inserted or deleted between two page fetches, the offset shifts, so the client silently **skips rows or sees the same row twice** across page boundaries. **Performance:** the database must scan and discard all `offset` rows before returning the page, so deep pages (`OFFSET 100000`) degrade linearly. **Cursor (keyset) pagination** fixes both by paginating *relative to a stable ordering key* instead of a numeric skip: `WHERE (sort_key, id) > (:last_sort_key, :last_id) ORDER BY sort_key, id LIMIT :n`. Return an **opaque, encoded `next_cursor`** (the last row's ordering key(s) — never a raw offset the client can tamper with) plus `has_next`. It requires a **total, stable sort** — append a unique tiebreaker (e.g. `id`) so ties can't reorder between requests. Trade-off: no "jump to page 50" and no exact `total_pages`. Use offset for small, bounded, human-paged lists; use cursor for infinite scroll, large tables, and any list that mutates under the reader.

> The offset-skips-rows / deep-page-cost rationale and the opaque-cursor convention follow common API design canon (e.g. Stripe's list pagination). Stack-agnostic; the `(sort_key, id)` keyset predicate maps to any ordered store.

## Skeleton / example

```python
# models.py
from typing import Optional, List
from pydantic import BaseModel, Field

class QueryParams(BaseModel):
    """Query parameters for list endpoint."""
    # Pagination (page/page_size style)
    page: int = Field(default=1, ge=1, description="Page number (1-indexed)")
    page_size: int = Field(default=20, ge=1, le=100, description="Records per page")

    # Sorting
    sort_by: str = Field(default="created_at", description="Field to sort by")
    sort_order: str = Field(default="desc", description="Sort direction: asc or desc")

    # Search
    search: Optional[str] = Field(None, description="Search text")

    # Multi-value filters
    status: Optional[List[str]] = Field(None, description="Filter by status")
    store_ids: Optional[List[str]] = Field(None, description="Filter by store IDs")

    # Hierarchical filters
    segment: Optional[str] = Field(None, description="Product segment")
    family: Optional[str] = Field(None, description="Product family")

    @property
    def offset(self) -> int:
        """Calculate offset from page and page_size."""
        return (self.page - 1) * self.page_size


class PaginationInfo(BaseModel):
    """Pagination metadata."""
    page: int = Field(..., description="Current page number")
    page_size: int = Field(..., description="Records per page")
    total_records: int = Field(..., description="Total number of records")
    total_pages: int = Field(..., description="Total number of pages")
    has_next: bool = Field(..., description="Whether more records are available")


class ItemListData(BaseModel):
    """Response data for item list."""
    data: List[dict] = Field(default_factory=list, description="List of records")
    pagination: PaginationInfo = Field(..., description="Pagination metadata")
```

```python
# query_builder.py
from typing import Dict, List, Any, Tuple

class ItemQueryBuilder:
    """Builds SQL queries for item list with filters, sorting, pagination."""

    def __init__(self, schema: str, table: str):
        self.schema = schema
        self.table = table
        self._params: List[Any] = []
        self._param_index = 1

    def build_data_query(
        self,
        query_params: QueryParams,
        limit: int,
        offset: int
    ) -> Tuple[str, List[Any]]:
        """Build main query with pagination."""
        self._reset_params()

From ajyadav013/claude-kit, MIT.

More from ajyadav013/claude-kit