Data Model & API Quick Reference¶

Last updated: 2026-03-25 | Covers: Phase 1 through Phase 9d

1. Data Model Overview¶

ER Diagram¶

erDiagram
    %% ── Core Business ──────────────────────────────
    businesses ||--o{ locations : "has"
    locations ||--o| data_cards : "has"
    locations ||--o| content_matrices : "has"
    locations ||--o{ articles : "has"
    locations ||--o| pipeline_runs : "has"
    locations ||--o{ images : "has"
    locations ||--o{ schema_markups : "has"
    locations ||--o| users : "assigned to"
    locations ||--o{ deployments : "has"
    locations ||--o{ batches : "has"
    locations ||--o{ batch_revisions : "has"
    locations ||--o{ notifications : "targets"
    locations ||--o{ consultation_requests : "has"
    locations ||--o{ visibility_scores : "has"
    locations ||--o{ visibility_alerts : "has"
    locations ||--o{ readiness_scores : "has"

    %% ── Pipeline ────────────────────────────────────
    pipeline_runs ||--o{ pipeline_events : "logs"
    pipeline_runs ||--o{ batches : "owns"
    pipeline_runs ||--o{ deployments : "triggers"

    %% ── Articles & Content ──────────────────────────
    articles ||--o{ schema_markups : "has"
    articles ||--o{ html_outputs : "rendered as"
    articles ||--o{ review_messages : "discussed in"
    articles ||--o{ article_versions : "versioned as"
    articles ||--o{ revision_instructions : "revised by"

    %% ── Batches & Revisions ─────────────────────────
    batches ||--o{ batch_revisions : "has"
    batch_revisions ||--o{ revision_instructions : "contains"

    %% ── Visibility ──────────────────────────────────
    visibility_scores ||--o{ visibility_checks : "contains"
    visibility_checks ||--o{ visibility_check_analyses : "analyzed by"
    visibility_scores ||--o| deployments : "linked to"
    visibility_scores ||--o{ visibility_alerts : "triggers"

    %% ── Readiness ───────────────────────────────────
    readiness_scores ||--o{ readiness_checks : "contains"

    %% ── Auth & Users ────────────────────────────────
    users ||--o{ notifications : "receives"
    users ||--o{ consultation_requests : "creates"
    users ||--o{ batch_revisions : "operator on"
    users ||--o{ system_settings : "updates"
    users ||--o{ settings_changelog : "authored"
    users ||--o{ system_broadcasts : "creates"

    %% ── API Usage ───────────────────────────────────
    locations ||--o{ api_usage : "incurs"

    %% ── Table shapes (key columns only) ─────────────
    businesses {
        uuid id PK
        string name
        string industry_vertical
        string business_type
        string website_url
    }
    locations {
        uuid id PK
        uuid business_id FK
        string city
        string state
        string location_slug
    }
    users {
        uuid id PK
        string supabase_id UK
        string email UK
        string role
        uuid location_id FK
    }
    articles {
        uuid id PK
        uuid location_id FK
        int matrix_item_index
        string title
        string review_status
        int batch_number
    }
    pipeline_runs {
        uuid id PK
        uuid location_id FK
        string status
        string current_step
        int current_batch_number
    }
    batches {
        uuid id PK
        uuid pipeline_run_id FK
        uuid location_id FK
        int batch_number
        string status
    }
    batch_revisions {
        uuid id PK
        uuid location_id FK
        uuid batch_id FK
        int revision_round
        string status
        float estimated_cost
    }
    visibility_scores {
        uuid id PK
        uuid location_id FK
        string score_type
        float composite_score
    }
    deployments {
        uuid id PK
        uuid location_id FK
        uuid pipeline_run_id FK
        string status
        int batch_number
    }
    system_broadcasts {
        uuid id PK
        text message
        bool is_active
        uuid created_by FK
        timestamptz created_at
        timestamptz deactivated_at
    }

2. Table Reference¶

All models inherit BaseMixin (unless noted), which provides: - id (UUID, PK, default uuid4) - created_at (DateTime TZ, server_default now()) - updated_at (DateTime TZ, server_default now(), onupdate utcnow)

2.1 businesses¶

Purpose: A customer's business entity (one business can have many locations).

Column	Type	Nullable	Default	Notes
`id`	UUID	no	uuid4	PK (BaseMixin)
`name`	String(255)	no
`industry_vertical`	String(100)	no
`business_type`	String(50)	no	`"single_location"`	single_location / multi_location / service_area
`website_url`	String(500)	yes
`website_platform`	String(100)	yes
`logo_url`	String(500)	yes
`nap_data`	JSON	yes		Name/Address/Phone
`offerings_data`	JSON	yes
`value_structure`	JSON	yes
`brand_snapshot`	JSON	yes

Relationships: locations (one-to-many, cascade delete)

2.2 locations¶

Purpose: A city+state delivery target within a business -- the central entity for a project.

Column	Type	Nullable	Default	Notes
`id`	UUID	no	uuid4	PK
`business_id`	UUID	no		FK -> businesses.id
`city`	String(100)	no
`state`	String(100)	no
`country`	String(100)	no	`"US"`
`lat`	Float	yes
`lng`	Float	yes
`location_slug`	String(200)	no		URL-safe slug
`pipeline_error`	Text	yes		Last pipeline error message

Constraints: UNIQUE(business_id, city, state)

Relationships: business, data_card (1:1), content_matrix (1:1), articles (1:N), pipeline_run (1:1), images (1:N), schema_markups (1:N), user (1:1)

2.3 data_cards¶

Purpose: BI research brief and client dossier for a location.

Column	Type	Nullable	Default	Notes
`id`	UUID	no	uuid4	PK
`location_id`	UUID	no		FK -> locations.id, UNIQUE
`bi_brief`	JSON	yes		BI Agent output
`local_proof_points`	JSON	yes
`competitors`	JSON	yes
`research_status`	String(20)	no	`"pending"`	pending / complete / reviewed
`client_feedback`	JSON	yes		Structured dossier (decisions, voice_and_tone, business_corrections, approved_patterns, red_lines, special_instructions, changelog)

2.4 content_matrices¶

Purpose: The 50-item content strategy matrix for a location.

Column	Type	Nullable	Default	Notes
`id`	UUID	no	uuid4	PK
`location_id`	UUID	no		FK -> locations.id, UNIQUE
`items`	JSON	yes		Array of matrix items
`total_pages`	Integer	no	`50`
`status`	String(20)	no	`"pending"`	pending / generated / reviewed / approved

2.5 articles¶

Purpose: A single AEO-optimized web page generated by the Writer Agent.

Column	Type	Nullable	Default	Notes
`id`	UUID	no	uuid4	PK
`location_id`	UUID	no		FK -> locations.id
`matrix_item_index`	Integer	no		Position in matrix
`title`	String(300)	no
`slug`	String(300)	no		URL slug
`page_type`	String(50)	no
`category`	String(100)	no
`body_markdown`	Text	yes
`body_html`	Text	yes
`faq_content`	JSON	yes		Array of Q&A objects
`value_section`	JSON	yes		CTA / value proposition
`local_proof_points_used`	JSON	yes
`seo_meta`	JSON	yes		Title, description, etc.
`cluster_assignment`	String(30)	yes		informational / comparison / problem_solution
`batch_number`	Integer	yes		1-5
`generation_status`	String(20)	no	`"pending"`	pending / generating / generated / reviewed / approved
`review_status`	String(20)	no	`"pending"`	pending / flagged / approved / edited
`customer_feedback`	Text	yes

Constraints: UNIQUE(location_id, matrix_item_index), CHECK review_status IN ('pending','flagged','approved','edited')

Relationships: location, schema_markups (1:N, cascade delete)

2.6 pipeline_runs¶

Purpose: State machine tracking pipeline execution for a location.

Column	Type	Nullable	Default	Notes
`id`	UUID	no	uuid4	PK
`location_id`	UUID	no		FK -> locations.id, UNIQUE
`status`	String(20)	no	`"queued"`	queued / running / paused / completed / failed
`current_step`	String(50)	no	`"bi_research"`
`started_at`	DateTime TZ	yes
`completed_at`	DateTime TZ	yes
`error_message`	Text	yes
`attempts`	Integer	no	`0`
`revision_round`	Integer	no	`0`
`current_batch_number`	Integer	yes		1-5 while in batch phase
`revision_in_progress`	Boolean	no	`false`	Concurrency guard for revision execution
`photo_gate_passed`	BOOLEAN	NOT NULL DEFAULT FALSE	Set TRUE when 100+ photos uploaded; skips photo gate on batches 2-5
`metadata_json`	JSON	yes

Relationships: location, events (1:N, ordered by created_at)

2.7 pipeline_events¶

Purpose: Append-only audit log of pipeline state transitions (immutable -- update/delete blocked by SQLAlchemy listeners).

Column	Type	Nullable	Default	Notes
`id`	UUID	no	uuid4	PK
`pipeline_run_id`	UUID	no		FK -> pipeline_runs.id
`event_type`	String(30)	no		status_change / gate_action / etc.
`from_status`	String(20)	yes
`to_status`	String(20)	yes
`step`	String(50)	yes
`actor_id`	UUID	yes		User who triggered the event
`note`	Text	yes

Indexes: (pipeline_run_id, created_at), (event_type, created_at)

2.8 images¶

Purpose: Tracks images through the processing pipeline (uploaded -> analyzed -> processed -> hosted).

Column	Type	Nullable	Default	Notes
`id`	UUID	no	uuid4	PK
`location_id`	UUID	no		FK -> locations.id, indexed
`original_filename`	String(500)	no
`raw_storage_path`	Text	no		Local temp path
`r2_url`	Text	yes		Cloudflare R2 public URL
`alt_text`	Text	yes		AI-generated alt text
`quality_score`	Float	yes
`category_match`	String(200)	yes
`is_ai_generated`	Boolean	no	`false`
`geotag_lat`	Float	yes
`geotag_lng`	Float	yes
`file_size_bytes`	Integer	yes
`status`	String(50)	no	`"uploaded"`	uploaded / analyzed / processed / hosted

Constraints: UNIQUE(location_id, original_filename) — prevents duplicate filename uploads per project.

2.9 schema_markups¶

Purpose: JSON-LD structured data for articles and global location schemas.

Column	Type	Nullable	Default	Notes
`id`	UUID	no	uuid4	PK
`location_id`	UUID	no		FK -> locations.id, indexed
`article_id`	UUID	yes		FK -> articles.id, indexed (null = global schema)
`schema_type`	String(100)	no		e.g. LocalBusiness, FAQPage
`json_ld`	Text	no		Raw JSON-LD string
`is_valid`	Boolean	no	`false`
`validation_errors`	JSON	yes

2.10 html_outputs¶

Purpose: Tracks generated HTML files uploaded to R2 for preview/delivery.

Column	Type	Nullable	Default	Notes
`id`	UUID	no	uuid4	PK
`location_id`	UUID	no		FK -> locations.id, indexed
`article_id`	UUID	no		FK -> articles.id
`r2_key`	String(500)	no		R2 storage key
`slug`	String(200)	no

2.11 deployments¶

Purpose: Tracks customer deployment lifecycle (pending -> confirmed -> measuring -> complete/measurement_failed).

Column	Type	Nullable	Default	Notes
`id`	UUID	no	uuid4	PK
`location_id`	UUID	no		FK -> locations.id, indexed
`pipeline_run_id`	UUID	no		FK -> pipeline_runs.id
`adapter_type`	String(30)	no		manual / wordpress / ghl
`status`	String(20)	no	`"pending"`	pending / confirmed / measuring / complete / measurement_failed
`hub_page_url`	String(500)	yes
`deployed_at`	DateTime TZ	yes
`visibility_score_id`	UUID	yes		FK -> visibility_scores.id
`batch_number`	Integer	yes		1-5 for batch-mode
`deployment_checklist`	JSON	yes		{pages_uploaded, google_search_console_submitted}

Constraints: UNIQUE(location_id, batch_number)

2.12 users¶

Purpose: Application user linked to Supabase Auth (admin or customer).

Column	Type	Nullable	Default	Notes
`id`	UUID	no	uuid4	PK
`supabase_id`	String(255)	no		UNIQUE, links to Supabase Auth
`email`	String(255)	no		UNIQUE
`role`	String(50)	no	`"customer"`	super_admin / admin / customer
`location_id`	UUID	yes		FK -> locations.id, UNIQUE
`is_active`	Boolean	no	`true`
`ghl_contact_id`	String(255)	yes		GoHighLevel contact ID
`product_tier`	String(50)	yes
`first_name`	String(100)	yes
`last_name`	String(100)	yes
`avatar_key`	String(500)	yes		R2 storage key for avatar

Constraints: CHECK role IN ('super_admin','admin','customer')

2.13 review_messages¶

Purpose: Server-side chat history for article review conversations (TD-010).

Column	Type	Nullable	Default	Notes
`id`	UUID	no	uuid4	PK
`created_at`	DateTime TZ	no	`now()`	Overrides BaseMixin to use `datetime.now(UTC)`
`article_id`	UUID	no		FK -> articles.id, indexed
`role`	String(20)	no		"customer" / "agent"
`content`	Text	no		Message body
`annotation_context`	JSON	yes		{element_text, annotation_number, source_tab}
`processed_in_round`	Integer	yes		Indexed; null = unprocessed, set after Haiku extraction

2.14 api_usage¶

Purpose: Tracks LLM API token usage and costs per agent call.

Column	Type	Nullable	Default	Notes
`id`	UUID	no	uuid4	PK
`location_id`	UUID	no		FK -> locations.id
`agent_type`	String(50)	no		bi_agent / strategist / writer / etc.
`model_used`	String(100)	no		e.g. claude-sonnet-4-20250514
`input_tokens`	Integer	no	`0`
`output_tokens`	Integer	no	`0`
`cache_read_tokens`	Integer	no	`0`
`cache_creation_tokens`	Integer	no	`0`
`estimated_cost`	Float	no	`0.0`	USD
`duration_ms`	Integer	no	`0`

2.15 pending_leads¶

Purpose: Proof-of-purchase gate -- created by GHL purchase webhook, consumed during onboarding.

Column	Type	Nullable	Default	Notes
`id`	UUID	no	uuid4	PK
`email`	String(255)	no		UNIQUE, indexed
`name`	String(255)	no
`phone`	String(50)	no	`""`
`ghl_contact_id`	String(255)	no
`product_tier`	String(50)	no
`status`	String(20)	no	`"pending"`	pending / converted
`converted_at`	DateTime TZ	yes		Set when customer completes onboarding

2.16 password_reset_tokens¶

Purpose: SHA-256 hashed, single-use, 15-minute-expiry tokens for password reset via GHL.

Column	Type	Nullable	Default	Notes
`id`	UUID	no	uuid4	PK
`email`	String(255)	no		indexed
`token_hash`	String(64)	no		UNIQUE, SHA-256 hex
`expires_at`	DateTime TZ	no		15 minutes from creation
`used`	Boolean	no	`false`	Atomic claim via UPDATE WHERE used=false

2.17 batches¶

Purpose: Tracks a batch of 10 articles through the production lifecycle.

Column	Type	Nullable	Default	Notes
`id`	UUID	no	uuid4	PK
`pipeline_run_id`	UUID	no		FK -> pipeline_runs.id
`location_id`	UUID	no		FK -> locations.id, indexed
`batch_number`	Integer	no		1-5
`status`	String(20)	no	`"pending"`	See CHECK below
`zip_url`	String(500)	yes		R2 key for batch ZIP
`failed_at_status`	String(20)	yes		Recovery breadcrumb
`operator_reviewed`	Boolean	no	`false`	True once operator reviews (batch 1 dual gate)
`revision_count`	Integer	no	`0`	Number of completed revision rounds

Constraints: UNIQUE(location_id, batch_number), CHECK status IN ('pending','writing','article_review','building_html','html_review','ready_to_ship','shipped','failed')

2.18 batch_revisions¶

Purpose: Tracks a batch-level revision round through approval and execution.

Column	Type	Nullable	Default	Notes
`id`	UUID	no	uuid4	PK
`location_id`	UUID	no		FK -> locations.id
`batch_id`	UUID	no		FK -> batches.id
`revision_round`	Integer	no
`estimated_cost`	Float	no	`0.0`
`status`	String(20)	no	`"pending_approval"`	See CHECK below
`auto_approved`	Boolean	no	`false`
`operator_id`	UUID	yes		FK -> users.id
`rejection_reason`	Text	yes
`error_message`	Text	yes
`approved_at`	DateTime TZ	yes
`completed_at`	DateTime TZ	yes

Constraints: CHECK status IN ('pending_approval','approved','executing','completed','failed','rejected')

Partial Unique Indexes: - uq_batch_revision_active_round -- one active revision per batch+round (excludes rejected/failed) - uq_batch_revision_inflight -- one in-flight revision per batch (approved or executing) - ix_batch_revision_pending -- fast lookup for pending approvals

2.19 revision_instructions¶

Purpose: Per-article revision instruction within a batch revision.

Column	Type	Nullable	Default	Notes
`id`	UUID	no	uuid4	PK
`batch_revision_id`	UUID	no		FK -> batch_revisions.id (CASCADE)
`article_id`	UUID	no		FK -> articles.id
`customer_instructions`	Text	no		From review chat extraction
`operator_instructions`	Text	yes		Optional operator override
`source_version_number`	Integer	yes		ArticleVersion at revision start
`result_version_number`	Integer	yes		ArticleVersion after revision
`execution_status`	String(20)	no	`"pending"`	pending / completed / failed
`error_detail`	Text	yes

Constraints: CHECK execution_status IN ('pending','completed','failed'), UNIQUE(batch_revision_id, article_id)

2.20 article_versions¶

Purpose: Content snapshots for article revision history (TD-021).

Column	Type	Nullable	Default	Notes
`id`	UUID	no	uuid4	PK
`article_id`	UUID	no		FK -> articles.id, indexed
`version_number`	Integer	no		Monotonically increasing
`body_markdown`	Text	no
`faq_content`	JSON	yes
`seo_meta`	JSON	yes
`value_section`	JSON	yes

Constraints: UNIQUE(article_id, version_number)

2.21 notifications¶

Purpose: In-app bell-icon notifications with dedup and cleanup.

Column	Type	Nullable	Default	Notes
`id`	UUID	no	uuid4	PK
`user_id`	UUID	no		FK -> users.id (CASCADE)
`location_id`	UUID	no		FK -> locations.id (CASCADE)
`event`	String(50)	no		CHECK against 33-event registry
`category`	String(20)	no		info / action_required / success / warning
`title`	String(200)	no
`message`	String(300)	no
`business_name`	String(200)	no
`batch_number`	Integer	yes
`action_url`	String(300)	yes
`is_read`	Boolean	no	`false`
`read_at`	DateTime TZ	yes
`dedup_key`	String(200)	yes		Partial unique index (non-null only)

Indexes: (user_id, is_read, created_at), cleanup partial index on created_at WHERE is_read=true

2.22 consultation_requests¶

Purpose: Customer upsell consultation requests with 7-day cooldown.

Column	Type	Nullable	Default	Notes
`id`	UUID	no	uuid4	PK
`location_id`	UUID	no		FK -> locations.id
`user_id`	UUID	no		FK -> users.id
`cooldown_until`	DateTime TZ	no		7 days from creation
`ghl_webhook_sent`	Boolean	no	`false`

Indexes: (location_id, cooldown_until)

2.23 system_settings¶

Purpose: Key-value DB overlay on env-var defaults for admin-configurable settings. No BaseMixin -- uses key as PK.

Column	Type	Nullable	Default	Notes
`key`	String(100)	no		PK
`value`	Text	no		String representation of setting value
`updated_at`	DateTime TZ	no	`now()`
`updated_by`	UUID	no		FK -> users.id

2.24 settings_changelog¶

Purpose: Append-only audit trail for admin settings changes. No BaseMixin -- standalone UUID PK.

Column	Type	Nullable	Default	Notes
`id`	UUID	no	uuid4	PK
`key`	String(100)	no		indexed
`old_value`	Text	yes		null for first-time set
`new_value`	Text	no
`changed_by`	UUID	no		FK -> users.id
`actor_role`	String(50)	yes
`changed_at`	DateTime TZ	no	`now()`

2.25 visibility_scores¶

Purpose: One visibility measurement run -- composite + per-engine + per-cluster scores.

Column	Type	Nullable	Default	Notes
`id`	UUID	no	uuid4	PK
`location_id`	UUID	no		FK -> locations.id
`score_type`	String(20)	no		baseline / post_deployment / scheduled / on_demand
`composite_score`	Float	no		0-100
`engine_scores`	JSON	no	`{}`	Per-engine breakdown
`cluster_scores`	JSON	no	`{}`	Per-cluster breakdown
`measured_at`	DateTime TZ	no
`quality_score`	Float	yes		0-100 (Phase 6a)
`negative_mention_rate`	Float	yes		0.0-1.0
`analysis_complete_ratio`	Float	yes		0.0-1.0
`scoring_version`	String(20)	yes		v0_provisional / v1_quality

Constraints: ck_visibility_score_type CHECK score_type IN ('baseline', 'post_deployment', 'scheduled', 'on_demand'), CHECK on quality_score (0-100), negative_mention_rate (0-1), analysis_complete_ratio (0-1)

2.26 visibility_checks¶

Purpose: Individual prompt check within a measurement run (one query to one AI engine).

Column	Type	Nullable	Default	Notes
`id`	UUID	no	uuid4	PK
`score_id`	UUID	no		FK -> visibility_scores.id, indexed
`cluster_type`	String(30)	no		informational / comparison / problem_solution / recommendation
`query`	String(500)	no		Test prompt sent to engine
`engine`	String(30)	no		chatgpt / perplexity / google_aio / gemini
`business_cited`	Boolean	no
`position`	Integer	yes		Rank position (null if not cited)
`competitors_cited`	JSON	no	`[]`
`source_urls`	JSON	no	`[]`
`raw_response_text`	Text	yes		Full engine response (16k cap)
`raw_snippet`	String(500)	yes
`is_truncated`	Boolean	yes
`response_hash`	String(64)	yes		SHA-256 of raw_response_text
`raw_response_expires_at`	DateTime TZ	yes		Retention expiry
`analysis_status`	String(20)	yes		pending / analyzing / analyzed / failed
`analysis_attempts`	Integer	no	`0`
`analysis_error`	Text	yes

Constraints: CHECK on analysis_status values, CHECK analysis_attempts >= 0

Indexes: (score_id), (analysis_status, updated_at) for sweeper

2.27 visibility_check_analyses¶

Purpose: Deep analysis of a single visibility check response (Haiku extraction, Sonnet escalation).

Column	Type	Nullable	Default	Notes
`id`	UUID	no	uuid4	PK
`check_id`	UUID	no		FK -> visibility_checks.id
`analyzer_version`	String(50)	no		e.g. "v1.1"
`analyzer_model`	String(50)	no		e.g. "haiku"
`sentiment`	String(10)	no		positive / neutral / negative
`recommendation_strength`	String(10)	no		primary / mentioned / passing
`source_attribution`	String(15)	no		own_site / directory / review_site / none
`confidence`	String(10)	no		high / medium / low
`evidence_text`	String(500)	yes
`evidence_url_match`	Boolean	no	`false`
`citation_context`	String(20)	yes		authority / proximity / reviews / recency / specialization / content_match / unknown
`citation_context_detail`	String(300)	yes
`started_at`	DateTime TZ	no
`completed_at`	DateTime TZ	yes

Constraints: UNIQUE(check_id, analyzer_version), CHECK constraints on sentiment, recommendation_strength, source_attribution, confidence, citation_context

2.28 visibility_alerts¶

Purpose: Alert history for visibility score drops with cooldown enforcement.

Column	Type	Nullable	Default	Notes
`id`	UUID	no	uuid4	PK
`location_id`	UUID	no		FK -> locations.id
`alert_type`	String(30)	no		score_drop / below_threshold
`trigger_score_id`	UUID	yes		FK -> visibility_scores.id (SET NULL on delete)
`composite_score`	Float	no		Score at time of alert
`peak_score`	Float	no		Historical peak
`drop_amount`	Float	no		peak - current
`cooldown_until`	DateTime TZ	no
`ghl_webhook_sent`	Boolean	no	`false`

Constraints: CHECK alert_type values, CHECK drop_amount >= 0

Indexes: (location_id, alert_type, cooldown_until) for cooldown queries

2.29 readiness_scores¶

Purpose: One website readiness measurement run for a location.

Column	Type	Nullable	Default	Notes
`id`	UUID	no	uuid4	PK
`location_id`	UUID	no		FK -> locations.id
`trigger`	String(20)	no		intake / post_deployment / on_demand
`status`	String(20)	no	`"running"`	running / complete / failed
`composite_score`	Float	yes		Weighted composite 0-100
`crawlability_grade`	String(10)	yes		pass / warn / fail
`crawlability_score`	Float	yes		0-100
`schema_grade`	String(10)	yes
`schema_score`	Float	yes
`speed_grade`	String(10)	yes
`speed_score`	Float	yes
`structured_data_grade`	String(10)	yes
`structured_data_score`	Float	yes
`pages_checked`	Integer	no	`0`
`issues_found`	Integer	no	`0`
`error_message`	Text	yes

Indexes: (location_id, trigger, created_at)

Relationships: checks (1:N, selectin)

2.30 readiness_checks¶

Purpose: One individual check within a readiness measurement.

Column	Type	Nullable	Default	Notes
`id`	UUID	no	uuid4	PK
`score_id`	UUID	no		FK -> readiness_scores.id
`category`	String(20)	no		crawlability / schema / speed / structured_data
`check_name`	String(50)	no
`target_url`	Text	no
`status`	String(10)	no		pass / fail / warn / error
`value`	Text	yes		Raw measurement value
`message`	Text	yes		Human-readable result

Indexes: (score_id, category)

2.31 system_broadcasts¶

Purpose: Operator-authored banner messages displayed to all customer portal users. Only one broadcast may be active at a time; creating a new one atomically deactivates the previous one.

Column	Type	Nullable	Default	Notes
`id`	UUID	no	uuid4	PK (own column, NOT BaseMixin)
`message`	Text	no		Broadcast content shown in portal banner
`is_active`	Boolean	no	`true`	Only one row may be active at a time
`created_by`	UUID	no		FK -> users.id (must be super_admin)
`created_at`	DateTime TZ	no	`now()`	Own column (NOT BaseMixin)
`deactivated_at`	DateTime TZ	yes		Set when broadcast is deactivated

Inheritance: SystemBroadcast inherits from Base only (NOT BaseMixin). It defines its own id (UUID) and created_at. It has NO updated_at column.

Constraints: Atomic deactivation enforced via SELECT FOR UPDATE on create — any existing active broadcast is deactivated before the new one is inserted.

Relationships: created_by -> users

3. API Endpoint Reference¶

All endpoints are mounted under the api_router in app/api/router.py.

Authentication levels: - Public -- no token required - Authenticated -- any valid JWT (customer or admin) - Customer -- authenticated + customer role (scoped to own project via location_id) - Admin -- require_admin dependency (admin or super_admin role) - Super Admin -- require_super_admin dependency (super_admin role only)

3.1 Health¶

Method	Path	Auth	Rate Limit	Description
`GET`	`/health`	Public	--	Returns `{"status": "ok"}`

3.2 Auth (`/api/v1`)¶

Method	Path	Auth	Rate Limit	Description
`GET`	`/api/v1/me`	Authenticated	--	Returns user role, email, profile, location_id. Response: `MeResponse`
`POST`	`/api/v1/auth/request-reset`	Public	5/min	Request password reset link via GHL webhook. Body: `PasswordResetRequest`
`POST`	`/api/v1/auth/reset-password`	Public	--	Confirm password reset with token. Body: `PasswordResetConfirm`

3.3 Onboarding (`/api/v1/onboard`)¶

Method	Path	Auth	Rate Limit	Description
`POST`	`/api/v1/onboard`	Public	5/min	Create customer account + project + start pipeline. Body: `OnboardingRequest`. Response: `OnboardingResponse` (201)

3.4 Projects (`/api/v1/projects`)¶

Method	Path	Auth	Rate Limit	Description
`POST`	`/api/v1/projects`	Admin	10/min	Create project + start pipeline. Body: `ProjectCreate`. Response: `ProjectResponse` (202)
`GET`	`/api/v1/projects/{location_id}`	Authenticated	--	Get project status. Response: `ProjectStatus`
`GET`	`/api/v1/projects/{location_id}/brief`	Authenticated	--	Get BI brief JSON
`GET`	`/api/v1/projects/{location_id}/matrix`	Authenticated	--	Get content matrix JSON
`GET`	`/api/v1/projects/{location_id}/articles`	Authenticated	--	Get all articles for project
`POST`	`/api/v1/projects/{location_id}/retry`	Admin	5/min	Retry failed pipeline (202)
`POST`	`/api/v1/projects/{location_id}/images`	Admin	--	Upload images (multipart). Response: `list[ImageResponse]` (201)

3.5 Admin (`/api/v1/admin`)¶

Method	Path	Auth	Rate Limit	Description
`GET`	`/api/v1/admin/metrics`	Admin	--	Dashboard metrics. Response: `DashboardMetrics`
`GET`	`/api/v1/admin/projects`	Admin	60/min	List all projects (with search via `?q=`). Response: `list[AdminProjectSummary]`
`GET`	`/api/v1/admin/projects/{location_id}/summary`	Admin	--	Single project summary. Response: `AdminProjectSummary`
`GET`	`/api/v1/admin/approvals`	Admin	--	Pending gate + revision approvals. Query: `?type=all\\|gate\\|revision`. Response: `list[AdminProjectSummary]`
`POST`	`/api/v1/admin/projects/{location_id}/gate`	Admin	10/min	Approve/reject quality gate. Body: `GateAction`. Response: `ActionResponse`
`GET`	`/api/v1/admin/projects/{location_id}/batches`	Admin	--	List batches for project. Response: `list[AdminBatchSummary]`

3.6 Admin Stats (`/api/v1/admin`)¶

Method	Path	Auth	Rate Limit	Description
`GET`	`/api/v1/admin/stats`	Admin	10/min	Full stats dashboard. Query: `?period=7d\\|30d\\|90d\\|all`. Response: `StatsResponse`

3.7 Admin Revisions (`/api/v1/admin`)¶

Method	Path	Auth	Rate Limit	Description
`GET`	`/api/v1/admin/revisions`	Admin	--	List batch revisions. Query: `?status=&limit=&offset=`. Response: `list[BatchRevisionSummary]`
`GET`	`/api/v1/admin/revisions/{revision_id}`	Admin	--	Revision detail with instructions. Response: `BatchRevisionDetail`
`POST`	`/api/v1/admin/revisions/{revision_id}/approve`	Admin	10/min	Approve revision + spawn executor. Body: `RevisionApproveRequest`. Response: `ActionResponse`
`POST`	`/api/v1/admin/revisions/{revision_id}/reject`	Admin	10/min	Reject revision. Body: `RevisionRejectRequest`. Response: `ActionResponse`
`POST`	`/api/v1/admin/revisions/{revision_id}/retry`	Admin	10/min	Retry failed revision. Response: `ActionResponse`

3.8 Admin Settings (`/api/v1/admin`)¶

Method	Path	Auth	Rate Limit	Description
`GET`	`/api/v1/admin/settings`	Admin	10/min	Get all settings (pipeline, visibility, alerts). Response: `AllSettingsResponse`
`PUT`	`/api/v1/admin/settings/{category}`	Super Admin	10/min	Update category settings. Body varies by category. Optimistic concurrency via `expected_updated_at`
`DELETE`	`/api/v1/admin/settings/{category}`	Super Admin	10/min	Reset category to env-var defaults

3.9 Customer Portal (`/api/v1/portal`)¶

Method	Path	Auth	Rate Limit	Description
`GET`	`/api/v1/portal/my-project`	Customer	--	Customer's project status. Response: `MyProjectResponse`
`GET`	`/api/v1/portal/my-project/articles`	Customer	--	Articles grouped by batch. Query: `?batch=N`. Response: `MyArticlesResponse` or `BatchArticles`
`POST`	`/api/v1/portal/my-project/articles/{article_id}/approve`	Customer	--	Approve single article. Response: `ActionResponse`
`POST`	`/api/v1/portal/my-project/articles/{article_id}/finish-editing`	Customer	--	Mark article as "edited" after review chat. Response: `ActionResponse`
`GET`	`/api/v1/portal/my-project/articles/{article_id}/messages`	Customer	--	Load chat history. Response: `list[ReviewMessageResponse]`
`POST`	`/api/v1/portal/my-project/approve-all`	Customer	--	Approve all content in current batch + resume pipeline. Response: `ActionResponse`
`POST`	`/api/v1/portal/my-project/review-chat`	Customer	--	Conversational review with Review Agent. Body: `RevisionRequest`. Response: `RevisionResponse`

3.10 Deployment (`/api/v1/portal`)¶

Method	Path	Auth	Rate Limit	Description
`POST`	`/api/v1/portal/my-project/confirm-deployment`	Customer	--	Confirm legacy (non-batch) deployment. Body: `ConfirmDeploymentRequest`. Response: `DeploymentResponse`
`GET`	`/api/v1/portal/my-project/deployment`	Customer	--	Get latest deployment. Response: `DeploymentResponse`
`GET`	`/api/v1/portal/my-project/preview/{slug}`	Customer	--	Serve HTML preview from R2 (iframe). Query: `?version=N`. Returns `HTMLResponse` with CSP sandbox
`POST`	`/api/v1/portal/my-project/measure-visibility`	Customer	1/hour	Trigger on-demand visibility scan. Response: `MeasureVisibilityResponse`

3.11 Download (`/api/v1/portal`)¶

Method	Path	Auth	Rate Limit	Description
`GET`	`/api/v1/portal/my-project/download`	Customer	--	Download project ZIP (batch or legacy). Response: streaming ZIP

3.12 Batch (`/api/v1/portal`)¶

Method	Path	Auth	Rate Limit	Description
`GET`	`/api/v1/portal/my-project/batches`	Customer	--	List all batches with counts. Response: `list[BatchSummary]`
`GET`	`/api/v1/portal/my-project/batches/{batch_number}/download`	Customer	--	Download batch ZIP. Response: streaming ZIP
`POST`	`/api/v1/portal/my-project/batches/{batch_number}/confirm-deployment`	Customer	5/min	Confirm batch deployment with checklist. Body: `BatchConfirmDeploymentRequest`
`POST`	`/api/v1/portal/my-project/batches/{batch_number}/finish-review`	Customer	5/min	Finish review -- extract dossier, create revision or resume pipeline. Response: `FinishReviewResponse`
`GET`	`/api/v1/portal/my-project/batches/{batch_number}/revision-status`	Authenticated	--	Returns latest revision status for a customer's batch.

3.13 Versions (`/api/v1/portal`)¶

Method	Path	Auth	Rate Limit	Description
`GET`	`/api/v1/portal/my-project/articles/{article_id}/versions`	Customer	--	List article versions. Response: `list[ArticleVersionSummary]`
`GET`	`/api/v1/portal/my-project/articles/{article_id}/versions/{version_number}`	Customer	--	Get full version content. Response: `ArticleVersionDetail`

3.14 Visibility (`/api/v1/visibility`)¶

Method	Path	Auth	Rate Limit	Description
`GET`	`/api/v1/visibility/{location_id}/scores`	Authenticated	--	All visibility scores, newest first. Response: `list[VisibilityScoreResponse]`
`GET`	`/api/v1/visibility/{location_id}/scores/latest`	Authenticated	--	Most recent score. Response: `VisibilityScoreResponse`
`GET`	`/api/v1/visibility/{location_id}/scores/{score_id}/checks`	Authenticated	--	Individual prompt checks for a score. Response: `list[VisibilityCheckResponse]`
`POST`	`/api/v1/visibility/{location_id}/scores/check`	Authenticated	3/hour	Trigger on-demand visibility check. Response: `VisibilityScoreResponse`

3.15 Admin Visibility (`/api/v1/admin/visibility`)¶

Method	Path	Auth	Rate Limit	Description
`POST`	`/api/v1/admin/visibility/{score_id}/reanalyze`	Admin	10/hour	Re-analyze all checks for a score. Response: `ReanalysisResponse`
`POST`	`/api/v1/admin/visibility/backfill`	Admin	5/hour	Re-analyze historical scores. Body: `BackfillRequest`. Response: `BackfillResponse`

3.15a Visibility Scan Scheduler (Background Task)¶

Not a direct API endpoint. A background task runs on an hourly tick and automatically queues visibility scans for deployed projects based on configurable frequency settings.

Behavior: - Queries all projects that have at least one complete deployment - For each project, determines whether a scan is due based on time since last visibility_scores row - Early period (first 30 days post-deployment): scans every scan_frequency_early days (default: 7) - Steady state (after 30 days): scans every scan_frequency_steady days (default: 30)

Configurable settings (admin settings UI, Visibility category, stored in system_settings):

Key	Type	Default	Description
`scan_enabled`	bool	`true`	Master switch — disables all scheduled scans when false
`scan_frequency_early`	int	`7`	Days between scans in early period (≤ 30 days post-deploy)
`scan_frequency_steady`	int	`30`	Days between scans in steady state (> 30 days post-deploy)

GHL event: Completed scheduled scans fire the visibility_measured webhook event.

3.16 Visibility Intelligence¶

Method	Path	Auth	Rate Limit	Description
`GET`	`/api/v1/visibility/{location_id}/trends`	Authenticated	30/hour	Score trend analysis. Query: `?since=ISO_DATE`. Response: `TrendResponse`
`GET`	`/api/v1/visibility/{location_id}/competitors`	Authenticated	30/hour	Aggregated competitor intelligence. Query: `?since=ISO_DATE`. Response: `CompetitorsResponse`
`GET`	`/api/v1/admin/visibility/{location_id}/alerts`	Admin	30/hour	Alert history for a location. Response: `AlertsResponse`

3.17 Readiness (`/api/v1/readiness`)¶

Method	Path	Auth	Rate Limit	Description
`POST`	`/api/v1/readiness/{location_id}/check`	Admin	5/hour	Trigger readiness check. Body: `ReadinessTriggerRequest`. Response: `ReadinessScoreResponse`
`GET`	`/api/v1/readiness/{location_id}/scores/latest`	Authenticated	--	Latest completed score per trigger. Response: `ReadinessLatestResponse`
`GET`	`/api/v1/readiness/{location_id}/scores/{score_id}/checks`	Authenticated	--	Individual checks for a score. Query: `?category=`. Response: `list[ReadinessCheckResponse]`
`GET`	`/api/v1/readiness/{location_id}/scores`	Authenticated	--	All readiness scores history. Response: `list[ReadinessScoreResponse]`

3.18 GHL Inbound Webhooks (`/api/v1/webhooks/ghl`)¶

Method	Path	Auth	Rate Limit	Description
`POST`	`/api/v1/webhooks/ghl/purchase`	Ed25519 (X-GHL-Signature) + legacy HMAC fallback	--	Receive purchase notification, create PendingLead. Body: `PurchaseWebhookPayload`. Response: `PurchaseWebhookResponse`

3.19 Notifications (`/api/v1`)¶

Method	Path	Auth	Rate Limit	Description
`GET`	`/api/v1/notifications/unread-count`	Authenticated	30/min	Unread count for bell badge. Response: `UnreadCountResponse`
`GET`	`/api/v1/notifications`	Authenticated	30/min	Paginated notification list. Query: `?limit=&cursor=`. Response: `NotificationListResponse`
`PATCH`	`/api/v1/notifications/{notification_id}/read`	Authenticated	--	Mark one notification read. Response: `NotificationResponse`
`POST`	`/api/v1/notifications/mark-all-read`	Authenticated	--	Mark all unread as read. Response: `MarkAllReadResponse`
`POST`	`/api/v1/admin/notifications/cleanup`	Admin	--	Delete expired notifications. Response: `CleanupResponse`

3.20 Consultation¶

Method	Path	Auth	Rate Limit	Description
`GET`	`/api/v1/portal/my-project/consultation-status`	Customer	30/hour	Check consultation cooldown status. Response: `ConsultationStatusResponse`
`POST`	`/api/v1/portal/my-project/request-consultation`	Customer	10/hour	Request consultation (7-day cooldown). Response: `ConsultationStatusResponse`

3.21 Profile (`/api/v1/profile`)¶

Method	Path	Auth	Rate Limit	Description
`PATCH`	`/api/v1/profile`	Authenticated	--	Update name fields. Body: `ProfileUpdate`. Response: `ProfileResponse`
`POST`	`/api/v1/profile/avatar`	Authenticated	10/min	Upload avatar (JPEG/PNG/WebP, 2MB max, EXIF stripped). Response: `ProfileResponse`
`DELETE`	`/api/v1/profile/avatar`	Authenticated	--	Remove avatar. Response: `ProfileResponse`

3.22 Customer Photos API (`app/api/photos.py`)¶

Endpoint	Method	Auth	Description
`/api/v1/portal/my-project/photos`	POST	Customer	Upload photos (multipart, multi-file). Validates magic bytes, size (10MB), dimensions (400x300 min). Deduplicates by filename. 300 cap. Auto-resumes pipeline at 100.
`/api/v1/portal/my-project/photos`	GET	Customer	List uploaded photos (paginated, 50/page). Includes quality badges (good/fair/poor).
`/api/v1/portal/my-project/photo-status`	GET	Customer	Quick count + gate status + quality breakdown for dashboard card.
`/api/v1/admin/projects/{location_id}/photo-status`	GET	Admin	Admin view of photo upload progress with quality breakdown.

3.23 Broadcast API (`/api/v1/admin/broadcast`, `/api/v1/portal/broadcast`)¶

Method	Path	Auth	Rate Limit	Description
`POST`	`/api/v1/admin/broadcast`	Super Admin	10/min	Create new broadcast. Atomically deactivates any existing active broadcast via SELECT FOR UPDATE before insert. Body: `{message: string}`. Response: `BroadcastAdminResponse`
`POST`	`/api/v1/admin/broadcast/deactivate`	Super Admin	10/min	Deactivate the current active broadcast. Response: `BroadcastAdminResponse`
`GET`	`/api/v1/admin/broadcast`	Super Admin	--	Get active broadcast (full fields: id, message, is_active, created_by, created_at, deactivated_at). Response: `BroadcastAdminResponse` or `null`
`GET`	`/api/v1/portal/broadcast`	Authenticated	--	Get active broadcast for portal banner (limited fields: id, message, created_at only). Response: `BroadcastResponse` or `null`

4. Auth Flow¶

JWT Authentication¶

Client -> Supabase Auth -> JWT (ECC P-256 signed)
             |
Client --[Authorization: Bearer <JWT>]--> FastAPI
             |
       middleware/auth.py
             |
       1. Extract token from Authorization header
       2. Decode JWT:
          - JWKS verification (asymmetric, ECC/RSA)
       3. Extract supabase_id (sub claim)
       4. Query `users` table by supabase_id
       5. Build UserContext(user_id, supabase_id, email, role, location_id)
             |
       Inject into endpoint via Depends()

Role-Based Access¶

Dependency	Allowed Roles	Use Case
`require_authenticated`	super_admin, admin, customer	Any logged-in user
`require_admin`	super_admin, admin	Operator portal endpoints
`require_super_admin`	super_admin	Settings mutations

Customer Scoping¶

Customer endpoints (/api/v1/portal/my-project/*) enforce ownership: 1. user.location_id must be non-null (403 otherwise) 2. All queries filter by location_id from UserContext 3. No customer can access another customer's data

Admin Scoping¶

Admin endpoints for project-specific data use authorize_location_access(): - Admins can access any location - Customers can only access their own location_id

GHL Webhook Auth¶

The purchase webhook (/api/v1/webhooks/ghl/purchase) uses dual authentication: - Primary: Ed25519 signature verification — verifies X-GHL-Signature header against GHL's published public key hardcoded in _GHL_ED25519_PUBLIC_KEY_PEM. A failed Ed25519 check does NOT fall through to legacy HMAC. - Fallback: Legacy HMAC — only used when X-GHL-Signature header is absent. Requires X-GHL-Secret header, compared via hmac.compare_digest() against GHL_WEBHOOK_SECRET env var.

Table Count Summary¶

Category	Tables	Count
Core Business	businesses, locations, data_cards, content_matrices	4
Articles & Content	articles, html_outputs, schema_markups, images, article_versions	5
Pipeline	pipeline_runs, pipeline_events, batches	3
Revisions	batch_revisions, revision_instructions, review_messages	3
Deployment	deployments	1
Auth & Users	users, pending_leads, password_reset_tokens	3
Visibility	visibility_scores, visibility_checks, visibility_check_analyses, visibility_alerts	4
Readiness	readiness_scores, readiness_checks	2
Notifications	notifications	1
Settings	system_settings, settings_changelog	2
Usage	api_usage	1
Consultation	consultation_requests	1
Broadcast	system_broadcasts	1
Total		31