How OpenSEO Captures and Stores Lighthouse Performance Data: A Complete Technical Guide

OpenSEO captures Lighthouse metrics via the DataForSEO API, stores raw JSON payloads in Cloudflare R2, and persists minimal scores with R2 references in PostgreSQL for efficient retrieval and analysis.

The every-app/open-seo repository implements a robust, dual-storage architecture for Lighthouse performance data. This design separates archival storage from queryable metadata, enabling fast API responses while preserving complete audit records for deep analysis.

The Dual-Storage Architecture

OpenSEO solves the tension between data completeness and query performance through two complementary storage layers:

Database Layer: Metadata and References

The auditLighthouseResults table stores essential metrics and a pointer to the full payload. This keeps database rows lightweight while maintaining fast lookups.

In src/server/features/audit/repositories/AuditRepository.ts, the insertLighthouseResults function handles database persistence:

// Core columns written: score, timing metrics, r2Key for payload retrieval
// Located at lines 55-76 in AuditRepository.ts

The r2Key column is the critical link to the full audit data stored in object storage.

Object Storage Layer: Raw Payload Preservation

Complete Lighthouse JSON responses land in Cloudflare R2. The storage key follows a predictable path pattern:

site-audit/<projectId>/<auditId>/<pageId>-<strategy>.json

The putTextToR2 function in src/server/lib/audit/lighthouse.ts (lines 13-21) handles this operation, returning the key and payload size for database recording.

The Capture Flow: Step by Step

Step 1: Initiate Lighthouse Fetch

The audit workflow invokes fetchAndStoreLighthouseResult for each target page. This triggers fetchLighthouseResult, which calls DataForSEO's dataforseo.lighthouse.live endpoint with automatic retry logic.

Located in src/server/lib/audit/lighthouse.ts (lines 25-60), the fetcher implements three retries with exponential back-off (2 seconds → 4 seconds) to handle transient API failures.

Step 2: Store Raw Payload to R2

Upon successful API response, putTextToR2 writes the JSON string to Cloudflare R2. The function returns:

• key: The R2 storage path
• sizeBytes: Payload size for monitoring

Step 3: Insert Minimal Record to Database

The returned scores, timing metrics, and R2 key are upserted into auditLighthouseResults via insertLighthouseResults in AuditRepository.ts (lines 55-77).

Step 4: Retrieve for API Consumption

When serving Lighthouse data, getAuditLighthouseData in src/serverFunctions/lighthouse.ts (lines 15-34) orchestrates retrieval:

1. Queries the database row for metadata and r2Key
2. Fetches the full payload from R2 via getJsonFromR2
3. Parses with readStoredLighthousePayload

Step 5: Parse and Filter Issues

The readStoredLighthousePayload function in src/server/lib/lighthousePayload.ts (lines 68-77) validates against storedLighthousePayloadSchema and builds issue reports. Category filtering (performance, accessibility, SEO, best-practices) happens at this stage.

Step 6: Expose via Server Functions

Two endpoints serve client requests:

• getAuditLighthouseIssues: Returns filtered issue arrays
• exportAuditLighthouseIssues: Generates downloadable JSON in three modes — full payload, issues only, or single-category issues

Running Lighthouse During Site Audits

Trigger a Lighthouse capture for any page with this pattern:

import { fetchAndStoreLighthouseResult } from "@/server/lib/audit/lighthouse";

await fetchAndStoreLighthouseResult({
  url: "https://example.com/blog",
  pageId: "page-123",
  strategy: "mobile",           // "mobile" | "desktop"
  billingCustomer: billingContext,
  projectId: "proj-456",
  auditId: "audit-789",
});

The strategy parameter determines the audit device profile and becomes part of the R2 storage key.

Reading Lighthouse Issues via API

Retrieve parsed issues for analysis or display:

import { getAuditLighthouseIssues } from "@/serverFunctions/lighthouse";

const response = await getAuditLighthouseIssues({
  method: "POST",
  body: JSON.stringify({ resultId: "lighthouse-abc123" }),
});

console.log(response.issues); // StoredLighthouseIssue[]

Each issue includes severity, description, and remediation guidance parsed from the raw Lighthouse report.

Exporting Lighthouse Data

Generate downloadable reports in three formats:

import { exportAuditLighthouseIssues } from "@/serverFunctions/lighthouse";

const exportFile = await exportAuditLighthouseIssues({
  method: "POST",
  body: JSON.stringify({
    resultId: "lighthouse-abc123",
    mode: "issues",               // "full" | "issues" | "category"
    category: "performance",      // optional, when mode is "category"
  }),
});

// exportFile.filename: "lighthouse-mobile-2024-08-09-issues.json"
// exportFile.content: JSON string ready for download

Key Architectural Decisions

Retry logic with backoff: The fetchLighthouseResult implementation prioritizes reliability over speed, retrying failed DataForSEO calls up to three times with increasing delays.

Payload size optimization: The storedLighthousePayloadSchema deliberately filters the raw Lighthouse JSON to preserve only actionable issue data, reducing storage costs and transfer times.

Separation of concerns: Database queries never handle multi-megabyte JSON payloads. The r2Key indirection keeps connection pools and query caches efficient.

Core Source Files

File	Responsibility
src/server/lib/audit/lighthouse.ts	DataForSEO API integration, R2 storage operations
src/server/features/audit/repositories/AuditRepository.ts	Database persistence for Lighthouse results
src/server/lib/lighthousePayload.ts	Payload parsing, issue extraction, export generation
src/server/lib/lighthouseStoredPayload.ts	Zod schema defining stored data structure
src/serverFunctions/lighthouse.ts	Server-function endpoints for data retrieval and export
src/db/audit.schema.ts	Database schema including r2_key column definition

Summary

• OpenSEO uses DataForSEO's Lighthouse API with automatic retry logic for reliable data collection
• Dual storage: R2 for raw JSON payloads, PostgreSQL for metadata and R2 references
• Storage keys follow site-audit/<projectId>/<auditId>/<pageId>-<strategy>.json
• Issue parsing and category filtering happen at retrieval time, not during storage
• Export flexibility supports full payloads, issues-only, or single-category views

Frequently Asked Questions

How does OpenSEO handle DataForSEO API failures?

The fetchLighthouseResult function in src/server/lib/audit/lighthouse.ts implements three retries with exponential back-off starting at 2 seconds. This captures transient network issues without manual intervention.

What Lighthouse data is kept in the database versus R2?

The database stores minimal fields: performance scores, timing metrics, audit metadata, and the r2Key. The complete JSON response—including full diagnostic details, screenshots, and network logs—resides in R2 object storage only.

Can I filter Lighthouse issues by category when retrieving data?

Yes. The getAuditLighthouseIssues endpoint accepts a category parameter, and readStoredLighthousePayload filters issues accordingly. Valid categories mirror Lighthouse's standard audits: performance, accessibility, best-practices, and SEO.

How are Lighthouse storage costs optimized?

OpenSEO reduces payload size through storedLighthousePayloadSchema, which selectively retains issue-level data while discarding verbose Lighthouse fields like screenshot thumbnails and redundant network entries. This typically reduces stored size by 60-80% compared to raw API responses.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}