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

## 📦 Table: `tbl_enquiries`

**Module:** `crm`  
**Schema:** `public`  
**Purpose:** Stores customer enquiries including personal details, notes, follow-up information, and status.  
**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                                     |
| `enq_id`       | VARCHAR      | YES      | NULL              | Custom enquiry identifier                       |
| `name`         | VARCHAR      | NO       | NULL              | First name of the enquirer                      |
| `last_name`    | VARCHAR      | YES      | NULL              | Last name of the enquirer                       |
| `mobile_no`    | VARCHAR      | NO       | NULL              | Contact mobile number                           |
| `email`        | VARCHAR      | NO       | NULL              | Email address of the enquirer                   |
| `notes`        | VARCHAR      | YES      | NULL              | Additional notes regarding the enquiry          |
| `followup_date`| DATE/TEXT    | YES      | NULL              | Scheduled follow-up date                        |
| `message`      | VARCHAR/TEXT | NO       | NULL              | Message content of the enquiry                  |
| `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 the enquiry                 |
| `updated_by`   | INT          | YES      | NULL              | User ID who last updated the enquiry            |
| `status_id`    | INT          | YES      | NULL              | Foreign key referencing `tbl_status.id`         |

---

## 🗂️ Constraints & Indexes

* **Primary Key:** `id`  
* **Foreign Key:**  
  * `status_id → tbl_status.id`  
* **Indexes:**  
  * `tbl_enquiries_pkey` on `id` (UNIQUE, BTREE)  
  * Consider index on `mobile_no` and `email` for quick lookups  
  * Consider index on `status_id` for filtering enquiries by status

---

## 🔗 Relationships

| Related Table          | Relationship Type | Foreign Key                                 | Description                            |
|------------------------|------------------|---------------------------------------------|----------------------------------------|
| `tbl_status`           | Many-to-One      | `tbl_enquiries.status_id → tbl_status.id`   | Enquiry is linked to a status          |
| `tbl_enquiry_tracking` | One-to-Many      | `tbl_enquiry_tracking.enquiry_id → tbl_enquiries.id` | Enquiry can have multiple follow-ups |

---

## 🧬 ER Diagram (optional)

```dbml
Table tbl_enquiries {
  id serial [pk]
  enq_id varchar
  name varchar
  last_name varchar
  mobile_no varchar
  email varchar
  notes varchar
  followup_date date
  message text
  created_at timestamp
  updated_at timestamp
  created_by int
  updated_by int
  status_id int
}
Ref: tbl_enquiries.status_id > tbl_status.id
Ref: tbl_enquiry_tracking.enquiry_id > tbl_enquiries.id

```

---

## 🛠️ Sample SQL

```sql
CREATE TABLE tbl_enquiries (
    id SERIAL PRIMARY KEY,
    enq_id VARCHAR DEFAULT NULL,
    name VARCHAR NOT NULL,
    last_name VARCHAR DEFAULT NULL,
    mobile_no VARCHAR NOT NULL,
    email VARCHAR NOT NULL,
    notes VARCHAR DEFAULT NULL,
    followup_date DATE DEFAULT NULL,
    message TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by INT,
    updated_by INT,
    status_id INT,
    CONSTRAINT fk_status FOREIGN KEY (status_id) REFERENCES tbl_status(id)
);

```

---

## 🕓 Change History

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