# Run the flapi server (note: "flapi" is taken on PyPI, so the package is "flapi-io")
uvx --from flapi-io flapi -c flapi.yaml

# Run the flapii CLI client (also bundled in flapi-io)
uvx --from flapi-io flapii

pip install flapi-io   # installs both "flapi" and "flapii" commands

> docker pull ghcr.io/datazoode/flapi:latest

> docker run -it --rm -p 8080:8080 -p 8081:8081 -v $(pwd)/examples/:/config ghcr.io/datazoode/flapi -c /config/flapi.yaml

> docker run -it --rm -p 8080:8080 -p 8081:8081 -v $(pwd)/examples/:/config ghcr.io/datazoode/flapi -c /config/flapi.yaml --enable-mcp

mcp:
  enabled: true
  port: 8081
  # ... other MCP configuration

> curl 'http://localhost:8080/'

         ___
     ___( o)>   Welcome to
     \  http://localhost:8080/doc`](http://localhost:8080/doc)

You should see the familiar Swagger UI page:

![flAPI Swagger UI](https://i.imgur.com/HqjHMlA.png)

The raw yaml [Swagger 2.0](https://swagger.io/specification/) is also available at [`http://localhost:8080/doc.yaml`](http://localhost:8080/doc.yaml)

#### 3.3 Test the MCP server:
If MCP is enabled, you can test the MCP server as well:

## 🤖 MCP (Model Context Protocol) Support

flAPI now supports the **Model Context Protocol (MCP)** in a **unified configuration approach**. Every flAPI instance automatically runs both a REST API server and an MCP server concurrently, allowing you to create AI tools alongside your REST endpoints using the same configuration files and SQL templates.

### Key Features

- **MCP `2026-07-28` (dual-era)**: serves the latest stateless MCP revision (`server/discover`, per-request metadata, cacheable results, OAuth discovery via RFC 9728) **alongside** the legacy `initialize`/session protocol — existing clients keep working unchanged
- **Long-running tools (Tasks extension)**: mark a tool `async` and slow queries return a task handle immediately instead of blocking the connection; the durable task store survives a restart, with `tasks/get` / `tasks/cancel` and per-caller isolation
- **Typed, structured tool contracts**: tool parameters advertise real types and constraints (int ranges, dates, uuid, enum, …), results carry machine-readable `structuredContent`, an `outputSchema` is learned after first use, and failures return actionable `isError` results the model can self-correct from
- **Unified Configuration**: Single YAML files can define REST endpoints, MCP tools, and MCP resources
- **Automatic Detection**: Configuration type is determined by presence of `url-path` (REST), `mcp-tool` (MCP tool), or `mcp-resource` (MCP resource)
- **Shared Components**: MCP tools and resources use the same SQL templates, parameter validation, authentication, and caching as REST endpoints
- **Security Integration**: method authorization enforced on every request, per-tool/resource/prompt RBAC (`allowed-roles`), shadow/dry-run (`_dryRun`), response shaping, per-tool rate limiting, and a tool-description hygiene scanner
- **Tool Discovery**: automatic tool discovery, pagination, resource templates (`flapi://customers/{id}`), and `x-mcp-header` for per-tenant edge routing

See [docs/MCP_REFERENCE.md](docs/MCP_REFERENCE.md) — the dual-era model and all new capabilities are documented in §11.

### MCP Endpoints

- `POST /mcp/jsonrpc` - Main JSON-RPC endpoint for tool calls
- `GET /mcp/health` - Health check endpoint

### Unified Configuration

**MCP is now automatically enabled** - no separate configuration needed! Every flAPI instance runs both REST API and MCP servers concurrently.

Configuration files can define multiple entity types:

#### REST Endpoint + MCP Tool (Unified)

#### MCP Resource Only

### Using MCP Tools

Once MCP is enabled, you can interact with tools using JSON-RPC 2.0:

## 🎓 Example

Here's a simple example of how to create an API endpoint using flAPI:

### 1. Create a basic flAPI configuration
flAPI uses the popular [YAML](https://yaml.org/) format to configure the API endpoints. A basic configuration file looks like this:

After that ensure that the template path (`./sqls` in this example) exists.

### 1. Define your API endpoint (`./sqls/customers.yaml`):
Each endpoint is at least defined by a YAML file and a corresponding SQL template in the template path. For our example we will create the file `./sqls/customers.yaml`:

There are many more configuration options available, see the [full documentation](link-to-your-docs) for more details.

### 2. Configure the endpoints SQL template (`./sqls/customers.sql`):
After the creation of the YAML endpoint configuration we need to connect the SQL template which connects the enpoint to the data connection.
The template files use the [Mustache templating language](https://mustache.github.io/) to dynamically generate the SQL query.

The above template uses the `path` parameter defined in the connection configuration to directly query a local parquet file. If the id parameter is 
provided, it will be used to filter the results.

### 3. Send a request:
To test the endpoint and see if everything worked, we can use curl. We should also provide the correct basic auth credentials (`admin:secret` in this case). To make the JSON result easier to read, we pipe the output to [`jq`](https://jqlang.github.io/jq/).

## ⁉️ DuckLake-backed caching (current implementation)
flAPI uses the DuckDB DuckLake extension to provide modern, snapshot-based caching. You write the SQL to define the cached table, and flAPI manages schemas, snapshots, retention, scheduling, and audit logs.

### Quick start: Full refresh cache
1) Configure DuckLake globally (alias is `cache` by default):

2) Add cache block to your endpoint (no `primary-key`/`cursor` → full refresh):

3) Write the cache SQL template (CTAS):

4) Query from the cache in your main SQL:

Notes:
- The cache schema (`cache.analytics`) is created automatically if missing.
- Regular GET requests never refresh the cache. Refreshes happen on warmup, on schedule, or via the manual API.
- **Data Inlining**: When `data-inlining-row-limit` is configured, small cache changes (≤ specified row limit) are written directly to DuckLake metadata instead of creating separate Parquet files. This improves performance for small incremental updates.

#### Data inlining (optional, for small changes)

DuckLake supports writing very small inserts directly into the metadata catalog instead of creating a Parquet file for every micro-batch. This is called "Data Inlining" and can significantly speed up small, frequent updates.

- **Enable globally**: configure once under the top-level `ducklake` block:

- **Behavior**:
  - Inserts with rows ≤ `data-inlining-row-limit` are inlined into the catalog metadata.
  - Larger inserts automatically fall back to normal Parquet file writes.
  - Inlining applies to all caches (global setting), no per-endpoint toggle.

- **Manual flush (optional)**: you can flush inlined data to Parquet files at any time using DuckLake’s function. Assuming your DuckLake alias is `cache`:

- **Notes**:
  - This feature is provided by DuckLake and is currently marked experimental upstream. See the DuckLake docs for details: [Data Inlining](https://ducklake.select/docs/stable/duckdb/advanced_features/data_inlining.html).
  - If you don’t set `data_inlining_row_limit`, flAPI won’t enable inlining and DuckLake will use regular Parquet writes.

### Advanced: Incremental append and merge
The engine infers sync mode from your YAML:
- No `primary-key`, no `cursor` → full refresh (CTAS)
- With `cursor` only → incremental append
- With `primary-key` + `cursor` → incremental merge (upsert)

Example YAMLs:

Cache template variables available to your SQL:
- `{{cache.catalog}}`, `{{cache.schema}}`, `{{cache.table}}`, `{{cache.schedule}}`
- `{{cache.snapshotId}}`, `{{cache.snapshotTimestamp}}` (current)
- `{{cache.previousSnapshotId}}`, `{{cache.previousSnapshotTimestamp}}` (previous)
- `{{cache.cursorColumn}}`, `{{cache.cursorType}}`
- `{{cache.primaryKeys}}`
- `{{params.cacheMode}}` is available with values `full`, `append`, or `merge`

Incremental append example:

Incremental merge example:

### When does the cache refresh?
- Startup warmup: flAPI refreshes caches for endpoints with cache enabled.
- Scheduled refresh: controlled by `cache.schedule` on each endpoint (e.g., `5m`).
- Manual refresh: call the refresh API (see below).
- Regular GET requests do not refresh the cache.

### Audit, retention, compaction, and control APIs
flAPI maintains an audit table inside DuckLake at `cache.audit.sync_events` and provides control endpoints:

- Manual refresh:

- Audit logs (endpoint-specific and global):

- Garbage collection (retention):
Retention can be configured per endpoint under `cache.retention`:

The system applies retention after each refresh and you can also trigger GC manually:

- Compaction:
If enabled in global `ducklake.scheduler`, periodic file merging is performed via DuckLake `ducklake_merge_adjacent_files`.

### Template authoring guide (reference)
Use these variables inside your cache templates and main queries:

- Identification
  - `{{cache.catalog}}` → usually `cache`
  - `{{cache.schema}}` → e.g., `analytics` (auto-created if missing)
  - `{{cache.table}}` → your cache table name

- Mode and scheduling
  - `{{params.cacheMode}}` → `full` | `append` | `merge`
  - `{{cache.schedule}}` → if set in YAML

- Snapshots
  - `{{cache.snapshotId}}`, `{{cache.snapshotTimestamp}}`
  - `{{cache.previousSnapshotId}}`, `{{cache.previousSnapshotTimestamp}}`

- Incremental hints
  - `{{cache.cursorColumn}}`, `{{cache.cursorType}}`
  - `{{cache.primaryKeys}}` → comma-separated list, e.g., `id,tenant_id`

Authoring tips:
- Full refresh: use `CREATE OR REPLACE TABLE ... AS SELECT ...`.
- Append: `INSERT INTO cache.table SELECT ... WHERE event_time > previousSnapshotTimestamp`.
- Merge: `MERGE INTO cache.table USING (SELECT ...) ON pk ...`.
- Do not create schemas in templates; flAPI does that automatically.

### Troubleshooting
- Cache refresh happens on every request: by design this is disabled. Ensure you’re not calling the manual refresh endpoint from a client and that your logs show scheduled or warmup refreshes only.
- Schema not found: verify `cache.schema` is set; flAPI will auto-create it.
- Retention errors: use time-based `max-snapshot-age` first. Version-based retention depends on DuckLake support.

## 🧩 YAML includes and environment variables
flAPI extends plain YAML with lightweight include and environment-variable features so you can keep configurations modular and environment-aware.

### Environment variables
- Write environment variables as `{{env.VAR_NAME}}` anywhere in your YAML.
- Only variables that match the whitelist in your root config are substituted:

- If the whitelist is empty or omitted, all environment variables are allowed.

Examples:

### Include syntax
You can splice content from another YAML file directly into the current document.

- Basic include: `{{include from path/to/file.yaml}}`
- Section include: `{{include:top_level_key from path/to/file.yaml}}` includes only that key
- Conditional include: append `if ` to either form

Conditions supported:
- `true` or `false`
- `env.VAR_NAME` (include if the variable exists and is non-empty)
- `!env.VAR_NAME` (include if the variable is missing or empty)

Examples:

Resolution rules and behavior:
- Paths are resolved relative to the current file first; absolute paths are supported.
- Includes inside YAML comments are ignored (e.g., lines starting with `#`).
- Includes are expanded before the YAML is parsed.
- Includes do not recurse: include directives within included files are not processed further.
- Circular includes are guarded against within a single expansion pass; avoid cycles.

Tips:
- Prefer section includes (`{{include:...}}`) to avoid unintentionally overwriting unrelated keys.
- Keep shared blocks in small files (e.g., `connections.yaml`, `auth.yaml`) and include them where needed.

## 📦 Self-packaging (single-binary deploy)

The same `flapi` binary that serves the API can fold an entire
config tree into itself, producing one self-contained executable
deployable via `scp`.

**How it works:** a ZIP archive is appended after the executable on
Linux/Windows (or written into a pre-allocated `__FLAPI/__bundle`
Mach-O segment on macOS, then re-`codesign`-ed so the result is
notarisable). At startup, flAPI reverse-scans for the bundle (or
probes the segment on macOS) and registers an
`EmbeddedArchiveFileProvider` plus an `embed://` DuckDB
filesystem so config / SQL templates / `read_csv()` calls all
resolve to the in-memory bundle. If no bundle is present
(unbundled binary, truncated tail), all paths fall back to the
local filesystem unchanged — existing operators see no behaviour
change.

**Secrets stay out of the bundle.** `flapi pack` refuses files
matching `*.env`, `secrets/*`, `*.pem`, `*.key` by default. The
override (`--allow-secrets`) is for testing only. Credentials
come from the environment at runtime (`AWS_*`, `GOOGLE_*`,
`AZURE_*`, `FLAPI_CONFIG_SERVICE_TOKEN`, `{{env.VAR}}` YAML
interpolation).

**Reproducible builds.** Set `SOURCE_DATE_EPOCH` before
`flapi pack` and the output is byte-identical across runs:

**12-factor env vars.** `FLAPI_CONFIG` falls back for `-c` /
`--config`; `FLAPI_LOG_LEVEL` falls back for `--log-level`. CLI
flag wins over env var wins over built-in default. Invalid log
levels exit non-zero with a single-line error.

**macOS notes.** The reserved-segment size is 16 MiB by default
(knob `FLAPI_RESERVED_BUNDLE_MIB` at CMake configure time);
oversized bundles exit non-zero with a corrective error. A
`--macos-append` flag is available for local debugging — it uses
the Linux/Windows append-after-EOF layout but the result is
intentionally not notarisable.

See [docs/CLI_REFERENCE.md §3](docs/CLI_REFERENCE.md#3-self-packaging-subcommands)
and [docs/spec/DESIGN_DECISIONS.md §9](docs/spec/DESIGN_DECISIONS.md#9-self-packaging-via-appended-zip)
for full reference + rationale.

## 🏭 Building from source
The source code of flAPI is written in C++ and closely resembles the [DuckDB build process](https://duckdb.org/docs/dev/building/overview). A good documentation of the build process is the GitHub action in [`build.yaml`](.github/workflows/build.yaml). In essecence a few prerequisites need to be met:
In essecence a few prerequisites need to be met:

- Install the dependencies: `sudo apt-get install -y build-essential cmake ninja-build`
- Checkout the repository and submodules: `git clone --recurse-submodules https://github.com/datazoode/flapi.git`
- Build the project: `make release`

The build process will download and build DuckDB v1.1.2 and install the vcpkg package manager. We depend on the following vcpkg ports:

- [`argparse`](https://github.com/p-ranav/argparse) - Command line argument parser
- [`crow`](https://github.com/CrowCpp/Crow) - Our REST-Web framework and JSON handling
- [`yaml-cpp`](https://github.com/jbeder/yaml-cpp) - YAML parser
- [`jwt-cpp`](https://github.com/Thalhammer/jwt-cpp) - JSON Web Token library
- [`openssl`](https://github.com/openssl/openssl) - Crypto library
- [`catch2`](https://github.com/catchorg/Catch2) - Testing framework

**Note**: MCP support is built-in and doesn't require additional dependencies beyond what's already included.

## 📚 Documentation

For more detailed information, check out our [full documentation](docs/):

- **[Reference Documentation Map](docs/REFERENCE_MAP.md)** - Quick navigation guide for all docs
- **[Configuration Reference](docs/CONFIG_REFERENCE.md)** - Complete flapi.yaml configuration options
- **[MCP Reference](docs/MCP_REFERENCE.md)** - Model Context Protocol specification and implementation
- **[Config Service API Reference](docs/CONFIG_SERVICE_API_REFERENCE.md)** - REST API for runtime configuration
- **[CLI Reference](docs/CLI_REFERENCE.md)** - Server executable command-line options
- **[Cloud Storage Guide](docs/CLOUD_STORAGE_GUIDE.md)** - Using cloud storage backends (S3, GCS, Azure)
- **[Architecture & Design](docs/spec/)** - System architecture, design decisions, and component documentation

### MCP Registry

flAPI is listed in the official [MCP Registry](https://registry.modelcontextprotocol.io) under the name below (this line also serves as the registry's PyPI ownership marker):

mcp-name: io.github.datazoode/flapi

## 📊 Telemetry

flAPI sends anonymous `application_start` and `application_stop` events to help the team understand adoption. No query data, credentials, or personal information is ever sent.

**Opt out** (any one of these is sufficient):

See [CLI Reference](docs/CLI_REFERENCE.md#disable-telemetry---no-telemetry) and [Configuration Reference](docs/CONFIG_REFERENCE.md) for full details.

## 🤝 Contributing

We welcome contributions! Please see our [Contributing Guide](CONTRIBUTING.md) for more details.

## 📄 License

flAPI is licensed under the [Business Source License (BSL) Version 1.1](./LICENSE). The BSL is a source-available license that gives you the following permissions:

### Allowed
1. **Copy, modify, and create derivative works**: You can copy the software, modify it, and create derivative works.
2. **Redistribute and non-production use**: Redistribution and non-production use of the software is permitted.
3. **Limited production use**: You can use flAPI in production, but with one restriction (see below).
4. **Change License rights**: After the Change Date (five years from first publication of the Licensed Work), the software automatically becomes available under the Change License (MPL 2.0).

### Not allowed
1. **Offering to third parties on a hosted or embedded basis**: The Additional Use Grant explicitly restricts using the software in a way that offers it to third parties as a hosted service or embedded component. If you want to do that, you need a commercial license.
2. **Violation of current license requirements**: If your use does not comply with the BSL, you must either purchase a commercial license or stop using flAPI.
3. **Trademark usage**: You do not have rights to the flAPI or DataZoo trademarks or logos, except as expressly required by the License.

For commercial licensing — embedding flAPI in a product, offering it as a hosted service, or any redistribution that the Additional Use Grant restricts — contact [contact@data-zoo.de](mailto:contact@data-zoo.de).

### Licensing parameters
- **Licensor**: DataZoo GmbH
- **Licensed Work**: flAPI — SQL-to-API framework
- **Change Date**: five years from the first publication of each version
- **Change License**: MPL 2.0

See the [LICENSE](./LICENSE) file for the full text.

## 🙋‍♀️ Support

If you have any questions or need help, please [open an issue](https://github.com/yourusername/flapi/issues) or join our [community chat](link-to-your-chat).

---

## Feedback

If flAPI misbehaves — an endpoint that will not serve, a cache that will not invalidate,
an auth flow that will not complete — please
[open an issue](https://github.com/DataZooDE/flapi/issues). Deployments differ in ways we
cannot reproduce here, so a report with your config is the fastest path to a fix. Every
JSON error response carries a `report_issue` link for exactly this reason.

If it saved you time, a star on the repo helps other people find it.

On an interactive start, a small banner says the same thing once a day. Under a container
or systemd there is no terminal, so it never prints — the startup log line carries the
pointer instead. Silence both with `DATAZOO_NO_BANNER=1`.