#314

#329

Co-authored-by: miteruzo <miteruzo@naver.com>
Reviewed-on: #338

backend/Gemfile

@@ -50,8 +50,6 @@ group :development, :test do
   gem 'factory_bot_rails'
 end
 
-
-
 gem "mysql2", "~> 0.5.6"
 
 gem "image_processing", "~> 1.14"
@@ -69,3 +67,5 @@ gem 'whenever', require: false
 gem 'discard'
 
 gem "rspec-rails", "~> 8.0", :groups => [:development, :test]
+
+gem 'aws-sdk-s3', require: false

backend/Gemfile.lock

@@ -73,6 +73,25 @@ GEM
       tzinfo (~> 2.0, >= 2.0.5)
       uri (>= 0.13.1)
     ast (2.4.3)
+    aws-eventstream (1.4.0)
+    aws-partitions (1.1231.0)
+    aws-sdk-core (3.244.0)
+      aws-eventstream (~> 1, >= 1.3.0)
+      aws-partitions (~> 1, >= 1.992.0)
+      aws-sigv4 (~> 1.9)
+      base64
+      bigdecimal
+      jmespath (~> 1, >= 1.6.1)
+      logger
+    aws-sdk-kms (1.123.0)
+      aws-sdk-core (~> 3, >= 3.244.0)
+      aws-sigv4 (~> 1.5)
+    aws-sdk-s3 (1.217.0)
+      aws-sdk-core (~> 3, >= 3.244.0)
+      aws-sdk-kms (~> 1)
+      aws-sigv4 (~> 1.5)
+    aws-sigv4 (1.12.1)
+      aws-eventstream (~> 1, >= 1.0.2)
     base64 (0.2.0)
     bcrypt_pbkdf (1.1.1)
     bcrypt_pbkdf (1.1.1-arm64-darwin)
@@ -157,6 +176,7 @@ GEM
       pp (>= 0.6.0)
       rdoc (>= 4.0.0)
       reline (>= 0.4.2)
+    jmespath (1.6.2)
     json (2.12.0)
     jwt (2.10.1)
       base64
@@ -441,6 +461,7 @@ PLATFORMS
   x86_64-linux-musl
 
 DEPENDENCIES
+  aws-sdk-s3
   bootsnap
   brakeman
   diff-lcs

backend/app/controllers/materials_controller.rb

@@ -0,0 +1,101 @@
+class MaterialsController < ApplicationController
+  def index
+    page = (params[:page].presence || 1).to_i
+    limit = (params[:limit].presence || 20).to_i
+
+    page = 1 if page < 1
+    limit = 1 if limit < 1
+
+    offset = (page - 1) * limit
+
+    tag_id = params[:tag_id].presence
+    parent_id = params[:parent_id].presence
+
+    q = Material.includes(:tag, :created_by_user).with_attached_file
+    q = q.where(tag_id:) if tag_id
+    q = q.where(parent_id:) if parent_id
+
+    count = q.count
+    materials = q.order(created_at: :desc, id: :desc).limit(limit).offset(offset)
+
+    render json: { materials: MaterialRepr.many(materials, host: request.base_url), count: count }
+  end
+
+  def show
+    material =
+        Material
+        .includes(:tag)
+        .with_attached_file
+        .find_by(id: params[:id])
+    return head :not_found unless material
+
+    wiki_page_body = material.tag.tag_name.wiki_page&.current_revision&.body
+
+    render json: MaterialRepr.base(material, host: request.base_url).merge(wiki_page_body:)
+  end
+
+  def create
+    return head :unauthorized unless current_user
+
+    tag_name_raw = params[:tag].to_s.strip
+    file = params[:file]
+    url = params[:url].to_s.strip.presence
+    return head :bad_request if tag_name_raw.blank? || (file.blank? && url.blank?)
+
+    tag_name = TagName.find_undiscard_or_create_by!(name: tag_name_raw)
+    tag = tag_name.tag
+    tag = Tag.create!(tag_name:, category: :material) unless tag
+
+    material = Material.new(tag:, url:,
+                            created_by_user: current_user,
+                            updated_by_user: current_user)
+    material.file.attach(file)
+
+    if material.save
+      render json: MaterialRepr.base(material, host: request.base_url), status: :created
+    else
+      render json: { errors: material.errors.full_messages }, status: :unprocessable_entity
+    end
+  end
+
+  def update
+    return head :unauthorized unless current_user
+    return head :forbidden unless current_user.gte_member?
+
+    material = Material.with_attached_file.find_by(id: params[:id])
+    return head :not_found unless material
+
+    tag_name_raw = params[:tag].to_s.strip
+    file = params[:file]
+    url = params[:url].to_s.strip.presence
+    return head :bad_request if tag_name_raw.blank? || (file.blank? && url.blank?)
+
+    tag_name = TagName.find_undiscard_or_create_by!(name: tag_name_raw)
+    tag = tag_name.tag
+    tag = Tag.create!(tag_name:, category: :material) unless tag
+
+    material.update!(tag:, url:, updated_by_user: current_user)
+    if file
+      material.file.attach(file)
+    else
+      material.file.purge
+    end
+
+    if material.save
+      render json: MaterialRepr.base(material, host: request.base_url)
+    else
+      render json: { errors: material.errors.full_messages }, status: :unprocessable_entity
+    end
+  end
+
+  def destroy
+    return head :unauthorized unless current_user
+    return head :forbidden unless current_user.gte_member?
+
+    material = Material.find_by(id: params[:id])
+    return head :not_found unless material
+
+    material.discard
+    head :no_content
+  end
+end

backend/app/controllers/nico_tags_controller.rb

@@ -30,15 +30,22 @@ class NicoTagsController < ApplicationController
     id = params[:id].to_i
 
     tag = Tag.find(id)
-    return head :bad_request if tag.category != 'nico'
+    return head :bad_request unless tag.nico?
 
-    linked_tag_names = params[:tags].to_s.split(' ')
-    linked_tags = Tag.normalise_tags(linked_tag_names, with_tagme: false)
-    return head :bad_request if linked_tags.any? { |t| t.category == 'nico' }
+    linked_tag_names = params[:tags].to_s.split
+    linked_tags = Tag.normalise_tags(linked_tag_names, with_tagme: false,
+                                                       with_no_deerjikist: false)
+    return head :bad_request if linked_tags.any? { |t| t.nico? }
+
+    ApplicationRecord.transaction do
+      TagVersioning.record_tag_snapshots!(linked_tags, created_by_user: current_user)
 
       tag.linked_tags = linked_tags
       tag.save!
 
+      NicoTagVersionRecorder.record!(tag:, event_type: :update, created_by_user: current_user)
+    end
+
     render json: tag.linked_tags.map { |t| TagRepr.base(t) }, status: :ok
   end
 end

backend/app/controllers/post_versions_controller.rb

@@ -0,0 +1,119 @@
+class PostVersionsController < ApplicationController
+  def index
+    post_id = params[:post].presence
+    tag_id = params[:tag].presence
+    page = (params[:page].presence || 1).to_i
+    limit = (params[:limit].presence || 20).to_i
+
+    page = 1 if page < 1
+    limit = 1 if limit < 1
+
+    offset = (page - 1) * limit
+
+    tag_name =
+        if tag_id
+          TagName.joins(:tag).find_by(tag: { id: tag_id })
+        end
+    return render json: { versions: [], count: 0 } if tag_id && tag_name.blank?
+
+    q = PostVersion.joins(<<~SQL.squish)
+          LEFT JOIN
+            post_versions prev
+            ON
+              prev.post_id = post_versions.post_id
+              AND prev.version_no = post_versions.version_no - 1
+        SQL
+        .select('post_versions.*', 'prev.title AS prev_title', 'prev.url AS prev_url',
+                'prev.thumbnail_base AS prev_thumbnail_base', 'prev.tags AS prev_tags',
+                'prev.original_created_from AS prev_original_created_from',
+                'prev.original_created_before AS prev_original_created_before')
+    q = q.where('post_versions.post_id = ?', post_id) if post_id
+    if tag_name
+      escaped = ActiveRecord::Base.sanitize_sql_like(tag_name.name)
+      q = q.where(("CONCAT(' ', post_versions.tags, ' ') LIKE :kw " +
+                   "OR CONCAT(' ', prev.tags, ' ') LIKE :kw"),
+                  kw: "% #{ escaped } %")
+    end
+
+    count = q.except(:select, :order, :limit, :offset).count
+
+    versions = q.order(Arel.sql('post_versions.created_at DESC, post_versions.id DESC'))
+                .limit(limit)
+                .offset(offset)
+
+    render json: { versions: serialise_versions(versions), count: }
+  end
+
+  private
+
+  def serialise_versions rows
+    user_ids = rows.map(&:created_by_user_id).compact.uniq
+    users_by_id = User.where(id: user_ids).pluck(:id, :name).to_h
+
+    rows.map do |row|
+      cur_tags = split_tags(row.tags)
+      prev_tags = split_tags(row.attributes['prev_tags'])
+
+      {
+        post_id: row.post_id,
+        version_no: row.version_no,
+        event_type: row.event_type,
+        title: {
+          current: row.title,
+          prev: row.attributes['prev_title']
+        },
+        url: {
+          current: row.url,
+          prev: row.attributes['prev_url']
+        },
+        thumbnail: {
+          current: nil,
+          prev: nil
+        },
+        thumbnail_base: {
+          current: row.thumbnail_base,
+          prev: row.attributes['prev_thumbnail_base']
+        },
+        tags: build_version_tags(cur_tags, prev_tags),
+        original_created_from: {
+          current: row.original_created_from&.iso8601,
+          prev: row.attributes['prev_original_created_from']&.iso8601
+        },
+        original_created_before: {
+          current: row.original_created_before&.iso8601,
+          prev: row.attributes['prev_original_created_before']&.iso8601
+        },
+        created_at: row.created_at.iso8601,
+        created_by_user:
+          if row.created_by_user_id
+            {
+              id: row.created_by_user_id,
+              name: users_by_id[row.created_by_user_id]
+            }
+          end
+      }
+    end
+  end
+
+  def build_version_tags(cur_tags, prev_tags)
+    (cur_tags | prev_tags).map do |name|
+      type =
+        if cur_tags.include?(name) && prev_tags.include?(name)
+          'context'
+        elsif cur_tags.include?(name)
+          'added'
+        else
+          'removed'
+        end
+
+      {
+        name:,
+        type:
+      }
+    end
+  end
+
+  def split_tags(tags)
+    tags.to_s.split(/\s+/).reject(&:blank?)
+  end
+end

backend/app/controllers/posts_controller.rb

@@ -44,7 +44,7 @@ class PostsController < ApplicationController
         filtered_posts
         .joins("LEFT JOIN (#{ pt_max_sql }) pt_max ON pt_max.post_id = posts.id")
         .reselect('posts.*', Arel.sql("#{ updated_at_all_sql } AS updated_at_all"))
-        .preload(tags: { tag_name: :wiki_page })
+        .preload(tags: [:materials, { tag_name: :wiki_page }])
         .with_attached_thumbnail
 
     q = q.where('posts.url LIKE ?', "%#{ url }%") if url
@@ -75,7 +75,7 @@ class PostsController < ApplicationController
         else
           "posts.#{ order[0] }"
         end
-    posts = q.order(Arel.sql("#{ sort_sql } #{ order[1] }, id #{ order[1] }"))
+    posts = q.order(Arel.sql("#{ sort_sql } #{ order[1] }, posts.id #{ order[1] }"))
              .limit(limit)
              .offset(offset)
              .to_a
@@ -95,28 +95,21 @@ class PostsController < ApplicationController
   end
 
   def random
-    post = filtered_posts.preload(tags: { tag_name: :wiki_page })
+    post = filtered_posts.preload(tags: [:materials, { tag_name: :wiki_page }])
                          .order('RAND()')
                          .first
     return head :not_found unless post
 
-    viewed = current_user&.viewed?(post) || false
-
-    render json: PostRepr.base(post).merge(viewed:)
+    render json: PostRepr.base(post, current_user)
   end
 
   def show
-    post = Post.includes(tags: { tag_name: :wiki_page }).find_by(id: params[:id])
+    post = Post.includes(tags: [:materials, { tag_name: :wiki_page }]).find_by(id: params[:id])
     return head :not_found unless post
 
-    viewed = current_user&.viewed?(post) || false
-
-    json = post.as_json
-    json['tags'] = build_tag_tree_for(post.tags)
-    json['related'] = post.related(limit: 20)
-    json['viewed'] = viewed
-
-    render json:
+    render json: PostRepr.base(post, current_user)
+                         .merge(tags: build_tag_tree_for(post.tags),
+                                related: post.related(limit: 20))
   end
 
   def create
@@ -134,17 +127,22 @@ class PostsController < ApplicationController
     post = Post.new(title:, url:, thumbnail_base: nil, uploaded_user: current_user,
                     original_created_from:, original_created_before:)
     post.thumbnail.attach(thumbnail)
-    if post.save
-      post.resized_thumbnail!
+
+    ApplicationRecord.transaction do
+      post.save!
       tags = Tag.normalise_tags(tag_names)
+      TagVersioning.record_tag_snapshots!(tags, created_by_user: current_user)
+
       tags = Tag.expand_parent_tags(tags)
       sync_post_tags!(post, tags)
+      post.resized_thumbnail!
+      PostVersionRecorder.record!(post:, event_type: :create, created_by_user: current_user)
+    end
 
     post.reload
     render json: PostRepr.base(post), status: :created
-    else
+  rescue ActiveRecord::RecordInvalid
     render json: { errors: post.errors.full_messages }, status: :unprocessable_entity
-    end
   rescue Tag::NicoTagNormalisationError
     head :bad_request
   end
@@ -173,25 +171,34 @@ class PostsController < ApplicationController
     original_created_before = params[:original_created_before]
 
     post = Post.find(params[:id].to_i)
-    if post.update(title:, original_created_from:, original_created_before:)
-      tags = post.tags.where(category: 'nico').to_a +
-             Tag.normalise_tags(tag_names, with_tagme: false)
+
+    ApplicationRecord.transaction do
+      PostVersionRecorder.ensure_snapshot!(post, created_by_user: current_user)
+
+      post.update!(title:, original_created_from:, original_created_before:)
+
+      normalised_tags = Tag.normalise_tags(tag_names, with_tagme: false)
+      TagVersioning.record_tag_snapshots!(normalised_tags, created_by_user: current_user)
+
+      tags = post.tags.nico.to_a + normalised_tags
       tags = Tag.expand_parent_tags(tags)
       sync_post_tags!(post, tags)
+      PostVersionRecorder.record!(post:, event_type: :update, created_by_user: current_user)
+    end
 
     post.reload
     json = post.as_json
     json['tags'] = build_tag_tree_for(post.tags)
     render json:, status: :ok
-    else
+  rescue ActiveRecord::RecordInvalid
     render json: post.errors, status: :unprocessable_entity
-    end
   rescue Tag::NicoTagNormalisationError
     head :bad_request
   end
 
   def changes
     id = params[:id].presence
+    tag_id = params[:tag].presence
     page = (params[:page].presence || 1).to_i
     limit = (params[:limit].presence || 20).to_i
 
@@ -202,8 +209,9 @@ class PostsController < ApplicationController
 
     pts = PostTag.with_discarded
     pts = pts.where(post_id: id) if id.present?
+    pts = pts.where(tag_id:) if tag_id.present?
     pts = pts.includes(:post, :created_user, :deleted_user,
-                       tag: { tag_name: :wiki_page })
+                       tag: [:materials, { tag_name: :wiki_page }])
 
     events = []
     pts.each do |pt|
@@ -245,18 +253,40 @@ class PostsController < ApplicationController
   end
 
   def filter_posts_by_tags tag_names, match_type
-    tag_names = TagName.canonicalise(tag_names)
+    literals = tag_names.map do |raw_name|
+      { name: TagName.canonicalise(raw_name.sub(/\Anot:/i, '')).first,
+        negative: raw_name.downcase.start_with?('not:') }
+    end
 
-    posts = Post.joins(tags: :tag_name)
+    return Post.all if literals.empty?
 
     if match_type == 'any'
-      posts.where(tag_names: { name: tag_names }).distinct
+      literals.reduce(Post.none) do |posts, literal|
+        posts.or(tag_literal_relation(literal[:name], negative: literal[:negative]))
+      end
     else
-      posts.where(tag_names: { name: tag_names })
-           .group('posts.id')
-           .having('COUNT(DISTINCT tag_names.id) = ?', tag_names.uniq.size)
+      literals.reduce(Post.all) do |posts, literal|
+        ids = tagged_post_ids_for(literal[:name])
+        if literal[:negative]
+          posts.where.not(id: ids)
+        else
+          posts.where(id: ids)
         end
       end
+    end
+  end
+
+  def tag_literal_relation name, negative:
+    ids = tagged_post_ids_for(name)
+    if negative
+      Post.where.not(id: ids)
+    else
+      Post.where(id: ids)
+    end
+  end
+
+  def tagged_post_ids_for(name) =
+      Post.joins(tags: :tag_name).where(tag_names: { name: }).select(:id)
 
   def sync_post_tags! post, desired_tags
     desired_tags.each do |t|

backend/app/controllers/tag_children_controller.rb

@@ -7,7 +7,16 @@ class TagChildrenController < ApplicationController
     child_id = params[:child_id]
     return head :bad_request if parent_id.blank? || child_id.blank?
 
-    Tag.find(parent_id).children << Tag.find(child_id) rescue nil
+    parent = Tag.find(parent_id)
+    child = Tag.find(child_id)
+    return head :bad_request if parent.nico? || child.nico?
+
+    ApplicationRecord.transaction do
+      TagVersioning.ensure_snapshot!(child, created_by_user: current_user)
+
+      TagImplication.find_or_create_by!(parent_tag: parent, tag: child)
+      TagVersionRecorder.record!(tag: child, event_type: :update, created_by_user: current_user)
+    end
 
     head :no_content
   end
@@ -20,7 +29,16 @@ class TagChildrenController < ApplicationController
     child_id = params[:child_id]
     return head :bad_request if parent_id.blank? || child_id.blank?
 
-    Tag.find(parent_id).children.delete(Tag.find(child_id)) rescue nil
+    parent = Tag.find(parent_id)
+    child = Tag.find(child_id)
+    return head :bad_request if parent.nico? || child.nico?
+
+    ApplicationRecord.transaction do
+      TagVersioning.ensure_snapshot!(child, created_by_user: current_user)
+
+      TagImplication.find_by(parent_tag: parent, tag: child)&.destroy!
+      TagVersionRecorder.record!(tag: child, event_type: :update, created_by_user: current_user)
+    end
 
     head :no_content
   end

backend/app/controllers/tag_versions_controller.rb

@@ -0,0 +1,92 @@
+class TagVersionsController < ApplicationController
+  def index
+    tag_id = params[:id].presence
+    page = (params[:page].presence || 1).to_i
+    limit = (params[:limit].presence || 20).to_i
+
+    page = 1 if page < 1
+    limit = 1 if limit < 1
+
+    offset = (page - 1) * limit
+
+    q = TagVersion.joins(<<~SQL.squish)
+          LEFT JOIN
+            tag_versions prev
+            ON
+              prev.tag_id = tag_versions.tag_id
+              AND prev.version_no = tag_versions.version_no - 1
+        SQL
+      .select('tag_versions.*', 'prev.name AS prev_name', 'prev.category AS prev_category',
+              'prev.aliases AS prev_aliases', 'prev.parent_tag_ids AS prev_parent_tag_ids')
+    q = q.where('tag_versions.tag_id = ?', tag_id) if tag_id
+
+    count = q.except(:select, :order, :limit, :offset).count
+
+    versions = q.order(Arel.sql('tag_versions.created_at DESC, tag_versions.id DESC'))
+                .limit(limit)
+                .offset(offset)
+
+    render json: { versions: serialise_versions(versions), count: }
+  end
+
+  private
+
+  def serialise_versions rows
+    user_ids = rows.map(&:created_by_user_id).compact.uniq
+    users_by_id = User.where(id: user_ids).pluck(:id, :name).to_h
+
+    rows.map do |row|
+      cur_aliases = split_values(row.aliases)
+      prev_aliases = split_values(row.attributes['prev_aliases'])
+
+      cur_parent_tag_ids = split_parent_tag_ids(row.parent_tag_ids)
+      prev_parent_tag_ids = split_parent_tag_ids(row.attributes['prev_parent_tag_ids'])
+
+      all_parent_tag_ids = (cur_parent_tag_ids | prev_parent_tag_ids)
+
+      tags_by_id =
+          Tag
+          .includes(:tag_name, :materials, { tag_name: :wiki_page })
+          .where(id: all_parent_tag_ids)
+          .index_by(&:id)
+
+      parent_tags =
+          build_version_values(cur_parent_tag_ids, prev_parent_tag_ids, key: :tag_id)
+          .map do |h|
+            { tag: TagRepr.base(tags_by_id[h[:tag_id]]),
+              type: h[:type] }
+          end
+
+      { tag_id: row.tag_id,
+        version_no: row.version_no,
+        event_type: row.event_type,
+        name: { current: row.name, prev: row.attributes['prev_name'] },
+        category: { current: row.category, prev: row.attributes['prev_category'] },
+        aliases: build_version_values(cur_aliases, prev_aliases, key: :name),
+        parent_tags:,
+        created_at: row.created_at.iso8601,
+        created_by_user: row.created_by_user_id &&
+                         { id: row.created_by_user_id,
+                           name: users_by_id[row.created_by_user_id] } }
+    end
+  end
+
+  def build_version_values cur_values, prev_values, key:
+    (cur_values | prev_values).map do |value|
+      type =
+          if cur_values.include?(value) && prev_values.include?(value)
+            'context'
+          elsif cur_values.include?(value)
+            'added'
+          else
+            'removed'
+          end
+
+      { key => value, type: }
+    end
+  end
+
+  def split_values(values) = values.to_s.split(/\s+/).reject(&:blank?)
+
+  def split_parent_tag_ids(values) = split_values(values).map(&:to_i)
+end

backend/app/controllers/tags_controller.rb

@@ -2,23 +2,113 @@ class TagsController < ApplicationController
   def index
     post_id = params[:post]
 
-    tags =
+    name = params[:name].presence
+    category = params[:category].presence
+    post_count_between = (params[:post_count_gte].presence || -1).to_i,
+                         (params[:post_count_lte].presence || -1).to_i
+    post_count_between[0] = nil if post_count_between[0] < 0
+    post_count_between[1] = nil if post_count_between[1] < 0
+    created_between = params[:created_from].presence, params[:created_to].presence
+    updated_between = params[:updated_from].presence, params[:updated_to].presence
+
+    order = params[:order].to_s.split(':', 2).map(&:strip)
+    unless order[0].in?(['name', 'category', 'post_count', 'created_at', 'updated_at'])
+      order[0] = 'post_count'
+    end
+    unless order[1].in?(['asc', 'desc'])
+      order[1] = order[0].in?(['name', 'category']) ? 'asc' : 'desc'
+    end
+
+    page = (params[:page].presence || 1).to_i
+    limit = (params[:limit].presence || 20).to_i
+
+    page = 1 if page < 1
+    limit = 1 if limit < 1
+
+    offset = (page - 1) * limit
+
+    q =
         if post_id.present?
           Tag.joins(:posts, :tag_name)
         else
           Tag.joins(:tag_name)
         end
-        .includes(:tag_name, tag_name: :wiki_page)
-    if post_id.present?
-      tags = tags.where(posts: { id: post_id })
+        .includes(:tag_name, :materials, tag_name: :wiki_page)
+    q = q.where(posts: { id: post_id }) if post_id.present?
+
+    q = q.where('tag_names.name LIKE ?', "%#{ name }%") if name
+    q = q.where(category:) if category
+    q = q.where('tags.post_count >= ?', post_count_between[0]) if post_count_between[0]
+    q = q.where('tags.post_count <= ?', post_count_between[1]) if post_count_between[1]
+    q = q.where('tags.created_at >= ?', created_between[0]) if created_between[0]
+    q = q.where('tags.created_at <= ?', created_between[1]) if created_between[1]
+    q = q.where('tags.updated_at >= ?', updated_between[0]) if updated_between[0]
+    q = q.where('tags.updated_at <= ?', updated_between[1]) if updated_between[1]
+
+    sort_sql =
+        case order[0]
+        when 'name'
+          'tag_names.name'
+        when 'category'
+          'CASE tags.category ' +
+          "WHEN 'deerjikist' THEN 0 " +
+          "WHEN 'meme' THEN 1 " +
+          "WHEN 'character' THEN 2 " +
+          "WHEN 'general' THEN 3 " +
+          "WHEN 'material' THEN 4 " +
+          "WHEN 'meta' THEN 5 " +
+          "WHEN 'nico' THEN 6 END"
+        else
+          "tags.#{ order[0] }"
+        end
+    tags = q.order(Arel.sql("#{ sort_sql } #{ order[1] }, tags.id #{ order[1] }"))
+            .limit(limit)
+            .offset(offset)
+            .to_a
+
+    render json: { tags: TagRepr.many(tags), count: q.size }
   end
 
-    render json: TagRepr.base(tags)
+  def with_depth
+    parent_tag_id = params[:parent].to_i
+    parent_tag_id = nil if parent_tag_id <= 0
+
+    tag_ids =
+        if parent_tag_id
+          TagImplication.where(parent_tag_id:).select(:tag_id)
+        else
+          Tag.where.not(id: TagImplication.select(:tag_id)).select(:id)
+        end
+
+    tags =
+        Tag
+        .joins(:tag_name)
+        .includes(:tag_name, :materials, tag_name: :wiki_page)
+        .where(category: [:meme, :character, :material])
+        .where(id: tag_ids)
+        .order('tag_names.name')
+        .distinct
+        .to_a
+
+    has_children_tag_ids =
+        if tags.empty?
+          []
+        else
+          TagImplication
+              .joins(:tag)
+              .where(parent_tag_id: tags.map(&:id),
+                     tags: { category: [:meme, :character, :material] })
+              .distinct
+              .pluck(:parent_tag_id)
+        end
+
+    render json: tags.map { |tag|
+      TagRepr.base(tag).merge(has_children: has_children_tag_ids.include?(tag.id), children: [])
+    }
   end
 
   def autocomplete
-    q = params[:q].to_s.strip
-    return render json: [] if q.blank?
+    q = params[:q].to_s.strip.sub(/\Anot:/i, '')
 
     with_nico = bool?(:nico, default: true)
     present_only = bool?(:present, default: true)
@@ -38,7 +128,7 @@ class TagsController < ApplicationController
     end
 
     base = Tag.joins(:tag_name)
-              .includes(:tag_name, tag_name: :wiki_page)
+              .includes(:tag_name, :materials, tag_name: :wiki_page)
     base = base.where('tags.post_count > 0') if present_only
 
     canonical_hit =
@@ -63,7 +153,7 @@ class TagsController < ApplicationController
 
   def show
     tag = Tag.joins(:tag_name)
-             .includes(:tag_name, tag_name: :wiki_page)
+             .includes(:tag_name, :materials, tag_name: :wiki_page)
              .find_by(id: params[:id])
     if tag
       render json: TagRepr.base(tag)
@@ -77,7 +167,7 @@ class TagsController < ApplicationController
     return head :bad_request if name.blank?
 
     tag = Tag.joins(:tag_name)
-             .includes(:tag_name, tag_name: :wiki_page)
+             .includes(:tag_name, :materials, tag_name: :wiki_page)
              .find_by(tag_names: { name: })
     if tag
       render json: TagRepr.base(tag)
@@ -107,6 +197,72 @@ class TagsController < ApplicationController
     render json: DeerjikistRepr.many(tag.deerjikists)
   end
 
+  def materials_by_name
+    name = params[:name].to_s.strip
+    return head :bad_request if name.blank?
+
+    tag = Tag.joins(:tag_name)
+             .includes(:tag_name, :materials, tag_name: :wiki_page)
+             .find_by(tag_names: { name: })
+    return head :not_found unless tag
+
+    render json: build_tag_children(tag)
+  end
+
+  def update_all
+    return head :unauthorized unless current_user
+    return head :forbidden unless current_user.gte_member?
+
+    tag = Tag.find_by(id: params[:id])
+    return head :not_found unless tag
+
+    name = params[:name].to_s.strip
+    category = params[:category].to_s.strip
+    return head :unprocessable_entity if name.blank? || category.blank?
+
+    if name != tag.name &&
+        tag.in?([Tag.tagme, Tag.bot, Tag.no_deerjikist, Tag.video, Tag.niconico])
+      return render json: { error: 'システム・タグの名称は変更できません．' },
+                    status: :unprocessable_entity
+    end
+
+    if tag.nico? || category == 'nico'
+      return render json: { error: 'ニコタグは変更できません．' },
+                    status: :unprocessable_entity
+    end
+
+    alias_names = params[:aliases].to_s.split.uniq
+    parent_names = params[:parent_tags].to_s.split.uniq
+
+    ApplicationRecord.transaction do
+      TagVersioning.ensure_snapshot!(tag, created_by_user: current_user)
+
+      old_name = tag.name
+      name_changed = name != old_name
+      wiki_page = tag.tag_name.wiki_page if name_changed
+
+      tag.update!(category:)
+      tag.tag_name.update!(name:)
+
+      alias_names << old_name if name_changed
+      alias_names.delete(name)
+
+      update_aliases!(tag, alias_names)
+      update_parent_tags!(tag, parent_names)
+
+      tag.reload
+
+      record_tag_version!(
+          tag,
+          event_type: :update,
+          created_by_user: current_user,
+          name_changed:,
+          wiki_page:)
+    end
+
+    render json: TagRepr.base(tag.reload)
+  end
+
   def update
     return head :unauthorized unless current_user
     return head :forbidden unless current_user.gte_member?
@@ -116,14 +272,123 @@ class TagsController < ApplicationController
 
     tag = Tag.find(params[:id])
 
-    if name.present?
-      tag.tag_name.update!(name:)
+    if tag.nico? || (category.present? && category == 'nico')
+      return render json: { error: 'ニコタグは変更できません．' },
+                    status: :unprocessable_entity
     end
 
-    if category.present?
-      tag.update!(category:)
+    ApplicationRecord.transaction do
+      TagVersioning.ensure_snapshot!(tag, created_by_user: current_user)
+
+      old_name = tag.name
+      name_changed = name.present? && name != old_name
+      wiki_page = tag.tag_name.wiki_page if name_changed
+
+      tag.tag_name.update!(name:) if name.present?
+      tag.update!(category:) if category.present?
+
+      tag.reload
+
+      record_tag_version!(
+          tag,
+          event_type: :update,
+          created_by_user: current_user,
+          name_changed:,
+          wiki_page:)
     end
 
-    render json: TagRepr.base(tag)
+    render json: TagRepr.base(tag.reload)
+  end
+
+  private
+
+  def build_tag_children tag
+    material = tag.materials.first
+    file = nil
+    content_type = nil
+    if material&.file&.attached?
+      file = rails_storage_proxy_url(material.file, only_path: false)
+      content_type = material.file.blob.content_type
+    end
+
+    TagRepr.base(tag).merge(
+        children: tag.children.sort_by { _1.name }.map { build_tag_children(_1) },
+        material: material.as_json&.merge(file:, content_type:))
+  end
+
+  def record_tag_version! tag, event_type:, created_by_user:, name_changed: false, wiki_page: nil
+    if tag.nico?
+      NicoTagVersionRecorder.record!(tag:, event_type:, created_by_user:)
+      return
+    end
+
+    TagVersionRecorder.record!(tag:, event_type:, created_by_user:)
+
+    return unless name_changed
+
+    wiki_page ||= tag.tag_name.wiki_page
+    return unless wiki_page&.wiki_versions&.exists?
+
+    WikiVersionRecorder.record!(
+        page: wiki_page,
+        event_type: :update,
+        created_by_user:)
+  end
+
+  def update_aliases! tag, alias_names
+    alias_names = alias_names.uniq
+
+    affected_tags = [tag]
+
+    current_aliases = tag.tag_name.aliases.to_a
+
+    current_aliases.each do |alias_tag_name|
+      next if alias_names.include?(alias_tag_name.name)
+
+      affected_tags << alias_tag_name.canonical&.tag
+    end
+
+    alias_names.each do |alias_name|
+      alias_tag_name = TagName.find_undiscard_or_create_by!(name: alias_name)
+      affected_tags << alias_tag_name.canonical&.tag
+    end
+
+    affected_tags.compact.uniq.each do |affected_tag|
+      TagVersioning.ensure_snapshot!(affected_tag, created_by_user: current_user)
+    end
+
+    current_aliases.each do |alias_tag_name|
+      next if alias_names.include?(alias_tag_name.name)
+
+      alias_tag_name.update!(canonical: nil)
+    end
+
+    alias_names.each do |alias_name|
+      alias_tag_name = TagName.find_undiscard_or_create_by!(name: alias_name)
+      alias_tag_name.update!(canonical: tag.tag_name)
+    end
+
+    affected_tags.compact.uniq.each do |affected_tag|
+      record_tag_version!(affected_tag, event_type: :update, created_by_user: current_user)
+    end
+  end
+
+  def update_parent_tags! tag, parent_names
+    parent_tags = Tag.normalise_tags(parent_names, with_tagme: false,
+                                                   with_no_deerjikist: false,
+                                                   deny_nico: true)
+
+    old_parent_tags = tag.parents.to_a
+
+    TagVersioning.record_tag_snapshots!((old_parent_tags + parent_tags).uniq,
+                                        created_by_user: current_user)
+
+    tag.reversed_tag_implications.destroy_all
+
+    parent_tags.each do |parent_tag|
+      next if parent_tag == tag
+
+      TagImplication.create!(tag:, parent_tag:)
+    end
   end
 end

backend/app/controllers/theatre_comments_controller.rb

@@ -0,0 +1,32 @@
+class TheatreCommentsController < ApplicationController
+  def index
+    no_gt = params[:no_gt].to_i
+    no_gt = 0 if no_gt.negative?
+
+    comments = TheatreComment
+               .where(theatre_id: params[:theatre_id])
+               .where('no > ?', no_gt)
+               .order(no: :desc)
+
+    render json: comments.as_json(include: { user: { only: [:id, :name] } })
+  end
+
+  def create
+    return head :unauthorized unless current_user
+
+    content = params[:content]
+    return head :unprocessable_entity if content.blank?
+
+    theatre = Theatre.find_by(id: params[:theatre_id])
+    return head :not_found unless theatre
+
+    comment = nil
+    theatre.with_lock do
+      no = theatre.next_comment_no
+      comment = TheatreComment.create!(theatre:, no:, user: current_user, content:)
+      theatre.update!(next_comment_no: no + 1)
+    end
+
+    render json: comment, status: :created
+  end
+end

backend/app/controllers/theatres_controller.rb

@@ -0,0 +1,54 @@
+class TheatresController < ApplicationController
+  def show
+    theatre = Theatre.find_by(id: params[:id])
+    return head :not_found unless theatre
+
+    render json: TheatreRepr.base(theatre)
+  end
+
+  def watching
+    return head :unauthorized unless current_user
+
+    theatre = Theatre.find_by(id: params[:id])
+    return head :not_found unless theatre
+
+    host_flg = false
+    post_id = nil
+    post_started_at = nil
+
+    theatre.with_lock do
+      TheatreWatchingUser.find_or_initialize_by(theatre:, user: current_user).tap {
+        _1.expires_at = 30.seconds.from_now
+      }.save!
+
+      if (!(theatre.host_user_id?) ||
+          !(theatre.watching_users.exists?(id: theatre.host_user_id)))
+        theatre.update!(host_user_id: current_user.id)
+      end
+
+      host_flg = theatre.host_user_id == current_user.id
+      post_id = theatre.current_post_id
+      post_started_at = theatre.current_post_started_at
+    end
+
+    render json: {
+        host_flg:, post_id:, post_started_at:,
+        watching_users: theatre.watching_users.as_json(only: [:id, :name]) }
+  end
+
+  def next_post
+    return head :unauthorized unless current_user
+
+    theatre = Theatre.find_by(id: params[:id])
+    return head :not_found unless theatre
+    return head :forbidden if theatre.host_user != current_user
+
+    post = Post.where("url LIKE '%nicovideo.jp%'")
+           .or(Post.where("url LIKE '%youtube.com%'"))
+           .order('RAND()')
+           .first
+    theatre.update!(current_post: post, current_post_started_at: Time.current)
+
+    head :no_content
+  end
+end

backend/app/controllers/users_controller.rb

@@ -1,18 +1,26 @@
 class UsersController < ApplicationController
   def create
-    user = User.create!(inheritance_code: SecureRandom.uuid, role: 'guest')
+    return head :unprocessable_entity if request.remote_ip.blank?
+
+    user = nil
+
+    User.transaction do
+      user = User.create!(inheritance_code: SecureRandom.uuid, role: :guest)
+      attach_ip_address!(user)
+    end
+
     render json: { code: user.inheritance_code,
-                   user: user.slice(:id, :name, :inheritance_code, :role) }
+                   user: user.slice(:id, :name, :inheritance_code, :role) },
+           status: :created
   end
 
   def verify
-    ip_bin = IPAddr.new(request.remote_ip).hton
-    ip_address = IpAddress.find_or_create_by!(ip_address: ip_bin)
-
     user = User.find_by(inheritance_code: params[:code])
     return render json: { valid: false } unless user
 
-    UserIp.find_or_create_by!(user:, ip_address:)
+    return head :unprocessable_entity if request.remote_ip.blank?
+
+    attach_ip_address!(user)
 
     render json: { valid: true, user: user.slice(:id, :name, :inheritance_code, :role) }
   end
@@ -41,9 +49,18 @@ class UsersController < ApplicationController
     return head :bad_request if name.blank?
 
     if user.update(name:)
-      render json: user.slice(:id, :name, :inheritance_code, :role), status: :created
+      render json: user.slice(:id, :name, :inheritance_code, :role), status: :ok
     else
       render json: user.errors, status: :unprocessable_entity
     end
   end
+
+  private
+
+  def attach_ip_address! user
+    ip_bin = IPAddr.new(request.remote_ip).hton
+    ip_address = IpAddress.create_or_find_by!(ip_address: ip_bin)
+
+    UserIp.create_or_find_by!(user:, ip_address:)
+  end
 end

backend/app/controllers/wiki_pages_controller.rb

@@ -85,22 +85,24 @@ class WikiPagesController < ApplicationController
     return head :unauthorized unless current_user
     return head :forbidden unless current_user.gte_member?
 
-    name = params[:title]&.strip
+    title = params[:title].to_s.strip
     body = params[:body].to_s
-
-    return head :unprocessable_entity if name.blank? || body.blank?
-
-    tag_name = TagName.find_or_create_by!(name:)
-    page = WikiPage.new(tag_name:, created_user: current_user, updated_user: current_user)
-    if page.save
     message = params[:message].presence
-      Wiki::Commit.content!(page:, body:, created_user: current_user, message:)
+
+    return head :unprocessable_entity if title.blank? || body.blank?
+
+    tag_name = TagName.find_undiscard_or_create_by!(name: title)
+
+    page =
+        Wiki::Commit.create_content!(
+            tag_name:,
+            body:,
+            created_by_user: current_user,
+            message:)
 
     render json: WikiPageRepr.base(page), status: :created
-    else
-      render json: { errors: page.errors.full_messages },
-             status: :unprocessable_entity
-    end
+  rescue ActiveRecord::RecordInvalid, ActiveRecord::RecordNotUnique
+    head :unprocessable_entity
   end
 
   def update
@@ -113,12 +115,21 @@ class WikiPagesController < ApplicationController
     return head :unprocessable_entity if title.blank? || body.blank?
 
     page = WikiPage.find(params[:id])
-    base_revision_id = page.current_revision.id
+    base_revision_id = params[:base_revision_id].presence
 
-    if params[:title].present? && params[:title].strip != page.title
-      return head :unprocessable_entity
+    ApplicationRecord.transaction do
+      page.lock!
+
+      old_title = page.title
+
+      tag = Tag.find_by(tag_name_id: page.tag_name_id)
+
+      if tag && title != old_title
+        TagVersioning.ensure_snapshot!(tag, created_by_user: current_user)
       end
 
+      page.tag_name.update!(name: title) if title != old_title
+
       message = params[:message].presence
       Wiki::Commit.content!(page:,
                             body:,
@@ -126,6 +137,12 @@ class WikiPagesController < ApplicationController
                             message:,
                             base_revision_id:)
 
+      if tag && title != old_title
+        tag.reload
+        TagVersionRecorder.record!(tag:, event_type: :update, created_by_user: current_user)
+      end
+    end
+
     head :ok
   end
 

backend/app/models/ip_address.rb

@@ -2,5 +2,6 @@ class IpAddress < ApplicationRecord
   validates :ip_address, presence: true, length: { maximum: 16 }
   validates :banned, inclusion: { in: [true, false] }
 
-  has_many :users
+  has_many :user_ips, dependent: :destroy
+  has_many :users, through: :user_ips
 end

backend/app/models/material.rb

@@ -0,0 +1,39 @@
+class Material < ApplicationRecord
+  include MyDiscard
+
+  default_scope -> { kept }
+
+  belongs_to :parent, class_name: 'Material', optional: true
+  has_many :children, class_name: 'Material', foreign_key: :parent_id, dependent: :nullify
+
+  belongs_to :tag, optional: true
+  belongs_to :created_by_user, class_name: 'User', optional: true
+  belongs_to :updated_by_user, class_name: 'User', optional: true
+
+  has_one_attached :file, dependent: :purge
+
+  validates :tag_id, presence: true, uniqueness: true
+
+  validate :file_must_be_attached
+  validate :tag_must_be_material_category
+
+  def content_type
+    return nil unless file&.attached?
+
+    file.blob.content_type
+  end
+
+  private
+
+  def file_must_be_attached
+    return if url.present? || file.attached?
+
+    errors.add(:url, 'URL かファイルのどちらかは必須です．')
+  end
+
+  def tag_must_be_material_category
+    return if tag.blank? || tag.character? || tag.material?
+
+    errors.add(:tag, '素材カテゴリのタグを指定してください．')
+  end
+end

backend/app/models/my_discard.rb

@@ -0,0 +1,24 @@
+module MyDiscard
+  extend ActiveSupport::Concern
+
+  included do
+    include Discard::Model
+
+    default_scope -> { kept }
+  end
+
+  class_methods do
+    def find_undiscard_or_create_by! attrs, &block
+      record = with_discarded.find_by(attrs)
+
+      if record&.discarded?
+        record.undiscard!
+        record.update_columns(created_at: record.reload.updated_at)
+      end
+
+      record or create!(attrs, &block)
+    rescue ActiveRecord::RecordNotUnique
+      retry
+    end
+  end
+end

backend/app/models/nico_tag_version.rb

@@ -0,0 +1,7 @@
+class NicoTagVersion < ApplicationRecord
+  include VersionRecord
+
+  belongs_to :tag
+
+  validates :name, presence: true
+end

backend/app/models/post.rb

@@ -11,6 +11,7 @@ class Post < ApplicationRecord
 
   has_many :user_post_views, dependent: :delete_all
   has_many :post_similarities, dependent: :delete_all
+  has_many :post_versions
 
   has_one_attached :thumbnail
 
@@ -30,6 +31,8 @@ class Post < ApplicationRecord
     super(options).merge(thumbnail: nil)
   end
 
+  def snapshot_tag_names = tags.joins(:tag_name).order('tag_names.name').pluck('tag_names.name')
+
   def related limit: nil
     ids = post_similarities.order(cos: :desc)
     ids = ids.limit(limit) if limit

backend/app/models/post_version.rb

@@ -0,0 +1,22 @@
+class PostVersion < ApplicationRecord
+  include VersionRecord
+
+  belongs_to :post
+  belongs_to :parent, class_name: 'Post', optional: true
+
+  validates :url, presence: true
+
+  validate :validate_original_created_range
+
+  private
+
+  def validate_original_created_range
+    f = original_created_from
+    b = original_created_before
+    return if f.blank? || b.blank?
+
+    if f >= b
+      errors.add :original_created_before, 'オリジナルの作成日時の順番がをかしぃです．'
+    end
+  end
+end

backend/app/models/tag.rb

@@ -1,4 +1,9 @@
+require 'set'
+
+
 class Tag < ApplicationRecord
+  include MyDiscard
+
   class NicoTagNormalisationError < ArgumentError
     ;
   end
@@ -27,6 +32,10 @@ class Tag < ApplicationRecord
            class_name: 'TagSimilarity', foreign_key: :target_tag_id, dependent: :delete_all
 
   has_many :deerjikists, dependent: :delete_all
+  has_many :materials
+
+  has_many :tag_versions
+  has_many :nico_tag_versions
 
   belongs_to :tag_name
   delegate :wiki_page, to: :tag_name
@@ -68,27 +77,18 @@ class Tag < ApplicationRecord
 
   def has_wiki = wiki_page.present?
 
-  def self.tagme
-    @tagme ||= find_or_create_by_tag_name!('タグ希望', category: :meta)
-  end
+  def material_id = materials.first&.id
 
-  def self.bot
-    @bot ||= find_or_create_by_tag_name!('bot操作', category: :meta)
-  end
+  def self.tagme = find_or_create_by_tag_name!('タグ希望', category: :meta)
+  def self.bot = find_or_create_by_tag_name!('bot操作', category: :meta)
+  def self.no_deerjikist = find_or_create_by_tag_name!('ニジラー情報不詳', category: :meta)
+  def self.video = find_or_create_by_tag_name!('動画', category: :meta)
+  def self.niconico = find_or_create_by_tag_name!('ニコニコ', category: :meta)
+  def self.youtube = find_or_create_by_tag_name!('YouTube', category: :meta)
 
-  def self.no_deerjikist
-    @no_deerjikist ||= find_or_create_by_tag_name!('ニジラー情報不詳', category: :meta)
-  end
-
-  def self.video
-    @video ||= find_or_create_by_tag_name!('動画', category: :meta)
-  end
-
-  def self.niconico
-    @niconico ||= find_or_create_by_tag_name!('ニコニコ', category: :meta)
-  end
-
-  def self.normalise_tags tag_names, with_tagme: true, deny_nico: true
+  def self.normalise_tags tag_names, with_tagme: true,
+                                     with_no_deerjikist: true,
+                                     deny_nico: true
     if deny_nico && tag_names.any? { |n| n.downcase.start_with?('nico:') }
       raise NicoTagNormalisationError
     end
@@ -102,7 +102,7 @@ class Tag < ApplicationRecord
     end
 
     tags << Tag.tagme if with_tagme && tags.size < 10 && tags.none?(Tag.tagme)
-    tags << Tag.no_deerjikist if tags.all? { |t| !(t.deerjikist?) }
+    tags << Tag.no_deerjikist if with_no_deerjikist && tags.all? { |t| !(t.deerjikist?) }
     tags.uniq(&:id)
   end
 
@@ -130,39 +130,62 @@ class Tag < ApplicationRecord
   end
 
   def self.find_or_create_by_tag_name! name, category:
-    tn = TagName.find_or_create_by!(name: name.to_s.strip)
+    tn = TagName.find_undiscard_or_create_by!(name: name.to_s.strip)
     tn = tn.canonical if tn.canonical_id?
 
-    Tag.find_or_create_by!(tag_name_id: tn.id) do |t|
+    Tag.find_undiscard_or_create_by!(tag_name_id: tn.id) do |t|
       t.category = category
     end
   rescue ActiveRecord::RecordNotUnique
     retry
   end
 
-  def self.merge_tags! target_tag, source_tags
+  def self.merge_tags! target_tag, source_tags, created_by_user: nil
     target_tag => Tag
 
+    affected_post_ids = Set.new
+
     Tag.transaction do
-      Array(source_tags).compact.uniq.each do |st|
-        st => Tag
+      TagVersioning.ensure_snapshot!(target_tag, created_by_user:)
 
-        next if st == target_tag
+      Array(source_tags).compact.uniq.each do |source_tag|
+        source_tag => Tag
 
-        st.post_tags.find_each do |pt|
-          if PostTag.kept.exists?(post_id: pt.post_id, tag_id: target_tag.id)
-            pt.discard_by!(nil)
-            # discard 後の update! は禁止なので DB を直に更新
-            pt.update_columns(tag_id: target_tag.id, updated_at: Time.current)
+        next if source_tag == target_tag
+
+        TagVersioning.ensure_snapshot!(source_tag, created_by_user:)
+
+        source_tag.post_tags.kept.find_each do |source_pt|
+          post_id = source_pt.post_id
+          affected_post_ids << post_id
+          source_pt.discard_by!(created_by_user)
+          unless PostTag.kept.exists?(post_id:, tag: target_tag)
+            PostTag.create!(post_id:, tag: target_tag)
+          end
+        end
+
+        source_tag_name = source_tag.tag_name
+
+        if source_tag_name.wiki_page.present?
+          raise ActiveRecord::RecordInvalid.new(source_tag_name)
+        end
+
+        TagVersioning.record!(source_tag, event_type: :discard, created_by_user:)
+        source_tag.discard!
+
+        if source_tag.nico?
+          source_tag_name.discard!
         else
-            pt.update!(tag: target_tag)
-          end
+          source_tag_name.update_columns(canonical_id: target_tag.tag_name_id,
+                                         updated_at: Time.current)
         end
 
-        tag_name = st.tag_name
-        st.destroy!
-        tag_name.reload
-        tag_name.update!(canonical: target_tag.tag_name)
+        TagVersioning.record!(target_tag, event_type: :update, created_by_user:)
+      end
+
+      Post.where(id: affected_post_ids.to_a).find_each do |post|
+        PostVersionRecorder.ensure_snapshot!(post, created_by_user:)
+        PostVersionRecorder.record!(post:, event_type: :update, created_by_user:)
       end
 
       # 投稿件数を再集計
@@ -172,6 +195,14 @@ class Tag < ApplicationRecord
     target_tag.reload
   end
 
+  def snapshot_aliases = tag_name.aliases.kept.order(:name).pluck(:name)
+
+  def snapshot_parent_tag_ids = parents.order(:id).pluck(:id)
+
+  def snapshot_linked_tag_names
+    linked_tags.joins(:tag_name).order('tag_names.name').pluck('tag_names.name')
+  end
+
   private
 
   def nico_tag_name_must_start_with_nico

backend/app/models/tag_name.rb

@@ -1,4 +1,6 @@
 class TagName < ApplicationRecord
+  include MyDiscard
+
   has_one :tag
   has_one :wiki_page
 
@@ -10,6 +12,7 @@ class TagName < ApplicationRecord
   validate :canonical_must_be_canonical
   validate :alias_name_must_not_have_prefix
   validate :canonical_must_not_be_present_with_tag_or_wiki_page
+  validate :name_must_be_sanitised
 
   def self.canonicalise names
     names = Array(names).map { |n| n.to_s.strip }.reject(&:blank?)
@@ -39,4 +42,10 @@ class TagName < ApplicationRecord
       errors.add :canonical, 'タグもしくは Wiki の参照がある名前はエーリアスになれません．'
     end
   end
+
+  def name_must_be_sanitised
+    if name? && name != TagNameSanitisationRule.sanitise(name)
+      errors.add :name, '名前に使用できない文字が含まれてゐます．'
+    end
+  end
 end

backend/app/models/tag_name_sanitisation_rule.rb

@@ -0,0 +1,58 @@
+class TagNameSanitisationRule < ApplicationRecord
+  include Discard::Model
+
+  self.primary_key = :priority
+
+  default_scope -> { kept }
+
+  validates :source_pattern, presence: true, uniqueness: true
+
+  validate :source_pattern_must_be_regexp
+
+  class << self
+    def sanitise(name) =
+        rules.reduce(name.dup) { |name, (pattern, replacement)| name.gsub(pattern, replacement) }
+
+    def apply!
+      TagName.find_each do |tn|
+        name = sanitise(tn.name)
+        next if name == tn.name
+
+        TagName.transaction do
+          existing_tn = TagName.find_by(name:)
+          if existing_tn
+            existing_tn = existing_tn.canonical || existing_tn
+            next if existing_tn.id == tn.id
+
+            existing_tag = Tag.find_by(tag_name_id: existing_tn.id)
+            source_tag = Tag.find_by(tag_name_id: tn.id)
+
+            if existing_tag
+              Tag.merge_tags!(existing_tag, source_tag) if tn.tag
+            elsif source_tag
+              source_tag.update_columns(tag_name_id: existing_tn.id, updated_at: Time.current)
+            end
+            tn.discard!
+
+            next
+          end
+
+          # TagName 側の自動サニタイズを回避
+          tn.update_columns(name:, updated_at: Time.current)
+        end
+      end
+    end
+
+    private
+
+    def rules = kept.order(:priority).map { |r| [Regexp.new(r.source_pattern), r.replacement] }
+  end
+
+  private
+
+  def source_pattern_must_be_regexp
+    Regexp.new(source_pattern)
+  rescue RegexpError
+    errors.add :source_pattern, '変な正規表現だね〜（笑）'
+  end
+end

backend/app/models/tag_version.rb

@@ -0,0 +1,15 @@
+class TagVersion < ApplicationRecord
+  include VersionRecord
+
+  belongs_to :tag
+
+  enum :category, { deerjikist: 'deerjikist',
+                    meme: 'meme',
+                    character: 'character',
+                    general: 'general',
+                    material: 'material',
+                    meta: 'meta' }, validate: true
+
+  validates :name, presence: true
+  validates :category, presence: true
+end

backend/app/models/theatre.rb

@@ -0,0 +1,13 @@
+class Theatre < ApplicationRecord
+  include MyDiscard
+
+  has_many :comments, class_name: 'TheatreComment'
+  has_many :theatre_watching_users, dependent: :delete_all
+  has_many :active_theatre_watching_users, -> { active },
+           class_name: 'TheatreWatchingUser', inverse_of: :theatre
+  has_many :watching_users, through: :active_theatre_watching_users, source: :user
+
+  belongs_to :host_user, class_name: 'User', optional: true
+  belongs_to :current_post, class_name: 'Post', optional: true
+  belongs_to :created_by_user, class_name: 'User'
+end

backend/app/models/theatre_comment.rb

@@ -0,0 +1,8 @@
+class TheatreComment < ApplicationRecord
+  include Discard::Model
+
+  self.primary_key = :theatre_id, :no
+
+  belongs_to :theatre
+  belongs_to :user
+end

backend/app/models/theatre_watching_user.rb

@@ -0,0 +1,13 @@
+class TheatreWatchingUser < ApplicationRecord
+  self.primary_key = :theatre_id, :user_id
+
+  belongs_to :theatre
+  belongs_to :user
+
+  scope :active, -> { where('expires_at >= ?', Time.current) }
+  scope :expired, -> { where('expires_at < ?', Time.current) }
+
+  def active? = expires_at >= Time.current
+
+  def refresh! = update!(expires_at: 30.seconds.from_now)
+end

backend/app/models/version_record.rb

@@ -0,0 +1,19 @@
+module VersionRecord
+  extend ActiveSupport::Concern
+
+  def readonly? = persisted?
+
+  included do
+    belongs_to :created_by_user, class_name: 'User', optional: true
+
+    enum :event_type, { create: 'create',
+                        update: 'update',
+                        discard: 'discard',
+                        restore: 'restore' }, prefix: true, validate: true
+
+    validates :version_no, presence: true, numericality: { only_integer: true, greater_than: 0 }
+    validates :event_type, presence: true
+
+    scope :chronological, -> { order(:version_no, :id) }
+  end
+end

backend/app/models/wiki_page.rb

@@ -2,6 +2,8 @@ require 'set'
 
 
 class WikiPage < ApplicationRecord
+  include MyDiscard
+
   has_many :wiki_revisions, dependent: :destroy
   belongs_to :created_user, class_name: 'User'
   belongs_to :updated_user, class_name: 'User'
@@ -11,8 +13,11 @@ class WikiPage < ApplicationRecord
            foreign_key: :redirect_page_id,
            dependent: :nullify
 
+  has_many :wiki_versions
+
   belongs_to :tag_name
   validates :tag_name, presence: true
+  validates :body, presence: true
 
   def title = tag_name.name
 
@@ -22,11 +27,6 @@ class WikiPage < ApplicationRecord
 
   def current_revision = wiki_revisions.order(id: :desc).first
 
-  def body
-    rev = current_revision
-    rev.body if rev&.content?
-  end
-
   def resolve_redirect limit: 10
     page = self
     visited = Set.new

backend/app/models/wiki_version.rb

@@ -0,0 +1,8 @@
+class WikiVersion < ApplicationRecord
+  include VersionRecord
+
+  belongs_to :wiki_page
+
+  validates :title, presence: true
+  validates :body, presence: true
+end

backend/app/representations/material_repr.rb

@@ -0,0 +1,24 @@
+# frozen_string_literal: true
+
+
+module MaterialRepr
+  BASE = { only: [:id, :url, :created_at, :updated_at],
+           methods: [:content_type],
+           include: { tag: TagRepr::BASE,
+                      created_by_user: UserRepr::BASE,
+                      updated_by_user: UserRepr::BASE } }.freeze
+
+  module_function
+
+  def base material, host:
+    material.as_json(BASE).merge(
+        file: if material.file.attached?
+                Rails.application.routes.url_helpers.rails_storage_proxy_url(
+                    material.file, host:)
+              end)
+  end
+
+  def many materials, host:
+    materials.map { |m| base(m, host:) }
+  end
+end

backend/app/representations/post_repr.rb

@@ -2,15 +2,19 @@
 
 
 module PostRepr
-  BASE = { include: { tags: TagRepr::BASE } }.freeze
+  BASE = { include: { tags: TagRepr::BASE, uploaded_user: UserRepr::BASE } }.freeze
 
   module_function
 
-  def base post
-    post.as_json(BASE)
+  def base post, current_user = nil
+    json = post.as_json(BASE)
+    return json.merge(viewed: false) unless current_user
+
+    viewed = current_user.viewed?(post)
+    json.merge(viewed:)
   end
 
-  def many posts
-    posts.map { |p| base(p) }
+  def many posts, current_user = nil
+    posts.map { |p| base(p, current_user) }
   end
 end

backend/app/representations/tag_repr.rb

@@ -2,15 +2,15 @@
 
 
 module TagRepr
-  BASE = { only: [:id, :category, :post_count], methods: [:name, :has_wiki] }.freeze
+  BASE = { only: [:id, :category, :post_count, :created_at, :updated_at],
+           methods: [:name, :has_wiki, :material_id] }.freeze
 
   module_function
 
   def base tag
-    tag.as_json(BASE)
+    tag.as_json(BASE).merge(aliases: tag.snapshot_aliases,
+                            parents: tag.parents.map { _1.as_json(BASE) })
   end
 
-  def many tags
-    tags.map { |t| base(t) }
-  end
+  def many(tags) = tags.map { |t| base(t) }
 end

backend/app/representations/theatre_repr.rb

@@ -0,0 +1,17 @@
+# frozen_string_literal: true
+
+
+module TheatreRepr
+  BASE = { only: [:id, :name, :opens_at, :closes_at, :created_at, :updated_at],
+           include: { created_by_user: { only: [:id, :name] } } }.freeze
+
+  module_function
+
+  def base theatre
+    theatre.as_json(BASE)
+  end
+
+  def many theatre
+    theatre.map { |t| base(t) }
+  end
+end

backend/app/representations/user_repr.rb

@@ -0,0 +1,16 @@
+# frozen_string_literal: true
+
+
+module UserRepr
+  BASE = { only: [:id, :name] }.freeze
+
+  module_function
+
+  def base user
+    user.as_json(BASE)
+  end
+
+  def many users
+    users.map { |u| base(u) }
+  end
+end

backend/app/services/nico_tag_version_recorder.rb

@@ -0,0 +1,19 @@
+class NicoTagVersionRecorder < VersionRecorder
+  def self.record! tag:, event_type:, created_by_user:
+    new(tag:, event_type:, created_by_user:).record!
+  end
+
+  def initialize tag:, event_type:, created_by_user:
+    super(record: tag, event_type:, created_by_user:)
+  end
+
+  private
+
+  def version_class = NicoTagVersion
+  def version_association = :nico_tag_versions
+  def record_key = :tag
+
+  def snapshot_attributes
+    { name: @record.name, linked_tags: @record.snapshot_linked_tag_names.join(' ') }
+  end
+end

backend/app/services/post_version_recorder.rb

@@ -0,0 +1,31 @@
+class PostVersionRecorder < VersionRecorder
+  def self.record! post:, event_type:, created_by_user:
+    new(post:, event_type:, created_by_user:).record!
+  end
+
+  def initialize post:, event_type:, created_by_user:
+    super(record: post, event_type:, created_by_user:)
+  end
+
+  def self.ensure_snapshot! post, created_by_user:
+    return if post.post_versions.exists?
+
+    record!(post:, event_type: :create, created_by_user:)
+  end
+
+  private
+
+  def version_class = PostVersion
+  def version_association = :post_versions
+  def record_key = :post
+
+  def snapshot_attributes
+    { title: @record.title,
+      url: @record.url,
+      thumbnail_base: @record.thumbnail_base,
+      tags: @record.snapshot_tag_names.join(' '),
+      parent_id: @record.parent_id,
+      original_created_from: @record.original_created_from,
+      original_created_before: @record.original_created_before }
+  end
+end

backend/app/services/tag_version_recorder.rb

@@ -0,0 +1,22 @@
+class TagVersionRecorder < VersionRecorder
+  def self.record! tag:, event_type:, created_by_user:
+    new(tag:, event_type:, created_by_user:).record!
+  end
+
+  def initialize tag:, event_type:, created_by_user:
+    super(record: tag, event_type:, created_by_user:)
+  end
+
+  private
+
+  def version_class = TagVersion
+  def version_association = :tag_versions
+  def record_key = :tag
+
+  def snapshot_attributes
+    { name: @record.name,
+      category: @record.category,
+      aliases: @record.snapshot_aliases.join(' '),
+      parent_tag_ids: @record.snapshot_parent_tag_ids.join(' ') }
+  end
+end

backend/app/services/tag_versioning.rb

@@ -0,0 +1,38 @@
+class TagVersioning
+  def self.record! tag, event_type:, created_by_user:
+    if tag.nico?
+      NicoTagVersionRecorder.record!(tag:, event_type:, created_by_user:)
+    else
+      TagVersionRecorder.record!(tag:, event_type:, created_by_user:)
+    end
+  end
+
+  def self.ensure_snapshot! tag, created_by_user:
+    if tag.nico?
+      return if tag.nico_tag_versions.exists?
+
+      NicoTagVersionRecorder.record!(tag:, event_type: :create, created_by_user:)
+    else
+      return if tag.tag_versions.exists?
+
+      TagVersionRecorder.record!(tag:, event_type: :create, created_by_user:)
+    end
+  end
+
+  def self.record_tag_snapshot! tag, created_by_user:
+    event_type =
+        if tag.nico?
+          tag.nico_tag_versions.exists? ? :update : :create
+        else
+          tag.tag_versions.exists? ? :update : :create
+        end
+
+    record!(tag, event_type:, created_by_user:)
+  end
+
+  def self.record_tag_snapshots! tags, created_by_user:
+    tags.each do |tag|
+      record_tag_snapshot!(tag, created_by_user:)
+    end
+  end
+end

backend/app/services/version_recorder.rb

@@ -0,0 +1,62 @@
+class VersionRecorder
+  EVENT_TYPES = ['create', 'update', 'discard', 'restore'].freeze
+
+  def initialize record:, event_type:, created_by_user:
+    @record = record
+    @event_type = event_type.to_s
+    @created_by_user = created_by_user
+
+    validate_event_type!
+  end
+
+  def record!
+    raise "#{ record_class.name } must be persisted" unless @record.persisted?
+
+    ApplicationRecord.transaction do
+      @record = record_class.unscoped.lock.find(@record.id)
+      latest = latest_version
+
+      if !(latest) && @event_type != 'create'
+        raise "#{ version_class.name } first event must be create"
+      end
+
+      if @event_type == 'create' && latest
+        raise "#{ version_class.name } create event already exists"
+      end
+
+      attrs = snapshot_attributes
+
+      return latest if @event_type == 'update' && latest && same_snapshot?(latest, attrs)
+
+      version_class.create!(base_attributes(latest).merge(record_key => @record).merge(attrs))
+    end
+  end
+
+  private
+
+  def latest_version = versions.order(version_no: :desc).first
+
+  def versions = @record.public_send(version_association)
+
+  def base_attributes latest
+    { version_no: (latest&.version_no || 0) + 1,
+      event_type: @event_type,
+      created_at: Time.current,
+      created_by_user: @created_by_user }
+  end
+
+  def same_snapshot?(version, attrs) = attrs.all? { |k, v| version.public_send(k) == v }
+
+  def validate_event_type!
+    return if EVENT_TYPES.include?(@event_type)
+
+    raise ArgumentError, "Invalid event_type: #{ @event_type }"
+  end
+
+  def version_class = raise NotImplementedError
+  def version_association = raise NotImplementedError
+  def record_key = raise NotImplementedError
+  def snapshot_attributes = raise NotImplementedError
+
+  def record_class = @record.class
+end

backend/app/services/wiki/commit.rb

@@ -7,6 +7,31 @@ module Wiki
       ;
     end
 
+    def self.create_content! tag_name:, body:, created_by_user:, message: nil
+      normalised = normalise_body(body)
+
+      page = WikiPage.new(tag_name:,
+                          body: normalised,
+                          created_user: created_by_user,
+                          updated_user: created_by_user)
+
+      if normalised.blank?
+        page.errors.add(:body, :blank)
+        raise ActiveRecord::RecordInvalid, page
+      end
+
+      ActiveRecord::Base.transaction do
+        page.save!
+
+        new(page:, created_user: created_by_user).content!(
+            body: normalised,
+            message:,
+            base_revision_id: nil)
+
+        page
+      end
+    end
+
     def self.content! page:, body:, created_user:, message: nil, base_revision_id: nil
       new(page:, created_user:).content!(body:, message:, base_revision_id:)
     end
@@ -21,7 +46,12 @@ module Wiki
     end
 
     def content! body:, message:, base_revision_id:
-      normalised = normalise_body(body)
+      normalised = self.class.normalise_body(body)
+      if normalised.blank?
+        @page.errors.add(:body, :blank)
+        raise ActiveRecord::RecordInvalid, @page
+      end
+
       lines = split_lines(normalised)
 
       line_shas = lines.map { |line| Digest::SHA256.hexdigest(line) }
@@ -37,10 +67,19 @@ module Wiki
           current_id = @page.wiki_revisions.maximum(:id)
           if current_id && current_id != base_revision_id.to_i
             raise Conflict,
-                "競合が発生してゐます（現在の Id.：#{ current_id }，ベース Id.：#{ base_revision_id }）．"
+                "競合が発生してゐます" +
+                "（現在の Id.：#{ current_id }，ベース Id.：#{ base_revision_id }）．"
           end
         end
 
+        @page.update!(body: normalised)
+
+        WikiVersionRecorder.record!(
+            page: @page,
+            event_type: @page.wiki_versions.exists? ? :update : :create,
+            reason: message,
+            created_by_user: @created_user)
+
         rev = WikiRevision.create!(
             wiki_page:        @page,
             base_revision_id:,
@@ -54,65 +93,45 @@ module Wiki
         rows = line_ids.each_with_index.map do |line_id, pos|
           { wiki_revision_id: rev.id, wiki_line_id: line_id, position: pos }
         end
-        WikiRevisionLine.insert_all!(rows)
+        WikiRevisionLine.insert_all!(rows) if rows.any?
 
         rev
       end
     end
 
-    def redirect! redirect_page:, message:, base_revision_id:
-      ActiveRecord::Base.transaction do
-        @page.lock!
+    def redirect!(redirect_page:, message:, base_revision_id:) = raise '廃止しました．'
 
-        if base_revision_id.present?
-          current_id = @page.wiki_revisions.maximum(:id)
-          if current_id && current_id != base_revision_id.to_i
-            raise Conflict,
-                "競合が発生してゐます（現在の Id.：#{ current_id }，ベース Id.：#{ base_revision_id }）．"
-          end
-        end
-
-        WikiRevision.create!(
-            wiki_page:        @page,
-            base_revision_id:,
-            created_user:     @created_user,
-            kind:             :redirect,
-            redirect_page:,
-            message:,
-            lines_count:      0,
-            tree_sha256:      nil)
-      end
+    def self.normalise_body body
+      s = body.to_s
+      s.gsub!(/\r\n?/, "\n")
+      s.encode('UTF-8', invalid: :replace, undef: :replace, replace: '🖕')
+      s.gsub(/\n+$/, '')
     end
 
     private
 
-    def normalise_body body
-      s = body.to_s
-      s.gsub!("\r\n", "\n")
-      s.encode('UTF-8', invalid: :replace, undef: :replace, replace: '🖕')
-    end
-
-    def split_lines body
-      body.split("\n")
-    end
+    def split_lines(body) = body.split("\n")
 
     def upsert_lines! lines, line_shas
       now = Time.current
 
       id_by_sha = WikiLine.where(sha256: line_shas).pluck(:sha256, :id).to_h
 
-      missing_rows = []
+      missing_by_sha = { }
+
       line_shas.each_with_index do |sha, i|
         next if id_by_sha.key?(sha)
+        next if missing_by_sha.key?(sha)
 
-        missing_rows << { sha256:     sha,
+        missing_by_sha[sha] = {
+            sha256:     sha,
             body:       lines[i],
             created_at: now,
             updated_at: now }
       end
 
-      if missing_rows.any?
-        WikiLine.upsert_all(missing_rows)
+      if missing_by_sha.any?
+        WikiLine.upsert_all(missing_by_sha.values)
         id_by_sha = WikiLine.where(sha256: line_shas).pluck(:sha256, :id).to_h
       end
 

backend/app/services/wiki_version_recorder.rb

@@ -0,0 +1,21 @@
+class WikiVersionRecorder < VersionRecorder
+  def self.record! page:, event_type:, reason: nil, created_by_user:
+    new(page:, event_type:, reason:, created_by_user:).record!
+  end
+
+  def initialize page:, event_type:, reason: nil, created_by_user:
+    @reason = reason
+    super(record: page, event_type:, created_by_user:)
+  end
+
+  private
+
+  def version_class = WikiVersion
+  def version_association = :wiki_versions
+  def record_key = :wiki_page
+
+  def snapshot_attributes = {
+      title: @record.title,
+      body: @record.body,
+      reason: @reason }
+end

backend/app/services/youtube/api_client.rb

@@ -0,0 +1,73 @@
+require 'json'
+require 'net/http'
+require 'uri'
+
+
+module Youtube
+  class ApiClient
+    ENDPOINT = 'https://www.googleapis.com/youtube/v3'
+
+    def initialize api_key: ENV.fetch('YOUTUBE_API_KEY')
+      @api_key = api_key
+    end
+
+    def search_videos q:, published_after: nil, published_before: nil, page_token: nil
+      get_json('/search', {
+          part:           'snippet',
+          type:           'video',
+          q:,
+          order:          'date',
+          maxResults:     50,
+          regionCode:     'JP',
+          relevanceLanguage: 'ja',
+          publishedAfter: published_after&.iso8601,
+          publishedBefore: published_before&.iso8601,
+          pageToken:      page_token }.compact)
+    end
+
+    def videos ids
+      return { 'items' => [] } if ids.empty?
+
+      get_json('/videos', part: 'snippet,status,contentDetails', id: ids.join(','))
+    end
+
+    def playlist_items playlist_id:, page_token: nil
+      get_json('/playlistItems', {
+          part:       'snippet,contentDetails,status',
+          playlistId: playlist_id,
+          maxResults: 50,
+          pageToken:  page_token }.compact)
+    end
+
+    def channel id: nil, handle: nil
+      raise ArgumentError, 'id or handle is required' if id.present? == handle.present?
+
+      params = { part: 'snippet,contentDetails' }
+      params[:id] = id if id.present?
+      params[:forHandle] = handle if handle.present?
+
+      get_json('/channels', params)
+    end
+
+    private
+
+    def get_json path, params
+      uri = URI(ENDPOINT + path)
+      uri.query = URI.encode_www_form(params.merge(key: @api_key))
+
+      response = Net::HTTP.start(uri.host,
+                                 uri.port,
+                                 use_ssl: true,
+                                 open_timeout: 10,
+                                 read_timeout: 30) do |http|
+        http.get(uri)
+      end
+
+      unless response.is_a?(Net::HTTPSuccess)
+        raise "YouTube API error: #{ response.code } #{ response.body }"
+      end
+
+      JSON.parse(response.body)
+    end
+  end
+end

backend/app/services/youtube/sync.rb

@@ -0,0 +1,168 @@
+require 'open-uri'
+require 'set'
+require 'time'
+
+
+module Youtube
+  class Sync
+    def initialize client: ApiClient.new
+      @client = client
+    end
+
+    def sync!
+      video_ids = discover_video_ids
+      return if video_ids.empty?
+
+      video_ids.each_slice(50) do |ids|
+        @client.videos(ids).fetch('items', []).each do |item|
+          sync_video!(VideoItem.new(item))
+        end
+      end
+    end
+
+    private
+
+    def discover_video_ids
+      ids = Set.new
+
+      query_terms.each do |q|
+        response = @client.search_videos(q:, published_after: sync_since)
+
+        response.fetch('items', []).each do |item|
+          video_id = item.dig('id', 'videoId')
+          ids << video_id if video_id.present?
+        end
+      end
+
+      playlist_ids.each do |playlist_id|
+        each_playlist_item(playlist_id) do |item|
+          video_id = item.dig('contentDetails', 'videoId')
+          video_id ||= item.dig('snippet', 'resourceId', 'videoId')
+
+          ids << video_id if video_id.present?
+        end
+      end
+
+      ids.to_a
+    end
+
+    def sync_video! video
+      post = Post.where('url REGEXP ?', youtube_url_regexp(video.id)).first
+
+      original_created_from = video.published_at.change(sec: 0)
+      original_created_before = original_created_from + 1.minute
+
+      post_created = false
+      post_changed = false
+
+      if post
+        post.assign_attributes(title: video.title,
+                               original_created_from:,
+                               original_created_before:,
+                               thumbnail_base: video.thumbnail_url)
+
+        post_changed = post.changed?
+        post.save! if post_changed
+
+        attach_thumbnail_if_needed!(post, video.thumbnail_url)
+      else
+        post_created = true
+        post = Post.create!(
+            title: video.title,
+            url: video.url,
+            thumbnail_base: video.thumbnail_url,
+            uploaded_user_id: nil,
+            original_created_from:,
+            original_created_before:)
+
+        attach_thumbnail_if_needed!(post, video.thumbnail_url)
+
+        sync_post_tags!(post, [Tag.tagme.id, Tag.bot.id, Tag.youtube.id, Tag.video.id])
+      end
+
+      kept_tag_ids = post.tags.pluck(:id).to_set
+      desired_tag_ids = kept_tag_ids.to_a
+
+      deerjikist = Deerjikist.find_by(platform: :youtube, code: video.channel_id)
+      if deerjikist
+        desired_tag_ids.delete(Tag.no_deerjikist.id)
+        desired_tag_ids << deerjikist.tag_id
+      elsif post.tags.where(category: :deerjikist).none?
+        desired_tag_ids << Tag.no_deerjikist.id
+      end
+
+      desired_tag_ids.uniq!
+
+      sync_post_tags!(post, desired_tag_ids, current_tag_ids: kept_tag_ids)
+
+      if post_created
+        PostVersionRecorder.record!(post:, event_type: :create, created_by_user: nil)
+      elsif post_changed || kept_tag_ids != desired_tag_ids.to_set
+        PostVersionRecorder.ensure_snapshot!(post, created_by_user: nil)
+        PostVersionRecorder.record!(post:, event_type: :update, created_by_user: nil)
+      end
+    end
+
+    def sync_post_tags! post, desired_tag_ids, current_tag_ids: nil
+      current_tag_ids ||= PostTag.kept.where(post_id: post.id).pluck(:tag_id).to_set
+      desired_tag_ids = desired_tag_ids.compact.to_set
+
+      to_add = desired_tag_ids - current_tag_ids
+      to_remove = current_tag_ids - desired_tag_ids
+
+      Tag.where(id: to_add.to_a).find_each do |tag|
+        begin
+          PostTag.create!(post:, tag:)
+        rescue ActiveRecord::RecordNotUnique
+          ;
+        end
+      end
+
+      PostTag.where(post_id: post.id, tag_id: to_remove.to_a).kept.find_each do |pt|
+        pt.discard_by!(nil)
+      end
+    end
+
+    def attach_thumbnail_if_needed! post, thumbnail_url
+      return if post.thumbnail.attached?
+      return if thumbnail_url.blank?
+
+      post.thumbnail.attach(
+          io:           URI.open(thumbnail_url),
+          filename:     File.basename(URI.parse(thumbnail_url).path),
+          content_type: 'image/jpeg')
+
+      post.resized_thumbnail!
+    end
+
+    def youtube_url_regexp id
+      escaped = Regexp.escape(id)
+      "(youtube\\.com/watch\\?v=#{ escaped }|youtu\\.be/#{ escaped })([^A-Za-z0-9_-]|$)"
+    end
+
+    def query_terms = ['ぼざろクリーチャーシリーズ', '伊地知ニジカ', '伊地知虹鹿']
+
+    def playlist_ids
+      ['PLrOch4zHkI5vu29b-f9umUQQ4tQkuWLPX',
+       'PLrOch4zHkI5vOK0RaytQq6PbucxQkkL0K',
+       'PLrOch4zHkI5tdwm9vSegiDQJOM-hgpcOC']
+    end
+
+    def sync_since = 14.days.ago
+
+    def each_playlist_item playlist_id
+      page_token = nil
+
+      loop do
+        response = @client.playlist_items(playlist_id:, page_token:)
+
+        response.fetch('items', []).each do |item|
+          yield item
+        end
+
+        page_token = response['nextPageToken']
+        break if page_token.blank?
+      end
+    end
+  end
+end

backend/app/services/youtube/video_item.rb

@@ -0,0 +1,32 @@
+require 'time'
+
+
+module Youtube
+  class VideoItem
+    attr_reader :id, :title, :channel_id, :published_at, :thumbnail_url, :raw_tags
+
+    def initialize item
+      snippet = item.fetch('snippet')
+
+      @id = item.fetch('id')
+      @title = snippet['title']
+      @channel_id = snippet['channelId']
+      @published_at = Time.iso8601(snippet['publishedAt'])
+      @thumbnail_url = pick_thumbnail(snippet['thumbnails'] || { })
+      @raw_tags = snippet['tags'] || []
+    end
+
+    def url = "https://www.youtube.com/watch?v=#{ @id }"
+
+    private
+
+    def pick_thumbnail thumbnails
+      ['maxres', 'standard', 'high', 'medium', 'default'].each do |key|
+        url = thumbnails.dig(key, 'url')
+        return url if url.present?
+      end
+
+      nil
+    end
+  end
+end

backend/config/environments/production.rb

@@ -18,8 +18,7 @@ Rails.application.configure do
   # Enable serving of images, stylesheets, and JavaScripts from an asset server.
   # config.asset_host = "http://assets.example.com"
 
-  # Store uploaded files on the local file system (see config/storage.yml for options).
-  config.active_storage.service = :local
+  config.active_storage.service = :r2
 
   # Assume all access to the app is happening through a SSL-terminating reverse proxy.
   config.assume_ssl = true

backend/config/environments/test.rb

@@ -50,4 +50,6 @@ Rails.application.configure do
 
   # Raise error when a before_action's only/except options reference missing actions.
   config.action_controller.raise_on_missing_callback_actions = true
+
+  Rails.application.routes.default_url_options[:host] = 'www.example.com'
 end

backend/config/routes.rb

@@ -6,17 +6,23 @@ Rails.application.routes.draw do
     delete ':child_id', action: :destroy
   end
 
-  resources :tags, only: [:index, :show, :update] do
+  resources :tags, only: [:index, :show] do
     collection do
       get :autocomplete
+      get :'with-depth', action: :with_depth
+      get :versions, to: 'tag_versions#index'
 
       scope :name do
         get ':name/deerjikists', action: :deerjikists_by_name
+        get ':name/materials', action: :materials_by_name
         get ':name', action: :show_by_name
       end
     end
 
     member do
+      put '', action: :update_all
+      patch '', action: :update
+
       get :deerjikists
     end
   end
@@ -47,6 +53,7 @@ Rails.application.routes.draw do
     collection do
       get :random
       get :changes
+      get :versions, to: 'post_versions#index'
     end
 
     member do
@@ -72,4 +79,15 @@ Rails.application.routes.draw do
       end
     end
   end
+
+  resources :theatres, only: [:show] do
+    member do
+      put :watching
+      patch :next_post
+    end
+
+    resources :comments, controller: :theatre_comments, only: [:index, :create]
+  end
+
+  resources :materials, only: [:index, :show, :create, :update, :destroy]
 end

backend/config/schedule.rb

@@ -1,12 +1,27 @@
 env :PATH, '/root/.rbenv/shims:/root/.rbenv/bin:/usr/local/bin:/usr/bin:/bin'
 
+set :path, '/var/www/btrc-hub/backend'
+set :environment, 'production'
 set :output, standard: '/var/log/btrc_hub_nico_sync.log',
              error:    '/var/log/btrc_hub_nico_sync_err.log'
 
-every 1.day, at: '3:00 pm' do
+job_type :rake,
+         'cd :path && set -a && . /etc/btrc-hub/backend.env && set +a && ' \
+         ':environment_variable=:environment bundle exec rake :task --silent :output'
+
+every 1.day, at: '11:00 am' do
   rake 'nico:sync', environment: 'production'
 end
 
 every 1.day, at: '0:00 am' do
   rake 'post_similarity:calc', environment: 'production'
+  rake 'tag_similarity:calc', environment: 'production'
+end
+
+every 1.day, at: '7:50 am' do
+  rake 'nico:export', environment: 'production'
+end
+
+every :hour do
+  rake 'post:sync', environment: 'production'
 end

backend/config/storage.yml

@@ -6,29 +6,11 @@ local:
   service: Disk
   root: <%= Rails.root.join("storage") %>
 
-# Use bin/rails credentials:edit to set the AWS secrets (as aws:access_key_id|secret_access_key)
-# amazon:
-#   service: S3
-#   access_key_id: <%= Rails.application.credentials.dig(:aws, :access_key_id) %>
-#   secret_access_key: <%= Rails.application.credentials.dig(:aws, :secret_access_key) %>
-#   region: us-east-1
-#   bucket: your_own_bucket-<%= Rails.env %>
-
-# Remember not to checkin your GCS keyfile to a repository
-# google:
-#   service: GCS
-#   project: your_project
-#   credentials: <%= Rails.root.join("path/to/gcs.keyfile") %>
-#   bucket: your_own_bucket-<%= Rails.env %>
-
-# Use bin/rails credentials:edit to set the Azure Storage secret (as azure_storage:storage_access_key)
-# microsoft:
-#   service: AzureStorage
-#   storage_account_name: your_account_name
-#   storage_access_key: <%= Rails.application.credentials.dig(:azure_storage, :storage_access_key) %>
-#   container: your_container_name-<%= Rails.env %>
-
-# mirror:
-#   service: Mirror
-#   primary: local
-#   mirrors: [ amazon, google, microsoft ]
+r2:
+  service: S3
+  endpoint: <%= ENV['R2_ENDPOINT'] %>
+  access_key_id: <%= ENV['R2_ACCESS_KEY_ID'] %>
+  secret_access_key: <%= ENV['R2_SECRET_ACCESS_KEY'] %>
+  bucket: <%= ENV['R2_BUCKET'] %>
+  region: auto
+  request_checksum_calculation: when_required

backend/db/migrate/20260309123200_create_tag_name_sanitisation_rules.rb

@@ -0,0 +1,31 @@
+class CreateTagNameSanitisationRules < ActiveRecord::Migration[8.0]
+  def up
+    create_table :tag_name_sanitisation_rules, id: :integer, primary_key: :priority do |t|
+      t.string :source_pattern, null: false
+      t.string :replacement, null: false
+      t.timestamps
+      t.datetime :discarded_at
+      t.index :source_pattern, unique: true
+      t.index :discarded_at
+    end
+
+    now = ActiveRecord::Base.connection.quote(Time.current)
+    execute <<~SQL
+      INSERT INTO
+        tag_name_sanitisation_rules(priority, source_pattern, replacement, created_at, updated_at)
+      VALUES
+        (10, '\\\\*',           '_',         #{ now }, #{ now })
+      , (20, '\\\\?',           '_',         #{ now }, #{ now })
+      , (25, '\\\\/',           '_',         #{ now }, #{ now })
+      , (30, '_+',              '_',         #{ now }, #{ now })
+      , (40, '_$',              '',          #{ now }, #{ now })
+      , (45, '^([^:]+\\\\:)?_', '\\\\1',     #{ now }, #{ now })
+      , (50, '^([^:]+\\\\:)?$', '\\\\1null', #{ now }, #{ now })
+      ;
+    SQL
+  end
+
+  def down
+    drop_table :tag_name_sanitisation_rules
+  end
+end

backend/db/migrate/20260311123100_add_discarded_at_to_tags.rb

@@ -0,0 +1,6 @@
+class AddDiscardedAtToTags < ActiveRecord::Migration[8.0]
+  def change
+    add_column :tags, :discarded_at, :datetime
+    add_index :tags, :discarded_at
+  end
+end

backend/db/migrate/20260311232100_add_discarded_at_to_tag_names.rb

@@ -0,0 +1,6 @@
+class AddDiscardedAtToTagNames < ActiveRecord::Migration[8.0]
+  def change
+    add_column :tag_names, :discarded_at, :datetime
+    add_index :tag_names, :discarded_at
+  end
+end

backend/db/migrate/20260311232300_add_discarded_at_to_wiki_pages.rb

@@ -0,0 +1,6 @@
+class AddDiscardedAtToWikiPages < ActiveRecord::Migration[8.0]
+  def change
+    add_column :wiki_pages, :discarded_at, :datetime
+    add_index :wiki_pages, :discarded_at
+  end
+end

backend/db/migrate/20260316082500_create_theatres.rb

@@ -0,0 +1,17 @@
+class CreateTheatres < ActiveRecord::Migration[8.0]
+  def change
+    create_table :theatres do |t|
+      t.string :name
+      t.datetime :opens_at, null: false, index: true
+      t.datetime :closes_at, index: true
+      t.integer :kind, null: false, index: true
+      t.references :current_post, foreign_key: { to_table: :posts }, index: true
+      t.datetime :current_post_started_at
+      t.integer :next_comment_no, null: false, default: 1
+      t.references :host_user, foreign_key: { to_table: :users }
+      t.references :created_by_user, null: false, foreign_key: { to_table: :users }, index: true
+      t.timestamps
+      t.datetime :discarded_at, index: true
+    end
+  end
+end

backend/db/migrate/20260316083500_create_theatre_comments.rb

@@ -0,0 +1,12 @@
+class CreateTheatreComments < ActiveRecord::Migration[8.0]
+  def change
+    create_table :theatre_comments, primary_key: [:theatre_id, :no] do |t|
+      t.references :theatre, null: false, foreign_key: { to_table: :theatres }
+      t.integer :no, null: false
+      t.references :user, foreign_key: { to_table: :users }
+      t.text :content, null: false
+      t.timestamps
+      t.datetime :discarded_at, index: true
+    end
+  end
+end

backend/db/migrate/20260317015000_create_theatre_watching_users.rb

@@ -0,0 +1,12 @@
+class CreateTheatreWatchingUsers < ActiveRecord::Migration[8.0]
+  def change
+    create_table :theatre_watching_users, primary_key: [:theatre_id, :user_id] do |t|
+      t.references :theatre, null: false, foreign_key: { to_table: :theatres }
+      t.references :user, null: false, foreign_key: { to_table: :users }, index: true
+      t.datetime :expires_at, null: false, index: true
+      t.timestamps
+
+      t.index [:theatre_id, :expires_at]
+    end
+  end
+end

backend/db/migrate/20260329034700_create_materials.rb

@@ -0,0 +1,34 @@
+class CreateMaterials < ActiveRecord::Migration[8.0]
+  def change
+    create_table :materials do |t|
+      t.string :url
+      t.references :parent, index: true, foreign_key: { to_table: :materials }
+      t.references :tag, index: true, foreign_key: true
+      t.references :created_by_user, foreign_key: { to_table: :users }
+      t.references :updated_by_user, foreign_key: { to_table: :users }
+      t.timestamps
+      t.datetime :discarded_at, index: true
+      t.virtual :active_url, type: :string,
+                             as: 'IF(discarded_at IS NULL, url, NULL)',
+                             stored: false
+
+      t.index :active_url, unique: true
+    end
+
+    create_table :material_versions do |t|
+      t.references :material, null: false, foreign_key: true
+      t.integer :version_no, null: false
+      t.string :url, index: true
+      t.references :parent, index: true, foreign_key: { to_table: :materials }
+      t.references :tag, index: true, foreign_key: true
+      t.references :created_by_user, foreign_key: { to_table: :users }
+      t.references :updated_by_user, foreign_key: { to_table: :users }
+      t.timestamps
+      t.datetime :discarded_at, index: true
+
+      t.index [:material_id, :version_no],
+              unique: true,
+              name: 'index_material_versions_on_material_id_and_version_no'
+    end
+  end
+end

backend/db/migrate/20260409123700_create_post_versions.rb

@@ -0,0 +1,203 @@
+require 'set'
+
+
+class CreatePostVersions < ActiveRecord::Migration[8.0]
+  class Post < ActiveRecord::Base
+    self.table_name = 'posts'
+  end
+
+  class PostTag < ActiveRecord::Base
+    self.table_name = 'post_tags'
+  end
+
+  class PostVersion < ActiveRecord::Base
+    self.table_name = 'post_versions'
+  end
+
+  def up
+    create_table :post_versions do |t|
+      t.references :post, null: false, foreign_key: true
+      t.integer :version_no, null: false
+      t.string :event_type, null: false
+      t.string :title
+      t.string :url, limit: 768, null: false
+      t.string :thumbnail_base, limit: 2000
+      t.text :tags, null: false
+      t.references :parent, foreign_key: { to_table: :posts }
+      t.datetime :original_created_from
+      t.datetime :original_created_before
+      t.datetime :created_at, null: false
+      t.references :created_by_user, foreign_key: { to_table: :users }
+
+      t.index [:post_id, :version_no], unique: true
+      t.check_constraint 'version_no > 0',
+                         name: 'post_versions_version_no_positive'
+      t.check_constraint "event_type IN ('create', 'update', 'discard', 'restore')",
+                         name: 'post_versions_event_type_valid'
+    end
+
+    PostVersion.reset_column_information
+
+    say_with_time 'Backfilling post_versions' do
+      Post.find_in_batches(batch_size: 500) do |posts|
+        post_ids = posts.map(&:id)
+
+        post_tag_rows_by_post_id =
+            PostTag
+            .joins('INNER JOIN tags ON tags.id = post_tags.tag_id')
+            .joins('INNER JOIN tag_names ON tag_names.id = tags.tag_name_id')
+            .where(post_id: post_ids)
+            .pluck('post_tags.post_id',
+                   'post_tags.created_at',
+                   'post_tags.discarded_at',
+                   'post_tags.created_user_id',
+                   'post_tags.deleted_user_id',
+                   'tag_names.name')
+            .each_with_object(Hash.new { |h, k| h[k] = [] }) do |row, h|
+              post_id, created_at, discarded_at, created_user_id, deleted_user_id, tag_name = row
+              h[post_id] << { created_at:,
+                              discarded_at:,
+                              created_user_id:,
+                              deleted_user_id:,
+                              tag_name: }
+            end
+
+        rows = []
+
+        posts.each do |post|
+          post_tag_rows = post_tag_rows_by_post_id[post.id]
+
+          events = post_tag_rows.flat_map do |post_tag_row|
+            ary = [[post_tag_row[:created_at],
+                    post_tag_row[:created_user_id],
+                    :add,
+                    post_tag_row[:tag_name]]]
+
+            if post_tag_row[:discarded_at]
+              ary << [post_tag_row[:discarded_at],
+                      post_tag_row[:deleted_user_id],
+                      :remove,
+                      post_tag_row[:tag_name]]
+            end
+
+            ary
+          end
+
+          kind_order = { add: 0, remove: 1 }
+
+          events.sort_by! do |event_at, user_id, kind, tag_name|
+            [event_at, user_id || 0, kind_order.fetch(kind), tag_name]
+          end
+
+          event_buckets = bucket_events(events)
+
+          active_tags = Set.new
+          version_no = 0
+
+          if event_buckets.empty?
+            version_no += 1
+            rows << build_row(post:,
+                              version_no:,
+                              event_type: 'create',
+                              created_at: post.created_at,
+                              created_by_user_id: post.uploaded_user_id,
+                              tags: [])
+            next
+          end
+
+          first_bucket = event_buckets.first
+          merge_first_bucket_into_create = first_bucket[:first_at] <= post.created_at + 1.second
+
+          if merge_first_bucket_into_create
+            event_buckets.shift
+            apply_bucket!(active_tags, first_bucket)
+
+            version_no += 1
+            rows << build_row(
+                post:,
+                version_no:,
+                event_type: 'create',
+                created_at: post.created_at,
+                created_by_user_id: post.uploaded_user_id || first_bucket[:user_ids].compact.first,
+                tags: active_tags.to_a.sort)
+          else
+            version_no += 1
+            rows << build_row(
+                post:,
+                version_no:,
+                event_type: 'create',
+                created_at: post.created_at,
+                created_by_user_id: post.uploaded_user_id,
+                tags: [])
+          end
+
+          event_buckets.each do |bucket|
+            apply_bucket!(active_tags, bucket)
+
+            version_no += 1
+            rows << build_row(
+                post:,
+                version_no:,
+                event_type: 'update',
+                created_at: bucket[:first_at],
+                created_by_user_id: bucket[:user_ids].compact.first,
+                tags: active_tags.to_a.sort)
+          end
+        end
+
+        PostVersion.insert_all!(rows) if rows.any?
+      end
+    end
+  end
+
+  def down
+    drop_table :post_versions
+  end
+
+  private
+
+  def bucket_events events
+    buckets = []
+
+    events.each do |event_at, user_id, kind, tag_name|
+      if buckets.empty? || event_at - buckets.last[:last_at] > 1.second
+        buckets << { first_at: event_at,
+                     last_at: event_at,
+                     user_ids: [user_id],
+                     events: [[kind, tag_name]] }
+      else
+        bucket = buckets.last
+        bucket[:last_at] = event_at
+        bucket[:user_ids] << user_id
+        bucket[:events] << [kind, tag_name]
+      end
+    end
+
+    buckets
+  end
+
+  def apply_bucket! active_tags, bucket
+    bucket[:events].each do |kind, tag_name|
+      if kind == :add
+        active_tags.add(tag_name)
+      else
+        active_tags.delete(tag_name)
+      end
+    end
+  end
+
+  def build_row post:, version_no:, event_type:, created_at:, created_by_user_id:, tags:
+    { post_id: post.id,
+      version_no:,
+      event_type:,
+      title: post.title,
+      url: post.url,
+      thumbnail_base: post.thumbnail_base,
+      tags: tags.join(' '),
+      parent_id: post.parent_id,
+      original_created_from: post.original_created_from,
+      original_created_before: post.original_created_before,
+      created_at:,
+      created_by_user_id: }
+  end
+end

backend/db/migrate/20260419035400_create_tag_versions.rb

@@ -0,0 +1,156 @@
+class CreateTagVersions < ActiveRecord::Migration[8.0]
+  class Tag < ActiveRecord::Base
+    self.table_name = 'tags'
+  end
+
+  class TagName < ActiveRecord::Base
+    self.table_name = 'tag_names'
+  end
+
+  class TagImplication < ActiveRecord::Base
+    self.table_name = 'tag_implications'
+  end
+
+  class TagVersion < ActiveRecord::Base
+    self.table_name = 'tag_versions'
+  end
+
+  class NicoTagVersion < ActiveRecord::Base
+    self.table_name = 'nico_tag_versions'
+  end
+
+  class NicoTagRelation < ActiveRecord::Base
+    self.table_name = 'nico_tag_relations'
+  end
+
+  def up
+    create_table :tag_versions do |t|
+      t.references :tag, null: false, foreign_key: true, index: false
+      t.integer :version_no, null: false
+      t.string :event_type, null: false
+      t.string :name, null: false
+      t.string :category, null: false
+      t.text :aliases, null: false
+      t.text :parent_tag_ids, null: false
+      t.datetime :created_at, null: false
+      t.references :created_by_user, foreign_key: { to_table: :users }, index: false
+
+      t.index [:tag_id, :version_no], unique: true
+      t.index :created_at
+      t.index [:tag_id, :created_at], order: { created_at: :desc }
+      t.index [:created_by_user_id, :created_at], order: { created_at: :desc }
+      t.check_constraint 'version_no > 0',
+                         name: 'tag_versions_version_no_positive'
+    end
+
+    create_table :nico_tag_versions do |t|
+      t.references :tag, null: false, foreign_key: true, index: false
+      t.integer :version_no, null: false
+      t.string :event_type, null: false
+      t.string :name, null: false
+      t.text :linked_tags, null: false
+      t.datetime :created_at, null: false
+      t.references :created_by_user, foreign_key: { to_table: :users }, index: false
+
+      t.index [:tag_id, :version_no], unique: true
+      t.index :created_at
+      t.index [:tag_id, :created_at], order: { created_at: :desc }
+      t.index [:created_by_user_id, :created_at], order: { created_at: :desc }
+      t.check_constraint 'version_no > 0',
+                         name: 'nico_tag_versions_version_no_positive'
+    end
+
+    TagVersion.reset_column_information
+    say_with_time 'Backfilling tag_versions' do
+      Tag.where(discarded_at: nil)
+         .where.not(category: 'nico')
+         .find_in_batches(batch_size: 500) do |tags|
+        tag_ids = tags.map(&:id)
+
+        tag_implication_rows_by_tag_id =
+            TagImplication
+            .where(tag_id: tag_ids)
+            .pluck(:tag_id, :parent_tag_id)
+            .each_with_object(Hash.new { |h, k| h[k] = [] }) do |row, h|
+              h[row[0]] << row[1]
+            end
+
+        tag_name_rows_by_tag_id =
+            TagName
+            .joins('INNER JOIN tags ON tags.tag_name_id = tag_names.id')
+            .where(tags: { id: tag_ids })
+            .pluck('tags.id', 'tag_names.name')
+            .each_with_object({ }) do |row, h|
+              h[row[0]] = row[1]
+            end
+
+        tag_alias_rows_by_tag_id =
+            TagName
+            .joins('INNER JOIN tags ON tags.tag_name_id = tag_names.canonical_id')
+            .where(tags: { id: tag_ids })
+            .where(tag_names: { discarded_at: nil })
+            .pluck('tags.id', 'tag_names.name')
+            .each_with_object(Hash.new { |h, k| h[k] = [] }) do |row, h|
+              h[row[0]] << row[1]
+            end
+
+        TagVersion.insert_all(tags.map { |tag|
+          { tag_id: tag.id,
+            version_no: 1,
+            event_type: 'create',
+            name: tag_name_rows_by_tag_id[tag.id],
+            category: tag.category,
+            aliases: tag_alias_rows_by_tag_id[tag.id].sort.join(' '),
+            parent_tag_ids: tag_implication_rows_by_tag_id[tag.id].sort.join(' '),
+            created_at: tag.created_at,
+            created_by_user_id: nil }
+        })
+      end
+    end
+
+    NicoTagVersion.reset_column_information
+    say_with_time 'Backfilling nico_tag_versions' do
+      Tag.where(discarded_at: nil, category: 'nico')
+         .find_in_batches(batch_size: 500) do |tags|
+        tag_ids = tags.map(&:id)
+
+        tag_name_rows_by_tag_id =
+            TagName
+            .joins('INNER JOIN tags ON tags.tag_name_id = tag_names.id')
+            .where(tags: { id: tag_ids })
+            .pluck('tags.id', 'tag_names.name')
+            .each_with_object({ }) do |row, h|
+              h[row[0]] = row[1]
+            end
+
+        nico_tag_relation_rows_by_tag_id =
+            NicoTagRelation
+            .joins('INNER JOIN tags nico_tags ON nico_tags.id = nico_tag_relations.nico_tag_id')
+            .joins('INNER JOIN tags linked_tags ON linked_tags.id = nico_tag_relations.tag_id')
+            .joins('INNER JOIN tag_names ON tag_names.id = linked_tags.tag_name_id')
+            .where(nico_tags: { id: tag_ids })
+            .where(linked_tags: { discarded_at: nil })
+            .where(tag_names: { discarded_at: nil })
+            .pluck('nico_tags.id', 'tag_names.name')
+            .each_with_object(Hash.new { |h, k| h[k] = [] }) do |row, h|
+              h[row[0]] << row[1]
+            end
+
+        NicoTagVersion.insert_all(tags.map { |tag|
+          { tag_id: tag.id,
+            version_no: 1,
+            event_type: 'create',
+            name: tag_name_rows_by_tag_id[tag.id],
+            linked_tags: nico_tag_relation_rows_by_tag_id[tag.id].sort.join(' '),
+            created_at: tag.created_at,
+            created_by_user_id: nil }
+        })
+      end
+    end
+  end
+
+  def down
+    drop_table :nico_tag_versions
+    drop_table :tag_versions
+  end
+end

backend/db/migrate/20260426120600_create_wiki_versions.rb

@@ -0,0 +1,91 @@
+class CreateWikiVersions < ActiveRecord::Migration[8.0]
+  class WikiPage < ActiveRecord::Base
+    self.table_name = 'wiki_pages'
+  end
+
+  class WikiRevision < ActiveRecord::Base
+    self.table_name = 'wiki_revisions'
+  end
+
+  class WikiRevisionLine < ActiveRecord::Base
+    self.table_name = 'wiki_revision_lines'
+  end
+
+  class WikiLine < ActiveRecord::Base
+    self.table_name = 'wiki_lines'
+  end
+
+  class WikiVersion < ActiveRecord::Base
+    self.table_name = 'wiki_versions'
+  end
+
+  class TagName < ActiveRecord::Base
+    self.table_name = 'tag_names'
+  end
+
+  def up
+    add_column :wiki_pages, :body, :text, after: :tag_name_id
+
+    create_table :wiki_versions do |t|
+      t.references :wiki_page, null: false, foreign_key: true
+      t.integer :version_no, null: false
+      t.string :event_type, null: false
+      t.string :title, null: false
+      t.text :body, null: false
+      t.text :reason
+      t.datetime :created_at, null: false
+      t.references :created_by_user, foreign_key: { to_table: :users }
+
+      t.index [:wiki_page_id, :version_no], unique: true
+      t.check_constraint 'version_no > 0',
+                         name: 'wiki_versions_version_no_positive'
+      t.check_constraint "event_type IN ('create', 'update', 'discard', 'restore')",
+                         name: 'wiki_versions_event_type_valid'
+    end
+
+    WikiPage.reset_column_information
+    WikiVersion.reset_column_information
+
+    say_with_time 'Backfilling wiki_versions' do
+      WikiPage.find_each do |page|
+        base_revision_id = nil
+        version_no = 1
+        title = TagName.find(page.tag_name_id).name
+        body = nil
+        loop do
+          rev = WikiRevision.where(wiki_page_id: page.id).find_by(base_revision_id:)
+          break unless rev
+
+          body = WikiRevisionLine.where(wiki_revision_id: rev.id).order(:position).map { |wrl|
+            WikiLine.find(wrl.wiki_line_id).body
+          }.join("\n")
+
+          WikiVersion.create!(
+              wiki_page_id: page.id,
+              version_no:,
+              event_type: version_no == 1 ? 'create' : 'update',
+              title:,
+              body:,
+              reason: rev.message,
+              created_at: rev.created_at,
+              created_by_user_id: rev.created_user_id)
+
+          version_no += 1
+          base_revision_id = rev.id
+        end
+        if body
+          page.update!(body:)
+        else
+          page.destroy!
+        end
+      end
+    end
+
+    change_column_null :wiki_pages, :body, false
+  end
+
+  def down
+    drop_table :wiki_versions
+    remove_column :wiki_pages, :body
+  end
+end

backend/db/schema.rb

@@ -10,7 +10,7 @@
 #
 # It's strongly recommended that you check this file into your version control system.
 
-ActiveRecord::Schema[8.0].define(version: 2026_03_03_122700) do
+ActiveRecord::Schema[8.0].define(version: 2026_04_26_120600) do
   create_table "active_storage_attachments", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci", force: :cascade do |t|
     t.string "name", null: false
     t.string "record_type", null: false
@@ -56,6 +56,45 @@ ActiveRecord::Schema[8.0].define(version: 2026_03_03_122700) do
     t.index ["ip_address"], name: "index_ip_addresses_on_ip_address", unique: true
   end
 
+  create_table "material_versions", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci", force: :cascade do |t|
+    t.bigint "material_id", null: false
+    t.integer "version_no", null: false
+    t.string "url"
+    t.bigint "parent_id"
+    t.bigint "tag_id"
+    t.bigint "created_by_user_id"
+    t.bigint "updated_by_user_id"
+    t.datetime "created_at", null: false
+    t.datetime "updated_at", null: false
+    t.datetime "discarded_at"
+    t.index ["created_by_user_id"], name: "index_material_versions_on_created_by_user_id"
+    t.index ["discarded_at"], name: "index_material_versions_on_discarded_at"
+    t.index ["material_id", "version_no"], name: "index_material_versions_on_material_id_and_version_no", unique: true
+    t.index ["material_id"], name: "index_material_versions_on_material_id"
+    t.index ["parent_id"], name: "index_material_versions_on_parent_id"
+    t.index ["tag_id"], name: "index_material_versions_on_tag_id"
+    t.index ["updated_by_user_id"], name: "index_material_versions_on_updated_by_user_id"
+    t.index ["url"], name: "index_material_versions_on_url"
+  end
+
+  create_table "materials", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci", force: :cascade do |t|
+    t.string "url"
+    t.bigint "parent_id"
+    t.bigint "tag_id"
+    t.bigint "created_by_user_id"
+    t.bigint "updated_by_user_id"
+    t.datetime "created_at", null: false
+    t.datetime "updated_at", null: false
+    t.datetime "discarded_at"
+    t.virtual "active_url", type: :string, as: "if((`discarded_at` is null),`url`,NULL)"
+    t.index ["active_url"], name: "index_materials_on_active_url", unique: true
+    t.index ["created_by_user_id"], name: "index_materials_on_created_by_user_id"
+    t.index ["discarded_at"], name: "index_materials_on_discarded_at"
+    t.index ["parent_id"], name: "index_materials_on_parent_id"
+    t.index ["tag_id"], name: "index_materials_on_tag_id"
+    t.index ["updated_by_user_id"], name: "index_materials_on_updated_by_user_id"
+  end
+
   create_table "nico_tag_relations", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci", force: :cascade do |t|
     t.bigint "nico_tag_id", null: false
     t.bigint "tag_id", null: false
@@ -65,6 +104,21 @@ ActiveRecord::Schema[8.0].define(version: 2026_03_03_122700) do
     t.index ["tag_id"], name: "index_nico_tag_relations_on_tag_id"
   end
 
+  create_table "nico_tag_versions", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci", force: :cascade do |t|
+    t.bigint "tag_id", null: false
+    t.integer "version_no", null: false
+    t.string "event_type", null: false
+    t.string "name", null: false
+    t.text "linked_tags", null: false
+    t.datetime "created_at", null: false
+    t.bigint "created_by_user_id"
+    t.index ["created_at"], name: "index_nico_tag_versions_on_created_at"
+    t.index ["created_by_user_id", "created_at"], name: "index_nico_tag_versions_on_created_by_user_id_and_created_at", order: { created_at: :desc }
+    t.index ["tag_id", "created_at"], name: "index_nico_tag_versions_on_tag_id_and_created_at", order: { created_at: :desc }
+    t.index ["tag_id", "version_no"], name: "index_nico_tag_versions_on_tag_id_and_version_no", unique: true
+    t.check_constraint "`version_no` > 0", name: "nico_tag_versions_version_no_positive"
+  end
+
   create_table "post_similarities", primary_key: ["post_id", "target_post_id"], charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci", force: :cascade do |t|
     t.bigint "post_id", null: false
     t.bigint "target_post_id", null: false
@@ -93,6 +147,27 @@ ActiveRecord::Schema[8.0].define(version: 2026_03_03_122700) do
     t.index ["tag_id"], name: "index_post_tags_on_tag_id"
   end
 
+  create_table "post_versions", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci", force: :cascade do |t|
+    t.bigint "post_id", null: false
+    t.integer "version_no", null: false
+    t.string "event_type", null: false
+    t.string "title"
+    t.string "url", limit: 768, null: false
+    t.string "thumbnail_base", limit: 2000
+    t.text "tags", null: false
+    t.bigint "parent_id"
+    t.datetime "original_created_from"
+    t.datetime "original_created_before"
+    t.datetime "created_at", null: false
+    t.bigint "created_by_user_id"
+    t.index ["created_by_user_id"], name: "index_post_versions_on_created_by_user_id"
+    t.index ["parent_id"], name: "index_post_versions_on_parent_id"
+    t.index ["post_id", "version_no"], name: "index_post_versions_on_post_id_and_version_no", unique: true
+    t.index ["post_id"], name: "index_post_versions_on_post_id"
+    t.check_constraint "`event_type` in (_utf8mb4'create',_utf8mb4'update',_utf8mb4'discard',_utf8mb4'restore')", name: "post_versions_event_type_valid"
+    t.check_constraint "`version_no` > 0", name: "post_versions_version_no_positive"
+  end
+
   create_table "posts", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci", force: :cascade do |t|
     t.string "title"
     t.string "url", limit: 768, null: false
@@ -127,12 +202,24 @@ ActiveRecord::Schema[8.0].define(version: 2026_03_03_122700) do
     t.index ["tag_id"], name: "index_tag_implications_on_tag_id"
   end
 
+  create_table "tag_name_sanitisation_rules", primary_key: "priority", id: :integer, charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci", force: :cascade do |t|
+    t.string "source_pattern", null: false
+    t.string "replacement", null: false
+    t.datetime "created_at", null: false
+    t.datetime "updated_at", null: false
+    t.datetime "discarded_at"
+    t.index ["discarded_at"], name: "index_tag_name_sanitisation_rules_on_discarded_at"
+    t.index ["source_pattern"], name: "index_tag_name_sanitisation_rules_on_source_pattern", unique: true
+  end
+
   create_table "tag_names", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci", force: :cascade do |t|
     t.string "name", null: false
     t.bigint "canonical_id"
     t.datetime "created_at", null: false
     t.datetime "updated_at", null: false
+    t.datetime "discarded_at"
     t.index ["canonical_id"], name: "index_tag_names_on_canonical_id"
+    t.index ["discarded_at"], name: "index_tag_names_on_discarded_at"
     t.index ["name"], name: "index_tag_names_on_name", unique: true
   end
 
@@ -144,15 +231,81 @@ ActiveRecord::Schema[8.0].define(version: 2026_03_03_122700) do
     t.index ["target_tag_id"], name: "index_tag_similarities_on_target_tag_id"
   end
 
+  create_table "tag_versions", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci", force: :cascade do |t|
+    t.bigint "tag_id", null: false
+    t.integer "version_no", null: false
+    t.string "event_type", null: false
+    t.string "name", null: false
+    t.string "category", null: false
+    t.text "aliases", null: false
+    t.text "parent_tag_ids", null: false
+    t.datetime "created_at", null: false
+    t.bigint "created_by_user_id"
+    t.index ["created_at"], name: "index_tag_versions_on_created_at"
+    t.index ["created_by_user_id", "created_at"], name: "index_tag_versions_on_created_by_user_id_and_created_at", order: { created_at: :desc }
+    t.index ["tag_id", "created_at"], name: "index_tag_versions_on_tag_id_and_created_at", order: { created_at: :desc }
+    t.index ["tag_id", "version_no"], name: "index_tag_versions_on_tag_id_and_version_no", unique: true
+    t.check_constraint "`version_no` > 0", name: "tag_versions_version_no_positive"
+  end
+
   create_table "tags", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci", force: :cascade do |t|
     t.bigint "tag_name_id", null: false
     t.string "category", default: "general", null: false
     t.datetime "created_at", null: false
     t.datetime "updated_at", null: false
     t.integer "post_count", default: 0, null: false
+    t.datetime "discarded_at"
+    t.index ["discarded_at"], name: "index_tags_on_discarded_at"
     t.index ["tag_name_id"], name: "index_tags_on_tag_name_id", unique: true
   end
 
+  create_table "theatre_comments", primary_key: ["theatre_id", "no"], charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci", force: :cascade do |t|
+    t.bigint "theatre_id", null: false
+    t.integer "no", null: false
+    t.bigint "user_id"
+    t.text "content", null: false
+    t.datetime "created_at", null: false
+    t.datetime "updated_at", null: false
+    t.datetime "discarded_at"
+    t.index ["discarded_at"], name: "index_theatre_comments_on_discarded_at"
+    t.index ["theatre_id"], name: "index_theatre_comments_on_theatre_id"
+    t.index ["user_id"], name: "index_theatre_comments_on_user_id"
+  end
+
+  create_table "theatre_watching_users", primary_key: ["theatre_id", "user_id"], charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci", force: :cascade do |t|
+    t.bigint "theatre_id", null: false
+    t.bigint "user_id", null: false
+    t.datetime "expires_at", null: false
+    t.datetime "created_at", null: false
+    t.datetime "updated_at", null: false
+    t.index ["expires_at"], name: "index_theatre_watching_users_on_expires_at"
+    t.index ["theatre_id", "expires_at"], name: "index_theatre_watching_users_on_theatre_id_and_expires_at"
+    t.index ["theatre_id"], name: "index_theatre_watching_users_on_theatre_id"
+    t.index ["user_id"], name: "index_theatre_watching_users_on_user_id"
+  end
+
+  create_table "theatres", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci", force: :cascade do |t|
+    t.string "name"
+    t.datetime "opens_at", null: false
+    t.datetime "closes_at"
+    t.integer "kind", null: false
+    t.bigint "current_post_id"
+    t.datetime "current_post_started_at"
+    t.integer "next_comment_no", default: 1, null: false
+    t.bigint "host_user_id"
+    t.bigint "created_by_user_id", null: false
+    t.datetime "created_at", null: false
+    t.datetime "updated_at", null: false
+    t.datetime "discarded_at"
+    t.index ["closes_at"], name: "index_theatres_on_closes_at"
+    t.index ["created_by_user_id"], name: "index_theatres_on_created_by_user_id"
+    t.index ["current_post_id"], name: "index_theatres_on_current_post_id"
+    t.index ["discarded_at"], name: "index_theatres_on_discarded_at"
+    t.index ["host_user_id"], name: "index_theatres_on_host_user_id"
+    t.index ["kind"], name: "index_theatres_on_kind"
+    t.index ["opens_at"], name: "index_theatres_on_opens_at"
+  end
+
   create_table "user_ips", primary_key: ["user_id", "ip_address_id"], charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci", force: :cascade do |t|
     t.bigint "user_id", null: false
     t.bigint "ip_address_id", null: false
@@ -178,6 +331,19 @@ ActiveRecord::Schema[8.0].define(version: 2026_03_03_122700) do
     t.datetime "updated_at", null: false
   end
 
+  create_table "wiki_assets", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci", force: :cascade do |t|
+    t.bigint "wiki_page_id", null: false
+    t.integer "no", null: false
+    t.string "alt_text"
+    t.binary "sha256", limit: 32, null: false
+    t.bigint "created_by_user_id", null: false
+    t.datetime "created_at", null: false
+    t.datetime "updated_at", null: false
+    t.index ["created_by_user_id"], name: "index_wiki_assets_on_created_by_user_id"
+    t.index ["wiki_page_id", "no"], name: "index_wiki_assets_on_wiki_page_id_and_no", unique: true
+    t.index ["wiki_page_id", "sha256"], name: "index_wiki_assets_on_wiki_page_id_and_sha256", unique: true
+  end
+
   create_table "wiki_lines", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci", force: :cascade do |t|
     t.string "sha256", limit: 64, null: false
     t.text "body", null: false
@@ -188,11 +354,15 @@ ActiveRecord::Schema[8.0].define(version: 2026_03_03_122700) do
 
   create_table "wiki_pages", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci", force: :cascade do |t|
     t.bigint "tag_name_id", null: false
+    t.text "body", null: false
     t.bigint "created_user_id", null: false
     t.bigint "updated_user_id", null: false
     t.datetime "created_at", null: false
     t.datetime "updated_at", null: false
+    t.datetime "discarded_at"
+    t.integer "next_asset_no", default: 1, null: false
     t.index ["created_user_id"], name: "index_wiki_pages_on_created_user_id"
+    t.index ["discarded_at"], name: "index_wiki_pages_on_discarded_at"
     t.index ["tag_name_id"], name: "index_wiki_pages_on_tag_name_id", unique: true
     t.index ["updated_user_id"], name: "index_wiki_pages_on_updated_user_id"
   end
@@ -227,16 +397,46 @@ ActiveRecord::Schema[8.0].define(version: 2026_03_03_122700) do
     t.index ["wiki_page_id"], name: "index_wiki_revisions_on_wiki_page_id"
   end
 
+  create_table "wiki_versions", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci", force: :cascade do |t|
+    t.bigint "wiki_page_id", null: false
+    t.integer "version_no", null: false
+    t.string "event_type", null: false
+    t.string "title", null: false
+    t.text "body", null: false
+    t.text "reason"
+    t.datetime "created_at", null: false
+    t.bigint "created_by_user_id"
+    t.index ["created_by_user_id"], name: "index_wiki_versions_on_created_by_user_id"
+    t.index ["wiki_page_id", "version_no"], name: "index_wiki_versions_on_wiki_page_id_and_version_no", unique: true
+    t.index ["wiki_page_id"], name: "index_wiki_versions_on_wiki_page_id"
+    t.check_constraint "`event_type` in (_utf8mb4'create',_utf8mb4'update',_utf8mb4'discard',_utf8mb4'restore')", name: "wiki_versions_event_type_valid"
+    t.check_constraint "`version_no` > 0", name: "wiki_versions_version_no_positive"
+  end
+
   add_foreign_key "active_storage_attachments", "active_storage_blobs", column: "blob_id"
   add_foreign_key "active_storage_variant_records", "active_storage_blobs", column: "blob_id"
+  add_foreign_key "material_versions", "materials"
+  add_foreign_key "material_versions", "materials", column: "parent_id"
+  add_foreign_key "material_versions", "tags"
+  add_foreign_key "material_versions", "users", column: "created_by_user_id"
+  add_foreign_key "material_versions", "users", column: "updated_by_user_id"
+  add_foreign_key "materials", "materials", column: "parent_id"
+  add_foreign_key "materials", "tags"
+  add_foreign_key "materials", "users", column: "created_by_user_id"
+  add_foreign_key "materials", "users", column: "updated_by_user_id"
   add_foreign_key "nico_tag_relations", "tags"
   add_foreign_key "nico_tag_relations", "tags", column: "nico_tag_id"
+  add_foreign_key "nico_tag_versions", "tags"
+  add_foreign_key "nico_tag_versions", "users", column: "created_by_user_id"
   add_foreign_key "post_similarities", "posts"
   add_foreign_key "post_similarities", "posts", column: "target_post_id"
   add_foreign_key "post_tags", "posts"
   add_foreign_key "post_tags", "tags"
   add_foreign_key "post_tags", "users", column: "created_user_id"
   add_foreign_key "post_tags", "users", column: "deleted_user_id"
+  add_foreign_key "post_versions", "posts"
+  add_foreign_key "post_versions", "posts", column: "parent_id"
+  add_foreign_key "post_versions", "users", column: "created_by_user_id"
   add_foreign_key "posts", "posts", column: "parent_id"
   add_foreign_key "posts", "users", column: "uploaded_user_id"
   add_foreign_key "settings", "users"
@@ -245,11 +445,22 @@ ActiveRecord::Schema[8.0].define(version: 2026_03_03_122700) do
   add_foreign_key "tag_names", "tag_names", column: "canonical_id"
   add_foreign_key "tag_similarities", "tags"
   add_foreign_key "tag_similarities", "tags", column: "target_tag_id"
+  add_foreign_key "tag_versions", "tags"
+  add_foreign_key "tag_versions", "users", column: "created_by_user_id"
   add_foreign_key "tags", "tag_names"
+  add_foreign_key "theatre_comments", "theatres"
+  add_foreign_key "theatre_comments", "users"
+  add_foreign_key "theatre_watching_users", "theatres"
+  add_foreign_key "theatre_watching_users", "users"
+  add_foreign_key "theatres", "posts", column: "current_post_id"
+  add_foreign_key "theatres", "users", column: "created_by_user_id"
+  add_foreign_key "theatres", "users", column: "host_user_id"
   add_foreign_key "user_ips", "ip_addresses"
   add_foreign_key "user_ips", "users"
   add_foreign_key "user_post_views", "posts"
   add_foreign_key "user_post_views", "users"
+  add_foreign_key "wiki_assets", "users", column: "created_by_user_id"
+  add_foreign_key "wiki_assets", "wiki_pages"
   add_foreign_key "wiki_pages", "tag_names"
   add_foreign_key "wiki_pages", "users", column: "created_user_id"
   add_foreign_key "wiki_pages", "users", column: "updated_user_id"
@@ -259,4 +470,6 @@ ActiveRecord::Schema[8.0].define(version: 2026_03_03_122700) do
   add_foreign_key "wiki_revisions", "wiki_pages"
   add_foreign_key "wiki_revisions", "wiki_pages", column: "redirect_page_id"
   add_foreign_key "wiki_revisions", "wiki_revisions", column: "base_revision_id"
+  add_foreign_key "wiki_versions", "users", column: "created_by_user_id"
+  add_foreign_key "wiki_versions", "wiki_pages"
 end

backend/lib/tasks/export_nico.rake

@@ -0,0 +1,23 @@
+namespace :nico do
+  desc 'ニコニコ DB 逆連携'
+  task export: :environment do
+    require 'open3'
+
+    mysql_user = ENV.fetch('MYSQL_USER')
+    mysql_pass = ENV.fetch('MYSQL_PASS')
+    nizika_nico_path = ENV.fetch('NIZIKA_NICO_PATH')
+
+    videos = Post.where('url LIKE ?', '%nicovideo.jp/watch/%').pluck(:url).filter_map {
+      _1[%r{nicovideo\.jp/watch/([^/?#]+)}, 1]
+    }.uniq
+
+    next if videos.empty?
+
+    _, stderr, status = Open3.capture3(
+        { 'MYSQL_USER' => mysql_user, 'MYSQL_PASS' => mysql_pass },
+        'python3', '-m', 'tracked_videos.put_bulk_upsert', *videos,
+        chdir: nizika_nico_path)
+
+    raise stderr unless status.success?
+  end
+end

backend/lib/tasks/sync_nico.rake

@@ -61,6 +61,9 @@ namespace :nico do
       original_created_from = original_created_at&.change(sec: 0)
       original_created_before = original_created_from&.+(1.minute)
 
+      post_created = false
+      post_changed = false
+
       if post
         attrs = { title:, original_created_from:, original_created_before: }
 
@@ -76,11 +79,13 @@ namespace :nico do
         end
 
         post.assign_attributes(attrs)
-        if post.changed?
+        post_changed = post.changed?
+        if post_changed
           post.save!
           post.resized_thumbnail! if post.thumbnail.attached?
         end
       else
+        post_created = true
         url = "https://www.nicovideo.jp/watch/#{ code }"
         thumbnail_base = fetch_thumbnail.(url) rescue nil
         post = Post.new(title:, url:, thumbnail_base:, uploaded_user: nil,
@@ -108,8 +113,12 @@ namespace :nico do
       desired_non_nico_tag_ids = []
 
       datum['tags'].each do |raw|
-        name = "nico:#{ raw }"
+        name = TagNameSanitisationRule.sanitise("nico:#{ raw }")
         tag = Tag.find_or_create_by_tag_name!(name, category: :nico)
+
+        event_type = tag.nico_tag_versions.exists? ? :update : :create
+        NicoTagVersionRecorder.record!(tag:, event_type:, created_by_user: nil)
+
         desired_nico_tag_based_ids << tag.id
 
         # 新たに記載される外部タグと連携される内部タグを記載
@@ -140,6 +149,13 @@ namespace :nico do
       desired_all_tag_ids.uniq!
 
       sync_post_tags!(post, desired_all_tag_ids, current_tag_ids: kept_tag_ids)
+
+      if post_created
+        PostVersionRecorder.record!(post:, event_type: :create, created_by_user: nil)
+      elsif post_changed || kept_tag_ids != desired_all_tag_ids.to_set
+        PostVersionRecorder.ensure_snapshot!(post, created_by_user: nil)
+        PostVersionRecorder.record!(post:, event_type: :update, created_by_user: nil)
+      end
     end
   end
 end

backend/lib/tasks/sync_posts.rake

@@ -0,0 +1,6 @@
+namespace :post do
+  desc '投稿同期（ニコニコ以外）'
+  task sync: :environment do
+    Youtube::Sync.new.sync!
+  end
+end

backend/spec/factories/tags.rb

@@ -1,12 +1,22 @@
 FactoryBot.define do
   factory :tag do
+    transient do
+      name { nil }
+    end
+
     category { :general }
     post_count { 0 }
     association :tag_name
 
+    after(:build) do |tag, evaluator|
+      tag.name = evaluator.name if evaluator.name.present?
+    end
+
     trait :nico do
       category { :nico }
-      tag_name { association(:tag_name, name: "nico:#{ SecureRandom.hex(4) }") }
+      transient do
+        name { "nico:#{ SecureRandom.hex(4) }" }
+      end
     end
   end
 end

backend/spec/factories/theatre_comments.rb

@@ -0,0 +1,8 @@
+FactoryBot.define do
+  factory :theatre_comment do
+    association :theatre
+    association :user
+    sequence (:no) { |n| n }
+    content { 'test comment' }
+  end
+end

backend/spec/factories/theatres.rb

@@ -0,0 +1,11 @@
+FactoryBot.define do
+  factory :theatre do
+    name { 'Test Theatre' }
+    kind { 1 }
+    opens_at { Time.current }
+    closes_at { 1.day.from_now }
+    next_comment_no { 1 }
+
+    association :created_by_user, factory: :user
+  end
+end

backend/spec/factories/wiki_pages.rb

@@ -3,5 +3,7 @@ FactoryBot.define do
     title { "TestPage" }
     association :created_user, factory: :user
     association :updated_user, factory: :user
+
+    body { ' ' }
   end
 end

backend/spec/models/post_version_spec.rb

@@ -0,0 +1,41 @@
+require 'rails_helper'
+
+RSpec.describe PostVersion, type: :model do
+  let!(:tag_name) { TagName.create!(name: 'post_version_spec_tag') }
+  let!(:tag) { Tag.create!(tag_name: tag_name, category: :general) }
+
+  let!(:post_record) do
+    Post.create!(title: 'spec post', url: 'https://example.com/post-version-spec').tap do |post|
+      PostTag.create!(post: post, tag: tag)
+    end
+  end
+
+  let!(:post_version) do
+    PostVersion.create!(
+      post: post_record,
+      version_no: 1,
+      event_type: 'create',
+      title: post_record.title,
+      url: post_record.url,
+      thumbnail_base: post_record.thumbnail_base,
+      tags: post_record.snapshot_tag_names.join(' '),
+      parent: post_record.parent,
+      original_created_from: post_record.original_created_from,
+      original_created_before: post_record.original_created_before,
+      created_at: Time.current,
+      created_by_user: nil
+    )
+  end
+
+  it 'is read only after create' do
+    expect do
+      post_version.update!(title: 'changed')
+    end.to raise_error(ActiveRecord::ReadOnlyRecord)
+  end
+
+  it 'cannot be destroyed' do
+    expect do
+      post_version.destroy!
+    end.to raise_error(ActiveRecord::ReadOnlyRecord)
+  end
+end

backend/spec/models/tag_name_sanitisation_rule_spec.rb

@@ -0,0 +1,101 @@
+require 'rails_helper'
+
+RSpec.describe TagNameSanitisationRule, type: :model do
+  describe '.sanitise' do
+    before do
+      described_class.create!(priority: 10, source_pattern: '_', replacement: '')
+      described_class.create!(priority: 20, source_pattern: 'ABC', replacement: 'abc')
+    end
+
+    it 'applies sanitisation rules sequentially in priority order' do
+      expect(described_class.sanitise('A_B_C')).to eq('abc')
+    end
+
+    it 'does not fail when a rule does not match' do
+      expect { described_class.sanitise('xyz') }.not_to raise_error
+      expect(described_class.sanitise('xyz')).to eq('xyz')
+    end
+  end
+
+  describe 'validations' do
+    it 'is invalid when source_pattern is not a valid regexp' do
+      rule = described_class.new(priority: 10, source_pattern: '[', replacement: '')
+      expect(rule).to be_invalid
+      expect(rule.errors[:source_pattern]).to be_present
+    end
+  end
+
+  describe '.apply!' do
+    before do
+      described_class.create!(priority: 10, source_pattern: '_', replacement: '')
+    end
+
+    context 'when no conflicting tag_name exists' do
+      let!(:tag_name) do
+        TagName.create!(name: 'tmp').tap do |tn|
+          tn.update_columns(name: 'foo_bar', updated_at: Time.current)
+        end
+      end
+
+      it 'renames the tag_name' do
+        described_class.apply!
+        expect(tag_name.reload.name).to eq('foobar')
+      end
+    end
+
+    context 'when a conflicting canonical tag_name exists' do
+      let!(:existing) { TagName.create!(name: 'foobar') }
+      let!(:source) do
+        TagName.create!(name: 'tmp').tap do |tn|
+          tn.update_columns(name: 'foo_bar', updated_at: Time.current)
+        end
+      end
+
+      it 'deletes the source tag_name' do
+        described_class.apply!
+        expect(TagName.exists?(source.id)).to be(false)
+        expect(existing.reload.name).to eq('foobar')
+      end
+    end
+
+    context 'when the source tag_name has a tag and the existing one has no tag' do
+      let!(:existing) { TagName.create!(name: 'foobar') }
+      let!(:source_tag) { create(:tag, name: 'tmp', category: :general) }
+      let!(:source_tag_name_id) { source_tag.tag_name_id }
+
+      before do
+        source_tag.tag_name.update_columns(name: 'foo_bar', updated_at: Time.current)
+      end
+
+      it 'moves the tag to the existing tag_name' do
+        described_class.apply!
+        expected_tag_name_id = existing.canonical_id || existing.id
+        expect(source_tag.reload.tag_name_id).to eq(expected_tag_name_id)
+        expect(TagName.exists?(source_tag_name_id)).to be(false)
+      end
+    end
+
+    context 'when both source and existing tag_names have tags' do
+      let!(:existing_tn) { TagName.create!(name: 'foobar') }
+      let!(:existing_tag) { Tag.create!(tag_name: existing_tn, category: :general) }
+
+      let!(:source_tn) { TagName.create!(name: 'tmp') }
+      let!(:source_tag) { Tag.create!(tag_name: source_tn, category: :general) }
+      let!(:source_tag_name_id) { source_tn.id }
+
+      before do
+        source_tn.update_columns(name: 'foo_bar', updated_at: Time.current)
+      end
+
+      it 'merges the source tag into the existing tag and deletes the source tag_name' do
+        expect(TagName.find_by(name: 'foobar')&.tag&.id).to eq(existing_tag.id)
+        expect(TagName.find_by(name: 'foo_bar')&.tag&.id).to eq(source_tag.id)
+
+        described_class.apply!
+
+        expect(Tag.exists?(source_tag.id)).to be(false)
+        expect(TagName.exists?(source_tag.tag_name_id)).to be(false)
+      end
+    end
+  end
+end

backend/spec/models/tag_spec.rb

@@ -2,20 +2,31 @@ require 'rails_helper'
 
 RSpec.describe Tag, type: :model do
   describe '.merge_tags!' do
-    let!(:target_tag) { create(:tag) }
-    let!(:source_tag) { create(:tag) }
+    let!(:target_tag) { create(:tag, category: :general) }
+    let!(:source_tag) { create(:tag, category: :general) }
+    let!(:source_tag_name) { source_tag.tag_name }
 
-    let!(:post_record) { Post.create!(url: 'https://example.com/posts/1', title: 'test post') }
+    let!(:post_record) do
+      Post.create!(url: 'https://example.com/posts/1', title: 'test post')
+    end
 
     context 'when merging a simple source tag' do
       let!(:source_post_tag) { PostTag.create!(post: post_record, tag: source_tag) }
 
-      it 'moves the post_tag, deletes the source tag, and aliases the source tag_name' do
+      it 'discards the source post_tag, creates an active target post_tag, discards the source tag, and aliases the source tag_name' do
         described_class.merge_tags!(target_tag, [source_tag])
 
-        expect(source_post_tag.reload.tag_id).to eq(target_tag.id)
-        expect(Tag.exists?(source_tag.id)).to be(false)
-        expect(source_tag.tag_name.reload.canonical_id).to eq(target_tag.tag_name_id)
+        source_pt = PostTag.with_discarded.find(source_post_tag.id)
+        active_target = PostTag.kept.find_by(post_id: post_record.id, tag_id: target_tag.id)
+
+        expect(source_pt.discarded_at).to be_present
+        expect(source_pt.tag_id).to eq(source_tag.id)
+        expect(active_target).to be_present
+
+        expect(Tag.with_discarded.find(source_tag.id)).to be_discarded
+        expect(TagName.with_discarded.find(source_tag_name.id)).not_to be_discarded
+        expect(source_tag_name.reload.canonical_id).to eq(target_tag.tag_name_id)
+        expect(target_tag.reload.post_count).to eq(1)
       end
     end
 
@@ -23,38 +34,86 @@ RSpec.describe Tag, type: :model do
       let!(:target_post_tag) { PostTag.create!(post: post_record, tag: target_tag) }
       let!(:source_post_tag) { PostTag.create!(post: post_record, tag: source_tag) }
 
-      it 'discards the duplicate source post_tag and keeps one active target post_tag' do
+      it 'discards the source post_tag, keeps one active target post_tag, discards the source tag, and aliases the source tag_name' do
         described_class.merge_tags!(target_tag, [source_tag])
 
+        source_pt = PostTag.with_discarded.find(source_post_tag.id)
         active = PostTag.kept.where(post_id: post_record.id, tag_id: target_tag.id)
-        discarded_source = PostTag.with_discarded.find(source_post_tag.id)
 
+        expect(source_pt.discarded_at).to be_present
+        expect(source_pt.tag_id).to eq(source_tag.id)
         expect(active.count).to eq(1)
-        expect(discarded_source.discarded_at).to be_present
-        expect(Tag.exists?(source_tag.id)).to be(false)
-        expect(source_tag.tag_name.reload.canonical_id).to eq(target_tag.tag_name_id)
+        expect(active.first.id).to eq(target_post_tag.id)
+
+        expect(Tag.with_discarded.find(source_tag.id)).to be_discarded
+        expect(TagName.with_discarded.find(source_tag_name.id)).not_to be_discarded
+        expect(source_tag_name.reload.canonical_id).to eq(target_tag.tag_name_id)
+        expect(target_tag.reload.post_count).to eq(1)
       end
     end
 
     context 'when source_tags includes the target itself' do
       let!(:source_post_tag) { PostTag.create!(post: post_record, tag: source_tag) }
 
-      it 'ignores the target tag in source_tags' do
+      it 'ignores the target in source_tags while still merging the source tag' do
         described_class.merge_tags!(target_tag, [source_tag, target_tag])
 
-        expect(Tag.exists?(target_tag.id)).to be(true)
-        expect(Tag.exists?(source_tag.id)).to be(false)
-        expect(source_post_tag.reload.tag_id).to eq(target_tag.id)
+        source_pt = PostTag.with_discarded.find(source_post_tag.id)
+        active_target = PostTag.kept.find_by(post_id: post_record.id, tag_id: target_tag.id)
+
+        expect(Tag.find(target_tag.id)).to be_present
+        expect(Tag.with_discarded.find(source_tag.id)).to be_discarded
+        expect(source_pt.discarded_at).to be_present
+        expect(source_pt.tag_id).to eq(source_tag.id)
+        expect(active_target).to be_present
+        expect(source_tag_name.reload.canonical_id).to eq(target_tag.tag_name_id)
+        expect(target_tag.reload.post_count).to eq(1)
       end
     end
 
-    context 'when aliasing the source tag_name is invalid' do
+    context 'when the source tag_name is invalid under sanitisation rules' do
+      let!(:source_post_tag) { PostTag.create!(post: post_record, tag: source_tag) }
+      let!(:sanitisation_rule) do
+        TagNameSanitisationRule.create!(
+          priority: 99_999,
+          source_pattern: 'INVALIDTOKEN',
+          replacement: ''
+        )
+      end
+
+      before do
+        source_tag_name.update_columns(
+          name: "#{ source_tag_name.name }INVALIDTOKEN",
+          updated_at: Time.current
+        )
+      end
+
+      it 'still merges, but discards the source tag_name instead of aliasing it' do
+        described_class.merge_tags!(target_tag, [source_tag])
+
+        source_pt = PostTag.with_discarded.find(source_post_tag.id)
+        active_target = PostTag.kept.find_by(post_id: post_record.id, tag_id: target_tag.id)
+        discarded_source_tag_name = TagName.with_discarded.find(source_tag_name.id)
+
+        expect(source_pt.discarded_at).to be_present
+        expect(source_pt.tag_id).to eq(source_tag.id)
+        expect(active_target).to be_present
+
+        expect(Tag.with_discarded.find(source_tag.id)).to be_discarded
+        expect(target_tag.reload.post_count).to eq(1)
+      end
+    end
+
+    context 'when the source tag_name has a wiki_page' do
       let!(:source_post_tag) { PostTag.create!(post: post_record, tag: source_tag) }
       let!(:wiki_page) do
-        WikiPage.create!(
-            tag_name: source_tag.tag_name,
-            created_user: create_admin_user!,
-            updated_user: create_admin_user!)
+        admin = create_admin_user!
+
+        Wiki::Commit.create_content!(
+            tag_name: source_tag_name,
+            body: 'source wiki body',
+            created_by_user: admin,
+            message: 'init')
       end
 
       it 'rolls back the transaction' do
@@ -62,9 +121,95 @@ RSpec.describe Tag, type: :model do
           described_class.merge_tags!(target_tag, [source_tag])
         }.to raise_error(ActiveRecord::RecordInvalid)
 
-        expect(Tag.exists?(source_tag.id)).to be(true)
-        expect(source_post_tag.reload.tag_id).to eq(source_tag.id)
-        expect(source_tag.tag_name.reload.canonical_id).to be_nil
+        expect(Tag.with_discarded.find(source_tag.id)).not_to be_discarded
+        expect(TagName.with_discarded.find(source_tag_name.id)).not_to be_discarded
+        expect(PostTag.kept.find(source_post_tag.id).tag_id).to eq(source_tag.id)
+        expect(PostTag.kept.find_by(post_id: post_record.id, tag_id: target_tag.id)).to be_nil
+        expect(source_tag_name.reload.canonical_id).to be_nil
+        expect(target_tag.reload.post_count).to eq(0)
+      end
+    end
+
+    context 'when merging a nico source tag' do
+      let!(:target_tag) { create(:tag, category: :nico, name: 'nico:foo') }
+      let!(:source_tag) { create(:tag, category: :nico, name: 'nico:bar') }
+      let!(:source_tag_name_id) { source_tag.tag_name_id }
+
+      it 'discards the source tag_name instead of aliasing it' do
+        described_class.merge_tags!(target_tag, [source_tag])
+
+        discarded_source_tag = Tag.with_discarded.find(source_tag.id)
+        discarded_source_tag_name = TagName.with_discarded.find(source_tag_name_id)
+
+        expect(discarded_source_tag).to be_discarded
+        expect(discarded_source_tag_name).to be_discarded
+        expect(discarded_source_tag_name.canonical_id).to be_nil
+        expect(target_tag.reload.post_count).to eq(0)
+      end
+    end
+
+    def snapshot_tags(post)
+      post.snapshot_tag_names.join(' ')
+    end
+
+    def create_post_version_for!(post, version_no: 1, event_type: 'create', created_by_user: nil)
+      PostVersion.create!(
+        post: post,
+        version_no: version_no,
+        event_type: event_type,
+        title: post.title,
+        url: post.url,
+        thumbnail_base: post.thumbnail_base,
+        tags: snapshot_tags(post),
+        parent: post.parent,
+        original_created_from: post.original_created_from,
+        original_created_before: post.original_created_before,
+        created_at: Time.current,
+        created_by_user: created_by_user
+      )
+    end
+
+    context 'when post versions are enabled' do
+      let!(:source_post_tag) { PostTag.create!(post: post_record, tag: source_tag) }
+      let!(:unaffected_post) do
+        Post.create!(url: 'https://example.com/posts/2', title: 'unaffected post')
+      end
+
+      before do
+        create_post_version_for!(post_record)
+        create_post_version_for!(unaffected_post)
+      end
+
+      it 'creates an update post_version only for affected posts' do
+        expect {
+          described_class.merge_tags!(target_tag, [source_tag])
+        }.to change(PostVersion, :count).by(1)
+
+        affected_versions = post_record.reload.post_versions.order(:version_no)
+        expect(affected_versions.pluck(:version_no)).to eq([1, 2])
+
+        latest = affected_versions.last
+        expect(latest.event_type).to eq('update')
+        expect(latest.created_by_user).to be_nil
+        expect(latest.tags).to eq(snapshot_tags(post_record.reload))
+
+        expect(unaffected_post.reload.post_versions.count).to eq(1)
+      end
+    end
+
+    context 'when the source tag has no active post_tags' do
+      let!(:another_post) do
+        Post.create!(url: 'https://example.com/posts/3', title: 'another post')
+      end
+
+      before do
+        create_post_version_for!(another_post)
+      end
+
+      it 'does not create any post_version' do
+        expect {
+          described_class.merge_tags!(target_tag, [source_tag])
+        }.not_to change(PostVersion, :count)
       end
     end
   end

backend/spec/models/version_record_spec.rb

@@ -0,0 +1,74 @@
+require 'rails_helper'
+
+RSpec.describe VersionRecord, type: :model do
+  let!(:tag) { create(:tag, name: 'version_record_tag') }
+  let!(:nico_tag) { create(:tag, :nico, name: 'nico:version_record_tag') }
+
+  it 'makes TagVersion read only after create' do
+    version = TagVersion.create!(
+      tag: tag,
+      version_no: 1,
+      event_type: 'create',
+      name: tag.name,
+      category: tag.category,
+      aliases: '',
+      parent_tag_ids: '',
+      created_at: Time.current,
+      created_by_user: nil
+    )
+
+    expect {
+      version.update!(name: 'changed')
+    }.to raise_error(ActiveRecord::ReadOnlyRecord)
+  end
+
+  it 'prevents TagVersion destroy' do
+    version = TagVersion.create!(
+      tag: tag,
+      version_no: 1,
+      event_type: 'create',
+      name: tag.name,
+      category: tag.category,
+      aliases: '',
+      parent_tag_ids: '',
+      created_at: Time.current,
+      created_by_user: nil
+    )
+
+    expect {
+      version.destroy!
+    }.to raise_error(ActiveRecord::ReadOnlyRecord)
+  end
+
+  it 'makes NicoTagVersion read only after create' do
+    version = NicoTagVersion.create!(
+      tag: nico_tag,
+      version_no: 1,
+      event_type: 'create',
+      name: nico_tag.name,
+      linked_tags: '',
+      created_at: Time.current,
+      created_by_user: nil
+    )
+
+    expect {
+      version.update!(name: 'nico:changed')
+    }.to raise_error(ActiveRecord::ReadOnlyRecord)
+  end
+
+  it 'prevents NicoTagVersion destroy' do
+    version = NicoTagVersion.create!(
+      tag: nico_tag,
+      version_no: 1,
+      event_type: 'create',
+      name: nico_tag.name,
+      linked_tags: '',
+      created_at: Time.current,
+      created_by_user: nil
+    )
+
+    expect {
+      version.destroy!
+    }.to raise_error(ActiveRecord::ReadOnlyRecord)
+  end
+end

backend/spec/requests/materials_spec.rb

@@ -0,0 +1,378 @@
+require 'rails_helper'
+
+RSpec.describe 'Materials API', type: :request do
+  let!(:member_user) { create(:user, :member) }
+  let!(:guest_user)  { create(:user) }
+
+  def dummy_upload(filename: 'dummy.png', type: 'image/png', body: 'dummy')
+    Rack::Test::UploadedFile.new(StringIO.new(body), type, original_filename: filename)
+  end
+
+  def response_materials
+    json.fetch('materials')
+  end
+
+  def build_material(tag:, user:, parent: nil, file: dummy_upload, url: nil)
+    Material.new(tag:, parent:, url:, created_by_user: user, updated_by_user: user).tap do |material|
+      material.file.attach(file) if file
+      material.save!
+    end
+  end
+
+  describe 'GET /materials' do
+    let!(:tag_a) { Tag.create!(tag_name: TagName.create!(name: 'material_index_a'), category: :material) }
+    let!(:tag_b) { Tag.create!(tag_name: TagName.create!(name: 'material_index_b'), category: :material) }
+
+    let!(:material_a) do
+      build_material(tag: tag_a, user: member_user, file: dummy_upload(filename: 'a.png'))
+    end
+
+    let!(:material_b) do
+      build_material(tag: tag_b, user: member_user, parent: material_a, file: dummy_upload(filename: 'b.png'))
+    end
+
+    before do
+      old_time = Time.zone.local(2026, 3, 29, 1, 0, 0)
+      new_time = Time.zone.local(2026, 3, 29, 2, 0, 0)
+
+      material_a.update_columns(created_at: old_time, updated_at: old_time)
+      material_b.update_columns(created_at: new_time, updated_at: new_time)
+    end
+
+    it 'returns materials with count and metadata' do
+      get '/materials'
+
+      expect(response).to have_http_status(:ok)
+      expect(json).to include('materials', 'count')
+      expect(response_materials).to be_an(Array)
+      expect(json['count']).to eq(2)
+
+      row = response_materials.find { |m| m['id'] == material_b.id }
+      expect(row).to be_present
+      expect(row['tag']).to include(
+        'id' => tag_b.id,
+        'name' => 'material_index_b',
+        'category' => 'material'
+      )
+      expect(row['created_by_user']).to include(
+        'id' => member_user.id,
+        'name' => member_user.name
+      )
+      expect(row['content_type']).to eq('image/png')
+    end
+
+    it 'filters materials by tag_id' do
+      get '/materials', params: { tag_id: material_a.tag_id }
+
+      expect(response).to have_http_status(:ok)
+      expect(json['count']).to eq(1)
+      expect(response_materials.map { |m| m['id'] }).to eq([material_a.id])
+    end
+
+    it 'filters materials by parent_id' do
+      get '/materials', params: { parent_id: material_a.id }
+
+      expect(response).to have_http_status(:ok)
+      expect(json['count']).to eq(1)
+      expect(response_materials.map { |m| m['id'] }).to eq([material_b.id])
+    end
+
+    it 'paginates and keeps total count' do
+      get '/materials', params: { page: 2, limit: 1 }
+
+      expect(response).to have_http_status(:ok)
+      expect(json['count']).to eq(2)
+      expect(response_materials.size).to eq(1)
+      expect(response_materials.first['id']).to eq(material_a.id)
+    end
+
+    it 'normalises invalid page and limit' do
+      get '/materials', params: { page: 0, limit: 0 }
+
+      expect(response).to have_http_status(:ok)
+      expect(json['count']).to eq(2)
+      expect(response_materials.size).to eq(1)
+      expect(response_materials.first['id']).to eq(material_b.id)
+    end
+  end
+
+  describe 'GET /materials/:id' do
+    let!(:tag) { Tag.create!(tag_name: TagName.create!(name: 'material_show'), category: :material) }
+    let!(:material) do
+      build_material(tag:, user: member_user, file: dummy_upload(filename: 'show.png'))
+    end
+
+    it 'returns a material with file, tag, and content_type' do
+      get "/materials/#{ material.id }"
+
+      expect(response).to have_http_status(:ok)
+      expect(json).to include(
+        'id' => material.id,
+        'content_type' => 'image/png'
+      )
+      expect(json['file']).to be_present
+      expect(json['tag']).to include(
+        'id' => tag.id,
+        'name' => 'material_show',
+        'category' => 'material'
+      )
+    end
+
+    it 'returns 404 when material does not exist' do
+      get '/materials/999999999'
+      expect(response).to have_http_status(:not_found)
+    end
+  end
+
+  describe 'POST /materials' do
+    context 'when not logged in' do
+      before { sign_out }
+
+      it 'returns 401' do
+        post '/materials', params: {
+          tag: 'material_create_unauthorized',
+          file: dummy_upload
+        }
+
+        expect(response).to have_http_status(:unauthorized)
+      end
+    end
+
+    context 'when logged in' do
+      before { sign_in_as(guest_user) }
+
+      it 'returns 400 when tag is blank' do
+        post '/materials', params: { tag: '   ', file: dummy_upload }
+
+        expect(response).to have_http_status(:bad_request)
+      end
+
+      it 'returns 400 when both file and url are blank' do
+        post '/materials', params: { tag: 'material_create_blank' }
+
+        expect(response).to have_http_status(:bad_request)
+      end
+
+      it 'creates a material with an attached file' do
+        expect do
+          post '/materials', params: {
+            tag: 'material_create_new',
+            file: dummy_upload(filename: 'created.png')
+          }
+        end.to change(Material, :count).by(1)
+          .and change(Tag, :count).by(1)
+          .and change(TagName, :count).by(1)
+
+        expect(response).to have_http_status(:created)
+
+        material = Material.order(:id).last
+        expect(material.tag.name).to eq('material_create_new')
+        expect(material.tag.category).to eq('material')
+        expect(material.created_by_user).to eq(guest_user)
+        expect(material.updated_by_user).to eq(guest_user)
+        expect(material.file.attached?).to be(true)
+
+        expect(json['id']).to eq(material.id)
+        expect(json.dig('tag', 'name')).to eq('material_create_new')
+        expect(json['content_type']).to eq('image/png')
+      end
+
+      it 'returns 422 when the existing tag is not material/character' do
+        general_tag_name = TagName.create!(name: 'material_create_general_tag')
+        Tag.create!(tag_name: general_tag_name, category: :general)
+
+        post '/materials', params: {
+          tag: 'material_create_general_tag',
+          file: dummy_upload
+        }
+
+        expect(response).to have_http_status(:unprocessable_entity)
+      end
+
+      it 'persists url-only material' do
+        expect do
+          post '/materials', params: {
+            tag: 'material_create_url_only',
+            url: 'https://example.com/material-source'
+          }
+        end.to change(Material, :count).by(1)
+
+        expect(response).to have_http_status(:created)
+
+        material = Material.order(:id).last
+        expect(material.tag.name).to eq('material_create_url_only')
+        expect(material.url).to eq('https://example.com/material-source')
+        expect(material.file.attached?).to be(false)
+      end
+
+      it 'returns the original url for url-only material' do
+        post '/materials', params: {
+          tag: 'material_create_url_only_response',
+          url: 'https://example.com/material-source'
+        }
+
+        expect(response).to have_http_status(:created)
+        expect(json['url']).to eq('https://example.com/material-source')
+      end
+    end
+  end
+
+  describe 'PUT /materials/:id' do
+    let!(:tag) { Tag.create!(tag_name: TagName.create!(name: 'material_update_old'), category: :material) }
+    let!(:material) do
+      build_material(tag:, user: member_user, file: dummy_upload(filename: 'old.png'))
+    end
+
+    context 'when not logged in' do
+      before { sign_out }
+
+      it 'returns 401' do
+        put "/materials/#{ material.id }", params: {
+          tag: 'material_update_new',
+          file: dummy_upload(filename: 'new.png')
+        }
+
+        expect(response).to have_http_status(:unauthorized)
+      end
+    end
+
+    context 'when logged in but not member' do
+      before { sign_in_as(guest_user) }
+
+      it 'returns 403' do
+        put "/materials/#{ material.id }", params: {
+          tag: 'material_update_new',
+          file: dummy_upload(filename: 'new.png')
+        }
+
+        expect(response).to have_http_status(:forbidden)
+      end
+    end
+
+    context 'when member' do
+      before { sign_in_as(member_user) }
+
+      it 'returns 404 when material does not exist' do
+        put '/materials/999999999', params: {
+          tag: 'material_update_missing',
+          file: dummy_upload
+        }
+
+        expect(response).to have_http_status(:not_found)
+      end
+
+      it 'returns 400 when tag is blank' do
+        put "/materials/#{ material.id }", params: {
+          tag: '   ',
+          file: dummy_upload
+        }
+
+        expect(response).to have_http_status(:bad_request)
+      end
+
+      it 'returns 400 when both file and url are blank' do
+        put "/materials/#{ material.id }", params: {
+          tag: 'material_update_no_payload'
+        }
+
+        expect(response).to have_http_status(:bad_request)
+      end
+
+      it 'updates tag, url, file, and updated_by_user' do
+        old_blob_id = material.file.blob.id
+
+        put "/materials/#{ material.id }", params: {
+          tag: 'material_update_new',
+          url: 'https://example.com/updated-source',
+          file: dummy_upload(filename: 'updated.jpg', type: 'image/jpeg')
+        }
+
+        expect(response).to have_http_status(:ok)
+
+        material.reload
+        expect(material.tag.name).to eq('material_update_new')
+        expect(material.tag.category).to eq('material')
+        expect(material.url).to eq('https://example.com/updated-source')
+        expect(material.updated_by_user).to eq(member_user)
+        expect(material.file.attached?).to be(true)
+        expect(material.file.blob.id).not_to eq(old_blob_id)
+        expect(material.file.blob.filename.to_s).to eq('updated.jpg')
+        expect(material.file.blob.content_type).to eq('image/jpeg')
+
+        expect(json['id']).to eq(material.id)
+        expect(json['file']).to be_present
+        expect(json['content_type']).to eq('image/jpeg')
+        expect(json.dig('tag', 'name')).to eq('material_update_new')
+      end
+
+      it 'purges the existing file when file is omitted and url is provided' do
+        old_blob_id = material.file.blob.id
+
+        put "/materials/#{ material.id }", params: {
+          tag: 'material_update_remove_file',
+          url: 'https://example.com/updated-source'
+        }
+
+        expect(response).to have_http_status(:ok)
+
+        material.reload
+        expect(material.tag.name).to eq('material_update_remove_file')
+        expect(material.url).to eq('https://example.com/updated-source')
+        expect(material.updated_by_user).to eq(member_user)
+        expect(material.file.attached?).to be(false)
+
+        expect(
+          ActiveStorage::Blob.where(id: old_blob_id).exists?
+        ).to be(false)
+
+        expect(json['id']).to eq(material.id)
+        expect(json['file']).to be_nil
+        expect(json['content_type']).to be_nil
+        expect(json.dig('tag', 'name')).to eq('material_update_remove_file')
+        expect(json['url']).to eq('https://example.com/updated-source')
+      end
+    end
+  end
+
+  describe 'DELETE /materials/:id' do
+    let!(:tag) { Tag.create!(tag_name: TagName.create!(name: 'material_destroy'), category: :material) }
+    let!(:material) do
+      build_material(tag:, user: member_user, file: dummy_upload(filename: 'destroy.png'))
+    end
+
+    context 'when not logged in' do
+      before { sign_out }
+
+      it 'returns 401' do
+        delete "/materials/#{ material.id }"
+        expect(response).to have_http_status(:unauthorized)
+      end
+    end
+
+    context 'when logged in but not member' do
+      before { sign_in_as(guest_user) }
+
+      it 'returns 403' do
+        delete "/materials/#{ material.id }"
+        expect(response).to have_http_status(:forbidden)
+      end
+    end
+
+    context 'when member' do
+      before { sign_in_as(member_user) }
+
+      it 'returns 404 when material does not exist' do
+        delete '/materials/999999999'
+        expect(response).to have_http_status(:not_found)
+      end
+
+      it 'discards the material and returns 204' do
+        delete "/materials/#{ material.id }"
+
+        expect(response).to have_http_status(:no_content)
+        expect(Material.find_by(id: material.id)).to be_nil
+        expect(Material.with_discarded.find(material.id)).to be_discarded
+      end
+    end
+  end
+end

backend/spec/requests/nico_tags_spec.rb

@@ -14,6 +14,7 @@ RSpec.describe 'NicoTags', type: :request do
 
   describe 'PATCH /tags/nico/:id' do
     let(:member) { create(:user, :member) }
+    let(:admin) { create(:user, :admin) }
     let(:nico_tag) { create(:tag, :nico) }
 
     it '401 when not logged in' do
@@ -34,5 +35,59 @@ RSpec.describe 'NicoTags', type: :request do
       patch "/tags/nico/#{non_nico.id}", params: { tags: 'a b' }
       expect(response).to have_http_status(:bad_request)
     end
+
+    it '200 and updates linked tags while recording tag versions' do
+      sign_in_as(admin)
+
+      nico_tag_name = TagName.create!(name: 'nico:nico_tags_spec_source')
+      nico_tag = Tag.create!(tag_name: nico_tag_name, category: :nico)
+
+      linked_a_name = TagName.create!(name: 'nico_linked_a')
+      linked_a = Tag.create!(tag_name: linked_a_name, category: :general)
+
+      linked_b_name = TagName.create!(name: 'nico_linked_b')
+      linked_b = Tag.create!(tag_name: linked_b_name, category: :general)
+
+      TagVersioning.ensure_snapshot!(nico_tag, created_by_user: admin)
+
+      expect {
+        patch "/tags/nico/#{nico_tag.id}", params: {
+          tags: "  #{linked_a.name}\n#{linked_b.name}  "
+        }
+      }.to change(TagVersion, :count).by(2)
+       .and change(NicoTagVersion, :count).by(1)
+
+      expect(response).to have_http_status(:ok)
+
+      names = json.map { |t| t['name'] }
+      expect(names).to match_array(['nico_linked_a', 'nico_linked_b'])
+
+      linked_versions = TagVersion.where(tag: [linked_a, linked_b]).order(:tag_id)
+      expect(linked_versions.map(&:event_type)).to eq(['create', 'create'])
+      expect(linked_versions.map(&:created_by_user_id)).to all(eq(admin.id))
+
+      versions = nico_tag.reload.nico_tag_versions.order(:version_no)
+      expect(versions.map(&:event_type)).to eq(['create', 'update'])
+      expect(versions.last.linked_tags.split).to match_array([
+        'nico_linked_a',
+        'nico_linked_b'
+      ])
+      expect(versions.last.created_by_user_id).to eq(admin.id)
+    end
+
+    it '400 when linked tag normalises to nico tag' do
+      sign_in_as(member)
+
+      other_nico = create(:tag, :nico, name: 'nico:linked_ng')
+      TagName.create!(name: 'linked_ng_alias', canonical: other_nico.tag_name)
+
+      TagVersioning.ensure_snapshot!(nico_tag, created_by_user: member)
+
+      expect {
+        patch "/tags/nico/#{nico_tag.id}", params: { tags: 'linked_ng_alias' }
+      }.not_to change(NicoTagVersion, :count)
+
+      expect(response).to have_http_status(:bad_request)
+    end
   end
 end

backend/spec/requests/posts_spec.rb

@@ -1,8 +1,8 @@
-include ActiveSupport::Testing::TimeHelpers
-
 require 'rails_helper'
 require 'set'
 
+include ActiveSupport::Testing::TimeHelpers
+
 RSpec.describe 'Posts API', type: :request do
   # create / update で thumbnail.attach は走るが、
   # resized_thumbnail! が MiniMagick 依存でコケやすいので request spec ではスタブしとくのが無難。
@@ -16,7 +16,7 @@ RSpec.describe 'Posts API', type: :request do
   end
 
   let!(:tag_name) { TagName.create!(name: 'spec_tag') }
-  let!(:tag)      { Tag.create!(tag_name: tag_name, category: 'general') }
+  let!(:tag)      { Tag.create!(tag_name: tag_name, category: :general) }
 
   let!(:post_record) do
     Post.create!(title: 'spec post', url: 'https://example.com/spec').tap do |p|
@@ -28,9 +28,9 @@ RSpec.describe 'Posts API', type: :request do
     let!(:user) { create_member_user! }
 
     let!(:tag_name) { TagName.create!(name: "spec_tag") }
-    let!(:tag) { Tag.create!(tag_name:, category: "general") }
+    let!(:tag) { Tag.create!(tag_name:, category: :general) }
     let!(:tag_name2) { TagName.create!(name: 'unko') }
-    let!(:tag2) { Tag.create!(tag_name: tag_name2, category: 'deerjikist') }
+    let!(:tag2) { Tag.create!(tag_name: tag_name2, category: :deerjikist) }
     let!(:alias_tag_name) { TagName.create!(name: 'manko', canonical: tag_name) }
 
     let!(:hit_post) do
@@ -116,6 +116,123 @@ RSpec.describe 'Posts API', type: :request do
       end
     end
 
+    context 'when tags contain not:' do
+      let!(:foo_tag_name) { TagName.create!(name: 'not_spec_foo') }
+      let!(:foo_tag) { Tag.create!(tag_name: foo_tag_name, category: :general) }
+
+      let!(:bar_tag_name) { TagName.create!(name: 'not_spec_bar') }
+      let!(:bar_tag) { Tag.create!(tag_name: bar_tag_name, category: :general) }
+
+      let!(:baz_tag_name) { TagName.create!(name: 'not_spec_baz') }
+      let!(:baz_tag) { Tag.create!(tag_name: baz_tag_name, category: :general) }
+
+      let!(:foo_alias_tag_name) do
+        TagName.create!(name: 'not_spec_foo_alias', canonical: foo_tag_name)
+      end
+
+      let!(:foo_only_post) do
+        Post.create!(uploaded_user: user, title: 'foo only',
+                     url: 'https://example.com/not-spec-foo').tap do |p|
+          PostTag.create!(post: p, tag: foo_tag)
+        end
+      end
+
+      let!(:bar_only_post) do
+        Post.create!(uploaded_user: user, title: 'bar only',
+                     url: 'https://example.com/not-spec-bar').tap do |p|
+          PostTag.create!(post: p, tag: bar_tag)
+        end
+      end
+
+      let!(:baz_only_post) do
+        Post.create!(uploaded_user: user, title: 'baz only',
+                     url: 'https://example.com/not-spec-baz').tap do |p|
+          PostTag.create!(post: p, tag: baz_tag)
+        end
+      end
+
+      let!(:foo_bar_post) do
+        Post.create!(uploaded_user: user, title: 'foo bar',
+                     url: 'https://example.com/not-spec-foo-bar').tap do |p|
+          PostTag.create!(post: p, tag: foo_tag)
+          PostTag.create!(post: p, tag: bar_tag)
+        end
+      end
+
+      let!(:foo_baz_post) do
+        Post.create!(uploaded_user: user, title: 'foo baz',
+                     url: 'https://example.com/not-spec-foo-baz').tap do |p|
+          PostTag.create!(post: p, tag: foo_tag)
+          PostTag.create!(post: p, tag: baz_tag)
+        end
+      end
+
+      let(:controlled_ids) do
+        [foo_only_post.id, bar_only_post.id, baz_only_post.id,
+         foo_bar_post.id, foo_baz_post.id]
+      end
+
+      it 'supports not search' do
+        get '/posts', params: { tags: 'not:not_spec_foo' }
+
+        expect(response).to have_http_status(:ok)
+
+        expect(json.fetch('posts').map { |p| p['id'] } & controlled_ids).to match_array(
+          [bar_only_post.id, baz_only_post.id]
+        )
+      end
+
+      it 'supports alias in not search' do
+        get '/posts', params: { tags: 'not:not_spec_foo_alias' }
+
+        expect(response).to have_http_status(:ok)
+
+        expect(json.fetch('posts').map { |p| p['id'] } & controlled_ids).to match_array(
+          [bar_only_post.id, baz_only_post.id]
+        )
+      end
+
+      it 'treats multiple not terms as AND when match is omitted' do
+        get '/posts', params: { tags: 'not:not_spec_foo not:not_spec_bar' }
+
+        expect(response).to have_http_status(:ok)
+
+        expect(json.fetch('posts').map { |p| p['id'] } & controlled_ids).to match_array(
+          [baz_only_post.id]
+        )
+      end
+
+      it 'treats multiple not terms as OR when match=any' do
+        get '/posts', params: { tags: 'not:not_spec_foo not:not_spec_bar', match: 'any' }
+
+        expect(response).to have_http_status(:ok)
+
+        expect(json.fetch('posts').map { |p| p['id'] } & controlled_ids).to match_array(
+          [foo_only_post.id, bar_only_post.id, baz_only_post.id, foo_baz_post.id]
+        )
+      end
+
+      it 'supports mixed positive and negative search with AND' do
+        get '/posts', params: { tags: 'not_spec_foo not:not_spec_bar' }
+
+        expect(response).to have_http_status(:ok)
+
+        expect(json.fetch('posts').map { |p| p['id'] } & controlled_ids).to match_array(
+          [foo_only_post.id, foo_baz_post.id]
+        )
+      end
+
+      it 'supports mixed positive and negative search with OR when match=any' do
+        get '/posts', params: { tags: 'not_spec_foo not:not_spec_bar', match: 'any' }
+
+        expect(response).to have_http_status(:ok)
+
+        expect(json.fetch('posts').map { |p| p['id'] } & controlled_ids).to match_array(
+          [foo_only_post.id, baz_only_post.id, foo_bar_post.id, foo_baz_post.id]
+        )
+      end
+    end
+
     context 'when url is provided' do
       let!(:url_hit_post) do
         Post.create!(uploaded_user: user, title: 'url hit',
@@ -408,8 +525,9 @@ RSpec.describe 'Posts API', type: :request do
 
     context "when nico tag already exists in tags" do
       before do
-        Tag.find_or_create_by!(tag_name: TagName.find_or_create_by!(name: 'nico:nico_tag'),
-                               category: 'nico')
+        Tag.find_undiscard_or_create_by!(
+            tag_name: TagName.find_undiscard_or_create_by!(name: 'nico:nico_tag'),
+            category: :nico)
       end
 
       it 'return 400' do
@@ -475,7 +593,7 @@ RSpec.describe 'Posts API', type: :request do
 
       # 追加で別タグも作って、更新時に入れ替わることを見る
       tn2 = TagName.create!(name: 'spec_tag_2')
-      Tag.create!(tag_name: tn2, category: 'general')
+      Tag.create!(tag_name: tn2, category: :general)
 
       put "/posts/#{post_record.id}", params: {
         title: 'updated title',
@@ -493,8 +611,9 @@ RSpec.describe 'Posts API', type: :request do
 
     context "when nico tag already exists in tags" do
       before do
-        Tag.find_or_create_by!(tag_name: TagName.find_or_create_by!(name: 'nico:nico_tag'),
-                               category: 'nico')
+        Tag.find_undiscard_or_create_by!(
+            tag_name: TagName.find_undiscard_or_create_by!(name: 'nico:nico_tag'),
+            category: :nico)
       end
 
       it 'return 400' do
@@ -531,7 +650,7 @@ RSpec.describe 'Posts API', type: :request do
     it 'returns add/remove events (history) for a post' do
       # add
       tn2 = TagName.create!(name: 'spec_tag2')
-      tag2 = Tag.create!(tag_name: tn2, category: 'general')
+      tag2 = Tag.create!(tag_name: tn2, category: :general)
       pt = PostTag.create!(post: post_record, tag: tag2, created_user: member)
 
       # remove (discard)
@@ -548,6 +667,305 @@ RSpec.describe 'Posts API', type: :request do
       expect(types).to include('add')
       expect(types).to include('remove')
     end
+
+    it 'filters history by tag' do
+      tn2 = TagName.create!(name: 'history_tag_hit')
+      tag2 = Tag.create!(tag_name: tn2, category: :general)
+
+      tn3 = TagName.create!(name: 'history_tag_miss')
+      tag3 = Tag.create!(tag_name: tn3, category: :general)
+
+      other_post = Post.create!(
+        title: 'other post',
+        url: 'https://example.com/history-other'
+      )
+
+      # hit: add
+      PostTag.create!(post: post_record, tag: tag2, created_user: member)
+
+      # hit: add + remove
+      pt2 = PostTag.create!(post: other_post, tag: tag2, created_user: member)
+      pt2.discard_by!(member)
+
+      # miss: add + remove
+      pt3 = PostTag.create!(post: post_record, tag: tag3, created_user: member)
+      pt3.discard_by!(member)
+
+      get '/posts/changes', params: { tag: tag2.id }
+
+      expect(response).to have_http_status(:ok)
+      expect(json).to include('changes', 'count')
+      expect(json['count']).to eq(3)
+
+      changes = json.fetch('changes')
+
+      expect(changes.map { |e| e.dig('tag', 'id') }.uniq).to eq([tag2.id])
+      expect(changes.map { |e| e['change_type'] }).to match_array(%w[add add remove])
+      expect(changes.map { |e| e.dig('post', 'id') }).to match_array([
+        post_record.id,
+        other_post.id,
+        other_post.id
+      ])
+    end
+
+    it 'filters history by post and tag together' do
+      tn2 = TagName.create!(name: 'history_tag_combo_hit')
+      tag2 = Tag.create!(tag_name: tn2, category: :general)
+
+      tn3 = TagName.create!(name: 'history_tag_combo_miss')
+      tag3 = Tag.create!(tag_name: tn3, category: :general)
+
+      other_post = Post.create!(
+        title: 'other combo post',
+        url: 'https://example.com/history-combo-other'
+      )
+
+      # hit
+      PostTag.create!(post: post_record, tag: tag2, created_user: member)
+
+      # miss by post
+      pt2 = PostTag.create!(post: other_post, tag: tag2, created_user: member)
+      pt2.discard_by!(member)
+
+      # miss by tag
+      pt3 = PostTag.create!(post: post_record, tag: tag3, created_user: member)
+      pt3.discard_by!(member)
+
+      get '/posts/changes', params: { id: post_record.id, tag: tag2.id }
+
+      expect(response).to have_http_status(:ok)
+      expect(json).to include('changes', 'count')
+      expect(json['count']).to eq(1)
+
+      changes = json.fetch('changes')
+      expect(changes.size).to eq(1)
+      expect(changes[0]['change_type']).to eq('add')
+      expect(changes[0].dig('post', 'id')).to eq(post_record.id)
+      expect(changes[0].dig('tag', 'id')).to eq(tag2.id)
+    end
+
+    it 'returns empty history when tag does not match' do
+      tn2 = TagName.create!(name: 'history_tag_no_hit')
+      tag2 = Tag.create!(tag_name: tn2, category: :general)
+
+      get '/posts/changes', params: { tag: tag2.id }
+
+      expect(response).to have_http_status(:ok)
+      expect(json.fetch('changes')).to eq([])
+      expect(json.fetch('count')).to eq(0)
+    end
+  end
+
+  describe 'GET /posts/versions' do
+    let(:member) { create(:user, :member, name: 'version member') }
+
+    let(:t_v1) { Time.zone.local(2020, 1, 1, 12, 0, 0) }
+    let(:t_v2) { Time.zone.local(2020, 1, 2, 12, 0, 0) }
+    let(:t_other) { Time.zone.local(2020, 1, 3, 12, 0, 0) }
+
+    let(:oc_from) { Time.zone.local(2019, 12, 31, 0, 0, 0) }
+    let(:oc_before) { Time.zone.local(2020, 1, 1, 0, 0, 0) }
+
+    let!(:tag_name2) { TagName.create!(name: 'spec_tag_2') }
+    let!(:tag2) { Tag.create!(tag_name: tag_name2, category: :general) }
+
+    def snapshot_tags(post)
+      post.snapshot_tag_names.join(' ')
+    end
+
+    def create_post_version!(post, version_no:, event_type:, created_by_user:, created_at:)
+      PostVersion.create!(
+        post: post,
+        version_no: version_no,
+        event_type: event_type,
+        title: post.title,
+        url: post.url,
+        thumbnail_base: post.thumbnail_base,
+        tags: snapshot_tags(post),
+        parent: post.parent,
+        original_created_from: post.original_created_from,
+        original_created_before: post.original_created_before,
+        created_at: created_at,
+        created_by_user: created_by_user
+      )
+    end
+
+    let!(:v1) do
+      travel_to(t_v1) do
+        create_post_version!(
+          post_record,
+          version_no: 1,
+          event_type: 'create',
+          created_by_user: member,
+          created_at: t_v1
+        )
+      end
+    end
+
+    let!(:v2) do
+      post_record.post_tags.kept.find_by!(tag: tag).discard_by!(member)
+      PostTag.create!(post: post_record, tag: tag2, created_user: member)
+      post_record.update!(
+        title: 'updated spec post',
+        original_created_from: oc_from,
+        original_created_before: oc_before
+      )
+
+      travel_to(t_v2) do
+        create_post_version!(
+          post_record.reload,
+          version_no: 2,
+          event_type: 'update',
+          created_by_user: member,
+          created_at: t_v2
+        )
+      end
+    end
+
+    let!(:other_post_version) do
+      other_post = Post.create!(
+        title: 'other versioned post',
+        url: 'https://example.com/other-versioned'
+      )
+      PostTag.create!(post: other_post, tag: tag)
+
+      travel_to(t_other) do
+        create_post_version!(
+          other_post,
+          version_no: 1,
+          event_type: 'create',
+          created_by_user: member,
+          created_at: t_other
+        )
+      end
+    end
+
+    it 'returns versions for the specified post in reverse chronological order' do
+      get '/posts/versions', params: { post: post_record.id }
+
+      expect(response).to have_http_status(:ok)
+      expect(json).to include('versions', 'count')
+      expect(json.fetch('count')).to eq(2)
+
+      versions = json.fetch('versions')
+      expect(versions.map { |v| v['post_id'] }.uniq).to eq([post_record.id])
+      expect(versions.map { |v| v['version_no'] }).to eq([2, 1])
+
+      latest = versions.first
+      expect(latest).to include(
+        'post_id' => post_record.id,
+        'version_no' => 2,
+        'event_type' => 'update',
+        'created_by_user' => {
+          'id' => member.id,
+          'name' => member.name
+        }
+      )
+
+      expect(latest.fetch('title')).to eq(
+        'current' => 'updated spec post',
+        'prev' => 'spec post'
+      )
+      expect(latest.fetch('url')).to eq(
+        'current' => 'https://example.com/spec',
+        'prev' => 'https://example.com/spec'
+      )
+      expect(latest.fetch('thumbnail')).to eq(
+        'current' => nil,
+        'prev' => nil
+      )
+      expect(latest.fetch('thumbnail_base')).to eq(
+        'current' => nil,
+        'prev' => nil
+      )
+      expect(latest.fetch('tags')).to include(
+        { 'name' => 'spec_tag_2', 'type' => 'added' },
+        { 'name' => 'spec_tag', 'type' => 'removed' }
+      )
+      expect(latest.fetch('original_created_from')).to eq(
+        'current' => oc_from.iso8601,
+        'prev' => nil
+      )
+      expect(latest.fetch('original_created_before')).to eq(
+        'current' => oc_before.iso8601,
+        'prev' => nil
+      )
+      expect(latest.fetch('created_at')).to eq(t_v2.iso8601)
+
+      first = versions.second
+      expect(first).to include(
+        'post_id' => post_record.id,
+        'version_no' => 1,
+        'event_type' => 'create',
+        'created_by_user' => {
+          'id' => member.id,
+          'name' => member.name
+        }
+      )
+      expect(first.fetch('title')).to eq(
+        'current' => 'spec post',
+        'prev' => nil
+      )
+      expect(first.fetch('tags')).to include(
+        { 'name' => 'spec_tag', 'type' => 'added' }
+      )
+      expect(first.fetch('created_at')).to eq(t_v1.iso8601)
+    end
+
+    it 'filters versions by tag when the current snapshot includes the tag' do
+      get '/posts/versions', params: { post: post_record.id, tag: tag2.id }
+
+      expect(response).to have_http_status(:ok)
+      expect(json.fetch('count')).to eq(1)
+
+      versions = json.fetch('versions')
+      expect(versions.size).to eq(1)
+      expect(versions[0]['post_id']).to eq(post_record.id)
+      expect(versions[0]['version_no']).to eq(2)
+      expect(versions[0]['tags']).to include(
+        { 'name' => 'spec_tag_2', 'type' => 'added' }
+      )
+    end
+
+    it 'filters versions by tag when the tag exists in either current or previous snapshot' do
+      get '/posts/versions', params: { post: post_record.id, tag: tag.id }
+
+      expect(response).to have_http_status(:ok)
+      expect(json.fetch('count')).to eq(2)
+
+      versions = json.fetch('versions')
+      expect(versions.map { |v| v['post_id'] }).to all(eq(post_record.id))
+      expect(versions.map { |v| v['version_no'] }).to eq([2, 1])
+
+      latest = versions[0]
+      first = versions[1]
+
+      expect(latest['tags']).to include(
+        { 'name' => 'spec_tag', 'type' => 'removed' }
+      )
+      expect(first['tags']).to include(
+        { 'name' => 'spec_tag', 'type' => 'added' }
+      )
+    end
+
+    it 'returns empty when tag does not exist' do
+      get '/posts/versions', params: { tag: 999_999_999 }
+
+      expect(response).to have_http_status(:ok)
+      expect(json.fetch('versions')).to eq([])
+      expect(json.fetch('count')).to eq(0)
+    end
+
+    it 'clamps page and limit to at least 1' do
+      get '/posts/versions', params: { post: post_record.id, page: 0, limit: 0 }
+
+      expect(response).to have_http_status(:ok)
+      expect(json.fetch('count')).to eq(2)
+
+      versions = json.fetch('versions')
+      expect(versions.size).to eq(1)
+      expect(versions[0]['version_no']).to eq(2)
+    end
   end
 
   describe 'POST /posts/:id/viewed' do
@@ -589,4 +1007,176 @@ RSpec.describe 'Posts API', type: :request do
       expect(user.reload.viewed?(post_record)).to be(false)
     end
   end
+
+  describe 'post versioning' do
+    let(:member) { create(:user, :member) }
+
+    def snapshot_tags(post)
+      post.snapshot_tag_names.join(' ')
+    end
+
+    def create_post_version_for!(post)
+      PostVersion.create!(
+        post: post,
+        version_no: 1,
+        event_type: 'create',
+        title: post.title,
+        url: post.url,
+        thumbnail_base: post.thumbnail_base,
+        tags: snapshot_tags(post),
+        parent: post.parent,
+        original_created_from: post.original_created_from,
+        original_created_before: post.original_created_before,
+        created_at: post.created_at,
+        created_by_user: post.uploaded_user
+      )
+    end
+
+    it 'creates version 1 on POST /posts' do
+      sign_in_as(member)
+
+      expect do
+        post '/posts', params: {
+          title: 'versioned post',
+          url: 'https://example.com/versioned-post',
+          tags: 'spec_tag',
+          thumbnail: dummy_upload
+        }
+      end.to change(PostVersion, :count).by(1)
+
+      expect(response).to have_http_status(:created)
+
+      created_post = Post.find(json.fetch('id'))
+      version = PostVersion.find_by!(post: created_post, version_no: 1)
+
+      expect(version.event_type).to eq('create')
+      expect(version.title).to eq('versioned post')
+      expect(version.url).to eq('https://example.com/versioned-post')
+      expect(version.created_by_user_id).to eq(member.id)
+      expect(version.tags).to eq(snapshot_tags(created_post))
+    end
+
+    it 'creates next version on PUT /posts/:id when snapshot changes' do
+      sign_in_as(member)
+      create_post_version_for!(post_record)
+
+      tag_name2 = TagName.create!(name: 'spec_tag_2')
+      Tag.create!(tag_name: tag_name2, category: :general)
+
+      expect do
+        put "/posts/#{post_record.id}", params: {
+          title: 'updated title',
+          tags: 'spec_tag_2'
+        }
+      end.to change(PostVersion, :count).by(1)
+
+      expect(response).to have_http_status(:ok)
+
+      version = post_record.reload.post_versions.order(:version_no).last
+      expect(version.version_no).to eq(2)
+      expect(version.event_type).to eq('update')
+      expect(version.title).to eq('updated title')
+      expect(version.created_by_user_id).to eq(member.id)
+      expect(version.tags).to eq(snapshot_tags(post_record.reload))
+    end
+
+    it 'does not create a new version on PUT /posts/:id when snapshot is unchanged' do
+      sign_in_as(member)
+
+      PostTag.create!(post: post_record, tag: Tag.no_deerjikist)
+      create_post_version_for!(post_record.reload)
+
+      expect {
+        put "/posts/#{post_record.id}", params: {
+          title: post_record.title,
+          tags: 'spec_tag'
+        }
+      }.not_to change(PostVersion, :count)
+      expect(response).to have_http_status(:ok)
+
+      version = post_record.reload.post_versions.order(:version_no).last
+      expect(version.version_no).to eq(1)
+      expect(version.event_type).to eq('create')
+      expect(version.tags).to eq(snapshot_tags(post_record))
+    end
+
+    it 'does not create a version when POST /posts is invalid' do
+      sign_in_as(member)
+
+      expect do
+        post '/posts', params: {
+          title: 'invalid post',
+          url: 'ぼざクリタグ広場',
+          tags: 'spec_tag',
+          thumbnail: dummy_upload
+        }
+      end.not_to change(PostVersion, :count)
+
+      expect(response).to have_http_status(:unprocessable_entity)
+    end
+
+    it 'does not create a version when PUT /posts/:id is invalid' do
+      sign_in_as(member)
+      create_post_version_for!(post_record)
+
+      expect do
+        put "/posts/#{post_record.id}", params: {
+          title: 'updated title',
+          tags: 'spec_tag',
+          original_created_from: Time.zone.local(2020, 1, 2, 0, 0, 0).iso8601,
+          original_created_before: Time.zone.local(2020, 1, 1, 0, 0, 0).iso8601
+        }
+      end.not_to change(PostVersion, :count)
+
+      expect(response).to have_http_status(:unprocessable_entity)
+    end
+  end
+
+  describe 'tag versioning from post write actions' do
+    let(:member) { create(:user, :member) }
+
+    it 'creates tag snapshot for normalised tags on POST /posts' do
+      sign_in_as(member)
+
+      expect {
+        post '/posts', params: {
+          title: 'tag versioned post',
+          url: 'https://example.com/tag-versioned-post',
+          tags: 'spec_tag',
+          thumbnail: dummy_upload
+        }
+      }.to change { tag.reload.tag_versions.count }.by(1)
+
+      expect(response).to have_http_status(:created)
+
+      version = tag.reload.tag_versions.order(:version_no).last
+      expect(version.version_no).to eq(1)
+      expect(version.event_type).to eq('create')
+      expect(version.name).to eq('spec_tag')
+      expect(version.category).to eq('general')
+      expect(version.created_by_user_id).to eq(member.id)
+    end
+
+    it 'creates tag snapshot for normalised tags on PUT /posts/:id' do
+      sign_in_as(member)
+
+      tag_name2 = TagName.create!(name: 'spec_tag_2')
+      tag2 = Tag.create!(tag_name: tag_name2, category: :general)
+
+      expect {
+        put "/posts/#{post_record.id}", params: {
+          title: 'updated title',
+          tags: 'spec_tag_2'
+        }
+      }.to change { tag2.reload.tag_versions.count }.by(1)
+
+      expect(response).to have_http_status(:ok)
+
+      version = tag2.reload.tag_versions.order(:version_no).last
+      expect(version.version_no).to eq(1)
+      expect(version.event_type).to eq('create')
+      expect(version.name).to eq('spec_tag_2')
+      expect(version.created_by_user_id).to eq(member.id)
+    end
+  end
 end

backend/spec/requests/tag_children_spec.rb

@@ -58,15 +58,47 @@ RSpec.describe "TagChildren", type: :request do
       end
     end
 
-    context "when Tag.find raises (invalid ids) it still returns 204" do
+    context "when Tag.find raises (invalid ids)" do
       before { stub_current_user(admin) }
 
       let(:parent_id) { -1 }
       let(:child_id) { -1 }
 
-      it "returns 204 (rescue nil)" do
+      it "returns 404" do
         do_request
-        expect(response).to have_http_status(:no_content)
+        expect(response).to have_http_status(:not_found)
+      end
+    end
+
+    context 'when parent is nico' do
+      before { stub_current_user(admin) }
+
+      let!(:parent) { create(:tag, :nico, name: 'nico:parent_ng') }
+      let(:parent_id) { parent.id }
+      let(:child_id) { child.id }
+
+      it 'returns 400 and does not create relation' do
+        expect {
+          do_request
+        }.not_to change(TagImplication, :count)
+
+        expect(response).to have_http_status(:bad_request)
+      end
+    end
+
+    context 'when child is nico' do
+      before { stub_current_user(admin) }
+
+      let!(:child) { create(:tag, :nico, name: 'nico:child_ng') }
+      let(:parent_id) { parent.id }
+      let(:child_id) { child.id }
+
+      it 'returns 400 and does not create relation' do
+        expect {
+          do_request
+        }.not_to change(TagImplication, :count)
+
+        expect(response).to have_http_status(:bad_request)
       end
     end
   end
@@ -116,17 +148,57 @@ RSpec.describe "TagChildren", type: :request do
 
         expect(response).to have_http_status(:no_content)
       end
+
+      it 'records create and update versions for child tag' do
+        expect {
+          do_request
+        }.to change(TagVersion, :count).by(2)
+
+        expect(response).to have_http_status(:no_content)
+
+        versions = child.reload.tag_versions.order(:version_no)
+        expect(versions.map(&:event_type)).to eq(['create', 'update'])
+        expect(versions.first.parent_tag_ids.split).to include(parent.id.to_s)
+        expect(versions.second.parent_tag_ids).to eq('')
+        expect(versions.second.created_by_user_id).to eq(admin.id)
+      end
     end
 
-    context "when Tag.find raises (invalid ids) it still returns 204" do
+    context "when Tag.find raises (invalid ids)" do
       before { stub_current_user(admin) }
 
       let(:parent_id) { -1 }
       let(:child_id) { -1 }
 
-      it "returns 204 (rescue nil)" do
+      it "returns 404" do
         do_request
-        expect(response).to have_http_status(:no_content)
+        expect(response).to have_http_status(:not_found)
+      end
+    end
+
+    context 'when parent is nico' do
+      before { stub_current_user(admin) }
+
+      let!(:parent) { create(:tag, :nico, name: 'nico:parent_ng_delete') }
+      let(:parent_id) { parent.id }
+      let(:child_id) { child.id }
+
+      it 'returns 400' do
+        do_request
+        expect(response).to have_http_status(:bad_request)
+      end
+    end
+
+    context 'when child is nico' do
+      before { stub_current_user(admin) }
+
+      let!(:child) { create(:tag, :nico, name: 'nico:child_ng_delete') }
+      let(:parent_id) { parent.id }
+      let(:child_id) { child.id }
+
+      it 'returns 400' do
+        do_request
+        expect(response).to have_http_status(:bad_request)
       end
     end
   end

backend/spec/requests/tag_versions_spec.rb

@@ -0,0 +1,243 @@
+require 'rails_helper'
+
+RSpec.describe 'TagVersions API', type: :request do
+  let(:member) { create(:user, :member, name: 'version member') }
+
+  let!(:tag) { create(:tag, name: 'tag_versions_target', category: :general) }
+  let!(:other_tag) { create(:tag, name: 'tag_versions_other', category: :general) }
+
+  let!(:parent_shared) { create(:tag, name: 'parent_shared', category: :general) }
+  let!(:parent_old) { create(:tag, name: 'parent_old', category: :general) }
+  let!(:parent_new) { create(:tag, name: 'parent_new', category: :general) }
+  let!(:other_parent) { create(:tag, name: 'other_parent', category: :general) }
+
+  let(:t_v1) { Time.zone.local(2020, 1, 1, 12, 0, 0) }
+  let(:t_v2) { Time.zone.local(2020, 1, 2, 12, 0, 0) }
+  let(:t_other) { Time.zone.local(2020, 1, 3, 12, 0, 0) }
+
+  def create_tag_version!(
+    tag:,
+    version_no:,
+    event_type:,
+    name:,
+    category:,
+    aliases: [],
+    parent_tags: [],
+    created_by_user:,
+    created_at:
+  )
+    TagVersion.create!(
+      tag: tag,
+      version_no: version_no,
+      event_type: event_type,
+      name: name,
+      category: category,
+      aliases: Array(aliases).join(' '),
+      parent_tag_ids: Array(parent_tags).map(&:id).join(' '),
+      created_by_user: created_by_user,
+      created_at: created_at
+    )
+  end
+
+  let!(:v1) do
+    create_tag_version!(
+      tag: tag,
+      version_no: 1,
+      event_type: 'create',
+      name: 'old_tag_name',
+      category: 'general',
+      aliases: ['alias_shared', 'alias_old'],
+      parent_tags: [parent_shared, parent_old],
+      created_by_user: member,
+      created_at: t_v1
+    )
+  end
+
+  let!(:v2) do
+    create_tag_version!(
+      tag: tag,
+      version_no: 2,
+      event_type: 'update',
+      name: 'new_tag_name',
+      category: 'meme',
+      aliases: ['alias_shared', 'alias_new'],
+      parent_tags: [parent_shared, parent_new],
+      created_by_user: member,
+      created_at: t_v2
+    )
+  end
+
+  let!(:other_v1) do
+    create_tag_version!(
+      tag: other_tag,
+      version_no: 1,
+      event_type: 'create',
+      name: 'other_tag_name',
+      category: 'general',
+      aliases: ['other_alias'],
+      parent_tags: [other_parent],
+      created_by_user: member,
+      created_at: t_other
+    )
+  end
+
+  describe 'GET /tags/versions' do
+    it 'returns all versions in reverse chronological order when id is omitted' do
+      get '/tags/versions'
+
+      expect(response).to have_http_status(:ok)
+      expect(json).to include('versions', 'count')
+      expect(json.fetch('count')).to eq(3)
+
+      versions = json.fetch('versions')
+
+      expect(versions.map { |v| [v['tag_id'], v['version_no']] }).to eq([
+        [other_tag.id, 1],
+        [tag.id, 2],
+        [tag.id, 1]
+      ])
+    end
+
+    it 'returns versions for the specified tag with diffs' do
+      get '/tags/versions', params: { id: tag.id }
+
+      expect(response).to have_http_status(:ok)
+      expect(json).to include('versions', 'count')
+      expect(json.fetch('count')).to eq(2)
+
+      versions = json.fetch('versions')
+      expect(versions.map { |v| v['tag_id'] }.uniq).to eq([tag.id])
+      expect(versions.map { |v| v['version_no'] }).to eq([2, 1])
+
+      latest = versions.first
+      expect(latest).to include(
+        'tag_id' => tag.id,
+        'version_no' => 2,
+        'event_type' => 'update',
+        'created_by_user' => {
+          'id' => member.id,
+          'name' => member.name
+        }
+      )
+
+      expect(latest.fetch('name')).to eq(
+        'current' => 'new_tag_name',
+        'prev' => 'old_tag_name'
+      )
+      expect(latest.fetch('category')).to eq(
+        'current' => 'meme',
+        'prev' => 'general'
+      )
+      expect(latest.fetch('aliases')).to include(
+        { 'name' => 'alias_shared', 'type' => 'context' },
+        { 'name' => 'alias_new', 'type' => 'added' },
+        { 'name' => 'alias_old', 'type' => 'removed' }
+      )
+      expect(latest.fetch('parent_tags')).to include(
+        a_hash_including(
+          'type' => 'context',
+          'tag' => a_hash_including(
+            'id' => parent_shared.id
+          )
+        ),
+        a_hash_including(
+          'type' => 'added',
+          'tag' => a_hash_including(
+            'id' => parent_new.id
+          )
+        ),
+        a_hash_including(
+          'type' => 'removed',
+          'tag' => a_hash_including(
+            'id' => parent_old.id
+          )
+        )
+      )
+      expect(latest.fetch('created_at')).to eq(t_v2.iso8601)
+
+      first = versions.second
+      expect(first).to include(
+        'tag_id' => tag.id,
+        'version_no' => 1,
+        'event_type' => 'create',
+        'created_by_user' => {
+          'id' => member.id,
+          'name' => member.name
+        }
+      )
+      expect(first.fetch('name')).to eq(
+        'current' => 'old_tag_name',
+        'prev' => nil
+      )
+      expect(first.fetch('category')).to eq(
+        'current' => 'general',
+        'prev' => nil
+      )
+      expect(first.fetch('aliases')).to include(
+        { 'name' => 'alias_shared', 'type' => 'added' },
+        { 'name' => 'alias_old', 'type' => 'added' }
+      )
+      expect(first.fetch('parent_tags')).to include(
+        a_hash_including(
+          'type' => 'added',
+          'tag' => a_hash_including(
+            'id' => parent_shared.id
+          )
+        ),
+        a_hash_including(
+          'type' => 'added',
+          'tag' => a_hash_including(
+            'id' => parent_old.id
+          )
+        )
+      )
+      expect(first.fetch('created_at')).to eq(t_v1.iso8601)
+    end
+
+    it 'returns empty when the specified tag has no versions' do
+      fresh_tag = create(:tag, name: 'no_versions_tag', category: :general)
+
+      get '/tags/versions', params: { id: fresh_tag.id }
+
+      expect(response).to have_http_status(:ok)
+      expect(json.fetch('versions')).to eq([])
+      expect(json.fetch('count')).to eq(0)
+    end
+
+    it 'clamps page and limit to at least 1' do
+      get '/tags/versions', params: { id: tag.id, page: 0, limit: 0 }
+
+      expect(response).to have_http_status(:ok)
+      expect(json.fetch('count')).to eq(2)
+
+      versions = json.fetch('versions')
+      expect(versions.size).to eq(1)
+      expect(versions.first['version_no']).to eq(2)
+    end
+
+    it 'does not create tag versions by wiki updates when tag has no versions yet' do
+      wiki_tag_name = TagName.create!(name: 'tag_versions_from_wiki')
+      wiki_tag = Tag.create!(tag_name: wiki_tag_name, category: :general)
+
+      wiki_page =
+          Wiki::Commit.create_content!(
+              tag_name: wiki_tag_name,
+              body: 'before',
+              created_by_user: member,
+              message: 'init')
+
+      Wiki::Commit.content!(
+          page: wiki_page,
+          body: 'after',
+          created_user: member,
+          message: 'edit',
+          base_revision_id: wiki_page.current_revision.id)
+
+      get '/tags/versions', params: { id: wiki_tag.id }
+
+      expect(response).to have_http_status(:ok)
+      expect(json.fetch('versions')).to eq([])
+      expect(json.fetch('count')).to eq(0)
+    end
+  end
+end

backend/spec/requests/tag_wiki_history_integrity_spec.rb

@@ -0,0 +1,160 @@
+require 'rails_helper'
+
+RSpec.describe 'Tag and wiki history integrity', type: :request do
+  let(:member_user) { create(:user, role: 'member') }
+
+  def stub_current_user user
+    allow_any_instance_of(ApplicationController).to receive(:current_user).and_return(user)
+  end
+
+  def create_tag! name:, category: :general
+    tag_name = TagName.create!(name:)
+    Tag.create!(tag_name:, category:)
+  end
+
+  def create_wiki_for_tag! tag:, body: 'wiki body', user: member_user
+    Wiki::Commit.create_content!(
+        tag_name: tag.tag_name,
+        body:,
+        created_by_user: user,
+        message: 'init')
+  end
+
+  before do
+    stub_current_user(member_user)
+  end
+
+  describe 'PATCH /tags/:id' do
+    it 'records wiki_version when tag name changes and tag has wiki' do
+      tag = create_tag!(name: 'patch_tag_wiki_before')
+      wiki_page = create_wiki_for_tag!(tag:, body: 'wiki body before')
+
+      expect {
+        patch "/tags/#{ tag.id }", params: {
+          name: 'patch_tag_wiki_after',
+        }
+      }
+          .to change(TagVersion, :count).by(2)
+          .and change(WikiVersion, :count).by(1)
+
+      expect(response).to have_http_status(:ok)
+
+      tag.reload
+      wiki_page.reload
+      version = wiki_page.wiki_versions.order(:version_no).last
+
+      expect(tag.name).to eq('patch_tag_wiki_after')
+      expect(wiki_page.title).to eq('patch_tag_wiki_after')
+
+      expect(version).to have_attributes(
+        event_type: 'update',
+        title: 'patch_tag_wiki_after',
+        body: 'wiki body before',
+        created_by_user_id: member_user.id
+      )
+    end
+
+    it 'does not record wiki_version when only category changes' do
+      tag = create_tag!(name: 'patch_tag_category_only')
+      wiki_page = create_wiki_for_tag!(tag:, body: 'wiki body before')
+
+      before_wiki_versions = wiki_page.wiki_versions.count
+
+      expect {
+        patch "/tags/#{ tag.id }", params: {
+          category: 'meme',
+        }
+      }
+          .to change(TagVersion, :count).by(2)
+
+      expect(response).to have_http_status(:ok)
+
+      tag.reload
+      wiki_page.reload
+
+      expect(tag.name).to eq('patch_tag_category_only')
+      expect(tag.category).to eq('meme')
+      expect(wiki_page.wiki_versions.count).to eq(before_wiki_versions)
+    end
+  end
+
+  describe 'PUT /tags/:id' do
+    it 'records wiki_version when tag name changes and tag has wiki' do
+      tag = create_tag!(name: 'put_tag_wiki_before')
+      wiki_page = create_wiki_for_tag!(tag:, body: 'wiki body before')
+
+      expect {
+        put "/tags/#{ tag.id }", params: {
+          name: 'put_tag_wiki_after',
+          category: 'general',
+          aliases: '',
+          parent_tags: '',
+        }
+      }
+          .to change(TagVersion, :count).by(2)
+          .and change(WikiVersion, :count).by(1)
+
+      expect(response).to have_http_status(:ok)
+
+      tag.reload
+      wiki_page.reload
+      version = wiki_page.wiki_versions.order(:version_no).last
+
+      expect(tag.name).to eq('put_tag_wiki_after')
+      expect(wiki_page.title).to eq('put_tag_wiki_after')
+
+      expect(version).to have_attributes(
+        event_type: 'update',
+        title: 'put_tag_wiki_after',
+        body: 'wiki body before',
+        created_by_user_id: member_user.id
+      )
+    end
+
+    it 'does not record wiki_version when only category changes' do
+      tag = create_tag!(name: 'put_tag_category_only')
+      wiki_page = create_wiki_for_tag!(tag:, body: 'wiki body before')
+
+      before_wiki_versions = wiki_page.wiki_versions.count
+
+      expect {
+        put "/tags/#{ tag.id }", params: {
+          name: 'put_tag_category_only',
+          category: 'meme',
+          aliases: '',
+          parent_tags: '',
+        }
+      }
+          .to change(TagVersion, :count).by(2)
+
+      expect(response).to have_http_status(:ok)
+
+      tag.reload
+      wiki_page.reload
+
+      expect(tag.name).to eq('put_tag_category_only')
+      expect(tag.category).to eq('meme')
+      expect(wiki_page.wiki_versions.count).to eq(before_wiki_versions)
+    end
+
+    it 'does not record wiki_version when only aliases change' do
+      tag = create_tag!(name: 'put_tag_alias_only')
+      wiki_page = create_wiki_for_tag!(tag:, body: 'wiki body before')
+
+      before_wiki_versions = wiki_page.wiki_versions.count
+
+      expect {
+        put "/tags/#{ tag.id }", params: {
+          name: 'put_tag_alias_only',
+          category: 'general',
+          aliases: 'put_tag_alias_only_alias',
+          parent_tags: '',
+        }
+      }
+          .to change(TagVersion, :count).by(2)
+
+      expect(response).to have_http_status(:ok)
+      expect(wiki_page.reload.wiki_versions.count).to eq(before_wiki_versions)
+    end
+  end
+end

backend/spec/requests/theatre_comments_spec.rb

@@ -0,0 +1,150 @@
+require 'rails_helper'
+
+RSpec.describe 'TheatreComments', type: :request do
+  def sign_in_as(user)
+    allow_any_instance_of(ApplicationController).to receive(:current_user).and_return(user)
+  end
+
+  describe 'GET /theatres/:theatre_id/comments' do
+    let(:theatre) { create(:theatre) }
+    let(:other_theatre) { create(:theatre) }
+    let(:alice) { create(:user, name: 'Alice') }
+    let(:bob) { create(:user, name: 'Bob') }
+
+    let!(:comment_3) do
+      create(
+        :theatre_comment,
+        theatre: theatre,
+        no: 3,
+        user: alice,
+        content: 'third comment'
+      )
+    end
+
+    let!(:comment_1) do
+      create(
+        :theatre_comment,
+        theatre: theatre,
+        no: 1,
+        user: alice,
+        content: 'first comment'
+      )
+    end
+
+    let!(:comment_2) do
+      create(
+        :theatre_comment,
+        theatre: theatre,
+        no: 2,
+        user: bob,
+        content: 'second comment'
+      )
+    end
+
+    let!(:other_comment) do
+      create(
+        :theatre_comment,
+        theatre: other_theatre,
+        no: 1,
+        user: bob,
+        content: 'other theatre comment'
+      )
+    end
+
+    it 'theatre_id で絞り込み、no_gt より大きいものを no 降順で返す' do
+      get "/theatres/#{theatre.id}/comments", params: { no_gt: 1 }
+
+      expect(response).to have_http_status(:ok)
+      expect(response.parsed_body.map { |row| row['no'] }).to eq([3, 2])
+      expect(response.parsed_body.map { |row| row['content'] }).to eq([
+        'third comment',
+        'second comment'
+      ])
+    end
+
+    it 'user は id と name だけを含む' do
+      get "/theatres/#{theatre.id}/comments", params: { no_gt: 1 }
+
+      expect(response).to have_http_status(:ok)
+
+      expect(response.parsed_body.first['user']).to eq({
+        'id' => alice.id,
+        'name' => 'Alice'
+      })
+      expect(response.parsed_body.first['user'].keys).to contain_exactly('id', 'name')
+    end
+
+    it 'no_gt が負数なら 0 として扱う' do
+      get "/theatres/#{theatre.id}/comments", params: { no_gt: -100 }
+
+      expect(response).to have_http_status(:ok)
+      expect(response.parsed_body.map { |row| row['no'] }).to eq([3, 2, 1])
+    end
+  end
+
+  describe 'POST /theatres/:theatre_id/comments' do
+    let(:user) { create(:user, name: 'Alice') }
+    let(:theatre) { create(:theatre, next_comment_no: 2) }
+
+    before do
+      create(
+        :theatre_comment,
+        theatre: theatre,
+        no: 1,
+        user: user,
+        content: 'existing comment'
+      )
+    end
+
+    it '未ログインなら 401 を返す' do
+      expect {
+        post "/theatres/#{theatre.id}/comments", params: { content: 'hello' }
+      }.not_to change(TheatreComment, :count)
+
+      expect(response).to have_http_status(:unauthorized)
+    end
+
+    it 'content が blank なら 422 を返す' do
+      sign_in_as(user)
+
+      expect {
+        post "/theatres/#{theatre.id}/comments", params: { content: '   ' }
+      }.not_to change(TheatreComment, :count)
+
+      expect(response).to have_http_status(:unprocessable_entity)
+    end
+
+    it 'theatre が存在しなければ 404 を返す' do
+      sign_in_as(user)
+
+      expect {
+        post '/theatres/999999/comments', params: { content: 'hello' }
+      }.not_to change(TheatreComment, :count)
+
+      expect(response).to have_http_status(:not_found)
+    end
+
+    it 'コメントを作成し、user を紐づけ、next_comment_no を進める' do
+      sign_in_as(user)
+
+      expect {
+        post "/theatres/#{theatre.id}/comments", params: { content: 'new comment' }
+      }.to change(TheatreComment, :count).by(1)
+
+      expect(response).to have_http_status(:created)
+
+      comment = TheatreComment.find_by!(theatre: theatre, no: 2)
+
+      expect(comment.user).to eq(user)
+      expect(comment.content).to eq('new comment')
+      expect(theatre.reload.next_comment_no).to eq(3)
+
+      expect(response.parsed_body.slice('theatre_id', 'no', 'user_id', 'content')).to eq({
+        'theatre_id' => theatre.id,
+        'no' => 2,
+        'user_id' => user.id,
+        'content' => 'new comment'
+      })
+    end
+  end
+end

backend/spec/requests/theatres_spec.rb

@@ -0,0 +1,307 @@
+require 'rails_helper'
+require 'active_support/testing/time_helpers'
+
+
+RSpec.describe 'Theatres API', type: :request do
+  include ActiveSupport::Testing::TimeHelpers
+
+  around do |example|
+    travel_to(Time.zone.parse('2026-03-18 21:00:00')) do
+      example.run
+    end
+  end
+
+  let(:member) { create(:user, :member, name: 'member user') }
+  let(:other_user) { create(:user, :member, name: 'other user') }
+
+  let!(:youtube_post) do
+    Post.create!(
+      title: 'youtube post',
+      url: 'https://www.youtube.com/watch?v=spec123'
+    )
+  end
+
+  let!(:other_post) do
+    Post.create!(
+      title: 'other post',
+      url: 'https://example.com/posts/1'
+    )
+  end
+
+  let!(:theatre) do
+    Theatre.create!(
+      name: 'spec theatre',
+      opens_at: Time.zone.parse('2026-03-18 20:00:00'),
+      kind: 0,
+      created_by_user: member
+    )
+  end
+
+  describe 'GET /theatres/:id' do
+    subject(:do_request) do
+      get "/theatres/#{theatre_id}"
+    end
+
+    context 'when theatre exists' do
+      let(:theatre_id) { theatre.id }
+
+      it 'returns theatre json' do
+        do_request
+
+        expect(response).to have_http_status(:ok)
+        expect(json).to include(
+          'id' => theatre.id,
+          'name' => 'spec theatre'
+        )
+        expect(json).to have_key('opens_at')
+        expect(json).to have_key('closes_at')
+        expect(json).to have_key('created_at')
+        expect(json).to have_key('updated_at')
+
+        expect(json['created_by_user']).to include(
+          'id' => member.id,
+          'name' => 'member user'
+        )
+      end
+    end
+
+    context 'when theatre does not exist' do
+      let(:theatre_id) { 999_999_999 }
+
+      it 'returns 404' do
+        do_request
+        expect(response).to have_http_status(:not_found)
+      end
+    end
+  end
+
+  describe 'PUT /theatres/:id/watching' do
+    subject(:do_request) do
+      put "/theatres/#{theatre_id}/watching"
+    end
+
+    let(:theatre_id) { theatre.id }
+
+    context 'when not logged in' do
+      it 'returns 401' do
+        sign_out
+        do_request
+        expect(response).to have_http_status(:unauthorized)
+      end
+    end
+
+    context 'when theatre does not exist' do
+      let(:theatre_id) { 999_999_999 }
+
+      it 'returns 404' do
+        sign_in_as(member)
+        do_request
+        expect(response).to have_http_status(:not_found)
+      end
+    end
+
+    context 'when theatre has no host yet' do
+      before do
+        sign_in_as(member)
+      end
+
+      it 'creates watching row, assigns current user as host, and returns current theatre info' do
+        expect { do_request }
+          .to change { TheatreWatchingUser.count }.by(1)
+
+        expect(response).to have_http_status(:ok)
+
+        theatre.reload
+        watch = TheatreWatchingUser.find_by!(theatre: theatre, user: member)
+
+        expect(theatre.host_user_id).to eq(member.id)
+        expect(watch.expires_at).to be_within(1.second).of(30.seconds.from_now)
+
+        expect(json).to include(
+          'host_flg' => true,
+          'post_id' => nil,
+          'post_started_at' => nil
+        )
+
+        expect(json.fetch('watching_users')).to contain_exactly(
+          {
+            'id' => member.id,
+            'name' => 'member user'
+          }
+        )
+      end
+    end
+
+    context 'when current user is already watching' do
+      let!(:watching_row) do
+        TheatreWatchingUser.create!(
+          theatre: theatre,
+          user: member,
+          expires_at: 5.seconds.from_now
+        )
+      end
+
+      before do
+        sign_in_as(member)
+      end
+
+      it 'refreshes expires_at without creating another row' do
+        expect { do_request }
+          .not_to change { TheatreWatchingUser.count }
+
+        expect(response).to have_http_status(:ok)
+
+        expect(watching_row.reload.expires_at)
+          .to be_within(1.second).of(30.seconds.from_now)
+      end
+    end
+
+    context 'when another active host exists' do
+      before do
+        TheatreWatchingUser.create!(
+          theatre: theatre,
+          user: other_user,
+          expires_at: 10.minutes.from_now
+        )
+        theatre.update!(host_user: other_user)
+        sign_in_as(member)
+      end
+
+      it 'does not steal host and returns host_flg false' do
+        expect { do_request }
+          .to change { TheatreWatchingUser.count }.by(1)
+
+        expect(response).to have_http_status(:ok)
+        expect(theatre.reload.host_user_id).to eq(other_user.id)
+
+        expect(json).to include(
+          'host_flg' => false,
+          'post_id' => nil,
+          'post_started_at' => nil
+        )
+
+        expect(json.fetch('watching_users')).to contain_exactly(
+          {
+            'id' => member.id,
+            'name' => 'member user'
+          },
+          {
+            'id' => other_user.id,
+            'name' => 'other user'
+          }
+        )
+      end
+    end
+
+    context 'when host is set but no longer actively watching' do
+      let(:started_at) { 2.minutes.ago }
+
+      before do
+        TheatreWatchingUser.create!(
+          theatre: theatre,
+          user: other_user,
+          expires_at: 1.second.ago
+        )
+        theatre.update!(
+          host_user: other_user,
+          current_post: youtube_post,
+          current_post_started_at: started_at
+        )
+        sign_in_as(member)
+      end
+
+      it 'reassigns host to current user and returns current post info' do
+        expect { do_request }
+          .to change { TheatreWatchingUser.count }.by(1)
+
+        expect(response).to have_http_status(:ok)
+
+        theatre.reload
+        expect(theatre.host_user_id).to eq(member.id)
+
+        expect(json['host_flg']).to eq(true)
+        expect(json['post_id']).to eq(youtube_post.id)
+        expect(Time.zone.parse(json['post_started_at']))
+          .to be_within(1.second).of(started_at)
+      end
+    end
+  end
+
+  describe 'PATCH /theatres/:id/next_post' do
+    subject(:do_request) do
+      patch "/theatres/#{theatre_id}/next_post"
+    end
+
+    let(:theatre_id) { theatre.id }
+
+    context 'when not logged in' do
+      it 'returns 401' do
+        sign_out
+        do_request
+        expect(response).to have_http_status(:unauthorized)
+      end
+    end
+
+    context 'when theatre does not exist' do
+      let(:theatre_id) { 999_999_999 }
+
+      it 'returns 404' do
+        sign_in_as(member)
+        do_request
+        expect(response).to have_http_status(:not_found)
+      end
+    end
+
+    context 'when logged in but not host' do
+      before do
+        theatre.update!(host_user: other_user)
+        sign_in_as(member)
+      end
+
+      it 'returns 403' do
+        do_request
+        expect(response).to have_http_status(:forbidden)
+      end
+    end
+
+    context 'when current user is host' do
+      before do
+        theatre.update!(host_user: member)
+        sign_in_as(member)
+      end
+
+      it 'sets current_post to an eligible post and updates current_post_started_at' do
+        expect { do_request }
+          .to change { theatre.reload.current_post_id }
+          .from(nil).to(youtube_post.id)
+
+        expect(response).to have_http_status(:no_content)
+        expect(theatre.reload.current_post_started_at)
+          .to be_within(1.second).of(Time.current)
+      end
+    end
+
+    context 'when current user is host and no eligible post exists' do
+      before do
+        youtube_post.destroy!
+        theatre.update!(
+          host_user: member,
+          current_post: other_post,
+          current_post_started_at: 1.hour.ago
+        )
+        sign_in_as(member)
+      end
+
+      it 'still returns 204 and clears current_post' do
+        do_request
+
+        expect(response).to have_http_status(:no_content)
+
+        theatre.reload
+        expect(theatre.current_post_id).to be_nil
+        expect(theatre.current_post_started_at)
+          .to be_within(1.second).of(Time.current)
+      end
+    end
+  end
+end

backend/spec/requests/users_spec.rb

@@ -1,11 +1,10 @@
 require "rails_helper"
 
-
 RSpec.describe "Users", type: :request do
   describe "POST /users" do
     it "creates guest user and returns code" do
       post "/users"
-      expect(response).to have_http_status(:ok)
+      expect(response).to have_http_status(:created)
       expect(json["code"]).to be_present
       expect(json["user"]["role"]).to eq("guest")
     end
@@ -38,7 +37,7 @@ RSpec.describe "Users", type: :request do
       sign_in_as(user)
       put "/users/#{user.id}", params: { name: "new-name" }
 
-      expect(response).to have_http_status(:created)
+      expect(response).to have_http_status(:ok)
       expect(json["id"]).to eq(user.id)
       expect(json["name"]).to eq("new-name")
 

backend/spec/requests/wiki_body_search_pending_spec.rb

@@ -0,0 +1,27 @@
+require 'rails_helper'
+
+RSpec.describe 'Wiki body search', type: :request do
+  let!(:user) { create_member_user! }
+
+  it 'searches wiki pages by body text' do
+    pending '#336 で対応予定'
+
+    Wiki::Commit.create_content!(
+        tag_name: TagName.create!(name: 'wiki_body_search_hit'),
+        body: 'unique body keyword for wiki search',
+        created_by_user: user,
+        message: 'init')
+
+    Wiki::Commit.create_content!(
+        tag_name: TagName.create!(name: 'wiki_body_search_miss'),
+        body: 'ordinary body',
+        created_by_user: user,
+        message: 'init')
+
+    get '/wiki/search', params: { body: 'unique body keyword' }
+
+    expect(response).to have_http_status(:ok)
+    expect(json.map { |page| page['title'] }).to include('wiki_body_search_hit')
+    expect(json.map { |page| page['title'] }).not_to include('wiki_body_search_miss')
+  end
+end

backend/spec/requests/wiki_conflict_spec.rb

@@ -0,0 +1,42 @@
+require 'rails_helper'
+
+RSpec.describe 'Wiki conflict handling', type: :request do
+  let!(:user) { create_member_user! }
+
+  def auth_headers user
+    { 'X-Transfer-Code' => user.inheritance_code }
+  end
+
+  it 'returns 409 when base_revision_id is stale' do
+    page =
+        Wiki::Commit.create_content!(
+            tag_name: TagName.create!(name: 'wiki_conflict_request'),
+            body: 'first',
+            created_by_user: user,
+            message: 'init')
+
+    stale_id = page.current_revision.id
+
+    Wiki::Commit.content!(
+        page:,
+        body: 'second',
+        created_user: user,
+        message: 'other edit',
+        base_revision_id: stale_id)
+
+    put "/wiki/#{ page.id }",
+        params: {
+          title: 'wiki_conflict_request',
+          body: 'third',
+          message: 'stale',
+          base_revision_id: stale_id,
+        },
+        headers: auth_headers(user)
+
+    expect(response).to have_http_status(:conflict)
+
+    page.reload
+    expect(page.body).to eq('second')
+    expect(page.current_revision.message).to eq('other edit')
+  end
+end

backend/spec/requests/wiki_history_integrity_spec.rb

@@ -0,0 +1,196 @@
+require 'cgi'
+require 'rails_helper'
+
+RSpec.describe 'Wiki history integrity', type: :request do
+  let!(:user) { create_member_user! }
+
+  def auth_headers user
+    { 'X-Transfer-Code' => user.inheritance_code }
+  end
+
+  def create_wiki_page title:, body: 'body', message: 'init', user: self.user
+    Wiki::Commit.create_content!(
+        tag_name: TagName.create!(name: title),
+        body:,
+        created_by_user: user,
+        message:)
+  end
+
+  describe 'POST /wiki' do
+    it 'creates wiki_page, wiki_revision, and wiki_version atomically' do
+      expect {
+        post '/wiki',
+             params: {
+               title: 'wiki_history_create_atomic',
+               body: "a\nb\nc",
+               message: 'initial commit',
+             },
+             headers: auth_headers(user)
+      }
+          .to change(WikiPage, :count).by(1)
+          .and change(WikiRevision, :count).by(1)
+          .and change(WikiVersion, :count).by(1)
+
+      expect(response).to have_http_status(:created)
+
+      page = WikiPage.find(json.fetch('id'))
+      revision = page.current_revision
+      version = page.wiki_versions.order(:version_no).last
+
+      expect(page.title).to eq('wiki_history_create_atomic')
+      expect(page.body).to eq("a\nb\nc")
+
+      expect(revision).to be_content
+      expect(revision.message).to eq('initial commit')
+      expect(revision.lines_count).to eq(3)
+
+      expect(version).to have_attributes(
+        version_no: 1,
+        event_type: 'create',
+        title: 'wiki_history_create_atomic',
+        body: "a\nb\nc",
+        reason: 'initial commit',
+        created_by_user_id: user.id
+      )
+    end
+
+    it 'returns 422 and creates nothing when normalised body is blank' do
+      expect {
+        post '/wiki',
+             params: {
+               title: 'wiki_history_blank_body',
+               body: "\r\n\r\n",
+               message: 'blank',
+             },
+             headers: auth_headers(user)
+      }
+          .not_to change(WikiPage, :count)
+
+      expect(response).to have_http_status(:unprocessable_entity)
+      expect(WikiPage.joins(:tag_name).where(tag_names: { name: 'wiki_history_blank_body' })).not_to exist
+    end
+
+    it 'returns 422 and creates no partial page when title already exists' do
+      create_wiki_page(title: 'wiki_history_duplicate_title', body: 'first')
+
+      expect {
+        post '/wiki',
+             params: {
+               title: 'wiki_history_duplicate_title',
+               body: 'second',
+               message: 'duplicate',
+             },
+             headers: auth_headers(user)
+      }
+          .not_to change(WikiPage, :count)
+
+      expect(response).to have_http_status(:unprocessable_entity)
+      expect(WikiPage.joins(:tag_name).where(tag_names: { name: 'wiki_history_duplicate_title' }).count).to eq(1)
+    end
+  end
+
+  describe 'PUT /wiki/:id' do
+    it 'updates body and records wiki_revision and wiki_version' do
+      page = create_wiki_page(title: 'wiki_history_update_body', body: 'before')
+      current_id = page.current_revision.id
+
+      expect {
+        put "/wiki/#{ page.id }",
+            params: {
+              title: 'wiki_history_update_body',
+              body: 'after',
+              message: 'edit body',
+              base_revision_id: current_id,
+            },
+            headers: auth_headers(user)
+      }
+          .to change(WikiRevision, :count).by(1)
+          .and change(WikiVersion, :count).by(1)
+
+      expect(response).to have_http_status(:ok)
+
+      page.reload
+      version = page.wiki_versions.order(:version_no).last
+
+      expect(page.title).to eq('wiki_history_update_body')
+      expect(page.body).to eq('after')
+
+      expect(version).to have_attributes(
+        event_type: 'update',
+        title: 'wiki_history_update_body',
+        body: 'after',
+        reason: 'edit body',
+        created_by_user_id: user.id
+      )
+    end
+
+    it 'renames title and records wiki_version with new title' do
+      page = create_wiki_page(title: 'wiki_history_rename_before', body: 'before')
+      current_id = page.current_revision.id
+
+      expect {
+        put "/wiki/#{ page.id }",
+            params: {
+              title: 'wiki_history_rename_after',
+              body: 'after',
+              message: 'rename',
+              base_revision_id: current_id,
+            },
+            headers: auth_headers(user)
+      }
+          .to change(WikiRevision, :count).by(1)
+          .and change(WikiVersion, :count).by(1)
+
+      expect(response).to have_http_status(:ok)
+
+      page.reload
+      version = page.wiki_versions.order(:version_no).last
+
+      expect(page.title).to eq('wiki_history_rename_after')
+      expect(page.body).to eq('after')
+
+      expect(version).to have_attributes(
+        event_type: 'update',
+        title: 'wiki_history_rename_after',
+        body: 'after',
+        reason: 'rename',
+        created_by_user_id: user.id
+      )
+    end
+
+    it 'does not change title, body, revision, or version on stale base_revision_id' do
+      page = create_wiki_page(title: 'wiki_history_conflict_page', body: 'first')
+      stale_id = page.current_revision.id
+
+      Wiki::Commit.content!(
+          page:,
+          body: 'second',
+          created_user: user,
+          message: 'other edit',
+          base_revision_id: stale_id)
+
+      page.reload
+      current_title = page.title
+      current_body = page.body
+      revision_count = page.wiki_revisions.count
+      version_count = page.wiki_versions.count
+
+      put "/wiki/#{ page.id }",
+          params: {
+            title: 'wiki_history_conflict_renamed',
+            body: 'third',
+            message: 'stale edit',
+            base_revision_id: stale_id,
+          },
+          headers: auth_headers(user)
+
+      expect(response).to have_http_status(:conflict)
+
+      page.reload
+      expect(page.title).to eq(current_title)
+      expect(page.body).to eq(current_body)
+      expect(page.wiki_revisions.count).to eq(revision_count)
+      expect(page.wiki_versions.count).to eq(version_count)
+    end
+  end
+end

backend/spec/requests/wiki_restore_pending_spec.rb

@@ -0,0 +1,37 @@
+require 'rails_helper'
+
+RSpec.describe 'Wiki restore', type: :request do
+  let!(:user) { create_member_user! }
+
+  def auth_headers user
+    { 'X-Transfer-Code' => user.inheritance_code }
+  end
+
+  it 'restores wiki page to previous version' do
+    pending '#337 で対応予定'
+
+    page =
+        Wiki::Commit.create_content!(
+            tag_name: TagName.create!(name: 'wiki_restore_page'),
+            body: 'v1',
+            created_by_user: user,
+            message: 'init')
+
+    v1 = page.wiki_versions.order(:version_no).last
+
+    Wiki::Commit.content!(
+        page:,
+        body: 'v2',
+        created_user: user,
+        message: 'edit',
+        base_revision_id: page.current_revision.id)
+
+    post "/wiki/#{ page.id }/restore",
+         params: { version_no: v1.version_no },
+         headers: auth_headers(user)
+
+    expect(response).to have_http_status(:ok)
+    expect(page.reload.body).to eq('v1')
+    expect(page.wiki_versions.order(:version_no).last.event_type).to eq('restore')
+  end
+end

backend/spec/requests/wiki_spec.rb

@@ -4,13 +4,19 @@ require 'securerandom'
 
 
 RSpec.describe 'Wiki API', type: :request do
+  def auth_headers(user)
+    { 'X-Transfer-Code' => user.inheritance_code }
+  end
+
   let!(:user) { create_member_user! }
 
   let!(:tn) { TagName.create!(name: 'spec_wiki_title') }
   let!(:page) do
-    WikiPage.create!(tag_name: tn, created_user: user, updated_user: user).tap do |p|
-      Wiki::Commit.content!(page: p, body: 'init', created_user: user, message: 'init')
-    end
+    Wiki::Commit.create_content!(
+        tag_name: tn,
+        body: 'init',
+        created_by_user: user,
+        message: 'init')
   end
 
   describe 'GET /wiki' do
@@ -37,6 +43,7 @@ RSpec.describe 'Wiki API', type: :request do
     context 'when wiki page exists' do
       it 'returns wiki page with title' do
         request
+
         expect(response).to have_http_status(:ok)
 
         expect(json).to include(
@@ -50,6 +57,7 @@ RSpec.describe 'Wiki API', type: :request do
 
       it 'returns 404' do
         request
+
         expect(response).to have_http_status(:not_found)
       end
     end
@@ -99,25 +107,34 @@ RSpec.describe 'Wiki API', type: :request do
         end
             .to change(WikiPage, :count).by(1)
             .and change(WikiRevision, :count).by(1)
+            .and change(WikiVersion, :count).by(1)
 
         expect(response).to have_http_status(:created)
 
         page_id = json.fetch('id')
         expect(json.fetch('title')).to eq('TestPage')
 
-        page = WikiPage.find(page_id)
-        rev = page.current_revision
+        created_page = WikiPage.find(page_id)
+        version = created_page.wiki_versions.order(:version_no).last
+
+        expect(version).to have_attributes(
+          version_no: 1,
+          event_type: 'create',
+          title: 'TestPage',
+          body: "a\nb\nc",
+          created_by_user_id: member.id
+        )
+
+        rev = created_page.current_revision
         expect(rev).to be_present
         expect(rev).to be_content
         expect(rev.message).to eq('init')
 
-        # body が復元できること
-        expect(page.body).to eq("a\nb\nc")
+        expect(created_page.body).to eq("a\nb\nc")
 
-        # 行数とリレーションの整合
         expect(rev.lines_count).to eq(3)
         expect(rev.wiki_revision_lines.order(:position).pluck(:position)).to eq([0, 1, 2])
-        expect(rev.wiki_lines.pluck(:body)).to match_array(%w[a b c])
+        expect(rev.wiki_lines.pluck(:body)).to match_array(['a', 'b', 'c'])
       end
 
       it 'reuses existing WikiLine rows by sha256' do
@@ -135,6 +152,41 @@ RSpec.describe 'Wiki API', type: :request do
         # "a" の WikiLine が増殖しない（1行のはず）
         expect(WikiLine.where(body: 'a').count).to eq(1)
       end
+
+      it 'deduplicates duplicated new lines before upsert' do
+        duplicated = 'duplicated_line_for_wiki_line_upsert_spec'
+
+        post endpoint,
+             params: { title: 'DuplicateNewLine', body: "#{ duplicated }\n#{ duplicated }" },
+             headers: auth_headers(member)
+
+        expect(response).to have_http_status(:created)
+
+        page = WikiPage.find(json.fetch('id'))
+        rev = page.current_revision
+
+        expect(rev.lines_count).to eq(2)
+        expect(WikiLine.where(body: duplicated).count).to eq(1)
+        expect(rev.wiki_revision_lines.count).to eq(2)
+        expect(rev.wiki_revision_lines.pluck(:wiki_line_id).uniq.size).to eq(1)
+      end
+
+      it 'normalises CRLF and strips trailing newlines' do
+        post endpoint,
+             params: { title: 'NormalisedBody', body: "a\r\nb\r\n\r\n", message: 'normalise' },
+             headers: auth_headers(member)
+
+        expect(response).to have_http_status(:created)
+
+        page = WikiPage.find(json.fetch('id'))
+        rev = page.current_revision
+        version = page.wiki_versions.order(:version_no).last
+
+        expect(page.body).to eq("a\nb")
+        expect(version.body).to eq("a\nb")
+        expect(rev.lines_count).to eq(2)
+        expect(rev.wiki_lines.order('wiki_revision_lines.position').map(&:body)).to eq(['a', 'b'])
+      end
     end
   end
 
@@ -146,17 +198,14 @@ RSpec.describe 'Wiki API', type: :request do
       { 'X-Transfer-Code' => user.inheritance_code }
     end
 
-    #let!(:page) { create(:wiki_page, title: 'TestPage') }
-    let!(:page) do
-      build(:wiki_page, title: 'TestPage').tap do |p|
-        puts p.errors.full_messages unless p.valid?
-        p.save!
-      end
-    end
+    let!(:test_tag_name) { TagName.create!(name: 'TestPage') }
 
-    before do
-      # 初期版を 1 つ作っておく（更新が“2版目”になるように）
-      Wiki::Commit.content!(page: page, body: "a\nb", created_user: member, message: 'init')
+    let!(:page) do
+      Wiki::Commit.create_content!(
+          tag_name: test_tag_name,
+          body: "a\nb",
+          created_by_user: member,
+          message: 'init')
     end
 
     context 'when not logged in' do
@@ -182,14 +231,6 @@ RSpec.describe 'Wiki API', type: :request do
             headers: auth_headers(member)
         expect(response).to have_http_status(:unprocessable_entity)
       end
-
-      it 'returns 422 when title mismatched (if you forbid rename here)' do
-        put "/wiki/#{page.id}",
-            params: { title: 'OtherTitle', body: 'x' },
-            headers: auth_headers(member)
-        # 君の controller 例だと title 変更は 422 にしてた
-        expect(response).to have_http_status(:unprocessable_entity)
-      end
     end
 
     context 'when success' do
@@ -200,7 +241,18 @@ RSpec.describe 'Wiki API', type: :request do
           put "/wiki/#{page.id}",
               params: { title: 'TestPage', body: "x\ny", message: 'edit', base_revision_id: current_id },
               headers: auth_headers(member)
-        end.to change(WikiRevision, :count).by(1)
+        end
+            .to change(WikiRevision, :count).by(1)
+            .and change(WikiVersion, :count).by(1)
+
+        version = page.wiki_versions.order(:version_no).last
+
+        expect(version).to have_attributes(
+          event_type: 'update',
+          title: 'TestPage',
+          body: "x\ny",
+          created_by_user_id: member.id
+        )
 
         expect(response).to have_http_status(:ok)
 
@@ -211,25 +263,60 @@ RSpec.describe 'Wiki API', type: :request do
         expect(page.body).to eq("x\ny")
         expect(rev.base_revision_id).to eq(current_id)
       end
+
+      it 'wiki body だけを変更しても tag version は作成しない' do
+        linked_tag_name = TagName.create!(name: 'wiki_body_only_tag')
+        linked_tag = Tag.create!(tag_name: linked_tag_name, category: :general)
+
+        TagVersionRecorder.record!(
+            tag: linked_tag,
+            event_type: :create,
+            created_by_user: member)
+
+        linked_page =
+            Wiki::Commit.create_content!(
+                tag_name: linked_tag_name,
+                body: 'before',
+                created_by_user: member,
+                message: 'init')
+
+        current_id = linked_page.current_revision.id
+        before_count = linked_tag.reload.tag_versions.count
+
+        expect {
+          put "/wiki/#{ linked_page.id }",
+              params: {
+                title: 'wiki_body_only_tag',
+                body: 'after',
+                message: 'edit',
+                base_revision_id: current_id,
+              },
+              headers: auth_headers(member)
+        }
+            .to change(WikiRevision, :count).by(1)
+            .and change(WikiVersion, :count).by(1)
+
+        expect(response).to have_http_status(:ok)
+        expect(linked_tag.reload.tag_versions.count).to eq(before_count)
+      end
     end
 
-    # TODO: コンフリクト未実装のため，実装したらコメント外す．
-    # context 'when conflict' do
-    #   it 'returns 409 when base_revision_id mismatches' do
-    #     # 先に別ユーザ（同じ member でもOK）が 1 回更新して先頭を進める
-    #     Wiki::Commit.content!(page: page, body: "zzz", created_user: member, message: 'other edit')
-    #     page.reload
+    context 'when conflict' do
+      it 'returns 409 when base_revision_id mismatches' do
+        # 先に別ユーザ（同じ member でもOK）が 1 回更新して先頭を進める
+        Wiki::Commit.content!(page: page, body: "zzz", created_user: member, message: 'other edit')
+        page.reload
 
-    #     stale_id = page.wiki_revisions.order(:id).first.id # わざと古い id
-    #     put "/wiki/#{page.id}",
-    #         params: { title: 'TestPage', body: 'x', base_revision_id: stale_id },
-    #         headers: auth_headers(member)
+        stale_id = page.wiki_revisions.order(:id).first.id # わざと古い id
+        put "/wiki/#{page.id}",
+            params: { title: 'TestPage', body: 'x', base_revision_id: stale_id },
+            headers: auth_headers(member)
 
-    #     expect(response).to have_http_status(:conflict)
-    #     json = JSON.parse(response.body)
-    #     expect(json['error']).to eq('conflict')
-    #   end
-    # end
+        expect(response).to have_http_status(:conflict)
+        json = JSON.parse(response.body)
+        expect(json['error']).to eq('conflict')
+      end
+    end
 
     context 'when page not found' do
       it 'returns 404' do
@@ -261,14 +348,17 @@ RSpec.describe 'Wiki API', type: :request do
 
   describe 'GET /wiki/search' do
     before do
-      # 追加で検索ヒット用
-      TagName.create!(name: 'spec_wiki_title_2')
-      WikiPage.create!(tag_name: TagName.find_by!(name: 'spec_wiki_title_2'),
-                       created_user: user, updated_user: user)
+      Wiki::Commit.create_content!(
+          tag_name: TagName.create!(name: 'spec_wiki_title_2'),
+          body: 'search body 2',
+          created_by_user: user,
+          message: 'init')
 
-      TagName.create!(name: 'unrelated_title')
-      WikiPage.create!(tag_name: TagName.find_by!(name: 'unrelated_title'),
-                       created_user: user, updated_user: user)
+      Wiki::Commit.create_content!(
+          tag_name: TagName.create!(name: 'unrelated_title'),
+          body: 'unrelated body',
+          created_by_user: user,
+          message: 'init')
     end
 
     it 'returns up to 20 pages filtered by title like' do
@@ -278,7 +368,9 @@ RSpec.describe 'Wiki API', type: :request do
       expect(json).to be_an(Array)
 
       titles = json.map { |p| p['title'] }
-      expect(titles).to include('spec_wiki_title', 'spec_wiki_title_2')
+
+      expect(titles).to include('spec_wiki_title')
+      expect(titles).to include('spec_wiki_title_2')
       expect(titles).not_to include('unrelated_title')
     end
 
@@ -329,7 +421,12 @@ RSpec.describe 'Wiki API', type: :request do
     it 'returns empty array when page has no revisions and filtered by id' do
       # 別ページを作って revision 無し
       tn2 = TagName.create!(name: 'spec_no_rev')
-      p2 = WikiPage.create!(tag_name: tn2, created_user: user, updated_user: user)
+      # 異常データ: revision 無し WikiPage を直接作る
+      p2 = WikiPage.create!(
+          tag_name: tn2,
+          body: 'init',
+          created_user: user,
+          updated_user: user)
 
       get "/wiki/changes?id=#{p2.id}"
       expect(response).to have_http_status(:ok)
@@ -398,29 +495,68 @@ RSpec.describe 'Wiki API', type: :request do
       expect(json['older_revision_id']).to eq(rev_a.id)
       expect(json['newer_revision_id']).to eq(page.current_revision.id)
     end
-
-    it 'returns 422 when "to" is redirect revision' do
-      # redirect revision を作る
-      tn2 = TagName.create!(name: 'redirect_target')
-      target = WikiPage.create!(tag_name: tn2, created_user: user, updated_user: user)
-
-      Wiki::Commit.redirect!(page: page, redirect_page: target, created_user: user, message: 'redir')
-      redirect_rev = page.current_revision
-      expect(redirect_rev).to be_redirect
-
-      get "/wiki/#{page.id}/diff?from=#{rev_a.id}&to=#{redirect_rev.id}"
-      expect(response).to have_http_status(:unprocessable_entity)
   end
 
-    it 'returns 422 when "from" is redirect revision' do
-      tn2 = TagName.create!(name: 'redirect_target2')
-      target = WikiPage.create!(tag_name: tn2, created_user: user, updated_user: user)
+  describe 'Wiki::Commit.redirect!' do
+    it 'raises because redirect revisions are deprecated' do
+      target_tag_name = TagName.create!(name: 'redirect_deprecated_target')
+      target =
+          Wiki::Commit.create_content!(
+              tag_name: target_tag_name,
+              body: 'target',
+              created_by_user: user,
+              message: 'init')
 
-      Wiki::Commit.redirect!(page: page, redirect_page: target, created_user: user, message: 'redir2')
-      redirect_rev = page.current_revision
-
-      get "/wiki/#{page.id}/diff?from=#{redirect_rev.id}&to=#{rev_b.id}"
-      expect(response).to have_http_status(:unprocessable_entity)
+      expect {
+        Wiki::Commit.redirect!(
+          page: page,
+          redirect_page: target,
+          created_user: user,
+          message: 'redirect',
+          base_revision_id: page.current_revision.id
+        )
+      }.to raise_error(RuntimeError, '廃止しました．')
     end
   end
+
+  it 'wiki title を変更すると対応する tag の version を作成する' do
+    linked_tag_name = TagName.create!(name: 'wiki_linked_tag_for_version')
+    linked_tag = Tag.create!(tag_name: linked_tag_name, category: :general)
+
+    linked_page =
+        Wiki::Commit.create_content!(
+            tag_name: linked_tag_name,
+            body: 'before',
+            created_by_user: user,
+            message: 'init')
+
+    current_id = linked_page.current_revision.id
+
+    expect {
+      put "/wiki/#{ linked_page.id }",
+          params: {
+            title: 'wiki_linked_tag_for_version_renamed',
+            body: 'after',
+            message: 'rename',
+            base_revision_id: current_id,
+          },
+          headers: auth_headers(user)
+    }
+        .to change(WikiRevision, :count).by(1)
+        .and change(WikiVersion, :count).by(1)
+        .and change { linked_tag.reload.tag_versions.count }.by(2)
+
+    expect(response).to have_http_status(:ok)
+
+    linked_tag.reload
+    expect(linked_tag.name).to eq('wiki_linked_tag_for_version_renamed')
+
+    versions = linked_tag.tag_versions.order(:version_no)
+
+    expect(versions.first.event_type).to eq('create')
+    expect(versions.first.name).to eq('wiki_linked_tag_for_version')
+
+    expect(versions.second.event_type).to eq('update')
+    expect(versions.second.name).to eq('wiki_linked_tag_for_version_renamed')
+  end
 end

backend/spec/requests/wiki_title_collision_spec.rb

@@ -0,0 +1,62 @@
+require 'rails_helper'
+
+RSpec.describe 'Wiki title collision', type: :request do
+  let!(:user) { create_member_user! }
+
+  def auth_headers user
+    { 'X-Transfer-Code' => user.inheritance_code }
+  end
+
+  def create_wiki_page title:, body:
+    Wiki::Commit.create_content!(
+        tag_name: TagName.create!(name: title),
+        body:,
+        created_by_user: user,
+        message: 'init')
+  end
+
+  it 'returns 422 when renaming wiki title to existing title' do
+    source = create_wiki_page(title: 'wiki_collision_source', body: 'source body')
+    create_wiki_page(title: 'wiki_collision_target', body: 'target body')
+
+    source_revision_count = source.wiki_revisions.count
+    source_version_count = source.wiki_versions.count
+    old_title = source.title
+    old_body = source.body
+
+    put "/wiki/#{ source.id }",
+        params: {
+          title: 'wiki_collision_target',
+          body: 'new body',
+          message: 'rename collision',
+          base_revision_id: source.current_revision.id,
+        },
+        headers: auth_headers(user)
+
+    expect(response).to have_http_status(:unprocessable_entity)
+
+    source.reload
+
+    expect(source.title).to eq(old_title)
+    expect(source.body).to eq(old_body)
+    expect(source.wiki_revisions.count).to eq(source_revision_count)
+    expect(source.wiki_versions.count).to eq(source_version_count)
+  end
+
+  it 'returns 422 when creating wiki with existing title' do
+    create_wiki_page(title: 'wiki_collision_create', body: 'already exists')
+
+    expect {
+      post '/wiki',
+           params: {
+             title: 'wiki_collision_create',
+             body: 'new body',
+             message: 'duplicate create',
+           },
+           headers: auth_headers(user)
+    }
+        .not_to change(WikiPage, :count)
+
+    expect(response).to have_http_status(:unprocessable_entity)
+  end
+end

backend/spec/services/wiki/commit_integrity_spec.rb

@@ -0,0 +1,173 @@
+require 'digest'
+require 'rails_helper'
+
+RSpec.describe Wiki::Commit do
+  let(:user) { create_member_user! }
+
+  def create_page title:, body: 'initial body'
+    described_class.create_content!(
+        tag_name: TagName.create!(name: title),
+        body:,
+        created_by_user: user,
+        message: 'init')
+  end
+
+  describe '.create_content!' do
+    it 'creates page, revision, and version with normalised body' do
+      expect {
+        described_class.create_content!(
+            tag_name: TagName.create!(name: 'commit_integrity_create'),
+            body: "a\r\nb\r\n\r\n",
+            created_by_user: user,
+            message: 'init')
+      }
+          .to change(WikiPage, :count).by(1)
+          .and change(WikiRevision, :count).by(1)
+          .and change(WikiVersion, :count).by(1)
+
+      page = WikiPage.joins(:tag_name).find_by!(tag_names: { name: 'commit_integrity_create' })
+      revision = page.current_revision
+      version = page.wiki_versions.order(:version_no).last
+
+      expect(page.body).to eq("a\nb")
+      expect(revision.lines_count).to eq(2)
+      expect(version.body).to eq("a\nb")
+      expect(version.reason).to eq('init')
+    end
+
+    it 'rejects body that becomes blank after normalisation' do
+      tag_name = TagName.create!(name: 'commit_integrity_blank')
+
+      expect {
+        described_class.create_content!(
+            tag_name:,
+            body: "\r\n\r\n",
+            created_by_user: user,
+            message: 'blank')
+      }
+          .to raise_error(ActiveRecord::RecordInvalid)
+
+      expect(WikiPage.where(tag_name:)).not_to exist
+    end
+  end
+
+  describe '.content!' do
+    it 'updates page body, revision, and version' do
+      page = create_page(title: 'commit_integrity_update', body: 'before')
+      current_id = page.current_revision.id
+
+      expect {
+        described_class.content!(
+            page:,
+            body: 'after',
+            created_user: user,
+            message: 'edit',
+            base_revision_id: current_id)
+      }
+          .to change(WikiRevision, :count).by(1)
+          .and change(WikiVersion, :count).by(1)
+
+      page.reload
+      version = page.wiki_versions.order(:version_no).last
+
+      expect(page.body).to eq('after')
+      expect(version.body).to eq('after')
+      expect(version.reason).to eq('edit')
+    end
+
+    it 'does not record tag_version on body-only wiki update' do
+      tag_name = TagName.create!(name: 'commit_integrity_linked_tag')
+      tag = Tag.create!(tag_name:, category: :general)
+
+      page =
+          described_class.create_content!(
+              tag_name:,
+              body: 'before',
+              created_by_user: user,
+              message: 'init')
+
+      TagVersionRecorder.record!(
+          tag:,
+          event_type: :create,
+          created_by_user: user)
+
+      before_count = tag.reload.tag_versions.count
+
+      described_class.content!(
+          page:,
+          body: 'after',
+          created_user: user,
+          message: 'edit',
+          base_revision_id: page.current_revision.id)
+
+      expect(tag.reload.tag_versions.count).to eq(before_count)
+    end
+
+    it 'raises conflict and leaves page, revision, and version unchanged' do
+      page = create_page(title: 'commit_integrity_conflict', body: 'first')
+      stale_id = page.current_revision.id
+
+      described_class.content!(
+          page:,
+          body: 'second',
+          created_user: user,
+          message: 'second',
+          base_revision_id: stale_id)
+
+      page.reload
+      before_body = page.body
+      before_revision_count = page.wiki_revisions.count
+      before_version_count = page.wiki_versions.count
+
+      expect {
+        described_class.content!(
+            page:,
+            body: 'third',
+            created_user: user,
+            message: 'stale',
+            base_revision_id: stale_id)
+      }
+          .to raise_error(Wiki::Commit::Conflict)
+
+      page.reload
+      expect(page.body).to eq(before_body)
+      expect(page.wiki_revisions.count).to eq(before_revision_count)
+      expect(page.wiki_versions.count).to eq(before_version_count)
+    end
+
+    it 'deduplicates duplicated missing wiki lines' do
+      page = create_page(title: 'commit_integrity_dedup', body: 'before')
+      duplicated = 'commit_integrity_duplicate_line'
+
+      described_class.content!(
+          page:,
+          body: "#{ duplicated }\n#{ duplicated }",
+          created_user: user,
+          message: 'dedup',
+          base_revision_id: page.current_revision.id)
+
+      revision = page.reload.current_revision
+
+      expect(WikiLine.where(body: duplicated).count).to eq(1)
+      expect(revision.wiki_revision_lines.count).to eq(2)
+      expect(revision.wiki_revision_lines.pluck(:wiki_line_id).uniq.size).to eq(1)
+    end
+  end
+
+  describe '.redirect!' do
+    it 'raises because redirect revisions are deprecated' do
+      page = create_page(title: 'commit_integrity_redirect_source', body: 'source')
+      target = create_page(title: 'commit_integrity_redirect_target', body: 'target')
+
+      expect {
+        described_class.redirect!(
+            page:,
+            redirect_page: target,
+            created_user: user,
+            message: 'redirect',
+            base_revision_id: page.current_revision.id)
+      }
+          .to raise_error(RuntimeError, '廃止しました．')
+    end
+  end
+end

backend/spec/services/wiki/commit_spec.rb

@@ -0,0 +1,150 @@
+require 'rails_helper'
+
+RSpec.describe Wiki::Commit do
+  let(:user) { create_member_user! }
+
+  def create_page(title: 'commit_spec_page', body: 'initial body')
+    tag_name = TagName.create!(name: title)
+
+    Wiki::Commit.create_content!(
+        tag_name:,
+        body:,
+        created_by_user: user,
+        message: 'init')
+  end
+
+  describe '.content!' do
+    it 'stores normalised body in wiki_pages and wiki_versions' do
+      page = create_page(title: 'commit_normalised_page')
+
+      described_class.content!(
+        page:,
+        body: "a\r\nb\r\n\r\n",
+        created_user: user,
+        message: 'init'
+      )
+
+      page.reload
+      version = page.wiki_versions.order(:version_no).last
+
+      expect(page.body).to eq("a\nb")
+      expect(version.body).to eq("a\nb")
+      expect(page.current_revision.lines_count).to eq(2)
+    end
+
+    it 'deduplicates duplicated missing wiki lines before upsert' do
+      page = create_page(title: 'commit_duplicate_line_page')
+      duplicated = 'commit_duplicate_line'
+
+      described_class.content!(
+        page:,
+        body: "#{ duplicated }\n#{ duplicated }",
+        created_user: user,
+        message: 'init'
+      )
+
+      page.reload
+
+      expect(WikiLine.where(body: duplicated).count).to eq(1)
+      expect(page.current_revision.lines_count).to eq(2)
+      expect(page.current_revision.wiki_revision_lines.count).to eq(2)
+    end
+
+    it 'raises conflict when base_revision_id is stale' do
+      page = create_page(title: 'commit_conflict_page')
+
+      first = described_class.content!(
+        page:,
+        body: 'first',
+        created_user: user,
+        message: 'first'
+      )
+
+      described_class.content!(
+        page:,
+        body: 'second',
+        created_user: user,
+        message: 'second',
+        base_revision_id: first.id
+      )
+
+      expect {
+        described_class.content!(
+          page:,
+          body: 'third',
+          created_user: user,
+          message: 'third',
+          base_revision_id: first.id
+        )
+      }.to raise_error(Wiki::Commit::Conflict)
+    end
+
+    it 'does not record tag version when corresponding tag has no versions' do
+      tag_name = TagName.create!(name: 'commit_linked_tag_without_versions')
+      tag = Tag.create!(tag_name:, category: :general)
+
+      page =
+          described_class.create_content!(
+              tag_name:,
+              body: 'before',
+              created_by_user: user,
+              message: 'init')
+
+      expect(tag.reload.tag_versions.count).to eq(0)
+
+      current_revision_id = page.current_revision.id
+
+      expect {
+        described_class.content!(
+            page:,
+            body: 'after',
+            created_user: user,
+            message: 'edit',
+            base_revision_id: current_revision_id)
+      }.to change(WikiVersion, :count).by(1)
+
+      expect(tag.reload.tag_versions.count).to eq(0)
+    end
+
+    it 'does not record tag version when corresponding tag has no versions' do
+      tag_name = TagName.create!(name: 'commit_linked_tag_without_versions')
+      tag = Tag.create!(tag_name:, category: :general)
+
+      page =
+          described_class.create_content!(
+              tag_name:,
+              body: 'before',
+              created_by_user: user,
+              message: 'init')
+
+      current_revision_id = page.current_revision.id
+
+      expect {
+        described_class.content!(
+            page:,
+            body: 'after',
+            created_user: user,
+            message: 'edit',
+            base_revision_id: current_revision_id)
+      }.to change(WikiVersion, :count).by(1)
+
+      expect(tag.reload.tag_versions.count).to eq(0)
+    end
+  end
+
+  describe '.redirect!' do
+    it 'raises because redirect revisions are deprecated' do
+      page = create_page(title: 'commit_redirect_source')
+      target = create_page(title: 'commit_redirect_target')
+
+      expect {
+        described_class.redirect!(
+          page:,
+          redirect_page: target,
+          created_user: user,
+          message: 'redirect'
+        )
+      }.to raise_error(RuntimeError, '廃止しました．')
+    end
+  end
+end

backend/spec/services/wiki_version_recorder_spec.rb

@@ -0,0 +1,99 @@
+require 'rails_helper'
+
+RSpec.describe WikiVersionRecorder do
+  let(:user) { create_member_user! }
+
+  def create_page title:, body: 'body'
+    Wiki::Commit.create_content!(
+        tag_name: TagName.create!(name: title),
+        body:,
+        created_by_user: user,
+        message: 'init')
+  end
+
+  describe '.record!' do
+    it 'records title, body, reason, user, and version number' do
+      page = create_page(title: 'wiki_version_recorder_basic', body: 'body')
+
+      expect {
+        described_class.record!(
+            page:,
+            event_type: :update,
+            reason: 'manual reason',
+            created_by_user: user)
+      }
+          .to change { page.reload.wiki_versions.count }.by(1)
+
+      version = page.wiki_versions.order(:version_no).last
+
+      expect(version).to have_attributes(
+        version_no: 2,
+        event_type: 'update',
+        title: 'wiki_version_recorder_basic',
+        body: 'body',
+        reason: 'manual reason',
+        created_by_user_id: user.id
+      )
+    end
+
+    it 'does not create duplicated update version for identical snapshot' do
+      page = create_page(title: 'wiki_version_recorder_duplicate', body: 'body')
+
+      described_class.record!(
+          page:,
+          event_type: :update,
+          reason: nil,
+          created_by_user: user)
+
+      before_count = page.reload.wiki_versions.count
+
+      described_class.record!(
+          page:,
+          event_type: :update,
+          reason: nil,
+          created_by_user: user)
+
+      expect(page.reload.wiki_versions.count).to eq(before_count)
+    end
+
+    it 'creates update version when title changes' do
+      page = create_page(title: 'wiki_version_recorder_title_before', body: 'body')
+      page.tag_name.update!(name: 'wiki_version_recorder_title_after')
+
+      expect {
+        described_class.record!(
+            page:,
+            event_type: :update,
+            reason: 'rename',
+            created_by_user: user)
+      }
+          .to change { page.reload.wiki_versions.count }.by(1)
+
+      version = page.wiki_versions.order(:version_no).last
+
+      expect(version.title).to eq('wiki_version_recorder_title_after')
+      expect(version.body).to eq('body')
+      expect(version.reason).to eq('rename')
+    end
+
+    it 'creates update version when body changes' do
+      page = create_page(title: 'wiki_version_recorder_body', body: 'before')
+      page.update!(body: 'after')
+
+      expect {
+        described_class.record!(
+            page:,
+            event_type: :update,
+            reason: 'body',
+            created_by_user: user)
+      }
+          .to change { page.reload.wiki_versions.count }.by(1)
+
+      version = page.wiki_versions.order(:version_no).last
+
+      expect(version.title).to eq('wiki_version_recorder_body')
+      expect(version.body).to eq('after')
+      expect(version.reason).to eq('body')
+    end
+  end
+end

backend/spec/services/youtube/api_client_spec.rb

@@ -0,0 +1,130 @@
+require 'rails_helper'
+
+RSpec.describe Youtube::ApiClient do
+  let(:api_key) { 'test-api-key' }
+  let(:client) { described_class.new(api_key:) }
+
+  describe '#search_videos' do
+    it 'calls YouTube search API with expected params' do
+      published_after = Time.zone.parse('2026-05-01 00:00:00')
+      published_before = Time.zone.parse('2026-05-02 00:00:00')
+
+      expect(client).to receive(:get_json).with(
+        '/search',
+        {
+          part: 'snippet',
+          type: 'video',
+          q: 'ぼざろクリーチャー',
+          order: 'date',
+          maxResults: 50,
+          regionCode: 'JP',
+          relevanceLanguage: 'ja',
+          publishedAfter: published_after.iso8601,
+          publishedBefore: published_before.iso8601,
+          pageToken: 'NEXT'
+        }
+      ).and_return({ 'items' => [] })
+
+      client.search_videos(
+        q: 'ぼざろクリーチャー',
+        published_after:,
+        published_before:,
+        page_token: 'NEXT'
+      )
+    end
+
+    it 'omits nil optional params' do
+      expect(client).to receive(:get_json).with(
+        '/search',
+        hash_excluding(:publishedAfter, :publishedBefore, :pageToken)
+      ).and_return({ 'items' => [] })
+
+      client.search_videos(q: 'ぼざろクリーチャー')
+    end
+  end
+
+  describe '#videos' do
+    it 'returns empty items when ids are empty' do
+      expect(client).not_to receive(:get_json)
+
+      expect(client.videos([])).to eq({ 'items' => [] })
+    end
+
+    it 'calls videos API with comma separated ids' do
+      expect(client).to receive(:get_json).with(
+        '/videos',
+        {
+          part: 'snippet,status,contentDetails',
+          id: 'video-1,video-2'
+        }
+      ).and_return({ 'items' => [] })
+
+      client.videos(['video-1', 'video-2'])
+    end
+  end
+
+  describe '#playlist_items' do
+    it 'calls playlistItems API with page token' do
+      expect(client).to receive(:get_json).with(
+        '/playlistItems',
+        {
+          part: 'snippet,contentDetails,status',
+          playlistId: 'PL123',
+          maxResults: 50,
+          pageToken: 'NEXT'
+        }
+      ).and_return({ 'items' => [] })
+
+      client.playlist_items(playlist_id: 'PL123', page_token: 'NEXT')
+    end
+
+    it 'omits page token when nil' do
+      expect(client).to receive(:get_json).with(
+        '/playlistItems',
+        {
+          part: 'snippet,contentDetails,status',
+          playlistId: 'PL123',
+          maxResults: 50
+        }
+      ).and_return({ 'items' => [] })
+
+      client.playlist_items(playlist_id: 'PL123')
+    end
+  end
+
+  describe '#channel' do
+    it 'calls channels API by id' do
+      expect(client).to receive(:get_json).with(
+        '/channels',
+        {
+          part: 'snippet,contentDetails',
+          id: 'UC123'
+        }
+      ).and_return({ 'items' => [] })
+
+      client.channel(id: 'UC123')
+    end
+
+    it 'calls channels API by handle' do
+      expect(client).to receive(:get_json).with(
+        '/channels',
+        {
+          part: 'snippet,contentDetails',
+          forHandle: '@some_handle'
+        }
+      ).and_return({ 'items' => [] })
+
+      client.channel(handle: '@some_handle')
+    end
+
+    it 'raises when neither id nor handle is given' do
+      expect { client.channel }.to raise_error(ArgumentError, 'id or handle is required')
+    end
+
+    it 'raises when both id and handle are given' do
+      expect do
+        client.channel(id: 'UC123', handle: '@some_handle')
+      end.to raise_error(ArgumentError, 'id or handle is required')
+    end
+  end
+end

backend/spec/services/youtube/sync_spec.rb

@@ -0,0 +1,310 @@
+require 'rails_helper'
+
+RSpec.describe Youtube::Sync do
+  let(:client) { instance_double(Youtube::ApiClient) }
+  let(:sync) { described_class.new(client:) }
+
+  before do
+    allow(PostVersionRecorder).to receive(:record!)
+    allow(PostVersionRecorder).to receive(:ensure_snapshot!)
+    allow(sync).to receive(:attach_thumbnail_if_needed!)
+  end
+
+  describe '#sync!' do
+    it 'returns without fetching video details when no video ids are discovered' do
+      allow(sync).to receive(:query_terms).and_return([])
+      allow(sync).to receive(:playlist_ids).and_return([])
+
+      expect(client).not_to receive(:videos)
+
+      sync.sync!
+    end
+
+    it 'discovers ids from search and all playlist pages' do
+      allow(sync).to receive(:query_terms).and_return(['ぼざろクリーチャー'])
+      allow(sync).to receive(:playlist_ids).and_return(['PL123'])
+      allow(sync).to receive(:sync_since).and_return(Time.zone.parse('2026-05-01 00:00:00'))
+
+      allow(client).to receive(:search_videos).with(
+        q: 'ぼざろクリーチャー',
+        published_after: Time.zone.parse('2026-05-01 00:00:00')
+      ).and_return({
+        'items' => [
+          {
+            'id' => {
+              'videoId' => 'search-video-1'
+            }
+          }
+        ]
+      })
+
+      allow(client).to receive(:playlist_items).with(
+        playlist_id: 'PL123',
+        page_token: nil
+      ).and_return({
+        'items' => [
+          {
+            'contentDetails' => {
+              'videoId' => 'playlist-video-1'
+            }
+          }
+        ],
+        'nextPageToken' => 'NEXT'
+      })
+
+      allow(client).to receive(:playlist_items).with(
+        playlist_id: 'PL123',
+        page_token: 'NEXT'
+      ).and_return({
+        'items' => [
+          {
+            'snippet' => {
+              'resourceId' => {
+                'videoId' => 'playlist-video-2'
+              }
+            }
+          }
+        ]
+      })
+
+      expect(client).to receive(:videos).with(
+        satisfy do |ids|
+          ids.sort == ['playlist-video-1', 'playlist-video-2', 'search-video-1']
+        end
+      ).and_return({ 'items' => [] })
+
+      sync.sync!
+    end
+
+    it 'creates a YouTube post with default tags and no_deerjikist when no deerjikist mapping exists' do
+      Tag.tagme
+      Tag.bot
+      Tag.youtube
+      Tag.video
+      Tag.no_deerjikist
+
+      allow(sync).to receive(:query_terms).and_return([])
+      allow(sync).to receive(:playlist_ids).and_return(['PL123'])
+
+      allow(client).to receive(:playlist_items).with(
+        playlist_id: 'PL123',
+        page_token: nil
+      ).and_return({
+        'items' => [
+          {
+            'contentDetails' => {
+              'videoId' => 'video-1'
+            }
+          }
+        ]
+      })
+
+      allow(client).to receive(:videos).with(['video-1']).and_return({
+        'items' => [
+          youtube_video_item(
+            id: 'video-1',
+            title: 'YouTube テスト動画',
+            channel_id: 'UC_NO_MAPPING'
+          )
+        ]
+      })
+
+      expect do
+        sync.sync!
+      end.to change(Post, :count).by(1)
+
+      post = Post.find_by!(url: 'https://www.youtube.com/watch?v=video-1')
+      tag_ids = post.tags.pluck(:id)
+
+      expect(post.title).to eq('YouTube テスト動画')
+      expect(post.uploaded_user_id).to be_nil
+      expect(post.original_created_from).to eq(Time.zone.parse('2026-05-01 12:34:00'))
+      expect(post.original_created_before).to eq(Time.zone.parse('2026-05-01 12:35:00'))
+
+      expect(tag_ids).to include(Tag.tagme.id)
+      expect(tag_ids).to include(Tag.bot.id)
+      expect(tag_ids).to include(Tag.youtube.id)
+      expect(tag_ids).to include(Tag.video.id)
+      expect(tag_ids).to include(Tag.no_deerjikist.id)
+
+      expect(PostVersionRecorder).to have_received(:record!).with(
+        post:,
+        event_type: :create,
+        created_by_user: nil
+      )
+    end
+
+    it 'uses deerjikist tag when channel id is mapped' do
+      Tag.tagme
+      Tag.bot
+      Tag.youtube
+      Tag.video
+      Tag.no_deerjikist
+
+      deerjikist_tag = Tag.find_or_create_by_tag_name!('テスト投稿者', category: :deerjikist)
+      Deerjikist.create!(
+        platform: 'youtube',
+        code: 'UC_MAPPED',
+        tag: deerjikist_tag
+      )
+
+      allow(sync).to receive(:query_terms).and_return([])
+      allow(sync).to receive(:playlist_ids).and_return(['PL123'])
+
+      allow(client).to receive(:playlist_items).with(
+        playlist_id: 'PL123',
+        page_token: nil
+      ).and_return({
+        'items' => [
+          {
+            'contentDetails' => {
+              'videoId' => 'video-1'
+            }
+          }
+        ]
+      })
+
+      allow(client).to receive(:videos).with(['video-1']).and_return({
+        'items' => [
+          youtube_video_item(
+            id: 'video-1',
+            title: 'YouTube テスト動画',
+            channel_id: 'UC_MAPPED'
+          )
+        ]
+      })
+
+      sync.sync!
+
+      post = Post.find_by!(url: 'https://www.youtube.com/watch?v=video-1')
+      tag_ids = post.tags.pluck(:id)
+
+      expect(tag_ids).to include(deerjikist_tag.id)
+      expect(tag_ids).not_to include(Tag.no_deerjikist.id)
+    end
+
+    it 'removes no_deerjikist when deerjikist mapping is added later' do
+      Tag.no_deerjikist
+
+      post = Post.create!(
+        title: '旧タイトル',
+        url: 'https://www.youtube.com/watch?v=video-1',
+        uploaded_user_id: nil,
+        original_created_from: Time.zone.parse('2026-05-01 00:00:00'),
+        original_created_before: Time.zone.parse('2026-05-01 00:01:00')
+      )
+      PostTag.create!(post:, tag: Tag.no_deerjikist)
+
+      deerjikist_tag = Tag.find_or_create_by_tag_name!('後から判明した投稿者', category: :deerjikist)
+      Deerjikist.create!(
+        platform: 'youtube',
+        code: 'UC_MAPPED_LATER',
+        tag: deerjikist_tag
+      )
+
+      allow(sync).to receive(:query_terms).and_return([])
+      allow(sync).to receive(:playlist_ids).and_return(['PL123'])
+
+      allow(client).to receive(:playlist_items).with(
+        playlist_id: 'PL123',
+        page_token: nil
+      ).and_return({
+        'items' => [
+          {
+            'contentDetails' => {
+              'videoId' => 'video-1'
+            }
+          }
+        ]
+      })
+
+      allow(client).to receive(:videos).with(['video-1']).and_return({
+        'items' => [
+          youtube_video_item(
+            id: 'video-1',
+            title: '新タイトル',
+            channel_id: 'UC_MAPPED_LATER'
+          )
+        ]
+      })
+
+      sync.sync!
+
+      post.reload
+      tag_ids = post.tags.pluck(:id)
+
+      expect(post.title).to eq('新タイトル')
+      expect(tag_ids).to include(deerjikist_tag.id)
+      expect(tag_ids).not_to include(Tag.no_deerjikist.id)
+
+      expect(PostVersionRecorder).to have_received(:ensure_snapshot!).with(
+        post,
+        created_by_user: nil
+      )
+      expect(PostVersionRecorder).to have_received(:record!).with(
+        post:,
+        event_type: :update,
+        created_by_user: nil
+      )
+    end
+
+    it 'matches existing youtu.be URL and does not create duplicate post' do
+      post = Post.create!(
+        title: '旧タイトル',
+        url: 'https://youtu.be/video-1',
+        uploaded_user_id: nil,
+        original_created_from: Time.zone.parse('2026-05-01 00:00:00'),
+        original_created_before: Time.zone.parse('2026-05-01 00:01:00')
+      )
+
+      allow(sync).to receive(:query_terms).and_return([])
+      allow(sync).to receive(:playlist_ids).and_return(['PL123'])
+
+      allow(client).to receive(:playlist_items).with(
+        playlist_id: 'PL123',
+        page_token: nil
+      ).and_return({
+        'items' => [
+          {
+            'contentDetails' => {
+              'videoId' => 'video-1'
+            }
+          }
+        ]
+      })
+
+      allow(client).to receive(:videos).with(['video-1']).and_return({
+        'items' => [
+          youtube_video_item(
+            id: 'video-1',
+            title: '新タイトル',
+            channel_id: 'UC_NO_MAPPING'
+          )
+        ]
+      })
+
+      expect do
+        sync.sync!
+      end.not_to change(Post, :count)
+
+      expect(post.reload.title).to eq('新タイトル')
+    end
+  end
+
+  def youtube_video_item(id:, title:, channel_id:)
+    {
+      'id' => id,
+      'snippet' => {
+        'title' => title,
+        'channelId' => channel_id,
+        'publishedAt' => '2026-05-01T12:34:56Z',
+        'thumbnails' => {
+          'high' => {
+            'url' => "https://img.youtube.com/#{id}.jpg"
+          }
+        },
+        'tags' => ['tag-a', 'tag-b']
+      }
+    }
+  end
+end

backend/spec/services/youtube/video_item_spec.rb

@@ -0,0 +1,93 @@
+require 'rails_helper'
+
+RSpec.describe Youtube::VideoItem do
+  describe '#initialize' do
+    it 'extracts fields from YouTube video API item' do
+      item = {
+        'id' => 'video-1',
+        'snippet' => {
+          'title' => 'テスト動画',
+          'channelId' => 'UC123',
+          'publishedAt' => '2026-05-01T12:34:56Z',
+          'tags' => ['tag-a', 'tag-b'],
+          'thumbnails' => {
+            'high' => {
+              'url' => 'https://img.youtube.com/high.jpg'
+            },
+            'medium' => {
+              'url' => 'https://img.youtube.com/medium.jpg'
+            }
+          }
+        }
+      }
+
+      video = described_class.new(item)
+
+      expect(video.id).to eq('video-1')
+      expect(video.title).to eq('テスト動画')
+      expect(video.channel_id).to eq('UC123')
+      expect(video.published_at).to eq(Time.iso8601('2026-05-01T12:34:56Z'))
+      expect(video.thumbnail_url).to eq('https://img.youtube.com/high.jpg')
+      expect(video.raw_tags).to eq(['tag-a', 'tag-b'])
+      expect(video.url).to eq('https://www.youtube.com/watch?v=video-1')
+    end
+
+    it 'uses highest priority thumbnail' do
+      item = {
+        'id' => 'video-1',
+        'snippet' => {
+          'title' => 'テスト動画',
+          'channelId' => 'UC123',
+          'publishedAt' => '2026-05-01T12:34:56Z',
+          'thumbnails' => {
+            'default' => {
+              'url' => 'https://img.youtube.com/default.jpg'
+            },
+            'standard' => {
+              'url' => 'https://img.youtube.com/standard.jpg'
+            },
+            'maxres' => {
+              'url' => 'https://img.youtube.com/maxres.jpg'
+            }
+          }
+        }
+      }
+
+      video = described_class.new(item)
+
+      expect(video.thumbnail_url).to eq('https://img.youtube.com/maxres.jpg')
+    end
+
+    it 'falls back to empty raw tags' do
+      item = {
+        'id' => 'video-1',
+        'snippet' => {
+          'title' => 'テスト動画',
+          'channelId' => 'UC123',
+          'publishedAt' => '2026-05-01T12:34:56Z',
+          'thumbnails' => {}
+        }
+      }
+
+      video = described_class.new(item)
+
+      expect(video.raw_tags).to eq([])
+    end
+
+    it 'returns nil thumbnail when no thumbnail exists' do
+      item = {
+        'id' => 'video-1',
+        'snippet' => {
+          'title' => 'テスト動画',
+          'channelId' => 'UC123',
+          'publishedAt' => '2026-05-01T12:34:56Z',
+          'thumbnails' => {}
+        }
+      }
+
+      video = described_class.new(item)
+
+      expect(video.thumbnail_url).to be_nil
+    end
+  end
+end

backend/spec/tasks/nico_export_spec.rb

@@ -0,0 +1,100 @@
+require 'rails_helper'
+require 'rake'
+require 'open3'
+
+RSpec.describe 'nico:export' do
+  let(:task) { Rake::Task['nico:export'] }
+  let(:success_status) { instance_double(Process::Status, success?: true) }
+  let(:failure_status) { instance_double(Process::Status, success?: false) }
+
+  def create_post(url)
+    Post.create!(url:)
+  end
+
+  before(:all) do
+    Rails.application.load_tasks unless Rake::Task.task_defined?('nico:export')
+  end
+
+  before do
+    task.reenable
+
+    allow(ENV).to receive(:fetch).with('MYSQL_USER').and_return('mysql-user')
+    allow(ENV).to receive(:fetch).with('MYSQL_PASS').and_return('mysql-pass')
+    allow(ENV).to receive(:fetch).with('NIZIKA_NICO_PATH').and_return('/srv/nizika-nico')
+  end
+
+  describe 'export' do
+    it 'exports nicovideo ids to shared nico DB' do
+      create_post('https://www.nicovideo.jp/watch/sm12345?ref=foo')
+      create_post('https://www.nicovideo.jp/watch/so67890#comments')
+      create_post('https://www.nicovideo.jp/watch/nm24680')
+      create_post('https://example.com/watch/sm99999')
+
+      expect(Open3).to receive(:capture3) do |env, *args, **kwargs|
+        expect(env).to eq(
+          {
+            'MYSQL_USER' => 'mysql-user',
+            'MYSQL_PASS' => 'mysql-pass',
+          },
+        )
+
+        expect(args.take(3)).to eq(
+          [
+            'python3',
+            '-m',
+            'tracked_videos.put_bulk_upsert',
+          ],
+        )
+
+        expect(args.drop(3)).to contain_exactly(
+          'sm12345',
+          'so67890',
+          'nm24680',
+        )
+
+        expect(kwargs).to eq(chdir: '/srv/nizika-nico')
+
+        ['', '', success_status]
+      end
+
+      task.invoke
+    end
+
+    it 'deduplicates video ids' do
+      create_post('https://www.nicovideo.jp/watch/sm12345')
+      create_post('https://www.nicovideo.jp/watch/sm12345?from=1')
+
+      expect(Open3).to receive(:capture3) do |_env, *args, **_kwargs|
+        expect(args.drop(3)).to eq(['sm12345'])
+
+        ['', '', success_status]
+      end
+
+      task.invoke
+    end
+
+    it 'does not call python when there are no nicovideo posts' do
+      create_post('https://example.com/watch/sm12345')
+
+      expect(Open3).not_to receive(:capture3)
+
+      task.invoke
+    end
+
+    it 'raises stderr when python command fails' do
+      create_post('https://www.nicovideo.jp/watch/sm12345')
+
+      allow(Open3).to receive(:capture3).and_return(
+        [
+          '',
+          'bulk upsert failed',
+          failure_status,
+        ],
+      )
+
+      expect {
+        task.invoke
+      }.to raise_error(RuntimeError, 'bulk upsert failed')
+    end
+  end
+end

backend/spec/tasks/nico_sync_spec.rb

@@ -8,8 +8,8 @@ RSpec.describe "nico:sync" do
   end
 
   def create_tag!(name, category:)
-    tn = TagName.find_or_create_by!(name: name.to_s.strip)
-    Tag.find_or_create_by!(tag_name_id: tn.id) { |t| t.category = category }
+    tn = TagName.find_undiscard_or_create_by!(name: name.to_s.strip)
+    Tag.find_undiscard_or_create_by!(tag_name_id: tn.id) { |t| t.category = category }
   end
 
   def link_nico_to_tag!(nico_tag, tag)
@@ -90,4 +90,232 @@ RSpec.describe "nico:sync" do
     expect(active_names).to include("nico:NEW")
     expect(active_names).not_to include("nico:OLD")
   end
+
+  def snapshot_tags(post)
+    post.snapshot_tag_names.join(' ')
+  end
+
+  def create_post_version_for!(post, version_no: 1, event_type: 'create', created_by_user: nil)
+    PostVersion.create!(
+      post: post,
+      version_no: version_no,
+      event_type: event_type,
+      title: post.title,
+      url: post.url,
+      thumbnail_base: post.thumbnail_base,
+      tags: snapshot_tags(post),
+      parent: post.parent,
+      original_created_from: post.original_created_from,
+      original_created_before: post.original_created_before,
+      created_at: Time.current,
+      created_by_user: created_by_user
+    )
+  end
+
+  it '新規 post 作成時に version 1 を作る' do
+    Tag.bot
+    Tag.tagme
+    Tag.niconico
+    Tag.video
+    Tag.no_deerjikist
+
+    stub_python([{
+      'code' => 'sm9',
+      'title' => 't',
+      'tags' => ['AAA'],
+      'uploaded_at' => '2026-01-01 12:34:56'
+    }])
+
+    allow(URI).to receive(:open).and_return(StringIO.new('<html></html>'))
+
+    expect {
+      run_rake_task('nico:sync')
+    }.to change(PostVersion, :count).by(1)
+
+    post = Post.find_by!(url: 'https://www.nicovideo.jp/watch/sm9')
+    version = post.post_versions.order(:version_no).last
+
+    expect(version.version_no).to eq(1)
+    expect(version.event_type).to eq('create')
+    expect(version.created_by_user).to be_nil
+    expect(version.tags).to eq(snapshot_tags(post.reload))
+  end
+
+  it '既存 post の内容または tags が変わったとき update version を作る' do
+    post = Post.create!(
+      title: 'old',
+      url: 'https://www.nicovideo.jp/watch/sm9',
+      uploaded_user: nil
+    )
+
+    kept_general = create_tag!('spec_kept', category: 'general')
+    PostTag.create!(post: post, tag: kept_general)
+    create_post_version_for!(post)
+
+    linked = create_tag!('spec_linked', category: 'general')
+    nico = create_tag!('nico:AAA', category: 'nico')
+    link_nico_to_tag!(nico, linked)
+
+    Tag.bot
+    Tag.tagme
+    Tag.no_deerjikist
+
+    stub_python([{
+      'code' => 'sm9',
+      'title' => 't',
+      'tags' => ['AAA'],
+      'uploaded_at' => '2026-01-01 12:34:56'
+    }])
+
+    allow(URI).to receive(:open).and_return(StringIO.new('<html></html>'))
+
+    expect {
+      run_rake_task('nico:sync')
+    }.to change(PostVersion, :count).by(1)
+
+    version = post.reload.post_versions.order(:version_no).last
+    expect(version.version_no).to eq(2)
+    expect(version.event_type).to eq('update')
+    expect(version.created_by_user).to be_nil
+    expect(version.tags).to eq(snapshot_tags(post.reload))
+  end
+
+  it '既存 post に差分が無いときは新しい version を作らない' do
+    nico = create_tag!('nico:AAA', category: 'nico')
+    no_deerjikist = create_tag!('ニジラー情報不詳', category: 'meta')
+
+    post = Post.create!(
+      title: 't',
+      url: 'https://www.nicovideo.jp/watch/sm9',
+      uploaded_user: nil,
+      original_created_from: Time.iso8601('2026-01-01T03:34:00Z'),
+      original_created_before: Time.iso8601('2026-01-01T03:35:00Z')
+    )
+
+    PostTag.create!(post: post, tag: nico)
+    PostTag.create!(post: post, tag: no_deerjikist)
+    create_post_version_for!(post)
+
+    stub_python([{
+      'code' => 'sm9',
+      'title' => 't',
+      'tags' => ['AAA'],
+      'uploaded_at' => '2026-01-01 12:34:56'
+    }])
+
+    allow(URI).to receive(:open).and_return(StringIO.new('<html></html>'))
+
+    expect {
+      run_rake_task('nico:sync')
+    }.not_to change(PostVersion, :count)
+
+    version = post.reload.post_versions.order(:version_no).last
+    expect(version.version_no).to eq(1)
+    expect(version.event_type).to eq('create')
+    expect(version.tags).to eq(snapshot_tags(post.reload))
+  end
+
+  it '新規 nico tag に nico tag version を作る' do
+    Tag.bot
+    Tag.tagme
+    Tag.niconico
+    Tag.video
+    Tag.no_deerjikist
+
+    stub_python([{
+      'code' => 'sm9',
+      'title' => 't',
+      'tags' => ['AAA'],
+      'uploaded_at' => '2026-01-01 12:34:56'
+    }])
+
+    allow(URI).to receive(:open).and_return(StringIO.new('<html></html>'))
+
+    expect {
+      run_rake_task('nico:sync')
+    }.to change(NicoTagVersion, :count).by(1)
+
+    nico_tag = Tag.joins(:tag_name).find_by!(tag_names: { name: 'nico:AAA' })
+    version = nico_tag.nico_tag_versions.order(:version_no).last
+
+    expect(version.version_no).to eq(1)
+    expect(version.event_type).to eq('create')
+    expect(version.name).to eq('nico:AAA')
+    expect(version.created_by_user).to be_nil
+  end
+
+  it '既存 post に version が無い場合は create snapshot を補う' do
+    post = Post.create!(
+      title: 'old',
+      url: 'https://www.nicovideo.jp/watch/sm9',
+      uploaded_user: nil
+    )
+
+    kept_general = create_tag!('spec_kept_without_version', category: 'general')
+    PostTag.create!(post: post, tag: kept_general)
+
+    Tag.bot
+    Tag.tagme
+    Tag.no_deerjikist
+
+    stub_python([{
+      'code' => 'sm9',
+      'title' => 'changed title',
+      'tags' => ['AAA'],
+      'uploaded_at' => '2026-01-01 12:34:56'
+    }])
+
+    allow(URI).to receive(:open).and_return(StringIO.new('<html></html>'))
+
+    expect {
+      run_rake_task('nico:sync')
+    }.to change { post.reload.post_versions.count }.by(1)
+
+    versions = post.reload.post_versions.order(:version_no)
+
+    expect(versions.map(&:event_type)).to eq(['create'])
+    expect(versions.first.title).to eq('changed title')
+    expect(versions.first.tags).to eq(snapshot_tags(post.reload))
+  end
+
+  it '既存 version がある post には update version を作る' do
+    post = Post.create!(
+      title: 'old',
+      url: 'https://www.nicovideo.jp/watch/sm9',
+      uploaded_user: nil
+    )
+
+    kept_general = create_tag!('spec_kept_with_version', category: 'general')
+    PostTag.create!(post: post, tag: kept_general)
+
+    PostVersionRecorder.record!(
+      post: post,
+      event_type: :create,
+      created_by_user: nil
+    )
+
+    Tag.bot
+    Tag.tagme
+    Tag.no_deerjikist
+
+    stub_python([{
+      'code' => 'sm9',
+      'title' => 'changed title',
+      'tags' => ['AAA'],
+      'uploaded_at' => '2026-01-01 12:34:56'
+    }])
+
+    allow(URI).to receive(:open).and_return(StringIO.new('<html></html>'))
+
+    expect {
+      run_rake_task('nico:sync')
+    }.to change { post.reload.post_versions.count }.by(1)
+
+    versions = post.reload.post_versions.order(:version_no)
+
+    expect(versions.map(&:event_type)).to eq(['create', 'update'])
+    expect(versions.first.title).to eq('old')
+    expect(versions.second.title).to eq('changed title')
+    expect(versions.second.tags).to eq(snapshot_tags(post.reload))
+  end
 end

backend/spec/tasks/post_sync_spec.rb

@@ -0,0 +1,25 @@
+require 'rails_helper'
+require 'rake'
+
+RSpec.describe 'post:sync' do
+  around do |example|
+    original_application = Rake.application
+    Rake.application = Rake::Application.new
+
+    Rake::Task.define_task(:environment)
+    load Rails.root.join('lib/tasks/sync_posts.rake')
+
+    example.run
+  ensure
+    Rake.application = original_application
+  end
+
+  it 'runs Youtube::Sync' do
+    sync = instance_double(Youtube::Sync)
+
+    expect(Youtube::Sync).to receive(:new).once.and_return(sync)
+    expect(sync).to receive(:sync!).once
+
+    Rake::Task['post:sync'].invoke
+  end
+end

frontend/package.json

@@ -15,6 +15,8 @@
     "@dnd-kit/modifiers": "^9.0.0",
     "@dnd-kit/utilities": "^3.2.2",
     "@fontsource-variable/noto-sans-jp": "^5.2.9",
+    "@mdx-js/react": "^3.1.1",
+    "@mdx-js/rollup": "^3.1.1",
     "@radix-ui/react-dialog": "^1.1.14",
     "@radix-ui/react-switch": "^1.2.5",
     "@radix-ui/react-toast": "^1.2.14",
@@ -37,13 +39,15 @@
     "react-youtube": "^10.1.0",
     "remark-gfm": "^4.0.1",
     "tailwind-merge": "^3.3.0",
-    "zustand": "^5.0.8",
-    "unist-util-visit-parents": "^6.0.1"
+    "unist-util-visit-parents": "^6.0.1",
+    "zustand": "^5.0.8"
   },
   "devDependencies": {
     "@eslint/js": "^9.25.0",
+    "@tailwindcss/typography": "^0.5.19",
     "@types/axios": "^0.14.4",
     "@types/markdown-it": "^14.1.2",
+    "@types/mdx": "^2.0.13",
     "@types/node": "^24.0.13",
     "@types/react": "^19.1.2",
     "@types/react-dom": "^19.1.2",

frontend/scripts/generate-sitemap.js

@@ -20,7 +20,7 @@ const fetchPosts = async tagName => (await axios.get (`${ API_BASE_URL }/posts`,
                                  match: 'all',
                                  limit: '20' }) } })).data.posts
 
-const fetchTags = async () => (await axios.get (`${ API_BASE_URL }/tags`)).data
+const fetchTags = async () => (await axios.get (`${ API_BASE_URL }/tags`)).data.tags
 const fetchTagNames = async () => (await fetchTags ()).map (tag => tag.name)
 
 const fetchWikiPages = async () => (await axios.get (`${ API_BASE_URL }/wiki`)).data