Merge branch 'main' into feature/406

backend/app/controllers/post_versions_controller.rb

@@ -23,19 +23,17 @@ class PostVersionsController < ApplicationController
               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_json AS prev_tags_json',
+        .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.video_ms AS prev_video_ms',
                 '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(("JSON_CONTAINTS(post_version.tags_json, JSON_OBJECT('id', :tag_id)) " +
-                   "OR JSON_CONTAINS(prev.tags_json, JSON_OBJECT('id', :tag_id))"), tag_id:)
+      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
@@ -49,47 +47,56 @@ class PostVersionsController < ApplicationController
 
   private
 
-  def tag_literal tag
-    sections = tag.fetch('sections', []).map do |sec|
-      begin_ms = sec.fetch('begin_ms')
-      end_ms = sec['end_ms']
-
-      "[#{ Post.ms_to_time(begin_ms) }-#{ end_ms ? Post.ms_to_time(end_ms) : '' }]"
-    end
-
-    "#{ tag.fetch('name') }#{ sections.join }"
-  end
-
   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_json.map { tag_literal(_1) })
-      prev_tags = split_tags(row.attributes['prev_tags_json'].map { tag_literal(_1) })
+      cur_tags = split_tags(row.tags)
+      prev_tags = split_tags(row.attributes['prev_tags'])
 
-      { post_id: row.post_id,
+      {
+        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'] },
-        video_ms: { current: row.video_ms, prev: row.attributes['prev_video_ms'] },
+        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']
+        },
+        video_ms: {
+          current: row.video_ms,
+          prev: row.attributes['prev_video_ms']
+        },
         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 },
+          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 },
+          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 }
+          if row.created_by_user_id
+            {
+              id: row.created_by_user_id,
+              name: users_by_id[row.created_by_user_id]
+            }
+          end
+      }
     end
   end
 

backend/app/controllers/posts_controller.rb

@@ -699,10 +699,7 @@ class PostsController < ApplicationController
   end
 
   def editable_tag_names_from_version version
-    version.tags_json
-        .reject { _1[:name].downcase.start_with?('nico:') }
-        .map { tag_literal(_1) }
-        .sort
+    version.tags.to_s.split.reject { |name| name.downcase.start_with?('nico:') }.sort
   end
 
   def post_snapshot_from_record post

backend/app/controllers/tags_controller.rb

@@ -200,14 +200,45 @@ class TagsController < ApplicationController
              .find_by(id: params[:id])
     return head :not_found unless tag
 
+    rows = normalise_deerjikist_rows(tag)
+    return if performed?
+
     ApplicationRecord.transaction do
-      tag.deerjikists = []
-      params[:_json].each.with_index do |item, i|
-        platform = item[:platform]
-        code = normalise_deerjikist_code(platform, item[:code])
-        deerjikist = Deerjikist.find_or_initialize_by(platform:, code:)
-        deerjikist.tag = tag
-        render_deerjikist_form_record_invalid(deerjikist, i) unless deerjikist.save
+      tag.lock!
+
+      requested_keys = rows.map { |row| [row[:platform], row[:code]] }.uniq
+      row_indexes_by_key = rows_by_key(rows)
+      locked_deerjikists = lock_deerjikists_for_tag_update(tag.id, requested_keys)
+      current_deerjikists = locked_deerjikists.filter { |deerjikist|
+        deerjikist.tag_id == tag.id
+      }
+      requested_deerjikists = locked_deerjikists.filter { |deerjikist|
+        row_indexes_by_key.key?([deerjikist.platform, deerjikist.code])
+      }
+
+      render_deerjikist_conflicts(requested_deerjikists, row_indexes_by_key, tag)
+      raise ActiveRecord::Rollback if performed?
+
+      requested_keys_set = requested_keys.to_set
+      current_deerjikists.each do |deerjikist|
+        key = [deerjikist.platform, deerjikist.code]
+        deerjikist.destroy! unless requested_keys_set.include?(key)
+      end
+
+      existing_keys = requested_deerjikists.to_h { |deerjikist|
+        [[deerjikist.platform, deerjikist.code], true]
+      }
+      requested_keys.each do |platform, code|
+        next if existing_keys[[platform, code]]
+
+        deerjikist = Deerjikist.new(platform:, code:, tag:)
+        row_index = row_indexes_by_key[[platform, code]].first
+        begin
+          render_deerjikist_form_record_invalid(deerjikist, row_index) unless deerjikist.save
+        rescue ActiveRecord::RecordNotUnique
+          conflicts = lock_deerjikists_for_tag_update(tag.id, [[platform, code]])
+          render_deerjikist_conflicts(conflicts, row_indexes_by_key, tag)
+        end
         raise ActiveRecord::Rollback if performed?
       end
     end
@@ -653,6 +684,7 @@ class TagsController < ApplicationController
   end
 
   def normalise_deerjikist_code platform, code
+    code = code.to_s
     return code if platform != 'youtube' || code[0] != '@'
 
     url = "https://www.youtube.com/#{ code }"
@@ -669,6 +701,82 @@ class TagsController < ApplicationController
     nil
   end
 
+  def normalise_deerjikist_rows tag
+    rows = []
+
+    params[:_json].each.with_index do |item, index|
+      platform = item[:platform]
+      unless Deerjikist.platforms.key?(platform)
+        render_deerjikist_platform_invalid(index)
+        return rows
+      end
+
+      code = normalise_deerjikist_code(platform, item[:code])
+      deerjikist = Deerjikist.new(platform:, code:, tag:)
+      unless deerjikist.valid?
+        render_deerjikist_form_record_invalid(deerjikist, index)
+        return rows
+      end
+
+      rows << { index:, platform:, code: }
+    end
+
+    rows
+  end
+
+  def lock_deerjikists_for_tag_update tag_id, keys
+    clauses = ['tag_id = ?']
+    values = [tag_id]
+
+    keys.each do |platform, code|
+      clauses << '(platform = ? AND code = ?)'
+      values << platform << code
+    end
+
+    Deerjikist
+      .where(clauses.join(' OR '), *values)
+      .order(:platform, :code)
+      .lock
+      .to_a
+  end
+
+  def render_deerjikist_conflicts deerjikists, row_indexes_by_key, tag
+    conflicts = deerjikists.filter { |deerjikist| deerjikist.tag_id != tag.id }
+    return if conflicts.empty?
+
+    tag_names_by_id = Tag
+                      .joins(:tag_name)
+                      .where(id: conflicts.map(&:tag_id).uniq)
+                      .pluck('tags.id', 'tag_names.name')
+                      .to_h
+    fields = { }
+
+    conflicts.each do |deerjikist|
+      message = "この情報は既に「#{ tag_names_by_id[deerjikist.tag_id] }」に紐づいてゐます．"
+      row_indexes_by_key[[deerjikist.platform, deerjikist.code]].each do |index|
+        field = :"deerjikists.#{ index }.code"
+        fields[field] ||= []
+        fields[field] << message
+      end
+    end
+
+    render_validation_error fields:
+  end
+
+  def render_deerjikist_platform_invalid index
+    render_validation_error fields: {
+      :"deerjikists.#{ index }.platform" => ['値が不正です．'],
+    }
+  end
+
+  def rows_by_key rows
+    rows.each_with_object({ }) do |row, result|
+      key = [row[:platform], row[:code]]
+      result[key] ||= []
+      result[key] << row[:index]
+    end
+  end
+
   def render_deerjikist_form_record_invalid deerjikist, index
     fields = { }
 

backend/app/models/post.rb

@@ -137,21 +137,6 @@ class Post < ApplicationRecord
     end
   end
 
-  def snapshot_tags_json
-    post_tags
-        .kept
-        .joins(tag: :tag_name)
-        .includes(:sections, tag: :tag_name)
-        .order('tag_names.name')
-        .map do |pt|
-      { id: pt.tag.id,
-        version_no: pt.tag.version_no,
-        name: pt.tag.name,
-        category: pt.tag.category,
-        sections: pt.sections.map { { begin_ms: _1.begin_ms, end_ms: _1.end_ms } } }
-    end
-  end
-
   def self.section_literal section
     end_ms =
         section.end_ms ? Post.ms_to_time(section.end_ms) : ''

backend/app/services/post_version_recorder.rb

@@ -25,7 +25,6 @@ class PostVersionRecorder < VersionRecorder
       thumbnail_base: @record.thumbnail_base,
       video_ms: @record.video_ms,
       tags: @record.snapshot_tag_names.join(' '),
-      tags_json: @record.snapshot_tags_json,
       parent_post_ids: @record.snapshot_parent_post_ids.join(' '),
       original_created_from: @record.original_created_from,
       original_created_before: @record.original_created_before }

backend/db/migrate/20260727123600_add_tags_json_to_post_versions.rb

@@ -1,279 +0,0 @@
-class AddTagsJsonToPostVersions < ActiveRecord::Migration[8.0]
-  RESOLUTION_GRACE = 1.second
-  SECTION_LITERAL_PATTERN = /\[[^\[\]\s]*-[^\[\]\s]*\]\z/
-
-  class MigrationPostVersion < ActiveRecord::Base
-    self.table_name = 'post_versions'
-  end
-
-  class MigrationTag < ActiveRecord::Base
-    self.table_name = 'tags'
-  end
-
-  class MigrationTagName < ActiveRecord::Base
-    self.table_name = 'tag_names'
-  end
-
-  class MigrationTagVersion < ActiveRecord::Base
-    self.table_name = 'tag_versions'
-  end
-
-  class MigrationNicoTagVersion < ActiveRecord::Base
-    self.table_name = 'nico_tag_versions'
-  end
-
-  def up
-    add_column :post_versions, :tags_json, :json, after: :tags
-    MigrationPostVersion.reset_column_information
-
-    backfill_missing_initial_tag_versions!
-    intervals_by_name = build_intervals_by_name
-
-    say_with_time 'Backfilling post_versions.tags_json' do
-      MigrationPostVersion
-          .where(tags_json: nil)
-          .find_each(batch_size: 500) do |version|
-        version.update_columns(
-            tags_json: build_tags_json(version, intervals_by_name))
-      end
-    end
-
-    assert_backfill_complete!
-    change_column_null :post_versions, :tags_json, false
-  end
-
-  def down
-    remove_column :post_versions, :tags_json
-  end
-
-  private
-
-  def backfill_missing_initial_tag_versions!
-    say_with_time 'Backfilling missing initial tag versions' do
-      tag_rows = missing_initial_version_rows(MigrationTagVersion, nico: false)
-      nico_rows = missing_initial_version_rows(MigrationNicoTagVersion, nico: true)
-
-      MigrationTagVersion.insert_all!(tag_rows) if tag_rows.any?
-      MigrationNicoTagVersion.insert_all!(nico_rows) if nico_rows.any?
-
-      tag_rows.length + nico_rows.length
-    end
-  end
-
-  def missing_initial_version_rows version_class, nico:
-    first_versions =
-        version_class
-        .order(:tag_id, :version_no)
-        .to_a
-        .group_by(&:tag_id)
-        .transform_values(&:first)
-    rows = []
-
-    MigrationTag.find_each do |tag|
-      next if (tag.category == 'nico') != nico
-
-      first_version = first_versions[tag.id]
-      next unless first_version
-      next if valid_initial_version?(first_version)
-
-      assert_inferable_initial_version!(tag, first_version)
-      rows << initial_version_row(tag, first_version, nico:)
-    end
-
-    rows
-  end
-
-  def valid_initial_version? version
-    version.version_no == 1 && version.event_type == 'create'
-  end
-
-  def assert_inferable_initial_version! tag, version
-    inferable =
-        version.version_no == 2 &&
-        version.event_type == 'discard' &&
-        tag.created_at < version.created_at
-    return if inferable
-
-    details = [
-        "tag_id=#{ tag.id }",
-        "version_no=#{ version.version_no }",
-        "event_type=#{ version.event_type.inspect }",
-        "tag_created_at=#{ tag.created_at.iso8601(6) }",
-        "version_created_at=#{ version.created_at.iso8601(6) }"]
-
-    raise "Cannot infer initial tag version: #{ details.join(', ') }"
-  end
-
-  def initial_version_row tag, discard_version, nico:
-    row = {
-        tag_id: tag.id,
-        version_no: 1,
-        event_type: 'create',
-        name: discard_version.name,
-        created_at: tag.created_at,
-        created_by_user_id: nil }
-
-    if nico
-      return row.merge(linked_tags: discard_version.linked_tags)
-    end
-
-    row.merge(
-        category: discard_version.category,
-        aliases: discard_version.aliases,
-        parent_tag_ids: discard_version.parent_tag_ids,
-        deprecated_at: discard_version.deprecated_at)
-  end
-
-  def build_intervals_by_name
-    intervals_by_name = Hash.new { |hash, name| hash[name] = [] }
-    versions_by_kind = {
-        tag: versions_by_tag_id(MigrationTagVersion),
-        nico: versions_by_tag_id(MigrationNicoTagVersion) }
-    current_names = current_names_by_tag_id
-
-    MigrationTag.find_each do |tag|
-      nico = tag.category == 'nico'
-      kind = nico ? :nico : :tag
-      versions = versions_by_kind.fetch(kind).fetch(tag.id, [])
-
-      intervals_for(
-          tag,
-          versions,
-          current_name: current_names.fetch(tag.id),
-          nico:).each do |interval|
-        name = interval.delete(:name)
-        intervals_by_name[name] << interval
-      end
-    end
-
-    intervals_by_name
-  end
-
-  def versions_by_tag_id version_class
-    version_class
-        .order(:tag_id, :version_no)
-        .to_a
-        .group_by(&:tag_id)
-  end
-
-  def current_names_by_tag_id
-    MigrationTagName
-        .joins('INNER JOIN tags ON tags.tag_name_id = tag_names.id')
-        .pluck('tags.id', 'tag_names.name')
-        .to_h
-  end
-
-  def intervals_for tag, versions, current_name:, nico:
-    if versions.empty?
-      return [{
-          name: current_name,
-          tag_id: tag.id,
-          version_no: tag.version_no,
-          category: nico ? 'nico' : tag.category,
-          from: tag.created_at,
-          to: tag.discarded_at }]
-    end
-
-    versions.each_with_index.filter_map do |version, index|
-      next if version.event_type == 'discard'
-
-      {
-          name: version.name,
-          tag_id: tag.id,
-          version_no: version.version_no,
-          category: nico ? 'nico' : version.category,
-          from: version.created_at,
-          to: versions[index + 1]&.created_at || tag.discarded_at }
-    end
-  end
-
-  def build_tags_json version, intervals_by_name
-    entries = version.tags.to_s.split.map do |literal|
-      name = tag_name_from_literal(literal)
-      interval = resolve_tag!(intervals_by_name.fetch(name, []), name:, version:)
-
-      { 'id' => interval.fetch(:tag_id),
-        'version_no' => interval.fetch(:version_no),
-        'name' => name,
-        'category' => interval.fetch(:category),
-        'sections' => [] }
-    end
-
-    assert_unique_tag_ids!(version, entries)
-
-    entries.sort_by { |entry| entry.fetch('id') }
-  end
-
-  def tag_name_from_literal literal
-    name = literal.dup
-    name.sub!(SECTION_LITERAL_PATTERN, '') while name.match?(
-        SECTION_LITERAL_PATTERN)
-
-    if name.empty? || name.include?('[') || name.include?(']')
-      raise "Invalid legacy tag literal: #{ literal.inspect }"
-    end
-
-    name
-  end
-
-  def resolve_tag! intervals, name:, version:
-    time = version.created_at
-    candidates = intervals.select do |interval|
-      interval.fetch(:from) <= time &&
-        (interval[:to].nil? || time < interval.fetch(:to))
-    end
-
-    candidates = future_candidates(intervals, time) if candidates.empty?
-
-    return candidates.first if candidates.one?
-
-    candidate_versions = candidates.map do |candidate|
-      [candidate.fetch(:tag_id), candidate.fetch(:version_no)]
-    end
-    details = [
-        "post_version_id=#{ version.id }",
-        "post_id=#{ version.post_id }",
-        "name=#{ name.inspect }",
-        "created_at=#{ time.iso8601(6) }",
-        "candidates=#{ candidate_versions.inspect }"].join(', ')
-
-    raise "Could not resolve tag snapshot: #{ details }"
-  end
-
-  def future_candidates intervals, time
-    candidates = intervals.select do |interval|
-      interval.fetch(:from) > time &&
-        interval.fetch(:from) <= time + RESOLUTION_GRACE
-    end
-    return [] if candidates.empty?
-
-    nearest_from = candidates.map { |interval| interval.fetch(:from) }.min
-
-    candidates.select do |interval|
-      interval.fetch(:from) == nearest_from
-    end
-  end
-
-  def assert_unique_tag_ids! version, entries
-    duplicate_tag_ids =
-        entries
-        .map { |entry| entry.fetch('id') }
-        .tally
-        .select { |_tag_id, count| count > 1 }
-        .keys
-    return if duplicate_tag_ids.empty?
-
-    details = [
-        "post_version_id=#{ version.id }",
-        "duplicate_tag_ids=#{ duplicate_tag_ids.inspect }"].join(', ')
-
-    raise "Duplicate tag IDs: #{ details }"
-  end
-
-  def assert_backfill_complete!
-    missing_count = MigrationPostVersion.where(tags_json: nil).count
-    return if missing_count.zero?
-
-    raise "#{ missing_count } post versions were not backfilled"
-  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_07_27_123600) do
+ActiveRecord::Schema[8.0].define(version: 2026_07_13_000000) 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
@@ -349,7 +349,6 @@ ActiveRecord::Schema[8.0].define(version: 2026_07_27_123600) do
     t.string "url", limit: 768, null: false
     t.string "thumbnail_base", limit: 2000
     t.text "tags", null: false
-    t.json "tags_json", null: false
     t.text "parent_post_ids", null: false
     t.datetime "original_created_from"
     t.datetime "original_created_before"

backend/spec/requests/tags_deerjikists_spec.rb

@@ -226,6 +226,14 @@ RSpec.describe 'Tags deerjikists API', type: :request do
               [platform2, code2],
             )
         end
+
+        it 'locks the tag before replacing the complete list' do
+          expect_any_instance_of(Tag).to receive(:lock!).and_call_original
+
+          do_request
+
+          expect(response).to have_http_status(:ok)
+        end
       end
 
       context 'when tag already has deerjikists' do
@@ -299,6 +307,81 @@ RSpec.describe 'Tags deerjikists API', type: :request do
         end
       end
 
+      context 'when platform is outside the enum' do
+        let(:payload) do
+          [
+            { platform: 'invalid', code: code1 },
+          ]
+        end
+
+        it 'returns 422 with an indexed platform error without changing the list' do
+          Deerjikist.create!(platform: platform1, code: code1, tag: tag)
+
+          expect {
+            do_request
+          }.not_to change { Deerjikist.where(tag: tag).map { |d| [d.platform, d.code] } }
+
+          expect(response).to have_http_status(:unprocessable_entity)
+          expect(json.fetch('errors')).to include(
+            'deerjikists.0.platform' => [be_present],
+          )
+        end
+      end
+
+      context 'when a requested deerjikist belongs to another tag' do
+        let!(:other_tag) { create(:tag, category: :deerjikist) }
+        let!(:owned_deerjikist) do
+          Deerjikist.create!(platform: platform1, code: code1, tag: tag)
+        end
+        let!(:conflicting_deerjikist) do
+          Deerjikist.create!(platform: platform2, code: code2, tag: other_tag)
+        end
+        let(:payload) do
+          [
+            { platform: 'nico', code: 'new-code' },
+            { platform: platform2, code: code2 },
+          ]
+        end
+
+        before do
+          other_tag.tag_name.update!(name: 'existing-deerjikist')
+        end
+
+        it 'returns an indexed 422 error and rolls back the complete replacement' do
+          expect {
+            do_request
+          }.not_to change { Deerjikist.order(:platform, :code).pluck(:platform, :code, :tag_id) }
+
+          expect(response).to have_http_status(:unprocessable_entity)
+          expect(json.fetch('errors')).to include(
+            'deerjikists.1.code' => [include('existing-deerjikist')],
+          )
+          expect(owned_deerjikist.reload.tag_id).to eq(tag.id)
+          expect(conflicting_deerjikist.reload.tag_id).to eq(other_tag.id)
+        end
+      end
+
+      context 'when a requested deerjikist already belongs to the same tag' do
+        let!(:existing_deerjikist) do
+          Deerjikist.create!(platform: platform1, code: code1, tag: tag)
+        end
+        let(:payload) do
+          [
+            { platform: platform1, code: code1 },
+          ]
+        end
+
+        it 'keeps the existing row' do
+          expect {
+            do_request
+          }.not_to change { existing_deerjikist.reload.created_at }
+
+          expect(response).to have_http_status(:ok)
+          expect(Deerjikist.where(tag: tag).pluck(:platform, :code))
+            .to eq([[platform1, code1]])
+        end
+      end
+
       context 'when youtube code is handle' do
         let(:channel_id) { 'UCabcdefghijklmnopqrstuv' }
         let(:payload) do