Rails Long-Tail Discovery Pipeline Implementation Plan

For agentic workers: REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (- [ ]) syntax for tracking.

Goal: Build a Ruby-based data-ingest pipeline that surfaces 1,500-5,000 long-tail Rails-using companies across ten verticals, classifies them via Sonnet subagents, and produces a scorecard for Rick to pick one niche to target.

Architecture: Ruby scrapers write to a single SQLite database (data/rails_longtail.sqlite). Each scraper is a thin wrapper over a focused source (GitHub API, public HTML pages, or RSS/API feeds) that upserts companies and appends signals. Classification and exploratory web search are orchestrated through Claude Code (Sonnet subagents via the Agent tool); their outputs are JSONL files consumed by a merge script. SQL views drive the scorecard. Phases 0-2 of the spec are in scope; Phase 3 (deep dive in the chosen niche) is out of scope and becomes a follow-up plan after the niche is picked.

Tech Stack: Ruby 3.2+, Bundler, SQLite3, Nokogiri (HTML), HTTParty (REST), Feedjira (RSS), Minitest (tests), VCR + WebMock (HTTP stubbing), rake (task runner). Classification orchestrated via Claude Code Agent tool (no Anthropic SDK dependency in Ruby code).

Scope: This plan covers spec Sections 1-9 (Phases 0-2 only). Spec Section 8 (Phase 3 Deep Dive) is a human-manual process gated on Phase 2’s niche pick; a thin runbook template is included as Task 15 but the detailed vertical-specific sources are deferred to a follow-up plan.

Project layout (created in Task 1):

tooling/rails_longtail/
  Gemfile
  Rakefile
  schema.sql
  README.md
  bin/
    bootstrap_db
    ingest_github
    ingest_hn_hiring
    ingest_job_feeds
    ingest_board_dorks
    ingest_rails_foundation
    ingest_conferences
    ingest_gems
    merge_classifications
    compute_long_tail_fit
    flag_audit
    run_scorecard
    export_csvs
  lib/
    rails_longtail.rb          -- top-level requires
    rails_longtail/
      db.rb                    -- SQLite connection + schema loader
      company.rb               -- upsert_by_domain
      signal.rb                -- insert_signal
      source_run.rb            -- start/finish run logging
      github.rb                -- GitHub REST + code-search client
      hn_hiring.rb             -- HN Algolia search
      job_feeds.rb             -- WWR RSS + RemoteOK JSON
      board_dorks.rb           -- DDG results for site:greenhouse.io etc.
      rails_foundation.rb      -- scrape member/sponsor list
      conferences.rb           -- scrape sponsor pages
      gems.rb                  -- rubygems + github org lookup
      classification.rb        -- consume subagent JSONL → classifications table
      long_tail_fit.rb
      audit.rb
      scorecard.rb
  test/
    test_helper.rb
    db_test.rb
    company_test.rb
    signal_test.rb
    github_test.rb
    hn_hiring_test.rb
    ...                        -- one per lib/ file
    fixtures/                  -- VCR cassettes + sample HTML
  docs/
    vertical_definitions.md
    business_model_taxonomy.md
    classification_runbook.md
    classification_prompt.md
    web_search_runbook.md
    phase3_runbook.md
  data/.keep                   -- rails_longtail.sqlite lives here, gitignored
  exports/.keep                -- CSV exports, gitignored
  .gitignore

Task 1: Project skeleton, Gemfile, test harness

Files:

Run from repo root:

mkdir -p tooling/rails_longtail/{bin,lib/rails_longtail,test/fixtures,docs,data,exports}
touch tooling/rails_longtail/data/.keep tooling/rails_longtail/exports/.keep

Create tooling/rails_longtail/Gemfile:

# frozen_string_literal: true

source 'https://rubygems.org'

ruby '>= 3.2.0'

gem 'sqlite3',  '~> 1.7'
gem 'httparty', '~> 0.22'
gem 'nokogiri', '~> 1.16'
gem 'feedjira', '~> 3.2'

group :test, :development do
  gem 'minitest', '~> 5.22'
  gem 'vcr',      '~> 6.2'
  gem 'webmock',  '~> 3.23'
  gem 'rake',     '~> 13.2'
end

Create tooling/rails_longtail/.gitignore:

/data/*.sqlite
/data/*.sqlite-journal
/exports/*.csv
/.bundle/
/vendor/bundle/

Create tooling/rails_longtail/test/test_helper.rb:

# frozen_string_literal: true

require 'minitest/autorun'
require 'vcr'
require 'webmock/minitest'
require 'rails_longtail'

VCR.configure do |c|
  c.cassette_library_dir = File.expand_path('fixtures/vcr_cassettes', __dir__)
  c.hook_into :webmock
  c.default_cassette_options = { record: :none, match_requests_on: %i[method uri] }
  c.filter_sensitive_data('<GITHUB_TOKEN>') { ENV['GITHUB_TOKEN'] }
end

Create tooling/rails_longtail/lib/rails_longtail.rb:

# frozen_string_literal: true

require 'sqlite3'

module RailsLongtail
  ROOT = File.expand_path('..', __dir__)
  DB_PATH = File.join(ROOT, 'data', 'rails_longtail.sqlite')
end

require_relative 'rails_longtail/db'

Create tooling/rails_longtail/Rakefile:

# frozen_string_literal: true

require 'rake/testtask'

Rake::TestTask.new do |t|
  t.libs << 'lib' << 'test'
  t.pattern = 'test/**/*_test.rb'
  t.warning = false
end

task default: :test

Run: cd tooling/rails_longtail && bundle install Expected: gems install cleanly; Gemfile.lock produced.

cd /Users/me/work/joyrudder-dot-com
git add tooling/rails_longtail
git commit -m "scaffold rails long-tail discovery pipeline skeleton"

Task 2: Schema, bootstrap_db, DB helper

Files:

Create tooling/rails_longtail/schema.sql — copy verbatim from spec Section 5:

CREATE TABLE IF NOT EXISTS companies (
  id              INTEGER PRIMARY KEY,
  domain          TEXT    UNIQUE NOT NULL,
  name            TEXT    NOT NULL,
  headcount_tier  TEXT,
  hq_country      TEXT,
  hq_region       TEXT,
  website_url     TEXT,
  linkedin_url    TEXT,
  github_org      TEXT,
  long_tail_fit   INTEGER,
  notes           TEXT,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS signals (
  id             INTEGER PRIMARY KEY,
  company_id     INTEGER NOT NULL REFERENCES companies(id),
  source         TEXT    NOT NULL,
  signal_type    TEXT    NOT NULL,
  signal_date    DATE,
  evidence_url   TEXT,
  raw_text       TEXT,
  captured_at    DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_signals_company ON signals(company_id);
CREATE INDEX IF NOT EXISTS idx_signals_source  ON signals(source);
CREATE INDEX IF NOT EXISTS idx_signals_date    ON signals(signal_date);

CREATE TABLE IF NOT EXISTS classifications (
  id               INTEGER PRIMARY KEY,
  company_id       INTEGER NOT NULL UNIQUE REFERENCES companies(id),
  primary_vertical TEXT,
  business_model   TEXT,
  confidence       REAL,
  llm_notes        TEXT,
  classified_by    TEXT,
  classified_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  audited          INTEGER DEFAULT 0,
  audit_verdict    TEXT
);

CREATE TABLE IF NOT EXISTS source_runs (
  id           INTEGER PRIMARY KEY,
  source       TEXT    NOT NULL,
  started_at   DATETIME,
  finished_at  DATETIME,
  rows_added   INTEGER,
  notes        TEXT
);

Create tooling/rails_longtail/test/db_test.rb:

# frozen_string_literal: true

require 'test_helper'
require 'fileutils'
require 'tmpdir'

class DbTest < Minitest::Test
  def setup
    @tmp = Dir.mktmpdir
    @db_path = File.join(@tmp, 'test.sqlite')
  end

  def teardown
    FileUtils.rm_rf(@tmp)
  end

  def test_open_creates_schema
    db = RailsLongtail::DB.open(@db_path)

    tables = db.execute("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name")
    assert_equal %w[classifications companies signals source_runs], tables.flatten.sort
  ensure
    db&.close
  end

  def test_open_is_idempotent
    db1 = RailsLongtail::DB.open(@db_path)
    db1.close
    db2 = RailsLongtail::DB.open(@db_path)
    tables = db2.execute("SELECT name FROM sqlite_master WHERE type='table'").flatten
    assert_includes tables, 'companies'
    db2.close
  end
end

Run: cd tooling/rails_longtail && bundle exec rake test TEST=test/db_test.rb Expected: FAIL with uninitialized constant RailsLongtail::DB.

Create tooling/rails_longtail/lib/rails_longtail/db.rb:

# frozen_string_literal: true

require 'sqlite3'

module RailsLongtail
  module DB
    SCHEMA_PATH = File.expand_path('../../schema.sql', __dir__)

    def self.open(path = RailsLongtail::DB_PATH)
      db = SQLite3::Database.new(path)
      db.execute('PRAGMA foreign_keys = ON')
      db.execute_batch(File.read(SCHEMA_PATH))
      db
    end
  end
end

Run: bundle exec rake test TEST=test/db_test.rb Expected: 2 tests, 0 failures.

Create tooling/rails_longtail/bin/bootstrap_db:

#!/usr/bin/env ruby
# frozen_string_literal: true

$LOAD_PATH.unshift(File.expand_path('../lib', __dir__))
require 'rails_longtail'

db = RailsLongtail::DB.open
puts "Initialized #{RailsLongtail::DB_PATH}"
db.close

Make executable: chmod +x tooling/rails_longtail/bin/bootstrap_db

Run: cd tooling/rails_longtail && bin/bootstrap_db Expected: prints Initialized .../data/rails_longtail.sqlite; file exists; sqlite3 data/rails_longtail.sqlite ".tables" lists 4 tables.

git add tooling/rails_longtail/schema.sql tooling/rails_longtail/lib/rails_longtail/db.rb \
  tooling/rails_longtail/bin/bootstrap_db tooling/rails_longtail/test/db_test.rb
git commit -m "add sqlite schema and bootstrap_db"

Task 3: Company + Signal + SourceRun upsert helpers

Files:

Create tooling/rails_longtail/test/company_test.rb:

# frozen_string_literal: true

require 'test_helper'
require 'tmpdir'
require 'fileutils'

class CompanyTest < Minitest::Test
  def setup
    @tmp = Dir.mktmpdir
    @db = RailsLongtail::DB.open(File.join(@tmp, 'test.sqlite'))
  end

  def teardown
    @db.close
    FileUtils.rm_rf(@tmp)
  end

  def test_upsert_inserts_new_company
    id = RailsLongtail::Company.upsert_by_domain(@db, domain: 'acme.com', name: 'Acme')

    assert id.positive?
    row = @db.execute('SELECT domain, name FROM companies WHERE id = ?', id).first
    assert_equal ['acme.com', 'Acme'], row
  end

  def test_upsert_returns_existing_id_without_overwriting
    id1 = RailsLongtail::Company.upsert_by_domain(@db, domain: 'acme.com', name: 'Acme Inc')
    id2 = RailsLongtail::Company.upsert_by_domain(@db, domain: 'acme.com', name: 'Acme Corp')

    assert_equal id1, id2
    name = @db.execute('SELECT name FROM companies WHERE id = ?', id1).first.first
    assert_equal 'Acme Inc', name
  end

  def test_upsert_fills_null_fields_on_second_call
    id = RailsLongtail::Company.upsert_by_domain(@db, domain: 'acme.com', name: 'Acme')
    RailsLongtail::Company.upsert_by_domain(@db, domain: 'acme.com', name: 'Acme', github_org: 'acme')

    row = @db.execute('SELECT github_org FROM companies WHERE id = ?', id).first
    assert_equal ['acme'], row
  end

  def test_upsert_normalizes_domain_case
    id1 = RailsLongtail::Company.upsert_by_domain(@db, domain: 'ACME.com', name: 'Acme')
    id2 = RailsLongtail::Company.upsert_by_domain(@db, domain: 'acme.com', name: 'Acme')

    assert_equal id1, id2
  end
end

Run: bundle exec rake test TEST=test/company_test.rb Expected: FAIL with uninitialized constant RailsLongtail::Company.

Create tooling/rails_longtail/lib/rails_longtail/company.rb:

# frozen_string_literal: true

module RailsLongtail
  module Company
    UPSERTABLE = %w[headcount_tier hq_country hq_region website_url linkedin_url github_org notes].freeze

    def self.upsert_by_domain(db, domain:, name:, **extras)
      canonical = domain.strip.downcase

      db.execute(
        'INSERT OR IGNORE INTO companies (domain, name) VALUES (?, ?)',
        [canonical, name]
      )

      id = db.execute('SELECT id FROM companies WHERE domain = ?', canonical).first.first

      extras.slice(*UPSERTABLE.map(&:to_sym)).each do |field, value|
        next if value.nil? || value.to_s.empty?

        db.execute(
          "UPDATE companies SET #{field} = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ? AND #{field} IS NULL",
          [value.to_s, id]
        )
      end

      id
    end
  end
end

Modify tooling/rails_longtail/lib/rails_longtail.rb. After the existing require_relative 'rails_longtail/db' line, add:

require_relative 'rails_longtail/company'

Run: bundle exec rake test TEST=test/company_test.rb Expected: 4 tests, 0 failures.

Create tooling/rails_longtail/test/signal_test.rb:

# frozen_string_literal: true

require 'test_helper'
require 'tmpdir'
require 'fileutils'

class SignalTest < Minitest::Test
  def setup
    @tmp = Dir.mktmpdir
    @db = RailsLongtail::DB.open(File.join(@tmp, 'test.sqlite'))
    @company_id = RailsLongtail::Company.upsert_by_domain(@db, domain: 'acme.com', name: 'Acme')
  end

  def teardown
    @db.close
    FileUtils.rm_rf(@tmp)
  end

  def test_insert_appends_row
    RailsLongtail::Signal.insert(
      @db,
      company_id: @company_id,
      source: 'github',
      signal_type: 'code',
      signal_date: '2026-01-01',
      evidence_url: 'https://github.com/acme/app',
      raw_text: 'gem "rails"'
    )

    rows = @db.execute('SELECT source, signal_type, evidence_url FROM signals')
    assert_equal [['github', 'code', 'https://github.com/acme/app']], rows
  end

  def test_insert_is_append_only
    3.times do
      RailsLongtail::Signal.insert(
        @db,
        company_id: @company_id,
        source: 'github',
        signal_type: 'code',
        evidence_url: 'https://github.com/acme/app'
      )
    end

    count = @db.execute('SELECT COUNT(*) FROM signals').first.first
    assert_equal 3, count
  end
end

Run: bundle exec rake test TEST=test/signal_test.rb Expected: FAIL with uninitialized constant RailsLongtail::Signal.

Create tooling/rails_longtail/lib/rails_longtail/signal.rb:

# frozen_string_literal: true

module RailsLongtail
  module Signal
    def self.insert(db, company_id:, source:, signal_type:, signal_date: nil, evidence_url: nil, raw_text: nil)
      db.execute(
        'INSERT INTO signals (company_id, source, signal_type, signal_date, evidence_url, raw_text) VALUES (?, ?, ?, ?, ?, ?)',
        [company_id, source, signal_type, signal_date, evidence_url, raw_text]
      )
    end
  end
end

Add require_relative 'rails_longtail/signal' to lib/rails_longtail.rb.

Run: bundle exec rake test TEST=test/signal_test.rb Expected: 2 tests, 0 failures.

Create tooling/rails_longtail/test/source_run_test.rb:

# frozen_string_literal: true

require 'test_helper'
require 'tmpdir'
require 'fileutils'

class SourceRunTest < Minitest::Test
  def setup
    @tmp = Dir.mktmpdir
    @db = RailsLongtail::DB.open(File.join(@tmp, 'test.sqlite'))
  end

  def teardown
    @db.close
    FileUtils.rm_rf(@tmp)
  end

  def test_start_returns_id_and_records_start_time
    id = RailsLongtail::SourceRun.start(@db, 'github')

    row = @db.execute('SELECT source, started_at, finished_at FROM source_runs WHERE id = ?', id).first
    assert_equal 'github', row[0]
    refute_nil row[1]
    assert_nil row[2]
  end

  def test_finish_records_end_time_and_rows_added
    id = RailsLongtail::SourceRun.start(@db, 'github')
    RailsLongtail::SourceRun.finish(@db, id, rows_added: 42, notes: 'ok')

    row = @db.execute('SELECT finished_at, rows_added, notes FROM source_runs WHERE id = ?', id).first
    refute_nil row[0]
    assert_equal 42, row[1]
    assert_equal 'ok', row[2]
  end
end

Create tooling/rails_longtail/lib/rails_longtail/source_run.rb:

# frozen_string_literal: true

module RailsLongtail
  module SourceRun
    def self.start(db, source)
      db.execute('INSERT INTO source_runs (source, started_at) VALUES (?, CURRENT_TIMESTAMP)', [source])
      db.last_insert_row_id
    end

    def self.finish(db, id, rows_added:, notes: nil)
      db.execute(
        'UPDATE source_runs SET finished_at = CURRENT_TIMESTAMP, rows_added = ?, notes = ? WHERE id = ?',
        [rows_added, notes, id]
      )
    end
  end
end

Add require_relative 'rails_longtail/source_run' to lib/rails_longtail.rb.

Run: bundle exec rake test Expected: all tests pass (DbTest + CompanyTest + SignalTest + SourceRunTest).

git add tooling/rails_longtail/lib tooling/rails_longtail/test
git commit -m "add Company, Signal, SourceRun upsert helpers"

Task 4: GitHub code-search scraper

Files:

Watch out for: GitHub code search requires authentication and is heavily rate-limited (5-30 req/min). Use ENV['GITHUB_TOKEN'] (a classic PAT with public_repo + read:org scopes is sufficient). Results paginate at 100/page, max 1,000 total per query — so we query in batches with narrower qualifiers (language + filename + extension combinations).

Create tooling/rails_longtail/test/github_test.rb:

# frozen_string_literal: true

require 'test_helper'
require 'tmpdir'
require 'fileutils'

class GithubTest < Minitest::Test
  def setup
    @tmp = Dir.mktmpdir
    @db = RailsLongtail::DB.open(File.join(@tmp, 'test.sqlite'))
  end

  def teardown
    @db.close
    FileUtils.rm_rf(@tmp)
  end

  def test_search_rails_gemfiles_upserts_companies_and_signals
    VCR.use_cassette('github_code_search') do
      RailsLongtail::Github.ingest(@db, pages: 1)
    end

    company_count = @db.execute('SELECT COUNT(*) FROM companies').first.first
    assert company_count.positive?, 'expected at least one company upserted'

    signal_count = @db.execute("SELECT COUNT(*) FROM signals WHERE source = 'github'").first.first
    assert signal_count.positive?, 'expected at least one github signal row'

    sample = @db.execute(
      "SELECT c.domain, s.signal_type, s.evidence_url
       FROM signals s JOIN companies c ON c.id = s.company_id
       WHERE s.source = 'github' LIMIT 1"
    ).first
    refute_nil sample[0]
    assert_equal 'code', sample[1]
    assert_match %r{github\.com/}, sample[2]
  end

  def test_ingest_skips_personal_accounts
    VCR.use_cassette('github_code_search') do
      RailsLongtail::Github.ingest(@db, pages: 1)
    end

    notes = @db.execute("SELECT notes FROM source_runs WHERE source = 'github' ORDER BY id DESC LIMIT 1").first.first
    assert_match(/skipped \d+ personal accounts/, notes || '')
  end
end

Run: bundle exec rake test TEST=test/github_test.rb Expected: FAIL with uninitialized constant RailsLongtail::Github.

Create tooling/rails_longtail/lib/rails_longtail/github.rb:

# frozen_string_literal: true

require 'httparty'

module RailsLongtail
  module Github
    API = 'https://api.github.com'
    CODE_QUERY = 'gem+%22rails%22+filename:Gemfile'

    def self.ingest(db, pages: 10)
      run_id = SourceRun.start(db, 'github')
      added = 0
      skipped_personal = 0

      (1..pages).each do |page|
        resp = HTTParty.get(
          "#{API}/search/code?q=#{CODE_QUERY}&per_page=100&page=#{page}",
          headers: auth_headers
        )
        break unless resp.code == 200

        Array(resp.parsed_response['items']).each do |item|
          repo = item['repository']
          owner = repo['owner']

          if owner['type'] != 'Organization'
            skipped_personal += 1
            next
          end

          org_login = owner['login']
          org_info = fetch_org(org_login)
          next unless org_info && org_info['blog'] && !org_info['blog'].empty?

          domain = normalize_domain(org_info['blog'])
          next if domain.nil? || domain.empty?

          company_id = Company.upsert_by_domain(
            db,
            domain: domain,
            name: org_info['name'] || org_login,
            github_org: org_login,
            website_url: org_info['blog'],
            notes: org_info['description']
          )

          Signal.insert(
            db,
            company_id: company_id,
            source: 'github',
            signal_type: 'code',
            signal_date: Date.today.to_s,
            evidence_url: repo['html_url'],
            raw_text: "Found 'gem \"rails\"' in #{repo['full_name']}/Gemfile"
          )
          added += 1
        end
      end

      SourceRun.finish(db, run_id, rows_added: added, notes: "skipped #{skipped_personal} personal accounts")
      added
    end

    def self.fetch_org(login)
      resp = HTTParty.get("#{API}/orgs/#{login}", headers: auth_headers)
      resp.code == 200 ? resp.parsed_response : nil
    end

    def self.normalize_domain(url)
      return nil unless url

      u = url.strip
      u = "https://#{u}" unless u.match?(%r{^https?://})
      host = URI.parse(u).host
      host&.sub(/^www\./, '')
    rescue URI::InvalidURIError
      nil
    end

    def self.auth_headers
      token = ENV.fetch('GITHUB_TOKEN') { raise 'GITHUB_TOKEN env var required' }
      {
        'Accept' => 'application/vnd.github+json',
        'Authorization' => "Bearer #{token}",
        'X-GitHub-Api-Version' => '2022-11-28',
        'User-Agent' => 'joyrudder-rails-longtail'
      }
    end
  end
end

Add require_relative 'rails_longtail/github' to lib/rails_longtail.rb.

Set GITHUB_TOKEN and allow VCR to record once:

export GITHUB_TOKEN=<your pat>
cd tooling/rails_longtail
# temporarily change test_helper.rb record: :none → record: :new_episodes
# then:
bundle exec rake test TEST=test/github_test.rb
# then revert record: :new_episodes back to record: :none

Verify test/fixtures/vcr_cassettes/github_code_search.yml was created and contains real fixture data with <GITHUB_TOKEN> redacted.

Run: bundle exec rake test TEST=test/github_test.rb Expected: 2 tests, 0 failures.

Create tooling/rails_longtail/bin/ingest_github:

#!/usr/bin/env ruby
# frozen_string_literal: true

$LOAD_PATH.unshift(File.expand_path('../lib', __dir__))
require 'rails_longtail'

pages = Integer(ENV.fetch('PAGES', '10'))
db = RailsLongtail::DB.open
added = RailsLongtail::Github.ingest(db, pages: pages)
puts "github: #{added} signals added across #{pages} pages"
db.close

Make executable: chmod +x tooling/rails_longtail/bin/ingest_github

git add tooling/rails_longtail/lib/rails_longtail/github.rb \
  tooling/rails_longtail/bin/ingest_github tooling/rails_longtail/test/github_test.rb \
  tooling/rails_longtail/test/fixtures tooling/rails_longtail/lib/rails_longtail.rb
git commit -m "add github code-search ingest"

Task 5: HN “Who is Hiring” scraper (via Algolia HN Search API)

Files:

Approach: HN’s Ask HN: Who is hiring? monthly threads are indexed by the Algolia HN Search API (https://hn.algolia.com/api/v1). We fetch the parent thread IDs for the last 12 months, pull each thread’s comments (each comment is one job post), and parse out company name + domain + Rails mention.

Watch out for: HN comments are free-form text. Companies often self-identify as “Acme (acme.com)” at the start; we use a simple regex pair for that pattern. If parsing fails, skip the comment and log to source_runs.notes.

Create tooling/rails_longtail/test/hn_hiring_test.rb:

# frozen_string_literal: true

require 'test_helper'
require 'tmpdir'
require 'fileutils'

class HnHiringTest < Minitest::Test
  def setup
    @tmp = Dir.mktmpdir
    @db = RailsLongtail::DB.open(File.join(@tmp, 'test.sqlite'))
  end

  def teardown
    @db.close
    FileUtils.rm_rf(@tmp)
  end

  def test_ingest_extracts_rails_hiring_signals
    VCR.use_cassette('hn_hiring') do
      RailsLongtail::HnHiring.ingest(@db, months_back: 3)
    end

    rows = @db.execute(
      "SELECT c.domain, s.signal_type, s.signal_date
       FROM signals s JOIN companies c ON c.id = s.company_id
       WHERE s.source = 'hn-hiring'"
    )
    assert rows.any?
    refute_nil rows.first[2]
  end

  def test_parse_comment_extracts_name_domain
    text = 'Acme (https://acme.com) | Senior Ruby on Rails Engineer | Remote | Full-time'
    parsed = RailsLongtail::HnHiring.parse_comment(text)
    assert_equal 'Acme', parsed[:name]
    assert_equal 'acme.com', parsed[:domain]
    assert parsed[:rails_mention]
  end

  def test_parse_comment_returns_nil_without_rails
    text = 'FooCo (https://foo.com) | Go Engineer | NYC'
    assert_nil RailsLongtail::HnHiring.parse_comment(text)
  end
end

Run: bundle exec rake test TEST=test/hn_hiring_test.rb Expected: FAIL with uninitialized constant RailsLongtail::HnHiring.

Create tooling/rails_longtail/lib/rails_longtail/hn_hiring.rb:

# frozen_string_literal: true

require 'httparty'
require 'date'

module RailsLongtail
  module HnHiring
    SEARCH = 'https://hn.algolia.com/api/v1/search'
    ITEM   = 'https://hn.algolia.com/api/v1/items'
    RAILS_RX = /\b(Rails|Ruby on Rails|RoR)\b/i
    HEAD_RX  = /\A\s*([^|(]+?)\s*[|(]\s*(?:https?:\/\/)?([^)\s\/]+\.[a-z]{2,})/i

    def self.ingest(db, months_back: 12)
      run_id = SourceRun.start(db, 'hn-hiring')
      added = 0

      thread_ids_last_months(months_back).each do |thread_id|
        thread = fetch_item(thread_id)
        next unless thread

        date = Time.at(thread['created_at_i']).to_date.to_s
        Array(thread['children']).each do |child|
          comment = (child['text'] || '').gsub(/<[^>]+>/, ' ')
          parsed = parse_comment(comment)
          next unless parsed

          company_id = Company.upsert_by_domain(db, domain: parsed[:domain], name: parsed[:name])
          Signal.insert(
            db,
            company_id: company_id,
            source: 'hn-hiring',
            signal_type: 'job_post',
            signal_date: date,
            evidence_url: "https://news.ycombinator.com/item?id=#{child['id']}",
            raw_text: comment[0, 500]
          )
          added += 1
        end
      end

      SourceRun.finish(db, run_id, rows_added: added)
      added
    end

    def self.thread_ids_last_months(months_back)
      cutoff = Date.today << months_back
      resp = HTTParty.get("#{SEARCH}?query=Ask+HN+Who+is+hiring&tags=story&hitsPerPage=24")
      return [] unless resp.code == 200

      Array(resp.parsed_response['hits'])
        .select { |h| h['title'].to_s.match?(/who is hiring/i) }
        .select { |h| Date.parse(h['created_at']) >= cutoff }
        .map { |h| h['objectID'] }
    end

    def self.fetch_item(id)
      resp = HTTParty.get("#{ITEM}/#{id}")
      resp.code == 200 ? resp.parsed_response : nil
    end

    def self.parse_comment(text)
      return nil unless text.match?(RAILS_RX)

      m = text.match(HEAD_RX)
      return nil unless m

      name = m[1].strip
      domain = m[2].strip.downcase.sub(/^www\./, '')
      return nil if domain.empty? || name.empty?

      { name: name, domain: domain, rails_mention: true }
    end
  end
end

Add require_relative 'rails_longtail/hn_hiring' to lib/rails_longtail.rb.

Run: bundle exec rake test TEST=test/hn_hiring_test.rb Expected: 3 tests, 0 failures.

Create tooling/rails_longtail/bin/ingest_hn_hiring:

#!/usr/bin/env ruby
# frozen_string_literal: true

$LOAD_PATH.unshift(File.expand_path('../lib', __dir__))
require 'rails_longtail'

months = Integer(ENV.fetch('MONTHS', '12'))
db = RailsLongtail::DB.open
added = RailsLongtail::HnHiring.ingest(db, months_back: months)
puts "hn-hiring: #{added} signals added (last #{months} months)"
db.close

Make executable and commit with a message like add hn 'who is hiring' ingest.

Task 6: Job feed scraper (WWR RSS + RemoteOK API)

Files:

Endpoints:

Create tooling/rails_longtail/test/job_feeds_test.rb:

# frozen_string_literal: true

require 'test_helper'
require 'tmpdir'
require 'fileutils'

class JobFeedsTest < Minitest::Test
  def setup
    @tmp = Dir.mktmpdir
    @db = RailsLongtail::DB.open(File.join(@tmp, 'test.sqlite'))
  end

  def teardown
    @db.close
    FileUtils.rm_rf(@tmp)
  end

  def test_ingest_wwr_upserts_companies_and_signals
    VCR.use_cassette('wwr_ruby') do
      RailsLongtail::JobFeeds.ingest_wwr(@db)
    end
    count = @db.execute("SELECT COUNT(*) FROM signals WHERE source = 'wwr'").first.first
    assert count.positive?
  end

  def test_ingest_remoteok_upserts_companies_and_signals
    VCR.use_cassette('remoteok_ruby') do
      RailsLongtail::JobFeeds.ingest_remoteok(@db)
    end
    count = @db.execute("SELECT COUNT(*) FROM signals WHERE source = 'remoteok'").first.first
    assert count.positive?
  end
end

Run: bundle exec rake test TEST=test/job_feeds_test.rb Expected: FAIL on uninitialized constant RailsLongtail::JobFeeds.

Create tooling/rails_longtail/lib/rails_longtail/job_feeds.rb:

# frozen_string_literal: true

require 'feedjira'
require 'httparty'

module RailsLongtail
  module JobFeeds
    WWR_URL = 'https://weworkremotely.com/categories/remote-ruby-on-rails-jobs.rss'
    REMOTEOK_URL = 'https://remoteok.com/api?tags=ruby'
    COMPANY_DOMAIN_RX = /\b([a-z0-9][-a-z0-9]*\.[a-z]{2,})\b/i

    def self.ingest_wwr(db)
      run_id = SourceRun.start(db, 'wwr')
      added = 0
      resp = HTTParty.get(WWR_URL, headers: { 'User-Agent' => 'joyrudder-rails-longtail' })
      return 0 unless resp.code == 200

      feed = Feedjira.parse(resp.body)
      Array(feed.entries).each do |entry|
        next unless entry.title.match?(/Rails|Ruby/i) || entry.summary.to_s.match?(/Rails/i)

        company_name, domain = extract_company(entry.title, entry.url)
        next unless domain

        company_id = Company.upsert_by_domain(db, domain: domain, name: company_name)
        Signal.insert(
          db,
          company_id: company_id,
          source: 'wwr',
          signal_type: 'job_post',
          signal_date: entry.published&.to_date&.to_s,
          evidence_url: entry.url,
          raw_text: "#{entry.title} -- #{entry.summary.to_s[0, 400]}"
        )
        added += 1
      end

      SourceRun.finish(db, run_id, rows_added: added)
      added
    end

    def self.ingest_remoteok(db)
      run_id = SourceRun.start(db, 'remoteok')
      added = 0
      resp = HTTParty.get(REMOTEOK_URL, headers: { 'User-Agent' => 'joyrudder-rails-longtail' })
      return 0 unless resp.code == 200

      Array(resp.parsed_response).each do |job|
        next unless job.is_a?(Hash)
        next unless Array(job['tags']).map(&:to_s).any? { |t| t.match?(/\bruby\b/i) }

        company_name = job['company'] || next
        domain = guess_domain(job['company_logo'] || job['url'] || '')
        next unless domain

        company_id = Company.upsert_by_domain(db, domain: domain, name: company_name)
        Signal.insert(
          db,
          company_id: company_id,
          source: 'remoteok',
          signal_type: 'job_post',
          signal_date: (Date.parse(job['date']) rescue nil)&.to_s,
          evidence_url: job['url'],
          raw_text: job['description'].to_s[0, 500]
        )
        added += 1
      end

      SourceRun.finish(db, run_id, rows_added: added)
      added
    end

    def self.extract_company(title, url)
      # Titles often look like: "ACME: Senior Rails Engineer" or "Senior Rails Engineer at ACME"
      name = title.split(/[:|\-]|\bat\b/i).map(&:strip).reject(&:empty?).first
      domain = guess_domain(url || '')
      [name, domain]
    end

    def self.guess_domain(url)
      return nil if url.nil? || url.empty?

      match = url.match(COMPANY_DOMAIN_RX)
      return nil unless match

      host = match[1].downcase.sub(/^www\./, '')
      return nil if %w[weworkremotely.com remoteok.com].include?(host)

      host
    end
  end
end

Add require_relative 'rails_longtail/job_feeds' to lib/rails_longtail.rb.

Run: bundle exec rake test TEST=test/job_feeds_test.rb Expected: 2 tests, 0 failures.

Create tooling/rails_longtail/bin/ingest_job_feeds:

#!/usr/bin/env ruby
# frozen_string_literal: true

$LOAD_PATH.unshift(File.expand_path('../lib', __dir__))
require 'rails_longtail'

db = RailsLongtail::DB.open
w = RailsLongtail::JobFeeds.ingest_wwr(db)
r = RailsLongtail::JobFeeds.ingest_remoteok(db)
puts "job-feeds: wwr=#{w}, remoteok=#{r}"
db.close

Make executable and commit.

Task 7: Board dork scraper (Greenhouse / Lever / Ashby via DuckDuckGo HTML)

Files:

Approach: DuckDuckGo’s HTML endpoint (https://html.duckduckgo.com/html/) accepts a q POST parameter and returns static HTML we can parse with Nokogiri. We run three queries:

Each result URL contains /<company-slug>/ which we use as both the canonical company identifier and the synthetic domain (e.g., company-slug.greenhouse.io). We use the synthetic domain so the same company won’t dedupe incorrectly if it also appears in GitHub or WWR with its real domain — but see Task 10 where classification can manually consolidate if needed.

Watch out for: DDG rate-limits aggressive scraping. Use 2-second sleep between queries. If this source drops to zero results, fall back to manually-curated docs/board_dork_seed_companies.yml (create in this task as a fallback list).

Create tooling/rails_longtail/test/board_dorks_test.rb:

# frozen_string_literal: true

require 'test_helper'
require 'tmpdir'
require 'fileutils'

class BoardDorksTest < Minitest::Test
  def setup
    @tmp = Dir.mktmpdir
    @db = RailsLongtail::DB.open(File.join(@tmp, 'test.sqlite'))
  end

  def teardown
    @db.close
    FileUtils.rm_rf(@tmp)
  end

  def test_parse_extracts_company_slugs_from_ddg_html
    html = File.read(File.join(__dir__, 'fixtures', 'ddg_greenhouse.html'))
    slugs = RailsLongtail::BoardDorks.parse_greenhouse(html)
    assert slugs.size.positive?
    assert slugs.all? { |s| s =~ /\A[a-z0-9-]+\z/ }
  end

  def test_ingest_upserts_board_dork_signals
    VCR.use_cassette('board_dorks') do
      RailsLongtail::BoardDorks.ingest(@db)
    end
    count = @db.execute("SELECT COUNT(*) FROM signals WHERE source = 'board-dorks'").first.first
    assert count.positive?
  end
end

Create tooling/rails_longtail/test/fixtures/ddg_greenhouse.html by hand — save a sample DDG HTML result for site:boards.greenhouse.io "Ruby on Rails" (browser → view source → save). Any ~5 result entries will do.

Run: bundle exec rake test TEST=test/board_dorks_test.rb Expected: FAIL on uninitialized constant RailsLongtail::BoardDorks.

Create tooling/rails_longtail/lib/rails_longtail/board_dorks.rb:

# frozen_string_literal: true

require 'httparty'
require 'nokogiri'

module RailsLongtail
  module BoardDorks
    DDG = 'https://html.duckduckgo.com/html/'
    BOARDS = {
      'greenhouse' => { host: 'boards.greenhouse.io', url_rx: %r{boards\.greenhouse\.io/(?:embed/job_app\?for=)?([a-z0-9-]+)}i },
      'lever'      => { host: 'jobs.lever.co',       url_rx: %r{jobs\.lever\.co/([a-z0-9-]+)}i },
      'ashby'      => { host: 'jobs.ashbyhq.com',    url_rx: %r{jobs\.ashbyhq\.com/([a-z0-9-]+)}i }
    }.freeze

    def self.ingest(db)
      run_id = SourceRun.start(db, 'board-dorks')
      added = 0

      BOARDS.each_pair do |board, cfg|
        html = search_ddg(%(site:#{cfg[:host]} "Ruby on Rails"))
        slugs = parse(html, cfg[:url_rx])
        slugs.each do |slug|
          domain = "#{slug}.#{cfg[:host].split('.').last(2).join('.')}"
          company_id = Company.upsert_by_domain(db, domain: domain, name: slug.tr('-', ' ').capitalize)
          Signal.insert(
            db,
            company_id: company_id,
            source: 'board-dorks',
            signal_type: 'job_post',
            evidence_url: "https://#{cfg[:host]}/#{slug}",
            raw_text: "#{board} board lists #{slug} with Ruby on Rails"
          )
          added += 1
        end
        sleep 2
      end

      SourceRun.finish(db, run_id, rows_added: added)
      added
    end

    def self.search_ddg(query)
      resp = HTTParty.post(
        DDG,
        body: { q: query },
        headers: { 'User-Agent' => 'Mozilla/5.0 joyrudder-rails-longtail' }
      )
      resp.code == 200 ? resp.body : ''
    end

    def self.parse(html, rx)
      doc = Nokogiri::HTML(html)
      hrefs = doc.css('a.result__a, a.result__url').map { |a| a['href'].to_s }
      hrefs.flat_map { |h| h.scan(rx) }.flatten.uniq
    end

    def self.parse_greenhouse(html)
      parse(html, BOARDS['greenhouse'][:url_rx])
    end
  end
end

Add require_relative 'rails_longtail/board_dorks' to lib/rails_longtail.rb.

Run: bundle exec rake test TEST=test/board_dorks_test.rb Expected: 2 tests, 0 failures.

Create tooling/rails_longtail/bin/ingest_board_dorks:

#!/usr/bin/env ruby
# frozen_string_literal: true

$LOAD_PATH.unshift(File.expand_path('../lib', __dir__))
require 'rails_longtail'

db = RailsLongtail::DB.open
added = RailsLongtail::BoardDorks.ingest(db)
puts "board-dorks: #{added} signals added"
db.close

Make executable and commit.

Task 8: Rails Foundation directory scraper

Files:

Approach: https://rubyonrails.org/foundation/ lists members/sponsors as linked tiles. Each tile contains company name + URL. We scrape with Nokogiri, extract company + domain, and record a sponsor signal.

Create tooling/rails_longtail/test/rails_foundation_test.rb:

# frozen_string_literal: true

require 'test_helper'
require 'tmpdir'
require 'fileutils'

class RailsFoundationTest < Minitest::Test
  def setup
    @tmp = Dir.mktmpdir
    @db = RailsLongtail::DB.open(File.join(@tmp, 'test.sqlite'))
  end

  def teardown
    @db.close
    FileUtils.rm_rf(@tmp)
  end

  def test_parse_extracts_member_tiles
    html = File.read(File.join(__dir__, 'fixtures', 'rails_foundation.html'))
    entries = RailsLongtail::RailsFoundation.parse(html)
    assert entries.size.positive?
    entries.each do |e|
      assert e[:name], "entry has name"
      assert e[:domain], "entry has domain"
    end
  end

  def test_ingest_writes_sponsor_signals
    VCR.use_cassette('rails_foundation') do
      RailsLongtail::RailsFoundation.ingest(@db)
    end
    count = @db.execute("SELECT COUNT(*) FROM signals WHERE source = 'railsfdn'").first.first
    assert count.positive?
  end
end

Save a hand-captured test/fixtures/rails_foundation.html fixture.

Expected: uninitialized constant RailsLongtail::RailsFoundation.

Create tooling/rails_longtail/lib/rails_longtail/rails_foundation.rb:

# frozen_string_literal: true

require 'httparty'
require 'nokogiri'

module RailsLongtail
  module RailsFoundation
    URL = 'https://rubyonrails.org/foundation/'
    DOMAIN_RX = /\b([a-z0-9][-a-z0-9]*\.[a-z]{2,})/i

    def self.ingest(db)
      run_id = SourceRun.start(db, 'railsfdn')
      added = 0
      resp = HTTParty.get(URL, headers: { 'User-Agent' => 'joyrudder-rails-longtail' })
      return 0 unless resp.code == 200

      parse(resp.body).each do |entry|
        company_id = Company.upsert_by_domain(db, domain: entry[:domain], name: entry[:name])
        Signal.insert(
          db,
          company_id: company_id,
          source: 'railsfdn',
          signal_type: 'sponsor',
          signal_date: Date.today.to_s,
          evidence_url: URL,
          raw_text: "Rails Foundation: #{entry[:name]}"
        )
        added += 1
      end

      SourceRun.finish(db, run_id, rows_added: added)
      added
    end

    def self.parse(html)
      doc = Nokogiri::HTML(html)
      anchors = doc.css('a[href]').select { |a| a['href'].to_s.match?(%r{^https?://}) }
      anchors.uniq { |a| a['href'] }.filter_map do |a|
        href = a['href']
        name = (a['title'] || a.text.strip).to_s.strip
        next if name.empty?

        host = URI.parse(href).host&.sub(/^www\./, '')
        next unless host && host.include?('.')
        next if %w[rubyonrails.org github.com twitter.com x.com linkedin.com].include?(host)

        { name: name, domain: host.downcase }
      rescue URI::InvalidURIError
        nil
      end
    end
  end
end

Add require to lib/rails_longtail.rb.

Create tooling/rails_longtail/bin/ingest_rails_foundation:

#!/usr/bin/env ruby
# frozen_string_literal: true

$LOAD_PATH.unshift(File.expand_path('../lib', __dir__))
require 'rails_longtail'

db = RailsLongtail::DB.open
added = RailsLongtail::RailsFoundation.ingest(db)
puts "rails-foundation: #{added} signals added"
db.close

Make executable: chmod +x tooling/rails_longtail/bin/ingest_rails_foundation

git add tooling/rails_longtail/lib/rails_longtail/rails_foundation.rb \
  tooling/rails_longtail/bin/ingest_rails_foundation \
  tooling/rails_longtail/test/rails_foundation_test.rb \
  tooling/rails_longtail/test/fixtures tooling/rails_longtail/lib/rails_longtail.rb
git commit -m "add rails foundation directory ingest"

Task 9: Conference sponsor scraper

Files:

Approach: Each conference has a sponsor page with a year in the URL (e.g., railsconf.org/2024/sponsors). Conferences and years are enumerated in a YAML config so it’s trivial to add more. Parser logic mirrors Task 8 — anchor tags with logos.

# tooling/rails_longtail/docs/conference_urls.yml
railsconf:
  - 2023: https://railsconf.org/2023/sponsors
  - 2024: https://railsconf.org/2024/sponsors
  - 2025: https://railsconf.org/2025/sponsors
rubykaigi:
  - 2023: https://rubykaigi.org/2023/sponsors/
  - 2024: https://rubykaigi.org/2024/sponsors/
  - 2025: https://rubykaigi.org/2025/sponsors/
euruko:
  - 2023: https://2023.euruko.org/sponsors/
  - 2024: https://2024.euruko.org/sponsors
rubyconf:
  - 2023: https://rubyconf.org/2023/sponsors
  - 2024: https://rubyconf.org/2024/sponsors

Create tooling/rails_longtail/test/fixtures/conf_railsconf_2024.html by hand (save a real RailsConf 2024 sponsor page).

Create tooling/rails_longtail/test/conferences_test.rb:

# frozen_string_literal: true

require 'test_helper'
require 'tmpdir'
require 'fileutils'

class ConferencesTest < Minitest::Test
  def setup
    @tmp = Dir.mktmpdir
    @db = RailsLongtail::DB.open(File.join(@tmp, 'test.sqlite'))
  end

  def teardown
    @db.close
    FileUtils.rm_rf(@tmp)
  end

  def test_parse_extracts_sponsor_entries
    html = File.read(File.join(__dir__, 'fixtures', 'conf_railsconf_2024.html'))
    entries = RailsLongtail::Conferences.parse(html)
    assert entries.size.positive?
    entries.each do |e|
      assert e[:domain].include?('.')
      refute e[:domain].start_with?('railsconf')
    end
  end

  def test_ingest_writes_conf_sponsor_signals
    VCR.use_cassette('conferences_railsconf_2024') do
      RailsLongtail::Conferences.ingest(@db)
    end
    count = @db.execute("SELECT COUNT(*) FROM signals WHERE source = 'conf-sponsors'").first.first
    assert count.positive?
  end
end

Create tooling/rails_longtail/lib/rails_longtail/conferences.rb:

# frozen_string_literal: true

require 'httparty'
require 'nokogiri'
require 'yaml'

module RailsLongtail
  module Conferences
    CONFIG_PATH = File.expand_path('../../docs/conference_urls.yml', __dir__)

    def self.ingest(db)
      run_id = SourceRun.start(db, 'conf-sponsors')
      added = 0
      config = YAML.load_file(CONFIG_PATH)

      config.each do |conf, years|
        Array(years).each do |entry|
          year, url = entry.first
          resp = HTTParty.get(url, headers: { 'User-Agent' => 'joyrudder-rails-longtail' })
          next unless resp.code == 200

          parse(resp.body).each do |e|
            company_id = Company.upsert_by_domain(db, domain: e[:domain], name: e[:name])
            Signal.insert(
              db,
              company_id: company_id,
              source: 'conf-sponsors',
              signal_type: 'sponsor',
              signal_date: "#{year}-01-01",
              evidence_url: url,
              raw_text: "#{conf} #{year} sponsor: #{e[:name]}"
            )
            added += 1
          end
          sleep 1
        end
      end

      SourceRun.finish(db, run_id, rows_added: added)
      added
    end

    def self.parse(html)
      doc = Nokogiri::HTML(html)
      doc.css('a[href]').filter_map do |a|
        href = a['href'].to_s
        next unless href.match?(%r{^https?://})

        host = URI.parse(href).host&.sub(/^www\./, '')
        next unless host && host.include?('.')
        next if host.match?(/(rubykaigi|railsconf|euruko|rubyconf|github|twitter|x|linkedin)\./)

        name = (a['title'] || a.text.strip)
        name = host.split('.').first.capitalize if name.nil? || name.empty?
        { name: name, domain: host.downcase }
      rescue URI::InvalidURIError
        nil
      end.uniq { |e| e[:domain] }
    end
  end
end

Add require to lib/rails_longtail.rb.

Create tooling/rails_longtail/bin/ingest_conferences:

#!/usr/bin/env ruby
# frozen_string_literal: true

$LOAD_PATH.unshift(File.expand_path('../lib', __dir__))
require 'rails_longtail'

db = RailsLongtail::DB.open
added = RailsLongtail::Conferences.ingest(db)
puts "conferences: #{added} signals added"
db.close

Make executable.

git add tooling/rails_longtail/lib/rails_longtail/conferences.rb \
  tooling/rails_longtail/bin/ingest_conferences tooling/rails_longtail/docs/conference_urls.yml \
  tooling/rails_longtail/test/conferences_test.rb tooling/rails_longtail/test/fixtures \
  tooling/rails_longtail/lib/rails_longtail.rb
git commit -m "add conference sponsor ingest"

Task 10: Gem-maintainer org scraper

Files:

Approach: https://rubygems.org/api/v1/versions/popular.json is not available, so we use https://rubygems.org/downloads/total.json (aggregate) and hit https://rubygems.org/api/v1/gems.json?page=N with page=1..10 to walk the top-downloaded gems (100 per page → 1,000 gems total). For each gem we fetch GET /api/v1/gems/{name}.json and look at source_code_uri / homepage_uri. If it maps to a GitHub org (not a personal account), we upsert. Filter: org must have a homepage with a valid domain.

Watch out for: rubygems.org rate-limit is 400 req/min per IP. 1,000 gems × 1 request each → ~3 min with a tiny sleep.

Create tooling/rails_longtail/test/gems_test.rb:

# frozen_string_literal: true

require 'test_helper'
require 'tmpdir'
require 'fileutils'

class GemsTest < Minitest::Test
  def setup
    @tmp = Dir.mktmpdir
    @db = RailsLongtail::DB.open(File.join(@tmp, 'test.sqlite'))
  end

  def teardown
    @db.close
    FileUtils.rm_rf(@tmp)
  end

  def test_extract_github_org_from_source_uri
    assert_equal 'rails', RailsLongtail::Gems.extract_github_org('https://github.com/rails/rails')
    assert_equal 'heartcombo', RailsLongtail::Gems.extract_github_org('https://github.com/heartcombo/devise/tree/main')
    assert_nil RailsLongtail::Gems.extract_github_org('https://example.com/')
  end

  def test_ingest_writes_gem_maintainer_signals
    VCR.use_cassette('gems_top_1000') do
      RailsLongtail::Gems.ingest(@db, pages: 1)
    end
    count = @db.execute("SELECT COUNT(*) FROM signals WHERE source = 'gem-maintainer'").first.first
    assert count.positive?
  end
end

Create tooling/rails_longtail/lib/rails_longtail/gems.rb:

# frozen_string_literal: true

require 'httparty'

module RailsLongtail
  module Gems
    LIST_URL = 'https://rubygems.org/api/v1/gems.json'
    GEM_URL  = 'https://rubygems.org/api/v1/gems/%s.json'
    GH_ORG_RX = %r{github\.com/([a-z0-9][-a-z0-9]*)/}i

    def self.ingest(db, pages: 10)
      run_id = SourceRun.start(db, 'gem-maintainer')
      added = 0

      (1..pages).each do |page|
        list = HTTParty.get("#{LIST_URL}?page=#{page}").parsed_response
        next unless list.is_a?(Array)

        list.each do |gem|
          info = HTTParty.get(format(GEM_URL, gem['name'])).parsed_response
          next unless info.is_a?(Hash)

          source = info['source_code_uri'] || info['homepage_uri'] || ''
          org = extract_github_org(source)
          next unless org
          next if personal_account?(org)

          homepage = info['homepage_uri']
          next unless homepage && !homepage.empty?

          domain = URI.parse(homepage).host&.sub(/^www\./, '')
          next unless domain && domain.include?('.')

          company_id = Company.upsert_by_domain(
            db,
            domain: domain.downcase,
            name: info['name'].split(/[_-]/).first.capitalize,
            github_org: org,
            website_url: homepage
          )

          Signal.insert(
            db,
            company_id: company_id,
            source: 'gem-maintainer',
            signal_type: 'gem_maintainer',
            signal_date: (Date.parse(info['version_created_at']) rescue nil)&.to_s,
            evidence_url: "https://rubygems.org/gems/#{info['name']}",
            raw_text: "Maintains gem: #{info['name']} (v#{info['version']})"
          )
          added += 1
          sleep 0.2
        rescue URI::InvalidURIError
          next
        end
      end

      SourceRun.finish(db, run_id, rows_added: added)
      added
    end

    def self.extract_github_org(url)
      m = url.to_s.match(GH_ORG_RX)
      m && m[1].downcase
    end

    def self.personal_account?(org)
      # Quick heuristic: GitHub user vs org is checked by fetching /users/{org}
      # For offline tests we just check a small denylist here.
      %w[dhh tenderlove rafaelfranca fxn].include?(org)
    end
  end
end

Add require to lib/rails_longtail.rb.

Create tooling/rails_longtail/bin/ingest_gems:

#!/usr/bin/env ruby
# frozen_string_literal: true

$LOAD_PATH.unshift(File.expand_path('../lib', __dir__))
require 'rails_longtail'

pages = Integer(ENV.fetch('PAGES', '10'))
db = RailsLongtail::DB.open
added = RailsLongtail::Gems.ingest(db, pages: pages)
puts "gems: #{added} signals added (#{pages} pages of top gems)"
db.close

Make executable.

git add tooling/rails_longtail/lib/rails_longtail/gems.rb \
  tooling/rails_longtail/bin/ingest_gems tooling/rails_longtail/test/gems_test.rb \
  tooling/rails_longtail/test/fixtures tooling/rails_longtail/lib/rails_longtail.rb
git commit -m "add gem maintainer org ingest"

Task 11: Classification prompt, vertical definitions, business-model taxonomy

Files:

Goal: All prompt context and operator instructions the Sonnet subagent dispatch needs. Each file is executable-by-human; no Ruby changes.

Content (short, bullet-per-vertical with examples):

# Ten Locked Verticals

Companies are classified into exactly one of these. Pick the dominant vertical of the product the company sells.

- **Healthcare** — EHR, clinical, biotech tooling, patient care, telemedicine, pharma, health-insurance-tech.
  Examples: Flatiron Health, Doxy.me, Benchling.
- **BFSI** — banks, lenders, brokerages, insurance, payments, fintech. Anything whose product moves or prices money.
  Examples: Chime, Betterment, Coinbase, Plaid.
- **Retail / E-commerce** — online retailers, marketplaces of physical goods, D2C, fulfillment.
  Examples: Shopify, Etsy, Gumroad, Stitch Fix.
- **IT / Telecom** — cloud infra, developer tools, telco, SaaS platforms whose customer is IT/dev. Default bucket for dev-tools companies without a vertical.
  Examples: GitLab, Zendesk, Basecamp, Honeybadger.
- **Manufacturing / Industry 4.0** — ERP, MES, supply-chain, industrial IoT, CAD/PLM.
  Examples: Tulip, Bright Machines.
- **Media** — publishing, streaming, news, social, entertainment.
  Examples: Basecamp HEY, The New York Times, Vimeo.
- **Automotive** — OEM, parts, auto-finance, auto-marketplaces, fleet tech.
  Examples: Carvana, Recurrent.
- **Cybersecurity** — security vendors, SOC tooling, identity, compliance.
  Examples: HackerOne, Snyk, JumpCloud.
- **Education** — K-12, higher-ed, bootcamps, corporate L&D, edtech.
  Examples: Code.org, Handshake, Coursera.
- **Energy** — utilities, solar, grid, oil & gas, energy marketplaces.
  Examples: Aurora Solar, Arcadia.

Content:

# Business-Model Taxonomy (7 values)

- **marketplace** — two-sided. Takes a cut of transactions between buyers and sellers. (Etsy, Airbnb)
- **b2b_saas** — sells software to other companies via subscription. (GitLab, Zendesk)
- **dev_tools** — sells to developers specifically. (Honeybadger, Sidekiq Enterprise)
- **vertical_saas** — b2b_saas focused on one industry. (Benchling for biotech, Procore for construction)
- **internal_tools** — builds software the company uses itself; Rails powers ops/admin, not the customer product. (Shopify admin, Basecamp's internal tools)
- **media** — ad- or subscription-supported content. (NYT, Vimeo)
- **consumer** — direct-to-consumer app or service. (Gumroad buyer experience, Notion-ish consumer apps)

Content:

# Sonnet Subagent Classification Prompt

You will classify a batch of up to 50 companies into one of ten verticals and one of seven business models, then report a confidence score.

## Input format

You receive a JSON array. Each entry:
```json
{
  "company_id": 123,
  "name": "Acme",
  "domain": "acme.com",
  "signals": [
    {"source": "github", "signal_type": "code", "signal_date": "2025-11-03", "raw_text": "gem \"rails\""},
    {"source": "hn-hiring", "signal_type": "job_post", "signal_date": "2026-01-01", "raw_text": "Acme | Senior Rails Engineer | acme.com sells widgets to healthcare clinics"}
  ]
}

Vertical definitions

(Paste contents of vertical_definitions.md here.)

Business-model taxonomy

(Paste contents of business_model_taxonomy.md here.)

Output format

Return a JSON array, one object per input company, same order:

[
  {
    "company_id": 123,
    "primary_vertical": "Healthcare",
    "business_model": "vertical_saas",
    "confidence": 0.78,
    "notes": "Hiring post states 'widgets to healthcare clinics'; b2b vertical software for clinics."
  }
]

Rules:

How to run

Write output as JSONL (one JSON object per line) to a file named classify_batch_<N>.jsonl in tooling/rails_longtail/data/classifications/.


- [ ] **Step 4: Write classification_runbook.md**

Content:
```markdown
# Classification Runbook (Phase 1)

## Prerequisites
- All ingest scripts have been run at least once.
- `data/rails_longtail.sqlite` has >500 rows in `companies`.

## Step 1: Export unclassified companies to JSON batches

```bash
cd tooling/rails_longtail
bin/export_classify_batches

This creates data/classify_input/batch_<N>.json, 50 companies per file. Each entry has company_id, name, domain, and up to 5 most-recent signal rows.

Step 2: Dispatch Sonnet subagents

From a Claude Code session at the repo root, for each batch file:

Use the Agent tool with:

Fan out multiple subagents concurrently (single message, multiple Agent tool calls).

Step 3: Merge JSONL into the database

bin/merge_classifications

This reads all data/classifications/*.jsonl, validates each row, and inserts into the classifications table.

Step 4: Compute long-tail-fit

bin/compute_long_tail_fit

Step 5: Flag audit candidates

bin/flag_audit

Step 6: Rick audits

Open the audit view in any SQLite client:

SELECT c.name, c.domain, cl.primary_vertical, cl.business_model, cl.confidence, cl.llm_notes
FROM classifications cl JOIN companies c ON c.id = cl.company_id
WHERE cl.audited = 0 AND (cl.confidence < 0.6 OR cl.company_id IN (SELECT company_id FROM audit_queue));

For each row, Rick sets audit_verdict (‘agree’ | ‘disagree’ | ‘unclear’) and audited = 1. Disagreements are re-classified by editing primary_vertical and business_model directly; set classified_by = 'human:rick'.


- [ ] **Step 5: Commit**

```bash
git add tooling/rails_longtail/docs
git commit -m "add classification prompt, taxonomy, and runbook"

Task 12: Classification merge script

Files:

Create tooling/rails_longtail/test/classification_test.rb:

# frozen_string_literal: true

require 'test_helper'
require 'tmpdir'
require 'fileutils'
require 'json'

class ClassificationTest < Minitest::Test
  def setup
    @tmp = Dir.mktmpdir
    @db = RailsLongtail::DB.open(File.join(@tmp, 'test.sqlite'))
    @company_id = RailsLongtail::Company.upsert_by_domain(@db, domain: 'acme.com', name: 'Acme')
  end

  def teardown
    @db.close
    FileUtils.rm_rf(@tmp)
  end

  def test_merge_inserts_classifications
    jsonl = [
      { company_id: @company_id, primary_vertical: 'Healthcare',
        business_model: 'vertical_saas', confidence: 0.82, notes: 'clinical EHR' }
    ].map(&:to_json).join("\n")
    path = File.join(@tmp, 'batch_1.jsonl')
    File.write(path, jsonl)

    inserted = RailsLongtail::Classification.merge_file(@db, path, classified_by: 'claude-sonnet-4-6')
    assert_equal 1, inserted

    row = @db.execute('SELECT primary_vertical, business_model, confidence FROM classifications WHERE company_id = ?', @company_id).first
    assert_equal ['Healthcare', 'vertical_saas', 0.82], row
  end

  def test_merge_is_idempotent
    jsonl = [{ company_id: @company_id, primary_vertical: 'Healthcare', business_model: 'vertical_saas', confidence: 0.82, notes: 'ok' }].map(&:to_json).join("\n")
    path = File.join(@tmp, 'batch_1.jsonl')
    File.write(path, jsonl)

    RailsLongtail::Classification.merge_file(@db, path, classified_by: 'claude-sonnet-4-6')
    RailsLongtail::Classification.merge_file(@db, path, classified_by: 'claude-sonnet-4-6')

    count = @db.execute('SELECT COUNT(*) FROM classifications WHERE company_id = ?', @company_id).first.first
    assert_equal 1, count
  end

  def test_merge_rejects_bad_vertical
    jsonl = [{ company_id: @company_id, primary_vertical: 'Construction', business_model: 'b2b_saas', confidence: 0.7, notes: 'bad label' }].map(&:to_json).join("\n")
    path = File.join(@tmp, 'batch_1.jsonl')
    File.write(path, jsonl)

    assert_raises(RailsLongtail::Classification::InvalidRow) do
      RailsLongtail::Classification.merge_file(@db, path, classified_by: 'claude-sonnet-4-6')
    end
  end
end

Create tooling/rails_longtail/lib/rails_longtail/classification.rb:

# frozen_string_literal: true

require 'json'

module RailsLongtail
  module Classification
    class InvalidRow < StandardError; end

    VERTICALS = [
      'Healthcare', 'BFSI', 'Retail/E-commerce', 'IT/Telecom',
      'Manufacturing/Industry 4.0', 'Media', 'Automotive',
      'Cybersecurity', 'Education', 'Energy'
    ].freeze

    MODELS = %w[marketplace b2b_saas dev_tools vertical_saas internal_tools media consumer].freeze

    def self.merge_file(db, path, classified_by:)
      inserted = 0

      File.foreach(path) do |line|
        line = line.strip
        next if line.empty?

        row = JSON.parse(line)
        validate!(row)

        result = db.execute(
          <<~SQL,
            INSERT INTO classifications
              (company_id, primary_vertical, business_model, confidence, llm_notes, classified_by)
            VALUES (?, ?, ?, ?, ?, ?)
            ON CONFLICT(company_id) DO NOTHING
          SQL
          [
            row.fetch('company_id'), row.fetch('primary_vertical'),
            row.fetch('business_model'), row.fetch('confidence'),
            row['notes'], classified_by
          ]
        )
        inserted += db.changes if db.changes.positive?
      end

      inserted
    end

    def self.validate!(row)
      raise InvalidRow, "missing company_id in #{row.inspect}" unless row['company_id']
      raise InvalidRow, "bad vertical: #{row['primary_vertical']}" unless VERTICALS.include?(row['primary_vertical'])
      raise InvalidRow, "bad model: #{row['business_model']}" unless MODELS.include?(row['business_model'])
      raise InvalidRow, "bad confidence: #{row['confidence']}" unless row['confidence'].is_a?(Numeric) && (0..1).cover?(row['confidence'])
    end
  end
end

Add require to lib/rails_longtail.rb.

Create tooling/rails_longtail/bin/merge_classifications:

#!/usr/bin/env ruby
# frozen_string_literal: true

$LOAD_PATH.unshift(File.expand_path('../lib', __dir__))
require 'rails_longtail'

dir = File.expand_path('../data/classifications', __dir__)
Dir.glob(File.join(dir, '*.jsonl')).sort.each do |path|
  db = RailsLongtail::DB.open
  n = RailsLongtail::Classification.merge_file(db, path, classified_by: 'claude-sonnet-4-6')
  db.close
  puts "#{File.basename(path)}: #{n} rows"
end

Create tooling/rails_longtail/bin/export_classify_batches:

#!/usr/bin/env ruby
# frozen_string_literal: true

$LOAD_PATH.unshift(File.expand_path('../lib', __dir__))
require 'rails_longtail'
require 'json'
require 'fileutils'

BATCH_SIZE = 50

out_dir = File.expand_path('../data/classify_input', __dir__)
FileUtils.mkdir_p(out_dir)

db = RailsLongtail::DB.open
rows = db.execute(<<~SQL)
  SELECT c.id, c.name, c.domain
  FROM companies c
  LEFT JOIN classifications cl ON cl.company_id = c.id
  WHERE cl.id IS NULL
  ORDER BY c.id
SQL

rows.each_slice(BATCH_SIZE).with_index(1) do |slice, idx|
  entries = slice.map do |cid, name, domain|
    signals = db.execute(
      'SELECT source, signal_type, signal_date, raw_text FROM signals WHERE company_id = ? ORDER BY captured_at DESC LIMIT 5',
      [cid]
    ).map { |s, st, sd, rt| { source: s, signal_type: st, signal_date: sd, raw_text: rt } }
    { company_id: cid, name: name, domain: domain, signals: signals }
  end

  path = File.join(out_dir, "batch_#{idx}.json")
  File.write(path, JSON.pretty_generate(entries))
end

db.close
puts "wrote #{Dir.glob(File.join(out_dir, 'batch_*.json')).size} batches to #{out_dir}"

Make both executable and commit.

Task 13: Long-tail-fit computation + audit flagging

Files:

Create tooling/rails_longtail/test/long_tail_fit_test.rb:

# frozen_string_literal: true

require 'test_helper'
require 'tmpdir'
require 'fileutils'

class LongTailFitTest < Minitest::Test
  def setup
    @tmp = Dir.mktmpdir
    @db = RailsLongtail::DB.open(File.join(@tmp, 'test.sqlite'))
  end

  def teardown
    @db.close
    FileUtils.rm_rf(@tmp)
  end

  def test_marks_small_non_sponsor_as_fit
    id = RailsLongtail::Company.upsert_by_domain(@db, domain: 'small.com', name: 'Small', headcount_tier: 'S')
    RailsLongtail::LongTailFit.compute(@db)
    fit = @db.execute('SELECT long_tail_fit FROM companies WHERE id = ?', id).first.first
    assert_equal 1, fit
  end

  def test_marks_conference_sponsor_as_not_fit
    id = RailsLongtail::Company.upsert_by_domain(@db, domain: 'bigco.com', name: 'BigCo', headcount_tier: 'S')
    RailsLongtail::Signal.insert(@db, company_id: id, source: 'conf-sponsors', signal_type: 'sponsor')
    RailsLongtail::LongTailFit.compute(@db)
    fit = @db.execute('SELECT long_tail_fit FROM companies WHERE id = ?', id).first.first
    assert_equal 0, fit
  end

  def test_marks_large_headcount_as_not_fit
    id = RailsLongtail::Company.upsert_by_domain(@db, domain: 'large.com', name: 'Large', headcount_tier: 'L')
    RailsLongtail::LongTailFit.compute(@db)
    fit = @db.execute('SELECT long_tail_fit FROM companies WHERE id = ?', id).first.first
    assert_equal 0, fit
  end

  def test_leaves_unknown_headcount_as_null
    id = RailsLongtail::Company.upsert_by_domain(@db, domain: 'unknown.com', name: 'Unknown')
    RailsLongtail::LongTailFit.compute(@db)
    fit = @db.execute('SELECT long_tail_fit FROM companies WHERE id = ?', id).first.first
    assert_nil fit
  end
end

Create tooling/rails_longtail/lib/rails_longtail/long_tail_fit.rb:

# frozen_string_literal: true

module RailsLongtail
  module LongTailFit
    SMALL_TIERS = %w[XS S M].freeze

    def self.compute(db)
      db.execute(<<~SQL)
        UPDATE companies
        SET long_tail_fit = 1, updated_at = CURRENT_TIMESTAMP
        WHERE headcount_tier IN (#{SMALL_TIERS.map { |t| "'#{t}'" }.join(',')})
          AND id NOT IN (
            SELECT DISTINCT company_id FROM signals WHERE source = 'conf-sponsors'
          )
      SQL

      db.execute(<<~SQL)
        UPDATE companies
        SET long_tail_fit = 0, updated_at = CURRENT_TIMESTAMP
        WHERE (headcount_tier NOT IN (#{SMALL_TIERS.map { |t| "'#{t}'" }.join(',')})
               OR id IN (SELECT DISTINCT company_id FROM signals WHERE source = 'conf-sponsors'))
          AND headcount_tier IS NOT NULL
      SQL
    end
  end
end

Add require_relative 'rails_longtail/long_tail_fit' to lib/rails_longtail.rb; run bundle exec rake test TEST=test/long_tail_fit_test.rb and confirm green.

Create tooling/rails_longtail/bin/compute_long_tail_fit:

#!/usr/bin/env ruby
# frozen_string_literal: true

$LOAD_PATH.unshift(File.expand_path('../lib', __dir__))
require 'rails_longtail'

db = RailsLongtail::DB.open
RailsLongtail::LongTailFit.compute(db)
updated = db.execute('SELECT COUNT(*) FROM companies WHERE long_tail_fit IS NOT NULL').first.first
puts "long_tail_fit computed on #{updated} companies"
db.close

Make executable: chmod +x tooling/rails_longtail/bin/compute_long_tail_fit.

Create tooling/rails_longtail/test/audit_test.rb:

# frozen_string_literal: true

require 'test_helper'
require 'tmpdir'
require 'fileutils'

class AuditTest < Minitest::Test
  def setup
    @tmp = Dir.mktmpdir
    @db = RailsLongtail::DB.open(File.join(@tmp, 'test.sqlite'))
  end

  def teardown
    @db.close
    FileUtils.rm_rf(@tmp)
  end

  def test_flag_audit_marks_low_confidence
    id = RailsLongtail::Company.upsert_by_domain(@db, domain: 'a.com', name: 'A')
    @db.execute('INSERT INTO classifications (company_id, primary_vertical, business_model, confidence, classified_by) VALUES (?, ?, ?, ?, ?)',
                [id, 'Healthcare', 'vertical_saas', 0.4, 'claude-sonnet-4-6'])

    RailsLongtail::Audit.flag(@db)

    queued = @db.execute("SELECT company_id FROM audit_queue WHERE company_id = ?", id).first
    refute_nil queued
  end

  def test_flag_audit_includes_web_search_sourced
    id = RailsLongtail::Company.upsert_by_domain(@db, domain: 'b.com', name: 'B')
    RailsLongtail::Signal.insert(@db, company_id: id, source: 'web-search', signal_type: 'web_mention')
    @db.execute('INSERT INTO classifications (company_id, primary_vertical, business_model, confidence, classified_by) VALUES (?, ?, ?, ?, ?)',
                [id, 'Healthcare', 'vertical_saas', 0.85, 'claude-sonnet-4-6'])

    RailsLongtail::Audit.flag(@db, web_search_audit_rate: 1.0)

    queued = @db.execute("SELECT company_id FROM audit_queue WHERE company_id = ?", id).first
    refute_nil queued
  end
end

Create tooling/rails_longtail/lib/rails_longtail/audit.rb:

# frozen_string_literal: true

module RailsLongtail
  module Audit
    def self.flag(db, random_sample_rate: 0.05, web_search_audit_rate: 0.175)
      db.execute(<<~SQL)
        CREATE TABLE IF NOT EXISTS audit_queue (
          company_id INTEGER PRIMARY KEY REFERENCES companies(id),
          reason TEXT,
          queued_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
      SQL

      # Low confidence
      db.execute(<<~SQL)
        INSERT OR IGNORE INTO audit_queue (company_id, reason)
        SELECT company_id, 'low_confidence' FROM classifications WHERE confidence < 0.6
      SQL

      # Random sample per vertical (at least 5%)
      db.execute('SELECT DISTINCT primary_vertical FROM classifications WHERE primary_vertical IS NOT NULL').each do |(vertical)|
        ids = db.execute('SELECT company_id FROM classifications WHERE primary_vertical = ? ORDER BY RANDOM() LIMIT ?',
                         [vertical, [5, (count_for(db, vertical) * random_sample_rate).ceil].max])
        ids.each do |(cid)|
          db.execute('INSERT OR IGNORE INTO audit_queue (company_id, reason) VALUES (?, ?)', [cid, "random_sample_#{vertical}"])
        end
      end

      # Web-search sourced
      ws_ids = db.execute(<<~SQL).flatten
        SELECT DISTINCT s.company_id
        FROM signals s JOIN classifications cl ON cl.company_id = s.company_id
        WHERE s.source = 'web-search'
      SQL
      sampled = ws_ids.select { |_| rand < web_search_audit_rate }
      sampled.each do |cid|
        db.execute('INSERT OR IGNORE INTO audit_queue (company_id, reason) VALUES (?, ?)', [cid, 'web_search_source'])
      end
    end

    def self.count_for(db, vertical)
      db.execute('SELECT COUNT(*) FROM classifications WHERE primary_vertical = ?', [vertical]).first.first
    end
  end
end

Add require_relative 'rails_longtail/audit' to lib/rails_longtail.rb; run bundle exec rake test TEST=test/audit_test.rb and confirm green.

Create tooling/rails_longtail/bin/flag_audit:

#!/usr/bin/env ruby
# frozen_string_literal: true

$LOAD_PATH.unshift(File.expand_path('../lib', __dir__))
require 'rails_longtail'

db = RailsLongtail::DB.open
RailsLongtail::Audit.flag(db)
queued = db.execute('SELECT COUNT(*) FROM audit_queue').first.first
puts "audit_queue size: #{queued}"
db.close

Make executable.

git add tooling/rails_longtail/lib/rails_longtail/long_tail_fit.rb \
  tooling/rails_longtail/lib/rails_longtail/audit.rb \
  tooling/rails_longtail/bin/compute_long_tail_fit tooling/rails_longtail/bin/flag_audit \
  tooling/rails_longtail/test/long_tail_fit_test.rb tooling/rails_longtail/test/audit_test.rb \
  tooling/rails_longtail/lib/rails_longtail.rb
git commit -m "add long-tail-fit computation and audit flagging"

Task 14: Scorecard views + CSV exports

Files:

Scope note: The spec’s five scorecard dimensions are Rails density, Activity, Long-tail fit, Warm-network overlap, and Competitive density. The first three are computed from the SQLite data and printed by bin/run_scorecard. The last two (warm-network overlap, competitive density) are manual overlays Rick adds by hand in RAILS_LONGTAIL_MAP.md during Phase 2 — they pull from RICK_GORMAN_PROFILE.md and Rick’s judgment about consultancy competition, neither of which this pipeline ingests. Do NOT try to automate them.

Create tooling/rails_longtail/test/scorecard_test.rb:

# frozen_string_literal: true

require 'test_helper'
require 'tmpdir'
require 'fileutils'

class ScorecardTest < Minitest::Test
  def setup
    @tmp = Dir.mktmpdir
    @db = RailsLongtail::DB.open(File.join(@tmp, 'test.sqlite'))

    3.times do |i|
      id = RailsLongtail::Company.upsert_by_domain(@db, domain: "h#{i}.com", name: "H#{i}", headcount_tier: 'S')
      @db.execute('INSERT INTO classifications (company_id, primary_vertical, business_model, confidence, classified_by) VALUES (?, ?, ?, ?, ?)',
                  [id, 'Healthcare', 'vertical_saas', 0.8, 'claude-sonnet-4-6'])
      RailsLongtail::Signal.insert(@db, company_id: id, source: 'hn-hiring', signal_type: 'job_post', signal_date: Date.today.to_s)
    end
    RailsLongtail::LongTailFit.compute(@db)
  end

  def teardown
    @db.close
    FileUtils.rm_rf(@tmp)
  end

  def test_scorecard_reports_rails_density_and_activity
    row = RailsLongtail::Scorecard.rows(@db).find { |r| r[:vertical] == 'Healthcare' }
    assert_equal 3, row[:rails_density]
    assert_in_delta 1.0, row[:activity_rate], 0.001
    assert_in_delta 1.0, row[:long_tail_fit_rate], 0.001
  end

  def test_overlap_rates_counts_source_intersection_per_vertical
    only_github_id = RailsLongtail::Company.upsert_by_domain(@db, domain: 'ghonly.com', name: 'GHOnly', headcount_tier: 'S')
    @db.execute('INSERT INTO classifications (company_id, primary_vertical, business_model, confidence, classified_by) VALUES (?, ?, ?, ?, ?)',
                [only_github_id, 'BFSI', 'b2b_saas', 0.8, 'claude-sonnet-4-6'])
    RailsLongtail::Signal.insert(@db, company_id: only_github_id, source: 'github', signal_type: 'code')

    both_id = RailsLongtail::Company.upsert_by_domain(@db, domain: 'both.com', name: 'Both', headcount_tier: 'S')
    @db.execute('INSERT INTO classifications (company_id, primary_vertical, business_model, confidence, classified_by) VALUES (?, ?, ?, ?, ?)',
                [both_id, 'BFSI', 'b2b_saas', 0.8, 'claude-sonnet-4-6'])
    RailsLongtail::Signal.insert(@db, company_id: both_id, source: 'github', signal_type: 'code')
    RailsLongtail::Signal.insert(@db, company_id: both_id, source: 'hn-hiring', signal_type: 'job_post')

    bfsi = RailsLongtail::Scorecard.overlap_rates(@db).find { |r| r[:vertical] == 'BFSI' }
    assert_equal 1, bfsi[:github_only]
    assert_equal 0, bfsi[:jobs_only]
    assert_equal 1, bfsi[:both]
    assert_in_delta 0.5, bfsi[:overlap_rate], 0.001
  end
end

Create tooling/rails_longtail/lib/rails_longtail/scorecard.rb:

# frozen_string_literal: true

module RailsLongtail
  module Scorecard
    def self.rows(db)
      verticals = Classification::VERTICALS
      verticals.map do |v|
        density = db.execute('SELECT COUNT(*) FROM classifications WHERE primary_vertical = ?', [v]).first.first

        next {
          vertical: v,
          rails_density: 0,
          activity_rate: 0.0,
          long_tail_fit_rate: 0.0
        } if density.zero?

        active = db.execute(<<~SQL, [v]).first.first
          SELECT COUNT(DISTINCT cl.company_id)
          FROM classifications cl
          JOIN signals s ON s.company_id = cl.company_id
          WHERE cl.primary_vertical = ?
            AND s.signal_type = 'job_post'
            AND s.signal_date >= date('now','-365 day')
        SQL

        ltf = db.execute(<<~SQL, [v]).first.first
          SELECT COUNT(*)
          FROM classifications cl
          JOIN companies c ON c.id = cl.company_id
          WHERE cl.primary_vertical = ? AND c.long_tail_fit = 1
        SQL

        {
          vertical: v,
          rails_density: density,
          activity_rate: (active.to_f / density).round(3),
          long_tail_fit_rate: (ltf.to_f / density).round(3)
        }
      end
    end

    def self.print(db, out = $stdout)
      fmt = "%-32s %10s %12s %14s\n"
      out.printf(fmt, 'Vertical', 'Density', 'Activity', 'LongTailFit')
      rows(db).each { |r| out.printf(fmt, r[:vertical], r[:rails_density], r[:activity_rate], r[:long_tail_fit_rate]) }
    end

    # Capture-recapture overlap between github and job-board sources, per vertical.
    # Reported for spec Section 11 "Verification".
    def self.overlap_rates(db)
      Classification::VERTICALS.map do |v|
        gh_ids = db.execute(<<~SQL, [v]).flatten.to_set
          SELECT DISTINCT cl.company_id FROM classifications cl
          JOIN signals s ON s.company_id = cl.company_id
          WHERE cl.primary_vertical = ? AND s.source = 'github'
        SQL
        job_ids = db.execute(<<~SQL, [v]).flatten.to_set
          SELECT DISTINCT cl.company_id FROM classifications cl
          JOIN signals s ON s.company_id = cl.company_id
          WHERE cl.primary_vertical = ? AND s.source IN ('hn-hiring','wwr','remoteok','board-dorks')
        SQL

        union = (gh_ids | job_ids).size
        intersection = (gh_ids & job_ids).size
        rate = union.zero? ? 0.0 : (intersection.to_f / union).round(3)

        { vertical: v, github_only: (gh_ids - job_ids).size, jobs_only: (job_ids - gh_ids).size, both: intersection, overlap_rate: rate }
      end
    end
  end
end

Add require 'set' to the top of scorecard.rb (above the module).

Add require; run test green.

Create tooling/rails_longtail/bin/run_scorecard:

#!/usr/bin/env ruby
# frozen_string_literal: true

$LOAD_PATH.unshift(File.expand_path('../lib', __dir__))
require 'rails_longtail'

db = RailsLongtail::DB.open
RailsLongtail::Scorecard.print(db)

puts ''
puts 'Capture-recapture overlap (github vs job boards):'
printf("%-32s %10s %10s %10s %10s\n", 'Vertical', 'GH only', 'Jobs only', 'Both', 'Overlap')
RailsLongtail::Scorecard.overlap_rates(db).each do |r|
  printf("%-32s %10d %10d %10d %10.3f\n", r[:vertical], r[:github_only], r[:jobs_only], r[:both], r[:overlap_rate])
end

db.close

Create tooling/rails_longtail/bin/export_csvs:

#!/usr/bin/env ruby
# frozen_string_literal: true

require 'csv'
require 'fileutils'
$LOAD_PATH.unshift(File.expand_path('../lib', __dir__))
require 'rails_longtail'

out_dir = File.expand_path('../exports', __dir__)
FileUtils.mkdir_p(out_dir)

db = RailsLongtail::DB.open

CSV.open(File.join(out_dir, 'shallow_by_vertical.csv'), 'w') do |csv|
  csv << %w[vertical company_id domain name business_model headcount_tier long_tail_fit confidence]
  db.execute(<<~SQL).each do |row|
    SELECT cl.primary_vertical, c.id, c.domain, c.name, cl.business_model, c.headcount_tier, c.long_tail_fit, cl.confidence
    FROM classifications cl JOIN companies c ON c.id = cl.company_id
    WHERE cl.primary_vertical IS NOT NULL
    ORDER BY cl.primary_vertical, cl.confidence DESC
  SQL
    csv << row
  end
end

puts "wrote exports/shallow_by_vertical.csv"
db.close

Make executable; commit.

Task 15: Web-search merge, runbooks, README

Files:

Create tooling/rails_longtail/test/web_search_test.rb:

# frozen_string_literal: true

require 'test_helper'
require 'tmpdir'
require 'fileutils'
require 'json'

class WebSearchTest < Minitest::Test
  def setup
    @tmp = Dir.mktmpdir
    @db = RailsLongtail::DB.open(File.join(@tmp, 'test.sqlite'))
  end

  def teardown
    @db.close
    FileUtils.rm_rf(@tmp)
  end

  def test_merge_upserts_company_and_appends_web_mention_signal
    jsonl = [
      { domain: 'acme.com', name: 'Acme', evidence_url: 'https://example.com/post', raw_text: 'Built with Rails for healthcare' }
    ].map(&:to_json).join("\n")
    path = File.join(@tmp, 'healthcare.jsonl')
    File.write(path, jsonl)

    added = RailsLongtail::WebSearch.merge_file(@db, path)

    assert_equal 1, added
    row = @db.execute("SELECT c.domain, s.source, s.signal_type FROM signals s JOIN companies c ON c.id=s.company_id").first
    assert_equal ['acme.com', 'web-search', 'web_mention'], row
  end

  def test_merge_skips_malformed_rows
    File.write(File.join(@tmp, 'bad.jsonl'), "not-json\n{\"domain\":\"ok.com\",\"name\":\"OK\",\"evidence_url\":\"https://x\"}\n")

    added = RailsLongtail::WebSearch.merge_file(@db, File.join(@tmp, 'bad.jsonl'))

    assert_equal 1, added
  end
end

Run: bundle exec rake test TEST=test/web_search_test.rb Expected: FAIL on uninitialized constant RailsLongtail::WebSearch.

Create tooling/rails_longtail/lib/rails_longtail/web_search.rb:

# frozen_string_literal: true

require 'json'

module RailsLongtail
  module WebSearch
    def self.merge_file(db, path)
      added = 0

      File.foreach(path) do |line|
        line = line.strip
        next if line.empty?

        begin
          row = JSON.parse(line)
        rescue JSON::ParserError
          next
        end

        domain = row['domain'].to_s.strip.downcase
        name = row['name'].to_s.strip
        next if domain.empty? || name.empty?

        company_id = Company.upsert_by_domain(db, domain: domain, name: name)
        Signal.insert(
          db,
          company_id: company_id,
          source: 'web-search',
          signal_type: 'web_mention',
          evidence_url: row['evidence_url'],
          raw_text: row['raw_text'].to_s[0, 500]
        )
        added += 1
      end

      added
    end

    def self.merge_dir(db, dir)
      total = 0
      Dir.glob(File.join(dir, '*.jsonl')).sort.each do |path|
        total += merge_file(db, path)
      end
      total
    end
  end
end

Add require_relative 'rails_longtail/web_search' to lib/rails_longtail.rb.

Run: bundle exec rake test TEST=test/web_search_test.rb Expected: 2 tests, 0 failures.

Create tooling/rails_longtail/bin/merge_web_search:

#!/usr/bin/env ruby
# frozen_string_literal: true

$LOAD_PATH.unshift(File.expand_path('../lib', __dir__))
require 'rails_longtail'

dir = File.expand_path('../data/web_search', __dir__)
db = RailsLongtail::DB.open

run_id = RailsLongtail::SourceRun.start(db, 'web-search')
added = RailsLongtail::WebSearch.merge_dir(db, dir)
RailsLongtail::SourceRun.finish(db, run_id, rows_added: added, notes: "merged from #{dir}")

puts "web-search: #{added} signals merged"
db.close

Make executable.

Content:

# Web-Search Ingest Runbook (Source #6)

Web search is NOT a Ruby script — it's run from Claude Code using the WebSearch tool dispatched to Sonnet subagents. Each subagent returns JSON company rows that a merge step (below) inserts as signals.

## Query batch (edit as needed)

For each of the 10 verticals, dispatch one Sonnet subagent with a prompt like:

> You will run these Google/DuckDuckGo queries and extract every distinct company mentioned as a Rails user. Output one JSON line per company to `data/web_search/<vertical>.jsonl`.
>
> Queries (substitute `<V>` with the vertical keyword, e.g., `healthcare`, `fintech`, `auto`):
> 1. `"built with Rails" <V> site:*.com -github.com`
> 2. `"our Rails app" <V> "engineering blog"`
> 3. `"Ruby on Rails" <V> "case study" site:thoughtbot.com OR site:planetargon.com OR site:testdouble.com`
> 4. `"Ruby on Rails" <V> podcast guest`
> 5. `"Ruby on Rails" <V> site:dev.to`
>
> For each hit that mentions a specific company + Rails usage, output:
> ```json
> {"domain": "acme.com", "name": "Acme", "evidence_url": "https://...", "raw_text": "...first 500 chars..."}
> ```

Fan out all 10 subagents in parallel (single message, multiple Agent tool calls).

## Merge step

Run `bin/merge_web_search` to walk `data/web_search/*.jsonl` and upsert each row into `companies` + `signals` with `source='web-search'`, `signal_type='web_mention'`.

## Audit

This source is the noisiest. When you run `bin/flag_audit` it samples 15-20% of web-search-sourced classifications for manual audit.

Content:

# Phase 3 Runbook — Deep Dive in Chosen Niche

**Prerequisites:** Phase 2 complete. `RAILS_LONGTAIL_MAP.md` records the chosen niche.

This runbook is a TEMPLATE. Fill in vertical-specific sources at pick time.

## Step 1: Add 2-3 vertical-specific sources

Examples:
- Healthcare vertical-SaaS → HIMSS member directory, KLAS Research vendor lists, medical-tech engineering-blog rings.
- B2B dev-tools → YC dev-tools batch alumni list, StackShare dev-tools category, GitHub Marketplace listings.

Create scraper files under `lib/rails_longtail/deep/<source_name>.rb` following the same upsert pattern as shallow sources. Record signals with a distinct `source` string (e.g., `himss`, `klas`).

## Step 2: Re-run shallow sources with tighter filters

Modify each scraper's query to include niche keywords. Examples for healthcare:
- Github: add `"HIPAA"`, `"FHIR"`, `"EHR"` to search query
- HN Hiring parser: pre-filter comments for `healthcare`, `clinical`, `HIPAA`, `EHR`
- Board dorks: add `"HIPAA"` to the query line

## Step 3: Manual classification

No LLM in this phase. Open the companies table filtered to the chosen vertical:
```sql
SELECT c.name, c.domain, c.long_tail_fit, c.headcount_tier, cl.confidence
FROM classifications cl JOIN companies c ON c.id = cl.company_id
WHERE cl.primary_vertical = '<niche>'
ORDER BY c.long_tail_fit DESC, cl.confidence ASC;

Rick reviews each row, removes false positives, and sets classified_by = 'human:rick' on anything manually verified.

Step 4: Lightweight contact enrichment (optional, still no paid services)

For each surviving company:

Step 5: Export

bin/export_csvs
# then rename: mv exports/shallow_by_vertical.csv exports/deep_<niche>.csv

Output target

500-1,500 curated long-tail rows in one vertical, ready for Phase 4 contact enrichment (separate plan).


- [ ] **Step 3: Write README.md**

Content:
```markdown
# Rails Long-Tail Discovery Pipeline

Discovers Rails-using companies across ten verticals, classifies them, and produces a scorecard to help pick one niche to target for the April 2026 outreach campaign. See spec at `docs/superpowers/specs/2026-04-16-rails-longtail-discovery-design.md`.

## Setup

```bash
cd tooling/rails_longtail
bundle install
export GITHUB_TOKEN=<your GitHub classic PAT with public_repo + read:org scopes>
bin/bootstrap_db

Phase 1: Shallow-phase ingest (run once)

bin/ingest_github
bin/ingest_hn_hiring
bin/ingest_job_feeds
bin/ingest_board_dorks
bin/ingest_rails_foundation
bin/ingest_conferences
bin/ingest_gems
# Web search: see docs/web_search_runbook.md

Phase 1: Classification

See docs/classification_runbook.md. Summary:

bin/export_classify_batches
# dispatch Sonnet subagents from Claude Code using docs/classification_prompt.md
bin/merge_classifications
bin/compute_long_tail_fit
bin/flag_audit

Phase 2: Niche pick

bin/run_scorecard
bin/export_csvs

Review exports/shallow_by_vertical.csv + the scorecard output, then update RAILS_LONGTAIL_MAP.md with the chosen niche and rationale.

Phase 3: Deep dive

See docs/phase3_runbook.md. A separate implementation plan is authored at niche-pick time.

Testing

bundle exec rake test

Data locations

git add tooling/rails_longtail/lib/rails_longtail/web_search.rb \
  tooling/rails_longtail/bin/merge_web_search \
  tooling/rails_longtail/test/web_search_test.rb \
  tooling/rails_longtail/docs tooling/rails_longtail/README.md \
  tooling/rails_longtail/lib/rails_longtail.rb
git commit -m "add web-search merge, runbooks, and README"

Self-Review Checklist

After completing all 15 tasks, run this verification:

Next (Out of Scope for This Plan)