Reddit Brand Alert Workflow

import sqlite3
import requests
from datetime import datetime
import time

DB_PATH = "brand_mentions.db"
SCRAVIO_KEY = "YOUR_API_KEY"
BRAND_QUERY = "scavio OR scavio.com"
ALERT_WEBHOOK = "https://hooks.slack.com/services/YOUR/WEBHOOK"

NEGATIVE_KEYWORDS = ["broken", "terrible", "doesn't work", "does not work",
                      "waste", "refund", "scam", "useless", "garbage", "awful"]
FEATURE_KEYWORDS = ["please add", "would be great", "wish it had", "can you add",
                     "feature request", "would love", "should support"]
POSITIVE_KEYWORDS = ["love", "great", "works perfectly", "recommend",
                      "excellent", "fantastic", "best tool"]

def init_db():
    conn = sqlite3.connect(DB_PATH)
    conn.executescript("""
        CREATE TABLE IF NOT EXISTS brand_mentions (
            url TEXT PRIMARY KEY, title TEXT, subreddit TEXT,
            sentiment TEXT, intent TEXT, snippet TEXT, detected_at TEXT
        );
        CREATE TABLE IF NOT EXISTS daily_summaries (
            date TEXT, hour INTEGER, positive INTEGER, negative INTEGER, feature_request INTEGER, neutral INTEGER
        );
    """)
    conn.commit()
    return conn

def classify(text: str) -> tuple[str, str]:
    t = text.lower()
    sentiment = "neutral"
    intent = "mention"
    if any(k in t for k in NEGATIVE_KEYWORDS):
        sentiment = "negative"
    elif any(k in t for k in POSITIVE_KEYWORDS):
        sentiment = "positive"
    if any(k in t for k in FEATURE_KEYWORDS):
        intent = "feature_request"
    return sentiment, intent

def run():
    conn = init_db()
    now = datetime.utcnow().isoformat()
    resp = requests.post(
        "https://api.scavio.dev/api/v1/search",
        headers={"x-api-key": SCRAVIO_KEY},
        json={"query": BRAND_QUERY, "platform": "reddit", "num": 20}
    )
    resp.raise_for_status()
    results = resp.json().get("results", [])
    counts = {"positive": 0, "negative": 0, "feature_request": 0, "neutral": 0}
    for r in results:
        url = r.get("url", "")
        if not url:
            continue
        existing = conn.execute("SELECT 1 FROM brand_mentions WHERE url=?", (url,)).fetchone()
        if existing:
            continue
        title = r.get("title", "")
        snippet = r.get("snippet", "")
        combined = f"{title} {snippet}"
        sentiment, intent = classify(combined)
        subreddit = r.get("subreddit", r.get("source", ""))
        conn.execute("INSERT OR IGNORE INTO brand_mentions VALUES (?,?,?,?,?,?,?)",
                     (url, title, subreddit, sentiment, intent, snippet[:300], now))
        key = "feature_request" if intent == "feature_request" else sentiment
        counts[key] = counts.get(key, 0) + 1
        if sentiment == "negative" or intent == "feature_request":
            requests.post(ALERT_WEBHOOK, json={
                "text": f"Reddit alert [{sentiment}/{intent}] r/{subreddit}: {title} {url}"
            })
    conn.commit()
    print(f"Mentions: {counts}")

if __name__ == "__main__":
    run()

const Database = require('better-sqlite3');
const fetch = require('node-fetch');

const DB_PATH = 'brand_mentions.db';
const SCRAVIO_KEY = 'YOUR_API_KEY';
const BRAND_QUERY = 'scavio OR scavio.com';
const ALERT_WEBHOOK = 'https://hooks.slack.com/services/YOUR/WEBHOOK';
const NEGATIVE = ['broken','terrible','does not work','waste','refund','scam','useless'];
const FEATURE = ['please add','would be great','wish it had','feature request','would love'];
const POSITIVE = ['love','great','works perfectly','recommend','excellent'];

const db = new Database(DB_PATH);
db.exec(`
  CREATE TABLE IF NOT EXISTS brand_mentions (url TEXT PRIMARY KEY, title TEXT, subreddit TEXT, sentiment TEXT, intent TEXT, snippet TEXT, detected_at TEXT);
`);

function classify(text) {
  const t = text.toLowerCase();
  const sentiment = NEGATIVE.some(k => t.includes(k)) ? 'negative' : POSITIVE.some(k => t.includes(k)) ? 'positive' : 'neutral';
  const intent = FEATURE.some(k => t.includes(k)) ? 'feature_request' : 'mention';
  return { sentiment, intent };
}

async function run() {
  const now = new Date().toISOString();
  const res = await fetch('https://api.scavio.dev/api/v1/search', {
    method: 'POST',
    headers: { 'x-api-key': SCRAVIO_KEY, 'Content-Type': 'application/json' },
    body: JSON.stringify({ query: BRAND_QUERY, platform: 'reddit', num: 20 })
  });
  const results = (await res.json()).results || [];
  for (const r of results) {
    if (!r.url || db.prepare('SELECT 1 FROM brand_mentions WHERE url=?').get(r.url)) continue;
    const { sentiment, intent } = classify(`${r.title} ${r.snippet}`);
    db.prepare('INSERT OR IGNORE INTO brand_mentions VALUES (?,?,?,?,?,?,?)').run(
      r.url, r.title, r.subreddit || '', sentiment, intent, (r.snippet || '').slice(0, 300), now
    );
    if (sentiment === 'negative' || intent === 'feature_request') {
      await fetch(ALERT_WEBHOOK, {
        method: 'POST', headers: { 'Content-Type': 'application/json' },
        body: JSON.stringify({ text: `Reddit alert [${sentiment}/${intent}]: ${r.title} ${r.url}` })
      });
    }
  }
}

run().catch(console.error);