Index: backend/src/main/resources/db.migration/R__allergy_prescription_safety_enforcement.sql
===================================================================
--- backend/src/main/resources/db.migration/R__allergy_prescription_safety_enforcement.sql	(revision e1f74f6c04f9d460135de378f659fedfd9c74b44)
+++ backend/src/main/resources/db.migration/R__allergy_prescription_safety_enforcement.sql	(revision e1f74f6c04f9d460135de378f659fedfd9c74b44)
@@ -0,0 +1,85 @@
+CREATE DOMAIN non_negative_currency AS DECIMAL(12,2)
+    CHECK (VALUE >= 0);
+
+CREATE OR REPLACE FUNCTION t1_prescription_allergy_check()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    v_patient_id BIGINT;
+    v_conflict_allergy_id BIGINT;
+    v_allergy_name TEXT;
+BEGIN
+    SELECT patient_id INTO v_patient_id
+    FROM medical_records
+    WHERE record_id = NEW.record_id;
+
+    IF v_patient_id IS NULL THEN
+        RAISE EXCEPTION 'Medical record % not found', NEW.record_id;
+    END IF;
+
+    SELECT apr.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
+    WHERE pr_rest.prescription_id = NEW.prescription_id
+    AND mra.record_id = NEW.record_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;
+    END IF;
+
+    RETURN NEW;
+END;
+$$;
+
+DROP TRIGGER IF EXISTS trg_prescription_allergy_check ON prescription_medical_records;
+CREATE TRIGGER trg_prescription_allergy_check
+    BEFORE INSERT
+    ON prescription_medical_records
+    FOR EACH ROW
+    EXECUTE FUNCTION t1_prescription_allergy_check();
+
+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 VIEW v_prescription_allergy_conflicts AS
+SELECT
+    mr.record_id,
+    p.patient_id,
+    p.first_name,
+    p.last_name,
+    pmr.prescription_id,
+    pr.medication_name,
+    a.allergy_id,
+    a.name AS allergy_name,
+    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
+FROM prescription_medical_records pmr
+JOIN medical_records mr ON pmr.record_id = mr.record_id
+JOIN patients p ON mr.patient_id = p.patient_id
+JOIN prescriptions pr ON pmr.prescription_id = pr.prescription_id
+JOIN prescription_restriction pr_rest ON pr.prescription_id = pr_rest.prescription_id
+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;
Index: backend/src/main/resources/db.migration/R__billing_integrity.sql
===================================================================
--- backend/src/main/resources/db.migration/R__billing_integrity.sql	(revision e1f74f6c04f9d460135de378f659fedfd9c74b44)
+++ backend/src/main/resources/db.migration/R__billing_integrity.sql	(revision e1f74f6c04f9d460135de378f659fedfd9c74b44)
@@ -0,0 +1,145 @@
+-- schema addition: billing had no "issued" date, only payment_date (populated only once
+-- paid), so there was no way to measure how long a still-PENDING bill has been outstanding.
+-- Existing rows will backfill to NOW() at ALTER time, which is not historically accurate —
+-- acceptable for this project, but worth noting.
+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,
+                                                 bill_id BIGINT NOT NULL REFERENCES billing(bill_id),
+    old_amount DECIMAL(12,2),
+    new_amount DECIMAL(12,2),
+    old_status TEXT,
+    new_status TEXT,
+    change_type TEXT CHECK (change_type IN ('INSERT', 'UPDATE', 'LINE_ITEM_ADD', 'LINE_ITEM_REMOVE')),
+    changed_at TIMESTAMP DEFAULT NOW()
+    );
+
+-- Note: billing_procedures links to the catalog procedure_id, not to a specific
+-- performed_procedures row, so when the same procedure type has been performed on more
+-- than one patient there is no reliable way to verify a billed line item belongs to the
+-- same patient as the bill. That check is intentionally left out rather than implemented
+-- unreliably.
+
+CREATE OR REPLACE FUNCTION recalculate_billing_total(p_bill_id BIGINT, p_change_type TEXT)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+v_procedure_total DECIMAL;
+    v_lab_total DECIMAL;
+    v_new_total DECIMAL;
+BEGIN
+SELECT COALESCE(SUM(p.cost), 0) INTO v_procedure_total
+FROM billing_procedures bp
+         JOIN procedures p ON p.procedure_id = bp.procedure_id
+WHERE bp.bill_id = p_bill_id;
+
+SELECT COALESCE(SUM(lt.cost), 0) INTO v_lab_total
+FROM billing_lab_tests blt
+         JOIN lab_tests lt ON lt.test_id = blt.test_id
+WHERE blt.bill_id = p_bill_id;
+
+v_new_total := v_procedure_total + v_lab_total;
+
+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);
+END;
+$$;
+
+-- one shared trigger function, reused for both billing_procedures and billing_lab_tests
+-- (both tables have a bill_id column, so NEW.bill_id / OLD.bill_id works either way)
+CREATE OR REPLACE FUNCTION t1_billing_line_item_changed()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    IF TG_OP = 'DELETE' THEN
+        PERFORM recalculate_billing_total(OLD.bill_id, 'LINE_ITEM_REMOVE');
+RETURN OLD;
+ELSE
+        PERFORM recalculate_billing_total(NEW.bill_id, 'LINE_ITEM_ADD');
+RETURN NEW;
+END IF;
+END;
+$$;
+
+DROP TRIGGER IF EXISTS trg_billing_procedures_update_total ON billing_procedures;
+CREATE TRIGGER trg_billing_procedures_update_total
+    AFTER INSERT OR DELETE ON billing_procedures
+FOR EACH ROW
+EXECUTE FUNCTION t1_billing_line_item_changed();
+
+DROP TRIGGER IF EXISTS trg_billing_lab_tests_update_total ON billing_lab_tests;
+CREATE TRIGGER trg_billing_lab_tests_update_total
+    AFTER INSERT OR DELETE ON billing_lab_tests
+FOR EACH ROW
+EXECUTE FUNCTION t1_billing_line_item_changed();
+
+CREATE OR REPLACE FUNCTION is_valid_billing_transition(p_old TEXT, p_new TEXT)
+RETURNS BOOLEAN
+LANGUAGE sql
+IMMUTABLE
+AS $$
+SELECT CASE
+           WHEN p_old = p_new THEN TRUE
+           WHEN p_old = 'PENDING' AND p_new IN ('PAID', 'CANCELLED') THEN TRUE
+           ELSE FALSE
+           END;
+$$;
+
+CREATE OR REPLACE FUNCTION t2_billing_status_transition()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    IF NOT is_valid_billing_transition(OLD.payment_status, NEW.payment_status) THEN
+        RAISE EXCEPTION 'Cannot transition billing status from % to %',
+            OLD.payment_status, NEW.payment_status;
+END IF;
+
+    IF OLD.payment_status <> NEW.payment_status THEN
+        INSERT INTO billing_audit_log (bill_id, old_status, new_status, change_type)
+        VALUES (NEW.bill_id, OLD.payment_status, NEW.payment_status, 'UPDATE');
+END IF;
+
+RETURN NEW;
+END;
+$$;
+
+DROP TRIGGER IF EXISTS trg_billing_status_transition ON billing;
+CREATE TRIGGER trg_billing_status_transition
+    BEFORE UPDATE ON billing
+    FOR EACH ROW
+    EXECUTE FUNCTION t2_billing_status_transition();
+
+CREATE OR REPLACE VIEW v_overdue_billings AS
+SELECT
+    b.bill_id,
+    p.patient_id, p.first_name, p.last_name,
+    b.total_cost, b.payment_status, b.created_at,
+    CURRENT_DATE - b.created_at::DATE AS days_outstanding,
+        CASE
+            WHEN CURRENT_DATE - b.created_at::DATE > 60 THEN 'CRITICAL'
+        WHEN CURRENT_DATE - b.created_at::DATE > 30 THEN 'OVERDUE'
+        ELSE 'PENDING'
+END AS urgency
+FROM billing b
+JOIN medical_records mr ON b.record_id = mr.record_id
+JOIN patients p ON mr.patient_id = p.patient_id
+WHERE b.payment_status = 'PENDING'
+  AND CURRENT_DATE - b.created_at::DATE >= 30
+ORDER BY days_outstanding DESC;
+
+CREATE OR REPLACE PROCEDURE job_billing_alerts()
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    v_overdue_count INT;
+BEGIN
+    SELECT COUNT(*) INTO v_overdue_count FROM v_overdue_billings WHERE days_outstanding > 30;
+    RAISE NOTICE 'Found % overdue billing records requiring follow-up', v_overdue_count;
+END;
+$$;
Index: backend/src/main/resources/db.migration/R__custom_domains.sql
===================================================================
--- backend/src/main/resources/db.migration/R__custom_domains.sql	(revision e1f74f6c04f9d460135de378f659fedfd9c74b44)
+++ backend/src/main/resources/db.migration/R__custom_domains.sql	(revision e1f74f6c04f9d460135de378f659fedfd9c74b44)
@@ -0,0 +1,14 @@
+CREATE DOMAIN embg_format AS TEXT
+    CHECK (VALUE ~ '^\d{13}$');
+
+CREATE DOMAIN email_format AS TEXT
+    CHECK (
+        VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'
+        AND LENGTH(VALUE) <= 254
+    );
+
+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);
Index: backend/src/main/resources/db.migration/R__medical_record_integrity.sql
===================================================================
--- backend/src/main/resources/db.migration/R__medical_record_integrity.sql	(revision e1f74f6c04f9d460135de378f659fedfd9c74b44)
+++ backend/src/main/resources/db.migration/R__medical_record_integrity.sql	(revision e1f74f6c04f9d460135de378f659fedfd9c74b44)
@@ -0,0 +1,104 @@
+CREATE OR REPLACE FUNCTION t1_diagnosis_record_consistency()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    v_record_patient BIGINT;
+    v_diagnosis_patient BIGINT;
+BEGIN
+    SELECT patient_id INTO v_record_patient FROM medical_records WHERE record_id = NEW.record_id;
+    SELECT patient_id INTO v_diagnosis_patient FROM diagnosis WHERE diagnosis_id = NEW.diagnosis_id;
+
+    IF v_record_patient IS NULL THEN
+        RAISE EXCEPTION 'Medical record % not found', NEW.record_id;
+    END IF;
+
+    IF v_diagnosis_patient IS NULL THEN
+        RAISE EXCEPTION 'Diagnosis % not found', NEW.diagnosis_id;
+    END IF;
+
+    IF v_record_patient <> v_diagnosis_patient THEN
+        RAISE EXCEPTION 'Diagnosis % belongs to patient %, but medical record % belongs to patient %',
+            NEW.diagnosis_id, v_diagnosis_patient, NEW.record_id, v_record_patient;
+    END IF;
+
+    RETURN NEW;
+END;
+$$;
+
+DROP TRIGGER IF EXISTS trg_diagnosis_record_consistency ON diagnosis_medical_records;
+CREATE TRIGGER trg_diagnosis_record_consistency
+    BEFORE INSERT ON diagnosis_medical_records
+    FOR EACH ROW EXECUTE FUNCTION t1_diagnosis_record_consistency();
+
+CREATE OR REPLACE FUNCTION t2_procedure_diagnosis_consistency()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    v_diagnosis_patient BIGINT;
+BEGIN
+    IF NEW.diagnosis_id IS NOT NULL THEN
+        SELECT patient_id INTO v_diagnosis_patient FROM diagnosis WHERE diagnosis_id = NEW.diagnosis_id;
+
+        IF v_diagnosis_patient IS NULL THEN
+            RAISE EXCEPTION 'Diagnosis % not found', NEW.diagnosis_id;
+        END IF;
+
+        IF NEW.patient_id <> v_diagnosis_patient THEN
+            RAISE EXCEPTION 'Procedure belongs to patient %, but diagnosis % belongs to patient %',
+                NEW.patient_id, NEW.diagnosis_id, v_diagnosis_patient;
+        END IF;
+    END IF;
+
+    RETURN NEW;
+END;
+$$;
+
+DROP TRIGGER IF EXISTS trg_procedure_diagnosis_consistency ON performed_procedures;
+CREATE TRIGGER trg_procedure_diagnosis_consistency
+    BEFORE INSERT OR UPDATE ON performed_procedures
+                         FOR EACH ROW EXECUTE FUNCTION t2_procedure_diagnosis_consistency();
+
+CREATE OR REPLACE FUNCTION t3_referral_consistency()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+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;
+    END IF;
+
+    RETURN NEW;
+END;
+$$;
+
+DROP TRIGGER IF EXISTS trg_referral_consistency ON referrals;
+CREATE TRIGGER trg_referral_consistency
+    BEFORE INSERT ON referrals
+    FOR EACH ROW EXECUTE FUNCTION t3_referral_consistency();
+
+CREATE OR REPLACE VIEW v_medical_record_overview AS
+SELECT
+    mr.record_id,
+    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 mrp.procedure_id) AS procedures_count,
+    COUNT(DISTINCT mrl.result_id) AS lab_results_count,
+    COUNT(DISTINCT ref.referral_id) AS referrals_count,
+    COUNT(DISTINCT mra.allergy_id) AS allergies_count,
+    COUNT(DISTINCT CASE WHEN ref.from_doctor_id = ref.to_doctor_id THEN ref.referral_id END) AS self_referrals_detected
+FROM medical_records mr
+    JOIN patients p ON mr.patient_id = p.patient_id
+    LEFT JOIN diagnosis_medical_records dmr ON dmr.record_id = mr.record_id
+    LEFT JOIN diagnosis d ON d.diagnosis_id = dmr.diagnosis_id
+    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
+GROUP BY mr.record_id, p.patient_id, p.first_name, p.last_name, p.embg;
Index: backend/src/main/resources/db.migration/V6__Insert_Billing_Admin_Users.sql
===================================================================
--- backend/src/main/resources/db.migration/V6__Insert_Billing_Admin_Users.sql	(revision e1f74f6c04f9d460135de378f659fedfd9c74b44)
+++ backend/src/main/resources/db.migration/V6__Insert_Billing_Admin_Users.sql	(revision e1f74f6c04f9d460135de378f659fedfd9c74b44)
@@ -0,0 +1,28 @@
+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;
+
+SELECT
+    lt.test_id,
+    lt.test_name,
+    lt.description,
+    lt.cost,
+    plt.test_date,
+    plt.notes
+FROM lab_tests lt
+         JOIN performed_lab_tests plt ON lt.test_id = plt.test_id
+WHERE plt.patient_id = 4 AND plt.test_date = '2026-06-16';
+
+
Index: backend/src/main/resources/db.migration/V7__Update_Billing_Admin_Names.sql
===================================================================
--- backend/src/main/resources/db.migration/V7__Update_Billing_Admin_Names.sql	(revision e1f74f6c04f9d460135de378f659fedfd9c74b44)
+++ backend/src/main/resources/db.migration/V7__Update_Billing_Admin_Names.sql	(revision e1f74f6c04f9d460135de378f659fedfd9c74b44)
@@ -0,0 +1,1 @@
+
Index: backend/src/main/resources/db.migration/V8__appointment_scheduling_integrity.sql
===================================================================
--- backend/src/main/resources/db.migration/V8__appointment_scheduling_integrity.sql	(revision e1f74f6c04f9d460135de378f659fedfd9c74b44)
+++ backend/src/main/resources/db.migration/V8__appointment_scheduling_integrity.sql	(revision e1f74f6c04f9d460135de378f659fedfd9c74b44)
@@ -0,0 +1,133 @@
+-- schema change: NO_SHOW must be added to the existing status constraint before
+-- anything below can ever set it
+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'));
+
+-- No generated column needed - we'll compute the time ranges directly in the triggers
+-- This approach is simpler and avoids immutability constraints
+
+CREATE OR REPLACE FUNCTION is_valid_appointment_transition(p_old TEXT, p_new TEXT)
+RETURNS BOOLEAN
+LANGUAGE sql
+IMMUTABLE
+AS $$
+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
+           ELSE FALSE
+           END;
+$$;
+
+CREATE OR REPLACE FUNCTION trigger_appointments_enforce()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    v_combined_datetime TIMESTAMP;
+BEGIN
+    v_combined_datetime := NEW.appointment_date::TIMESTAMP + NEW.appointment_time;
+
+    IF TG_OP = 'INSERT' AND v_combined_datetime < NOW() THEN
+        RAISE EXCEPTION 'Cannot schedule appointment in the past (appointment_date=%, appointment_time=%)',
+            NEW.appointment_date, NEW.appointment_time;
+    END IF;
+
+    IF TG_OP = 'UPDATE' THEN
+        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;
+
+    RETURN NEW;
+END;
+$$;
+
+DROP TRIGGER IF EXISTS trigger_appointments_enforce ON appointments;
+CREATE TRIGGER trigger_appointments_enforce
+    BEFORE INSERT OR UPDATE
+                         ON appointments
+                         FOR EACH ROW
+                         EXECUTE FUNCTION trigger_appointments_enforce();
+
+CREATE OR REPLACE FUNCTION t1_appointments_no_overlap()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    v_new_start TIMESTAMP;
+    v_new_end TIMESTAMP;
+BEGIN
+    IF NEW.status NOT IN ('SCHEDULED', 'IN_PROGRESS', 'COMPLETED') THEN
+        RETURN NEW;
+    END IF;
+
+    v_new_start := NEW.appointment_date::TIMESTAMP + NEW.appointment_time;
+    v_new_end := v_new_start + INTERVAL '30 minutes';
+
+    -- Check for doctor double-booking
+    -- Overlap condition: existing_start < new_end AND new_start < existing_end
+    IF EXISTS (
+        SELECT 1 FROM appointments a
+        WHERE a.doctor_id = NEW.doctor_id
+          AND a.status IN ('SCHEDULED', 'IN_PROGRESS', 'COMPLETED')
+          AND (a.appointment_date::TIMESTAMP + a.appointment_time) < v_new_end
+          AND v_new_start < (a.appointment_date::TIMESTAMP + a.appointment_time + INTERVAL '30 minutes')
+          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;
+    END IF;
+
+    -- Check for patient double-booking
+    IF EXISTS (
+        SELECT 1 FROM appointments a
+        WHERE a.patient_id = NEW.patient_id
+          AND a.status IN ('SCHEDULED', 'IN_PROGRESS', 'COMPLETED')
+          AND (a.appointment_date::TIMESTAMP + a.appointment_time) < v_new_end
+          AND v_new_start < (a.appointment_date::TIMESTAMP + a.appointment_time + INTERVAL '30 minutes')
+          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;
+    END IF;
+
+    RETURN NEW;
+END;
+$$;
+
+DROP TRIGGER IF EXISTS trigger_appointments_no_overlap ON appointments;
+CREATE TRIGGER trigger_appointments_no_overlap
+    BEFORE INSERT OR UPDATE
+                         ON appointments
+                         FOR EACH ROW
+                         EXECUTE FUNCTION t1_appointments_no_overlap();
+
+CREATE OR REPLACE PROCEDURE job_mark_no_show()
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    UPDATE appointments
+    SET status = 'NO_SHOW'
+    WHERE status = 'SCHEDULED'
+      AND (appointment_date::TIMESTAMP + appointment_time) < (NOW() - INTERVAL '45 minutes');
+END;
+$$;
+
+CREATE OR REPLACE VIEW v_overdue_appointments AS
+SELECT
+    a.appointment_id, a.patient_id, p.first_name, p.last_name,
+    a.doctor_id, d.first_name AS doctor_first_name, d.last_name AS doctor_last_name,
+    a.appointment_date, a.appointment_time, a.status,
+    NOW() - (a.appointment_date::TIMESTAMP + a.appointment_time) AS time_overdue
+FROM appointments a
+         JOIN patients p ON a.patient_id = p.patient_id
+         JOIN doctors d ON a.doctor_id = d.doctor_id
+WHERE a.status = 'SCHEDULED'
+  AND (a.appointment_date::TIMESTAMP + a.appointment_time) < (NOW() - INTERVAL '45 minutes')
+ORDER BY time_overdue DESC;
Index: backend/src/main/resources/db.migration/V9__mv_revenue_reporting.sql
===================================================================
--- backend/src/main/resources/db.migration/V9__mv_revenue_reporting.sql	(revision e1f74f6c04f9d460135de378f659fedfd9c74b44)
+++ backend/src/main/resources/db.migration/V9__mv_revenue_reporting.sql	(revision e1f74f6c04f9d460135de378f659fedfd9c74b44)
@@ -0,0 +1,60 @@
+-- Revenue reporting: monthly revenue, split by procedure vs. lab test.
+-- Procedure revenue is attributed to a department, since `procedures` carries a
+-- doctor_id directly on the catalog row. Lab revenue cannot be attributed to a
+-- department: lab_tests has no doctor/department reference at all, and
+-- billing_lab_tests links only to the catalog test_id, not to a specific performed
+-- instance, so there is no reliable way to know which doctor/department administered
+-- a billed test. Lab rows are reported at clinic-wide monthly granularity only
+-- (department_id/department_name are NULL for those rows).
+
+CREATE MATERIALIZED VIEW mv_revenue_monthly AS
+SELECT
+    DATE_TRUNC('month', b.payment_date)::DATE AS month,
+    dept.department_id,
+    dept.department_name,
+    'PROCEDURE' AS revenue_type,
+    SUM(p.cost) AS revenue,
+    COUNT(DISTINCT b.bill_id) AS transaction_count
+FROM billing b
+         JOIN billing_procedures bp ON bp.bill_id = b.bill_id
+         JOIN procedures p ON p.procedure_id = bp.procedure_id
+         JOIN doctors doc ON doc.doctor_id = p.doctor_id
+         JOIN departments dept ON dept.department_id = doc.department_id
+WHERE b.payment_status = 'PAID'
+GROUP BY DATE_TRUNC('month', b.payment_date), dept.department_id, dept.department_name
+
+UNION ALL
+
+SELECT
+    DATE_TRUNC('month', b.payment_date)::DATE AS month,
+    NULL AS department_id,
+    NULL AS department_name,
+    'LAB' AS revenue_type,
+    SUM(lt.cost) AS revenue,
+    COUNT(DISTINCT b.bill_id) AS transaction_count
+FROM billing b
+         JOIN billing_lab_tests blt ON blt.bill_id = b.bill_id
+         JOIN lab_tests lt ON lt.test_id = blt.test_id
+WHERE b.payment_status = 'PAID'
+GROUP BY DATE_TRUNC('month', b.payment_date);
+
+CREATE INDEX idx_mv_revenue_monthly_month ON mv_revenue_monthly (month, revenue_type);
+
+-- Background job: recomputes the materialized view. Designed to be invoked
+-- periodically (nightly) via pg_cron, OS-level cron, or an external scheduler.
+-- pg_cron was not available in this hosting environment to test automatic
+-- scheduling directly, so this procedure is verified by manual invocation:
+--   CALL medora_job_refresh_revenue_view();
+CREATE OR REPLACE PROCEDURE medora_job_refresh_revenue_view()
+    LANGUAGE plpgsql
+AS $$
+BEGIN
+    REFRESH MATERIALIZED VIEW mv_revenue_monthly;
+END;
+$$;
+
+CREATE OR REPLACE VIEW v_current_month_revenue AS
+SELECT month, department_id, department_name, revenue_type, revenue, transaction_count
+FROM mv_revenue_monthly
+WHERE month = DATE_TRUNC('month', CURRENT_DATE)::DATE
+ORDER BY revenue_type, revenue DESC;