Appointments — Technical Reference
This page covers the data models, architecture, and future integration patterns behind the Appointments module.
Data Models
appointments
| Column | Type | Description |
|---|---|---|
id | uuid | Primary key |
workspace_id | uuid | FK → workspaces.id — tenant isolation (ON DELETE CASCADE) |
title | text | Required. Short label for the appointment |
description | text | Optional detailed notes or agenda |
location | text | Optional location (physical address, Zoom link, etc.) |
type | text | 'meeting', 'service', or 'other' (CHECK constraint, defaults to 'meeting') |
start_time | timestamptz | Required. When the appointment begins (stored in UTC) |
end_time | timestamptz | Required. When the appointment ends (stored in UTC, must be after start_time) |
status | text | 'scheduled', 'completed', 'canceled', or 'no_show' (CHECK constraint, defaults to 'scheduled') |
contact_id | uuid | Required. FK → contacts.id (ON DELETE CASCADE) |
assigned_to | uuid | FK → user_profiles.id — assigned member (defaults to creator) |
source | text | Identifies origin system (currently always 'manual', reserved for integrations) |
external_id | text | Reserved for third-party integration IDs |
external_url | text | Reserved for third-party booking page URLs |
transaction_id | uuid | Dormant FK → transactions.id (ON DELETE SET NULL) |
created_by | uuid | FK → user_profiles.id — who created the appointment |
updated_by | uuid | FK → user_profiles.id — who last modified the appointment |
created_at | timestamptz | Creation timestamp |
updated_at | timestamptz | Last modification timestamp |
Foreign Key Constraints
The contact_id foreign key uses ON DELETE CASCADE. If a contact is deleted, all their
appointments are automatically removed. This differs from Tasks (ON DELETE SET NULL)
because an appointment without an associated contact has no operational context in the CRM.
Architecture
Derived Status (Assumed Past)
Appointments use an "assumed past" model. While the database schema includes a 'completed'
status value, the UI rarely relies on it. Instead, the UI derives the "Past" state dynamically
by comparing end_time against the current time (now).
If an appointment is not 'canceled' or 'no_show', and its end_time is in the past,
it is displayed with a green "Past" badge regardless of whether the database status is
'scheduled' or 'completed'.
Smart Defaults and Validation
When initializing the appointment creation form, the UI sets smart defaults:
start_time: Tomorrow at 10:00 AM (local time)end_time: Tomorrow at 11:00 AM (local time)
When a user modifies the start_time, the client-side logic automatically adjusts the
end_time to preserve the current duration gap.
Both client-side and server-side validation enforce that end_time must be chronologically
after start_time.
Live Contact Search
When creating an appointment from the Appointments page, the contact selection uses a live search picker:
- Minimum 2 characters to trigger search
- 300ms debounce
- Performs an ILIKE search against
first_name,last_name, andemail
When creating from a Contact Detail page, this picker is replaced by a locked, read-only display of the current contact.
Future: Third-Party Integrations
The appointments schema is future-proofed for third-party scheduling integrations (like Calendly, Acuity, etc.) using an adapter pattern.
| Column | Purpose |
|---|---|
source | Distinguishes between 'manual' and future external sources (e.g., 'calendly') |
external_id | The appointment's ID in the external system, used for webhook idempotency and deduplication |
external_url | Direct link to the appointment in the external system's UI |
A composite unique index on (workspace_id, source, external_id) prevents duplicate records
from webhook retries while scoping the IDs to the specific source to avoid collisions
between different providers.
Note: These columns are currently dormant and reserved for future development.
Security
RLS Policies
Appointments use standard workspace-scoped Row Level Security with no role restrictions:
| Operation | Policy |
|---|---|
| SELECT | All workspace members |
| INSERT | All workspace members |
| UPDATE | All workspace members |
| DELETE | All workspace members |
Unlike core entities (Deals, Companies) that restrict deletion to Admins, appointment deletion is available to all members to support daily operational workflows (e.g., a receptionist managing bookings).