Vivek Kumar

Posted on Jun 19

How to Embed a SQL Dashboard into Your SaaS App (Without Building Everything from Scratch)

#sql #analytics #database #webdev

Your SaaS app manages customer data. A lot of it. Orders, events, usage logs, revenue transactions — all living in your database. But when customers want to see that data, what do you offer them? A CSV export? A table with 15 columns and no way to filter?

If you're nodding uncomfortably, you're not alone. Analytics is often the last thing SaaS teams build — and the first thing customers ask for. The good news is that embedding a SQL-backed dashboard into your app is more straightforward than it sounds. Tools like Draxlr can shortcut a lot of the work. But even if you're rolling your own, you don't need a dedicated BI team. You just need the right architecture.

This guide walks through exactly how to do it: from the database queries that power your charts to the authentication layer that keeps each customer's data isolated.

What "Embedded Dashboard" Actually Means

An embedded dashboard renders analytics inside your application — same URL, same nav, same branding. Your users never know they're looking at a separate system.

Contrast this with:

Exporting to CSV — a workaround, not a feature
Linking to Metabase/Looker — visible context switch, authentication headaches
Building charts in your frontend — viable, but you're now maintaining a BI system

Embedded analytics sits between "roll it all yourself" and "send them to a separate tool." The goal is data that feels native.

Step 1: Know What You're Querying

Before you touch a frontend component, get your SQL right. Let's use a SaaS app with these tables:

-- Core tables
users (id, tenant_id, email, created_at, plan)
events (id, tenant_id, user_id, event_name, occurred_at)
subscriptions (id, tenant_id, status, mrr, started_at, cancelled_at)
invoices (id, tenant_id, amount, status, created_at)

The most common dashboard queries for customer-facing analytics:

Monthly Active Users

SELECT
  DATE_TRUNC('month', occurred_at) AS month,
  COUNT(DISTINCT user_id) AS mau
FROM events
WHERE tenant_id = $1
  AND occurred_at >= NOW() - INTERVAL '6 months'
GROUP BY 1
ORDER BY 1;

Revenue Over Time

SELECT
  DATE_TRUNC('month', created_at) AS month,
  SUM(amount) AS total_revenue
FROM invoices
WHERE tenant_id = $1
  AND status = 'paid'
GROUP BY 1
ORDER BY 1;

Feature Adoption Breakdown

SELECT
  event_name,
  COUNT(*) AS total_events,
  COUNT(DISTINCT user_id) AS unique_users
FROM events
WHERE tenant_id = $1
  AND occurred_at >= NOW() - INTERVAL '30 days'
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10;

Notice WHERE tenant_id = $1 everywhere. That parameter is non-negotiable — it's your data isolation. We'll come back to this.

Step 2: Build Your Data API Layer

Your frontend shouldn't query the database directly. You need an API layer that:

Authenticates the request
Injects the correct tenant_id
Runs the query
Returns the result as JSON

Here's a simple Node.js/Express example:

// GET /api/analytics/mau
app.get('/api/analytics/mau', requireAuth, async (req, res) => {
  const tenantId = req.user.tenantId; // from your auth middleware

  const result = await db.query(`
    SELECT
      DATE_TRUNC('month', occurred_at) AS month,
      COUNT(DISTINCT user_id) AS mau
    FROM events
    WHERE tenant_id = $1
      AND occurred_at >= NOW() - INTERVAL '6 months'
    GROUP BY 1
    ORDER BY 1
  `, [tenantId]);

  res.json(result.rows);
});

The critical thing here: tenantId comes from the authenticated session, not the request body. Never trust the client to tell you which tenant's data to return.

Step 3: Render Charts in Your Frontend

With a clean data API, your frontend just needs to call endpoints and render charts. Chart.js and Recharts are popular choices that don't require a separate BI platform.

Using Recharts in React:

import { LineChart, Line, XAxis, YAxis, Tooltip } from 'recharts';
import { useEffect, useState } from 'react';

export function MAUChart() {
  const [data, setData] = useState([]);

  useEffect(() => {
    fetch('/api/analytics/mau')
      .then(r => r.json())
      .then(rows => setData(rows.map(r => ({
        month: new Date(r.month).toLocaleDateString('en-US', { month: 'short', year: '2-digit' }),
        mau: r.mau
      }))));
  }, []);

  return (
    <LineChart width={600} height={300} data={data}>
      <XAxis dataKey="month" />
      <YAxis />
      <Tooltip />
      <Line type="monotone" dataKey="mau" stroke="#6366f1" strokeWidth={2} />
    </LineChart>
  );
}

This approach gives you full control over styling, but you're responsible for every chart you build.

Step 4: Handle Multi-Tenancy Correctly

If your app serves multiple customers, each customer must see only their data. There are two ways to enforce this — and one of them is wrong.

Wrong approach: filter in the application layer only

// ❌ Don't rely solely on this
const data = await db.query('SELECT * FROM events WHERE tenant_id = $1', [tenantId]);

If a bug slips through — a missing middleware, a copied endpoint that forgets to inject tenantId — a customer could see another customer's data. This is a catastrophic breach.

Right approach: enforce at the database layer with Row-Level Security (PostgreSQL)

-- Create a policy so the DB itself enforces isolation
ALTER TABLE events ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON events
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

Then in your connection setup:

await db.query(`SET app.current_tenant_id = '${tenantId}'`);

Now even a buggy query that forgets the WHERE tenant_id filter will return empty results instead of leaking data. Defense in depth.

Step 5: Authentication for Embedded Views

If you're embedding a dashboard in an iframe (using a third-party tool like Metabase, Draxlr, or similar), you'll need signed embed tokens. The flow looks like this:

1. User logs into your app
2. Your backend generates a signed JWT containing tenant_id and permitted dashboard IDs
3. Frontend receives the signed URL and renders the iframe
4. The embedded platform validates the JWT and scopes all queries to that tenant

A simple signed URL generation in Node.js:

const jwt = require('jsonwebtoken');

function generateEmbedToken(tenantId, dashboardId) {
  return jwt.sign(
    {
      tenant_id: tenantId,
      resource: { dashboard: dashboardId },
      exp: Math.round(Date.now() / 1000) + (60 * 60) // 1 hour
    },
    process.env.EMBED_SECRET
  );
}

// In your route handler:
app.get('/dashboard-embed-url', requireAuth, (req, res) => {
  const token = generateEmbedToken(req.user.tenantId, req.query.dashboardId);
  res.json({ url: `https://analytics.yourplatform.com/embed/dashboard?token=${token}` });
});

Never expose the embed secret on the client side. Tokens should be short-lived and generated server-side.

Build vs. Buy: When Each Makes Sense

This is the question every SaaS team eventually faces. Here's a practical breakdown:

Scenario	Build	Buy
2–3 fixed dashboard views	✅ Chart.js / Recharts	Overkill
Customers want custom reports	Very expensive	✅ Embedded BI tool
Need white-labeling	✅ Full control	✅ Most tools support it
10+ metrics, drill-downs	Months of eng time	✅ Ship faster
Tight performance requirements	✅ Optimize your own queries	Depends on platform

The rule of thumb: if you can enumerate all the charts your customers will ever want, build them yourself. If customers will want to explore their data in ways you can't predict, buy an embedded analytics tool.

Common Mistakes (And How to Avoid Them)

1. Not caching query results

Analytics queries are often expensive. Running a 6-month revenue aggregation on every page load will bring your database to its knees under load. Cache results for 5–15 minutes — your customers won't notice, and your database will thank you.

// Simple cache with node-cache
const cache = new NodeCache({ stdTTL: 300 }); // 5 minutes

app.get('/api/analytics/revenue', requireAuth, async (req, res) => {
  const cacheKey = `revenue:${req.user.tenantId}`;
  const cached = cache.get(cacheKey);
  if (cached) return res.json(cached);

  const result = await db.query(revenueQuery, [req.user.tenantId]);
  cache.set(cacheKey, result.rows);
  res.json(result.rows);
});

2. Overloading your production database

Analytics queries are read-heavy and slow. Running them against your primary write database adds latency for your real-time operations. Ideally: use a read replica for all analytics queries. At minimum: schedule expensive aggregations to run during off-peak hours and store the results.

3. Forgetting empty states

New customers have no data. Your charts will render as empty white boxes and look broken. Always handle the empty case:

if (data.length === 0) {
  return <EmptyState message="No events recorded yet. Data will appear here once your users start engaging." />;
}

4. No date filtering

A chart showing all-time data is rarely what customers want. Add a date range picker from day one. Retrofitting it later is painful.

Key Takeaways

Scope all analytics queries to tenant_id — both in your application layer and at the database level with row-level security policies
Build a clean data API layer between your database and frontend; never expose database credentials or queries to the client
Cache expensive aggregation queries — 5 to 15 minutes of staleness is acceptable for most dashboard metrics
Use read replicas for analytics to protect your primary database's write performance
Build your own charts when you have a fixed, small set of metrics; use an embedded analytics tool when customers need self-service exploration

Analytics is increasingly table-stakes for SaaS. Customers expect to see their data in your product — not in a spreadsheet they export on Fridays.

What's your current approach to embedded dashboards? Are you building with chart libraries, using an embedded BI tool, or still on the "CSV export for now" plan? Share your setup in the comments — especially if you've found a clever solution to multi-tenant data isolation.

DEV Community