[Bug]: oc_activity design easily leads to bloat, see current usage by "recognize" app

⚠️ This issue respects the following points: ⚠️

• This is a bug, not a question or a configuration/webserver/proxy issue.
• This issue is not already reported on Github (I've searched it).
• Nextcloud Server is up to date. See Maintenance and Release Schedule for supported versions.
• Nextcloud Server is running on 64bit capable CPU, PHP and OS.
• I agree to follow Nextcloud's Code of Conduct.

Bug description

The current design of the oc_activity table makes it easy or even necessary for apps that modify "systemtags" automatically to waste enormous recources in a short period of time - without noticeable benefit to the users. I am not deep into the uses and requirments of oc_activity but logging number_of_files * number_of_feature_catalog_entries * number_of_users entries of seemingly useless systemtag changes seems not useful. So, could apps be enabled to disable oc_activity logging for their changes?
We have gathered over 7 GB of oc_activity data mainly caused by recognize over only 90 days with about 30,000 files and 100 users.

See https://github.com/nextcloud/recognize/issues/844 for the corresponding issue in nextcloud/recognize.

Steps to reproduce

1. Install and enable recognize on a medium sized NextCloud server.
2. Wait a few weeks.
3. Do:

mysql -t <<SQL
SELECT table_schema, table_name, table_rows, data_length, index_length,
round(((data_length + index_length) / 1024 / 1024),2) "MB Size"
FROM information_schema.TABLES
ORDER BY (data_length + index_length) ASC;
SQL

5. Be puzzled why oc_activity is so huge.

Expected behavior

No app can by simple metadata manipulations cause such table bloat on oc_activity.

Installation method

Community Manual installation with Archive

Nextcloud Server version

26

Operating system

Debian/Ubuntu

PHP engine version

PHP 8.1

Web server

Nginx

Database engine version

MariaDB

Is this bug present after an update or on a fresh install?

None

Are you using the Nextcloud Server Encryption module?

None

What user-backends are you using?

• Default user-backend (database)
• LDAP/ Active Directory
• SSO - SAML
• Other

Configuration report

# sudo -u www-data php occ config:list system                                                                                                                                                        {         
    "system": {                                   
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "cron.lockfile.location": "<redacted>",
        "dbtype": "mysql",
        "version": "26.0.1.1",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "default_phone_region": "DE",
        "dbtableprefix": "oc_",
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "default_locale": "de_DE",
        "activity_expire_days": 90,
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "installed": true,
        "forcessl": true,
        "maxZipInputSize": 838860800,
        "defaultapp": "apporder",
        "allowZipDownload": true,
        "mail_smtpmode": "smtp",
        "mail_smtphost": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpport": "587",
        "mail_smtptimeout": 10,
        "mail_smtpauthtype": "PLAIN",
        "trusted_domains": [
            "<redacted>",
            "<redacted>",
            "<redacted>",
            "<redacted>",
            "<redacted>",
            "<redacted>"
        ],
        "memcache.local": "\\OC\\Memcache\\Redis",
        "redis": {
            "host": "***REMOVED SENSITIVE VALUE***",
            "port": 6379
        },
        "activity_use_cached_mountpoints": "true",
        "loglevel": 0,
        "secret": "***REMOVED SENSITIVE VALUE***",
        "forceSSLforSubdomains": false,
        "appstore.experimental.enabled": true,
        "trashbin_retention_obligation": "auto",
        "updater.release.channel": "stable",
        "overwrite.cli.url": "https:\/\/<redacted>",
        "default_language": "de",
        "mail_domain": "***REMOVED SENSITIVE VALUE***",
        "mail_from_address": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpauth": 1,
        "mail_smtpname": "***REMOVED SENSITIVE VALUE***",
        "mail_smtppassword": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpsecure": "tls",
        "mysql.utf8mb4": true,
        "has_rebuilt_cache": true,
        "twofactor_enforced": "true",
        "twofactor_enforced_groups": [
            "admin"
        ],
        "twofactor_enforced_excluded_groups": [],
        "theme": "",
        "mail_sendmailmode": "smtp",
        "trusted_proxies": "***REMOVED SENSITIVE VALUE***",
        "maintenance": false,
        "app_install_overwrite": [
            "emlviewer",
            "apporder"
        ]
    }
}

List of activated Apps

After having removed "recognize":

# sudo -u www-data php occ app:list                                                                                                                                                                  Enabled:                           
  - activity: 2.18.0
  - admin_audit: 1.16.0  
  - announcementcenter: 6.6.0
  - apporder: 0.15.0
  - bruteforcesettings: 2.6.0                                                                                                          
  - calendar: 4.3.3                     
  - circles: 26.0.0                                  
  - cloud_federation_api: 1.9.0          
  - comments: 1.16.0                            
  - contacts: 5.2.0        
  - contactsinteraction: 1.7.0                                                                                                                                                                                                                                                
  - dashboard: 7.6.0
  - dav: 1.25.0
  - deck: 1.9.2
  - emlviewer: 1.0.7
  - federatedfilesharing: 1.16.0
  - federation: 1.16.0
  - files: 1.21.1
  - files_external: 1.18.0
  - files_fulltextsearch: 26.0.0
  - files_lock: 27.0.0
  - files_markdown: 2.4.0
  - files_pdfviewer: 2.7.0
  - files_rightclick: 1.5.0
  - files_sharing: 1.18.0
  - files_trashbin: 1.16.0
  - files_versions: 1.19.1
  - firstrunwizard: 2.15.0
  - forms: 3.2.0
  - fulltextsearch: 26.0.0
  - fulltextsearch_elasticsearch: 26.0.0
  - groupfolders: 14.0.2
  - holiday_calendars: 0.3.0
  - impersonate: 1.13.0
  - integration_twitter: 1.0.6
  - keeweb: 0.6.12
  - logreader: 2.11.0
  - lookup_server_connector: 1.14.0
  - mail: 3.1.1     
  - maps: 1.0.2          
  - music: 1.8.3             
  - news: 21.2.0    
  - nextcloud_announcements: 1.15.0                                                                                                    
  - notes: 4.7.2                        
  - notifications: 2.14.0                            
  - notify_push: 0.6.3                   
  - oauth2: 1.14.0                              
  - password_policy: 1.16.0
  - photos: 2.2.0                                                                                                                                                                                                                                                             
  - polls: 5.0.5    
  - privacy: 1.10.0
  - provisioning_api: 1.16.0
  - qownnotesapi: 23.3.0
  - quota_warning: 1.17.0       
  - recommendations: 1.5.0
  - related_resources: 1.1.0-alpha1
  - richdocuments: 8.0.1  
  - serverinfo: 1.16.0          
  - settings: 1.8.0   
  - sharebymail: 1.16.0  
  - spreed: 16.0.3        
  - support: 1.9.0         
  - survey_client: 1.14.0
  - suspicious_login: 4.4.0
  - systemtags: 1.16.0    
  - tasks: 0.14.5         
  - text: 3.7.2 
  - theming: 2.1.1        
  - twofactor_backupcodes: 1.15.0       
  - twofactor_email: 2.7.2
  - twofactor_nextcloud_notification: 3.7.0
  - twofactor_totp: 8.0.0-alpha.0
  - updatenotification: 1.16.0
  - user_retention: 1.10.0
  - user_status: 1.6.0
  - user_usage_report: 1.10.0      
  - viewer: 1.10.0        
  - weather_status: 1.6.0
  - workflowengine: 2.8.0 
Disabled:                               
  - calendar_resource_management: 0.1.1-alpha.1 (installed 0.1.1-alpha.1)
  - encryption: 2.14.0 (installed 1.0.0)   
  - integration_whiteboard: 0.0.14 (installed 0.0.14)
  - sharelisting: 1.1.1 (installed 1.1.1)
  - twofactor_gateway: 0.20.0 (installed 0.20.0)
  - user_ldap: 1.16.0

Nextcloud Signing status

No errors have been found.

Nextcloud Logs

No response

Additional info

No response

Start by reviewing how the activity app writes systemtag changes into oc_activity and compare that behavior with the linked recognize issue #844. Define and validate a design that prevents routine metadata changes from causing disproportionate table growth, including how apps would opt out and how existing activity behavior should be preserved.