Three-phase extraction (bookmarks → activities → objects) streams
a pg_dump without loading it into memory. Raindrop uploader batches
in groups of 100 with tag preservation and content excerpts.

Assisted-by: Claude Opus 4.6 via pi

diff --git a/akkoma-bookmarks/extract_bookmarks.rb b/akkoma-bookmarks/extract_bookmarks.rb
new file mode 100644
index 0000000..d2c1dc0
--- /dev/null
+++ b/akkoma-bookmarks/extract_bookmarks.rb
@@ -0,0 +1,170 @@
+#!/usr/bin/env ruby
+# frozen_string_literal: true
+
+# Extract bookmarked posts from an Akkoma SQL dump.
+#
+# Phase 1: collect bookmark rows (activity_id + bookmarked_at)
+# Phase 2: stream activities to map activity_id → object URL
+# Phase 3: stream objects to resolve object URL → full object data
+#
+# Output: bookmarks.json — an array of bookmark records ready for Raindrop import.
+
+require "json"
+
+DUMP = File.join(__dir__, "akkoma_dump.sql")
+
+# ── Phase 1: Extract bookmark rows ──────────────────────────────────────────
+
+bookmarks = [] # { activity_id:, bookmarked_at: }
+
+puts "Phase 1: extracting bookmark rows…"
+in_bookmarks = false
+File.foreach(DUMP) do |line|
+  if line.start_with?("COPY public.bookmarks ")
+    in_bookmarks = true
+    next
+  end
+
+  if in_bookmarks
+    break if line.start_with?("\\.")
+
+    # id \t user_id \t activity_id \t inserted_at \t updated_at
+    parts = line.chomp.split("\t")
+    bookmarks << { activity_id: parts[2], bookmarked_at: parts[3] }
+  end
+end
+
+puts "  found #{bookmarks.size} bookmarks"
+activity_ids = bookmarks.map { _1[:activity_id] }.to_set
+
+# ── Phase 2: Map activity_id → object URL ───────────────────────────────────
+
+activity_to_object = {} # activity_id → object URL (string)
+
+puts "Phase 2: resolving activity → object URL (streaming #{DUMP})…"
+in_activities = false
+File.foreach(DUMP) do |line|
+  if line.start_with?("COPY public.activities ")
+    in_activities = true
+    next
+  end
+
+  if in_activities
+    break if line.start_with?("\\.")
+
+    # id \t data(json) \t inserted_at \t updated_at \t local \t actor \t recipients
+    tab = line.index("\t")
+    activity_id = line[0, tab]
+    next unless activity_ids.include?(activity_id)
+
+    json_end = line.index("\t", tab + 1)
+    json_str = line[(tab + 1)...json_end].gsub("\\\\", "\\")
+    data = JSON.parse(json_str)
+    obj = data["object"]
+
+    if obj.is_a?(String)
+      activity_to_object[activity_id] = obj
+    elsif obj.is_a?(Hash) && obj["id"]
+      activity_to_object[activity_id] = obj["id"]
+    end
+  end
+end
+
+puts "  resolved #{activity_to_object.size}/#{activity_ids.size} activities"
+
+object_urls = activity_to_object.values.to_set
+
+# ── Phase 3: Resolve object URLs → object data ─────────────────────────────
+
+objects = {} # object URL → parsed JSON data
+
+puts "Phase 3: resolving objects (streaming #{DUMP})…"
+in_objects = false
+File.foreach(DUMP) do |line|
+  if line.start_with?("COPY public.objects ")
+    in_objects = true
+    next
+  end
+
+  if in_objects
+    break if line.start_with?("\\.")
+
+    # Quick pre-check: does this line contain any of our object URLs?
+    # (avoids JSON parsing every row in a potentially huge table)
+    next unless object_urls.any? { |url| line.include?(url) }
+
+    # id(bigint) \t data(json) \t inserted_at \t updated_at
+    tab = line.index("\t")
+    json_end = line.index("\t", tab + 1)
+    # pg_dump COPY TEXT format escapes backslashes: \\ → literal \
+    json_str = line[(tab + 1)...json_end].gsub("\\\\", "\\")
+    begin
+      data = JSON.parse(json_str)
+    rescue JSON::ParserError => e
+      match = line.match(/"id":\s*"([^"]+)"/)
+      next unless match && object_urls.include?(match[1])
+
+      warn "  WARN: malformed JSON for object #{match[1]}: #{e.message}"
+      next
+    end
+
+    if object_urls.include?(data["id"])
+      objects[data["id"]] = data
+    end
+  end
+end
+
+puts "  resolved #{objects.size}/#{object_urls.size} objects"
+
+# ── Phase 4: Assemble bookmark records ──────────────────────────────────────
+
+puts "Phase 4: assembling bookmark records…"
+
+results = bookmarks.filter_map do |bm|
+  object_url = activity_to_object[bm[:activity_id]]
+  next unless object_url
+
+  obj = objects[object_url]
+  next unless obj
+
+  # Strip HTML tags for a plain-text excerpt
+  content_html = obj["content"] || ""
+  content_text = content_html
+    .gsub(/<br\s*\/?>/, "\n")
+    .gsub(/<[^>]+>/, "")
+    .gsub(/&amp;/, "&")
+    .gsub(/&lt;/, "<")
+    .gsub(/&gt;/, ">")
+    .gsub(/&#39;/, "'")
+    .gsub(/&quot;/, '"')
+    .strip
+
+  # Extract plain URLs from the content
+  urls_in_content = content_html.scan(/href="([^"]+)"/).flatten
+    .reject { |u| u.include?("/tags/") || u.include?("/users/") || u.start_with?("https://www.w3.org") }
+
+  # Tags/hashtags
+  tags = Array(obj["tag"])
+    .filter_map { |t| t.is_a?(Hash) ? t["name"]&.delete_prefix("#") : (t.is_a?(String) ? t : nil) }
+    .map(&:downcase)
+    .uniq
+
+  {
+    bookmarked_at: bm[:bookmarked_at],
+    post_url: obj["url"] || obj["id"],
+    author: obj["attributedTo"],
+    published: obj["published"],
+    content_html: content_html,
+    content_text: content_text,
+    tags: tags,
+    urls_in_content: urls_in_content,
+    sensitive: obj["sensitive"] || false,
+    attachments: Array(obj["attachment"]).map { |a| { url: a["url"], type: a["mediaType"] } },
+  }
+end
+
+results.sort_by! { _1[:bookmarked_at] }
+
+output_path = File.join(__dir__, "bookmarks.json")
+File.write(output_path, JSON.pretty_generate(results))
+puts "Wrote #{results.size} bookmarks to #{output_path}"
diff --git a/akkoma-bookmarks/upload_to_raindrop.rb b/akkoma-bookmarks/upload_to_raindrop.rb
new file mode 100644
index 0000000..4d6b4db
--- /dev/null
+++ b/akkoma-bookmarks/upload_to_raindrop.rb
@@ -0,0 +1,157 @@
+#!/usr/bin/env ruby
+# frozen_string_literal: true
+
+# Upload extracted Akkoma bookmarks to Raindrop.io
+#
+# Prerequisites:
+#   1. Create a Raindrop.io app at https://app.raindrop.io/settings/integrations
+#   2. Generate a "test token" (personal access token)
+#   3. Set RAINDROP_TOKEN env var, or create a .raindrop_token file
+#
+# Usage:
+#   RAINDROP_TOKEN=xxx ruby upload_to_raindrop.rb [--dry-run] [--collection NAME]
+#
+# Each bookmark becomes a Raindrop entry linking to the original fedi post.
+# The post content is stored as the excerpt, tags are preserved,
+# and any URLs from the post content are listed in a note.
+
+require "json"
+require "net/http"
+require "uri"
+require "time"
+
+BOOKMARKS_FILE = File.join(__dir__, "bookmarks.json")
+
+# ── Config ──────────────────────────────────────────────────────────────────
+
+def raindrop_token
+  ENV["RAINDROP_TOKEN"] ||
+    (File.read(File.join(__dir__, ".raindrop_token")).strip rescue nil) ||
+    abort("Set RAINDROP_TOKEN env var or create .raindrop_token file")
+end
+
+DRY_RUN = ARGV.include?("--dry-run")
+COLLECTION_NAME = if (idx = ARGV.index("--collection"))
+  ARGV[idx + 1] || abort("--collection requires a name")
+else
+  "Akkoma Bookmarks"
+end
+
+# ── Raindrop API helpers ────────────────────────────────────────────────────
+
+API_BASE = URI("https://api.raindrop.io/rest/v1")
+
+def api_request(method, path, body: nil)
+  uri = URI("#{API_BASE}#{path}")
+  http = Net::HTTP.new(uri.host, uri.port)
+  http.use_ssl = true
+
+  req = case method
+  when :get    then Net::HTTP::Get.new(uri)
+  when :post   then Net::HTTP::Post.new(uri)
+  when :put    then Net::HTTP::Put.new(uri)
+  end
+
+  req["Authorization"] = "Bearer #{raindrop_token}"
+  req["Content-Type"] = "application/json"
+
+  if body
+    req.body = JSON.generate(body)
+  end
+
+  response = http.request(req)
+
+  unless response.is_a?(Net::HTTPSuccess)
+    warn "API error #{response.code}: #{response.body}"
+    return nil
+  end
+
+  JSON.parse(response.body)
+end
+
+# ── Find or create collection ───────────────────────────────────────────────
+
+def find_or_create_collection(name)
+  # List root collections
+  result = api_request(:get, "/collections")
+  return nil unless result
+
+  existing = result["items"]&.find { |c| c["title"] == name }
+  return existing["_id"] if existing
+
+  # Create it
+  result = api_request(:post, "/collection", body: {
+    title: name,
+    sort: 0,         # manual sort
+    public: false,
+    view: "list",
+  })
+
+  result&.dig("item", "_id")
+end
+
+# ── Build raindrop payload ──────────────────────────────────────────────────
+
+def build_raindrop(bookmark, collection_id)
+  link = bookmark["post_url"]
+
+  note = bookmark["content_text"]
+
+  tags = bookmark["tags"].dup
+  tags << "akkoma-bookmark"  # easy to find them all later
+
+  created = Time.parse(bookmark["bookmarked_at"]).utc.iso8601
+
+  {
+    link: link,
+    note: note,
+    tags: tags,
+    collection: { "$id" => collection_id },
+    created: created,
+    pleaseParse: {},  # ask Raindrop to fetch metadata/favicon
+  }
+end
+
+# ── Main ────────────────────────────────────────────────────────────────────
+
+bookmarks = JSON.parse(File.read(BOOKMARKS_FILE))
+puts "Loaded #{bookmarks.size} bookmarks from #{BOOKMARKS_FILE}"
+
+if DRY_RUN
+  puts "[DRY RUN] Would upload #{bookmarks.size} bookmarks to collection '#{COLLECTION_NAME}'"
+  bookmarks.each_with_index do |bm, i|
+    puts "  #{i + 1}. #{bm["post_url"]}"
+    puts "     #{bm["content_text"][0, 80]}..."
+  end
+  exit
+end
+
+collection_id = find_or_create_collection(COLLECTION_NAME)
+abort "Failed to find/create collection '#{COLLECTION_NAME}'" unless collection_id
+puts "Using collection '#{COLLECTION_NAME}' (id: #{collection_id})"
+
+# Upload in batches — Raindrop supports bulk create via POST /rest/v1/raindrops
+# (up to 100 items per request)
+created = 0
+failed = 0
+
+bookmarks.each_slice(100).with_index do |batch, batch_idx|
+  items = batch.map { |bm| build_raindrop(bm, collection_id) }
+
+  puts "Uploading batch #{batch_idx + 1} (#{items.size} items)…"
+
+  result = api_request(:post, "/raindrops", body: { items: items })
+
+  if result && result["items"]
+    created += result["items"].size
+    puts "  ✓ created #{result["items"].size} raindrops"
+  else
+    failed += batch.size
+    puts "  ✗ batch failed"
+  end
+
+  sleep 1  # be polite to the API
+end
+
+puts
+puts "Done! Created #{created} raindrops, #{failed} failed."