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

-