#399

AGENTS.md

@@ -574,32 +574,8 @@ and layout reuse, follow `frontend/AGENTS.md`.
   wording and placement before implementing it.
 - Do not invent replacement copy when removing unrequested wording.
 - Do not create, modify, or run tests unless the user explicitly asks for
-  test work. When the user asks for tests, keep working within the permitted
+  test work. When the user asks for tests, keep working and rerun them until
-  test-file scope and rerun them until they pass or the remaining failure is
+  they pass or the remaining failure is clearly blocked.
-  clearly blocked.
-- Test-only work includes adding, updating, deleting, reorganising, or fixing
-  SyntaxError in tests. During test-only work, do not modify production code.
-- During test-only work, do not change production constants, behaviour, API
-  contracts, validation, routes, authentication, permissions, UI, copy,
-  dependencies, limits, thresholds, defaults, migrations, schema, or
-  environment settings to satisfy tests.
-- Do not make production code match failing tests, mock assumptions, fixtures,
-  snapshots, old expectations, or stale setup. This includes changing
-  production constants merely because a test expects a different value.
-- If test work reveals a production bug, spec mismatch, or missing behaviour,
-  stop without modifying production code and report: the failing test or
-  discovered issue, the related production file, the actual behaviour, the
-  expected behaviour, and why a production change appears necessary.
-- Modify production code for test failures only when the user explicitly asks
-  for that production change. Do not expand a test task into a production task
-  on your own authority.
-- If the user explicitly asks for both production implementation and test
-  updates, implement production code to the confirmed specification first,
-  then add or update tests to verify that specification. Never roll production
-  behaviour back to satisfy old tests.
-- If it is unclear whether the test or the production implementation is stale,
-  or a test cannot be corrected without changing production code, ask the user
-  instead of guessing.
 
 ## Backend rules
 

backend/app/controllers/application_controller.rb

@@ -85,11 +85,4 @@ class ApplicationController < ActionController::API
                    base_errors: },
            status:
   end
-
-  def normalise_json value
-    return nil if value.nil?
-    return JSON.parse(value) if value.is_a?(String)
-
-    value
-  end
 end

backend/app/controllers/post_versions_controller.rb

@@ -1,7 +1,7 @@
 class PostVersionsController < ApplicationController
   def index
     post_id = params[:post].presence
-    tag_id = params[:tag].presence&.to_i
+    tag_id = params[:tag].presence
     page = (params[:page].presence || 1).to_i
     limit = (params[:limit].presence || 20).to_i
 
@@ -10,6 +10,12 @@ class PostVersionsController < ApplicationController
 
     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
@@ -17,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.*',
+        .select('post_versions.*', 'prev.title AS prev_title', 'prev.url AS prev_url',
-                'prev.title AS prev_title',
+                'prev.thumbnail_base AS prev_thumbnail_base', 'prev.tags AS prev_tags',
-                'prev.url AS prev_url',
-                'prev.thumbnail_base AS prev_thumbnail_base',
-                'prev.tags_json AS prev_tags_json',
                 '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_id
+    if tag_name
-      q = q.where(("JSON_CONTAINS(post_versions.tags_json, JSON_OBJECT('id', :tag_id)) " +
+      escaped = ActiveRecord::Base.sanitize_sql_like(tag_name.name)
-                   "OR JSON_CONTAINS(prev.tags_json, JSON_OBJECT('id', :tag_id))"),
+      q = q.where(("CONCAT(' ', post_versions.tags, ' ') LIKE :kw " +
-                  tag_id:)
+                   "OR CONCAT(' ', prev.tags, ' ') LIKE :kw"),
+                  kw: "% #{ escaped } %")
     end
 
     count = q.except(:select, :order, :limit, :offset).count
@@ -48,35 +52,51 @@ class PostVersionsController < ApplicationController
     users_by_id = User.where(id: user_ids).pluck(:id, :name).to_h
 
     rows.map do |row|
-      cur_tags =
+      cur_tags = split_tags(row.tags)
-          normalise_json(row.tags_json)
+      prev_tags = split_tags(row.attributes['prev_tags'])
-          .map { Post.tag_snapshot_literal(_1) }
-      prev_tags =
-          (normalise_json(row.attributes['prev_tags_json']) || [])
-          .map { Post.tag_snapshot_literal(_1) }
 
-      { 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'] },
+        title: {
-        url: { current: row.url, prev: row.attributes['prev_url'] },
+          current: row.title,
-        thumbnail: { current: nil, prev: nil },
+          prev: row.attributes['prev_title']
-        thumbnail_base: { current: row.thumbnail_base,
+        },
-                          prev: row.attributes['prev_thumbnail_base'] },
+        url: {
-        video_ms: { current: row.video_ms, prev: row.attributes['prev_video_ms'] },
+          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,
+          current: row.original_created_from&.iso8601,
-            prev: row.attributes['prev_original_created_from']&.iso8601 },
+          prev: row.attributes['prev_original_created_from']&.iso8601
+        },
         original_created_before: {
-            current: row.original_created_before&.iso8601,
+          current: row.original_created_before&.iso8601,
-            prev: row.attributes['prev_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
+          if row.created_by_user_id
-              { id: row.created_by_user_id,
+            {
-                name: users_by_id[row.created_by_user_id] }
+              id: row.created_by_user_id,
-            end }
+              name: users_by_id[row.created_by_user_id]
+            }
+          end
+      }
     end
   end
 
@@ -97,4 +117,8 @@ class PostVersionsController < ApplicationController
       }
     end
   end
+
+  def split_tags(tags)
+    tags.to_s.split(/\s+/).reject(&:blank?)
+  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
+    version.tags.to_s.split.reject { |name| name.downcase.start_with?('nico:') }.sort
-        .reject { _1.fetch('category') == 'nico' }
-        .map { Post.tag_snapshot_literal(_1) }
-        .sort
   end
 
   def post_snapshot_from_record post

backend/app/models/post.rb

@@ -137,34 +137,6 @@ class Post < ApplicationRecord
     end
   end
 
-  def self.tag_snapshot_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 snapshot_tags_json
-    post_tags
-        .kept
-        .joins(tag: :tag_name)
-        .includes(:sections, tag: :tag_name)
-        .order('tags.id')
-        .map do |pt|
-      { 'id' => pt.tag.id,
-        'version_no' => pt.tag.version_no,
-        'name' => pt.tag.name,
-        'category' => pt.tag.category,
-        'sections' => pt.sections.sort_by(&:begin_ms).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,299 +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
-
-    change_column_null :post_versions, :tags_json, false
-
-    schema = connection.quote(JSON.generate({
-        type: 'array',
-        items: { type: 'object',
-                 properties: { id: { type: 'integer', minimum: 1 },
-                               version_no: { type: 'integer', minimum: 1 },
-                               name: { type: 'string', minLength: 1 },
-                               category: { type: 'string', enum: ['deerjikist',
-                                                                  'meme',
-                                                                  'character',
-                                                                  'general',
-                                                                  'material',
-                                                                  'meta',
-                                                                  'nico'] },
-                               sections: { type: 'array',
-                                           items: { type: 'object',
-                                                    properties: { begin_ms: { type: 'integer',
-                                                                              minimum: 0 },
-                                                                  end_ms: { type: ['integer',
-                                                                                   'null'],
-                                                                            minimum: 0 } },
-                                                    required: ['begin_ms', 'end_ms'],
-                                                    additionalProperties: false } } },
-                 required: ['id', 'version_no', 'name', 'category', 'sections'],
-                 additionalProperties: false } }))
-
-    add_check_constraint :post_versions,
-                         "JSON_SCHEMA_VALID(#{ schema }, tags_json)",
-                         name: 'chk_post_versions_tags_json_schema'
-  end
-
-  def down
-    remove_check_constraint :post_versions, name: 'chk_post_versions_tags_json_schema'
-
-    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
-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"
@@ -363,7 +362,6 @@ ActiveRecord::Schema[8.0].define(version: 2026_07_27_123600) do
     t.check_constraint "(`video_ms` is null) or (`video_ms` > 0)", name: "chk_post_versions_video_ms_positive"
     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"
-    t.check_constraint "json_schema_valid(_utf8mb4'{\"type\":\"array\",\"items\":{\"type\":\"object\",\"properties\":{\"id\":{\"type\":\"integer\",\"minimum\":1},\"version_no\":{\"type\":\"integer\",\"minimum\":1},\"name\":{\"type\":\"string\",\"minLength\":1},\"category\":{\"type\":\"string\",\"enum\":[\"deerjikist\",\"meme\",\"character\",\"general\",\"material\",\"meta\",\"nico\"]},\"sections\":{\"type\":\"array\",\"items\":{\"type\":\"object\",\"properties\":{\"begin_ms\":{\"type\":\"integer\",\"minimum\":0},\"end_ms\":{\"type\":[\"integer\",\"null\"],\"minimum\":0}},\"required\":[\"begin_ms\",\"end_ms\"],\"additionalProperties\":false}}},\"required\":[\"id\",\"version_no\",\"name\",\"category\",\"sections\"],\"additionalProperties\":false}}',`tags_json`)", name: "chk_post_versions_tags_json_schema"
   end
 
   create_table "posts", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci", force: :cascade do |t|

frontend/src/lib/postNewQueryState.test.ts

@@ -29,15 +29,15 @@ describe ('post new review URL state', () => {
 	.toEqual (['one', 'two'])
   })
 
-  it ('allows at most a 6 143 byte request target', () => {
+  it ('allows at most a 4095-byte request target', () => {
     const baseUrl = 'https://example.com/'
     const baseLength = postNewReviewPathByteLength ([baseUrl])
-    const allowed = `${ baseUrl }${ 'a'.repeat (6_143 - baseLength) }`
+    const allowed = `${ baseUrl }${ 'a'.repeat (4_095 - baseLength) }`
     const denied = `${ allowed }a`
 
-    expect (postNewReviewPathByteLength ([allowed])).toBe (6_143)
+    expect (postNewReviewPathByteLength ([allowed])).toBe (4_095)
     expect (isPostNewReviewPathWithinLimit ([allowed])).toBe (true)
-    expect (postNewReviewPathByteLength ([denied])).toBe (6_144)
+    expect (postNewReviewPathByteLength ([denied])).toBe (4_096)
     expect (isPostNewReviewPathWithinLimit ([denied])).toBe (false)
   })
 })

frontend/src/lib/postNewQueryState.ts

@@ -1,5 +1,5 @@
 const POST_NEW_REVIEW_PATH_PREFIX = '/posts/new?urls='
-const MAX_POST_NEW_REVIEW_TARGET_BYTES = 6_144
+const MAX_POST_NEW_REVIEW_TARGET_BYTES = 4_096
 
 const textEncoder = new TextEncoder ()
 

frontend/src/pages/posts/PostImportSourcePage.test.tsx

@@ -60,7 +60,7 @@ describe ('PostImportSourcePage', () => {
     const input = screen.getByRole ('textbox', { name: '' })
 
     fireEvent.change (input, {
-      target: { value: `https://example.com/${ 'a'.repeat (6_200) }` } })
+      target: { value: `https://example.com/${ 'a'.repeat (4_100) }` } })
 
     expect (screen.getByRole ('button', { name: '次へ' })).toBeDisabled ()
   })