How to Build a KPI Dashboard with Replit

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

1-- Run this SQL in the Replit SQL Editor after tables are created:
2CREATE OR REPLACE VIEW v_metric_summary AS
3SELECT
4  m.id,
5  m.name,
6  m.category,
7  m.unit,
8  latest.value AS current_value,
9  prev.value AS previous_value,
10  CASE
11    WHEN prev.value = 0 OR prev.value IS NULL THEN NULL
12    ELSE ROUND(((latest.value - prev.value) / ABS(prev.value)) * 100, 2)
13  END AS percentage_change,
14  rolling.avg_7d AS rolling_7d_avg,
15  t.target_value,
16  CASE
17    WHEN t.target_value IS NULL THEN NULL
18    WHEN latest.value >= t.target_value THEN 'on_track'
19    WHEN latest.value >= t.target_value * 0.8 THEN 'at_risk'
20    ELSE 'behind'
21  END AS target_status
22FROM metrics m
23LEFT JOIN LATERAL (
24  SELECT value FROM metric_values WHERE metric_id = m.id ORDER BY period_end DESC LIMIT 1
25) latest ON true
26LEFT JOIN LATERAL (
27  SELECT value FROM metric_values WHERE metric_id = m.id ORDER BY period_end DESC LIMIT 1 OFFSET 1
28) prev ON true
29LEFT JOIN LATERAL (
30  SELECT ROUND(AVG(value), 2) AS avg_7d FROM (
31    SELECT value FROM metric_values WHERE metric_id = m.id ORDER BY period_end DESC LIMIT 7
32  ) last7
33) rolling ON true
34LEFT JOIN metric_targets t ON t.metric_id = m.id AND t.period = 'monthly';

1const express = require('express');
2const { db } = require('../db');
3const { metrics, metricValues, dashboards } = require('../../shared/schema');
4const { eq, and, gte, lte, sql } = require('drizzle-orm');
5
6const router = express.Router();
7
8// GET /api/metrics — summary cards from view
9router.get('/metrics', async (req, res) => {
10  const summary = await db.execute(sql`SELECT * FROM v_metric_summary ORDER BY category, name`);
11  res.json(summary.rows);
12});
13
14// GET /api/metrics/:id/history?from=2024-01-01&to=2024-12-31
15router.get('/metrics/:id/history', async (req, res) => {
16  const { from, to } = req.query;
17  const conditions = [eq(metricValues.metricId, parseInt(req.params.id))];
18  if (from) conditions.push(gte(metricValues.periodEnd, new Date(from)));
19  if (to) conditions.push(lte(metricValues.periodEnd, new Date(to)));
20
21  const history = await db.select({
22    value: metricValues.value,
23    periodStart: metricValues.periodStart,
24    periodEnd: metricValues.periodEnd,
25    source: metricValues.source,
26  })
27  .from(metricValues)
28  .where(and(...conditions))
29  .orderBy(metricValues.periodEnd);
30
31  res.json(history);
32});
33
34// POST /api/metrics/:id/values — manual data entry
35router.post('/metrics/:id/values', async (req, res) => {
36  const { value, periodStart, periodEnd, source } = req.body;
37  const [inserted] = await db.insert(metricValues).values({
38    metricId: parseInt(req.params.id),
39    value,
40    periodStart: new Date(periodStart),
41    periodEnd: new Date(periodEnd),
42    source: source || 'manual',
43  }).returning();
44  res.status(201).json(inserted);
45});
46
47// PUT /api/dashboards/:id — save layout
48router.put('/dashboards/:id', async (req, res) => {
49  const [updated] = await db.update(dashboards)
50    .set({ layout: req.body.layout, name: req.body.name })
51    .where(and(eq(dashboards.id, parseInt(req.params.id)), eq(dashboards.userId, req.user.id)))
52    .returning();
53  res.json(updated);
54});
55
56module.exports = router;

1const Stripe = require('stripe');
2
3// GET /api/import/stripe — pull MRR and subscriber count
4router.get('/import/stripe', async (req, res) => {
5  const stripe = new Stripe(process.env.STRIPE_SECRET_KEY);
6
7  try {
8    // Get active subscriptions for MRR calculation
9    const subscriptions = await stripe.subscriptions.list({ status: 'active', limit: 100 });
10
11    const mrr = subscriptions.data.reduce((sum, sub) => {
12      const monthlyAmount = sub.items.data.reduce((s, item) => {
13        const price = item.price;
14        const monthly = price.recurring.interval === 'month'
15          ? price.unit_amount * item.quantity
16          : price.unit_amount * item.quantity / 12;
17        return s + monthly;
18      }, 0);
19      return sum + monthlyAmount;
20    }, 0) / 100; // Convert cents to dollars
21
22    const subscriberCount = subscriptions.data.length;
23    const now = new Date();
24    const periodStart = new Date(now.getFullYear(), now.getMonth(), 1); // First of month
25
26    // Find or create MRR and subscriber count metric IDs
27    // (assumes you've created these metrics manually in Drizzle Studio)
28    const mrrMetricId = parseInt(process.env.MRR_METRIC_ID);
29    const subscriberMetricId = parseInt(process.env.SUBSCRIBER_METRIC_ID);
30
31    if (mrrMetricId) {
32      await db.insert(metricValues).values({
33        metricId: mrrMetricId,
34        value: mrr,
35        periodStart,
36        periodEnd: now,
37        source: 'stripe',
38      });
39    }
40
41    if (subscriberMetricId) {
42      await db.insert(metricValues).values({
43        metricId: subscriberMetricId,
44        value: subscriberCount,
45        periodStart,
46        periodEnd: now,
47        source: 'stripe',
48      });
49    }
50
51    res.json({ mrr, subscriberCount, importedAt: now });
52  } catch (err) {
53    res.status(500).json({ error: err.message });
54  }
55});

1// server/db.js — connection pool with retry for idle DB
2const { drizzle } = require('drizzle-orm/node-postgres');
3const { Pool } = require('pg');
4
5const pool = new Pool({
6  connectionString: process.env.DATABASE_URL,
7  max: 10,
8  idleTimeoutMillis: 30000,
9});
10
11exports.db = drizzle(pool);
12
13// Used before scheduled jobs to ensure DB is awake
14exports.pingDb = async () => {
15  try {
16    await pool.query('SELECT 1');
17    return true;
18  } catch (err) {
19    console.log('DB ping failed, retrying in 2s...');
20    await new Promise(r => setTimeout(r, 2000));
21    await pool.query('SELECT 1');
22    return true;
23  }
24};
25
26// server/scheduler.js — hourly Stripe import (for Reserved VM)
27const { pingDb } = require('./db');
28const axios = require('axios');
29
30const INTERVAL_MS = 60 * 60 * 1000; // 1 hour
31
32async function runImport() {
33  await pingDb();
34  try {
35    await axios.get(`http://localhost:5000/api/import/stripe`);
36    console.log('Stripe import completed:', new Date().toISOString());
37  } catch (err) {
38    console.error('Stripe import failed:', err.message);
39  }
40}
41
42setInterval(runImport, INTERVAL_MS);
43runImport(); // Run once on startup