mirror of
https://github.com/kuhyx/signal-bot.git
synced 2026-11-10 18:17:02 +01:00
Krzysztof kuhy Rudnicki
405541be9b
One row per command attempt, not two aggregates written only on success. command_uses holds last-use only and daily_uses counts successes; both are rate-limiter state and both are written by store.record_use, which the dispatcher calls only when a handler returns cleanly. A gate refusal and a crash returned before it, so a command that failed every time looked exactly like one nobody ran, and nothing knew which command a counted message was. command_events now takes every attempt with its outcome, its duration and the item it served; upstream_calls takes every outbound request made while serving one. The two writes stay separate on purpose -- recording a refusal as a use is what slid the cooldown window forward every time somebody was told to wait -- and test_dispatcher_outcomes asserts last_used does not move. Backfilled from journald joined against message_events, with daily_uses filling the residual so the two sources cannot double-count. Two things were wrong on the first attempt and both were found by measuring rather than by reasoning: the window has to be symmetric, because sent_at_ms comes from the sender's phone and the journal stamp from this host (194 of 299 lines landed *before* their own message, by up to 3.2 s); and matching has to be a global closest-pair assignment, because per-row greedy cannot separate two commands seconds apart. Together those took the dropped rows from 137 to zero, and recovered 418 invocations back to 29 August. Also: !analityka in the group, twelve Polish lines, naming at most three people and only ones whose display name has been seen; an Analytics tab with Nivo charts; and continuous latency measurement, which answers the open question in DOCS-latency.md -- a text send is 235 ms and an image send about 2.2 s, so the upload really is where the time goes. EPHEMERAL_TABLES was doing two unrelated jobs and is now two tuples: what a schema bump rebuilds, and what leaving a group clears. daily_uses left the first (it is a backfill source now) and stayed in the second, and _drop_reshaped took over the repair a bump used to do for it. Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_01T4QTp3h5Q3MvfrwGV8xqPf
79 lines
3.6 KiB
Python
79 lines
3.6 KiB
Python
# Copyright (c) 2026 Krzysztof Rudnicki. MIT licence.
|
|
"""The command history's own tables, kept apart from the rest of the schema.
|
|
|
|
Split out of ``schema.py`` so both stay under the 250-line cap, the same
|
|
reason ``schema.py`` was split out of ``store.py``. It is applied straight
|
|
after ``SCHEMA`` and by the same method, so this is a filing decision and
|
|
not a second schema: there is one database and one ``PRAGMA user_version``.
|
|
|
|
Neither table is ever dropped -- see ``PERMANENT_TABLES``. The whole point
|
|
of them is answering questions about a past that nothing can recompute.
|
|
"""
|
|
|
|
from __future__ import annotations
|
|
|
|
from typing import Final
|
|
|
|
COMMAND_SCHEMA: Final = """
|
|
-- One row per command *attempt*, including the ones that never ran. The two
|
|
-- tables above it are aggregates a rate limiter reads; this is the history,
|
|
-- and nothing recomputes it.
|
|
--
|
|
-- Keyed on the envelope's own timestamp for the reason unknown_commands is:
|
|
-- signal-cli replays undelivered frames on reconnect, and a clock reading is
|
|
-- fresh on every replay. invoked_at_ms is message.timestamp, so a replay
|
|
-- collides with the row it already wrote instead of adding a second one.
|
|
--
|
|
-- source is part of the key because a backfilled row and a live row can
|
|
-- describe the same invocation from two sources that disagree about the
|
|
-- hour, and neither may overwrite the other.
|
|
CREATE TABLE IF NOT EXISTS command_events (
|
|
group_id TEXT NOT NULL,
|
|
user_uuid TEXT NOT NULL,
|
|
command TEXT NOT NULL,
|
|
-- Never free text. A pack pseudonym key the registry already resolved
|
|
-- ('p1', 'add', 'random'), or '' / '+args'. See registry.resolve_with_args.
|
|
args TEXT NOT NULL DEFAULT '',
|
|
-- A public identifier for what was served -- a Wikidata QID, a Commons
|
|
-- page id, a Wolne Lektury slug, a content-addressed filename. Never a
|
|
-- title, a caption or a name: an identifier cannot carry one.
|
|
item_id TEXT,
|
|
outcome TEXT NOT NULL,
|
|
-- The exception's class name when outcome is 'error', '' otherwise.
|
|
-- A name, never a message: an exception's str() carries whatever was
|
|
-- being processed, which for this bot is somebody's message.
|
|
error_class TEXT NOT NULL DEFAULT '',
|
|
invoked_at_ms INTEGER NOT NULL,
|
|
day TEXT NOT NULL,
|
|
local_hour INTEGER NOT NULL,
|
|
weekday INTEGER NOT NULL,
|
|
handler_ms INTEGER,
|
|
send_ms INTEGER,
|
|
source TEXT NOT NULL DEFAULT 'live',
|
|
PRIMARY KEY (group_id, user_uuid, command, invoked_at_ms, source)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_command_events_group_day
|
|
ON command_events (group_id, day);
|
|
CREATE INDEX IF NOT EXISTS idx_command_events_group_command_day
|
|
ON command_events (group_id, command, day);
|
|
CREATE INDEX IF NOT EXISTS idx_command_events_group_user_day
|
|
ON command_events (group_id, user_uuid, day);
|
|
-- One row per outbound HTTP call made while a command was running, joined to
|
|
-- command_events by (group_id, invoked_at_ms). seq orders the calls within
|
|
-- one invocation and keeps two calls to the same host distinct.
|
|
CREATE TABLE IF NOT EXISTS upstream_calls (
|
|
group_id TEXT NOT NULL,
|
|
invoked_at_ms INTEGER NOT NULL,
|
|
command TEXT NOT NULL,
|
|
host TEXT NOT NULL,
|
|
-- 0 means the request never got a reply at all: DNS, connect, timeout.
|
|
-- Distinguishable from a 5xx, which is upstream answering badly.
|
|
status INTEGER NOT NULL,
|
|
duration_ms INTEGER NOT NULL,
|
|
seq INTEGER NOT NULL,
|
|
PRIMARY KEY (group_id, invoked_at_ms, seq)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_upstream_calls_host
|
|
ON upstream_calls (group_id, host);
|
|
"""
|