Commit: 2fc3ea3
Version: 3.4dev-20260817_131411

MB657 turns Mediabot’s existing time-of-day achievements into a proper measurable progression system.

The key idea was not to add another expensive historical scan. Instead, MB657 reuses and improves the existing hour-band logic so Night Owl and Early Bird progress become visible, scalable and cheaper to calculate.

---

🌙 Night Owl becomes a ladder

The existing Night Owl achievement remains at:

🌙 Night Owl — 50 messages between 00:00 and 05:59

MB657 adds two new milestones:

🌌 Midnight Regular      250 night messages
🦇 Creature of the Night 1000 night messages

All three use the same progress counter:

night_messages

That makes the ladder measurable through the normal Achievement progress views.

---

🌅 Early Bird becomes measurable too

The existing Early Bird achievement remains at:

🌅 Early Bird — 50 messages between 06:00 and 08:59

MB657 now stores its progress through:

morning_messages

So the user can see progress before the unlock instead of having the achievement behave like a hidden boolean threshold.

---

⚙️ One query instead of hourly GROUP BY

The historical implementation used:

GROUP BY HOUR(cl.ts)

and reconstructed the bands in Perl.

MB657 replaces that with one conditional aggregate:

SUM(CASE
    WHEN HOUR(cl.ts) BETWEEN 0 AND 5
    THEN 1 ELSE 0
END)

SUM(CASE
    WHEN HOUR(cl.ts) BETWEEN 6 AND 8
    THEN 1 ELSE 0
END)

One query now returns both counters directly.

That also removes the old grouped-hour path that was known to encourage temporary-table/filesort work.

---

🧠 Smarter scan short-circuit

The previous MB450 guard effectively assumed a fixed threshold of 50.

That was fine when each hour-band had only one achievement, but it becomes wrong once the ladder grows.

MB657 now computes the lowest threshold that is still locked.

Conceptually:

Night Owl unlocked?
  ↓ yes
Midnight Regular still locked?
  ↓
next useful floor = 250

If the user’s total message count cannot possibly reach that next threshold, Mediabot skips the expensive historical hour-band scan.

This also means configured Achievement threshold overrides remain respected.

---

⏱️ Existing throttle preserved

The hour-band scan still keeps the existing throttle:

maximum one scan per hour
per nick/channel

MB657 does not make the historical scan more frequent.

---

🔄 Async worker path updated

The async Achievement worker now transports both new measurable counters back to the parent process:

night_messages
morning_messages

That keeps async Achievement processing consistent with the normal persistence/progress path.

---

🧪 Focused regression

The focused MB657 suite passed:

PASSED : 490/490
RC=0

Coverage included:

message filters
Achievement persistence
channel identity handling
hour-band short-circuit behaviour
deferred checks
async Achievement worker
progress persistence
threshold overrides
progress rendering
MB646 durable identity
MB654 identity diagnostics
MB655 streak achievements
MB656 comeback achievements
MB657 Night Owl ladder
module structure sanity

---

🔎 Source guards

The MB657 structural checks confirmed:

OK: old GROUP BY HOUR path removed
OK: one conditional aggregate supplies night + morning counts
OK: hour-band scan keeps the 3600s throttle
OK: scan floor follows the lowest still-locked threshold
OK: async parent imports night/morning progress from worker
MB657_SOURCE_GUARD=OK

---

🗄️ Real MariaDB equivalence test

The new SQL was compared against the historical implementation using real CHANNEL_LOG data in a read-only MariaDB transaction.

A real historical identity was selected:

NICK           = Balibalo
CHANNEL        = #radiocapsule
TOTAL_MESSAGES = 3925
BAND_MESSAGES  = 1598

Historical result:

OLD_NIGHT   = 964
OLD_MORNING = 634

MB657 result:

NEW_NIGHT   = 964
NEW_MORNING = 634

Validation:

NIGHT_MATCH    = YES
MORNING_MATCH  = YES
READ_ONLY_TRANSACTION=ROLLED_BACK
REAL_MB657_HOURBAND_CHECK=OK
RC=0

So the SQL refactor changes the shape of the query, not its functional result.

---

🧪 Full-suite validation

The complete Mediabot suite passed:

PASSED : 13520/13520
RC=0

Runtime:

870s

---

🧱 Operational scope

MB657 introduces:

no database schema change
no migration
no configuration change
no service change
no systemd change

It reuses:

CHANNEL_LOG
existing Achievement persistence
existing progress counters
existing async Achievement worker
existing MB450 throttle

---

✨ Commit

2fc3ea3
🌙 Turn Night Owls Into a Measurable Achievement Ladder

Version:

3.4dev-20260817_131411

Committed scope:

CHANGELOG.md
VERSION
Mediabot/Achievements.pm
t/cases/665_mb450_achievements_hourband_shortcircuit.t
t/cases/795_mb612_achievement_progress_view.t
t/cases/839_mb657_night_owl_ladder.t

Night Owl was already there.

MB657 gives it somewhere to go.