Horizon CAPA Dashboard

The problem

Quality teams needed answers from CAPA, CAPA-task and deviation data but depended on analysts to write SQL and build reports. They wanted to prioritize open items by real precedent rather than generic benchmarks, see the fiscal-year compliance posture without assembling a deck by hand, and — after the standalone search tool was decommissioned — keep semantic search over four years of deviation records somewhere they could still open.

Architecture

Reliability and scale

• Unified CAPA, task and deviation table in RSQLite in memory, because DuckDB cannot be installed on the hosting server
• Month arithmetic hand-rolled because lubridate cannot be installed; a helper undoes SQLite's numeric-date coercion
• Activity logging re-uploads the whole session file per event to avoid a read-modify-write race; a logging failure never interrupts the user

What it does

Horizon is a text-to-SQL quality dashboard for quality managers and directors, built on the querychat package with Databricks-hosted models. A dual-mode sidebar takes either a plain-English question (translated to SQL over a unified CAPA / task / deviation table) or manual filters, and every tab respects the result.

• Dashboard — Overdue / Due 7 / Due 30 / On Track value boxes with CAPA-vs-task sub-counts and click-through drill-downs, an 18-month trend with cumulative backlog, and closure-distribution boxplots
• Calendar — a monthly view in a 9-color scheme: 3 record types × 3 urgency levels
• Quality KPIs — the fiscal-year posture, plant-wide
• AI Insights — a streaming advisor that calls tools on demand
• Deviation Search — the semantic search inherited from DevSight

Historical-similarity prioritization

The standout feature: the Prioritize action uses LLM tool calling to find semantically similar closed CAPAs, pull each match's actual closure time and task breakdown, and rank open items against that real history rather than an aggregate benchmark — citing the matched record. Calling tools on demand keeps prompts at ~500 tokens versus 8–12K for a monolithic prompt that front-loads a schema and a rulebook; the tool bodies are local R, so it stays a single round-trip.

• Five registered ellmer tools and five quick-analysis actions: full analysis, risk report, bottlenecks, by department, prioritize
• Owner-workload chart, a 60-second streaming watchdog, 3-second rate limiting, and 10 turns of memory
• A hardened prompt: quote only, no arithmetic, never invent an identifier, label truncated lists as truncated

Fiscal-year KPIs and a board-ready one-pager

The Quality KPIs tab is written for the people who present, not the people who query. A headline posture strip sits above four fiscal-year KPIs as red / amber / green value boxes with month-over-month delta arrows, 2×2 small-multiple trend panels with target lines, on-time CAPA closure by department, a clickable scorecard, a risk watchlist (top overdue, due in the next 7 days, aging, overdue by owner) and a KPI methodology modal. One click prints a board-ready one-pager.

Deviation Search — the DevSight successor

When DevSight was decommissioned, its general-purpose search moved here on 2026-08-14 as pure R utilities plus one Shiny module with its own test suite. It searches four years of deviation records — 2,213 records indexed as 31,577 chunks — with natural-language date filtering, streamed synthesis that quotes rather than derives, and R-computed insight cards: closure statistics, root-cause themes, similar past records.

One discipline carried over intact. Search time was measured on DevSight, in production, at roughly 30 minutes down to 2. Its effect inside Horizon has not been measured, so the proof of concept states the value qualitatively and refuses to quote a new number until production data supports one.

Constraint-driven engineering

• DuckDB can't be installed on the hosting server, so the unified table lives in RSQLite in-memory with precomputed due-status and days-open columns and a hard 'no SQL date functions' rule in the prompt
• lubridate can't be installed either — month arithmetic is hand-rolled, with a helper that undoes SQLite's numeric-date coercion
• Activity logging accumulates rows in memory and re-uploads the whole session file per event, deliberately avoiding a read-modify-write race; a logging failure never interrupts the user

Decommissioned, then rebuilt as a proof of concept

Horizon was decommissioned in July 2026 during a global application consolidation — while it was performing well, weeks after v1.1.0 shipped with a roadmap through 2027. In August it was rebuilt as v1.2.0 because the site still needed a home for DevSight's search, and Horizon was the surface that could carry it. It remains a proof of concept, not in production. Build the hard part once; carry it forward.

Honest state of play: the merged search module is tested; the fiscal-year KPI helpers that go to management review are not yet, and that is the next item on the list.