Text-to-SQL looks great in a demo. Then someone ships a conversion rate that JOIN s without ON, a revenue figure that double-counts through a fan-out, or a "tenant-safe" query that forgot account_id.
The chart library was never the hard part. Metric definition is.
I built KPIAssembler for that gap: inspect the live schema, let a model propose KPI recipes, and let deterministic Ruby decide what is certified. The model never gets a vote on publication.
Gem: kpi_assembler
Source: github.com/Akshatsrivastava700/kpi_assembler
The LLM proposes. Code certifies.
Certification is boring on purpose. Each accepted candidate must:
SELECT (or WITH … JOIN without ON
NULLIF / CASE) EXPLAIN and a sample-period replay
Fail any check and the KPI is draft, with reasons. Draft is not "you rejected it in the UI." Rejected candidates never reach this step. Draft means you accepted it and the engine still refused to publish the number.
That distinction is the whole product.
Heuristics still run as a backfill. If Gemini is down or Ollama has the wrong model pulled, you get schema-driven candidates instead of an empty screen. The UI says whether the proposer was llm or heuristics.
gem "kpi_assembler", "~> 0.5"
bundle install
bin/rails generate kpi_assembler:install
mount KPIAssembler::Engine => "/kpi-assembler"
Point the initializer at a read-only pool. Certification executes candidate SQL. Do not hang this off the write primary.
KPIAssembler.configure do |config|
config.connection_provider = lambda do |_controller|
ApplicationRecord.connected_to(role: :reading) do
ApplicationRecord.connection_pool
end
end
config.tenant_column = "account_id"
config.tenant_id_resolver = ->(controller) { controller.send(:current_account).id }
config.authorize_with = lambda do |controller|
controller.send(:authenticate_user!)
controller.send(:current_account).present?
end
config.llm_provider = :gemini
config.gemini_api_key = ENV["GEMINI_API_KEY"]
end
In .env:
KPI_LLM_PROVIDER=gemini
GEMINI_API_KEY=your-key
KPI_GEMINI_MODEL=gemini-2.0-flash
No Gemini URL to set. Restart, sign in, open /kpi-assembler, click Discover metrics.
Prefer local models? KPI_LLM_PROVIDER=ollama and a running ollama serve. Prefer no model? KPI_USE_LLM=false.
Walkthrough and troubleshooting: setup guide.
The sample catalog includes a metric that is supposed to fail: revenue per lead via an unconstrained join. It is accepted on purpose so you can watch certification refuse it.
You should see reasons like:
JOIN without ON — unconstrained join / cartesian risk`` Unsafe division without NULLIF or CASE
That is the demo I care about — not a green dashboard.
Other drafts you will hit with a real LLM: missing account_id = 123, invented table names, or Sample-period replay returned NULL when NULLIF did its job on an empty window. Those are honest failures. Empty last-30-days is not the same as bad SQL, but the engine currently treats a NULL replay as unpublished. Read the reasons array before you rewrite the query.
It is not Looker, Metabase, or a warehouse. It does not persist packs to your database yet: the engine keeps the latest pack in memory per tenant. Restart the process and it is gone. GET /kpi-assembler/api/v1/pack is the JSON to save yourself if you need it durable.
It is not "AI analytics." It is a gated compiler for metric SQL.
gem "kpi_assembler", "~> 0.5"
Issues and PRs: Akshatsrivastava700/kpi_assembler.
If you already generate KPIs with a chatbot, run one of those queries through a join-without-ON check before you put it on a slide. That is the same instinct this gem encodes.