Blog database

1 OTUS Blog Move the mouse over tables & columns to read the comments. Generated using DbSchema © Wise Coders Fk fk_comment_likes_comments_1 comment_likes ref comments ( comment_id -> id ) Fk fk_comment_likes_comments_1 comment_likes ref comments ( comment_id -> id ) comment_id Fk fk_comment_likes_users_1 comment_likes ref users ( user_id -> id ) Fk fk_comment_likes_users_1 comment_likes ref users ( user_id -> id ) user_id Fk fk_logs_articles_1 logs ref articles ( article_id -> id ) Fk fk_logs_articles_1 logs ref articles ( article_id -> id ) article_id Fk fk_logs_comments_1 logs ref comments ( comment_id -> id ) Fk fk_logs_comments_1 logs ref comments ( comment_id -> id ) comment_id Fk fk_logs_log_actions_1 logs ref log_actions ( action_id -> id ) Fk fk_logs_log_actions_1 logs ref log_actions ( action_id -> id ) action_id Fk fk_logs_threads_1 logs ref threads ( thread_id -> id ) Fk fk_logs_threads_1 logs ref threads ( thread_id -> id ) thread_id Fk fk_logs_users_1 logs ref users ( user_id -> id ) Fk fk_logs_users_1 logs ref users ( user_id -> id ) user_id Fk fk_logs_users_2 logs ref users ( admin_id -> id ) Fk fk_logs_users_2 logs ref users ( admin_id -> id ) admin_id Fk fk_tags_to_articles_articles_1 tags_to_articles ref articles ( article_id -> id ) Fk fk_tags_to_articles_articles_1 tags_to_articles ref articles ( article_id -> id ) article_id Fk fk_tags_to_articles_tags_1 tags_to_articles ref tags ( tags_id -> id ) Fk fk_tags_to_articles_tags_1 tags_to_articles ref tags ( tags_id -> id ) tags_id Fk fk_likes_articles_1 article_likes ref articles ( article_id -> id ) Fk fk_likes_articles_1 article_likes ref articles ( article_id -> id ) article_id Fk fk_likes_users_1 article_likes ref users ( user_id -> id ) Fk fk_likes_users_1 article_likes ref users ( user_id -> id ) user_id Fk fk_threads_thread_priorities_1 threads ref thread_priorities ( priority_id -> id ) Fk fk_threads_thread_priorities_1 threads ref thread_priorities ( priority_id -> id ) priority_id Fk fk_threads_threads_statuses_1 threads ref threads_statuses ( status_id -> id ) Fk fk_threads_threads_statuses_1 threads ref threads_statuses ( status_id -> id ) status_id Fk fk_threads_users_1 threads ref users ( user_id -> id ) Fk fk_threads_users_1 threads ref users ( user_id -> id ) user_id Fk fk_threads_users_2 threads ref users ( admin_id -> id ) Fk fk_threads_users_2 threads ref users ( admin_id -> id ) admin_id Fk fk_threads_messages_threads_1 threads_messages ref threads ( thread_id -> id ) Fk fk_threads_messages_threads_1 threads_messages ref threads ( thread_id -> id ) thread_id Fk fk_threads_messages_users_1 threads_messages ref users ( user_id -> id ) Fk fk_threads_messages_users_1 threads_messages ref users ( user_id -> id ) user_id Fk fk_user_to_role_roles_1 user_to_role ref roles ( role_id -> id ) Fk fk_user_to_role_roles_1 user_to_role ref roles ( role_id -> id ) role_id Fk fk_user_to_role_users_1 user_to_role ref users ( user_id -> id ) Fk fk_user_to_role_users_1 user_to_role ref users ( user_id -> id ) user_id Fk fk_followers_users_1 followers ref users ( follower_id -> id ) Fk fk_followers_users_1 followers ref users ( follower_id -> id ) follower_id Fk fk_followers_users_2 followers ref users ( author_id -> id ) Fk fk_followers_users_2 followers ref users ( author_id -> id ) author_id Fk fk_role_to_permission_permissions_1 role_to_permission ref permissions ( role_id -> id ) Fk fk_role_to_permission_permissions_1 role_to_permission ref permissions ( role_id -> id ) role_id Fk fk_role_to_permission_roles_1 role_to_permission ref roles ( permission_id -> id ) Fk fk_role_to_permission_roles_1 role_to_permission ref roles ( permission_id -> id ) permission_id Fk fk_users_languages_1 users ref languages ( language_id -> id ) Fk fk_users_languages_1 users ref languages ( language_id -> id ) language_id Fk fk_users_timezones_1 users ref timezones ( timezone_id -> id ) Fk fk_users_timezones_1 users ref timezones ( timezone_id -> id ) timezone_id Fk fk_users_user_types_1 users ref user_internal_statuses ( internal_status_id -> id ) Fk fk_users_user_types_1 users ref user_internal_statuses ( internal_status_id -> id ) internal_status_id Fk fk_users_user_statuses_1 users ref user_statuses ( status_id -> id ) Fk fk_users_user_statuses_1 users ref user_statuses ( status_id -> id ) status_id Fk fk_articles_categories_1 articles ref article_categories ( category_id -> id ) Fk fk_articles_categories_1 articles ref article_categories ( category_id -> id ) category_id Fk fk_articles_article_statuses_1 articles ref article_statuses ( article_status_id -> id ) Fk fk_articles_article_statuses_1 articles ref article_statuses ( article_status_id -> id ) article_status_id Fk fk_articles_languages_1 articles ref languages ( language_id -> id ) Fk fk_articles_languages_1 articles ref languages ( language_id -> id ) language_id Fk fk_articles_users_1 articles ref users ( author_id -> id ) Fk fk_articles_users_1 articles ref users ( author_id -> id ) author_id Fk fk_comments_articles_1 comments ref articles ( article_id -> id ) Fk fk_comments_articles_1 comments ref articles ( article_id -> id ) article_id Fk fk_comments_comments_1 comments ref comments ( parent_id -> id ) Fk fk_comments_comments_1 comments ref comments ( parent_id -> id ) parent_id Fk fk_comments_users_1 comments ref users ( author_id -> id ) Fk fk_comments_users_1 comments ref users ( author_id -> id ) author_id article_categoriesTable public.article_categories Categories that uses in articles Pk article_categories_pkey ( id ) idid * smallserial smallserialReferred by articles ( category_id -> id ) titletitle varchar(255) name of category varchar(255) comment_likesTable public.comment_likes Table for comment likes. User can add (create row) or remove (delete row) only one like. Unq comment_likes_user_id_comment_id_unique ( user_id, comment_id ) comment_likes_user_id_index ( user_id ) user_iduser_id * integer reference to user integerReferences users ( user_id -> id ) Unq comment_likes_user_id_comment_id_unique ( user_id, comment_id ) comment_likes_comment_id_index ( comment_id ) comment_idcomment_id * integer regerence to comment integerReferences comments ( comment_id -> id ) log_actionsTable public.log_actions List of available log actions. Pk log_actions_pkey ( id ) idid * smallserial smallserialReferred by logs ( action_id -> id ) titletitle varchar(255) name of action varchar(255) logsTable public.logs Realization of simple log system. For controlling some users activities. logs_created_at_idx ( created_at ) created_atcreated_at * timestamptz default now() date of creation event timestamptz logs_action_id_idx ( action_id ) action_idaction_id * integer reference to log-action integerReferences log_actions ( action_id -> id ) logs_user_id_idx ( user_id ) user_iduser_id integer reference to user that was envolved or that was used in the action integerReferences users ( user_id -> id ) logs_admin_id_idx ( admin_id ) admin_idadmin_id integer reference to user with admin role that create action integerReferences users ( admin_id -> id ) logs_thread_id_idx ( thread_id ) thread_idthread_id integer reference to thread if its entity was used in the action integerReferences threads ( thread_id -> id ) logs_article_id_idx ( article_id ) article_idarticle_id integer reference to article_id if this entity was used in the action integerReferences articles ( article_id -> id ) logs_comment_id_idx ( comment_id ) comment_idcomment_id integer reference to comment_id if this entity was used in the action integerReferences comments ( comment_id -> id ) logs_metadata_idx ( metadata ) metadatametadata * jsonb additional fields for action that don't have strictly structure jsonb tagsTable public.tags List of available tags of articles. Pk tags_pkey ( id ) idid * serial serialReferred by tags_to_articles ( tags_id -> id ) titletitle * varchar(255) name of tag varchar(255) tags_to_articlesTable public.tags_to_articles Mapping tags to articles. tags_to_articles_article_id_idx ( article_id ) Unq tags_to_articles_article_id_tags_id_unique_idx ( article_id, tags_id ) article_idarticle_id * integer reference to article integerReferences articles ( article_id -> id ) tags_to_articles_tags_id_idx ( tags_id ) Unq tags_to_articles_article_id_tags_id_unique_idx ( article_id, tags_id ) tags_idtags_id * integer reference to tag integerReferences tags ( tags_id -> id ) article_statusesTable public.article_statuses Statuses for articles. For example, "on moderation", "publish", "banned" etc. Pk article_statuses_pkey ( id ) idid * smallserial smallserialReferred by articles ( article_status_id -> id ) titletitle varchar(255) name of article status varchar(255) article_likesTable public.article_likes Table for likes. Contains user and article reference. User can like (add row) and unlike (delete row) article. Unq article_likes_user_id_comment_id_unique ( user_id, article_id ) article_likes_user_id_index ( user_id ) user_iduser_id * integer user reference integerReferences users ( user_id -> id ) Unq article_likes_user_id_comment_id_unique ( user_id, article_id ) article_likes_article_id_index ( article_id ) article_idarticle_id * integer integerReferences articles ( article_id -> id ) languagesTable public.languages Dictionary for available languages. Pk languages_pkey ( id ) idid * smallserial smallserialReferred by articles ( language_id -> id ) Referred by users ( language_id -> id ) iso_639-1iso_639-1 varchar(255) iso-631-1 view ("en") varchar(255) titletitle varchar(255) full name of language varchar(255) threads_statusesTable public.threads_statuses Dictionary with threads statuses. Threads can be "closed" for example if problem of user was resolved. Pk threads_statuses_pkey ( id ) idid * smallserial smallserialReferred by threads ( status_id -> id ) titletitle varchar(255) name of thread status varchar(255) threadsTable public.threads Chat-room with user and admin. Pk threads_pkey ( id ) idid * serial serialReferred by logs ( thread_id -> id ) Referred by threads_messages ( thread_id -> id ) threads_created_at_idx ( created_at ) created_atcreated_at * timestamptz default now() date of creating timestamptz priority_idpriority_id * integer reference to priority integerReferences thread_priorities ( priority_id -> id ) threads_user_id_idx ( user_id ) user_iduser_id * integer reference to user integerReferences users ( user_id -> id ) threads_admin_id_idx ( admin_id ) admin_idadmin_id * integer reference to user with role admin integerReferences users ( admin_id -> id ) status_idstatus_id * integer integerReferences threads_statuses ( status_id -> id ) thread_prioritiesTable public.thread_priorities Dictionary of thread priorities. User can set priority of thread for emergency messages. Pk thread_priorities_pkey ( id ) idid * smallserial smallserialReferred by threads ( priority_id -> id ) titletitle varchar(255) kind of priority varchar(255) threads_messagesTable public.threads_messages Messages inside threads between users and admins. Pk threads_messages_pkey ( id ) idid * serial serial threads_messages_thread_id_created_at_ids ( thread_id, created_at ) thread_idthread_id * integer reference to chat-room integerReferences threads ( thread_id -> id ) threads_messages_thread_id_created_at_ids ( thread_id, created_at ) created_atcreated_at * timestamptz default now() date of creating timestamptz updated_atupdated_at * timestamptz default now() date of last updating timestamptz removed_atremoved_at timestamptz date of removing timestamptz user_iduser_id * integer reference to user that create message integerReferences users ( user_id -> id ) contentcontent text content of message text user_statusesTable public.user_statuses Pk user_statuses_pkey ( id ) idid * smallserial smallserialReferred by users ( status_id -> id ) titletitle varchar(255) name of status varchar(255) user_to_roleTable public.user_to_role Mapping user to role. Unq user_to_role_user_id_idx ( user_id, role_id ) user_iduser_id * integer reference to user integerReferences users ( user_id -> id ) Unq user_to_role_user_id_idx ( user_id, role_id ) role_idrole_id * integer reference to role integerReferences roles ( role_id -> id ) created_atcreated_at * timestamptz default now() date of creation timestamptz timezonesTable public.timezones List of available timezones. Pk timezones_pkey ( id ) idid * smallserial smallserialReferred by users ( timezone_id -> id ) titletitle varchar(255) name of timezone ("Europe/Kiev") varchar(255) followersTable public.followers Realisation of ability for subscribing to another user activity (publish new articles). Unq followers_follower_id_author_id_uindex ( follower_id, author_id ) followers_follower_id_index ( follower_id ) follower_idfollower_id * integer reference to user that want to subscribe integerReferences users ( follower_id -> id ) Unq followers_follower_id_author_id_uindex ( follower_id, author_id ) followers_author_id_index ( author_id ) author_idauthor_id * integer publisher integerReferences users ( author_id -> id ) rolesTable public.roles Available roles. Pk roles_pkey ( id ) idid * smallserial smallserialReferred by role_to_permission ( permission_id -> id ) Referred by user_to_role ( role_id -> id ) titletitle varchar(255) name of role varchar(255) role_to_permissionTable public.role_to_permission Mapping role to permissions. One to many. role_idrole_id * integer reference to role integerReferences permissions ( role_id -> id ) permission_idpermission_id * integer reference to permission integerReferences roles ( permission_id -> id ) permissionsTable public.permissions List of permissions. Pk permissions_pkey ( id ) idid * smallserial smallserialReferred by role_to_permission ( role_id -> id ) titletitle varchar(255) name of permission varchar(255) user_internal_statusesTable public.user_internal_statuses List of user internal statuses that used for system defining. Pk user_internal_statuses_pkey ( id ) idid * smallserial smallserialReferred by users ( internal_status_id -> id ) titletitle * varchar(255) name of internal status varchar(255) usersTable public.users User entity. Pk users_pkey ( id ) idid * serial serialReferred by article_likes ( user_id -> id ) Referred by articles ( author_id -> id ) Referred by comment_likes ( user_id -> id ) Referred by comments ( author_id -> id ) Referred by followers ( follower_id -> id ) Referred by followers ( author_id -> id ) Referred by logs ( user_id -> id ) Referred by logs ( admin_id -> id ) Referred by threads ( user_id -> id ) Referred by threads ( admin_id -> id ) Referred by threads_messages ( user_id -> id ) Referred by user_to_role ( user_id -> id ) users_created_at_idx ( created_at ) created_atcreated_at * timestamptz default now() date of create timestamptz users_updated_at_idx ( updated_at ) updated_atupdated_at * timestamptz default now() date of last change timestamptz removed_atremoved_at timestamptz date of remove or ban timestamptz Unq users_email_idx ( email ) emailemail * varchar(255) email of user that needs for registration varchar(255) phonephone varchar(255) not required fields with phone-number varchar(255) passwordpassword * varchar(255) hash of password varchar(255) status_idstatus_id * integer reference to user status integerReferences user_statuses ( status_id -> id ) language_idlanguage_id * integer reference to language integerReferences languages ( language_id -> id ) timezone_idtimezone_id * integer reference to user timezone integerReferences timezones ( timezone_id -> id ) Unq users_nickname_idx ( nickname ) nicknamenickname * varchar(255) user nickname varchar(255) birthdaybirthday date date of birth date imageimage varchar(255) link to avatar varchar(255) internal_status_idinternal_status_id integer reference to internal user status integerReferences user_internal_statuses ( internal_status_id -> id ) articlesTable public.articles Article entity. Pk articles_pkey ( id ) idid * serial serialReferred by article_likes ( article_id -> id ) Referred by comments ( article_id -> id ) Referred by logs ( article_id -> id ) Referred by tags_to_articles ( article_id -> id ) articles_created_at_idx ( created_at ) created_atcreated_at * timestamptz default now() date of creation timestamptz articles_updated_at_idx ( updated_at ) updated_atupdated_at * timestamptz default now() date of last update timestamptz articles_removed_at_idx ( removed_at ) removed_atremoved_at timestamptz date of removing article timestamptz titletitle * varchar(1024) name of article varchar(1024) contentcontent * text content of article text articles_author_id_idx ( author_id ) author_idauthor_id * integer reference to author of article integerReferences users ( author_id -> id ) articles_article_status_id_idx ( article_status_id ) article_status_idarticle_status_id * integer reference to article status integerReferences article_statuses ( article_status_id -> id ) articles_language_id_idx ( language_id ) language_idlanguage_id * integer reference to language integerReferences languages ( language_id -> id ) articles_category_id_idx ( category_id ) category_idcategory_id integer regerenct to article category integerReferences article_categories ( category_id -> id ) imageimage varchar(255) link to the image varchar(255) commentsTable public.comments Comment entity. Can be relative on parent comment (needed for creating recursive tree of comments with answers). Pk comments_pkey ( id ) idid * serial serialReferred by comment_likes ( comment_id -> id ) Referred by comments ( parent_id -> id ) Referred by logs ( comment_id -> id ) comments_created_at_idx ( created_at ) created_atcreated_at * timestamptz default now() date of creating comment timestamptz comments_updated_at_idx ( updated_at ) updated_atupdated_at * timestamptz default now() date of last updating timestamptz comments_removed_at_idx ( removed_at ) removed_atremoved_at timestamptz date of removing timestamptz comments_author_id_idx ( author_id ) author_idauthor_id * integer user that create comment integerReferences users ( author_id -> id ) comments_article_id_idx ( article_id ) article_idarticle_id * integer reference to article integerReferences articles ( article_id -> id ) comments_parent_id_idx ( parent_id ) parent_idparent_id integer reference to parent comment for recursive showing integerReferences comments ( parent_id -> id ) contentcontent text main content of comment text
Tables




Table article_categories

Categories that uses in articles

IdxField NameData TypeDescription
* id smallserial AUTOINCREMENT
  title varchar( 255 ) name of category
Indexes
article_categories_pkey ON id


Table article_likes

Table for likes. Contains user and article reference. User can like (add row) and unlike (delete row) article.

IdxField NameData TypeDescription
* user_id integer user reference
* article_id integer
Indexes
article_likes_user_id_comment_id_unique ON user_id, article_id
article_likes_article_id_index ON article_id
article_likes_user_id_index ON user_id
Foreign Keys
fk_likes_articles_1 ( article_id ) ref articles (id)
fk_likes_users_1 ( user_id ) ref users (id)


Table article_statuses

Statuses for articles. For example, "on moderation", "publish", "banned" etc.

IdxField NameData TypeDescription
* id smallserial AUTOINCREMENT
  title varchar( 255 ) name of article status
Indexes
article_statuses_pkey ON id


Table articles

Article entity.

IdxField NameData TypeDescription
* id serial AUTOINCREMENT
* created_at timestamptz DEFAULT now() date of creation
* updated_at timestamptz DEFAULT now() date of last update
removed_at timestamptz date of removing article
* title varchar( 1024 ) name of article
* content text content of article
* author_id integer reference to author of article
* article_status_id integer reference to article status
* language_id integer reference to language
category_id integer regerenct to article category
  image varchar( 255 ) link to the image
Indexes
articles_pkey ON id
articles_article_status_id_idx ON article_status_id
articles_author_id_idx ON author_id
articles_category_id_idx ON category_id
articles_created_at_idx ON created_at
articles_language_id_idx ON language_id
articles_removed_at_idx ON removed_at
articles_updated_at_idx ON updated_at
Foreign Keys
fk_articles_categories_1 ( category_id ) ref article_categories (id)
fk_articles_article_statuses_1 ( article_status_id ) ref article_statuses (id)
fk_articles_languages_1 ( language_id ) ref languages (id)
fk_articles_users_1 ( author_id ) ref users (id)
Triggers
  t_articles_upd 
CREATE TRIGGER t_articles_upd BEFORE UPDATE ON public.articles FOR EACH ROW EXECUTE PROCEDURE upd_updated_at()


Table comment_likes

Table for comment likes. User can add (create row) or remove (delete row) only one like.

IdxField NameData TypeDescription
* user_id integer reference to user
* comment_id integer regerence to comment
Indexes
comment_likes_user_id_comment_id_unique ON user_id, comment_id
comment_likes_comment_id_index ON comment_id
comment_likes_user_id_index ON user_id
Foreign Keys
fk_comment_likes_comments_1 ( comment_id ) ref comments (id)
fk_comment_likes_users_1 ( user_id ) ref users (id)


Table comments

Comment entity. Can be relative on parent comment (needed for creating recursive tree of comments with answers).

IdxField NameData TypeDescription
* id serial AUTOINCREMENT
* created_at timestamptz DEFAULT now() date of creating comment
* updated_at timestamptz DEFAULT now() date of last updating
removed_at timestamptz date of removing
* author_id integer user that create comment
* article_id integer reference to article
parent_id integer reference to parent comment for recursive showing
  content text main content of comment
Indexes
comments_pkey ON id
comments_article_id_idx ON article_id
comments_author_id_idx ON author_id
comments_created_at_idx ON created_at
comments_parent_id_idx ON parent_id
comments_removed_at_idx ON removed_at
comments_updated_at_idx ON updated_at
Foreign Keys
fk_comments_articles_1 ( article_id ) ref articles (id)
fk_comments_comments_1 ( parent_id ) ref comments (id)
fk_comments_users_1 ( author_id ) ref users (id)
Triggers
  t_comments_upd 
CREATE TRIGGER t_comments_upd BEFORE UPDATE ON public.comments FOR EACH ROW EXECUTE PROCEDURE upd_updated_at()


Table followers

Realisation of ability for subscribing to another user activity (publish new articles).

IdxField NameData TypeDescription
* follower_id integer reference to user that want to subscribe
* author_id integer publisher
Indexes
followers_follower_id_author_id_uindex ON follower_id, author_id
followers_author_id_index ON author_id
followers_follower_id_index ON follower_id
Foreign Keys
fk_followers_users_1 ( follower_id ) ref users (id)
fk_followers_users_2 ( author_id ) ref users (id)


Table languages

Dictionary for available languages.

IdxField NameData TypeDescription
* id smallserial AUTOINCREMENT
  iso_639-1 varchar( 255 ) iso-631-1 view ("en")
  title varchar( 255 ) full name of language
Indexes
languages_pkey ON id


Table log_actions

List of available log actions.

IdxField NameData TypeDescription
* id smallserial AUTOINCREMENT
  title varchar( 255 ) name of action
Indexes
log_actions_pkey ON id


Table logs

Realization of simple log system. For controlling some users activities.

IdxField NameData TypeDescription
* created_at timestamptz DEFAULT now() date of creation event
* action_id integer reference to log-action
user_id integer reference to user that was envolved or that was used in the action
admin_id integer reference to user with admin role that create action
thread_id integer reference to thread if its entity was used in the action
article_id integer reference to article_id if this entity was used in the action
comment_id integer reference to comment_id if this entity was used in the action
* metadata jsonb additional fields for action that don't have strictly structure
Indexes
logs_action_id_idx ON action_id
logs_admin_id_idx ON admin_id
logs_article_id_idx ON article_id
logs_comment_id_idx ON comment_id
logs_created_at_idx ON created_at
logs_metadata_idx ON metadata
logs_thread_id_idx ON thread_id
logs_user_id_idx ON user_id
Foreign Keys
fk_logs_articles_1 ( article_id ) ref articles (id)
fk_logs_comments_1 ( comment_id ) ref comments (id)
fk_logs_log_actions_1 ( action_id ) ref log_actions (id)
fk_logs_threads_1 ( thread_id ) ref threads (id)
fk_logs_users_1 ( user_id ) ref users (id)
fk_logs_users_2 ( admin_id ) ref users (id)


Table permissions

List of permissions.

IdxField NameData TypeDescription
* id smallserial AUTOINCREMENT
  title varchar( 255 ) name of permission
Indexes
permissions_pkey ON id


Table role_to_permission

Mapping role to permissions. One to many.

IdxField NameData TypeDescription
* role_id integer reference to role
* permission_id integer reference to permission
Foreign Keys
fk_role_to_permission_permissions_1 ( role_id ) ref permissions (id)
fk_role_to_permission_roles_1 ( permission_id ) ref roles (id)


Table roles

Available roles.

IdxField NameData TypeDescription
* id smallserial AUTOINCREMENT
  title varchar( 255 ) name of role
Indexes
roles_pkey ON id


Table tags

List of available tags of articles.

IdxField NameData TypeDescription
* id serial AUTOINCREMENT
* title varchar( 255 ) name of tag
Indexes
tags_pkey ON id


Table tags_to_articles

Mapping tags to articles.

IdxField NameData TypeDescription
* article_id integer reference to article
* tags_id integer reference to tag
Indexes
tags_to_articles_article_id_idx ON article_id
tags_to_articles_tags_id_idx ON tags_id
tags_to_articles_article_id_tags_id_unique_idx ON article_id, tags_id
Foreign Keys
fk_tags_to_articles_articles_1 ( article_id ) ref articles (id)
fk_tags_to_articles_tags_1 ( tags_id ) ref tags (id)


Table thread_priorities

Dictionary of thread priorities. User can set priority of thread for emergency messages.

IdxField NameData TypeDescription
* id smallserial AUTOINCREMENT
  title varchar( 255 ) kind of priority
Indexes
thread_priorities_pkey ON id


Table threads

Chat-room with user and admin.

IdxField NameData TypeDescription
* id serial AUTOINCREMENT
* created_at timestamptz DEFAULT now() date of creating
* priority_id integer reference to priority
* user_id integer reference to user
* admin_id integer reference to user with role admin
* status_id integer
Indexes
threads_pkey ON id
threads_admin_id_idx ON admin_id
threads_created_at_idx ON created_at
threads_user_id_idx ON user_id
Foreign Keys
fk_threads_thread_priorities_1 ( priority_id ) ref thread_priorities (id)
fk_threads_threads_statuses_1 ( status_id ) ref threads_statuses (id)
fk_threads_users_1 ( user_id ) ref users (id)
fk_threads_users_2 ( admin_id ) ref users (id)


Table threads_messages

Messages inside threads between users and admins.

IdxField NameData TypeDescription
* id serial AUTOINCREMENT
* thread_id integer reference to chat-room
* created_at timestamptz DEFAULT now() date of creating
* updated_at timestamptz DEFAULT now() date of last updating
  removed_at timestamptz date of removing
* user_id integer reference to user that create message
  content text content of message
Indexes
threads_messages_pkey ON id
threads_messages_thread_id_created_at_ids ON thread_id, created_at
Foreign Keys
fk_threads_messages_threads_1 ( thread_id ) ref threads (id)
fk_threads_messages_users_1 ( user_id ) ref users (id)
Triggers
  t_threads_messages_upd 
CREATE TRIGGER t_threads_messages_upd BEFORE UPDATE ON public.threads_messages FOR EACH ROW EXECUTE PROCEDURE upd_updated_at()


Table threads_statuses

Dictionary with threads statuses. Threads can be "closed" for example if problem of user was resolved.

IdxField NameData TypeDescription
* id smallserial AUTOINCREMENT
  title varchar( 255 ) name of thread status
Indexes
threads_statuses_pkey ON id


Table timezones

List of available timezones.

IdxField NameData TypeDescription
* id smallserial AUTOINCREMENT
  title varchar( 255 ) name of timezone ("Europe/Kiev")
Indexes
timezones_pkey ON id


Table user_internal_statuses

List of user internal statuses that used for system defining.

IdxField NameData TypeDescription
* id smallserial AUTOINCREMENT
* title varchar( 255 ) name of internal status
Indexes
user_internal_statuses_pkey ON id


Table user_statuses
IdxField NameData TypeDescription
* id smallserial AUTOINCREMENT
  title varchar( 255 ) name of status
Indexes
user_statuses_pkey ON id


Table user_to_role

Mapping user to role.

IdxField NameData TypeDescription
* user_id integer reference to user
* role_id integer reference to role
* created_at timestamptz DEFAULT now() date of creation
Indexes
user_to_role_user_id_idx ON user_id, role_id
Foreign Keys
fk_user_to_role_roles_1 ( role_id ) ref roles (id)
fk_user_to_role_users_1 ( user_id ) ref users (id)


Table users

User entity.

IdxField NameData TypeDescription
* id serial AUTOINCREMENT
* created_at timestamptz DEFAULT now() date of create
* updated_at timestamptz DEFAULT now() date of last change
  removed_at timestamptz date of remove or ban
* email varchar( 255 ) email of user that needs for registration
  phone varchar( 255 ) not required fields with phone-number
* password varchar( 255 ) hash of password
* status_id integer reference to user status
* language_id integer reference to language
* timezone_id integer reference to user timezone
* nickname varchar( 255 ) user nickname
  birthday date date of birth
  image varchar( 255 ) link to avatar
internal_status_id integer reference to internal user status
Indexes
users_pkey ON id
users_email_idx ON email
users_nickname_idx ON nickname
users_created_at_idx ON created_at
users_updated_at_idx ON updated_at
Foreign Keys
fk_users_languages_1 ( language_id ) ref languages (id)
fk_users_timezones_1 ( timezone_id ) ref timezones (id)
fk_users_user_types_1 ( internal_status_id ) ref user_internal_statuses (id)
fk_users_user_statuses_1 ( status_id ) ref user_statuses (id)
Triggers
  t_users_upd 
CREATE TRIGGER t_users_upd BEFORE UPDATE ON public.users FOR EACH ROW EXECUTE PROCEDURE upd_updated_at()


Schema public
ProceduresDescription
upd_updated_atProcedure for triggering update columns and setting current timestamp in 'updated_at'-field.