SEO module

The SEO module tracks keyword rankings, runs site audits, monitors backlinks, and surfaces recommendations. Provider gating on SerpApi + Google PageSpeed Insights so the heavy data calls are off the critical path.

Entities

Entity	What it represents
SEO project	A tracked domain. Has name, domain, target country, status.
Keyword	A search term being tracked. Has search volume estimate, current_rank, previous_rank, status (tracking/paused/lost).
Rank history	Daily snapshot of one keyword's rank. UNIQUE on (keyword_id, date) prevents day-doubles.
Audit	A site-wide check — PageSpeed Insights core scores + crawl-derived issue list.
Backlink	An external page linking to the project's domain. Has source_url, target_url, anchor_text, domain_authority, status (active/lost/pending).
Recommendation	Generated issue/action item with priority + type.
Competitor	Optional tracked rival domain for comparison reports.

Schema under seo with public.* shims.

Keyword rank tracking

Daily cron seo-keyword-ranks queries SerpApi for every active keyword and writes a seo_rank_history row. Caps:

Per-tenant cap of 50 keywords/run so a single tenant doesn't burn the shared SerpApi quota.
Global cap of 500 keywords/run as a safety ceiling.

Without SERPAPI_API_KEY configured, the cron logs "unconfigured" and exits — no errors, just no rank updates. Useful for self-host installs without SerpApi access.

Rank deltas surface on the project dashboard with up/down arrows; the per-keyword history page renders a sparkline.

Site audits

Weekly cron seo-audit-rerun reruns audits for SEO projects where the last audit is more than 7 days old. Caps to 20 audits per run. Each audit:

Fetches PageSpeed Insights mobile + desktop scores.
Crawls the homepage HTML for common issues (missing meta, thin H1, broken canonical, etc.).
Stores results + generates recommendations rows.

Audits can also be triggered manually from the project page.

Backlink verification

Weekly cron seo-backlinks-verify re-checks every status='active' backlink to confirm the source still links the target:

Fetches source_url (with SSRF guard — internal addresses blocked, redirects bounded, response body capped).
Parses HTML for an <a href=target_url> match (with URL normalization for trailing-slash + protocol differences).
Flips status='lost' if missing; leaves active if a fetch error (transient).

Backlink discovery is out of scope for the built-in cron — discovery needs a paid index (Ahrefs / Majestic / SerpApi link-search). Operators import discovered backlinks via CSV.

Recommendations

Audits produce recommendations grouped by type:

technical — site speed, crawlability, schema markup.
content — thin pages, missing H1, keyword cannibalization.
on_page — title length, meta description, alt text.
off_page — backlinks, mentions.
performance — Core Web Vitals.

Priority is one of critical / high / medium / low. Status moves open → in_progress → done (or dismissed).

Cross-module insights

SEO × Content: a published content item can be linked to target keywords; the SEO dashboard shows rank-after-publish.
SEO × Analytics: scheduled "rank by keyword" exports through the report builder.

Permissions