# How OpenSEO Caches Lighthouse Audit Results: R2 Storage and Database Hybrid Strategy

> Discover how OpenSEO caches Lighthouse audit results using Cloudflare R2 and PostgreSQL. Learn about their hybrid strategy for cost-effective data retrieval.

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

---

**OpenSEO caches Lighthouse audit results by storing the full JSON payload in Cloudflare R2 object storage while maintaining a lightweight `r2Key` reference in a PostgreSQL database, enabling cost-effective retrieval without re-running expensive audits.**

The `every-app/open-seo` repository implements a "store-once-read-many" caching layer that separates bulk data storage from relational metadata. This approach eliminates redundant calls to DataForSEO's API while keeping database queries fast and memory-efficient.

## The Lighthouse Caching Pipeline

OpenSEO's caching mechanism follows a four-stage pipeline: fetch, persist, reference, and retrieve. Each stage is optimized to minimize latency and storage costs.

### Running the Initial Audit

The process begins in [`src/server/lib/audit/lighthouse.ts`](https://github.com/every-app/open-seo/blob/main/src/server/lib/audit/lighthouse.ts) where `fetchLighthouseResult` (lines 25-50) calls DataForSEO's `lighthouse.live` endpoint. This function returns a structured `LighthouseResult` object alongside the complete raw JSON payload (`payloadJson`), ensuring the system captures all audit details in a single API request.

### Persisting to Cloudflare R2

Once fetched, `storeLighthouseResult` (lines 74-92) handles the actual caching logic. It uploads the `payloadJson` to R2 using a deterministic key pattern:

```typescript
// R2 key structure
`site-audit/<projectId>/<auditId>/<pageId>-<strategy>.json`

```

This function enriches the result with `r2Key` (the storage path) and `payloadSizeBytes`, then returns the metadata for database persistence. By offloading the multi-megabyte JSON to R2, OpenSEO avoids database bloat while maintaining immutable audit history.

### Storing Metadata References

The relational database only stores searchable metadata. 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` method (lines 24-50) inserts a row into the `auditLighthouseResults` table containing the `r2Key`, audit identifiers, and computed metrics. This reference record typically measures under 1 KB, even when the R2 payload exceeds 10 MB.

## Retrieving Cached Lighthouse Data

When clients request audit results, OpenSEO reconstructs the full report using the stored reference key without re-running the lighthouse process.

### Database Lookup and R2 Retrieval

The retrieval flow starts in [`src/serverFunctions/lighthouse.ts`](https://github.com/every-app/open-seo/blob/main/src/serverFunctions/lighthouse.ts) where `getAuditLighthouseData` queries the database for the `r2Key` associated with a specific `resultId`. It then fetches the actual JSON from R2 via `getJsonFromR2` located in [`src/server/lib/r2.ts`](https://github.com/every-app/open-seo/blob/main/src/server/lib/r2.ts) (lines 3-10). This separation allows the database connection pool to remain lightweight while R2 handles the heavy I/O.

### Parsing and Filtering Results

Raw R2 data flows through `readStoredLighthousePayload` in [`src/server/lib/lighthousePayload.ts`](https://github.com/every-app/open-seo/blob/main/src/server/lib/lighthousePayload.ts) (lines 30-78). This parser extracts performance scores, Core Web Vitals metrics, and generates a sorted array of issues. It supports category filtering (performance, accessibility, SEO, best practices), allowing the UI to request specific audit categories without downloading or parsing the full payload.

## API Endpoints for Cached Results

OpenSEO exposes two primary server functions that leverage this caching layer:

- **`getAuditLighthouseIssues`** (lines 15-44 of [`src/serverFunctions/lighthouse.ts`](https://github.com/every-app/open-seo/blob/main/src/serverFunctions/lighthouse.ts)): Returns parsed scores, metrics, and a filtered issue list for dashboard rendering
- **`exportAuditLighthouseIssues`** (lines 46-88): Generates downloadable JSON exports in three modes (`full`, `issues`, or `category`), streaming directly from the R2 cache

Both endpoints execute in sub-second time because they avoid the 30-60 seconds typically required for a fresh Lighthouse audit.

## Implementation Example

The following pattern demonstrates the complete caching workflow:

```typescript
// Stage 1: Run audit and cache
const fetchResult = await fetchLighthouseResult(
  "https://example.com",
  "page-123",
  "mobile",
  billingCustomer,
);
const storedResult = await storeLighthouseResult({
  projectId,
  auditId,
  fetched: fetchResult,
});
// storedResult.r2Key now points to "site-audit/proj-1/audit-5/page-123-mobile.json"

// Stage 2: Retrieve cached data for UI
const { issues, scores, metrics } = await getAuditLighthouseIssues({
  resultId: storedResult.id,
  projectId,
});

// Stage 3: Export cached audit data
const exportFile = await exportAuditLighthouseIssues({
  resultId: storedResult.id,
  projectId,
  mode: "issues", // Options: "full" | "issues" | "category"
});

```

## Summary

- **Hybrid architecture**: OpenSEO caches Lighthouse audit results using Cloudflare R2 for JSON payloads (via `storeLighthouseResult`) and PostgreSQL for metadata references (via `AuditRepository`)
- **Deterministic addressing**: R2 objects use structured keys (`site-audit/<projectId>/<auditId>/<pageId>-<strategy>.json`) ensuring consistent cache locations and preventing duplicate storage
- **Performance optimization**: The database stores only the `r2Key` and metrics, while `getJsonFromR2` streams large payloads on-demand
- **Flexible consumption**: `readStoredLighthousePayload` enables category-specific filtering and sorted issue lists without re-parsing raw Lighthouse JSON on the client
- **Cost efficiency**: The "store-once-read-many" strategy eliminates redundant DataForSEO API usage and reduces compute costs for repeated audit views

## Frequently Asked Questions

### What storage service does OpenSEO use for caching Lighthouse results?

OpenSEO uses **Cloudflare R2** object storage to persist the raw Lighthouse JSON payloads. The relational database maintains only the `r2Key` string and lightweight metadata, keeping query times fast while leveraging R2's cost structure for large audit files that can exceed 5 MB per page.

### How does OpenSEO determine the cache key for Lighthouse audits?

Cache keys follow a deterministic pattern implemented in `storeLighthouseResult`: `site-audit/<projectId>/<auditId>/<pageId>-<strategy>.json`. This structure ensures that identical page and strategy (mobile/desktop) combinations always map to the same R2 object, preventing duplicate storage and enabling predictable cache invalidation.

### Can I export data from OpenSEO's Lighthouse cache?

Yes. The `exportAuditLighthouseIssues` function in [`src/serverFunctions/lighthouse.ts`](https://github.com/every-app/open-seo/blob/main/src/serverFunctions/lighthouse.ts) supports three export modes: `full` (complete raw JSON), `issues` (processed findings with scores), and `category` (filtered by audit category). These exports read directly from the R2 cache via the stored `r2Key`, ensuring exported data matches the original audit exactly.

### Does OpenSEO re-run Lighthouse if cached data exists?

No. OpenSEO strictly follows a "store-once-read-many" strategy. Once `storeLighthouseResult` persists the payload to R2 and `AuditRepository` saves the reference, subsequent calls to `getAuditLighthouseIssues` retrieve the cached data from R2 without triggering new DataForSEO API calls, significantly reducing latency and API costs.