Skip to content
English - United Kingdom
Sign in
Contact us
  • There are no suggestions because the search field is empty.

Supabase Schema Overview

Navigate the Ops Hub database without reading every migration file.

About the database

Ops Hub uses a single Supabase project (pphqguceuhrytszaykgd). There is no dev/live split -- all environments point at the same database. Schema changes are always applied via timestamped migration files in supabase/migrations/; never alter tables directly.

All tables use Row Level Security (enabled, no policies). The app reads and writes using the service-role key which bypasses RLS. The empty policy set blocks accidental anon-key access.

Core client and user tables

  • clients -- master client list. Key columns: id (TEXT), name, package, email, website, account_manager, ga4_data_account_id, gsc_data_account_id, sf_default_config_name, default_report_type, start_date, keyword_com_project_id, staging_url.
  • users -- BTT staff. Columns include role (admin/exec/management/team), encrypted Google tokens, booking_link, msp_member, msp_can_approve_changes, msp_can_close_cycles, payroll_protected.
  • google_data_accounts -- reporting Google accounts (not staff accounts). Encrypted tokens, label, used for GA4/GSC data resolution.

Reporting tables

  • reports -- monthly client reports. Columns: client_id, report_type, status (draft/published), period_start, period_end, data_snapshot (JSONB), generation_cost_gbp, magic_link_token, published_at.
  • report_auto_create_runs -- audit log for the monthly auto-create cron. One row per firing with outcome counts and per-client errors.
  • aeo_runs / aeo_results -- AEO scan runs and per-prompt results. Pruned after 12 months.

Managed SEO Platform tables

  • cycles -- one row per billable MSP cycle. Status machine: pending > active > in_review > closed/abandoned. FKs to clients and close_codes.
  • changes -- append-only. One row per proposed or applied SEO change within a cycle. Change types: meta_title, meta_description, schema_inject, alt_text. Approval status: proposed/approved/rejected/applied/failed.
  • platform_users -- client-side CMS users (WP admin, Shopify installer) the connector authenticates as.
  • connector_tokens -- SHA-256 hashed Bearer tokens issued to client CMS connectors.
  • crawl_jobs -- SF crawl jobs run as part of an MSP cycle.
  • blog_drafts -- skeleton blog post stubs generated per cycle by the msp-cycle-blogs cron.
  • magic_link_tokens -- cycle-close report links (MSP variant).
  • health_pings -- heartbeat records from client CMS connectors.
  • close_codes -- enum of cycle close reasons (seeded, append-only).

Outbound prospecting tables

  • prospecting_queue -- queued campaigns (industry + location combos).
  • prospecting_runs -- execution log for each queue item.
  • prospecting_audits -- per-prospect detail (domain, contacts, enrichment data, email drafts, outreach status). Pruned after 180 days.
  • prospecting_exclusions -- every excluded domain/email with reason code and run_id for audit.
  • prospecting_blocklist -- permanent domain-level block list (Settings UI).

Automation and alerting tables

  • churn_alerts -- current risk scores per client: score, signals fired, last updated.
  • discovery_call_log -- record of HubSpot Meetings found and processed by the discovery call cron.
  • fathom_processed_recordings -- idempotency table for Fathom webhook/cron processing.
  • hubspot_mention_drafts -- dedup table for the HubSpot @mention auto-reply cron.
  • payment_alerts -- Xero paid invoices missing a HubSpot ticket. Self-resolves when the ticket appears.
  • hubspot_sync_log -- MSP ticket poller watermark and processing log. Pruned after 60 days.
  • admin_audit_log -- every admin-level mutation with actor, action, target, details JSON.

Business intelligence tables

  • pnl_snapshots -- weekly P&L snapshots from Xero with week-over-week deltas.
  • beach_snapshots -- weekly Beach Dashboard snapshots (GA4, GSC, AI citations, Instagram). One row per source per ISO week.
  • account_cost_snapshots -- daily account-level spend sync from Anthropic, DataForSEO, Apollo.
  • btt_ai_citation_runs / btt_ai_citation_results / btt_ai_citation_queries -- monthly brand mention scans across ChatGPT, Claude, Perplexity, Gemini.
  • intel_runs -- Jarvis Intel system run history.

Other key tables

  • app_settings -- key-value store for Xero tokens, MSP poller watermark, etc.
  • sf_audits -- SF crawl history across all clients (archived after 90 days).
  • sf_configs -- per-client SF config file registry.
  • parking_bookings -- car parking bookings (space 65, weekdays only).
  • suppliers -- trusted suppliers directory.
  • launcher_runs -- Skills Launcher run history with quality ratings.
  • brand_context / brand_items -- Brand Hub content (source for getBrandContextMarkdown()).
  • client_offboarding -- structured offboarding checklist per client.