query

query

A clicky + clicky-ui app for managing connections and query profiles and running them. query serve starts an embedded postgres, exposes a REST API, and serves the web UI.

go -C cmd/query run . serve --port 8080 --profiles-dir ../../profiles
# UI:      http://localhost:8080/
# OpenAPI: http://localhost:8080/api/openapi.json

Lifecycle

1. Add connections — DB-backed (models.Connection); the create form is driven by a polymorphic (if/then) JSON Schema keyed on the connection type. The url/username/password/certificate fields carry an x-clicky-component: secret-key-selector hint, so the form renders a clicky-ui SecretKeySelector (Secret / ConfigMap / Value) backed by GET /api/v1/secrets[/preview]; the chosen reference is stored as an EnvVar string (secret://<name>/<key>) and resolved at runtime.
2. Create profiles — stored in the migrated PostgreSQL profiles table. Every command resolves its store from the root --db flag, so the CLI reads what the web app wrote; --db= opts out and falls back to YAML under --profiles-dir. Existing YAML profiles are imported without overwriting database rows. The form is driven by the profile-setup schema. A profile declares a provider, a query, server-side filter params, and output columns.
3. Run profiles — GET /api/v1/profile/{name}?<param>=<value> validates the params, resolves their query references ({{.params.<name>}}), executes via the query engine, and returns the rows.

SQL parameters are driver-bound values by default. Declare a database, schema, table, or column parameter as identifier; qualified names are validated as plain dot-separated names and each part is quoted for the connected SQL dialect:

profile: orders
provider:
  type: postgres
query: SELECT id, region FROM {{.params.table}} WHERE {{.params.column}} = {{.params.value}}
params:
  - name: table
    type: identifier
    default: public.orders
  - name: column
    type: identifier
    default: region
  - name: value
    type: string
    required: true

API

Resources are addressed by one REST endpoint; content negotiation selects the representation:

(?__schema and ?__info are accepted as aliases for their Accept headers.)

An info request runs the page it is asked about — that is the only way to know what the provider was actually sent, since the query is rendered from the params and filters in the URL. It never runs an all-row export: the scope is forced back to one page, so explaining a download costs a page and not the download. A failed execution answers with the same diagnostics plus the error, which is the case the endpoint mostly exists for. It is what the result table's Show query menu item reads.

Profile results export as JSON, NDJSON, CSV, YAML, Markdown, HTML, XLSX, or PDF. SQL and OpenSearch all-row exports keep bounded memory by consuming a backend cursor directly when every processor supports pages; whole-result processors and top/global sorting use the buffered compatibility path. PDF is capped at 1,000 rows. Schema-less all-row results can use JSON, NDJSON, or YAML; table-oriented formats require declared columns.

Every execution path uses the same row pipeline:

provider rows → processors → aliases → ignore → row filters → columns → styles

Processors therefore read provider-native fields, while columns and aliases may read fields produced by processors. Sampling skips processors unless processor preview is enabled; the preview records the raw input and each raw processor stage before mapping its final rows.

A trace streams page-capable processors one row at a time. A processor that needs a whole result requires a bounded raw-row batch under trace.buffer:

trace:
  buffer:
    maxRows: 200
    maxWait: 250ms

At least one bound is required and the first reached flushes the batch. A group cannot cross a flush boundary. The remaining batch flushes when the provider ends, the session is stopped, or its deadline is reached; maxEvents caps the final emitted event ring rather than the raw input buffer.

Architecture

cmd/query is an independent Go module. Its reusable code is split by domain:

• connections owns connection CRUD, discovery, actions, and browser/catalog APIs.
• profiles owns file/database stores, profile CRUD, execution, sampling, and OpenAPI.
• sessions owns trace/top execution plus live and persisted session APIs.
• internal/app composes those packages into the server runtime.
• internal/commands translates Cobra inputs into one application/library call per command.

Releases tag the parent module first and then publish the matching cmd/query/vX.Y.Z nested-module tag. The initial split targets parent version v0.1.15, so pre-release checkouts use the repository go.work to resolve the parent locally without committing a replace directive.

The server request pipeline (outer → inner) is:

sessions → profiles → connections → secrets → schemas → clicky executor + UI mux

• All connection/profile CRUD (list/get/create/update/delete), OpenAPI and the cobra CLI come from registered clicky entities. Create/Update use the context-aware handlers and read the raw nested JSON body via rpc.RequestFromContext, so connection properties and profile provider/params/columns survive intact (the executor still flattens flags for parameter-style operations).
• The application runtime registers connection, profiles, and each profile-<slug> before Cobra parses commands. serve injects the database, execution context, and database-backed profile store explicitly. The base profile flow needs no database — only postgres/sqlite processors do.
• Schemas are generated by query/schema and committed under schemas/ (task query:schema / query schema --out schemas). schemas/src/ contains the external-ref source graph: one component per connection type and query provider plus aggregate documents that reference those components. The top-level connection.json and profile.json bundle the same graph under local $defs, which is the self-contained shape clicky-ui's JsonSchemaForm consumes and the server returns via content negotiation.
• Query profiles may set namespace; it scopes secret/configmap references and workload URLs used by inline provider URLs. Saved connections continue to use their own namespace.

Frontend (www/)

Runtime state defaults to $XDG_CONFIG_HOME/flanksource/query, falling back to ~/.config/flanksource/query on every operating system. Embedded PostgreSQL uses the postgres/ subdirectory and standalone/imported query profiles use profiles/. Override the root with --config-dir or QUERY_CONFIG_DIR; the existing --data-dir/QUERY_DATA_DIR and --profiles-dir/QUERY_PROFILES_DIR overrides remain available. Existing .query/pg and ./profiles directories are not migrated automatically.

--db/QUERY_DB_URL picks the store for serve and every sub-command alike:

Connecting is deferred until a command needs the store, so --help, version, schema and shell completion never start PostgreSQL. A cluster started by a sub-command is left running for the next invocation; serve stops the one it starts.

The toolbar exposes explicit Add Connection and Add Profile actions. Connections and profiles are persisted in the embedded database. Private YAML profiles are imported on startup and remain the standalone CLI fallback. After a profile is added, the UI refreshes its OpenAPI discovery data and exposes the profile as a runnable sidebar surface without a server restart.

A Vite + React app using @flanksource/clicky-ui's EntityExplorerApp, embedded via go:embed (www/embed.go). A placeholder dist/index.html is committed so the Go binary builds before a frontend build.

task www:build           # pnpm install && vite build → www/dist
task query:build         # build the binary with the embedded UI
# dev: run the API, then `pnpm --dir cmd/query/www dev` (proxies /api → :8080)
go -C cmd/query run . serve --dev   # serves the Vite dev server through the binary

Note: the per-resource schema endpoints (Accept: application/schema+json) are the contract that drives the connection if/then form, the profile-setup form, and the per-profile FilterBar + DataTable. EntityExplorerApp's add/edit modals fetch these (clicky-ui SchemaActionForm) and render JsonSchemaForm; submit sends the nested value to the entity's create/update route.