
Building a Scalable Healthcare Analytics Dashboard
Eliminated manual report compilation for hospital executives, reducing the time to generate operational reports by 95% and transforming days of manual effort into seconds of real-time visibility. NavPoint Health is an enterprise healthcare analytics dashboard that consolidates fragmented data from 6+ disparate hospital systems into a single, intuitive interface.
By replacing a patchwork of spreadsheets and legacy reports with automated, real-time analytics, the platform empowers hospital administrators to make data-driven decisions regarding patient flow, resource utilization, and financial performance without the 'paperwork tax' of manual data entry.
Built to serve multi-hospital health systems, the platform handles the immense complexity of data fragmentation across different EHR vendors, providing a unified source of truth while maintaining strict HIPAA compliance and role-based data isolation.
Large healthcare systems suffer from extreme data fragmentation. With each hospital operating its own EHR, billing, and scheduling software, getting a unified view of operations required extracting data from 6+ silos and manually consolidating it into spreadsheets, a process that took executives 8-12 hours per week.
The 'Information Gap' meant that critical operational questions, such as current ED crowding or average wait times, could not be answered in real-time. Decisions were often made based on outdated data or anecdotal reports from department heads, leading to inefficient resource allocation and increased patient boarding times.
Existing BI tools like Tableau were insufficient because they required dedicated analysts to maintain custom dashboards and lacked the ability to pull real-time operational data without expensive, custom-built integration layers for every new source.
The primary technical barrier was the lack of a unified query layer. There was no consistent data model across the health system, meaning that a 'discharge' in one hospital was recorded differently in another, making system-wide analytics mathematically impossible without a robust normalization layer.
Business Goals
- Eliminate manual report compilation by automating data aggregation from all hospital systems
- Provide hospital executives with real-time operational visibility across all facilities
- Reduce the time to answer operational questions from days to seconds
- Enable data-driven decision-making with historical trend analysis and predictive alerts
Technical Goals
- Build a unified GraphQL API layer that abstracts data from 6+ backend systems
- Achieve sub-second query performance for dashboard views that aggregate data across hospitals
- Support real-time data updates through GraphQL subscriptions for operational metrics
- Design a schema that can accommodate new data sources without breaking existing queries
- Ensure 99.9% uptime for the dashboard during business hours (6 AM - 8 PM)
User Goals
- Hospital executives need a executive summary view with at-a-glance KPIs and drill-down capability
- Department heads need detailed views of their specific metrics with comparison to targets
- Clinical managers need real-time patient flow data (census, admissions, discharges, transfers)
- All users need role-appropriate access - a department head should not see other departments' financials
Functional
Non‑Functional
Performance
- • Dashboard load time under 2 seconds for the executive summary view
- • Chart rendering under 500ms for any single metric visualization
- • GraphQL queries returning aggregated data across 6 hospitals in under 1 second
- • Data freshness within 5 minutes of source system updates
Security
- • HIPAA-compliant data handling with role-based access controls
- • SSO integration with existing hospital identity providers (Okta, Azure AD)
- • Row-level security in database queries ensuring users only see authorized data
- • Audit logging of all data access with drill-down to individual record views
- • Data encryption at rest and in transit
Scalability
- • Support for up to 500 concurrent users during peak hours (morning executive reviews)
- • Graceful handling of upstream system latency without dashboard degradation
- • Caching layer that survives upstream system outages
Maintainability
- • GraphQL schema documentation with auto-generated GraphiQL explorer
- • Integration test suite for each upstream data source connection
- • Automated monitoring of data freshness from each source system
- • Feature flags for gradual rollout of new dashboard views
System Flow
- 1The React frontend renders the dashboard and sends GraphQL queries for the required metrics. Each query specifies exactly which fields are needed, avoiding the over-fetching problem common with REST APIs.
- 2The GraphQL API gateway receives the query and resolves each field through its resolver functions. Resolvers check the Redis cache first for frequently accessed data, then query PostgreSQL for aggregated metrics, and fall back to live upstream API calls for real-time data.
- 3A background data pipeline runs every 5 minutes, extracting data from each hospital system (EHR, billing, scheduling), transforming it into the unified data model, and loading it into PostgreSQL. This ETL process handles data normalization, unit conversion, and deduplication.
- 4For real-time metrics (current ED census, bed availability), the system uses GraphQL subscriptions over WebSocket. When the data pipeline completes a refresh cycle, it pushes updates to subscribed clients through the WebSocket connection.
- 5The frontend uses TanStack Query for client-side caching and optimistic updates. When a user applies a filter, the query is re-fetched from the GraphQL API, but previous data is shown during loading for a smooth experience.
Request Flow
- 1An executive opens the NavPoint Health dashboard. The React frontend sends a GraphQL query requesting the executive summary: current census, ED wait times, bed occupancy, and today's revenue across all hospitals.
- 2The GraphQL gateway resolves the query by executing resolver functions for each field. The `currentCensus` resolver queries the `fact_patient_flow` table for admissions minus discharges today, grouped by hospital.
- 3The `todayRevenue` resolver queries the pre-aggregated `metrics` table for revenue data with today's date. This query returns in under 50ms because the data is pre-computed during the last ETL cycle.
- 4All resolver results are assembled into the GraphQL response and returned as a single JSON payload matching the query structure. The frontend receives exactly the data it requested - no more, no less.
- 5TanStack Query caches the response for 5 minutes. If the executive navigates away and back within 5 minutes, the dashboard renders instantly from cache while a background refetch updates the data.
Deployment Flow
- 1The React frontend is deployed as a static site on Vercel with global CDN distribution. The GraphQL API runs on a Kubernetes cluster (Amazon EKS) for auto-scaling based on query load.
- 2The PostgreSQL data warehouse runs on Amazon RDS with read replicas for dashboard queries and the primary instance for ETL writes. Read replicas ensure dashboard performance is not impacted by ETL load.
- 3The data pipeline runs as scheduled jobs on Amazon ECS Fargate, triggered every 5 minutes by CloudWatch Events. Each ETL job runs as a stateless container that processes one source system's data.
- 4Kubernetes HPA (Horizontal Pod Autoscaler) scales the GraphQL API replicas based on CPU utilization and request latency. During peak morning hours (7-9 AM), the cluster scales from 3 to 12 replicas.
- 5Deployment follows a canary release strategy: new versions are rolled out to 10% of traffic first, monitored for errors and latency, then gradually expanded to 100%.
Database Design
- PostgreSQL serves as the query-optimized data warehouse, storing pre-aggregated metrics at multiple granularities: hourly, daily, and monthly. This star-schema design enables sub-second query performance for dashboard views that would otherwise scan millions of source records.
- The `metrics` table stores numerical KPI values with dimensions: `metric_name`, `hospital_id`, `department_id`, `timestamp`, and `value`. A separate `metric_dimensions` table defines the metadata for each metric: unit, display name, target value, and alert thresholds.
- The `fact_patient_flow` table records patient movement events (admission, discharge, transfer) with timestamps, enabling real-time census calculations through SQL window functions.
- Materialized views pre-compute the most common dashboard queries: daily census by hospital, weekly revenue trends, and monthly quality metrics. These views refresh after each ETL cycle.
- The schema uses PostgreSQL table partitioning by month for event data, keeping query performance predictable as data accumulates over years.
Architectural Decisions
GraphQL vs. REST for the API layer
I evaluated REST but found it too rigid for a dashboard with dozens of dynamic views. Each view required a different combination of metrics, which would have led to a proliferation of custom endpoints. I implemented GraphQL to allow each view to request exactly the data it needs, reducing over-fetching and providing a strongly-typed schema that served as living documentation for the frontend team.
Background ETL vs. real-time federation queries
We prototyped federated queries to source systems, but the latency was unpredictable; some EHR queries took 10+ seconds or timed out entirely. I pivoted to a periodic ETL pipeline to decouple dashboard performance from upstream system instability. While this introduced a 5-minute data lag, it guaranteed sub-second query performance for executives, which was the primary success metric.
Materialized views vs. live aggregation
Live aggregation of millions of records was too slow for an executive dashboard. I implemented incrementally refreshable materialized views (PostgreSQL 16) to pre-compute the 20 most common KPIs. This reduced the most frequent query times from 3-5 seconds down to under 50ms, ensuring the dashboard felt instantaneous.
TanStack Query vs. Apollo Client for GraphQL
I chose TanStack Query over Apollo Client to avoid being locked into a GraphQL-only caching layer. Since the application had to handle a mix of GraphQL and legacy REST fallbacks, TanStack Query provided a unified hook API that handled both sources with the same caching and deduplication logic, simplifying the frontend architecture.
GraphQL provides a single API endpoint that serves all dashboard views with precise data shapes. The strongly typed schema eliminates the documentation drift problem common with REST APIs - the schema is always the source of truth. Apollo Studio provides schema validation and performance tracing for production queries.
Trade-off
React provides the component model for building composable dashboard widgets. TypeScript ensures type safety between the GraphQL schema and the frontend components - we used GraphQL Code Generator to auto-generate TypeScript types from the schema, eliminating manual type definitions for API responses.
Trade-off
PostgreSQL was chosen as the data warehouse for its advanced analytics features: window functions for running calculations like moving averages, CTEs for readable query structures, table partitioning for time-series data, and materialized views for pre-computed aggregations. These features eliminate the need for a separate OLAP database at the current scale.
Trade-off
Containerization ensures the GraphQL API runs identically in development, staging, and production. Kubernetes provides auto-scaling based on CPU utilization and request latency, which is essential for handling the morning peak when hospital executives check their dashboards simultaneously.
Trade-off
Real-Time Patient Flow Dashboard
Problem
Hospital administrators had no real-time visibility into patient flow across the health system. ED crowding, boarding (admitted patients waiting in the ED for an inpatient bed), and ambulance diversion were identified after the fact - too late for proactive intervention.
Solution
We built a live patient flow dashboard that displays current census, admissions, discharges, and transfers for each hospital. The dashboard updates every 5 minutes through the ETL pipeline and provides color-coded alerts when metrics exceed configurable thresholds (e.g., ED census > 120% of capacity turns red).
Engineering Challenges
The hardest challenge was reconciling patient movement data across different EHR systems. One hospital calls it 'Discharge,' another calls it 'Check-out,' and a third uses 'Transfer to external facility.' We built a normalization layer that maps each system's terminology to a unified event model. This mapping required 200+ rules and collaboration with clinical informatics teams from each hospital.
Multi-Dimensional Report Builder
Problem
Hospital executives needed the ability to create custom reports combining metrics from different domains - for example, 'Show me readmission rates by department, compared to budget, for the last 6 months, filtered by payer type.' Existing tools required IT assistance to build each report.
Solution
We built a drag-and-drop report builder that allows users to select metrics, dimensions, date ranges, and filters from the GraphQL schema. The builder generates a GraphQL query on the fly and renders the results as configurable visualizations (bar charts, line charts, tables, heatmaps). Saved reports appear on the user's dashboard.
Engineering Challenges
Making the report builder intuitive for non-technical users while supporting complex queries was a significant UX challenge. A simple report (revenue by month) should be 3 clicks, while an advanced report (readmission rate by diagnosis code, stratified by age group, with year-over-year comparison) required a guided wizard interface. We iterated through 4 UX prototypes with real hospital administrators before landing on the right balance.
Data inconsistency across hospital systems
Different hospitals in the health system use different EHR vendors, and even hospitals using the same vendor have customized their configurations. 'Discharge date' means different things: the date the physician wrote the order, the date the patient left the room, or the date the bed was cleaned. We built a data quality layer that runs validation rules on incoming data and flags anomalies. When the data pipeline detects an inconsistency (e.g., negative length of stay), it quarantines the record and alerts the data engineering team.
Trade-off
Handling upstream system outages gracefully
When a hospital's EHR system goes down, the ETL pipeline for that source fails. We implemented a circuit breaker pattern: if a source system fails 3 consecutive ETL cycles, the data pipeline marks that source as degraded and continues processing other sources. The dashboard shows the most recent available data for the degraded source, with a yellow warning banner indicating stale data. When the source recovers, the ETL processes the missed cycles in catch-up mode.
Trade-off
GraphQL query performance with deep nesting
Executives viewing department-level drill-downs triggered GraphQL queries with 5+ levels of nesting (hospital → department → metric → time series → comparison). These queries took 8-12 seconds to resolve. We implemented a query complexity analysis middleware that estimates query cost before execution. Queries exceeding a complexity threshold are rejected with a suggestion to narrow the scope. We also added DataLoader for automatic batching of resolver calls.
Trade-off
- ▸GraphQL DataLoader reduced hospital→department→metric queries from N+1 (1 + 6 + 48 = 55 queries) to 3 queries through automatic batching.
- ▸PostgreSQL materialized views pre-compute the 20 most common dashboard queries, reducing query time from 3-5 seconds to under 50ms.
- ▸Redis caching of metric metadata (names, units, thresholds) eliminates database lookups for repeated queries, reducing average response time by 35%.
- ▸Frontend component virtualization (TanStack Virtual) for department lists and time-series tables reduced DOM nodes by 90% on pages with >500 data points.
- ▸GraphQL persisted queries reduce request size by 60% and enable CDN caching of query results for anonymous dashboard views.
- ▸The data pipeline processes 500,000 events per cycle (5-minute intervals) with an end-to-end latency of under 4 minutes from source to dashboard.
- ▸Authentication uses the health system's existing SSO provider (Okta) through SAML 2.0 integration. Users log in with their existing hospital credentials.
- ▸Authorization uses a role-permission model: System Admin (full access), Hospital Admin (their hospital only), Department Head (their department only), and Viewer (read-only). Each role is enforced at three layers: GraphQL resolver, database RLS, and frontend route guards.
- ▸Row-level security (RLS) in PostgreSQL ensures that even if a query bypasses the application layer, users can only access data for their authorized hospitals and departments.
- ▸All data access is logged with user ID, timestamp, query, and response metadata. A quarterly audit review process identifies unusual access patterns.
- ▸The dashboard enforces a 15-minute idle session timeout. For shared workstations in hospital administrative areas, this prevents unauthorized access when a user forgets to log out.
- ▸Patient-level data is only accessible through the drill-down views, and access is restricted to clinicians with a direct treatment relationship. All other dashboard views work with aggregated, de-identified metrics.
- 1Data normalization across hospital systems is the hardest problem in healthcare analytics. We underestimated the effort required by 3x. The data mapping rules grew from an expected 50 rules to 200+ rules, and each rule required domain expert validation.
- 2GraphQL's flexibility is both a strength and a risk. The 'ask for anything' capability led to frontend queries that were unnecessarily complex and slow. Adding query complexity analysis early in the project would have prevented performance issues that needed refactoring later.
- 3Hospital executives prefer consistent, predictable data over real-time but inconsistent data. During user testing, they consistently chose the 5-minute old data that was accurate over fresher data that sometimes had normalization errors. This validated our ETL-first approach over live federation.
- 4Building for a health system with 6 hospitals is fundamentally different from building for a single hospital. Cross-hospital comparisons, standardized metrics, and role-based access across facilities add complexity at every layer of the stack.
- 5Charts and visualizations require healthcare-specific design patterns. A line chart showing monthly revenue is straightforward. A chart showing readmission rates with risk-adjusted expected ranges, national benchmarks, and statistical significance bands requires healthcare domain expertise in the visualization design.
Roadmap
- 1Implement predictive analytics: using historical data to forecast patient volume, staffing needs, and supply demand. Machine learning models could predict ED surges 24 hours in advance based on weather data, historical patterns, and community health trends.
- 2Add natural language query support: executives should be able to type 'Show me readmission rates by hospital for the last quarter' and get the visualization without navigating the report builder.
- 3Integrate with more hospital systems: OR scheduling, pharmacy inventory, lab turnaround times, and imaging utilization. Each new source adds dimensions to the analytics platform.
- 4Build a mobile app for hospital administrators who need to check metrics on-call from their phones. The mobile view would show critical alerts and a condensed KPI summary.
- 5Implement automated insight generation: AI-powered analysis that surfaces notable trends, anomalies, and opportunities without manual exploration of the dashboard.
- 6Expand to multi-health-system support, allowing the platform to serve as a benchmarking tool where anonymized, aggregated metrics can be compared across health systems.
Want to discuss this project?
I am always happy to talk about engineering decisions, architecture, or how I can help with your next project.

