Database | Tables overview
This is a list of all tables used for the Ushahidi Platform.
Table | Description | Relevant areas of Ushahidi UI/functionality | Comments |
apikeys | Generate API keys for external services that connect with the platform API | Setting -> General (it shows your API Key). | - |
config | Stores configuration for the deployment | - | - |
contacts | Stores contact information for users | Used for: SMS contacts, twitter contacts , mail contacts. Messages are usually associated with contacts. | - |
country_codes | Stores countrycodes for targeted surveys | Settings -> Surveys -> Create targeted survey | This feature needs the targeted survey feature-flag to be turned on. |
csv | Stores details about a CSV export. | Used by the UI to check on CSV export status (done, in progress ,etc) and get the url to download the file generated. Settings -> Export data | - |
export_batches | Used to separate a large export in chunks to be processed. | Settings -> Export data | - |
export_job | Used to store data about the exports requested by the user in the csv endpoint. | Settings->Export data | - |
form_attribute_hxl_attribute_tag | Stores the relationship between an HXL tag and attribute and a form attribute. For instance the match of a #meta tag +value attribute with a "description" field | Settings-> Export data | HDX Feature - available only in some deployments through the Export UI |
form_attributes | Stores information about each field (question) in a survey. | Settings -> Surveys -> Create/Edit survey | - |
form_roles | Stores information if a survey is restricted to specific roles only. | Settings -> Surveys -> Create/Edit survey -> Configuration- | - |
form_stages | Stores information about each section in a survey, for example post or tasks. | Settings -> Surveys -> Create/Edit survey | - |
form_stages_posts | Stores relations between posts and forms_stages and if a task is completed in a post. | Settings -> Surveys -> Create/Edit survey Add post Edit post View post | - |
forms | Stores basic information about each survey | Settings -> Surveys Add post View post Edit/Structure post | - |
hxl_attribute_type_tag | Stores the types of attributes that can be matched with each HXL attribute. For instance you can only match #geo (+lat + lon) to location fields. | Settings -> Export data | HDX feature - only available in some deployments through export UI. Uses this field to show the list of available tags for each field |
hxl_attributes | All the available HXL attributes | Settings -> Export data | HDX feature only available in some deployments. |
hxl_license | All the available HDX licenses | Settings -> Export data | HDX feature only available in some deployments. |
hxl_meta_data | Information required to save a dataset into HDX (humdata.org) such as license selected, name, and privacy settings | Settings -> Export data | HDX feature only available in some deployments. |
hxl_tag_attributes | Relationship between HXL Tags and Attributes (which attributes are available for each tag) | This field is used to show the correct attributes when you select a tag Settings -> Export data | HDX feature only available in some deployments. |
hxl_tags | Stores all the available HXL tags | Settings -> Export data | HDX feature only available in some deployments. |
layers | Used to determine which map layers wee have available. | Settings -> General contains the list of layers | - |
media | Stores info about images uploaded to posts. | Add post Edit post View post | - |
messages | Stores SMS, Twitter and other messages and their relationship to a contact. | Edit/Structure post View post Settings -> Data Sources | - |
notification_queue | Keeping track of which kinds of notifications have been requested by users | Collections/Saved searches | - |
notifications | The actual notifications to be sent, until they are processed and actually sent out (in the background) | Collections/Saved searches | - |
oauth_access_tokens | Links users and their client ID to an access token generated to login | - | - |
oauth_auth_codes | N/A | N/A | N/A |
oauth_clients | All available clients. Used by the platform UI to get authorization to perform actions. We currently use 2 grants, the client_credentials one for anon requests which uses the oauth_clients data, and the password grant which uses oauth_clients and the user's login details to authenticate a user | - | - |
oauth_personal_access_clients | NA | NA | N/A |
oauth_refresh_tokens | Used to refresh Authorization tokens | - | - |
permissions | Stores the permissions availble in the platform. | Settings -> Roles | - |
phinxlog | Used to store information about the migrations already executed in the platform. | - | - |
post_comments | N/A | N/A | N/A |
post_datetime | Stores post-values for form-attributes "Date" and "Date&Time". | Add Posts View Posts Edit Posts | - |
post_decimal | Stores post-values for form-attribute "Number(decimal)". | Add Posts View Posts Edit Posts | - |
post_geometry | Stores OpenGIS formatted geometries | Add Posts View Posts Edit Posts | - |
post_int | Stores post-values for form-attribute "Number(integer)" | Add Posts View Posts Edit Posts | - |
post_locks | Stores info about which posts are currently being edited and locked for other users to edit. | Add Posts View Posts Edit Posts | - |
post_markdown | Stores post-values for form-attribute "Markdown" | Add Posts View Posts Edit Posts | - |
post_media | Stores id for media-files uploaded in a post (form-attribute type "Image"). | Add Posts View Posts Edit Posts | - |
post_point | Stores post-values for form-attribute "Location". | Add Posts View Posts Edit Posts | - |
post_relation | Stores post-values for form-attribute "Related posts". | Add Posts View Posts Edit Posts | - |
post_text | Stores post-values for form-attribute "Long Text". | Add Posts View Posts Edit Posts | - |
post_varchar | Stores post-values for form-attributes "Short text", "Select (dropdowns)", "Radio buttons", "Checkboxes" and "Embed video" | Add Posts View Posts Edit Posts | - |
posts | Store basic info about each post. | Add Posts View Posts Edit Posts | - |
posts_media | Holds info about the files that are uploaded to posts. | Add Posts View Posts Edit Posts | - |
posts_sets | Holds information about saved searches. | Sort & Filter -> filter by Saved Search Sort & Filter -> Save search Sort & Filter -> Update search | - |
posts_tags | Stores which categories is selected in which post. | Add posts View posts Edit posts Setting -> Categories | - |
roles | Stores all defined roles. | Settings -> Roles Settings -> Users | - |
roles_permissions | Stores relations between roles and permissions | Settings -> Roles | - |
sets | Stores all saved searches available. | Sort & Filter -> Saved searches | - |
tags | All categories created | Settings -> Categories Settings -> Surveys -> Create/Edit survey Add post View post Edit post | - |
targeted_survey_state | This is used to save the status of a targeted survey (which was the last survey question sent out to each user) | Settings -> Surveys | Only available to deployments in some custom enterprise plans. |
tos | Stores info of when and if each has signed the terms and conditions | - | - |
user_reset_tokens | used to store password reset tokens | Login -> Forgot Your Password? | - |
user_settings | User specific settings such as HDX api keys | - | - |
users | Store users | Settings -> Users | - |
webhook_job | Stores jobs generated by the system (used by the webhooks jobs queue). | Settings -> Webhooks | - |
webhooks | Stores information of each webhook such as url, method, etc | Settings -> Webhooks | - |