Startup Dashboard Architecture

TL;DR

• Bottom line: A startup dashboard architecture has five layers — ingestion, storage, transformation, serving, and access control — connected by ELT pipelines that load raw data first and transform in-warehouse.
• Key tool/command: CREATE MATERIALIZED VIEW dashboard_metrics AS SELECT ... — pre-aggregate metrics for sub-3s query performance.
• Watch out for: Exposing production database directly to BI tools — use read replicas or a dedicated analytics schema with RLS.
• Works with: Supabase/PostgreSQL + Metabase (free), BigQuery + Looker Studio (free), Snowflake + Preset (paid).

Constraints

• Use ELT not ETL. Load raw data first, transform in the warehouse. [src5]
• Enforce RBAC at the database layer (RLS), not just the application layer. [src4]
• Dashboard queries must complete in under 3 seconds. Pre-aggregate with materialized views.
• Never expose the production database directly to dashboard tools. [src6]
• Explicitly define data freshness SLA per metric. [src1]

Quick Reference

Decision Tree

START: Choose your architecture tier
├── Data volume < 1M rows AND team has basic SQL?
│   ├── YES → TIER 1: PostgreSQL-native (Supabase + Metabase)
│   └── NO ↓
├── Data volume 1M-100M rows OR need multi-source integration?
│   ├── YES → TIER 2: Warehouse-based (BigQuery/Snowflake + dbt + Metabase)
│   └── NO ↓
├── Data volume > 100M rows OR need real-time streaming?
│   ├── YES → TIER 3: Full data platform (warehouse + streaming + orchestration)
│   └── NO ↓
└── No existing data infrastructure?
    └── Start with TIER 1, migrate when performance degrades

Data freshness:
├── Weekly metrics → Batch: nightly/weekly SQL transforms
├── Daily metrics → Scheduled: hourly/daily dbt runs
└── Real-time metrics → Streaming: Supabase Realtime / Kafka

Step-by-Step Guide

1. Design the Data Model

Separate operational data (transactional) from analytical data (aggregated). Create materialized views for pre-aggregated dashboard metrics with scheduled refresh via pg_cron. [src1]

CREATE MATERIALIZED VIEW daily_metrics AS
SELECT date_trunc('day', created_at) AS day,
  event_type, COUNT(*) AS event_count,
  COUNT(DISTINCT user_id) AS unique_users
FROM events GROUP BY 1, 2;

SELECT cron.schedule('refresh-daily-metrics',
  '0 2 * * *', 'REFRESH MATERIALIZED VIEW CONCURRENTLY daily_metrics');

Verify: Schema supports all command center KPIs. Materialized views cover frequent queries.

2. Build the Ingestion Layer

Connect external data sources using ELT: load raw data first, transform after. Use webhooks for real-time sources (Stripe), REST API polling for CRM/analytics, and client SDKs for product events. [src3] [src5]

Verify: All data sources connected, raw data landing in staging tables at expected frequency.

3. Build the Transformation Layer

Transform raw data into dashboard-ready metrics. Use simple views for real-time low-complexity queries, materialized views for expensive aggregations, and dbt models for complex multi-source business logic. [src5]

Verify: All KPIs computable from transformation layer. Query time < 3 seconds.

4. Implement Access Control

Enforce data security using PostgreSQL Row Level Security (RLS) with role-based policies. Create separate roles for CEO, department heads, managers, board, and all-hands with appropriate data visibility. [src4]

ALTER TABLE revenue_details ENABLE ROW LEVEL SECURITY;
CREATE POLICY dept_isolation ON revenue_details
  FOR SELECT USING (department = current_setting('app.user_department'));

Verify: Each access tier tested, RLS policies enforced, no data leakage.

5. Set Up the Serving Layer

Connect transformation layer to dashboard tools. For free: Supabase + Metabase with a dedicated read-only user. For custom: API endpoints with caching. [src6]

Verify: Dashboard tool connected, all metrics rendering, query performance < 3 seconds.

6. Configure Monitoring and Alerting

Monitor data freshness, row count anomalies, materialized view refresh status, query performance, and storage growth. Alert on stale data (> 2x expected interval), count drops (> 50%), and refresh failures. [src3]

Verify: Monitoring queries running, alert channels configured, test alert sent.

Code Examples

PostgreSQL: Complete Dashboard Schema

-- Pre-aggregated KPI snapshot for sub-3s queries
CREATE SCHEMA IF NOT EXISTS analytics;
CREATE MATERIALIZED VIEW analytics.kpi_snapshot AS
SELECT CURRENT_DATE AS snapshot_date,
  (SELECT SUM(amount)/100 FROM subscriptions
   WHERE status='active') AS mrr,
  (SELECT COUNT(*) FROM users
   WHERE created_at >= CURRENT_DATE - 30) AS new_users_30d,
  (SELECT COUNT(DISTINCT user_id) FROM events
   WHERE created_at >= CURRENT_DATE - 1) AS dau;
SELECT cron.schedule('kpi-refresh', '0 * * * *',
  'REFRESH MATERIALIZED VIEW CONCURRENTLY analytics.kpi_snapshot');

JavaScript: Real-Time Dashboard Subscriber

// Subscribe to real-time metric changes via Supabase
import { createClient } from '@supabase/supabase-js';
const supabase = createClient(SUPABASE_URL, SUPABASE_KEY);
const channel = supabase
  .channel('dashboard-metrics')
  .on('postgres_changes',
    { event: 'INSERT', schema: 'public', table: 'events' },
    (payload) => updateDashboard(payload.new))
  .subscribe();

Anti-Patterns

Wrong: Querying production database for dashboards

Running analytical queries against the production transactional database. Dashboard queries compete with application queries, degrading both. [src6]

Correct: Separate analytics layer

Use read replicas, a dedicated analytics schema, or materialized views. Production handles transactions; analytics handles dashboard queries.

Wrong: ETL with premature transformation

Transforming data during extraction before loading. When requirements change, source data is gone and the pipeline must be rebuilt.

Correct: ELT — load raw, transform in warehouse

Load raw data first. Transform using SQL views or dbt models. Raw data is always available for new analyses. [src5]

Wrong: Application-layer-only access control

RBAC only in frontend or API code. Anyone with a database connection bypasses all restrictions.

Correct: Database-layer RLS + application RBAC

Enforce RLS at PostgreSQL level. Database prevents unauthorized access even if application is bypassed. [src4]

Common Pitfalls

• Materialized views not refreshed: Dashboard shows stale data because refresh cron failed silently. Fix: monitor refresh job status, alert on failure. [src3]
• No data freshness indicator: Users trust numbers without knowing data age. Fix: display "last updated" derived from actual data, not refresh schedule.
• Over-engineering early: Full Airflow + Snowflake + dbt for 10-person startup with 100K rows. Fix: start with PostgreSQL views and Metabase. [src1]
• Missing data validation: Dashboard shows impossible values. Fix: add CHECK constraints and validation triggers at database level.
• Single point of failure: One pipeline failure takes down the entire dashboard. Fix: separate critical metric pipelines from nice-to-have metrics. [src3]

When to Use / When Not to Use

Important Caveats

• Start with simplest architecture (Tier 1: PostgreSQL + Metabase) and add complexity only when performance demands it
• Materialized view refresh rewrites the entire view — for large datasets, consider incremental materialization
• Supabase free tier has connection limits (50 concurrent) and storage limits (500MB) that may be insufficient for analytics
• Real-time dashboards via WebSocket cost more than polling — use real-time only for metrics that genuinely require it