schema.sql

-- Enable UUID extension
create extension if not exists "uuid-ossp";

-- Main businesses table
create table businesses (
  id uuid primary key default uuid_generate_v4(),

  -- Raw CSV data
  name text not null,
  url text,
  email text,
  phone text,
  address text,
  city text,
  state text,
  business_type text,
  rating numeric,
  review_count integer,

  -- Pipeline state
  status text not null default 'raw',
  created_at timestamptz default now(),
  updated_at timestamptz default now(),

  -- Viability classification (Module 2 output)
  viability_score integer,
  viability_reason text,
  viability_flags jsonb, -- array of detected signals

  -- Crawl data (Module 3 output)
  crawl_data jsonb, -- raw Firecrawl response
  crawl_text text, -- extracted page text
  crawl_screenshots text[], -- screenshot URLs if available
  crawl_error text,
  crawled_at timestamptz,

  -- Lead quality score (Module 3 output, post-crawl)
  quality_score integer,
  quality_signals jsonb, -- which positive signals were detected
  quality_disqualifiers jsonb, -- which disqualifiers were checked
  quality_summary text, -- Claude's 2-3 sentence summary of why this is/isn't a good lead

  -- Generated site (Module 4 output)
  generated_site_html text, -- the full HTML of the generated site
  generated_site_path text, -- e.g. /sites/uuid/index.html
  generated_site_url text, -- e.g. http://localhost:3000/sites/uuid
  generation_prompt text, -- the prompt used, for debugging
  generated_at timestamptz,

  -- Outreach (Module 4 output)
  outreach_email_subject text,
  outreach_email_body text,

  -- Error tracking
  error_message text,
  error_module text
);

-- Index for pipeline polling (critical for performance)
create index businesses_status_idx on businesses(status);
create index businesses_created_at_idx on businesses(created_at);

-- Processing log for debugging
create table processing_log (
  id uuid primary key default uuid_generate_v4(),
  business_id uuid references businesses(id),
  module text not null,
  action text not null,
  details jsonb,
  created_at timestamptz default now()
);