Design a Backlink Monitor System

A complete architectural blueprint — rolling verification schedules, fetch coalescing, transactional outboxes, Vitess database sharding, CDC to ClickHouse, and 1M to 100M edge capacity planning.

🔗 Edge Tracking ⚡ Fetch Coalescing 🗄️ Vitess Sharding 📊 CDC & ClickHouse ⏱️ Distributed Scheduler

In Search Engine Optimization (SEO) and web analytics, tracking external backlinks pointing to a customer's website is vital for authority building, risk auditing, and competitor analysis. In this chapter, we design an enterprise-grade distributed Backlink Monitoring System capable of monitoring millions to 100 million active backlink edges continuously.

1. Understand the Problem & Scope

A backlink is a directed logical edge from a source URL (referring web page) to a target URL (monitored customer page). Designing a backlink monitoring system differs significantly from building a general web crawler: rather than recursively discovering the entire web graph, a backlink monitor performs targeted, scheduled revalidation of known edges, tracking state transitions over time.

Clarifying Questions & Scope Confirmation

Candidate: What is the core functionality of the backlink monitoring system?
Interviewer: The system allows customers to monitor backlinks pointing to their domains. It must detect state changes: New, Lost, Recovered, Redirected, NoFollow tag changes, or target page status changes (e.g., 404, 500, indexability).
Candidate: What scale are we targeting?
Interviewer: Design for a 10 million backlink baseline initially, with a clear architectural roadmap to scale up to 100 million backlink edges.
Candidate: How often should each backlink be verified?
Interviewer: Every eligible backlink edge should be checked at least once during a rolling 7-day window. Missing backlinks should be confirmed within 2 hours under normal load.
Candidate: How long should raw HTML and observation history be stored?
Interviewer: Transactional state stores current status and recent changes. Historical observation analytics follow the tenant's contractual retention policy and deletion obligations, while raw HTML content is deduplicated and normally expires after 30 days.

Functional & Non-Functional Requirements

Functional Requirements
  • Track durable availability states (UNKNOWN, LIVE, LOST) separately from transition events (NEW, LOST, RECOVERED) and link properties such as redirects.
  • Monitor anchor text, rel flags (nofollow, ugc, sponsored), and DOM location.
  • Support source and target HTTP evaluation, including X-Robots-Tag, the HTTP Link canonical relation, and HTML canonical elements.
  • Provide configurable multi-channel alert delivery (Email, Slack, Webhooks).
  • Support bulk candidate imports from third-party SEO data providers.
Non-Functional Requirements
  • High Efficiency: Shared fetching so one source URL is requested only once per window even if monitored by multiple projects.
  • Idempotency: No duplicate alerts or lost status transitions during worker retries or failures.
  • Low Dashboard Latency: Transactional queries for customer dashboards complete in < 50ms without scanning full history.
  • Politeness & Safety: Strict per-host crawling rate limits and obey robots.txt.

MVP Boundary

In Scope for MVP Deferred Until a Capacity Gate
Projects, target definitions, manual/API/provider candidate imports, scheduled source verification, target health, current-state dashboards, alerts, and auditable observations A proprietary web-scale discovery index, browser rendering by default, multi-region active-active writes, and Vitess sharding before measured single-writer limits justify it

Acceptance Criteria and Measurement Points

Requirement Testable Acceptance Criterion
Weekly coverage At least 99.9% of eligible source URLs receive complete validation within seven rolling days. “Eligible” means active, admitted within quota, permitted by policy/robots, and not blocked by an explicitly reported host-capacity exception.
Missing-link confirmation p95 confirmation completes within two hours measured from the first complete missing-link observation, under normal admitted load.
Dashboard latency p95 API service time is below 50 ms for defined current-state list and summary queries, excluding client network time.
Alert latency p95 first delivery attempt occurs within five minutes of a committed transition; duplicate delivery rate is below 0.01% when receivers honor the idempotency key.
Availability and recovery Customer API availability is 99.9% monthly; initial recovery objectives are RPO ≤ 5 minutes and RTO ≤ 60 minutes, validated by restore drills.
Evidence safety No truncated parse, network failure, target outage, or unavailable artifact may increment the source-link miss counter or produce a lost transition.

Minimum API Contract

The MVP exposes versioned tenant-scoped APIs for POST/GET /v1/projects, POST/GET/DELETE /v1/projects/{project_id}/targets, POST /v1/projects/{project_id}/imports, GET /v1/projects/{project_id}/backlinks, GET /v1/backlinks/{backlink_id}/observations, and alert-rule management. List APIs use stable cursor pagination, reject cross-tenant identifiers before database access, support an Idempotency-Key header on mutations, and return a request ID plus machine-readable error code. OpenAPI and event schemas are versioned artifacts and form part of acceptance testing.

2. Back-of-the-Envelope Estimation

Network fetch volume is determined by the number of distinct source URLs after deduplication, rather than the raw edge count.

1M, 10M & 100M Edge Capacity Envelopes
Scale Stage Distinct Source Ratio Scenario Distinct Source URLs Daily Source Validations Avg QPS Recommended Peak Capacity
1 Million Edges (7-day window) Base Case (1.6:1) 625,000 89,300 / day 1.03 req/sec 5–10 req/sec
One Source per Edge (1:1) 1.0 Million 142,850 / day 1.65 req/sec 10–15 req/sec
10 Million Edges (7-day window) Strong Source Reuse (3:1) 3.33 Million 476,200 / day 5.51 req/sec 25–40 req/sec
Base Case (1.6:1) 6.25 Million 893,000 / day 10.34 req/sec 50–75 req/sec
One Source per Edge (1:1) 10.0 Million 1.43 Million / day 16.5 req/sec 75–125 req/sec
100 Million Edges (7-day window) Base Case (1.6:1) 62.5 Million 8.93 Million / day 103.34 req/sec 500–750 req/sec
One Source per Edge (1:1) 100.0 Million 14.29 Million / day 165.0 req/sec 750–1,250 req/sec

Bandwidth & Conditional GET Savings

Assuming an average compressed HTML response body of 250 KB per referring page:

  • Full-Body Transfer without caching: At 1M edges (625K URLs/week) = 156 GB / week; at 10M edges (6.25M URLs/week) = 1.56 TB / week; at 100M edges (62.5M URLs/week) = 15.6 TB / week.
  • With Conditional Headers (ETag / If-Modified-Since): Assuming a 70% 304 Not Modified rate across weekly recrawls, response-body bandwidth drops to approximately 47 GB / week at 1M edges, 469 GB / week at 10M edges, and 4.69 TB / week at 100M edges.

These are successful source-page body transfers only. Production capacity must separately budget for redirects, robots requests, confirmation and network retries, target-page checks, browser assets, object-storage traffic, Kafka replication, and CDC. Size workers and brokers from measured p95/p99 service time and maintain at least 2× recovery headroom over the forecast peak.

Storage Growth Math

  • Current Edge State (base-row floor): 1M edges = ~500 MB; 10M edges = ~5 GB; 100M edges = ~50 GB at 500 bytes per logical row. Capacity planning must add secondary indexes, URL/domain dictionaries, occurrences, jobs, rollups, free space, backups, and replica copies; validate the multiplier with production-shaped data.
  • Immutable Observations (ClickHouse planning baseline): 1M edges = 52M rows / year; 10M edges = 520M rows / year; 100M edges = 5.2B rows / year. Byte estimates must be benchmarked with real anchor-text and metadata distributions rather than treated as a fixed compression ratio.

3. High-Level Architecture

The system decouples network fetching, raw body storage, offline HTML parsing, project matching, and alert processing into separate asynchronous pipeline stages connected via message queues and an event backbone.

Distributed Pipeline & Data Flow Topology
 [ Cron / Trigger ] ──► [ Global Scheduler ] ──► [ Jobs Ledger ]
                              │
                              ▼
                [ Fetch Queue (Kafka / Broker) ]
                              │
                              ▼
                [ Distributed HTTP Fetchers ]
                              │
           ┌──────────────────┴──────────────────┐
           ▼                                     ▼
 [ Compressed HTML Storage ]           [ DOM / Link Parsers ]
                                                 │
                                                 ▼
                                     [ Project Matcher Workers ]
                                                 │
               ┌─────────────────────────────────┴─────────────────────────────────┐
               ▼                                                                   ▼
 [ Project State MySQL Shards ]                           [ ClickHouse Analytics Engine ]
(Current Edges & Alerts Outbox)                          (Immutable Fact Stream via CDC)
               │
               ▼
 [ Alert Dispatch Workers ] ──► Email / Slack / Webhook

Pipeline Execution Stages

  1. Scheduler: Scans due projects and target source URLs, creating fetch tasks without creating duplicate network jobs.
  2. Fetch Coalescer: Groups pending checks by public source URL, ensuring multiple projects monitoring links on the same external page trigger only one HTTP request.
  3. Source Fetch Workers: Perform HTTP/HTTPS requests with conditional headers (If-None-Match, If-Modified-Since), handling redirects, timeouts, and rate limits. Save changed source bodies to Object Storage.
  4. Target Validation Workers: Coalesce distinct monitored target URLs into lightweight validation jobs that record status, redirect destination, canonical identity, and indexability. Target failures do not prove that a source backlink is missing.
  5. Offline Parsers: Parse DOM trees from stored HTML snapshots, extract outgoing anchor tags, canonical tags, meta directives, and write a light link manifest.
  6. Project Matchers: Compare extracted target links against target rules for subscribing projects. Calculate availability transitions (for example, LIVE ➔ LOST) and emit transition events separately.
  7. Transactional Outbox & CDC: Commit edge state changes into MySQL. An outbox worker emits notification events while MySQL Binlog CDC streams facts into ClickHouse.

4. Database Design & Storage Placement (Developer PRD Spec)

To deliver sub-50ms dashboard response times while maintaining complete historical audit trails, data is tier-placed across MySQL 8.0+ / Vitess (System of Record for current state and transactional queues), ClickHouse (Analytics engine for immutable observation history), and Object Storage (Raw compressed HTML snapshots). Identifiers use application-generated UUIDv7 stored as BINARY(16) for sequential primary key locality.

Domain 1: Control Plane, Projects & Operational Quotas

Manages tenant projects, canonical URLs, monitored targets, bulk URL imports, and daily operational quota enforcement.

Table: projects
CREATE TABLE projects (
  id                       BINARY(16) PRIMARY KEY,
  tenant_id                BINARY(16) NOT NULL,
  name                     VARCHAR(255) NOT NULL,
  timezone                 VARCHAR(64) NOT NULL DEFAULT 'UTC',
  recrawl_interval_seconds INT UNSIGNED NOT NULL DEFAULT 86400,
  next_crawl_at            TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  last_crawl_at            TIMESTAMP(6) NULL,
  is_active                BOOLEAN NOT NULL DEFAULT TRUE,
  created_at               TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at               TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
                             ON UPDATE CURRENT_TIMESTAMP(6),
  CONSTRAINT projects_recrawl_positive CHECK (recrawl_interval_seconds > 0),
  UNIQUE KEY projects_tenant_name_uniq (tenant_id, name),
  UNIQUE KEY projects_tenant_id_uniq (tenant_id, id),
  KEY projects_due_idx (is_active, next_crawl_at, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Table: tenant_crawl_limits
CREATE TABLE tenant_crawl_limits (
  tenant_id                BINARY(16) PRIMARY KEY,
  daily_page_check_limit   INT UNSIGNED NOT NULL,
  max_concurrent_checks    INT UNSIGNED NOT NULL DEFAULT 10,
  max_active_jobs          INT UNSIGNED NOT NULL DEFAULT 10000,
  max_targets_per_project  INT UNSIGNED NOT NULL DEFAULT 300,
  max_links_per_page       INT UNSIGNED NOT NULL DEFAULT 10000,
  daily_browser_seconds    INT UNSIGNED NOT NULL DEFAULT 0,
  scheduler_weight         TINYINT UNSIGNED NOT NULL DEFAULT 1,
  updated_at               TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
                             ON UPDATE CURRENT_TIMESTAMP(6)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Table: tenant_crawl_usage_daily
CREATE TABLE tenant_crawl_usage_daily (
  tenant_id                BINARY(16) NOT NULL,
  usage_date               DATE NOT NULL,
  page_checks_reserved     BIGINT UNSIGNED NOT NULL DEFAULT 0,
  page_checks_completed    BIGINT UNSIGNED NOT NULL DEFAULT 0,
  network_fetches          BIGINT UNSIGNED NOT NULL DEFAULT 0,
  bytes_downloaded         BIGINT UNSIGNED NOT NULL DEFAULT 0,
  browser_render_ms        BIGINT UNSIGNED NOT NULL DEFAULT 0,
  provider_rows_ingested   BIGINT UNSIGNED NOT NULL DEFAULT 0,
  updated_at               TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
                             ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (tenant_id, usage_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Table: domains
CREATE TABLE domains (
  id                    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  hostname              VARCHAR(253) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL,
  registrable_domain    VARCHAR(253) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL,
  public_suffix         VARCHAR(253) CHARACTER SET ascii COLLATE ascii_general_ci,
  created_at            TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  UNIQUE KEY domains_hostname_uniq (hostname)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Table: urls
CREATE TABLE urls (
  id                    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  domain_id             BIGINT UNSIGNED NOT NULL,
  normalized_url        VARCHAR(2048) COLLATE utf8mb4_bin NOT NULL,
  normalization_version SMALLINT UNSIGNED NOT NULL,
  url_hash              BINARY(32) NOT NULL,
  scheme                ENUM('http', 'https') NOT NULL,
  path                  VARCHAR(2048) COLLATE utf8mb4_bin NOT NULL DEFAULT '/',
  query_string          TEXT COLLATE utf8mb4_bin,
  created_at            TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  CONSTRAINT urls_domain_fk FOREIGN KEY (domain_id) REFERENCES domains(id),
  UNIQUE KEY urls_hash_uniq (url_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Table: project_targets
CREATE TABLE project_targets (
  id                    BINARY(16) PRIMARY KEY,
  project_id            BINARY(16) NOT NULL,
  scope                 ENUM('domain', 'subdomain', 'url_prefix', 'exact_url') NOT NULL,
  domain_id             BIGINT UNSIGNED,
  url_id                BIGINT UNSIGNED,
  url_prefix            VARCHAR(2048) COLLATE utf8mb4_bin,
  target_key            BINARY(32) NOT NULL,
  include_subdomains    BOOLEAN NOT NULL DEFAULT TRUE,
  is_active             BOOLEAN NOT NULL DEFAULT TRUE,
  is_primary            BOOLEAN NOT NULL DEFAULT FALSE,
  paused_at             TIMESTAMP(6) NULL,
  deleted_at            TIMESTAMP(6) NULL,
  active_target_key     BINARY(32) GENERATED ALWAYS AS
                          (CASE WHEN deleted_at IS NULL THEN target_key ELSE NULL END) STORED,
  primary_project_id    BINARY(16) GENERATED ALWAYS AS
                          (CASE WHEN is_primary = TRUE AND deleted_at IS NULL
                                THEN project_id ELSE NULL END) STORED,
  created_at            TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  CONSTRAINT project_targets_project_fk FOREIGN KEY (project_id)
    REFERENCES projects(id) ON DELETE CASCADE,
  CONSTRAINT project_targets_domain_fk FOREIGN KEY (domain_id) REFERENCES domains(id),
  CONSTRAINT project_targets_url_fk FOREIGN KEY (url_id) REFERENCES urls(id),
  UNIQUE KEY project_targets_active_identity_uniq (project_id, active_target_key),
  UNIQUE KEY one_primary_target_per_project (primary_project_id),
  UNIQUE KEY project_targets_project_id_uniq (project_id, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Table: target_import_batches
CREATE TABLE target_import_batches (
  id                    BINARY(16) PRIMARY KEY,
  project_id            BINARY(16) NOT NULL,
  submitted_count       INT UNSIGNED NOT NULL,
  accepted_count        INT UNSIGNED NOT NULL DEFAULT 0,
  duplicate_count       INT UNSIGNED NOT NULL DEFAULT 0,
  invalid_count         INT UNSIGNED NOT NULL DEFAULT 0,
  status                ENUM('processing', 'completed', 'failed') NOT NULL DEFAULT 'processing',
  created_at            TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  completed_at          TIMESTAMP(6) NULL,
  CONSTRAINT target_import_batches_project_fk FOREIGN KEY (project_id)
    REFERENCES projects(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Table: target_import_items
CREATE TABLE target_import_items (
  batch_id              BINARY(16) NOT NULL,
  row_number            INT UNSIGNED NOT NULL,
  raw_url               TEXT NOT NULL,
  url_id                BIGINT UNSIGNED,
  project_target_id     BINARY(16),
  result                ENUM('accepted', 'duplicate', 'invalid') NOT NULL,
  error_code            VARCHAR(64),
  PRIMARY KEY (batch_id, row_number),
  CONSTRAINT target_import_items_batch_fk FOREIGN KEY (batch_id)
    REFERENCES target_import_batches(id) ON DELETE CASCADE,
  CONSTRAINT target_import_items_url_fk FOREIGN KEY (url_id) REFERENCES urls(id),
  CONSTRAINT target_import_items_target_fk FOREIGN KEY (project_target_id)
    REFERENCES project_targets(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Domain 2: Crawl Scheduler, Shared Fetching & Parse Pipeline

Manages execution runs, fair worker leases, public HTTP fetch coalescing, compressed HTML body metadata, and reusable offline parser manifests.

Table: crawl_runs
CREATE TABLE crawl_runs (
  id                    BINARY(16) PRIMARY KEY,
  tenant_id             BINARY(16) NOT NULL,
  project_id            BINARY(16) NOT NULL,
  idempotency_key       BINARY(32) NOT NULL,
  run_type              ENUM('discovery', 'verification', 'confirmation', 'metrics') NOT NULL,
  status                ENUM('queued', 'running', 'completed', 'partial', 'failed', 'cancelled')
                          NOT NULL DEFAULT 'queued',
  scheduled_at          TIMESTAMP(6) NOT NULL,
  started_at            TIMESTAMP(6) NULL,
  completed_at          TIMESTAMP(6) NULL,
  pages_planned         INT UNSIGNED NOT NULL DEFAULT 0,
  pages_completed       INT UNSIGNED NOT NULL DEFAULT 0,
  pages_failed          INT UNSIGNED NOT NULL DEFAULT 0,
  expansion_completed   BOOLEAN NOT NULL DEFAULT FALSE,
  expansion_completed_at TIMESTAMP(6) NULL,
  error_summary         JSON NOT NULL DEFAULT (JSON_OBJECT()),
  created_at            TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  CONSTRAINT crawl_runs_project_fk FOREIGN KEY (tenant_id, project_id)
    REFERENCES projects(tenant_id, id) ON DELETE CASCADE,
  UNIQUE KEY crawl_runs_idempotency_uniq (idempotency_key),
  UNIQUE KEY crawl_runs_tenant_project_id_uniq (tenant_id, project_id, id),
  KEY crawl_runs_finalize_idx (status, expansion_completed, scheduled_at, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Table: crawl_pages
CREATE TABLE crawl_pages (
  id                    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id             BINARY(16) NOT NULL,
  project_id            BINARY(16) NOT NULL,
  crawl_run_id          BINARY(16) NOT NULL,
  source_url_id         BIGINT UNSIGNED NOT NULL,
  status                ENUM('waiting_fetch', 'waiting_parse', 'queued', 'leased', 'completed', 'failed')
                          NOT NULL DEFAULT 'waiting_fetch',
  priority              SMALLINT NOT NULL DEFAULT 0,
  available_at          TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  lease_owner           VARCHAR(128),
  leased_until          TIMESTAMP(6) NULL,
  attempts              SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  completed_at          TIMESTAMP(6) NULL,
  CONSTRAINT crawl_pages_run_fk FOREIGN KEY (tenant_id, project_id, crawl_run_id)
    REFERENCES crawl_runs(tenant_id, project_id, id) ON DELETE CASCADE,
  CONSTRAINT crawl_pages_source_url_fk FOREIGN KEY (source_url_id) REFERENCES urls(id),
  UNIQUE KEY crawl_pages_run_url_uniq (crawl_run_id, source_url_id),
  KEY crawl_pages_claim_idx (status, available_at, priority, id),
  KEY crawl_pages_expired_lease_idx (status, leased_until, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Table: page_content_snapshots
CREATE TABLE page_content_snapshots (
  id                    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  content_hash          BINARY(32) NOT NULL,
  object_key            VARCHAR(1024) NOT NULL,
  status                ENUM('pending', 'available', 'quarantined', 'deleted') NOT NULL DEFAULT 'pending',
  content_type          VARCHAR(255),
  storage_encoding      ENUM('gzip', 'zstd', 'identity') NOT NULL,
  uncompressed_bytes    BIGINT UNSIGNED NOT NULL,
  stored_bytes          BIGINT UNSIGNED NOT NULL,
  retention_until       TIMESTAMP(6) NULL,
  last_referenced_at    TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  created_at            TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  UNIQUE KEY page_content_hash_uniq (content_hash),
  UNIQUE KEY page_content_object_key_uniq (object_key),
  KEY page_content_retention_idx (status, retention_until, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Table: page_fetch_jobs
CREATE TABLE page_fetch_jobs (
  id                    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  source_url_id         BIGINT UNSIGNED NOT NULL,
  fetch_profile_hash    BINARY(32) NOT NULL,
  render_mode           ENUM('http', 'browser') NOT NULL DEFAULT 'http',
  validation_deadline   TIMESTAMP(6) NOT NULL,
  coalescing_key        BINARY(32) NOT NULL,
  status                ENUM('queued', 'leased', 'completed', 'failed') NOT NULL DEFAULT 'queued',
  priority              SMALLINT NOT NULL DEFAULT 0,
  available_at          TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  attempts              SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  lease_owner           VARCHAR(128),
  leased_until          TIMESTAMP(6) NULL,
  fetched_at            TIMESTAMP(6) NULL,
  http_status           SMALLINT UNSIGNED,
  validation_method     ENUM('full_body', 'not_modified', 'no_body'),
  final_url_id          BIGINT UNSIGNED,
  content_snapshot_id   BIGINT UNSIGNED,
  etag                  VARCHAR(1024),
  last_modified_at      TIMESTAMP(6) NULL,
  response_headers      JSON NOT NULL DEFAULT (JSON_OBJECT()),
  response_time_ms      INT UNSIGNED,
  failure_code          VARCHAR(64),
  created_at            TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  CONSTRAINT page_fetch_jobs_source_fk FOREIGN KEY (source_url_id) REFERENCES urls(id),
  CONSTRAINT page_fetch_jobs_final_url_fk FOREIGN KEY (final_url_id) REFERENCES urls(id),
  CONSTRAINT page_fetch_jobs_content_fk FOREIGN KEY (content_snapshot_id)
    REFERENCES page_content_snapshots(id),
  UNIQUE KEY page_fetch_jobs_coalescing_uniq (coalescing_key),
  KEY page_fetch_jobs_claim_idx (status, available_at, priority, id),
  KEY page_fetch_jobs_expired_lease_idx (status, leased_until, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Table: target_validation_jobs
CREATE TABLE target_validation_jobs (
  id                    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  target_url_id         BIGINT UNSIGNED NOT NULL,
  validation_window     TIMESTAMP(6) NOT NULL,
  coalescing_key        BINARY(32) NOT NULL,
  status                ENUM('queued', 'leased', 'completed', 'failed') NOT NULL DEFAULT 'queued',
  priority              SMALLINT NOT NULL DEFAULT 0,
  available_at          TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  attempts              SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  lease_owner           VARCHAR(128),
  leased_until          TIMESTAMP(6) NULL,
  validated_at          TIMESTAMP(6) NULL,
  http_status           SMALLINT UNSIGNED,
  final_url_id          BIGINT UNSIGNED,
  robots_indexable      BOOLEAN,
  canonical_url_id      BIGINT UNSIGNED,
  content_snapshot_id   BIGINT UNSIGNED,
  response_headers      JSON NOT NULL DEFAULT (JSON_OBJECT()),
  response_time_ms      INT UNSIGNED,
  failure_code          VARCHAR(64),
  created_at            TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  CONSTRAINT target_validation_url_fk FOREIGN KEY (target_url_id) REFERENCES urls(id),
  CONSTRAINT target_validation_final_url_fk FOREIGN KEY (final_url_id) REFERENCES urls(id),
  CONSTRAINT target_validation_canonical_url_fk FOREIGN KEY (canonical_url_id) REFERENCES urls(id),
  CONSTRAINT target_validation_content_fk FOREIGN KEY (content_snapshot_id)
    REFERENCES page_content_snapshots(id),
  UNIQUE KEY target_validation_coalescing_uniq (coalescing_key),
  KEY target_validation_claim_idx (status, available_at, priority, id),
  KEY target_validation_expired_lease_idx (status, leased_until, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Table: crawl_page_fetch_consumers
CREATE TABLE crawl_page_fetch_consumers (
  crawl_page_id         BIGINT UNSIGNED PRIMARY KEY,
  page_fetch_job_id     BIGINT UNSIGNED NOT NULL,
  required_validated_after TIMESTAMP(6) NOT NULL,
  validation_deadline   TIMESTAMP(6) NOT NULL,
  created_at            TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  CONSTRAINT crawl_page_fetch_consumer_page_fk FOREIGN KEY (crawl_page_id)
    REFERENCES crawl_pages(id) ON DELETE CASCADE,
  CONSTRAINT crawl_page_fetch_consumer_job_fk FOREIGN KEY (page_fetch_job_id)
    REFERENCES page_fetch_jobs(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Table: url_revalidation_state
CREATE TABLE url_revalidation_state (
  source_url_id         BIGINT UNSIGNED NOT NULL,
  fetch_profile_hash    BINARY(32) NOT NULL,
  latest_fetch_job_id   BIGINT UNSIGNED,
  last_validated_at     TIMESTAMP(6) NULL,
  next_global_due_at    TIMESTAMP(6) NULL,
  consecutive_failures  SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  updated_at            TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
                          ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (source_url_id, fetch_profile_hash),
  CONSTRAINT url_revalidation_state_url_fk FOREIGN KEY (source_url_id) REFERENCES urls(id),
  CONSTRAINT url_revalidation_state_fetch_fk FOREIGN KEY (latest_fetch_job_id)
    REFERENCES page_fetch_jobs(id) ON DELETE SET NULL,
  KEY url_revalidation_due_idx (next_global_due_at, source_url_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Table: page_parse_results
CREATE TABLE page_parse_results (
  id                    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  content_snapshot_id   BIGINT UNSIGNED NOT NULL,
  base_url_id           BIGINT UNSIGNED NOT NULL,
  parser_version        VARCHAR(64) NOT NULL,
  manifest_schema_version SMALLINT UNSIGNED NOT NULL,
  status                ENUM('queued', 'leased', 'completed', 'failed') NOT NULL DEFAULT 'queued',
  priority              SMALLINT NOT NULL DEFAULT 0,
  available_at          TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  attempts              SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  lease_owner           VARCHAR(128),
  leased_until          TIMESTAMP(6) NULL,
  link_manifest_key     VARCHAR(1024),
  link_manifest_hash    BINARY(32),
  links_found           INT UNSIGNED,
  parse_complete        BOOLEAN NOT NULL DEFAULT FALSE,
  is_truncated          BOOLEAN NOT NULL DEFAULT FALSE,
  failure_code          VARCHAR(64),
  completed_at          TIMESTAMP(6) NULL,
  created_at            TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  CONSTRAINT page_parse_results_content_fk FOREIGN KEY (content_snapshot_id)
    REFERENCES page_content_snapshots(id),
  CONSTRAINT page_parse_results_base_url_fk FOREIGN KEY (base_url_id) REFERENCES urls(id),
  UNIQUE KEY page_parse_results_identity_uniq
    (content_snapshot_id, base_url_id, parser_version, manifest_schema_version),
  KEY page_parse_results_claim_idx (status, available_at, priority, id),
  KEY page_parse_results_expired_lease_idx (status, leased_until, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Table: crawl_page_parse_consumers
CREATE TABLE crawl_page_parse_consumers (
  crawl_page_id         BIGINT UNSIGNED PRIMARY KEY,
  page_parse_result_id  BIGINT UNSIGNED NOT NULL,
  created_at            TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  CONSTRAINT crawl_page_parse_consumer_page_fk FOREIGN KEY (crawl_page_id)
    REFERENCES crawl_pages(id) ON DELETE CASCADE,
  CONSTRAINT crawl_page_parse_consumer_result_fk FOREIGN KEY (page_parse_result_id)
    REFERENCES page_parse_results(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Domain 3: Materialized Backlink State, Occurrences & Observations

Holds the latest current edge state for fast UI queries, multi-target attribution matches, DOM occurrences, and an immutable observation evidence ledger.

Table: backlinks
CREATE TABLE backlinks (
  id                    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  project_id            BINARY(16) NOT NULL,
  source_url_id         BIGINT UNSIGNED NOT NULL,
  target_url_id         BIGINT UNSIGNED NOT NULL,
  first_discovery_source ENUM('crawler', 'provider_import', 'search_console', 'manual', 'api') NOT NULL,
  current_state         ENUM('unknown', 'live', 'lost') NOT NULL DEFAULT 'unknown',
  source_fetch_health   ENUM('unknown', 'ok', 'blocked', 'error') NOT NULL DEFAULT 'unknown',
  target_fetch_health   ENUM('unknown', 'ok', 'blocked', 'error') NOT NULL DEFAULT 'unknown',
  is_redirected         BOOLEAN NOT NULL DEFAULT FALSE,
  state_version         BIGINT UNSIGNED NOT NULL DEFAULT 0,
  first_seen_at         TIMESTAMP(6) NOT NULL,
  last_checked_at       TIMESTAMP(6) NOT NULL,
  next_check_at         TIMESTAMP(6) NOT NULL,
  check_priority        SMALLINT NOT NULL DEFAULT 0,
  last_seen_live_at     TIMESTAMP(6) NULL,
  lost_at               TIMESTAMP(6) NULL,
  consecutive_failures  SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  source_http_status    SMALLINT UNSIGNED,
  target_http_status    SMALLINT UNSIGNED,
  final_target_url_id   BIGINT UNSIGNED,
  target_robots_indexable BOOLEAN,
  anchor_text           TEXT,
  link_count            SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  rel_nofollow          BOOLEAN NOT NULL DEFAULT FALSE,
  rel_sponsored         BOOLEAN NOT NULL DEFAULT FALSE,
  rel_ugc               BOOLEAN NOT NULL DEFAULT FALSE,
  is_image_link         BOOLEAN NOT NULL DEFAULT FALSE,
  is_sitewide           BOOLEAN NOT NULL DEFAULT FALSE,
  placement             ENUM('content', 'header', 'footer', 'sidebar', 'unknown'),
  language_code         VARCHAR(16),
  updated_at            TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
                          ON UPDATE CURRENT_TIMESTAMP(6),
  CONSTRAINT backlinks_project_fk FOREIGN KEY (project_id)
    REFERENCES projects(id) ON DELETE CASCADE,
  CONSTRAINT backlinks_source_url_fk FOREIGN KEY (source_url_id) REFERENCES urls(id),
  CONSTRAINT backlinks_target_url_fk FOREIGN KEY (target_url_id) REFERENCES urls(id),
  CONSTRAINT backlinks_final_target_url_fk FOREIGN KEY (final_target_url_id) REFERENCES urls(id),
  UNIQUE KEY backlinks_project_edge_uniq (project_id, source_url_id, target_url_id),
  UNIQUE KEY backlinks_project_id_uniq (project_id, id),
  KEY backlinks_due_idx (project_id, next_check_at, check_priority, source_url_id),
  KEY backlinks_state_idx (project_id, current_state, last_checked_at, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Table: backlink_target_matches
CREATE TABLE backlink_target_matches (
  project_id            BINARY(16) NOT NULL,
  backlink_id           BIGINT UNSIGNED NOT NULL,
  project_target_id     BINARY(16) NOT NULL,
  first_matched_at      TIMESTAMP(6) NOT NULL,
  last_matched_at       TIMESTAMP(6) NOT NULL,
  is_active             BOOLEAN NOT NULL DEFAULT TRUE,
  ended_at              TIMESTAMP(6) NULL,
  PRIMARY KEY (backlink_id, project_target_id),
  CONSTRAINT backlink_target_matches_backlink_fk
    FOREIGN KEY (project_id, backlink_id)
    REFERENCES backlinks(project_id, id) ON DELETE CASCADE,
  CONSTRAINT backlink_target_matches_target_fk
    FOREIGN KEY (project_id, project_target_id)
    REFERENCES project_targets(project_id, id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Table: backlink_occurrences
CREATE TABLE backlink_occurrences (
  id                    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  backlink_id           BIGINT UNSIGNED NOT NULL,
  occurrence_key        BINARY(32) NOT NULL,
  locator_hash          BINARY(32),
  anchor_text           TEXT,
  rel_nofollow          BOOLEAN NOT NULL DEFAULT FALSE,
  rel_sponsored         BOOLEAN NOT NULL DEFAULT FALSE,
  rel_ugc               BOOLEAN NOT NULL DEFAULT FALSE,
  is_image_link         BOOLEAN NOT NULL DEFAULT FALSE,
  placement             ENUM('content', 'header', 'footer', 'sidebar', 'unknown'),
  first_seen_at         TIMESTAMP(6) NOT NULL,
  last_seen_at          TIMESTAMP(6) NOT NULL,
  is_live               BOOLEAN NOT NULL DEFAULT TRUE,
  CONSTRAINT backlink_occurrences_backlink_fk FOREIGN KEY (backlink_id)
    REFERENCES backlinks(id) ON DELETE CASCADE,
  UNIQUE KEY backlink_occurrences_identity_uniq (backlink_id, occurrence_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Table: backlink_observations
CREATE TABLE backlink_observations (
  id                    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  backlink_id           BIGINT UNSIGNED NOT NULL,
  crawl_run_id          BINARY(16),
  page_fetch_job_id     BIGINT UNSIGNED,
  target_validation_job_id BIGINT UNSIGNED,
  page_parse_result_id  BIGINT UNSIGNED,
  idempotency_key       BINARY(32) NOT NULL,
  observed_at           TIMESTAMP(6) NOT NULL,
  evidence_complete     BOOLEAN NOT NULL DEFAULT FALSE,
  evidence_reason       VARCHAR(64),
  state                 ENUM('unknown', 'live', 'lost') NOT NULL,
  transition_event      ENUM('none', 'new', 'lost', 'recovered') NOT NULL DEFAULT 'none',
  source_fetch_health   ENUM('unknown', 'ok', 'blocked', 'error') NOT NULL DEFAULT 'unknown',
  target_fetch_health   ENUM('unknown', 'ok', 'blocked', 'error') NOT NULL DEFAULT 'unknown',
  is_redirected         BOOLEAN NOT NULL DEFAULT FALSE,
  state_version         BIGINT UNSIGNED NOT NULL,
  source_http_status    SMALLINT UNSIGNED,
  target_http_status    SMALLINT UNSIGNED,
  final_target_url_id   BIGINT UNSIGNED,
  anchor_text           TEXT,
  link_count            SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  rel_nofollow          BOOLEAN NOT NULL DEFAULT FALSE,
  rel_sponsored         BOOLEAN NOT NULL DEFAULT FALSE,
  rel_ugc               BOOLEAN NOT NULL DEFAULT FALSE,
  is_image_link         BOOLEAN NOT NULL DEFAULT FALSE,
  placement             VARCHAR(32),
  page_title            TEXT,
  html_lang             VARCHAR(16),
  canonical_url_id      BIGINT UNSIGNED,
  source_robots_indexable BOOLEAN,
  target_robots_indexable BOOLEAN,
  response_time_ms      INT UNSIGNED,
  failure_code          VARCHAR(64),
  metadata              JSON NOT NULL DEFAULT (JSON_OBJECT()),
  CONSTRAINT observations_backlink_fk FOREIGN KEY (backlink_id)
    REFERENCES backlinks(id) ON DELETE CASCADE,
  CONSTRAINT observations_run_fk FOREIGN KEY (crawl_run_id)
    REFERENCES crawl_runs(id) ON DELETE SET NULL,
  CONSTRAINT observations_fetch_job_fk FOREIGN KEY (page_fetch_job_id)
    REFERENCES page_fetch_jobs(id),
  CONSTRAINT observations_target_validation_job_fk FOREIGN KEY (target_validation_job_id)
    REFERENCES target_validation_jobs(id),
  CONSTRAINT observations_parse_result_fk FOREIGN KEY (page_parse_result_id)
    REFERENCES page_parse_results(id),
  CONSTRAINT observations_lost_requires_complete_chk
    CHECK (state <> 'lost' OR evidence_complete = TRUE),
  UNIQUE KEY observations_idempotency_uniq (idempotency_key),
  KEY observations_backlink_time_idx (backlink_id, observed_at, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Domain 4: Metrics, External Provider Candidate Imports & Multi-Channel Alerts

Handles third-party API credentials, domain/page authority score history, bulk provider candidate discovery, and alert event delivery queues.

Table: domain_metric_snapshots
CREATE TABLE domain_metric_snapshots (
  domain_id             BIGINT UNSIGNED NOT NULL,
  measured_at           TIMESTAMP(6) NOT NULL,
  provider              VARCHAR(64) NOT NULL,
  authority_score       DECIMAL(6,2),
  spam_score            DECIMAL(6,2),
  referring_domains     BIGINT UNSIGNED,
  backlinks             BIGINT UNSIGNED,
  estimated_traffic     BIGINT UNSIGNED,
  organic_keywords      BIGINT UNSIGNED,
  metadata              JSON NOT NULL DEFAULT (JSON_OBJECT()),
  PRIMARY KEY (domain_id, provider, measured_at),
  CONSTRAINT domain_metrics_domain_fk FOREIGN KEY (domain_id) REFERENCES domains(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Table: page_metric_snapshots
CREATE TABLE page_metric_snapshots (
  url_id                BIGINT UNSIGNED NOT NULL,
  measured_at           TIMESTAMP(6) NOT NULL,
  provider              VARCHAR(64) NOT NULL,
  authority_score       DECIMAL(6,2),
  estimated_traffic     BIGINT UNSIGNED,
  outgoing_links        INT UNSIGNED,
  metadata              JSON NOT NULL DEFAULT (JSON_OBJECT()),
  PRIMARY KEY (url_id, provider, measured_at),
  CONSTRAINT page_metrics_url_fk FOREIGN KEY (url_id) REFERENCES urls(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Table: provider_connections
CREATE TABLE provider_connections (
  id                    BINARY(16) PRIMARY KEY,
  tenant_id             BINARY(16) NOT NULL,
  provider              VARCHAR(64) NOT NULL,
  secret_reference      VARCHAR(1024) NOT NULL, -- Secret Manager key reference
  status                ENUM('active', 'disabled', 'error') NOT NULL DEFAULT 'active',
  created_at            TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  UNIQUE KEY provider_connections_tenant_uniq (tenant_id, provider),
  UNIQUE KEY provider_connections_tenant_id_uniq (tenant_id, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Table: import_runs
CREATE TABLE import_runs (
  id                     BINARY(16) PRIMARY KEY,
  tenant_id              BINARY(16) NOT NULL,
  project_id             BINARY(16) NOT NULL,
  provider_connection_id BINARY(16),
  provider               VARCHAR(64) NOT NULL,
  status                 ENUM('queued', 'running', 'completed', 'partial', 'failed', 'cancelled')
                           NOT NULL DEFAULT 'queued',
  cursor                 TEXT,
  rows_received          INT UNSIGNED NOT NULL DEFAULT 0,
  rows_accepted          INT UNSIGNED NOT NULL DEFAULT 0,
  created_at             TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  CONSTRAINT import_runs_project_fk FOREIGN KEY (tenant_id, project_id)
    REFERENCES projects(tenant_id, id) ON DELETE CASCADE,
  CONSTRAINT import_runs_connection_fk FOREIGN KEY (tenant_id, provider_connection_id)
    REFERENCES provider_connections(tenant_id, id) ON DELETE RESTRICT,
  UNIQUE KEY import_runs_tenant_id_uniq (tenant_id, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Table: backlink_discoveries
CREATE TABLE backlink_discoveries (
  id                     BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id              BINARY(16) NOT NULL,
  project_id             BINARY(16) NOT NULL,
  backlink_id            BIGINT UNSIGNED NOT NULL,
  discovery_key          BINARY(32) NOT NULL,
  source                 ENUM('crawler', 'provider_import', 'search_console', 'manual', 'api') NOT NULL,
  provider               VARCHAR(64),
  first_discovered_at    TIMESTAMP(6) NOT NULL,
  last_discovered_at     TIMESTAMP(6) NOT NULL,
  CONSTRAINT backlink_discoveries_project_fk FOREIGN KEY (tenant_id, project_id)
    REFERENCES projects(tenant_id, id) ON DELETE CASCADE,
  CONSTRAINT backlink_discoveries_backlink_fk FOREIGN KEY (project_id, backlink_id)
    REFERENCES backlinks(project_id, id) ON DELETE CASCADE,
  UNIQUE KEY backlink_discoveries_key_uniq (discovery_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Table: alert_rules
CREATE TABLE alert_rules (
  id                    BINARY(16) PRIMARY KEY,
  project_id            BINARY(16) NOT NULL,
  name                  VARCHAR(255) NOT NULL,
  event_types           JSON NOT NULL,
  filters               JSON NOT NULL DEFAULT (JSON_OBJECT()),
  channels              JSON NOT NULL DEFAULT (JSON_ARRAY()),
  cooldown_seconds      INT UNSIGNED NOT NULL DEFAULT 3600,
  is_active             BOOLEAN NOT NULL DEFAULT TRUE,
  created_at            TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  CONSTRAINT alert_rules_project_fk FOREIGN KEY (project_id)
    REFERENCES projects(id) ON DELETE CASCADE,
  UNIQUE KEY alert_rules_project_id_uniq (project_id, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Table: alert_events
CREATE TABLE alert_events (
  id                    BINARY(16) PRIMARY KEY,
  project_id            BINARY(16) NOT NULL,
  alert_rule_id         BINARY(16),
  backlink_id           BIGINT UNSIGNED,
  event_type            VARCHAR(64) NOT NULL,
  deduplication_key     BINARY(32) NOT NULL,
  payload               JSON NOT NULL,
  occurred_at           TIMESTAMP(6) NOT NULL,
  aggregate_delivery_status ENUM('pending', 'partial', 'delivered', 'failed', 'cancelled')
                          NOT NULL DEFAULT 'pending',
  created_at            TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  CONSTRAINT alert_events_project_fk FOREIGN KEY (project_id)
    REFERENCES projects(id) ON DELETE CASCADE,
  CONSTRAINT alert_events_rule_fk FOREIGN KEY (project_id, alert_rule_id)
    REFERENCES alert_rules(project_id, id) ON DELETE RESTRICT,
  CONSTRAINT alert_events_backlink_fk FOREIGN KEY (project_id, backlink_id)
    REFERENCES backlinks(project_id, id) ON DELETE CASCADE,
  UNIQUE KEY alert_events_dedupe_uniq (deduplication_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Table: alert_deliveries
CREATE TABLE alert_deliveries (
  id                    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  alert_event_id        BINARY(16) NOT NULL,
  channel               ENUM('email', 'slack', 'webhook') NOT NULL,
  destination_hash      BINARY(32) NOT NULL,
  status                ENUM('pending', 'leased', 'delivered', 'failed', 'cancelled')
                          NOT NULL DEFAULT 'pending',
  attempts              SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  available_at          TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  lease_owner           VARCHAR(128),
  leased_until          TIMESTAMP(6) NULL,
  delivered_at          TIMESTAMP(6) NULL,
  last_error_code       VARCHAR(64),
  created_at            TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  CONSTRAINT alert_deliveries_event_fk FOREIGN KEY (alert_event_id)
    REFERENCES alert_events(id) ON DELETE CASCADE,
  UNIQUE KEY alert_delivery_destination_uniq
    (alert_event_id, channel, destination_hash),
  KEY alert_deliveries_claim_idx (status, available_at, id),
  KEY alert_deliveries_expired_lease_idx (status, leased_until, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Domain 5: Cron Job Locks, Transactional Outbox & Dashboard Rollups

Guarantees single-execution locks for distributed cron instances, transactional outbox bridging to Kafka/SQS, and incremental dashboard summaries for <50ms response times.

Table: scheduled_job_runs
CREATE TABLE scheduled_job_runs (
  id                    BINARY(16) PRIMARY KEY,
  job_name              VARCHAR(64) NOT NULL,
  scheduled_for         TIMESTAMP(6) NOT NULL,
  status                ENUM('running', 'completed', 'failed') NOT NULL DEFAULT 'running',
  lease_owner           VARCHAR(128),
  leased_until          TIMESTAMP(6) NULL,
  started_at            TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  completed_at          TIMESTAMP(6) NULL,
  stats                 JSON NOT NULL DEFAULT (JSON_OBJECT()),
  error_code            VARCHAR(64),
  UNIQUE KEY scheduled_job_slot_uniq (job_name, scheduled_for),
  KEY scheduled_job_lease_idx (status, leased_until, job_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Table: transactional_outbox
CREATE TABLE transactional_outbox (
  id                    BINARY(16) PRIMARY KEY,
  event_key             BINARY(32) NOT NULL,
  topic                 VARCHAR(128) NOT NULL,
  partition_key         VARBINARY(128) NOT NULL,
  payload               JSON NOT NULL,
  status                ENUM('pending', 'leased', 'published', 'failed')
                          NOT NULL DEFAULT 'pending',
  attempts              SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  available_at          TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  lease_owner           VARCHAR(128),
  leased_until          TIMESTAMP(6) NULL,
  published_at          TIMESTAMP(6) NULL,
  last_error_code       VARCHAR(64),
  created_at            TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  UNIQUE KEY transactional_outbox_event_uniq (event_key),
  KEY transactional_outbox_claim_idx (status, available_at, id),
  KEY transactional_outbox_expired_lease_idx (status, leased_until, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Table: project_backlink_daily_stats
CREATE TABLE project_backlink_daily_stats (
  project_id            BINARY(16) NOT NULL,
  stat_date             DATE NOT NULL,
  observations_count    BIGINT UNSIGNED NOT NULL DEFAULT 0,
  new_count             BIGINT UNSIGNED NOT NULL DEFAULT 0,
  lost_count            BIGINT UNSIGNED NOT NULL DEFAULT 0,
  recovered_count       BIGINT UNSIGNED NOT NULL DEFAULT 0,
  updated_at            TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
                          ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (project_id, stat_date),
  CONSTRAINT project_daily_stats_project_fk FOREIGN KEY (project_id)
    REFERENCES projects(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Table: project_referring_domain_stats
CREATE TABLE project_referring_domain_stats (
  project_id            BINARY(16) NOT NULL,
  source_domain_id      BIGINT UNSIGNED NOT NULL,
  live_backlinks        BIGINT UNSIGNED NOT NULL DEFAULT 0,
  lost_backlinks        BIGINT UNSIGNED NOT NULL DEFAULT 0,
  first_seen_at         TIMESTAMP(6) NULL,
  last_seen_at          TIMESTAMP(6) NULL,
  updated_at            TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
                          ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (project_id, source_domain_id),
  CONSTRAINT project_domain_stats_project_fk FOREIGN KEY (project_id)
    REFERENCES projects(id) ON DELETE CASCADE,
  CONSTRAINT project_domain_stats_domain_fk FOREIGN KEY (source_domain_id)
    REFERENCES domains(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Table: project_target_current_stats
CREATE TABLE project_target_current_stats (
  project_id            BINARY(16) NOT NULL,
  project_target_id     BINARY(16) NOT NULL,
  live_backlinks        BIGINT UNSIGNED NOT NULL DEFAULT 0,
  lost_backlinks        BIGINT UNSIGNED NOT NULL DEFAULT 0,
  referring_domains     BIGINT UNSIGNED NOT NULL DEFAULT 0,
  updated_at            TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
                          ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (project_id, project_target_id),
  CONSTRAINT project_target_stats_target_fk FOREIGN KEY (project_id, project_target_id)
    REFERENCES project_targets(project_id, id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Domain 6: ClickHouse Analytics Fact Schema & Partitioning

Committed observation changes stream through a CDC enrichment processor before ClickHouse ingestion. The processor resolves project_id and URL hashes, converts nullable fields explicitly, and emits a versioned analytics event. It never requires ClickHouse to join against transactional MySQL tables at query time.

ClickHouse Table: backlink_observations_fact
CREATE TABLE backlink_observations_fact (
    observation_id       UInt64,
    project_id           UUID,
    backlink_id          UInt64,
    source_url_hash      FixedString(32),
    target_url_hash      FixedString(32),
    state                Enum8('unknown'=0, 'live'=1, 'lost'=2),
    transition_event     Enum8('none'=0, 'new'=1, 'lost'=2, 'recovered'=3),
    source_fetch_health  Enum8('unknown'=0, 'ok'=1, 'blocked'=2, 'error'=3),
    target_fetch_health  Enum8('unknown'=0, 'ok'=1, 'blocked'=2, 'error'=3),
    is_redirected        UInt8,
    source_robots_indexable Nullable(UInt8),
    target_robots_indexable Nullable(UInt8),
    source_http_status   Nullable(UInt16),
    target_http_status   Nullable(UInt16),
    rel_nofollow         UInt8,
    rel_sponsored        UInt8,
    rel_ugc              UInt8,
    anchor_text          Nullable(String),
    response_time_ms     Nullable(UInt32),
    observed_at          DateTime64(6, 'UTC'),
    ingest_version       UInt64
) ENGINE = ReplacingMergeTree(ingest_version)
PARTITION BY toYYYYMM(observed_at)
ORDER BY (project_id, observation_id)
SETTINGS index_granularity = 8192;

The CDC connector uses observation_id as the idempotency identity and assigns a monotonic ingest_version. Historical queries avoid FINAL on large ranges by using deduplicated views or periodic compaction where immediate deduplication is required.

5. Cron, Scheduler & Worker Flow

Cron is strictly a lightweight trigger mechanism—it initiates job reservations and exits immediately. All scheduler instances route writes to the authoritative database primary; row locking and renewable leases prevent duplicate ownership across regions.

Job Reservation with SKIP LOCKED

To safely claim due project jobs across multiple concurrent scheduler instances, workers execute atomic batch claims:

START TRANSACTION;

SELECT id, recrawl_interval_seconds
FROM projects
WHERE is_active = TRUE 
  AND next_crawl_at <= UTC_TIMESTAMP(6)
ORDER BY next_crawl_at ASC
LIMIT 100
FOR UPDATE SKIP LOCKED;

-- For each selected project, insert the deterministic crawl_runs row here.
-- A duplicate idempotency key is treated as an already-created run.

-- Reserve the next schedule before releasing the row lock. GREATEST skips a
-- catch-up storm while preserving the prior cadence when only slightly late.
UPDATE projects 
SET next_crawl_at = GREATEST(
  DATE_ADD(next_crawl_at, INTERVAL recrawl_interval_seconds SECOND),
  DATE_ADD(UTC_TIMESTAMP(6), INTERVAL recrawl_interval_seconds SECOND)
)
WHERE id IN (...claimed_ids...);

COMMIT;

Fetch Coalescing & Outbox Publisher Flow

How Fetch Coalescing Prevents Redundant HTTP Requests:

When 10 projects monitor backlinks located on https://popular-blog.com/top-100-tools, the scheduler generates 10 internal checks. The Fetch Coalescer hashes the public source URL and aggregates all 10 checks under a single page_fetch_job row. A single HTTP fetcher downloads the page once, saves the HTML snapshot, and fans the resulting DOM manifest out to all 10 projects.

10 Project Subscriptions
➔ 1 HTTP Request ➔
1 Shared Manifest

Default Recurring Background Jobs

Cron is only the trigger. Each job below runs globally — one process finds all due rows by indexed timestamps; no per-customer cron instance is needed.

Job Default Cadence Responsibility
Project scheduler Every minute Create verification runs for due projects
Confirmation scheduler Every minute Create priority runs for due missing-link confirmations
Run expander Every minute or event-driven Turn runs into unique source-page jobs
Target health scheduler Every minute Coalesce and queue due monitored target URLs independently from source-link verification
Expired lease recovery Every minute Requeue abandoned crawl and delivery jobs
Run finalizer Every minute Close runs with no outstanding page jobs
Provider discovery sync Hourly, plan-dependent Import candidate backlinks from configured providers
Metric refresh scheduler Hourly Queue only domains/pages whose metrics are due
Alert dispatcher Continuous or every minute Deliver pending alert jobs
Outbox publisher Continuous Publish committed work to the message broker
CDC pipeline Continuous Stream committed history into ClickHouse
Daily digest builder Daily per tenant timezone Aggregate eligible events into one digest
Weekly digest builder Weekly per tenant timezone Build weekly summary
Reporting rollups Every 15 minutes Refresh project and referring-domain summaries
Retention / archive Daily during low traffic Archive/purge old observations in bounded batches
Batch-audit cleanup Daily Remove expired raw bulk-import audit items
Artifact reconciler Hourly Repair or remove orphaned/pending objects and manifests
Job-ledger cleanup Weekly Remove old successful scheduler audit rows
Public Suffix List update Weekly Refresh domain parsing rules after validation

Step-by-Step Verification Cycle

Each verification run follows a deterministic 10-step pipeline. Every step is idempotent and safe to retry after a crash.

Step 1 — Trigger One Scheduler Slot

An external cron starts project_scheduler every minute. It rounds the current UTC time to the minute and inserts one row into scheduled_job_runs with job_name = 'project_scheduler' and scheduled_for = current minute. The winner records a short renewable lease. A competing instance exits only while that lease is valid; after expiry, a recovery instance atomically reclaims the same slot. This avoids a permanently stuck minute when the first process crashes.

Step 2 — Claim Due Projects

In a short transaction, select a small batch of active projects ordered by urgency. No network calls may occur while this transaction is open.

Claim Due Projects — SKIP LOCKED
START TRANSACTION;

SELECT id, next_crawl_at, recrawl_interval_seconds
FROM projects
WHERE is_active = TRUE
  AND next_crawl_at <= UTC_TIMESTAMP(6)
ORDER BY next_crawl_at
LIMIT 100
FOR UPDATE SKIP LOCKED;

-- Keep this transaction open only for local database writes. For every row,
-- insert the deterministic crawl run and advance next_crawl_at. Then COMMIT.
-- Do not perform network or broker calls in this transaction.

COMMIT;
Step 3 — Create an Idempotent Crawl Run

For each locked project, calculate a deterministic run key, insert a crawl_runs row, and advance next_crawl_at before the same transaction commits. The unique key on idempotency_key makes uncertain retries harmless. Normally advance from the previous scheduled value; when it remains overdue, move it to one interval after the current time so recovery creates at most one catch-up run rather than an unbounded storm.

Run Idempotency Key Formula
run_key = SHA-256(project_id + ':verification:' + scheduled_slot)
Step 4 — Expand the Run into Unique Page Checks

The run expander groups backlinks due for verification by source URL. One source page linking to 50 monitored URLs generates only one page check. Each consumer records required_validated_after and validation_deadline. The coalescing key prevents two projects from triggering two HTTP fetches for the same source page.

Run Expansion Query
SELECT b.source_url_id, MAX(b.check_priority) AS priority
FROM backlinks AS b
WHERE b.project_id = ?
  AND b.next_check_at <= UTC_TIMESTAMP(6)
  AND EXISTS (
    SELECT 1
    FROM backlink_target_matches AS m
    JOIN project_targets AS t ON t.id = m.project_target_id
    WHERE m.backlink_id = b.id
      AND m.is_active = TRUE
      AND t.is_active = TRUE
      AND t.deleted_at IS NULL
  )
GROUP BY b.source_url_id
ORDER BY priority DESC;
Fetch Coalescing Key Formula
coalescing_key = SHA-256(source_url_id + ':' + fetch_profile_hash + ':' + validation_window)
Step 5 — Workers Lease Shared Fetch Jobs

Each fetch worker claims a small batch without blocking others. A typical lease is 2–5 minutes. The HTTP fetch occurs after commit. The worker loads the most recent successful validation and sends conditional headers when available.

Worker Lease Claim
START TRANSACTION;

SELECT id
FROM page_fetch_jobs
WHERE status = 'queued'
  AND available_at <= UTC_TIMESTAMP(6)
ORDER BY priority DESC, available_at
LIMIT 25
FOR UPDATE SKIP LOCKED;

-- Update the selected IDs:
-- status = 'leased', lease_owner = worker_id
-- leased_until = UTC_TIMESTAMP(6) + lease_duration
-- attempts = attempts + 1

COMMIT;
HTTP 200 — New Body

Compress HTML, store in object storage, insert page_content_snapshots using SHA-256 body hash. Duplicate hash reuses existing snapshot — no second upload.

HTTP 304 — Not Modified

Create a new validation result referencing the previous snapshot. No HTML is downloaded or stored again. If referenced content is missing, retry once unconditionally.

Error / Non-HTML

Record metadata and failure policy. Never create an HTML snapshot. Retain previous live/lost state — a network error is not evidence of a missing backlink.

Target-Page Health Checks

Source verification and target health are independent signals. A target_validation_job is coalesced by normalized target URL and validation window, uses the same SSRF, redirect, timeout, robots-policy, and global per-host controls as source fetching, and never downloads a body unless canonical or indexability evaluation requires it. Its result populates target_http_status, final_target_url_id, target fetch health, and target indexability. A target outage may create a target-health event, but it cannot create a source-link lost transition.

Step 6 — Apply Safe Fetch Controls (SSRF Prevention)
  1. Permit only http and https schemes.
  2. Resolve DNS and reject loopback, private, link-local, metadata-service, and other forbidden IP ranges.
  3. Revalidate redirect destinations with the same SSRF rules.
  4. Apply robots.txt and product-policy rules.
  5. Acquire a per-host rate-limit token from Redis — global across all workers, not local to one process.
  6. Enforce connect timeout, response timeout, body-size limit, redirect-count limit, and total-time limit.
  7. Use a clear crawler user-agent string with a contact URL.
Browser Rendering Tier: Raw HTTP fetching is the default. Browser rendering is a separate quota-controlled fetch profile used only when project policy requests it or heuristics indicate JavaScript-rendered content is necessary. Browser workers run in a hardened sandbox with no tenant cookies. Downloaded HTML is data — never execute it in the ordinary parser process.
Step 7 — Parse the Stored Snapshot Offline

After a successful fetch, create or reuse page_parse_results identified by (content_snapshot_id, base_url_id, parser_version). The parser downloads the compressed object internally, extracts raw and resolved links, anchors, rel attributes, image-link status, placement, canonical data, and indexability, then writes a compact compressed link manifest back to object storage.

URL resolution base matters: Identical HTML served from two different URLs may contain relative links that resolve to different absolute targets — the base URL is part of the parse identity.

Truncation rule: Hitting body, decompression, parse-time, DOM-node, or extracted-link limits produces is_truncated = TRUE. A truncated result can confirm links that were found, but it can never prove that a previously known link is absent.

Step 8 — Record Observations Atomically

In one database transaction for one project consuming the parsed page, perform all of the following steps atomically:

  1. Upsert any newly encountered domains and URLs by their hashes.
  2. Upsert the logical backlinks edge using (project_id, source_url_id, target_url_id).
  3. Insert one immutable backlink_observations row per edge.
  4. Maintain backlink_target_matches for every active target definition matched by the edge.
  5. Update individual backlink_occurrences; the immutable link manifest remains the detailed historical evidence.
  6. Reference the source fetch job, optional target validation job, and parse result so every observation has reproducible evidence.
  7. Use an observation idempotency key: SHA-256(crawl_run_id + ':' + source_url_id + ':' + target_url_id).
  8. Update the current backlink row and next_check_at.
  9. Insert any alert event, its channel delivery rows, and required broker events in the transactional outbox.
  10. Mark the project-scoped crawl_pages consumer as completed. Then commit.
Idempotent Retries: If the worker retries after an uncertain database response, the observation, backlink edge, and alert unique keys prevent duplicates. The system is deliberately at-least-once; exactly-once network execution is not realistic at scale.
Step 9 — Handle Missing Links vs Fetch Failures Differently

A successful page fetch where the expected link is absent is evidence of potential loss. A timeout, DNS failure, or server error is not the same evidence.

Signal Action Retry Window
Successful page, link missing Increment consecutive_failures, schedule confirmation check 30–120 minutes with jitter
Second confirmed miss after retry window Change state to lost, set lost_at, emit backlink.lost —
Fetch / network failure Retain previous live/lost state, record error observation, retry with backoff 5 min → 24 hours (exponential)
HTTP 404/410 source page Stronger evidence but still use configured confirmation policy Configurable
Link returns after loss Change durable state to live, reset failures, increment the state version, and emit backlink.recovered —
Stable lost link (30+ days) Progressively reduce checking frequency to weekly under product policy Weekly
Critical Rule: Only complete, non-truncated fetch and parse evidence may advance consecutive_failures or create a lost transition. Network and parser failures update operational retry counters instead.
Step 10 — Finalize the Run

Workers increment pages_completed or pages_failed exactly once when a consumer reaches a terminal state. The run finalizer does not repeatedly count large job tables.

Run Completion Condition
IF expansion_completed = TRUE
  AND (pages_completed + pages_failed) = pages_planned:

    status = 'completed'  -- all jobs succeeded
    status = 'partial'    -- some jobs exhausted retries
    Set completed_at
    Update projects.last_crawl_at for verification runs

New-Backlink Discovery Flow

Verifying known source pages cannot discover every new backlink on the web. Adding 300 target URLs does not tell the system which external pages link to them. Discovery needs at least one candidate source:

Provider Import

Third-party SEO data providers. Hourly import scheduler with cursor-safe pagination and idempotent import runs.

Search Console

Google Search Console or another owner-authorized source with verified property ownership.

Proprietary Crawl Index

A shared crawl/index built by the product. Source pages are anonymous public responses shared safely across tenants.

Manual / API Upload

CSV or API upload of known referring pages. Validated, deduplicated, and queued for immediate verification.

The hourly provider scheduler creates an idempotent import_runs row. Import workers page through provider results using the stored cursor, normalize and deduplicate source and target URLs, upsert backlink candidates, record each provider/source in backlink_discoveries, and queue immediate verification by setting next_check_at to the current time. New projects should run discovery first — a verification run with no known backlink candidates should complete successfully with zero page jobs.

Alert Flow

Immediate Alerts

The observation transaction creates one alert_events row per meaningful state transition and one alert_deliveries row per configured channel/destination. Deduplication keys include the rule, backlink, event type, and state-transition version.

Provider Response Action
Permanent config / auth error Mark failed, notify administrator through an independent channel
Rate limit or temporary provider error Reschedule with exponential backoff and jitter
Timeout after uncertain webhook response Retry with a stable outbound idempotency header

The alert_events.aggregate_delivery_status is derived from child delivery rows: pending while none have finished; partial when outcomes are mixed; delivered when all required deliveries succeed; failed when all terminal deliveries fail; cancelled only when intentionally suppressed.

Digests

Daily/weekly digest builders run globally and calculate each tenant's due window in its configured timezone. A unique digest key prevents duplicate summaries during daylight-saving transitions or retries. All event times are stored in UTC.

Outbox Ownership

Each database shard owns a local transactional_outbox. The same transaction that changes backlink state inserts its versioned broker event; a publisher later sends it and marks it published. Alert delivery rows remain the durable delivery queue and are not recreated from Kafka. This gives one unambiguous path: the outbox bridges committed state to Kafka, while alert_deliveries controls email, Slack, and webhook retries.

Lease Recovery

Every minute, a recovery job finds fetch work whose lease has expired and requeues it. The same pattern applies to target_validation_jobs, page_parse_results, project match work in crawl_pages, alert_deliveries, and the transactional outbox. Always process limited ID batches — never update an unbounded number of rows at once.

Expired Lease Recovery SQL
-- Requeue expired leases under retry limit:
UPDATE page_fetch_jobs
SET status       = 'queued',
    lease_owner  = NULL,
    leased_until = NULL,
    available_at = UTC_TIMESTAMP(6) + INTERVAL 1 MINUTE
WHERE status      = 'leased'
  AND leased_until < UTC_TIMESTAMP(6)
  AND attempts    < 5
LIMIT 500;

-- Mark permanently failed once max attempts reached:
UPDATE page_fetch_jobs
SET status = 'failed'
WHERE status      = 'leased'
  AND leased_until < UTC_TIMESTAMP(6)
  AND attempts   >= 5
LIMIT 500;

Retention & Maintenance Policy

Object storage and MySQL cannot commit atomically. Use this artifact lifecycle for every compressed HTML body and link manifest:

  1. Create or claim a pending content record with a deterministic content-addressed key. Reuse is allowed only for anonymous public responses with compatible fetch profiles and encryption policy.
  2. Upload to a temporary or final key with server-side encryption.
  3. Read metadata and checksum back from storage.
  4. Mark the snapshot available only after verification.
  5. Allow parsers to consume only available objects.
  6. Have the artifact reconciler repair or delete stale pending rows and orphaned objects.
  7. On every reuse, atomically update last_referenced_at and extend retention_until with GREATEST.
  8. Delete an object only after retention_until has passed and no retained fetch, parse, observation, legal-hold, or export reference remains.
Data Class Retention Policy
Current backlinks rows Keep while project exists
Hot observations (MySQL) 7–30 days, then archive to ClickHouse before deletion
ClickHouse observation history Tenant-contract retention; deletion tombstones and completion reconciliation are required
Raw HTML bodies (object storage) 7–30 days; compressed with Zstandard
Parsed link manifests Longer than HTML when useful for parser re-runs
Failed job runs Keep longer than successful audit rows
Scheduler audit rows Purge after weekly cleanup job

Concurrency & Idempotency Guarantees

The system is deliberately at-least-once. Every risk below has a corresponding database-level protection that makes retries safe without creating duplicates.

Risk Protection Mechanism
Two cron instances run simultaneously Unique (job_name, scheduled_for) on scheduled_job_runs
Scheduler retries a project slot Unique crawl-run idempotency_key
Run expansion retries Unique (crawl_run_id, source_url_id) on crawl_pages
Multiple projects request the same URL Unique fetch coalescing_key on page_fetch_jobs
Unchanged page is revalidated Conditional GET reuses existing content snapshot (HTTP 304)
Parser or deployment retries Unique (content_snapshot_id, base_url_id, parser_version) on parse results
Two workers claim work simultaneously FOR UPDATE SKIP LOCKED plus a time-bounded lease
Worker commits but misses the response Observation idempotency key: SHA-256 of run+source+target
Same backlink seen repeatedly Unique (project_id, source_url_id, target_url_id) edge
Same state event evaluated twice Unique alert-event deduplication key per rule+backlink+transition
Alert delivery retries Unique event/channel/destination plus outbound idempotency header

Operational Limits & Tenant Fairness

Start with these limits and tune from measurements. Large projects must not monopolize the queue — weighted fair scheduling selects runnable consumers across tenants first, then by priority and age within each tenant.

Operation Starting Limit
Project scheduler claim per transaction 100 projects
Run expansion per transaction 1,000–5,000 source pages
Fetch-worker claim per transaction 10–25 URLs
Parser-worker claim per transaction 10–50 snapshots (based on body size)
Per-host concurrency 1–2 requests by default
Maximum links extracted per page Configurable; mark truncated if exceeded
Daily page checks per tenant Reserved atomically in tenant_crawl_usage_daily
Browser-rendering time budget Separate quota-controlled tier

Monitoring, Backup & Recovery

Page the operations team when queue age or expired leases exceed a threshold — not merely when a single fetch fails.

  • Scheduler delay: current time minus oldest due project
  • Queued, leased, expired, completed, and failed page jobs
  • Crawl duration percentiles by host and response class
  • Runs stuck without a recent heartbeat
  • Retry and permanent-failure rates
  • Observations and state transitions per minute
  • Alert delivery latency and failure rate
  • MySQL lock-wait time, deadlocks, replica lag, and table growth
  • ClickHouse CDC watermark vs MySQL deletion boundary
  • Object storage upload error rate and orphaned artifact backlog
Backup Requirements: Maintain automated encrypted MySQL backups with point-in-time binary-log recovery and object-storage versioning. Run scheduled restore drills that verify both database rows and referenced artifacts. Schema migrations must be backward-compatible with running workers, use online DDL where possible, and have an explicit roll-forward or rollback procedure.

6. Multi-Tenant Identity & Access Boundary (Developer PRD Spec)

To keep the backlink analytics system clean and prevent coupling with authentication credentials, user profiles, tenant organizations, RBAC memberships, and invitations reside in an isolated Identity & Access Database. The backlink monitoring database stores only an opaque tenant_id (corresponding to organizations.id).

Identity Table: users
CREATE TABLE users (
  id                    BINARY(16) PRIMARY KEY,
  auth_subject          VARCHAR(255) NOT NULL,
  email                 VARCHAR(320) COLLATE utf8mb4_0900_ai_ci NOT NULL,
  display_name          VARCHAR(255),
  status                ENUM('active', 'suspended', 'deleted') NOT NULL DEFAULT 'active',
  created_at            TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at            TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
                          ON UPDATE CURRENT_TIMESTAMP(6),
  UNIQUE KEY users_auth_subject_uniq (auth_subject),
  UNIQUE KEY users_email_uniq (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Identity Table: organizations
CREATE TABLE organizations (
  id                    BINARY(16) PRIMARY KEY,
  name                  VARCHAR(255) NOT NULL,
  slug                  VARCHAR(191) COLLATE utf8mb4_0900_ai_ci NOT NULL,
  plan_code             VARCHAR(64) NOT NULL DEFAULT 'free',
  status                ENUM('active', 'suspended', 'closed') NOT NULL DEFAULT 'active',
  created_at            TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at            TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
                          ON UPDATE CURRENT_TIMESTAMP(6),
  UNIQUE KEY organizations_slug_uniq (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Identity Table: organization_members
CREATE TABLE organization_members (
  organization_id       BINARY(16) NOT NULL,
  user_id               BINARY(16) NOT NULL,
  role                  ENUM('owner', 'admin', 'analyst', 'viewer') NOT NULL,
  created_at            TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at            TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
                          ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (organization_id, user_id),
  CONSTRAINT organization_members_org_fk FOREIGN KEY (organization_id)
    REFERENCES organizations(id) ON DELETE CASCADE,
  CONSTRAINT organization_members_user_fk FOREIGN KEY (user_id)
    REFERENCES users(id) ON DELETE CASCADE,
  KEY organization_members_user_idx (user_id, organization_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Identity Table: organization_invitations
CREATE TABLE organization_invitations (
  id                    BINARY(16) PRIMARY KEY,
  organization_id       BINARY(16) NOT NULL,
  email                 VARCHAR(320) COLLATE utf8mb4_0900_ai_ci NOT NULL,
  role                  ENUM('owner', 'admin', 'analyst', 'viewer') NOT NULL,
  token_hash            BINARY(32) NOT NULL,
  status                ENUM('pending', 'accepted', 'revoked', 'expired')
                          NOT NULL DEFAULT 'pending',
  invited_by_user_id    BINARY(16) NOT NULL,
  accepted_by_user_id   BINARY(16),
  expires_at            TIMESTAMP(6) NOT NULL,
  accepted_at           TIMESTAMP(6) NULL,
  created_at            TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  pending_email         VARCHAR(320) COLLATE utf8mb4_0900_ai_ci GENERATED ALWAYS AS
                          (CASE WHEN status = 'pending' THEN email ELSE NULL END) STORED,
  CONSTRAINT organization_invitations_org_fk FOREIGN KEY (organization_id)
    REFERENCES organizations(id) ON DELETE CASCADE,
  CONSTRAINT organization_invitations_inviter_fk FOREIGN KEY (invited_by_user_id)
    REFERENCES users(id),
  CONSTRAINT organization_invitations_acceptor_fk FOREIGN KEY (accepted_by_user_id)
    REFERENCES users(id),
  UNIQUE KEY organization_invitations_token_uniq (token_hash),
  UNIQUE KEY one_pending_invitation_per_org_email (organization_id, pending_email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

7. Scaling to 100 Million Edges

At 100 million backlink edges (~14.3M checks/day), a single monolithic MySQL instance becomes a bottleneck for writes and index updates. The system activates a distributed sharded architecture using Vitess and Kafka.

Vitess Keyspace Sharding Strategy

Keyspace Domain Sharding Vindex / Routing Key Stored Entities & Responsibility
Control Plane Keyspace Unsharded / Global Replicated Tenant plans, crawl policies, project metadata, shard maps.
Source-Fetch Keyspace source_url_key (Hash Vindex) Global source URL revalidation queue, HTTP cache headers, public content manifests. Guarantees 1 validation per source URL globally.
Project-State Keyspace (tenant_id, project_id) (Lookup Vindex) Current backlink state, occurrences, target matches, alert outbox. All dashboard queries hit exactly one shard.

Physical Inversion at 100M Scale

Rather than having projects pull source pages, scheduling is physically inverted: Source-shard schedulers iterate over due source URLs, perform validation, parse outgoing links, and publish committed edge updates to Kafka topics partitioned by backlink_key. Project-state shards consume these event topics asynchronously, applying local updates idempotently.

Production Topology at 100M Scale

At 100M edges, the architecture physically inverts: source-shard schedulers own the fetch schedule and fan results out through Kafka to project-state shards. One source URL validation serves all subscribing projects simultaneously.

Distributed 100M Pipeline — Kafka-Backed Topology
 [ Control-plane MySQL ] ──► [ Target & Policy Events ]
 [ Discovery Imports   ] ──► [ Kafka / Event Backbone ]
                                          │
                          ┌───────────────┴───────────────┐
                          ▼                               ▼
              [ Source Subscription Index ]   [ Global Source Scheduler ]
                                                          │
                                               [ Fetch Topic (Kafka) ]
                                                          │
                                           [ HTTP / Browser Fetch Workers ]
                                                          │
                            ┌─────────────────────────────┴──────────┐
                            ▼                                        ▼
                  [ Object Storage ]                   [ Parse Topic (Kafka) ]
                  (HTML + Manifests)                               │
                                                     [ Offline Parser Workers ]
                                                                │
                                                     [ Match Topic (Kafka) ]
                                                                │
                                              [ Source-Centric Matcher Workers ]
                                                                │
                                             [ Backlink State Topic (Kafka) ]
                                                                │
                           ┌────────────────────────────────────┤
                           ▼                                    ▼
             [ Project-State MySQL Shards ]        [ ClickHouse History Engine ]
             (Current Edges + Alert Outbox)         (Immutable Fact Stream via CDC)
                           │
                           ▼
              [ API & Rollup Readers ]

Source-Centric Subscription Model

At 100M edges, repeatedly creating one project match job for each source page wastes work. Instead, a source-sharded subscription index maps each source URL to all projects that monitor it:

Subscription Index Structure
source_url_key
  → tenant_id
  → project_id
  → expected target URL/domain identities
  → freshness requirement and priority
  1. A provider discovery or target change emits a subscription event.
  2. The source shard idempotently updates the watch subscription.
  3. The source scheduler calculates the intersection of consumer freshness windows.
  4. One source validation is scheduled for the compatible consumer group.
  5. HTML is fetched once and parsed once.
  6. The matcher loads all subscriptions for that source in bounded pages.
  7. Found and missing results are emitted individually, keyed by backlink.
  8. Project-state consumers apply ordered transitions to the correct project shard.

Sharded DDL Tables (Vitess Keyspaces)

Table: source_watch_subscriptions (Source-Fetch Keyspace) 
-- Source-fetch keyspace; primary routing key is source_url_key.
CREATE TABLE source_watch_subscriptions (
  source_url_key          BINARY(16) NOT NULL,
  subscription_key        BINARY(16) NOT NULL,
  tenant_id               BINARY(16) NOT NULL,
  project_id              BINARY(16) NOT NULL,
  backlink_key            BINARY(16) NOT NULL,
  target_identity_key     BINARY(16) NOT NULL,
  fetch_profile_hash      BINARY(32) NOT NULL,
  next_due_at             TIMESTAMP(6) NOT NULL,
  validation_deadline     TIMESTAMP(6) NOT NULL,
  priority                SMALLINT NOT NULL DEFAULT 0,
  status                  ENUM('active', 'paused', 'deleted') NOT NULL DEFAULT 'active',
  subscription_version    BIGINT UNSIGNED NOT NULL,
  updated_at              TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
                            ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (source_url_key, subscription_key),
  KEY source_subscription_due_idx (next_due_at, source_url_key)
);
Table: backlink_current_sharded (Project-State Keyspace)
-- Project-state keyspace; primary routing is tenant/project plus edge bucket.
CREATE TABLE backlink_current_sharded (
  tenant_id                         BINARY(16) NOT NULL,
  project_id                        BINARY(16) NOT NULL,
  edge_bucket                       SMALLINT UNSIGNED NOT NULL,
  backlink_key                      BINARY(16) NOT NULL,
  source_url_key                    BINARY(16) NOT NULL,
  target_url_key                    BINARY(16) NOT NULL,
  current_state                     ENUM('unknown','live','lost') NOT NULL,
  source_fetch_health               ENUM('unknown','ok','blocked','error') NOT NULL,
  target_fetch_health               ENUM('unknown','ok','blocked','error') NOT NULL,
  is_redirected                     BOOLEAN NOT NULL DEFAULT FALSE,
  subscription_version              BIGINT UNSIGNED NOT NULL DEFAULT 0,
  last_applied_validation_sequence  BIGINT UNSIGNED NOT NULL DEFAULT 0,
  state_version                     BIGINT UNSIGNED NOT NULL DEFAULT 0,
  first_seen_at                     TIMESTAMP(6) NOT NULL,
  last_checked_at                   TIMESTAMP(6) NOT NULL,
  next_check_at                     TIMESTAMP(6) NOT NULL,
  updated_at                        TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
                                      ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (tenant_id, project_id, edge_bucket, backlink_key),
  KEY backlink_project_state_idx
    (tenant_id, project_id, current_state, last_checked_at DESC)
);

Kafka Event Topics

Every consumer is at-least-once and idempotent. Poison events go to a dead-letter topic with the original payload, schema version, failure reason, and retry history. Use a schema registry with compatibility checks.

Topic Partition Key Purpose
target-policy-events project ID Add / pause / delete target definitions
discovery-events source URL key Upsert source subscriptions
fetch-jobs source URL key Distributed fetch dispatch; host limits coordinated separately
fetch-results source URL key Produce/reuse immutable snapshots
parse-results source URL key Publish manifest evidence
target-validation-jobs target URL key Dispatch coalesced target status, redirect, canonical, and indexability checks
target-validation-results target URL key Publish target health independently from source-link availability
backlink-state-events backlink key Ordered per-edge transitions — all state changes for one edge stay in partition order
alert-events project ID Evaluate and deliver notifications
rollup-events project ID Maintain current project and referring-domain summaries
deletion-events tenant ID Orchestrate tenant/project erasure across all planes

Rollout Path to 100M

Activate the distributed architecture before any single MySQL writer or project queue approaches its tested capacity limit.

Stage Edge Target What Gets Enabled
1 100,000 edges Full production pipeline — monolithic MySQL, full scheduler, fetch coalescing, CDC, alerts
2 1 million edges Validate cost, source-domain distribution, 304 rates, and parser throughput
3 3–10 million edges Enable Kafka broker, CDC to ClickHouse, source-centric scheduling, and backpressure
4 10–30 million edges Split control, source-fetch, and project-state keyspaces; exercise live shard split/resharding
5 100 million edges Admit only after a 62.5M distinct-source base-case test passes the weekly SLO with required failure headroom

SLOs & Capacity Gates

Area SLO / Gate
Weekly coverage Eligible source validated before its hard 7-day deadline
Confirmation latency Normal missing-link confirmation completes within 2 hours
Scheduler health Oldest unscheduled due source remains inside rolling horizon
Fetch broker p99 queue age remains below consumer deadline slack
State stream Per-partition lag remains bounded and drains after bursts
MySQL shard Write latency, lock waits, buffer hit rate, storage, replica lag, and backup duration below tested limits
CDC / ClickHouse Durable watermark remains ahead of MySQL deletion boundary
Artifact integrity No observation references unavailable required evidence
Tenant fairness Small-tenant progress remains healthy during a dominant-tenant spike

Major Failure Scenarios & Responses

The following scenarios cover the most critical edge cases at 100M scale. Each has a defined system response that preserves correctness without violating host rate limits or producing false link-loss events.

Shard project state by tenant/project and apply weighted fairness. Shared fetches are anonymous public responses. One tenant's jobs, queries, imports, or exports cannot consume all shard or worker capacity.

A simple tenant-only shard key creates a hotspot. Subshard that tenant by project or stable edge bucket. Send large exports to ClickHouse/object storage instead of querying all transactional shards synchronously.

Use a project plus edge-bucket routing key. All full-project counts come from rollups — never scatter-count raw rows interactively. List queries include the relevant bucket plan or use a merge service over bounded shard cursors.

Per-host politeness makes the weekly SLA infeasible at low permitted rates. Calculate projected completion continuously, prioritize valuable/change-prone pages, reduce low-value checks under explicit policy, and show limited coverage. Never evade host rate limits.

A target gains millions of candidate backlinks in hours. Deduplicate before queueing, cap admission by tenant budget, verify in priority tiers, and keep provider discovery separate from verified live state.

Use provider discovery keys and import-run idempotency. Update last_discovered_at without recreating current edges, subscriptions, or verification jobs.

Network failures never mean "lost." Open a domain-level incident, suppress link-loss transitions, extend retries with jitter, and avoid an alert storm.

Require complete evidence and confirmation policy, then aggregate notifications into digests/incidents. Apply alert fan-out budgets rather than sending millions of individual emails.

Pin parser version in every manifest and observation. Stop affected consumers, deploy a fixed parser, and reparse retained HTML offline. Emit corrected state events with higher versions — do not refetch unnecessarily.

Create a new normalization version. Dual-read/dual-resolve during backfill, produce identity-remap events, merge only after collision and ownership verification, then retire the old version. Never rewrite 100 million identities in place.

HTTP parsing remains the default. Browser rendering is a separately budgeted tier triggered by policy or heuristics. Cache rendered snapshots and cap render time, memory, scripts, and network destinations.

Apply normalization rules, query-parameter policies, per-domain URL cardinality limits, path-depth limits, content fingerprinting, and crawl-frontier budgets. Quarantine domains producing explosive low-value URL variants.

Enforce compressed and expanded byte limits, ratio limits, parser CPU/memory deadlines, sandboxing, and no script execution in ordinary parsers. Mark evidence incomplete on any limit hit.

Pause parse dispatch, retain fetch job state, apply bounded retry, and exert backpressure before fetching additional bodies that cannot be stored. Do not mark links missing.

Keep transactional outbox rows until acknowledged, stop hot-history deletion, monitor per-partition lag, and scale/rebalance consumers. Ordering remains keyed by backlink — retries do not bypass a blocked edge with a newer event.

Current state continues in MySQL shards. Pause MySQL hot-history deletion when the analytics watermark falls behind. Historical dashboards expose stale-data status explicitly.

Fail over to a replica using the tested orchestration path. Consumers retry idempotently. Do not acknowledge broker events until the state transaction commits.

Throttle expensive exports, route reads to replicas when consistency allows, split the project using stable edge buckets, and move analytical queries to ClickHouse.

Write a deletion workflow record, stop new schedules, emit tombstones to every plane, delete project-shard state, remove subscriptions, expire artifacts when no references remain, and verify completion with reconciliation. Keep only legally permitted audit proof.

Apply event deduplication, per-rule cooldown, per-tenant delivery budgets, grouping, digest fallback, and incident-level notifications. Crawl correctness must not depend on notification success.

Keep project-to-home-region routing explicit. Restore/fail over control and project shards according to the recovery objective, replay broker events, and avoid active-active writes to one edge unless conflict resolution is formally designed.

Order state using monotonic state_version, not wall-clock time alone. Reject stale versions, retain event time for reporting, and synchronize infrastructure clocks.

Version cached robots decisions. On a new disallow rule, stop future fetches, set fetch health to blocked, and retain the previous availability state rather than declaring the link lost. On allow, resume through normal scheduling.

Verify artifact availability before accepting reuse. If unavailable, retry once without conditional headers and create a fresh snapshot.

After recovery, rebuild only the short rolling horizon, order work by hard deadline and value, and enforce admission limits. Do not immediately enqueue every missed slot. Record coverage debt and drain it within available host and tenant budgets.

Continue verifying already known backlinks. Pause or slow discovery imports with cursor-safe retry, expose discovery freshness separately from verification freshness, and never interpret absent provider data as lost backlinks.

Run exports asynchronously from ClickHouse into object storage, with a stable snapshot time, progress tracking, expiry, authorization recheck, and downloadable artifact. Never hold an API connection while scanning project-state shards.

Use the sharding layer's supported copy/catch-up/switch workflow, verify row counts and event watermarks, pause destructive cleanup until rollback expires, and load-test resharding before urgent capacity pressure. Keep routing keys immutable.

8. Summary & Key Takeaways

Architectural Summary Checklist

  • Fetch Coalescing: Deduplicates requests across projects for the same referring source URL.
  • Tiered Storage: MySQL for current state, ClickHouse for immutable analytics, Object Storage for compressed HTML.
  • Conditional GETs: Saves up to 70% network traffic with ETag / 304 Not Modified.
  • Reliable Alerts: Transactional outbox prevents duplicate or missed notifications.
  • Vitess Sharding: Scales from 10M to 100M edges by splitting project state from source scheduling.
  • Decoupled Auth: Opaque tenant_id keeps user identity separate from crawl data.
Need Architecture or Engineering Help?

Building Scalable Web Products or Custom APIs?

Explore how we architect high-throughput systems, browser extensions, and SaaS tools, or partner with us for dedicated AI product engineering.

Case Studies AI Services