swarm / companies/starter/work/project/migrations/001_data_quality_view.sql

CREATE OR REPLACE VIEW data_quality AS
SELECT
    c.customer_id,
    c.customer_name,
    COUNT(r.patient_record_id) AS row_count,
    MAX(r.synced_at) AS last_successful_sync,
    CASE
        WHEN ABS(COUNT(r.patient_record_id) - c.expected_row_count) > c.drift_threshold THEN true
        ELSE false
    END AS drift_detected
FROM
    customers c
LEFT JOIN
    patient_records r ON r.customer_id = c.customer_id
GROUP BY
    c.customer_id,
    c.customer_name,
    c.expected_row_count,
    c.drift_threshold;