Advanced Database Development

---

Allergy–prescription safety enforcement

A patient should never be prescribed a medication that conflicts with one of their documented allergies. This requirement blocks the unsafe insert outright, at the database level before it can happen: a new prescription_medical_records row is rejected if the prescription is linked, via allergy_prescription_restrictions → prescription_restriction to any allergy already on that patient's medical record.

Custom domains

CREATE DOMAIN non_negative_currency AS DECIMAL(12,2)
    CHECK (VALUE >= 0);

Triggers

CREATE TABLE IF NOT EXISTS prescription_allergy_conflicts_log (
    log_id BIGSERIAL PRIMARY KEY,
    record_id BIGINT NOT NULL REFERENCES medical_records(record_id),
    prescription_id BIGINT NOT NULL REFERENCES prescriptions(prescription_id),
    allergy_id BIGINT NOT NULL REFERENCES allergies(allergy_id),
    conflict_type TEXT CHECK (conflict_type IN ('ACTIVE', 'RESOLVED')),
    detected_date TIMESTAMP DEFAULT NOW(),
    resolution_notes TEXT,
    resolved_date TIMESTAMP
);

CREATE OR REPLACE FUNCTION t1_prescription_allergy_check()
RETURNS TRIGGER
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();

Views

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;

---

Appointment scheduling integrity

Appointments must obey real scheduling constraints so no scheduling in the past, no doublebooking the same doctor or patient in an overlapping time, no marking an appointment COMPLETED before its scheduled time, and valid status transitions only (SCHEDULED → IN_PROGRESS/COMPLETED/CANCELLED, IN_PROGRESS → COMPLETED/CANCELLED, nothing further out of COMPLETED/CANCELLED/NO_SHOW). A background job autotransitions appointments that remain SCHEDULED if they are 45+ minutes past their time to NO_SHOW.

Schema change

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'));

Triggers

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';

    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;

    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();

Stored procedures/functions

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;
$$;

Views

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;

---

Medical record consistency

Diagnoses, procedures, and referrals can be created independently but must stay consistent with the patient they actually serve as a mismatch like a diagnosis for one patient attached to a different patient's record, a doctor referring a patient to themselves, is both an integrity problem and a clinical safety risk.

Triggers

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();

Views

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;

---

Billing integrity

Billing records must stay in sync with their line items and payment status must follow a valid state machine. billing.total_cost is automatically recalculated whenever a procedure or lab test is added to or removed from a bill, rather than relying on the application to keep it in sync manually. payment_status may only move PENDING → PAID or PENDING → CANCELLED but never backwards once finalized and every status change is written to an audit log. Since billing originally had no "issued" date , a created_at column was added so outstanding PENDING bills can be tracked for follow-up.

Schema change

ALTER TABLE billing ADD COLUMN IF NOT EXISTS created_at TIMESTAMP DEFAULT NOW();

CREATE TABLE IF NOT EXISTS billing_audit_log (
    audit_id BIGSERIAL PRIMARY KEY,
    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()
);

Stored procedures/functions

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;
$$;

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 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;
$$;

Triggers

-- 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 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();

Views

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;

---

Custom domains for EMBG and Phone number formats

Custom domains replace one-off column checks with reusable types. embg_format, email_format, and phone_number_format are new or stricter than before; non_negative_cost consolidates a rule already repeated on three tables. non_negative_cost can be applied immediately, since it matches an existing rule. The other three need existing data checked first, since a stricter domain fails if any row doesn't already conform.

Custom domains

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);


ALTER TABLE procedures ALTER COLUMN cost TYPE non_negative_cost USING cost::non_negative_cost;
ALTER TABLE lab_tests ALTER COLUMN cost TYPE non_negative_cost USING cost::non_negative_cost;
ALTER TABLE billing ALTER COLUMN total_cost TYPE non_negative_cost USING total_cost::non_negative_cost;

-- run these BEFORE altering embg / email / phone columns — every one of these
-- should return ZERO rows, otherwise the corresponding ALTER will fail:
--   SELECT patient_id, embg FROM patients WHERE embg !~ '^\d{13}$';
--   SELECT doctor_id, email_address FROM doctors WHERE email_address !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';
--   SELECT patient_id, email_address FROM patients WHERE email_address IS NOT NULL AND email_address !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';
--   SELECT patient_id, phone_number FROM patients WHERE phone_number IS NOT NULL AND phone_number !~ '^\+?[\d\s\-().]{7,20}$';

---

Revenue reporting (materialized view and background refresh job)

Revenue reports need expensive joins across billing, procedures, and lab tests, so computing them fresh on every request is wasteful. This materialized view precomputes monthly revenue, split by procedure vs lab test. Procedure revenue is attributed down to department, since procedures carries a doctor_id directly. Lab revenue can't be: lab_tests has no doctor or department reference, and billing_lab_tests links only to the catalog test_id, not a specific performed instance so lab rows are reported to the clinic only.The refresh procedure was verified by manual invocation.

Views

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);

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;

Stored procedures/functions

CREATE OR REPLACE PROCEDURE medora_job_refresh_revenue_view()
    LANGUAGE plpgsql
AS $$
BEGIN
    REFRESH MATERIALIZED VIEW mv_revenue_monthly;
END;
$$;