From 45d3d3fde75173dfbf27e08aae8501842a9511dd Mon Sep 17 00:00:00 2001 From: Dennis Fink Date: Wed, 18 Nov 2026 22:07:58 +0100 Subject: perf(sender): aggregate source statistics in SQL Move sent Webmention counts from template iteration into a page-scoped SQL aggregate so source listings no longer load every related delivery row. Include zero-target sources and expose sent, no-endpoint, failed, and retrying counts directly to the template. --- webmentions_ssg/models/sources.py | 37 +++++++++++++++++++++++++++++++++++++ 1 file changed, 37 insertions(+) (limited to 'webmentions_ssg/models') diff --git a/webmentions_ssg/models/sources.py b/webmentions_ssg/models/sources.py index f0fd618..be56e24 100644 --- a/webmentions_ssg/models/sources.py +++ b/webmentions_ssg/models/sources.py @@ -4,6 +4,7 @@ """Source documents and outgoing Webmention state.""" +from collections.abc import Collection from datetime import datetime from enum import StrEnum from uuid import UUID, uuid7 @@ -68,6 +69,42 @@ class SourceDocument(Base): """Represent the source by its public URL.""" return f"" + @classmethod + def sent_webmention_statistics( + cls, source_uuids: Collection[UUID] + ) -> sa.Select[UUID, int, int, int, int, int]: + """Aggregate delivery counts for a page of sources in one SQL query. + + The outer join includes sources without Webmentions, with zero counts. + Retrying is independent of delivery status, matching the list badges. + """ + retrying = sa.and_( + SentWebmention.awaiting_processing, + SentWebmention.attempt_count > 0, + SentWebmention.next_retry_at.is_not(None), + ) + return ( + sa.select( + cls.uuid.label("source_uuid"), + sa.func.count(SentWebmention.uuid).label("total"), + sa.func.count( + sa.case((SentWebmention.status == SentWebmentionStatus.SENT, 1)) + ).label("sent"), + sa.func.count( + sa.case( + (SentWebmention.status == SentWebmentionStatus.NO_ENDPOINT, 1) + ) + ).label("no_endpoint"), + sa.func.count( + sa.case((SentWebmention.status == SentWebmentionStatus.FAILED, 1)) + ).label("failed"), + sa.func.count(sa.case((retrying, 1))).label("retrying"), + ) + .outerjoin(SentWebmention, SentWebmention.source_uuid == cls.uuid) + .where(cls.uuid.in_(source_uuids)) + .group_by(cls.uuid) + ) + @property def created_at(self) -> datetime: """Return the creation time encoded in the source UUID.""" -- cgit v1.3.1