active-users

name	active-users
description	Count distinct users who logged task sessions in production over a given number of days (default 2). Opens a fly proxy, runs the query, then closes the proxy.

Count distinct users who logged task sessions in production over the last N days (default: 2).

Steps

1. Start the Fly proxy in the background:

   fly proxy 5433:5432 -a titr-db &
   sleep 4
   

2. Retrieve the database password at runtime:

   fly ssh console -a tikkit -C "printenv DATABASE_URL"
   

   This returns the full connection string. Extract the password from it.

3. Run the query (replace 2 with the requested number of days if specified):

   SELECT COUNT(DISTINCT user_id) AS active_users
   FROM user_data,
     jsonb_array_elements(tasks_json::jsonb -> 'tasks') AS task,
     jsonb_array_elements(task -> 'sessions') AS session
   WHERE (session ->> 'start')::bigint
       > (EXTRACT(EPOCH FROM NOW() - INTERVAL '<N> days') * 1000)::bigint;
   

   Full command (substitute the password retrieved above):

   psql "postgres://tikkit:<password>@localhost:5433/tikkit?sslmode=disable" -c "<query above>"
   

4. Report the result clearly to the user.

5. Kill the proxy process when done.

Notes

• The proxy may already be running from a previous call — the bind error is harmless, the query will still work.
• Timestamps in sessions are JavaScript milliseconds (epoch × 1000).