aboutsummaryrefslogtreecommitdiff
path: root/webmentions_ssg
diff options
context:
space:
mode:
authorDennis Fink2026-11-18 22:07:58 +0100
committerDennis Fink2026-11-18 22:07:58 +0100
commit45d3d3fde75173dfbf27e08aae8501842a9511dd (patch)
tree96926d2ff0111e838620afdc73a66a8172bfa6c8 /webmentions_ssg
parentc724c87eb5e23581732a275f1dd808da72932286 (diff)
downloadwebmentions-ssg-45d3d3fde75173dfbf27e08aae8501842a9511dd.tar.gz
webmentions-ssg-45d3d3fde75173dfbf27e08aae8501842a9511dd.zip
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.
Diffstat (limited to 'webmentions_ssg')
-rw-r--r--webmentions_ssg/models/sources.py37
-rw-r--r--webmentions_ssg/templates/sent.html29
-rw-r--r--webmentions_ssg/views/sources.py17
3 files changed, 61 insertions, 22 deletions
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"<SourceDocument {self.url!r}>"
+ @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."""
diff --git a/webmentions_ssg/templates/sent.html b/webmentions_ssg/templates/sent.html
index b613f4b..46296b8 100644
--- a/webmentions_ssg/templates/sent.html
+++ b/webmentions_ssg/templates/sent.html
@@ -84,17 +84,7 @@ SPDX-License-Identifier: BSD-3-Clause
</thead>
<tbody>
{% for source in sources %}
- {% set statistics = namespace(sent=0, failed=0, retrying=0) %}
- {% for webmention in source.sent_webmentions %}
- {% if webmention.status == "sent" %}
- {% set statistics.sent = statistics.sent + 1 %}
- {% elif webmention.status == "failed" %}
- {% set statistics.failed = statistics.failed + 1 %}
- {% endif %}
- {% if webmention.awaiting_processing and webmention.attempt_count > 0 and webmention.next_retry_at is not none %}
- {% set statistics.retrying = statistics.retrying + 1 %}
- {% endif %}
- {% endfor %}
+ {% set counts = statistics[source.uuid] %}
{% if source.deleted_at is none %}
{% set row_class = "table-success" %}
{% set badge_class = "text-bg-success" %}
@@ -115,15 +105,18 @@ SPDX-License-Identifier: BSD-3-Clause
<a href="{{ source.url }}">{{ source.url }}</a>
</td>
<td>
- <span class="badge text-bg-info">{{ source.sent_webmentions | length }} Total</span>
- {% if statistics.sent %}
- <span class="badge text-bg-success">{{ statistics.sent }} sent</span>
+ <span class="badge text-bg-info">{{ counts.total }} Total</span>
+ {% if counts.sent %}
+ <span class="badge text-bg-success">{{ counts.sent }} sent</span>
{% endif %}
- {% if statistics.failed %}
- <span class="badge text-bg-danger">{{ statistics.failed }} failed</span>
+ {% if counts.no_endpoint %}
+ <span class="badge text-bg-secondary">{{ counts.no_endpoint }} no endpoint</span>
{% endif %}
- {% if statistics.retrying %}
- <span class="badge text-bg-warning">{{ statistics.retrying }} awaiting retry</span>
+ {% if counts.failed %}
+ <span class="badge text-bg-danger">{{ counts.failed }} failed</span>
+ {% endif %}
+ {% if counts.retrying %}
+ <span class="badge text-bg-warning">{{ counts.retrying }} awaiting retry</span>
{% endif %}
</td>
<td class="d-none d-md-table-cell">{{ source.revision }}</td>
diff --git a/webmentions_ssg/views/sources.py b/webmentions_ssg/views/sources.py
index a3dbb45..22cabb6 100644
--- a/webmentions_ssg/views/sources.py
+++ b/webmentions_ssg/views/sources.py
@@ -13,7 +13,6 @@ import sqlalchemy as sa
from flask import abort, current_app, flash, redirect, render_template, request, url_for
from flask.typing import ResponseReturnValue
from flask_login import login_required
-from sqlalchemy.orm import selectinload
from ..extensions import db
from ..forms.admin import ConfirmActionForm
@@ -68,9 +67,7 @@ def source_list() -> ResponseReturnValue:
"""Display source documents with filters for outgoing delivery problems."""
filter_form = source_filter_form()
filters = source_filter_args(filter_form)
- query = sa.select(SourceDocument).options(
- selectinload(SourceDocument.sent_webmentions)
- )
+ query = sa.select(SourceDocument)
# EXISTS avoids duplicate source rows when several Webmentions match.
sent = sa.select(SentWebmention.uuid).where(
@@ -144,6 +141,17 @@ def source_list() -> ResponseReturnValue:
max_per_page=current_app.config["SOURCES_MAX_PAGE_SIZE"],
)
+ statistics = {}
+ if sources.items:
+ statistics = {
+ row.source_uuid: row
+ for row in db.session.execute(
+ SourceDocument.sent_webmention_statistics(
+ [source.uuid for source in sources.items]
+ )
+ )
+ }
+
sort_urls = {}
for key in sort_columns:
if key == sort_key:
@@ -157,6 +165,7 @@ def source_list() -> ResponseReturnValue:
"sent.html",
title="Sent Webmentions",
sources=sources,
+ statistics=statistics,
filters=filters,
sort_key=sort_key,
sort_order=sort_order,