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

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%' }



203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
 
# File 'app/services/assistant/postgres_chat_tool_builder.rb', line 203

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:) ⇒ Object 



163
164
165
166
167
168
169
170
171
 
# File 'app/services/assistant/postgres_chat_tool_builder.rb', line 163

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:) ⇒ String

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

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

Parameters:

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

Returns:

  • (String) —

    JSON payload

  • (String) —

    JSON payload



91
92
93
94
95
96
 
# File 'app/services/assistant/postgres_chat_tool_builder.rb', line 91

def self.run_describe_available_data(view_name:, role:, allowed_objects:, party_id:, desc_limit:)
  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)
end

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



224
225
226
227
228
229
 
# File 'app/services/assistant/postgres_chat_tool_builder.rb', line 224

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