Refresh reporting views

Outcome

The 6 (+ EVV) materialized views in the reporting schema stay fresh enough for the dashboards to be trustworthy, with manual refresh available for incident recovery.

Prerequisites

• reports.write for the manual REST endpoint.
• DB access to inspect pg_matviews for freshness.

Views in the reporting schema

Manual refresh

SELECT reporting.fn_refresh_reporting_views();

This refreshes concurrently (no read locks):

• reporting.mv_ar_aging_by_payer
• reporting.mv_claim_status_summary
• reporting.mv_denial_rate_by_category
• reporting.mv_revenue_by_facility_month
• reporting.mv_authorization_utilization
• reporting.mv_clean_claim_rate

EVV compliance is non-concurrent (a separate refresh path) — see fn_refresh_evv_compliance.

Day-to-day

Views refresh via ReportingService.refreshAll() on every claim-status change, so operators normally don't need to touch them. Use the manual refresh when:

• Seeding demo data.
• Recovering from a stuck aggregator.
• Right after a bulk import where you want immediate dashboard accuracy.

The REST endpoint:

curl -X POST -H "Authorization: Bearer $TOKEN" \
  $RCM_CORE_URL/api/v1/ledger/reporting/refresh

(reports.write required.)

Recommended schedule

If event-driven refresh is disabled or unreliable, fall back to:

View	Frequency	Rationale
AR aging	Daily (off-peak)	Financial reporting accuracy
Claim status	Every 4 hours	Operational dashboard
Denial rate	Daily	Denial trend monitoring
Revenue by facility	Daily	Financial reporting
Auth utilization	Every 4 hours	Authorization tracking
Clean claim rate	Daily	Quality metrics

Verify freshness

SELECT schemaname, matviewname, last_refresh
  FROM pg_catalog.pg_matviews
 WHERE schemaname = 'reporting'
 ORDER BY matviewname;

last_refresh lag	Severity
< 5 min	Healthy (event-driven path is working)
5 min – 1 h	Watch — likely a quiet period
> 1 h sustained	Investigate — refreshAll() may be stuck

Validation

Check	Expected
Manual refresh succeeds	No exceptions
pg_matviews.last_refresh	Within recommended window
Dashboard widgets	Reflect recent activity

Troubleshooting

Symptom	Cause	Fix
Refresh fails with concurrent refresh requires unique index	View dropped or index missing	Re-run the migration that created the view.
Dashboards show stale data despite recent activity	Event-driven path stalled	Manual refresh; check rcm-core logs for refreshAll errors.
Refresh blocks for minutes	Unusually large data set	Run during off-peak; for very large tables, consider partitioning.

Cross-references

• Dashboard + scheduled email — the consumer of these views.
• Drill-through & per-widget RBAC.

Next

7.4 — Dashboard reporting + scheduled email