Designing a Chatbot Interview App Database – Part 7: Storing Answers

by DL Keeshin

---

November 7, 2024

---

---

In my last post, I described how I created a process to retrieve and format interview questions using a simple web app prototype. Today I'll focus on sending answers back to the database.

Database Changes

Reviewing the design, I noticed that the interview.question table was missing a critical foreign key, interview_id. This ID links each question back to its corresponding interview in the client.interview table. Adding this ID ensures that questions are correctly associated with specific interviews, enabling better data retrieval and organization. Here are the needed changes:

 ALTER TABLE interview.question
ADD COLUMN interview_id UUID NULL;

UPDATE interview.question 
SET interview_id = (SELECT interview_id FROM client.interview LIMIT 1);  -- Example assignment for testing

ALTER TABLE interview.question
ADD CONSTRAINT fk_interview_id FOREIGN KEY (interview_id)
REFERENCES client.interview (interview_id)
ON UPDATE NO ACTION
ON DELETE NO ACTION;

These steps link necessary tables, making it easier to retrieve questions and gather the required IDs when sending answers back.

Creating a View for Simplified Querying

To streamline question retrieval, I created a view, interview.vw_question_lookup. Views are useful because they provide a virtual table that simplifies complex joins, keeping the main query clean, readable, and easily securable. Here’s the view definition:

CREATE VIEW interview.vw_question_lookup AS
SELECT ir.email_ AS email_,
       ir.role_id AS role_id, 
       cr.description_ AS description_, 
       iq.question_id AS question_id,
       ir.respondent_id AS respondent_id,
       iq.syntax_ AS question_text,
       iq.gui_type AS gui_type,
       iq.help_->>'help_text' AS help_text,
       iq.help_->'help_list' AS help_list,
       iq.sort_order AS sort_order
FROM interview.respondent ir
INNER JOIN client.role cr ON ir.role_id = cr.role_id
INNER JOIN client.interview ci ON cr.role_id = ci.role_id
INNER JOIN interview.question iq ON iq.interview_id = ci.interview_id;

By using this view, I can pass an email address to retrieve the associated interview questions for a given user. Here’s how the fetch_questions function in interview_form.py fetches questions from this view:

def fetch_questions(cur, email):
    cur.execute("""
        SELECT
            email_,
            respondent_id,
            description_,
            question_id,
            question_text,
            gui_type,
            help_text,
            help_list
        FROM
            interview.vw_question_lookup
        WHERE
            email_ = %s ORDER BY sort_order;
    """, (email,))
    
    questions = cur.fetchall()
    formatted_questions = [(row[4], row[5], row[6], row[7], row[3], row[1]) for row in questions]
    
    return formatted_questions

The cur object represents an active database connection, allowing the script to reuse the connection without opening new ones each time a SQL statement needs to run.

Stored Procedure for Answers

To store answers, I created a stored procedure, interview.insert_answer. Stored procedures are helpful because they encapsulate logic within the database, making operations more efficient and performant. They reduce the amount of SQL embedded in application code. Here’s the procedure:

CREATE OR REPLACE PROCEDURE interview.insert_answer(
    in_question_id UUID,
    in_respondent_id UUID,
    in_answer JSONB,
    in_answer_date DATE,
    in_source VARCHAR
) 
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
    INSERT INTO interview.answer_in_progress (
        question_id, 
        respondent_id, 
        answer_, 
        answer_date, 
        source_
    ) VALUES (
        in_question_id, 
        in_respondent_id, 
        in_answer, 
        in_answer_date, 
        in_source
    );
END;
$BODY$;

Processing Answers in the App

When users submit their answers via the form, the following code in the app handles the submission:

if request.method == 'POST':
    submitted_by = email
    answer_date = date.today()
    source = "form submission"
    
    for key, value in request.form.items():
        if key.startswith("question_id_"):
            question_id_n = key.split("question_id_")[1]
            question_id = value
            respondent_id = request.form.get(f"respondent_id_{question_id_n}")
            answer = request.form.get(f"response_{question_id_n}")
            answer_jsonb = json.dumps(answer) if answer else None
            insert_answer(cur, question_id, respondent_id, answer_jsonb, answer_date, source)

    conn.commit()
    cur.close()
    conn.close()

    return "Responses submitted successfully!"

This code extracts values from each form field and submits the answers using the insert_answer function.

It also checks if a form field key starts with "question_id_" to identify a question entry. It extracts the question ID suffix, retrieves the question, respondent, and answer values, and converts the answer to JSON if it's non-empty. The question_id and respondent_id are stored on the form as hidden fields the app renders from form.html:

<input type="hidden" name="question_id_{{ loop.index }}" value="{{ question_id }}">
<input type="hidden" name="respondent_id_{{ loop.index }}" value="{{ respondent_id }}">

These fields ensure that each answer is correctly linked to the relevant IDs in the database.

This post describes linking questions to interviews via a foreign key, creating a view to fetch questions by respondent, and using a stored procedure to save answers efficiently in JSON format. The design enables smooth data retrieval and storage for interview responses.

If you want to try generating a kds_discovery test database and run the Python code described here, see the updated section under "Database Installation" in the README file here.

As always, thanks for stopping by.