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
-

Tech Stack - Previous

API Documentation

Next - Tech Stack

Database | Database Schema Diagram

Last updated -7

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	-