signal-bot/signal_bot/schema.py

# Copyright (c) 2026 Krzysztof Rudnicki. MIT licence.
"""The database shape, kept apart from the code that queries it.

Split out of ``store.py`` so both stay under the 250-line cap, and because
"what the tables are" and "how the bot reads them" are two things.
"""

from __future__ import annotations

from typing import Final

# Bumped whenever the shape below changes. See Store._apply_schema for why
# a version number is the only safe way to notice.
#
# Deliberately NOT bumped when a whole new table is added: CREATE TABLE IF
# NOT EXISTS is additive and harmless, whereas a bump drops every table and
# would throw away the live tallies to gain nothing.
SCHEMA_VERSION: Final = 2

# Every table is keyed by group first. The bot watches several groups, and a
# tally, a cooldown or a vote that leaked between them would mean a test
# message throttling the production group -- so the scope is in the primary
# key rather than in the callers' discipline.
SCHEMA: Final = """
CREATE TABLE IF NOT EXISTS command_uses (
    group_id  TEXT NOT NULL,
    user_uuid TEXT NOT NULL,
    command   TEXT NOT NULL,
    used_at   REAL NOT NULL,
    PRIMARY KEY (group_id, user_uuid, command)
);
CREATE TABLE IF NOT EXISTS daily_uses (
    group_id  TEXT NOT NULL,
    user_uuid TEXT NOT NULL,
    command   TEXT NOT NULL,
    day       TEXT NOT NULL,
    uses      INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (group_id, user_uuid, command, day)
);
CREATE TABLE IF NOT EXISTS votes (
    group_id   TEXT NOT NULL,
    command    TEXT NOT NULL,
    voter_uuid TEXT NOT NULL,
    voted_at   REAL NOT NULL,
    PRIMARY KEY (group_id, command, voter_uuid)
);
CREATE TABLE IF NOT EXISTS group_meta (
    group_id TEXT NOT NULL,
    key      TEXT NOT NULL,
    value    TEXT NOT NULL,
    PRIMARY KEY (group_id, key)
);
CREATE TABLE IF NOT EXISTS message_events (
    group_id     TEXT    NOT NULL,
    uuid         TEXT    NOT NULL,
    display_name TEXT    NOT NULL,
    sent_at_ms   INTEGER NOT NULL,
    day          TEXT    NOT NULL,
    local_hour   INTEGER NOT NULL,
    media_kind   TEXT    NOT NULL DEFAULT '',
    media_count  INTEGER NOT NULL DEFAULT 0,
    char_len     INTEGER NOT NULL DEFAULT 0,
    is_command   INTEGER NOT NULL DEFAULT 0,
    is_reply     INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (group_id, uuid, sent_at_ms)
);
CREATE INDEX IF NOT EXISTS idx_events_group_day
    ON message_events (group_id, day);
CREATE INDEX IF NOT EXISTS idx_events_group_uuid_day
    ON message_events (group_id, uuid, day);
CREATE TABLE IF NOT EXISTS group_people (
    group_id     TEXT NOT NULL,
    uuid         TEXT NOT NULL,
    display_name TEXT NOT NULL,
    number       TEXT NOT NULL DEFAULT '',
    first_seen   TEXT NOT NULL,
    last_seen    TEXT NOT NULL,
    PRIMARY KEY (group_id, uuid)
);
-- Keyed on the envelope's own sent timestamp, never on the wall clock.
-- signal-cli replays undelivered frames when the bot reconnects; a clock
-- reading is fresh on every replay, so a clock-keyed table collects a
-- duplicate row per reconnect instead of ignoring one. message_events
-- learned this already -- see its ON CONFLICT DO NOTHING.
CREATE TABLE IF NOT EXISTS unknown_commands (
    group_id   TEXT    NOT NULL,
    word      TEXT    NOT NULL,
    body       TEXT    NOT NULL,
    sent_at_ms INTEGER NOT NULL,
    PRIMARY KEY (group_id, word, sent_at_ms)
);
CREATE INDEX IF NOT EXISTS idx_unknown_group_word
    ON unknown_commands (group_id, word);
CREATE TABLE IF NOT EXISTS suggestions (
    group_id   TEXT    NOT NULL,
    command    TEXT    NOT NULL,
    body       TEXT    NOT NULL,
    sent_at_ms INTEGER NOT NULL,
    PRIMARY KEY (group_id, command, sent_at_ms)
);
-- The operator's switches, one row per command that has been touched from
-- the management page; a command with no row is on, with the configured
-- cooldown. Not keyed by group on purpose: "!kot is off" is a decision
-- about the bot, and the page that makes it has no group selector.
-- disabled_until is epoch seconds, like group_meta's mute deadline.
CREATE TABLE IF NOT EXISTS command_settings (
    command          TEXT    PRIMARY KEY,
    enabled          INTEGER NOT NULL DEFAULT 1,
    disabled_until   REAL,
    cooldown_seconds REAL
);
"""

# These were one tuple, EPHEMERAL_TABLES, until command analytics needed the
# two jobs it was doing to come apart. "Dropped when the schema changes" and
# "cleared when the bot leaves a group" happened to name the same four tables,
# so one constant served both -- and then daily_uses became a source the
# analytics history is backfilled from, which must survive a schema bump but
# must still reset a rejoined group's daily limits. Two questions, two tuples.

# Dropped and recreated when SCHEMA_VERSION rises. Only tables whose contents
# cost nothing to lose: a vote in progress, a per-group marker.
#
# The price of taking command_uses/daily_uses out of here is explicit: if
# their *shape* ever changes, CREATE TABLE IF NOT EXISTS is a no-op against
# the old one and every INSERT starts failing. Reshaping either of them now
# needs a written migration rather than a version bump. That is the trade
# that keeps the use history.
REBUILT_ON_BUMP: Final = (
    "votes",
    "group_meta",
)
# Rows deleted for one group when the bot is removed from it, so that being
# added back starts with a clean cooldown rather than resurrecting whatever
# was true whenever it was last in there.
CLEARED_ON_LEAVE: Final = (
    "command_uses",
    "daily_uses",
    "votes",
    "group_meta",
)
# Never dropped and never cleared, by any path. These hold the whole
# statistical history, which cannot be recomputed from anything -- so a
# transient group listing that omits a member (the exact failure
# discovery._permitted guards against) must not be able to delete years of
# it, and neither must a future schema bump that only meant to reshape a
# cooldown table.
PERMANENT_TABLES: Final = (
    "message_events",
    "group_people",
    # What the group wished existed. Neither can be recomputed from anything
    # -- an idea somebody typed once is gone the moment it is dropped -- and
    # forget_group() clears its own set on the transient group-listing gap
    # discovery._permitted guards against, which must not cost a year of
    # collected suggestions.
    "unknown_commands",
    "suggestions",
    # Every command attempt ever made, and every upstream call made serving
    # one. In neither tuple above: a schema bump must not throw away the
    # answer to "which command is dying", and leaving a group must not
    # either -- message_events already survives both for the same reason.
    "command_events",
    "upstream_calls",
    # Not history but the operator's own decisions, which no group leaving
    # and no schema bump has any business undoing.
    "command_settings",
)
# Every table the schema declares. command_uses and daily_uses are in
# CLEARED_ON_LEAVE but not PERMANENT_TABLES, so the union is spelled out
# rather than concatenated -- test_schema_tables checks it against sqlite's
# own catalogue, which is what keeps this honest.
TABLES: Final = (
    "command_uses",
    "daily_uses",
    *REBUILT_ON_BUMP,
    *PERMANENT_TABLES,
)

# The two tables that carry history and are NOT rebuilt on a version bump.
# Because they survive, a bump can no longer fix them if their shape is
# wrong, and CREATE TABLE IF NOT EXISTS will not either -- which is the
# failure _apply_schema exists to prevent: a bot that boots clean and then
# fails on every INSERT. So their columns are declared here and checked, and
# a mismatch drops the table after all. Losing a cooldown is recoverable;
# a bot that answers nothing is not.
#
# This is the "written migration" the comment on REBUILT_ON_BUMP promises.
# A deliberate reshape means editing the tuple below in the same commit, and
# accepting that the old rows go. test_declared_columns_match_the_schema
# asserts these against what sqlite actually creates, so they cannot drift.
RETAINED_COLUMNS: Final = {
    "command_uses": ("group_id", "user_uuid", "command", "used_at"),
    "daily_uses": ("group_id", "user_uuid", "command", "day", "uses"),
}