RoundsCorrowmere Clinics · Estmark synthetic data · seed 23

Authorisation running out before its appointment

A prior authorisation or repeat prescription that runs out (or has run out) before a booked appointment that needs it, with no renewal asked for. The rule: ask for the renewal 14 days before the expiry.

Unit: authorisations · parameters: warn days 14 · also said as: expiring authorisation, prior authorisation expiring, prescription expiring, authorisation expiry, renewal needed, expiring prescription

Booking

Head of booking (booking) · version 1, changed 2026-11-02

Expires within warn_days from now (or expired in the last 30 days), no renewal requested by now, and the patient has a live booking after the expiry: for a prior authorisation, a procedure with the same code; for a repeat prescription, an appointment with the prescriber's specialty.

First version.

WITH due AS (
  SELECT a.* FROM authorisations a
  WHERE a.valid_from <= {now}
    AND (a.renewal_requested_at IS NULL OR a.renewal_requested_at > {now})
    AND a.expires_at <= {now} + INTERVAL ({warn_days}) DAY
    AND a.expires_at >= {now} - INTERVAL 30 DAY
),
need AS (
  SELECT d.id AS authorisation_id, min(ap.start_at) AS appointment_at, arg_min(ap.id, ap.start_at) AS appointment_id
  FROM due d
  JOIN clinicians pk ON pk.id = d.clinician_id
  JOIN appointments ap ON ap.patient_id = d.patient_id AND ap.start_at > d.expires_at AND ap.start_at > {now}
    AND ap.booked_at <= {now} AND (ap.cancelled_at IS NULL OR ap.cancelled_at > {now})
    AND ((d.procedure_code IS NOT NULL AND ap.procedure_code = d.procedure_code)
         OR (d.procedure_code IS NULL AND ap.specialty_id = pk.specialty_id))
  GROUP BY d.id
)
SELECT 'authorisation' AS subject_type, d.id AS subject_id, d.id AS authorisation_id, d.patient_id,
       d.clinic_id, d.clinician_id, n.appointment_id,
       d.expires_at - to_days({warn_days}) AS started_at,
       CASE WHEN d.expires_at <= {now} THEN 'high' ELSE 'medium' END AS severity,
       json_object(
         'kind', d.kind, 'item', d.item, 'expires_at', d.expires_at, 'appointment_at', n.appointment_at,
         'days_to_expiry', round(date_diff('hour', {now}, d.expires_at) / 24.0, 1),
         'already_expired', d.expires_at <= {now}, 'prescriber', k.name, 'clinic', c.name
       ) AS evidence
FROM due d
JOIN need n ON n.authorisation_id = d.id
JOIN clinics c ON c.id = d.clinic_id
LEFT JOIN clinicians k ON k.id = d.clinician_id
ORDER BY started_at, d.id

Tests: count_between, unique