Reporting API

1. Why a Separate Reporting API

You could embed SQL queries directly in the dashboard, but that couples your front-end to your database schema. A separate API layer creates a clean boundary: the dashboard is pure client-side JavaScript that fetches JSON, and the API handles all data access and authentication on the server.

Separation of Concerns

The dashboard should not know that pageviews are stored in a MySQL table called pageviews, that timestamps are in the server_timestamp column, or that session duration requires a TIMESTAMPDIFF calculation. The dashboard should ask "give me pageviews for the last 7 days" and receive a clean JSON response. If you later switch from MySQL to PostgreSQL, or restructure your tables, or add caching — the dashboard does not change.

Multiple Clients

An API can serve more than one consumer. Today it powers your web dashboard. Tomorrow it could power a mobile app, a CLI tool for quick checks, a Slack bot that posts daily summaries, or an export script that generates CSV reports. Every consumer speaks the same JSON protocol. Without an API, each client would need its own database connection and query logic.

Independent Testing, Security, and Scaling

• Testing: You can test API endpoints with curl or a test framework without loading a browser. Input validation, authentication, and query correctness are all testable in isolation.
• Security: Authentication and authorization live in one place. The database credentials never leave the server. Rate limiting, input sanitization, and CORS headers are enforced at the API layer.
• Scaling: If the API becomes a bottleneck, you can add caching, deploy it on a separate server, or put it behind a load balancer — all without touching the dashboard code.

2. Endpoint Design

The API follows RESTful conventions: resources are nouns, HTTP methods indicate the action, and responses use standard status codes. Every analytics endpoint uses GET because dashboards only read data. The only POST endpoints are for authentication.

This is a small, focused API. Each analytics endpoint maps to one panel in the dashboard. The /api/dashboard endpoint is a convenience route that aggregates the summary numbers from several tables into a single response, so the dashboard can populate its overview cards with one request instead of four.

3. Query Parameters

Every analytics endpoint accepts query parameters for date filtering, pagination, and grouping. These parameters are consistent across all GET endpoints so the dashboard can apply the same date picker and pagination controls everywhere.

Date Range Filtering

All analytics endpoints accept start and end parameters in YYYY-MM-DD format. If omitted, the API defaults to the last 7 days.

GET /api/pageviews?start=2026-02-01&end=2026-02-14

Pagination

Endpoints that return lists (top pages, error messages) accept page and limit parameters. The default is page 1 with 20 results per page.

GET /api/errors?page=2&limit=10

Grouping

Time-series endpoints accept a group parameter to control the granularity of the data points. Valid values are day and hour. The default is day.

GET /api/pageviews?start=2026-02-10&end=2026-02-11&group=hour

Example Request and Response

// Request
GET /api/pageviews?start=2026-02-01&end=2026-02-07&group=day&page=1&limit=5

// Response
{
  "success": true,
  "data": {
    "timeseries": [
      { "date": "2026-02-01", "views": 1243 },
      { "date": "2026-02-02", "views": 987 },
      { "date": "2026-02-03", "views": 1102 },
      { "date": "2026-02-04", "views": 1455 },
      { "date": "2026-02-05", "views": 1321 },
      { "date": "2026-02-06", "views": 1189 },
      { "date": "2026-02-07", "views": 1067 }
    ],
    "top_pages": [
      { "url": "/", "views": 2847 },
      { "url": "/about", "views": 1203 },
      { "url": "/blog", "views": 891 },
      { "url": "/contact", "views": 654 },
      { "url": "/pricing", "views": 512 }
    ]
  },
  "meta": {
    "total": 23,
    "page": 1,
    "limit": 5
  }
}

4. Response Format

Every API response uses a consistent JSON envelope. The dashboard can rely on the same structure regardless of which endpoint it calls. This makes error handling predictable and simplifies the front-end code.

Success Response

{
  "success": true,
  "data": { ... },
  "meta": {
    "total": 142,
    "page": 1,
    "limit": 20
  }
}

The data field contains the actual payload — its shape varies by endpoint. The meta field contains pagination information. For endpoints that do not paginate (like /api/dashboard), meta can be omitted or empty.

Error Response

{
  "success": false,
  "error": "Invalid date range: start must be before end"
}

HTTP Status Codes

The API returns proper HTTP status codes alongside the JSON body. The front-end should check the status code first, then parse the body.

5. Authentication: Session-Based

The reporting API uses session-based authentication. The user logs in with email and password, the server validates the credentials, creates a server-side session, and sends back a cookie. Every subsequent request includes the cookie automatically — no token management needed on the client.

Login Flow

1. The dashboard sends a POST /api/login request with email and password in the request body.
2. The server looks up the user by email in the users table.
3. The server compares the submitted password against the stored hash using bcrypt.compare() (Node.js) or password_verify() (PHP).
4. If the credentials are valid, the server creates an HTTP session and stores the user's ID and role in the session data.
5. The server responds with Set-Cookie: session_id=abc123; HttpOnly; Secure; SameSite=Strict.
6. All subsequent requests from the browser automatically include this cookie. The server reads the session data to identify the user.

Cookie Flags

6. User Roles

Not every dashboard user should have the same access. The users table includes a role column with three possible values, each granting a different level of access.

• Viewer: Can see all analytics data (pageviews, performance, errors, sessions) but cannot create or delete user accounts or change any settings. This is the default role for new accounts.
• Admin: Everything a viewer can do, plus the ability to create, edit, and delete user accounts. Useful for team leads who need to grant dashboard access to new team members.
• Owner: Full access, including system-level settings like data retention policies, API keys, and collector configuration. There should be exactly one owner per installation.

Role Checking in Middleware

Role enforcement happens in middleware that runs before every route handler. The middleware reads the session, extracts the user's role, and compares it against the required role for the requested endpoint. If the role is insufficient, the middleware returns a 403 Forbidden response before the route handler ever executes.

// Middleware: require authentication
function requireAuth(req, res, next) {
    if (!req.session || !req.session.userId) {
        return res.status(401).json({
            success: false,
            error: 'Authentication required'
        });
    }
    next();
}

// Middleware: require a minimum role level
function requireRole(minRole) {
    const hierarchy = { viewer: 1, admin: 2, owner: 3 };

    return (req, res, next) => {
        const userRole = req.session.role;
        if (hierarchy[userRole] < hierarchy[minRole]) {
            return res.status(403).json({
                success: false,
                error: 'Insufficient permissions'
            });
        }
        next();
    };
}

// Usage in routes:
app.get('/api/pageviews', requireAuth, getPageviews);
app.post('/api/users',    requireAuth, requireRole('admin'), createUser);
app.delete('/api/users/:id', requireAuth, requireRole('admin'), deleteUser);

7. Rate Limiting

Rate limiting prevents a single client from overwhelming the API with too many requests. Without it, a bug in the dashboard (an infinite loop calling fetch) or a malicious script can saturate the server and degrade service for everyone.

Simple In-Memory Rate Limiter

The simplest approach: maintain a Map keyed by client IP address, tracking the number of requests made in the current minute. When the count exceeds the limit, return 429 Too Many Requests.

// Simple in-memory rate limiter
const rateLimitMap = new Map();
const WINDOW_MS  = 60 * 1000;  // 1 minute
const MAX_REQUESTS = 60;        // 60 requests per minute per IP

function rateLimit(req, res, next) {
    const ip = req.ip;
    const now = Date.now();

    if (!rateLimitMap.has(ip)) {
        rateLimitMap.set(ip, { count: 1, windowStart: now });
        return next();
    }

    const entry = rateLimitMap.get(ip);

    // Reset window if expired
    if (now - entry.windowStart > WINDOW_MS) {
        entry.count = 1;
        entry.windowStart = now;
        return next();
    }

    entry.count++;

    if (entry.count > MAX_REQUESTS) {
        return res.status(429).json({
            success: false,
            error: 'Too many requests. Try again in a minute.'
        });
    }

    next();
}

// Apply to all API routes
app.use('/api', rateLimit);

Response Headers

Well-behaved rate limiters include headers that tell the client how many requests remain and when the window resets:

HTTP/1.1 200 OK
X-RateLimit-Limit: 60
X-RateLimit-Remaining: 42
X-RateLimit-Reset: 1740000060

8. SPA vs Server-Rendered Dashboard

The API-first approach dictates that the dashboard is a single-page application (SPA). The server serves one HTML file (plus CSS and JavaScript), and all data comes from API calls. The server never renders HTML for dashboard pages.

How It Works

1. The user navigates to https://example.com/dashboard.
2. The server returns a single index.html file with a <script> tag.
3. The JavaScript boots up, checks for an active session (calls GET /api/dashboard), and renders the UI.
4. If the user is not logged in (401 response), the JS shows a login form.
5. All navigation within the dashboard (switching between pageviews, performance, errors tabs) happens in JavaScript — no page reloads.
6. Each tab switch triggers a fetch() call to the appropriate API endpoint and re-renders that section.

Why This Pattern

• Clean separation: The API is a data service. The dashboard is a UI application. They can be developed, tested, and deployed independently.
• Cacheable API responses: JSON responses can be cached by the browser or a CDN. Server-rendered HTML is harder to cache because it often varies by user session.
• Modern tooling: Most analytics dashboards (Google Analytics, Mixpanel, PostHog) are SPAs. The pattern is well-understood, well-documented, and well-supported by frameworks.
• No server-side templating: The server does not need a template engine. It serves static files for the dashboard and JSON for the API. This keeps the server code simple and focused.

This is the pattern used by virtually every modern analytics tool. Google Analytics, Plausible, PostHog, Mixpanel — all of them serve a static SPA that consumes a JSON API. The dashboard you build in the next phase follows the same approach.

Tutorial Modules