UseCase07 - Lab Technician Submits Lab Results

Initiating Actor - Lab Technician

Description

A lab technician views pending lab tests, performs the test, and submits the results. The system records the results, links them to the patient's medical record, and makes them available to the doctor.

Scenario

1. Lab technician logs in and navigates to "Pending Lab Tests".

SELECT
  plt.performed_test_id,
  lt.test_name,
  u_p.first_name AS patient_first_name,
  u_p.last_name AS patient_last_name,
  u_d.first_name AS doctor_first_name,
  u_d.last_name AS doctor_last_name,
  plt.test_date,
  plt.notes
FROM performed_lab_tests plt
JOIN lab_tests lt ON plt.test_id = lt.test_id
JOIN patients p ON plt.patient_id = p.patient_id
JOIN users u_p ON p.patient_id = u_p.patient_id
JOIN doctors d ON plt.doctor_id = d.doctor_id
JOIN users u_d ON d.doctor_id = u_d.doctor_id;

2. Technician selects a pending test and views its details.

SELECT
  lt.test_id,
  lt.test_name,
  lt.description,
  lt.cost
FROM lab_tests lt
WHERE lt.test_id = (
SELECT test_id 
FROM performed_lab_tests 
WHERE performed_test_id = 1);

3. Technician performs the test and submits the results.

INSERT INTO lab_results (test_id, results, result_date)
VALUES (
  (SELECT test_id FROM performed_lab_tests WHERE performed_test_id = 1),
  'Blood pressure: 145/90, Glucose: 105 mg/dL',
  '2026-06-05'
)
RETURNING result_id;

4. System links the lab result to the patient's medical record.

INSERT INTO medical_record_lab_results (record_id, result_id)
VALUES (
  (SELECT record_id FROM medical_records WHERE patient_id = (
    SELECT patient_id FROM performed_lab_tests WHERE performed_test_id = 1
  )),
  (SELECT MAX(result_id) FROM lab_results)
);

5. System confirms submission and displays the results now on record.

SELECT
  lt.test_name,
  lr.results,
  lr.result_date
FROM medical_record_lab_results mrlr
JOIN lab_results lr ON mrlr.result_id = lr.result_id
JOIN lab_tests lt ON lr.test_id = lt.test_id
WHERE mrlr.record_id = (
  SELECT record_id FROM medical_records
  WHERE patient_id = (
SELECT patient_id 
FROM performed_lab_tests 
WHERE performed_test_id = 1)
);