# How to Build a KPI Dashboard with Replit

- Tool: How to Build with Replit
- Difficulty: Intermediate
- Compatibility: Replit Free
- Last updated: April 2026

## TL;DR

Build a KPI dashboard in Replit in 1-2 hours. Use Replit Agent to generate an Express + PostgreSQL app that tracks business metrics — revenue, users, conversion rates — with sparkline trend charts, goal tracking, and optional Stripe data import. Deploy on Reserved VM if using scheduled data imports.

## Before you start

- A Replit account (Free plan for development, Core or higher for deployment)
- A list of the 5-10 KPIs you want to track (revenue, users, signups, churn, etc.)
- Optional: Stripe API key if you want to import revenue data automatically
- Optional: Google Analytics API credentials for user/traffic metrics

## Step-by-step guide

### 1. Scaffold the project with Replit Agent

Create a new Repl and use the Agent prompt below to generate the full Express + PostgreSQL KPI dashboard with Drizzle schema, aggregation views, routes, and React frontend.

```
// Type this into Replit Agent:
// Build a KPI dashboard with Express and PostgreSQL using Drizzle ORM.
// Tables:
// - metrics: id serial pk, name text not null, category text not null
//   (enum: revenue/users/engagement/performance), unit text not null
//   (enum: currency/count/percentage/duration), description text, created_at timestamp default now()
// - metric_values: id serial pk, metric_id integer FK metrics, value numeric not null,
//   period_start timestamp not null, period_end timestamp not null,
//   source text, created_at timestamp default now()
// - dashboards: id serial pk, user_id text not null, name text not null,
//   layout jsonb not null default '[]', created_at timestamp default now()
// - metric_targets: id serial pk, metric_id integer FK metrics, target_value numeric not null,
//   period text not null (enum: daily/weekly/monthly/quarterly),
//   start_date timestamp, end_date timestamp
// Create a PostgreSQL view v_metric_summary that for each metric shows:
// current_value (latest metric_value), previous_value (second-latest), percentage_change,
// and rolling_7d_avg.
// Routes: GET /api/metrics, GET /api/metrics/:id/history?from=&to=,
// POST /api/metrics/:id/values, GET /api/dashboards, POST /api/dashboards,
// PUT /api/dashboards/:id, GET /api/import/stripe.
// Use Replit Auth. React frontend with draggable KPI cards grid, sparkline charts
// (recharts), period selector, and a metrics management table. Bind server to 0.0.0.0.
```

> Pro tip: After Agent creates the schema, immediately add 5-10 rows to the metrics table using Drizzle Studio. This lets you test the dashboard layout before any real data is imported.

**Expected result:** A running Express app with all tables, the v_metric_summary view, and a React dashboard with empty KPI cards. The console shows 'KPI Dashboard running on port 5000'.

### 2. Build the metric aggregation view and history endpoint

The v_metric_summary view pre-calculates period-over-period change so the dashboard doesn't run expensive queries on every load. The history endpoint returns time-series data for the chart view.

```
-- Run this SQL in the Replit SQL Editor after tables are created:
CREATE OR REPLACE VIEW v_metric_summary AS
SELECT
  m.id,
  m.name,
  m.category,
  m.unit,
  latest.value AS current_value,
  prev.value AS previous_value,
  CASE
    WHEN prev.value = 0 OR prev.value IS NULL THEN NULL
    ELSE ROUND(((latest.value - prev.value) / ABS(prev.value)) * 100, 2)
  END AS percentage_change,
  rolling.avg_7d AS rolling_7d_avg,
  t.target_value,
  CASE
    WHEN t.target_value IS NULL THEN NULL
    WHEN latest.value >= t.target_value THEN 'on_track'
    WHEN latest.value >= t.target_value * 0.8 THEN 'at_risk'
    ELSE 'behind'
  END AS target_status
FROM metrics m
LEFT JOIN LATERAL (
  SELECT value FROM metric_values WHERE metric_id = m.id ORDER BY period_end DESC LIMIT 1
) latest ON true
LEFT JOIN LATERAL (
  SELECT value FROM metric_values WHERE metric_id = m.id ORDER BY period_end DESC LIMIT 1 OFFSET 1
) prev ON true
LEFT JOIN LATERAL (
  SELECT ROUND(AVG(value), 2) AS avg_7d FROM (
    SELECT value FROM metric_values WHERE metric_id = m.id ORDER BY period_end DESC LIMIT 7
  ) last7
) rolling ON true
LEFT JOIN metric_targets t ON t.metric_id = m.id AND t.period = 'monthly';
```

> Pro tip: The LATERAL join is PostgreSQL-specific and very efficient for this pattern — it runs one small subquery per metric row rather than joining the entire metric_values table.

**Expected result:** SELECT * FROM v_metric_summary returns all metrics with current_value, previous_value, percentage_change, and target_status. Metrics with no values show NULLs.

### 3. Build the Express API routes for metrics and dashboards

The metrics endpoint reads from the view for fast summary cards. The history endpoint accepts date range params for chart data. The dashboards endpoint saves and loads JSONB layout configurations.

```
const express = require('express');
const { db } = require('../db');
const { metrics, metricValues, dashboards } = require('../../shared/schema');
const { eq, and, gte, lte, sql } = require('drizzle-orm');

const router = express.Router();

// GET /api/metrics — summary cards from view
router.get('/metrics', async (req, res) => {
  const summary = await db.execute(sql`SELECT * FROM v_metric_summary ORDER BY category, name`);
  res.json(summary.rows);
});

// GET /api/metrics/:id/history?from=2024-01-01&to=2024-12-31
router.get('/metrics/:id/history', async (req, res) => {
  const { from, to } = req.query;
  const conditions = [eq(metricValues.metricId, parseInt(req.params.id))];
  if (from) conditions.push(gte(metricValues.periodEnd, new Date(from)));
  if (to) conditions.push(lte(metricValues.periodEnd, new Date(to)));

  const history = await db.select({
    value: metricValues.value,
    periodStart: metricValues.periodStart,
    periodEnd: metricValues.periodEnd,
    source: metricValues.source,
  })
  .from(metricValues)
  .where(and(...conditions))
  .orderBy(metricValues.periodEnd);

  res.json(history);
});

// POST /api/metrics/:id/values — manual data entry
router.post('/metrics/:id/values', async (req, res) => {
  const { value, periodStart, periodEnd, source } = req.body;
  const [inserted] = await db.insert(metricValues).values({
    metricId: parseInt(req.params.id),
    value,
    periodStart: new Date(periodStart),
    periodEnd: new Date(periodEnd),
    source: source || 'manual',
  }).returning();
  res.status(201).json(inserted);
});

// PUT /api/dashboards/:id — save layout
router.put('/dashboards/:id', async (req, res) => {
  const [updated] = await db.update(dashboards)
    .set({ layout: req.body.layout, name: req.body.name })
    .where(and(eq(dashboards.id, parseInt(req.params.id)), eq(dashboards.userId, req.user.id)))
    .returning();
  res.json(updated);
});

module.exports = router;
```

**Expected result:** GET /api/metrics returns an array of metric summaries with current_value and percentage_change. GET /api/metrics/1/history?from=2024-01-01 returns the time series for charting.

### 4. Add the Stripe data import route

Store your Stripe secret key in Replit Secrets and call the Stripe API to pull MRR and subscription counts. This route can be triggered manually or called by a Scheduled Deployment every hour.

```
const Stripe = require('stripe');

// GET /api/import/stripe — pull MRR and subscriber count
router.get('/import/stripe', async (req, res) => {
  const stripe = new Stripe(process.env.STRIPE_SECRET_KEY);

  try {
    // Get active subscriptions for MRR calculation
    const subscriptions = await stripe.subscriptions.list({ status: 'active', limit: 100 });

    const mrr = subscriptions.data.reduce((sum, sub) => {
      const monthlyAmount = sub.items.data.reduce((s, item) => {
        const price = item.price;
        const monthly = price.recurring.interval === 'month'
          ? price.unit_amount * item.quantity
          : price.unit_amount * item.quantity / 12;
        return s + monthly;
      }, 0);
      return sum + monthlyAmount;
    }, 0) / 100; // Convert cents to dollars

    const subscriberCount = subscriptions.data.length;
    const now = new Date();
    const periodStart = new Date(now.getFullYear(), now.getMonth(), 1); // First of month

    // Find or create MRR and subscriber count metric IDs
    // (assumes you've created these metrics manually in Drizzle Studio)
    const mrrMetricId = parseInt(process.env.MRR_METRIC_ID);
    const subscriberMetricId = parseInt(process.env.SUBSCRIBER_METRIC_ID);

    if (mrrMetricId) {
      await db.insert(metricValues).values({
        metricId: mrrMetricId,
        value: mrr,
        periodStart,
        periodEnd: now,
        source: 'stripe',
      });
    }

    if (subscriberMetricId) {
      await db.insert(metricValues).values({
        metricId: subscriberMetricId,
        value: subscriberCount,
        periodStart,
        periodEnd: now,
        source: 'stripe',
      });
    }

    res.json({ mrr, subscriberCount, importedAt: now });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});
```

> Pro tip: Add STRIPE_SECRET_KEY and MRR_METRIC_ID to Replit Secrets (lock icon in sidebar). Never hardcode these values. Set up a Scheduled Deployment to call this endpoint every hour for always-fresh data.

### 5. Add the DB retry wrapper and deploy on Reserved VM

The scheduled import job needs a reliable DB connection. Replit's PostgreSQL sleeps after 5 minutes — the retry wrapper handles the wake-up. Deploy on Reserved VM if using setInterval for scheduled imports.

```
// server/db.js — connection pool with retry for idle DB
const { drizzle } = require('drizzle-orm/node-postgres');
const { Pool } = require('pg');

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
  idleTimeoutMillis: 30000,
});

exports.db = drizzle(pool);

// Used before scheduled jobs to ensure DB is awake
exports.pingDb = async () => {
  try {
    await pool.query('SELECT 1');
    return true;
  } catch (err) {
    console.log('DB ping failed, retrying in 2s...');
    await new Promise(r => setTimeout(r, 2000));
    await pool.query('SELECT 1');
    return true;
  }
};

// server/scheduler.js — hourly Stripe import (for Reserved VM)
const { pingDb } = require('./db');
const axios = require('axios');

const INTERVAL_MS = 60 * 60 * 1000; // 1 hour

async function runImport() {
  await pingDb();
  try {
    await axios.get(`http://localhost:5000/api/import/stripe`);
    console.log('Stripe import completed:', new Date().toISOString());
  } catch (err) {
    console.error('Stripe import failed:', err.message);
  }
}

setInterval(runImport, INTERVAL_MS);
runImport(); // Run once on startup
```

> Pro tip: For Reserved VM deployment: require('./scheduler') in server/index.js to start the hourly import. For Autoscale deployment, use a separate Scheduled Deployment that calls GET /api/import/stripe via HTTP instead.

**Expected result:** The app runs on Reserved VM with the scheduler active. Every hour, Stripe MRR data is inserted into metric_values. The dashboard shows updated values on the next page load.

## Complete code example

File: `server/routes/metrics.js`

```javascript
const express = require('express');
const { db } = require('../db');
const { metrics, metricValues, dashboards, metricTargets } = require('../../shared/schema');
const { eq, and, gte, lte, sql, desc } = require('drizzle-orm');

const router = express.Router();

// GET /api/metrics — all metrics with summary from view
router.get('/metrics', async (req, res) => {
  try {
    const result = await db.execute(sql`SELECT * FROM v_metric_summary ORDER BY category, name`);
    res.json(result.rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// GET /api/metrics/:id/history?from=ISO&to=ISO&limit=90
router.get('/metrics/:id/history', async (req, res) => {
  const metricId = parseInt(req.params.id);
  const { from, to, limit = 90 } = req.query;
  const conditions = [eq(metricValues.metricId, metricId)];
  if (from) conditions.push(gte(metricValues.periodEnd, new Date(from)));
  if (to) conditions.push(lte(metricValues.periodEnd, new Date(to)));

  const history = await db.select()
    .from(metricValues)
    .where(and(...conditions))
    .orderBy(desc(metricValues.periodEnd))
    .limit(parseInt(limit));

  res.json(history.reverse());
});

// POST /api/metrics — create a new metric definition
router.post('/metrics', async (req, res) => {
  const { name, category, unit, description } = req.body;
  const [metric] = await db.insert(metrics)
    .values({ name, category, unit, description })
    .returning();
  res.status(201).json(metric);
});

// POST /api/metrics/:id/values — manual data entry
router.post('/metrics/:id/values', async (req, res) => {
  const { value, periodStart, periodEnd, source } = req.body;
  const [inserted] = await db.insert(metricValues).values({
    metricId: parseInt(req.params.id),
    value: parseFloat(value),
    periodStart: new Date(periodStart),
    periodEnd: new Date(periodEnd || new Date()),
    source: source || 'manual',
  }).returning();
  res.status(201).json(inserted);
});

// GET /api/dashboards
router.get('/dashboards', async (req, res) => {
  const rows = await db.select().from(dashboards).where(eq(dashboards.userId, req.user.id));
  res.json(rows);
```

## Common mistakes

- **Running aggregate queries on every dashboard page load instead of using the summary view** — Without pre-aggregation, every KPI card triggers a full metric_values table scan. With 10 metrics and 365 days of daily data, that's 10 expensive queries per page load. Fix: Read all metrics from v_metric_summary in a single query. Only fetch the full time series (from metric_values) when a user clicks on a card to open the chart view.
- **Scheduled import failing silently when the DB is idle** — Replit's PostgreSQL sleeps after 5 minutes. If the scheduled import runs while the DB is idle, the first query throws ECONNREFUSED and the import silently fails. Fix: Call pingDb() at the start of every scheduled job. The ping retries the connection with a 2-second delay, ensuring the first import query succeeds after DB wake-up.
- **Using Autoscale for apps that rely on setInterval for scheduled imports** — Autoscale scales instances to zero when idle. A setInterval defined in the Express process disappears when the instance sleeps, so scheduled imports stop running. Fix: Use Reserved VM for the Express process if you rely on setInterval. Alternatively, use Replit's Scheduled Deployment feature to trigger the import endpoint via HTTP from a separate always-on process.
- **Saving the Stripe secret key as a hardcoded string in the import route** — Any collaborator with read access to your Repl can see the secret key in the source file, leading to unauthorized Stripe API access. Fix: Open the Secrets panel (lock icon in the Replit sidebar), add STRIPE_SECRET_KEY, and access it with process.env.STRIPE_SECRET_KEY in your route handler.

## Best practices

- Store all API keys (Stripe, Google Analytics) in Replit Secrets (lock icon in sidebar) — never hardcode them in routes.
- Use Drizzle Studio to manually add your metric definitions to the metrics table during initial setup, before writing a single line of import code.
- Deploy on Reserved VM if you use setInterval for scheduled imports. Use Autoscale + a separate Scheduled Deployment for the import job if you want cost savings during low traffic.
- Add a unique constraint on (metric_id, period_end) in metric_values to prevent duplicate imports from inserting the same data point twice.
- Build the v_metric_summary PostgreSQL view immediately after creating the tables — it's the single source of truth for the dashboard cards and must reflect the current schema.
- Use the pingDb() helper before every scheduled job to gracefully handle Replit's PostgreSQL idle sleep.
- Limit the time-series chart data to 90 data points by default — more than that makes sparklines unreadable and slows the chart render.

## Frequently asked questions

### Do I need to know SQL to build this dashboard?

No. Replit Agent generates all the SQL, including the v_metric_summary view. If you want to customize the aggregation logic, the Agent prompt explains what each query does in plain English. Drizzle Studio also lets you browse the results visually.

### Can I track metrics that don't come from an API?

Yes. The manual data entry form lets you POST a value with a date range to any metric. This is useful for metrics you track weekly in a spreadsheet, like NPS scores or sales call counts.

### What Replit plan do I need for the scheduled Stripe import?

The Free plan is sufficient for development. For the scheduled import to run reliably in production, you need a paid plan (Core or higher) and either Reserved VM ($10-20/month) or a Scheduled Deployment. Autoscale alone won't keep setInterval running because instances scale to zero.

### How do I handle metrics that update at different frequencies?

Each metric_values row has its own period_start and period_end timestamps. Daily revenue updates daily, weekly NPS updates weekly — both live in the same table. The v_metric_summary view always shows the most recent value regardless of update frequency.

### Can I share the dashboard with my team?

Yes. Add a shared_dashboards table with a share_token column. Generate a UUID token and store it when a user shares their dashboard. A public GET /api/dashboards/shared/:token route returns the layout without requiring authentication.

### How do I prevent duplicate metric values when the import runs multiple times?

Add a unique constraint on (metric_id, period_end) in the metric_values table. When inserting, use INSERT ... ON CONFLICT (metric_id, period_end) DO UPDATE SET value = EXCLUDED.value to upsert instead of inserting a duplicate.

### Can RapidDev build a custom KPI dashboard for my business?

Yes. RapidDev has built 600+ apps including analytics dashboards and business intelligence tools. They can integrate your specific data sources — CRM, ad platforms, databases — and build custom chart types. Book a free consultation at rapidevelopers.com.

### Is the drag-and-drop layout saved permanently?

Yes. The dashboard layout is stored as a JSONB array in the dashboards table, where each element contains the metric_id, widget_type, and grid position. PUT /api/dashboards/:id saves the new layout after every drag event.

---

Source: https://www.rapidevelopers.com/how-to-build-replit/kpi-dashboard
© RapidDev — https://www.rapidevelopers.com/how-to-build-replit/kpi-dashboard