feat: framework-level pagination support for YAML SQL tools

[ajshedivy]

Summary

Add declarative pagination support to YAML-defined SQL tools so that tool authors don't have to manually wire up OFFSET/FETCH FIRST boilerplate, hasMore detection, or pagination metadata in every tool.

Background

PR #__ (feat/default-toolset branch) added offset/limit pagination to the built-in list_schemas and list_tables_in_schema default tools using the limit+1 trick for hasMore detection. This pattern should be available to YAML tool authors as a first-class framework feature.

Current state

YAML authors can add pagination manually today using named parameters:

tools:
  find_customers:
    statement: |
      SELECT * FROM CUSTOMERS
      WHERE STATE = :state_code
      ORDER BY NAME
      OFFSET :offset ROWS FETCH FIRST :limit ROWS ONLY
    parameters:
      - name: limit
        type: integer
        default: 50
        min: 1
        max: 500
      - name: offset
        type: integer
        default: 0
        min: 0

But this has drawbacks:

• Every tool repeats the same boilerplate parameters and SQL clause
• No automatic hasMore detection (the limit+1 trick)
• No pagination metadata (hasMore, limit, offset) in the structured output
• Easy to forget ORDER BY (pagination without deterministic ordering is a bug)

Proposed Solution

Add a declarative pagination block to the YAML tool schema:

tools:
  find_customers:
    source: production
    description: "Find customers by state"
    statement: |
      SELECT CUSTID, NAME, CITY, STATE
      FROM CUSTOMERS
      WHERE STATE = :state_code
      ORDER BY NAME
    pagination:
      enabled: true
      defaultLimit: 50
      maxLimit: 500
    parameters:
      - name: state_code
        type: string
        required: true

When pagination.enabled: true, the framework would:

1. Auto-inject limit and offset into the Zod input schema (toolConfigBuilder.ts → generateZodSchema())
2. Auto-append OFFSET ? ROWS FETCH FIRST ? ROWS ONLY to the SQL statement (in the executor layer)
3. Apply the limit+1 trick — fetch one extra row to detect hasMore, then trim before returning
4. Inject hasMore, limit, offset into the structured output schema
5. Show pagination info in the response formatter ("Found 50 results (offset 0, limit 50, hasMore: true)")

Configuration options

Field	Type	Default	Description
enabled	boolean	false	Enable framework-managed pagination
defaultLimit	integer	50	Default page size when limit not provided
maxLimit	integer	500	Maximum allowed page size

Alternative Considered

Convention via reserved parameter names — if the YAML defines parameters named limit and offset with a pagination: true flag, the executor applies hasMore logic but the SQL stays explicit (no auto-injection). This is more transparent but still requires SQL boilerplate.

Affected Components

Component	File	Change
YAML schema validation	config.ts	Add pagination to SqlToolConfigSchema
Schema generation	toolConfigBuilder.ts → generateZodSchema()	Auto-add limit/offset params
SQL execution	toolFactory.ts → executeStatementWithParameters()	Append OFFSET/FETCH clause, apply limit+1
Output schema	toolConfigBuilder.ts → buildToolConfig()	Add hasMore/limit/offset to output
Response formatter	toolDefinitions.ts → sqlResponseFormatter()	Show pagination info
YAML validation	npm run validate	Validate pagination config

Context

• The MCP protocol does not support cursor-based pagination in tools/call responses (open proposal)
• Application-level offset/limit in input + hasMore in output is the standard pattern across production MCP servers
• IBM i Db2 natively supports OFFSET ? ROWS FETCH FIRST ? ROWS ONLY with bind variables