Definition

The Current Appointment data model captures the latest appointment slot state for OpenSAFELY analysis. It includes slot and patient pseudo-identifiers, slot timing, appointment lifecycle timestamps, and current slot status information.

Constraints and Notes

• Primary key usage: slot_id is used with organisation as a composite key in the serving layer and is not globally unique without organisation context.
• Pseudonymisation: slot_id and patient_id are generated using pseudo-identifier logic in the warehouse layer.
• Soft deletion: when is_deleted is true, data fields may be nullified in upstream transformations and should be excluded from most analytical queries.

Examples

Active booked appointments in the next 7 days

SELECT
  slot_id,
  patient_id,
  slot_start_datetime,
  slot_end_datetime,
  current_status_description,
  slot_type_description,
  organisation
FROM explorer_open_safely.current_appointment
WHERE
  NOT is_deleted
  AND slot_start_datetime >= CURRENT_TIMESTAMP
  AND slot_start_datetime < CURRENT_TIMESTAMP + INTERVAL '7' DAY
  AND current_status_description = 'Booked';

Appointments by current status

SELECT
  current_status_type_description,
  current_status_description,
  COUNT(*) AS appointment_count
FROM explorer_open_safely.current_appointment
WHERE NOT is_deleted
GROUP BY
  current_status_type_description,
  current_status_description
ORDER BY appointment_count DESC;