Schema
Schema v2
Section titled “Schema v2”Note: For a thorough analysis of the schema check out the interactive schema representation generated with SchemaSpy of our demo sqlite export db.
Dossier info
Section titled “Dossier info”dossiers
Section titled “dossiers”| Attribute | Description |
|---|---|
| id | our internal id |
| external_identifier | also known as epd_id or clientid, the clientid we got over sso when internally_managed is false |
| internally_managed | true when dossier not created through sso, but within RoQua admin area |
| tester | true if tagged as test account, should not be used for research |
| metadata | json string of extra metadata added to dossier. |
respondents
Section titled “respondents”A respondent is a someone who fills out a questionnaire. It’s the you in “How are you?” and “How is your child/patient” on a questionnaire. The one who enters the data into roqua is not always the respondent.
Right now there is only one respondent per type, but that will change in the future.
| Attribute | Description |
|---|---|
| id | Our internal id. |
| dossier_id | foreign key to dossier this respondent belongs to |
| type | patient, parent, second_parent, teacher, caregiver, profess. second_parent will likely be merged into parent in the future |
| email_present | true if we have an email in our system for this respondent |
| label | human readable specifier. e.g. Moeder, Medewerker, Buurman |
protocol_subscriptions
Section titled “protocol_subscriptions”A protocol subscription is when an automatic protocol is activated for a dossier. The same protocol can be subscribed multiple times for one dossier, but only once the previous subscription has been stopped (either automatically upon completion, or by cancelling manually).
| Attribute | Description |
|---|---|
| dossier_id | foreign key to the dossier which is subscribed to this protocol |
| protocol_id | foreign key to the protocol that is subscribed to |
| start_at | the first day that responses will be created on |
| stop_at | the last day that responses will be created on |
| status | scheduled when the automatic protocol is activated but the first questionnaires haven’t been prepared yet (usually because at time of export, start_at was in the future; started while the protocol is ongoing; paused when the protocol is on hold for a while (currently not feature exposed in the API or UI); stopped when stopped (either because stop_at is in the past, or because it was stopped manually in the UI) |
Response data
Section titled “Response data”responses
Section titled “responses”A Response the set of answers to one fill out of a questionnaire. Most attributes descriptions can be found at response endpoint
| Attribute | Description |
|---|---|
| id | our internal id. Same in all api’s. |
| dossier_id | foreign key to dossier who the answer belongs to. |
| respondent_id | foreign key to respondent who the answers belong to. |
| entered_by_id | foreign key to professional who typed in the answer if the respondent didn’t do it directly (so copying from paper or doing an interview) |
| entered_at | datetime the response was entered into our system. |
| observation_time | datetime the respondent answered the questions (can be changed in the interface, when copied from paper) |
| questionnaire_key | roqua-key of questionnaire (in a few cases different from quby-key) points to questionnaires.key |
| questionnaire_variant | roqua-key of specific questionnaire variant (e.g. bulk version) |
| requester_id | foreign key to professional who prepared the response in the RoQua epd interface |
| status | inactive / aborted / completed / scheduled / open / expired / unattached |
| non_response_id | foreign key to non_response |
| open_from | datetime start of timewindow to fill out the response, usually the time we send an email/sms |
| open_till | datetime end of the timewindow to fill out the response |
| started_at | datetime the respondent opened the response |
| hide_pii_from_researchers | Should show the respondent id with the response_values/scores. |
| hide_values_from_professionals | Should not show the response_values/scores within a dossiers. |
| notes | notes added by a professional. |
| measurement_id | foreign key to measurement this response was prepared from. |
| team_id | foreign key to team that was active when response was prepared. |
response_values
Section titled “response_values”Answer to a single question in a response
| Attribute | Description |
|---|---|
| response_id | |
| field_key | often the question key, but option_key for multi_select and date_part_keys for date_parts e.g. v_1, v_2_a3, v_3_yyyy. |
| data | value of answer e.g. ‘2’ for radio with value 2, ‘1’ for checked checkbox, ‘hello’ for text |
| question_option_key. | option key in case the question is of type single_select. |
response_scores
Section titled “response_scores”Scores are calculated values, based on the response_values.
| Attribute | Description |
|---|---|
| response_id | int |
| score_key | |
| score_subkey | main score is called value, |
| data | stringified result of the subscore integer/float/string |
non_responses
Section titled “non_responses”| Attribute | Description |
|---|---|
| timestamp | Time at of non-response, can be set by the user, defaults to creation time. |
| reason_group: | e.g. “Patiënt is wel benaderd voor de ROM |
| reason_option: | e.g. “Patiënt retourneert meetinstrument niet.” |
| reason_code: | e.g. “04” |
| reason_other: | user entered text |
response_flags
Section titled “response_flags”Flags turn on/off questions in a response.
| Attribute | Description |
|---|---|
| response_id | foreign key to responses |
| key | e.g. first_measurement_of_day / last_measurement_of_day / ptr_semi_slaap / caps_5_copisac |
| value | true/false/null |
Primary key: [response_id, key]
response_textvars
Section titled “response_textvars”Textvars allow for customizing questions to e.g. make them more specific.
| Attribute | Description |
|---|---|
| response_id | foreign key to responses |
| key | e.g. adhd_rs5_l_tijdsperiode / ptr_semi_eigen_vraag1 |
| value | text |
Primary key: [response_id, key]
Global data
Section titled “Global data”questionnaires
Section titled “questionnaires”| Attribute | Description |
|---|---|
| key | Primary key string |
| title | |
| short_description | |
| description | |
| license | free / pay_per_completion / private / deprecated / unknown |
| sbg_key |
questions
Section titled “questions”| Attribute | Description |
|---|---|
| questionnaire_key | point to questionnaires.key |
| key | |
| position | order on the screen |
| title | title on screen for the respondent |
| context_free_title | title for professional, adds context if needed, often removes examples. defaults to title |
| sbg_key | |
| question_type | text / integer / float / single_select / multi_select / date_parts |
Primary key: [questionnaire_key, key]
question_options
Section titled “question_options”| Attribute | Description |
|---|---|
| questionnaire_key | points to questionnaires.key |
| question_key | points to questions.key |
| key | option key |
| position | order on screen |
| description | |
| value | stringified integer/float |
Primary key: [questionnaire_key, question_key, key]
fields
Section titled “fields”| Attribute | Description |
|---|---|
| questionnaire_key | points to questionnaires.key |
| key | see responses.field_key |
| type | string / integer / decimal / boolean |
| description |
Primary key: [questionnaire_key, key]
scores
Section titled “scores”| Attribute | Description |
|---|---|
| questionnaire_key | points to questionanires.key |
| score_key | |
| score_subkey | primary subscore is value. |
| csv_key | header to use in csv export |
| label | |
| sbg_key |
Primary key: [questionnaire_key, score_key, score_subkey]