# KPIAssembler: stop hand-picking KPIs, let AI propose them

> Source: <https://dev.to/akshat_srivastava_1930291/kpiassembler-stop-hand-picking-kpis-let-ai-propose-them-kbo>
> Published: 2026-09-11 10:17:11+00:00

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](https://rubygems.org/gems/kpi_assembler)

Source: [github.com/Akshatsrivastava700/kpi_assembler](https://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`.

```
# 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](https://github.com/Akshatsrivastava700/kpi_assembler/blob/main/docs/setup.md).

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](https://github.com/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.