Enhancement: custom module installs (with community telemetry to track installs)

[gmarcon]

(First of all, thank you for this module, I find the idea genius and it greatly simplified managing custom webtrees modules: updating existing modules, finding new modules, easily testing new modules..)

I would like to propose a new community feature for the Custom Module Manager: gathering and displaying anonymous installation statistics for custom modules.

Knowing which custom modules are widely used benefits the entire ecosystem. It helps site administrators discover trusted and popular extensions, helps module developers prioritize maintenance/security updates, and helps core webtrees maintainers plan around API changes by seeing which hooks or dependencies are heavily relied upon.

An option would have been to have this feature directly in the latest version check of webtrees, I opened an issue 5377 and a pull request 5378 in the main project but @fisharebest is skeptical about including it in the core.

On afterthought, as your module is already a reasonable entry point to the custom modules world (with the catalogue of available/known custom modules),
I think it makes perfect sense and your module is the natural home for this feature.

I made a first proposal of the implementation for you to review that you can find describe below and in the pull request that I will open.

• Data is stored on a serverless supabase backend on a dedicated account (webtrees-custom-modules-telemetry@proton.me) with two RPC endpoints, one for telemetry (posts the site unique uuid and the installed modules) and one for getting the statistics (how many installs for each module in the sites that sent telemetry in the last 60 days).
• The changes in the module contained in the pull request are:
  • settings for disabling telemetry, parameters for connecting to the telemetry server
  • telemetry call included in the modules update check (if not opted out)
  • statistics retrieval for the modules table, with the possibility of sorting by number of installs to see popular modules (if not opted out)

These are the supabase tables and services:

CREATE TABLE modules (
    id SERIAL PRIMARY KEY,
    module_name TEXT UNIQUE NOT NULL
);
ALTER TABLE modules ENABLE ROW LEVEL SECURITY;

CREATE TABLE site_telemetry (
    site_uuid TEXT PRIMARY KEY,
    installed_modules INT[] NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
);
ALTER TABLE site_telemetry ENABLE ROW LEVEL SECURITY;

CREATE OR REPLACE FUNCTION submit_telemetry(p_site_uuid TEXT, p_modules_list TEXT[])
RETURNS VOID AS $$
DECLARE
  module_ids INT[] := '{}';
  m_name TEXT;
  m_id INT;
BEGIN
  -- Loop through text names and ensure they exist in our lookup table, gathering their IDs
  FOREACH m_name IN ARRAY p_modules_list LOOP
    INSERT INTO modules (module_name) 
    VALUES (m_name) 
    ON CONFLICT (module_name) DO UPDATE SET module_name = EXCLUDED.module_name
    RETURNING id INTO m_id;
    
    module_ids := array_append(module_ids, m_id);
  END LOOP;

  -- Upsert the site data. One site uuid = exactly one row of truth.
  INSERT INTO site_telemetry (site_uuid, installed_modules, updated_at)
  VALUES (p_site_uuid, module_ids, now())
  ON CONFLICT (site_uuid) 
  DO UPDATE SET installed_modules = EXCLUDED.installed_modules, updated_at = now();
END;
$$ LANGUAGE plpgsql SECURITY DEFINER; --allows the function to bypass RLS and write to the tables

CREATE OR REPLACE FUNCTION get_module_statistics()
RETURNS TABLE(module_name TEXT, active_installs BIGINT) AS $$
BEGIN
  RETURN QUERY
  SELECT 
    m.module_name, 
    COUNT(s.site_uuid) as active_installs
  FROM 
    modules m
    JOIN site_telemetry s ON m.id = ANY(s.installed_modules)
  WHERE 
    s.updated_at > now() - INTERVAL '60 days' -- Filter out dead/abandoned sites
  GROUP BY 
    m.module_name
  ORDER BY 
    active_installs DESC;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

If you are OK with this approach, I would make you administrator of the supabase organization containining the project and hand you over the account webtrees-custom-modules-telemetry@proton.me.

[gmarcon]

This is a screenshot of the settings:

And this is a screenshot of the modules list with the new column "Installs":