Viên ngọc phương Đông

## 📦 Table: `tbl_users`

**Module:** `user-management`  
**Schema:** `public`  
**Purpose:** Stores user information, authentication details, and links to roles.  
**Created By:** Mahendran T  
**Created On:** 2025-07-31  
**Last Modified:** 2025-07-31

---

## 🧩 Columns

| Column Name     | Data Type      | Nullable | Default            | Description                                   |
|-----------------|---------------|----------|--------------------|-----------------------------------------------|
| `id`            | SERIAL        | NO       | auto-increment     | Primary key                                   |
| `name`          | VARCHAR       | NO       | NULL               | Full name of the user                         |
| `mobile_no`     | VARCHAR       | YES      | NULL               | User's mobile number                          |
| `is_deleted`    | BOOLEAN       | NO       | FALSE              | Logical deletion flag                         |
| `email`         | VARCHAR       | NO       | NULL               | User email address                            |
| `password`      | VARCHAR(60)   | YES      | NULL               | Hashed password                               |
| `created_at`    | TIMESTAMP     | NO       | CURRENT_TIMESTAMP  | Record creation timestamp                     |
| `updated_at`    | TIMESTAMP     | NO       | CURRENT_TIMESTAMP  | Last updated timestamp                        |
| `created_by`    | INT           | YES      | NULL               | User ID who created this record               |
| `updated_by`    | INT           | YES      | NULL               | User ID who last updated this record          |
| `status`        | BOOLEAN/INT   | NO       | TRUE               | Account status (1 = Active, 0 = Inactive)     |
| `role_id`       | INT           | YES      | NULL               | Foreign key referencing `tbl_roles.id`        |
| `fcm_token`     | VARCHAR       | YES      | NULL               | Firebase Cloud Messaging token for push notif |
| `access_token`  | LONGTEXT      | YES      | NULL               | Access token for authentication               |
| `refresh_token` | LONGTEXT      | YES      | NULL               | Refresh token for authentication              |

---

## 🗂️ Constraints & Indexes

* **Primary Key:** `id`  
* **Foreign Key:** `role_id → tbl_roles.id`  
* **Indexes:**  
  * `tbl_users_pkey` on `id` (UNIQUE, BTREE)  
  * `tbl_users_email_key` on `email` (consider UNIQUE)  
  * `tbl_users_mobile_no_key` on `mobile_no` (consider UNIQUE)

---

## 🔗 Relationships

| Related Table      | Relationship Type | Foreign Key                          | Description                          |
|--------------------|------------------|--------------------------------------|--------------------------------------|
| `tbl_roles`        | Many-to-One      | `tbl_users.role_id → tbl_roles.id`   | Each user belongs to one role        |
| `tbl_login_activity` | One-to-Many    | `tbl_login_activity.user_id → tbl_users.id` | Each user can have many login records |

---

## 🧬 ER Diagram (optional)

```dbml
Table tbl_users {
  id serial [pk]
  name varchar
  mobile_no varchar
  is_deleted boolean
  email varchar
  password varchar(60)
  created_at timestamp
  updated_at timestamp
  created_by int
  updated_by int
  status boolean
  role_id int
  fcm_token varchar
  access_token longtext
  refresh_token longtext
}
Ref: tbl_users.role_id > tbl_roles.id

---

```

## 🛠️ Sample SQL

```sql
CREATE TABLE tbl_users (
    id SERIAL PRIMARY KEY,
    name VARCHAR NOT NULL,
    mobile_no VARCHAR,
    is_deleted BOOLEAN DEFAULT FALSE,
    email VARCHAR NOT NULL,
    password VARCHAR(60),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by INT,
    updated_by INT,
    status BOOLEAN DEFAULT TRUE,
    role_id INT,
    fcm_token VARCHAR,
    access_token LONGTEXT,
    refresh_token LONGTEXT,
    CONSTRAINT fk_role FOREIGN KEY (role_id) REFERENCES tbl_roles(id)
);
```

---

## 🕓 Change History

| Date       | Author      | Change Description          |
| ---------- | ----------- | --------------------------- |
| 2025-07-31 | Mahendran | Initial documentation draft |