How OpenSEO Manages Lighthouse Data Storage: Cloudflare R2 and Database Architecture

OpenSEO stores Lighthouse audit results in Cloudflare R2 object storage while keeping only lightweight metadata and reference keys in PostgreSQL or SQLite, creating a scalable hybrid architecture that separates heavy JSON payloads from relational queries.

OpenSEO is an open-source SEO platform that processes Lighthouse audits through the DataforSEO API. To handle the large JSON payloads generated by these audits without bloating the primary database, the codebase implements a hybrid storage pattern that offloads raw data to Cloudflare R2 while maintaining performant relational access paths.

The Hybrid Storage Architecture

The storage strategy separates concerns between cheap, durable object storage and fast relational lookups. Raw Lighthouse JSON payloads—which can range from hundreds of kilobytes to several megabytes—are stored in Cloudflare R2, while the PostgreSQL or SQLite database retains only metadata, summary scores, and a reference pointer (r2Key).

This design ensures that database rows remain lightweight for indexing and querying, while the full audit details remain accessible on-demand via the object storage key.

Fetching and Storing Audit Results

Running the Audit with DataforSEO

The process begins in src/server/lib/audit/lighthouse.ts with the fetchLighthouseResult function. This utility calls the DataforSEO client (createDataforseoClient) to execute the Lighthouse audit and returns a LighthouseFetchResult containing both a typed summary and the raw JSON string.

// Run a Lighthouse audit for a specific page
const fetchResult = await fetchLighthouseResult(
  "https://example.com",
  "page-123",
  "mobile",
  billingContext,
);

The function returns payloadJson—the complete Lighthouse report as a string—alongside the parsed summary data.

Persisting Payloads to Cloudflare R2

Once fetched, storeLighthouseResult handles the persistence logic. Rather than inserting the JSON into the database, it delegates to putTextToR2 (defined in src/server/lib/r2.ts) to write the payload to Cloudflare R2.

The function generates a deterministic key path:

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

// Store the result – payload goes to R2, reference key saved in DB
const storedResult = await storeLighthouseResult({
  projectId: "proj-42",
  auditId: "audit-99",
  fetched: fetchResult,
});

console.log("R2 key:", storedResult.r2Key);
// Output: site-audit/proj-42/audit-99/page-123-mobile.json

The database receives only the r2Key string, keeping the relational row size minimal.

Database Schema and References

The database schema in src/db/audit.schema.ts defines the r2_key column (mapped as r2Key in TypeScript models) as an optional string field. This column stores the R2 object key, acting as a foreign reference to the actual payload.

By maintaining this pointer in the relational database, OpenSEO can query audit metadata efficiently without loading the full JSON into memory, deferring the heavy payload retrieval until explicitly requested.

Retrieving Stored Results

When clients request a Lighthouse audit, src/serverFunctions/lighthouse.ts performs a two-step retrieval process:

1. Query the database for the audit record to obtain the r2Key
2. Fetch the raw JSON from R2 using getJsonFromR2

After retrieval, the system validates the JSON structure using the Zod schema defined in src/server/lib/lighthouseStoredPayload.ts (storedLighthousePayloadSchema).

// Retrieve the audit metadata
const audit = await getAuditLighthouseData({ id: storedResult.id });

// Pull the raw JSON from R2
const rawJson = await getJsonFromR2(audit.r2Key);

// Validate and parse the payload
const payload = storedLighthousePayloadSchema.parse(JSON.parse(rawJson));

This pattern ensures type safety while keeping the API responsive, as database queries never carry the weight of the full audit payload.

GDPR Compliance and Data Erasure

OpenSEO implements comprehensive data deletion through src/server/gdpr/storage-erasure.ts. When a user requests data erasure, the system enumerates all r2Key values from the audit table and issues env.R2.delete commands to remove the corresponding objects from Cloudflare R2.

This approach ensures that raw Lighthouse payloads are completely purged from object storage when user data is erased, maintaining compliance with privacy regulations while avoiding orphaned files in R2.

Summary

• Hybrid architecture: Heavy JSON payloads live in Cloudflare R2 while PostgreSQL/SQLite stores lightweight metadata and R2 keys.
• Deterministic keys: R2 objects use structured paths (site-audit/project/audit/page-strategy.json) for consistent addressing.
• Type-safe retrieval: The storedLighthousePayloadSchema Zod validator ensures data integrity when reading from object storage.
• GDPR compliance: The erasure utility cleans up both database records and R2 objects to prevent data retention beyond user requests.
• Cost efficiency: Separating storage tiers reduces database storage costs while maintaining query performance for audit metadata.

Frequently Asked Questions

Where does OpenSEO store the actual Lighthouse JSON data?

OpenSEO stores the actual Lighthouse JSON data in Cloudflare R2 object storage, not in the primary database. The PostgreSQL or SQLite database only stores a reference key (r2Key) pointing to the R2 object location, along with summary metadata like scores and timestamps.

How does OpenSEO handle data deletion for GDPR compliance?

The system handles GDPR deletion through the utility in src/server/gdpr/storage-erasure.ts. When erasing user data, it queries all r2Key values associated with the user's audits and programmatically deletes each corresponding object from Cloudflare R2 using env.R2.delete, ensuring complete removal of raw audit payloads.

What schema validates the Lighthouse data when retrieved from storage?

The storedLighthousePayloadSchema defined in src/server/lib/lighthouseStoredPayload.ts validates the data. When fetching from R2 via getJsonFromR2, the system parses the JSON and runs it through this Zod schema to ensure type safety and data integrity before returning it to the application layer.

Why does OpenSEO use R2 instead of storing JSON directly in the database?

OpenSEO uses R2 to avoid database bloat and performance degradation. Lighthouse JSON payloads are large and compress poorly in relational rows; storing them in R2 keeps database queries fast and storage costs low, while R2 provides durable, S3-compatible object storage for the heavy audit data.

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