SP NET MOD TOOL Mod Analytics Bot (PHP + MySQL)

Tracks moderator activity in Telegram groups and generates monthly reward sheets with budget-based suggestions.

Features

Messages sent, warnings, bans, mutes
Active time (message-based) + membership time (join/leave)
Monthly leaderboard and reward suggestions
Attractive HTML reward sheet (shareable)
Eligibility thresholds + anti-spam scoring
Impact score + consistency index + 3-month trend
KPI bonus pool (Top Mod / Most Active / Most Improved)
Performance badges (Top Helper / Most Balanced / Consistency King / Fast Responder)
Approval workflow + audit log exports
Audit scoring logs (every reward calculation recorded)
Reward forecast (mid-month pace projection)
Budget optimizer (minimum budget for fairness targets)
Bonus split planner (badge/role bonus pools)
Conflict/spam risk detector (aggressive moderation vs new users)
Burnout risk alerts (high hours + dropping quality/consistency)
Personal mod dashboard (self stats, badges, feedback)
Daily micro-feedback (private mod tips)
Goal tracker (monthly goals + progress)
Role multipliers for senior/lead moderators
Insights: most active, most improved, most consistent, peak hour
Test payments (Telegram Stars / crypto)
Live dashboard page (auto-refresh)
Manager digest dashboard (board report)
Manager digest PDF export
CSV export + Google Sheets webhook export
Auto-scheduled monthly reports
Mid-month progress reports (MTD)
Weekly summary DM with TL;DR
Multi-chat summary report
External stats import (ChatKeeper/Combot)
Premium subscriptions (plan gating + upgrades)
Coaching tips + team health insights
Team health score + coverage map + action quality review
Retention contributors (proxy score)
Executive summary + trend report + PDF export
Import wizard (dashboard upload)
Mod roster manager + report archive
Owner notifications (report DM, mid-month alerts, congrats templates)
Performance badges shown on dashboard, manager digest, weekly summary, and AI reviews

Quick Start

Ensure MySQL or MariaDB is running.
Run the migration:
- mysql -u root -p < /Users/savanpatel/Documents/SPNET-MODTOOL/migrations/001_init.sql
- If you use MariaDB CLI: mariadb -u root -p < /Users/savanpatel/Documents/SPNET-MODTOOL/migrations/001_init.sql
Run the auto-report migration:
- mysql -u root -p < /Users/savanpatel/Documents/SPNET-MODTOOL/migrations/002_auto_reports.sql
- If you use MariaDB CLI: mariadb -u root -p < /Users/savanpatel/Documents/SPNET-MODTOOL/migrations/002_auto_reports.sql
Run the user settings migration:
- mysql -u root -p < /Users/savanpatel/Documents/SPNET-MODTOOL/migrations/003_user_settings.sql
- If you use MariaDB CLI: mariadb -u root -p < /Users/savanpatel/Documents/SPNET-MODTOOL/migrations/003_user_settings.sql
Run the external stats migration:
- mysql -u root -p < /Users/savanpatel/Documents/SPNET-MODTOOL/migrations/004_external_user_stats.sql
- If you use MariaDB CLI: mariadb -u root -p < /Users/savanpatel/Documents/SPNET-MODTOOL/migrations/004_external_user_stats.sql
Run the external stats extensions migration:
- mysql -u root -p < /Users/savanpatel/Documents/SPNET-MODTOOL/migrations/005_external_user_stats_actions.sql
- If you use MariaDB CLI: mariadb -u root -p < /Users/savanpatel/Documents/SPNET-MODTOOL/migrations/005_external_user_stats_actions.sql
Run the progress report migration:
- mysql -u root -p < /Users/savanpatel/Documents/SPNET-MODTOOL/migrations/006_progress_reports.sql
- If you use MariaDB CLI: mariadb -u root -p < /Users/savanpatel/Documents/SPNET-MODTOOL/migrations/006_progress_reports.sql
Run the subscriptions migration:
- mysql -u root -p < /Users/savanpatel/Documents/SPNET-MODTOOL/migrations/007_subscriptions.sql
Run the mod roster migration:
- mysql -u root -p < /Users/savanpatel/Documents/SPNET-MODTOOL/migrations/008_mod_roster.sql
Run the report archive migration:

mysql -u root -p < /Users/savanpatel/Documents/SPNET-MODTOOL/migrations/009_report_archive.sql

Run the notification log migration:

mysql -u root -p < /Users/savanpatel/Documents/SPNET-MODTOOL/migrations/010_notification_log.sql

Run the reward history migration:

mysql -u root -p < /Users/savanpatel/Documents/SPNET-MODTOOL/migrations/011_reward_history.sql

Run the approvals migration:

mysql -u root -p < /Users/savanpatel/Documents/SPNET-MODTOOL/migrations/012_report_approvals.sql

Run the audit log migration:

mysql -u root -p < /Users/savanpatel/Documents/SPNET-MODTOOL/migrations/013_audit_log.sql

Run the payments migration:

mysql -u root -p < /Users/savanpatel/Documents/SPNET-MODTOOL/migrations/014_payments.sql

Run the weekly summary migration:

mysql -u root -p < /Users/savanpatel/Documents/SPNET-MODTOOL/migrations/015_weekly_summary.sql

Run the inactivity alerts migration:

mysql -u root -p < /Users/savanpatel/Documents/SPNET-MODTOOL/migrations/016_inactivity_alerts.sql

Run the AI review migration:

mysql -u root -p < /Users/savanpatel/Documents/SPNET-MODTOOL/migrations/017_ai_review.sql

Run the retention alerts migration:

mysql -u root -p < /Users/savanpatel/Documents/SPNET-MODTOOL/migrations/018_retention_alerts.sql

Run the inactivity spike alerts migration:

mysql -u root -p < /Users/savanpatel/Documents/SPNET-MODTOOL/migrations/019_inactivity_spikes.sql

Run the goals + daily feedback migration:

mysql -u root -p < /Users/savanpatel/Documents/SPNET-MODTOOL/migrations/020_goals_and_feedback.sql

Copy config overrides:

cp /Users/savanpatel/Documents/SPNET-MODTOOL/config.example.php /Users/savanpatel/Documents/SPNET-MODTOOL/config.local.php

Edit /Users/savanpatel/Documents/SPNET-MODTOOL/config.local.php with your bot token and DB creds.
- Optional: add owner_user_ids and manager_user_ids for staff access.
Run in long-poll mode:

php /Users/savanpatel/Documents/SPNET-MODTOOL/bin/poll.php

Commands

Analytics commands are handled in the bot’s private chat. Moderation commands are disabled in this bot. Use /mychats in private chat to get chat IDs, then set a default with /usechat. If you still get “no permission,” add your Telegram user id to owner_user_ids in config.local.php.

Private chat commands:

/mychats – list your group chat IDs
/usechat <chat_id> (or /usechat <title> or /usechat off)
/menu (command center: status + next steps)
/guide (full usage guide with examples)
/mydashboard [YYYY-MM] (personal mod dashboard)
/goalset <metric> <value> [YYYY-MM] (set monthly goal)
/goalstatus [YYYY-MM]
/goalclear [YYYY-MM]
/mychats (list your group chats)
/usechat <chat_id> (set default chat)
/linkedchat (show default linked chat)
/whoami (your role + user id)
/botusers [days] [limit] (recent bot users)
/stats [chat_id] [YYYY-MM] [@user]
/timesheet <@username|user_id> [YYYY-MM-DD] [YYYY-MM-DD] [chat_id]
/compare <@user1|id1> <@user2|id2> [YYYY-MM] [chat_id]
/weeklysummary [days] (manual weekly summary)
/leaderboard [chat_id] [YYYY-MM] [budget]
/report [chat_id] [YYYY-MM] [budget]
/reportcsv [chat_id] [YYYY-MM] [budget]
/exportgsheet [chat_id] [YYYY-MM] [budget]
/summary [YYYY-MM] [budget] (multi-chat summary)
/plan
/setplan <free|premium|enterprise> [days] (owner only)
/giftplan <chat_id> <free|premium|enterprise> [days] [note] (manager/owner)
/grantplan <chat_id> <free|premium|enterprise> [days] [note] (manager/owner)
/approval on|off <chat_id> (manager/owner)
/approvereport <chat_id> [YYYY-MM] (manager/owner)
/approvalstatus <chat_id> [YYYY-MM] (manager/owner)
/auditlogcsv <chat_id> [limit] (manager/owner)
/premium (see premium benefits)
/pricing (tiers + features)
/buy_stars_test <amount> [chat_id] (manager/owner test purchase)
/buy_crypto_test <amount> [chat_id] (manager/owner test purchase)
/paystatus (latest payment)
/coach [YYYY-MM] (premium)
/health [YYYY-MM] (premium)
/aireview [YYYY-MM] (premium AI performance review)
/retention [YYYY-MM] [threshold%] (premium retention risk alerts)
/trend [YYYY-MM] [budget] (premium)
/execsummary [YYYY-MM] [budget] (premium)
/archive
/setbudget <amount> [chat_id]
/settimezone <Region/City> [chat_id]
/setactivity <gap_minutes> <floor_minutes> [chat_id]
/autoreport on [day] [hour] [chat_id]
/autoreport off [chat_id]
/autoaireview on [day] [hour] [chat_id]
/autoaireview off [chat_id]
/autoretention on [day] [hour] [threshold%] [chat_id]
/autoretention off [chat_id]
/autospike on [hour] [threshold%] [chat_id]
/autospike off [chat_id]
/autofeedback on [hour] [chat_id]
/autofeedback off [chat_id]
/autoreport status [chat_id]
/autoprogress on [day] [hour] [chat_id]
/autoprogress off [chat_id]
/autoprogress status [chat_id]
/progress [chat_id] [budget] (month-to-date)
/forecast [chat_id] [budget] (current month forecast)
/modadd [chat_id] <@username|user_id>
/modremove [chat_id] <@username|user_id>
/modlist [chat_id]
/modaudit [chat_id] [limit]
/rosteradd <@username|user_id> <role> [notes]
/rosterrole <@username|user_id> <role> [notes]
/rosterremove <@username|user_id>
/rosterlist

Tip: You can forward a user’s message to the bot in private chat and reply with /modadd (or /modremove) to avoid hunting for the user id.

Group chat commands:

Moderation commands are disabled.
/mod remove (reply)

In-Depth Usage Guide (with examples)

This section walks through the full workflow from setup to monthly rewards.

1) Add the bot to your groups

Add the bot to each Telegram group you want tracked.
Make the bot an admin or disable privacy mode in BotFather so it can read all messages.
Send any message in the group so the bot can discover the chat.
In private chat with the bot, run:

/mychats

Set a default chat:

/usechat -1001234567890

Optional helpers:

/linkedchat
/whoami

2) Add and manage mods

Use /modadd and /modremove in private chat.

/modadd @alex
/modadd 123456789
/modremove @alex
/modlist
/modaudit 25

Tip: You can forward a user message to the bot in private chat and reply with /modadd to avoid searching for IDs.

3) Check stats and leaderboards

/stats
/stats 2026-02
/stats @alex
/timesheet @alex 2026-02-01 2026-02-28
/compare @alex @maria 2026-02
/weeklysummary 7
/leaderboard
/leaderboard 2026-02

If you do not set /usechat, include the chat id first:

/stats -1001234567890 2026-02 @alex

4) Generate reward sheets

Budget is optional. If provided, the bot splits it across eligible mods based on score.

/report 2026-02 5000
/reportcsv 2026-02 5000

If you do not pass a budget, it will still rank mods and output reward suggestions using config defaults.

5) Mid-month progress check

/progress
/progress 7500
/forecast
/forecast 7500

5b) Admin audit

/botusers 30 20

6) Multi-chat summary (combined view)

/summary 2026-02 12000

7) Coaching, health, trends (premium)

/coach 2026-02
/health 2026-02
/trend 2026-02 5000
/execsummary 2026-02 5000

7b) AI performance reviews (premium)

Auto-generate per-mod feedback summaries:

/aireview 2026-02

7c) Retention risk alerts (premium)

Identify mods with month-over-month drops:

/retention 2026-02 30%

8) Budget and scoring controls

/setbudget 8000
/settimezone Asia/Kolkata
/setactivity 5 1

9) Automation (auto reports + progress checks)

Enable monthly reports and mid-month progress:

/autoreport on 1 9
/autoprogress on 15 12
/autoaireview on 1 9
/autoretention on 2 10 30%
/autospike on 10 35%

The scheduler must run hourly:

php /Users/savanpatel/Documents/SPNET-MODTOOL/bin/run-scheduled.php

10) Dashboard (live stats)

Start the local server:

php -S 127.0.0.1:8000 -t /Users/savanpatel/Documents/SPNET-MODTOOL/public

Open in browser:

http://127.0.0.1:8000/dashboard.php?token=YOUR_TOKEN

Optional query params:

http://127.0.0.1:8000/dashboard.php?token=YOUR_TOKEN&chat_id=-1001234567890&month=2026-02

Manager digest (board report) page:

http://127.0.0.1:8000/manager-digest.php?token=YOUR_TOKEN&chat_id=-1001234567890&month=2026-02&budget=5000

Use filters for Impact, Consistency, Role, and toggle Source breakdown to compare Bot vs ChatKeeper/Combot imports.

11) Import historical data (ChatKeeper/Combot)

CLI import examples:

php /Users/savanpatel/Documents/SPNET-MODTOOL/bin/import-chatkeeper.php --file=/path/analysis_users.csv --chat=-1001234567890 --month=2026-02
php /Users/savanpatel/Documents/SPNET-MODTOOL/bin/import-combot.php --file=/path/combot.csv --chat=-1001234567890 --month=2026-02

Use --replace to overwrite an existing import for the same month. Premium users can also upload from the browser:

http://127.0.0.1:8000/import.php?token=YOUR_TOKEN

12) Roster management

/rosteradd @alex Moderator Night shift lead
/rosterrole @alex Senior Moderator Handles appeals
/rosterlist

13) Premium plans

/plan
/premium
/pricing
/setplan premium 30

14) Approval workflow + audit log

/approval on
/approvereport 2026-02
/approvalstatus 2026-02
/auditlogcsv 200

15) Test payments (Telegram Stars + crypto)

/buy_stars_test 500
/buy_crypto_test 25
/paystatus

Edit payments tiers in /Users/savanpatel/Documents/SPNET-MODTOOL/config.local.php to map purchases to plans. For the Stars sandbox UI, enable telegram.test_environment=true and use a Telegram test bot token. For crypto sandbox UI, set dashboard.base_url and open the checkout link provided by /buy_crypto_test. Ensure polling.allowed_updates includes pre_checkout_query for Stars payments.

16) Export to Google Sheets

/exportgsheet 2026-02 5000

17) Troubleshooting

Bot not responding: check DNS/network on the host, then run curl -I https://api.telegram.org.
Bot silent in groups: expected (reports go to the reports channel + manager DMs).
"No permission": ensure you are an admin in that group or add your user id to owner_user_ids in /Users/savanpatel/Documents/SPNET-MODTOOL/config.local.php.
"No mods are added": run /modadd first.
"No group chats found": add the bot to a group and send any message there, then run /mychats.

Notes

Moderation commands are disabled; the bot is analytics-only.
The bot stays silent in groups and does not DM mods. Reports go to the reports channel + manager DMs.
“Active time” is estimated from message gaps (configurable).
“Membership time” is time between join and leave events, not actual presence.
Scoring uses log/sqrt scaling and day normalization. Tune it in score_weights and score_rules.
Reward eligibility is controlled by eligibility in config.local.php (days, messages, score, actions, active hours).
Approvals can be required per chat with /approval on; audit logs are downloadable with /auditlogcsv.
Restrict the bot to specific groups by listing IDs in security.whitelist_chat_ids.

Premium Features

Use /plan to view the current plan and /setplan premium 30 to enable premium (owner only).
Fair reward engine with anti-spam caps + day normalization.
KPI bonus pool with Top Mod / Most Active / Most Improved badges.
Smarter rewards with max-share cap, stability bonus, and penalty decay (see premium.reward in config).
Coaching tips and team health (coverage gaps, workload balance, burnout risk).
Executive summary + trend report + PDF export.
Import wizard with ChatKeeper/Combot source breakdown.
Approval workflow + audit log exports.
Report archive + reward history.
Owner notifications (auto report DMs, mid-month alerts, congrats templates).
Log channel + changelog updates.
Enterprise add-ons: white-label branding, assisted setup, scoring calibration, dedicated support + SLA.
PDF export requires wkhtmltopdf on the host.

Webhook (optional)

You can use /Users/savanpatel/Documents/SPNET-MODTOOL/public/webhook.php as your Telegram webhook handler.

Make It Live (launchd on macOS)

Copy the launchd plists:
- cp /Users/savanpatel/Documents/SPNET-MODTOOL/ops/launchd/com.spnet.modtool.bot.plist ~/Library/LaunchAgents/
- cp /Users/savanpatel/Documents/SPNET-MODTOOL/ops/launchd/com.spnet.modtool.scheduler.plist ~/Library/LaunchAgents/
Load them:
- launchctl bootstrap gui/$(id -u) ~/Library/LaunchAgents/com.spnet.modtool.bot.plist
- launchctl bootstrap gui/$(id -u) ~/Library/LaunchAgents/com.spnet.modtool.scheduler.plist
Check logs:
- /Users/savanpatel/Documents/SPNET-MODTOOL/storage/logs/poll.out.log
- /Users/savanpatel/Documents/SPNET-MODTOOL/storage/logs/poll.err.log
- /Users/savanpatel/Documents/SPNET-MODTOOL/storage/logs/scheduler.out.log
- /Users/savanpatel/Documents/SPNET-MODTOOL/storage/logs/scheduler.err.log

Polling Speed

Adjust polling in /Users/savanpatel/Documents/SPNET-MODTOOL/config.local.php to control speed:

timeout_seconds (long-poll timeout)
limit (max updates per request)
sleep_ms (pause between loops)

Live Dashboard

Set dashboard.token in config.local.php
Start a local server:
- php -S 127.0.0.1:8000 -t /Users/savanpatel/Documents/SPNET-MODTOOL/public
Open http://127.0.0.1:8000/dashboard.php?token=YOUR_TOKEN
Optional: add &chat_id=CHAT_ID&month=YYYY-MM
Use “All Chats” for the multi-chat view
Export buttons call /Users/savanpatel/Documents/SPNET-MODTOOL/public/export.php
Filters: search, min_messages, min_actions, min_active_hours, min_score, only_eligible, only_improving, limit, compact, show_sources, refresh
Premium: PDF export, executive summary, trend report, and import wizard

Log Channel

Send bot logs + changelog updates to a Telegram channel.

Create a channel and add the bot as admin.
Set logging.channel_id in /Users/savanpatel/Documents/SPNET-MODTOOL/config.local.php (format: -1001234567890).
Optional: set logging.log_updates = true to log every message update (very noisy).

Reports Channel

Send analytics and reward reports to a dedicated reports-only channel (no logs).

Create a channel and add the bot as admin.
Set reports.channel_id in /Users/savanpatel/Documents/SPNET-MODTOOL/config.local.php (format: -1001234567890).
Reports will also be sent to all manager_user_ids + owner_user_ids.
To allow mod DMs for micro-feedback, set reports.send_to_mods = true.
Set reports.detailed_channel = true to add a detailed header + unique Report ID in the channel.

Google Sheets Export (optional)

This uses a webhook URL from Google Apps Script. Set google_sheets.webhook_url in config.local.php.

Example Apps Script (deploy as Web App):

function doPost(e) {
  var data = JSON.parse(e.postData.contents);
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Rewards');
  if (!sheet) sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet('Rewards');
  sheet.clear();
  sheet.appendRow(['Rank','Mod','Score','Messages','Warnings','Mutes','Bans','Active Hours','Membership Hours','Days Active','Improvement %','Reward']);
  data.rows.forEach(function(r) {
    sheet.appendRow([r.rank,r.mod,r.score,r.messages,r.warnings,r.mutes,r.bans,r.active_hours,r.membership_hours,r.days_active,r.improvement,r.reward]);
  });
  return ContentService.createTextOutput('ok');
}

Auto Reports

Run this script hourly via cron (or a scheduler):

php /Users/savanpatel/Documents/SPNET-MODTOOL/bin/run-scheduled.php It sends the previous month’s report on the configured day/hour in the chat’s timezone. Reports and progress sheets are delivered to reports.channel_id + manager DMs. Progress reports (MTD) are sent when /autoprogress is enabled.

Import ChatKeeper CSV (for backfill)

If you have a ChatKeeper export (like analysis_users.csv), import it with:

php /Users/savanpatel/Documents/SPNET-MODTOOL/bin/import-chatkeeper.php --file=/path/analysis_users.csv --chat=-1001234567890 --month=YYYY-MM Add --replace to overwrite an existing import for the same month. Imported message counts are added to the monthly message total for each mod.

Import Combot CSV (for backfill)

If you have a Combot export, import it with:

php /Users/savanpatel/Documents/SPNET-MODTOOL/bin/import-combot.php --file=/path/combot.csv --chat=-1001234567890 --month=YYYY-MM Add --replace to overwrite an existing import for the same month. Warnings, mutes, bans, and active time (if present in the CSV) are merged into monthly stats.

Import Wizard (Premium)

Open:

http://127.0.0.1:8000/import.php?token=YOUR_TOKEN Upload ChatKeeper/Combot CSV files directly from the browser.

Name		Name	Last commit message	Last commit date
.github/workflows		.github/workflows
bin		bin
migrations		migrations
ops/launchd		ops/launchd
public		public
src		src
tests		tests
.gitignore		.gitignore
LICENSE		LICENSE
README.md		README.md
composer.json		composer.json
composer.lock		composer.lock
config.example.php		config.example.php
config.php		config.php
phpunit.xml		phpunit.xml

Latest commit

History

Folders and files

Repository files navigation