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

> OpenSEO captures Lighthouse performance data using DataForSEO API, stores JSON in Cloudflare R2, and references scores in PostgreSQL. Learn the technical details.

- Repository: [Every App/open-seo](https://github.com/every-app/open-seo)
- Tags: deep-dive
- Published: 2026-08-09

---

**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`](https://github.com/every-app/open-seo/blob/main/src/server/features/audit/repositories/AuditRepository.ts), the `insertLighthouseResults` function handles database persistence:

```typescript
// 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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/AuditRepository.ts) (lines 55-77).

### Step 4: Retrieve for API Consumption

When serving Lighthouse data, `getAuditLighthouseData` in [`src/serverFunctions/lighthouse.ts`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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:

```typescript
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:

```typescript
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:

```typescript
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`](https://github.com/every-app/open-seo/blob/main/src/server/lib/audit/lighthouse.ts) | DataForSEO API integration, R2 storage operations |
| [`src/server/features/audit/repositories/AuditRepository.ts`](https://github.com/every-app/open-seo/blob/main/src/server/features/audit/repositories/AuditRepository.ts) | Database persistence for Lighthouse results |
| [`src/server/lib/lighthousePayload.ts`](https://github.com/every-app/open-seo/blob/main/src/server/lib/lighthousePayload.ts) | Payload parsing, issue extraction, export generation |
| [`src/server/lib/lighthouseStoredPayload.ts`](https://github.com/every-app/open-seo/blob/main/src/server/lib/lighthouseStoredPayload.ts) | Zod schema defining stored data structure |
| [`src/serverFunctions/lighthouse.ts`](https://github.com/every-app/open-seo/blob/main/src/serverFunctions/lighthouse.ts) | Server-function endpoints for data retrieval and export |
| [`src/db/audit.schema.ts`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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.