DEV Community

Cover image for KPIAssembler: stop hand-picking KPIs, let AI propose them
Akshat Srivastava
Akshat Srivastava

Posted on AI-assisted

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

#ai #ruby #rails #analytics

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 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 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"
Enter fullscreen mode Exit fullscreen mode 
bundle install
bin/rails generate kpi_assembler:install
Enter fullscreen mode Exit fullscreen mode 
# config/routes.rb — inside the same auth scope as the rest of the app
mount KPIAssembler::Engine => "/kpi-assembler"
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

In .env:

KPI_LLM_PROVIDER=gemini
GEMINI_API_KEY=your-key
KPI_GEMINI_MODEL=gemini-2.0-flash
Enter fullscreen mode Exit fullscreen mode

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"
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (1)

Collapse
 
raknaos profile image
Baptiste Le Bouquin •

This is the correct framing: the model proposes, deterministic code certifies. Most text-to-SQL failures we see are not model quality, they are the absence of a boring allow-list between proposal and publication — fan-out joins and missing tenant predicates are our personal hall of shame too.

Does the certification layer only check semantics (joins, aggregates, account_id present), or does it also gate on data, like sanity ranges on the metric value itself before anything ships?