---
# === IDENTITY ===
id: software/startup-dashboard/startup-dashboard-architecture/2026
canonical_question: "How do I architect a startup dashboard — data flow between phases, storage, access control, system design?"
aliases:
  - "startup metrics dashboard system architecture"
  - "analytics dashboard data pipeline design"
  - "dashboard backend architecture for startups"
  - "real-time metrics infrastructure design"
entity_type: software_reference
domain: software > startup-dashboard > startup-dashboard-architecture
region: global
jurisdiction: global
temporal_scope: 2025-2026

# === VERIFICATION ===
last_verified: 2026-03-13
confidence: 0.87
version: 1.0
first_published: 2026-03-13

# === TEMPORAL VALIDITY ===
temporal_validity:
  status: evolving
  last_breaking_change: "2025 — ELT pattern dominates over ETL for startups; Supabase Realtime and edge functions enable lower-cost real-time pipelines; embedded analytics tools (Metabase, Preset) reduce build-vs-buy threshold"
  next_review: 2026-09-09
  change_sensitivity: high

# === CONSTRAINTS ===
constraints:
  - "Start with ELT not ETL — load raw data first, transform in the warehouse; premature transformation loses source data and limits future analysis"
  - "Role-based access control must be enforced at the data layer (RLS), not just the application layer — application-layer-only RBAC is bypassable"
  - "Dashboard queries must complete in under 3 seconds — pre-aggregate or materialize views for any query exceeding this threshold"
  - "Never expose production database directly to dashboard tools — use read replicas or dedicated analytics database"
  - "Data freshness SLA must be explicitly defined per metric — mixing real-time and batch data without labeling freshness misleads decision-makers"

# === SKIP CONDITIONS ===
skip_this_unit_if:
  - condition: "Need dashboard UI/UX design, not data architecture"
    use_instead: "software/startup-dashboard/executive-command-center-design/2026"
  - condition: "Need pre-built dashboard templates"
    use_instead: "software/startup-dashboard/dashboard-template-library/2026"
  - condition: "Need notification and alert routing rules"
    use_instead: "software/startup-dashboard/notification-automation-rules/2026"

# === AGENT HINTS ===
inputs_needed:
  - key: data_volume
    question: "What is the approximate data volume?"
    type: choice
    options: ["small (< 1M rows total)", "medium (1M-100M rows)", "large (100M+ rows)", "unknown"]
  - key: update_frequency
    question: "How fresh does dashboard data need to be?"
    type: choice
    options: ["weekly batch is fine", "daily batch", "hourly near-real-time", "real-time (< 5 min delay)"]
  - key: tech_stack
    question: "What is the current tech stack?"
    type: choice
    options: ["Supabase/PostgreSQL", "AWS (RDS/Redshift)", "GCP (BigQuery)", "no existing data infrastructure"]
  - key: team_sql_fluency
    question: "How SQL-fluent is the team?"
    type: choice
    options: ["no SQL (need no-code)", "basic SQL", "intermediate SQL", "advanced SQL/data engineering"]

# === DISTRIBUTION ===
canonical_source: "https://knowledgelib.io/software/startup-dashboard/startup-dashboard-architecture/2026"
suggested_citation: "Source: knowledgelib.io — AI Knowledge Library (verified 2026-03-13)"

# === RELATED UNITS ===
related_kos:
  depends_on: []
  related_to:
    - id: "software/startup-dashboard/executive-command-center-design/2026"
      label: "UI/UX design for the command center this architecture feeds"
    - id: "software/startup-dashboard/data-integration-architecture/2026"
      label: "Detailed data integration patterns between systems"
  solves:
    - id: "software/startup-dashboard/notification-automation-rules/2026"
      label: "Alert infrastructure depends on this data architecture"
  alternative_to: []
  often_confused_with:
    - id: "software/startup-dashboard/executive-command-center-design/2026"
      label: "Command center is about KPI design and UI; this card is about data flow and infrastructure"

# === SOURCES ===
sources:
  - id: src1
    title: "Data Architecture Guide 2025: Models, Mesh, Metrics"
    author: GroupBWT
    url: https://groupbwt.com/glossary/data-architecture/
    type: technical_blog
    published: 2025-01-01
    reliability: established
  - id: src2
    title: "Architecture Overview"
    author: Supabase
    url: https://supabase.com/docs/guides/getting-started/architecture
    type: official_docs
    published: 2025-01-01
    reliability: authoritative
  - id: src3
    title: "Data Pipeline Architecture: A Guide to Better Design"
    author: RudderStack
    url: https://www.rudderstack.com/blog/data-pipeline-architecture/
    type: technical_blog
    published: 2024-06-01
    reliability: established
  - id: src4
    title: "Custom Claims and Role-Based Access Control"
    author: Supabase
    url: https://supabase.com/docs/guides/database/postgres/custom-claims-and-role-based-access-control-rbac
    type: official_docs
    published: 2024-09-01
    reliability: authoritative
  - id: src5
    title: "Data Integration in 2025: Architectures, Tools, and Best Practices"
    author: dbt Labs
    url: https://www.getdbt.com/blog/data-integration
    type: practitioner_guide
    published: 2025-02-01
    reliability: authoritative
  - id: src6
    title: "Connecting to Metabase"
    author: Supabase
    url: https://supabase.com/docs/guides/database/metabase
    type: official_docs
    published: 2024-08-01
    reliability: authoritative
---

# 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-3-second 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/Metabase (paid).

## Constraints
<!-- Agents: read this section before recommending any action from this unit. -->

- Use ELT not ETL for startup dashboards. Load raw data first, transform in the warehouse. Premature transformation before loading loses source data and limits future analysis. [src5]
- Enforce role-based access control at the database layer (Row Level Security), not just the application layer. Application-layer-only RBAC is bypassable via direct database access or API abuse. [src4]
- Dashboard queries must complete in under 3 seconds. Pre-aggregate with materialized views for any query exceeding this threshold.
- Never expose the production database directly to dashboard tools. Use read replicas, a dedicated analytics schema, or a separate analytics database. [src6]
- Explicitly define and label data freshness per metric. Mixing real-time and batch data without clear freshness indicators misleads decision-makers. [src1]

## Quick Reference

| Layer | Purpose | Startup Stack (Free) | Growth Stack (Paid) | Enterprise Stack |
|-------|---------|---------------------|--------------------|--------------------|
| Ingestion | Pull data from sources | Supabase webhooks + cron jobs | Airbyte / Fivetran | Fivetran + custom connectors |
| Storage | Raw data lake/warehouse | PostgreSQL (Supabase) | BigQuery / Snowflake | Snowflake + S3 data lake |
| Transformation | Clean, aggregate, model | SQL views + pg_cron | dbt Core | dbt Cloud + Great Expectations |
| Serving | Expose to dashboards | Supabase API + Metabase | Metabase Cloud / Preset | Looker / Tableau / custom API |
| Access Control | RBAC + data isolation | PostgreSQL RLS | RLS + application RBAC | RLS + SSO + audit logging |
| Orchestration | Schedule and monitor | pg_cron / GitHub Actions | Dagster / Prefect | Airflow / Dagster Cloud |
| Monitoring | Pipeline health | Supabase logs + Slack alerts | Metabase alerts | DataDog + PagerDuty |

## 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 query performance degrades

Data freshness decision:
├── Weekly metrics (board reports, cohort analysis)?
│   └── Batch: nightly or weekly SQL transforms
├── Daily metrics (revenue, pipeline, churn)?
│   └── Scheduled: hourly/daily dbt runs or materialized view refresh
├── Real-time metrics (active users, incident alerts)?
│   └── Streaming: Supabase Realtime, Kafka, or event-driven pipeline
```

## Step-by-Step Guide

### 1. Design the Data Model

Define the schema that stores dashboard metrics. Separate operational data (transactional) from analytical data (aggregated). [src1]

**Core tables for startup metrics:**

```sql
-- Operational (source-of-truth)
CREATE TABLE events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  event_type TEXT NOT NULL,    -- 'signup', 'purchase', 'churn', etc.
  user_id UUID REFERENCES users(id),
  properties JSONB,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Analytical (pre-aggregated for dashboard)
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;

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

**Verify**: Schema supports all KPIs from the command center design. Materialized views cover frequently-queried aggregations.

### 2. Build the Ingestion Layer

Connect external data sources to the warehouse. Use ELT: load raw data first, transform after. [src3] [src5]

**Ingestion patterns by source type:**

| Source | Method | Tool | Frequency |
|--------|--------|------|-----------|
| Stripe (revenue) | API webhook + REST poll | Supabase Edge Function / Airbyte | Real-time webhook + daily reconciliation |
| HubSpot (CRM) | REST API extraction | Airbyte / custom script | Hourly |
| Product events | Client SDK → database | Supabase JS client / PostHog | Real-time |
| Google Analytics | API extraction | Airbyte / custom script | Daily |
| Spreadsheets | CSV import or Google Sheets API | Manual upload / Airbyte | Weekly |

**Tier 1 ingestion (Supabase-native):**

```javascript
// Edge Function: Stripe webhook receiver
// Loads raw webhook payload into events table
export default async function handler(req) {
  const event = await req.json();
  const { error } = await supabase
    .from('stripe_events')
    .insert({ event_type: event.type,
              payload: event.data, raw: event });
  return new Response(error ? 'Error' : 'OK',
    { status: error ? 500 : 200 });
}
```

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

### 3. Build the Transformation Layer

Transform raw data into dashboard-ready metrics using SQL views and materialized views. [src5]

**Transformation patterns:**

```sql
-- Tier 1: SQL views for simple metrics
CREATE VIEW mrr_current AS
SELECT SUM(amount) / 100.0 AS mrr
FROM subscriptions
WHERE status = 'active';

-- Tier 1: Materialized view for expensive aggregations
CREATE MATERIALIZED VIEW monthly_cohort_retention AS
SELECT
  date_trunc('month', u.created_at) AS cohort_month,
  date_trunc('month', e.created_at) AS activity_month,
  COUNT(DISTINCT e.user_id) AS active_users,
  COUNT(DISTINCT u.id) AS cohort_size
FROM users u
LEFT JOIN events e ON u.id = e.user_id
GROUP BY 1, 2;

-- Tier 2: dbt model (models/dashboard/mrr_daily.sql)
-- {{ config(materialized='table') }}
-- SELECT date_trunc('day', created_at) AS day,
--   SUM(CASE WHEN type='new' THEN mrr ELSE 0 END) AS new_mrr,
--   SUM(CASE WHEN type='churn' THEN mrr ELSE 0 END) AS churned_mrr
-- FROM {{ ref('mrr_movements') }}
-- GROUP BY 1
```

**Transformation tier guide:**

| Tier | Method | When to Use | Refresh |
|------|--------|------------|---------|
| Simple views | `CREATE VIEW` | Real-time, low-complexity queries | Live (query-time) |
| Materialized views | `CREATE MATERIALIZED VIEW` | Expensive aggregations, < 1M source rows | Scheduled (pg_cron) |
| dbt models | dbt Core/Cloud | Multi-source joins, complex business logic, > 1M rows | Scheduled (Dagster/cron) |
| Streaming transforms | Supabase Realtime / Kafka Streams | Real-time dashboards, < 5 min latency needed | Continuous |

**Verify**: All command center KPIs can be computed from transformation layer. Query time < 3 seconds for each.

### 4. Implement Access Control

Enforce data security using PostgreSQL Row Level Security (RLS) and role-based access. [src4]

```sql
-- Create roles for dashboard access tiers
CREATE ROLE dashboard_viewer;
CREATE ROLE dashboard_admin;
CREATE ROLE board_viewer;

-- Grant read access to analytics schema
GRANT USAGE ON SCHEMA analytics TO dashboard_viewer;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO dashboard_viewer;

-- Enable RLS on sensitive tables
ALTER TABLE revenue_details ENABLE ROW LEVEL SECURITY;

-- Policy: viewers see only their department's data
CREATE POLICY dept_isolation ON revenue_details
  FOR SELECT USING (
    department = current_setting('app.user_department')
  );

-- Policy: admins see all data
CREATE POLICY admin_access ON revenue_details
  FOR SELECT TO dashboard_admin USING (true);

-- Policy: board sees aggregated data only (no individual records)
CREATE POLICY board_aggregate ON revenue_details
  FOR SELECT TO board_viewer USING (false);
-- Board accesses only via aggregated views:
GRANT SELECT ON mrr_summary TO board_viewer;
```

**Access tier matrix:**

| Role | Raw Data | Aggregated Metrics | Sensitive Metrics (comp, runway) | Admin Functions |
|------|----------|-------------------|--------------------------------|-----------------|
| CEO/Founder | Yes | Yes | Yes | Yes |
| VP/Director | Department only | All | Department only | No |
| Manager | Team only | Department | No | No |
| Board | No | Company-level only | Yes (pre-aggregated) | No |
| All-Hands | No | Company-level (sanitized) | No | No |

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

### 5. Set Up the Serving Layer

Connect the transformation layer to dashboard tools. [src6]

**Tier 1: Supabase + Metabase (free)**

```bash
# Create dedicated read-only Metabase user
psql -c "CREATE USER metabase_reader WITH PASSWORD 'secure_password';"
psql -c "GRANT USAGE ON SCHEMA analytics TO metabase_reader;"
psql -c "GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO metabase_reader;"
# Connect Metabase to Supabase PostgreSQL
# Host: db.{project-ref}.supabase.co
# Port: 5432, Database: postgres, User: metabase_reader
```

**Tier 2: API-based serving (custom dashboard)**

```javascript
// Supabase Edge Function: /api/dashboard/metrics
export default async function handler(req) {
  const { data, error } = await supabase
    .from('daily_metrics')
    .select('*')
    .gte('day', new Date(Date.now() - 30 * 86400000).toISOString())
    .order('day', { ascending: false });
  return new Response(JSON.stringify(data), {
    headers: { 'Content-Type': 'application/json',
               'Cache-Control': 'public, max-age=300' } // 5-min cache
  });
}
```

**Serving options comparison:**

| Option | Cost | Setup Time | Customization | Real-Time |
|--------|------|-----------|---------------|-----------|
| Metabase (self-hosted) | $0 | 1-2 hours | Medium | Polling (1 min) |
| Metabase Cloud | $85/mo | 30 min | Medium | Polling (1 min) |
| Grafana | $0 | 2-3 hours | High | Yes (streaming) |
| Custom (React + API) | Dev time | 1-2 weeks | Full | Yes (WebSocket) |
| Preset (Superset managed) | $0-$600/mo | 1 hour | Medium | Polling |
| Looker Studio | $0 | 1 hour | Low | Polling (15 min) |

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

### 6. Configure Monitoring and Alerting

Set up pipeline health monitoring to ensure data freshness and correctness. [src3]

**Pipeline health checks:**

```sql
-- Data freshness check: alert if no events in last 2 hours
SELECT CASE
  WHEN MAX(created_at) < now() - interval '2 hours'
  THEN 'STALE'
  ELSE 'FRESH'
END AS data_status
FROM events;

-- Row count anomaly: alert if daily count drops > 50%
SELECT CASE
  WHEN today_count < yesterday_count * 0.5
  THEN 'ANOMALY'
  ELSE 'NORMAL'
END AS count_status
FROM (
  SELECT
    COUNT(*) FILTER (WHERE created_at >= CURRENT_DATE) AS today_count,
    COUNT(*) FILTER (WHERE created_at >= CURRENT_DATE - 1
                     AND created_at < CURRENT_DATE) AS yesterday_count
  FROM events
) counts;
```

**Monitoring checklist:**
- Data freshness per source (alert if stale > 2x expected interval)
- Row count anomalies (alert if daily count drops > 50%)
- Materialized view refresh status (alert if refresh fails)
- Query performance (alert if p95 latency > 3 seconds)
- Storage growth rate (alert if > 80% of allocated storage)

**Verify**: Monitoring queries running on schedule, alert channels configured (Slack/email), test alert sent successfully.

## Code Examples

### PostgreSQL: Complete Dashboard Schema

```sql
-- Input:  Raw event data from product + Stripe + CRM
-- Output: Pre-aggregated metrics for sub-3s dashboard 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 COUNT(DISTINCT user_id) FROM events
   WHERE created_at >= CURRENT_DATE - 30) AS mau;

-- Refresh every hour
SELECT cron.schedule('kpi-refresh', '0 * * * *',
  'REFRESH MATERIALIZED VIEW CONCURRENTLY analytics.kpi_snapshot');
```

### JavaScript: Real-Time Dashboard Subscriber

```javascript
// Input:  Supabase Realtime channel subscription
// Output: Live dashboard updates via WebSocket

import { createClient } from '@supabase/supabase-js';
const supabase = createClient(SUPABASE_URL, SUPABASE_KEY);

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

function updateDashboard(event) {
  // Increment counters, update sparklines, check thresholds
  const metric = metricMap[event.event_type];
  if (metric) metric.increment(event);
}
```

## Anti-Patterns

### Wrong: Querying production database for dashboards
Running complex analytical queries directly against the production transactional database. Result: dashboard queries compete with application queries, production performance degrades, and dashboard loads slowly. [src6]

### Correct: Separate analytics layer
Use read replicas, a dedicated analytics schema, or materialized views. Production database handles transactions; analytics layer handles dashboard queries. No performance interference.

### Wrong: ETL with premature transformation
Transforming data during extraction (ETL) before loading into the warehouse. When business requirements change, the source data has been discarded and the pipeline must be rebuilt from scratch.

### Correct: ELT — load raw, transform in warehouse
Load raw data into the warehouse first. Transform using SQL views, materialized views, or dbt models. Raw data is always available for new analyses without rebuilding ingestion pipelines. [src5]

### Wrong: Application-layer-only access control
Implementing access control only in the dashboard application (checking roles in frontend or API code). A user who discovers the database connection string or API endpoint can bypass all restrictions.

### Correct: Database-layer RLS + application RBAC
Enforce Row Level Security at the PostgreSQL level. Even if someone bypasses the application, the database itself prevents unauthorized data access. Application-layer RBAC provides UX convenience on top of database-layer security. [src4]

## Common Pitfalls

- **Materialized views not refreshed**: Dashboard shows stale data because the materialized view refresh cron job failed silently. Fix: add monitoring for refresh job status, alert on failure. [src3]
- **No data freshness indicator**: Users trust dashboard numbers without knowing how old the data is. Fix: display "last updated" timestamp per metric, derived from actual data, not refresh schedule.
- **Over-engineering early**: Building a full Airflow + Snowflake + dbt stack for a 10-person startup with 100K rows. Fix: start with PostgreSQL views and Metabase. Migrate when query performance degrades. [src1]
- **Missing data validation**: Dashboard shows impossible values (negative revenue, > 100% churn) because upstream data has quality issues. Fix: add CHECK constraints and validation triggers at the database level.
- **Single point of failure**: All metrics flow through one pipeline that, when broken, takes down the entire dashboard. Fix: separate critical metric pipelines (revenue, churn) from nice-to-have metrics so failures are isolated. [src3]

## When to Use / When Not to Use

| Use When | Don't Use When | Use Instead |
|----------|---------------|-------------|
| Designing the data infrastructure behind a startup dashboard | Need dashboard UI/UX design | executive-command-center-design |
| Setting up data pipelines from SaaS tools to a warehouse | Need pre-built dashboard templates | dashboard-template-library |
| Implementing access control for multi-role dashboard access | Need alert routing rules | notification-automation-rules |
| Choosing between PostgreSQL, BigQuery, or Snowflake for metrics | Already have a data platform, need to optimize it | data-integration-architecture |

## Important Caveats

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

## Related Units

- [Executive Command Center Design](/software/startup-dashboard/executive-command-center-design/2026) — UI/UX design for the dashboard this architecture feeds
- [Data Integration Architecture](/software/startup-dashboard/data-integration-architecture/2026) — Detailed data integration patterns between systems
- [Notification Automation Rules](/software/startup-dashboard/notification-automation-rules/2026) — Alert infrastructure built on this data architecture
- [Dashboard Template Library](/software/startup-dashboard/dashboard-template-library/2026) — Pre-built templates for specific dashboard views