KPI Assembler: stop hand-picking KPIs, let AI propose them

Text-to-SQL looks great in a demo. Then someone ships a conversion rate that JOINs 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 KPI Assembler 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 rule

The LLM proposes. Code certifies.

Certification is boring on purpose. Each accepted candidate must:

• be a single read-only SELECT (or WITH … SELECT)
• reference tables that actually exist
• include tenant scope when that column is on the fact table
• never JOIN without ON
• guard divisions (NULLIF / CASE)
• survive 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.

What the pipeline actually does

1. Discover — introspect tables, columns, FKs, time columns
2. Propose — Gemini, local Ollama, or schema heuristics
3. Accept — you pick the recipes
4. Certify — the checks above
5. Publish — a JSON pack of certified KPIs (plus drafts and why they failed)

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.

Install in a Rails 7 app

# Gemfile
gem "kpi_assembler", "~> 0.5"

bundle install
bin/rails generate kpi_assembler:install

# config/routes.rb — inside the same auth scope as the rest of the app
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.

A certification failure worth keeping

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.

What this is not

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.

Try it

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.