worker.sh.example

#!/bin/bash
# Example: standalone scanner script for the hybrid deployment pattern.
#
# Runs on a fixed schedule via launchd (macOS) or systemd timer (Linux).
# Scans GitHub repos, upserts findings into SQLite, sends ntfy summaries.
# The AI agent reads the DB and does actual fixes — this script does NOT
# need an LLM; it's pure bash + gh CLI + sqlite3.
#
# Adapt: change REPOS, NTFY_TOPIC, ORG, and the triage/skip logic to
# match your project.
#
# See also:
#   examples/sqlite-state.md     — schema reference and query patterns
#   examples/kubestellar-fixer.md — full case study using this pattern
#   launchd/com.hive.scanner.plist.example — macOS scheduler

set -euo pipefail

# ── Configuration ────────────────────────────────────────────────────────────
DIR="$HOME/.my-project-fix-loop"
DB="$DIR/state.db"
LOCKFILE="$DIR/cycle.lock"
LOGFILE="$DIR/worker.log"
NTFY_TOPIC="your-ntfy-topic"          # change this
ORG="your-org"                         # change this
REPOS="repo-a repo-b repo-c"          # change this

log() { echo "[$(date '+%Y-%m-%d %H:%M:%S')] $*" >> "$LOGFILE"; }

ntfy() {
  local title="$1" body="$2" prio="${3:-default}" tags="${4:-robot}"
  curl -sf -o /dev/null -m 10 \
    -H "Title: $title" -H "Priority: $prio" -H "Tags: $tags" \
    -d "$body" "${NTFY_SERVER:-https://ntfy.sh}/$NTFY_TOPIC" 2>/dev/null || log "WARN: ntfy send failed"
}

# ── Lock (prevent overlapping cycles) ────────────────────────────────────────
if [ -f "$LOCKFILE" ]; then
  # macOS stat: -f %m gives mtime as epoch seconds
  LOCK_AGE=$(( $(date +%s) - $(stat -f %m "$LOCKFILE" 2>/dev/null || echo 0) ))
  if [ "$LOCK_AGE" -lt 600 ]; then
    log "Cycle locked (age ${LOCK_AGE}s < 600s). Skipping."
    exit 0
  fi
  log "Stale lock (age ${LOCK_AGE}s). Removing."
  rm -f "$LOCKFILE"
fi
echo $$ > "$LOCKFILE"
trap 'rm -f "$LOCKFILE"' EXIT

# ── Init SQLite (idempotent) ─────────────────────────────────────────────────
mkdir -p "$DIR"

init_db() {
  sqlite3 "$DB" <<'SQL'
CREATE TABLE IF NOT EXISTS items (
  repo TEXT NOT NULL,
  type TEXT NOT NULL,
  number INTEGER NOT NULL,
  title TEXT,
  author TEXT,
  created_at TEXT,
  status TEXT DEFAULT 'open',
  last_seen TEXT,
  fix_attempts INTEGER DEFAULT 0,
  fix_pr TEXT,
  notes TEXT,
  PRIMARY KEY (repo, type, number)
);
CREATE TABLE IF NOT EXISTS cycles (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  started_at TEXT NOT NULL,
  completed_at TEXT,
  total_issues INTEGER,
  total_prs INTEGER,
  items_fixed INTEGER DEFAULT 0,
  items_closed INTEGER DEFAULT 0
);
CREATE TABLE IF NOT EXISTS repo_counts (
  repo TEXT NOT NULL,
  cycle_id INTEGER NOT NULL,
  issues INTEGER,
  prs INTEGER,
  PRIMARY KEY (repo, cycle_id)
);
SQL
}

init_db

# ── Start cycle ──────────────────────────────────────────────────────────────
NOW=$(date -u +%Y-%m-%dT%H:%M:%SZ)
CYCLE_ID=$(sqlite3 "$DB" "INSERT INTO cycles (started_at) VALUES ('$NOW'); SELECT last_insert_rowid();")
log "=== Cycle $CYCLE_ID started ==="

# ── Scan repos ───────────────────────────────────────────────────────────────
TOTAL_I=0
TOTAL_P=0
SUMMARY=""

for repo in $REPOS; do
  # Fetch open issues via gh CLI
  ISSUES=$(gh issue list --repo "$ORG/$repo" --state open --limit 200 \
    --json number,title,author,createdAt 2>/dev/null || echo "[]")
  IC=$(echo "$ISSUES" | jq 'length' 2>/dev/null || echo 0)

  # Fetch open PRs
  PRS=$(gh pr list --repo "$ORG/$repo" --state open --limit 100 \
    --json number,title,author,createdAt 2>/dev/null || echo "[]")
  PC=$(echo "$PRS" | jq 'length' 2>/dev/null || echo 0)

  TOTAL_I=$((TOTAL_I + IC))
  TOTAL_P=$((TOTAL_P + PC))
  SUMMARY+="$repo: ${IC}i/${PC}pr  "

  # Save per-repo counts for this cycle
  sqlite3 "$DB" "INSERT OR REPLACE INTO repo_counts (repo, cycle_id, issues, prs) \
    VALUES ('$repo', $CYCLE_ID, $IC, $PC);"

  # Upsert issues — detect new items
  echo "$ISSUES" | jq -c '.[]' 2>/dev/null | while IFS= read -r item; do
    NUM=$(echo "$item" | jq -r '.number')
    TITLE=$(echo "$item" | jq -r '.title' | sed "s/'/''/g")
    AUTHOR=$(echo "$item" | jq -r '.author.login // .author')
    CREATED=$(echo "$item" | jq -r '.createdAt')

    EXISTS=$(sqlite3 "$DB" "SELECT status FROM items WHERE repo='$repo' AND type='issue' AND number=$NUM;" 2>/dev/null)
    if [ -z "$EXISTS" ]; then
      sqlite3 "$DB" "INSERT INTO items (repo, type, number, title, author, created_at, last_seen) \
        VALUES ('$repo','issue',$NUM,'$TITLE','$AUTHOR','$CREATED','$NOW');"
      log "NEW issue: $repo#$NUM — $TITLE"
    else
      sqlite3 "$DB" "UPDATE items SET last_seen='$NOW', title='$TITLE' \
        WHERE repo='$repo' AND type='issue' AND number=$NUM;"
    fi
  done

  # Upsert PRs
  echo "$PRS" | jq -c '.[]' 2>/dev/null | while IFS= read -r item; do
    NUM=$(echo "$item" | jq -r '.number')
    TITLE=$(echo "$item" | jq -r '.title' | sed "s/'/''/g")
    AUTHOR=$(echo "$item" | jq -r '.author.login // .author')
    CREATED=$(echo "$item" | jq -r '.createdAt')

    EXISTS=$(sqlite3 "$DB" "SELECT status FROM items WHERE repo='$repo' AND type='pr' AND number=$NUM;" 2>/dev/null)
    if [ -z "$EXISTS" ]; then
      sqlite3 "$DB" "INSERT INTO items (repo, type, number, title, author, created_at, last_seen) \
        VALUES ('$repo','pr',$NUM,'$TITLE','$AUTHOR','$CREATED','$NOW');"
      log "NEW pr: $repo#$NUM — $TITLE"
    else
      sqlite3 "$DB" "UPDATE items SET last_seen='$NOW', title='$TITLE' \
        WHERE repo='$repo' AND type='pr' AND number=$NUM;"
    fi
  done

  # Detect closed items (tracked as open but not seen in this scan)
  sqlite3 "$DB" "SELECT number, type, title FROM items \
    WHERE repo='$repo' AND status IN ('open','triaged','fixing') AND last_seen < '$NOW';" 2>/dev/null \
    | while IFS='|' read -r num typ title; do
      [ -z "$num" ] && continue
      sqlite3 "$DB" "UPDATE items SET status='closed' WHERE repo='$repo' AND type='$typ' AND number=$num;"
      log "CLOSED $typ: $repo#$num — $title"
      ntfy "✅ ${repo}#${num} closed" "${title}" "default" "white_check_mark"
    done
done

# ── Cycle complete ───────────────────────────────────────────────────────────
CLOSED_THIS=$(sqlite3 "$DB" "SELECT COUNT(*) FROM items WHERE status='closed' AND last_seen < '$NOW';" 2>/dev/null || echo 0)
sqlite3 "$DB" "UPDATE cycles SET completed_at='$(date -u +%Y-%m-%dT%H:%M:%SZ)', \
  total_issues=$TOTAL_I, total_prs=$TOTAL_P, items_closed=$CLOSED_THIS WHERE id=$CYCLE_ID;"

# ── ntfy: cycle summary ─────────────────────────────────────────────────────
ACTIONABLE=$(sqlite3 "$DB" "SELECT COUNT(*) FROM items WHERE status='open';" 2>/dev/null || echo "?")
ntfy "🔄 Scan #$CYCLE_ID — ${TOTAL_I}i/${TOTAL_P}p" \
  "$SUMMARY
Actionable: $ACTIONABLE  Closed this cycle: $CLOSED_THIS" "default" "repeat"

log "Cycle $CYCLE_ID complete: ${TOTAL_I}i/${TOTAL_P}p, closed=$CLOSED_THIS, actionable=$ACTIONABLE"

# ── Write trigger for AI agent ───────────────────────────────────────────────
# The AI agent (or a Copilot skill) can watch this file to know a fresh scan
# is available. It reads the DB, triages, and fixes actionable items.
echo "$CYCLE_ID" > "$DIR/trigger"