swarm / companies/starter/work/project/src/sync/metrics.sql

-- Schema for data quality metrics
-- This table stores aggregated metrics per customer to support the dashboard
CREATE TABLE IF NOT EXISTS sync_metrics (
    id SERIAL PRIMARY KEY,
    customer_id UUID NOT NULL,
    table_name TEXT NOT NULL,
    row_count BIGINT NOT NULL DEFAULT 0,
    last_sync_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    drift_detected BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Index for fast lookup by customer and table
CREATE INDEX IF NOT EXISTS idx_sync_metrics_customer_table ON sync_metrics (customer_id, table_name);

-- We only keep the latest metric per customer/table to keep the table small
-- This is a partial unique index to enforce uniqueness on the latest record
CREATE UNIQUE INDEX IF NOT EXISTS idx_sync_metrics_latest ON sync_metrics (customer_id, table_name) WHERE created_at = (
    SELECT MAX(created_at) FROM sync_metrics sm2 WHERE sm2.customer_id = sync_metrics.customer_id AND sm2.table_name = sync_metrics.table_name
);