Database Schema

190 migrations | 157 tables | 447 indexes

Migrations

#	File	Tables
001	`001_create_core_tables.sql`	`user_workspace_access`, `client_registry`, `client_settings`, `client_pricing`, `client_costs`, `monthly_revenue_snapshots`, `client_zipcodes`
002	`002_create_contact_pipeline.sql`	`raw_contacts`, `verified_contacts`, `weekly_batches`, `upload_audit_log`, `debounce_usage`
003	`003_create_scraping_jobs.sql`	`scraping_jobs`, `scraping_job_logs`, `agent_runs`, `agent_errors`, `site_credentials`
004	`004_create_lead_management.sql`	`client_leads`
005	`005_create_email_infrastructure.sql`	`email_accounts`, `email_account_metadata`, `email_account_polling`
006	`006_create_user_profiles.sql`	`user_profiles`
006	`006_optimize_raw_contacts.sql`	(alter/index only)
007	`007_fix_raw_contacts_schema.sql`	(alter/index only)
008	`008_create_filter_rules.sql`	`client_filter_rules`
009	`009_add_missing_verified_fields.sql`	(alter/index only)
010	`010_create_default_filter_rules.sql`	(alter/index only)
011	`011_add_email_bison_workspace_mapping.sql`	(alter/index only)
012	`012_add_bison_campaign_id.sql`	(alter/index only)
013	`013_create_vault_functions.sql`	(alter/index only)
014	`014_update_site_credentials_schema.sql`	(alter/index only)
015	`015_create_credential_audit_log.sql`	`credential_audit_log`
016	`016_create_scraping_job_batches.sql`	`scraping_job_batches`
017	`017_create_campaign_metrics.sql`	`campaign_metrics`
018	`018_add_duplicate_detection_indexes.sql`	(alter/index only)
018	`018_create_debounce_bulk_jobs.sql`	`debounce_bulk_jobs`
019	`019_add_credential_to_jobs.sql`	(alter/index only)
020	`020_create_task_progress_tables.sql`	`filtering_tasks`, `verification_tasks`
021	`021_add_workspace_contact_fields.sql`	(alter/index only)
022	`022_tighten_rls_policies.sql`	(alter/index only)
023	`023_create_pipeline_runs.sql`	`pipeline_runs`
024	`024_add_month_filter_to_stats.sql`	(alter/index only)
025	`025_add_upload_status_to_verified_contacts.sql`	(alter/index only)
026	`026_update_pipeline_stats_with_upload_status.sql`	(alter/index only)
027	`027_email_bison_account_sync.sql`	(alter/index only)
028	`028_campaign_metrics_progress.sql`	(alter/index only)
029	`029_client_leads_bison_sync.sql`	(alter/index only)
030	`030_client_targets.sql`	`client_targets`
031	`031_crm_webhook_integration.sql`	`crm_delivery_log`, `slack_notifications_sent`, `lead_replies`, `webhook_delivery_log`, `webhook_health`
032	`032_master_contacts_integration.sql`	`home_value_codes`
033	`033_campaign_crm_routing.sql`	`campaign_crm_routing`
034	`034_add_csv_processing_status.sql`	(alter/index only)
035	`035_add_month_leading_indexes.sql`	(alter/index only)
035	`035_create_tasks_table.sql`	`tasks`
035	`035_reply_response_tracking.sql`	(alter/index only)
036	`036_auto_reply_config.sql`	`auto_reply_config`, `auto_reply_log`
037	`037_thomas_migration_compat.sql`	`reply_templates`, `sent_replies`, `sma_policies`, `client_metrics`, `monthly_daily_revenue_history`
038	`038_add_bounce_alert_throttle.sql`	(alter/index only)
038	`038_daily_revenue_unique_index.sql`	(alter/index only)
039	`039_extra_data_and_bison_field_mapping.sql`	(alter/index only)
039	`039_unique_workspace_lead_email.sql`	(alter/index only)
040	`040_add_source_type_to_contact_stats.sql`	(alter/index only)
041	`041_backfill_source_type_scraped.sql`	(alter/index only)
041	`041_bison_source_of_truth.sql`	(alter/index only)
042	`042_pipeline_stats_all_months.sql`	(alter/index only)
042	`042_workspace_stats_cache.sql`	`workspace_stats_cache`
043	`043_analyze_and_index_for_performance.sql`	(alter/index only)
044	`044_pipeline_stats_cache_table.sql`	`pipeline_stats_cache`
045	`045_workspace_daily_stats.sql`	`workspace_daily_stats`
046	`046_backfill_client_type.sql`	(alter/index only)
047	`047_mx_checked_at.sql`	(alter/index only)
048	`048_infrastructure_orders.sql`	`reseller_configs`, `infrastructure_orders`, `infrastructure_order_events`
049	`049_normalize_pipeline_stages.sql`	(alter/index only)
050	`050_sma_policies_rls.sql`	(alter/index only)
051	`051_workspace_access_rls_helper.sql`	(alter/index only)
052	`052_onboarding_drafts.sql`	`onboarding_drafts`
053	`053_agent_conversations.sql`	`agent_threads`, `agent_messages`
054	`054_infrastructure_action_items_rpc.sql`	(alter/index only)
055	`055_fix_warmup_disabled_count.sql`	(alter/index only)
056	`056_fix_action_items_jsonb_cast.sql`	(alter/index only)
057	`057_fix_reseller_warmup_periods.sql`	(alter/index only)
058	`058_domain_tracking.sql`	(alter/index only)
059	`059_task_events.sql`	`task_events`
060	`060_hall_of_fame.sql`	`hall_of_fame_nominations`, `hall_of_fame_votes`
060	`060_tasks_v2.sql`	`task_activity`
062	`062_increment_daily_interested.sql`	(alter/index only)
063	`063_normalize_contact_names_trigger.sql`	(alter/index only)
064	`064_reply_ai_upgrade.sql`	`ai_generation_log`, `prompt_eval_results`
064	`064_week_bucket_not_null.sql`	(alter/index only)
065	`065_crm_destinations.sql`	`crm_destinations`
065	`065_reply_templates_unique_workspace.sql`	(alter/index only)
066	`066_crm_routing_flag.sql`	(alter/index only)
067	`067_burnt_domains_root_domain_grouping.sql`	(alter/index only)
068	`068_dedup_aggregation_functions.sql`	(alter/index only)
069	`069_client_registry_excluded_zips.sql`	(alter/index only)
070	`070_zip_inclusion_mode.sql`	(alter/index only)
071	`071_raw_contacts_bypass_routing.sql`	(alter/index only)
072	`072_resources.sql`	`resources`, `resource_requests`
073	`073_revenue_adjustments.sql`	`revenue_refunds`, `revenue_referrals`
073	`073_task_timeline.sql`	`task_timeline`
074	`074_client_registry_slack_webhook_commercial.sql`	(alter/index only)
074	`074_task_timeline_more_events.sql`	(alter/index only)
075	`075_user_profiles_slack_user_id.sql`	(alter/index only)
076	`076_slack_event_dedup.sql`	`slack_event_dedup`
076	`076_verified_contacts_provider.sql`	(alter/index only)
077	`077_account_counter_safety.sql`	(alter/index only)
078	`078_dedup_campaign_metrics.sql`	(alter/index only)
079	`079_audit_phase_a_config_tables.sql`	`audit_verticals`, `audit_recommendation_rules`, `audit_sync_state`
080	`080_audit_phase_a_rank_tables.sql`	`audit_rank_by_subject`, `audit_rank_by_pitch`, `audit_rank_by_parent_template`, `audit_rank_by_master_template`, `audit_rank_by_step`, `audit_rank_by_sender_combo`, `audit_rank_by_sender_domain`, `audit_rank_by_sender_local_pattern`
081	`081_audit_phase_a_action_tables.sql`	`audit_recommendations`, `audit_deployments`, `audit_outcomes`
081	`081_xpressdocs_bulk_backfill.sql`	`xpressdocs_backfill_jobs`
082	`082_audit_phase_a_replies_origins.sql`	`audit_replies`, `audit_origins`
083	`083_audit_phase_a_step_stats.sql`	`audit_step_stats`
083	`083_lead_replies_sender_email.sql`	(alter/index only)
084	`084_audit_phase_a_senders.sql`	`audit_senders`
084	`084_email_account_daily_stats.sql`	`email_account_daily_stats`
085	`085_audit_phase_a_origin_outcome.sql`	(alter/index only)
086	`086_audit_phase_a_briefings.sql`	`audit_briefings`
087	`087_workspace_volume_tracking.sql`	`workspace_volume_tracking`
088	`088_drop_volume_tracking_check.sql`	(alter/index only)
089	`089_audit_client_category.sql`	(alter/index only)
090	`090_billing_audit.sql`	`billing_subscriptions`, `billing_receipts`, `billing_disputes`
091	`091_billing_reconciliation_columns.sql`	(alter/index only)
092	`092_vendor_id_dedup_per_month.sql`	(alter/index only)
093	`093_client_registry_slack_webhook_url_commercial.sql`	(alter/index only)
094	`094_audit_step_stats_current.sql`	(alter/index only)
095	`095_audit_rank_by_pitch_sample_subject.sql`	(alter/index only)
095	`095_qualifying_question_conversion.sql`	(alter/index only)
096	`096_audit_commercial_recommendation_rules.sql`	(alter/index only)
097	`097_audit_stage_runs.sql`	`audit_stage_runs`
098	`098_client_registry_exclude_from_dashboard.sql`	(alter/index only)
099	`099_scraped_renewal_backfill_rpc.sql`	(alter/index only)
100	`100_scraped_renewal_rpc_renewal_formatted.sql`	(alter/index only)
102	`102_webhook_ingress_status.sql`	(alter/index only)
103	`103_billing_first_interested_rpc.sql`	(alter/index only)
109	`109_interested_format_promotions.sql`	`interested_format_promotions`
110	`110_email_accounts_mx_checked_at.sql`	(alter/index only)
111	`111_dob_backfill_rpc.sql`	(alter/index only)
112	`112_infrastructure_summary_rpc.sql`	(alter/index only)
113	`113_audit_workspace_pitches.sql`	`audit_workspace_pitches`
113	`113_raw_contacts_source_type_datamoon.sql`	(alter/index only)
114	`114_audit_audience_stats.sql`	`audit_audience_stats`
115	`115_carrier_list_scrub.sql`	`carrier_scrub_runs`, `carrier_affiliations`
116	`116_carrier_scrub_slack_channel.sql`	(alter/index only)
117	`117_spam_block_events.sql`	`spam_block_events`
118	`118_email_conversation_operating_intake_setup.sql`	`operating_source_accounts`, `operating_source_events`, `operating_threads`, `operating_thread_messages`, `operating_source_artifacts`, `operating_intake_runs`
118	`118_slack_operating_intake_capture.sql`	`slack_conversations`, `slack_users`, `slack_events_raw`, `slack_messages`, `slack_ingest_runs`
119	`119_client_operating_timeline.sql`	`operating_timeline_events`, `operating_timeline_event_links`, `operating_timeline_export_runs`
119	`119_slack_operating_intake_phase_c_autonomy.sql`	`slack_channel_policies`, `slack_channel_coverage_events`, `operating_intake_items`, `brain_writeback_queue`
120	`120_client_operating_timeline_view_security.sql`	(alter/index only)
120	`120_infra_order_pipeline_v2.sql`	`client_onboardings`, `onboarding_steps`
121	`121_client_onboardings_form_fields.sql`	(alter/index only)
121	`121_email_verification_second_pass_queue.sql`	`email_verification_second_pass_queue`
121	`121_operating_indexing_engine.sql`	`operating_entities`, `operating_entity_matches`, `operating_intake_classifications`, `operating_intake_review_items`, `operating_intake_writebacks`, `operating_digest_runs`
122	`122_infra_orders_workspace_ingest.sql`	(alter/index only)
122	`122_operating_knowledge_promotion.sql`	`operating_entity_aliases`, `operating_process_taxonomy`, `operating_timeline_events`, `operating_timeline_event_links`, `operating_promotion_runs`, `operating_brain_patch_candidates`
122	`122_slack_operating_intake_phase_c_fk_indexes.sql`	(alter/index only)
123	`123_client_onboardings_expected_domains.sql`	(alter/index only)
123	`123_operating_timeline_event_link_replacement_rpc.sql`	(alter/index only)
124	`124_operating_knowledge_promotion_index_cleanup.sql`	(alter/index only)
125	`125_operating_timeline_source_first_shape.sql`	(alter/index only)
126	`126_brain_patch_path_resolution.sql`	(alter/index only)
127	`127_message_context_wiring.sql`	(alter/index only)
128	`128_operating_backfill_controller.sql`	`operating_backfill_jobs`, `operating_backfill_items`
129	`129_purge_cross_workspace_stat_pollution.sql`	`stat_pollution_backup_2026_07`
130	`130_purge_volume_tracking_gaudio_copies_0619.sql`	(alter/index only)
131	`131_daily_audit_engine.sql`	`audit_rules`, `audit_findings`
132	`132_client_status_onboarding.sql`	(alter/index only)
133	`133_operating_timeline_summary_repair_backup.sql`	`operating_timeline_summary_repair_backup`
134	`134_auto_forward_dropped_producers.sql`	`auto_forward_config`, `auto_forward_log`
135	`135_expected_domains_per_provider.sql`	(alter/index only)
136	`136_reseller_tags_per_provider.sql`	(alter/index only)
137	`137_rename_limits_set_to_warmup_running.sql`	(alter/index only)
138	`138_mailbox_counts_per_provider.sql`	(alter/index only)
139	`139_chicago_bucket_time_series.sql`	(alter/index only)
140	`140_infrastructure_orders_bison_campaign_id.sql`	(alter/index only)
141	`141_orders_first_spine.sql`	`infra_reconciliation_runs`
143	`143_workspace_diagnostics.sql`	`diagnostics_runs`, `workspace_diagnostics_findings`, `bison_campaign_snapshots`
144	`144_purge_schrauf_phantom_campaign_stats.sql`	(alter/index only)
145	`145_channel_client_attribution.sql`	(alter/index only)
145	`145_purge_audit_phantom_rows_polluted_workspaces.sql`	(alter/index only)
146	`146_email_accounts_order_link.sql`	(alter/index only)
147	`147_internal_panels_reseller_configs.sql`	(alter/index only)
148	`148_client_registry_google_chat_webhook.sql`	(alter/index only)
148	`148_daily_sending_target_audit.sql`	(alter/index only)
149	`149_diagnostics_runs_skipped.sql`	(alter/index only)
150	`150_audience_rebuild_per_workspace.sql`	(alter/index only)
151	`151_client_announcements.sql`	`client_announcements`, `announcement_deliveries`, `announcement_reads`, `announcement_email_optouts`, `announcement_extra_recipients`
151	`151_order_send_activity.sql`	`order_send_activity`
152	`152_announcements_webhook_column.sql`	(alter/index only)
152	`152_order_send_activity_limit_tracking.sql`	(alter/index only)
153	`153_drop_announcement_extra_recipients.sql`	(alter/index only)
153	`153_infra_workspace_daily.sql`	`infra_workspace_daily`
153	`153_sensor_repair.sql`	(alter/index only)
154	`154_announcement_event_engine.sql`	`announcement_event_policies`
154	`154_disconnect_state.sql`	`sender_disconnect_state`
155	`155_announcement_pipeline_enrollment.sql`	`announcement_pipeline_enrollment`
155	`155_ramp_executor_rule.sql`	(alter/index only)
156	`156_account_deletion_queue.sql`	`account_deletion_queue`
157	`157_reseller_values_confirmed.sql`	(alter/index only)
158	`158_burnt_cohorts.sql`	`burnt_cohorts`
159	`159_send_failure_spike.sql`	(alter/index only)
160	`160_summary_freshness.sql`	(alter/index only)
161	`161_burnt_domains_warmup_score_gate.sql`	(alter/index only)
161	`161_spam_block_events_dsn_body.sql`	(alter/index only)
162	`162_resources_workspace_targeting.sql`	(alter/index only)

Tables

`user_workspace_access`

Multi-tenant user access control - maps users to workspaces with roles

Defined in: 001_create_core_tables.sql

Column	Type	Constraints
`id`	`UUID`	PRIMARY KEY DEFAULT gen_random_uuid()
`user_id`	`UUID`	NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE
`workspace_name`	`TEXT`	NOT NULL
`role`	`TEXT`	NOT NULL CHECK (role IN ('admin', 'client', 'viewer'))
`created_at`	`TIMESTAMPTZ`	DEFAULT NOW()
`updated_at`	`TIMESTAMPTZ`	DEFAULT NOW()

`client_registry`

Master client registry - single source of truth for all client data

Defined in: 001_create_core_tables.sql

Column	Type	Constraints
`workspace_id`	`SERIAL`	PRIMARY KEY
`workspace_name`	`TEXT`	NOT NULL UNIQUE
`display_name`	`TEXT`
`is_active`	`BOOLEAN`	DEFAULT true
`billing_type`	`TEXT`	CHECK (billing_type IN ('per_lead', 'retainer'))
`price_per_lead`	`DECIMAL(10,2)`	DEFAULT 0.00
`retainer_amount`	`DECIMAL(10,2)`	DEFAULT 0.00
`monthly_kpi_target`	`INTEGER`	DEFAULT 0
`monthly_contact_target`	`INTEGER`	DEFAULT 0
`contact_tier`	`TEXT`	CHECK (contact_tier IN ('100_leads', '200_leads', 'custom'))
`daily_sending_target`	`INTEGER`	DEFAULT 0
`bison_workspace_id`	`INTEGER`
`bison_api_key`	`TEXT`
`bison_instance`	`TEXT`
`agency_color`	`TEXT`
`airtable_record_id`	`TEXT`
`client_type`	`TEXT`	DEFAULT 'home_insurance'
`slack_webhook_url`	`TEXT`
`notes`	`TEXT`
`created_at`	`TIMESTAMPTZ`	DEFAULT NOW()
`updated_at`	`TIMESTAMPTZ`	DEFAULT NOW()

`client_settings`

Defined in: 001_create_core_tables.sql

Column	Type	Constraints
`id`	`SERIAL`	PRIMARY KEY
`workspace_name`	`TEXT`	UNIQUE REFERENCES public.client_registry(workspace_name) ON DELETE CASCADE
`settings`	`JSONB`	DEFAULT '{}'::jsonb
`created_at`	`TIMESTAMPTZ`	DEFAULT NOW()
`updated_at`	`TIMESTAMPTZ`	DEFAULT NOW()

`client_pricing`

Defined in: 001_create_core_tables.sql

Column	Type	Constraints
`id`	`SERIAL`	PRIMARY KEY
`workspace_name`	`TEXT`	UNIQUE REFERENCES public.client_registry(workspace_name) ON DELETE CASCADE
`billing_type`	`TEXT`	CHECK (billing_type IN ('per_lead', 'retainer'))
`price_per_lead`	`DECIMAL(10,2)`	DEFAULT 0.00
`retainer_amount`	`DECIMAL(10,2)`	DEFAULT 0.00
`effective_date`	`DATE`	DEFAULT CURRENT_DATE
`created_at`	`TIMESTAMPTZ`	DEFAULT NOW()

`client_costs`

Defined in: 001_create_core_tables.sql

Column	Type	Constraints
`id`	`SERIAL`	PRIMARY KEY
`workspace_name`	`TEXT`	REFERENCES public.client_registry(workspace_name) ON DELETE CASCADE
`month`	`TEXT`	NOT NULL
`email_account_costs`	`DECIMAL(10,2)`	DEFAULT 0.00
`labor_costs`	`DECIMAL(10,2)`	DEFAULT 0.00
`other_costs`	`DECIMAL(10,2)`	DEFAULT 0.00
`total_costs`	`DECIMAL(10,2)`	GENERATED ALWAYS AS (email_account_costs + labor_costs + other_costs) STORED
`created_at`	`TIMESTAMPTZ`	DEFAULT NOW()

`monthly_revenue_snapshots`

Defined in: 001_create_core_tables.sql

Column	Type	Constraints
`id`	`SERIAL`	PRIMARY KEY
`workspace_name`	`TEXT`	REFERENCES public.client_registry(workspace_name) ON DELETE CASCADE
`month`	`TEXT`	NOT NULL
`billable_leads`	`INTEGER`	DEFAULT 0
`per_lead_revenue`	`DECIMAL(10,2)`	DEFAULT 0.00
`retainer_revenue`	`DECIMAL(10,2)`	DEFAULT 0.00
`total_revenue`	`DECIMAL(10,2)`	GENERATED ALWAYS AS (per_lead_revenue + retainer_revenue) STORED
`snapshot_date`	`DATE`	DEFAULT CURRENT_DATE
`created_at`	`TIMESTAMPTZ`	DEFAULT NOW()

`client_zipcodes`

ZIP code assignments by client and month

Defined in: 001_create_core_tables.sql

Column	Type	Constraints
`id`	`SERIAL`	PRIMARY KEY
`workspace_name`	`TEXT`	REFERENCES public.client_registry(workspace_name) ON DELETE CASCADE
`zip_code`	`TEXT`	NOT NULL
`state`	`TEXT`	NOT NULL
`month`	`TEXT`	NOT NULL, -- "2025-11" or "active" for staging
`assigned_at`	`TIMESTAMPTZ`	DEFAULT NOW()
`created_at`	`TIMESTAMPTZ`	DEFAULT NOW()

`raw_contacts`

Raw contact uploads from Xpressdocs/Cole X Dates before processing

Defined in: 002_create_contact_pipeline.sql

Column	Type	Constraints
`id`	`BIGSERIAL`	PRIMARY KEY
`upload_batch_id`	`UUID`	NOT NULL DEFAULT gen_random_uuid()
`workspace_name`	`TEXT`	REFERENCES public.client_registry(workspace_name)
`month`	`TEXT`	NOT NULL
`uploaded_by`	`TEXT`
`uploaded_at`	`TIMESTAMPTZ`	DEFAULT NOW()
`scraping_job_id`	`UUID`
`zip_code`	`TEXT`
`state`	`TEXT`
`first_name`	`TEXT`
`last_name`	`TEXT`
`email`	`TEXT`	NOT NULL
`phone`	`TEXT`
`mailing_address`	`TEXT`
`mailing_city`	`TEXT`
`mailing_state`	`TEXT`
`mailing_zip`	`TEXT`
`property_address`	`TEXT`
`property_city`	`TEXT`
`property_state`	`TEXT`
`property_zip`	`TEXT`
`home_value_estimate`	`DECIMAL(12,2)`
`purchase_date`	`DATE`
`processing_status`	`TEXT`	DEFAULT 'pending' CHECK (processing_status IN ('pending', 'filtered_out', 'ready_for_verification', 'verified', 'failed'))
`is_head_of_household`	`BOOLEAN`	DEFAULT false
`meets_value_criteria`	`BOOLEAN`	DEFAULT false
`is_high_net_worth`	`BOOLEAN`	DEFAULT false
`parsed_purchase_date`	`DATE`
`filter_reason`	`TEXT`
`processed_at`	`TIMESTAMPTZ`
`created_at`	`TIMESTAMPTZ`	DEFAULT NOW()

`verified_contacts`

Email-verified contacts ready for weekly batch uploads

Defined in: 002_create_contact_pipeline.sql

Column	Type	Constraints
`id`	`BIGSERIAL`	PRIMARY KEY
`raw_contact_id`	`BIGINT`	REFERENCES public.raw_contacts(id) ON DELETE CASCADE
`workspace_name`	`TEXT`	NOT NULL REFERENCES public.client_registry(workspace_name)
`month`	`TEXT`	NOT NULL
`first_name`	`TEXT`
`last_name`	`TEXT`
`email`	`TEXT`	NOT NULL
`property_address`	`TEXT`
`property_city`	`TEXT`
`property_state`	`TEXT`
`property_zip`	`TEXT`
`home_value_estimate`	`DECIMAL(12,2)`
`purchase_date`	`DATE`
`purchase_day`	`INTEGER`
`renewal_start_date`	`DATE`
`renewal_end_date`	`DATE`
`week_bucket`	`INTEGER`	CHECK (week_bucket BETWEEN 1 AND 4)
`debounce_status`	`TEXT`	CHECK (debounce_status IN ('deliverable', 'undeliverable', 'risky', 'unknown', 'pending'))
`debounce_response`	`JSONB`
`debounce_verified_at`	`TIMESTAMPTZ`
`debounce_credits_used`	`INTEGER`	DEFAULT 1
`is_high_net_worth`	`BOOLEAN`	DEFAULT false
`target_campaign`	`TEXT`
`is_uploaded`	`BOOLEAN`	DEFAULT false
`upload_batch_id`	`UUID`
`uploaded_at`	`TIMESTAMPTZ`
`created_at`	`TIMESTAMPTZ`	DEFAULT NOW()
`updated_at`	`TIMESTAMPTZ`	DEFAULT NOW()

`weekly_batches`

Tracks each weekly batch upload to Email Bison (every Monday)

Defined in: 002_create_contact_pipeline.sql

Column	Type	Constraints
`batch_id`	`UUID`	PRIMARY KEY DEFAULT gen_random_uuid()
`workspace_name`	`TEXT`	NOT NULL REFERENCES public.client_registry(workspace_name)
`month`	`TEXT`	NOT NULL
`week_number`	`INTEGER`	NOT NULL CHECK (week_number BETWEEN 1 AND 4)
`week_bucket`	`INTEGER`	NOT NULL CHECK (week_bucket BETWEEN 1 AND 4)
`scheduled_upload_date`	`DATE`	NOT NULL
`actual_upload_date`	`DATE`
`contact_count`	`INTEGER`	DEFAULT 0
`hnw_count`	`INTEGER`	DEFAULT 0
`csv_file_path`	`TEXT`
`csv_generated_at`	`TIMESTAMPTZ`
`bison_upload_id`	`TEXT`
`bison_campaign_name`	`TEXT`
`bison_upload_status`	`TEXT`	DEFAULT 'pending' CHECK (bison_upload_status IN ('pending', 'uploaded', 'added_to_campaign', 'failed'))
`bison_error_message`	`TEXT`
`slack_notification_sent`	`BOOLEAN`	DEFAULT false
`slack_message_ts`	`TEXT`
`slack_approved_by`	`TEXT`
`slack_approved_at`	`TIMESTAMPTZ`
`created_at`	`TIMESTAMPTZ`	DEFAULT NOW()
`updated_at`	`TIMESTAMPTZ`	DEFAULT NOW()

`upload_audit_log`

Complete audit trail of all automated pipeline operations

Defined in: 002_create_contact_pipeline.sql

Column	Type	Constraints
`id`	`BIGSERIAL`	PRIMARY KEY
`batch_id`	`UUID`	REFERENCES public.weekly_batches(batch_id) ON DELETE CASCADE
`workspace_name`	`TEXT`	NOT NULL
`month`	`TEXT`	NOT NULL
`action`	`TEXT`	NOT NULL CHECK (action IN ('csv_upload', 'contact_verification', 'batch_generation', 'bison_upload', 'campaign_add', 'campaign_rename', 'slack_notification'))
`status`	`TEXT`	NOT NULL CHECK (status IN ('success', 'partial_success', 'failed'))
`contacts_processed`	`INTEGER`	DEFAULT 0
`contacts_succeeded`	`INTEGER`	DEFAULT 0
`contacts_failed`	`INTEGER`	DEFAULT 0
`api_endpoint`	`TEXT`
`api_request`	`JSONB`
`api_response`	`JSONB`
`error_details`	`JSONB`
`duration_ms`	`INTEGER`
`credits_used`	`INTEGER`
`performed_by`	`TEXT`	DEFAULT 'system'
`performed_at`	`TIMESTAMPTZ`	DEFAULT NOW()

`debounce_usage`

Tracks Debounce API credit usage for billing and monitoring

Defined in: 002_create_contact_pipeline.sql

Column	Type	Constraints
`id`	`BIGSERIAL`	PRIMARY KEY
`month`	`TEXT`	NOT NULL
`workspace_name`	`TEXT`
`batch_id`	`UUID`	REFERENCES public.weekly_batches(batch_id)
`credits_used`	`INTEGER`	NOT NULL DEFAULT 0
`emails_verified`	`INTEGER`	NOT NULL DEFAULT 0
`deliverable_count`	`INTEGER`	DEFAULT 0
`undeliverable_count`	`INTEGER`	DEFAULT 0
`risky_count`	`INTEGER`	DEFAULT 0
`unknown_count`	`INTEGER`	DEFAULT 0
`verified_at`	`TIMESTAMPTZ`	DEFAULT NOW()

`scraping_jobs`

Tracks Xpressdocs scraping jobs with status and results

Defined in: 003_create_scraping_jobs.sql

Column	Type	Constraints
`job_id`	`UUID`	PRIMARY KEY DEFAULT gen_random_uuid()
`workspace_name`	`TEXT`	REFERENCES public.client_registry(workspace_name)
`job_type`	`TEXT`	DEFAULT 'xpressdocs_scrape'
`state`	`TEXT`	NOT NULL
`zip_codes`	`TEXT[]`	NOT NULL
`month`	`TEXT`	NOT NULL
`list_type`	`TEXT`	DEFAULT 'Emailing'
`status`	`TEXT`	DEFAULT 'pending' CHECK (status IN ('pending', 'running', 'completed', 'failed', 'limit_exceeded', 'cancelled'))
`progress`	`INTEGER`	DEFAULT 0
`total_zips`	`INTEGER`
`processed_zips`	`INTEGER`	DEFAULT 0
`records_downloaded`	`INTEGER`	DEFAULT 0
`csv_files`	`TEXT[]`
`error_message`	`TEXT`
`celery_task_id`	`TEXT`
`started_at`	`TIMESTAMPTZ`
`completed_at`	`TIMESTAMPTZ`
`created_at`	`TIMESTAMPTZ`	DEFAULT NOW()
`updated_at`	`TIMESTAMPTZ`	DEFAULT NOW()

`scraping_job_logs`

Detailed logs for each step of a scraping job

Defined in: 003_create_scraping_jobs.sql

Column	Type	Constraints
`id`	`BIGSERIAL`	PRIMARY KEY
`job_id`	`UUID`	REFERENCES public.scraping_jobs(job_id) ON DELETE CASCADE
`step`	`TEXT`	NOT NULL
`status`	`TEXT`	NOT NULL
`message`	`TEXT`
`zip_codes`	`TEXT[]`
`records_count`	`INTEGER`
`logged_at`	`TIMESTAMPTZ`	DEFAULT NOW()

`agent_runs`

Tracks all automation workflow executions

Defined in: 003_create_scraping_jobs.sql

Column	Type	Constraints
`run_id`	`UUID`	PRIMARY KEY DEFAULT gen_random_uuid()
`workflow`	`TEXT`	NOT NULL
`client_id`	`INTEGER`	REFERENCES public.client_registry(workspace_id)
`site`	`TEXT`
`status`	`TEXT`	DEFAULT 'running' CHECK (status IN ('running', 'success', 'failed', 'partial'))
`started_at`	`TIMESTAMPTZ`	DEFAULT NOW()
`finished_at`	`TIMESTAMPTZ`
`metrics`	`JSONB`
`trace_url`	`TEXT`
`error`	`TEXT`
`created_at`	`TIMESTAMPTZ`	DEFAULT NOW()
`updated_at`	`TIMESTAMPTZ`	DEFAULT NOW()

`agent_errors`

Detailed error tracking with screenshots and traces

Defined in: 003_create_scraping_jobs.sql

Column	Type	Constraints
`id`	`BIGSERIAL`	PRIMARY KEY
`run_id`	`UUID`	REFERENCES public.agent_runs(run_id) ON DELETE CASCADE
`step`	`TEXT`	NOT NULL
`error_type`	`TEXT`	NOT NULL
`message`	`TEXT`	NOT NULL
`stack_trace`	`TEXT`
`screenshot_url`	`TEXT`
`trace_url`	`TEXT`
`context`	`JSONB`
`retry_count`	`INTEGER`	DEFAULT 0
`resolved`	`BOOLEAN`	DEFAULT false
`resolved_at`	`TIMESTAMPTZ`
`created_at`	`TIMESTAMPTZ`	DEFAULT NOW()

`site_credentials`

Secure credential storage for external sites

Defined in: 003_create_scraping_jobs.sql

Column	Type	Constraints
`id`	`SERIAL`	PRIMARY KEY
`site`	`TEXT`	NOT NULL
`username`	`TEXT`	NOT NULL
`secret_ref`	`TEXT`	NOT NULL
`state_coverage`	`TEXT[]`
`mfa_type`	`TEXT`
`last_verified_at`	`TIMESTAMPTZ`
`notes`	`TEXT`
`created_at`	`TIMESTAMPTZ`	DEFAULT NOW()
`updated_at`	`TIMESTAMPTZ`	DEFAULT NOW()

`client_leads`

Client portal leads with pipeline management

Defined in: 004_create_lead_management.sql

Column	Type	Constraints
`id`	`UUID`	PRIMARY KEY DEFAULT gen_random_uuid()
`workspace_name`	`TEXT`	NOT NULL
`lead_email`	`TEXT`
`first_name`	`TEXT`
`last_name`	`TEXT`
`phone`	`TEXT`
`address`	`TEXT`
`city`	`TEXT`
`state`	`TEXT`
`zip`	`TEXT`
`date_received`	`TIMESTAMPTZ`
`reply_received`	`TEXT`
`email_sent`	`TEXT`
`email_subject`	`TEXT`
`lead_value`	`DECIMAL(10,2)`	DEFAULT 500.00
`renewal_date`	`TEXT`
`birthday`	`TEXT`
`campaign_name`	`TEXT`
`sender_email`	`TEXT`
`icp`	`BOOLEAN`	DEFAULT false
`pipeline_stage`	`TEXT`	DEFAULT 'new'
`pipeline_position`	`INTEGER`	DEFAULT 0
`interested`	`BOOLEAN`	DEFAULT false
`notes`	`TEXT`
`premium_amount`	`DECIMAL(10,2)`
`policy_type`	`TEXT`
`bison_conversation_url`	`TEXT`
`bison_lead_id`	`TEXT`
`reply_uuid`	`TEXT`
`custom_variables`	`JSONB`
`tags`	`TEXT[]`
`airtable_id`	`TEXT`	UNIQUE
`deleted_at`	`TIMESTAMPTZ`
`created_at`	`TIMESTAMPTZ`	DEFAULT NOW()
`updated_at`	`TIMESTAMPTZ`	DEFAULT NOW()
`last_synced_at`	`TIMESTAMPTZ`	DEFAULT NOW()

`email_accounts`

Email account inventory with health and capacity tracking

Defined in: 005_create_email_infrastructure.sql

Column	Type	Constraints
`id`	`SERIAL`	PRIMARY KEY
`email_address`	`TEXT`	UNIQUE NOT NULL
`workspace_name`	`TEXT`	REFERENCES public.client_registry(workspace_name)
`status`	`TEXT`	DEFAULT 'active'
`health_score`	`INTEGER`	DEFAULT 100
`daily_sending_limit`	`INTEGER`	DEFAULT 50
`volume_per_account`	`INTEGER`	DEFAULT 0
`provider`	`TEXT`
`notes`	`TEXT`
`created_at`	`TIMESTAMPTZ`	DEFAULT NOW()
`updated_at`	`TIMESTAMPTZ`	DEFAULT NOW()

`email_account_metadata`

Daily performance metrics for email accounts

Defined in: 005_create_email_infrastructure.sql

Column	Type	Constraints
`id`	`SERIAL`	PRIMARY KEY
`email_account_id`	`INTEGER`	REFERENCES public.email_accounts(id) ON DELETE CASCADE
`sent_count`	`INTEGER`	DEFAULT 0
`delivered_count`	`INTEGER`	DEFAULT 0
`bounce_rate`	`DECIMAL(5,2)`
`spam_rate`	`DECIMAL(5,2)`
`metric_date`	`DATE`	NOT NULL
`created_at`	`TIMESTAMPTZ`	DEFAULT NOW()

`email_account_polling`

Tracks email account polling job status

Defined in: 005_create_email_infrastructure.sql

Column	Type	Constraints
`id`	`SERIAL`	PRIMARY KEY
`last_poll_at`	`TIMESTAMPTZ`
`next_poll_at`	`TIMESTAMPTZ`
`status`	`TEXT`	DEFAULT 'idle'
`error_message`	`TEXT`
`created_at`	`TIMESTAMPTZ`	DEFAULT NOW()
`updated_at`	`TIMESTAMPTZ`	DEFAULT NOW()

`user_profiles`

Extended user profile information linked to auth.users

Defined in: 006_create_user_profiles.sql

Column	Type	Constraints
`id`	`UUID`	PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE
`email`	`TEXT`	NOT NULL UNIQUE
`full_name`	`TEXT`
`avatar_url`	`TEXT`
`phone`	`TEXT`
`default_workspace_name`	`TEXT`	REFERENCES public.client_registry(workspace_name) ON DELETE SET NULL
`is_active`	`BOOLEAN`	DEFAULT true
`email_verified`	`BOOLEAN`	DEFAULT false
`last_login_at`	`TIMESTAMPTZ`
`login_count`	`INTEGER`	DEFAULT 0
`metadata`	`JSONB`	DEFAULT '{}'::jsonb
`created_at`	`TIMESTAMPTZ`	DEFAULT NOW()
`updated_at`	`TIMESTAMPTZ`	DEFAULT NOW()

`client_filter_rules`

Client-specific filter rules applied before email verification

Defined in: 008_create_filter_rules.sql

Column	Type	Constraints
`id`	`UUID`	PRIMARY KEY DEFAULT gen_random_uuid()
`workspace_name`	`TEXT`	NOT NULL REFERENCES public.client_registry(workspace_name) ON DELETE CASCADE
`rule_name`	`TEXT`	NOT NULL
`priority`	`INTEGER`	NOT NULL DEFAULT 100, -- Lower number = higher priority
`is_active`	`BOOLEAN`	DEFAULT true
`field_name`	`TEXT`	NOT NULL, -- Column name in raw_contacts
`operator`	`TEXT`	NOT NULL CHECK (operator IN (
`value`	`JSONB`	NOT NULL, -- Expected value (can be string, number, array)
`action`	`TEXT`	NOT NULL CHECK (action IN ('require', 'reject')), -- What to do if rule matches
`created_at`	`TIMESTAMPTZ`	DEFAULT NOW()
`updated_at`	`TIMESTAMPTZ`	DEFAULT NOW()

`credential_audit_log`

Audit trail for all credential management actions

Defined in: 015_create_credential_audit_log.sql

Column	Type	Constraints
`id`	`BIGSERIAL`	PRIMARY KEY
`credential_id`	`INTEGER`	REFERENCES public.site_credentials(id) ON DELETE SET NULL
`action`	`TEXT`	NOT NULL CHECK (action IN ('created', 'updated', 'deleted', 'tested', 'password_changed', 'accessed'))
`performed_by`	`UUID`	REFERENCES auth.users(id) ON DELETE SET NULL
`ip_address`	`TEXT`
`user_agent`	`TEXT`
`details`	`JSONB`	DEFAULT '{}'::jsonb
`previous_state`	`JSONB`
`new_state`	`JSONB`
`success`	`BOOLEAN`	DEFAULT true
`error_message`	`TEXT`
`created_at`	`TIMESTAMPTZ`	DEFAULT NOW()

`scraping_job_batches`

Child table for scraping_jobs. Each batch represents one state being scraped in parallel. Enables 3-5x speedup via concurrent worker execution.

Defined in: 016_create_scraping_job_batches.sql

Column	Type	Constraints
`id`	`BIGSERIAL`	PRIMARY KEY
`job_id`	`UUID`	NOT NULL REFERENCES public.scraping_jobs(job_id) ON DELETE CASCADE
`batch_number`	`INT`	NOT NULL
`state`	`TEXT`	NOT NULL
`zip_codes`	`TEXT[]`	NOT NULL
`month`	`TEXT`	NOT NULL
`list_type`	`TEXT`	NOT NULL
`workspace_name`	`TEXT`	NOT NULL
`credential_id`	`INT`	REFERENCES public.site_credentials(id)
`status`	`TEXT`	NOT NULL DEFAULT 'pending'
`celery_task_id`	`TEXT`	UNIQUE
`csv_file_path`	`TEXT`
`record_count`	`INT`	DEFAULT 0
`split_count`	`INT`	DEFAULT 0, -- Number of times batch was split due to 10K limit
`created_at`	`TIMESTAMPTZ`	NOT NULL DEFAULT NOW()
`started_at`	`TIMESTAMPTZ`
`completed_at`	`TIMESTAMPTZ`
`duration_seconds`	`INT`
`retry_count`	`INT`	DEFAULT 0
`max_retries`	`INT`	DEFAULT 3
`error_message`	`TEXT`
`error_type`	`TEXT,`	-- 'browser_crash', 'timeout', 'auth_failure', 'network_error'
`browser_profile`	`TEXT,`	-- Chromium profile used
`ip_address`	`TEXT,`	-- For debugging geolocation issues

`campaign_metrics`

Stores performance metrics synced from Email Bison campaigns

Defined in: 017_create_campaign_metrics.sql

Column	Type	Constraints
`id`	`BIGSERIAL`	PRIMARY KEY
`batch_id`	`UUID`	REFERENCES public.weekly_batches(batch_id) ON DELETE CASCADE
`email_bison_campaign_id`	`TEXT`	NOT NULL
`emails_sent`	`INT`	DEFAULT 0
`emails_delivered`	`INT`	DEFAULT 0
`emails_bounced`	`INT`	DEFAULT 0
`emails_opened`	`INT`	DEFAULT 0
`emails_clicked`	`INT`	DEFAULT 0
`emails_replied`	`INT`	DEFAULT 0
`unsubscribed`	`INT`	DEFAULT 0
`open_rate`	`FLOAT`	DEFAULT 0.0
`click_rate`	`FLOAT`	DEFAULT 0.0
`reply_rate`	`FLOAT`	DEFAULT 0.0
`synced_at`	`TIMESTAMPTZ`	DEFAULT NOW()
`created_at`	`TIMESTAMPTZ`	DEFAULT NOW()

`debounce_bulk_jobs`

Tracks bulk email verification jobs submitted to Debounce Bulk API. Enables 10-100x faster verification compared to sequential API calls. Auto-used when batch_size > 50 emails.

Defined in: 018_create_debounce_bulk_jobs.sql

Column	Type	Constraints
`id`	`BIGSERIAL`	PRIMARY KEY
`job_id`	`UUID`	UNIQUE DEFAULT gen_random_uuid()
`workspace_name`	`TEXT`	NOT NULL REFERENCES public.client_registry(workspace_name)
`month`	`TEXT`	NOT NULL
`debounce_list_id`	`TEXT`	UNIQUE, -- From Debounce API response
`debounce_list_name`	`TEXT`
`upload_csv_url`	`TEXT`	NOT NULL, -- Public URL we sent to Debounce
`result_csv_url`	`TEXT,`	-- CDN URL from Debounce
`storage_path`	`TEXT,`	-- Our storage path for cleanup
`status`	`TEXT`	NOT NULL DEFAULT 'uploading'
`percentage`	`INT`	DEFAULT 0, -- Processing progress (0-100)
`total_emails`	`INT`	NOT NULL
`processed_emails`	`INT`	DEFAULT 0
`deliverable_count`	`INT`	DEFAULT 0
`undeliverable_count`	`INT`	DEFAULT 0
`risky_count`	`INT`	DEFAULT 0
`unknown_count`	`INT`	DEFAULT 0
`created_at`	`TIMESTAMPTZ`	DEFAULT NOW()
`submitted_at`	`TIMESTAMPTZ,`	-- When sent to Debounce
`completed_at`	`TIMESTAMPTZ`
`downloaded_at`	`TIMESTAMPTZ,`	-- When results downloaded
`error_message`	`TEXT`
`retry_count`	`INT`	DEFAULT 0
`max_retries`	`INT`	DEFAULT 3
`credits_used`	`INT`

`filtering_tasks`

Tracks filtering task progress for real-time SSE updates to frontend

Defined in: 020_create_task_progress_tables.sql

Column	Type	Constraints
`task_id`	`TEXT`	PRIMARY KEY
`workspace_name`	`TEXT`	NOT NULL REFERENCES public.client_registry(workspace_name)
`status`	`TEXT`	NOT NULL DEFAULT 'pending'
`total`	`INTEGER`	NOT NULL DEFAULT 0
`processed`	`INTEGER`	NOT NULL DEFAULT 0
`passed`	`INTEGER`	NOT NULL DEFAULT 0
`filtered_out`	`INTEGER`	NOT NULL DEFAULT 0
`source_status`	`TEXT`	NOT NULL DEFAULT 'pending'
`batch_size`	`INTEGER`	NOT NULL DEFAULT 1000
`error`	`TEXT`
`started_at`	`TIMESTAMPTZ`
`completed_at`	`TIMESTAMPTZ`
`created_at`	`TIMESTAMPTZ`	NOT NULL DEFAULT NOW()
`updated_at`	`TIMESTAMPTZ`	NOT NULL DEFAULT NOW()

`verification_tasks`

Tracks verification task progress for real-time SSE updates to frontend

Defined in: 020_create_task_progress_tables.sql

Column	Type	Constraints
`task_id`	`TEXT`	PRIMARY KEY
`workspace_name`	`TEXT`	NOT NULL REFERENCES public.client_registry(workspace_name)
`status`	`TEXT`	NOT NULL DEFAULT 'pending'
`method`	`TEXT`	NOT NULL DEFAULT 'sequential'
`total`	`INTEGER`	NOT NULL DEFAULT 0
`processed`	`INTEGER`	NOT NULL DEFAULT 0
`verified`	`INTEGER`	NOT NULL DEFAULT 0
`invalid`	`INTEGER`	NOT NULL DEFAULT 0
`unknown`	`INTEGER`	NOT NULL DEFAULT 0
`bulk_job_id`	`TEXT`
`debounce_status`	`TEXT`
`batch_size`	`INTEGER`	NOT NULL DEFAULT 100
`error`	`TEXT`
`started_at`	`TIMESTAMPTZ`
`completed_at`	`TIMESTAMPTZ`
`created_at`	`TIMESTAMPTZ`	NOT NULL DEFAULT NOW()
`updated_at`	`TIMESTAMPTZ`	NOT NULL DEFAULT NOW()

`pipeline_runs`

Tracks complete pipeline executions from sourcing to Email Bison upload

Defined in: 023_create_pipeline_runs.sql

Column	Type	Constraints
`id`	`SERIAL`	PRIMARY KEY
`run_id`	`UUID`	DEFAULT gen_random_uuid() UNIQUE NOT NULL
`workspace_name`	`TEXT`	NOT NULL REFERENCES client_registry(workspace_name)
`month`	`TEXT`	NOT NULL, -- YYYY-MM format
`source`	`TEXT`	NOT NULL DEFAULT 'scraper', -- 'scraper', 'master_db', 'csv_upload'
`target_volume`	`INTEGER`
`state`	`TEXT`
`zip_codes`	`TEXT[],`	-- Array of ZIP codes
`status`	`TEXT`	NOT NULL DEFAULT 'pending', -- pending, running, completed, failed, cancelled
`current_stage`	`TEXT`	DEFAULT 'init', -- init, scraping, processing, filtering, verifying, batching, uploading, completed
`stage_counts`	`JSONB`	DEFAULT '{}'::jsonb
`started_at`	`TIMESTAMPTZ`
`completed_at`	`TIMESTAMPTZ`
`error_message`	`TEXT`
`error_stage`	`TEXT`
`celery_task_ids`	`JSONB`	DEFAULT '{}'::jsonb
`created_at`	`TIMESTAMPTZ`	DEFAULT NOW()
`updated_at`	`TIMESTAMPTZ`	DEFAULT NOW()
`created_by`	`UUID`	REFERENCES auth.users(id)

`client_targets`

Monthly KPI targets and progress tracking for Gap-to-Goal dashboard

Defined in: 030_client_targets.sql

Column	Type	Constraints
`id`	`SERIAL`	PRIMARY KEY
`workspace_name`	`TEXT`	NOT NULL REFERENCES public.client_registry(workspace_name)
`month`	`TEXT`	NOT NULL, -- YYYY-MM format
`contact_volume_target`	`INTEGER`	DEFAULT 0
`email_volume_target`	`INTEGER`	DEFAULT 0
`lead_target`	`INTEGER`	DEFAULT 0
`revenue_target`	`NUMERIC(10,2)`	DEFAULT 0
`contacts_uploaded`	`INTEGER`	DEFAULT 0
`emails_sent`	`INTEGER`	DEFAULT 0
`leads_generated`	`INTEGER`	DEFAULT 0
`revenue_actual`	`NUMERIC(10,2)`	DEFAULT 0
`contacts_gap`	`INTEGER`	GENERATED ALWAYS AS (contact_volume_target - contacts_uploaded) STORED
`emails_gap`	`INTEGER`	GENERATED ALWAYS AS (email_volume_target - emails_sent) STORED
`leads_gap`	`INTEGER`	GENERATED ALWAYS AS (lead_target - leads_generated) STORED
`revenue_gap`	`NUMERIC(10,2)`	GENERATED ALWAYS AS (revenue_target - revenue_actual) STORED
`contacts_progress`	`NUMERIC(5,2)`	GENERATED ALWAYS AS (
`CASE`	`WHEN`	contact_volume_target > 0
`ELSE`	`0`	END
`emails_progress`	`NUMERIC(5,2)`	GENERATED ALWAYS AS (
`CASE`	`WHEN`	email_volume_target > 0
`ELSE`	`0`	END
`leads_progress`	`NUMERIC(5,2)`	GENERATED ALWAYS AS (
`CASE`	`WHEN`	lead_target > 0
`ELSE`	`0`	END
`revenue_progress`	`NUMERIC(5,2)`	GENERATED ALWAYS AS (
`CASE`	`WHEN`	revenue_target > 0
`THEN`	`LEAST(ROUND((revenue_actual`	/ revenue_target * 100), 2), 999.99)
`ELSE`	`0`	END
`targets_overridden`	`BOOLEAN`	DEFAULT false
`notes`	`TEXT`
`last_synced_at`	`TIMESTAMPTZ`
`created_at`	`TIMESTAMPTZ`	DEFAULT NOW()
`updated_at`	`TIMESTAMPTZ`	DEFAULT NOW()

`crm_delivery_log`

Tracks all lead deliveries to external CRMs (Agency Zoom, Ricochet, etc.)

Defined in: 031_crm_webhook_integration.sql

Column	Type	Constraints
`id`	`UUID`	PRIMARY KEY DEFAULT gen_random_uuid()
`workspace_name`	`TEXT`	NOT NULL
`lead_email`	`TEXT`	NOT NULL
`bison_lead_id`	`TEXT`
`bison_reply_id`	`TEXT`
`crm_type`	`TEXT`	NOT NULL, -- 'agency_zoom', 'ricochet', 'ezlynx', 'agency_mvp', 'generic'
`crm_endpoint`	`TEXT`	NOT NULL
`success`	`BOOLEAN`	NOT NULL DEFAULT FALSE
`http_status_code`	`INTEGER`
`response_body`	`TEXT`
`error_message`	`TEXT`
`attempt_number`	`INTEGER`	NOT NULL DEFAULT 1
`max_attempts`	`INTEGER`	NOT NULL DEFAULT 3
`next_retry_at`	`TIMESTAMPTZ`
`request_payload`	`JSONB`
`request_headers`	`JSONB`
`created_at`	`TIMESTAMPTZ`	NOT NULL DEFAULT NOW()
`completed_at`	`TIMESTAMPTZ`

`slack_notifications_sent`

Deduplication table for Slack notifications

Defined in: 031_crm_webhook_integration.sql

Column	Type	Constraints
`id`	`UUID`	PRIMARY KEY DEFAULT gen_random_uuid()
`reply_id`	`TEXT`	NOT NULL
`workspace_name`	`TEXT`	NOT NULL
`notification_type`	`TEXT`	NOT NULL, -- 'client', 'global'
`lead_email`	`TEXT`
`slack_webhook_url`	`TEXT`
`sent_at`	`TIMESTAMPTZ`	NOT NULL DEFAULT NOW()

`lead_replies`

Stores all Email Bison replies for live dashboard and CRM routing

Defined in: 031_crm_webhook_integration.sql

Column	Type	Constraints
`id`	`UUID`	PRIMARY KEY DEFAULT gen_random_uuid()
`workspace_name`	`TEXT`	NOT NULL
`lead_email`	`TEXT`	NOT NULL
`first_name`	`TEXT`
`last_name`	`TEXT`
`company`	`TEXT`
`title`	`TEXT`
`phone`	`TEXT`
`reply_text`	`TEXT`
`reply_date`	`TIMESTAMPTZ`	NOT NULL DEFAULT NOW()
`sentiment`	`TEXT`	CHECK (sentiment IN ('positive', 'negative', 'neutral'))
`is_interested`	`BOOLEAN`	DEFAULT FALSE
`confidence_score`	`INTEGER`
`ai_reasoning`	`TEXT`
`sentiment_source`	`TEXT`	CHECK (sentiment_source IN ('ai', 'bison', 'hybrid', 'manual'))
`bison_sentiment`	`TEXT`
`needs_review`	`BOOLEAN`	DEFAULT FALSE
`bison_lead_id`	`TEXT`
`bison_reply_id`	`TEXT`	UNIQUE
`bison_reply_numeric_id`	`INTEGER`
`bison_conversation_url`	`TEXT`
`bison_workspace_id`	`TEXT`
`live_replies_enabled`	`BOOLEAN`	DEFAULT TRUE
`crm_sent_at`	`TIMESTAMPTZ`
`crm_delivery_status`	`TEXT`	CHECK (crm_delivery_status IN ('pending', 'sent', 'failed', 'retrying'))
`created_at`	`TIMESTAMPTZ`	NOT NULL DEFAULT NOW()
`updated_at`	`TIMESTAMPTZ`	NOT NULL DEFAULT NOW()

`webhook_delivery_log`

Logs all incoming Email Bison webhooks for debugging

Defined in: 031_crm_webhook_integration.sql

Column	Type	Constraints
`id`	`UUID`	PRIMARY KEY DEFAULT gen_random_uuid()
`event_type`	`TEXT`	NOT NULL
`workspace_name`	`TEXT`
`payload`	`JSONB`	NOT NULL
`success`	`BOOLEAN`	NOT NULL DEFAULT FALSE
`processing_time_ms`	`INTEGER`
`error_message`	`TEXT`
`created_at`	`TIMESTAMPTZ`	NOT NULL DEFAULT NOW()

`webhook_health`

Per-workspace webhook health metrics

Defined in: 031_crm_webhook_integration.sql

Column	Type	Constraints
`id`	`UUID`	PRIMARY KEY DEFAULT gen_random_uuid()
`workspace_name`	`TEXT`	NOT NULL UNIQUE
`last_webhook_at`	`TIMESTAMPTZ`
`webhook_count_24h`	`INTEGER`	DEFAULT 0
`success_rate_24h`	`NUMERIC(5,2)`	DEFAULT 100.00
`is_healthy`	`BOOLEAN`	DEFAULT TRUE
`last_error_message`	`TEXT`
`created_at`	`TIMESTAMPTZ`	NOT NULL DEFAULT NOW()
`updated_at`	`TIMESTAMPTZ`	NOT NULL DEFAULT NOW()

`home_value_codes`

Lookup table for home value codes (A-U) to dollar amounts

Defined in: 032_master_contacts_integration.sql

Column	Type	Constraints
`code`	`TEXT`	PRIMARY KEY
`min_value`	`INTEGER`
`max_value`	`INTEGER`
`midpoint`	`INTEGER`	NOT NULL
`description`	`TEXT`

`campaign_crm_routing`

Campaign-level CRM routing overrides for workspace defaults

Defined in: 033_campaign_crm_routing.sql

Column	Type	Constraints
`id`	`SERIAL`	PRIMARY KEY
`campaign_id`	`INT`	NOT NULL UNIQUE
`campaign_name`	`TEXT,`	-- For reference/display
`workspace_name`	`TEXT`	NOT NULL
`crm_type`	`TEXT,`	-- 'zapier', 'agency_zoom', 'agency_mvp', etc.
`external_api_url`	`TEXT`	NOT NULL, -- The webhook URL
`external_api_token`	`TEXT,`	-- If authentication needed
`is_active`	`BOOLEAN`	DEFAULT true
`created_at`	`TIMESTAMPTZ`	DEFAULT NOW()
`updated_at`	`TIMESTAMPTZ`	DEFAULT NOW()

`tasks`

Defined in: 035_create_tasks_table.sql

Column	Type	Constraints
`id`	`UUID`	PRIMARY KEY DEFAULT gen_random_uuid()
`title`	`TEXT`	NOT NULL
`description`	`TEXT`
`assignee_id`	`UUID`	REFERENCES public.user_profiles(id) ON DELETE SET NULL
`assignee_name`	`TEXT,`	-- Denormalized for fast display
`created_by`	`UUID`	NOT NULL REFERENCES public.user_profiles(id)
`status`	`TEXT`	NOT NULL DEFAULT 'todo'
`priority`	`TEXT`	NOT NULL DEFAULT 'medium'
`category`	`TEXT`	NOT NULL DEFAULT 'operations'
`visibility`	`TEXT`	NOT NULL DEFAULT 'team'
`due_date`	`DATE`
`completed_at`	`TIMESTAMPTZ`
`source`	`JSONB`	DEFAULT '{}'::jsonb
`is_recurring`	`BOOLEAN`	DEFAULT false
`recurring_pattern`	`TEXT`
`last_completed`	`TIMESTAMPTZ`
`last_reminded_at`	`TIMESTAMPTZ`
`created_at`	`TIMESTAMPTZ`	DEFAULT NOW()
`updated_at`	`TIMESTAMPTZ`	DEFAULT NOW()

`auto_reply_config`

Defined in: 036_auto_reply_config.sql

Column	Type	Constraints
`id`	`UUID`	PRIMARY KEY DEFAULT gen_random_uuid()
`workspace_name`	`TEXT`	NOT NULL UNIQUE
`enabled`	`BOOLEAN`	NOT NULL DEFAULT false
`tone`	`TEXT`	NOT NULL DEFAULT 'professional'
`min_confidence_score`	`INT`	NOT NULL DEFAULT 70
`reply_to_interested`	`BOOLEAN`	NOT NULL DEFAULT true
`reply_to_not_interested`	`BOOLEAN`	NOT NULL DEFAULT false
`reply_to_neutral`	`BOOLEAN`	NOT NULL DEFAULT false
`skip_auto_replies`	`BOOLEAN`	NOT NULL DEFAULT true
`skip_bounced`	`BOOLEAN`	NOT NULL DEFAULT true
`max_replies_per_hour`	`INT`	NOT NULL DEFAULT 20
`delay_seconds`	`INT`	NOT NULL DEFAULT 300
`custom_instructions`	`TEXT`
`created_at`	`TIMESTAMPTZ`	DEFAULT now()
`updated_at`	`TIMESTAMPTZ`	DEFAULT now()

`auto_reply_log`

Defined in: 036_auto_reply_config.sql

Column	Type	Constraints
`id`	`UUID`	PRIMARY KEY DEFAULT gen_random_uuid()
`workspace_name`	`TEXT`	NOT NULL
`lead_reply_id`	`UUID`	REFERENCES lead_replies(id)
`bison_reply_numeric_id`	`INT`
`lead_email`	`TEXT`
`sentiment`	`TEXT`
`confidence_score`	`INT`
`tone_used`	`TEXT`
`reply_text`	`TEXT`
`model_used`	`TEXT`
`sent_successfully`	`BOOLEAN`	NOT NULL DEFAULT false
`skipped_reason`	`TEXT`
`error_message`	`TEXT`
`processing_time_ms`	`INT`
`created_at`	`TIMESTAMPTZ`	DEFAULT now()

`reply_templates`

Defined in: 037_thomas_migration_compat.sql

Column	Type	Constraints
`id`	`SERIAL`	PRIMARY KEY
`workspace_name`	`TEXT`	NOT NULL
`template_text_with_phone`	`TEXT`
`template_text_no_phone`	`TEXT`
`cc_emails`	`JSONB`	DEFAULT '[]'
`special_instructions`	`TEXT`
`created_at`	`TIMESTAMPTZ`	DEFAULT now()
`updated_at`	`TIMESTAMPTZ`	DEFAULT now()

`sent_replies`

Defined in: 037_thomas_migration_compat.sql

Column	Type	Constraints
`id`	`SERIAL`	PRIMARY KEY
`workspace_name`	`TEXT`	NOT NULL
`bison_reply_id`	`INTEGER`
`reply_uuid`	`TEXT`
`lead_name`	`TEXT`
`lead_email`	`TEXT`
`generated_reply_text`	`TEXT`
`cc_emails`	`JSONB`	DEFAULT '[]'
`sent_by`	`TEXT`
`sent_at`	`TIMESTAMPTZ`
`status`	`TEXT`	DEFAULT 'sent' CHECK (status IN ('pending', 'sent', 'failed', 'draft'))
`error_message`	`TEXT`
`created_at`	`TIMESTAMPTZ`	DEFAULT now()

`sma_policies`

Defined in: 037_thomas_migration_compat.sql

Column	Type	Constraints
`id`	`UUID`	PRIMARY KEY DEFAULT gen_random_uuid()
`workspace_name`	`TEXT`	NOT NULL
`lead_id`	`UUID`
`policy_type`	`TEXT`
`premium_amount`	`DECIMAL(10,2)`
`agency_commission`	`DECIMAL(10,2)`
`maverick_commission`	`DECIMAL(10,2)`
`created_at`	`TIMESTAMPTZ`	DEFAULT now()
`updated_at`	`TIMESTAMPTZ`	DEFAULT now()

`client_metrics`

Defined in: 037_thomas_migration_compat.sql

Column	Type	Constraints
`id`	`UUID`	PRIMARY KEY DEFAULT gen_random_uuid()
`workspace_name`	`TEXT`	NOT NULL
`metric_date`	`DATE`	NOT NULL
`metric_type`	`TEXT`	DEFAULT 'mtd'
`emails_sent`	`INTEGER`	DEFAULT 0
`emails_sent_today`	`INTEGER`	DEFAULT 0
`emails_sent_mtd`	`INTEGER`	DEFAULT 0
`emails_sent_last_7_days`	`INTEGER`	DEFAULT 0
`emails_sent_last_14_days`	`INTEGER`	DEFAULT 0
`emails_sent_last_30_days`	`INTEGER`	DEFAULT 0
`emails_scheduled_today`	`INTEGER`	DEFAULT 0
`emails_scheduled_tomorrow`	`INTEGER`	DEFAULT 0
`positive_replies`	`INTEGER`	DEFAULT 0
`positive_replies_mtd`	`INTEGER`	DEFAULT 0
`positive_replies_current_month`	`INTEGER`	DEFAULT 0
`positive_replies_last_7_days`	`INTEGER`	DEFAULT 0
`positive_replies_last_14_days`	`INTEGER`	DEFAULT 0
`positive_replies_last_30_days`	`INTEGER`	DEFAULT 0
`positive_replies_last_month`	`INTEGER`	DEFAULT 0
`all_replies_mtd`	`INTEGER`	DEFAULT 0
`bounced_mtd`	`INTEGER`	DEFAULT 0
`unsubscribed_mtd`	`INTEGER`	DEFAULT 0
`projection_emails_eom`	`INTEGER`	DEFAULT 0
`projection_positive_replies_eom`	`INTEGER`	DEFAULT 0
`mtd_leads_progress`	`DECIMAL(5,2)`	DEFAULT 0
`projection_replies_progress`	`DECIMAL(5,2)`	DEFAULT 0
`last_week_vs_week_before_progress`	`DECIMAL(5,2)`	DEFAULT 0
`created_at`	`TIMESTAMPTZ`	DEFAULT now()
`updated_at`	`TIMESTAMPTZ`	DEFAULT now()

`monthly_daily_revenue_history`

Defined in: 037_thomas_migration_compat.sql

Column	Type	Constraints
`id`	`UUID`	PRIMARY KEY DEFAULT gen_random_uuid()
`month_year`	`TEXT`	NOT NULL
`day`	`INTEGER`	NOT NULL
`date`	`DATE`
`daily_revenue`	`DECIMAL(10,2)`	DEFAULT 0
`cumulative_revenue`	`DECIMAL(10,2)`	DEFAULT 0
`lead_count`	`INTEGER`	DEFAULT 0
`created_at`	`TIMESTAMPTZ`	DEFAULT now()

`workspace_stats_cache`

*Pre-computed workspace KPIs rebuilt after every Bison sync. *

Defined in: 042_workspace_stats_cache.sql

Column	Type	Constraints
`id`	`BIGSERIAL`	PRIMARY KEY
`workspace_name`	`TEXT`	NOT NULL
`display_name`	`TEXT`
`stat_date`	`DATE`	NOT NULL DEFAULT CURRENT_DATE
`emails_sent`	`BIGINT`	NOT NULL DEFAULT 0
`total_leads_contacted`	`BIGINT`	NOT NULL DEFAULT 0
`opened`	`BIGINT`	NOT NULL DEFAULT 0
`unique_replies`	`BIGINT`	NOT NULL DEFAULT 0
`bounced`	`BIGINT`	NOT NULL DEFAULT 0
`interested`	`BIGINT`	NOT NULL DEFAULT 0
`not_interested`	`BIGINT`	NOT NULL DEFAULT 0
`unsubscribed`	`BIGINT`	NOT NULL DEFAULT 0
`open_rate`	`NUMERIC(6,2)`	NOT NULL DEFAULT 0
`reply_rate`	`NUMERIC(6,2)`	NOT NULL DEFAULT 0
`bounce_rate`	`NUMERIC(6,2)`	NOT NULL DEFAULT 0
`interested_percentage`	`NUMERIC(6,2)`	NOT NULL DEFAULT 0
`cache_type`	`TEXT`	NOT NULL DEFAULT 'current'
`synced_at`	`TIMESTAMPTZ`	NOT NULL DEFAULT NOW()

`pipeline_stats_cache`

Pre-computed pipeline statistics per workspace per month. Refreshed by Celery workers after pipeline stage completions and every 5 minutes by Celery Beat.

Defined in: 044_pipeline_stats_cache_table.sql

Column	Type	Constraints
`workspace_name`	`TEXT`	NOT NULL
`month`	`TEXT`	NOT NULL
`total_contacts`	`BIGINT`	DEFAULT 0
`pending`	`BIGINT`	DEFAULT 0
`filtered_out`	`BIGINT`	DEFAULT 0
`ready_for_verification`	`BIGINT`	DEFAULT 0
`verifying`	`BIGINT`	DEFAULT 0
`verified`	`BIGINT`	DEFAULT 0
`invalid`	`BIGINT`	DEFAULT 0
`unknown`	`BIGINT`	DEFAULT 0
`source_scraped`	`BIGINT`	DEFAULT 0
`source_uploaded`	`BIGINT`	DEFAULT 0
`source_master_db`	`BIGINT`	DEFAULT 0
`total_verified`	`BIGINT`	DEFAULT 0
`uploaded`	`BIGINT`	DEFAULT 0
`pending_upload`	`BIGINT`	DEFAULT 0
`rejected`	`BIGINT`	DEFAULT 0
`failed`	`BIGINT`	DEFAULT 0
`deliverable_count`	`BIGINT`	DEFAULT 0
`undeliverable_count`	`BIGINT`	DEFAULT 0
`risky_count`	`BIGINT`	DEFAULT 0
`updated_at`	`TIMESTAMPTZ`	DEFAULT now()

`workspace_daily_stats`

Daily incremental metrics per workspace from Bison chart API. Used for charts and date-filtered KPIs.

Defined in: 045_workspace_daily_stats.sql

Column	Type	Constraints
`id`	`BIGSERIAL`	PRIMARY KEY
`workspace_name`	`TEXT`	NOT NULL
`stat_date`	`DATE`	NOT NULL
`sent`	`BIGINT`	NOT NULL DEFAULT 0
`replied`	`BIGINT`	NOT NULL DEFAULT 0
`interested`	`BIGINT`	NOT NULL DEFAULT 0
`bounced`	`BIGINT`	NOT NULL DEFAULT 0
`total_opens`	`BIGINT`	NOT NULL DEFAULT 0
`unique_opens`	`BIGINT`	NOT NULL DEFAULT 0
`unsubscribed`	`BIGINT`	NOT NULL DEFAULT 0
`synced_at`	`TIMESTAMPTZ`	NOT NULL DEFAULT NOW()

`reseller_configs`

Defined in: 048_infrastructure_orders.sql

Column	Type	Constraints
`id`	`SERIAL`	PRIMARY KEY
`reseller_name`	`TEXT`	NOT NULL
`display_name`	`TEXT`	NOT NULL
`account_type`	`TEXT`	NOT NULL
`account_type_display`	`TEXT`	NOT NULL
`bison_provider_tag`	`TEXT`	NOT NULL
`bison_reseller_tag`	`TEXT`	NOT NULL
`max_send_per_day`	`INTEGER`	NOT NULL
`warmup_volume_per_day`	`INTEGER`	NOT NULL
`warmup_period_days`	`INTEGER`	NOT NULL
`ramp_start`	`INTEGER`	NOT NULL
`ramp_increment`	`INTEGER`	NOT NULL
`ramp_increment_interval_days`	`INTEGER`	NOT NULL DEFAULT 1
`cost_per_inbox`	`DECIMAL(10,2)`
`is_active`	`BOOLEAN`	DEFAULT true
`created_at`	`TIMESTAMPTZ`	DEFAULT NOW()
`updated_at`	`TIMESTAMPTZ`	DEFAULT NOW()

`infrastructure_orders`

Defined in: 048_infrastructure_orders.sql

Column	Type	Constraints
`id`	`SERIAL`	PRIMARY KEY
`workspace_name`	`TEXT`	NOT NULL REFERENCES public.client_registry(workspace_name)
`reseller_config_id`	`INTEGER`	NOT NULL REFERENCES public.reseller_configs(id)
`quantity`	`INTEGER`	NOT NULL
`emails_per_inbox_per_day`	`INTEGER`	NOT NULL
`stage`	`TEXT`	NOT NULL DEFAULT 'order_placed'
`expected_provider_tag`	`TEXT`
`expected_reseller_tag`	`TEXT`
`expected_date_tag`	`TEXT`
`fulfilled_at`	`TIMESTAMPTZ`
`fulfilled_account_count`	`INTEGER`	DEFAULT 0
`warmup_verified_at`	`TIMESTAMPTZ`
`launch_date`	`DATE`
`ramp_started_at`	`TIMESTAMPTZ`
`completed_at`	`TIMESTAMPTZ`
`matched_account_ids`	`JSONB`	DEFAULT '[]'::jsonb
`notes`	`TEXT`
`created_by`	`TEXT`
`created_at`	`TIMESTAMPTZ`	DEFAULT NOW()
`updated_at`	`TIMESTAMPTZ`	DEFAULT NOW()

`infrastructure_order_events`

Defined in: 048_infrastructure_orders.sql

Column	Type	Constraints
`id`	`SERIAL`	PRIMARY KEY
`order_id`	`INTEGER`	NOT NULL REFERENCES public.infrastructure_orders(id) ON DELETE CASCADE
`from_stage`	`TEXT`
`to_stage`	`TEXT`	NOT NULL
`event_type`	`TEXT`	NOT NULL
`detail`	`TEXT`
`created_by`	`TEXT`
`created_at`	`TIMESTAMPTZ`	DEFAULT NOW()

`onboarding_drafts`

Defined in: 052_onboarding_drafts.sql

Column	Type	Constraints
`id`	`UUID`	DEFAULT gen_random_uuid() PRIMARY KEY
`created_by`	`UUID`	REFERENCES auth.users(id)
`wizard_state`	`JSONB`	NOT NULL DEFAULT '{}'
`current_step`	`INTEGER`	NOT NULL DEFAULT 1
`workspace_name`	`TEXT`
`display_name`	`TEXT`
`status`	`TEXT`	NOT NULL DEFAULT 'in_progress' CHECK (status IN ('in_progress', 'completed', 'abandoned'))
`created_at`	`TIMESTAMPTZ`	DEFAULT NOW()
`updated_at`	`TIMESTAMPTZ`	DEFAULT NOW()

`agent_threads`

Defined in: 053_agent_conversations.sql

Column	Type	Constraints
`id`	`UUID`	DEFAULT gen_random_uuid() PRIMARY KEY
`user_id`	`UUID`	NOT NULL REFERENCES auth.users(id)
`title`	`TEXT`
`session_id`	`TEXT`
`is_archived`	`BOOLEAN`	DEFAULT false
`created_at`	`TIMESTAMPTZ`	DEFAULT NOW()
`updated_at`	`TIMESTAMPTZ`	DEFAULT NOW()

`agent_messages`

Defined in: 053_agent_conversations.sql

Column	Type	Constraints
`id`	`UUID`	DEFAULT gen_random_uuid() PRIMARY KEY
`thread_id`	`UUID`	NOT NULL REFERENCES agent_threads(id) ON DELETE CASCADE
`role`	`TEXT`	NOT NULL CHECK (role IN ('user', 'assistant', 'system'))
`content`	`TEXT`
`tool_calls`	`JSONB`
`token_usage`	`JSONB`
`created_at`	`TIMESTAMPTZ`	DEFAULT NOW()

`task_events`

Lightweight task event log for status page. Auto-pruned to 7 days.

Defined in: 059_task_events.sql

Column	Type	Constraints
`id`	`SERIAL`	PRIMARY KEY
`task_id`	`TEXT`	NOT NULL
`task_name`	`TEXT`	NOT NULL
`queue`	`TEXT`
`status`	`TEXT`	NOT NULL CHECK (status IN ('started', 'succeeded', 'failed', 'retried'))
`error_message`	`TEXT`
`duration_seconds`	`FLOAT`
`worker`	`TEXT`
`created_at`	`TIMESTAMPTZ`	DEFAULT NOW()

`hall_of_fame_nominations`

Defined in: 060_hall_of_fame.sql

Column	Type	Constraints
`id`	`UUID`	DEFAULT gen_random_uuid() PRIMARY KEY
`lead_reply_id`	`UUID`	NOT NULL REFERENCES public.lead_replies(id) ON DELETE CASCADE
`category`	`TEXT`	NOT NULL CHECK (category IN ('funny', 'rude', 'wholesome', 'creative', 'unhinged'))
`nominated_by`	`TEXT`	NOT NULL, -- user email or 'ai_system'
`nomination_source`	`TEXT`	NOT NULL DEFAULT 'manual'
`ai_confidence`	`INTEGER,`	-- 0-100, only for ai_detected
`note`	`TEXT,`	-- optional comment from nominator
`workspace_name`	`TEXT`	NOT NULL
`lead_email`	`TEXT`	NOT NULL
`reply_text`	`TEXT`	NOT NULL
`reply_date`	`TIMESTAMPTZ`	NOT NULL
`vote_count`	`INTEGER`	NOT NULL DEFAULT 0, -- materialized via trigger
`created_at`	`TIMESTAMPTZ`	DEFAULT NOW()
`updated_at`	`TIMESTAMPTZ`	DEFAULT NOW()

`hall_of_fame_votes`

Defined in: 060_hall_of_fame.sql

Column	Type	Constraints
`id`	`UUID`	DEFAULT gen_random_uuid() PRIMARY KEY
`nomination_id`	`UUID`	NOT NULL REFERENCES public.hall_of_fame_nominations(id) ON DELETE CASCADE
`user_email`	`TEXT`	NOT NULL
`created_at`	`TIMESTAMPTZ`	DEFAULT NOW()

`task_activity`

Defined in: 060_tasks_v2.sql

Column	Type	Constraints
`id`	`UUID`	PRIMARY KEY DEFAULT gen_random_uuid()
`task_id`	`UUID`	NOT NULL REFERENCES public.tasks(id) ON DELETE CASCADE
`event_type`	`TEXT`	NOT NULL CHECK (event_type IN (
`actor_id`	`UUID`	REFERENCES public.user_profiles(id)
`payload`	`JSONB`	DEFAULT '{}'::jsonb
`created_at`	`TIMESTAMPTZ`	NOT NULL DEFAULT NOW()

`ai_generation_log`

Defined in: 064_reply_ai_upgrade.sql

Column	Type	Constraints
`id`	`UUID`	DEFAULT gen_random_uuid() PRIMARY KEY
`workspace_name`	`TEXT`	NOT NULL
`lead_reply_id`	`UUID`
`task_type`	`TEXT`	NOT NULL
`model`	`TEXT`	NOT NULL
`prompt_tokens`	`INT`
`completion_tokens`	`INT`
`estimated_cost_usd`	`NUMERIC(10,`	6)
`latency_ms`	`INT`
`intent`	`TEXT`
`tone`	`TEXT`
`success`	`BOOLEAN`	DEFAULT TRUE
`error_message`	`TEXT`
`created_at`	`TIMESTAMPTZ`	DEFAULT NOW()

`prompt_eval_results`

Defined in: 064_reply_ai_upgrade.sql

Column	Type	Constraints
`id`	`UUID`	DEFAULT gen_random_uuid() PRIMARY KEY
`workspace_name`	`TEXT`	NOT NULL
`eval_type`	`TEXT`	NOT NULL
`total_tests`	`INT`	NOT NULL
`passed`	`INT`	NOT NULL
`failed`	`INT`	NOT NULL
`results`	`JSONB`	NOT NULL
`model_used`	`TEXT`
`triggered_by`	`TEXT`
`created_at`	`TIMESTAMPTZ`	DEFAULT NOW()

`crm_destinations`

*Multi-destination CRM routing. Each row represents one CRM destination *

Defined in: 065_crm_destinations.sql

Column	Type	Constraints
`id`	`SERIAL`	PRIMARY KEY
`workspace_name`	`TEXT`	NOT NULL
`campaign_id`	`INT`
`crm_type`	`TEXT`	NOT NULL
`external_api_url`	`TEXT`	NOT NULL
`external_api_token`	`TEXT`
`crm_params`	`JSONB`	NOT NULL DEFAULT '{}'::jsonb
`label`	`TEXT`
`is_active`	`BOOLEAN`	NOT NULL DEFAULT TRUE
`created_at`	`TIMESTAMPTZ`	NOT NULL DEFAULT NOW()
`updated_at`	`TIMESTAMPTZ`	NOT NULL DEFAULT NOW()

`resources`

*Global content library shown on the /resources page. Visible to every *

Defined in: 072_resources.sql

Column	Type	Constraints
`id`	`UUID`	PRIMARY KEY DEFAULT gen_random_uuid()
`title`	`TEXT`	NOT NULL
`description`	`TEXT`
`category`	`TEXT`	NOT NULL
`resource_type`	`TEXT`	NOT NULL
`loom_url`	`TEXT`
`article_content`	`TEXT`
`pdf_storage_path`	`TEXT`
`thumbnail_url`	`TEXT`
`published`	`BOOLEAN`	NOT NULL DEFAULT TRUE
`created_by`	`UUID`
`created_at`	`TIMESTAMPTZ`	NOT NULL DEFAULT NOW()
`updated_at`	`TIMESTAMPTZ`	NOT NULL DEFAULT NOW()
`AND`	`article_content`	IS NULL AND pdf_storage_path IS NULL)
`AND`	`loom_url`	IS NULL AND pdf_storage_path IS NULL)
`AND`	`loom_url`	IS NULL AND article_content IS NULL)

`resource_requests`

*Topic/video requests submitted by clients via the Resources page. *

Defined in: 072_resources.sql

Column	Type	Constraints
`id`	`UUID`	PRIMARY KEY DEFAULT gen_random_uuid()
`requester_user_id`	`UUID`	NOT NULL
`requester_email`	`TEXT`	NOT NULL
`workspace_name`	`TEXT`
`topic`	`TEXT`	NOT NULL
`requested_format`	`TEXT`	NOT NULL
`description`	`TEXT`
`status`	`TEXT`	NOT NULL DEFAULT 'requested'
`fulfilled_resource_id`	`UUID`	REFERENCES resources(id) ON DELETE SET NULL
`created_at`	`TIMESTAMPTZ`	NOT NULL DEFAULT NOW()
`updated_at`	`TIMESTAMPTZ`	NOT NULL DEFAULT NOW()

`revenue_refunds`

Defined in: 073_revenue_adjustments.sql

Column	Type	Constraints
`id`	`UUID`	DEFAULT gen_random_uuid() PRIMARY KEY
`workspace_name`	`TEXT`	NOT NULL
`month`	`TEXT`	NOT NULL CHECK (month ~ '^\d{4}-(0[1-9]\|1[0-2])$')
`lead_name`	`TEXT`	NOT NULL
`lead_email`	`TEXT`
`reason`	`TEXT`
`amount_override`	`NUMERIC(10,`	2)
`price_per_lead_snapshot`	`NUMERIC(10,`	2)
`created_by`	`UUID`	REFERENCES auth.users(id) ON DELETE SET NULL
`created_at`	`TIMESTAMPTZ`	DEFAULT NOW()
`updated_at`	`TIMESTAMPTZ`	DEFAULT NOW()

`revenue_referrals`

Defined in: 073_revenue_adjustments.sql

Column	Type	Constraints
`id`	`UUID`	DEFAULT gen_random_uuid() PRIMARY KEY
`workspace_name`	`TEXT`	NOT NULL
`month`	`TEXT`	NOT NULL CHECK (month ~ '^\d{4}-(0[1-9]\|1[0-2])$')
`referred_party`	`TEXT`
`credit_amount`	`NUMERIC(10,`	2) NOT NULL DEFAULT 500
`notes`	`TEXT`
`created_by`	`UUID`	REFERENCES auth.users(id) ON DELETE SET NULL
`created_at`	`TIMESTAMPTZ`	DEFAULT NOW()
`updated_at`	`TIMESTAMPTZ`	DEFAULT NOW()

`task_timeline`

Defined in: 073_task_timeline.sql

Column	Type	Constraints
`id`	`UUID`	PRIMARY KEY DEFAULT gen_random_uuid()
`task_id`	`UUID`	NOT NULL REFERENCES public.tasks(id) ON DELETE CASCADE
`kind`	`TEXT`	NOT NULL CHECK (kind IN ('system', 'comment'))
`event_type`	`TEXT`	CHECK (event_type IN (
`payload`	`JSONB`	DEFAULT '{}'::jsonb
`body`	`TEXT`
`edited_at`	`TIMESTAMPTZ`
`deleted_at`	`TIMESTAMPTZ`
`slack_message_ts`	`TEXT`
`actor_id`	`UUID`	REFERENCES public.user_profiles(id)
`source`	`TEXT`	NOT NULL DEFAULT 'dashboard'
`created_at`	`TIMESTAMPTZ`	NOT NULL DEFAULT NOW()

`slack_event_dedup`

Defined in: 076_slack_event_dedup.sql

Column	Type	Constraints
`event_id`	`TEXT`	PRIMARY KEY
`processed_at`	`TIMESTAMPTZ`	NOT NULL DEFAULT NOW()

`audit_verticals`

*Per-vertical configuration for audit discovery + ranking. JSONB regex *

Defined in: 079_audit_phase_a_config_tables.sql

Column	Type	Constraints
`vertical_id`	`TEXT`	PRIMARY KEY
`display_name`	`TEXT`	NOT NULL
`discovery_include_patterns`	`JSONB`	NOT NULL DEFAULT '[]'::jsonb
`discovery_exclude_patterns`	`JSONB`	NOT NULL DEFAULT '[]'::jsonb
`default_thresholds`	`JSONB`	NOT NULL DEFAULT '{}'::jsonb
`enabled`	`BOOLEAN`	NOT NULL DEFAULT true
`notes`	`TEXT`
`created_at`	`TIMESTAMPTZ`	NOT NULL DEFAULT NOW()
`updated_at`	`TIMESTAMPTZ`	NOT NULL DEFAULT NOW()

`audit_recommendation_rules`

*Tunable thresholds per (loop_type, vertical_id). audit.generate_recommendations *

Defined in: 079_audit_phase_a_config_tables.sql

Column	Type	Constraints
`rule_id`	`BIGSERIAL`	PRIMARY KEY
`loop_type`	`TEXT`	NOT NULL CHECK (loop_type IN (
`vertical_id`	`TEXT`	NOT NULL REFERENCES audit_verticals(vertical_id) ON DELETE RESTRICT
`threshold_json`	`JSONB`	NOT NULL DEFAULT '{}'::jsonb
`enabled`	`BOOLEAN`	NOT NULL DEFAULT true
`notes`	`TEXT`
`created_at`	`TIMESTAMPTZ`	NOT NULL DEFAULT NOW()
`updated_at`	`TIMESTAMPTZ`	NOT NULL DEFAULT NOW()

`audit_sync_state`

*Per-(workspace, campaign, stage) ingest checkpoint. Drives incremental *

Defined in: 079_audit_phase_a_config_tables.sql

Column	Type	Constraints
`workspace_name`	`TEXT`	NOT NULL
`campaign_id`	`TEXT`	NOT NULL
`stage`	`TEXT`	NOT NULL CHECK (stage IN (
`vertical_id`	`TEXT`	REFERENCES audit_verticals(vertical_id) ON DELETE SET NULL
`last_audited_at`	`TIMESTAMPTZ`
`last_status`	`TEXT`	NOT NULL DEFAULT 'pending' CHECK (last_status IN (
`last_error`	`TEXT`
`rows_processed`	`INTEGER`
`created_at`	`TIMESTAMPTZ`	NOT NULL DEFAULT NOW()
`updated_at`	`TIMESTAMPTZ`	NOT NULL DEFAULT NOW()