UseCase09 - Doctor Records Procedure Outcome

Initiating Actor - Doctor

Description

After performing a procedure, a doctor records the outcome. The system stores the procedure result and updates the performed procedure notes. The system also ensures the procedure catalog entry is linked to the patient's medical record, so the result becomes visible there.

Scenario

1. Doctor navigates to pending procedures for a patient.

SELECT
  pp.performed_id,
  pr.procedure_type,
  pr.description,
  pp.procedure_date,
  pp.notes
FROM performed_procedures pp
JOIN procedures pr ON pp.procedure_id = pr.procedure_id
WHERE pp.patient_id = (
    SELECT p.patient_id
    FROM patients p
    JOIN users u ON u.user_id = p.user_id
    WHERE u.username = '1402994123456')
  AND pp.doctor_id = (
    SELECT d.doctor_id
    FROM doctors d
    JOIN users u ON u.user_id = d.user_id
    WHERE u.username = 'elena.kirova@medora.com');

2. Doctor selects a performed procedure to record the outcome for.

SELECT
  pp.performed_id,
  pr.procedure_type,
  pr.description,
  u_p.first_name AS patient_first_name,
  u_p.last_name AS patient_last_name,
  pp.procedure_date,
  pp.notes
FROM performed_procedures pp
JOIN procedures pr ON pp.procedure_id = pr.procedure_id
JOIN patients p ON pp.patient_id = p.patient_id
JOIN users u_p ON u_p.user_id = p.user_id
WHERE pp.performed_id = (
    SELECT MAX(performed_id)
    FROM performed_procedures
    WHERE doctor_id = (
        SELECT d.doctor_id
        FROM doctors d
        JOIN users u ON u.user_id = d.user_id
        WHERE u.username = 'elena.kirova@medora.com'));

3. Doctor enters the outcome notes and submits.

UPDATE performed_procedures
SET notes = 'ECG completed successfully. Heart rhythm normal, no arrhythmias detected.'
WHERE performed_id = (
    SELECT MAX(performed_id)
    FROM performed_procedures
    WHERE doctor_id = (
        SELECT d.doctor_id
        FROM doctors d
        JOIN users u ON u.user_id = d.user_id
        WHERE u.username = 'elena.kirova@medora.com'));

4. System stores the detailed procedure result.

INSERT INTO procedure_results (result_id, procedure_id, result_description, result_date)
VALUES (
  (SELECT COALESCE(MAX(result_id), 0) + 1 FROM procedure_results),
  (SELECT procedure_id FROM procedures WHERE procedure_type = 'ECG'),
  'Normal sinus rhythm. Heart rate 72 bpm. No ST elevation.',
  '2026-06-10'
)
RETURNING result_id;

5. System links the procedure catalog entry to the patient's medical record, if not already linked, so the result becomes visible there.

INSERT INTO medical_record_procedures (record_id, procedure_id)
VALUES (
  (SELECT mr.record_id
   FROM medical_records mr
   JOIN patients p ON mr.patient_id = p.patient_id
   JOIN users u ON u.user_id = p.user_id
   WHERE u.username = '1402994123456'),
  (SELECT procedure_id FROM procedures WHERE procedure_type = 'ECG')
)
ON CONFLICT (record_id, procedure_id) DO NOTHING;

6. System confirms and displays all procedure results on the patient's medical record.

SELECT
  p.procedure_type,
  pres.result_description,
  pres.result_date
FROM medical_record_procedures mrp
JOIN procedures p ON mrp.procedure_id = p.procedure_id
JOIN procedure_results pres ON pres.procedure_id = p.procedure_id
WHERE mrp.record_id = (
    SELECT mr.record_id
    FROM medical_records mr
    JOIN patients p ON mr.patient_id = p.patient_id
    JOIN users u ON u.user_id = p.user_id
    WHERE u.username = '1402994123456'
);