Surveys (Interviews)

Surveys, referred to as interviews in the database, comprise sequential questions and answers presented to patients or answered on their behalf by a provider. The interview data model is composed of four main tables.

table	description
`overlord_interview_profile`	This is the top-level definition of an interview. It holds the basic high-level information regarding an interview definition.
`overlord_interview_profileversion`	The profile version defines the questions, answer types, and answer options if applicable. It's the blueprint for surveys that are assigned to patients.
`overlord_interview_interview`	This table tracks instance of interview assigned to patients. It points back to the profile version that defines the interview.
`overlord_interview_question`	This table contains a row for every question and answer pair from the interview.

Interpreting Answers

Interview data is often used to create a table of all questions and answers submitted by patients for in-depth analysis. However, due to the flexibility of the interview feature, understanding certain specifics is necessary to accurately flatten the data.

The overlord_interview_question table contains several fields that need clarification. Specifically, the content column houses a JSON structure that outlines the question presented to the patient. This structure includes the question, answer-type, answer-options, and the next-state which is the subsequent question based on the patient's response. An example of this structure is provided below.

{
  "question": "How confident are you that you can take care of your health needs?",
  "answer-type": "option",
  "answer-options": {
    "values": [
      { "value": "very_confident", "display": "Very confident" },
      { "value": "pretty_confident", "display": "Pretty confident" },
      { "value": "a_little_confident", "display": "A little confident" },
      { "value": "not_at_all_confident", "display": "Not at all confident" }
    ],
    "style": "button"
  },
  "next-state": {
    "very_confident": {
      "type": "question",
      "priority": 1,
      "value": "ches_hc_experience"
    },
    "pretty_confident": {
      "type": "question",
      "priority": 2,
      "value": "ches_hc_experience"
    },
    "a_little_confident": {
      "type": "question",
      "priority": 3,
      "value": "ches_hc_experience"
    },
    "not_at_all_confident": {
      "type": "question",
      "priority": 4,
      "value": "ches_hc_experience"
    }
  }
}

Valid answer-types are:

calendar
mutli-select
number
option
range
text

The value column in the overlord_interview_question table contains the raw answer provided by the patient. For free-form inputs like text, number, and date, this is the exact data entered by the patient. For answer-types where the patient selects from pre-defined answers, such as multi-select and option answers, this is a key that describes the answer. This key can be used to look up the text that was displayed to the patient in the content column.

Examples

List all Survey Profiles

SELECT
    iv_profile.id AS profile_id,
    iv_profile.name AS profile_name
FROM overlord_interview_profile AS iv_profile
LIMIT 50

Single Survey Q&A Export

Below is an example SQL query for exporting a complete list of question and answer details for a single interview profile. Note that this example filters for an interview state of stopped, indicating that the interview is either completed or expired. It also returns only interviews that were assigned to the patient on or after 2024-01-01.

SELECT
    iv_question.id AS question_id,
    indv.first_name AS first_name,
    indv.last_name AS last_name,
    indv.birth_date AS dob,
    iv_profile.name AS interview_name,
    iv_version.name AS interview_version,
    iv_interview.id AS interview_id,
    iv_interview.created_at AS assigned_at,
    iv_question.answered_at AS answer_at,
    JSON_EXTRACT_PATH_TEXT(iv_question.content, 'question') AS question,
    CASE
        WHEN
            JSON_EXTRACT_PATH_TEXT(iv_question.content, 'answer_type') = 'option'
        THEN
            JSON_EXTRACT_PATH_TEXT(iv_question.content, 'options', iv_question.value)
        ELSE
            iv_question.value
    END AS answer
FROM overlord_interview_profile AS iv_profile
JOIN overlord_interview_profileversion AS iv_version
    ON iv_version.profile_id = iv_profile.id
JOIN overlord_interview_interview AS iv_interview
    ON iv_interview.version_id = iv_version.id
JOIN overlord_interview_question AS iv_question
    ON iv_question.interview_id = iv_interview.id
JOIN identity_core_individual AS indv
    ON iv_interview.individual_id = indv.individual_id
WHERE iv_profile.id = '3eba45d03ebc41a0936ed7e08d5671b3' -- replace with target interview ID
    AND iv_interview.state = 'stopped'
    AND iv_interview.created_at >= '2024-01-01'
ORDER BY
    iv_interview.id ASC,
    iv_question.answered_at DESC
LIMIT 50

Interpreting Answers​

Examples​

Interpreting Answers

Examples