swarm / companies/starter/work/project/db/migrations/004_data_quality_dashboard.sql

raw file 614 bytes 
CREATE OR REPLACE VIEW v_data_quality AS
SELECT
    c.customer_id,
    c.customer_name,
    COUNT(r.id) AS total_rows,
    MAX(r.last_synced_at) AS last_successful_sync,
    -- Drift check: flag if the most recent sync is older than 24 hours
    CASE
        WHEN MAX(r.last_synced_at) < NOW() - INTERVAL '24 hours' THEN 'DRIFT'
        ELSE 'OK'
    END AS drift_status
FROM customers c
LEFT JOIN records r ON r.customer_id = c.customer_id
GROUP BY c.customer_id, c.customer_name;

-- Index to support the view efficiently
CREATE INDEX idx_records_customer_last_sync ON records (customer_id, last_synced_at DESC);