Database Schema

Every table, column, index, and constraint from SQL migrations

36 migrations | 38 tables | 169 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)

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()

Indexes

Index	Table	Columns
`idx_user_workspace_access_user_id`	`user_workspace_access`	`user_id`
`idx_user_workspace_access_workspace`	`user_workspace_access`	`workspace_name`
`idx_user_workspace_access_role`	`user_workspace_access`	`role`
`idx_client_registry_workspace_name`	`client_registry`	`workspace_name`
`idx_client_registry_active`	`client_registry`	`is_active`
`idx_client_registry_billing_type`	`client_registry`	`billing_type`
`idx_client_settings_workspace`	`client_settings`	`workspace_name`
`idx_client_pricing_workspace`	`client_pricing`	`workspace_name`
`idx_client_costs_workspace_month`	`client_costs`	`workspace_name, month`
`idx_revenue_snapshots_workspace_month`	`monthly_revenue_snapshots`	`workspace_name, month`
`idx_client_zipcodes_workspace`	`client_zipcodes`	`workspace_name`
`idx_client_zipcodes_state`	`client_zipcodes`	`state`
`idx_client_zipcodes_month`	`client_zipcodes`	`month`
`idx_client_zipcodes_zip`	`client_zipcodes`	`zip_code`
`idx_raw_contacts_upload_batch`	`raw_contacts`	`upload_batch_id`
`idx_raw_contacts_workspace_month`	`raw_contacts`	`workspace_name, month`
`idx_raw_contacts_status`	`raw_contacts`	`processing_status`
`idx_raw_contacts_purchase_date`	`raw_contacts`	`parsed_purchase_date`
`idx_raw_contacts_zip`	`raw_contacts`	`property_zip`
`idx_raw_contacts_scraping_job`	`raw_contacts`	`scraping_job_id`
`idx_verified_contacts_workspace_month`	`verified_contacts`	`workspace_name, month`
`idx_verified_contacts_week_bucket`	`verified_contacts`	`week_bucket`
`idx_verified_contacts_renewal_dates`	`verified_contacts`	`renewal_start_date, renewal_end_date`
`idx_verified_contacts_upload_status`	`verified_contacts`	`is_uploaded, week_bucket`
`idx_verified_contacts_debounce_status`	`verified_contacts`	`debounce_status`
`idx_verified_contacts_hnw`	`verified_contacts`	`is_high_net_worth`
`idx_weekly_batches_workspace_month`	`weekly_batches`	`workspace_name, month`
`idx_weekly_batches_schedule`	`weekly_batches`	`scheduled_upload_date, bison_upload_status`
`idx_weekly_batches_status`	`weekly_batches`	`bison_upload_status`
`idx_upload_audit_batch`	`upload_audit_log`	`batch_id`
`idx_upload_audit_workspace_month`	`upload_audit_log`	`workspace_name, month`
`idx_upload_audit_action`	`upload_audit_log`	`action`
`idx_upload_audit_status`	`upload_audit_log`	`status`
`idx_upload_audit_date`	`upload_audit_log`	`performed_at DESC`
`idx_debounce_usage_month`	`debounce_usage`	`month`
`idx_debounce_usage_workspace`	`debounce_usage`	`workspace_name`
`idx_debounce_usage_date`	`debounce_usage`	`verified_at DESC`
`idx_scraping_jobs_workspace`	`scraping_jobs`	`workspace_name`
`idx_scraping_jobs_status`	`scraping_jobs`	`status`
`idx_scraping_jobs_created`	`scraping_jobs`	`created_at DESC`
`idx_scraping_jobs_celery_task`	`scraping_jobs`	`celery_task_id`
`idx_scraping_job_logs_job_id`	`scraping_job_logs`	`job_id`
`idx_scraping_job_logs_logged_at`	`scraping_job_logs`	`logged_at DESC`
`idx_agent_runs_workflow`	`agent_runs`	`workflow`
`idx_agent_runs_client_id`	`agent_runs`	`client_id`
`idx_agent_runs_status`	`agent_runs`	`status`
`idx_agent_runs_started_at`	`agent_runs`	`started_at DESC`
`idx_agent_errors_run_id`	`agent_errors`	`run_id`
`idx_agent_errors_step`	`agent_errors`	`step`
`idx_agent_errors_type`	`agent_errors`	`error_type`
`idx_agent_errors_resolved`	`agent_errors`	`resolved`
`idx_site_credentials_site`	`site_credentials`	`site`
`idx_site_credentials_username`	`site_credentials`	`username`
`idx_client_leads_workspace`	`client_leads`	`workspace_name`
`idx_client_leads_pipeline`	`client_leads`	`pipeline_stage`
`idx_client_leads_date`	`client_leads`	`date_received DESC`
`idx_client_leads_airtable`	`client_leads`	`airtable_id`
`idx_client_leads_updated`	`client_leads`	`updated_at DESC`
`idx_client_leads_email`	`client_leads`	`lead_email`
`idx_client_leads_interested`	`client_leads`	`interested`
`idx_client_leads_deleted`	`client_leads`	`deleted_at`
`idx_email_accounts_workspace`	`email_accounts`	`workspace_name`
`idx_email_accounts_status`	`email_accounts`	`status`
`idx_email_accounts_health`	`email_accounts`	`health_score`
`idx_email_account_metadata_account`	`email_account_metadata`	`email_account_id`
`idx_email_account_metadata_date`	`email_account_metadata`	`metric_date DESC`
`idx_user_profiles_email`	`user_profiles`	`email`
`idx_user_profiles_default_workspace`	`user_profiles`	`default_workspace_name`
`idx_user_profiles_is_active`	`user_profiles`	`is_active`
`idx_user_workspace_access_active`	`user_workspace_access`	`user_id, workspace_name`
`idx_raw_contacts_workspace_status`	`raw_contacts`	`workspace_name, processing_status`
`idx_raw_contacts_workspace_month`	`raw_contacts`	`workspace_name, month`
`idx_raw_contacts_batch`	`raw_contacts`	`upload_batch_id`
`idx_raw_contacts_job`	`raw_contacts`	`scraping_job_id`
`idx_raw_contacts_email`	`raw_contacts`	`email`
`idx_raw_contacts_workspace_status_month`	`raw_contacts`	`workspace_name, processing_status, month`
`idx_raw_contacts_email_domain`	`raw_contacts`	`email_domain`
`idx_raw_contacts_purchase_year`	`raw_contacts`	`purchase_year`
`idx_raw_contacts_age`	`raw_contacts`	`age_years`
`idx_raw_contacts_dnc_landline`	`raw_contacts`	`dnc_landline`
`idx_raw_contacts_dnc_cell`	`raw_contacts`	`dnc_cell`
`idx_raw_contacts_dob`	`raw_contacts`	`date_of_birth`
`idx_raw_contacts_purchase_amount`	`raw_contacts`	`purchase_amount`
`idx_filter_rules_workspace_active`	`client_filter_rules`	`workspace_name, is_active, priority`
`idx_filter_rules_field`	`client_filter_rules`	`field_name`
`idx_verified_contacts_head_of_household`	`verified_contacts`	`is_head_of_household`
`idx_verified_contacts_email_domain`	`verified_contacts`	`email_domain`
`idx_verified_contacts_engagement`	`verified_contacts`	`last_engagement_at DESC`
`idx_verified_contacts_campaign_targeting`	`verified_contacts`	`workspace_name, is_uploaded, is_head_of_household, email_domain`
`idx_client_registry_email_bison_workspace`	`client_registry`	`email_bison_workspace_id`
`idx_weekly_batches_bison_campaign_id`	`weekly_batches`	`bison_campaign_id`
`idx_site_credentials_vault_secret_id`	`site_credentials`	`vault_secret_id`
`idx_credential_audit_credential_id`	`credential_audit_log`	`credential_id`
`idx_credential_audit_performed_by`	`credential_audit_log`	`performed_by`
`idx_credential_audit_action`	`credential_audit_log`	`action`
`idx_credential_audit_created_at`	`credential_audit_log`	`created_at DESC`
`idx_scraping_job_batches_job_id`	`scraping_job_batches`	`job_id`
`idx_scraping_job_batches_status`	`scraping_job_batches`	`status`
`idx_scraping_job_batches_workspace`	`scraping_job_batches`	`workspace_name`
`idx_scraping_job_batches_state_month`	`scraping_job_batches`	`state, month`
`idx_scraping_job_batches_credential`	`scraping_job_batches`	`credential_id`
`idx_scraping_job_batches_celery_task`	`scraping_job_batches`	`celery_task_id`
`idx_scraping_job_batches_active`	`scraping_job_batches`	`workspace_name, status, created_at DESC`
`idx_campaign_metrics_batch_id`	`campaign_metrics`	`batch_id`
`idx_campaign_metrics_campaign_id`	`campaign_metrics`	`email_bison_campaign_id`
`idx_campaign_metrics_synced_at`	`campaign_metrics`	`synced_at DESC`
`idx_raw_contacts_workspace_month_email`	`raw_contacts`	`workspace_name, month, email`
`idx_raw_contacts_workspace_month_address`	`raw_contacts`	`workspace_name, month, property_address`
`idx_debounce_bulk_workspace`	`debounce_bulk_jobs`	`workspace_name`
`idx_debounce_bulk_status`	`debounce_bulk_jobs`	`status`
`idx_debounce_bulk_list_id`	`debounce_bulk_jobs`	`debounce_list_id`
`idx_scraping_jobs_credential_username`	`scraping_jobs`	`credential_username`
`idx_filtering_tasks_workspace`	`filtering_tasks`	`workspace_name`
`idx_filtering_tasks_status`	`filtering_tasks`	`status`
`idx_filtering_tasks_created`	`filtering_tasks`	`created_at DESC`
`idx_verification_tasks_workspace`	`verification_tasks`	`workspace_name`
`idx_verification_tasks_status`	`verification_tasks`	`status`
`idx_verification_tasks_created`	`verification_tasks`	`created_at DESC`
`idx_verification_tasks_bulk_job`	`verification_tasks`	`bulk_job_id`
`idx_pipeline_runs_workspace`	`pipeline_runs`	`workspace_name`
`idx_pipeline_runs_status`	`pipeline_runs`	`status`
`idx_pipeline_runs_run_id`	`pipeline_runs`	`run_id`
`idx_pipeline_runs_created_at`	`pipeline_runs`	`created_at DESC`
`idx_verified_contacts_upload_status`	`verified_contacts`	`upload_status`
`idx_email_accounts_bison_id`	`email_accounts`	`bison_sender_email_id`
`idx_email_accounts_workspace_bison_status`	`email_accounts`	`workspace_name, bison_status`
`idx_email_accounts_warmup_score`	`email_accounts`	`warmup_score`
`idx_email_accounts_sent_today_date`	`email_accounts`	`emails_sent_today_date`
`idx_campaign_metrics_status`	`campaign_metrics`	`campaign_status`
`idx_campaign_metrics_workspace`	`campaign_metrics`	`workspace_name`
`idx_campaign_metrics_completion`	`campaign_metrics`	`completion_percentage DESC`
`idx_campaign_metrics_workspace_status`	`campaign_metrics`	`workspace_name, campaign_status`
`idx_client_leads_bison_id_int`	`client_leads`	`bison_lead_id_int`
`idx_client_leads_campaign_status`	`client_leads`	`workspace_name, lead_campaign_status`
`idx_client_leads_bison_campaign`	`client_leads`	`bison_campaign_id`
`idx_client_leads_verification_status`	`client_leads`	`bison_verification_status`
`idx_client_leads_synced_at`	`client_leads`	`bison_lead_synced_at DESC`
`idx_client_leads_automated_reply`	`client_leads`	`is_automated_reply`
`idx_client_targets_workspace`	`client_targets`	`workspace_name`
`idx_client_targets_month`	`client_targets`	`month DESC`
`idx_client_targets_workspace_month`	`client_targets`	`workspace_name, month`
`idx_client_targets_contacts_progress`	`client_targets`	`contacts_progress DESC`
`idx_client_targets_leads_progress`	`client_targets`	`leads_progress DESC`
`idx_crm_delivery_workspace`	`crm_delivery_log`	`workspace_name`
`idx_crm_delivery_lead_email`	`crm_delivery_log`	`lead_email`
`idx_crm_delivery_success`	`crm_delivery_log`	`success`
`idx_crm_delivery_created`	`crm_delivery_log`	`created_at`
`idx_crm_delivery_pending_retry`	`crm_delivery_log`	`next_retry_at`
`idx_slack_notifications_unique`	`slack_notifications_sent`	`reply_id, notification_type`
`idx_slack_notifications_workspace`	`slack_notifications_sent`	`workspace_name`
`idx_lead_replies_workspace`	`lead_replies`	`workspace_name`
`idx_lead_replies_email`	`lead_replies`	`lead_email`
`idx_lead_replies_interested`	`lead_replies`	`is_interested`
`idx_lead_replies_date`	`lead_replies`	`reply_date DESC`
`idx_lead_replies_live_enabled`	`lead_replies`	`live_replies_enabled, reply_date DESC`
`idx_webhook_log_workspace`	`webhook_delivery_log`	`workspace_name`
`idx_webhook_log_event`	`webhook_delivery_log`	`event_type`
`idx_webhook_log_created`	`webhook_delivery_log`	`created_at DESC`
`idx_webhook_log_failures`	`webhook_delivery_log`	`success`
`idx_webhook_health_workspace`	`webhook_health`	`workspace_name`
`idx_webhook_health_unhealthy`	`webhook_health`	`is_healthy`
`idx_raw_contacts_source_type`	`raw_contacts`	`source_type`
`idx_raw_contacts_master_vendor`	`raw_contacts`	`master_vendor_id`
`idx_raw_contacts_master_dedup`	`raw_contacts`	`workspace_name, month, master_vendor_id`
`idx_raw_contacts_home_value_code`	`raw_contacts`	`home_value_code`
`idx_campaign_crm_routing_campaign_id`	`campaign_crm_routing`	`campaign_id`
`idx_campaign_crm_routing_workspace`	`campaign_crm_routing`	`workspace_name`
`idx_campaign_crm_routing_active`	`campaign_crm_routing`	`is_active`
`idx_scraping_jobs_csv_processing_status`	`scraping_jobs`	`csv_processing_status`

Entity Relationships (Core Pipeline)

erDiagram
    client_registry ||--o{ user_workspace_access : has_users
    client_registry ||--o{ scraping_jobs : runs
    client_registry ||--o{ raw_contacts : owns
    scraping_jobs ||--o{ raw_contacts : produces
    raw_contacts ||--o{ filtered_contacts : filters_to
    filtered_contacts ||--o{ verified_contacts : verifies_to
    verified_contacts }o--|| contact_batches : batched_into
    client_registry ||--o{ bison_account_sync : syncs_with
    client_registry ||--o{ client_leads : tracks
    client_registry ||--o{ client_targets : configures

Previous

API Reference

Next

Environment Variables