Endpoints Overview

Endpoints are the heart of flAPI. Each YAML file in the template.path directory defines exactly one endpoint — a REST endpoint, an MCP tool, an MCP resource, or an MCP prompt. This guide covers REST endpoints; see MCP below for the MCP variants.

What is an Endpoint?

A REST endpoint combines:

1. URL path — where clients reach the API (e.g., /customers/, /customers/:id).
2. SQL template — the Mustache-templated query that runs against your connection.
3. Configuration — request parameters, validation, caching, auth, rate limits.

File Structure

Every endpoint typically pairs a YAML file with a SQL file:

project/
├── flapi.yaml           # Main configuration
└── sqls/
    ├── customers.yaml   # Endpoint configuration
    ├── customers.sql    # SQL template
    ├── orders.yaml
    └── orders.sql

Minimal Endpoint

sqls/hello.yaml:

url-path: /hello/
method: GET
template-source: hello.sql
connection:
  - my-database

sqls/hello.sql:

SELECT 'Hello, World!' AS message

Start flAPI and GET /hello/ returns the row.

REST Endpoint Configuration

Key	Type	Default	Description
url-path	string	required	HTTP path, e.g. /customers/:id
method	string	GET	One of GET, POST, PUT, PATCH, DELETE
template-source	string	required	Path to a .sql Mustache template
connection	list[string]	required	Connection name(s) from flapi.yaml
with-pagination	bool	true	Wrap responses in {data, next, total_count} and accept limit/offset
request-fields-validation	bool	false	Reject requests containing parameters not declared under request:
request	list	–	Request parameters — see Parameters
auth	mapping	–	Per-endpoint authentication — see Authentication
rate-limit	mapping	–	Per-endpoint rate limit
cache	mapping	–	DuckLake caching config
operation	mapping	auto	Override read/write detection — see Write Operations
heartbeat	mapping	–	Endpoint heartbeat / health-check params

Complete REST Endpoint

url-path: /customers/:id
method: GET

request:
  - field-name: id
    field-in: path
    description: Customer ID
    required: true
    validators:
      - type: int
        min: 1

  - field-name: segment
    field-in: query
    description: Market segment
    required: false
    validators:
      - type: enum
        allowedValues: [AUTOMOBILE, BUILDING, FURNITURE, HOUSEHOLD, MACHINERY]

template-source: customers.sql
connection:
  - customers-parquet

with-pagination: true
request-fields-validation: false

auth:
  enabled: true
  type: basic
  users:
    - username: admin
      password: '${ADMIN_PASSWORD}'
      roles: [admin, read]

rate-limit:
  enabled: true
  max: 1000
  interval: 60   # seconds

cache:
  enabled: true
  table: customers_cache
  schema: analytics
  schedule: 5m
  primary-key: [id]
  cursor:
    column: registration_date
    type: date

URL Paths

Basic Paths

url-path: /customers/
url-path: /products/search/
url-path: /analytics/revenue/

Rules:

• Must start with /.
• Lowercase with hyphens (/product-categories/).
• Trailing slash is optional but conventional.

Path Parameters

flAPI uses colon prefixes, not curly braces:

url-path: /customers/:id

request:
  - field-name: id
    field-in: path
    required: true
    validators:
      - type: int
        min: 1

Examples:

• /customers/:id → params.id
• /orders/:order_id/items/:item_id → params.order_id, params.item_id

Request Parameters

request:
  - field-name: segment
    field-in: query
    validators:
      - type: enum
        allowedValues: [AUTOMOBILE, BUILDING]

  - field-name: id
    field-in: path
    required: true
    validators:
      - type: int
        min: 1

  - field-name: x_region
    field-in: header
    validators:
      - type: enum
        allowedValues: [US, EU, APAC]

  - field-name: name
    field-in: body
    required: true
    validators:
      - type: string
        min: 2
        max: 100

See Parameters for the four locations and Validation for the seven validator types.

SQL Templates

template-source: customers.sql

-- sqls/customers.sql
SELECT
  c_custkey    AS id,
  c_name       AS name,
  c_mktsegment AS segment
FROM customers
WHERE 1=1
{{#params.segment}}
  AND c_mktsegment = '{{{ params.segment }}}'
{{/params.segment}}
{{#params.id}}
  AND c_custkey = {{ params.id }}
{{/params.id}}

See SQL Templating for the full guide.

Data Connections

Single Connection

connection:
  - customers-parquet

Multiple Connections

flAPI attaches every listed connection to the DuckDB session, so you can join across sources:

connection:
  - bigquery-warehouse
  - customers-parquet

The first connection's properties are exposed as conn.* in the template:

SELECT *
FROM read_parquet('{{{ conn.path }}}')

Response Format

By default flAPI returns JSON wrapped in an envelope:

{
  "data": [
    { "id": 1, "name": "Customer A" },
    { "id": 2, "name": "Customer B" }
  ],
  "next": "/customers/?offset=100&limit=100",
  "total_count": 1532
}

Disable pagination to return the raw array, or use ?format=csv / ?format=arrow for other formats. See Response Format.

MCP Endpoints

flAPI exposes the same YAML configuration as three MCP shapes for AI agents. Each MCP endpoint type uses a different top-level key.

MCP Tool

A callable tool that runs an SQL query.

Key	Type	Default	Description
mcp-tool.name	string	required	Unique tool name (alphanumeric / underscore)
mcp-tool.description	string	required	Tool description for the LLM
mcp-tool.result-mime-type	string	application/json	MIME type returned to the model

mcp-tool:
  name: customer_lookup
  description: Retrieve customer information by ID or filter criteria
  result-mime-type: application/json

request:
  - field-name: id
    field-in: query
    required: false
    validators:
      - type: int
        min: 1

template-source: customers.sql
connection:
  - customers-parquet

Only name, description, and result-mime-type are recognised under mcp-tool:. Any other keys are ignored.

MCP Resource

A readable resource (e.g. a schema dump).

Key	Type	Default	Description
mcp-resource.name	string	required	Unique resource name
mcp-resource.description	string	required	Resource description
mcp-resource.mime-type	string	application/json	Content MIME type

mcp-resource:
  name: customer_schema
  description: Customer database schema and field definitions
  mime-type: application/json

template-source: schema-query.sql
connection:
  - customers-db

MCP Prompt

A reusable, parameterised Mustache prompt. Prompts do not require connection or template-source — the prompt body is inline.

Key	Type	Default	Description
mcp-prompt.name	string	required	Unique prompt name
mcp-prompt.description	string	required	Prompt description
mcp-prompt.template	string	required	Inline Mustache template
mcp-prompt.arguments	list[string]	[]	Template argument names

mcp-prompt:
  name: customer_analysis
  description: Generate a customer analysis prompt
  template: |
    You are a data analyst. Analyse this customer:
    {{#customer_id}}Customer ID: {{customer_id}}{{/customer_id}}
    {{#segment}}Segment: {{segment}}{{/segment}}

    Provide insights on purchasing patterns and recommendations.
  arguments:
    - customer_id
    - segment

Endpoint Discovery

$ flapii endpoints list

Available Endpoints
Path                  Method  Cached
/customers/           GET     Yes
/customers/:id        GET     Yes
/orders/              POST    No
/analytics/revenue/   GET     Yes

$ flapii endpoints describe /customers/

Endpoint:   /customers/
Method:     GET
Template:   sqls/customers.sql
Connection: customers-parquet
Cache:      Enabled (5m schedule)
Auth:       Required (basic)
Rate Limit: 1000 req/min

OpenAPI Documentation

flAPI auto-generates an OpenAPI document at:

GET /docs

Each description: and validator becomes part of the spec.

Common Patterns

Pagination

limit and offset are always accepted when with-pagination: true; declare them only when you want to constrain them.

with-pagination: true

request:
  - field-name: limit
    field-in: query
    validators:
      - type: int
        min: 1
        max: 1000

  - field-name: offset
    field-in: query
    validators:
      - type: int
        min: 0

Search

request:
  - field-name: search
    field-in: query
    validators:
      - type: string
        min: 3
        max: 100
        regex: '^[A-Za-z0-9 _-]+$'

SELECT * FROM products
WHERE name ILIKE '%{{{ params.search }}}%'

Date Ranges

request:
  - field-name: start_date
    field-in: query
    validators:
      - type: date

  - field-name: end_date
    field-in: query
    validators:
      - type: date

SELECT * FROM orders
WHERE order_date BETWEEN '{{{ params.start_date }}}' AND '{{{ params.end_date }}}'

Best Practices

1. Descriptive URL paths. /customers/by-segment/, not /api1/.
2. Document every parameter with description: — it shows up in OpenAPI.
3. Validate every input. See Validation.
4. Cache read-heavy endpoints with DuckLake.
5. Rate-limit per endpoint for spiky traffic.
6. One YAML per endpoint — keep them small and focused.

Troubleshooting

404 Endpoint not found

• File exists in sqls/?
• url-path matches request (slashes, colon-prefixes correct)?
• Server reloaded since the change?

Template error

• template-source resolves to a real file?
• Mustache braces balanced?
• Run flapii templates expand /endpoint/.

Connection error

• Connection name matches flapi.yaml?
• Connection initialised at server start?

Next Steps

• YAML Syntax — extended YAML, includes, env vars
• Parameters — query/path/header/body
• Validation — the seven validator types
• SQL Templating — Mustache patterns
• Response Format — pagination, CSV, Arrow
• Authentication — basic / JWT / bearer / OIDC
• Caching — DuckLake caching