# INVEETAIRE — Entity Relationship Documentation

> **Version:** v1.0.0  
> **Generated:** 2026-07-21  
> **Describes:** All table relationships across the INVEETAIRE database

---

## High-Level Entity Map

```
roles ──────────────────────────────────── users
event_types ──────────────────────────── workspaces
commercial_plans ─────────────────────── workspaces
themes → theme_versions → theme_event_types ← event_types
                    │
                    └─────────────────── invitations

workspaces ─┬── users ──────────────────── sessions
            │           └─────────────── support_sessions
            │           └─────────────── crew_accounts
            ├── retention_policies
            ├── workspace_configurations
            ├── printer_configurations
            ├── notifications
            ├── invitations ───────────── invitation_sections
            ├── wedding_events ─────────── guest_events
            ├── guests ─────────────────── guest_events
            │          └──────────────── rsvps
            │          └──────────────── guest_timelines
            │          └──────────────── wishes
            ├── checkins ───────────────── label_print_jobs
            ├── message_templates ──────── communication_logs
            ├── media_files ────────────── media_variants
            ├── budget_settings
            ├── budget_categories ──────── budget_expenses ── budget_payments
            ├── timeline_phases ────────── timeline_tasks
            ├── timeline_milestones
            └── vendor_categories ──────── vendors ─── vendor_quotations
                                                   └── vendor_communications

audit_logs (platform-wide, soft-linked — no enforced FK)
```

---

## One-to-One Relationships

| Parent | Child | Description |
|--------|-------|-------------|
| `workspaces` | `invitations` | Every workspace has exactly one invitation (digital wedding card). UNIQUE constraint on `workspace_id`. |
| `workspaces` | `retention_policies` | Every workspace has exactly one retention policy record. UNIQUE on `workspace_id`. |
| `workspaces` | `budget_settings` | Every workspace has exactly one budget settings row. UNIQUE on `workspace_id`. |
| `workspaces` | `printer_configurations` | Every workspace has exactly one printer config. UNIQUE on `workspace_id`. |
| `guests` | `rsvps` | Each guest can have at most one RSVP record. UNIQUE on `guest_id`. |
| `users` | `crew_accounts` | One user account maps to one crew account (per workspace). UNIQUE on `(user_id, workspace_id)`. |

---

## One-to-Many Relationships

### Platform Level

| Parent | Children | Description |
|--------|----------|-------------|
| `roles` | `users` | One role can be assigned to many users |
| `event_types` | `workspaces` | One event type (Wedding, Engagement) can have many workspaces |
| `commercial_plans` | `workspaces` | One plan can be assigned to many workspaces |
| `themes` | `theme_versions` | One theme has multiple versioned releases |
| `theme_versions` | `theme_event_types` | One theme version supports multiple event type mappings |

### Workspace Level

| Parent | Children | Description |
|--------|----------|-------------|
| `workspaces` | `users` | One workspace has one couple + multiple crew users |
| `workspaces` | `wedding_events` | One workspace has one or more ceremonies |
| `workspaces` | `guests` | One workspace has many guests |
| `workspaces` | `notifications` | One workspace has many notifications |
| `workspaces` | `message_templates` | One workspace has up to 3 message templates (by type) |
| `workspaces` | `media_files` | One workspace has many uploaded media files |
| `workspaces` | `budget_categories` | One workspace has many budget categories |
| `workspaces` | `timeline_phases` | One workspace has many planning phases |
| `workspaces` | `timeline_milestones` | One workspace has many milestones |
| `workspaces` | `vendor_categories` | One workspace has many vendor categories |
| `invitations` | `invitation_sections` | One invitation has many configurable sections |
| `guests` | `guest_timelines` | One guest has many timeline events (append-only audit) |
| `guests` | `wishes` | One guest can submit multiple wishes (though typically one) |
| `checkins` | `label_print_jobs` | One check-in can trigger multiple label print jobs |
| `media_files` | `media_variants` | One uploaded file has multiple processed variants (thumbnail, display, full, gallery) |
| `budget_categories` | `budget_expenses` | One budget category has many expense items |
| `budget_expenses` | `budget_payments` | One expense item has multiple payment records |
| `timeline_phases` | `timeline_tasks` | One planning phase has many tasks |
| `vendor_categories` | `vendors` | One vendor category has many vendors |
| `vendors` | `vendor_quotations` | One vendor has multiple quotations |
| `vendors` | `vendor_communications` | One vendor has many communication/meeting logs |
| `message_templates` | `communication_logs` | One template can generate many delivery log entries |
| `users` | `sessions` | One user can have many sessions (multi-device) |

---

## Many-to-Many Relationships

| Entity A | Junction Table | Entity B | Description |
|----------|---------------|----------|-------------|
| `guests` | `guest_events` | `wedding_events` | A guest can be invited to multiple ceremonies; a ceremony has many guests |
| `theme_versions` | `theme_event_types` | `event_types` | A theme version can apply to multiple event types; an event type can use many themes |

---

## Dependency Flow (Creation Order)

When setting up a new workspace, records must be created in this order to satisfy FK constraints:

```
1. roles (seeded)
2. event_types (seeded)
3. commercial_plans (seeded)
4. themes → theme_versions → theme_event_types (seeded)
5. workspaces
6. users (couple account)
7. crew_accounts (crew users)
8. invitations
9. invitation_sections
10. wedding_events
11. retention_policies
12. workspace_configurations
13. printer_configurations
14. budget_settings
15. budget_categories
16. timeline_phases
17. vendor_categories
```

Guest, checkin, and messaging data is created during workspace operation.

---

## Workspace Hierarchy

```
workspaces (tenant root)
│
├── Identity & Auth
│   ├── users (role_id → roles, workspace_id)
│   │   ├── sessions (user_id)
│   │   └── crew_accounts (user_id, workspace_id)
│   └── support_sessions (admin_user_id, target_workspace_id, parent_session_id)
│
├── Configuration
│   ├── retention_policies (workspace_id) [1:1]
│   ├── workspace_configurations (workspace_id)
│   └── printer_configurations (workspace_id) [1:1]
│
├── Invitation
│   ├── invitations (workspace_id) [1:1]
│   │   └── invitation_sections (invitation_id)
│   └── media_files (workspace_id)
│       └── media_variants (media_file_id)
│
└── Operations
    ├── wedding_events (workspace_id)
    ├── guests (workspace_id)
    ├── guest_events (guest_id, wedding_event_id) [junction]
    ├── rsvps (guest_id) [1:1]
    ├── guest_timelines (guest_id)
    ├── wishes (workspace_id, guest_id nullable)
    ├── checkins (guest_id, wedding_event_id, operator_id)
    └── label_print_jobs (checkin_id)
```

---

## Guest Lifecycle Hierarchy

```
Guest Record (guests)
│
├── Invited to ceremonies → guest_events (junction with wedding_events)
│
├── Receives WhatsApp invitation
│   └── communication_logs (template_id → message_templates)
│
├── Opens invitation URL
│   └── guest_timelines (event_type: opened)
│
├── Submits RSVP
│   └── rsvps (1:1) + guest_timelines (event_type: rsvp_submitted)
│
├── Submits wish (optional)
│   └── wishes
│
└── Arrives at wedding
    └── checkins + guest_timelines (event_type: checked_in)
        └── label_print_jobs (optional angpao/souvenir labels)
```

---

## Invitation Hierarchy

```
invitations (1 per workspace)
│
├── theme_version_id → theme_versions → themes
│
├── music_file_id → media_files (background music)
│
└── invitation_sections (one row per theme section)
    - hero, story, gallery, venue, rsvp, wishes, gift, music
    - section_config: JSON per-section customization
```

---

## Attendance Hierarchy

```
checkins
├── guest_id → guests
├── wedding_event_id → wedding_events (which ceremony)
├── operator_id → users (crew or couple who performed check-in)
├── is_correction / corrects_id (self-referential — corrects a prior checkin)
└── label_print_jobs (triggered per check-in)
```

---

## Budget Hierarchy

```
budget_settings (1 per workspace, total budget)
└── budget_categories (user-defined categories: Venue, Catering, etc.)
    └── budget_expenses (line items per category)
        ├── vendor_id → vendors (optional — link to vendor record)
        └── budget_payments (payment milestones for each expense)
```

---

## Vendor Hierarchy

```
vendor_categories (user-defined: Photography, Catering, etc.)
└── vendors (individual vendor/supplier records)
    ├── budget_expense_id → budget_expenses (optional — links to budget)
    ├── vendor_quotations (price quotes and negotiated values)
    └── vendor_communications (meeting logs and follow-up records)
```

---

## Timeline Hierarchy

```
timeline_phases (planning time ranges: "12+ Months", "6-12 Months", etc.)
└── timeline_tasks (specific planning tasks per phase)
    ├── vendor_id → vendors (optional — task linked to a vendor)
    └── reminder settings (reminder_date, reminder_notes)

timeline_milestones (standalone date-based milestones, not in phases)
```

---

## Audit Trail

`audit_logs` is a **platform-wide append-only table** with:
- Soft (non-enforced) references to actors, workspaces, and entities
- Records survive actor or entity deletion (no CASCADE)
- Used for security, compliance, and debugging

`guest_timelines` is a **guest-scoped append-only log** that records every significant interaction for a single guest (harder delete via CASCADE on guest deletion).

---

## Cross-Module References

| From | To | Column | Type |
|------|----|--------|------|
| `invitations` | `media_files` | `music_file_id` | Deferred FK (circular dependency) |
| `workspaces` | `users` | `created_by` | Deferred FK (circular dependency) |
| `budget_expenses` | `vendors` | `vendor_id` | Optional link |
| `timeline_tasks` | `vendors` | `vendor_id` | Optional link |
| `vendors` | `budget_expenses` | `budget_expense_id` | Optional backlink |
| `audit_logs` | `users` | `actor_id` | Soft reference (not FK) |
| `audit_logs` | `workspaces` | `workspace_id` | Soft reference (not FK) |
| `guest_timelines` | `users` | `actor_id` | Soft reference (not FK) |