RoundsCorrowmere Clinics · Estmark synthetic data · seed 23

Consent missing before a procedure

A procedure in the next 72 hours with no signed consent on file. The 72-hour rule applies to procedures booked at least 72 hours ahead; consent for a shorter-notice procedure is taken on the day.

Unit: appointments · parameters: hours before 72 · also said as: consent missing, missing consent, no consent, unsigned consent, consent form

Clinical

Clinical governance lead (clinical governance) · version 2, changed 2026-11-09

Live procedure starting within hours_before, booked at least hours_before ahead, and no consent signed by now for the patient and procedure: either a consent for this appointment, or a course consent still valid on the day.

A course consent (injections, infusions, cryotherapy) counts for every procedure in the course while it is valid; short-notice procedures are left to on-the-day consent.

SELECT 'appointment' AS subject_type, a.id AS subject_id, a.id AS appointment_id, a.patient_id, a.clinic_id,
       a.clinician_id, a.start_at - to_hours({hours_before}) AS started_at,
       CASE WHEN a.start_at <= {now} + INTERVAL 24 HOUR THEN 'high' ELSE 'medium' END AS severity,
       json_object(
         'procedure_code', a.procedure_code, 'procedure', pr.name, 'start_at', a.start_at,
         'booked_at', a.booked_at, 'hours_to_procedure', round(date_diff('minute', {now}, a.start_at) / 60.0, 1),
         'course', pr.course, 'clinic', c.name, 'consent_requested_at', (
           SELECT max(m.sent_at) FROM messages m WHERE m.appointment_id = a.id AND m.kind = 'consent_request'
             AND m.sent_at <= {now})
       ) AS evidence
FROM appointments a
JOIN procedures pr ON pr.code = a.procedure_code
JOIN clinics c ON c.id = a.clinic_id
WHERE a.kind = 'procedure' AND a.start_at > {now} AND a.start_at <= {now} + to_hours({hours_before})
  AND a.start_at - a.booked_at >= to_hours({hours_before})
  AND a.booked_at <= {now} AND (a.cancelled_at IS NULL OR a.cancelled_at > {now})
  AND NOT EXISTS (
    SELECT 1 FROM consents k WHERE k.patient_id = a.patient_id AND k.procedure_code = a.procedure_code
      AND k.signed_at <= {now}
      AND (k.appointment_id = a.id OR (k.scope = 'course' AND k.valid_until >= a.start_at)))
ORDER BY started_at, a.id

Tests: count_between, unique

History: v1 2026-11-02 (Clinical governance lead): First version: a consent had to name the appointment.