# Analytical database schema (read-only profiling)

Use **only** the tables and columns below. Schema is `dbo`. Filter investigative queries with the target `user_id` (e.g. `WHERE user_id = @id` or your chosen parameter).

**Naming notes**

- Activity events live in **`user_activity_logs`** (not `activity_logs`).
- Free-text feedback is in **`reviews.review`**; numeric per-order scores are in **`order_reviews`** (join via `order_id` / `user_id` as needed). **`ratings`** is keyed by `review_id` → **`reviews.id`**.
- Join orders to line items: **`order_item_details.order_id`** = **`restaurant_order_master.id`**.
- Join orders to delivery address: **`customer_orders_address.order_id`** = **`restaurant_order_master.id`**.
- **`users.id`** is the same identifier as **`restaurant_order_master.user_id`** (and other `user_id` columns).

---

## `users`

**Security:** Do **not** `SELECT` or log authentication or OTP columns (`password`, `user_otp`, `email_verification_code`). They are omitted from this list so the model does not target them.

| column | type |
|--------|------|
| id | bigint |
| application_type | tinyint |
| name | varchar |
| email | varchar |
| phone_code | varchar |
| phone | varchar |
| image | varchar |
| otp_date | datetime |
| date_of_birth | date |
| anniversary_date | date |
| status | tinyint |
| is_deleted | tinyint |
| last_login | datetime |
| created_by | bigint |
| created_at | datetime |
| updated_by | bigint |
| updated_at | datetime |
| deleted_by | bigint |
| deleted_at | datetime |
| is_new_user | tinyint |
| is_phone_verified | tinyint |
| user_iso_code | varchar |
| referral_code | varchar |
| referral_code_used | varchar |

---

## `customers`

| column | type |
|--------|------|
| id | bigint |
| user_id | bigint |
| stripe_customer_id | varchar |
| google_social_media_id | varchar |
| facebook_social_media_id | varchar |
| apple_social_media_id | varchar |
| platform | varchar |
| dietary_preferences | text |
| special_requests | text |
| created_by | bigint |
| created_at | datetime |
| updated_by | bigint |
| updated_at | datetime |
| square_customer_id | varchar |

---

## `restaurant_order_master`

| column | type |
|--------|------|
| id | bigint |
| user_id | bigint |
| user_name | varchar |
| user_mobile_number | varchar |
| user_email | varchar |
| restaurant_id | bigint |
| restaurant_detail | ntext |
| language_id | int |
| address_id | bigint |
| coupon_id | bigint |
| table_id | bigint |
| transaction_id | varchar |
| total_amount | decimal |
| sub_total | decimal |
| tax_amount | decimal |
| tax_rate | decimal |
| tax_type | varchar |
| creditcard_fee_amount | decimal |
| creditcard_fee_rate | decimal |
| creditcard_fee_type | varchar |
| order_delivery | varchar |
| payment_option | varchar |
| admin_payment_option | varchar |
| order_status | varchar |
| accept_order_time | datetime |
| scheduled_date | date |
| scheduled_time | time |
| extra_comment | ntext |
| cancel_reason | text |
| reject_reason | text |
| invoice | varchar |
| refunded_amount | decimal |
| stripe_refund_id | varchar |
| refund_status | varchar |
| refund_reason | text |
| pickup_time | datetime |
| pickup_otp | varchar |
| order_from | tinyint |
| platform | varchar |
| is_deleted | tinyint |
| created_by | bigint |
| created_at | datetime |
| updated_by | bigint |
| updated_at | datetime |
| deleted_by | bigint |
| deleted_at | datetime |
| payment_status | varchar |
| reedem_points | int |
| redeem_amount | decimal |
| user_reward_points_id | bigint |
| contribution_amount | decimal |
| eatance_fee_percentage | decimal |
| eatance_fee_fixed | decimal |
| eatance_fee_amount | decimal |
| invoice_total_amount | decimal |
| completed_date | datetime |
| tip_amount | decimal |
| tip_type | varchar |
| tip_value | decimal |
| webhook_payment | tinyint |
| eatance_cost_type | varchar |
| eatance_cost_value | decimal |
| convenience_fees | decimal |
| convenience_amount | decimal |
| order_view | tinyint |
| refunded_date | datetime |
| latitude | varchar |
| longitude | varchar |
| square_order_id | varchar |
| square_receipt_id | varchar |
| payment_mode | varchar |
| user_timezone | varchar |

---

## `customer_orders_address`

| column | type |
|--------|------|
| id | bigint |
| order_id | bigint |
| address_title | varchar |
| address | text |
| area | text |
| landmark | text |
| latitude | varchar |
| longitude | varchar |
| zipcode | varchar |
| country | varchar |
| state | varchar |
| city | varchar |
| created_by | bigint |
| created_at | datetime |
| updated_by | bigint |
| updated_at | datetime |

---

## `order_item_details`

| column | type |
|--------|------|
| id | bigint |
| order_id | bigint |
| restaurant_id | bigint |
| menu_item_id | bigint |
| menu_name | varchar |
| category_id | bigint |
| category_name | varchar |
| quantity | int |
| price | decimal |
| offer_price | decimal |
| sub_total | decimal |
| total_price | decimal |
| comment | text |
| has_addon_items | tinyint |
| has_submenu_items | tinyint |
| offer_discount | decimal |

---

## `user_activity_logs`

| column | type |
|--------|------|
| id | bigint |
| user_id | bigint |
| application_type | tinyint |
| platform | varchar |
| restaurant_id | bigint |
| coupon_id | bigint |
| event_id | bigint |
| screen | varchar |
| search_keyword | varchar |
| is_filter_applied | tinyint |
| sort_by | varchar |
| price_filter | varchar |
| fast_delivery_filter | tinyint |
| new_on_eatance_filter | tinyint |
| rating4plus_filter | tinyint |
| pure_veg_filter | tinyint |
| offers_filter | tinyint |
| best_seller_filter | tinyint |
| activity_title | varchar |
| activity_details | varchar |
| flag_deal_attained | tinyint |
| is_table_booking | tinyint |
| latitude | varchar |
| longitude | varchar |
| user_ip | varchar |
| created_at | datetime |

---

## `restaurant_impression_logs`

| column | type |
|--------|------|
| id | bigint |
| restaurant_id | bigint |
| user_id | bigint |
| screen | varchar |
| is_table_booking | tinyint |
| platform | varchar |
| created_at | datetime |

---

## `restaurant_details`

| column | type |
|--------|------|
| id | bigint |
| restaurant_id | bigint |
| language_id | int |
| restaurant_name | nvarchar |
| description | ntext |
| search_keyword | ntext |
| review_keyword | ntext |
| meta_title | nvarchar |
| meta_description | ntext |
| payment_options | nvarchar |
| parking | nvarchar |
| dress_code | nvarchar |
| accessibility | nvarchar |
| billing_name | varchar |
| pickup_instruction | varchar |

---

## `coupon_master`

| column | type |
|--------|------|
| id | bigint |
| restaurant_id | bigint |
| coupon_code | varchar |
| discount_type | varchar |
| discount_type_slug | varchar |
| coupon_amount | decimal |
| minimum_amount | decimal |
| maximum_amount | decimal |
| coupon_available_day | varchar |
| coupon_type_id | bigint |
| expiry_date | datetime |
| view_count | bigint |
| load_impression_count | bigint |
| redirections_count | bigint |
| approval_status | tinyint |
| status | tinyint |
| is_deleted | tinyint |
| created_by | bigint |
| created_at | datetime |
| updated_by | bigint |
| updated_at | datetime |
| deleted_by | bigint |
| deleted_at | datetime |
| coupon_image | varchar |
| start_date_time | datetime |
| per_user_coupon_usage_limit | bigint |
| no_of_coupons | bigint |
| meal_time | varchar |
| order_mode | varchar |
| eatance_cost_type | varchar |
| eatance_cost_value | decimal |
| sort_order | int |
| coupon_usage_type | int |
| spin_expiry_days | int |
| third_party_redirect_url | varchar |

---

## `coupon_impression_log`

| column | type |
|--------|------|
| id | bigint |
| user_id | bigint |
| restaurant_id | bigint |
| coupon_id | bigint |
| created_at | datetime |
| is_table_booking | tinyint |
| platform | varchar |

---

## `reviews`

| column | type |
|--------|------|
| id | bigint |
| restaurant_id | bigint |
| user_id | bigint |
| review | ntext |
| user_language_id | int |
| status | tinyint |
| is_deleted | tinyint |
| created_by | bigint |
| created_at | datetime |
| updated_by | bigint |
| updated_at | datetime |
| deleted_by | bigint |
| deleted_at | datetime |
| order_id | bigint |

---

## `ratings`

| column | type |
|--------|------|
| id | bigint |
| review_id | bigint |
| category_id | bigint |
| rating | varchar |

---

## `order_reviews`

Per-order star-style ratings (separate from `reviews` text). Join to orders/users when you need these fields.

| column | type |
|--------|------|
| id | bigint |
| user_id | bigint |
| order_id | bigint |
| restaurant_id | bigint |
| order_experience_rating | tinyint |
| food_served_rating | tinyint |
| recommend_eatance_rating | tinyint |
| comment | ntext |
| approval_status | tinyint |
| status | tinyint |
| approved_by | bigint |
| created_by | bigint |
| created_at | datetime |
| updated_by | bigint |
| updated_at | datetime |

---

## `cart_master`

| column | type |
|--------|------|
| id | bigint |
| user_id | bigint |
| restaurant_id | bigint |
| table_id | bigint |
| coupon_id | bigint |
| created_by | bigint |
| created_at | datetime |
| updated_by | bigint |
| updated_at | datetime |
| flag_redeem_points | tinyint |
| is_restaurant_contribution | tinyint |
| tip_amount | decimal |
| tip_type | varchar |
| tip_value | decimal |
| guest_user_id | varchar |

---

## `add_to_cart_logs`

Cart add/remove events (same source as cart statistics in the profiling pipeline). `action` distinguishes event type (e.g. add/remove).

| column | type |
|--------|------|
| id | bigint |
| user_id | bigint |
| restaurant_id | bigint |
| item_id | bigint |
| price | decimal |
| action | varchar |
| created_at | datetime |
| created_by | bigint |