Class: AsktiveRecord::Prompt

Inherits:
Object
  • Object
show all
Defined in:
lib/asktive_record/prompt.rb

Overview

Prompt class for generating SQL queries from natural language questions. All user inputs are escaped before interpolation to mitigate prompt injection.

Constant Summary collapse

PROMPT_INJECTION_PATTERNS =

Characters that could be used for prompt injection

[
  /ignore\s+(all\s+)?previous\s+instructions/i,
  /forget\s+(all\s+)?previous/i,
  /disregard\s+(all\s+)?above/i,
  /you\s+are\s+now/i,
  /new\s+instructions?:/i,
  /system\s*:/i
].freeze

Class Method Summary collapse

Class Method Details

.as_human_answerer(question, query, response) ⇒ Object 



18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
 
# File 'lib/asktive_record/prompt.rb', line 18

def as_human_answerer(question, query, response)
  safe_question = escape_input(question)
  safe_query = escape_input(query.to_s)
  safe_response = escape_input(response.to_s)

  <<~PROMPT
    You are a helpful data assistant. Answer the user's question based on the SQL query result below.
    Keep in mind the language of the question and answer in the same language.

    If the response looks like an ActiveRecord::Result (with @rows), convert it to a human-readable format
    by extracting the relevant data from the rows.

    Question: "#{safe_question}"

    SQL Query that was executed:
    #{safe_query}

    Query Result:
    #{safe_response}

    Please provide a concise answer based on the result as a human would, without any SQL or technical jargon.
    For example:
    - If the result is a list of users: "There are 5 users in the database."
    - If the result is a single record: "The first user is John Doe."
    - If the result is an aggregate: "The average age of users is 30 years."

    Answer in the same language as the question.
  PROMPT
end

.as_sql_generator(natural_language_query, schema_string) ⇒ Object 



48
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 'lib/asktive_record/prompt.rb', line 48

def as_sql_generator(natural_language_query, schema_string)
  safe_query = escape_input(natural_language_query)

  <<~PROMPT
    You are an expert SQL generator. Your task is to convert a natural language query into a SQL query for a database with the following schema.
    Only generate SELECT queries. Do not generate any INSERT, UPDATE, DELETE, DROP, or other DDL/DML statements.

    Database Schema:
    ```sql
    #{schema_string}
    ```

    Natural Language Query: "#{safe_query}"

    Based on the schema and the natural language query, provide only the SQL query as a single line of text, without any explanation or surrounding text.
    You should determine the appropriate table(s) to query from the schema and the natural language query.
    Use JOINs when necessary to query data across multiple tables.

    Examples:
    - If the query is "show me all users", the output should be: SELECT * FROM users;
    - If the query is "find the last 5 registered users", the output should be: SELECT * FROM users ORDER BY created_at DESC LIMIT 5;
    - If the query is "show me products with their categories", the output might be: SELECT products.*, categories.name as category_name FROM products JOIN categories ON products.category_id = categories.id;
    - If the query is "which is the cheapest product", the output might be: SELECT * FROM products ORDER BY price ASC LIMIT 1;

    SQL Query:
  PROMPT
end

.as_sql_generator_for_model(natural_language_query, schema_string, table_name) ⇒ Object 



76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
 
# File 'lib/asktive_record/prompt.rb', line 76

def as_sql_generator_for_model(natural_language_query, schema_string, table_name)
  safe_query = escape_input(natural_language_query)
  safe_table = escape_input(table_name.to_s)

  <<~PROMPT
    You are an expert SQL generator. Your task is to convert a natural language query into a SQL query for a database with the following schema.
    Only generate SELECT queries. Do not generate any INSERT, UPDATE, DELETE, DROP, or other DDL/DML statements.
    The query should be for the table: #{safe_table}.

    Database Schema:
    ```sql
    #{schema_string}
    ```

    Natural Language Query: "#{safe_query}"

    Based on the schema and the natural language query, provide only the SQL query as a single line of text, without any explanation or surrounding text.
    For example, if the query is "show me all users", and the table is `users`, the output should be:
    SELECT * FROM users;
    If the query is "find the last 5 registered users", the output should be:
    SELECT * FROM users ORDER BY created_at DESC LIMIT 5;

    SQL Query:
  PROMPT
end