Skip to content
VisibilityIQ365 VisibilityIQ365 VisibilityIQ Help

Projects & Audits

projects and audits are the spine of the data model. An organization owns many projects (one per domain); a project owns many audits (one per crawl run); an audit owns many pages and issues. All four tables are defined in migrations/001_initial_schema.sql.

projects

Section titled “projects”

One row per tracked domain. The settings, schedule, and notifications columns are JSON blobs (genuinely dynamic crawl config, not relational data). status is constrained to active | paused | archived.

CREATE TABLE IF NOT EXISTS projects (
  id              TEXT PRIMARY KEY,
  organization_id TEXT NOT NULL,
  name            TEXT NOT NULL,
  domain          TEXT NOT NULL,
  settings        TEXT NOT NULL DEFAULT '{}',   -- { maxPages, crawlDepth, crawlDelay, userAgent, respectRobots, ignorePatterns }
  schedule        TEXT,                         -- { enabled, frequency, day, time }
  notifications   TEXT,                         -- { email[], slack?, webhook? }
  status          TEXT NOT NULL DEFAULT 'active'
                    CHECK(status IN ('active', 'paused', 'archived')),
  last_audit_at   TEXT,
  created_at      TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at      TEXT NOT NULL DEFAULT (datetime('now')),
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE
);

Indexes: idx_projects_org (organization_id), idx_projects_status (status), idx_projects_domain (domain). Deleting an organization cascades to its projects.

A project must be active to start an audit: POST /api/audits returns 404 if the project is missing and rejects a non-active project before enqueuing a crawl.

audits

Section titled “audits”

One row per crawl run. The config column snapshots the crawl configuration at run time, so re-reading an old audit reflects the settings it actually used. The scalar count columns (total_issues, critical_issues, warnings, notices, pages_crawled, …) are denormalized rollups maintained as the crawl completes — cheaper than aggregating issues on every dashboard read.

CREATE TABLE IF NOT EXISTS audits (
  id               TEXT PRIMARY KEY,
  project_id       TEXT NOT NULL,
  campaign_id      TEXT,
  url              TEXT NOT NULL,
  status           TEXT NOT NULL DEFAULT 'pending'
                     CHECK(status IN ('pending', 'running', 'completed', 'failed', 'cancelled')),
  config           TEXT NOT NULL DEFAULT '{}',
  score            INTEGER CHECK(score >= 0 AND score <= 100),
  total_issues     INTEGER NOT NULL DEFAULT 0,
  critical_issues  INTEGER NOT NULL DEFAULT 0,
  warnings         INTEGER NOT NULL DEFAULT 0,
  notices          INTEGER NOT NULL DEFAULT 0,
  pages_crawled    INTEGER NOT NULL DEFAULT 0,
  pages_total      INTEGER NOT NULL DEFAULT 0,
  bytes_crawled    INTEGER NOT NULL DEFAULT 0,
  duration_seconds INTEGER,
  started_at       TEXT,
  completed_at     TEXT,
  created_at       TEXT NOT NULL DEFAULT (datetime('now')),
  FOREIGN KEY (project_id)  REFERENCES projects(id)  ON DELETE CASCADE,
  FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE SET NULL
);

Indexes: idx_audits_project, idx_audits_campaign, idx_audits_status, idx_audits_created (created_at DESC, for the “recent audits” list). campaign_id is optional — audits can group under a campaign (audit grouping per project) or stand alone; deleting a campaign nulls the reference rather than cascading.

Status lifecycle

Section titled “Status lifecycle”
pending ──► running ──► completed
                   └──► failed
   └──► cancelled
StatusMeaning
pendingRow created, seed URL not yet picked up by the crawler
runningCrawl in flight; started_at set, count columns updating
completedCrawl finished; score, completed_at, duration_seconds finalized
failedCrawl aborted unrecoverably
cancelledOperator/user stopped the run

score (0–100) is only meaningful once status = 'completed'; it is nullable for in-flight and failed runs.

Downstream tables

Section titled “Downstream tables”
TableRelationshipNotes
pagesaudit_id → audits.id (ON DELETE CASCADE)one row per crawled URL per audit; holds raw + rendered metadata for parity
issuesaudit_id → audits.id, page_id → pages.idone row per detected defect; category and severity are CHECK-constrained, evidence is JSON
audit_historyaudit_id → audits.idsummary snapshot per audit for the compare view

Note the defect table is named issues, not audit_issues. Its severity CHECK is critical | high | medium | low | notice and its category CHECK enumerates indexation, canonical, crawlability, parity, structured_data, internal_links, performance, security, ai_visibility, onpage, content.

Section titled “Related”