Appointments — Technical Reference

This page covers the data models, architecture, and future integration patterns behind the Appointments module.

Data Models

appointments

appointment_internal_attendees

Junction table mapping appointments to internal workspace members (co-workers). Supports many-to-many attendee assignments.

appointment_external_attendees

Junction table mapping appointments to external contacts. Supports many-to-many attendee assignments.

Foreign Key Constraints

The database tables enforce strict relational integrity:

• appointment_external_attendees: The contact_id foreign key uses ON DELETE CASCADE. If a contact is deleted, their associated external attendee records are automatically cascade-deleted.
• appointment_internal_attendees: The user_id foreign key uses ON DELETE CASCADE. If a user profile is deleted, their associated internal attendee records are cascade-deleted.
• appointments: The main appointment table no longer contains direct foreign keys to contacts or users, preventing dangling references and enabling clean many-to-many associations.

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'.

Unified Single-Day Duration Engine & Validation

The CRM uses a unified scheduling model that replaces dual start/end datetime selectors with a Single-Day Picker + Relative Duration selector (utilizing a shared formatter utility formatDurationMinutes and predefined presets of 15m, 30m, 45m, 1h, 1h 30m, and Custom):

• Smart Form Defaults:
  • Date: Tomorrow
  • Start Time: 10:00 AM (local time)
  • Duration: 1 hour (auto-calculates end_time as 11:00 AM local time)
• Duration-Locked Updates: When modifying the appointment start date or time, the client-side logic automatically updates the computed end time to preserve the currently selected duration.
• Edit Mode Mapping: When loading existing appointments for editing, the form uses Math.round(minutes) to safely map stored database durations (derived from end_time - start_time) to the corresponding select preset, preventing floating-point mismatch errors.
• Custom Duration & Midnight Guard: Selecting "Custom" duration reveals a conditional End Time input field.
  • If a user inputs an End Time that is numerically earlier in the day than the Start Time (e.g. 11:00 PM to 1:00 AM), the engine automatically assumes a overnight wrap-around and adds +1 day to the underlying end_time date stamp, preventing database negative-duration constraint failures.
• Validation: Enforces that the computed end_time is 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, and email

When creating from a Contact Detail page, this picker is replaced by a locked, read-only display of the current contact.

Fetch and Filter Architecture

The appointments query pipeline is powered by a highly optimized PostgreSQL RPC database function get_filtered_appointments() that aggregates both internal and external many-to-many attendee lists into clean JSONB arrays (internal_attendees and external_attendees) in a single network round-trip. For single-record retrievals, the server action getAppointmentById performs joins to retrieve creator and editor user profiles, enabling the UI to render a full audit history of who created and last updated the appointment.

This database function is invoked by the server action getAppointments(params) which accepts the following parameters:

export interface AppointmentListParams {
    page?: number;
    pageSize?: number;
    search?: string;
    upcoming?: boolean | null;
    assignedTo?: string; // "mine" (current user), "unassigned", or "everyone" (default)
    sortBy?: string;
    sortDir?: "asc" | "desc";
}

Filtering & Parameters:

• Upcoming Filter (upcoming = true): Restricts results to appointments where start_time >= NOW().
• Past Filter (upcoming = false): Restricts results to appointments where end_time < NOW().
• Any Filter (upcoming = null): Bypasses time bounds, returning all appointments.
• Assignee Filter (assignedTo):
  • "mine": Filters using an EXISTS subquery to return only appointments where the current user ID is listed in the appointment_internal_attendees junction table.
  • "unassigned": Filters using a NOT EXISTS subquery to return only appointments with zero internal attendees.
  • "everyone" (default): Returns all appointments in the workspace.
• sortBy: Specifies the column to sort by. Supported values:
  • "start_time" (default): Sorts chronologically by start_time.
  • "type": Sorts alphabetically by appointment type.
• sortDir: The sort direction ("asc" or "desc").
• Search: Matches the search term against the appointment title or external attendee first/last names using an ILIKE query pattern.

Future: Third-Party Integrations

The appointments schema is future-proofed for third-party scheduling integrations (like Calendly, Acuity, etc.) using an adapter pattern.

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.

Performance & Hydration Optimization

Server-Side Pre-fetching & Lazy Loading

• Server Component (pages/appointments/page.tsx): Fetches getAppointments and getWorkspaceMembers in parallel on the server during request routing.
• Client Component (appointments-client.tsx): Manages appointment calendars, status tab controls, and filters.
• Dynamic imports: Lazily imports the heavy AppointmentFormDialog component on demand with ssr: false.
• Eager Mount Mitigation: Form dialogs and warning dialogs are conditionally rendered to keep closed DOM nodes clean.
• Gates: Mount fetch protection (isFirstQueryRef) and workspace transition state sync hooks are integrated.

Security

RLS Policies

Appointments use standard workspace-scoped Row Level Security with no role restrictions:

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).