# 05 схема БД (2026-11-12T18:52:54Z)
   version    |          applied_at          
--------------+------------------------------
 001_init.sql | 2026-11-12 18:07:29.27036+00
(1 row)

             List of relations
 Schema |       Name        | Type  | Owner 
--------+-------------------+-------+-------
 public | chat_members      | table | msg
 public | chats             | table | msg
 public | messages          | table | msg
 public | schema_migrations | table | msg
 public | sessions          | table | msg
 public | users             | table | msg
(6 rows)

                           Table "public.users"
    Column     |           Type           | Collation | Nullable | Default 
---------------+--------------------------+-----------+----------+---------
 id            | uuid                     |           | not null | 
 username      | text                     |           | not null | 
 display_name  | text                     |           | not null | 
 password_hash | text                     |           | not null | 
 created_at    | timestamp with time zone |           | not null | now()
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_username_key" UNIQUE CONSTRAINT, btree (username)
Check constraints:
    "users_display_name_check" CHECK (char_length(display_name) >= 1 AND char_length(display_name) <= 100)
    "users_username_check" CHECK (username ~ '^[a-z0-9_]{3,32}$'::text)
Referenced by:
    TABLE "chat_members" CONSTRAINT "chat_members_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)
    TABLE "messages" CONSTRAINT "messages_sender_id_fkey" FOREIGN KEY (sender_id) REFERENCES users(id)
    TABLE "sessions" CONSTRAINT "sessions_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)

                               Table "public.chats"
   Column   |           Type           | Collation | Nullable |      Default      
------------+--------------------------+-----------+----------+-------------------
 id         | uuid                     |           | not null | 
 title      | text                     |           | not null | 
 created_at | timestamp with time zone |           | not null | clock_timestamp()
Indexes:
    "chats_pkey" PRIMARY KEY, btree (id)
    "chats_created_idx" btree (created_at, id)
Check constraints:
    "chats_title_check" CHECK (char_length(title) >= 1 AND char_length(title) <= 100)
Referenced by:
    TABLE "chat_members" CONSTRAINT "chat_members_chat_id_fkey" FOREIGN KEY (chat_id) REFERENCES chats(id) ON DELETE CASCADE
    TABLE "messages" CONSTRAINT "messages_chat_id_fkey" FOREIGN KEY (chat_id) REFERENCES chats(id) ON DELETE CASCADE

           Table "public.chat_members"
 Column  | Type | Collation | Nullable | Default 
---------+------+-----------+----------+---------
 chat_id | uuid |           | not null | 
 user_id | uuid |           | not null | 
Indexes:
    "chat_members_pkey" PRIMARY KEY, btree (chat_id, user_id)
    "chat_members_user_idx" btree (user_id, chat_id)
Foreign-key constraints:
    "chat_members_chat_id_fkey" FOREIGN KEY (chat_id) REFERENCES chats(id) ON DELETE CASCADE
    "chat_members_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)

                             Table "public.messages"
   Column   |           Type           | Collation | Nullable |      Default      
------------+--------------------------+-----------+----------+-------------------
 id         | uuid                     |           | not null | 
 chat_id    | uuid                     |           | not null | 
 sender_id  | uuid                     |           | not null | 
 text       | text                     |           | not null | 
 created_at | timestamp with time zone |           | not null | clock_timestamp()
Indexes:
    "messages_pkey" PRIMARY KEY, btree (id)
    "messages_history_idx" btree (chat_id, created_at, id)
Check constraints:
    "messages_text_check" CHECK (char_length(text) >= 1 AND char_length(text) <= 4000)
Foreign-key constraints:
    "messages_chat_id_fkey" FOREIGN KEY (chat_id) REFERENCES chats(id) ON DELETE CASCADE
    "messages_sender_id_fkey" FOREIGN KEY (sender_id) REFERENCES users(id)

                        Table "public.sessions"
   Column   |           Type           | Collation | Nullable | Default 
------------+--------------------------+-----------+----------+---------
 token_hash | bytea                    |           | not null | 
 user_id    | uuid                     |           | not null | 
 expires_at | timestamp with time zone |           | not null | 
 revoked_at | timestamp with time zone |           |          | 
Indexes:
    "sessions_pkey" PRIMARY KEY, btree (token_hash)
    "sessions_expires_idx" btree (expires_at)
Foreign-key constraints:
    "sessions_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)

--- пароли хранятся как Argon2id
 hash_prefix | count 
-------------+-------
 $argon2id$  |    21
(1 row)

--- в sessions только sha256 токена (32 байта), не сам токен
 token_hash_bytes | count 
------------------+-------
               32 |    22
(1 row)

--- volume с данными PostgreSQL
volume mtusi2026lab2_pgdata -> /var/lib/postgresql/data
bind  -> /run/secrets/pg_password

