# Backlog Work that has been discussed but not built, in priority order (highest first). This is a planning document, not a promise - items move, merge, or drop as the project's needs become clearer. See [CHANGELOG.md](CHANGELOG.md) for what has already shipped. Each item has an **Impact** (why it matters for a production-grade, multi-user deployment) and, where the sizing isn't obvious, a **Notes** line on approach and what it can reuse from the existing code. ## Milestone: 1.0 1.0 is a promise, not a feature count. It freezes the surfaces CHANGELOG.md's "Versioning and compatibility" section already lists: REST endpoints and response shapes, CLI commands and flags, environment variables, the on-disk storage format, API-key/role grant fields, the saved-query definition format, and the MCP tool contract. After 1.0, breaking any of them needs 2.0. The aim is to make the breaking changes that are cheap now *before* the freeze, and to give anything still moving an explicit "experimental" status so it isn't frozen by accident. **Ship before the freeze** (breaking changes or contract decisions, cheap now, expensive after): | Item | Why before 1.0 | |---|---| | ~~#61 One governed core for every front door~~ (shipped) | Applying rate limits to MCP calls, and accepting JWT there, changes MCP behaviour | | ~~#62 Ad-hoc SQL runs in history and events~~ (shipped) | Touches the history schema and `GET /history` response shapes | | ~~#59 Dedicated event log (*storage decision only*)~~ (decided: [ADR 0002](documentation/adr/0002-event-ids.md)) | Decides where event ids come from; the full event log can ship later | | ~~#69 Consistent error format~~ (shipped) | `detail` just changed in 0.12.0; settle the error shape once | | ~~#70 Decide 1.0's deployment shape~~ (decided: several instances supported, #55-#58 shipped) | Single instance + PostgreSQL store, or multi-instance (#55-#57); don't promise it implicitly | | ~~#74 Respect a saved query's own `LIMIT`~~ (shipped) | A behaviour change to `/q` and `/execute_sql` results; better made before results are a frozen contract | | ~~#72 Management API v1 (`/api/v1`)~~ (shipped; legacy routes removed) | Otherwise 1.x has to carry `/list_files`, `/save_sql_to_file` and the other legacy management routes as its contract | **Needed for 1.0 to be a credible promise:** | Item | What | |---|---| | ~~#64 Experimental features label~~ (shipped) | A documented way to ship outside the freeze; live events are the first candidate | | ~~#65 Upgrade guarantee and upgrade CI~~ (shipped) | Every 0.x store upgrades automatically or refuses with a clear message | | ~~#66 Database support matrix~~ (shipped) | Tier 1 vs experimental database types | | ~~#67 Deprecation policy~~ (shipped) | How long a 1.x deprecation lives before 2.0 removes it | | ~~#68 Supported Python versions~~ (shipped: 3.11+) | Raise the floor from 3.9 (end of life since October 2025) | | ~~#71 PostgreSQL-store backup and restore~~ (shipped) | Documented and tested; DEPLOYMENT.md §5 covers only the SQLite volume | | ~~#84 Admin identity, Phase 1~~ (shipped) | Named administrators, admin roles and audit by person change the management API's permission model and the audit record - cheap now, breaking after | **Additive, can land after 1.0:** #63 column masking (new grant fields), #60 CDC, webhook delivery from #59's log, #100 a Helm chart (plain manifests are in `deploy/kubernetes/`), OIDC discovery beyond the current JWKS support. **More sources, after 1.0** (each starts experimental, #64): #75 files and data lakes through DuckDB (shipped), #76 Trino (shipped), #77 cloud warehouses, #78 MongoDB aggregation pipelines, #79 Elasticsearch and Cypher, #80 stored procedures. **More from DuckDB:** #81 Parquet and Arrow output, #82 snapshots of slow queries, #83 Iceberg and Delta tables (federation considered and deferred, see #83). **Partners and pipelines, after 1.0:** #89 exports to object storage, incremental - the clock stays with cron, systemd or Kubernetes. **AI agents:** #85 OAuth 2.1 for the MCP server (first - it opens hosted AI clients), #63 column masking, #86 descriptions for agents, #87 per-agent usage, #88 write tools with confirmation. **Enterprise operations:** #90 rotating the encryption key and #91 reissuing API keys (first - both add to the store format and the API, cheaper before the freeze), #92 tracing, #93 security headers, #94 per-key limits, #95 quotas, #96 a tamper-evident audit stream, #97 configuration as code, #98 secrets managers; #100 a Helm chart and #101 compliance documentation. #99 workspaces only if asked for. **Suggested sequence:** 1. **0.13:** #61, #62, #69, #65, plus the #59 storage decision. (Released as 0.13.0 on 2026-10-04.) 2. **0.14:** #64, #66, #67, #68, #71 and the how-to guides. (Released as 0.14.0 on 2026-10-04.) 3. **0.15:** #70 decided - several instances supported - and delivered by #55-#58; #75 files through DuckDB; what 0.14 deprecated, removed. (Released as 0.15.0 on 2026-10-04.) 4. **0.16: #84 Phase 1**, admin identity - the last contract change before the freeze; with #81 Parquet output and automatic views over files. (Released as 0.16.0 on 2026-10-06.) 5. **0.17: #89 exports to object storage and #63 column masking**, both experimental - delivering data to partners and pipelines. (Released as 0.17.0 on 2026-10-11.) 6. **0.18: #85 OAuth for the MCP server, #86 descriptions for agents and #87 per-agent usage**, all experimental - letting AI agents near your data, governed ([ADR 0006](documentation/adr/0006-ai-agents.md)). (Released as 0.18.0 on 2026-10-11.) 7. **0.19: #84 Phase 2**, Console sign-in through the organisation's identity provider, experimental ([ADR 0007](documentation/adr/0007-console-sign-in-through-sso.md)). (Released as 0.19.0 on 2026-10-11; 0.19.1 fixed the DuckDB minimum.) 8. **0.20: consolidation** - alerts, remote files, exports and column masking graduate, on terms written into the versioning policy (`experimental.GRADUATION`); CI against real S3, Keycloak and the oldest dependencies allowed. (Released as 0.20.0 on 2026-10-11; 0.20.1 refused writes over MCP called by name.) 9. **0.21: #88 write tools over MCP, each write approved by a person**, experimental ([ADR 0008](documentation/adr/0008-mcp-write-tools-with-approval.md)). (Released as 0.21.0 on 2026-10-11.) 10. **0.22: #90 rotating the encryption key and #91 reissuing an API key's secret** ([ADR 0009](documentation/adr/0009-key-rotation.md)). (Released as 0.22.0 on 2026-10-11.) 11. **0.23: #92 OpenTelemetry tracing and #93 security headers** ([ADR 0010](documentation/adr/0010-tracing-and-security-headers.md)). (Released as 0.23.0 on 2026-10-11; 0.22.1 before it made cached responses private.) 12. **0.24: #94 per-key limits on simultaneous queries, time and rows, then #95 quotas** ([ADR 0011](documentation/adr/0011-per-key-limits-and-quotas.md)). (Released as 0.24.0 on 2026-10-11.) 13. **0.25: #96 a tamper-evident audit log, streamed to a SIEM** ([ADR 0012](documentation/adr/0012-tamper-evident-audit-log-and-siem.md)). (Released as 0.25.0 on 2026-10-12.) 14. **0.26: #100 a Helm chart** ([ADR 0013](documentation/adr/0013-helm-chart.md)). (Released as 0.26.0 on 2026-10-12.) 15. **0.27: #97 configuration as code** ([ADR 0014](documentation/adr/0014-configuration-as-code.md)). (Released as 0.27.0 on 2026-10-12.) 16. **0.28: #98 secrets managers and short-lived credentials** ([ADR 0015](documentation/adr/0015-secrets-managers-and-short-lived-credentials.md)). (Released as 0.28.0 on 2026-10-12.) 17. **0.29: #101 where data goes, and FIPS** ([ADR 0016](documentation/adr/0016-data-flows-and-fips.md)). (Released as 0.29.0 on 2026-10-12.) 18. **0.30: eight features graduate**, after a security review each ([ADR 0017](documentation/adr/0017-what-graduates-for-1-0-0-rc1.md)). (0.30.0, 2026-10-13.) 19. **0.31: signed releases**, and what they're built from pinned ([ADR 0018](documentation/adr/0018-signed-releases.md)). (0.31.0, 2026-10-13.) 20. **0.32: the SIEM stream against real receivers**, and stable ([ADR 0019](documentation/adr/0019-the-siem-stream-against-real-receivers.md)). (0.32.0, 2026-10-13.) 21. **0.33: #59 the event log**, live events' own table, retention, filters and payload ([ADR 0020](documentation/adr/0020-the-event-log.md)). (0.33.0, 2026-10-13.) 22. **0.34: live events, and secrets from files and Vault, graduate**, after a security review each ([ADR 0021](documentation/adr/0021-live-events-and-secrets-graduate.md)). AWS's stay experimental. (0.34.0, 2026-10-13.) 0.34.1 and 0.34.2 fixed three MongoDB holes the review of what's left found. 23. **0.35: MongoDB against a real server in CI**, and at `pymongo`'s floor; forgetting a person waits for a usage write in flight. (0.35.0, 2026-10-14.) 0.35.1 closed an `allowed_tables` bypass through a CTE and a one-request ReDoS, found by review, CodeQL and fuzzing, and brought Scorecard to 7.8. (0.35.1, 2026-10-15.) 24. **0.36: `require_claim_binding`**, a role for customer-facing APIs - its signed-in users run only queries that bind their token, never a connection; the H2 driver from Maven Central or a mirror. (0.36.0, 2026-10-16.) 25. **0.37: MongoDB graduates**, with streaming exports - the one essential tier-1 feature it lacked (ADR 0022). (0.37.0, 2026-10-16.) 26. **0.38: #99 workspaces** (experimental), delegated administration for teams on one server ([ADR 0023](documentation/adr/0023-workspaces.md)) - and a sign-in page for the Console. Not a rolling upgrade. (0.38.0, 2026-10-16.) 27. **0.39: Apache-2.0** - open source, from FSL-1.1-MIT; the code unchanged. (0.39.0, 2026-10-17.) 28. **0.40: MCP for agents** - a dedicated MCP Console screen; per-query MCP exposure and query-level masking; self-describing tool parameters, a `describe_table` tool, capped-result guidance, and semantic search (experimental, `embed` + pgvector, [ADR 0025](documentation/adr/0025-semantic-retrieval.md)). (0.40.0, 2026-10-18.) 29. **0.41: Trino, and Console access insights** - Trino as a native connection type (experimental, [ADR 0026](documentation/adr/0026-trino-connections.md)); plus a per-key details screen and 30-day activity sparklines across the access screens (API keys, connections, cached queries, MCP tools, roles). (0.41.0, 2026-10-20.) 30. **0.42: Performance** - masked and table-restricted calls as fast as plain ones, fewer store reads per call, an opt-in metadata cache (`QUERYAPIGATE_METADATA_CACHE_TTL`), a pool sized to the threads; measured in [Performance, v3](documentation/PERFORMANCE_V3.md) with a published benchmark harness. (0.42.0, 2026-10-24.) 31. **0.43: Governance fixes, fewer round trips** - schema-qualified `allowed_tables`, unverifiable statements refused for table-restricted keys, masking and allow-list fixes; PostgreSQL in one round trip per query, MySQL in two without the GIL-holding ping ([ADR 0027](documentation/adr/0027-fewer-round-trips-per-query.md)); masking's schema refreshed in the background; sqlglot 30; `QUERYAPIGATE_THREADS`. Measured in [Performance, v4](documentation/PERFORMANCE_V4.md). (0.43.0, 2026-10-27.) 32. **0.44: A Rust data plane for `/q/`** (#102, experimental) - a prototype in front of the Python server that serves saved-query reads from Python's plans and forwards everything else, gated by a differential test against Python and decided go: 3.2-9.4x the calls a second per gateway core on lookups, about 8x on the same 8 cores ([ADR 0028](documentation/adr/0028-rust-data-plane-prototype.md), [Performance, v5](documentation/PERFORMANCE_V5.md)). Built from the repository, not yet in the image or the chart; run history writes only the rows it keeps. (0.44.0, 2026-10-28.) 33. **0.45: An optional ClickHouse run store** (#103, experimental) - run history, the event log and usage rollups in ClickHouse, the data plane writing its own runs: every run recorded at 20,000 a second and at the 64-caller peak, where 0.44's store kept about half, in about 13 bytes a run and with 11% fewer gateway cores; usage reports exact ([ADR 0029](documentation/adr/0029-clickhouse-run-store.md)). (0.45.0, 2026-10-28.) 34. **0.46: The run store's security review** (ADR 0029) - event frames that a stored row can't split, a data plane that writes only to the run store Python names, ad-hoc statements kept as hashes within the retention settings, `forget` reaching runs still in flight; schema version 2, migrated at the first start; a replay of the whole log twice as fast at 250 million runs. (0.46.0, 2026-10-29.) 35. **0.47: The data plane ships** in the image and the Helm chart (experimental, [ADR 0030](documentation/adr/0030-shipping-the-data-plane.md)) - one image with both, a second container sharing the pod's network, behind trusted proxies, paired within a release; the image's data plane measured as fast as the from-source one. And the MCP server refuses remote clients in open-access mode, as REST does. (0.47.0, 2026-10-30.) 36. **0.48: The data plane serves limited keys** (experimental) - with Redis, rate-limited keys and `QUERYAPIGATE_RATE_LIMIT`, counted in Python's own buckets, 4.4x Python's calls a second per core ([ADR 0032](documentation/adr/0032-dataplane-rate-limits.md)); and a key's own `query_timeout` and `max_rows`. The image's Python is python-build-standalone's 3.13, with no `pip`: 6-9% more calls a second where Python serves them ([ADR 0031](documentation/adr/0031-the-image-s-python.md)). (0.48.0, 2026-11-01.) 37. **0.49: The run store graduates** ([ADR 0029](documentation/adr/0029-clickhouse-run-store.md)) - stable, with upgrade fixtures from 0.45.0 to 0.48.0, a rolling upgrade with it on and its limits written down; and masking's `hash` on PostgreSQL `bytea` hashes the value, not a memory address. (0.49.0, 2026-11-04.) 38. **0.50: Tracing graduates, and Trino reaches the image** ([ADR 0010](documentation/adr/0010-tracing-and-security-headers.md)) - tracing stable, its database spans named as OpenTelemetry's conventions say; the `trino` client in the Docker image and a lakehouse example (#76); and a workspace's owner no longer opens files on the server through a connection. (0.50.0, 2026-11-06.) 0.50.1 (2026-11-09) reads files over TLS again with DuckDB 2.0 - `https://` files and real S3, GCS and R2 buckets. 39. **1.0.0-rc1:** freeze; invite external users to upgrade real stores and report back. AWS against AWS graduates `aws_secrets` whenever an account to test it in exists - it doesn't hold up the freeze. 40. **1.0.0.** ## 1. Per-key API permissions **Status: shipped** (first version - connections + write access; see below for what's still open). `QUERYAPIGATE_API_KEY` stays a full-access admin key, unchanged. Scoped keys (`POST /api_keys`, admin only) are each limited to a list of connection names (or every connection) and can be denied write access even when the server otherwise allows it (`allow_writes` per key narrows `QUERYAPIGATE_ALLOW_WRITES`, never widens it). Only the admin key manages connections, saved queries or other API keys - a scoped key can only run queries. A key's secret is never stored, only its SHA-256 hash; the server generates and shows it once. Verified: connection scoping, the write-access ceiling in both directions, admin-only management, the open-server bootstrap path (and its startup warning when it would lock configuration out), key update/revoke, and that the stored file never contains a plaintext secret. Managing keys from `/ui` (create/edit/revoke, with the one-time secret reveal) shipped too - see #7. **Per-saved-query access also shipped**: a key's `queries` grant (a list of saved-query names, or `"*"`) is independent of and additive with `connections` - it needed to be additive, not a narrower filter inside `connections`, once the actual use case turned out to be external clients with *no* connection access at all, just a curated handful of named queries (`monthly_revenue` and similar), never ad-hoc SQL. Also closed a pre-existing gap this surfaced: `/openapi.json`/`/docs` previously showed every saved query's name, description and parameters to any authenticated key regardless of its own scope - the catalogue now reflects what a key can actually reach. See [Per-saved-query access](documentation/API.md#per-saved-query-access-external-clients). Further permission-granularity ideas that build on this - per-query write curation, per-key rate limits, key expiry, IP allowlisting, per-key usage visibility - are their own items, #14-#18 below. ## 2. Observability: structured logs, request IDs, slow-query log, `/metrics` **Status: shipped.** Every response carries `X-Request-Id`; log lines from handling that request carry the same ID and the calling key's name (`admin`, a scoped key's own name, or `-`). `QUERYAPIGATE_JSON_LOGS=1` switches to structured (one JSON object per line) logs. Queries at or past `QUERYAPIGATE_SLOW_QUERY_THRESHOLD` (default 1s) are logged as warnings. `GET /metrics` serves Prometheus text-format request/query counts (also by the calling key) and latency histograms (by endpoint/status and by connection/dialect, not by key, so the number of keys never multiplies the bucketed output), idle pool occupancy, and rate-limit rejections - in-process counters, correct for the single-worker image this project ships, documented as not carrying over to a multi-process deployment. ## 3. Streaming exports **Status: shipped.** `?stream=true` on `POST /execute_sql` and `GET`/`POST /q/` (csv/tsv/ndjson), always read-only. Went further than "stream from the cursor" alone: MySQL (unbuffered cursor), PostgreSQL (named/server-side cursor) and ClickHouse (`execute_iter`) get real constant-memory streaming from the database itself, not just a cap removed - verified against real servers with 1M rows and the actual server process's memory sampled throughout the download (flat, not growing). SQLite/H2/generic `jdbc`/DuckDB still bound *this project's* memory per batch even though their own engine/driver may hold more internally (documented per-dialect from what was actually measured, not assumed - see `runners.py`). The "care around query-timeout and connection-pooling" this item called out for was real: a PostgreSQL named cursor's `execute()` turns out to be a `DECLARE CURSOR` that doesn't run the query at all, so column info and `statement_timeout` cancellation are only available after the first fetch (the reverse of every other driver); and the pool already holds a connection for a request's full duration, which for a streamed export can now be a long time - documented as an operational consideration, not a new mechanism, since the existing checkout/release design already handles it correctly once a generator's lifetime is tied to it. See [Streaming exports](documentation/API.md#streaming-exports). ## 4. Generalise database support via JDBC **Status: shipped.** `db: "jdbc"` reaches any JDBC-compliant database - Oracle, SQL Server, DB2, Snowflake and others - by generalising `_H2`'s embedded-JVM pattern: `jar`, `driver_class` and `jdbc_url` replace `host`/`port`/`database`, and pooling, the SQL guard, parameter binding and formats are unchanged. Two things turned out not to be quite "only connect() differs", both handled and documented rather than papered over: pagination (`LIMIT`/`OFFSET` is not portable SQL - a generic jdbc connection now paginates client-side instead, correct for any vendor, just not server-side-optimal) and the JVM's classpath, which is fixed at first startup - every jar from every currently-configured H2/jdbc connection is loaded on whichever one connects first, so only adding a *new* jdbc connection to an already-running server needs a restart. Two further limits are inherent to one JVM per process and documented, not fixed: no native query-timeout cancellation, and no schema introspection (`GET /connections//schema` answers 400 for this type). Verified end-to-end against a real H2 server reached through the *generic* driver (not the dedicated `h2` one) - proving the driver itself, not just the idea of it, works against a real JDBC database - plus unit tests for the classpath-union logic with distinct fake vendor jars. ## 5. Schema browser endpoint **Status: shipped.** `GET /connections//schema` lists a connection's tables and views with their columns (name, type, nullability, position), for every supported database. Verified against real MySQL, PostgreSQL, ClickHouse, SQLite and H2 servers, including views and an empty (table-free) database. ## 6. Response caching **Status: shipped.** Opt-in `cache_ttl` (seconds) per saved query; a hit answers with `X-Cache: HIT`, the identical body, `ETag` and `Cache-Control: max-age=`, and honours `If-None-Match` with a bodyless `304`. Interacts safely with both open questions from the original note: never used for a query whose SQL is a write, no matter `cache_ttl` (would otherwise silently skip that write on every call after the first - verified against a real MySQL server), and a cache hit is not recorded in `execution_history`, since nothing ran. State lives in one process's memory (`app.extensions['queryapigate_cache']`, one per Flask app, like the rate limiter) - a multi-process deployment would need a shared backing store instead. ## 7. Admin UI **Status: shipped**, including every follow-up originally listed here - connections CRUD (with a proper password-mask round trip), saved-query CRUD, per-version delete, an execution-history view per version, a hand-rolled (no-dependency) syntax-highlighted SQL editor, an ad-hoc SQL runner with page-size presets and Next/Previous, an API Keys tab (create/edit/revoke scoped keys, with the one-time secret reveal), and a Schema panel next to both SQL editors (list tables, expand for columns, click to insert into the editor, backed by #5's endpoint) - all as a thin client of the existing JSON API with no new backend logic. Verified with real-browser (Playwright) tests: 39 assertions covering connections/queries/runner plus an XSS-payload check (unauthenticated and with an API key set); a pass covering the API Keys tab end-to-end (create, secret reveal, edit, revoke, the admin-only empty state a scoped key sees); and a pass covering the schema panel in both mount points (expand/collapse, click-to-insert for tables and columns, connection-switch reactivity, the unsupported-dialect message shown inline, the refresh button) - zero uncaught JS errors across all three. ## 8. Client SDKs generated from the OpenAPI spec **Status: shipped**, as documentation rather than a new pipeline - no generated code is committed, since `/openapi.json` is already enough. README's "Client SDKs" section documents running it through `openapi-generator-cli`, verified by actually generating Java (okhttp-gson) and TypeScript (fetch) clients against a live server. Specifically **no duplication of the SQL guard**: the actual SQL execution still happens in this one service. ## 9. Type checking and CI hardening **Status: shipped.** `mypy` runs in CI in gradual-typing mode (catches real static errors without demanding annotations across an until-now-untyped codebase) - it found and fixed two genuine, if low-impact, issues: an untyped base-class attribute that made every dialect subclass a type error, and unannotated module-level counters in `metrics.py`. It also surfaced a real cross-version mypy quirk on the first actual CI run: mypy 2.x can't run on Python 3.9 at all, so that job resolves an old 1.x release, which - unlike 2.x - doesn't treat `ignore_missing_imports` as covering a stub-less-but-installed module (PyYAML); fixed with an explicit `disable_error_code`, verified against both mypy generations. Coverage is measured and uploaded to Codecov (badge in the README), currently 93% from the unit suite alone. Python 3.14 added to the CI matrix. Still open (needs the next real CI run to confirm): whether every C-extension dependency (`psycopg2-binary`, `JPype1`, etc.) already has 3.14 wheels, and whether Codecov's tokenless upload works for this public repo on the first real run - if not, add a `CODECOV_TOKEN` repository secret from codecov.io. ## 10. Docs site (MkDocs on GitHub Pages) **Status: shipped and live** at . `mkdocs.yml` (Material theme) and `.github/workflows/docs.yml` build and deploy the site on every push to `main` that touches a doc file. `docs/` holds no real content - every file there is a one-line `pymdownx.snippets` include of the actual document (this README, `documentation/*.md`, `CONTRIBUTING.md`, etc.), mirroring their repo-relative paths so the links between them keep working unchanged on the built site; there is exactly one place to edit each document, same as before this existed. ## 11. DuckDB: query flat files directly, and as a general embedded database **Status: shipped.** `db: "duckdb"` - own `_Driver` subclass in `runners.py`, opt-in `queryapigate[duckdb]` extra, schema introspection, and a `database: ` shape matching SQLite's, resolved the open question below in favour of a persistent `.db` file with no new connection fields for the file-querying case (`SELECT * FROM read_csv(:path)` from a saved query's own SQL). Two things only found by testing against a real DuckDB database, not guessable from the docs: - DuckDB refuses a second connection to the same file with a different read-only setting than one already open on it in-process - fatal for this project's pool, which keys read-only and read-write connections to the same details separately. Every pooled connection is opened read-write regardless of the caller's read-only flag, and the guarantee rests on `validate_sql()` alone, same as `h2`/`jdbc`. - Unlike `h2`/`jdbc`, DuckDB's Python client *does* support cancelling a running statement (`Connection.interrupt()`), so - unlike those two - the query time limit is genuinely enforced here, via a background timer. Also worth remembering: a relative path inside `read_csv()`/`read_parquet()` resolves against the *server process's* working directory, not `QUERYAPIGATE_HOME` (unlike the connection's own `database` field) - see [DuckDB connections](documentation/DATABASE_CONNECTION_CONFIGURATION.md#duckdb-connections). And the same mypy/Python-3.9 cross-version gap documented under item #9 recurred here too: `duckdb` only gained `sleep_ms()` in its 1.5 release, and CI's Python 3.9 job resolves the newest 3.9-compatible one, 1.4.5 - the integration test's "slow query" fixture uses a CPU-bound recursive CTE instead, verified interruptible on both 1.4.5 and 1.5.5. Verified end-to-end: a real DuckDB integration test class (`tests/test_integration.py`, runs unconditionally - no service container needed, unlike the network databases), and a real server/curl session for the flat-file path (aggregation, filtering, and the file path itself passed as a bound parameter). ## 12. Basic data visualization in the admin UI **Status: shipped.** A "Chart" toggle button next to "Copy as TSV" (Run SQL and saved-query Run tabs, `ui.py`'s shared `renderResponse()`/`buildChartPanel()`) appears whenever a JSON result has at least one numeric column, off by default so it never competes with the table/raw view. Resolved the module's stated risk directly: the panel's own toolbar carries a visible "Chart of this page only (N rows) - not the full result" note (dynamic row count, and "N of M" when the page itself was further truncated to the chart's own 50-bar cap) rather than implying a full-dataset view the API was never asked to compute. Label and value columns are pickable from dropdowns - value defaults to the first column that's numeric-or-null in every row, label to the first column that isn't - and repaint live on change. Rendered as hand-rolled inline SVG (`barChartSvg()`, raw `document.createElementNS`, since the existing `h()` DOM builder is HTML-only) with no charting library, matching the SQL editor's own no-dependency syntax highlighting; handles negative values correctly (a zero baseline, bars extending up or down from it). Scoped to a bar chart only for this first version, not line/scatter too, to keep a single hand-rolled renderer rather than three. No backend changes - purely a `/ui` addition. Verified with a real server and real headless Chrome: the button appears only when a numeric column exists (and is absent for an all-text result), the panel is hidden by default and toggles correctly, the row count and truncation note are accurate, the label/value dropdowns default correctly and repaint on change, and negative values render below the zero line. ## 13. Admin UI quick wins, lowest effort first **Impact:** ten small, UI-only additions (no backend changes for any of them) gathered from a few different tools - a SQL IDE (DataGrip/DBeaver), an API client (Postman), a BI tool (Superset), and everyday UX conventions (password managers, JSON viewers, spreadsheets). Each reuses infrastructure this project already shipped (the schema browser, the run/execute path, response data already in scope) rather than adding a new endpoint. Listed lowest effort first, so it doubles as a pick-up-in-order list, not just an idea dump. 1. **"Import into Postman" tip. Shipped.** `/openapi.json` already exists, and Postman/Insomnia can import an OpenAPI spec directly by URL - a one-line documentation callout in [API.md](documentation/API.md#interactive-documentation), no code at all. 2. **Status + timing badge for successful responses, not just errors. Shipped.** The Run SQL tab's stat bar now leads with the HTTP status code and status text (Postman's "200 OK · 1 row · json · 8 ms"), accent-colored to read as success at a glance. Verified with a real headless-Chrome test: the badge appears first, is visually distinct from the plain stat numbers, and the error path (its own `.eb-code` badge) is unaffected. 3. **Show/hide toggle on the connection form's password field. Shipped.** A "Show"/"Hide" button next to the field, working in both create and edit mode without disturbing the existing password-mask round-trip. Verified with a real headless-Chrome test. 4. **"Explain" button. Shipped.** `EXPLAIN` is already an allowed read-only statement - the guard accepts it today. A button next to Run runs `EXPLAIN ` through the existing execute path and shows the plan (DataGrip/DBeaver-style). Verified with a real headless-Chrome test against a live server. 5. **Response headers panel. Shipped.** A collapsible "Headers" section under the results, listing everything the response actually carries - `X-Page`, `X-Request-Id`, etc. - using data `renderResponse()` already has in `res.headers` (Postman-style). Verified with a real headless-Chrome test that the panel starts hidden and lists real response headers once toggled open. 6. **"Copy as curl." Shipped.** A button next to Run that gives the exact `curl` command for the request just made, built from data already on hand (sql, connection, params, format). Deliberately scoped to the Run SQL tab's ad-hoc queries, not saved-query runs. The API key, if any, is written as a `YOUR_KEY_HERE` placeholder rather than the real value, since this text is meant to be copied out of the browser and the key typed into the page shouldn't ride along by default. Verified with a real headless-Chrome test (including reading the actual clipboard contents). 7. **"Copy as TSV." Shipped.** Paste-ready for Excel/Sheets, from the rows already sitting in memory from the last query - a pure client-side transform, no extra round-trip. Verified with a real headless-Chrome test that the clipboard contains a correct header row and data rows. 8. **Client-side ad-hoc query history. Shipped.** A `sessionStorage`-backed "last N queries" list under the Run SQL tab (click to restore into the editor), the same pattern already used for the API key and the last-active tab (Superset SQL Lab-style). Verified with a real headless-Chrome test, including that history survives a page reload within the same tab. 9. **Table preview from the schema browser. Shipped.** A small "preview" affordance next to a table builds `SELECT * FROM ` and runs it through the existing execute path, from both the Run SQL tab's own schema panel and the query drawer's (which also closes the drawer and switches to the Run SQL tab). Verified with real headless-Chrome tests covering both entry points. 10. **Collapsible JSON tree for non-tabular responses, instead of today's flat `
` dump. Shipped.** What
    browser devtools or `jq` give for free - an expand/collapse tree for any non-array JSON response (for
    example the `{"message": "No results returned"}` shape). Verified with a real headless-Chrome test that
    the tree renders and the toggle collapses/expands it.

Explicitly **not** worth doing here: per-table row counts in the schema browser (needs an extra `COUNT(*)`
per table - not actually cheap), a SQL auto-formatter (needs a real formatter), a "collections" concept
(saved queries already group and tag queries), and pre/post-request scripting (real scope creep for an
admin UI).

## 14. Per-query write curation for API keys

**Status: shipped.** `allow_writes` used to be a blanket flag on the whole key - if a key could write at
all, it could write through *any* connection or saved query it was scoped to, not just a specific one. Now
a `queries` entry can be `{"name": "submit_order", "allow_writes": true}` instead of a plain name, adding
write access to that one query specifically, on top of (never instead of) the key's blanket `allow_writes` -
the shape the external-client `queries` grant (#1) was originally built for but couldn't quite express: a
key with `connections: []` and `allow_writes: false` can still write through one curated endpoint and
nothing else. Resolved the open question in favour of the safer option: `_normalize_queries()` never lets
`"*"` carry a write grant, structurally - "*" is a plain string, never a list, so there is no way to attach
a per-entry write flag to it at all; wanting write access to a specific query means enumerating the whole
`queries` list explicitly, the same explicit opt-in shape `allow_writes` already has everywhere else.
`Permission.queries` changed from a `frozenset` of names to a `{name: allow_writes}` dict for a scoped key
(a plain-string entry reads back as `allow_writes=False`, so an existing key's stored file needs no
migration) - dict membership behaves identically to the frozenset it replaces for the existing
`can_use_query()` check, so that and `describe_saved_queries()`'s catalogue filtering needed no changes at
all; only `run_saved()` gained a new `apikeys.can_write_query()` OR'd with the key's blanket `allow_writes`
before calling `engine.execute_sql()`. Still subject to the existing ceiling (never wider than server-wide
`QUERYAPIGATE_ALLOW_WRITES`) and the normal SQL guard, including a key's own `allowed_write_ops` (#21) if it has
one - both restrictions apply together, independently. Admin UI's saved-query checkbox grid gained a
per-query "write" checkbox, disabled and cleared automatically when its own query checkbox is unchecked.
Verified: write through a curated query with zero other access of any kind, a plain-string entry granting
no write access, write curation not leaking to a different query, ad-hoc SQL still fully blocked, the
server-wide ceiling still capping a query-curated grant, the wildcard never carrying write access, a
blanket-write key unaffected, add/remove via `PATCH`, duplicate-name and malformed-entry rejection, the
admin key unaffected, and a real headless-Chrome UI test (per-query write checkbox, disabled state tracking
its own read checkbox, edit-form prefill for both plain and write-curated entries, table display
distinguishing `name` from `name (write)`).

## 15. Per-key rate limiting

**Status: shipped.** An optional `rate_limit` (e.g. `"100/minute"`, `config.parse_rate_limit`'s grammar
reused via a new `label` parameter so a malformed value's error message correctly names `rate_limit`, not
`QUERYAPIGATE_RATE_LIMIT`) on a key's stored permissions. Open question resolved in favour of "applies in
addition to" the IP-based limit, not instead of it - the more defensible option, and the one that actually
needed the extra work the question called out. Checked after `g.permission` is resolved (the IP-based check
stays exactly where it was, still ahead of authentication, so "guessing keys is throttled too" is
unaffected), via a distinct `check_key_rate_limit()` and its own `X-RateLimit-Key-*` headers and
`"...for this API key"` error text, so the two limits are never conflated in a response. Needed a real
architectural piece, not just a new field: `RateLimiter` only supports one spec per instance (wipes every
bucket when a different one arrives - by design, for `QUERYAPIGATE_RATE_LIMIT` itself), so two keys with
different limits can't share one; `ratelimit.KeyRateLimiters` routes each distinct spec to its own
`RateLimiter`, lazily created, with keys sharing a spec correctly sharing an instance (and still getting
independent buckets within it). Verified: over-limit rejection with the right error/headers, a key with no
`rate_limit` never throttled by this mechanism, two keys with the same limit having independent budgets,
both limits genuinely required together (tight per-key limit bites while a generous server-wide one stays
comfortable), the admin key exempt, health/metrics exempt, malformed input rejected with the correct field
name, explicit-null clearing, and a real headless-Chrome UI test - plus a low-level unit test of
`KeyRateLimiters` proving different specs don't clobber each other's state (the actual bug this design
avoids, not just an assumption). See [Per-key rate limiting](documentation/API.md#per-key-rate-limiting).

## 16. API key expiry (TTL)

**Status: shipped.** An optional `expires_at` (`YYYY-MM-DD`, day granularity - not a full timestamp, since
the use case only ever needs day precision and a bare date is what a plain HTML date input gives you with
no timezone-conversion surprises). Checked live in `apikeys.authenticate()` alongside `active`, valid
through the *end* of the given date (23:59:59), not its start - "expires today" still works today. No
background sweep, exactly as scoped: an expired key just stops matching, the same way flipping `active` to
`false` already does. Both open questions resolved: an expired key still lists (the admin UI shows it
visually distinguished, "(expired)" in red, same row style as a revoked one, not hidden), and an expiry can
be extended or cleared without rotating the secret - `PATCH /api_keys/` needed a real presence check
(`'expires_at' in body`, not `.get()`) to distinguish an explicit `{"expires_at": null}` (clear it) from
the field simply being absent (leave it alone), since both look identical through `.get()`. Verified: an
expired key rejected (401), a not-yet-expired one still works, a key expiring today still works, malformed
dates rejected, listing/table display, explicit-null clearing re-enabling a key, and that an unrelated
`PATCH` leaves an existing expiry untouched - via real unit tests and a real headless-Chrome UI test.
See [Key expiry](documentation/API.md#key-expiry).

## 17. IP allowlisting per API key

**Status: shipped.** An optional `allowed_ips` field (a list of IP addresses or CIDR ranges, IPv4 or IPv6,
mixed freely) alongside `connections`/`queries` on a key's stored permissions, checked in
`apikeys.authenticate()` (a new `_ip_allowed()` helper) against `request.remote_addr` passed in from
`app.resolve_permission()` - the same address `QUERYAPIGATE_TRUST_PROXY`/`ProxyFix` already establish as
trustworthy for rate limiting, reused rather than re-derived. Uses the stdlib `ipaddress` module for parsing
and CIDR containment, exactly as scoped - no new dependency. A request from outside the list fails the same
way a wrong key does (`401`), not a distinct error, consistent with how an expired or inactive key already
behaves; the admin key (an env var, not a stored entry) is never restricted by it. `PATCH /api_keys/`
needed the same real presence check as `expires_at`/`rate_limit` (`'allowed_ips' in body`, not `.get()`) so
an explicit `{"allowed_ips": null}` (clear the restriction) is distinguishable from the field being absent
(leave it alone). Admin UI gained a comma-separated "Allowed IPs" form field and a compact "IPs" table
column (a count with the full list in a tooltip, to avoid widening an already nine-column table). Verified:
exact-address and CIDR-range matching for both IPv4 and IPv6, a non-matching address rejected, an unset key
working from any address, malformed input rejected, listing display, explicit-null clearing, an unrelated
`PATCH` leaving an existing restriction untouched, and that the admin key is exempt - via real unit tests
and a real headless-Chrome UI test (create with an allowlist, table shows the count and correct tooltip,
edit form is pre-filled, clearing it in the form restores "any"). See
[IP allowlisting](documentation/API.md#ip-allowlisting).

**Explicitly flagged, not dismissed:** real operational friction comes with this one - dynamic IPs, NAT,
cloud provider IP churn, and a client behind a CDN or corporate proxy can all make "the expected IP" a
moving target that generates support burden more often than it stops an attacker. Worth having as an
opt-in for the callers who actually have stable infrastructure (most external B2B integrations do), not
something to default new keys into.

## 18. Per-key usage visibility (last used, recent activity)

**Status: shipped.** `last_used_at` recorded on a key's stored entry in `api_keys.json`, on every
`authenticate()` match, throttled to once per 60 seconds per key (`apikeys._record_use()`,
`_USE_RECORD_INTERVAL`) - resolving the open question in favour of the "once-per-minute-ish" end of the
range, since that already answers "is this key alive" without meaningfully misleading anyone. Re-reads the
stored entry fresh under `store.lock` immediately before writing, rather than reusing the copy
`authenticate()` already had in hand, so a concurrent admin change (revoke, connections update) is never
clobbered with stale data - same discipline `update_key()`/`delete_key()` already use. `GET /api_keys`
surfaces it automatically (the existing `list_keys()` already passes through every stored field except the
hash, so no separate change was needed there), and the admin UI's table gained a "Last used" column,
showing "never" for a key that hasn't been. The admin key (`QUERYAPIGATE_API_KEY`) has no stored entry to
record use against - it's an env var, not a row in `api_keys.json` - so `authenticate()`'s admin-key branch
never calls this at all, unaffected. Verified: first use recorded, rapid re-use throttled to exactly one
disk write (`_write` call-count asserted directly, not just "the timestamp looks similar" - coarse
timestamp resolution alone wouldn't have proven the throttle actually fired), a write happens again once
the throttle window passes, two different keys don't throttle each other, and a real headless-Chrome UI
test end to end.

## 19. Structured per-query observability fields (query correlation, SQL hash, serialization time)

**Status: shipped.** Observability used to be split across three places that didn't line up with each
other - structured JSON logs (`logging_setup.py`), Prometheus counters/histograms (`metrics.py`), and a
saved query's `execution_history` (`store.py`) - and none of the three carried all of the fields an
operator actually wants when tracing one specific slow or failed call. Landed in two slices:
- **Query correlation.** `execution_history` now carries `request_id` and `key_name` (captured in
  `app.run_saved()`, and for a streamed saved query in `app.stream_sql_response()` specifically - not
  inside the lazily-run `_record_stream_history()` generator itself, whose body only executes once the
  response is actually streaming out, by which point the request/app context that produces `g` and
  `caller_key_name()` is already gone; no `flask.stream_with_context()` is used here. Getting this wrong
  was caught immediately by the test suite as `RuntimeError: Working outside of application context` the
  moment a streamed request was exercised). JSON logs are no longer only partially structured either:
  `_JsonFormatter` now promotes any attribute passed via a call's `extra={...}` to its own top-level JSON
  key, computed generically (diffed against a vanilla `LogRecord`'s own attributes, no fixed field list to
  maintain) - wired up at the per-query line (`connection`, `dialect`, `limit`, `offset`, `timeout`), the
  streaming-start line (`connection`, `dialect`), the slow-query warning (`connection`, `dialect`,
  `duration_ms`), and the per-request access log line (`method`, `path`, `status`, `duration_ms`).
- **SQL hash and serialization time.** The per-query and streaming-start log lines now also carry a full
  SHA-256 `sql_hash` (`engine._sql_hash()`, same full-length-hex convention `pool.py`/`cache.py` already
  use) alongside the existing full SQL text, not instead of it. Response serialization (JSON/CSV/TSV/XML/
  YAML/XLSX encoding) is now timed inside `app.render()` itself and surfaced three ways: a new
  `queryapigate_serialization_duration_seconds` metric by output format, a `serialization_ms` field on the
  access log line (only present when a paged response actually went through `render()`), and a
  `serialization_ms` field on a saved query's `execution_history` entries alongside the existing
  `duration_ms` (query time) - required moving `store.record_execution()` to *after* `render()` in
  `run_saved()` so both timings land in the same entry rather than two. Deliberately never measured for
  streaming responses, which format row by row interleaved with network I/O - there's no single
  "serialization happened here" span to measure the way there is for a page built in memory up front.
  `database_time` vs. `execution_time` stays a non-gap, as originally assessed: one measurement is genuinely
  enough absent a concrete need to split pool-checkout time from driver time specifically.
Verified: a direct unit test of `_JsonFormatter` (extra fields appear, no stray fields when none are
passed, no collision with `request_id`/`key`), an integration test per log call site asserting real field
values (including that `sql_hash` is the actual SHA-256 of the actual SQL and differs between different
queries) in real JSON output, `execution_history` correlation for both paged and streamed saved queries,
the serialization metric appearing for a paged response and *not* moving for a streamed one, and a
low-level histogram-bucketing test for the new metric.

## 20. Two missing `/metrics` series: rows returned and in-flight queries

**Status: shipped.** Two new series, plain module-level state in `metrics.py` matching every other
counter/gauge there - no change to the existing "in-memory, single gunicorn worker" limitation documented
at the top of that file. `queryapigate_rows_returned_total{connection,dialect,key}` sums rows actually returned:
`engine.execute_sql()` records the trimmed page's length (the same `rows[:limit]` already computed for
`has_more`, so the exact figure sent to the caller, not the possibly-larger raw fetch used to detect
another page); `engine._drain()` counts streamed rows as they pass through (an explicit loop replacing the
old `yield from rows`, mirroring the technique `app._record_stream_history()` already used for
`execution_history`) and records the count on every exit path - success, a mid-stream error, or a client
disconnect (`GeneratorExit`) - since a partial count is still data that left the server, not nothing.
`queryapigate_active_queries` is an unlabeled gauge, incremented before a query starts and decremented once it's
done. For a paged query that's a single `engine.execute_sql()` call; for a streaming export the connection
stays checked out for as long as the client keeps reading, so the increment happens in `stream_sql()` but
the decrement is deferred to wherever the query actually stops being active - `stream_sql()`'s own
`finally` if it never got to `_drain()` (a connection/driver error before draining ever starts), or
`_drain()` itself on every one of its exit paths otherwise. A related question - whether errors deserve
their own counter name rather than living as a `status="error"` label on `queryapigate_queries_total` - turned
out to already be a reasonable, common Prometheus convention as-is, not a gap; left unchanged. Verified:
row counts for both a paged query and a full streaming export (via real HTTP requests through the test
client, asserting the delta against the live `/metrics` output, not a mocked count), a low-level test that
`_drain()` settles the active-query gauge back to its prior value after a client disconnect
(`GeneratorExit`) specifically - not just the happy path - and a real concurrency check outside the test
suite (a slow query run on a background thread while polling `/metrics` from the main one, confirming the
gauge reads 1 mid-flight and returns to 0 once the request completes). See
[Observability](documentation/API.md#observability).

## 21. Finer-grained query governance: table allow-lists, operation-type granularity, a real streaming ceiling

**Status: shipped, three of three gaps**, table allow-listing (the long-deferred remainder) now included -
for `mysql`/`postgres`/`clickhouse`/`sqlite`/`duckdb` connections; `h2`/`jdbc`/`mongo` remain a deliberately
deferred, explicitly-scoped-out remainder of *this* gap (see below), not silently unsupported. `timeout`,
`cache_ttl` and `rate_limit` were already solid, and `allowed_users` was already covered (a key's
`connections`/`queries` grants, just modeled the other way round - not a gap).
**Shipped:**
- **`allowed_write_ops`.** A key with `allow_writes` on can now be narrowed to specific write keywords
  (`["insert", "update"]`, say) rather than every write keyword being equally permitted once writes are on
  at all. New `apikeys._validate_allowed_write_ops()` (no fixed enum - the write-keyword space is
  open-ended, so a name is accepted as given), a seventh `Permission` field, and a new `elif` branch in
  `sqltools.validate_sql()` right next to the existing read-only check - never restricts a read-only
  statement, only narrows which *write* ones a key may perform, checked against the statement's own leading
  keyword via the already-existing `first_keyword()`. No parser needed, exactly as scoped. Admin UI gained
  an "Allowed write operations" field and a write-op count on the existing Access column (no new table
  column, to keep the table's width unchanged).
- **`QUERYAPIGATE_STREAM_MAX_ROWS`.** A `?stream=true` export was completely unbounded regardless of
  `QUERYAPIGATE_MAX_PAGE_SIZE`; this caps it, validated at startup like `QUERYAPIGATE_RATE_LIMIT` (a typo shouldn't
  silently leave a requested safety cap unbounded, unlike `QUERYAPIGATE_MAX_PAGE_SIZE`'s own lenient fallback).
  Enforced in `engine._drain()`, which already counted rows as they passed through: past the cap, it stops
  yielding and calls `rows.close()` on the still-open inner generator to release the connection immediately
  (the same `GeneratorExit`-based cleanup a client disconnecting mid-stream already triggers -
  `runners._make_stream_runner`'s `finally: session.close()`) rather than waiting on garbage collection. A
  `break` inside the `for` loop still reaches the wrapping `try`'s own `else:` normally (only a `for`'s
  *own* `else` is skipped by `break`, not an enclosing `try`'s), so the usual success bookkeeping still
  runs. `queryapigate_stream_exports_total` counts a truncated export under a new `status="truncated"`, distinct
  from `"success"`; a saved query's `execution_history` still records `"success"` with the truncated row
  count, since it did succeed, just not to completion. Scoped server-wide only, not per-key, to keep this
  slice cheap - a per-key override would need its own `Permission` field and create/update-key API surface,
  deferred as a possible future refinement rather than blocking this.
Verified: a permitted write allowed, a non-permitted one rejected naming the allowed set, read-only never
restricted, unset meaning any write, the restriction never widening `allow_writes`, keyword
case-normalisation, malformed input rejected, listing/clearing/leave-unchanged for `allowed_write_ops`, the
admin key exempt from both restrictions, a stream truncated at exactly the cap (not the cap minus or plus
one), an under-cap result unaffected, startup rejection of a malformed cap, `execution_history` reflecting
the truncated count, the new `"truncated"` metric status, and a real headless-Chrome UI test (write-op
count and tooltip, edit-form prefill, table width unchanged at 1238px).
- **`allowed_tables`.** Scoped with the user up front to `mysql`/`postgres`/`clickhouse`/`sqlite`/`duckdb`
  only - the same five dialects `sqlflow.py` (#40) already parses for the Access tab's "Query flow" diagram;
  `h2`/`jdbc`/`mongo` have no `sqlglot` dialect to parse against and are deliberately deferred, not silently
  unsupported (see the fail-closed behavior below). Real SQL parsing was needed, not `sqltools.py`'s
  lightweight regex guard - `sqlglot` (already an optional dependency via the `flow` extra) turned out to
  have exactly the right tool, verified directly rather than assumed: a naive `find_all(exp.Table)` walk
  (what `sqlflow.extract_flow()` already does for its visualization) wrongly treats a CTE's own name as a
  real table; `sqlglot.optimizer.scope.build_scope()`, the "correct" CTE-aware tool, was tested and found to
  return `None` entirely for `DELETE`/`UPDATE`/`INSERT` - it's optimizer machinery built for `SELECT`-shaped
  queries only, and would have silently missed every write statement's own target table, a real hole for a
  key that also restricts writes via `allowed_write_ops`. The actual fix, verified against CTEs, subqueries,
  self-joins, `UNION`, and bare or CTE'd `DELETE`/`UPDATE`/`INSERT`: `find_all(exp.Table)` minus the names of
  any `exp.CTE` in the same parse tree - simpler than the scope-builder, and unlike it, correct for every
  statement type this guard needs to check. New `queryapigate/tableguard.py` (deliberately not folded into
  `sqlflow.py`, whose own docstring commits to "never raises" for its visualization-only job - a security
  check needs the opposite contract: an unverifiable query must be rejected, never quietly skipped).
  `sqltools.validate_sql()` gained one more parameter, threaded through `engine.execute_sql()`/`stream_sql()`
  to the same `app.py` call sites `allowed_write_ops` already reaches; `apikeys.py` got the same
  create/update-key/role sweep `allowed_write_ops` already went through (`_validate_allowed_tables()`, a
  ninth `Permission` field, role-copy-onto-key support). Unlike `allowed_write_ops`, this restricts *every*
  statement, read or write, since a forbidden table is forbidden regardless of what's being done to it. A
  table-restricted key used against an `h2`/`jdbc`/`mongo` connection is refused on *every* query with a
  clear `403` naming the unsupported dialect - a deliberate, visible failure mode, not a silent bypass; an
  admin who also grants such a connection to a table-restricted key will find it unusable until the
  restriction is narrowed or this dialect gap is separately picked up. Admin UI gained an "Allowed tables"
  field on both the API Keys and Roles forms, and a `"N tables"` badge on the Access column, alongside (not
  replacing) the existing write-ops badge, matching #21's own established "no new table column" precedent.
  Known, accepted limitations (verified, not guessed): a table name is extracted bare
  (`information_schema.tables` -> `tables`), not schema-qualified, matching `allowed_write_ops`'s own
  bare-keyword-list style but unable to disambiguate two identically-named tables in different schemas; a
  table-valued function (e.g. ClickHouse's `numbers(10)`) touches no real table, so this can't meaningfully
  restrict one.

Verified (this shipment): every `tableguard.extract_tables()` case above codified as a permanent unit test,
`sqltools.validate_sql()`'s own `allowed_tables` enforcement unit-tested directly (unset means unrestricted,
a forbidden table rejected, a join pulling in one forbidden table rejected even with an allowed one present,
an unsupported dialect always rejected, an existing `allowed_write_ops` violation still reported first), a
full `AllowedTablesTests` integration class mirroring `AllowedWriteOpsTests` one-for-one (permitted/forbidden
queries, restricting writes too, case-normalisation, malformed input, listing, clear-via-null,
leave-unchanged, the admin key exempt, the h2 fail-closed case, a role correctly copying the restriction onto
a key created from it), and a real headless-Chrome check (the "Allowed tables" field on both forms, the
table-count badge with its tooltip, and real enforcement proven via direct `curl` calls against a key created
entirely through the UI - a permitted table's query returning `200`, a forbidden one `403`).

## 22. Named permission roles/templates (reusable RBAC on top of per-key ACLs)

**Status: shipped**, as a **template**, resolving the open question below in favor of the simpler of the two
designs. `apikeys.create_role()`/`update_role()`/`delete_role()`/`list_roles()` manage roles in their own
`roles.json`, reusing every one of `apikeys.py`'s existing field validators
(`_normalize_connections`/`_normalize_queries`/`_validate_rate_limit`/`_validate_allowed_ips`/
`_validate_allowed_write_ops`) so there is exactly one definition of each field's validity, never duplicated
for roles. `POST /api_keys` with `"role": ""` copies that role's `connections`, `allow_writes`,
`queries`, `rate_limit`, `allowed_ips` and `allowed_write_ops` onto the new key **once, at creation** -
`authenticate()` only ever reads the key's own stored entry afterward, never the role, so editing or
deleting a role has **zero effect** on a key already created from it: no accidental blast radius, and
nothing to keep in sync. A key records which role it came from in `created_from_role` - purely informational
metadata, never consulted by any permission check. `role` is rejected (`400`, naming the conflicting fields)
if combined with an explicit grant field in the same request - create the key from the role, then `PATCH` it
afterward to customize; `expires_at` is the one field that can still be set alongside `role`, since it's
inherently per-key rather than part of a shared template. All four role-management routes are admin-only and
audited (`create_role`/`update_role`/`delete_role`, see #23). Admin UI gained a Roles tab (create/edit/delete,
plus a "New key from this" shortcut) and a "Create from" picker in the New API key form that dims the grant
fields below it while a role is selected. Verified: role CRUD (including duplicate-name and malformed-field
rejection), `create_key(role=...)` field resolution, the conflict-rejection `400`, an unknown-role `404`, the
core template guarantee (editing/deleting a role after key creation never changes that key, confirmed both
in `tests/test_roles.py` and via a live request against a running server), `created_from_role` in `GET
/api_keys`, audit-log entries for all three role actions, and a real headless-Chrome run through the Roles
tab and the New API key form's role picker end to end. See
[Permission roles](documentation/API.md#permission-roles-templates).

*Original open question, for context:* a **template** (copied into a key at creation time, then independent
- no accidental blast radius when the template later changes, matching how a `PATCH` to one key today never
affects another) is materially simpler than a **live-linked role** (a key stores `role: "reporting"` and its
effective permissions are resolved from the role at `authenticate()` time, so editing the role instantly
changes every key referencing it - fewer places to update, but now a role edit is itself a sensitive,
blast-radius action). The template was chosen as the shipped design.

## 23. Audit logging for administrative actions

**Status: shipped.** A durable, append-only record (`store.record_audit()`/`read_audit_log()`, plain JSON
in `audit_log.json`, `store.lock`-serialised, capped at `config.AUDIT_LOG_LIMIT` (500) the same way
`execution_history` is capped per query version) of every create/update/delete across API keys,
connections and saved queries: `{timestamp, actor, action, target, changes}`. An update (`update_key`,
`update_connection`) records a diff of only the fields that actually changed (`app._dict_diff()`); a create
or delete records a full snapshot instead, since there's no prior or remaining state to diff against.
Resolved the module's one real design risk before shipping: a connection's `password` is never a value in
either form - `app._connection_audit_changes()` reports it only as the literal string `"changed"` in a
diff, and snapshots (create/delete) go through the existing `store.mask_passwords()` - so the audit log
never becomes a second place a real password leaks from, and an API key's entry never includes its secret
or hash (`apikeys.list_keys()` already strips it). Exposed as `GET /audit_log` (admin only, newest first)
and an "Audit Log" tab in `/ui`. Deliberately not a replacement for `QUERYAPIGATE_JSON_LOGS` + an external log
aggregator, which remains the right tool for full request-level traffic analysis - this is specifically for
the smaller, higher-stakes set of configuration-changing actions, durable inside `QUERYAPIGATE_HOME` itself
rather than depending on external log retention being configured correctly. Verified: every action type
records the expected entry shape, a no-op update records nothing new, a connection's password never
appears anywhere in a recorded entry (as a value) whether created, changed or deleted, an API key's secret
never appears, non-admin callers get 403, ordering and the cap, that a write failure never breaks the
action it's recording, and a real headless-Chrome UI test confirming the tab renders diffs and snapshots
correctly with no leaked secret in the DOM. See [Audit log](documentation/API.md#audit-log).

## 24. Credential encryption at rest and secret-manager integration

**Status: shipped.** A password given as a literal string (rather than a `${VAR}` reference) in
`db_connections.json` used to be stored **in plaintext on disk** - `mask_passwords()` only hid it in API
*responses* (`GET /connections`), never encrypted the stored file itself. Landed in two slices:
- **Startup warning (the cheap first slice).** `store.plaintext_password_connections()` names every
  connection whose password is a non-empty literal with no protection at all, in one warning line at
  `app.create_app()` - a nudge, not an enforcement.
- **Encryption at rest (the real fix).** `QUERYAPIGATE_SECRET_KEY` (a Fernet key, validated at startup) turns
  every literal password into ciphertext before `write_json_atomic()` writes it - existing connections
  immediately, via a new `store.encrypt_plaintext_passwords_in_place()` run once at startup rather than
  waiting for a manual migration or a connection's next `PATCH`; new ones the moment they're saved, in
  `update_connections()`. Decrypted only in `get_connection()`, at the exact moment a connection is opened -
  never held decrypted anywhere else. Stored with an `enc:` prefix so a literal, a `${VAR}` reference and an
  encrypted value are always distinguishable at a glance, and so `mask_passwords()` and the startup warning
  above could be taught the difference: an encrypted value now masks the same as a literal in API responses
  and the audit log (neither is ever a value a client should see), but no longer trips the "literal
  password" warning once it's actually protected. `cryptography` is a new dependency, but an *optional* one
  (`queryapigate[encryption]`, bundled in `[all]`) imported lazily only when `QUERYAPIGATE_SECRET_KEY` is actually set -
  a deployment that never opts in installs nothing extra, matching how the DB driver extras already work; a
  clear startup error names the missing package rather than a raw `ImportError` if the key is set without it.
  Both open questions resolved as scoped: a missing or rotated key fails a request clearly (`500`, naming the
  problem - "fail closed, say why", the same precedent `expires_at`/`rate_limit` already set) rather than
  passing ciphertext to the driver or silently falling back to plaintext, and a startup warning fires if
  encrypted passwords exist on disk with no key configured to read them at all (a rotated-out or removed key,
  most likely). Third-party secret-manager integration (Vault, AWS/GCP Secrets Manager) stays explicitly out
  of scope, per the original assessment - `${VAR}` already covers pulling a secret from any of them via an
  operator's own startup tooling.
Verified: startup migration encrypting a literal password (and leaving a `${VAR}` reference or an
already-encrypted value untouched, and an empty password alone), a query working end-to-end through an
encrypted password, `GET /connections` masking it, echoing the mask back keeping the stored ciphertext
unchanged (never re-encrypted), no encryption at all without a configured key (today's behaviour, unaffected),
a missing key both warning at startup and failing clearly on use, a rotated key failing clearly with its own
distinct message, a malformed key rejected at startup, a missing `cryptography` package rejected at startup
with an actionable message, neither the plaintext nor the ciphertext ever appearing in the audit log, and a
real headless-Chrome UI check confirming the connection edit form shows the mask (never the ciphertext) and a
query against an encrypted connection still runs successfully end to end through the UI.

## 25. Benchmark coverage gaps: general API latency, connection pooling, cache performance

**Status: shipped**, as three new scripts (`benchmarks/latency.py`, `pooling.py`, `caching.py`) alongside the
existing `run.py`, sharing a new `benchmarks/common.py` (the `Server`/`Sampler`/native-connect plumbing
`run.py` used to own alone). Real runs committed for MySQL and ClickHouse (disposable Docker containers,
matching CI's own images/credentials, torn down after) - see `benchmarks/README.md` for the full results and
methodology per script. One real finding worth flagging here since it changed the pooling benchmark's own
design partway through: `pool.py` only bounds *idle* connections kept around, not concurrent ones, so
"wait time when the pool is exhausted" - the framing this entry originally used below - does not apply to
this codebase's pool at all. `pooling.py` measures what's actually real instead: throughput/latency and the
idle-pool hit rate (`queryapigate_pool_idle_connections`, sampled during the run) as concurrency increases
relative to `QUERYAPIGATE_POOL_SIZE`. Postgres/SQLite/H2/DuckDB runs are left as a follow-up - the harness
handles every dialect `run.py` already does, just not yet run and committed for the other four.

**Impact (as originally scoped):** `benchmarks/` (see [benchmarks/README.md](https://github.com/AnanthaRajuC/QueryAPIGate/blob/main/benchmarks/README.md))
used to answer exactly one
question - buffered vs. streamed memory and latency for one large flat `SELECT`, per dialect - and answered
it well. It did not answer three other questions a production-grade deployment would reasonably want
evidence for:
- **General API latency.** The existing suite only measures latency at the 1M-row large-export scale; there
  is no benchmark for the common case - a typical small `/execute_sql` or `GET /q/` call - so there's
  no p50/p95/p99 figure for "how much overhead does QueryAPIGate itself add" independent of query execution time
  (parameter binding, the SQL guard, rate-limit/permission checks, response serialization).
- **Connection pooling under concurrency.** `queryapigate_pool_idle_connections` exists as a live gauge
  (`GET /metrics`), but nothing exercises it under load - no benchmark shows pool hit rate, wait time for a
  connection when the pool is exhausted, or throughput as concurrent callers increase relative to
  `QUERYAPIGATE_POOL_SIZE`.
- **Cache performance.** Opt-in saved-query response caching (`cache_ttl`, see [Response
  caching](documentation/API.md#response-caching)) has no benchmark showing the actual hit-vs-miss latency
  delta or effectiveness under a realistic repeated-call pattern - only correctness is tested today
  (`tests/test_queryapigate.py`), not the performance claim caching exists to deliver.

**Notes:** same manual-not-CI methodology as the existing suite applies here too (a timing/memory benchmark
on a shared CI runner is inherently flaky, and none of these need checking on every push) - real subprocess
over real HTTP, not the Flask test client, sampled the same way `benchmarks/run.py` already does. Connection
pooling and cache benchmarks both need a *concurrent* client (multiple in-flight requests), which
`benchmarks/run.py`'s current single-request-at-a-time harness doesn't yet support and would need extending
for (e.g. a thread pool of clients hitting the same server, varying concurrency level as the independent
variable rather than row count/width). General API latency could reuse the existing harness almost as-is,
just against a small, fixed-size dataset instead of the current 1M-row one.

## 26. An API catalogue: one place showing what's exposed, and under what terms

**Status: shipped**, as an endpoint rather than an admin-UI tab - see the note below on why. `GET /catalog`
(`app.py`) joins, per saved query the caller can reach, exactly the governance facts OpenAPI has no field
for: `cache_ttl`, and `can_write` - whether *this specific caller* can write through this query
(`apikeys.can_write_query()`; per-query write curation means this can differ query to query even for the
same key). Alongside the query list, a `caller` object surfaces the calling key's own terms: `allow_writes`,
`allowed_write_ops`, its own `rate_limit`, and the server-wide `server_rate_limit` - both rendered back into
human form (`"100/minute"`) by a new `config.format_rate_limit()`, the inverse of the existing
`parse_rate_limit()`. `queries` is scoped by the exact same reachability rule `describe_saved_queries()`
already applies for `/openapi.json` (kept as its own loop rather than shared - the two describe different
things to different consumers, and OpenAPI's shape is a stable contract other tooling parses, not something
to risk changing by threading new fields through it). Unlike `/openapi.json`, `/catalog` is never public -
it requires a resolved caller like any other functional endpoint, since "what can *I* use" is meaningless
without one. Verified: reachability scoping (a query-scoped key sees only its queries, a connection-scoped
key sees every query on its connections), `cache_ttl` reporting `null` when unset rather than `0` or being
omitted, per-query write curation reflected correctly per query for the same key, the `caller` object's
fields (including that the admin key and a key with no rate limit of its own both report `rate_limit: null`
correctly), and that unauthenticated access is rejected the same way any other functional endpoint rejects
it. See [The API catalogue](documentation/API.md#the-api-catalogue).

**Why an endpoint, not an admin-UI tab:** the admin UI already lets an *admin* piece this same information
together today, query by query on the Saved Queries tab and key by key on the API Keys tab - there's no gap
for that audience. The actual gap `/catalog` closes is for a *scoped* key's own caller, who cannot reach
either of those admin-only tabs at all (see "the same 'only the admin key' message" noted under Admin UI) -
for them, the endpoint itself, consumable directly via `curl` or a script, *is* the catalogue. A UI tab
would only have helped the audience that already had this information.

## 27. Real-world example APIs under `examples/`

**Status: shipped**, in a different shape than sketched below - see the note at the end.

**Impact:** `examples/` currently ships two general-purpose sample databases (sakila, chinook) and two
generic saved queries (`actor_by_id`, `films_by_rating`) - enough to prove the mechanics work, but not
enough to show *why* someone would reach for this project over hand-rolling a controller. A newcomer
evaluating QueryAPIGate benefits more from seeing a named, complete scenario ("here's a reporting API," "here's
what a partner-integration key looks like") than from a single actor-lookup query.
**Notes:** add a handful of fully worked scenarios reusing the existing sakila/chinook data, each pairing a
saved query with the API-key shape that would realistically front it - e.g. a **reporting API** (a
read-only, rate-limited key scoped to a small set of aggregate queries, mirroring the `reporting` role
example already in [documentation/API.md](documentation/API.md#permission-roles-templates)), a **dashboard
data API** (several small saved queries meant to be polled), a **data export API** (a `?stream=true` query
against a large table, tying into [Streaming exports](documentation/API.md#streaming-exports)), and a
**partner/integration API** (a `queries`-scoped external-client key with an expiry, per [Per-saved-query
access](documentation/API.md#per-saved-query-access-external-clients)). Each should be a short, runnable
walkthrough (seed data already exists; add the saved-query JSON and the exact `curl`/admin-UI steps to wire
up its key) rather than a new dataset - the goal is showing intent, not multiplying fixtures.

**What shipped, and how it differs from the sketch above:** the four scenarios (reporting, dashboard, export,
partner) are real and runnable, but they are *loadable into any home* rather than files under the repo's `examples/`
folder - `queryapigate examples load | unload | status`, `QUERYAPIGATE_LOAD_EXAMPLES=yes` for containers,
`GET/POST/DELETE /examples` and a control in the admin UI (the model is Superset's: opt-in load, an environment
variable for containers, easy removal). That needed them to work from a `pip install`, which the repo's Sakila and
Chinook files cannot: those are not in the wheel and their licences were never resolved for redistribution. So the
examples run against a small SQLite database *generated locally* (~2 MB, 60 films / 200 customers / 20,000 rentals,
dated relative to the day it is generated) - a new fixture, which the sketch wanted to avoid, in exchange for nothing
third-party being shipped. The repo's `examples/` folder is unchanged and still the manual route. Each scenario is a
collection plus a role shaped like the key that fronts it (built from the collections work in #35); the walkthrough is
`documentation/EXAMPLES.md`.

**Strengthened afterward:** a fifth role, `example-executive` (`EXTRA_ROLES` in `examples.py`, deliberately
separate from the four `SCENARIOS` - nothing iterates it 1:1 with a collection), read-only and granted both
`examples-reporting` and `examples-dashboard` at once - the one access-model capability the four scenarios
never demonstrated on their own (a role/key spanning more than one collection).

The example database also grew from 3 tables to 8, at explicit follow-up request: `category`, `store`,
`staff`, `address` and `payment` (each with a real foreign key, except `category` - a deliberate plain
lookup table, joined into nothing, a contrast case), added purely additively so `film`/`customer`/`rental`'s
own rows and the `rng` sequence that produces them are byte-for-byte unchanged. `example_all_rentals` (the
export scenario) now joins six tables - `rental`, `film`, `customer`, `payment`, `staff`, `store` -
`payment` is exactly 1:1 with `rental` by construction, so its row count is still exactly `RENTAL_COUNT`,
unchanged. Still deliberately left out: a Mongo scenario (needs a real running server, unlike the generated
SQLite database, so it can't be a `load()`-and-it-just-works fixture the way the rest is); a second version
of an existing query (would need to change `load()`'s name-only idempotency tracking, real risk to a
load-bearing invariant `tests/test_examples.py` heavily exercises, for a cosmetic History-tab demo).
`documentation/EXAMPLES.md` points at the newer schema/parsing features using this real data: PK/FK badges
throughout the new tables, the Access tab's Query flow diagram on the new 6-table join, table-scoped
autocomplete, and real SQL pretty-printing.

**Safety properties, each guarded by a test that was confirmed to fail when broken:** everything installed is marked
`example` and removal deletes exactly that (never a query of yours that shares a name); loading refuses, changing
nothing, if something unmarked holds an example's name (a query, role, key, connection *or* the database file);
loading is idempotent (no extra versions, no extra history, no new keys on re-run) and completes an interrupted
load, reporting `partial` meanwhile. A startup problem under `QUERYAPIGATE_LOAD_EXAMPLES` is a logged warning,
never a startup failure, and a malformed value is rejected.

**Reversed afterward, deliberately: real example API keys and seeded request history, at explicit follow-up
request.** `load()` now creates one real API key per role (`role=`, the same "create key from role" path
the admin UI uses) - this is a deliberate reversal of the "no key is created" property above, confirmed with the
user up front rather than an oversight: the moment these keys exist, `apikeys.auth_required()` flips true and
every request needs a key, everywhere, not just the example endpoints. Weighed and accepted: a demo an admin can
actually call immediately, with real per-scenario keys to copy into a client, was judged worth losing the
"stays open" default. Each key's secret is shown exactly once - printed by `queryapigate examples load`,
returned in `POST /examples`'s response, or logged once at server startup for `QUERYAPIGATE_LOAD_EXAMPLES`
(the only channel available there, with no interactive terminal watching) - and is genuinely unrecoverable after
that, the same rule every API key already has; a second `load()` creates no new keys and returns no new secrets.
A real security bug caught and fixed *before* it shipped, not after: the first draft passed `load()`'s full
return value (including the plaintext secrets) straight into `store.record_audit()`, which would have persisted
every secret into the audit log forever, visible on the Audit Log tab to anyone who can view it - fixed with a
`redact_for_audit()` helper that replaces the secrets with just the sorted key names before anything is
persisted, mirroring how a connection's password is already masked before an audit entry is ever written.

`load()` also seeds each freshly-created query's `execution_history` with 8-20 realistic-looking synthetic runs,
spread over the last 7 days, through `store.record_execution()` - the exact function a real request already
uses - so the admin UI's History tab, Home tab and per-query requests-per-day chart show something immediately
instead of staying empty until someone actually calls a query; a second `load()` never adds more, since only
queries created *this* call are ever seeded. Deliberately *not* extended to the in-memory `/metrics` counters or
the API Keys/Connections "Usage" columns - confirmed with the user up front - since those are documented,
genuinely live-traffic counters that reset on every restart; synthesizing them would only last until the next
one while quietly changing what they honestly mean. Verified directly, not assumed: a fresh restart immediately
after loading shows `/metrics` and every key's usage at zero, while the seeded `execution_history` (real SQLite
rows, unaffected by a restart) is still there in full - proving the two are genuinely independent, not a partial
leak in either direction.

**Deliberately not done:** hiding examples while keeping them installed (a filter across every listing surface is
exactly the kind of thing that drifts; removal is one explicit, exact operation instead), refreshing examples in place
when a newer release changes them (`unload` then `load`), and setting `QUERYAPIGATE_LOAD_EXAMPLES=no` to *remove* them
(a startup setting that deletes data would be a surprise).

*Follow-up:* the repo's own `examples/` folder (the Sakila and Chinook files, two saved queries) has since been removed
in favour of `queryapigate examples load`, so the repository carries no third-party data (0.7.0).

## 28. Package metadata improvements for PyPI discoverability

**Status: shipped.** `[project.urls]` gained `Documentation` (the MkDocs site) and `Repository` - PyPI's
project-page sidebar recognizes both as well-known keys and renders them as clickable links; previously only
`Homepage`, `Issues` and `Changelog` were set, so a visitor had no direct link to the docs site or a second
link to the source without going through the homepage first. Classifiers gained `Environment :: Web
Environment`, `Operating System :: OS Independent`, and per-version `Programming Language :: Python :: 3.9`
through `3.14` matching the CI test matrix exactly (PyPI's "Programming Language" filter facet uses these).
Verified with `python -m build` and `twine check dist/*` (both pass) and by inspecting the built wheel's own
`METADATA` file directly for the expected `Project-URL`/`Classifier` lines. Building the package surfaced an
unrelated deprecation warning (the `License :: OSI Approved :: MIT License` classifier), fixed alongside this
by moving to a PEP 639 SPDX `license = "MIT"` expression in place of the old `license = { text = "MIT" }`
table form and the now-redundant classifier - the build now emits `License-Expression: MIT` and bundles
`LICENSE` under the wheel's standard `licenses/` directory automatically.

**Deliberately left out:** a `Typing :: Typed` classifier (or a `py.typed` marker, PEP 561's mechanism for
actually shipping it). The project's own `[tool.mypy]` config says why this doesn't apply yet - "the
codebase has no type hints yet" and mypy's job here is catching structural errors like a subclass assigning
the wrong type to a base class's annotated attribute, not full annotation coverage. `Typing :: Typed` is a
promise to *consumers* that they get useful inline types when they import this package and run their own
type checker against it - not true today, so the classifier would be actively misleading rather than merely
optimistic. Worth revisiting once the codebase is more thoroughly annotated, not before.

## 29. A pre-built Grafana dashboard for `/metrics`, and a live in-app Metrics tab

**Status: shipped**, in a broader shape than originally framed - see the correction below. Two independent
pieces:
- **`documentation/grafana-dashboard.json`** - a ready-to-import Grafana dashboard against a Prometheus
  scrape of `/metrics`: request/query rate and latency (p50/p95/p99), error rate, rows returned, active
  queries, pool occupancy and rate-limit rejections. For deployments that already run Prometheus/Grafana,
  closing the gap between "the data exists" and "someone can actually see it" without every deployment
  building the same panels from scratch.
- **A "Metrics" tab in the admin UI** (`ui.py`) - stat tiles, two bar charts (requests by status, queries by
  connection) and a per-connection latency/error table, built by parsing `/metrics`' own Prometheus text
  client-side (`parseMetricsText()` and a handful of small aggregation helpers) - no new backend endpoint,
  no new state. For a deployment with no Prometheus/Grafana stack at all, this is the only visibility it has
  by default; needs no API key, since `/metrics` is already public.

**Correction to this item's original framing:** it said "deliberately *not* an in-app dashboard" and ruled
one out for duplicating Grafana and lacking the persistence a real dashboard needs. That reasoning holds for
a *historical* dashboard - trends over time, per-consumer breakdowns as their own analytics product - which
still correctly belongs to Grafana alone, not this project (see the "not recommended" entry on in-app
usage-analytics dashboards). A *live snapshot* - today's totals, no time axis, refreshed on demand - is a
different, smaller thing, already legitimized by #32's Usage columns: reading the same live, in-memory,
current-process numbers `/metrics` already has and formatting them for a human, once per tab load, is not
the same category of risk as building a second time-series dashboard. The tab's own subtitle says this
explicitly - "Live totals since this process started - no history, no trends" - so nobody mistakes it for a
replacement for the Grafana dashboard above, which remains the right answer for real observability.

## 30. Configurable, exportable long-term audit history

**Status: shipped.** `config.audit_log_limit()` (`QUERYAPIGATE_AUDIT_LOG_LIMIT`, default 500) replaces the
hardcoded `AUDIT_LOG_LIMIT` constant `store.record_audit()` used directly before - validated at startup
(`check_settings()`), the same pattern `QUERYAPIGATE_STREAM_MAX_ROWS` already uses, but always a positive count,
never "unbounded" the way the streaming cap can be: `audit_log.json` is read and rewritten in full on every
single audit event, so letting it grow without bound would make every administrative action progressively
slower, not just use more disk. `config.audit_log_export_file()` (`QUERYAPIGATE_AUDIT_LOG_EXPORT_FILE`), when
set, also appends every entry to a separate, append-only file - one JSON object per line, never capped or
rewritten - for retention a rolling cap can never satisfy regardless of how it's sized. The two writes in
`record_audit()` are independently wrapped (each still "never raises," the same trade-off it already made
for the primary write), so a problem with one - the export path's directory missing, say - never blocks the
other. A webhook-delivery option (also proposed in this item's original framing) was left out: it needs
network calls, retries and failure handling inside what has to stay a fire-and-forget, never-raising
function - a materially bigger feature than "write another line to a file," not the "both cheap" this item
was scoped as. Verified: the cap using the env var default and an explicit override, malformed values
rejected at startup, the export file accumulating every entry uncapped even while the primary log is capped
tightly, and each write's failure (a broken export path, a primary-write disk error) confirmed independent
of the other.

## 31. A CLI helper for cron-driven exports to a local file

**Status: shipped.** `queryapigate export  --format csv|tsv|ndjson --connection  --out
/path/{name}_{date}.csv` (`queryapigate/cli.py`). Runs entirely in-process against `QUERYAPIGATE_HOME` - no server
needs to be running, no HTTP round trip, no API key, since this is a trusted local operator with the same
reach the admin key already has. `{date}`/`{name}` placeholders in `--out` are resolved (and a typo'd
placeholder is rejected with a clear message rather than silently writing a literal `{bogus}` into the
filename); `--param name=value` (repeatable) supplies a required parameter, resolved through the exact same
`params.resolve()` validation a real request gets. Calls `engine.stream_sql()` directly - the same function
`stream_sql_response()` already calls internally for `?stream=true` - rather than shelling out to `curl`
against a running server, via a newly-factored-out `formats.iter_stream_chunks()` shared by both the HTTP
response path and this one. Writes to a `.part` temp file in the target directory and renames into place
only once the write completes, so a failed run - a missing query, a bad connection, a missing required
parameter, a malformed startup setting - never leaves a partial or stale file at the final path; exits `1`
for a data/query error and `2` for a startup-configuration error (matching `queryapigate serve`'s existing use of
that code), so cron's own failure handling (mail, whatever alerting the operator already has) works
unmodified. Local filesystem only, no new credential storage, no new dependency, no new daemon - exactly the
scope this item was framed for; see the "not recommended" entry on a full multi-destination scheduler for
why the fuller version doesn't fit this project. Verified: CSV/TSV/NDJSON output, placeholder resolution,
`--connection` overriding the saved query's own default, `--param` supplying a required value, and every
failure path (unknown query, bad connection, missing required parameter, malformed `--out` placeholder,
malformed server setting) confirmed to leave no partial or final file behind.

## 32. Surface each API key's and connection's own live usage in the admin UI

**Status: shipped.** Prompted by a screen-by-screen comparison against Langfuse's admin UI - its
Users/Sessions tables pair every identity with usage stats right next to their config, where QueryAPIGate's API
Keys and Connections screens were config/inventory-only (a key's row showed grants and, at best, a single
`last_used_at` timestamp). `metrics.summary_for_key(name)`/`metrics.summary_for_connection(name)`
(`metrics.py`) aggregate the same in-process counters `/metrics` already renders - queries run, of those how
many failed, and rows returned, plus an average query latency for a connection - and `GET /api_keys`/
`GET /connections` (`app.py`) now attach that as a `usage` object on each entry. The admin UI's Connections
and API Keys tables both gained a "Usage" column rendering it (`usageCell()` in `ui.py`), reading "No
activity yet" when a row has no activity since this process started rather than showing zeros. Deliberately
not a new dashboard or historical view (see the "not recommended" entries on in-app analytics dashboards) -
these are the same live, in-memory, current-process numbers `/metrics` already has, just read once per
admin-UI table load instead of via a separate Prometheus scrape; no new persistence, no time series, no
charting.

Two scope reductions from how this was originally framed, both driven by what `metrics.py`'s existing data
shape actually supports rather than invented afterward: a key's `usage` has no average latency, because the
request/query duration histograms are deliberately not split by key (to keep `/metrics`' bucketed output
from growing with the number of keys - see `observe_request()`'s docstring); a connection's `usage` has no
pool-occupancy figure, because `pool.py`'s idle-connection tracking is keyed by a fingerprint of connection
*details*, not by connection *name*, so today's `idle_count()` is necessarily pool-wide, not
per-connection - breaking that out would be a real `pool.py` change, not just an aggregation function, and
was left out of this pass. Verified: seven new tests (`tests/test_observability.py`) covering both
aggregation functions directly (multi-key/multi-connection isolation, zero-activity defaults, latency
averaging) and both list endpoints end-to-end through the Flask test client; a live headless-Chrome check
against a running server with real query traffic confirmed the rendered "N queries · N failed · Nms avg"
text matches actual activity and "No activity yet" shows correctly for untouched rows.

## 33. Close the gaps in the saved-query History tab: missing columns, no filtering, no drill-down

**Status: shipped**, in a narrower shape than originally framed - see the correction below. `renderHistoryTab()`
(`ui.py`) now shows "Caller" (`key_name`) and "Request ID" columns - `execution_history` already recorded
both (added for the log-correlation work closing #19), but the admin UI never rendered either, so that
traceability was invisible unless someone read the underlying JSON file directly. A status filter
(all/success/failed) and a search box (matching connection, caller, request ID and error text) sit above the
table, filtering client-side over the run history the page already has - no backend change, consistent with
`config.HISTORY_LIMIT` capping this at 50 entries per version to begin with.

**Correction to this item's original framing:** it claimed a failed run's error was only "truncated" to fit
a table cell, and that a drill-down would surface it in full "along with the bound parameters that produced
it." Neither survived contact with the actual code. The error message was never truncated - `x.error` was
already rendered as complete text in the DOM, just prone to forcing horizontal scroll on a long message
(fixed here with proper word-wrapping, not a new drill-down). The bound parameters, meanwhile, were never
captured *at all*: `run_saved()` computes them locally but never includes them in the dict passed to
`store.record_execution()`. Adding that was deliberately left out of this pass rather than done quietly - a
saved query's parameters can carry arbitrary caller-supplied values (a customer ID, an email address, ...),
and there is no existing precedent in this codebase for deciding which of those are safe to persist into a
capped-but-still-durable history file versus needing the same masking treatment `store.mask_passwords()`
gives connection passwords. That is a real design question, not a rendering gap, and belongs in its own
backlog item if it's wanted - not folded silently into a UI-only change.

## 34. Make the audit log scannable at volume: filtering and compact rows

**Status: shipped**, in a narrower shape than originally framed - see the correction below.
`renderAuditLog()`/`renderAuditChanges()` (`ui.py`) gained: an action-type filter (`