Changes between Version 2 and Version 3 of AdvancedDatabaseDevelopment

Timestamp:

09/23/26 20:22:29 (8 hours ago)

Author:

236021

Comment:

--

AdvancedDatabaseDevelopment

@@ -6 +6 @@
 == Allergy–prescription safety enforcement ==
 
-A patient should never be prescribed a medication that conflicts with one of their documented allergies. This requirement blocks the unsafe insert outright, at the database level before it can happen: a new **prescription_medical_records** row is rejected if the prescription is linked, via **allergy_prescription_restrictions** → **prescription_restriction** to any allergy already on that patient's medical record.
+A patient should never be prescribed a medication that conflicts with one of their documented allergies. This requirement blocks the unsafe prescription outright, at the database level: a new or changed **prescription_medical_records** row is rejected if the prescription is linked, via **prescription_restriction** → **allergy_prescription_restrictions**, to an allergy the patient has on file, either in **patient_allergies** or in **medical_record_allergies** on any of the patient's medical records. The trigger only prevents new conflicts; prescriptions that were recorded before it existed are listed by the **v_prescription_allergy_conflicts** view so they can be reviewed.
 
 === Custom domains ===
 
 {{{
-CREATE DOMAIN non_negative_currency AS DECIMAL(12,2)
+CREATE DOMAIN non_negative_cost AS DECIMAL(12,2)
     CHECK (VALUE >= 0);
+
+ALTER TABLE procedures ALTER COLUMN cost TYPE non_negative_cost USING cost::non_negative_cost;
+ALTER TABLE lab_tests ALTER COLUMN cost TYPE non_negative_cost USING cost::non_negative_cost;
+ALTER TABLE billing ALTER COLUMN total_cost TYPE non_negative_cost USING total_cost::non_negative_cost;
+
+ALTER TABLE procedures DROP CONSTRAINT IF EXISTS procedures_cost_chk;
+ALTER TABLE lab_tests DROP CONSTRAINT IF EXISTS lab_tests_cost_chk;
+ALTER TABLE billing DROP CONSTRAINT IF EXISTS billing_cost_chk;
 }}}
 
@@ -18 +26 @@
 
 {{{
-CREATE TABLE IF NOT EXISTS prescription_allergy_conflicts_log (
-    log_id BIGSERIAL PRIMARY KEY,
-    record_id BIGINT NOT NULL REFERENCES medical_records(record_id),
-    prescription_id BIGINT NOT NULL REFERENCES prescriptions(prescription_id),
-    allergy_id BIGINT NOT NULL REFERENCES allergies(allergy_id),
-    conflict_type TEXT CHECK (conflict_type IN ('ACTIVE', 'RESOLVED')),
-    detected_date TIMESTAMP DEFAULT NOW(),
-    resolution_notes TEXT,
-    resolved_date TIMESTAMP
-);
-
 CREATE OR REPLACE FUNCTION t1_prescription_allergy_check()
 RETURNS TRIGGER
@@ -46 +43 @@
     END IF;
 
-    SELECT apr.allergy_id, a.name
+    SELECT a.allergy_id, a.name
     INTO v_conflict_allergy_id, v_allergy_name
     FROM prescription_restriction pr_rest
     JOIN allergy_prescription_restrictions apr ON apr.restriction_id = pr_rest.restriction_id
-    JOIN allergies a ON apr.allergy_id = a.allergy_id
-    JOIN medical_record_allergies mra ON a.allergy_id = mra.allergy_id
+    JOIN allergies a ON a.allergy_id = apr.allergy_id
     WHERE pr_rest.prescription_id = NEW.prescription_id
-      AND mra.record_id = NEW.record_id
+      AND (
+            EXISTS (SELECT 1 FROM patient_allergies pa
+                    WHERE pa.patient_id = v_patient_id
+                      AND pa.allergy_id = a.allergy_id)
+         OR EXISTS (SELECT 1 FROM medical_record_allergies mra
+                    JOIN medical_records mr2 ON mr2.record_id = mra.record_id
+                    WHERE mr2.patient_id = v_patient_id
+                      AND mra.allergy_id = a.allergy_id)
+          )
     LIMIT 1;
 
     IF v_conflict_allergy_id IS NOT NULL THEN
-        RAISE EXCEPTION 'PRESCRIPTION_ALLERGY_CONFLICT: Prescription % conflicts with allergy % (%) in patient''s record %',
-            NEW.prescription_id, v_conflict_allergy_id, v_allergy_name, NEW.record_id;
+        RAISE EXCEPTION 'PRESCRIPTION_ALLERGY_CONFLICT: Prescription % conflicts with allergy % (%) of patient %',
+            NEW.prescription_id, v_conflict_allergy_id, v_allergy_name, v_patient_id;
     END IF;
 
@@ -67 +71 @@
 DROP TRIGGER IF EXISTS trg_prescription_allergy_check ON prescription_medical_records;
 CREATE TRIGGER trg_prescription_allergy_check
-    BEFORE INSERT
+    BEFORE INSERT OR UPDATE OF prescription_id, record_id
     ON prescription_medical_records
     FOR EACH ROW
@@ -88 +92 @@
     a.allergy_severity,
     apr.restriction_id,
-    pr_rest.description AS restriction_description,
-    CASE
-        WHEN mra.record_id IS NOT NULL THEN 'ACTIVE_CONFLICT'
-        ELSE 'ARCHIVED'
-    END AS conflict_status
+    pr_rest.description AS restriction_description
 FROM prescription_medical_records pmr
 JOIN medical_records mr ON pmr.record_id = mr.record_id
@@ -100 +100 @@
 JOIN allergy_prescription_restrictions apr ON pr_rest.restriction_id = apr.restriction_id
 JOIN allergies a ON apr.allergy_id = a.allergy_id
-LEFT JOIN medical_record_allergies mra ON mr.record_id = mra.record_id
-    AND a.allergy_id = mra.allergy_id
-WHERE a.allergy_severity IN ('HIGH', 'CRITICAL')
-ORDER BY p.patient_id, a.allergy_severity DESC;
+WHERE EXISTS (SELECT 1 FROM patient_allergies pa
+              WHERE pa.patient_id = p.patient_id
+                AND pa.allergy_id = a.allergy_id)
+   OR EXISTS (SELECT 1 FROM medical_record_allergies mra
+              JOIN medical_records mr2 ON mr2.record_id = mra.record_id
+              WHERE mr2.patient_id = p.patient_id
+                AND mra.allergy_id = a.allergy_id)
+ORDER BY
+    p.patient_id,
+    CASE a.allergy_severity
+        WHEN 'CRITICAL' THEN 1
+        WHEN 'HIGH' THEN 2
+        WHEN 'MEDIUM' THEN 3
+        ELSE 4
+    END;
 }}}
 
@@ -110 +121 @@
 == Appointment scheduling integrity ==
 
-Appointments must obey real scheduling constraints so no scheduling in the past, no doublebooking the same doctor or patient in an overlapping time, no marking an appointment `COMPLETED` before its scheduled time, and valid status transitions only (`SCHEDULED → IN_PROGRESS/COMPLETED/CANCELLED`, `IN_PROGRESS → COMPLETED/CANCELLED`, nothing further out of `COMPLETED`/`CANCELLED`/`NO_SHOW`). A background job autotransitions appointments that remain `SCHEDULED` if they are 45+ minutes past their time to `NO_SHOW`.
-
-=== Schema change  ===
-
-{{{
-
+Appointments must obey real scheduling constraints: no scheduling in the past (when an appointment is created or moved to a new date or time), no double-booking the same doctor or patient in an overlapping time, no marking an appointment `COMPLETED` before its scheduled time, and valid status transitions only (`SCHEDULED → IN_PROGRESS/COMPLETED/CANCELLED/NO_SHOW`, `IN_PROGRESS → COMPLETED/CANCELLED`, nothing further out of `COMPLETED`/`CANCELLED`/`NO_SHOW`). Each appointment is treated as a 30-minute slot, and only `SCHEDULED`, `IN_PROGRESS` and `COMPLETED` appointments occupy a slot. The **job_mark_no_show** procedure moves appointments that are still `SCHEDULED` 45+ minutes past their time to `NO_SHOW`; it is run with `CALL job_mark_no_show();`, either manually or from a scheduler such as pg_cron, and **v_overdue_appointments** shows the appointments it will pick up.
+
+=== Schema change ===
+
+{{{
 ALTER TABLE appointments DROP CONSTRAINT appointments_status_chk;
 ALTER TABLE appointments ADD CONSTRAINT appointments_status_chk
-    CHECK (status IN ('SCHEDULED','COMPLETED','CANCELLED','IN_PROGRESS','NO_SHOW'));
+    CHECK (status IN ('SCHEDULED', 'COMPLETED', 'CANCELLED', 'IN_PROGRESS', 'NO_SHOW'));
 }}}
 
@@ -131 +141 @@
 SELECT CASE
            WHEN p_old = p_new THEN TRUE
-           WHEN p_old = 'SCHEDULED'    AND p_new IN ('IN_PROGRESS', 'COMPLETED', 'CANCELLED') THEN TRUE
-           WHEN p_old = 'IN_PROGRESS'  AND p_new IN ('COMPLETED', 'CANCELLED') THEN TRUE
+           WHEN p_old = 'SCHEDULED'   AND p_new IN ('IN_PROGRESS', 'COMPLETED', 'CANCELLED', 'NO_SHOW') THEN TRUE
+           WHEN p_old = 'IN_PROGRESS' AND p_new IN ('COMPLETED', 'CANCELLED') THEN TRUE
            ELSE FALSE
-           END;
+       END;
 $$;
 
@@ -152 +162 @@
 
     IF TG_OP = 'UPDATE' THEN
+        IF (NEW.appointment_date, NEW.appointment_time) IS DISTINCT FROM (OLD.appointment_date, OLD.appointment_time)
+           AND v_combined_datetime < NOW() THEN
+            RAISE EXCEPTION 'Cannot move appointment % into the past (appointment_date=%, appointment_time=%)',
+                NEW.appointment_id, NEW.appointment_date, NEW.appointment_time;
+        END IF;
+
         IF NOT is_valid_appointment_transition(OLD.status, NEW.status) THEN
             RAISE EXCEPTION 'Appointment status cannot transition from % to %',
                 OLD.status, NEW.status;
         END IF;
-
-        IF NEW.status = 'COMPLETED' AND v_combined_datetime > NOW() THEN
-            RAISE EXCEPTION 'Cannot mark appointment COMPLETED before its scheduled time (scheduled for %)',
-                v_combined_datetime;
-        END IF;
+    END IF;
+
+    IF NEW.status = 'COMPLETED' AND v_combined_datetime > NOW() THEN
+        RAISE EXCEPTION 'Cannot mark appointment COMPLETED before its scheduled time (scheduled for %)',
+            v_combined_datetime;
     END IF;
 
@@ -197 +213 @@
           AND (TG_OP <> 'UPDATE' OR a.appointment_id <> NEW.appointment_id)
     ) THEN
-        RAISE EXCEPTION 'Doctor % has overlapping appointment at %', NEW.doctor_id, NEW.appointment_date;
+        RAISE EXCEPTION 'Doctor % has an overlapping appointment at % %',
+            NEW.doctor_id, NEW.appointment_date, NEW.appointment_time;
     END IF;
 
@@ -208 +225 @@
           AND (TG_OP <> 'UPDATE' OR a.appointment_id <> NEW.appointment_id)
     ) THEN
-        RAISE EXCEPTION 'Patient % has overlapping appointment at %', NEW.patient_id, NEW.appointment_date;
+        RAISE EXCEPTION 'Patient % has an overlapping appointment at % %',
+            NEW.patient_id, NEW.appointment_date, NEW.appointment_time;
     END IF;
 
@@ -259 +277 @@
 == Medical record consistency ==
 
-**Diagnoses, procedures, and referrals** can be created independently but must stay consistent with the patient they actually serve as a mismatch like a diagnosis for one patient attached to a different patient's record, a doctor referring a patient to themselves, is both an **integrity problem** and a clinical **safety risk**. 
+**Diagnoses, procedures, and referrals** can be created independently but must stay consistent with the patient they actually serve. Linking a diagnosis to a different patient's medical record, linking a performed procedure to a diagnosis that belongs to another patient, or a doctor referring a patient to themselves is both an **integrity problem** and a clinical **safety risk**, so these are rejected on insert and on update. The **v_medical_record_overview** view summarises each medical record and flags diagnosis mismatches and self-referrals that were recorded before the triggers existed.
 
 === Triggers ===
@@ -294 +312 @@
 DROP TRIGGER IF EXISTS trg_diagnosis_record_consistency ON diagnosis_medical_records;
 CREATE TRIGGER trg_diagnosis_record_consistency
-    BEFORE INSERT ON diagnosis_medical_records
+    BEFORE INSERT OR UPDATE ON diagnosis_medical_records
     FOR EACH ROW EXECUTE FUNCTION t1_diagnosis_record_consistency();
 
@@ -331 +349 @@
 AS $$
 BEGIN
-    IF NOT EXISTS (SELECT 1 FROM medical_records WHERE record_id = NEW.record_id) THEN
-        RAISE EXCEPTION 'Medical record % not found', NEW.record_id;
-    END IF;
-
     IF NEW.from_doctor_id = NEW.to_doctor_id THEN
-        RAISE EXCEPTION 'Doctor % cannot refer to themselves', NEW.from_doctor_id;
+        RAISE EXCEPTION 'Doctor % cannot refer a patient to themselves', NEW.from_doctor_id;
     END IF;
 
@@ -345 +359 @@
 DROP TRIGGER IF EXISTS trg_referral_consistency ON referrals;
 CREATE TRIGGER trg_referral_consistency
-    BEFORE INSERT ON referrals
+    BEFORE INSERT OR UPDATE ON referrals
     FOR EACH ROW EXECUTE FUNCTION t3_referral_consistency();
 }}}
@@ -357 +371 @@
     p.patient_id, p.first_name, p.last_name, p.embg,
     COUNT(DISTINCT dmr.diagnosis_id) AS diagnosis_count,
-    COUNT(DISTINCT CASE WHEN d.patient_id IS NOT NULL AND d.patient_id <> p.patient_id THEN dmr.diagnosis_id END) AS diagnosis_mismatches,
+    COUNT(DISTINCT CASE WHEN d.patient_id <> p.patient_id THEN dmr.diagnosis_id END) AS diagnosis_mismatches,
     COUNT(DISTINCT mrp.procedure_id) AS procedures_count,
     COUNT(DISTINCT mrl.result_id) AS lab_results_count,
@@ -369 +383 @@
     LEFT JOIN medical_record_procedures mrp ON mrp.record_id = mr.record_id
     LEFT JOIN medical_record_lab_results mrl ON mrl.record_id = mr.record_id
-    LEFT JOIN referrals ref ON mr.record_id = ref.record_id
-    LEFT JOIN medical_record_allergies mra ON mr.record_id = mra.record_id
+    LEFT JOIN referrals ref ON ref.record_id = mr.record_id
+    LEFT JOIN medical_record_allergies mra ON mra.record_id = mr.record_id
 GROUP BY mr.record_id, p.patient_id, p.first_name, p.last_name, p.embg;
 }}}
@@ -378 +392 @@
 == Billing integrity ==
 
-Billing records must stay in sync with their line items and payment status must follow a valid state machine. **billing.total_cost** is automatically recalculated whenever a procedure or lab test is added to or removed from a bill, rather than relying on the application to keep it in sync manually. **payment_status** may only move `PENDING → PAID` or `PENDING → CANCELLED` but never backwards once finalized and every status change is written to an audit log.  Since **billing** originally had no "issued" date , a **created_at** column was added so outstanding `PENDING` bills can be tracked for follow-up.
+Billing records must stay in sync with their line items, and payment status must follow a valid state machine. billing.total_cost is recalculated automatically whenever a procedure or lab test is added to or removed from a bill, as the sum of the list prices (`cost`) of its line items, rather than relying on the application to keep it in sync. Line items can only be changed while the bill is `PENDING`, so a `PAID` or `CANCELLED` bill can no longer change its amount. payment_status may only move `PENDING → PAID` or `PENDING → CANCELLED`, never backwards once finalized; when a bill becomes `PAID` without a payment_date, the current date is filled in. Every billing status change and every change to the calculated total caused by adding or removing a billing line item is recorded in billing_audit_log. The existing created_at column is used to track outstanding `PENDING` bills: **v_overdue_billings** lists bills that have been pending for more than 30 days (`CRITICAL` after 60 days), and job_billing_alerts reports how many need follow-up.
 
 === Schema change ===
 
 {{{
-
-ALTER TABLE billing ADD COLUMN IF NOT EXISTS created_at TIMESTAMP DEFAULT NOW();
-
 CREATE TABLE IF NOT EXISTS billing_audit_log (
     audit_id BIGSERIAL PRIMARY KEY,
@@ -401 +412 @@
 
 {{{
-
-
 CREATE OR REPLACE FUNCTION recalculate_billing_total(p_bill_id BIGINT, p_change_type TEXT)
 RETURNS VOID
@@ -408 +417 @@
 AS $$
 DECLARE
+    v_old_total DECIMAL;
+    v_status TEXT;
     v_procedure_total DECIMAL;
     v_lab_total DECIMAL;
     v_new_total DECIMAL;
 BEGIN
+    SELECT total_cost, payment_status
+    INTO v_old_total, v_status
+    FROM billing
+    WHERE bill_id = p_bill_id;
+
+    IF v_status IS NULL THEN
+        RAISE EXCEPTION 'Bill % not found', p_bill_id;
+    END IF;
+
+    IF v_status <> 'PENDING' THEN
+        RAISE EXCEPTION 'Bill % is % and its line items can no longer be changed', p_bill_id, v_status;
+    END IF;
+
     SELECT COALESCE(SUM(p.cost), 0) INTO v_procedure_total
     FROM billing_procedures bp
@@ -426 +450 @@
     UPDATE billing SET total_cost = v_new_total WHERE bill_id = p_bill_id;
 
-    INSERT INTO billing_audit_log (bill_id, new_amount, change_type)
-    VALUES (p_bill_id, v_new_total, p_change_type);
+    INSERT INTO billing_audit_log (bill_id, old_amount, new_amount, change_type)
+    VALUES (p_bill_id, v_old_total, v_new_total, p_change_type);
 END;
 $$;
@@ -440 +464 @@
            WHEN p_old = 'PENDING' AND p_new IN ('PAID', 'CANCELLED') THEN TRUE
            ELSE FALSE
-           END;
+       END;
 $$;
 
@@ -449 +473 @@
     v_overdue_count INT;
 BEGIN
-    SELECT COUNT(*) INTO v_overdue_count FROM v_overdue_billings WHERE days_outstanding > 30;
+    SELECT COUNT(*) INTO v_overdue_count FROM v_overdue_billings;
     RAISE NOTICE 'Found % overdue billing records requiring follow-up', v_overdue_count;
 END;
@@ -458 +482 @@
 
 {{{
-
 CREATE OR REPLACE FUNCTION t1_billing_line_item_changed()
 RETURNS TRIGGER
@@ -494 +517 @@
         RAISE EXCEPTION 'Cannot transition billing status from % to %',
             OLD.payment_status, NEW.payment_status;
+    END IF;
+
+    IF NEW.payment_status = 'PAID' AND NEW.payment_date IS NULL THEN
+        NEW.payment_date := CURRENT_DATE;
     END IF;
 
@@ -523 +550 @@
     CASE
         WHEN CURRENT_DATE - b.created_at::DATE > 60 THEN 'CRITICAL'
-        WHEN CURRENT_DATE - b.created_at::DATE > 30 THEN 'OVERDUE'
-        ELSE 'PENDING'
+        ELSE 'OVERDUE'
     END AS urgency
 FROM billing b
@@ -530 +556 @@
 JOIN patients p ON mr.patient_id = p.patient_id
 WHERE b.payment_status = 'PENDING'
-  AND CURRENT_DATE - b.created_at::DATE >= 30
+  AND CURRENT_DATE - b.created_at::DATE > 30
 ORDER BY days_outstanding DESC;
 }}}
@@ -538 +564 @@
 == Custom domains for EMBG and Phone number formats ==
 
-Custom domains replace one-off column checks with reusable types. **embg_format, email_format, and phone_number_format** are new or stricter than before; **non_negative_cost** consolidates a rule already repeated on three tables. non_negative_cost can be applied immediately, since it matches an existing rule. The other three need existing data checked first, since a stricter domain fails if any row doesn't already conform.
+Custom domains replace one-off column checks with reusable types. **non_negative_cost** consolidates the `cost >= 0` rule that was repeated on **procedures**, **lab_tests** and **billing**; since it matches an existing rule it is applied immediately and the three old CHECK constraints are dropped. It is created in the Custom domains block of the first section on this page, because the column types must be changed before any view that uses these columns is created (such as **v_overdue_billings** and **mv_revenue_monthly**), and PostgreSQL cannot change the type of a column that a view depends on. **embg_format** (the same rule as the existing `patients_embg_format_chk`), **email_format** and **phone_number_format** are defined for reuse but not yet applied to any column: email_format is stricter than the existing checks (for example, it requires a domain ending such as `.com`, which the admin and lab technician checks do not), so existing data has to be checked before it is applied.
 
 === Custom domains ===
@@ -554 +580 @@
 CREATE DOMAIN phone_number_format AS TEXT
     CHECK (VALUE ~ '^\+?[\d\s\-().]{7,20}$');
-
-CREATE DOMAIN non_negative_cost AS DECIMAL(12,2)
-    CHECK (VALUE >= 0);
-
-
-ALTER TABLE procedures ALTER COLUMN cost TYPE non_negative_cost USING cost::non_negative_cost;
-ALTER TABLE lab_tests ALTER COLUMN cost TYPE non_negative_cost USING cost::non_negative_cost;
-ALTER TABLE billing ALTER COLUMN total_cost TYPE non_negative_cost USING total_cost::non_negative_cost;
 }}}
 
@@ -568 +586 @@
 == Revenue reporting (materialized view and background refresh job) ==
 
-Revenue reports need expensive joins across **billing, procedures, and lab tests**, so computing them fresh on every request is wasteful. This materialized view precomputes monthly revenue, split by procedure vs lab test. Procedure revenue is attributed down to department, since procedures carries a **doctor_id** directly. Lab revenue can't be: lab_tests has no doctor or department reference, and **billing_lab_tests** links only to the catalog **test_id**, not a specific performed instance so lab rows are reported to the clinic only.The refresh procedure was verified by manual invocation.
+Revenue reports need expensive joins across **billing, procedures, and lab tests**, so computing them fresh on every request is wasteful. This materialized view precomputes monthly revenue from `PAID` bills, split by procedure vs lab test and grouped by the month of **payment_date**. Procedure revenue is attributed down to department, since **procedures** carries a **doctor_id** directly. Lab revenue can't be: **lab_tests** has no doctor or department reference, and **billing_lab_tests** links only to the catalog **test_id**, not a specific performed instance, so lab rows are reported for the clinic as a whole. The view is refreshed with `CALL medora_job_refresh_revenue_view();`, either manually or from a scheduler such as pg_cron; the refresh procedure was verified by manual invocation.
 
 === Views ===
 
 {{{
+DROP MATERIALIZED VIEW IF EXISTS mv_revenue_monthly CASCADE;
+
 CREATE MATERIALIZED VIEW mv_revenue_monthly AS
 SELECT
@@ -587 +607 @@
     JOIN departments dept ON dept.department_id = doc.department_id
 WHERE b.payment_status = 'PAID'
+  AND b.payment_date IS NOT NULL
 GROUP BY DATE_TRUNC('month', b.payment_date), dept.department_id, dept.department_name
 
@@ -602 +623 @@
     JOIN lab_tests lt ON lt.test_id = blt.test_id
 WHERE b.payment_status = 'PAID'
+  AND b.payment_date IS NOT NULL
 GROUP BY DATE_TRUNC('month', b.payment_date);
 
@@ -617 +639 @@
 {{{
 CREATE OR REPLACE PROCEDURE medora_job_refresh_revenue_view()
-    LANGUAGE plpgsql
+LANGUAGE plpgsql
 AS $$
 BEGIN