Overview The Trending Society database contains 127 custom tables plus 37 Supabase-managed tables (164 total), organized across 3 schemas.
Project: trendingsociety-production
Project Ref: ymdccxqzmhxgbjbppywf
Schema Version: 015
Last Updated: 2025-12-25Schema Tables Purpose Access public111 Product data, user content RLS required system14 Infrastructure, API keys service_role only events1 Immutable audit logs append-only
Multi-Tenant Status: 42/127 tables have tenant_id column.
Source: agents/schema/SCHEMA.md
Table Index
System Infrastructure (10 tables)
system.circuit_breakers API resilience with automatic recovery. Column Type Description iduuid Primary key service_nametext Unique service identifier failure_countinteger Current failure count is_openboolean Circuit is open (blocking requests) last_failuretimestamptz Last failure timestamp last_successtimestamptz Last success timestamp opened_attimestamptz When circuit opened last_errortext Last error message last_error_codetext HTTP status or error code thresholdinteger Failures before opening (default: 5) cooldown_secondsinteger Wait before retry (default: 60) created_attimestamptz Row created updated_attimestamptz Row updated
system.checkpoints Workflow state for resumable processing. Column Type Description iduuid Primary key workflow_nametext Unique workflow identifier last_processed_idtext Last successfully processed ID last_processed_attimestamptz When last item was processed items_processedinteger Total items in current run current_run_idtext UUID for current execution statustext ’running’, ‘completed’, ‘failed’, ‘paused’ started_attimestamptz Current run started completed_attimestamptz Current run completed error_messagetext Error if failed retry_countinteger Retry attempts metadatajsonb Workflow-specific state created_attimestamptz Row created updated_attimestamptz Row updated
system.ai_usage Token and cost tracking for near-zero optimization. Column Type Description iduuid Primary key tasktext Task type (classify, extract, generate, etc.) workflow_nametext Parent workflow execution_idtext Specific execution providertext ’openai’, ‘anthropic’, ‘google’ modeltext Specific model name input_tokensinteger Input tokens used output_tokensinteger Output tokens used total_tokensinteger Total tokens estimated_costnumeric(10,6) Cost in USD (6 decimal places) content_iduuid Related content if applicable record_typetext ’content’, ‘audience’, ‘generation’ latency_msinteger Response time created_attimestamptz Row created
system.ai_model_config Model routing for cost optimization. Column Type Description iduuid Primary key task_typetext ’classify’, ‘extract’, ‘generate’, ‘vision’, ‘embed’, ‘reason’ providertext ’google’, ‘openai’, ‘anthropic’ model_nametext Full model identifier api_endpointtext API URL cost_per_1m_inputnumeric Cost per 1M input tokens cost_per_1m_outputnumeric Cost per 1M output tokens max_tokensinteger Max output tokens requests_per_minuteinteger Rate limit tokens_per_minuteinteger Token rate limit fallback_modelsjsonb Array of fallback model configs supports_visionboolean Can process images supports_json_modeboolean Structured output supports_streamingboolean Streaming responses is_activeboolean Currently enabled created_attimestamptz Row created updated_attimestamptz Row updated
system.state_history Audit trail for all state transitions. Column Type Description iduuid Primary key table_nametext Source table record_iduuid Record that changed old_statetext Previous state value new_statetext New state value changed_byuuid User/system that made change change_reasontext Why the change occurred metadatajsonb Additional context created_attimestamptz When change occurred
system.api_keys Encrypted API key storage. Column Type Description iduuid Primary key service_nametext Service identifier key_nametext Key identifier (e.g., ‘primary’, ‘backup’) encrypted_valuetext Encrypted key value expires_attimestamptz Expiration date last_rotated_attimestamptz Last rotation is_activeboolean Currently active created_attimestamptz Row created updated_attimestamptz Row updated
system.ai_pricing AI model pricing per token for cost optimization. Column Type Description iduuid Primary key providertext ’openai’, ‘anthropic’, ‘google’ model_nametext Full model identifier input_cost_centsinteger Cost per 1M input tokens in cents output_cost_centsinteger Cost per 1M output tokens in cents context_windowinteger Max context tokens max_output_tokensinteger Max output tokens supports_visionboolean Image input support supports_function_callingboolean Tool use support supports_json_modeboolean Structured output latency_tiertext ’fast’, ‘medium’, ‘slow’ quality_tiertext ’basic’, ‘standard’, ‘premium’ is_activeboolean Currently enabled deprecated_attimestamptz Deprecation date effective_fromtimestamptz When pricing took effect created_attimestamptz Row created updated_attimestamptz Row updated
system.tenant_automations Per-tenant automation subscriptions. Cron jobs query this to run automations for enabled tenants. Column Type Description iduuid Primary key tenant_idtext Tenant identifier (‘trendingsociety’, ‘golfinsider’) automation_keytext Automation type (‘social_post’, ‘order_sync’) automation_nametext Human-readable name automation_descriptiontext Description for UI configjsonb Automation-specific settings credentials_reftext Reference to vault credentials cron_overridetext Custom cron expression (null = use default) timezonetext Tenant’s timezone (default: UTC) enabledboolean Is automation active? paused_attimestamptz When manually paused paused_reasontext Why paused last_run_attimestamptz Last execution time last_run_statustext ’success’, ‘failure’, ‘skipped’ last_run_duration_msinteger Execution duration last_errortext Last error message run_countinteger Total successful runs error_countinteger Total failed runs tiertext ’free’, ‘standard’, ‘premium’ monthly_price_centsinteger Price if selling as product created_attimestamptz Row created updated_attimestamptz Row updated
system.automation_registry Master list of available automations (product catalog for multi-tenant platform). Column Type Description iduuid Primary key automation_keytext Unique automation identifier nametext Display name descriptiontext Description for UI categorytext ’content’, ‘ecommerce’, ‘reporting’ default_crontext Default cron expression default_timezonetext Default timezone required_credentialstext[] Required credential types required_config_fieldsjsonb Schema for config validation available_tierstext[] Available subscription tiers tier_pricingjsonb Pricing per tier in cents is_activeboolean Available for subscription is_betaboolean Beta feature flag created_attimestamptz Row created updated_attimestamptz Row updated
system.automation_runs Audit log of all automation executions. Column Type Description iduuid Primary key tenant_automation_iduuid FK to tenant_automations tenant_idtext Tenant identifier automation_keytext Automation type started_attimestamptz Execution start finished_attimestamptz Execution end duration_msinteger Execution duration statustext ’running’, ‘success’, ‘failure’, ‘skipped’ items_processedinteger Total items processed items_succeededinteger Successful items items_failedinteger Failed items error_messagetext Error if failed error_stacktext Stack trace trigger_typetext ’cron’, ‘manual’, ‘webhook’ execution_contextjsonb Additional metadata
events.outbox Transactional outbox for async event processing (Stripe/Uber/Meta pattern). Column Type Description iduuid Primary key aggregate_typetext Entity that generated event (e.g., publisher_post) aggregate_iduuid Entity ID event_typetext What happened: created, updated, published payloadjsonb Full event data tenant_iduuid Multi-tenant scope (NULL for system) actor_iduuid Who caused this (user, system, webhook) correlation_iduuid Distributed trace ID occurred_attimestamptz When business event happened enqueued_attimestamptz When inserted to outbox processed_attimestamptz NULL = pending, timestamp = delivered retry_countinteger Delivery attempts last_errortext Most recent delivery error sequence_numberbigint Monotonic ordering key
Content Engine (12 tables)
content_sources Multi-platform scraping configuration. Column Type Description iduuid Primary key platformtext ’instagram’, ‘tiktok’, ‘youtube’, ‘twitter’, ‘reddit’, ‘rss’ source_typetext ’profile’, ‘hashtag’, ‘location’, ‘feed’, ‘channel’ identifiertext Username, hashtag, URL, etc. display_nametext Human-readable name profile_urltext Link to source avatar_urltext Profile image followers_countinteger Follower count posts_countinteger Total posts is_verifiedboolean Platform verified scrape_frequencytext ’hourly’, ‘daily’, ‘weekly’ apify_actor_idtext Apify actor for this source scrape_configjsonb Actor-specific settings last_scraped_attimestamptz Last scrape time next_scrape_attimestamptz Scheduled next scrape consecutive_failuresinteger Failures since last success avg_yield_ratenumeric Avg new items per scrape avg_quality_scorenumeric Avg content quality degraded_attimestamptz When quality dropped auto_disabled_attimestamptz When auto-disabled vertical_iduuid FK → publisher_verticals trust_scoreinteger 0-100 source reliability is_activeboolean Currently active created_attimestamptz Row created updated_attimestamptz Row updated
source_scrape_logs Quality scoring per scrape run. Column Type Description iduuid Primary key source_iduuid FK → content_sources run_idtext Apify run ID items_foundinteger Raw items discovered items_newinteger New items (not duplicates) items_processedinteger Successfully processed yield_ratenumeric items_new / items_found quality_scorenumeric Avg content quality apify_costnumeric Apify credit cost duration_secondsinteger Scrape duration error_messagetext Error if failed created_attimestamptz Scrape timestamp
raw_content Platform-agnostic normalized storage. Column Type Description iduuid Primary key source_iduuid FK → content_sources platformtext Origin platform platform_content_idtext Platform’s unique ID original_urltext Permalink shortcodetext Platform shortcode (Instagram) content_typetext ’image’, ‘video’, ‘carousel’, ‘reel’, ‘story’ titletext Title if available captiontext Full caption text descriptiontext Extended description hashtagstext[] Extracted hashtags mentionstext[] @mentions media_urltext Original media URL thumbnail_urltext Thumbnail URL cloudinary_urltext Backed up media cloudinary_public_idtext Cloudinary asset ID cloudinary_thumbnail_urltext Backed up thumbnail duration_secondsnumeric Video duration widthinteger Media width heightinteger Media height aspect_ratiotext ’1:1’, ‘4:5’, ‘9:16’, ‘16:9’ likes_countinteger Like count comments_countinteger Comment count shares_countinteger Share count views_countinteger View count saves_countinteger Save count engagement_ratenumeric Engagement / reach virality_scorenumeric Shares / views platform_metadatajsonb Platform-specific data statustext ’pending’, ‘analyzed’, ‘ready’, ‘archived’ published_attimestamptz Original publish date scraped_attimestamptz When scraped tenant_iduuid FK → tenants (multi-tenant isolation) created_attimestamptz Row created updated_attimestamptz Row updated text_embeddingvector(1536) OpenAI text-embedding-3-small for caption/description visual_embeddingvector(1536) Visual content embedding (Cloudinary AI/CLIP) combined_embeddingvector(1536) Weighted fusion (0.3 text + 0.7 visual for video) embedding_modeltext Model used for embeddings embedded_attimestamptz When embeddings were generated
content_analysis Comprehensive AI analysis extracted ONCE, cached forever. Column Type Description iduuid Primary key content_iduuid FK → raw_content content_hashtext Hash for deduplication scene_descriptiontext AI-generated scene description objects_detectedtext[] Objects in frame dominant_colorsjsonb [{color: “#fff”, percent: 40}] composition_typetext ’centered’, ‘rule_of_thirds’, ‘symmetrical’ lighting_styletext ’natural’, ‘studio’, ‘golden_hour’, ‘low_key’ setting_typetext ’indoor’, ‘outdoor’, ‘studio’ has_faceboolean Face detected face_countinteger Number of faces primary_emotiontext Dominant facial emotion has_text_overlayboolean Text on image text_in_imagetext OCR extracted text has_musicboolean Background music music_energytext ’calm’, ‘moderate’, ‘high_energy’ music_genretext Detected genre is_trending_soundboolean Platform trending audio sound_idtext Platform sound identifier has_voiceoverboolean Voiceover narration has_speechboolean Any speech detected transcripttext Full transcript speaking_pacetext ’slow’, ‘moderate’, ‘fast’ language_detectedtext ISO language code hook_typetext ’question’, ‘statement’, ‘visual’, ‘curiosity_gap’ hook_texttext First 3 seconds text hook_strengthinteger 0-100 hook effectiveness cta_presentboolean Call to action exists cta_typetext ’follow’, ‘comment’, ‘link’, ‘buy’ cta_texttext CTA text content_categorytext Primary category content_formattext ’tutorial’, ‘review’, ‘vlog’, ‘meme’, ‘news’ topicstext[] Topic tags entitiesjsonb Named entities tonetext ’educational’, ‘entertaining’, ‘inspirational’ reading_leveltext ’simple’, ‘moderate’, ‘advanced’ word_countinteger Caption word count sentimenttext ’positive’, ‘neutral’, ‘negative’ sentiment_scorenumeric -1.0 to 1.0 visual_embeddingvector(768) Image embedding text_embeddingvector(768) Text embedding combined_embeddingvector(768) Fused embedding extracted_factsjsonb Structured facts for prompt injection analyzed_attimestamptz Analysis timestamp analysis_versiontext Version identifier model_usedtext AI model used analysis_tokensinteger Tokens consumed analysis_costnumeric Cost in USD
Publishing destinations with requirements. Column Type Description iduuid Primary key nametext Unique identifier (‘tiktok’, ‘instagram_reels’) display_nametext Human-readable name supported_typestext[] [‘video’, ‘image’, ‘carousel’] min_duration_secondsinteger Minimum video length max_duration_secondsinteger Maximum video length aspect_ratiostext[] [‘9:16’, ‘1:1’, ‘4:5’] max_caption_lengthinteger Character limit max_hashtagsinteger Hashtag limit allows_linksboolean Links in caption max_file_size_mbinteger Upload limit supported_formatstext[] [‘mp4’, ‘mov’] has_apiboolean API available api_supports_schedulingboolean Can schedule posts api_supports_analyticsboolean Can pull analytics audience_typetext ’gen_z’, ‘millennial’, ‘professional’ content_velocitytext ’high’, ‘medium’, ‘low’ algorithm_favorstext[] [‘trending_audio’, ‘original_content’, ‘engagement’] peak_hoursjsonb Best posting times time_zonetext Default timezone avg_engagement_ratenumeric Platform average total_postsinteger Our posts on platform algorithm_notestext Platform-specific notes best_practicestext Guidelines is_activeboolean Currently publishing created_attimestamptz Row created updated_attimestamptz Row updated
What works WHERE - the core prediction engine. Column Type Description iduuid Primary key pattern_nametext Human-readable name pattern_typetext ’format’, ‘topic’, ‘hook’, ‘timing’, ‘audio’ pattern_descriptiontext What this pattern represents pattern_criteriajsonb Matching rules (see below) platform_iduuid FK → platforms sample_sizeinteger Data points avg_viewsinteger Average views for pattern avg_likesinteger Average likes avg_commentsinteger Average comments avg_sharesinteger Average shares avg_engagement_ratenumeric Average engagement avg_retention_ratenumeric Average retention avg_share_ratenumeric Average share rate lift_vs_platform_avgnumeric THE MAGIC NUMBER confidence_scorenumeric 0-1 based on sample_size best_daystext[] [‘Monday’, ‘Thursday’] best_hoursinteger[] [9, 12, 18] seasonal_trendsjsonb Seasonal variations last_calculated_attimestamptz Last recalculation created_attimestamptz Row created updated_attimestamptz Row updated
content_platform_scores AI prediction for each content + platform combination. Column Type Description iduuid Primary key content_iduuid FK → raw_content (or content_analysis) platform_iduuid FK → platforms fit_scoreinteger 0-100 overall fit fit_reasoningtext Why this score matched_patternsjsonb Patterns that matched with lifts total_pattern_liftnumeric Combined lift adaptations_neededjsonb Required changes adaptation_difficultytext ’none’, ‘easy’, ‘moderate’, ‘complex’ adaptation_cost_estimatenumeric Estimated cost predicted_views_lowinteger Conservative estimate predicted_views_midinteger Expected estimate predicted_views_highinteger Optimistic estimate predicted_engagement_ratenumeric Expected engagement predicted_virality_scorenumeric Expected virality recommended_post_timetimestamptz Best time to post recommended_daytext Best day recommended_actiontext Decision output scored_attimestamptz When scored scoring_model_versiontext Model version created_attimestamptz Row created updated_attimestamptz Row updated
content_templates Reusable generation patterns. Column Type Description iduuid Primary key nametext Template name output_typetext ’article’, ‘caption’, ‘script’, ‘email’ target_platform_iduuid FK → platforms system_prompttext System instructions user_prompt_templatetext Template with {{placeholders}} available_placeholdersjsonb Documented placeholders model_nametext Default model temperaturenumeric Default temperature max_tokensinteger Default max tokens target_word_countinteger Target length target_duration_secondsinteger Target duration required_sectionstext[] Must-have sections times_usedinteger Usage count avg_performance_scorenumeric Average performance is_activeboolean Currently active created_attimestamptz Row created updated_attimestamptz Row updated
generated_content AI-created content ready for distribution. Column Type Description iduuid Primary key source_content_iduuid FK → raw_content source_analysis_iduuid FK → content_analysis template_iduuid FK → content_templates voice_profile_iduuid FK → voice_profiles target_platform_iduuid FK → platforms pattern_idsuuid[] Matched patterns adaptations_appliedjsonb What was changed output_typetext ’article’, ‘video’, ‘caption’ titletext Generated title bodytext Full body text captiontext Social caption hashtagstext[] Generated hashtags ctatext Call to action meta_descriptiontext SEO description target_keywordtext SEO keyword scripttext Video script script_word_countinteger Word count estimated_duration_secondsnumeric Estimated length emotion_markersjsonb [{time: 0, emotion: “excited”}] scene_markersjsonb [{time: 0, scene: “intro”}] content_urltext Primary content URL thumbnail_urltext Thumbnail URL preview_urltext Preview URL video_urltext Video URL audio_urltext Audio URL llm_providertext LLM provider used llm_modeltext Model used llm_input_tokensinteger Input tokens llm_output_tokensinteger Output tokens llm_costnumeric LLM cost avatar_providertext Avatar provider avatar_video_idtext Avatar video ID avatar_costnumeric Avatar cost voice_providertext Voice provider voice_audio_idtext Voice audio ID voice_costnumeric Voice cost cloudinary_costnumeric Storage/transform cost total_generation_costnumeric Total cost statustext ’draft’, ‘generating’, ‘ready’, ‘approved’, ‘published’, ‘failed’ ai_quality_scoreinteger AI quality assessment human_approvedboolean Human approved approved_byuuid FK → users approved_attimestamptz Approval time error_messagetext Error if failed retry_countinteger Retry attempts created_attimestamptz Row created updated_attimestamptz Row updated experiment_iduuid FK → content_experiments (if part of A/B test) experiment_varianttext ’A’ (control), ‘B’, ‘C’, ‘D’ generation_cost_usdnumeric(10,6) Total cost to generate this content generation_breakdownjsonb Step-by-step cost breakdown models_usedtext[] All models used in generation total_tokensinteger Total tokens consumed latency_msinteger Total generation time generation_started_attimestamptz When generation began generation_completed_attimestamptz When generation completed
pattern_feedback Connect performance back to patterns for learning. Column Type Description iduuid Primary key content_platform_score_iduuid FK → content_platform_scores pattern_iduuid FK → platform_patterns distribution_iduuid FK → distributions predicted_viewsinteger What we predicted actual_viewsinteger What happened views_error_percentnumeric Prediction error predicted_engagement_ratenumeric Predicted engagement actual_engagement_ratenumeric Actual engagement engagement_error_percentnumeric Engagement error pattern_contributedboolean Pattern helped success expected_liftnumeric Expected lift actual_liftnumeric Actual lift processedboolean Batch processed processed_attimestamptz Processing time created_attimestamptz Row created
content_experiments A/B test configuration for content generation. Column Type Description iduuid Primary key nametext Experiment name hypothesistext What we’re testing experiment_typetext ’template’, ‘voice’, ‘format’, ‘timing’, ‘headline’, ‘cta’, ‘image_style’ control_template_iduuid FK → content_templates (control) control_voice_iduuid FK → voice_profiles (control) variantsjsonb Variant configs with traffic percentages target_platformsuuid[] Platforms to test on (NULL = all) target_verticalsuuid[] Verticals to test on (NULL = all) metric_primarytext Primary metric to optimize metric_secondarytext[] Secondary metrics to track confidence_thresholdnumeric Required confidence (0.80-0.99) minimum_sample_sizeinteger Min samples per variant statustext ’draft’, ‘running’, ‘paused’, ‘completed’, ‘cancelled’ winner_variant_idtext Winning variant (‘A’, ‘B’, etc.) winning_lift_percentnumeric Winner’s lift vs control winning_confidencenumeric Statistical confidence started_attimestamptz Test start time ended_attimestamptz Test end time auto_end_on_significanceboolean Auto-complete when significant max_duration_daysinteger Max test duration created_byuuid FK → auth.users created_attimestamptz Row created updated_attimestamptz Row updated
content_experiment_results Daily computed statistics per variant. Column Type Description iduuid Primary key experiment_iduuid FK → content_experiments variant_idtext ’A’, ‘B’, ‘C’, ‘D’ computed_attimestamptz When computed sample_sizeinteger Number of samples metric_meannumeric Mean value metric_mediannumeric Median value metric_stddevnumeric Standard deviation metric_ci_lowernumeric 95% CI lower bound metric_ci_uppernumeric 95% CI upper bound lift_percentnumeric Lift vs control p_valuenumeric Statistical p-value is_significantboolean Meets confidence threshold secondary_metricsjsonb Other tracked metrics
AI Visibility (10 tables)
aiv_bot_domains Domains registered for AI bot tracking. Column Type Description iduuid Primary key tenant_iduuid FK → tenants (required) domaintext Domain name (e.g., ‘example.com’) script_tokentext Unique token for tracking script is_activeboolean Currently tracking verification_methodtext ’dns’, ‘meta_tag’, ‘file’ verified_attimestamptz When domain was verified settingsjsonb Domain-specific settings created_attimestamptz Row created updated_attimestamptz Row updated
aiv_bot_events Individual bot crawl events detected on your site. Column Type Description iduuid Primary key domain_iduuid FK → aiv_bot_domains tenant_iduuid FK → tenants bot_nametext Bot identifier (‘GPTBot’, ‘ClaudeBot’) bot_companytext Company (‘OpenAI’, ‘Anthropic’, ‘Google’) is_verified_botboolean Verified via reverse DNS page_urltext Full URL crawled page_pathtext Path portion of URL page_titletext Page title if available content_lengthinteger Response size in bytes content_hashtext Hash for deduplication user_agenttext Full user agent string ip_addressinet Bot IP address country_codetext ISO country code citytext City from IP geolocation crawled_attimestamptz When crawl occurred metadatajsonb Additional event data created_attimestamptz Row created
aiv_bot_geo_scores GEO (Generative Engine Optimization) scores per page. Column Type Description iduuid Primary key domain_iduuid FK → aiv_bot_domains tenant_iduuid FK → tenants page_urltext Full URL analyzed page_pathtext Path portion of URL overall_scoreinteger 0-100 overall GEO score schema_scoreinteger Structured data quality semantic_scoreinteger Content semantic clarity readability_scoreinteger Reading ease score entity_scoreinteger Named entity recognition freshness_scoreinteger Content freshness schema_typestext[] Schema.org types found issuesjsonb Detected issues recommendationsjsonb Improvement suggestions content_hashtext Hash for change detection analyzed_attimestamptz When analysis ran created_attimestamptz Row created updated_attimestamptz Row updated
aiv_bot_alerts Alert configuration for bot activity thresholds. Column Type Description iduuid Primary key domain_iduuid FK → aiv_bot_domains tenant_iduuid FK → tenants alert_typetext ’bot_activity’, ‘geo_score_drop’, etc. channeltext ’email’, ‘slack’, ‘webhook’ configjsonb Channel-specific config thresholdjsonb Trigger thresholds enabledboolean Alert is active trigger_countinteger Times triggered last_triggered_attimestamptz Last trigger time created_attimestamptz Row created updated_attimestamptz Row updated
aiv_bot_google_aio Google AI Overview (SGE) appearances for your content. Column Type Description iduuid Primary key domain_iduuid FK → aiv_bot_domains tenant_iduuid FK → tenants querytext Search query that triggered AIO query_hashtext Hash for deduplication positioninteger Position in AIO sources (1 = first) cited_urltext Your URL that was cited cited_pathtext Path portion of cited URL snippettext Text shown in AIO search_locationtext Geographic location of search detected_attimestamptz When detected metadatajsonb Additional data created_attimestamptz Row created
AI platforms we track for citations. Column Type Description iduuid Primary key nametext Platform key (‘chatgpt’, ‘perplexity’, etc.) display_nametext Human-readable name detection_methodtext ’api’, ‘scrape’, ‘extension’, ‘referrer’, ‘manual’ api_availableboolean Has API access api_endpointtext API URL if available scrape_allowedboolean Can scrape results icon_urltext Platform icon color_hextext Brand color market_share_percentnumeric Estimated market share is_activeboolean Currently tracking created_attimestamptz Row created updated_attimestamptz Row updated
aiv_cite_events Individual citation events when AI references your content. Column Type Description iduuid Primary key domain_iduuid FK → aiv_bot_domains (for unified tracking) tenant_iduuid Multi-tenant isolation vertical_iduuid Vertical for filtering content_iduuid FK → generated_content distribution_iduuid FK → distributions publisher_post_iduuid FK → publisher_posts cited_urltext URL that was cited (required) cited_domaintext Domain for grouping platform_iduuid FK → aiv_cite_platforms platform_nametext Denormalized platform name (required) query_texttext User’s query (if known) query_categorytext ’informational’, ‘navigational’, ‘transactional’, ‘commercial’ query_intenttext ’how_to’, ‘what_is’, ‘comparison’, ‘recommendation’, ‘news’ citation_typetext ’source_link’, ‘inline_quote’, ‘reference’, ‘knowledge_panel’, ‘featured’ citation_positioninteger 1 = first source (lower is better) citation_snippettext Text shown to user total_sources_showninteger Total sources in response detected_attimestamptz When detected detection_methodtext Detection method (required): ‘browser_extension’, ‘api_monitor’, ‘manual_entry’ detection_sourcetext Tool/user that reported confidence_scorenumeric 0-1 confidence screenshot_urltext Evidence screenshot raw_response_jsonjsonb Full AI response response_hashtext Hash for deduplication template_iduuid FK → content_templates (attribution) voice_profile_iduuid FK → voice_profiles (attribution) experiment_iduuid FK → content_experiments experiment_varianttext A/B variant if applicable created_attimestamptz Row created
aiv_cite_daily_stats Pre-aggregated statistics for dashboard performance. Column Type Description iduuid Primary key datedate Stats date tenant_iduuid Optional tenant filter vertical_iduuid Optional vertical filter platform_nametext Optional platform filter template_iduuid Optional template filter total_citationsinteger Total citations unique_urls_citedinteger Unique URLs cited unique_queriesinteger Unique queries avg_citation_positionnumeric Average position position_1_countinteger Times ranked #1 position_top3_countinteger Times in top 3 position_top5_countinteger Times in top 5 source_link_countinteger Source link citations inline_quote_countinteger Inline quote citations featured_countinteger Featured citations citations_prev_periodinteger Previous period count citations_change_percentnumeric Period-over-period change computed_attimestamptz When computed
aiv_cite_target_queries Queries you want to rank for (like SEO keywords but for AEO). Column Type Description iduuid Primary key query_texttext The query to track query_categorytext Query type tenant_iduuid Multi-tenant isolation vertical_iduuid FK → publisher_verticals target_platformtext ’all’ or specific platform prioritytext ’high’, ‘medium’, ‘low’ is_activeboolean Currently monitoring current_positioninteger Last known position current_platform_positionsjsonb Per-platform positions best_positioninteger Best ever achieved best_position_attimestamptz When best was achieved check_frequencytext ’hourly’, ‘daily’, ‘weekly’ last_checked_attimestamptz Last check time next_check_attimestamptz Next scheduled check created_byuuid FK → auth.users created_attimestamptz Row created updated_attimestamptz Row updated
aiv_cite_query_rankings Time series of your ranking position for target queries. Column Type Description iduuid Primary key target_query_iduuid FK → aiv_cite_target_queries platform_nametext Platform checked positioninteger Your position (NULL = not found) our_urltext Which URL ranked total_sourcesinteger Total sources shown checked_attimestamptz When checked check_methodtext ’api’, ‘scrape’, ‘manual’ response_snippettext Raw response text all_sourcesjsonb All sources in response
Audience Intelligence (4 tables)
audience_profiles Cross-platform user identity (Amazon/Netflix-style). Column Type Description iduuid Primary key emailtext Email address phonetext Phone number user_iduuid FK → users (if registered) platform_identitiesjsonb {instagram: “handle”, tiktok: “id”} first_nametext First name last_nametext Last name avatar_urltext Profile image company_nametext Company company_domaintext Company domain company_sizetext ’solo’, ‘smb’, ‘mid-market’, ‘enterprise’ company_industrytext Industry company_revenue_rangetext Revenue bracket job_titletext Job title job_leveltext ’ic’, ‘manager’, ‘director’, ‘vp’, ‘c-level’ job_functiontext Function area geo_citytext City geo_statetext State/Province geo_countrytext Country timezonetext Timezone device_typetext ’mobile’, ‘desktop’, ‘tablet’ device_value_scoreinteger Apple = high WTP browsertext Browser first_seen_attimestamptz First interaction last_active_attimestamptz Last interaction total_sessionsinteger Session count total_page_viewsinteger Page views total_content_viewsinteger Content views avg_session_duration_secondsnumeric Avg session length peak_activity_hoursinteger[] Most active hours peak_activity_daystext[] Most active days favorite_topicstext[] Preferred topics favorite_content_typestext[] Preferred formats favorite_formatstext[] Preferred formats preferred_platformstext[] Preferred platforms wtp_scoreinteger Willingness to pay (0-100) wtp_breakdownjsonb Score breakdown by factor lead_scoreinteger Lead quality (0-100) lead_tiertext ’cold’, ‘warm’, ‘hot’, ‘qualified’ engagement_scoreinteger Engagement level (0-100) churn_risk_scoreinteger Churn risk (0-100) lifecycle_stagetext ’anonymous’, ‘known’, ‘subscriber’, ‘lead’, ‘customer’, ‘churned’ became_subscriber_attimestamptz Subscription date became_lead_attimestamptz Lead date became_customer_attimestamptz Customer date churned_attimestamptz Churn date micro_segment_iduuid FK → micro_segments segment_assigned_attimestamptz Assignment date predicted_ltvnumeric Predicted lifetime value conversion_probabilitynumeric Conversion likelihood next_action_predictiontext Predicted next action price_tiertext ’budget’, ‘standard’, ‘premium’ recommended_pricenumeric Personalized price personalization_enabledboolean Personalization active created_attimestamptz Row created updated_attimestamptz Row updated
audience_events Event sourcing for full attribution. Column Type Description iduuid Primary key audience_iduuid FK → audience_profiles event_typetext ’page_view’, ‘content_view’, ‘click’, ‘form_submit’, ‘purchase’ event_categorytext Category event_actiontext Action event_labeltext Label session_idtext Session identifier page_urltext Page URL page_titletext Page title referrer_urltext Referrer referrer_domaintext Referrer domain utm_sourcetext UTM source utm_mediumtext UTM medium utm_campaigntext UTM campaign utm_termtext UTM term utm_contenttext UTM content content_iduuid FK → generated_content distribution_iduuid FK → distributions platformtext Source platform vertical_iduuid FK → publisher_verticals post_iduuid FK → publisher_posts video_percent_watchedinteger Video progress video_seconds_watchednumeric Watch time event_valuenumeric Event value currencytext Currency code device_typetext Device browsertext Browser ostext Operating system ip_addressinet IP address geo_citytext City geo_countrytext Country lead_score_deltainteger Score change wtp_score_deltainteger WTP change metadatajsonb Additional data created_attimestamptz Event time
micro_segments Netflix-style behavioral clusters. Column Type Description iduuid Primary key segment_idtext Unique identifier segment_nametext Human name segment_descriptiontext Description characteristicsjsonb Defining characteristics defining_traitstext[] Key traits recommended_content_typestext[] Best content recommended_topicstext[] Best topics recommended_offertext Best offer type recommended_messagingtext Best messaging avg_wtp_scoreinteger Average WTP price_tiertext Price tier price_multipliernumeric 0.6x - 1.4x base price user_countinteger Users in segment avg_conversion_ratenumeric Conversion rate avg_ltvnumeric Average LTV avg_engagement_scorenumeric Engagement cluster_algorithmtext Algorithm used feature_weightsjsonb Feature importance last_recalculated_attimestamptz Last update created_attimestamptz Row created updated_attimestamptz Row updated
wtp_scoring_rules Configuration for WTP calculation. Column Type Description iduuid Primary key rule_nametext Rule name rule_categorytext ’device’, ‘geo’, ‘behavior’, ‘firmographic’ criteriajsonb Matching criteria score_deltainteger Points to add/subtract priorityinteger Rule priority rationaletext Why this rule is_activeboolean Currently active created_attimestamptz Row created
voice_profiles Brand voice configuration. Column Type Description iduuid Primary key nametext Profile name brand_nametext Brand this voice represents brand_descriptiontext Brand context target_audiencetext Who we’re talking to tonetext[] [‘friendly’, ‘professional’, ‘witty’] styletext[] [‘conversational’, ‘authoritative’] avoidtext[] [‘jargon’, ‘emojis’, ‘exclamation_marks’] max_sentence_lengthinteger Word limit per sentence preferred_word_count_mininteger Min output length preferred_word_count_maxinteger Max output length use_contractionsboolean Use contractions example_goodtext[] Good examples example_badtext[] Bad examples avatar_presentertext Default avatar avatar_backgroundtext Default background avatar_outfittext Default outfit elevenlabs_voice_idtext ElevenLabs voice voice_stabilitynumeric Voice setting voice_similaritynumeric Voice setting voice_stylenumeric Voice setting d_id_presenter_idtext D-ID presenter d_id_driver_idtext D-ID driver heygen_avatar_idtext HeyGen avatar vertical_iduuid FK → publisher_verticals is_defaultboolean Default for vertical created_attimestamptz Row created updated_attimestamptz Row updated
distributions Track every publication. Column Type Description iduuid Primary key content_iduuid FK → generated_content platform_iduuid FK → platforms platform_post_idtext Platform’s post ID published_urltext Live URL caption_usedtext Actual caption posted hashtags_usedtext[] Actual hashtags scheduled_attimestamptz Scheduled time published_attimestamptz Actual publish time varianttext A/B test variant (‘A’, ‘B’) test_iduuid A/B test identifier test_hypothesistext What we’re testing predicted_viewsinteger Snapshot of prediction predicted_engagement_ratenumeric Snapshot of prediction statustext ’scheduled’, ‘published’, ‘failed’, ‘deleted’ error_messagetext Error if failed buffer_post_idtext Buffer integration buffer_schedule_idtext Buffer schedule created_attimestamptz Row created updated_attimestamptz Row updated
distribution_metrics Time-series performance tracking. Column Type Description iduuid Primary key distribution_iduuid FK → distributions viewsinteger Views at snapshot likesinteger Likes at snapshot commentsinteger Comments at snapshot sharesinteger Shares at snapshot savesinteger Saves at snapshot clicksinteger Clicks at snapshot engagement_ratenumeric(5,4) Engagement rate virality_scorenumeric(5,2) Virality score share_ratenumeric(5,4) Share rate retention_ratenumeric(5,4) Video retention retention_curvejsonb [{second: 1, percent: 100}] avg_watch_time_secondsnumeric(6,2) Average watch time prediction_error_percentnumeric(5,2) How wrong we were attributed_clicksinteger Revenue clicks attributed_revenuenumeric(12,2) Revenue attributed hours_since_publishinteger Hours since publish snapshot_typetext ’scheduled’, ‘manual’, ‘final’ created_attimestamptz Snapshot time
ad_components Meta-style component assembly for A/B testing. Column Type Description iduuid Primary key component_typetext ’headline’, ‘cta’, ‘image’, ‘video’, ‘body’ target_segment_iduuid FK → micro_segments contenttext Component content image_urltext Image if applicable impressionsinteger Impressions clicksinteger Clicks conversionsinteger Conversions ctrnumeric Click-through rate variant_nametext A/B variant name is_activeboolean Currently active created_attimestamptz Row created updated_attimestamptz Row updated
publisher_verticals Column Type Description iduuid Primary key nametext Vertical name slugtext URL slug descriptiontext Description subdomaintext Subdomain (tech, golf, travel) primary_colortext Brand color secondary_colortext Secondary color logo_urltext Logo is_activeboolean Active created_attimestamptz Row created updated_attimestamptz Row updated
publisher_post_analytics See database or Supabase dashboard for complete column definitions. Full column definitions available in Supabase dashboard.
publisher_post_tags See database or Supabase dashboard for complete column definitions. Full column definitions available in Supabase dashboard.
publisher_post_viator_products See database or Supabase dashboard for complete column definitions. Full column definitions available in Supabase dashboard.
publisher_posts Column Type Description iduuid Primary key vertical_iduuid FK → publisher_verticals source_content_iduuid FK → raw_content generated_content_iduuid FK → generated_content titletext Post title slugtext URL slug excerpttext Preview text bodytext Full content featured_image_urltext Featured image statustext ’draft’, ‘scheduled’, ‘published’ published_attimestamptz Publish date meta_titletext SEO title meta_descriptiontext SEO description target_keywordtext SEO keyword author_iduuid FK → users created_attimestamptz Row created updated_attimestamptz Row updated
Platform (UCI) (4 tables)
agency_clients See database or Supabase dashboard for complete column definitions. Full column definitions available in Supabase dashboard.
agency_projects See database or Supabase dashboard for complete column definitions. Full column definitions available in Supabase dashboard.
agency_deliverables See database or Supabase dashboard for complete column definitions. Full column definitions available in Supabase dashboard.
agency_retainers See database or Supabase dashboard for complete column definitions. Full column definitions available in Supabase dashboard.
shopify_customers See database or Supabase dashboard for complete column definitions. Full column definitions available in Supabase dashboard.
shopify_products See database or Supabase dashboard for complete column definitions. Full column definitions available in Supabase dashboard.
shopify_orders See database or Supabase dashboard for complete column definitions. Full column definitions available in Supabase dashboard.
service_order_projects See database or Supabase dashboard for complete column definitions. Full column definitions available in Supabase dashboard.
This documentation is auto-generated from agents/schema/SCHEMA.md.
Run pnpm docs:sync-schema to update.
