Module: SqlChatbot::Grammar::ProgrammaticRenderer

Defined in:
lib/sql_chatbot/grammar/programmatic_renderer.rb

Overview

Unified programmatic-renderer registry. Mirror of npm’s programmatic-renderer.ts. The grammar’s intent extractor emits a primitive (COUNT, LIST, TOP_N, …); this module renders the result deterministically when it can — bypassing the answer-stream LLM which truncates lists, mis-narrates count=0 as “no records”, and hallucinates empty TOP_N results when the metric is all zero.

Adding a new primitive renderer (SUM, AVG, MIN_MAX, RANK) means: write a pure handler and add it to HANDLERS. No new files.

Constant Summary collapse

HANDLE_COUNT =

COUNT — one row, one numeric ‘count` column. Delegates to existing CountRenderer (already in unified shape).

->(label, rows, _rank, _field, _which) {
  CountRenderer.try_render("COUNT", label, rows)
}
HANDLE_LIST =

LIST — small result, picks readable label per row. Delegates.

->(label, rows, _rank, _field, _which) {
  ListRenderer.try_render("LIST", label, rows)
}
TOPN_THRESHOLD = 
10
PREFERRED_LABEL_KEYS = 
%w[title name label subject email username].freeze
HANDLE_TOPN =

TOP_N — small ordered result, label + rank value per row. The Gitea sweep failure: “biggest repo by stars” returned 5 rows with all num_stars=0; the answer LLM rendered “No matching records.” Programmatic render shows the data deterministically.

->(label, rows, rank_field, _field, _which) {
  next { ok: false } unless rows.is_a?(Array)
  next { ok: true, text: ProgrammaticRenderer.empty_text(label) } if rows.empty?
  next { ok: false } if rows.length > TOPN_THRESHOLD

  formatted = []
  rows.each do |row|
    next { ok: false } unless row.is_a?(Hash)
    lbl = ProgrammaticRenderer.pick_row_label(row)
    break { ok: false } unless lbl
    rank_value = ProgrammaticRenderer.pick_rank_value(row, rank_field)
    formatted << { label: lbl, rank: rank_value }
  end
  next formatted if formatted.is_a?(Hash) # short-circuit { ok: false }

  label_or_item = label.to_s.empty? ? "item" : label.to_s
  noun = rows.length == 1 ? CountRenderer.to_singular_label(label_or_item) : CountRenderer.to_plural_label(label_or_item)
  by_part = rank_field ? " by #{rank_field}" : ""
  intro = rows.length == 1 ? "Top #{noun}#{by_part}:" : "Top #{rows.length} #{noun}#{by_part}:"
  lines = formatted.map { |f| f[:rank] ? "- #{f[:label]} (#{f[:rank]})" : "- #{f[:label]}" }.join("\n")
  { ok: true, text: "#{intro}\n#{lines}" }
}
MONEY_HINTS =

SUM / AVG / MIN_MAX — one row, one numeric value. Mirror of TS handlers in programmatic-renderer.ts (V1.3-P / Fix G).

%w[amount price cost revenue total gross net fee].freeze
HANDLE_SUM = 
->(label, rows, _rank, field, _which) {
  value = ProgrammaticRenderer.first_aggregate_value(rows)
  next { ok: false } if value == :no_value
  formatted = ProgrammaticRenderer.format_aggregate_value(field, value)
  next { ok: true, text: "No data available to sum." } if formatted.nil?
  plural = CountRenderer.to_plural_label(label.to_s.empty? ? "item" : label.to_s)
  { ok: true, text: "Total #{ProgrammaticRenderer.humanize_field(field)} across #{plural}: #{formatted}" }
}
HANDLE_AVG = 
->(label, rows, _rank, field, _which) {
  value = ProgrammaticRenderer.first_aggregate_value(rows)
  next { ok: false } if value == :no_value
  formatted = ProgrammaticRenderer.format_aggregate_value(field, value)
  next { ok: true, text: "No data available to average." } if formatted.nil?
  plural = CountRenderer.to_plural_label(label.to_s.empty? ? "item" : label.to_s)
  { ok: true, text: "Average #{ProgrammaticRenderer.humanize_field(field)} across #{plural}: #{formatted}" }
}
HANDLE_MIN_MAX = 
->(label, rows, _rank, field, which) {
  value = ProgrammaticRenderer.first_aggregate_value(rows)
  next { ok: false } if value == :no_value
  formatted = ProgrammaticRenderer.format_aggregate_value(field, value)
  next { ok: true, text: "No data available." } if formatted.nil?
  plural = CountRenderer.to_plural_label(label.to_s.empty? ? "item" : label.to_s)
  adj = which.to_s == "MIN" ? "Lowest" : "Highest"
  { ok: true, text: "#{adj} #{ProgrammaticRenderer.humanize_field(field)} across #{plural}: #{formatted}" }
}
HANDLERS = 
{
  "COUNT"   => HANDLE_COUNT,
  "LIST"    => HANDLE_LIST,
  "TOP_N"   => HANDLE_TOPN,
  "SUM"     => HANDLE_SUM,
  "AVG"     => HANDLE_AVG,
  "MIN_MAX" => HANDLE_MIN_MAX,
}.freeze
COUNT_RE =

Bug D guard for the LLM SQL path. Mirror of TS renderEmptyForLlmSql. Hard sweep on 2026-04-28 surfaced five questions where grammar missed → LLM SQL → 0 rows / COUNT(*)=0 / NULL aggregate → answer LLM emitted “No matching records found.” even when a shape-aware programmatic rendering (“There are 0 X.”, “No Xs found.”) would be more honest. Returns nil when there is data —caller falls through to the answer LLM.

/SELECT\s+COUNT\s*\(/i
AGGREGATE_RE = 
/SELECT\s+(SUM|AVG|MIN|MAX)\s*\(/i
FROM_TABLE_RE = 
/\bFROM\s+(?:"?[\w$]+"?\s*\.\s*)?"?([\w$]+)"?/i

Class Method Summary collapse

Class Method Details

.as_number(v) ⇒ Object 



232
233
234
235
236
 
# File 'lib/sql_chatbot/grammar/programmatic_renderer.rb', line 232

def self.as_number(v)
  return v if v.is_a?(Numeric)
  return v.to_i if v.is_a?(String) && v =~ /\A-?\d+\z/
  nil
end

.empty_text(entity_display_label) ⇒ Object

Shared empty-text helper used by HANDLE_TOPN, ListRenderer, and render_empty_for_llm_sql so the user-facing string is consistent: “No <Plural> found.” instead of the V1.2 Bug D phrase “No matching records found.” which the user has flagged on the 2026-04-28 hard sweep as misleading.



249
250
251
252
253
254
255
 
# File 'lib/sql_chatbot/grammar/programmatic_renderer.rb', line 249

def self.empty_text(entity_display_label)
  if entity_display_label && !entity_display_label.to_s.strip.empty?
    "No #{CountRenderer.to_plural_label(entity_display_label.to_s)} found."
  else
    "I didn't find anything matching that — could you rephrase or be more specific?"
  end
end

.entity_from_from(sql) ⇒ Object 



238
239
240
241
242
 
# File 'lib/sql_chatbot/grammar/programmatic_renderer.rb', line 238

def self.entity_from_from(sql)
  m = sql.match(FROM_TABLE_RE)
  return nil unless m && m[1]
  m[1].split("_").reject(&:empty?).map { |w| w[0].upcase + w[1..].to_s.downcase }.join(" ")
end

.first_aggregate_value(rows) ⇒ Object 



107
108
109
110
111
112
113
 
# File 'lib/sql_chatbot/grammar/programmatic_renderer.rb', line 107

def self.first_aggregate_value(rows)
  return :no_value unless rows.is_a?(Array) && rows.length == 1
  row = rows.first
  return :no_value unless row.is_a?(Hash)
  return :no_value if row.empty? || row.size > 1
  row.values.first
end

.first_scalar(row) ⇒ Object 



227
228
229
230
 
# File 'lib/sql_chatbot/grammar/programmatic_renderer.rb', line 227

def self.first_scalar(row)
  return nil unless row.is_a?(Hash) && !row.empty?
  row.values.first
end

.format_aggregate_value(field, value) ⇒ Object 



115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
 
# File 'lib/sql_chatbot/grammar/programmatic_renderer.rb', line 115

def self.format_aggregate_value(field, value)
  return nil if value.nil?
  n =
    case value
    when Numeric then value
    when String then (value =~ /\A-?\d+(\.\d+)?\z/) ? value.to_f : nil
    end
  return nil unless n
  if looks_like_money?(field)
    # Format with thousands separators and 2 decimals, prefix $.
    whole = n.truncate.to_s.reverse.scan(/\d{1,3}/).join(",").reverse
    decimals = ((n - n.truncate).round(2).abs * 100).round.to_s.rjust(2, "0")
    "$#{whole}.#{decimals}"
  else
    if n == n.to_i
      n.to_i.to_s.reverse.scan(/\d{1,3}/).join(",").reverse
    else
      rounded = (n * 100).round / 100.0
      whole = rounded.truncate.to_s.reverse.scan(/\d{1,3}/).join(",").reverse
      frac = ((rounded - rounded.truncate).abs * 100).round.to_s.rjust(2, "0").gsub(/0$/, "")
      frac.empty? ? whole : "#{whole}.#{frac}"
    end
  end
end

.format_scalar(v) ⇒ Object 



177
178
179
180
181
182
 
# File 'lib/sql_chatbot/grammar/programmatic_renderer.rb', line 177

def self.format_scalar(v)
  return nil if v.nil?
  return v.to_s if v.is_a?(Numeric)
  return v if v.is_a?(String)
  nil
end

.humanize_field(field) ⇒ Object 



146
147
148
149
 
# File 'lib/sql_chatbot/grammar/programmatic_renderer.rb', line 146

def self.humanize_field(field)
  return "value" if field.nil? || field.to_s.empty?
  field.to_s.tr("_", " ")
end

.looks_like_money?(field) ⇒ Boolean

Returns:

  • (Boolean) 


140
141
142
143
144
 
# File 'lib/sql_chatbot/grammar/programmatic_renderer.rb', line 140

def self.looks_like_money?(field)
  return false if field.nil?
  f = field.to_s.downcase
  MONEY_HINTS.any? { |h| f.include?(h) }
end

.pick_rank_value(row, rank_field) ⇒ Object 



163
164
165
166
167
168
169
170
171
172
173
174
175
 
# File 'lib/sql_chatbot/grammar/programmatic_renderer.rb', line 163

def self.pick_rank_value(row, rank_field)
  if rank_field
    v = row[rank_field] || row[rank_field.to_s] || row[rank_field.to_sym]
    return format_scalar(v) if v
  end
  row.each do |k, v|
    next if %w[id count].include?(k.to_s)
    if v.is_a?(Numeric) || (v.is_a?(String) && v =~ /\A-?\d+(\.\d+)?\z/)
      return format_scalar(v)
    end
  end
  nil
end

.pick_row_label(row) ⇒ Object 



151
152
153
154
155
156
157
158
159
160
161
 
# File 'lib/sql_chatbot/grammar/programmatic_renderer.rb', line 151

def self.pick_row_label(row)
  PREFERRED_LABEL_KEYS.each do |k|
    v = row[k] || row[k.to_sym]
    return v.strip if v.is_a?(String) && !v.strip.empty?
  end
  row.each do |k, v|
    next if k.to_s == "id"
    return v.strip if v.is_a?(String) && !v.strip.empty?
  end
  nil
end

.render_empty_for_llm_sql(_question, sql, rows) ⇒ Object 



199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
 
# File 'lib/sql_chatbot/grammar/programmatic_renderer.rb', line 199

def self.render_empty_for_llm_sql(_question, sql, rows)
  is_count = sql =~ COUNT_RE
  is_aggregate = sql =~ AGGREGATE_RE

  if is_count && rows.is_a?(Array) && rows.length == 1
    val = first_scalar(rows[0])
    n = as_number(val)
    if n == 0
      label = entity_from_from(sql)
      return label ? "There are 0 #{CountRenderer.to_plural_label(label)}." : "There are 0 matching items."
    end
    return nil
  end

  if is_aggregate && rows.is_a?(Array) && rows.length == 1
    val = first_scalar(rows[0])
    return "No data available for that question." if val.nil?
    return nil
  end

  if rows.is_a?(Array) && rows.empty?
    label = entity_from_from(sql)
    return label ? "No #{CountRenderer.to_plural_label(label)} found." : "I didn't find anything matching that — could you rephrase or be more specific?"
  end

  nil
end

.try_render(primitive, entity_display_label, rows, rank_field: nil, field: nil, which: nil) ⇒ Object 



18
19
20
21
22
23
 
# File 'lib/sql_chatbot/grammar/programmatic_renderer.rb', line 18

def self.try_render(primitive, entity_display_label, rows, rank_field: nil, field: nil, which: nil)
  return { ok: false } if primitive.nil?
  handler = HANDLERS[primitive.to_s]
  return { ok: false } unless handler
  handler.call(entity_display_label, rows, rank_field, field, which)
end