marketing/migrations/0001_outreach.sql

-- Initial outreach/marketing schema. Port from lp.live entities/outreach-*/schema + usage in surfaces/my/outreach.ts

CREATE TABLE IF NOT EXISTS outreach_settings (
  id BIGSERIAL PRIMARY KEY CHECK (id=1),
  max_per_minute INT NOT NULL DEFAULT 10,
  max_per_hour INT NOT NULL DEFAULT 60,
  max_per_day INT NOT NULL DEFAULT 200,
  min_gap_seconds INT NOT NULL DEFAULT 30,
  require_batch_confirm BOOLEAN NOT NULL DEFAULT true,
  quiet_hours_start TEXT,
  quiet_hours_end TEXT,
  paused BOOLEAN NOT NULL DEFAULT false,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS outreach_batches (
  id BIGSERIAL PRIMARY KEY,
  query_json JSONB NOT NULL,
  template TEXT NOT NULL,
  vars_json JSONB,
  status TEXT NOT NULL DEFAULT 'draft', -- draft, previewed, sent, cancelled
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  sent_at TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS outreach_batch_items (
  id BIGSERIAL PRIMARY KEY,
  batch_id BIGINT NOT NULL REFERENCES outreach_batches(id) ON DELETE CASCADE,
  client_id BIGINT,
  client_alias TEXT,
  rendered_body TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending', -- pending, sent, failed, skipped
  sent_at TIMESTAMPTZ,
  error TEXT
);

CREATE INDEX IF NOT EXISTS idx_batch_items_sent ON outreach_batch_items(sent_at);
CREATE INDEX IF NOT EXISTS idx_batch_items_batch ON outreach_batch_items(batch_id);