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

> Explore the Open-SEO site audit crawling data model and learn how it detects duplicate content using SHA-256 hashing of body text. Understand the core tables and efficient duplicate identification.

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

---

**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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/src/db/audit.schema.ts), this column captures a deterministic fingerprint of the page's textual content:

```ts
// 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:

```sql
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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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:

```ts
// 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`:

```ts
// 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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/src/db/provider.ts), while crawl orchestration logic appears in [`src/serverFunctions/audit.ts`](https://github.com/every-app/open-seo/blob/main/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.