Definitions / authorisation_expiring
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