Technical Reference
Database Schema
Every table, column, index, and constraint from SQL migrations
82 migrations | 64 tables | 238 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 |
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() |
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 |
idx_tasks_status | tasks | status |
idx_tasks_assignee_id | tasks | assignee_id |
idx_tasks_priority | tasks | priority |
idx_tasks_due_date | tasks | due_date |
idx_tasks_created_by | tasks | created_by |
idx_tasks_created_at | tasks | created_at DESC |
idx_lead_replies_response_time | lead_replies | workspace_name, our_reply_sent_at |
idx_auto_reply_log_workspace | auto_reply_log | workspace_name, created_at DESC |
idx_auto_reply_log_rate_limit | auto_reply_log | workspace_name, sent_successfully, created_at |
idx_reply_templates_workspace | reply_templates | workspace_name |
idx_sent_replies_workspace | sent_replies | workspace_name, created_at DESC |
idx_sent_replies_bison_reply | sent_replies | bison_reply_id |
idx_sma_policies_workspace | sma_policies | workspace_name |
idx_client_metrics_workspace_date | client_metrics | workspace_name, metric_date DESC |
idx_client_metrics_unique | client_metrics | workspace_name, metric_date, metric_type |
idx_monthly_daily_revenue_month | monthly_daily_revenue_history | month_year, day |
idx_workspace_metrics_last_bounce_alert | workspace_metrics | workspace_name, last_bounce_alert_at |
idx_monthly_daily_revenue_unique | monthly_daily_revenue_history | month_year, day |
idx_client_leads_workspace_email | client_leads | workspace_name, lead_email |
idx_lead_replies_workspace_email_date | lead_replies | workspace_name, lead_email, reply_date DESC |
idx_wsc_type | workspace_stats_cache | cache_type |
idx_wsc_workspace | workspace_stats_cache | workspace_name |
idx_wsc_date_desc | workspace_stats_cache | stat_date DESC |
idx_wsc_type_ws | workspace_stats_cache | cache_type, workspace_name |
idx_raw_contacts_month | raw_contacts | month |
idx_verified_contacts_month | verified_contacts | month |
idx_pipeline_stats_cache_month | pipeline_stats_cache | month |
idx_wds_date | workspace_daily_stats | stat_date DESC |
idx_wds_ws_date | workspace_daily_stats | workspace_name, stat_date DESC |
idx_email_accounts_mx_checked | email_accounts | mx_checked_at |
idx_infra_orders_workspace | infrastructure_orders | workspace_name |
idx_infra_orders_stage | infrastructure_orders | stage |
idx_infra_orders_workspace_stage | infrastructure_orders | workspace_name, stage |
idx_order_events_order | infrastructure_order_events | order_id |
idx_onboarding_drafts_status | onboarding_drafts | status |
idx_onboarding_drafts_created_by | onboarding_drafts | created_by |
idx_agent_threads_user | agent_threads | user_id |
idx_agent_threads_updated | agent_threads | updated_at DESC |
idx_agent_messages_thread | agent_messages | thread_id, created_at |
idx_email_accounts_domain | email_accounts | domain |
idx_email_accounts_ws_domain | email_accounts | workspace_name, domain |
idx_task_events_status_created | task_events | status, created_at DESC |
idx_task_events_created | task_events | created_at |
idx_hof_nominations_category | hall_of_fame_nominations | category |
idx_hof_nominations_vote_count | hall_of_fame_nominations | vote_count DESC |
idx_hof_nominations_created | hall_of_fame_nominations | created_at DESC |
idx_hof_nominations_workspace | hall_of_fame_nominations | workspace_name |
idx_hof_votes_nomination | hall_of_fame_votes | nomination_id |
idx_hof_votes_user | hall_of_fame_votes | user_email |
idx_tasks_source_kind | tasks | (source->>'kind' |
idx_user_profiles_is_team_member | user_profiles | is_team_member |
idx_task_activity_task_id_created_at | task_activity | task_id, created_at DESC |
idx_task_activity_event_type | task_activity | event_type |
idx_lead_replies_intent | lead_replies | intent |
idx_ai_gen_log_workspace | ai_generation_log | workspace_name, created_at DESC |
idx_prompt_eval_workspace | prompt_eval_results | workspace_name, created_at DESC |
uq_crm_destinations_route | crm_destinations | workspace_name, crm_type, external_api_url, COALESCE(campaign_id, -1 |
idx_crm_destinations_workspace | crm_destinations | workspace_name |
idx_crm_destinations_campaign | crm_destinations | campaign_id |
idx_crm_delivery_log_idempotency | crm_delivery_log | bison_reply_id, destination_id |
idx_crm_delivery_log_parent | crm_delivery_log | parent_delivery_id |
idx_client_registry_v2_routing | client_registry | workspace_name |
idx_raw_contacts_workspace_month_email | raw_contacts | workspace_name, month |
idx_raw_contacts_workspace_master_vendor | raw_contacts | workspace_name |
idx_resources_category | resources | category |
idx_resources_type | resources | resource_type |
idx_resources_created_at | resources | created_at DESC |
idx_resource_requests_status_created | resource_requests | status, created_at DESC |
idx_resource_requests_requester | resource_requests | requester_user_id, created_at DESC |