Database Schema

The Flet-Chat application uses a relational database to store user data, chats, messages, and related information. The schema is designed to support the core functionalities of the chat application while maintaining data integrity and efficiency.

Tables

Overview:

Users

The users table stores information about registered users.

Column	Type	Constraints
id	Integer	Primary Key, Auto-increment
username	String	Unique, Indexed
email	String	Unique, Indexed
hashed_password	String
created_at	DateTime	Default: current timestamp
is_active	Boolean	Default: True

Chats

The chats table represents individual chat rooms or conversations.

Column	Type	Constraints
id	Integer	Primary Key, Auto-increment
name	String
created_at	DateTime	Default: current timestamp

Messages

The messages table stores all messages sent within chats.

Column	Type	Constraints
id	Integer	Primary Key, Auto-increment
content	String
created_at	DateTime	Default: current timestamp
updated_at	DateTime	On update: current timestamp
is_deleted	Boolean	Default: False
chat_id	Integer	Foreign Key (chats.id)
user_id	Integer	Foreign Key (users.id)

Tokens

The tokens table manages authentication tokens for users.

Column	Type	Constraints
id	Integer	Primary Key, Auto-increment
access_token	String	Unique, Indexed
refresh_token	String	Unique, Indexed
token_type	String
expires_at	DateTime
user_id	Integer	Foreign Key (users.id)

Message Statuses

The message_statuses table tracks the read status of messages for each user.

Column	Type	Constraints
id	Integer	Primary Key, Auto-increment
message_id	Integer	Foreign Key (messages.id)
user_id	Integer	Foreign Key (users.id)
is_read	Boolean	Default: False
read_at	DateTime	Nullable

Chat Members (Junction Table)

The chat_members table is a junction table that manages the many-to-many relationship between users and chats.

Column	Type	Constraints
chat_id	Integer	Primary Key, Foreign Key (chats.id)
user_id	Integer	Primary Key, Foreign Key (users.id)

Relationships

1. A User can be a member of multiple Chats, and a Chat can have multiple Users (Many-to-Many relationship through chat_members).
2. A User can send multiple Messages, but each Message belongs to one User (One-to-Many).
3. A Chat can contain multiple Messages, but each Message belongs to one Chat (One-to-Many).
4. A User can have multiple Tokens, but each Token belongs to one User (One-to-Many).
5. A Message can have multiple MessageStatuses (one for each user in the chat), and each MessageStatus belongs to one Message and one User (One-to-Many for both relationships).

Indexes

• users: Indexes on id, username, and email for faster lookups.
• chats: Index on id.
• messages: Indexes on id, chat_id, and user_id for efficient message retrieval.
• tokens: Indexes on id, access_token, and refresh_token for quick token validation.
• message_statuses: Indexes on id, message_id, and user_id for efficient status checks.

Constraints

• Foreign key constraints ensure referential integrity between related tables.
• Unique constraints on users.username, users.email, tokens.access_token, and tokens.refresh_token prevent duplicate entries.
• The composite primary key on chat_members (chat_id, user_id) ensures a user can only be added to a chat once.

This schema design supports the core functionalities of the chat application, including user management, chat creation and participation, message sending and reading, and token-based authentication. The use of foreign keys and indexes helps maintain data integrity and query performance.