Site Audit Crawling Data Model and Duplicate Content Detection in Open-SEO

Open-SEO implements a normalized relational schema across five core tables—audits, audit_pages, audit_links, audit_issues, and audit_lighthouse_results—where duplicate content is detected by computing a SHA-256 hash of visible body text stored in the contentHash column and grouping matching hashes post-crawl.

The open-source SEO toolkit Open-SEO persists comprehensive crawl data using Drizzle ORM to enable scalable site audit crawling and accurate duplicate content detection. Defined in src/db/audit.schema.ts, the data model separates execution metadata, page content, link graphs, and performance metrics into distinct tables optimized for querying and analysis.

Core Data Model for Site Audit Crawling

The schema orchestrates crawl data across five interconnected tables that capture every aspect of a site audit operation.

The audits Table (Execution Metadata)

Each audit run begins with a row in the audits table, which tracks high-level execution state. Key columns include id, projectId, startUrl, status, pagesCrawled, and pagesTotal, along with timestamps marking the crawl lifecycle. This table serves as the parent record for all subsequent crawl data, referenced by auditId foreign keys throughout the schema.

The audit_pages Table (Page-Level Data)

The audit_pages table stores one row per crawled URL and contains the fields necessary for content analysis and duplicate detection. Critical columns include url, statusCode, title, metaDescription, wordCount, isIndexable, and contentHash—the SHA-256 digest of the page's visible body text. Additional metrics such as imagesMissingAlt, internalLinkCount, and externalLinkCount support comprehensive on-page SEO analysis.

Supporting Tables (Links, Issues, and Performance)

Three additional tables complete the relational model:

• audit_links: An edge table mapping sourcePageId to targetUrl with boolean flags for isInternal and isNofollow, enabling full link graph reconstruction.
• audit_issues: Stores detected problems like broken links with issueType, severity, and detailsJson columns linked to specific pages via pageId.
• audit_lighthouse_results: Persists Lighthouse performance scores including strategy (mobile/desktop) and performanceScore per page.

How Duplicate Content Detection Works

Open-SEO identifies duplicate content through a deterministic hashing strategy applied during the crawling phase and analyzed after completion.

The SHA-256 Content Hashing Strategy

During page processing, the crawler extracts visible body text and computes a SHA-256 hash stored in the contentHash column of audit_pages. As defined in src/db/audit.schema.ts, this column captures a deterministic fingerprint of the page's textual content:

// src/db/audit.schema.ts
// SHA-256 of the visible body text, for duplicate-content grouping
contentHash: text("content_hash"),

This approach ensures that pages with identical visible text produce identical hashes regardless of URL differences, query parameters, or navigation elements, while storage in a normalized table with indexed columns makes grouping operations efficient.

Querying for Duplicate Content

After crawling completes, the system identifies duplicates by aggregating rows with matching contentHash values. The detection logic executes a grouped count query filtered by audit ID:

SELECT content_hash, COUNT(*) AS dupCount
FROM audit_pages
WHERE audit_id = ?
GROUP BY content_hash
HAVING dupCount > 1;

Pages returned by this query surface in the audit report's Duplicate Content section, allowing users to identify URLs that provide identical textual information.

Implementation Details

The detection pipeline relies on specific schema definitions and database operations implemented in TypeScript using Drizzle ORM.

Schema Definition in src/db/audit.schema.ts

The data model resides in src/db/audit.schema.ts, where the audit_pages table definition includes the contentHash field alongside other crawl metadata. This normalized structure keeps content fingerprints separate from titles and meta descriptions, ensuring duplicate detection focuses strictly on body text similarity rather than HTML structure or URL patterns.

Storing Crawled Pages with Content Hashes

When persisting crawl results in src/serverFunctions/audit.ts, the system hashes the extracted text before insertion. The following pattern demonstrates computing and storing the content hash using Node.js crypto:

// Example: Insert a crawled page (simplified)
import { db } from "@/db/provider";
import { auditPages } from "@/db/audit.schema";
import { v4 as uuid } from "uuid";
import crypto from "crypto";

async function storeCrawledPage(auditId: string, url: string, html: string) {
  const text = extractVisibleText(html);               // custom extractor
  const hash = crypto.createHash("sha256")
                    .update(text)
                    .digest("hex");

  await db.insert(auditPages).values({
    id: uuid(),
    auditId,
    url,
    title: extractTitle(html),
    contentHash: hash,
    // …other fields
  });
}

Retrieving Duplicate Groups

To surface duplicates in the UI, the application queries for hashes appearing multiple times within a specific audit. The following Drizzle ORM implementation aggregates URLs by content hash using GROUP_CONCAT:

// Example: Get duplicate-content pages for a specific audit
import { db } from "@/db/provider";
import { auditPages } from "@/db/audit.schema";

async function getDuplicatePages(auditId: string) {
  const dupRows = await db
    .select({
      contentHash: auditPages.contentHash,
      dupCount: db.sql<number>`COUNT(*)`,
      pages: db.sql<string[]>`GROUP_CONCAT(${auditPages.url})`,
    })
    .from(auditPages)
    .where(auditPages.auditId.eq(auditId))
    .groupBy(auditPages.contentHash)
    .having(db.sql<number>`COUNT(*) > 1`);

  return dupRows.map(row => ({
    contentHash: row.contentHash,
    count: row.dupCount,
    urls: row.pages?.split(",") ?? [],
  }));
}

Summary

• Open-SEO's site audit crawling data model uses five normalized tables (audits, audit_pages, audit_links, audit_issues, audit_lighthouse_results) defined in src/db/audit.schema.ts.
• Duplicate content detection relies on a SHA-256 hash of visible body text stored in the contentHash column of audit_pages.
• The hashing strategy is deterministic, ensuring identical page content produces identical hashes regardless of URL variations or query parameters.
• Post-crawl aggregation queries group pages by contentHash to identify duplicates efficiently using indexed database columns.
• The implementation uses Drizzle ORM with TypeScript, separating content fingerprints from metadata to maintain focus on textual similarity.

Frequently Asked Questions

How does Open-SEO store crawled page data during a site audit?

Open-SEO persists crawled data across five normalized tables in a relational database. The audits table tracks execution metadata, while audit_pages stores individual URL data including status codes, titles, word counts, and content hashes. Link relationships populate audit_links, technical issues populate audit_issues, and Lighthouse scores populate audit_lighthouse_results, all linked via auditId foreign keys.

What hashing algorithm does Open-SEO use for duplicate content detection?

The system uses SHA-256 to generate a hexadecimal hash of the visible body text extracted from each crawled page. This hash is stored in the contentHash column of the audit_pages table, enabling deterministic duplicate grouping regardless of URL differences or HTML structure variations.

Where is the site audit data model defined in the codebase?

The complete schema definition resides in src/db/audit.schema.ts, which exports Drizzle ORM table definitions including the auditPages schema with its contentHash field. Database connections are managed through src/db/provider.ts, while crawl orchestration logic appears in src/serverFunctions/audit.ts.

How are duplicate content issues queried in the database?

The system executes a SQL GROUP BY query on the contentHash column with a HAVING COUNT(*) > 1 clause to identify hashes appearing multiple times within a single audit. The Drizzle ORM implementation selects the hash value, count, and concatenated URLs to return complete duplicate groups for reporting.

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