1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
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}"