MLA Portal

Develop a real-time chat module that enables secure communication between citizens and MLA office staff. The system should allow admins to manage chat rooms, assign conversations to specific staff members, and monitor message flow. It must ensure that only assigned admins can respond, maintain chat status control (open or closed), support message attachments, and provide moderation features to block abusive users and maintain a safe communication environment.

***Table: chat_rooms

Field Type Description

id BIGINT (PK) Chat room ID
constituency_id BIGINT (FK) Constituency
public_user_id BIGINT (FK → users.id) Citizen
assigned_admin_id BIGINT (FK → users.id) Assigned admin
status ENUM('OPEN','CLOSED') Chat status
created_at TIMESTAMP Chat start time

***Table: chat_messages

Field Type Description

id BIGINT (PK) Message ID
chat_room_id BIGINT (FK) Chat room
sender_id BIGINT (FK → users.id) Sender
message_ml LONGTEXT Message in Malayalam
attachment TEXT File attachment
is_read BOOLEAN Read status
sent_at TIMESTAMP Sent time

***Validation

*constituency_id must exist in the constituencies table

*public_user_id must exist and have role = PUBLIC

*assigned_admin_id must exist and have role = ADMIN/STAFF

*Only one active OPEN chat room is allowed per public user per constituency

*status must be either OPEN or CLOSED

*Chat room can be created only for active users

*Only Admin can close a chat room

*chat_room_id must exist and be in OPEN status to send messages

*sender_id must be either the public_user_id or the assigned_admin_id of that chat room

*message_ml cannot be empty if no attachment is provided

*Attachments must be of allowed file types (image/pdf) and size limits

*is_read defaults to FALSE when message is created

*Messages cannot be edited after being sent

*Abusive or blocked users cannot send messages