Creating an OpenAI based chatbot with Clinical Trials Database Part 2

My last blog post I show how OpenAI enabled database chat engine help to analyze clinical trials databases. It highlights how such technology can uncover hidden insights by enabling users to interact with the data using conversational language. The key points what i have seen:

1. Enhanced Data Accessibility: The chat engine allows users to ask complex questions in plain language, making it easier to access and interpret data from clinical trials.
2. Improved Efficiency: chat engine significantly speeds up the process of extracting valuable information from large datasets.
3. Actionable Insights: The tool helps in identifying trends and patterns that might be missed through traditional data analysis methods, thus facilitating more informed decision-making.

This post is the extension of my previous post where i give a detailed walk through of integrating a Natural Language Processing (NLP) based chat engine with a Clinical Trial (AACT) PostgreSQL database and this can be extended to any sql database as we have SQLAlchemy. Below, I’ll break down the code structure you provided, explain why each component is needed, and why it’s implemented this way. The code is posted on my github repo to use . This is a minimal code to get started with the database chat.

This function establishes a connection to the PostgreSQL database using the connection parameters defined in the Config object.

def connect_db(config: Config):
    conn = psycopg2.connect(
        host=config.postgresConnection.host,
        port=config.postgresConnection.port,
        database=config.postgresConnection.database,
        user=config.postgresConnection.user,
        password=config.postgresConnection.password
    )
    return conn

def get_schema(conn):
    with conn.cursor(cursor_factory=RealDictCursor) as cursor:
        cursor.execute("""
             SELECT
                table_name,
                column_name,
                data_type,
                is_nullable,
                column_default
            FROM
                information_schema.columns
            WHERE
                table_schema = 'ctgov'
            ORDER BY
                ordinal_position;
        """)
        schema = cursor.fetchall()
        return schema

The get_schema function retrieves the schema of the database tables from the information_schema of PostgreSQL. It fetches metadata like table names, column names, data types, and other attributes. Understanding the database schema is crucial when writing queries or interpreting data. This function automates the extraction of this information, making it accessible for further processing.

get_example_rows help in understanding the type and nature of data stored in each table, which is useful when constructing queries or analyzing the schema. Fetches a single example row from each table in the database.

def get_example_rows(conn, tables):
    example_rows = {}
    with conn.cursor(cursor_factory=RealDictCursor) as cursor:
        for table_name in tables:
            cursor.execute(f"SELECT * FROM {table_name} LIMIT 1;")
            example_row = cursor.fetchone()
            example_rows[table_name] = example_row
    return example_rows

format_schema makes the schema information easily understandable, which is critical when interacting with the database, especially for those unfamiliar with the underlying structure. Formats the schema and example row information into a human-readable string. Each table is listed with its columns, data types and example values.

def format_schema(schema, example_rows):
    tables = {}
    for row in schema:
        table_name = row['table_name']
        if table_name not in tables:
            tables[table_name] = []
        tables[table_name].append(row)

    table_strings = []
    for table_name, columns in tables.items():
        column_strings = []
        for column in columns:
            example_value = 'N/A' if example_rows[table_name] is None else example_rows[table_name].get(column['column_name'], 'N/A')
            column_string = f"  {column['column_name']} {column['data_type']} {'NULL' if column['is_nullable'] == 'YES' else 'NOT NULL'}; Example: {example_value}"
            column_strings.append(column_string)
        table_string = f"Table {table_name}:\n" + "\n".join(column_strings)
        table_strings.append(table_string)

    return '\n\n'.join(table_strings)

ask_openai Interacts with OpenAI’s API to generate SQL queries based on a natural language prompt provided by the user. It automates the generation of SQL queries, allowing users to query the database using plain English. This is particularly useful for non-technical users who may not be familiar with SQL syntax.

def ask_openai(prompt: str, config: Config):
    client = Client(api_key=config.openAIAPIKey)
    response = client.chat.completions.create(
        model=config.openAIModel,
        messages=[
            {"role": "system", "content": "You are a database analyst and data scientist."},
            {"role": "user", "content": prompt}
        ],
        temperature=0.1,
        max_tokens=2500
    )
    return response['choices'][0]['message']['content'].strip()

In the main part of the code ,the purpose of WordCompleter and prompt_toolkit in the code is to enhance the user experience by providing an interactive command-line interface with features like auto completion, which makes it easier and more efficient for users to interact with the system.prompt_toolkit is a Python library designed for building interactive command-line interfaces. It offers a variety of features that improve the usability and interactivity of command-line applications, such as Autocompletion, Syntax Highlighting, Multi-line Input ,Input Validation

    table_completer = WordCompleter(tables, ignore_case=True)

    selected_table = prompt_toolkit.prompt("Select a table: ", completer=table_completer)
    if selected_table not in tables:
        print("Invalid table name. Exiting.")
        return

    columns = [row['column_name'] for row in schema if row['table_name'] == selected_table]
    column_completer = WordCompleter(columns, ignore_case=True)

    initial_question = prompt_toolkit.prompt("Ask me a question about this database, and I'll try to answer! (q to quit): ", completer=column_completer)
    if initial_question.lower() == 'q':
        return

    messages = [
        {"role": "system", "content": "You are a helpful assistant that writes SQL queries in order to answer questions about a database."},
        {"role": "user", "content": f"Hello, I have a database with the following schema:\n\n{schema_string}\n\nI'd like to work with you to answer a question I have. I can run several queries to get the answer, and tell you the results along the way. I'd like to use the fewest queries possible, so use joins where you can. If you're not sure what to do, you can ask me questions about the database or run intermediate queries to learn more about the data, but I can only run one query at a time.\n\nThe question I have is:\n\n\"{initial_question}\""}
    ]

There is a infinite loop while True: allows the user to repeatedly interact with the system, ask multiple questions, and receive responses from the GPT model without restarting the program. The loop continues to prompt the user for input until they choose to exit the program with the GPT model and the database until the user decides to quit.

Now when one execute the code with correct configuration it ask to select a table from database . This step ensures that the user is interacting with a valid table in the database. It prevents errors by limiting the input to known table names, thereby reducing the chance of the user entering an invalid or incorrect table name.

# Prompt user to select a table
selected_table = prompt_toolkit.prompt("Select a table: ", completer=table_completer)
if selected_table not in tables:
    print("Invalid table name. Exiting.")
    return

# Create a completer for column names of the selected table
columns = [row['column_name'] for row in schema if row['table_name'] == selected_table]
column_completer = WordCompleter(columns, ignore_case=True)

Now comes the interesting part as this is just a database chat engine which generates queries but does have the built in semantics to understand simple to complex questions like

Identifying the 5 drugs that were stopped or terminated in trials for safety concerns in 2023 ?

Placebo                  43
Gantenerumab              6
sotrovimab                4
Paclitaxel                4
Dexamethasone             3

Now placebo is not important we can ask to remove in the chat and get back results which is like “remove placebo and get results”

drug_name        stop_count
-------------  ------------
Gantenerumab              6
Paclitaxel                4
sotrovimab                4
Dexamethasone             3
Avelumab                  3

We have Avelumab ,why Avelumab was stopped. Though the trials are stopped but the output does give correct response because it is not stopped for safety , as safety appeared as keyword and this is not correct semantically.

nct_id       why_stopped                                                                                                                                                                                                                      completion_date
-----------  -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -----------------
NCT03565991  The study was terminated since there was no need for further safety or efficacy data to be collected. The participants having benefit from the investigational treatments have been moved to a continuation study (NCT05059522)  2023-02-03
NCT02554812  The study was terminated since there was no need for further safety or efficacy data to be collected. The participants having benefit from the Investigational treatments have been moved to a continuation study (NCT05059522)  2023-03-23
NCT05568407  The trial terminated due to the inability to recruit the planned number of subjects. The decision was not based on any safety and/or efficacy concerns.                                                                          2023-06-16

Now how to get those drugs which are actually meant to be stopped for safety related issues , i use the prompt below

Retrieve 5 drugs where the reason for stopping is exclusively due to safety concerns. Exclude cases where the stoppage is due to ‘no safety concerns’ or associated with other negative conditions. Additionally, search for synonyms of ‘safety’ to broaden the search scope

This generated a SQL query which gives

SELECT
    i.name AS drug_name,
    s.nct_id,
    s.why_stopped,
    s.completion_date
FROM
    studies s
JOIN
    interventions i ON s.nct_id = i.nct_id
WHERE
    (s.why_stopped ILIKE '%safety concern%'
     OR s.why_stopped ILIKE '%adverse event%'
     OR s.why_stopped ILIKE '%safety issue%'
     OR s.why_stopped ILIKE '%safety%'
     OR s.why_stopped ILIKE '%risk%'
     OR s.why_stopped ILIKE '%hazard%'
     OR s.why_stopped ILIKE '%adverse reaction%'
     OR s.why_stopped ILIKE '%side effect%')
    AND s.completion_date BETWEEN '2023-01-01' AND '2023-12-31'
    -- Exclude cases indicating no safety concerns or negative conditions
    AND s.why_stopped NOT ILIKE '%no need for further safety%'
    AND s.why_stopped NOT ILIKE '%not based on any safety%'
    AND s.why_stopped NOT ILIKE '%business decision%'
    AND s.why_stopped NOT ILIKE '%due to business reasons%'
    AND s.why_stopped NOT ILIKE '%no safety concerns%'
    AND s.why_stopped NOT ILIKE '%no safety issues%'
    AND s.why_stopped NOT ILIKE '%administrative reasons%'
    AND s.why_stopped NOT ILIKE '%not appropriate for analysis%'
    AND s.why_stopped NOT ILIKE '%feasibility%'
GROUP BY
    i.name, s.nct_id, s.why_stopped, s.completion_date
ORDER BY
    s.completion_date DESC
LIMIT 5;

drug_name               nct_id       why_stopped                                                                                                                                                                              completion_date
----------------------  -----------  ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -----------------
Docetaxel               NCT04921358  Due to safety risks and unfavorable risk-benefit assessment results, the sponsor has decided to voluntarily terminate the study.                                                         2023-12-20
Sitravatinib            NCT04921358  Due to safety risks and unfavorable risk-benefit assessment results, the sponsor has decided to voluntarily terminate the study.                                                         2023-12-20
Tislelizumab            NCT04921358  Due to safety risks and unfavorable risk-benefit assessment results, the sponsor has decided to voluntarily terminate the study.                                                         2023-12-20
Aflibercept Ophthalmic  NCT04707625  IRB stopped study due to safety concerns. No further data collection and what has been collected is not appropriate for analysis.                                                        2023-12-18
Belumosudil (KD025)     NCT03640481  The sponsor has decided to prematurely terminate the study due to the challenges encountered in recruiting adolescent participants. This decision was made without any safety concerns.  2023-12-11

What you see other than Belumosudil rest it almost generated data of drugs which are stopped for safety concerns and also extracted synonyms for safety like adverse , hazard , risk, side effect which is quiet cool . However the same query with 10 drugs the error is more prominent .

drug_name                                           nct_id       why_stopped                                                                                                                                                                                                                                               completion_date
--------------------------------------------------  -----------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -----------------
Docetaxel                                           NCT04921358  Due to safety risks and unfavorable risk-benefit assessment results, the sponsor has decided to voluntarily terminate the study.                                                                                                                          2023-12-20
Sitravatinib                                        NCT04921358  Due to safety risks and unfavorable risk-benefit assessment results, the sponsor has decided to voluntarily terminate the study.                                                                                                                          2023-12-20
Tislelizumab                                        NCT04921358  Due to safety risks and unfavorable risk-benefit assessment results, the sponsor has decided to voluntarily terminate the study.                                                                                                                          2023-12-20
Aflibercept Ophthalmic                              NCT04707625  IRB stopped study due to safety concerns. No further data collection and what has been collected is not appropriate for analysis.                                                                                                                         2023-12-18
Aqueous Gel                                         NCT05491603  Suspended: Study halted prematurely but potentially will resume. Sponsor suspension, and not due to safety or site-related matters.                                                                                                                       2023-11-30
DBI-001                                             NCT05491603  Suspended: Study halted prematurely but potentially will resume. Sponsor suspension, and not due to safety or site-related matters.                                                                                                                       2023-11-30
DBI-002                                             NCT05491603  Suspended: Study halted prematurely but potentially will resume. Sponsor suspension, and not due to safety or site-related matters.                                                                                                                       2023-11-30
Iloperidone                                         NCT05344365  Study was terminated for business reasons; not due to safety or efficacy concerns.                                                                                                                                                                        2023-11-30
PROFEMUR® Gladiator HA Coated Modular Femoral Stem  NCT02314611  The primary reason for this study was to collect safety and performance data to meet regulatory requirements. However, these requirements have changed over time. Our recentassessment has determined that now there is sufficient data on this product.  2023-11-30
Dexamethasone                                       NCT05289492  Company operational decision. Decision to stop study is not due to safety or efficacy concerns                                                                                                                                                            2023-11-29

Further fine tuning of the prompt :

Retrieve a list of 10 drugs from a database where the only reason for discontinuation is explicitly due to safety concerns. The query should:

1. Use wildcards to capture any variation of phrases indicating the drug was stopped for reasons directly related to safety, such as ‘not related safety’ or ‘not safety ’.*
2. Exclude any records where the discontinuation reason includes phrases like ‘no safety concerns’, ‘not due to safety’, ‘without any safety concerns’, ‘not related to any efficacy or safety’, using similar wildcard patterns to ensure comprehensive exclusion.
3. Specifically exclude all entries where the drug type resembles a placebo, using wildcards to catch variations like ‘placebo’ or similar terms.
4. Limit the output to 10 records for focused analysis.

Adjust the query parameters to accommodate variations in language around safety concerns and placebos, ensuring both inclusivity and exclusivity as per the research requirements. Specify that the output should include the drug names and any other relevant details available in the database.

Which generated a big sql query

SELECT DISTINCT
    i.name AS drug_name,
    s.nct_id,
    s.why_stopped,
    s.completion_date
FROM
    studies s
JOIN
    interventions i ON s.nct_id = i.nct_id
WHERE
    -- Include only records with reasons directly related to safety
    (s.why_stopped ILIKE '%safety concern%'
     OR s.why_stopped ILIKE '%adverse event%'
     OR s.why_stopped ILIKE '%safety issue%'
     OR s.why_stopped ILIKE '%safety%'
     OR s.why_stopped ILIKE '%risk%'
     OR s.why_stopped ILIKE '%hazard%'
     OR s.why_stopped ILIKE '%adverse reaction%'
     OR s.why_stopped ILIKE '%side effect%')
    AND s.completion_date BETWEEN '2023-01-01' AND '2023-12-31'
    -- Exclude any records with non-safety-related phrases
    AND s.why_stopped NOT ILIKE '%no safety concerns%'
    AND s.why_stopped NOT ILIKE '%not due to safety%'
    AND s.why_stopped NOT ILIKE '%no safety issues%'
    AND s.why_stopped NOT ILIKE '%not based on any safety%'
    AND s.why_stopped NOT ILIKE '%without any safety concerns%'
    AND s.why_stopped NOT ILIKE '%not related to any efficacy or safety%'
    AND s.why_stopped NOT ILIKE '%not related to safety%'
    AND s.why_stopped NOT ILIKE '%not a safety%'
    AND s.why_stopped NOT ILIKE '%business decision%'
    AND s.why_stopped NOT ILIKE '%due to business reasons%'
    AND s.why_stopped NOT ILIKE '%administrative reasons%'
    AND s.why_stopped NOT ILIKE '%feasibility%'
    AND s.why_stopped NOT ILIKE '%realignment%'
    AND s.why_stopped NOT ILIKE '%change in the benefit-risk balance%'
    AND s.why_stopped NOT ILIKE '%sponsor suspension%'
    AND s.why_stopped NOT ILIKE '%company operational decision%'
    AND s.why_stopped NOT ILIKE '%study halted prematurely%'
    AND s.why_stopped NOT ILIKE '%meet regulatory requirements%'
    -- Exclude placebos and their synonyms
    AND i.name NOT ILIKE '%placebo%'
    AND i.name NOT ILIKE '%control%'
    AND i.name NOT ILIKE '%standard of care%'
GROUP BY
    i.name, s.nct_id, s.why_stopped, s.completion_date
ORDER BY
    s.completion_date DESC
LIMIT 10;

The results are good but still it has some errors less than previous one.

drug_name                                              nct_id       why_stopped                                                                                                                                                                                                                                          completion_date
-----------------------------------------------------  -----------  ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -----------------
Docetaxel                                              NCT04921358  Due to safety risks and unfavorable risk-benefit assessment results, the sponsor has decided to voluntarily terminate the study.                                                                                                                     2023-12-20
Sitravatinib                                           NCT04921358  Due to safety risks and unfavorable risk-benefit assessment results, the sponsor has decided to voluntarily terminate the study.                                                                                                                     2023-12-20
Tislelizumab                                           NCT04921358  Due to safety risks and unfavorable risk-benefit assessment results, the sponsor has decided to voluntarily terminate the study.                                                                                                                     2023-12-20
Aflibercept Ophthalmic                                 NCT04707625  IRB stopped study due to safety concerns. No further data collection and what has been collected is not appropriate for analysis.                                                                                                                    2023-12-18
Tranexamic Acid                                        NCT03954314  Upon the Data Safety Monitoring Board review of the interim analysis (75% of participants have finished their follow-up) on November 17, 2023, they made a recommendation to stop recruitment into the trial.                                        2023-11-28
Buprenorphine injection (BUP-Inj)                      NCT05283304  Side effects of medication                                                                                                                                                                                                                           2023-11-19
Body weight support system with balance perturbations  NCT05110300  Interim analysis showed that there was no added benefit to participants, so recruitment was terminated for patient safety.                                                                                                                           2023-11-06
Benralizumab                                           NCT04612790  Independent Data Monitoring Committee recommended terminating the study following a pre-planned analysis as the efficacy results did not meet the pre-defined futility guidelines. There were no new safety concerns identified from this analysis.  2023-10-26
Filgotinib                                             NCT03201445  Early Termination for Reasons other than Safety                                                                                                                                                                                                      2023-10-24
Durvalumab                                             NCT04866017  This decision was conducted by the sponsor and not driven by safety concerns as no new safety signals have been observed in the ociperlimab program.                                                                                                 2023-10-17

As we still see these errors are coming some ways we can handle is

1 . create a column with sentiments with binary values use LLM then look for sentiments and code them 0 and 1 and then perform prompts.

2 . Implement a user feedback system where users can flag irrelevant results. Use this feedback to refine both the exclusion list and the model’s training data, improving the system’s ability to exclude irrelevant results autonomously over time.

3. Incorporate regular expressions (regex) to detect and exclude complex patterns in text data.

4. Build a advanced RAG based approach to solve this .

The code to execute the example content is available on my github repo to use . This is a minimal code to get started with the database chat.

f you enjoyed reading this, and/or want to be kept in the loop about the next blog, follow me on Medium.

Feel free to connect with me on LinkedIn and if you feel this can be revolutionary in your industry research/job implementation.

if you feel to leave some tips send it **here .**