Development chronicle — mb626 to mb628 · Full-period AI summaries, index-friendly date queries and a final runtime truth check
This round looks small from the command line, but it fixes something fundamental: when Mediabot says it summarized a period, it must have genuinely covered that period.
The work started with ai summary today, moved into MariaDB query efficiency, and ended with a final audit that forced the tests and runtime to tell exactly the same story.
No database schema change was required.
Earlier work had already improved ai summary by raising the fetch ceiling and spreading the prompt sample across the messages that had been loaded.
That was better, but it still hid a structural limitation.
The database read itself remained equivalent to:
ORDER BY id_channel_log DESC
LIMIT 1500
On a channel with 4,000 messages in one day, ai summary today therefore read only the latest 1,500.
The sampler could distribute its 400 prompt messages beautifully — but only inside that final slice of the day.
The grimoire claimed to have read the chapter while holding only its last pages.
When a requested period contains more messages than the direct-fetch ceiling, Mediabot now divides the actual time window into eight equal slices.
Conceptually:
00:00 23:59
|--------|--------|--------|--------|--------|--------|--------|--------|
1 2 3 4 5 6 7 8
Each slice contributes its own share of messages.
This makes the fetched material representative of the entire period rather than only its busiest or most recent end.
The boundaries are calculated by MariaDB, and every slice remains a range on:
(id_channel, ts)
rather than applying a function to the timestamp column.
Before mb626, a capped read could only announce something approximate:
1500+ messages in the period
The command now performs an indexed COUNT(*) over the same requested period and the same optional nickname filter.
That matters.
A summary of one user must not announce the total traffic of the whole channel.
The result can now say:
4213 messages in the period - summarising a distributed sample of 400.
instead of exposing only the size of the internal fetch ceiling.
The new machinery does not punish ordinary requests.
If the requested period contains fewer messages than the ceiling, Mediabot keeps the simple path:
COUNT
|
+-- below ceiling --> one normal indexed read
|
+-- above ceiling --> eight bounded reads
So the more expensive sliced strategy is reserved for periods that actually require it.
While reviewing the period code, another old class of query problems was worth finishing.
The database already has the useful composite index:
(id_channel, ts)
But an index cannot help much when SQL wraps the indexed timestamp column inside functions such as:
DATE(ts)
YEAR(ts)
MONTH(ts)
DAY(ts)
For a large CHANNEL_LOG, that difference matters.
Instead of:
DATE(ts) >= some_date
the query should preferably look like:
ts >= some_date
or a bounded interval:
ts >= start
AND ts < end
mb627 finishes three remaining opportunities.
Before:
DATE(ts) >= CURDATE() - INTERVAL 365 DAY
After:
ts >= CURDATE() - INTERVAL 365 DAY
Before:
YEAR(ts) = ...
AND MONTH(ts) = ...
After:
[first day of current month,
first day of next month)
onthisday for a known yearBefore:
YEAR(ts) = ?
AND MONTH(ts) = ?
AND DAY(ts) = ?
After:
[start of requested day,
start of following day)
These ranges express the same date selection while giving MariaDB a much better chance to use the timestamp portion of the index.
onthisday query deliberately stays differentNot every use of MONTH(ts) and DAY(ts) is automatically wrong.
The historical onthisday query that searches the same month and day across every year cannot be represented as one continuous timestamp interval.
Its purpose is inherently cross-year, and its grouping by year reflects that.
That query therefore remains in its functional form, with an explanation in the source so a future cleanup does not “optimize” it into incorrect behaviour.
Turning one equality expression into a bounded range often repeats the same date expression twice:
ts >= X
AND
ts < X + 1 day
With placeholders, this also means supplying the corresponding values twice.
That is easy to get wrong while still producing syntactically valid SQL.
The regression test therefore checks not only the query shape but also:
Performance work is useless if the optimized query returns the wrong records.
The final pre-commit audit found an important contradiction.
mb627 had correctly converted the onthisday top-talker query to an index-friendly day range.
But an older regression test still expected the previous form:
MONTH(ts) = ?
AND DAY(ts) = ?
Both statements could not simultaneously describe the current code.
The historical test was therefore updated to verify the new runtime contract, including the range and its bind arity.
This is precisely why old regression tests should survive long enough to challenge new optimizations — and why they must be updated when the intended contract genuinely changes.
ai summary last keeps its strict boundaryThe period-slicing refactor introduced another tiny semantic drift.
Historically:
last
meant messages strictly after the timestamp recorded for the previous summary:
ts > previous_summary_timestamp
The generic range machinery had turned that lower boundary into:
ts >= previous_summary_timestamp
That could replay a message whose timestamp landed exactly on the saved second.
mb628 restores the strict > boundary for the first slice of last, while normal calendar periods continue using inclusive starts.
mb626 introduced a truthful exact count before reading a large period.
That count is not merely cosmetic — it determines whether Mediabot should use one read or the eight-slice strategy.
The final audit therefore tightened the failure mode.
If the exact period COUNT(*) cannot be obtained, Mediabot now returns:
DB error.
and stops.
It does not silently fall back to the old capped recent-message read.
Otherwise the original bug could reappear exactly when the mechanism designed to prevent it was unavailable.
This commit is protected by the following focused tests:
709 historical onthisday date contract, now aligned with mb627
809 complete-period slicing and exact-count runtime behaviour
810 remaining sargable CHANNEL_LOG date predicates
811 final runtime/test coherence and recent test-number uniqueness
The final guard also verifies that recently added test numbers remain unique.
That small check matters after several development rounds where features and tests were renumbered before publication.
These changes are not micro-optimizations for an empty development table.
CHANNEL_LOG is exactly the kind of table that grows continuously and makes innocent-looking date expressions expensive over time.
This round therefore improves two kinds of truth at once:
Application truth
"today" really covers today
Database truth
the query shape lets the index do its job
Regression truth
tests describe the runtime that is actually committed
Those three need to stay aligned.
The grimoire used to open the last pages of a long chapter, spread them neatly across the desk, and claim it had read the whole story.
Now it walks the shelves from beginning to end.
The librarian has also stopped inspecting every book just to find one date; the catalogue is finally being used where it can help.
And before the castle records the work in ink, the final proofreader checks one last thing:
the spell, the test and the story must all say the same thing.
You must be logged in to reply.