`db/orbit-schema.sql`

123 lines
-- Orbit: Kalshi <-> Petition Bridge + RSS News Intelligence
-- Part of Patty - The Petition Specialist
-- Database: postgresql://dw_admin@127.0.0.1:5432/  # password in .env.local (DATABASE_URL)
--   patty

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Kalshi prediction markets synced from Ken agent
CREATE TABLE IF NOT EXISTS orbit_markets (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  ticker TEXT NOT NULL UNIQUE,
  event_ticker TEXT,
  title TEXT NOT NULL,
  subtitle TEXT,
  category TEXT,
  yes_bid INT,
  yes_ask INT,
  no_bid INT,
  no_ask INT,
  last_price INT,
  volume INT,
  volume_24h INT,
  open_interest INT,
  status TEXT DEFAULT 'open',
  close_time TIMESTAMPTZ,
  petition_relevance REAL DEFAULT 0, -- 0-100 AI-scored relevance to advocacy
  suggested_petition TEXT, -- AI-generated petition angle
  suggested_target TEXT,
  raw_data JSONB,
  last_synced_at TIMESTAMPTZ DEFAULT NOW(),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Links between markets and petitions
CREATE TABLE IF NOT EXISTS orbit_links (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  market_id UUID REFERENCES orbit_markets(id) ON DELETE CASCADE,
  petition_id UUID REFERENCES petitions(id) ON DELETE CASCADE,
  link_type TEXT NOT NULL DEFAULT 'influence', -- influence, counter, support
  strength REAL DEFAULT 0.5, -- 0-1 connection strength
  ai_reasoning TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(market_id, petition_id)
);

-- RSS/Atom feed sources
CREATE TABLE IF NOT EXISTS orbit_feeds (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT NOT NULL,
  url TEXT NOT NULL UNIQUE,
  feed_type TEXT NOT NULL DEFAULT 'rss', -- rss, atom, json
  category TEXT DEFAULT 'national', -- national, local, wire
  station TEXT, -- CNN, Fox, NBC, local call sign
  market TEXT, -- DMA market for local (New York, Los Angeles, etc.)
  is_active BOOLEAN DEFAULT TRUE,
  last_fetched_at TIMESTAMPTZ,
  fetch_interval_min INT DEFAULT 30,
  article_count INT DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Articles fetched from RSS feeds
CREATE TABLE IF NOT EXISTS orbit_articles (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  feed_id UUID REFERENCES orbit_feeds(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  url TEXT NOT NULL,
  summary TEXT,
  published_at TIMESTAMPTZ,
  sentiment TEXT, -- positive, negative, neutral, mixed
  sentiment_score REAL, -- -1 to 1
  relevance_score REAL DEFAULT 0, -- 0-100 relevance to petition topics
  matched_markets TEXT[], -- Kalshi market tickers this article relates to
  matched_petitions UUID[], -- Petition IDs this article relates to
  tags TEXT[],
  raw_data JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(feed_id, url)
);

-- Cross-platform petition posts
CREATE TABLE IF NOT EXISTS orbit_posts (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  petition_id UUID REFERENCES petitions(id) ON DELETE CASCADE,
  platform TEXT NOT NULL, -- moveon, change_org, we_the_people, custom
  external_url TEXT,
  status TEXT DEFAULT 'draft', -- draft, posted, failed, archived
  post_data JSONB, -- platform-specific payload
  response_data JSONB, -- platform response
  posted_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_orbit_markets_category ON orbit_markets(category);
CREATE INDEX IF NOT EXISTS idx_orbit_markets_relevance ON orbit_markets(petition_relevance DESC);
CREATE INDEX IF NOT EXISTS idx_orbit_markets_status ON orbit_markets(status);
CREATE INDEX IF NOT EXISTS idx_orbit_links_market ON orbit_links(market_id);
CREATE INDEX IF NOT EXISTS idx_orbit_links_petition ON orbit_links(petition_id);
CREATE INDEX IF NOT EXISTS idx_orbit_articles_sentiment ON orbit_articles(sentiment_score);
CREATE INDEX IF NOT EXISTS idx_orbit_articles_published ON orbit_articles(published_at DESC);
CREATE INDEX IF NOT EXISTS idx_orbit_articles_feed ON orbit_articles(feed_id);
CREATE INDEX IF NOT EXISTS idx_orbit_feeds_active ON orbit_feeds(is_active);
CREATE INDEX IF NOT EXISTS idx_orbit_posts_platform ON orbit_posts(platform);
CREATE INDEX IF NOT EXISTS idx_orbit_posts_petition ON orbit_posts(petition_id);

-- Trigger for orbit_markets updated_at
CREATE OR REPLACE FUNCTION update_updated_at_column() RETURNS TRIGGER AS $$
BEGIN NEW.updated_at = NOW(); RETURN NEW; END;
$$ LANGUAGE plpgsql;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_trigger WHERE tgname = 'tr_orbit_markets_updated'
  ) THEN
    CREATE TRIGGER tr_orbit_markets_updated
      BEFORE UPDATE ON orbit_markets
      FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
  END IF;
END $$;