# 📊 Loyalty Package – Database Schema Documentation
---

## 🧍‍♂️ `users` (extended)

Holds user information with loyalty-related fields.

| Column              | Type       | Description                      |
| ------------------- | ---------- | -------------------------------- |
| id                  | bigint     | Primary key                      |
| name                | string     | User full name                   |
| email               | string     | User email (unique)              |
| password            | string     | User password hash               |
| tier\_level         | string     | Loyalty tier name (e.g., silver) |
| tier\_points\_total | string     | Sum of all points ever earned    |
| referral\_code      | string     | User's referral code             |
| timestamps          | timestamps | Created/updated                  |

---

## 🪙 `loyalty_points`

Tracks all point-earning events per user.

| Column      | Type       | Description                            |                                  |
| ----------- | ---------- | -------------------------------------- | -------------------------------- |
| id          | bigint     | Primary key                            |                                  |
| user\_id    | foreignId  | Related user                           |                                  |
| points      | integer    | Points earned                          |                                  |
| description | string     | Description of the point event         |                                  |
| type        | string     | Source type (e.g., purchase, referral) |                                  |
| source\_id  | string     | null                                   | Optional ID of the source object |
| expires\_at | timestamp  | When the points expire                 |                                  |
| timestamps  | timestamps | Created/updated                        |                                  |

---

## 🎁 `rewards`

Redeemable rewards that cost points.

| Column       | Type       | Description                    |
| ------------ | ---------- | ------------------------------ |
| id           | bigint     | Primary key                    |
| name         | string     | Reward name                    |
| description  | text       | Optional description           |
| points\_cost | integer    | Cost in points                 |
| reward\_type | string     | Type (e.g., discount, product) |
| validity     | timestamp  | Optional expiration date       |
| timestamps   | timestamps | Created/updated                |

---

## 🔁 `redemptions`

Tracks when users redeem rewards.

| Column           | Type       | Description                       |
| ---------------- | ---------- | --------------------------------- |
| id               | bigint     | Primary key                       |
| user\_id         | foreignId  | Redeeming user                    |
| reward\_id       | foreignId  | Redeemed reward                   |
| points\_spent    | integer    | Points used                       |
| redemption\_date | timestamp  | When the redemption occurred      |
| status           | string     | e.g., pending, approved, rejected |
| timestamps       | timestamps | Created/updated                   |

---

## 📐 `tiers`

Defines loyalty tiers with point ranges.

| Column         | Type       | Description                    |
| -------------- | ---------- | ------------------------------ |
| id             | bigint     | Primary key                    |
| name           | string     | Tier name (e.g., Bronze, Gold) |
| min\_points    | integer    | Required minimum points        |
| max\_points    | integer    | Optional maximum points        |
| benefits\_json | json       | Encoded list of tier benefits  |
| timestamps     | timestamps | Created/updated                |

---

## 🧑‍🤝‍🧑 `referrals`

Tracks referral connections between users.

| Column         | Type       | Description           |
| -------------- | ---------- | --------------------- |
| id             | bigint     | Primary key           |
| referrer\_id   | bigint     | User who referred     |
| referee\_id    | bigint     | User who was referred |
| bonus\_awarded | integer    | Referral points given |
| timestamps     | timestamps | Created/updated       |

---

## ⚙️ `loyalty_settings`

Admin-configurable key/value settings.

| Column     | Type       | Description                                   |
| ---------- | ---------- | --------------------------------------------- |
| id         | bigint     | Primary key                                   |
| key        | string     | Config key (e.g., points.per\_currency\_unit) |
| value      | text       | Stored value as string                        |
| timestamps | timestamps | Created/updated                               |

---