Client Manager Dashboard

This document provides SQL query examples for analyzing tenant utilization metrics stored in the dashboard tables.

Table Overview

The dashboard system uses three main tables in the public schema:

dashboard_tenant_metrics - Current snapshot of all tenant metrics
dashboard_tenant_metrics_history - Historical time-series data for trending
dashboard_sync_status_history - Historical sync validation between AlloyDB and BigQuery

Available Views

The following views simplify common query patterns and eliminate repetitive CTEs:

`v_dashboard_latest_sync_status`

Latest sync validation status per tenant. Eliminates the need for manual MAX(check_time) CTE construction.

Columns: customer_id, check_time, sync_status, sync_health_score, alloydb_total_clients, alloydb_recently_updated_clients, alloydb_stale_clients, bigquery_client_count, bigquery_partition_date, count_difference, count_difference_percent, has_significant_drift

Example usage:

-- Get all tenants with sync issues
SELECT customer_id, sync_status, sync_health_score, count_difference_percent
FROM v_dashboard_latest_sync_status
WHERE sync_status IN ('warning', 'critical')
ORDER BY sync_health_score ASC;

`v_dashboard_tenant_health`

Comprehensive health overview combining metrics + sync status with calculated health categorization and priority scoring.

Health statuses: healthy, high_stale_rate, sync_warning, sync_critical, high_orphan_rate, high_duplicate_tokens

Columns: customer_id, total_clients, authenticated_clients, clients_with_push_token, unique_contacts, orphaned_clients, duplicate_push_token_count, health_status, health_priority, sync_status, sync_health_score, stale_client_count, stale_client_percent, has_significant_drift, metrics_updated_at, sync_checked_at

Example usage:

-- Get unhealthy tenants sorted by severity
SELECT customer_id, health_status, health_priority, total_clients, stale_client_percent
FROM v_dashboard_tenant_health
WHERE health_status != 'healthy'
ORDER BY health_priority, total_clients DESC;

`v_dashboard_tenant_growth_7d`

7-day growth comparison showing current vs week-ago client counts with absolute and percentage growth.

Columns: customer_id, current_clients, clients_7d_ago, growth, growth_percent

Example usage:

-- Top 20 fastest growing tenants
SELECT customer_id, current_clients, growth, growth_percent
FROM v_dashboard_tenant_growth_7d
ORDER BY growth DESC
LIMIT 20;

Basic Queries

Get All Tenant Metrics (Latest Snapshot)

SELECT
  customer_id,
  total_clients,
  authenticated_clients,
  clients_with_push_token,
  unique_contacts,
  duplicate_push_token_count,
  aggregated_at
FROM dashboard_tenant_metrics
ORDER BY total_clients DESC;

Get Metrics for Specific Tenant

SELECT *
FROM dashboard_tenant_metrics
WHERE customer_id = 123;

Sync Status Queries

Get Latest Sync Status for All Tenants

Using view (recommended):

SELECT
  customer_id,
  sync_status,
  sync_health_score,
  count_difference,
  count_difference_percent,
  alloydb_total_clients,
  bigquery_client_count,
  bigquery_partition_date,
  check_time
FROM v_dashboard_latest_sync_status
ORDER BY sync_health_score ASC;

Alternative: Without view (manual CTE)

WITH latest_checks AS (
  SELECT
    customer_id,
    MAX(check_time) as latest_check_time
  FROM dashboard_sync_status_history
  GROUP BY customer_id
)
SELECT
  s.customer_id,
  s.sync_status,
  s.sync_health_score,
  s.count_difference,
  s.count_difference_percent,
  s.alloydb_total_clients,
  s.bigquery_client_count,
  s.bigquery_partition_date,
  s.check_time
FROM dashboard_sync_status_history s
INNER JOIN latest_checks l
  ON s.customer_id = l.customer_id
  AND s.check_time = l.latest_check_time
ORDER BY s.sync_health_score ASC;

Get Tenants with Sync Issues

Using view (recommended):

SELECT
  customer_id,
  sync_status,
  sync_health_score,
  count_difference,
  count_difference_percent,
  has_significant_drift
FROM v_dashboard_latest_sync_status
WHERE sync_status IN ('warning', 'critical')
ORDER BY sync_health_score ASC;

Alternative: Without view (manual CTE)

WITH latest_checks AS (
  SELECT
    customer_id,
    MAX(check_time) as latest_check_time
  FROM dashboard_sync_status_history
  GROUP BY customer_id
)
SELECT
  s.customer_id,
  s.sync_status,
  s.sync_health_score,
  s.count_difference,
  s.count_difference_percent,
  s.has_significant_drift
FROM dashboard_sync_status_history s
INNER JOIN latest_checks l
  ON s.customer_id = l.customer_id
  AND s.check_time = l.latest_check_time
WHERE s.sync_status IN ('warning', 'critical')
ORDER BY s.sync_health_score ASC;

Sync Status Trend for Specific Tenant

SELECT
  check_time,
  alloydb_total_clients,
  bigquery_client_count,
  count_difference,
  count_difference_percent,
  sync_health_score,
  sync_status
FROM dashboard_sync_status_history
WHERE customer_id = 123
  AND check_time >= NOW() - INTERVAL '7 days'
ORDER BY check_time ASC;

Growth and Trending Queries

Client Growth Trend for Specific Tenant (Last 7 Days)

SELECT
  snapshot_time,
  total_clients,
  authenticated_clients,
  clients_with_push_token,
  unique_contacts
FROM dashboard_tenant_metrics_history
WHERE customer_id = 123
  AND snapshot_time >= NOW() - INTERVAL '7 days'
ORDER BY snapshot_time ASC;

Daily Client Growth Across All Tenants

SELECT
  DATE(snapshot_time) as snapshot_date,
  SUM(total_clients) as total_clients_all,
  SUM(unique_contacts) as total_unique_contacts,
  COUNT(DISTINCT customer_id) as active_tenants
FROM dashboard_tenant_metrics_history
WHERE snapshot_time >= NOW() - INTERVAL '30 days'
GROUP BY DATE(snapshot_time)
ORDER BY snapshot_date ASC;

Compare Client Count Change (Current vs 7 Days Ago)

Using view (recommended):

SELECT
  customer_id,
  current_clients,
  clients_7d_ago,
  growth,
  growth_percent
FROM v_dashboard_tenant_growth_7d
ORDER BY growth DESC
LIMIT 20;

Alternative: Without view (manual CTE)

WITH current AS (
  SELECT customer_id, total_clients
  FROM dashboard_tenant_metrics
),
week_ago AS (
  SELECT
    customer_id,
    total_clients
  FROM dashboard_tenant_metrics_history
  WHERE snapshot_time >= NOW() - INTERVAL '7 days' - INTERVAL '1 hour'
    AND snapshot_time <= NOW() - INTERVAL '7 days' + INTERVAL '1 hour'
)
SELECT
  c.customer_id,
  c.total_clients as current_clients,
  w.total_clients as clients_7d_ago,
  c.total_clients - COALESCE(w.total_clients, 0) as growth,
  ROUND(
    (c.total_clients - COALESCE(w.total_clients, 0))::numeric /
    NULLIF(w.total_clients, 0) * 100,
    2
  ) as growth_percent
FROM current c
LEFT JOIN week_ago w ON c.customer_id = w.customer_id
WHERE c.total_clients > 0
ORDER BY growth DESC;

Data Quality Queries

Tenants with Highest Data Quality Issues

SELECT
  customer_id,
  duplicate_push_token_count,
  orphaned_clients,
  total_clients,
  ROUND(
    (duplicate_push_token_count + orphaned_clients)::numeric /
    NULLIF(total_clients, 0) * 100,
    2
  ) as issue_rate_percent
FROM dashboard_tenant_metrics
WHERE total_clients > 0
ORDER BY issue_rate_percent DESC
LIMIT 20;

Tenants with Most Orphaned Clients

SELECT
  customer_id,
  orphaned_clients,
  total_clients,
  ROUND(orphaned_clients::numeric / NULLIF(total_clients, 0) * 100, 2) as orphan_rate_percent
FROM dashboard_tenant_metrics
WHERE total_clients > 0
  AND orphaned_clients > 0
ORDER BY orphan_rate_percent DESC
LIMIT 20;

Aggregate Statistics

Global Statistics Across All Tenants

SELECT
  COUNT(*) as total_tenants,
  SUM(total_clients) as total_clients_all,
  SUM(authenticated_clients) as total_authenticated,
  SUM(clients_with_push_token) as total_with_push_token,
  SUM(unique_contacts) as total_unique_contacts,
  SUM(duplicate_push_token_count) as total_duplicate_tokens,
  SUM(orphaned_clients) as total_orphaned,
  ROUND(AVG(clients_with_push_token::numeric / NULLIF(total_clients, 0)) * 100, 2) as avg_push_token_adoption_percent
FROM dashboard_tenant_metrics
WHERE total_clients > 0;

Tenant Size Distribution

SELECT
  CASE
    WHEN total_clients < 1000 THEN '< 1K'
    WHEN total_clients < 10000 THEN '1K - 10K'
    WHEN total_clients < 100000 THEN '10K - 100K'
    WHEN total_clients < 1000000 THEN '100K - 1M'
    ELSE '> 1M'
  END as client_range,
  COUNT(*) as tenant_count,
  SUM(total_clients) as total_clients_in_range
FROM dashboard_tenant_metrics
WHERE total_clients > 0
GROUP BY
  CASE
    WHEN total_clients < 1000 THEN '< 1K'
    WHEN total_clients < 10000 THEN '1K - 10K'
    WHEN total_clients < 100000 THEN '10K - 100K'
    WHEN total_clients < 1000000 THEN '100K - 1M'
    ELSE '> 1M'
  END
ORDER BY MIN(total_clients);

Contact Distribution Queries

Tenants with Highest Average Clients Per Contact

SELECT
  customer_id,
  unique_contacts,
  total_clients,
  avg_clients_per_contact,
  max_clients_per_contact
FROM dashboard_tenant_metrics
WHERE unique_contacts > 0
ORDER BY avg_clients_per_contact DESC
LIMIT 20;

Tenants with Power Users (Many Clients Per Contact)

SELECT
  customer_id,
  unique_contacts,
  total_clients,
  max_clients_per_contact,
  avg_clients_per_contact
FROM dashboard_tenant_metrics
WHERE max_clients_per_contact > 10  -- Contacts with more than 10 clients
ORDER BY max_clients_per_contact DESC
LIMIT 20;

Stale Data Detection

Tenants with High Stale Client Rates

Using view (recommended):

SELECT
  customer_id,
  alloydb_total_clients,
  alloydb_stale_clients,
  ROUND(
    alloydb_stale_clients::numeric /
    NULLIF(alloydb_total_clients, 0) * 100,
    2
  ) as stale_rate_percent,
  check_time
FROM v_dashboard_latest_sync_status
WHERE alloydb_total_clients > 0
  AND alloydb_stale_clients > 0
ORDER BY stale_rate_percent DESC
LIMIT 20;

Alternative: Without view (manual CTE)

WITH latest_checks AS (
  SELECT
    customer_id,
    MAX(check_time) as latest_check_time
  FROM dashboard_sync_status_history
  GROUP BY customer_id
)
SELECT
  s.customer_id,
  s.alloydb_total_clients,
  s.alloydb_stale_clients,
  ROUND(
    s.alloydb_stale_clients::numeric /
    NULLIF(s.alloydb_total_clients, 0) * 100,
    2
  ) as stale_rate_percent,
  s.check_time
FROM dashboard_sync_status_history s
INNER JOIN latest_checks l
  ON s.customer_id = l.customer_id
  AND s.check_time = l.latest_check_time
WHERE s.alloydb_total_clients > 0
  AND s.alloydb_stale_clients > 0
ORDER BY stale_rate_percent DESC
LIMIT 20;

Combined Health View

Tenant Health Dashboard

Using view (recommended):

SELECT
  customer_id,
  total_clients,
  health_status,
  health_priority,
  orphaned_clients,
  duplicate_push_token_count,
  stale_client_count,
  stale_client_percent,
  sync_status,
  sync_health_score,
  has_significant_drift
FROM v_dashboard_tenant_health
ORDER BY health_priority, total_clients DESC;

Alternative: Without view (manual CTE)

WITH latest_sync AS (
  SELECT
    customer_id,
    MAX(check_time) as latest_check_time
  FROM dashboard_sync_status_history
  GROUP BY customer_id
),
health_data AS (
  SELECT
    m.customer_id,
    m.total_clients,
    m.orphaned_clients,
    m.duplicate_push_token_count,
    s.sync_status,
    s.sync_health_score,
    s.alloydb_stale_clients,
    s.has_significant_drift
  FROM dashboard_tenant_metrics m
  LEFT JOIN dashboard_sync_status_history s ON m.customer_id = s.customer_id
  LEFT JOIN latest_sync l ON s.customer_id = l.customer_id AND s.check_time = l.latest_check_time
)
SELECT
  customer_id,
  total_clients,
  CASE
    WHEN orphaned_clients::numeric / NULLIF(total_clients, 0) > 0.1 THEN 'high_orphan_rate'
    WHEN duplicate_push_token_count > 100 THEN 'high_duplicate_tokens'
    WHEN sync_status = 'critical' THEN 'sync_critical'
    WHEN sync_status = 'warning' THEN 'sync_warning'
    WHEN alloydb_stale_clients::numeric / NULLIF(total_clients, 0) > 0.5 THEN 'high_stale_rate'
    ELSE 'healthy'
  END as health_status,
  orphaned_clients,
  duplicate_push_token_count,
  sync_status,
  sync_health_score,
  has_significant_drift
FROM health_data
WHERE total_clients > 0
ORDER BY
  CASE health_status
    WHEN 'sync_critical' THEN 1
    WHEN 'high_orphan_rate' THEN 2
    WHEN 'high_duplicate_tokens' THEN 3
    WHEN 'sync_warning' THEN 4
    WHEN 'high_stale_rate' THEN 5
    ELSE 6
  END,
  total_clients DESC;

Performance Considerations

Add indexes on frequently queried columns (already included in migration)
Use LIMIT clauses for large result sets
Consider materialized views for expensive aggregate queries
For historical trending, query specific time ranges to optimize performance

Notes

All timestamps are stored with timezone (timestamptz)
The check_time in dashboard_sync_status_history represents when the sync validation was performed
The snapshot_time in dashboard_tenant_metrics_history represents when metrics were aggregated
Historical data is append-only (never overwritten)