Index: backend/src/main/resources/application.properties
===================================================================
--- backend/src/main/resources/application.properties	(revision 9af201e94b5a79beb92a30858fc54de4bc3b9449)
+++ backend/src/main/resources/application.properties	(revision cdcff72e9765342b84a5e4b5d98ca5cb050a5500)
@@ -23,3 +23,2 @@
 spring.jackson.serialization.fail-on-empty-beans=false
 spring.jackson.default-property-inclusion=non_null
-
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 9af201e94b5a79beb92a30858fc54de4bc3b9449)
+++ 	(revision )
@@ -1,85 +1,0 @@
-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 9af201e94b5a79beb92a30858fc54de4bc3b9449)
+++ 	(revision )
@@ -1,145 +1,0 @@
--- 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 9af201e94b5a79beb92a30858fc54de4bc3b9449)
+++ 	(revision )
@@ -1,14 +1,0 @@
-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 9af201e94b5a79beb92a30858fc54de4bc3b9449)
+++ 	(revision )
@@ -1,104 +1,0 @@
-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/V1.1__Create_Daily_Billing_View.sql
===================================================================
--- backend/src/main/resources/db.migration/V1.1__Create_Daily_Billing_View.sql	(revision 9af201e94b5a79beb92a30858fc54de4bc3b9449)
+++ 	(revision )
@@ -1,28 +1,0 @@
--- Create materialized view for daily patient billing totals
-CREATE MATERIALIZED VIEW IF NOT EXISTS daily_patient_billing_totals AS
-SELECT
-    p.patient_id,
-    CAST(pp.procedure_date AS DATE) as service_date,
-    COALESCE(SUM(pr.cost), 0) as procedure_cost,
-    0::decimal as lab_test_cost,
-    COALESCE(SUM(pr.cost), 0) as total_cost
-FROM patients p
-LEFT JOIN performed_procedures pp ON p.patient_id = pp.patient_id
-LEFT JOIN procedures pr ON pp.procedure_id = pr.procedure_id
-GROUP BY p.patient_id, CAST(pp.procedure_date AS DATE)
-
-UNION ALL
-
-SELECT
-    p.patient_id,
-    CAST(plt.test_date AS DATE) as service_date,
-    0::decimal as procedure_cost,
-    COALESCE(SUM(lt.cost), 0) as lab_test_cost,
-    COALESCE(SUM(lt.cost), 0) as total_cost
-FROM patients p
-LEFT JOIN performed_lab_tests plt ON p.patient_id = plt.patient_id
-LEFT JOIN lab_tests lt ON plt.test_id = lt.test_id
-GROUP BY p.patient_id, CAST(plt.test_date AS DATE);
-
--- Create index for better query performance
-CREATE INDEX IF NOT EXISTS idx_daily_billing_patient_date ON daily_patient_billing_totals(patient_id, service_date);
Index: backend/src/main/resources/db.migration/V1.2__Create_Users_Table.sql
===================================================================
--- backend/src/main/resources/db.migration/V1.2__Create_Users_Table.sql	(revision 9af201e94b5a79beb92a30858fc54de4bc3b9449)
+++ 	(revision )
@@ -1,33 +1,0 @@
-user
--- Create users table
-CREATE TABLE IF NOT EXISTS users (
-    user_id SERIAL PRIMARY KEY,
-    username VARCHAR(255) NOT NULL UNIQUE,
-    password VARCHAR(255) NOT NULL,
-    role VARCHAR(50) NOT NULL,
-    first_name VARCHAR(100),
-    last_name VARCHAR(100),
-    patient_id BIGINT,
-    doctor_id BIGINT,
-    is_active BOOLEAN DEFAULT true,
-    FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
-    FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id)
-);
-
--- Create index for username lookup
-CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
-
--- Insert admin user (password: admin123)
-INSERT INTO users (username, password, role, first_name, last_name, is_active)
-VALUES ('admin', 'admin123', 'ADMIN', 'System', 'Administrator', true)
-ON CONFLICT (username) DO NOTHING;
-
--- Insert sample patient users (password: password123 for all)
--- These will be linked to existing patients by EMBG
-INSERT INTO users (username, password, role, first_name, last_name, patient_id, is_active)
-SELECT p.embg, 'password123', 'PATIENT', p.first_name, p.last_name, p.patient_id, true
-FROM patients p
-WHERE NOT EXISTS (
-    SELECT 1 FROM users u WHERE u.username = p.embg
-)
-LIMIT 39;
Index: backend/src/main/resources/db.migration/V2__Add_appointment_fields_to_referrals.sql
===================================================================
--- backend/src/main/resources/db.migration/V2__Add_appointment_fields_to_referrals.sql	(revision 9af201e94b5a79beb92a30858fc54de4bc3b9449)
+++ 	(revision )
@@ -1,7 +1,0 @@
--- Add appointment_date and appointment_time columns to referrals table
-ALTER TABLE referrals ADD COLUMN IF NOT EXISTS appointment_date DATE;
-ALTER TABLE referrals ADD COLUMN IF NOT EXISTS appointment_time TIME;
-
--- Set default values for existing rows
-UPDATE referrals SET appointment_date = referral_date + INTERVAL '1 day', appointment_time = '10:00:00'
-WHERE appointment_date IS NULL;
Index: backend/src/main/resources/db.migration/V3__Insert_Doctor_Users.sql
===================================================================
--- backend/src/main/resources/db.migration/V3__Insert_Doctor_Users.sql	(revision 9af201e94b5a79beb92a30858fc54de4bc3b9449)
+++ 	(revision )
@@ -1,8 +1,0 @@
--- Insert Doctor users from doctors table
-INSERT INTO users (username, password, role, first_name, last_name, doctor_id, is_active)
-SELECT d.email_address, 'doctor123', 'DOCTOR', d.first_name, d.last_name, d.doctor_id, true
-FROM doctors d
-WHERE NOT EXISTS (
-    SELECT 1 FROM users u WHERE u.username = d.email_address
-)
-ON CONFLICT (username) DO NOTHING;
Index: backend/src/main/resources/db.migration/V4__Insert_Lab_Technician_Users.sql
===================================================================
--- backend/src/main/resources/db.migration/V4__Insert_Lab_Technician_Users.sql	(revision 9af201e94b5a79beb92a30858fc54de4bc3b9449)
+++ 	(revision )
@@ -1,9 +1,0 @@
--- Insert Lab Technician users
-INSERT INTO users (username, password, role, first_name, last_name, is_active)
-VALUES
-  ('lab_darko', 'lab123', 'LAB_TECHNICIAN', 'Darko', 'Milosev', true),
-  ('lab_biljana', 'lab123', 'LAB_TECHNICIAN', 'Biljana', 'Trajkovska', true),
-  ('lab_stefan', 'lab123', 'LAB_TECHNICIAN', 'Stefan', 'Nikolovski', true),
-  ('lab_marina', 'lab123', 'LAB_TECHNICIAN', 'Marina', 'Petreska', true),
-  ('lab_aleksandar', 'lab123', 'LAB_TECHNICIAN', 'Aleksandar', 'Ristovski', true)
-ON CONFLICT (username) DO NOTHING;
Index: backend/src/main/resources/db.migration/V5__Insert_Billing_Admin_Users.sql
===================================================================
--- backend/src/main/resources/db.migration/V5__Insert_Billing_Admin_Users.sql	(revision 9af201e94b5a79beb92a30858fc54de4bc3b9449)
+++ 	(revision )
@@ -1,16 +1,0 @@
--- Insert Billing Admin users
-INSERT INTO users (username, password, role, first_name, last_name, is_active)
-SELECT 'admin_ilija', 'adminmedora123', 'BILLING_ADMIN', 'Ilija', 'Admin', true
-WHERE NOT EXISTS (SELECT 1 FROM users WHERE username = 'admin_ilija')
-UNION ALL
-SELECT 'admin_elena', 'adminmedora123', 'BILLING_ADMIN', 'Elena', 'Admin', true
-WHERE NOT EXISTS (SELECT 1 FROM users WHERE username = 'admin_elena')
-UNION ALL
-SELECT 'admin_marjan', 'adminmedora123', 'BILLING_ADMIN', 'Marjan', 'Admin', true
-WHERE NOT EXISTS (SELECT 1 FROM users WHERE username = 'admin_marjan')
-UNION ALL
-SELECT 'admin_vesna', 'adminmedora123', 'BILLING_ADMIN', 'Vesna', 'Admin', true
-WHERE NOT EXISTS (SELECT 1 FROM users WHERE username = 'admin_vesna')
-UNION ALL
-SELECT 'admin_dushanka', 'adminmedora123', 'BILLING_ADMIN', 'Dushanka', 'Admin', true
-WHERE NOT EXISTS (SELECT 1 FROM users WHERE username = 'admin_dushanka');
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 9af201e94b5a79beb92a30858fc54de4bc3b9449)
+++ 	(revision )
@@ -1,28 +1,0 @@
-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 9af201e94b5a79beb92a30858fc54de4bc3b9449)
+++ 	(revision )
@@ -1,1 +1,0 @@
-
Index: backend/src/main/resources/db.migration/V8__appointment_scheduling_integrity.sql
===================================================================
--- backend/src/main/resources/db.migration/V8__appointment_scheduling_integrity.sql	(revision 9af201e94b5a79beb92a30858fc54de4bc3b9449)
+++ 	(revision )
@@ -1,133 +1,0 @@
--- 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 9af201e94b5a79beb92a30858fc54de4bc3b9449)
+++ 	(revision )
@@ -1,60 +1,0 @@
--- 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;
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 cdcff72e9765342b84a5e4b5d98ca5cb050a5500)
+++ backend/src/main/resources/db/migration/R__allergy_prescription_safety_enforcement.sql	(revision cdcff72e9765342b84a5e4b5d98ca5cb050a5500)
@@ -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 cdcff72e9765342b84a5e4b5d98ca5cb050a5500)
+++ backend/src/main/resources/db/migration/R__billing_integrity.sql	(revision cdcff72e9765342b84a5e4b5d98ca5cb050a5500)
@@ -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__create_scoped_app_role.sql
===================================================================
--- backend/src/main/resources/db/migration/R__create_scoped_app_role.sql	(revision cdcff72e9765342b84a5e4b5d98ca5cb050a5500)
+++ backend/src/main/resources/db/migration/R__create_scoped_app_role.sql	(revision cdcff72e9765342b84a5e4b5d98ca5cb050a5500)
@@ -0,0 +1,35 @@
+-- Create scoped application role with limited privileges
+-- Run this as the postgres superuser once, then update application.properties
+-- to connect as medora_app instead of postgres
+
+-- Step 1: Create the scoped application user
+-- WARNING: Change the password to something strong and unique before running!
+-- This example uses a placeholder — use: openssl rand -base64 32
+-- DO NOT commit the actual password to source control
+CREATE USER medora_app WITH PASSWORD 'CHANGE_ME_TO_A_STRONG_PASSWORD';
+
+-- Step 2: Grant connection and usage privileges
+GRANT CONNECT ON DATABASE medora TO medora_app;
+GRANT USAGE ON SCHEMA public TO medora_app;
+
+-- Step 3: Grant data manipulation privileges (CRUD only, no DDL)
+GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO medora_app;
+GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO medora_app;
+
+-- Step 4: Ensure future tables (created by migrations) automatically grant permissions
+ALTER DEFAULT PRIVILEGES IN SCHEMA public
+GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO medora_app;
+
+ALTER DEFAULT PRIVILEGES IN SCHEMA public
+GRANT USAGE ON SEQUENCES TO medora_app;
+
+-- Verification queries (run as medora_app to confirm access):
+-- SELECT * FROM patients LIMIT 1;  -- should work
+-- CREATE TABLE test (id INT);       -- should fail (not permitted)
+-- DROP TABLE patients;              -- should fail (not permitted)
+
+-- After confirming this works:
+-- 1. Update application.properties: spring.datasource.username=medora_app
+-- 2. Update application.properties: spring.datasource.password=${DB_PASSWORD}
+-- 3. Set environment variable: export DB_PASSWORD='<the password you chose>'
+-- 4. Restart the application
Index: backend/src/main/resources/db/migration/R__custom_domains.sql
===================================================================
--- backend/src/main/resources/db/migration/R__custom_domains.sql	(revision cdcff72e9765342b84a5e4b5d98ca5cb050a5500)
+++ backend/src/main/resources/db/migration/R__custom_domains.sql	(revision cdcff72e9765342b84a5e4b5d98ca5cb050a5500)
@@ -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 cdcff72e9765342b84a5e4b5d98ca5cb050a5500)
+++ backend/src/main/resources/db/migration/R__medical_record_integrity.sql	(revision cdcff72e9765342b84a5e4b5d98ca5cb050a5500)
@@ -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/V1.1__Create_Daily_Billing_View.sql
===================================================================
--- backend/src/main/resources/db/migration/V1.1__Create_Daily_Billing_View.sql	(revision cdcff72e9765342b84a5e4b5d98ca5cb050a5500)
+++ backend/src/main/resources/db/migration/V1.1__Create_Daily_Billing_View.sql	(revision cdcff72e9765342b84a5e4b5d98ca5cb050a5500)
@@ -0,0 +1,28 @@
+ -- Create materialized view for daily patient billing totals
+CREATE MATERIALIZED VIEW IF NOT EXISTS daily_patient_billing_totals AS
+SELECT
+    p.patient_id,
+    CAST(pp.procedure_date AS DATE) as service_date,
+    COALESCE(SUM(pr.cost), 0) as procedure_cost,
+    0::decimal as lab_test_cost,
+    COALESCE(SUM(pr.cost), 0) as total_cost
+FROM patients p
+LEFT JOIN performed_procedures pp ON p.patient_id = pp.patient_id
+LEFT JOIN procedures pr ON pp.procedure_id = pr.procedure_id
+GROUP BY p.patient_id, CAST(pp.procedure_date AS DATE)
+
+UNION ALL
+
+SELECT
+    p.patient_id,
+    CAST(plt.test_date AS DATE) as service_date,
+    0::decimal as procedure_cost,
+    COALESCE(SUM(lt.cost), 0) as lab_test_cost,
+    COALESCE(SUM(lt.cost), 0) as total_cost
+FROM patients p
+LEFT JOIN performed_lab_tests plt ON p.patient_id = plt.patient_id
+LEFT JOIN lab_tests lt ON plt.test_id = lt.test_id
+GROUP BY p.patient_id, CAST(plt.test_date AS DATE);
+
+-- Create index for better query performance
+CREATE INDEX IF NOT EXISTS idx_daily_billing_patient_date ON daily_patient_billing_totals(patient_id, service_date);
Index: backend/src/main/resources/db/migration/V1.2__Create_Users_Table.sql
===================================================================
--- backend/src/main/resources/db/migration/V1.2__Create_Users_Table.sql	(revision cdcff72e9765342b84a5e4b5d98ca5cb050a5500)
+++ backend/src/main/resources/db/migration/V1.2__Create_Users_Table.sql	(revision cdcff72e9765342b84a5e4b5d98ca5cb050a5500)
@@ -0,0 +1,41 @@
+-- Create users table
+CREATE TABLE IF NOT EXISTS users (
+    user_id SERIAL PRIMARY KEY,
+    username VARCHAR(255) NOT NULL UNIQUE,
+    password VARCHAR(255) NOT NULL,
+    role VARCHAR(50) NOT NULL,
+    first_name VARCHAR(100),
+    last_name VARCHAR(100),
+    patient_id BIGINT,
+    doctor_id BIGINT,
+    is_active BOOLEAN DEFAULT true,
+    FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
+    FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id)
+);
+
+-- Create index for username lookup
+CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
+
+-- Insert admin user (password: admin123)
+INSERT INTO users (username, password, role, first_name, last_name, is_active)
+VALUES ('admin', 'admin123', 'ADMIN', 'System', 'Administrator', true)
+ON CONFLICT (username) DO NOTHING;
+
+-- Insert sample patient users (password: password123 for all)
+-- These will be linked to existing patients by EMBG
+INSERT INTO users (username, password, role, first_name, last_name, patient_id, is_active)
+SELECT p.embg, 'password123', 'PATIENT', p.first_name, p.last_name, p.patient_id, true
+FROM patients p
+WHERE NOT EXISTS (
+    SELECT 1 FROM users u WHERE u.username = p.embg
+)
+LIMIT 39;
+
+-- Insert doctor users (password: doctor123 for all)
+-- These will be linked to existing doctors by email
+INSERT INTO users (username, password, role, first_name, last_name, doctor_id, is_active)
+SELECT d.email_address, 'doctor123', 'DOCTOR', d.first_name, d.last_name, d.doctor_id, true
+FROM doctors d
+WHERE NOT EXISTS (
+    SELECT 1 FROM users u WHERE u.username = d.email_address
+);
Index: backend/src/main/resources/db/migration/V2__Add_appointment_fields_to_referrals.sql
===================================================================
--- backend/src/main/resources/db/migration/V2__Add_appointment_fields_to_referrals.sql	(revision cdcff72e9765342b84a5e4b5d98ca5cb050a5500)
+++ backend/src/main/resources/db/migration/V2__Add_appointment_fields_to_referrals.sql	(revision cdcff72e9765342b84a5e4b5d98ca5cb050a5500)
@@ -0,0 +1,7 @@
+-- Add appointment_date and appointment_time columns to referrals table
+ALTER TABLE referrals ADD COLUMN IF NOT EXISTS appointment_date DATE;
+ALTER TABLE referrals ADD COLUMN IF NOT EXISTS appointment_time TIME;
+
+-- Set default values for existing rows
+UPDATE referrals SET appointment_date = referral_date + INTERVAL '1 day', appointment_time = '10:00:00'
+WHERE appointment_date IS NULL;
Index: backend/src/main/resources/db/migration/V3__Fix_LabTechnician_Admin_Structure.sql
===================================================================
--- backend/src/main/resources/db/migration/V3__Fix_LabTechnician_Admin_Structure.sql	(revision cdcff72e9765342b84a5e4b5d98ca5cb050a5500)
+++ backend/src/main/resources/db/migration/V3__Fix_LabTechnician_Admin_Structure.sql	(revision cdcff72e9765342b84a5e4b5d98ca5cb050a5500)
@@ -0,0 +1,63 @@
+-- V3__Fix_LabTechnician_Admin_Structure.sql
+-- Refactor LabTechnician and Admin to use user_id FK instead of duplicating user data
+
+-- Step 1: Drop FK constraints from tables that reference lab_technician and admin
+ALTER TABLE IF EXISTS billing DROP CONSTRAINT IF EXISTS "FKd17oy1jh5wl1m8vgitr17ks5m";
+ALTER TABLE IF EXISTS performed_lab_tests DROP CONSTRAINT IF EXISTS "FK7itndt1cw4ekph05b0kuadfge";
+ALTER TABLE IF EXISTS users DROP CONSTRAINT IF EXISTS "FKlsz5c3rwmg8f4p8xfay9wqy4w";
+ALTER TABLE IF EXISTS users DROP CONSTRAINT IF EXISTS "FK9lxmsmidme9l8ofsx1xfyamtk";
+
+-- Step 2: Clear admin_id and technician_id from billing and performed_lab_tests tables
+UPDATE billing SET admin_id = NULL WHERE admin_id IS NOT NULL;
+UPDATE performed_lab_tests SET technician_id = NULL WHERE technician_id IS NOT NULL;
+
+-- Step 3: Create new structure for lab_technician table with user_id FK
+CREATE TABLE IF NOT EXISTS lab_technician_new (
+    technician_id BIGINT PRIMARY KEY,
+    user_id BIGINT NOT NULL UNIQUE,
+    certification VARCHAR(255),
+    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
+);
+
+-- Step 4: Migrate existing lab_technician data
+INSERT INTO lab_technician_new (technician_id, user_id, certification)
+SELECT lt.technician_id, u.user_id, lt.email
+FROM lab_technician lt
+LEFT JOIN users u ON u.username = lt.username
+WHERE u.user_id IS NOT NULL
+ON CONFLICT DO NOTHING;
+
+-- Step 5: Drop the old lab_technician table
+DROP TABLE IF EXISTS lab_technician CASCADE;
+
+-- Step 6: Rename new table to original name
+ALTER TABLE lab_technician_new RENAME TO lab_technician;
+
+-- Step 7: Create new structure for admin table with user_id FK
+CREATE TABLE IF NOT EXISTS admin_new (
+    admin_id BIGINT PRIMARY KEY,
+    user_id BIGINT NOT NULL UNIQUE,
+    permissions VARCHAR(255),
+    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
+);
+
+-- Step 8: Migrate existing admin data
+INSERT INTO admin_new (admin_id, user_id, permissions)
+SELECT a.admin_id, u.user_id, NULL
+FROM admin a
+LEFT JOIN users u ON u.username = a.username
+WHERE u.user_id IS NOT NULL AND u.role = 'ADMIN'
+ON CONFLICT DO NOTHING;
+
+-- Step 9: Drop the old admin table
+DROP TABLE IF EXISTS admin CASCADE;
+
+-- Step 10: Rename new table to original name
+ALTER TABLE admin_new RENAME TO admin;
+
+-- Step 11: Create indexes for performance
+CREATE INDEX IF NOT EXISTS idx_lab_technician_user_id ON lab_technician(user_id);
+CREATE INDEX IF NOT EXISTS idx_admin_user_id ON admin(user_id);
+
+-- Step 12: Add FK constraints back (now with proper data)
+ALTER TABLE performed_lab_tests ADD CONSTRAINT FK7itndt1cw4ekph05b0kuadfge FOREIGN KEY (technician_id) REFERENCES lab_technician(technician_id) ON DELETE SET NULL;
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 cdcff72e9765342b84a5e4b5d98ca5cb050a5500)
+++ backend/src/main/resources/db/migration/V6__Insert_Billing_Admin_Users.sql	(revision cdcff72e9765342b84a5e4b5d98ca5cb050a5500)
@@ -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 cdcff72e9765342b84a5e4b5d98ca5cb050a5500)
+++ backend/src/main/resources/db/migration/V7__Update_Billing_Admin_Names.sql	(revision cdcff72e9765342b84a5e4b5d98ca5cb050a5500)
@@ -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 cdcff72e9765342b84a5e4b5d98ca5cb050a5500)
+++ backend/src/main/resources/db/migration/V8__appointment_scheduling_integrity.sql	(revision cdcff72e9765342b84a5e4b5d98ca5cb050a5500)
@@ -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 cdcff72e9765342b84a5e4b5d98ca5cb050a5500)
+++ backend/src/main/resources/db/migration/V9__mv_revenue_reporting.sql	(revision cdcff72e9765342b84a5e4b5d98ca5cb050a5500)
@@ -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;
Index: backend/src/main/resources/db/phase7/verify_section2_deployment.sql
===================================================================
--- backend/src/main/resources/db/phase7/verify_section2_deployment.sql	(revision cdcff72e9765342b84a5e4b5d98ca5cb050a5500)
+++ backend/src/main/resources/db/phase7/verify_section2_deployment.sql	(revision cdcff72e9765342b84a5e4b5d98ca5cb050a5500)
@@ -0,0 +1,130 @@
+-- ============================================================================
+-- Section 2: Appointment Scheduling Integrity - Deployment Verification
+-- ============================================================================
+
+-- 1. Check triggers on appointments table
+-- ============================================================================
+-- === 1. TRIGGERS ON APPOINTMENTS TABLE ===
+SELECT
+    tgname as trigger_name,
+    CASE WHEN tgdisabled = 0 THEN 'ENABLED' ELSE 'DISABLED' END as status
+FROM pg_trigger
+WHERE tgrelid = 'appointments'::regclass
+ORDER BY tgname;
+
+-- 2. Check trigger functions
+-- ============================================================================
+-- === 2. TRIGGER FUNCTIONS ===
+SELECT
+    proname as function_name,
+    pronargs as parameter_count,
+    prokind as kind
+FROM pg_proc
+WHERE proname IN ('trigger_appointments_enforce', 'trigger_appointments_no_overlap', 'is_valid_appointment_transition')
+ORDER BY proname;
+
+-- 3. Check background job procedure
+-- ============================================================================
+-- === 3. BACKGROUND JOB PROCEDURE ===
+SELECT
+    proname as procedure_name,
+    prokind as kind,
+    'PL/pgSQL' as language
+FROM pg_proc
+WHERE proname = 'job_mark_no_show';
+
+-- 4. Check view
+-- ============================================================================
+-- === 4. VIEWS ===
+SELECT
+    viewname as view_name,
+    schemaname as schema_name
+FROM pg_views
+WHERE viewname = 'v_overdue_appointments';
+
+-- 5. Check appointment status constraint
+-- ============================================================================
+-- === 5. STATUS CONSTRAINT (CHECK) ===
+SELECT
+    constraint_name,
+    constraint_type,
+    table_name
+FROM information_schema.table_constraints
+WHERE table_name = 'appointments'
+  AND constraint_type = 'CHECK'
+  AND constraint_name LIKE '%status%';
+
+-- 6. Verify constraint includes NO_SHOW
+-- ============================================================================
+-- === 6. CONSTRAINT DEFINITION ===
+SELECT
+    constraint_name,
+    check_clause
+FROM information_schema.check_constraints
+WHERE constraint_name = 'appointments_status_chk';
+
+-- 7. Test appointment status values
+-- ============================================================================
+-- === 7. VALID APPOINTMENT STATUSES ===
+SELECT 'SCHEDULED' as status
+UNION ALL
+SELECT 'IN_PROGRESS'
+UNION ALL
+SELECT 'COMPLETED'
+UNION ALL
+SELECT 'CANCELLED'
+UNION ALL
+SELECT 'NO_SHOW'
+ORDER BY status;
+
+-- 8. Summary Statistics
+-- ============================================================================
+-- === 8. DEPLOYMENT SUMMARY ===
+SELECT
+    'Triggers' as component,
+    COUNT(*) as count
+FROM pg_trigger
+WHERE tgrelid = 'appointments'::regclass
+UNION ALL
+SELECT
+    'Trigger Functions' as component,
+    COUNT(*) as count
+FROM pg_proc
+WHERE proname IN ('trigger_appointments_enforce', 'trigger_appointments_no_overlap')
+UNION ALL
+SELECT
+    'Validation Functions' as component,
+    COUNT(*) as count
+FROM pg_proc
+WHERE proname = 'is_valid_appointment_transition'
+UNION ALL
+SELECT
+    'Background Procedures' as component,
+    COUNT(*) as count
+FROM pg_proc
+WHERE proname = 'job_mark_no_show'
+UNION ALL
+SELECT
+    'Views' as component,
+    COUNT(*) as count
+FROM pg_views
+WHERE viewname = 'v_overdue_appointments'
+ORDER BY component;
+
+-- 9. Test the transition validation function
+-- ============================================================================
+-- === 9. STATUS TRANSITION VALIDATION TEST ===
+SELECT
+    'SCHEDULED → IN_PROGRESS' as transition,
+    is_valid_appointment_transition('SCHEDULED', 'IN_PROGRESS') as valid
+UNION ALL
+SELECT 'SCHEDULED → COMPLETED', is_valid_appointment_transition('SCHEDULED', 'COMPLETED')
+UNION ALL
+SELECT 'SCHEDULED → CANCELLED', is_valid_appointment_transition('SCHEDULED', 'CANCELLED')
+UNION ALL
+SELECT 'IN_PROGRESS → COMPLETED', is_valid_appointment_transition('IN_PROGRESS', 'COMPLETED')
+UNION ALL
+SELECT 'COMPLETED → SCHEDULED', is_valid_appointment_transition('COMPLETED', 'SCHEDULED')
+UNION ALL
+SELECT 'COMPLETED → COMPLETED', is_valid_appointment_transition('COMPLETED', 'COMPLETED')
+ORDER BY transition;
Index: backend/src/main/resources/import.sql
===================================================================
--- backend/src/main/resources/import.sql	(revision cdcff72e9765342b84a5e4b5d98ca5cb050a5500)
+++ backend/src/main/resources/import.sql	(revision cdcff72e9765342b84a5e4b5d98ca5cb050a5500)
@@ -0,0 +1,3 @@
+-- Add appointment_date and appointment_time columns to referrals table if they don't exist
+ALTER TABLE IF EXISTS referrals ADD COLUMN IF NOT EXISTS appointment_date DATE;
+ALTER TABLE IF EXISTS referrals ADD COLUMN IF NOT EXISTS appointment_time TIME;