`db/migrations/001_init.sql`

121 lines
-- Ventura Claw Leads v0.1 — directory + lead-routing for unregulated brick-and-
-- mortar businesses on Ventura Blvd. Mirrors the NPH schema where the shape
-- works; diverges where the licensed-trade specifics don't apply.
--
-- HARD RULES this schema enforces:
--   - businesses.vertical is constrained to the 8-vertical green list. Adding
--     any "regulated" vertical (medical / legal / financial / real-estate /
--     contracting) requires a new migration that explicitly amends the CHECK,
--     forcing a code review where someone sees the regulatory issue.
--   - No application_fee_amount / Stripe Connect tables. Consumers never pay
--     this platform; businesses pay subscription only. Per-lead billing is
--     metered against the subscription, not collected from the consumer.

CREATE TABLE IF NOT EXISTS businesses (
  id              BIGSERIAL PRIMARY KEY,
  slug            TEXT UNIQUE NOT NULL,
  business_name   TEXT NOT NULL,
  vertical        TEXT NOT NULL CHECK (vertical IN (
    'food',          -- restaurants, cafes, bakeries, delis, food trucks
    'beauty',        -- hair, nails, brows/lashes, barbers, makeup, tanning, non-medical spa
    'retail',        -- boutiques, jewelry, shoes, accessories, home goods, gift shops
    'fitness',       -- yoga, pilates, gyms, dance, martial arts, personal trainers
    'pet_services',  -- groomers, walkers, daycare, pet sitters, training, supply
    'auto_detail',   -- detailing, hand car wash, window tint, mobile detailers
    'cleaning',      -- house cleaners, organizers, residential moving, staging
    'creative'       -- photographers, DJs, event planners, florists, designers
  )),
  description     TEXT,
  headline        TEXT,

  -- Address (Ventura Blvd corridor)
  street          TEXT,
  city            TEXT,                  -- Sherman Oaks, Studio City, Encino, Tarzana, Woodland Hills
  state           TEXT DEFAULT 'CA',
  zip             TEXT,
  neighborhood    TEXT,                  -- finer cut than city
  latitude        DOUBLE PRECISION,
  longitude       DOUBLE PRECISION,

  -- Contact + socials (no consumer payment surface)
  phone           TEXT,
  email           TEXT,
  website         TEXT,
  instagram_handle TEXT,

  -- Subscription tier (business-side billing). 'free' = directory shell only.
  tier            TEXT NOT NULL DEFAULT 'free' CHECK (tier IN ('free','starter','standard','premier')),
  subscription_status TEXT NOT NULL DEFAULT 'inactive',
  stripe_customer_id  TEXT,
  stripe_subscription_id TEXT,

  -- Claim flow (lifted from NPH).
  claim_status    TEXT NOT NULL DEFAULT 'unclaimed' CHECK (claim_status IN ('unclaimed','self','claimed')),
  claimed_by_email TEXT,
  claimed_at      TIMESTAMPTZ,

  -- Lifecycle
  status          TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active','pending','hidden','removed')),
  verified        BOOLEAN NOT NULL DEFAULT false,
  source          TEXT NOT NULL DEFAULT 'seed',  -- 'seed', 'public_records', 'self_signup'

  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS businesses_vertical_idx ON businesses (vertical) WHERE status = 'active';
CREATE INDEX IF NOT EXISTS businesses_city_idx ON businesses (city) WHERE status = 'active';
CREATE INDEX IF NOT EXISTS businesses_neighborhood_idx ON businesses (neighborhood) WHERE status = 'active';
CREATE INDEX IF NOT EXISTS businesses_geo_idx ON businesses (latitude, longitude) WHERE latitude IS NOT NULL;

-- Lead-capture queue per business. The lead is the actual product — when a
-- consumer fills the contact form, the business pays a per-lead fee against
-- their subscription (charged at month-end, not at capture time).
CREATE TABLE IF NOT EXISTS business_interest (
  id              BIGSERIAL PRIMARY KEY,
  business_id     BIGINT NOT NULL REFERENCES businesses(id) ON DELETE CASCADE,
  consumer_name   TEXT,
  consumer_email  TEXT NOT NULL,
  consumer_phone  TEXT,
  message         TEXT,
  zip             TEXT,
  source          TEXT NOT NULL DEFAULT 'profile_form',  -- 'profile_form', 'home_search', 'map_pin'
  ip_hash         TEXT,
  user_agent      TEXT,
  -- Per-vertical extras: party_size for restaurants, service_kind for beauty,
  -- preferred_date, etc. Schema-light JSON so we don't migrate per vertical.
  extra_fields    JSONB DEFAULT '{}'::jsonb,

  -- Lifecycle
  delivered_at    TIMESTAMPTZ DEFAULT now(),     -- when we routed it to the business
  delivery_email  TEXT,                          -- the email we sent the lead to
  delivery_msg_id TEXT,                          -- SMTP message-id for audit
  billed_at       TIMESTAMPTZ,                   -- when we metered the per-lead fee
  bill_amount_cents INTEGER,
  consumer_unsubscribed BOOLEAN NOT NULL DEFAULT false,

  created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS business_interest_business_idx ON business_interest (business_id);
CREATE INDEX IF NOT EXISTS business_interest_unbilled_idx ON business_interest (billed_at) WHERE billed_at IS NULL;
CREATE INDEX IF NOT EXISTS business_interest_email_idx ON business_interest (consumer_email);

-- Express session store. connect-pg-simple expects this exact shape.
CREATE TABLE IF NOT EXISTS "session" (
  "sid" varchar NOT NULL COLLATE "default",
  "sess" json NOT NULL,
  "expire" timestamp(6) NOT NULL,
  CONSTRAINT "session_pkey" PRIMARY KEY ("sid")
);
CREATE INDEX IF NOT EXISTS "IDX_session_expire" ON "session" ("expire");

-- updated_at autotouch for businesses.
CREATE OR REPLACE FUNCTION vcl_touch_updated_at() RETURNS TRIGGER AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS businesses_updated_at ON businesses;
CREATE TRIGGER businesses_updated_at BEFORE UPDATE ON businesses
  FOR EACH ROW EXECUTE FUNCTION vcl_touch_updated_at();