Rails Dashboards that scale – with SQL and dry-struct

« Philippe Creux

09 Jul 2025

While we can piece together Reports and Dashboards using ActiveRecord, it can be more efficient to use SQL to transform data and dry-struct to turn query results into Ruby objects with strong typing.

Why Not ActiveRecord?

ActiveRecord is a great ORM for typical CRUD operations. While it offers a handful of methods to aggregate data such as .group, .sum, and .joins, they are not sufficient for complex reporting queries.

That’s where raw SQL with Common Table Expressions (CTEs) shines. If you’re not familiar with CTEs (Common Table Expressions), they’re like temporary views that let you transform data from one shape into another. Here we transform operational data into a format suitable for reporting.

Building a simple report: Readership per Team

Zipline users can send messages with associated tasks to multiple teams and track readership and task completion. In this first report, we want to display how many people read messages, grouped by team.

Let’s use CTEs to compute:

• message_readership: percent of recipients who read each message
• message_team_metrics: readership per communication per team
• team_readership: average readership per team

The transformation flow looks like this:

flowchart TD %% === Tables Group === subgraph Tables direction TB Teams[teams] Messages[messages] MessageTeams[message_teams] end %% === CTEs Group === subgraph CTEs direction TB MessageReadership[message_readership] MessageTeamMetrics[message_team_metrics] TeamReadership[team_readership] end %% === Edges === Messages --> MessageReadership MessageTeams --> MessageTeamMetrics MessageReadership --> MessageTeamMetrics Teams --> TeamReadership MessageTeamMetrics --> TeamReadership TeamReadership --> Results %% === Styling === classDef table fill:#D6F5D6,stroke:#2C662D,color:#1E4D2B,stroke-width:2px; classDef cte fill:#DDEEFF,stroke:#225588,color:#113355,stroke-width:1px,stroke-dasharray: 5; class Messages,Teams,MessageTeams table; class MessageReadership,MessageTeamMetrics,TeamReadership cte;

Here is the SQL query with CTEs:

The resulting rows will hydrate a collection of DryStructs:

With this approach, we write a readable SQL query that hydrates a collection of strongly typed ruby objects. We can then build the view layer using DryStructs which is way easier than manipulating a collection of obscure hashes returned by a long SQL query.

Full Dashboard with Nested Structs

Here is a more complex example where we want a dashboard showing metrics at three levels:

• Overall metrics (e.g. total messages, avg readership)
• Per-message metrics (readership, execution)
• Per-team metrics (aggregated per team)

We’re going to implement this as one SQL query with CTEs to perform transformations in two stages:

1. Staging CTEs to calculate metrics per message x per team
2. Dashboard CTEs where we compute overal, per-message, and per-team metrics.

We then use Postgres json functions to return these as a json document.

flowchart TD %% === Tables Group === subgraph Tables direction TB Teams[Teams] Messages[Messages] Tasks[Tasks] end %% === CTEs Group === subgraph CTEs: Staging direction TB MessageReadership[message_readership] MessageTeams[message_team_metrics] MessageTasks[message_tasks] end subgraph CTEs: Dashboard Sections direction TB MetricsPerMessage[metrics_per_message] OverallMetrics[overall_metrics] MetricsPerTeam[metrics_per_team] end Dashboard["Dashboard (JSON)"] %% === Edges === Messages --> MessageReadership Messages --> MessageTeams Teams --> MessageTeams MessageTasks --> MessageTeams Messages --> MessageTasks Tasks --> MessageTasks %%Messages --> MetricsPerMessage MessageReadership --> MetricsPerMessage %%Teams --> MetricsPerTeam MessageTeams --> MetricsPerTeam MessageReadership --> OverallMetrics MessageTeams --> OverallMetrics OverallMetrics --> Dashboard MetricsPerMessage --> Dashboard MetricsPerTeam --> Dashboard %% === Styling === classDef table fill:#D6F5D6,stroke:#2C662D,color:#1E4D2B,stroke-width:2px; classDef cte fill:#DDEEFF,stroke:#225588,color:#113355,stroke-width:1px,stroke-dasharray: 5; class Messages,Teams,Tasks table; class MessageReadership,MessageTeams,MessageTasks,MetricsPerMessage,OverallMetrics,MetricsPerTeam,Dashboard cte;

Here are the corresponding DryStructs:

Let’s hydrate these Structs with the JSON document returned by Postgres:

We now have a fully structured dashboard in Ruby objects with validated types that we can use in the view layer.

Reuse ActiveRecord Scopes

We can easily inject ActiveRecord scopes in our SQL queries so that we don’t duplicate business logic:

Performance Tips

Such complex SQL queries can be slow, so we often use EXPLAIN to understand what’s causing poor performance. Here are the strategies I use most of the time:

1. Filter early: apply date/user filters in the first CTEs.
2. Duplicate filters across CTEs if needed.
3. Turn CTEs into temporary tables + indexes.

SQL also runs against Data warehouses

Using raw SQL unlocks the possibility of leveraging a data warehouse (Redshift, BigQuery, SnowFlake) to generate reports and dashboards from petabytes of data in seconds.

DryStruct doesn’t care about the data source

One of the best parts of using DryStruct for dashboards is that it decouples the data shape from the data source.

We can hydrate a DryStruct from:

• A live SQL query hitting the database or a data warehouse
• A hand-crafted YAML or JSON blob for testing
• A Faker-powered generator for the demo environment

This is incredibly powerful.

It means we can design and test dashboards without needing access to real data. We can render meaningful samples in staging, ship working demos to sales, and then swap in a real query when we go to production.

It’s also great for writing unit tests — we don’t need to mock a database, we just instantiate structs with test values.

Whether the data comes from petabytes in BigQuery or ten lines of Faker::Lorem, the rest of the app stays the same.

Wrap-Up

You can build high-performance, structured dashboards in Rails using:

• SQL with CTEs
• ActiveRecord scopes (to avoid duplicating filters)
• DryStruct (for type safety and nested hydration)
• JSON blobs (to transport nested metrics)

… and nothing prevents you from fetching data from a Data Warehouse or to inject fake data into your dashboards and reports!

Questions? Comments? 👉 [email protected].

---

You might also be interested in:

• Building a Robust Data Synchronization Framework with Rails
• Organize your Rails codebase with aaa engines
• Handling webhooks in Ruby on Rails
• Snapshot testing with dbt