Hey Folks, I want to work on a project as a database assistant, the main idea behind this project is that the user will ask human language questions and this assistant will translate the question into an SQL query to execute in the database and get the result from the database, I implemented the first application but the accuracy of the assistant is very low, sometimes answers correct but most of the cases are not.
am asking if the assistant is a good fit solution to my problem. and if yes how i can improve it to get a better result? if not what are the solutions that you propose?
Hi @rigor08 - I’ve created an Assistant that in part relies on SQL queries to an external database and it’s worked fairly well in my case.
When you say “the accuracy of the assistant is very low”, at what point in the process do you observe the issue, e.g. does the assistant not properly identify the query parameters or does it not properly interpret the information that is returned from the SQL query?
1 Like
Hi @jr.2509 - the accuracy that am talking about is when I got the SQL query from the assistant if it’s not correct it didn’t execute in the database so then I can’t answer the question.
my implementation of the solution is like that client → assistant → SQL → execute on database → result.
most of the time the SQL generated is not executable in the database.
1 Like
Right. Much depends on how well you define the function including the specific parameters. The more specific you can be in your description including in relation to the acceptable values, the higher your chances of the Assistant getting it right. Additionally, you need to ensure that your Assistant instructions are sufficiently specific and consistent with the information in your function description. I have found in practice that it helped to include a description of the SQL DB structure (i.e. the different columns and types of data/values in each column) in the instructions as well as to be specific about how the Assistant is expected to identify the inputs/variables required for the SQL query.
Is it necessary for you to have an exact match with values in the DB?
2 Likes
Yes, I’m working with a tech stack that is based on a pre-generated database that contains (users, accounts, purchases,…)
so to get the answer from the database I have to know the exact SQL query that gets this data from the database.
do you think that optimizing the instruction for the assistant will increase the accuracy of the assistant’s response?
can you provide me with some ressources on how to optimize the instruction of the assistant? thanks in advance .
1 Like
It should definitely help. I would approach it iteratively. Start with a simplified query to get a feel for how to best phrase your instructions and functions descriptions to get the Assistant to return the required variables, then progressively build out the complexity of the query.
One thing you may also consider is to supply the Assistant with example queries as part of the knowledge database and explicitly reference to this file in your instructions.
There is not a single dedicated resource I am aware of right now that explicitly addresses this point. However, you can find quite a few posts across the forum that discuss strategies for optimizing Assistant instructions.
Without being familiar with the details of your DB and dataset, it’s a bit difficult to be too specific with recommendations. But to reiterate: solely based on my experience I can say that being extremely specific and detailed in the function description, in particular as it relates to the nature of values for each column in the SQL DB, has made a significant difference.
If you are in a position to share some more concrete information about your data and your existing instructions and functions, then myself and/or others can help to take a closer look.
1 Like
Having done this for a while now, I can say this is the most critical piece. You MUST define you database tables as specifically as you can. No just table and field names – that’s jus the beginning. You have to also describe what each field is, what it does, and it’s relationship to the bigger picture. Ditto with the tables themselves. And keep in mind, when you are doing this, how your users are going to phrase their questions.
For example, I have a table named “log” with a field called “type”. Not only do I need to describe what the table is and what “types” are possible, but also let the model know that some people may ask questions like, “show me the queries for this date” which means search the log table for field type = “query”.
The more info you give the model about your data, beyond just the structure, the better it i going to be at answering questions.
2 Likes
hi @jr.2509 , i appreciate your effort to answer my questions, to put you more in context I’m working on the odoo database
[
{
"table_name": "product_tic_category",
"fields": [
"id",
"code",
"create_uid",
"write_uid",
"description",
"create_date",
"write_date"
]
},
{
"table_name": "ir_module_module_dependency",
"fields": [
"id",
"name",
"module_id",
"auto_install_required"
]
},
{
"table_name": "data_frame",
"fields": [
"id",
"create_uid",
"write_uid",
"quest_text",
"sql_query",
"create_date",
"write_date",
"fine_tune_module_id"
]
},
{
"table_name": "model_model",
"fields": [
"id",
"create_uid",
"write_uid",
"name",
"create_date",
"write_date",
"fine_tune_module_id"
]
},
{
"table_name": "ir_model_fields",
"fields": [
"id",
"relation_field_id",
"model_id",
"related_field_id",
"size",
"create_uid",
"write_uid",
"name",
"complete_name",
"model",
"relation",
"relation_field",
"ttype",
"related",
"state",
"on_delete",
"domain",
"relation_table",
"column1",
"column2",
"depends",
"field_description",
"help",
"compute",
"copied",
"required",
"readonly",
"index",
"translate",
"group_expand",
"selectable",
"store",
"create_date",
"write_date",
"tracking"
]
},
{
"table_name": "res_partner",
"fields": [
"id",
"company_id",
"create_date",
"name",
"title",
"parent_id",
"user_id",
"state_id",
"country_id",
"industry_id",
"color",
"commercial_partner_id",
"create_uid",
"write_uid",
"display_name",
"ref",
"lang",
"tz",
"vat",
"company_registry",
"website",
"function",
"type",
"street",
"street2",
"zip",
"city",
"email",
"phone",
"mobile",
"commercial_company_name",
"company_name",
"date",
"comment",
"partner_latitude",
"partner_longitude",
"active",
"employee",
"is_company",
"partner_share",
"write_date",
"fax",
"ice",
"patente_code",
"cin",
"contact_address_complete",
"message_main_attachment_id",
"message_bounce",
"email_normalized",
"signup_token",
"signup_type",
"signup_expiration",
"calendar_last_notif_ack",
"team_id",
"ocn_token",
"partner_gid",
"additional_info",
"phone_sanitized",
"supplier_rank",
"customer_rank",
"invoice_warn",
"invoice_warn_msg",
"debit_limit",
"last_time_entries_checked",
"sale_warn",
"sale_warn_msg"
]
},
{
"table_name": "res_users",
"fields": [
"id",
"company_id",
"partner_id",
"active",
"create_date",
"login",
"password",
"action_id",
"create_uid",
"write_uid",
"signature",
"share",
"write_date",
"totp_secret",
"notification_type",
"odoobot_state",
"odoobot_failed",
"sale_team_id",
"target_sales_won",
"target_sales_done",
"target_sales_invoiced"
]
},
{
"table_name": "fine_tune_module",
"fields": [
"id",
"create_uid",
"write_uid",
"name",
"create_date",
"write_date"
]
},
{
"table_name": "data_frame_model_model_rel",
"fields": [
"data_frame_id",
"model_model_id"
]
},
{
"table_name": "resource_calendar",
"fields": [
"id",
"company_id",
"create_uid",
"write_uid",
"name",
"tz",
"active",
"two_weeks_calendar",
"create_date",
"write_date",
"hours_per_day"
]
},
{
"table_name": "resource_calendar_attendance",
"fields": [
"id",
"calendar_id",
"resource_id",
"sequence",
"create_uid",
"write_uid",
"name",
"dayofweek",
"day_period",
"week_type",
"display_type",
"date_from",
"date_to",
"create_date",
"write_date",
"hour_from",
"hour_to"
]
},
{
"table_name": "resource_resource",
"fields": [
"id",
"company_id",
"user_id",
"calendar_id",
"create_uid",
"write_uid",
"name",
"resource_type",
"tz",
"active",
"create_date",
"write_date",
"time_efficiency"
]
},
{
"table_name": "resource_calendar_leaves",
"fields": [
"id",
"company_id",
"calendar_id",
"resource_id",
"create_uid",
"write_uid",
"name",
"time_type",
"date_from",
"date_to",
"create_date",
"write_date"
]
},
{
"table_name": "ir_logging",
"fields": [
"id",
"create_uid",
"write_uid",
"name",
"type",
"dbname",
"level",
"path",
"func",
"line",
"message",
"create_date",
"write_date"
]
},
{
"table_name": "ir_module_category",
"fields": [
"id",
"create_uid",
"create_date",
"write_date",
"write_uid",
"parent_id",
"name",
"sequence",
"description",
"visible",
"exclusive"
]
},
{
"table_name": "ir_module_module",
"fields": [
"id",
"create_uid",
"create_date",
"write_date",
"write_uid",
"website",
"summary",
"name",
"author",
"icon",
"state",
"latest_version",
"shortdesc",
"category_id",
"description",
"application",
"demo",
"web",
"license",
"sequence",
"auto_install",
"to_buy",
"maintainer",
"published_version",
"url",
"contributors",
"menus_by_module",
"reports_by_module",
"views_by_module"
]
},
{
"table_name": "ir_profile",
"fields": [
"id",
"sql_count",
"entry_count",
"session",
"name",
"init_stack_trace",
"sql",
"traces_async",
"traces_sync",
"qweb",
"create_date",
"duration"
]
},
{
"table_name": "utm_campaign",
"fields": [
"id",
"user_id",
"stage_id",
"color",
"create_uid",
"write_uid",
"name",
"title",
"is_auto_campaign",
"create_date",
"write_date",
"company_id"
]
},
{
"table_name": "utm_tag_rel",
"fields": [
"tag_id",
"campaign_id"
]
},
{
"table_name": "utm_tag",
"fields": [
"id",
"color",
"create_uid",
"write_uid",
"name",
"create_date",
"write_date"
]
},
{
"table_name": "utm_stage",
"fields": [
"id",
"sequence",
"create_uid",
"write_uid",
"name",
"create_date",
"write_date"
]
},
{
"table_name": "utm_medium",
"fields": [
"id",
"create_uid",
"write_uid",
"name",
"active",
"create_date",
"write_date"
]
},
{
"table_name": "utm_source",
"fields": [
"id",
"create_uid",
"write_uid",
"name",
"create_date",
"write_date"
]
},
{
"table_name": "res_users_apikeys",
"fields": [
"id",
"name",
"user_id",
"scope",
"index",
"key",
"create_date"
]
},
{
"table_name": "ir_model_fields_group_rel",
"fields": [
"field_id",
"group_id"
]
},
{
"table_name": "res_groups",
"fields": [
"id",
"name",
"category_id",
"color",
"create_uid",
"write_uid",
"comment",
"share",
"create_date",
"write_date"
]
},
{
"table_name": "ir_model_fields_selection",
"fields": [
"id",
"field_id",
"sequence",
"create_uid",
"write_uid",
"value",
"name",
"create_date",
"write_date"
]
},
{
"table_name": "ir_model_constraint",
"fields": [
"id",
"model",
"module",
"create_uid",
"write_uid",
"name",
"definition",
"type",
"message",
"write_date",
"create_date"
]
},
{
"table_name": "ir_model_relation",
"fields": [
"id",
"model",
"module",
"create_uid",
"write_uid",
"name",
"write_date",
"create_date"
]
},
{
"table_name": "ir_model_access",
"fields": [
"id",
"model_id",
"group_id",
"create_uid",
"write_uid",
"name",
"active",
"perm_read",
"perm_write",
"perm_create",
"perm_unlink",
"create_date",
"write_date"
]
},
{
"table_name": "ir_model_data",
"fields": [
"id",
"create_uid",
"create_date",
"write_date",
"write_uid",
"res_id",
"noupdate",
"name",
"module",
"model"
]
},
{
"table_name": "ir_ui_menu",
"fields": [
"id",
"sequence",
"parent_id",
"create_uid",
"write_uid",
"parent_path",
"web_icon",
"action",
"name",
"active",
"create_date",
"write_date"
]
},
{
"table_name": "wizard_ir_model_menu_create",
"fields": [
"id",
"menu_id",
"create_uid",
"write_uid",
"name",
"create_date",
"write_date"
]
},
{
"table_name": "ir_sequence",
"fields": [
"id",
"number_next",
"number_increment",
"padding",
"company_id",
"create_uid",
"write_uid",
"name",
"code",
"implementation",
"prefix",
"suffix",
"active",
"use_date_range",
"create_date",
"write_date"
]
},
{
"table_name": "ir_sequence_date_range",
"fields": [
"id",
"sequence_id",
"number_next",
"create_uid",
"write_uid",
"date_from",
"date_to",
"create_date",
"write_date"
]
},
{
"table_name": "ir_ui_menu_group_rel",
"fields": [
"menu_id",
"gid"
]
},
{
"table_name": "ir_ui_view_custom",
"fields": [
"id",
"ref_id",
"user_id",
"create_uid",
"write_uid",
"arch",
"create_date",
"write_date"
]
},
{
"table_name": "ir_ui_view_group_rel",
"fields": [
"view_id",
"group_id"
]
},
{
"table_name": "reset_view_arch_wizard",
"fields": [
"id",
"view_id",
"compare_view_id",
"create_uid",
"write_uid",
"reset_mode",
"create_date",
"write_date"
]
},
{
"table_name": "ir_asset",
"fields": [
"id",
"sequence",
"create_uid",
"write_uid",
"name",
"bundle",
"directive",
"path",
"target",
"active",
"create_date",
"write_date"
]
},
{
"table_name": "ir_actions",
"fields": [
"id",
"binding_model_id",
"create_uid",
"write_uid",
"type",
"binding_type",
"binding_view_types",
"name",
"help",
"create_date",
"write_date"
]
},
{
"table_name": "ir_act_window",
"fields": [
"id",
"binding_model_id",
"create_uid",
"write_uid",
"type",
"binding_type",
"binding_view_types",
"name",
"help",
"create_date",
"write_date",
"view_id",
"res_id",
"limit",
"search_view_id",
"domain",
"context",
"res_model",
"target",
"view_mode",
"usage",
"filter"
]
},
{
"table_name": "ir_act_window_group_rel",
"fields": [
"act_id",
"gid"
]
},
{
"table_name": "ir_act_window_view",
"fields": [
"id",
"sequence",
"view_id",
"act_window_id",
"create_uid",
"write_uid",
"view_mode",
"multi",
"create_date",
"write_date"
]
},
{
"table_name": "ir_ui_view",
"fields": [
"id",
"priority",
"inherit_id",
"create_uid",
"write_uid",
"name",
"model",
"key",
"type",
"arch_fs",
"field_parent",
"mode",
"arch_db",
"arch_prev",
"arch_updated",
"active",
"create_date",
"write_date",
"customize_show"
]
},
{
"table_name": "ir_act_url",
"fields": [
"id",
"binding_model_id",
"create_uid",
"write_uid",
"type",
"binding_type",
"binding_view_types",
"name",
"help",
"create_date",
"write_date",
"target",
"url"
]
},
{
"table_name": "res_partner_bank",
"fields": [
"id",
"partner_id",
"bank_id",
"sequence",
"currency_id",
"company_id",
"create_uid",
"write_uid",
"acc_number",
"sanitized_acc_number",
"acc_holder_name",
"active",
"allow_out_payment",
"create_date",
"write_date",
"message_main_attachment_id",
"aba_routing"
]
},
{
"table_name": "rel_server_actions",
"fields": [
"server_id",
"action_id"
]
},
{
"table_name": "ir_act_server_group_rel",
"fields": [
"act_id",
"gid"
]
},
{
"table_name": "ir_server_object_lines",
"fields": [
"id",
"server_id",
"col1",
"create_uid",
"write_uid",
"evaluation_type",
"value",
"create_date",
"write_date"
]
},
{
"table_name": "ir_actions_todo",
"fields": [
"id",
"action_id",
"sequence",
"create_uid",
"write_uid",
"state",
"name",
"create_date",
"write_date"
]
},
{
"table_name": "ir_act_client",
"fields": [
"id",
"binding_model_id",
"create_uid",
"write_uid",
"type",
"binding_type",
"binding_view_types",
"name",
"help",
"create_date",
"write_date",
"tag",
"target",
"res_model",
"context",
"params_store"
]
},
{
"table_name": "ir_act_report_xml",
"fields": [
"id",
"binding_model_id",
"create_uid",
"write_uid",
"type",
"binding_type",
"binding_view_types",
"name",
"help",
"create_date",
"write_date",
"paperformat_id",
"model",
"report_type",
"report_name",
"report_file",
"attachment",
"print_report_name",
"multi",
"attachment_use"
]
},
{
"table_name": "res_groups_report_rel",
"fields": [
"uid",
"gid"
]
},
{
"table_name": "report_paperformat",
"fields": [
"id",
"page_height",
"page_width",
"header_spacing",
"dpi",
"create_uid",
"write_uid",
"name",
"format",
"orientation",
"default",
"header_line",
"disable_shrinking",
"create_date",
"write_date",
"margin_top",
"margin_bottom",
"margin_left",
"margin_right"
]
},
{
"table_name": "ir_cron",
"fields": [
"id",
"ir_actions_server_id",
"user_id",
"interval_number",
"numbercall",
"priority",
"create_uid",
"write_uid",
"interval_type",
"cron_name",
"active",
"doall",
"nextcall",
"lastcall",
"create_date",
"write_date"
]
},
{
"table_name": "ir_cron_trigger",
"fields": [
"id",
"cron_id",
"create_uid",
"write_uid",
"call_at",
"create_date",
"write_date"
]
},
{
"table_name": "ir_filters",
"fields": [
"id",
"user_id",
"action_id",
"create_uid",
"write_uid",
"name",
"model_id",
"domain",
"context",
"sort",
"is_default",
"active",
"create_date",
"write_date"
]
},
{
"table_name": "ir_default",
"fields": [
"id",
"field_id",
"user_id",
"company_id",
"create_uid",
"write_uid",
"condition",
"json_value",
"create_date",
"write_date"
]
},
{
"table_name": "ir_exports",
"fields": [
"id",
"create_uid",
"write_uid",
"name",
"resource",
"create_date",
"write_date"
]
},
{
"table_name": "ir_exports_line",
"fields": [
"id",
"export_id",
"create_uid",
"write_uid",
"name",
"create_date",
"write_date"
]
},
{
"table_name": "ir_rule",
"fields": [
"id",
"model_id",
"create_uid",
"write_uid",
"name",
"domain_force",
"active",
"perm_read",
"perm_write",
"perm_create",
"perm_unlink",
"global",
"create_date",
"write_date"
]
},
{
"table_name": "rule_group_rel",
"fields": [
"rule_group_id",
"group_id"
]
},
{
"table_name": "ir_config_parameter",
"fields": [
"id",
"create_uid",
"write_uid",
"key",
"value",
"create_date",
"write_date"
]
},
{
"table_name": "ir_property",
"fields": [
"id",
"company_id",
"fields_id",
"value_integer",
"create_uid",
"write_uid",
"name",
"res_id",
"value_reference",
"type",
"value_text",
"value_datetime",
"create_date",
"write_date",
"value_float",
"value_binary"
]
},
{
"table_name": "ir_module_module_exclusion",
"fields": [
"id",
"module_id",
"create_uid",
"write_uid",
"name",
"create_date",
"write_date"
]
},
{
"table_name": "ir_demo",
"fields": [
"id",
"create_uid",
"write_uid",
"create_date",
"write_date"
]
},
{
"table_name": "ir_demo_failure",
"fields": [
"id",
"module_id",
"wizard_id",
"create_uid",
"write_uid",
"error",
"create_date",
"write_date"
]
},
{
"table_name": "ir_demo_failure_wizard",
"fields": [
"id",
"create_uid",
"write_uid",
"create_date",
"write_date"
]
},
{
"table_name": "report_layout",
"fields": [
"id",
"view_id",
"sequence",
"create_uid",
"write_uid",
"image",
"pdf",
"name",
"create_date",
"write_date"
]
},
{
"table_name": "base_enable_profiling_wizard",
"fields": [
"id",
"create_uid",
"write_uid",
"duration",
"expiration",
"create_date",
"write_date"
]
},
{
"table_name": "res_country",
"fields": [
"id",
"address_view_id",
"currency_id",
"phone_code",
"create_uid",
"write_uid",
"code",
"name_position",
"name",
"vat_label",
"address_format",
"state_required",
"zip_required",
"create_date",
"write_date"
]
},
{
"table_name": "res_country_res_country_group_rel",
"fields": [
"res_country_id",
"res_country_group_id"
]
},
{
"table_name": "res_country_group",
"fields": [
"id",
"create_uid",
"write_uid",
"name",
"create_date",
"write_date"
]
},
{
"table_name": "res_currency",
"fields": [
"id",
"name",
"symbol",
"decimal_places",
"create_uid",
"write_uid",
"full_name",
"position",
"currency_unit_label",
"currency_subunit_label",
"rounding",
"active",
"create_date",
"write_date"
]
},
{
"table_name": "res_country_state",
"fields": [
"id",
"country_id",
"create_uid",
"write_uid",
"name",
"code",
"create_date",
"write_date"
]
},
{
"table_name": "res_lang",
"fields": [
"id",
"create_uid",
"write_uid",
"name",
"code",
"iso_code",
"url_code",
"direction",
"date_format",
"time_format",
"week_start",
"grouping",
"decimal_point",
"thousands_sep",
"active",
"create_date",
"write_date"
]
},
{
"table_name": "res_partner_category",
"fields": [
"id",
"color",
"parent_id",
"create_uid",
"write_uid",
"parent_path",
"name",
"active",
"create_date",
"write_date"
]
},
{
"table_name": "res_partner_res_partner_category_rel",
"fields": [
"category_id",
"partner_id"
]
},
{
"table_name": "res_partner_title",
"fields": [
"id",
"create_uid",
"write_uid",
"name",
"shortcut",
"create_date",
"write_date"
]
},
{
"table_name": "res_partner_industry",
"fields": [
"id",
"create_uid",
"write_uid",
"name",
"full_name",
"active",
"create_date",
"write_date"
]
},
{
"table_name": "res_bank",
"fields": [
"id",
"state",
"country",
"create_uid",
"write_uid",
"name",
"street",
"street2",
"zip",
"city",
"email",
"phone",
"bic",
"active",
"create_date",
"write_date"
]
},
{
"table_name": "res_config",
"fields": [
"id",
"create_uid",
"write_uid",
"create_date",
"write_date"
]
},
{
"table_name": "res_config_installer",
"fields": [
"id",
"create_uid",
"write_uid",
"create_date",
"write_date"
]
},
{
"table_name": "res_currency_rate",
"fields": [
"id",
"currency_id",
"company_id",
"create_uid",
"write_uid",
"name",
"rate",
"create_date",
"write_date"
]
},
{
"table_name": "res_company_users_rel",
"fields": [
"cid",
"user_id"
]
},
{
"table_name": "res_groups_users_rel",
"fields": [
"gid",
"uid"
]
},
{
"table_name": "res_groups_implied_rel",
"fields": [
"gid",
"hid"
]
},
{
"table_name": "res_users_log",
"fields": [
"id",
"create_uid",
"write_uid",
"create_date",
"write_date"
]
},
{
"table_name": "res_users_identitycheck",
"fields": [
"id",
"create_uid",
"write_uid",
"request",
"password",
"create_date",
"write_date"
]
},
{
"table_name": "crm_convert_lead_mass_lead_rel",
"fields": [
"crm_lead2opportunity_partner_mass_id",
"crm_lead_id"
]
},
{
"table_name": "crm_lead2opportunity_partner_mass_res_users_rel",
"fields": [
"crm_lead2opportunity_partner_mass_id",
"res_users_id"
]
},
{
"table_name": "crm_merge_opportunity",
"fields": [
"id",
"user_id",
"team_id",
"create_uid",
"write_uid",
"create_date",
"write_date"
]
},
{
"table_name": "merge_opportunity_rel",
"fields": [
"merge_id",
"opportunity_id"
]
},
{
"table_name": "crm_lead_pls_update",
"fields": [
"id",
"create_uid",
"write_uid",
"pls_start_date",
"create_date",
"write_date"
]
},
{
"table_name": "crm_lead_pls_update_crm_lead_scoring_frequency_field_rel",
"fields": [
"crm_lead_pls_update_id",
"crm_lead_scoring_frequency_field_id"
]
},
{
"table_name": "crm_activity_report",
"fields": [
"id",
"lead_create_date",
"date_conversion",
"date_deadline",
"date_closed",
"subtype_id",
"mail_activity_type_id",
"author_id",
"date",
"body",
"lead_id",
"user_id",
"team_id",
"country_id",
"company_id",
"stage_id",
"partner_id",
"lead_type",
"active",
"won_status"
]
},
{
"table_name": "crm_lead",
"fields": [
"id",
"campaign_id",
"source_id",
"medium_id",
"message_main_attachment_id",
"message_bounce",
"user_id",
"team_id",
"company_id",
"stage_id",
"color",
"recurring_plan",
"partner_id",
"title",
"lang_id",
"state_id",
"country_id",
"lost_reason_id",
"create_uid",
"write_uid",
"phone_sanitized",
"email_normalized",
"email_cc",
"name",
"referred",
"type",
"priority",
"contact_name",
"partner_name",
"function",
"email_from",
"phone",
"mobile",
"phone_state",
"email_state",
"website",
"street",
"street2",
"zip",
"city",
"date_deadline",
"lead_properties",
"description",
"expected_revenue",
"prorated_revenue",
"recurring_revenue",
"recurring_revenue_monthly",
"recurring_revenue_monthly_prorated",
"active",
"date_closed",
"date_action_last",
"date_open",
"date_last_stage_update",
"date_conversion",
"create_date",
"write_date",
"day_open",
"day_close",
"probability",
"automated_probability",
"won_status",
"days_to_convert",
"days_exceeding_closing",
"reveal_id",
"iap_enrich_done",
"lead_mining_request_id"
]
},
{
"table_name": "crm_iap_lead_mining_request",
"fields": [
"id",
"lead_number",
"team_id",
"user_id",
"company_size_min",
"company_size_max",
"contact_number",
"preferred_role_id",
"seniority_id",
"create_uid",
"write_uid",
"name",
"state",
"search_type",
"error_type",
"lead_type",
"contact_filter_type",
"filter_on_size",
"create_date",
"write_date"
]
},
{
"table_name": "crm_iap_lead_helpers",
"fields": [
"id",
"create_uid",
"write_uid",
"create_date",
"write_date"
]
},
{
"table_name": "crm_iap_lead_industry",
"fields": [
"id",
"color",
"sequence",
"create_uid",
"write_uid",
"reveal_ids",
"name",
"create_date",
"write_date"
]
},
{
"table_name": "crm_iap_lead_role",
"fields": [
"id",
"color",
"create_uid",
"write_uid",
"reveal_id",
"name",
"create_date",
"write_date"
]
},
{
"table_name": "crm_iap_lead_seniority",
"fields": [
"id",
"create_uid",
"write_uid",
"reveal_id",
"name",
"create_date",
"write_date"
]
},
{
"table_name": "crm_iap_lead_mining_request_crm_tag_rel",
"fields": [
"crm_iap_lead_mining_request_id",
"crm_tag_id"
]
},
{
"table_name": "crm_iap_lead_mining_request_res_country_rel",
"fields": [
"crm_iap_lead_mining_request_id",
"res_country_id"
]
},
{
"table_name": "crm_iap_lead_mining_request_res_country_state_rel",
"fields": [
"crm_iap_lead_mining_request_id",
"res_country_state_id"
]
},
{
"table_name": "crm_iap_lead_industry_crm_iap_lead_mining_request_rel",
"fields": [
"crm_iap_lead_mining_request_id",
"crm_iap_lead_industry_id"
]
},
{
"table_name": "crm_iap_lead_mining_request_crm_iap_lead_role_rel",
"fields": [
"crm_iap_lead_mining_request_id",
"crm_iap_lead_role_id"
]
},
{
"table_name": "uom_category",
"fields": [
"id",
"create_uid",
"write_uid",
"name",
"create_date",
"write_date"
]
},
{
"table_name": "uom_uom",
"fields": [
"id",
"category_id",
"create_uid",
"write_uid",
"uom_type",
"name",
"factor",
"rounding",
"active",
"create_date",
"write_date"
]
},
{
"table_name": "account_analytic_plan",
"fields": [
"id",
"parent_id",
"company_id",
"color",
"create_uid",
"write_uid",
"name",
"parent_path",
"complete_name",
"default_applicability",
"description",
"create_date",
"write_date"
]
},
{
"table_name": "account_analytic_account",
"fields": [
"id",
"message_main_attachment_id",
"plan_id",
"root_plan_id",
"company_id",
"partner_id",
"create_uid",
"write_uid",
"name",
"code",
"active",
"create_date",
"write_date"
]
},
{
"table_name": "account_analytic_distribution_model",
"fields": [
"id",
"partner_id",
"partner_category_id",
"company_id",
"create_uid",
"write_uid",
"analytic_distribution",
"create_date",
"write_date",
"product_id",
"product_categ_id",
"account_prefix"
]
},
i can’t upload all the database due the characters limits
and the instructions that I used are:
```you name is Sale Assistant and you have access to files to answer employee questions about company policies
make sure to answer the User about any question related to Sale module
You are an SQL and odoo 16.0 expert
I have passed a JSON file to you that contains a list of objects. Each object represents a table, and within each object, there are fields associated with that table.
Here's a sample structure of the JSON file:
[
{
"table_name": "product_template",
"fields": [
"id",
"message_main_attachment_id",
"sequence",
"categ_id",
"uom_id",
"uom_po_id",
"company_id",
"color",
...
]
},
{
"table_name": "product_category",
"fields": [
"id",
"parent_id",
"create_uid",
"write_uid",
"name",
"complete_name",
...
]
},
// ... additional tables and fields
]
Please answer the user's question based on the file provided and some of the available table name and fields that provide in the file
Constraint:
1.Please understand the user's intention based on the user's question, and use the given table structure definition that in the file to create a grammatically correct sql query . If sql is not required, answer the user's question directly..
2.make sure for evry question that user provide you generate one sql query
3.Always limit the query to a maximum of 50 results unless the user specifies in the question the specific number of rows of data he wishes to obtain.
4.Please be careful not to mistake the relationship between tables and columns when generating SQL.
5.Please check the correctness of the SQL and ensure that the query performance is optimized under correct conditions.
6. - and make sure to respond with one sql query
- select 5 fields at most for each SQL query for example 'SELECT * FROM product_template' should be 'SELECT id, company_id, name, description, type FROM product_template'
- please ensure that all dependent fields are in the odoo 17.0 database table
- for each SQL query generated, double-check if all fields are current
User Question:
can you give me all tables in database ?
Please think step by step and respond according to the following JSON format:"{"sql": "SQL Query to run"}"
Ensure the response is correct json and can be parsed by Python json.loads.```
for example when I asked the assistant the question: “give me all the users in the database” it should give me "SELECT * FROM res_users;"
but he gives me "SELECT * FROM users;" which is not available in the database.
or
for example, it gives me this SQL response :
"SELECT id, name, login, active FROM res_users LIMIT 50"
which has the correct tables res_users but res_users doesn’t have the “name” field.
thank you in advance
Hi again -
I think there is a lot of complexity here given the sheer number of tables involved and I think the instructions you have provided are not reflective of this complexity, which inevitably leads to incorrect Assistant responses.
To @SomebodySysop’s point, the biggest challenge here will be to create a very clear picture of the landscape of tables and their fields. Even then, this will likely require a multi-step approach in order for the Assistant to generate a valid SQL query.
I also still struggle a bit to understand the bigger picture. Is this a sales tool or an internal tool for users to questions about company policies? What’s the scope of questions that users can ask?
2 Likes
Hi @jr.2509 ,
the Odoo framework has a complex database that manages users who use the platform additionally that odoo is based on modules each module handles a specific domain e.g.(account, sales, CRM, …) my idea is to create an assistant for each module, so I created the Sales assistant based on the tables of sales module,
I chose this approach to not upload the entire schema to limit the context of assistant to just Sales
tables.
and for the question that the client might ask for example :
Can you show me all the sales orders placed by a user_1?
The SQL that must be generated is :
SELECT * FROM sale_order WHERE partner_id = 44;
so, what are your suggestions?
thanks in advance 
Well, the starting point would be to create a very detailed overview of your different tables including their relationships.
The set of tables you have shared by itself is not self-explanatory and would be difficult for a model to make sense of. You would need a clear and sufficiently specific description of each table along with further definitions of the table fields and - as per above - a clear overview of how tables are and fields are interrelated.
Getting this right, is going to be a significant effort.
Incoming user requests would initially need to be evaluated to identify which table(s) house the information required to answer the question and based on that the model would then identify the query variables.
2 Likes
Thanks a lot for your insights 
.
FWIW, here is a video about strategies for doing text to SQL. Since I can’t paste links, you can search YouTube for the title “Text2SQL: The Dream versus Reality - Laurel Orr | Stanford MLSys #89” in channel “Stanford MLSys Seminars”.
3 Likes
Hey y’all I am also trying to make a chatbot similar to this but I am very new to this and I have a vague idea on how to start. @rigor08 how did you start off with this? My aim is to create a fullstack application with SpringBoot/Java, mySQL and React for frontend so that user can ask questions about soccer players as such. It will be a soccer database and a user can ask questions that get turned into SQL queries and those are executed similar to yours and show that data to the user. For example “Show me the player with the most goals in the last 3 games” is one of the prompts and it would find a sql query accordingly and show that data. Any help, criticism is greatly appreciated !!
This is an easy one.
- OpenAI Text to SQL
- Prompt Example - Natural Language to SQL
- https://platform.openai.com/examples/default-sql-translate
Start with the above. That’s the basics. Define your schema. But as has been stressed again and again above, you’ve got to do it in a way that clearly explains to the LLM how to respond to the questions it will get.
- OpenAI Functions calling the Poor Developers Vector Database
- Private Chat with CSV Data (@DevGirl explanation):
- My Prompt Example:
Look at the example from above:
Here is a case where, above and beyond the schema, you ALSO explain to the LLM:
- Queries with respect to users and sales orders will use the sale_order table
- The user id in the sales_order table is the partner_id field
1 Like
@SomebodySysop @rigor08 @thomas_packer @jr.2509 @omerk203
Hi, Can anyone of you share insights on as how can i enable my assistant to have access of my live database. Basically what i want is that my user will ask the assistant questions (like get me top 5 brands ) so for this assistant must have access to database right? so that it will return something like these are top 5 brands … so how can i achieve that…
i have this kind of flow in mind to use a proxy server.
- user enters a prompt
- assistant then generates a relevant db query
- the query is passed to api (proxy server) which will feetch the data from the database.
- data is converted to natural language by assistant and then passed to user.
is there a way to eliminate the step 3 and let assistant handle getting data from the live database. or is this a necessary step.
1 Like
I don’t think there is a way to eliminate step 3 if your information is housed in a separate DB.
1 Like
@jr.2509 yeah my database is hosted (live) that is what i discussed with my team lead that assistant can’t access and manipulate over databases or sites like with custom gpt actions? am i in the right here?