DB migration for module imported count

Add a migration to add a column to search_documents
that will hold the number of importing modules.

For golang/go#80242.

Change-Id: Ifd1ec3f97c790e96e57a21e3e83dbdd4df3a2db7
Reviewed-on: https://go-review.googlesource.com/c/pkgsite/+/796521
kokoro-CI: kokoro <noreply+kokoro@google.com>
LUCI-TryBot-Result: golang-scoped@luci-project-accounts.iam.gserviceaccount.com <golang-scoped@luci-project-accounts.iam.gserviceaccount.com>
Reviewed-by: Hyang-Ah Hana Kim <hyangah@gmail.com>

migrations/000157_add_search_documents_imported_by_module_count.down.sql

diff --git a/migrations/000157_add_search_documents_imported_by_module_count.down.sql b/migrations/000157_add_search_documents_imported_by_module_count.down.sql
new file mode 100644
index 0000000..4e0a9a8
--- /dev/null
+++ b/migrations/000157_add_search_documents_imported_by_module_count.down.sql
@@ -0,0 +1,10 @@
+-- Copyright 2026 The Go Authors. All rights reserved.
+-- Use of this source code is governed by a BSD-style
+-- license that can be found in the LICENSE file.
+
+BEGIN;
+
+ALTER TABLE search_documents DROP COLUMN imported_by_module_count;
+ALTER TABLE search_documents DROP COLUMN imported_by_module_count_updated_at;
+
+END;

migrations/000157_add_search_documents_imported_by_module_count.up.sql

diff --git a/migrations/000157_add_search_documents_imported_by_module_count.up.sql b/migrations/000157_add_search_documents_imported_by_module_count.up.sql
new file mode 100644
index 0000000..54855e6
--- /dev/null
+++ b/migrations/000157_add_search_documents_imported_by_module_count.up.sql
@@ -0,0 +1,35 @@
+-- Copyright 2026 The Go Authors. All rights reserved.
+-- Use of this source code is governed by a BSD-style
+-- license that can be found in the LICENSE file.
+
+BEGIN;
+
+-- imported_by_module_count is used to track the number of modules that import this package.
+ALTER TABLE search_documents
+    ADD COLUMN imported_by_module_count
+    INTEGER
+    DEFAULT 0 NOT NULL;
+
+COMMENT ON COLUMN search_documents.imported_by_module_count
+IS 'COLUMN imported_by_module_count is the number of modules that import this package.';
+
+ALTER TABLE search_documents
+    ADD COLUMN imported_by_module_count_updated_at
+    TIMESTAMP WITH TIME ZONE;
+
+COMMENT ON COLUMN search_documents.imported_by_module_count_updated_at
+IS 'COLUMN imported_by_module_count_updated_at is the time when search_documents.imported_by_module_count is updated.';
+
+CREATE INDEX idx_imported_by_module_count_desc
+ON search_documents (imported_by_module_count DESC);
+
+COMMENT ON INDEX idx_imported_by_module_count_desc IS
+'INDEX idx_imported_by_module_count_desc is used to scan popular search documents by module count.';
+
+CREATE INDEX idx_search_documents_imported_by_module_count_updated_at
+ON search_documents (imported_by_module_count_updated_at);
+
+COMMENT ON INDEX idx_search_documents_imported_by_module_count_updated_at IS
+'INDEX idx_search_documents_imported_by_module_count_updated_at is used for incremental updates of module imported_by counts.';
+
+END;