# Database | Tables overview

| 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.                                                                                                                                                                                                                                                                        | <p>Used by the UI to check on CSV export status (done, in progress ,etc) and get the url to download the file generated.</p><p><br>Settings -> Export data</p> | -                                                                                                                                                  |
| 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 | <p>Stores the relationship between an HXL tag and attribute and a form attribute. For instance the match of a</p><p>#meta tag</p><p>+value attribute</p><p>with a "description" field</p>                                                                                                                 | 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.                                                                                                                                                                                                                                      | <p>Settings -> Surveys -> Create/Edit survey -> Configuration-<br></p>                                                                                         | -                                                                                                                                                  |
| form\_stages                         | Stores information about each section in a survey, for example post or tasks.                                                                                                                                                                                                                             | <p>Settings</p><p>-> Surveys -> Create/Edit survey</p>                                                                                                         | -                                                                                                                                                  |
| form\_stages\_posts                  | Stores relations between posts and forms\_stages and if a task is completed in a post.                                                                                                                                                                                                                    | <p>Settings -> Surveys -> Create/Edit survey</p><p>Add post</p><p>Edit post</p><p>View post</p>                                                                | -                                                                                                                                                  |
| forms                                | Stores basic information about each survey                                                                                                                                                                                                                                                                | <p>Settings -> Surveys</p><p><br>Add post<br></p><p>View post</p><p>Edit/Structure post</p>                                                                    | -                                                                                                                                                  |
| 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                                                                                                                                        | <p>HDX feature - only available in some deployments through export UI.</p><p>Uses this field to show the list of available tags for each field</p> |
| 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)                                                                                                                                                                                                                | <p>This field is used to show the correct attributes when you select a tag</p><p><br>Settings -> Export data</p>                                               | 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.                                                                                                                                                                                                                                                               | <p>Add post</p><p><br>Edit post</p><p><br>View post</p>                                                                                                        | -                                                                                                                                                  |
| messages                             | Stores SMS, Twitter and other messages and their relationship to a contact.                                                                                                                                                                                                                               | <p>Edit/Structure post</p><p><br>View post</p><p><br>Settings -> Data Sources</p>                                                                              | -                                                                                                                                                  |
| 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".                                                                                                                                                                                                                                           | <p>Add Posts</p><p><br>View Posts</p><p><br>Edit Posts</p>                                                                                                     | -                                                                                                                                                  |
| post\_decimal                        | Stores post-values for form-attribute "Number(decimal)".                                                                                                                                                                                                                                                  | <p>Add Posts</p><p><br>View Posts</p><p><br>Edit Posts</p>                                                                                                     | -                                                                                                                                                  |
| post\_geometry                       | Stores OpenGIS formatted geometries                                                                                                                                                                                                                                                                       | <p>Add Posts</p><p><br>View Posts</p><p><br>Edit Posts</p>                                                                                                     | -                                                                                                                                                  |
| post\_int                            | Stores post-values for form-attribute "Number(integer)"                                                                                                                                                                                                                                                   | <p>Add Posts</p><p><br>View Posts</p><p><br>Edit Posts</p>                                                                                                     | -                                                                                                                                                  |
| post\_locks                          | Stores info about which posts are currently being edited and locked for other users to edit.                                                                                                                                                                                                              | <p>Add Posts</p><p><br>View Posts</p><p><br>Edit Posts</p>                                                                                                     | -                                                                                                                                                  |
| post\_markdown                       | Stores post-values for form-attribute "Markdown"                                                                                                                                                                                                                                                          | <p>Add Posts</p><p><br>View Posts</p><p><br>Edit Posts</p>                                                                                                     | -                                                                                                                                                  |
| post\_media                          | Stores id for media-files uploaded in a post (form-attribute type "Image").                                                                                                                                                                                                                               | <p>Add Posts</p><p><br>View Posts</p><p><br>Edit Posts</p>                                                                                                     | -                                                                                                                                                  |
| post\_point                          | Stores post-values for form-attribute "Location".                                                                                                                                                                                                                                                         | <p>Add Posts</p><p><br>View Posts</p><p><br>Edit Posts</p>                                                                                                     | -                                                                                                                                                  |
| post\_relation                       | Stores post-values for form-attribute "Related posts".                                                                                                                                                                                                                                                    | <p>Add Posts</p><p><br>View Posts</p><p><br>Edit Posts</p>                                                                                                     | -                                                                                                                                                  |
| post\_text                           | Stores post-values for form-attribute "Long Text".                                                                                                                                                                                                                                                        | <p>Add Posts</p><p><br>View Posts</p><p><br>Edit Posts</p>                                                                                                     | -                                                                                                                                                  |
| post\_varchar                        | Stores post-values for form-attributes "Short text", "Select (dropdowns)", "Radio buttons", "Checkboxes" and "Embed video"                                                                                                                                                                                | <p>Add Posts</p><p><br>View Posts</p><p><br>Edit Posts</p>                                                                                                     | -                                                                                                                                                  |
| posts                                | Store basic info about each post.                                                                                                                                                                                                                                                                         | <p>Add Posts</p><p><br>View Posts</p><p><br>Edit Posts</p>                                                                                                     | -                                                                                                                                                  |
| posts\_media                         | Holds info about the files that are uploaded to posts.                                                                                                                                                                                                                                                    | <p>Add Posts</p><p><br>View Posts</p><p><br>Edit Posts</p>                                                                                                     | -                                                                                                                                                  |
| posts\_sets                          | Holds information about saved searches.                                                                                                                                                                                                                                                                   | <p>Sort & Filter -> filter by Saved Search</p><p><br>Sort & Filter -> Save search</p><p><br>Sort & Filter -> Update search</p>                                 | -                                                                                                                                                  |
| posts\_tags                          | Stores which categories is selected in which post.                                                                                                                                                                                                                                                        | <p>Add posts</p><p><br>View posts</p><p><br>Edit posts</p><p><br>Setting -> Categories</p>                                                                     | -                                                                                                                                                  |
| roles                                | <p>Stores all</p><p>defined roles.</p>                                                                                                                                                                                                                                                                    | <p>Settings -> Roles</p><p><br>Settings -> Users</p>                                                                                                           | -                                                                                                                                                  |
| roles\_permissions                   | Stores relations between roles and permissions                                                                                                                                                                                                                                                            | Settings -> Roles                                                                                                                                              | -                                                                                                                                                  |
| sets                                 | Stores all saved searches available.                                                                                                                                                                                                                                                                      | Sort & Filter -> Saved searches                                                                                                                                | -                                                                                                                                                  |
| tags                                 | All categories created                                                                                                                                                                                                                                                                                    | <p>Settings -> Categories</p><p><br>Settings -><br>Surveys -> Create/Edit survey</p><p><br>Add post</p><p><br>View post</p><p><br>Edit post</p>                | -                                                                                                                                                  |
| 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                                                                                                                                           | -                                                                                                                                                  |