Class: Assistant::PostgresChatToolBuilder

Inherits:
Object
  • Object
show all
Defined in:
app/services/assistant/postgres_chat_tool_builder.rb

Overview

Builds RubyLLM::Tool subclasses for PostgreSQL database access.
Provides execute_sql, schema exploration, and activity notes search.

Named PostgresChatToolBuilder to distinguish from ::PostgresToolBuilder
(MCP server tools in app/mcp/).

Usage (via ChatToolBuilder):
tools = Assistant::PostgresChatToolBuilder.tools(
service_key, role:, allowed_objects:, audit_context:
)

Constant Summary collapse

TOOL_CONFIG =

Tool config.

{
  'app_db'            => { prefix: 'app_db',      label: 'App DB' },
  'postgres_versions' => { prefix: 'versions_db', label: 'Versions DB' }
}.freeze
DESCRIBE_SUMMARY_DESC_LIMIT =

Max length of per-object descriptions returned by the no-args summary
of build_describe_available_data_tool. The full description (and per-column
metadata) is available via the view_name: detail call. Truncating here
keeps the initial schema dump small enough that thinking-capable models
don't burn most of their turn duration just reading it
(see Assistant::ToolLoopGuard::MAX_TURN_DURATION).

80
IDENTIFIER_QUERY_REGEX =

Identifier-shaped query: 0-4 leading uppercase letters followed by 5+ digits
(covers ON5734284, SQ809861, CN25803159, plus bare numerics like 5734284).
When a query matches, run a literal notes ILIKE '%query%' against
activities first — semantic search of a numeric ID is useless ranking
noise, as the audit of conv 1743 showed (top hit for "5734284" was an
unrelated party at 70% cosine similarity).

/\A([A-Z]{0,4})(\d{5,})\z/
LITERAL_FALLBACK_LIMIT =

Maximum literal hits to merge in front of semantic results. We still
run semantic search for context, but exact matches always win the top spots.

5
COMMON_OBJECTS =

The objects Sunny actually asks about, most-used first, measured over the
90 days to 2026-08-12 (ruby_llm_tool_calls). The listing exists to ROUTE
the model to one of these, and the full catalogue is 594 objects — of
which only 159 have ever been described at all, with the top 5 taking 66%
of the calls and the top 10 taking 76%. Sending all 594 every time spent
~7,900 tokens to answer a question the first fifteen almost always
answer.

Refresh from telemetry rather than intuition; a name that stops existing
is skipped silently, since the manifest is the source of truth for what
is real.

%w[
  view_activities view_opportunities view_quotes view_sales_facts quotes
  parties view_customers items view_orders orders addresses opportunities
  activities view_line_items view_rmas view_invoices
].freeze

Class Method Summary collapse

Class Method Details

.activity_search(query:, limit:) ⇒ Array<Hash>

Run an activity search that prefers literal hits for ID-shaped queries
and falls back to semantic search for everything else.

Parameters:

  • query (String)
  • limit (Integer)

Returns:

  • (Array<Hash>) —

    each entry is { activity:, match:, similarity: }
    where match is "literal" or "semantic".



49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
 
# File 'app/services/assistant/postgres_chat_tool_builder.rb', line 49

def self.activity_search(query:, limit:)
  # Never overshoot the caller's limit. LITERAL_FALLBACK_LIMIT only applies
  # when the caller's budget is at least that large; smaller budgets cap
  # literal hits at the budget so the tool's contract holds for narrow
  # callers like activity_search(query:, limit: 1).
  literal_budget = [limit, LITERAL_FALLBACK_LIMIT].min
  literal_hits = literal_activity_search(query: query, limit: literal_budget)
  semantic_remaining = [limit - literal_hits.size, 0].max

  semantic_hits = if semantic_remaining.positive?
                    excluded_ids = literal_hits.map { |row| row[:activity].id }
                    ContentEmbedding::ActivityEmbedding
                      .semantic_search(query, limit: semantic_remaining + excluded_ids.size, published_only: false)
                      .filter_map do |embedding|
                        activity = embedding.embeddable
                        next if activity.nil? || excluded_ids.include?(activity.id)

                        similarity = ((1.0 - (embedding.neighbor_distance / 2.0)) * 100).round(1)
                        { activity: activity, match: 'semantic', similarity: "#{similarity}%" }
                      end.first(semantic_remaining)
                  else
                    []
                  end

  literal_hits + semantic_hits
end

.literal_activity_search(query:, limit:) ⇒ Array<Hash>

Literal notes ILIKE '%query%' match for ID-shaped inputs. Used by
activity_search as the first pass for identifier-shaped queries
(ON5734284, 5734284, CN…) before falling back to semantic search.
Returns [] for non-ID-shaped queries so the caller can short-circuit.

Parameters:

  • query (String) —

    Identifier-shaped query (must match IDENTIFIER_QUERY_REGEX)

  • limit (Integer) —

    Maximum activities to return

Returns:

  • (Array<Hash>) —

    Each entry: { activity: Activity, match: 'literal', similarity: '100%' }



253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
 
# File 'app/services/assistant/postgres_chat_tool_builder.rb', line 253

def self.literal_activity_search(query:, limit:)
  match = query.to_s.strip.match(IDENTIFIER_QUERY_REGEX)
  return [] unless match

  _prefix, numeric = match.captures
  # Search both the prefixed form and the bare numeric form so users can
  # paste either "ON5734284" or "5734284" without us missing the rows.
  patterns = [query.to_s.strip, numeric].uniq
  branches = patterns.map { |p| Activity.where('notes ILIKE ?', "%#{p}%") }

  Activity.where.any_of(*branches)
          .order(target_datetime: :desc)
          .limit(limit)
          .to_a
          .map { |activity| { activity: activity, match: 'literal', similarity: '100%' } }
rescue StandardError => e
  Rails.logger.warn("[PostgresChatToolBuilder] literal_activity_search failed: #{e.message}")
  []
end

.run_activity_notes_search(query:, limit:) ⇒ String

Logic for the search_activity_notes tool. Public so the
RubyLLM::Tool execute body can delegate.

Parameters:

  • query (String)
  • limit (Integer)

Returns:

  • (String) —

    JSON payload



213
214
215
216
217
218
219
220
221
 
# File 'app/services/assistant/postgres_chat_tool_builder.rb', line 213

def self.run_activity_notes_search(query:, limit:)
  limit = limit.to_i.clamp(1, 50)
  activities = activity_search(query: query, limit: limit)
  { query: query,
    total_results: activities.size,
    activities: activities.map { |row| serialize_activity_match(row) } }.to_json
rescue StandardError => e
  { error: "Activity search failed: #{e.message}" }.to_json
end

.run_describe_available_data(view_name:, role:, allowed_objects:, party_id:, desc_limit:, domain: nil) ⇒ String

Logic for the *_describe_available_data tool. Public so the
RubyLLM::Tool execute body can delegate.

Parameters:

  • view_name (String, nil)
  • role (Symbol)
  • allowed_objects (Array, Set, nil)
  • party_id (Integer, nil)
  • desc_limit (Integer)

Returns:

  • (String) —

    JSON payload



85
86
87
88
89
90
 
# File 'app/services/assistant/postgres_chat_tool_builder.rb', line 85

def self.run_describe_available_data(view_name:, role:, allowed_objects:, party_id:, desc_limit:, domain: nil)
  allowed = allowed_objects || Assistant::DataPolicy.allowed_objects(role)
  return describe_object_detail(view_name, allowed: allowed, party_id: party_id) if view_name.present?

  describe_object_summary(allowed: allowed, desc_limit: desc_limit, domain: domain)
end

.tools(service_key, role: :employee, allowed_objects: nil, audit_context: {}) ⇒ Object 



274
275
276
277
278
279
 
# File 'app/services/assistant/postgres_chat_tool_builder.rb', line 274

def tools(service_key, role: :employee, allowed_objects: nil, audit_context: {})
  ao_hash = allowed_objects ? Digest::MD5.hexdigest(allowed_objects.to_a.sort.join(',')) : 'nil'
  cache_key = "#{service_key}:#{role}:#{ao_hash}:#{audit_context[:conversation_id]}:#{audit_context[:user_id]}"
  @cache ||= {}
  @cache[cache_key] ||= build_tools(service_key, role: role, allowed_objects: allowed_objects, audit_context: audit_context)
end