Domains: Existing Domains Not Visible on Admin Page After Login

[Samk13]

Package version (if known): v12

Describe the bug

When attempting to create a new domain on the administration domain page works, however existing domains in the database created when users login are not visible.

Running invenio rdm rebuild-all-indices -o domains resolves the issue, and the domains then appear correctly on the domain admin page, so my guess that this issue arises because the domains are not indexed after the user logs in.

Steps to Reproduce

1. From a fresh instance, go to the administration domain page.
2. Notice that the domain you logged in with is not showing.
3. Attempt to create the domain manually you will get an error stating "Domain already exists.".
4. Try hit the domain with the API {baseurl}/domains notice its empty.
5. Check the db table, and notice the domain exists.
6. run invenio rdm rebuild-all-indices -o domains then the domain will appear in the UI and API results

Observe that the user statistics (Active, Inactive, Confirmed, Verified, Blocked) all display 0 despite there being real values in the system.

Expected behavior

The system should automatically index domains upon login, so all domains in the database are visible on the domain admin page without requiring a manual index rebuild.

Additional context

missing index step here?
https://github.com/inveniosoftware/invenio-accounts/blob/2ad7fdcd7c4b718828a450ba09109988be401a8e/invenio_accounts/domains.py#L22

When performing a search for a domain, the following error may occur:

search for domain error

opensearchpy.exceptions.RequestError: RequestError(400, 'search_phase_execution_exception', 'failed to create query: For input string: "test"'). The error arises from the API request GET /api/domains?q=test&sort=domain&page=1&size=10, resulting in a 400 error.

https://github.com/inveniosoftware/invenio-users-resources/pull/124/files

manually adding domain error Log

Traceback (most recent call last):
File "/home/user/.pyenv/versions/3.9.19/envs/v12-install/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
self.dialect.do_execute(
File "/home/user/.pyenv/versions/3.9.19/envs/v12-install/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
cursor.execute(statement, parameters)
psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "uq_accounts_domains_domain"
DETAIL: Key (domain)=(test2.com) already exists.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "/home/user/.pyenv/versions/3.9.19/envs/v12-install/lib/python3.9/site-packages/flask/app.py", line 1823, in full_dispatch_request
rv = self.dispatch_request()
File "/home/user/.pyenv/versions/3.9.19/envs/v12-install/lib/python3.9/site-packages/flask/app.py", line 1799, in dispatch_request
return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
File "/home/user/.pyenv/versions/3.9.19/envs/v12-install/lib/python3.9/site-packages/flask_resources/resources.py", line 65, in view
return view_meth()
File "/home/user/.pyenv/versions/3.9.19/envs/v12-install/lib/python3.9/site-packages/flask_resources/content_negotiation.py", line 116, in inner_content_negotiation
return f(*args, **kwargs)
File "/home/user/.pyenv/versions/3.9.19/envs/v12-install/lib/python3.9/site-packages/flask_resources/parsers/decorators.py", line 51, in inner
return f(self, *args, **kwargs)
File "/home/user/.pyenv/versions/3.9.19/envs/v12-install/lib/python3.9/site-packages/flask_resources/parsers/decorators.py", line 90, in inner
return f(self, *args, **kwargs)
File "/home/user/.pyenv/versions/3.9.19/envs/v12-install/lib/python3.9/site-packages/flask_resources/responses.py", line 39, in inner
res = f(*args, **kwargs)
File "/home/user/.pyenv/versions/3.9.19/envs/v12-install/lib/python3.9/site-packages/invenio_records_resources/resources/records/resource.py", line 93, in create
item = self.service.create(
File "/home/user/.pyenv/versions/3.9.19/envs/v12-install/lib/python3.9/site-packages/invenio_records_resources/services/uow.py", line 376, in inner
res = f(self, *args, **kwargs)
File "/home/user/.pyenv/versions/3.9.19/envs/v12-install/lib/python3.9/site-packages/invenio_records_resources/services/records/service.py", line 327, in create
return self._create(self.record_cls, identity, data, uow=uow, expand=expand)
File "/home/user/.pyenv/versions/3.9.19/envs/v12-install/lib/python3.9/site-packages/invenio_records_resources/services/uow.py", line 380, in inner
return f(self, *args, **kwargs)
File "/home/user/.pyenv/versions/3.9.19/envs/v12-install/lib/python3.9/site-packages/invenio_records_resources/services/records/service.py", line 364, in _create
uow.register(RecordCommitOp(record, self.indexer))
File "/home/user/.pyenv/versions/3.9.19/envs/v12-install/lib/python3.9/site-packages/invenio_records_resources/services/uow.py", line 349, in register
op.on_register(self)
File "/home/user/.pyenv/versions/3.9.19/envs/v12-install/lib/python3.9/site-packages/invenio_records_resources/services/uow.py", line 176, in on_register
self.record.commit()
File "/home/user/.pyenv/versions/3.9.19/envs/v12-install/lib/python3.9/site-packages/invenio_users_resources/records/api.py", line 108, in commit
db.session.add(self.model.model_obj)
File "/home/user/.pyenv/versions/3.9.19/envs/v12-install/lib/python3.9/site-packages/sqlalchemy/engine/util.py", line 237, in exit
self.rollback()
File "/home/user/.pyenv/versions/3.9.19/envs/v12-install/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in exit
compat.raise(
File "/home/user/.pyenv/versions/3.9.19/envs/v12-install/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 211, in raise
raise exception
File "/home/user/.pyenv/versions/3.9.19/envs/v12-install/lib/python3.9/site-packages/sqlalchemy/engine/util.py", line 233, in exit
self.commit()
File "/home/user/.pyenv/versions/3.9.19/envs/v12-install/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 832, in commit
self._prepare_impl()
File "/home/user/.pyenv/versions/3.9.19/envs/v12-install/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 811, in _prepare_impl
self.session.flush()
File "/home/user/.pyenv/versions/3.9.19/envs/v12-install/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 3449, in flush
self._flush(objects)
File "/home/user/.pyenv/versions/3.9.19/envs/v12-install/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 3589, in flush
transaction.rollback(capture_exception=True)
File "/home/user/.pyenv/versions/3.9.19/envs/v12-install/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in exit
compat.raise(
File "/home/user/.pyenv/versions/3.9.19/envs/v12-install/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 211, in raise
raise exception
File "/home/user/.pyenv/versions/3.9.19/envs/v12-install/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 3549, in _flush
flush_context.execute()
File "/home/user/.pyenv/versions/3.9.19/envs/v12-install/lib/python3.9/site-packages/sqlalchemy/orm/unitofwork.py", line 456, in execute
rec.execute(self)
File "/home/user/.pyenv/versions/3.9.19/envs/v12-install/lib/python3.9/site-packages/sqlalchemy/orm/unitofwork.py", line 630, in execute
util.preloaded.orm_persistence.save_obj(
File "/home/user/.pyenv/versions/3.9.19/envs/v12-install/lib/python3.9/site-packages/sqlalchemy/orm/persistence.py", line 245, in save_obj
_emit_insert_statements(
File "/home/user/.pyenv/versions/3.9.19/envs/v12-install/lib/python3.9/site-packages/sqlalchemy/orm/persistence.py", line 1238, in _emit_insert_statements
result = connection._execute_20(
File "/home/user/.pyenv/versions/3.9.19/envs/v12-install/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1710, in _execute_20
return meth(self, args_10style, kwargs_10style, execution_options)
File "/home/user/.pyenv/versions/3.9.19/envs/v12-install/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection
return connection._execute_clauseelement(
File "/home/user/.pyenv/versions/3.9.19/envs/v12-install/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1577, in _execute_clauseelement
ret = self._execute_context(
File "/home/user/.pyenv/versions/3.9.19/envs/v12-install/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1953, in _execute_context
self.handle_dbapi_exception(
File "/home/user/.pyenv/versions/3.9.19/envs/v12-install/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2134, in handle_dbapi_exception
util.raise(
File "/home/user/.pyenv/versions/3.9.19/envs/v12-install/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 211, in raise
raise exception
File "/home/user/.pyenv/versions/3.9.19/envs/v12-install/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
self.dialect.do_execute(
File "/home/user/.pyenv/versions/3.9.19/envs/v12-install/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "uq_accounts_domains_domain"
DETAIL: Key (domain)=(test2.com) already exists.

[SQL: INSERT INTO accounts_domains (created, updated, domain, tld, status, flagged, flagged_source, org_id, category, num_users, num_active, num_inactive, num_confirmed, num_verified, num_blocked) VALUES (%(created)s, %(updated)s, %(domain)s, %(tld)s, %(status)s, %(flagged)s, %(flagged_source)s, %(org_id)s, %(category)s, %(num_users)s, %(num_active)s, %(num_inactive)s, %(num_confirmed)s, %(num_verified)s, %(num_blocked)s) RETURNING accounts_domains.id]
[parameters: {'created': datetime.datetime(2024, 8, 6, 13, 37, 58, 802614), 'updated': datetime.datetime(2024, 8, 6, 13, 37, 58, 802619), 'domain': 'test2.com', 'tld': 'com', 'status': 1, 'flagged': True, 'flagged_source': '', 'org_id': None, 'category': 3, 'num_users': 0, 'num_active': 0, 'num_inactive': 0, 'num_confirmed': 0, 'num_verified': 0, 'num_blocked': 0}]
(Background on this error at: https://sqlalche.me/e/14/gkpj)
127.0.0.1 - - [06/Aug/2024 15:37:58] "POST /api/domains HTTP/1.1" 400 -