aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorDennis Fink2026-11-18 22:07:58 +0100
committerDennis Fink2026-11-18 22:07:58 +0100
commit45d3d3fde75173dfbf27e08aae8501842a9511dd (patch)
tree96926d2ff0111e838620afdc73a66a8172bfa6c8
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.
-rw-r--r--tests/models/test_sources.py70
-rw-r--r--tests/views/test_sources.py31
-rw-r--r--webmentions_ssg/models/sources.py37
-rw-r--r--webmentions_ssg/templates/sent.html29
-rw-r--r--webmentions_ssg/views/sources.py17
5 files changed, 162 insertions, 22 deletions
diff --git a/tests/models/test_sources.py b/tests/models/test_sources.py
index 10af3d6..100572f 100644
--- a/tests/models/test_sources.py
+++ b/tests/models/test_sources.py
@@ -98,6 +98,76 @@ def test_sent_webmention_pending_sql_matches_python(app: Flask) -> None:
assert result.rowcount == 3
+def test_source_sent_webmention_statistics_sql(app: Flask) -> None:
+ """SQL counts every delivery state and includes sources with no targets."""
+ now = datetime.now(UTC)
+ with app.app_context():
+ source = SourceDocument(
+ path="stats/index.html",
+ url="https://site.example/stats/",
+ content_hash="0" * 32,
+ last_seen_at=now,
+ revised_at=now,
+ )
+ empty = SourceDocument(
+ path="empty/index.html",
+ url="https://site.example/empty/",
+ content_hash="0" * 32,
+ last_seen_at=now,
+ revised_at=now,
+ )
+ for index, (status, processed, attempts, retry_at) in enumerate(
+ [
+ (SentWebmentionStatus.SENT, 1, 0, None),
+ (SentWebmentionStatus.SENT, 1, 0, None),
+ (SentWebmentionStatus.FAILED, None, 2, now),
+ (SentWebmentionStatus.NO_ENDPOINT, 1, 0, None),
+ (None, None, 1, None),
+ ]
+ ):
+ source.sent_webmentions.append(
+ SentWebmention(
+ target=f"https://target.example/{index}",
+ desired_revision=1,
+ processed_revision=processed,
+ status=status,
+ attempt_count=attempts,
+ next_retry_at=retry_at,
+ )
+ )
+ db.session.add_all([source, empty])
+ db.session.commit()
+
+ results = {
+ row.source_uuid: row
+ for row in db.session.execute(
+ SourceDocument.sent_webmention_statistics([source.uuid, empty.uuid])
+ )
+ }
+ assert set(results) == {source.uuid, empty.uuid}
+ assert (
+ results[source.uuid].total,
+ results[source.uuid].sent,
+ results[source.uuid].failed,
+ results[source.uuid].retrying,
+ ) == (5, 2, 1, 1)
+ assert (
+ results[empty.uuid].total,
+ results[empty.uuid].sent,
+ results[empty.uuid].failed,
+ results[empty.uuid].retrying,
+ ) == (0, 0, 0, 0)
+
+
+def test_source_sent_webmention_statistics_is_scoped_to_page(app: Flask) -> None:
+ """An empty UUID selection must not aggregate unrelated sources."""
+ with app.app_context():
+ assert (
+ list(db.session.execute(SourceDocument.sent_webmention_statistics([])))
+ == []
+ )
+
+
def test_source_repr() -> None:
source = SourceDocument(
path="example/index.html",
diff --git a/tests/views/test_sources.py b/tests/views/test_sources.py
index a41c18c..8f5672c 100644
--- a/tests/views/test_sources.py
+++ b/tests/views/test_sources.py
@@ -659,6 +659,37 @@ def test_resend_preserves_source_filter_context(
send.assert_called_once_with(mention_id)
+def test_sources_show_sql_statistics_with_zero_webmentions(
+ app: Flask, client: FlaskClient
+) -> None:
+ log_in(app, client)
+ make_source(
+ app,
+ "mixed-stats",
+ statuses=(
+ SentWebmentionStatus.SENT,
+ SentWebmentionStatus.SENT,
+ SentWebmentionStatus.FAILED,
+ SentWebmentionStatus.NO_ENDPOINT,
+ ),
+ )
+ make_source(app, "empty-stats", statuses=())
+
+ response = client.get("/sources", query_string={"sort": "path", "order": "asc"})
+ assert response.status_code == HTTPStatus.OK
+ rows = BeautifulSoup(response.get_data(as_text=True), "html.parser").select(
+ "tbody tr"
+ )
+ assert len(rows) == 2
+ assert "0 Total" in rows[0].get_text(" ", strip=True)
+ assert " sent" not in rows[0].get_text(" ", strip=True)
+ mixed = rows[1].get_text(" ", strip=True)
+ assert "4 Total" in mixed
+ assert "2 sent" in mixed
+ assert "1 failed" in mixed
+ assert "awaiting retry" not in mixed
+
+
def test_sources_empty_filter_result_keeps_rescan_modal(
app: Flask, client: FlaskClient
) -> None:
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,