# `guests` — Wedding Guests

**Module:** Guest  
**Type:** Tenant  
**Soft Delete:** Yes (`is_deleted`, `deleted_at`)

---

## Purpose

The core guest management table. Each row represents one invited guest in a workspace. Guests have a complete lifecycle from invitation through check-in.

---

## Columns

| Column | Type | Nullable | Default | Description |
|--------|------|----------|---------|-------------|
| `id` | `BIGINT UNSIGNED AUTO_INCREMENT` | No | — | Internal ID — never in URLs |
| `workspace_id` | `BIGINT UNSIGNED` | No | — | → `workspaces.id` |
| `guest_code` | `VARCHAR(20)` | No | — | Cryptographically random public token for URL |
| `full_name` | `VARCHAR(150)` | No | — | Guest full name |
| `side` | `VARCHAR(10)` | No | — | `groom \| bride \| couple` |
| `guest_group` | `VARCHAR(30)` | No | — | `family \| relatives \| friends \| colleagues \| church \| business_partners \| vip \| others` |
| `invited_pax` | `TINYINT UNSIGNED` | No | `1` | Max attendees (minimum 1) |
| `phone_number` | `VARCHAR(20)` | Yes | NULL | WhatsApp-compatible number |
| `notes` | `TEXT` | Yes | NULL | Internal notes. Not visible to guest |
| `qr_code_path` | `VARCHAR(500)` | Yes | NULL | Path to generated QR image file |
| `invitation_status` | `VARCHAR(10)` | No | `not_sent` | `not_sent \| sent \| opened` |
| `rsvp_status` | `VARCHAR(10)` | No | `pending` | `pending \| attending \| declined` |
| `checkin_status` | `VARCHAR(15)` | No | `not_arrived` | `not_arrived \| checked_in \| no_show` |
| `first_opened_at` | `DATETIME` | Yes | NULL | First invitation URL open timestamp |
| `last_opened_at` | `DATETIME` | Yes | NULL | Most recent URL open |
| `invitation_sent_at` | `DATETIME` | Yes | NULL | When couple first sent invitation |
| `rsvp_submitted_at` | `DATETIME` | Yes | NULL | When guest submitted RSVP |
| `checked_in_at` | `DATETIME` | Yes | NULL | When guest was checked in |
| `is_deleted` | `TINYINT(1)` | No | `0` | Soft delete flag |
| `deleted_at` | `DATETIME` | Yes | NULL | Soft delete timestamp |
| `created_at` | `DATETIME` | No | `CURRENT_TIMESTAMP` | — |
| `updated_at` | `DATETIME` | Yes | NULL | — |

---

## Primary Key

`id` — `BIGINT UNSIGNED`

---

## Indexes

| Name | Columns | Type |
|------|---------|------|
| `PRIMARY` | `id` | Primary |
| `uq_guests_code_workspace` | `workspace_id, guest_code` | Unique |
| `idx_guests_workspace_id` | `workspace_id` | Index |
| `idx_guests_workspace_side` | `workspace_id, side` | Index |
| `idx_guests_workspace_group` | `workspace_id, guest_group` | Index |
| `idx_guests_rsvp_status` | `workspace_id, rsvp_status` | Index |
| `idx_guests_checkin_status` | `workspace_id, checkin_status` | Index |
| `idx_guests_invitation_status` | `workspace_id, invitation_status` | Index |
| `idx_guests_is_deleted` | `workspace_id, is_deleted` | Index |
| `idx_guests_full_name` | `workspace_id, full_name` | Index |

---

## Foreign Keys

| Column | References | On Delete | On Update |
|--------|-----------|-----------|-----------|
| `workspace_id` | `workspaces.id` | CASCADE | CASCADE |

---

## Referenced By

| Table | Column | On Delete |
|-------|--------|-----------|
| `guest_events` | `guest_id` | CASCADE |
| `rsvps` | `guest_id` | CASCADE |
| `guest_timelines` | `guest_id` | CASCADE |
| `wishes` | `guest_id` | SET NULL |
| `checkins` | `guest_id` | RESTRICT |
| `communication_logs` | `guest_id` | RESTRICT |

---

## Guest Lifecycle

```
invited_pax set
    ↓
invitation_status: not_sent → sent → opened
    ↓
rsvp_status: pending → attending | declined
    ↓
checkin_status: not_arrived → checked_in | no_show
```

---

## Business Rules

- `guest_code` is unique per workspace (not globally), used in invitation URLs
- `invited_pax` determines max `confirmed_pax` in RSVP
- All queries MUST filter `is_deleted = 0` to exclude soft-deleted records
- Phone numbers should be in WhatsApp-compatible format (e.g., `628123456789`)
- `qr_code_path` is set when QR image is generated for check-in

---

## Notes

- `guest_code` is a URL-safe random token (not an incrementing ID) — guests cannot enumerate other guests
- `notes` are internal and never displayed on the invitation or to the guest
- The heavy index set on this table reflects the many filter combinations in the Guest List UI