All documentation

    Data model

    Rank Pilot AI SEO stores its data in a single Supabase Postgres project. All application tables live in the public schema, are protected by Row Level Security (RLS), and are versioned through timestamped SQL files in supabase/migrations. This document describes the shape of the schema and the access-control approach at a conceptual level — it is not a SQL dump, and it deliberately omits row-level examples, keys or connection details.

    Core entities

    profiles holds one row per authenticated user (mirroring auth.users), with account-level fields such as display name and preferences. It is the anchor most other user-owned tables reference indirectly via user_id.

    websites (plus the related client_websites) represent the sites a user tracks in the product — domain, display name, verification state, and links to whichever Google Search Console / GA4 / CMS connection applies to that property. Websites are the hub the rest of the SEO domain hangs off:

    • keywords — the keyword set tracked per website, including target keyword text and metadata (search intent, priority).
    • keyword_rankings and rank_history — point-in-time and historical rank-tracking results per keyword/website, populated by scheduled rank-check jobs.
    • keyword_sync_schedules — configuration for how often a website's keyword/rank data is refreshed.
    • site_audits, self_audits, speed_tests, integrity_runs / integrity_findings — technical SEO and site-health audit results tied to a website.
    • competitors, competitor_analyses, competitor_intelligence_reports — competitor tracking scoped to a website.
    • tracked_backlinks, backlink_campaigns, backlink_opportunities, backlink_outreach, outreach_templates — backlink monitoring and outreach workflows.
    • autopilot_connections, autopilot_rules, autopilot_queue, autopilot_activity, autopilot_fix_history — the autopilot subsystem that queues and applies automated on-site/CMS fixes, with an activity/history trail per website.
    • gsc_connections — stores the OAuth connection state linking a website to a verified Google Search Console property.
    • website_collaborators and website_invites — multi-user access to a website (agency/team seats), separate from account-level user_roles.
    • business_locations — local-SEO/citation data associated with a website, feeding citation audit/build features.

    user_roles and has_role — see the dedicated section below.

    subscription_plans and plan_features define the catalogue of billing plans and the feature flags/limits attached to each. subscriptions and user_subscriptions record a user's actual billing state (plan, status, provider identifiers), reconciled from Stripe via webhook-driven edge functions. discount_codes and affiliate_codes/affiliate_commissions/affiliate_referrals support promotions and the affiliate programme. aeo_passes, managed_services/managed_services_config, and seo_plans cover add-on purchases and managed-service engagements.

    Queues and scheduled work are represented by autopilot_queue (per-website automated task queue), job_locks (mutual-exclusion locks so scheduled edge functions don't run concurrently for the same target), and winback_runs/winback_run_entries (batches of lifecycle/win-back email processing). These tables back cron-triggered edge functions rather than being queried directly by the UI.

    Logs and observability tables include audit_logs (privileged/administrative action trail), user_role_audit_log (specifically tracks role grants/revocations), runtime_error_logs (client/server runtime error capture), email_events and mailchimp_sync_state (delivery/engagement tracking), onboarding_events (product-analytics style funnel events), support_ticket_audit (support-ticket state changes), and pricing_validation_runs (automated pricing-page integrity checks).

    Support and content tables cover support_tickets/support_ticket_messages/support_settings/support_webhook_secrets for the helpdesk flow, chat_conversations/chat_messages and agent_threads/agent_messages for AI chat/agent features, blog_articles and saved_ai_results for generated content, challenge_tasks/seo_challenges for gamified onboarding, and demo_leads/contact_submissions/google_url_submissions for marketing-site capture forms.

    Credentials live in user_api_keys (BYOK third-party keys — see integrations.md), passkeys/passkey_challenges (WebAuthn credentials), and translations_cache (a generic cache table, unrelated to secrets).

    Relationships, in outline

    Almost every SEO-domain table (keywords, keyword_rankings, site_audits, tracked_backlinks, autopilot_*, competitors, gsc_connections, business_locations) carries a foreign key back to websites.id, and websites in turn carries a user_id (or, for team access, is joined through website_collaborators) back to the owning account. Billing tables (subscriptions, user_subscriptions, aeo_passes, managed_services) key off user_id directly rather than a website, since plans are account-level. Queue and log tables reference whichever entity they act on (a website, a user, or a job type) but are otherwise independent of the read-facing domain tables — they exist for backend processing and traceability rather than being surfaced as first-class UI resources.

    RLS approach

    Every application table has RLS enabled, and access is granted exclusively through explicit CREATE POLICY statements rather than broad table grants. The general pattern is:

    • Owner-scoped tables (most website- and user-linked data) use a policy of the shape USING (auth.uid() = user_id), or, for tables reached through a parent (e.g. keywords via websites), a subquery that checks the parent row's user_id (and, where team access applies, membership in website_collaborators).
    • Admin-only tables (audit_logs, user_role_audit_log, cron/queue internals, pricing validation runs) restrict SELECT/mutation policies to callers for whom has_role(auth.uid(), 'admin') is true, layered on top of the base GRANT SELECT ON ... TO authenticated — RLS narrows what the grant actually exposes at the row level.
    • Service-role-only tables used purely by edge functions typically grant ALL to service_role and either omit an authenticated policy entirely or provide a much narrower read-only one, since edge functions authenticate to Postgres with the service role and bypass RLS by design, while the client-side/browser Supabase key never sees those tables.
    • Policies consistently avoid recursive self-references (e.g. a user_roles policy must not query user_roles again to decide access), which is why role checks are centralised in the has_role function described below rather than inlined per-policy.

    user_roles and the has_role pattern

    Roles (such as admin) are stored in a dedicated user_roles table (one row per user_id/role pair) rather than as a column on profiles, avoiding a common RLS pitfall where a user could edit their own role if it lived on a self-editable row. Role checks are centralised in a SECURITY DEFINER SQL function, has_role(_user_id uuid, _role app_role) RETURNS boolean, which runs with a fixed search_path and reads user_roles with elevated privilege so that RLS policies on other tables can call it safely without granting callers direct read access to the roles table itself.

    This function is used in two places:

    • In RLS policies on admin-restricted tables, e.g. USING (has_role(auth.uid(), 'admin')), so authorisation logic lives in one auditable place rather than being duplicated across policies.
    • From the application, via supabase.rpc('has_role', { _user_id, _role }) (see src/hooks/useAdmin.ts), so the client can decide whether to render admin-only UI — always as a convenience check, never as the sole enforcement point, since the underlying tables and edge functions re-check the same policy/role server-side.

    Changes to user_roles are themselves logged to user_role_audit_log, giving a durable record of who granted or revoked a role and when.