`db/migrations/009_linkedin.sql`

53 lines
-- 009_linkedin.sql · Steve's LinkedIn connection graph + agent surface

CREATE TABLE IF NOT EXISTS linkedin_connections (
  id            BIGSERIAL PRIMARY KEY,
  first_name    TEXT,
  last_name     TEXT,
  full_name     TEXT,
  profile_url   TEXT UNIQUE,
  email         TEXT,
  company       TEXT,
  position      TEXT,
  connected_on  DATE,
  imported_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  -- After matching, link to a pitch row if we found their corridor business
  matched_pitch_id BIGINT REFERENCES pitches(id) ON DELETE SET NULL,
  match_confidence  REAL,                  -- 0..1
  notes         TEXT
);

CREATE INDEX IF NOT EXISTS idx_li_company ON linkedin_connections (lower(company));
CREATE INDEX IF NOT EXISTS idx_li_matched ON linkedin_connections (matched_pitch_id);

-- Posts Steve plans to publish or has published, scheduled via the LinkedIn agent
CREATE TABLE IF NOT EXISTS linkedin_posts (
  id            BIGSERIAL PRIMARY KEY,
  topic         TEXT,                      -- 'corridor-insight'|'product-feature'|'project-reveal'
  hook          TEXT,                      -- first 200 chars (above-the-fold)
  body          TEXT,
  hashtags      TEXT[],
  image_path    TEXT,
  status        TEXT NOT NULL DEFAULT 'draft', -- 'draft'|'scheduled'|'posted'|'archived'
  scheduled_at  TIMESTAMPTZ,
  posted_at     TIMESTAMPTZ,
  external_url  TEXT,                      -- post URL after publish
  reactions     INTEGER DEFAULT 0,
  comments      INTEGER DEFAULT 0,
  reposts       INTEGER DEFAULT 0,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Engagement queue — businesses to comment on / engage with
CREATE TABLE IF NOT EXISTS linkedin_engagement_queue (
  id            BIGSERIAL PRIMARY KEY,
  pitch_id      BIGINT REFERENCES pitches(id) ON DELETE CASCADE,
  action        TEXT NOT NULL,             -- 'view-profile'|'like-post'|'comment'|'connect'|'send-dm'|'follow-up'
  scheduled_for DATE,
  completed_at  TIMESTAMPTZ,
  notes         TEXT,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_li_queue_scheduled ON linkedin_engagement_queue (scheduled_for) WHERE completed_at IS NULL;