webbed_duck

What is webbed_duck?

webbed_duck is a self-contained data server that turns declarative TOML + SQL route definitions into live DuckDB-backed web endpoints.

• Each route lives in routes_src/ as a .toml metadata file paired with a .sql query (with optional .md documentation). The TOML + SQL sidecar pair is the single source of truth for a route.
• The compiler translates those sources into executable Python modules and registers them as HTTP endpoints.
• The runtime ships the results as styled HTML tables or cards, JSON payloads, CSV and Parquet downloads, or Arrow streams—no custom FastAPI or Flask code required.
• Drop TOML/SQL pairs into a folder, run the bundled CLI, and immediately browse or export the data surfaces.
• Designed for operational, quality, and manufacturing review workflows where trustworthy tables with traceability matter more than bespoke UI code.

Quick start

1. Install the package and dependencies.

   pip install webbed-duck
   

2. Create your route source directory (default is routes_src/) and add paired route files (see Authoring a route). Each route needs .toml for metadata and .sql for the query. Add .md when you want extra documentation. The repository ships routes_src/hello.toml + hello.sql as a living example of the canonical layout so new contributors have a starting point for the sidecar format.

   > Legacy .sql.md files: Convert each legacy file into a TOML/SQL pair once (for example, copy the metadata into .toml and the query body into .sql). After that conversion, edit only the TOML/SQL pair—the old markdown stub can be deleted or ignored.

3. Compile the contracts into runnable manifests (optional when auto-compile is enabled).

   webbed-duck compile --source routes_src --build routes_build
   

4. Launch the server. The CLI compiles using your configuration defaults and can hot-reload routes while you edit them.

   webbed-duck serve --config config.toml --watch
   

   • --watch keeps the compiler running and reloads routes in-place when .toml, .sql, or .md files change.
   • Pass --no-auto-compile to serve pre-built routes_build/ artifacts without touching the source tree.

• Watching performs filesystem polls once per second by default; disable it (or raise server.watch_interval) if you run on slower hardware where constant polling is undesirable.
• The watch interval is clamped to a minimum of 0.2 seconds at runtime so accidental --watch-interval 0 or extremely small values do not spin a tight loop.
  • File watching relies on timestamp and size fingerprints of matching route files, so ensure your editor writes changes to disk (saving partial files can trigger reload attempts). Network or synced file systems that coalesce timestamp updates may require a longer watch_interval.
  • The webbed-duck perf helper expects compiled routes in the build directory, uses PyArrow tables to compute latency statistics, and accepts repeated --param name=value overrides for the target route.

> FastAPI runtime dependency: The published wheel already includes fastapi and uvicorn. Treat them as core requirements for both development and production—removing them is only safe if you intentionally vendor webbed_duck inside another ASGI host and accept skipped HTTP coverage during tests. The pseudo-auth login endpoints and share workflows depend on FastAPI's request parsing, so uninstalling the web stack disables those flows entirely.

> Testing note: The integration tests exercise the FastAPI stack via fastapi.testclient. When you purposefully uninstall the server stack (for example, in a trimmed-down tooling image) those tests will skip and coverage drops for auth, overlays, and caching flows. Reinstall fastapi and uvicorn whenever you need full confidence in HTTP behaviour, especially before tagging a release.

5. Browse the routes. Open http://127.0.0.1:8000/hello (or your route path) in a browser, or request alternate formats with ?format=csv, ?format=parquet, etc.

Project structure

webbed_duck/
  core/            # compiler, executor, cache (no web)
  server/          # FastAPI & HTTP semantics
  plugins/         # adapters (charts, email, assets, auth, storage)
  routes_src/      # .toml + .sql (+ .md)
  routes_build/    # compiled artifacts imported in prod
  tests/           # unit, property, snapshot, integration
  perf/            # micro-benchmarks

Quality gates (what CI runs)

• ruff — fast linting and import hygiene (docs).
• mypy --strict webbed_duck/core/** — enforce types in core; gradually tighten elsewhere (docs).
• pytest — unit/integration/snapshot/property suites (Hypothesis included).
• pytest-benchmark — guardrails on cache hits and Parquet throughput (docs).
• vulture — remove dead code before it rots (repo).
• radon — monitor cyclomatic complexity budgets (docs).
• bandit — static security checks across server/adapters (docs).

How it works today (v0.4)

Runtime startup

• webbed-duck serve loads configuration from config.toml (defaults to host 127.0.0.1, port 8000, storage under ./storage) and resolves server.source_dir / server.build_dir.
• With server.auto_compile = true (the default) the CLI compiles every .toml + .sql pair in the configured source directory before starting Uvicorn.
• Enabling watch mode (server.watch = true or the --watch flag) keeps a background poller running so route edits trigger re-compilation and live reloading without restarting the process.
• Combine server.watch = true with a sensible server.watch_interval (defaults to 1.0 seconds) to balance responsiveness and CPU usage.
• The server is a FastAPI application exposed via Uvicorn. No additional framework integration is necessary for development deployments.

Route discovery and mapping

• The compiler scans the source tree for .toml files and requires a sibling .sql. Optional .md prose is bundled alongside the SQL body.
• Each TOML file carries the metadata that previously lived in frontmatter: route id, HTTP path, optional version, formats, [params], [cache], preprocessors, postprocessors, and display metadata. New fields such as cache_mode, returns, and declarative [[uses]] dependencies control execution and composition.
• Compiled artifacts are written to the --build directory, mirroring the source folder structure but with .py files. These contain serialised ROUTE dictionaries consumed at runtime.
• At boot—and after each live reload triggered by the watcher—the server imports every compiled module and registers the route path on the FastAPI app. The id doubles as the logical identifier for /routes/{id} helper endpoints.

Parameter binding

• Parameters are declared under [params] in the TOML file. Each entry can be a table (with type, required, default, description, and extra metadata) or a string shorthand when you only need to annotate the DuckDB type.
• Within the SQL body, use $name placeholders to bind DuckDB parameters. Parameters declared with template_only = true render via {{ name }} before query execution, letting you safely interpolate identifiers and literals without exposing them to the database engine.
• At request time the runtime reads query string values, validates types (including boolean coercion for true/false, 1/0), applies defaults, and rejects missing required parameters. Ephemeral controls like pagination and sorting stay out of [params] and are applied after the core relation is resolved.
• Additional runtime controls:
  • ?limit= and ?offset= apply post-query pagination without changing the SQL.
  • ?column= can be repeated to restrict returned columns.

Route dependencies and execution modes

• cache_mode in the TOML metadata (materialize, passthrough, or force_live) governs how the executor interacts with cached parquet artifacts when serving HTTP requests or answering internal calls.
• returns declares the default return style for internal callers: relation (Arrow table), parquet (materialized artifact), or error_frame (schema-compatible error payload).
• Each [[uses]] block defines an upstream dependency with an alias, the downstream route to call, an execution mode (relation or parquet_path), and an [uses.args] table for pass-through, renamed, or literal parameters. The executor resolves these dependencies before your SQL runs and registers them under the provided alias.
• When mode = "parquet_path", the executor materializes the upstream route (using its cache settings) and exposes a DuckDB view built from the parquet paths, enabling composable, cache-aware joins without additional Python code.

Incremental catch-up jobs

• webbed_duck.core.incremental.run_incremental iterates over a date range and records the last successful cursor value inside storage_root/runtime/checkpoints.duckdb. Subsequent runs only execute dates newer than the recorded checkpoint so you can schedule the helper daily without re-reading historical data.
• When the underlying route raises an error the checkpoint is left unchanged, ensuring the same date is retried on the next invocation after you address the failure.
• Pass a custom callable via the runner parameter when you need to wrap run_route (for example to inject observability) while keeping the checkpoint and pagination logic intact.

Request lifecycle and paged caches

flowchart LR
  %% Request lifecycle and paged caches

  A[Client request] --> B[Route handler]
  B --> C[Run preprocessors]
  C --> D{Cache lookup}
  D --&gt;|Hit| E[Load Parquet page<br>from storage_root/cache]
  D --&gt;|Miss| F[Stream DuckDB<br>record batches]
  F --&gt; G[Write paged<br>Parquet artifacts]
  G --&gt; E
  E --&gt; H[Apply overrides<br>and column filters]
  H --&gt; I[Post-process<br>to HTML / JSON / CSV / Arrow]
  I --&gt; J[Send response]

• Cache settings come from the [cache] table in each route's TOML metadata merged with the global [cache] section in config.toml.
• Routes must declare cache.order_by with one or more result columns; cache population validates the schema and every cache hit (including superset/shard reuse) re-sorts combined pages on those columns before applying offsets.
• The executor snaps requested offsets to cache-friendly pages when a route enforces a rows_per_page limit, ensuring subsequent requests reuse the same Parquet artifacts.
• Cache hits skip DuckDB entirely and hydrate the response from on-disk Parquet pages while still applying overrides, analytics, and post-processing.
• Transformation-invariant filters can be declared under [cache.invariant_filters] (with param, column, optional separator, and case_insensitive flags). The cache stores per-value page maps so a superset result can satisfy filtered requests, and disjoint shards (e.g., product_code=widget and product_code=gadget) can be combined when clients request multiple values within the cache TTL. When case_insensitive = true, both cached tokens and runtime filters are normalised to lowercase so callers may supply any casing without triggering a cache miss.
  • To request cached rows where an invariant column is NULL, submit the parameter with the literal token __null__ (the UI’s (null) option emits this value); leaving the field blank still means “all values”. > Testing reminder: Install DuckDB, PyArrow, FastAPI, and Uvicorn before running the pytest suite so cache and HTTP coverage execute instead of skipping.

Supported outputs

All of the following formats work today, provided the route either allows them explicitly or leaves allowed_formats empty (which enables everything):

Routes may set default_format in TOML to choose the response when ?format is omitted.

Arrow RPC endpoints mirror x-total-rows, x-offset, and x-limit headers, and every HTML view exposes a “Download this slice (Arrow)” link for parity.

?format=chart_js renders every [[charts]] specification as a Chart.js canvas. The server vendors Chart.js into storage_root/static/vendor/chartjs/ on first run and serves it from /vendor/chart.umd.min.js?v=4.4.3, falling back to the CDN source only if the vendored copy cannot be prepared. The Python package ships with Chart.js 4.4.3 under webbed_duck/static/chartjs/chart.umd.min.js, so projects with read-only storage can still serve the script directly from site-packages. Override the script URL (and layout details like canvas_height) via [chart_js] metadata or @postprocess chart_js overrides. When you need to set a global default for every route, declare [ui] chartjs_source = "https://cdn.example.com/chart.umd.min.js" in config.toml. Provide your own build by dropping a chart.umd.min.js into that directory (or set WEBDUCK_SKIP_CHARTJS_DOWNLOAD=1 to skip the automatic fetch). Air-gapped environments must pre-populate that folder (or mount it read-only with the asset in place); otherwise the runtime will return a 404 for /vendor/chart.umd.min.js and continue referencing the CDN fallback. Append ?embed=1 to receive a snippet that includes the vendor `