AI: address issue #445

.gitea/ISSUE_TEMPLATE.md

@@ -1,35 +0,0 @@
-## 背景
-
-なぜ必要か。
-
-## 対象範囲
-
-- backend:
-- frontend:
-- docs:
-- migration:
-
-## やること
-
-- [ ] 
-
-## 受け入れ条件
-
-- [ ] 
-
-## 実行すべき確認
-
-- [ ] `cd backend && bundle exec rspec`
-- [ ] `cd frontend && npm run build`
-- [ ] `cd frontend && npm run lint`
-
-## 禁止事項
-
-- unrelated refactor はしない
-- 既存 API response shape を壊さない
-- 認証・認可・BAN を弱めない
-
-## Codex への指示
-
-この issue を読んで実装してください。
-不明点があれば、実装前に調査結果と選択肢を提示してください。

backend/app/controllers/post_versions_controller.rb

@@ -100,18 +100,9 @@ class PostVersionsController < ApplicationController
 
   def external_tag_names_for snapshots
     ids = snapshots.flatten.filter_map { _1['external_tag_id'] }.uniq
-    names = ExternalTag.where(id: ids).pluck(:id, :platform, :name).to_h { |id, platform, name|
+    ExternalTag.where(id: ids).pluck(:id, :platform, :name).to_h { |id, platform, name|
       [id, "#{ platform }:#{ name }"]
     }
-    missing_ids = ids - names.keys
-
-    HistoryAccess.visible(NicoTagVersion.all)
-        .where(tag_id: missing_ids)
-        .order(:tag_id, version_no: :desc)
-        .pluck(:tag_id, :name)
-        .each { |id, name| names[id] ||= name }
-
-    names
   end
 
   def snapshot_tag_literals snapshots, tag_names, external_tag_names

backend/app/models/external_tag.rb

@@ -1,8 +1,11 @@
 class ExternalTag < ApplicationRecord
   before_create { SharedResourceIdAllocator.assign!(self) }
+  # Must run before dependent association callbacks.
+  before_destroy :prevent_destroy
 
   enum :platform, nico: 'nico'
   validates :platform, presence: true, inclusion: { in: ExternalTag.platforms.keys }
+  validate :immutable_identity, on: :update
 
   has_many :post_external_tags, dependent: :delete_all
   has_many :posts, through: :post_external_tags
@@ -16,11 +19,21 @@ class ExternalTag < ApplicationRecord
 
   has_many :linked_tags, through: :nico_tag_relations, source: :tag
 
-  def snapshot_linked_tag_names language_code = 'ja'
-    linked_tags
-        .joins(:tag_names)
-        .where(tag_names: { language_code:, primary_flg: true })
-        .order('tag_names.name')
-        .pluck('tag_names.name')
+  def snapshot_linked_tag_ids = linked_tags.distinct.order(:id).pluck(:id)
+
+  def delete
+    raise ActiveRecord::ReadOnlyRecord, '外部タグは削除できません．'
+  end
+
+  private
+
+  def immutable_identity
+    errors.add(:platform, 'は変更できません．') if will_save_change_to_platform?
+    errors.add(:name, 'は変更できません．') if will_save_change_to_name?
+  end
+
+  def prevent_destroy
+    errors.add(:base, '外部タグは削除できません．')
+    throw(:abort)
   end
 end

backend/app/models/history_access.rb

@@ -106,21 +106,7 @@ class HistoryAccess < ActiveSupport::CurrentAttributes
              snapshot_reference(TagVersion, row),
              snapshot_reference(NicoTagVersion, row, external: true)]
     when 'nico_tag_versions'
-      # Legacy linked_tags snapshots contain names, not IDs. Match recorded names
-      # conservatively; never expose a private linked name through an external tag.
-      names = "JSON_MERGE_PRESERVE(JSON_ARRAY(private_tag.name), " +
-          "COALESCE(JSON_EXTRACT(private_tag.names, '$**.name'), JSON_ARRAY()))"
-      # DISTINCT prevents merging this independent name set into the correlated scan.
-      # Keep byte-distinct names even when the column collation considers them equal.
-      private_names = 'SELECT DISTINCT private_name.name, ' +
-          'CAST(private_name.name AS BINARY) AS name_bytes FROM tag_versions private_tag ' +
-          "JOIN (#{ discarded_keys('tag_versions', 'tag_id') }) blocked " +
-          'ON blocked.tag_id = private_tag.tag_id ' +
-          "JOIN JSON_TABLE(#{ names }, '$[*]' COLUMNS (name TEXT PATH '$')) private_name " +
-          "WHERE private_name.name <> ''"
-      ["NOT EXISTS (SELECT 1 FROM (#{ private_names }) private_name WHERE " +
-          "LOCATE(CONCAT(' ', private_name.name, ' '), " +
-          "CONCAT(' ', COALESCE(#{ row }.linked_tags, ''), ' ')) > 0)"]
+      [list_reference('tag_versions', 'tag_id', "#{ row }.linked_tag_ids")]
     when 'tag_versions'
       [list_reference('tag_versions', 'tag_id', "#{ row }.parent_tag_ids")]
     when 'tag_description_versions'

backend/app/models/nico_tag_version.rb

@@ -3,5 +3,10 @@ class NicoTagVersion < ApplicationRecord
 
   belongs_to :external_tag, foreign_key: :tag_id, inverse_of: :nico_tag_versions
 
-  validates :name, presence: true
+  validates :event_type, inclusion: { in: ['create', 'update'] }
+
+  def name
+    tag = external_tag
+    "#{ tag.platform }:#{ tag.name }" if tag
+  end
 end

backend/app/representations/counts_version_repr.rb

@@ -139,6 +139,8 @@ class CountsVersionRepr
     result = {}
     table = model.table_name
     comparison = @inclusive ? '<=' : '<'
+    name = model == NicoTagVersion ?
+        CountsResourceDefinition.new('nico_tags').expression('name', table) : 'name'
     requests.each_slice(100) do |batch|
       queries = batch.map do |id, point|
         scope = HistoryAccess.visible(model.where(tag_id: id))
@@ -148,7 +150,8 @@ class CountsVersionRepr
                 "AND newer.version_no > #{ table }.version_no)", point)
         quoted = model.connection.quote(point.iso8601(6))
         names = model == TagVersion ? 'names' : 'JSON_OBJECT() AS names'
-        scope.select("tag_id, name, #{ names }, #{ quoted } AS reference_point").to_sql
+        scope.select("tag_id, #{ name } AS name, #{ names }, " +
+            "#{ quoted } AS reference_point").to_sql
       end
       next if queries.empty?
 

backend/app/services/counts_candidates.rb

@@ -6,14 +6,8 @@ class CountsCandidates
 
   def initialize(params)
     QueryState.validate!(params, KEYS)
-    definition = CountsResourceDefinition.new(params.fetch('resource'))
-    fields = ['resource_id', 'user']
-    fields += ['tags', 'tags_json', 'tags.id'] if definition.resource == 'posts'
-    fields << 'members' if definition.resource == 'tag_groups'
-    if definition.resource == 'materials'
-      fields += ['updated_by_user_id', 'file_blob_id', 'source_file_id', 'drive_file_id']
-    end
-    raise ArgumentError unless fields.include?(params.fetch('field'))
+    raise ArgumentError unless params.fetch('resource') == 'posts'
+    raise ArgumentError unless ['tags', 'tags_json'].include?(params.fetch('field'))
 
     @params = params
     @page = Integer(params.fetch('page', '1'), 10)
@@ -24,28 +18,28 @@ class CountsCandidates
   end
 
   def call
-    references = ['tags', 'tags_json', 'tags.id', 'members']
-    return resource_candidates unless references.include?(@params['field'])
-
     options = @params.slice('locale', 'at', 'execution_at', 'timezone')
         .merge('definition' => 'latest', 'resource' => 'tags')
     internal = CountsPopulation.new(options)
-    groups_flg = @params['field'] == 'members'
-    external = CountsPopulation.new(options.merge(
-        'resource' => groups_flg ? 'tag_groups' : 'nico_tags',
+    external = CountsPopulation.new(options.merge('resource' => 'nico_tags',
         'execution_at' => internal.at.iso8601(6)))
     connection = TagVersion.connection
     sources = [[internal, 'internal', 'v.category', 'v.names'],
-        [external, groups_flg ? 'group' : 'external', 'NULL', 'JSON_OBJECT()']]
+               [external, 'external', 'NULL', 'JSON_OBJECT()']]
     sources = sources.map do |population, kind, category, names|
       scope = population.scope.except(:select)
-      identity = "v.#{ population.definition.keys.first }"
-      scope = search(scope, identity, 'v.name', names)
-      value = ['tags.id', 'members'].include?(@params['field']) ?
-          "CAST(#{ identity } AS CHAR)" : "CONCAT('#{ kind }:', #{ identity })"
+      name = population.definition.expression('name')
+      if @params['query'].present?
+        pattern = "%#{ ApplicationRecord.sanitize_sql_like(@params['query']) }%"
+        scope = scope.where("(#{ name } LIKE ? OR CAST(v.tag_id AS CHAR) = ? OR EXISTS (" +
+            "SELECT 1 FROM JSON_TABLE(COALESCE(JSON_EXTRACT(#{ names }, '$**.name'), " +
+            "JSON_ARRAY()), '$[*]' COLUMNS (name TEXT PATH '$')) candidate_name " +
+            'WHERE candidate_name.name LIKE ?))', pattern, @params['query'], pattern)
+      end
       scope.select(Arel.sql(
-          "#{ value } AS value, #{ identity } AS id, " +
-          "v.name AS name, #{ names } AS names, #{ category } AS category, '#{ kind }' AS kind"))
+          "CONCAT('#{ kind }:', v.tag_id) AS value, v.tag_id AS id, " +
+          "#{ name } AS name, #{ names } AS names, " +
+          "#{ category } AS category, '#{ kind }' AS kind"))
           .to_sql
     end
     clauses = []
@@ -63,63 +57,4 @@ class CountsCandidates
     { rows: rows.first(LIMIT), page: @page, more: rows.length > LIMIT,
       execution_at: internal.at.iso8601(6) }
   end
-
-  private
-
-  def search(scope, identity, name, names)
-    return scope if @params['query'].blank?
-
-    pattern = "%#{ ApplicationRecord.sanitize_sql_like(@params['query']) }%"
-    scope.where("(#{ name } LIKE ? OR CAST(#{ identity } AS CHAR) = ? OR EXISTS (" +
-        "SELECT 1 FROM JSON_TABLE(COALESCE(JSON_EXTRACT(#{ names }, '$**.name'), " +
-        "JSON_ARRAY()), '$[*]' COLUMNS (name TEXT PATH '$')) candidate_name " +
-        'WHERE candidate_name.name LIKE ?))', pattern, @params['query'], pattern)
-  end
-
-  def resource_candidates
-    options = @params.slice('at', 'execution_at', 'timezone')
-        .merge('resource' => @params.fetch('resource'), 'definition' => 'latest')
-    actor_flg = ['user', 'updated_by_user_id'].include?(@params['field'])
-    options['definition'] = 'history' if actor_flg
-    population = CountsPopulation.new(options)
-    scope = population.scope.except(:select)
-    if actor_flg
-      point = population.timestamp(@params['at']) || population.at
-      raise ArgumentError if point > population.at
-
-      column = @params['field'] == 'user' ? 'created_by_user_id' : 'updated_by_user_id'
-      identities = scope.where('v.created_at <= ?', point).select("v.#{ column }")
-      scope = User.where(id: identities)
-      identity = 'users.id'
-      name = 'users.name'
-      names = 'JSON_OBJECT()'
-    else
-      definition = population.definition
-      column = @params['field'] == 'resource_id' ? definition.keys.first : @params['field']
-      if column == 'drive_file_id'
-        column = 'source_file_id'
-        scope = scope.where('v.source_kind' => 'google_drive_file')
-      end
-      identity = "v.#{ column }"
-      label = ['title', 'name', 'url'].find { definition.fields.key?(_1) }
-      name = label ? "v.#{ label }" : 'NULL'
-      names = definition.resource == 'tags' ? 'v.names' : 'JSON_OBJECT()'
-      if ['file_blob_id', 'source_file_id', 'drive_file_id'].include?(@params['field'])
-        scope = scope.where.not("v.#{ column }" => nil).group(Arel.sql(identity))
-        name = 'v.file_filename'
-      end
-    end
-    scope = search(scope, identity, name, names)
-    title = ['file_blob_id', 'source_file_id', 'drive_file_id'].include?(@params['field']) ?
-        "MIN(#{ name })" : name
-    query = scope.optimizer_hints('MAX_EXECUTION_TIME(10000)').distinct
-        .select(Arel.sql("#{ identity } AS id, #{ title } AS name, #{ names } AS names"))
-        .order(Arel.sql(identity)).limit(LIMIT + 1).offset((@page - 1) * LIMIT)
-    rows = scope.klass.connection.select_all(query.to_sql).to_a.map do |values|
-      values['names'] = JSON.parse(values['names']) if values['names'].is_a?(String)
-      values.merge('value' => values.fetch('id').to_s)
-    end
-    { rows: rows.first(LIMIT), page: @page, more: rows.length > LIMIT,
-      execution_at: population.at.iso8601(6) }
-  end
 end

backend/app/services/counts_population.rb

@@ -113,7 +113,7 @@ class CountsPopulation
     else
       raise ArgumentError unless definition.axes(history).include?(key)
 
-      scope = scope.where(definition.expression(key) => entry['value'])
+      scope = scope.where("#{ definition.expression(key) } <=> ?", entry['value'])
     end
     scope
   end

backend/app/services/counts_resource_definition.rb

@@ -105,12 +105,25 @@ class CountsResourceDefinition
   def time_column = 'created_at'
   def column(key)
     raise ArgumentError unless fields.key?(key)
+    # Keep the V1 Counts field name at the API boundary only.
+    return 'linked_tag_ids' if resource == 'nico_tags' && key == 'linked_tags'
 
     { 'resource_id' => keys.first, 'user' => 'created_by_user_id' }.fetch(key, key)
   end
 
-  def expression(key, row = 'v') = "#{ row }.#{ column(key) }"
-  def changes = fields.keys - COMMON.keys - ['updated_at', 'updated_by_user_id']
+  def expression(key, row = 'v')
+    if resource == 'nico_tags' && key == 'name'
+      return "(SELECT CONCAT(external_tags.platform, ':', external_tags.name) " +
+          "FROM external_tags WHERE external_tags.id = #{ row }.tag_id)"
+    end
+
+    "#{ row }.#{ column(key) }"
+  end
+
+  def changes
+    columns = fields.keys - COMMON.keys - ['updated_at', 'updated_by_user_id']
+    resource == 'nico_tags' ? columns - ['name'] : columns
+  end
   def axes(history)
     fields.reject { |_key, type| type == :json }.keys +
         (resource == 'posts' ? ['tags'] : []) + (history ? ['changed_field'] : [])
@@ -118,7 +131,9 @@ class CountsResourceDefinition
 
   def metadata
     fields.map do |key, type|
-      { key:, type:, nullable: model.columns_hash.fetch(column(key)).null,
+      nullable = (resource == 'nico_tags' && key == 'name') ||
+          model.columns_hash.fetch(column(key)).null
+      { key:, type:, nullable:,
         operators: OPERATORS.fetch(type, []) }
     end
   end

backend/app/services/counts_state_query.rb

@@ -201,6 +201,8 @@ class CountsStateQuery
   end
 
   def listing
+    external_name = CountsResourceDefinition.new('nico_tags')
+        .expression('name', 'external_version')
     catalog = <<~SQL
       , tag_counts AS (
         SELECT membership.kind, membership.tag_id, COUNT(*) AS post_count
@@ -209,7 +211,7 @@ class CountsStateQuery
         SELECT 'internal' AS kind, tag_id, name, names,
           deprecated_at FROM tag_at WHERE event_type <> 'discard'
         UNION ALL
-        SELECT tc.kind, tc.tag_id, external_version.name, JSON_OBJECT() AS names,
+        SELECT tc.kind, tc.tag_id, #{ external_name } AS name, JSON_OBJECT() AS names,
           NULL AS deprecated_at
         FROM tag_counts tc
         LEFT JOIN tag_at internal_version

backend/app/services/counts_table_query.rb

@@ -182,6 +182,7 @@ class CountsTableQuery
     order = sort_sql { detail_expression(_1) }
     query = query.order(Arel.sql((order + ['v.id ASC']).join(', ')))
         .limit(limit).offset((page - 1) * limit)
+    query = query.preload(:external_tag) if @definition.resource == 'nico_tags'
     rows = query.to_a
     if ['posts', 'tags'].include?(@definition.resource)
       point = population.history ? population.at :
@@ -486,6 +487,7 @@ class CountsTableQuery
 
       table = model.table_name
       scope = HistoryAccess.visible(model.where(tag_id: ids)).where('created_at <= ?', point)
+      scope = scope.includes(:external_tag) if model == NicoTagVersion
       bound = @connection.quote(point)
       if population.history && @definition.resource == 'posts'
         key = kind == 'internal' ? 'tag_id' : 'external_tag_id'

backend/app/services/nico_tag_version_recorder.rb

@@ -1,4 +1,5 @@
 class NicoTagVersionRecorder < VersionRecorder
+  EVENT_TYPES = ['create', 'update'].freeze
   def self.record! external_tag:, event_type:, created_by_user:
     new(external_tag:, event_type:, created_by_user:).record!
   end
@@ -14,8 +15,7 @@ class NicoTagVersionRecorder < VersionRecorder
   def record_key = :external_tag
 
   def snapshot_attributes
-    { name: "#{ @record.platform }:#{ @record.name }",
-      linked_tags: @record.snapshot_linked_tag_names.join(' ') }
+    { linked_tag_ids: @record.snapshot_linked_tag_ids.join(' ') }
   end
 
   def tracks_version_no_on_record? = false

backend/db/migrate/20261108000000_add_linked_tag_ids_to_nico_tag_versions.rb

@@ -0,0 +1,5 @@
+class AddLinkedTagIdsToNicoTagVersions < ActiveRecord::Migration[8.0]
+  def change
+    add_column :nico_tag_versions, :linked_tag_ids, :text
+  end
+end

backend/db/migrate/20261108010000_migrate_linked_tags_in_nico_tag_versions.rb

@@ -0,0 +1,42 @@
+class MigrateLinkedTagsInNicoTagVersions < ActiveRecord::Migration[8.0]
+  class MigrationTagVersion < ActiveRecord::Base
+    self.table_name = 'tag_versions'
+  end
+
+  class MigrationNicoTagVersion < ActiveRecord::Base
+    self.table_name = 'nico_tag_versions'
+  end
+
+  def up
+    # 除票外部タグの恢復はないので除票履歴があれば全消しでよいはず．
+    execute <<~SQL
+      DELETE
+        t1
+      FROM
+        nico_tag_versions t1
+        INNER JOIN
+          nico_tag_versions t2
+          ON
+            t1.tag_id = t2.tag_id
+            AND t2.event_type = 'discard'
+    SQL
+
+    MigrationNicoTagVersion.find_each do |ntv|
+      linked_tag_ids = ntv.linked_tags.split.map { |name|
+        # 厳密には一意ぢゃないけどめんだぅだからこれでいいや．
+        MigrationTagVersion
+            .order(created_at: :desc, id: :desc)
+            .find_by("names ->> '$.ja.name' = ?", name)
+            &.tag_id
+      }.compact.uniq.sort.join(' ')
+
+      ntv.update_columns(linked_tag_ids:)
+    end
+
+    change_column_null :nico_tag_versions, :linked_tag_ids, false
+  end
+
+  def down
+    raise ActiveRecord::IrreversibleMigration, '戻せません．'
+  end
+end

backend/db/migrate/20261108020000_remove_linked_tags_from_nico_tag_versions.rb

@@ -0,0 +1,6 @@
+class RemoveLinkedTagsFromNicoTagVersions < ActiveRecord::Migration[8.0]
+  def change
+    remove_column :nico_tag_versions, :linked_tags
+    remove_column :nico_tag_versions, :name
+  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_10_15_010000) do
+ActiveRecord::Schema[8.0].define(version: 2026_11_08_020000) 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
@@ -301,10 +301,9 @@ ActiveRecord::Schema[8.0].define(version: 2026_10_15_010000) do
     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.text "linked_tag_ids", null: false
     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 }

backend/spec/models/external_tag_spec.rb

@@ -11,28 +11,63 @@ RSpec.describe ExternalTag, type: :model do
     expect(external.reload.name).to eq('raw tag[]')
   end
 
-  it 'deletes post associations without deleting posts or version history' do
+  it 'does not allow a persisted external tag to change its platform or name' do
+    external = create(:external_tag, name: 'immutable')
+
+    expect { external.update!(name: 'renamed') }
+        .to raise_error(ActiveRecord::RecordInvalid)
+    expect(external.errors[:name]).to include('は変更できません．')
+
+    external.reload
+    external.platform = nil
+    expect { external.save! }.to raise_error(ActiveRecord::RecordInvalid)
+    expect(external.errors[:platform]).to include('は変更できません．')
+
+    expect(external.reload).to have_attributes(platform: 'nico', name: 'immutable')
+  end
+
+  it 'rejects delete, which bypasses destroy callbacks' do
+    external = create(:external_tag)
+
+    expect { external.delete }.to raise_error(ActiveRecord::ReadOnlyRecord)
+    expect(external.reload).to be_persisted
+  end
+
+  it 'increments the counter through post associations without changing identity' do
+    external = create(:external_tag, name: 'counter')
+    post = create(:post)
+
+    expect {
+      PostExternalTag.create!(post:, external_tag: external)
+    }.to change { external.reload.post_count }.by(1)
+
+    expect(external.reload).to have_attributes(platform: 'nico', name: 'counter')
+  end
+
+  it 'does not destroy external tags or their post associations and history' do
     external = create(:external_tag)
     post = create(:post)
     PostExternalTag.create!(post:, external_tag: external)
     version = NicoTagVersionRecorder.record!(
         external_tag: external, event_type: :create, created_by_user: nil)
 
-    external.destroy!
+    expect { external.destroy! }.to raise_error(ActiveRecord::RecordNotDestroyed)
 
-    expect(PostExternalTag.where(external_tag_id: external.id)).to be_empty
-    expect(post.reload.external_tags).to be_empty
+    expect(external.reload).to be_persisted
+    expect(PostExternalTag.exists?(post:, external_tag: external)).to be(true)
+    expect(post.reload.external_tags).to contain_exactly(external)
     expect(version.reload.tag_id).to eq(external.id)
   end
 
-  it 'deletes external links without deleting linked internal tags' do
+  it 'does not destroy an external tag or its links to internal tags' do
     external = create(:external_tag)
     tag = create(:tag)
-    NicoTagRelation.create!(nico_tag: external, tag:)
+    relation = NicoTagRelation.create!(nico_tag: external, tag:)
 
-    external.destroy!
+    expect { external.destroy! }.to raise_error(ActiveRecord::RecordNotDestroyed)
 
-    expect(NicoTagRelation.where(nico_tag_id: external.id)).to be_empty
+    expect(external.reload).to be_persisted
+    expect(relation.reload).to be_persisted
     expect(Tag.exists?(tag.id)).to be(true)
   end
 end

backend/spec/models/history_access_spec.rb

@@ -76,7 +76,7 @@ RSpec.describe 'Counts artifact policy and external references' do
     end
   end
 
-  it 'does not expose a private linked tag name through an external history snapshot' do
+  it 'does not expose a private linked tag ID through an external history snapshot' do
     tag = create(:tag, primary_name: 'private-linked-name')
     TagVersion.create!(tag:, version_no: 1, event_type: 'discard', name: 'private-linked-name',
         category: 'general', aliases: '', parent_tag_ids: '', created_at: Time.utc(2024),
@@ -84,7 +84,7 @@ RSpec.describe 'Counts artifact policy and external references' do
                        auto_generated: false, aliases: [] } })
     external = create(:external_tag, name: 'public-external')
     row = NicoTagVersion.create!(external_tag: external, version_no: 1, event_type: 'create',
-        name: 'nico:public-external', linked_tags: 'private-linked-name',
+        linked_tag_ids: tag.id.to_s,
         created_at: Time.utc(2024))
     post = create(:post)
     snapshot = PostVersion.create!(post:, version_no: 1, event_type: 'create',
@@ -99,7 +99,7 @@ RSpec.describe 'Counts artifact policy and external references' do
       expect(HistoryAccess.visible(NicoTagVersion.where(id: row.id))).to exist
     end
     public_row = NicoTagVersion.create!(external_tag: external, version_no: 2, event_type: 'update',
-        name: 'nico:public-external', linked_tags: 'private-linked-name-extra',
+        linked_tag_ids: '',
         created_at: Time.utc(2024, 7))
     HistoryAccess.set(user: nil) do
       expect(HistoryAccess.visible(NicoTagVersion.where(id: public_row.id))).to exist

backend/spec/models/shared_tag_ids_spec.rb

@@ -10,23 +10,22 @@ RSpec.describe 'Shared Tag and ExternalTag IDs', type: :model do
     expect(next_tag.id).to be > external.id
   end
 
-  it 'does not reuse physically deleted IDs in either table' do
+  it 'does not reuse IDs after internal deletion while external tags remain' do
     tag = create(:tag)
     external = create(:external_tag)
     TagName.where(tag_id: tag.id).delete_all
     tag.destroy!
-    external.destroy!
+    expect(ExternalTag.exists?(external.id)).to be(true)
 
     expect(create(:tag).id).to be > external.id
     expect(create(:external_tag).id).to be > external.id
   end
 
-  it 'retains existing overlapping IDs without renumbering on update or insert' do
+  it 'retains overlapping IDs after a tag update and a new allocation' do
     tag = create(:tag)
     external = create(:external_tag)
     external.update_columns(id: tag.id)
     tag.update!(category: :meta)
-    external.update!(name: 'updated_external')
     before_tag = tag.reload.attributes
     before_external = external.reload.attributes
 

backend/spec/models/version_record_spec.rb

@@ -47,24 +47,34 @@ RSpec.describe VersionRecord, type: :model do
       external_tag: nico_tag,
       version_no: 1,
       event_type: 'create',
-      name: "nico:#{ nico_tag.name }",
-      linked_tags: '',
+      linked_tag_ids: '',
       created_at: Time.current,
       created_by_user: nil
     )
 
     expect {
-      version.update!(name: 'nico:changed')
+      version.update!(linked_tag_ids: '123')
     }.to raise_error(ActiveRecord::ReadOnlyRecord)
   end
 
+
+  it 'rejects discard and restore versions for external tags' do
+    [:discard, :restore].each do |event_type|
+      version = NicoTagVersion.new(
+          external_tag: nico_tag, version_no: 1, event_type:,
+          linked_tag_ids: '', created_at: Time.current)
+
+      expect(version).not_to be_valid
+      expect(version.errors[:event_type]).not_to be_empty
+    end
+  end
+
   it 'prevents NicoTagVersion destroy' do
     version = NicoTagVersion.create!(
       external_tag: nico_tag,
       version_no: 1,
       event_type: 'create',
-      name: "nico:#{ nico_tag.name }",
-      linked_tags: '',
+      linked_tag_ids: '',
       created_at: Time.current,
       created_by_user: nil
     )

backend/spec/requests/counts_table_spec.rb

@@ -19,7 +19,7 @@ RSpec.describe 'Counts table API', type: :request do
         names: { ja: { name: 'alpha', script_code: 'Jpan', auto_generated: false,  aliases: [] } },
         created_at: Time.utc(2023))
     NicoTagVersion.create!(external_tag: external, version_no: 1,
-        event_type: 'create', name: 'beta', linked_tags: '', created_at: Time.utc(2023))
+        event_type: 'create', linked_tag_ids: '', created_at: Time.utc(2023))
     snapshots = [
         { tag_id: tag.id, version_no: 1, name: 'alpha', category: 'general', sections: [] },
         { external_tag_id: external.id }]
@@ -50,6 +50,115 @@ RSpec.describe 'Counts table API', type: :request do
 
   def versions(result) = result.fetch('versions').map { _1.fetch('version_no') }
 
+  context 'nico link ID snapshots through the V1 field name' do
+    let(:base) do
+      super().merge(resource: 'nico_tags')
+    end
+
+    before do
+      NicoTagVersion.create!(external_tag: external, version_no: 2,
+          event_type: 'update', linked_tag_ids: tag.id.to_s,
+          created_at: Time.utc(2024, 1, 1))
+      NicoTagVersion.create!(external_tag: external, version_no: 3,
+          event_type: 'update', linked_tag_ids: '',
+          created_at: Time.utc(2024, 2, 1))
+    end
+
+    it 'returns ID values under linked_tags without exposing the physical column key' do
+      result = query
+      expect(versions(result)).to eq([1, 2, 3])
+      expect(result['versions'].map { _1.fetch('linked_tags') }).to eq(['', tag.id.to_s, ''])
+      result['versions'].each do |row|
+        expect(row).not_to have_key('linked_tag_ids')
+        expect(row.fetch('name')).to eq('nico:beta')
+      end
+    end
+
+    it 'preloads external names separately for aliased link-change details' do
+      statements = []
+      listener = lambda do |_name, _start, _finish, _id, payload|
+        statements << payload[:sql]
+      end
+      ActiveSupport::Notifications.subscribed(listener, 'sql.active_record') do
+        result = query(axis('changed_field', 'linked_tags'))
+        expect(versions(result)).to eq([2, 3])
+        expect(result.fetch('versions').map { _1.fetch('name') })
+            .to eq(['nico:beta', 'nico:beta'])
+      end
+
+      details = statements.find { _1.include?('AS counts_latest') }
+      expect(details).not_to be_nil
+      expect(details).not_to include('JOIN `external_tags`')
+      names = statements.select { _1.match?(/SELECT .* FROM `external_tags`/) }
+      expect(names.length).to eq(1)
+    end
+
+    it 'advertises the legacy field, label and default columns only' do
+      get '/counts/definitions/nico_tags', params: { locale: 'ja' }
+      expect(response).to have_http_status(:ok)
+      body = response.parsed_body
+      ['fields', 'filter_fields', 'output_fields'].each do |collection|
+        keys = body.fetch(collection).map { _1.fetch('key') }
+        expect(keys).to include('linked_tags')
+        expect(keys).not_to include('linked_tag_ids')
+      end
+      field = body.fetch('output_fields').find { _1['key'] == 'linked_tags' }
+      expect(field.fetch('label')).to eq('counts:attributes.linked_tags')
+      body.fetch('axes').each_value do |keys|
+        expect(keys).to include('linked_tags')
+        expect(keys).not_to include('linked_tag_ids')
+      end
+      body.fetch('defaults').select { _1['mode'] == 'details' }.each do |entry|
+        settings = entry.fetch('params').to_h { [_1.fetch('key'), _1.fetch('value')] }
+        expect(settings.fetch('columns').split).to include('name', 'linked_tags')
+        expect(settings.fetch('columns').split).not_to include('linked_tag_ids')
+      end
+    end
+
+    it 'filters IDs and legacy drills and compares link changes through linked_tags' do
+      predicate = { conditions: '', 'conditions.0.field' => 'linked_tags',
+                    'conditions.0.operator' => 'eq', 'conditions.0.value' => tag.id.to_s }
+      expect(versions(query(predicate))).to eq([2])
+      expect(versions(query(axis('linked_tags', tag.id.to_s, root: 'drill')))).to eq([2])
+      expect(versions(query(axis('changed_field', 'linked_tags')))).to eq([2, 3])
+      expect(versions(query(predicate.merge('conditions.0.value' => 'alpha')))).to be_empty
+    end
+
+    it 'sorts ID snapshots through the public field in both directions' do
+      expect(versions(query(sort: 'linked_tags:asc version_no:asc'))).to eq([1, 3, 2])
+      expect(versions(query(sort: 'linked_tags:desc version_no:asc'))).to eq([2, 1, 3])
+    end
+
+    it 'restores historical link IDs before applying a latest snapshot filter' do
+      predicate = { definition: 'latest', at: '2024-01-15T00:00:00Z',
+                    conditions: '', 'conditions.0.field' => 'linked_tags',
+                    'conditions.0.operator' => 'eq', 'conditions.0.value' => tag.id.to_s }
+      expect(versions(query(predicate))).to eq([2])
+      expect(versions(query(predicate.except(:at)))).to be_empty
+    end
+
+    it 'groups by linked_tags ID values and accepts the resulting drill condition' do
+      sign_in_as(create(:user, :admin))
+      result = query(mode: 'summary', rows: 'linked_tags', cols: '', sort: '')
+      expect(result.fetch('cells')).to match_array([
+          { 'values' => [''], 'count' => 2 },
+          { 'values' => [tag.id.to_s], 'count' => 1 }])
+      expect(versions(query(axis('linked_tags', tag.id.to_s)))).to eq([2])
+    end
+
+    it 'rejects the physical column as a public filter, axis, sort or change field' do
+      sign_in_as(create(:user, :admin))
+      [
+          axis('linked_tag_ids', tag.id.to_s),
+          axis('changed_field', 'linked_tag_ids'),
+          { mode: 'summary', rows: 'linked_tag_ids', cols: '', sort: '' },
+          { sort: 'linked_tag_ids:asc' }].each do |invalid|
+        get '/counts/query', params: base.merge(invalid)
+        expect(response).to have_http_status(:bad_request), invalid.inspect
+      end
+    end
+  end
+
   it 'identifies a totals timeout without exposing SQL in the error response' do
     allow_any_instance_of(CountsPivotTotals).to receive(:call)
         .and_raise(CountsPivotTotals::TimedOut, 'private SQL')
@@ -223,11 +332,10 @@ RSpec.describe 'Counts table API', type: :request do
       expect(versions(query(tags: 'renamed-alpha'))).to eq([1, 2, 4])
     end
 
-    it 'resolves external names from current registrations too' do
-      external.update!(name: 'renamed-beta')
-      expect(versions(query(tags: 'nico:renamed-beta'))).to eq([1])
-      expect(versions(query(tags: 'nico:beta'))).to eq([])
-      expect(versions(query(tags: 'not:nico:renamed-beta'))).to eq([2, 3, 4])
+    it 'resolves immutable external names across historical snapshots' do
+      expect(versions(query(tags: 'nico:beta'))).to eq([1])
+      expect(versions(query(tags: 'nico:renamed-beta'))).to eq([])
+      expect(versions(query(tags: 'not:nico:beta'))).to eq([2, 3, 4])
     end
   end
 

backend/spec/requests/nico_tags_spec.rb

@@ -38,12 +38,9 @@ RSpec.describe 'NicoTags', type: :request do
     end
 
     it 'filters by nico tag name, linked tag name, and link status' do
-      linked = create(:external_tag)
-      linked.update!(name: 'search_linked')
-      unlinked = create(:external_tag)
-      unlinked.update!(name: 'search_unlinked')
-      other = create(:external_tag)
-      other.update!(name: 'other')
+      linked = create(:external_tag, name: 'search_linked')
+      unlinked = create(:external_tag, name: 'search_unlinked')
+      other = create(:external_tag, name: 'other')
       destination = create(:tag, category: :general)
       primary_tag_name_for(destination, 'ja').update!(name: 'destination_search')
       NicoTagRelation.create!(nico_tag: linked, tag: destination)
@@ -80,11 +77,9 @@ RSpec.describe 'NicoTags', type: :request do
     end
 
     it 'sorts by name and timestamps' do
-      older = create(:external_tag)
-      older.update!(name: 'ordered_a')
+      older = create(:external_tag, name: 'ordered_a')
       older.update_columns(created_at: 2.days.ago)
-      newer = create(:external_tag)
-      newer.update!(name: 'ordered_b')
+      newer = create(:external_tag, name: 'ordered_b')
       newer.update_columns(created_at: 1.day.ago)
       older_post_tag =
         PostExternalTag.create!(post: create(:post), external_tag: older)
@@ -189,9 +184,8 @@ RSpec.describe 'NicoTags', type: :request do
 
       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.linked_tag_ids.split.map(&:to_i))
+          .to match_array([linked_a.id, linked_b.id])
       expect(versions.last.created_by_user_id).to eq(admin.id)
     end
 
@@ -210,7 +204,7 @@ RSpec.describe 'NicoTags', type: :request do
       expect(json).to eq([])
       expect(nico_tag.reload.linked_tags).to be_empty
       expect(nico_tag.nico_tag_versions.order(:version_no).last)
-          .to have_attributes(linked_tags: '', created_by_user: member)
+          .to have_attributes(linked_tag_ids: '', created_by_user: member)
     end
 
     it 'returns the tags field error when a nico tag is specified directly' do

backend/spec/requests/post_history_privacy_spec.rb

@@ -20,7 +20,7 @@ RSpec.describe 'Post history privacy', type: :request do
           created_at: origin + index.hours)
     end
     NicoTagVersion.create!(external_tag: external, version_no: 1, event_type: 'create',
-        name: 'nico:external-history-tag', linked_tags: 'private-history-tag',
+        linked_tag_ids: tag.id.to_s,
         created_at: origin)
     ['create', 'discard', 'restore', 'update'].each_with_index do |event, index|
       snapshots =

backend/spec/services/counts_export_spec.rb

@@ -22,6 +22,55 @@ RSpec.describe 'Counts export contracts' do
         .and_return(@counts_export_dir)
   end
 
+  context 'nico link IDs with the V1 export field name' do
+    let(:tag) { create(:tag, primary_name: 'linked export tag') }
+    let(:external) { create(:external_tag, name: 'external export tag') }
+    let(:payload) do
+      super().merge('resource' => 'nico_tags', 'columns' => 'linked_tags',
+          'headers' => '["linked_tags"]')
+    end
+
+    before do
+      NicoTagVersion.create!(external_tag: external, version_no: 1,
+          event_type: 'create', linked_tag_ids: '', created_at: Time.utc(2023))
+      NicoTagVersion.create!(external_tag: external, version_no: 2,
+          event_type: 'update', linked_tag_ids: tag.id.to_s, created_at: Time.utc(2024))
+    end
+
+    it 'exports recorded IDs with the legacy JSON key and metadata' do
+      result = JSON.parse(CountsTableExport.new(payload).body.to_a.join)
+      expect(result.fetch('data')).to eq([
+          { 'linked_tags' => '' }, { 'linked_tags' => tag.id.to_s }])
+      expect(result.fetch('metadata').fetch('columns')).to eq(['linked_tags'])
+      keys = result.fetch('metadata').fetch('fields').map { _1.fetch('key') }
+      expect(keys).to include('linked_tags')
+      expect(keys).not_to include('linked_tag_ids')
+    end
+
+    it 'exports the selected legacy CSV header and ID values' do
+      body = CountsTableExport.new(payload.merge('format' => 'csv')).body.to_a.join
+      expect(CSV.parse(body.delete_prefix("\uFEFF")))
+          .to eq([['linked_tags'], [''], [tag.id.to_s]])
+    end
+
+    it 'keeps the legacy link column in default JSON exports' do
+      options = payload.except('columns', 'headers')
+      result = JSON.parse(CountsTableExport.new(options).body.to_a.join)
+      expect(result.fetch('metadata').fetch('columns')).to include('linked_tags')
+      result.fetch('data').each do |row|
+        expect(row).to have_key('linked_tags')
+        expect(row).not_to have_key('linked_tag_ids')
+      end
+    end
+
+    it 'rejects the physical column as a selected CSV or JSON field' do
+      ['csv', 'json'].each do |format|
+        options = payload.merge('format' => format, 'columns' => 'linked_tag_ids')
+        expect { CountsTableExport.new(options) }.to raise_error(ArgumentError)
+      end
+    end
+  end
+
   context 'serialization after the authorised query' do
     let(:rows) { [{ title: "=formula\n\"quoted\"" }, { title: 'second' }] }
     let(:page) do

backend/spec/services/nico_tag_version_recorder_spec.rb

@@ -8,21 +8,23 @@ RSpec.describe NicoTagVersionRecorder do
     described_class.record!(external_tag:, event_type:, created_by_user: member)
   end
 
-  it 'records the external association and platform-qualified name' do
+  it 'records the external association and derives the platform-qualified name' do
     version = record(:create)
 
     expect(version).to have_attributes(
         external_tag:, version_no: 1, event_type: 'create',
-        name: 'nico:raw tag[]', linked_tags: '', created_by_user: member)
+        name: 'nico:raw tag[]', linked_tag_ids: '', created_by_user: member)
     expect(external_tag.reload.nico_tag_versions).to contain_exactly(version)
   end
 
   it 'uses the latest history number when the record has no version_no column' do
     first = record(:create)
-    external_tag.update!(name: 'changed')
+    tag = create(:tag)
+    NicoTagRelation.create!(nico_tag: external_tag, tag:)
     second = record(:update)
 
-    expect(second).to have_attributes(version_no: 2, name: 'nico:changed')
+    expect(second).to have_attributes(version_no: 2, name: 'nico:raw tag[]',
+        linked_tag_ids: tag.id.to_s)
     expect(first.reload.name).to eq('nico:raw tag[]')
     expect(external_tag.reload.has_attribute?(:version_no)).to be(false)
   end
@@ -40,7 +42,7 @@ RSpec.describe NicoTagVersionRecorder do
 
     expect(version).to have_attributes(
         external_tag: external, name: 'registered_external:foo', version_no: 1,
-        event_type: 'create', linked_tags: '', created_by_user: member)
+        event_type: 'create', linked_tag_ids: '', created_by_user: member)
   end
 
   it 'returns the latest version without appending an unchanged snapshot' do
@@ -64,13 +66,21 @@ RSpec.describe NicoTagVersionRecorder do
     expect(external_tag.nico_tag_versions).to contain_exactly(first)
   end
 
-  it 'records sorted linked internal names and later link removal' do
+
+  it 'rejects discard and restore events for external tags' do
+    [:discard, :restore].each do |event_type|
+      expect { record(event_type) }
+          .to raise_error(ArgumentError, "Invalid event_type: #{ event_type }")
+    end
+  end
+
+  it 'records sorted linked internal IDs and later link removal' do
     tags = ['z_link', 'a_link'].map { |name| create(:tag, primary_name: name, category: :general) }
     tags.each { |tag| NicoTagRelation.create!(nico_tag: external_tag, tag:) }
 
-    expect(record(:create).linked_tags).to eq('a_link z_link')
+    expect(record(:create).linked_tag_ids).to eq(tags.map(&:id).sort.join(' '))
     external_tag.linked_tags = [tags.first]
 
-    expect(record(:update)).to have_attributes(version_no: 2, linked_tags: 'z_link')
+    expect(record(:update)).to have_attributes(version_no: 2, linked_tag_ids: tags.first.id.to_s)
   end
 end

docs/counts-exports.md

@@ -158,7 +158,15 @@ private ID の正誤に応じて許可可否を変えないため、条件の一
 非公開列を含む列順変更は API の column_values_editable でも不可と通知する。
 公開列の値を使うソートは引き続き利用できる。
 明細には元の認可を適用し、抽出条件自体や取得済みの全言語値を表示設定で改変しない。
-外部タグ履歴の linked_tags は名前しか記録していないため、非公開タグの記録名を含む
-スナップショットを保守的に非公開とする。名前全体を空白区切りの境界で照合する。
-区切りが曖昧な旧データでは過剰な秘匿の可能性はあるが、参照 ID がない旧形式から
-名前の公開可否を楽観的に推定しない。
+外部タグ履歴の linked_tag_ids は、連携する内部タグの ID を半角スペース区切りで
+記録する。除票履歴のある内部タグの ID を含むスナップショットを非管理者へ公開しない。
+名前や UI ロカールを参照 ID の判定に使わない。
+外部タグ名は履歴に保存せず、external_tags の platform と name を参照する。
+
+V1 Counts の公開フィールド名は linked_tags を維持し、DB の linked_tag_ids へ対応付ける。
+明細・フィルタ・集計軸・変更項目・ソート・CSV / JSON 出力で同じ対応付けを使う。
+既定列と既存の表示ラベルも linked_tags のままとし、値は ID のスペース区切りとする。
+linked_tag_ids は物理カラム名であり、Counts の公開フィールドとしては受け付けない。
+旧 URL や保存済み設定のフィールド指定は維持できるが、値にタグ名を指定した検索条件や
+ドリル条件は ID 値への見直しが必要となる。旧履歴の名称は移行後の ID 列から復元できず、
+現在の名称からの推定変換も行わない。

frontend/src/components/PostEditForm.tsx

@@ -1,6 +1,5 @@
 import { useEffect, useMemo, useState } from 'react'
 
-import ResourcePickerButton from '@/components/common/ResourcePickerButton'
 import { useLocale } from '@/lib/useLocale'
 import PostOriginalCreatedTimeField from '@/components/PostOriginalCreatedTimeField'
 import FieldError from '@/components/common/FieldError'
@@ -185,12 +184,7 @@ const PostEditForm: FC<Props> = ({ post, onSave }) => {
 	    value={parentPostIds}
 	    disabled={disabled}
 	    errors={fieldErrors.parentPostIds}
-	    onChange={setParentPostIds}
-	    after={<ResourcePickerButton
-		source={{ resource: 'posts', field: 'resource_id' }}
-		values={parentPostIds.split (/\s+/).filter (Boolean)}
-		maximum={Number.MAX_SAFE_INTEGER} disabled={disabled}
-		onSelect={values => setParentPostIds (values.join (' '))}/>}/>
+	    onChange={setParentPostIds}/>
 
 	<PostTagsField
 	    disabled={disabled}

frontend/src/components/common/ResourcePickerButton.tsx

@@ -1,27 +0,0 @@
-import CandidatePicker from '@/components/common/CandidatePicker'
-import { Button } from '@/components/ui/button'
-import useDialogue from '@/lib/dialogues/useDialogue'
-import { useUITranslation } from '@/lib/useUITranslation'
-
-import type { CandidateSource } from '@/components/common/CandidatePicker'
-
-
-const ResourcePickerButton = (
-    { source, values, maximum = 1, disabled, onSelect }: {
-	source: CandidateSource; values: (string | number | boolean)[]
-	maximum?: number; disabled?: boolean
-	onSelect: (values: string[]) => void },
-) => {
-  const dialogue = useDialogue ()
-  const { label } = useUITranslation ('counts')
-  return <Button type="button" variant="outline" disabled={disabled || maximum === 0}
-      onClick={() => void dialogue.form ({
-	  title: label ('pattern.link'), size: 'large', footerLayout: 'inline',
-	  body: controls => <CandidatePicker source={source} initial={values}
-	      maximum={maximum} controls={controls} onSelect={onSelect}/> })}>
-    {label ('pattern.selection')}
-  </Button>
-}
-
-
-export default ResourcePickerButton

frontend/src/components/counts/CountsAdditionalConditions.tsx

@@ -2,10 +2,9 @@ import { useEffect, useState } from 'react'
 
 import CountsConditionValues from '@/components/counts/CountsConditionValues'
 import DateTimeField from '@/components/common/DateTimeField'
-import ResourcePickerButton from '@/components/common/ResourcePickerButton'
 import { Button } from '@/components/ui/button'
-import { countsCandidateSource, countsConditionInputs, countsEventTypes, countsFieldLabel,
-	 countsFilterFields, countsSetOperators, useCountsDefinition }
+import { countsConditionInputs, countsEventTypes, countsFieldLabel, countsFilterFields,
+	 countsSetOperators, useCountsDefinition }
     from '@/lib/countsDefinitions'
 import useDialogue from '@/lib/dialogues/useDialogue'
 import { countsConditionValue } from '@/lib/countsDisplay'
@@ -16,7 +15,7 @@ import { useUITranslation } from '@/lib/useUITranslation'
 
 import { readCountState, encodeCountState } from '@/lib/queryState'
 
-import type { CountsResource, CountsResult } from '@/lib/counts'
+import type { CountsResult } from '@/lib/counts'
 import type { DialogueFormControls } from '@/lib/dialogues/useDialogue'
 import type { CountsCondition, CountsDefinition } from '@/lib/countsDefinitions'
 
@@ -95,12 +94,10 @@ const ConditionEditor = (
 }
 
 
-const CountsAdditionalConditions = (
-    { resource, params, update, conditionLabels }: {
-	resource: CountsResource
-	conditionLabels?: CountsResult['conditionLabels']
-	params: URLSearchParams
-	update: (key: string, value: string) => void },
+const CountsAdditionalConditions = ({ params, update, conditionLabels }: {
+    conditionLabels?: CountsResult['conditionLabels']
+    params: URLSearchParams
+    update: (key: string, value: string) => void },
 ) => {
   const { label, message } = useUITranslation ('counts')
   const definition = useCountsDefinition ()
@@ -152,19 +149,11 @@ const CountsAdditionalConditions = (
 	  value={condition[key] == null ? '' : String (condition[key])} disabled={condition.blank}
 	  aria-label={aria.text} lang={aria.language}
 	  onChange={value => patch (index, { [key]: value ?? '' })}/>
-    const source = countsCandidateSource (resource, condition.field, params)
-    const value = condition[key]
-    return <>
-      <input value={condition[key] == null ? '' : String (condition[key])}
-	  disabled={condition.blank}
-	  type={(type === 'integer' || type === 'interval') ? 'number' : 'text'}
-	  className={inputClass (false, 'h-10 w-36 min-w-0 max-w-full py-1')}
-	  aria-label={aria.text} lang={aria.language}
-	  onChange={event => patch (index, { [key]: event.target.value })}/>
-      {source != null && <ResourcePickerButton source={source} disabled={condition.blank}
-	  values={value == null ? [] : [value]}
-	  onSelect={values => patch (index, { [key]: values[0] ?? '' })}/>}
-    </>
+    return <input value={condition[key] == null ? '' : String (condition[key])} disabled={condition.blank}
+	type={(type === 'integer' || type === 'interval') ? 'number' : 'text'}
+	className={inputClass (false, 'h-10 w-36 min-w-0 max-w-full py-1')}
+	aria-label={aria.text} lang={aria.language}
+	onChange={event => patch (index, { [key]: event.target.value })}/>
   }
   return <fieldset className="min-w-0 space-y-1">
     <legend className="flex items-center gap-2">{label ('additionalConditions')}
@@ -193,7 +182,7 @@ const CountsAdditionalConditions = (
 	  </Button>
 	  {countsSetOperators.includes (condition.operator) ? (
 	      <CountsConditionValues key={`${condition.field}:${condition.operator}`}
-		  condition={condition} resource={resource}
+		  condition={condition}
 		  field={metadata.find (field => field.key === condition.field)!}
 		  definition={definition} params={params}
 		  maximum={Math.max (0, 100 - conditions.reduce ((sum, item, position) =>

frontend/src/components/counts/CountsCandidatePicker.tsx

@@ -10,19 +10,16 @@ import { useUITranslation } from '@/lib/useUITranslation'
 import { inputClass } from '@/lib/utils'
 
 import type { DialogueFormControls } from '@/lib/dialogues/useDialogue'
-import type { CountsResource } from '@/lib/counts'
-import type { CountsCandidate } from '@/lib/countsDefinitions'
+import type { CountsCandidate, CountsDefinition, CountsFieldMetadata }
+    from '@/lib/countsDefinitions'
 
 
-type CandidateSource = {
-    resource: CountsResource; field: string; params?: URLSearchParams }
-
-const CandidatePicker = (
-    { source, choices = [], categories = [], initial, maximum, controls, onSelect }: {
-	source?: CandidateSource; choices?: CountsCandidate[]
-	categories?: { value: string; label: string }[]
+const CountsCandidatePicker = (
+    { field, definition, params, initial, maximum, controls, save }: {
+	field: CountsFieldMetadata; params: URLSearchParams
+	definition: CountsDefinition
 	initial: (string | number | boolean)[]; maximum: number
-	controls: DialogueFormControls; onSelect: (values: string[]) => void },
+	controls: DialogueFormControls; save: (values: string[]) => void },
 ) => {
   const { label, message } = useUITranslation ('counts')
   const locale = useLocale ()
@@ -32,58 +29,49 @@ const CandidatePicker = (
   const [search, setSearch] = useState ('')
   const [category, setCategory] = useState ('')
   const [page, setPage] = useState (1)
-  const params = source?.params ?? new URLSearchParams ()
   const point = useRef (params.get ('execution_at'))
-  const resource = source?.resource ?? 'posts'
-  const request = new URLSearchParams ({
-      field: source?.field ?? '', query: search, page: String (page) })
+  const request = new URLSearchParams ({ field: field.key, query: search, page: String (page) })
   for (const key of ['timezone', 'at', 'execution_at'])
     if (params.has (key)) request.set (key, params.get (key)!)
   if (countsUseLatest (params)) request.delete ('at')
   if (category) request.set ('category', category)
-  const dynamic = source != null
+  const dynamic = field.candidateSource === 'tags'
   const { data, isFetching: fetching, isError: errorFlg } = useQuery ({
-      queryKey: countsKeys.candidates (resource, request.toString (), locale), enabled: dynamic,
+      queryKey: countsKeys.candidates (request.toString (), locale), enabled: dynamic,
       queryFn: ({ signal }) => {
 	const pinned = new URLSearchParams (request)
 	if (point.current != null) pinned.set ('execution_at', point.current)
-	return fetchCountsCandidates (resource, pinned, locale, signal)
-      }, retry: false, staleTime: 0, gcTime: 0 })
+	return fetchCountsCandidates (pinned, locale, signal)
+      }, retry: false })
   useEffect (() => {
     if (data != null) point.current = data.executionAt
   }, [data])
   useEffect (() => {
     controls.setActions ([{ label: label ('counts:pattern.selection'),
-	onSelect: () => onSelect ([...selected]) }])
-  }, [controls, label, onSelect, selected])
-  const rows = dynamic ? data?.rows ?? [] : choices.filter (row =>
+	onSelect: () => save ([...selected]) }])
+  }, [controls, label, save, selected])
+  const fixed: CountsCandidate[] = (field.choices ?? []).map (choice => ({
+      value: choice.value, name: message (choice.label).text }))
+  const rows = dynamic ? data?.rows ?? [] : fixed.filter (row =>
       row.name?.includes (search))
   const headings: Record<string, string> = {
       id: 'columns.resourceId', name: 'columns.name', category: 'common:categoryLabel',
       kind: 'columns.kind' }
-  const referenceFlg = ['tags', 'tags_json', 'tags.id', 'members'].includes (source?.field ?? '')
-  const available =
-      dynamic
-      ? (referenceFlg ? ['id', 'name', 'category', 'kind'] : ['id', 'name']) : ['name']
+  const available = dynamic ? ['id', 'name', 'category', 'kind'] : ['name']
   const visible = available.filter (column => columns.includes (column))
+  const categories = definition.filterFields?.find (field =>
+      field.candidateSource === 'categories')?.choices ?? []
   const cell = (row: CountsCandidate, column: string) => {
-    if (column === 'category')
-      return row.category == null ? '' : message (`common:categories.${row.category}`).text
-    if (column === 'kind')
-    {
-      const key = row.kind === 'group' ? 'resources.tag_groups' : `kinds.${row.kind}`
-      return row.kind == null ? '' : message (key).text
-    }
+    if (column === 'category') return row.category == null ? ''
+	: message (`common:categories.${row.category}`).text
+    if (column === 'kind') return row.kind == null ? '' : message (`kinds.${row.kind}`).text
     return column === 'id' ? row.id : row.name
   }
   return <div className="min-w-0 space-y-2">
     <div className="flex min-w-0 items-center gap-2">
       <input className={inputClass (false, 'h-9 min-w-0 flex-1')}
-	  aria-label={message ('common:search').text} value={draft} maxLength={1000}
-	  onChange={event => {
-	    const value = event.target.value
-	    if (new TextEncoder ().encode (value).length <= 1000) setDraft (value)
-	  }}
+	  aria-label={message ('common:search').text} value={draft}
+	  onChange={event => setDraft (event.target.value)}
 	  onKeyDown={event => {
 	    if (event.key !== 'Enter') return
 	    event.preventDefault ()
@@ -93,7 +81,7 @@ const CandidatePicker = (
       <Button type="button" onClick={() => { setSearch (draft); setPage (1) }}>
 	{label ('common:search')}</Button>
     </div>
-    {categories.length > 0 && <select className={inputClass (false, 'h-9 w-full')}
+    {dynamic && <select className={inputClass (false, 'h-9 w-full')}
 	aria-label={message ('common:categoryLabel').text} value={category}
 	onChange={event => { setCategory (event.target.value); setPage (1) }}>
       <option value=""/>
@@ -120,13 +108,12 @@ const CandidatePicker = (
 	  {visible.map (column => <th key={column} className="p-2">
 	    {label (headings[column])}</th>)}
 	</tr></thead>
-	<tbody>{rows.map (row => <tr key={`${row.kind ?? ''}:${row.value}`}
-	    className="border-t dark:border-slate-700">
+	<tbody>{rows.map (row => <tr key={row.value} className="border-t dark:border-slate-700">
 	  <td className="p-2"><input type="checkbox" checked={selected.has (row.value)}
 	      aria-label={row.name ?? String (row.id ?? '')}
-	      disabled={maximum !== 1 && !selected.has (row.value) && selected.size >= maximum}
+	      disabled={!selected.has (row.value) && selected.size >= maximum}
 	      onChange={event => setSelected (previous => {
-		const next = new Set (maximum === 1 ? [] : previous)
+		const next = new Set (previous)
 		if (event.target.checked) next.add (row.value)
 		else next.delete (row.value)
 		return next
@@ -141,8 +128,7 @@ const CandidatePicker = (
 	  onClick={() => setPage (previous => previous - 1)}>
 	{label ('common:pagination.previous')}</Button>
       <span>{page}</span>
-      <Button type="button" variant="outline"
-	  disabled={!data?.more || page >= 100_000 || fetching}
+      <Button type="button" variant="outline" disabled={!data?.more || fetching}
 	  onClick={() => setPage (previous => previous + 1)}>
 	{label ('common:pagination.next')}</Button>
     </div>}
@@ -150,5 +136,4 @@ const CandidatePicker = (
 }
 
 
-export default CandidatePicker
-export type { CandidateSource }
+export default CountsCandidatePicker

frontend/src/components/counts/CountsConditionValues.tsx

@@ -1,22 +1,18 @@
 import { useState } from 'react'
 
-import CandidatePicker from '@/components/common/CandidatePicker'
+import CountsCandidatePicker from '@/components/counts/CountsCandidatePicker'
 import DateTimeField from '@/components/common/DateTimeField'
-import ResourcePickerButton from '@/components/common/ResourcePickerButton'
 import { Button } from '@/components/ui/button'
-import { countsCandidateSource } from '@/lib/countsDefinitions'
 import useDialogue from '@/lib/dialogues/useDialogue'
 import { inputClass } from '@/lib/utils'
 import { useUITranslation } from '@/lib/useUITranslation'
 
-import type { CountsResource } from '@/lib/counts'
 import type { CountsCondition, CountsDefinition, CountsFieldMetadata }
     from '@/lib/countsDefinitions'
 
 
 const CountsConditionValues = (
-    { resource, condition, field, definition, params, maximum, change }: {
-	resource: CountsResource
+    { condition, field, definition, params, maximum, change }: {
 	condition: CountsCondition; field: CountsFieldMetadata; definition: CountsDefinition
 	params: URLSearchParams; maximum: number
 	change: (values: (string | number | boolean)[]) => void },
@@ -25,21 +21,11 @@ const CountsConditionValues = (
   const dialogue = useDialogue ()
   const [draft, setDraft] = useState ('')
   const values = condition.values ?? []
-  const source = countsCandidateSource (resource, field.key, params)
   const pick = () => {
     void dialogue.form ({ title: label (field.candidateSource === 'tags'
 	? 'counts:pattern.link' : 'counts:pattern.pattern'), size: 'large', footerLayout: 'inline',
-	body: controls => <CandidatePicker
-	    source={
-		field.candidateSource === 'tags'
-		? { resource: 'posts', field: field.key, params } : undefined}
-	    choices={(field.choices ?? []).map (choice => ({
-		value: choice.value, name: message (choice.label).text }))}
-	    categories={
-		field.candidateSource === 'tags'
-		? definition.filterFields?.find (field =>
-		    field.candidateSource === 'categories')?.choices : undefined}
-	    initial={values} maximum={maximum} controls={controls} onSelect={change}/> })
+	body: controls => <CountsCandidatePicker field={field} definition={definition}
+	    params={params} initial={values} maximum={maximum} controls={controls} save={change}/> })
   }
   if (condition.input === 'list' || field.candidateSource === 'tags')
     return <Button type="button" variant="outline" disabled={condition.blank} onClick={pick}>
@@ -87,8 +73,6 @@ const CountsConditionValues = (
 	  onClick={() => change (values.filter ((_, position) => position !== index))}>×</Button>
     </span>)}
     {input ()}
-    {source != null && <ResourcePickerButton source={source} values={values}
-	maximum={maximum} disabled={condition.blank} onSelect={change}/>}
     <Button type="button" variant="outline" disabled={condition.blank || draft === ''
 	|| values.length >= maximum} onClick={append}>{label ('add')}</Button>
     <Button type="button" variant="ghost" disabled={condition.blank}

frontend/src/components/counts/CountsControls.tsx

@@ -7,7 +7,6 @@ import AnimatedCollapse from '@/components/common/AnimatedCollapse'
 import DateTimeField from '@/components/common/DateTimeField'
 import FormField from '@/components/common/FormField'
 import HelpPopover from '@/components/common/HelpPopover'
-import ResourcePickerButton from '@/components/common/ResourcePickerButton'
 import TagInput from '@/components/common/TagInput'
 import { Button } from '@/components/ui/button'
 import { countsRequestParams, countsUseLatest, fieldsForCounts, normaliseCountsParams, readCountSort,
@@ -224,17 +223,11 @@ const CountsControls = (
             {['resource_id', 'user_id'].map (key => <FormField key={key}
                 label={label (key === 'resource_id' ? 'columns.resourceId'
                     : 'fields.actor')}>
-              {() => <div className="flex flex-wrap items-center gap-1">
-		<input value={draft.get (key) ?? ''}
-		    aria-label={message (key === 'resource_id' ? 'columns.resourceId'
-			: 'fields.actor').text}
-		    className={inputClass (false, 'w-28 py-1')}
-		    onChange={event => update (key, event.target.value)}/>
-		<ResourcePickerButton source={{ resource,
-		    field: key === 'resource_id' ? key : 'user', params: draft }}
-		    values={draft.get (key) ? [draft.get (key)!] : []}
-		    onSelect={values => update (key, values[0] ?? '')}/>
-	      </div>}
+              {() => <input value={draft.get (key) ?? ''}
+                  aria-label={message (key === 'resource_id' ? 'columns.resourceId'
+                      : 'fields.actor').text}
+                  className={inputClass (false, 'w-28 py-1')}
+                  onChange={event => update (key, event.target.value)}/>}
             </FormField>)}
           </div>
           {resource === 'posts' && <FormField label={label ('columns.tags')}>
@@ -270,7 +263,7 @@ const CountsControls = (
               </>}
             </div>}
           </FormField>}
-	  <CountsAdditionalConditions resource={resource} params={draft} update={update}
+	  <CountsAdditionalConditions params={draft} update={update}
               conditionLabels={(labelParams != null
                   && draft.get ('current') === labelParams.get ('current')
                   && draft.get ('at') === labelParams.get ('at')

frontend/src/components/dialogues/DialogueProvider.tsx

@@ -227,13 +227,11 @@ const DialogueProvider: FC<Props> = ({ children }) => {
 		       </DialogHeader>
 
 		       <div className="min-h-0 flex-1 overflow-y-auto overscroll-contain">
-			 <DialogueProvider key={active.id}>
-			   {active.options.body (
-			       formControls.current[active.id] ??= {
+			 {active.options.body (
+			     formControls.current[active.id] ??= {
 				 close: () => closeRequest (active.id),
 				 setActions: actions => setRequestActions (active.id, actions),
 				 confirm: options => openNestedConfirm (active.id, options) })}
-			 </DialogueProvider>
 		       </div>
 
 		       <DialogFooter

frontend/src/components/posts/PostCoreDataFields.tsx

@@ -1,4 +1,3 @@
-import ResourcePickerButton from '@/components/common/ResourcePickerButton'
 import PostOriginalCreatedTimeField from '@/components/PostOriginalCreatedTimeField'
 import PostTagsField from '@/components/posts/PostTagsField'
 import PostTextField from '@/components/posts/PostTextField'
@@ -89,12 +88,7 @@ const PostCoreDataFields: FC<PostCoreDataFieldsProps> = (
           disabled={parentPostIds.disabled}
           warnings={parentPostIds.warnings}
           errors={parentPostIds.errors}
-          onChange={parentPostIds.onChange}
-	  after={<ResourcePickerButton
-	      source={{ resource: 'posts', field: 'resource_id' }}
-	      values={parentPostIds.value.split (/\s+/).filter (Boolean)}
-	      maximum={Number.MAX_SAFE_INTEGER} disabled={parentPostIds.disabled}
-	      onSelect={values => parentPostIds.onChange (values.join (' '))}/>}/>
+          onChange={parentPostIds.onChange}/>
     </>)
 
 export default PostCoreDataFields

frontend/src/components/posts/PostTextField.tsx

@@ -34,22 +34,18 @@ const PostTextField: FC<Props> = (
     <FormField label={label} messages={errors}>
       {({ describedBy, invalid }) => (
           <>
-	    <div className="flex items-center gap-2">
-	      <div className="min-w-0 flex-1">
-		<input
-		    type={type}
-		    value={value}
-		    disabled={disabled}
-		    placeholder={placeholder}
-		    onBlur={onBlur}
-		    onChange={ev => onChange (ev.target.value)}
-		    aria-describedby={describedBy}
-		    aria-invalid={invalid}
-		    className={inputClass (invalid, className)}/>
-	      </div>
-	      {after}
-	    </div>
-	    <FieldWarning messages={warnings}/>
+            <input
+                type={type}
+                value={value}
+                disabled={disabled}
+                placeholder={placeholder}
+                onBlur={onBlur}
+                onChange={ev => onChange (ev.target.value)}
+                aria-describedby={describedBy}
+                aria-invalid={invalid}
+                className={inputClass (invalid, className)}/>
+            <FieldWarning messages={warnings}/>
+            {after}
           </>)}
     </FormField>)
 

frontend/src/lib/countsDefinitions.ts

@@ -89,19 +89,18 @@ export const fetchCountsDefinition = (
     .then (definition => localiseCountsDefinition (definition, locale))
 
 export type CountsCandidate = {
-    value: string; id?: number | string; name: string | null; category?: string | null
+    value: string; id?: number; name: string | null; category?: string | null
     names?: Record<string, unknown>
-    kind?: 'internal' | 'external' | 'group' }
+    kind?: 'internal' | 'external' }
 export type CountsCandidatesResult = {
     rows: CountsCandidate[]; page: number; more: boolean; executionAt: string }
 
 export const fetchCountsCandidates = (
-    resource: CountsResource,
     params: URLSearchParams,
     locale: LocaleCode,
     signal?: AbortSignal,
 ): Promise<CountsCandidatesResult> => apiGet (
-    `/counts/definitions/${encodeURIComponent (resource)}/candidates`, {
+    '/counts/definitions/posts/candidates', {
 	params: Object.fromEntries (params), locale, signal })
 
 export const countsSetOperators = ['in', 'not_in', 'any', 'all', 'none']
@@ -156,25 +155,3 @@ export const countsAxisFields = (
     history: boolean,
 ): CountField[] => definition.axes[history ? 'history' : 'latest']
     .map (key => ({ key, label: countsFieldLabel (key) }))
-
-
-export const countsCandidateSource = (
-    resource: CountsResource,
-    field: string,
-    params: URLSearchParams,
-): { resource: CountsResource; field: string; params: URLSearchParams } | undefined => {
-  if (['resource_id', 'user', 'updated_by_user_id', 'file_blob_id', 'source_file_id']
-      .includes (field)) return { resource, field, params }
-  if (field === 'user.id') return { resource, field: 'user', params }
-  if ((field === 'tags.id' && resource === 'posts')
-      || (field === 'members' && resource === 'tag_groups'))
-    return { resource, field, params }
-  const leaf = field.split ('.').at (-1) ?? ''
-  const targets: Record<string, CountsResource> = {
-      tagGroupId: 'tag_groups', tagId: 'tags', tag_id: 'tags', parent_id: 'materials',
-      parents: resource === 'posts' ? 'posts' : 'tags',
-      'parents.id': resource === 'posts' ? 'posts' : 'tags',
-      parent_post_ids: 'posts', parent_tag_ids: 'tags' }
-  const target = targets[field] ?? targets[leaf]
-  return target == null ? undefined : { resource: target, field: 'resource_id', params }
-}

frontend/src/lib/queryKeys.ts

@@ -174,7 +174,6 @@ export const theatresKeys = {
 export const countsKeys = {
     definition: (resource: string, locale: LocaleCode = 'ja') =>
 	['counts', 'definition', resource, locale] as const,
-    candidates: (resource: string, params: string, locale: LocaleCode) =>
-	['counts', 'candidates', resource, locale, params] as const,
+    candidates: (params: string, locale: LocaleCode) => ['counts', 'candidates', locale, params] as const,
     index: (resource: string, params: string, locale: LocaleCode) =>
 	['counts', resource, locale, params] as const }

frontend/src/pages/materials/MaterialHistoryPage.tsx

@@ -6,7 +6,6 @@ import { useLocale, useLocaleNavigate } from '@/lib/useLocale'
 import PrefetchLink from '@/components/PrefetchLink'
 import FormField from '@/components/common/FormField'
 import PageTitle from '@/components/common/PageTitle'
-import ResourcePickerButton from '@/components/common/ResourcePickerButton'
 import Pagination from '@/components/common/Pagination'
 import MainArea from '@/components/layout/MainArea'
 import PageHead from '@/components/common/PageHead'
@@ -97,19 +96,13 @@ const MaterialHistoryPage: FC = () => {
 	    <div className="space-y-3">
 	      <FormField label={label ('materialId')}>
 		{({ invalid }) => (
-		    <div className="flex items-center gap-2">
-		      <input
+		    <input
 			type="text"
 			value={materialIdInput}
 			aria-label={message ('materialId').text}
 			lang={message ('materialId').language}
 			onChange={e => setMaterialIdInput (e.target.value)}
-			className={inputClass (invalid, 'min-w-0 flex-1')}/>
-		      <ResourcePickerButton
-			source={{ resource: 'materials', field: 'resource_id' }}
-			values={materialIdInput === '' ? [] : [materialIdInput]}
-			onSelect={values => setMaterialIdInput (values[0] ?? '')}/>
-		    </div>)}
+			className={inputClass (invalid)}/>)}
 	      </FormField>
 
 	      <FormField label={label ('tagName')}>

frontend/src/pages/materials/MaterialSyncSuppressionsPage.tsx

@@ -4,7 +4,6 @@ import { useState } from 'react'
 import { useLocale } from '@/lib/useLocale'
 import FormField from '@/components/common/FormField'
 import PageTitle from '@/components/common/PageTitle'
-import ResourcePickerButton from '@/components/common/ResourcePickerButton'
 import MainArea from '@/components/layout/MainArea'
 import { Button } from '@/components/ui/button'
 import { toast } from '@/components/ui/use-toast'
@@ -127,17 +126,11 @@ const MaterialSyncSuppressionsPage: FC = () => {
 
 	      <FormField label={label ('suppressions.driveFileId')}>
 		{({ invalid }) => (
-		    <div className="flex items-center gap-2">
-		      <input
+		    <input
 			type="text"
 			value={driveFileId}
 			onChange={e => setDriveFileId (e.target.value)}
-			className={inputClass (invalid, 'min-w-0 flex-1')}/>
-		      <ResourcePickerButton
-			source={{ resource: 'materials', field: 'drive_file_id' }}
-			values={driveFileId === '' ? [] : [driveFileId]}
-			onSelect={values => setDriveFileId (values[0] ?? '')}/>
-		    </div>)}
+			className={inputClass (invalid)}/>)}
 	      </FormField>
 
 	      <FormField label={label ('suppressions.reason')}>