Scheduling Service — Data Model

Status: populated Owner: TBD Last updated: 2026-04-17 Companion: Service Template · NAMING

1. ID Prefix Registry

Prefix	Entity
apt_	Appointment
slt_	Slot
sch_	Schedule
wl_	WaitlistEntry
exc_	ScheduleException

2. TypeScript Interfaces

export type AppointmentStatus =
  | 'proposed' | 'pending' | 'booked'
  | 'arrived' | 'fulfilled' | 'cancelled' | 'noshow' | 'entered-in-error';

export type SlotStatus = 'free' | 'busy' | 'blocked' | 'entered-in-error';

export interface AppointmentAggregate {
  id: string;
  tenantId: string;
  patientId: string;
  slotId?: string;
  scheduleId?: string;
  appointmentType?: string;
  status: AppointmentStatus;
  startAt: string;         // ISO-8601 UTC
  endAt: string;
  reason?: string;
  cancelReason?: string;
  allowDoubleBooking: boolean;
  source: 'staff' | 'portal' | 'hl7';
  providerId?: string;
  locationId?: string;
  createdBy: string;
  version: number;
  createdAt: string;
  updatedAt: string;
}

export interface ScheduleAggregate {
  id: string;
  tenantId: string;
  actorId: string;
  actorType: 'provider' | 'location' | 'device';
  timezone: string;        // IANA
  availabilityPattern?: AvailabilityPattern;
  serviceTypes?: string[];
  isActive: boolean;
  version: number;
  createdAt: string;
  updatedAt: string;
}

export interface SlotAggregate {
  id: string;
  tenantId: string;
  scheduleId: string;
  status: SlotStatus;
  startAt: string;
  endAt: string;
  appointmentType?: string;
  version: number;
  createdAt: string;
  updatedAt: string;
}

export interface WaitlistEntry {
  id: string;
  tenantId: string;
  patientId: string;
  appointmentType: string;
  scheduleId?: string;
  criteria?: Record<string, unknown>;
  priority: number;
  note?: string;
  status: 'waiting' | 'fulfilled' | 'deleted';
  createdBy: string;
  createdAt: string;
  updatedAt: string;
}

3. PostgreSQL Schema

-- Schedules
CREATE TABLE schedules (
  id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id             UUID NOT NULL,
  actor_id              UUID NOT NULL,
  actor_type            TEXT NOT NULL,
  timezone              TEXT NOT NULL DEFAULT 'UTC',
  availability_pattern  JSONB,
  service_types         TEXT[],
  is_active             BOOLEAN NOT NULL DEFAULT TRUE,
  version               INTEGER NOT NULL DEFAULT 1,
  created_at            TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at            TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX ix_schedules_tenant_actor ON schedules (tenant_id, actor_id);

-- Schedule exceptions (blocked windows)
CREATE TABLE schedule_exceptions (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  schedule_id UUID NOT NULL REFERENCES schedules(id),
  tenant_id   UUID NOT NULL,
  start_at    TIMESTAMPTZ NOT NULL,
  end_at      TIMESTAMPTZ NOT NULL,
  reason      TEXT,
  type        TEXT NOT NULL DEFAULT 'holiday'
);
CREATE INDEX ix_sched_exc_schedule ON schedule_exceptions (schedule_id, start_at);

-- Slots
CREATE TABLE slots (
  id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id        UUID NOT NULL,
  schedule_id      UUID NOT NULL REFERENCES schedules(id),
  status           TEXT NOT NULL DEFAULT 'free',
  start_at         TIMESTAMPTZ NOT NULL,
  end_at           TIMESTAMPTZ NOT NULL,
  appointment_type TEXT,
  version          INTEGER NOT NULL DEFAULT 1,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at       TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX ix_slots_schedule_status ON slots (schedule_id, status, start_at);
-- Prevent overlapping free/busy slots in same schedule
CREATE UNIQUE INDEX ix_slots_schedule_start ON slots (schedule_id, start_at)
  WHERE status IN ('free', 'busy');

-- Appointments
CREATE TABLE appointments (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id           UUID NOT NULL,
  patient_id          UUID NOT NULL,
  slot_id             UUID REFERENCES slots(id),
  schedule_id         UUID,
  appointment_type    TEXT,
  status              TEXT NOT NULL DEFAULT 'booked',
  start_at            TIMESTAMPTZ,
  end_at              TIMESTAMPTZ,
  reason              TEXT,
  cancel_reason       TEXT,
  allow_double_booking BOOLEAN NOT NULL DEFAULT FALSE,
  source              TEXT NOT NULL DEFAULT 'staff',
  provider_id         UUID,
  location_id         UUID,
  created_by          UUID NOT NULL,
  version             INTEGER NOT NULL DEFAULT 1,
  created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX ix_appointments_tenant_patient ON appointments (tenant_id, patient_id, status);
CREATE INDEX ix_appointments_tenant_slot ON appointments (tenant_id, slot_id);
CREATE INDEX ix_appointments_calendar ON appointments (tenant_id, provider_id, start_at);

-- Waitlist
CREATE TABLE waitlist_entries (
  id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id        UUID NOT NULL,
  patient_id       UUID NOT NULL,
  appointment_type TEXT NOT NULL,
  schedule_id      UUID,
  criteria         JSONB,
  priority         INTEGER NOT NULL DEFAULT 0,
  note             TEXT,
  status           TEXT NOT NULL DEFAULT 'waiting',
  created_by       UUID NOT NULL,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at       TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX ix_waitlist_tenant_status ON waitlist_entries (tenant_id, status, priority);

-- Outbox
CREATE TABLE outbox (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id    UUID NOT NULL,
  aggregate_id UUID NOT NULL,
  event_type   TEXT NOT NULL,
  payload      JSONB NOT NULL,
  published    BOOLEAN NOT NULL DEFAULT FALSE,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX ix_outbox_unpublished ON outbox (created_at) WHERE published = FALSE;

4. Row-Level Security

ALTER TABLE appointments ENABLE ROW LEVEL SECURITY;
CREATE POLICY appointments_tenant_isolation ON appointments
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- Same policy applied to: schedules, slots, waitlist_entries, schedule_exceptions, outbox