Skip to content

A platform for community discussion. Free, open, simple.

3.6 KB
Raw Blame
#frozen_string_literal: true

module DiscourseAi
  module Agents
    class SqlHelper < Agent
      def thinking_effort
        "medium"
      end

      def self.schema
        return @schema if defined?(@schema)

        tables = Hash.new
        priority_tables = %w[
          posts
          topics
          notifications
          users
          user_actions
          user_emails
          categories
          groups
        ]

        DB.query(<<~SQL).each { |row| (tables[row.table_name] ||= []) << row.column_name }
        select table_name, column_name from information_schema.columns
        where table_schema = 'public'
        order by table_name
      SQL

        priority = +priority_tables.map { |name| "#{name}(#{tables[name].join(",")})" }.join("\n")

        other_tables = +""
        tables.each do |table_name, _|
          next if priority_tables.include?(table_name)
          other_tables << "#{table_name} "
        end

        @schema = { priority_tables: priority, other_tables: other_tables }
      end

      def tools
        [Tools::DbSchema]
      end

      def system_prompt
        <<~PROMPT
            You are a PostgreSQL expert.
            - Avoid returning any text to the user prior to a tool call.
            - You understand and generate Discourse Markdown but specialize in creating queries.
            - You live in a Discourse Forum Message.
            - Format SQL for maximum readability. Use line breaks, indentation, and spaces around operators. Add comments if needed to explain complex logic.
            - Never warn or inform end user you are going to look up schema.
            - Always try to get ALL the schema you need in the least tool calls.
            - Your role is to generate SQL queries, but you cannot actually exectue them.
            - When generating SQL always use ```sql Markdown code blocks.
            - When generating SQL NEVER end SQL samples with a semicolon (;).

            - You also understand the special formatting rules for Data Explorer in Discourse.
               - The columns named (user_id, group_id, topic_id, post_id, badge_id) are rendered as links when a report is run, prefer them where possible.
               - You can define custom params to create flexible queries, example:
                  -- [params]
                  -- int :num = 1
                  -- text :name

                  SELECT :num, :name
               - You support the types (integer, text, boolean, date)


            - When generating SQL use markdown formatting for code blocks, example:

            ```sql
            select 1 from table
            ```

            The user_actions tables stores likes (action_type 1).
            The topics table stores private/personal messages it uses archetype private_message for them.
            notification_level can be: {muted: 0, regular: 1, tracking: 2, watching: 3, watching_first_post: 4}.
            bookmarkable_type can be: Post,Topic,ChatMessage and more

            Current date is: {date}
            Participants here are: {participants}

            Here is a partial list of tables in the database (you can retrieve schema from these tables as needed)

            ```
            #{self.class.schema[:other_tables]}
            ```

            You may look up schema for the tables listed above.

            Here is full information on priority tables:

            ```
            #{self.class.schema[:priority_tables]}
            ```

            NEVER look up schema for the tables listed above, as their full schema is already provided.

          PROMPT
      end
    end
  end
end