Use SQL on API logs to design the right API
A story of using SQL on API logs to find the real hot paths, collapse round‑trips and pick the exact fields clients need.

A GitHub API team wanted a smaller, faster surface. Engineers and clients argued about resources and fields. The team used real API logs and simple SQL to decide what to keep, what to combine, and what to leave alone.
In brief
Step by step
List the concrete issues: too many round trips, large payloads, tail latency, or fragile joins clients must do.
Log endpoint, parameters, response size, client id (or app id), session id when available, status and latency.
Run simple queries to show which endpoints clients actually call together, which clients drive most traffic, and typical payload sizes.
Design combined resources that collapse real multi-call flows. Ignore rare theoretical joins until there is evidence.
Run an internal pilot. Measure round-trip counts, latency, and error surface before rolling out.
Keep fields minimal. Add or change only when usage or client migration cost justifies it.
Worked example: three SQL audit queries
Assume an api_logs table with columns: timestamp, client_id, session_id, endpoint, method, response_size, latency_ms, status.
-- (a) Top endpoints by unique clients
SELECT endpoint, count(DISTINCT client_id) AS unique_clients
FROM api_logs
WHERE timestamp >= now() - interval '30 days'
GROUP BY endpoint
ORDER BY unique_clients DESC
LIMIT 50;
-- (b) Common two-call sequences within a session
WITH seq AS (
SELECT session_id, endpoint,
lead(endpoint) OVER (PARTITION BY session_id ORDER BY timestamp) AS next_endpoint
FROM api_logs
WHERE session_id IS NOT NULL
)
SELECT endpoint || ' -> ' || next_endpoint AS pair, count(*) AS occurrences
FROM seq
WHERE next_endpoint IS NOT NULL
GROUP BY pair
ORDER BY occurrences DESC
LIMIT 50;
-- (c) Median payload (response) size per endpoint
SELECT endpoint,
percentile_cont(0.5) WITHIN GROUP (ORDER BY response_size) AS median_response_size
FROM api_logs
WHERE response_size IS NOT NULL
GROUP BY endpoint
ORDER BY median_response_size DESC
LIMIT 50;
Read these results together. Top endpoints with many unique clients are candidates for a stable surface. Frequent "A -> B" pairs are hot paths to collapse. Endpoints with large medians are good targets to reduce payload bloat.

How to propose combined resources
From the queries above, pick 2–3 high‑impact combinations. For example:
- Combine resource "A + B" where A -> B occurs in the same session for most clients.
- Expose a single endpoint that returns the frequent join of user, repo meta, and a small set of related objects when median response sizes are large but correlated.
- Offer an optional lightweight projection parameter to avoid returning rarely used fields.
Checklist before you change the contract
Final note
Design the API surface from real calls. Use SQL on your logs to find the hot paths, collapse those, pilot internally, then iterate by usage and migration cost.


