# INVEETAIRE — Database Schema Reference

> **Documentation Version:** v1.0.0  
> **Generated:** 2026-07-21  
> **Engine:** MySQL / MariaDB 10.x (InnoDB)  
> **Charset:** `utf8mb4_unicode_ci`  
> **Schema Source:** `database/schema.sql`

---

## Overview

INVEETAIRE is a wedding management SaaS platform. The database powers three distinct user roles — **Super Admin**, **Couple**, and **Event Crew** — across a multi-tenant workspace architecture.

Each workspace corresponds to one wedding event and is fully isolated. All tenant data (guests, invitations, checkins, vendors, etc.) is scoped to a `workspace_id` foreign key.

---

## Total Tables

| Count | Module |
|-------|--------|
| 43    | Total tables across all modules |

---

## Module Grouping

| Module | Tables | Description |
|--------|--------|-------------|
| **Authentication** | `roles`, `users`, `sessions`, `support_sessions`, `crew_accounts` | User identity, role-based access, session management |
| **System Configuration** | `event_types`, `commercial_plans`, `system_configurations` | Platform-level reference data and feature flags |
| **Workspace** | `workspaces`, `retention_policies`, `workspace_configurations`, `printer_configurations`, `notifications` | Tenant lifecycle, configuration, and notifications |
| **Theme** | `themes`, `theme_versions`, `theme_event_types` | Invitation theme catalog and versioning |
| **Wedding** | `wedding_events` | Individual ceremony/event records within a workspace |
| **Guest** | `guests`, `guest_events`, `rsvps`, `guest_timelines`, `wishes` | Complete guest management, RSVP, and lifecycle tracking |
| **Invitation** | `invitations`, `invitation_sections` | Digital invitation configuration and section management |
| **Attendance** | `checkins`, `label_print_jobs` | Wedding-day check-in operations and label printing |
| **Messaging** | `message_templates`, `communication_logs` | WhatsApp message templates and delivery audit |
| **Media** | `media_files`, `media_variants` | Uploaded file management and processing pipeline |
| **Budget** | `budget_settings`, `budget_categories`, `budget_expenses`, `budget_payments` | Wedding budget planning and expense tracking |
| **Timeline** | `timeline_phases`, `timeline_tasks`, `timeline_milestones` | Pre-wedding task planning and milestone tracking |
| **Vendor** | `vendor_categories`, `vendors`, `vendor_quotations`, `vendor_communications` | Vendor management and communication logging |
| **Audit** | `audit_logs` | Immutable platform-wide event audit trail |

---

## Naming Conventions

### Table Names
- **Plural snake_case** — `guests`, `wedding_events`, `communication_logs`
- Avoid abbreviations; prefer full descriptive names
- Junction/pivot tables use both entity names: `theme_event_types`, `guest_events`

### Column Names
- **Singular snake_case** — `full_name`, `workspace_id`, `created_at`
- Boolean flags: prefixed with `is_` or `has_` — `is_active`, `has_angpao`
- Status enums: suffixed with `_status` — `workspace_status`, `invitation_status`

---

## Primary Key Convention

- All tables use a **surrogate auto-increment primary key** named `id`
- Type varies by expected cardinality:
  - `TINYINT UNSIGNED` — small reference tables (`roles`, `event_types`, `commercial_plans`)
  - `SMALLINT UNSIGNED` — medium reference tables (`themes`, `theme_versions`)
  - `BIGINT UNSIGNED` — all transactional/tenant tables (`users`, `guests`, `checkins`, etc.)
- Never exposed in public URLs — use opaque tokens (`guest_code`, `session_token`, `slug`) instead

---

## Foreign Key Convention

- All FK columns are named `{referenced_table_singular}_id` — e.g., `workspace_id → workspaces.id`
- FK constraint names follow: `fk_{table}_{column}` — e.g., `fk_guests_workspace_id`
- **Cascade behavior**:
  - `ON DELETE CASCADE` — child records deleted when parent is deleted (most workspace-scoped data)
  - `ON DELETE RESTRICT` — prevents orphan deletion when referential integrity must be preserved (e.g., `checkins.operator_id`)
  - `ON DELETE SET NULL` — nullable FK set to NULL when parent is deleted (e.g., `wishes.guest_id` for anonymous wishes)

---

## Timestamp Convention

| Column | Type | Purpose |
|--------|------|---------|
| `created_at` | `DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP` | Row creation — immutable |
| `updated_at` | `DATETIME NULL DEFAULT NULL` | Last modification — NULL until first update |
| `deleted_at` | `DATETIME NULL DEFAULT NULL` | Soft delete timestamp — NULL if active |
| `*_at` variants | `DATETIME NULL` | Domain-specific event timestamps (e.g., `checked_in_at`, `published_at`) |

All timestamps are stored in **UTC** and displayed in the application's local timezone.

---

## Soft Delete Convention

Several tables implement **soft deletes** to preserve audit history:

| Table | Flag Column | Timestamp Column |
|-------|-------------|-----------------|
| `guests` | `is_deleted TINYINT(1)` | `deleted_at DATETIME` |
| `wedding_events` | `is_deleted TINYINT(1)` | `deleted_at DATETIME` |
| `vendors` | `is_deleted TINYINT(1)` | `deleted_at DATETIME` |
| `budget_categories` | `is_deleted TINYINT(1)` | `deleted_at DATETIME` |
| `budget_expenses` | `is_deleted TINYINT(1)` | `deleted_at DATETIME` |
| `budget_payments` | `is_deleted TINYINT(1)` | `deleted_at DATETIME` |
| `timeline_tasks` | `is_deleted TINYINT(1)` | `deleted_at DATETIME` |
| `timeline_milestones` | `is_deleted TINYINT(1)` | `deleted_at DATETIME` |
| `vendor_quotations` | `is_deleted TINYINT(1)` | — |
| `vendor_communications` | `is_deleted TINYINT(1)` | — |

**Hard deletes** are used for: `sessions`, `rsvps`, `checkins`, `audit_logs` (immutable by design).

Workspace-scoped data is hard-deleted via `ON DELETE CASCADE` when a workspace is purged.

---

## General Database Architecture

```
Platform Level (non-tenant)
├── roles
├── event_types
├── commercial_plans
├── themes / theme_versions / theme_event_types
└── system_configurations

Tenant Root
└── workspaces
    ├── users (couple + crew)
    │   ├── sessions
    │   ├── support_sessions
    │   └── crew_accounts
    ├── retention_policies
    ├── workspace_configurations
    ├── printer_configurations
    ├── notifications
    ├── invitations
    │   └── invitation_sections
    ├── wedding_events
    │   └── guest_events (junction)
    ├── guests
    │   ├── guest_events (junction)
    │   ├── 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

Platform Audit
└── audit_logs (soft FK — survives entity deletion)
```

---

## Documentation Rules

Every schema modification **MUST** update:

1. `README_SCHEMA.md` — if table count, modules, or conventions change
2. `RELATIONSHIP.md` — if relationships between entities change
3. `tables/{table_name}.md` — for the affected table(s)
4. `CHANGELOG.md` — always, with semantic version bump
5. `AI_CONTEXT.md` — if the business logic changes meaningfully
6. `schema.sql` — keep synchronized with `database/schema.sql`

---

## References

- `database/schema.sql` — Ground-truth DDL
- `docs/database/RELATIONSHIP.md` — Entity relationship diagrams
- `docs/database/AI_CONTEXT.md` — Business domain guide for AI assistants
- `docs/database/CHANGELOG.md` — Schema change history
- `docs/database/tables/` — Per-table detailed documentation
