SaaS API Analytics: Usage Tracking, Rate Limit Dashboards

Quick answer. SaaS API analytics surface endpoint problems before customers complain, like a /v1/reports/generate p99 latency of 12 seconds or API keys nearing rate limits. Log every request to a write-optimized api_requests table partitioned by day, capturing endpoint, status_code, latency_ms, and request/response sizes for usage and rate-limit dashboards. Developer-facing SaaS products live or die by their API quality. But most teams only find out the API is slow after a customer complaint. API analytics built into your platform surface the problem earlier: you can see that the /v1/reports/generate endpoint has a p99 latency of 12 seconds before your enterprise customer escalates. You can see which API keys are burning through rate limits before they hit the wall.

Database Schema

-- Raw API request log (write-optimized, partitioned by day)
CREATE TABLE api_requests (
 id UUID DEFAULT gen_random_uuid(),
 api_key_id UUID NOT NULL,
 workspace_id UUID NOT NULL,
 endpoint TEXT NOT NULL, -- e.g. "POST /v1/reports/generate"
 status_code INTEGER NOT NULL,
 latency_ms INTEGER NOT NULL,
 request_size INTEGER NOT NULL DEFAULT 0, -- bytes
 response_size INTEGER NOT NULL DEFAULT 0,
 occurred_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
 PRIMARY KEY (id, occurred_at)
) PARTITION BY RANGE (occurred_at);
-- Create daily partitions (automate with pg_partman in production)
CREATE TABLE api_requests_2027_06 PARTITION OF api_requests
 FOR VALUES FROM ('2027年06月01日') TO ('2027年07月01日');
-- Indexes on the partition
CREATE INDEX ON api_requests (api_key_id, occurred_at DESC);
CREATE INDEX ON api_requests (workspace_id, endpoint, occurred_at DESC);
CREATE INDEX ON api_requests (endpoint, occurred_at DESC);
-- Hourly rollup for fast dashboard queries
CREATE TABLE api_request_hourly (
 workspace_id UUID NOT NULL,
 api_key_id UUID NOT NULL,
 endpoint TEXT NOT NULL,
 hour TIMESTAMPTZ NOT NULL, -- Truncated to hour
 request_count INTEGER NOT NULL DEFAULT 0,
 error_count INTEGER NOT NULL DEFAULT 0, -- 4xx + 5xx
 total_latency BIGINT NOT NULL DEFAULT 0, -- Sum for avg calculation
 p50_latency INTEGER,
 p95_latency INTEGER,
 p99_latency INTEGER,
 PRIMARY KEY (workspace_id, api_key_id, endpoint, hour)
);
CREATE INDEX ON api_request_hourly (workspace_id, hour DESC);

TypeScript Middleware: Request Logging

// middleware/api-analytics.ts
import { NextRequest, NextResponse } from "next/server";
import { db } from "@/lib/db";
import { getApiKeyFromRequest } from "@/lib/auth/api-keys";
export async function withApiAnalytics(
 request: NextRequest,
 handler: (req: NextRequest) => Promise<NextResponse>,
 endpoint: string
): Promise<NextResponse> {
 const startTime = Date.now();
 const apiKey = await getApiKeyFromRequest(request);
 let response: NextResponse;
 try {
 response = await handler(request);
 } catch (err) {
 response = NextResponse.json({ error: "Internal server error" }, { status: 500 });
 }
 const latencyMs = Date.now() - startTime;
 // Fire-and-forget: don't block response on analytics write
 void logApiRequest({
 apiKeyId: apiKey?.id ?? "anonymous",
 workspaceId: apiKey?.workspaceId ?? "unknown",
 endpoint,
 statusCode: response.status,
 latencyMs,
 }).catch((err) => console.error("[api-analytics] Failed to log request:", err));
 // Add latency header for debugging
 response.headers.set("X-Response-Time", `${latencyMs}ms`);
 return response;
}
async function logApiRequest(data: {
 apiKeyId: string;
 workspaceId: string;
 endpoint: string;
 statusCode: number;
 latencyMs: number;
}): Promise<void> {
 await db.execute(
 `INSERT INTO api_requests (api_key_id, workspace_id, endpoint, status_code, latency_ms)
 VALUES (1,ドル 2,ドル 3,ドル 4,ドル 5ドル)`,
 [data.apiKeyId, data.workspaceId, data.endpoint, data.statusCode, data.latencyMs]
 );
}

Hourly Rollup Job

// workers/api-analytics-rollup.ts — run every 15 minutes
import { db } from "@/lib/db";
export async function rollupApiRequests(): Promise<void> {
 // Roll up last 2 hours (overlap handles late-arriving rows)
 const twoHoursAgo = new Date(Date.now() - 2 * 3600_000);
 const hourBucket = new Date(
 Math.floor(Date.now() / 3600_000) * 3600_000
 );
 await db.execute(`
 INSERT INTO api_request_hourly
 (workspace_id, api_key_id, endpoint, hour,
 request_count, error_count, total_latency,
 p50_latency, p95_latency, p99_latency)
 SELECT
 workspace_id,
 api_key_id,
 endpoint,
 date_trunc('hour', occurred_at) AS hour,
 COUNT(*) AS request_count,
 COUNT(*) FILTER (WHERE status_code >= 400) AS error_count,
 SUM(latency_ms) AS total_latency,
 percentile_cont(0.50) WITHIN GROUP (ORDER BY latency_ms)::int AS p50_latency,
 percentile_cont(0.95) WITHIN GROUP (ORDER BY latency_ms)::int AS p95_latency,
 percentile_cont(0.99) WITHIN GROUP (ORDER BY latency_ms)::int AS p99_latency
 FROM api_requests
 WHERE occurred_at >= 1ドル
 GROUP BY workspace_id, api_key_id, endpoint, date_trunc('hour', occurred_at)
 ON CONFLICT (workspace_id, api_key_id, endpoint, hour)
 DO UPDATE SET
 request_count = EXCLUDED.request_count,
 error_count = EXCLUDED.error_count,
 total_latency = EXCLUDED.total_latency,
 p50_latency = EXCLUDED.p50_latency,
 p95_latency = EXCLUDED.p95_latency,
 p99_latency = EXCLUDED.p99_latency
 `, [twoHoursAgo]);
}

Dashboard Queries

-- 1. Per-endpoint latency for a workspace (last 24h, from hourly rollup)
SELECT
 endpoint,
 SUM(request_count) AS total_requests,
 ROUND(SUM(error_count)::numeric / NULLIF(SUM(request_count), 0) * 100, 2) AS error_rate_pct,
 -- Weighted average p50/p95/p99 across hours
 ROUND(SUM(p50_latency * request_count) / NULLIF(SUM(request_count), 0)) AS avg_p50_ms,
 ROUND(SUM(p95_latency * request_count) / NULLIF(SUM(request_count), 0)) AS avg_p95_ms,
 ROUND(SUM(p99_latency * request_count) / NULLIF(SUM(request_count), 0)) AS avg_p99_ms
FROM api_request_hourly
WHERE workspace_id = $1
 AND hour >= NOW() - INTERVAL '24 hours'
GROUP BY endpoint
ORDER BY total_requests DESC;
-- 2. Rate limit consumption per API key (last 1 hour from raw table)
-- Assuming rate limit is 1000 requests/hour per key
SELECT
 ak.name AS key_name,
 ak.id AS api_key_id,
 ak.rate_limit AS hourly_limit,
 COUNT(ar.id) AS requests_used,
 ROUND(COUNT(ar.id)::numeric / ak.rate_limit * 100, 1) AS pct_used,
 ak.rate_limit - COUNT(ar.id) AS remaining
FROM api_keys ak
LEFT JOIN api_requests ar
 ON ar.api_key_id = ak.id
 AND ar.occurred_at >= date_trunc('hour', NOW())
WHERE ak.workspace_id = $1
GROUP BY ak.id, ak.name, ak.rate_limit
ORDER BY pct_used DESC NULLS LAST;
-- 3. Request volume time-series (hourly buckets for last 7 days)
SELECT
 hour,
 SUM(request_count) AS requests,
 SUM(error_count) AS errors
FROM api_request_hourly
WHERE workspace_id = $1
 AND hour >= NOW() - INTERVAL '7 days'
GROUP BY hour
ORDER BY hour;
-- 4. Top endpoints by error rate (last 24h)
SELECT
 endpoint,
 SUM(request_count) AS total,
 SUM(error_count) AS errors,
 ROUND(SUM(error_count)::numeric / NULLIF(SUM(request_count), 0) * 100, 2) AS error_rate_pct,
 MAX(p99_latency) AS max_p99_ms
FROM api_request_hourly
WHERE workspace_id = $1
 AND hour >= NOW() - INTERVAL '24 hours'
 AND request_count > 0
GROUP BY endpoint
HAVING SUM(request_count) > 10 -- Exclude low-volume noise
ORDER BY error_rate_pct DESC
LIMIT 10;

TypeScript: Dashboard Data API

// app/api/analytics/overview/route.ts
import { NextRequest, NextResponse } from "next/server";
import { prisma } from "@/lib/prisma";
import { requireWorkspaceAuth } from "@/lib/auth/middleware";
export async function GET(req: NextRequest) {
 const { workspaceId } = await requireWorkspaceAuth(req);
 const [endpointStats, rateLimitStatus, volumeSeries] = await Promise.all([
 // Per-endpoint latency summary
 prisma.$queryRaw<{
 endpoint: string;
 total_requests: bigint;
 error_rate_pct: number;
 avg_p95_ms: number;
 }[]>`
 SELECT
 endpoint,
 SUM(request_count) AS total_requests,
 ROUND(SUM(error_count)::numeric / NULLIF(SUM(request_count), 0) * 100, 2) AS error_rate_pct,
 ROUND(SUM(p95_latency * request_count) / NULLIF(SUM(request_count), 0)) AS avg_p95_ms
 FROM api_request_hourly
 WHERE workspace_id = ${workspaceId}::uuid
 AND hour >= NOW() - INTERVAL '24 hours'
 GROUP BY endpoint
 ORDER BY total_requests DESC
 LIMIT 20
 `,
 // Rate limit status for all keys
 prisma.$queryRaw<{
 key_name: string;
 api_key_id: string;
 hourly_limit: number;
 requests_used: bigint;
 pct_used: number;
 }[]>`
 SELECT
 ak.name AS key_name,
 ak.id::text AS api_key_id,
 ak.rate_limit AS hourly_limit,
 COUNT(ar.id) AS requests_used,
 ROUND(COUNT(ar.id)::numeric / ak.rate_limit * 100, 1) AS pct_used
 FROM api_keys ak
 LEFT JOIN api_requests ar
 ON ar.api_key_id = ak.id
 AND ar.occurred_at >= date_trunc('hour', NOW())
 WHERE ak.workspace_id = ${workspaceId}::uuid
 GROUP BY ak.id, ak.name, ak.rate_limit
 ORDER BY pct_used DESC NULLS LAST
 `,
 // 7-day request volume time-series
 prisma.$queryRaw<{ hour: Date; requests: bigint; errors: bigint }[]>`
 SELECT hour, SUM(request_count) AS requests, SUM(error_count) AS errors
 FROM api_request_hourly
 WHERE workspace_id = ${workspaceId}::uuid
 AND hour >= NOW() - INTERVAL '7 days'
 GROUP BY hour
 ORDER BY hour
 `,
 ]);
 return NextResponse.json({
 endpointStats: endpointStats.map((r) => ({
 ...r,
 total_requests: Number(r.total_requests),
 })),
 rateLimitStatus: rateLimitStatus.map((r) => ({
 ...r,
 requests_used: Number(r.requests_used),
 })),
 volumeSeries: volumeSeries.map((r) => ({
 hour: r.hour.toISOString(),
 requests: Number(r.requests),
 errors: Number(r.errors),
 })),
 });
}

Cost Analysis

Explore More

• SaaS Customer Health Score
• SaaS Usage Analytics
• PostgreSQL Lateral Joins
• PostgreSQL Window Functions
• PostgreSQL Partitioning

What We Bring to the Table

API analytics require a two-tier storage strategy: raw event log for accuracy (partitioned by day for fast drops), hourly rollup for fast dashboard reads (p50/p95/p99 per endpoint). The fire-and-forget logging pattern keeps your API latency clean — analytics writes never block the response path.

What we deliver:

• api_requests: partitioned table by occurred_at, indexes on api_key_id+occurred_at and workspace_id+endpoint+occurred_at
• api_request_hourly: rollup with request_count, error_count, total_latency, p50/p95/p99
• withApiAnalytics middleware: fire-and-forget void logApiRequest().catch(), X-Response-Time header
• rollupApiRequests: percentile_cont(0.95) WITHIN GROUP, ON CONFLICT DO UPDATE upsert, 2-hour overlap window
• Dashboard SQL: weighted avg latency across hours, rate limit % consumption per key, 7-day time series
• GET /api/analytics/overview: Promise.all for 3 queries, bigint→Number serialization

Talk to our team about your API observability stack →

Or explore our SaaS development services.

About the Author

Building a SaaS Product?

We've helped launch 50+ SaaS platforms. Let's build yours — fast.

Free consultation • No commitment • Response within 24 hours