Settings

Run in Supabase → SQL Editor. Safe to re-run — uses IF NOT EXISTS and ADD COLUMN IF NOT EXISTS.

-- Run this in your Supabase SQL Editor to set up all tables

-- Core tables
CREATE TABLE IF NOT EXISTS businesses (
  id            UUID        DEFAULT gen_random_uuid() PRIMARY KEY,
  name          TEXT        NOT NULL,
  phone         TEXT,
  address       TEXT,
  has_website   BOOLEAN     DEFAULT false,
  website_score INT,
  website       TEXT,
  niche         TEXT,
  city          TEXT,
  place_id      TEXT,
  status        TEXT        DEFAULT 'prospect',
  created_at    TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(name, city)
);

CREATE TABLE IF NOT EXISTS sites (
  id            UUID        DEFAULT gen_random_uuid() PRIMARY KEY,
  slug          TEXT        UNIQUE NOT NULL,
  business_name TEXT        NOT NULL,
  config        JSONB       NOT NULL,
  photos        TEXT[]      DEFAULT '{}',
  place_id      TEXT,
  status        TEXT        DEFAULT 'preview' CHECK (status IN ('preview','sold')),
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS outreach_log (
  id                  UUID        DEFAULT gen_random_uuid() PRIMARY KEY,
  business_id         UUID        REFERENCES businesses(id),
  method              TEXT        NOT NULL,
  message             TEXT        NOT NULL,
  sent_at             TIMESTAMPTZ DEFAULT NOW(),
  delivery_status     TEXT,
  twilio_sid          TEXT,
  twilio_number_used  TEXT,
  message_variation   INT,
  responded           BOOLEAN     DEFAULT false
);

CREATE TABLE IF NOT EXISTS responses (
  id          UUID        DEFAULT gen_random_uuid() PRIMARY KEY,
  business_id UUID        REFERENCES businesses(id),
  from_number TEXT,
  to_number   TEXT,
  message     TEXT        NOT NULL,
  direction   TEXT        NOT NULL CHECK (direction IN ('inbound','outbound')),
  created_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS sales (
  id                UUID        DEFAULT gen_random_uuid() PRIMARY KEY,
  business_id       UUID        REFERENCES businesses(id),
  amount            NUMERIC     NOT NULL,
  stripe_payment_id TEXT,
  created_at        TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS follow_ups (
  id            UUID        DEFAULT gen_random_uuid() PRIMARY KEY,
  business_id   UUID        REFERENCES businesses(id),
  site_slug     TEXT,
  scheduled_for TIMESTAMPTZ NOT NULL,
  sent_at       TIMESTAMPTZ,
  status        TEXT        DEFAULT 'pending' CHECK (status IN ('pending','sent','skipped','failed')),
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS cron_runs (
  id                  UUID        DEFAULT gen_random_uuid() PRIMARY KEY,
  date                DATE        NOT NULL,
  niche               TEXT,
  city                TEXT,
  businesses_found    INT         DEFAULT 0,
  configs_generated   INT         DEFAULT 0,
  texts_sent          INT         DEFAULT 0,
  status              TEXT        DEFAULT 'running',
  created_at          TIMESTAMPTZ DEFAULT NOW()
);

-- Disable RLS on all tables
ALTER TABLE businesses    DISABLE ROW LEVEL SECURITY;
ALTER TABLE sites         DISABLE ROW LEVEL SECURITY;
ALTER TABLE outreach_log  DISABLE ROW LEVEL SECURITY;
ALTER TABLE responses     DISABLE ROW LEVEL SECURITY;
ALTER TABLE sales         DISABLE ROW LEVEL SECURITY;
ALTER TABLE follow_ups    DISABLE ROW LEVEL SECURITY;
ALTER TABLE cron_runs     DISABLE ROW LEVEL SECURITY;

-- Add columns that may be missing from existing tables
ALTER TABLE businesses   ADD COLUMN IF NOT EXISTS place_id TEXT;
ALTER TABLE outreach_log ADD COLUMN IF NOT EXISTS twilio_number_used TEXT;
ALTER TABLE outreach_log ADD COLUMN IF NOT EXISTS message_variation INT;
ALTER TABLE outreach_log ADD COLUMN IF NOT EXISTS responded BOOLEAN DEFAULT false;