Backfeed only read first-level replies (Threads /replies, GoToSocial direct replies, Bluesky depth 1) and skipped the owner's own replies, so conversations under crossposts lost everything after the first reply. - Threads /conversation, all GoToSocial descendants, Bluesky depth 10 - backfeed.thread_members keeps others' replies to our copy and any reply (ours included) to a kept reply; own replies are stored as notified - Migration 026: webmentions.parent_remote_id; replies render nested as comments of comments Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
430 lines
14 KiB
Python
430 lines
14 KiB
Python
"""
|
|
Webmention database query functions
|
|
|
|
Provides database query functions for webmention management.
|
|
Per display-moderation.md design specification.
|
|
|
|
Functions:
|
|
- get_webmentions_for_note: Get approved webmentions for public display
|
|
- list_webmentions: List webmentions for admin with filters and pagination
|
|
- get_webmention: Get single webmention by ID
|
|
- count_webmentions: Count webmentions (optionally filtered)
|
|
- update_webmention_status: Update moderation status
|
|
- delete_webmentions: Delete webmentions by IDs
|
|
- get_reply_context: Get cached reply context for a URL
|
|
"""
|
|
|
|
import logging
|
|
import math
|
|
from datetime import datetime
|
|
from pathlib import Path
|
|
from typing import List, Dict, Any, Optional
|
|
|
|
from flask import current_app
|
|
from starpunk.database import get_db
|
|
|
|
logger = logging.getLogger(__name__)
|
|
|
|
|
|
def get_webmentions_for_note(note_id: int) -> Dict[str, List[Dict[str, Any]]]:
|
|
"""
|
|
Get approved webmentions for a note, grouped by type for public display.
|
|
|
|
Per display-moderation.md:
|
|
- Only returns validation_status='valid' AND moderation_status='approved'
|
|
- Groups by mention_type (reply, like, repost, bookmark, mention)
|
|
- Sorts within groups by received_at ASC (oldest first)
|
|
|
|
Args:
|
|
note_id: Note database ID
|
|
|
|
Returns:
|
|
Dict with keys: 'replies', 'likes', 'reposts', 'bookmarks', 'quotes'
|
|
(backfed quote posts, v2.13.0), 'mentions'
|
|
Each value is a list of webmention dicts
|
|
"""
|
|
db = get_db()
|
|
|
|
# Query for approved webmentions only
|
|
query = """
|
|
SELECT * FROM webmentions
|
|
WHERE target_note_id = ?
|
|
AND validation_status = 'valid'
|
|
AND moderation_status = 'approved'
|
|
ORDER BY
|
|
CASE mention_type
|
|
WHEN 'reply' THEN 1
|
|
WHEN 'like' THEN 2
|
|
WHEN 'repost' THEN 3
|
|
WHEN 'bookmark' THEN 4
|
|
WHEN 'quote' THEN 5
|
|
WHEN 'mention' THEN 6
|
|
END,
|
|
received_at ASC
|
|
"""
|
|
|
|
rows = db.execute(query, (note_id,)).fetchall()
|
|
|
|
# Backfed responses say which silo they came from (v2.13.0)
|
|
silo_names = {}
|
|
if any(row['origin'] != 'webmention' for row in rows):
|
|
from starpunk.backfeed import SILO_NAMES
|
|
from starpunk.syndication import get_targets
|
|
silo_names = {**SILO_NAMES, **{t.id: t.name for t in get_targets().values()}}
|
|
|
|
# Group by type
|
|
grouped = {
|
|
'replies': [],
|
|
'likes': [],
|
|
'reposts': [],
|
|
'bookmarks': [],
|
|
'quotes': [],
|
|
'mentions': []
|
|
}
|
|
|
|
for row in rows:
|
|
wm = dict(row)
|
|
wm['via'] = silo_names.get(wm['origin']) if wm['origin'] != 'webmention' else None
|
|
# Parse timestamps (source_published may be missing or malformed)
|
|
for key in ('received_at', 'source_published'):
|
|
if isinstance(wm.get(key), str):
|
|
try:
|
|
wm[key] = datetime.fromisoformat(wm[key].replace('Z', '+00:00'))
|
|
except ValueError:
|
|
wm[key] = None
|
|
|
|
mention_type = wm.get('mention_type', 'mention')
|
|
if mention_type == 'reply':
|
|
grouped['replies'].append(wm)
|
|
elif mention_type == 'like':
|
|
grouped['likes'].append(wm)
|
|
elif mention_type == 'repost':
|
|
grouped['reposts'].append(wm)
|
|
elif mention_type == 'bookmark':
|
|
grouped['bookmarks'].append(wm)
|
|
elif mention_type == 'quote':
|
|
grouped['quotes'].append(wm)
|
|
else:
|
|
grouped['mentions'].append(wm)
|
|
|
|
grouped['reply_threads'] = reply_threads(grouped['replies'])
|
|
return grouped
|
|
|
|
|
|
def reply_threads(replies: List[Dict[str, Any]]) -> List[Dict[str, Any]]:
|
|
"""
|
|
Replies nested under the replies they answer (v2.20.2): each gets
|
|
'children'; the top-level ones are returned. A reply whose parent isn't
|
|
shown (hidden, removed) is shown at the top level.
|
|
"""
|
|
by_id = {}
|
|
for wm in replies:
|
|
wm['children'] = []
|
|
if wm.get('remote_id'):
|
|
by_id[(wm.get('origin'), wm['remote_id'])] = wm
|
|
top = []
|
|
for wm in replies:
|
|
parent = by_id.get((wm.get('origin'), wm.get('parent_remote_id'))) \
|
|
if wm.get('parent_remote_id') else None
|
|
if parent is not None and parent is not wm:
|
|
parent['children'].append(wm)
|
|
else:
|
|
top.append(wm)
|
|
return top
|
|
|
|
|
|
def response_counts(note_ids: List[int]) -> Dict[int, Dict[str, int]]:
|
|
"""
|
|
Likes, reposts, replies and quotes per note, for lists of notes
|
|
(v2.13.4; quotes v2.14.2).
|
|
|
|
Shown responses (valid and approved) plus the counts silos give without
|
|
names (Threads; people who opted out of backfeed) on synced crossposts.
|
|
Notes with none are left out.
|
|
"""
|
|
if not note_ids:
|
|
return {}
|
|
db = get_db()
|
|
marks = ','.join('?' * len(note_ids))
|
|
counts: Dict[int, Dict[str, int]] = {}
|
|
|
|
def add(note_id, key, n):
|
|
if n:
|
|
counts.setdefault(note_id, {'likes': 0, 'reposts': 0, 'replies': 0, 'quotes': 0})[key] += n
|
|
|
|
for row in db.execute(
|
|
f"""
|
|
SELECT target_note_id, mention_type, COUNT(*) FROM webmentions
|
|
WHERE target_note_id IN ({marks}) AND validation_status = 'valid'
|
|
AND moderation_status = 'approved' AND mention_type IN ('like', 'repost', 'reply', 'quote')
|
|
GROUP BY target_note_id, mention_type
|
|
""",
|
|
note_ids,
|
|
):
|
|
add(row[0], {'like': 'likes', 'repost': 'reposts', 'reply': 'replies', 'quote': 'quotes'}[row[1]], row[2])
|
|
for row in db.execute(
|
|
f"""
|
|
SELECT note_id, SUM(COALESCE(likes_count, 0)), SUM(COALESCE(reposts_count, 0)),
|
|
SUM(COALESCE(quotes_count, 0))
|
|
FROM syndications WHERE note_id IN ({marks}) AND state = 'synced'
|
|
GROUP BY note_id
|
|
""",
|
|
note_ids,
|
|
):
|
|
add(row[0], 'likes', row[1])
|
|
add(row[0], 'reposts', row[2])
|
|
add(row[0], 'quotes', row[3])
|
|
return counts
|
|
|
|
|
|
def list_webmentions(
|
|
status_filter: Optional[str] = None,
|
|
page: int = 1,
|
|
per_page: int = 25,
|
|
origin_filter: Optional[str] = None,
|
|
) -> tuple[List[Dict[str, Any]], int]:
|
|
"""
|
|
List webmentions for admin with pagination and filtering.
|
|
|
|
Args:
|
|
status_filter: Filter by moderation_status ('pending', 'approved', 'hidden',
|
|
'deleted'). Deleted ones are left out unless asked for (v2.13.0).
|
|
page: Page number (1-indexed)
|
|
per_page: Items per page (default: 25)
|
|
origin_filter: 'webmention' or a backfeed silo id (v2.13.0)
|
|
|
|
Returns:
|
|
Tuple of (webmentions list, total_pages)
|
|
"""
|
|
db = get_db()
|
|
|
|
# Build query
|
|
base_query = """
|
|
SELECT w.*, n.slug as target_slug
|
|
FROM webmentions w
|
|
LEFT JOIN notes n ON w.target_note_id = n.id
|
|
"""
|
|
count_query = "SELECT COUNT(*) FROM webmentions"
|
|
|
|
params = []
|
|
conditions = ["moderation_status = ?" if status_filter else "moderation_status != 'deleted'"]
|
|
if status_filter:
|
|
params.append(status_filter)
|
|
if origin_filter:
|
|
conditions.append("origin = ?")
|
|
params.append(origin_filter)
|
|
where = " AND ".join(conditions)
|
|
where_clause = " WHERE " + where.replace("moderation_status", "w.moderation_status").replace(
|
|
"origin", "w.origin")
|
|
count_query += " WHERE " + where
|
|
|
|
# Count total
|
|
total_count = db.execute(count_query, params).fetchone()[0]
|
|
total_pages = math.ceil(total_count / per_page) if total_count > 0 else 1
|
|
|
|
# Get paginated results
|
|
offset = (page - 1) * per_page
|
|
query = base_query + where_clause + " ORDER BY w.received_at DESC LIMIT ? OFFSET ?"
|
|
params.extend([per_page, offset])
|
|
|
|
rows = db.execute(query, params).fetchall()
|
|
|
|
# Convert to dicts
|
|
webmentions = []
|
|
for row in rows:
|
|
wm = dict(row)
|
|
# Parse timestamps
|
|
if isinstance(wm.get('received_at'), str):
|
|
wm['received_at'] = datetime.fromisoformat(wm['received_at'].replace('Z', '+00:00'))
|
|
if wm.get('validated_at') and isinstance(wm['validated_at'], str):
|
|
wm['validated_at'] = datetime.fromisoformat(wm['validated_at'].replace('Z', '+00:00'))
|
|
webmentions.append(wm)
|
|
|
|
return webmentions, total_pages
|
|
|
|
|
|
def get_webmention(webmention_id: int) -> Optional[Dict[str, Any]]:
|
|
"""
|
|
Get single webmention by ID.
|
|
|
|
Args:
|
|
webmention_id: Webmention database ID
|
|
|
|
Returns:
|
|
Webmention dict or None if not found
|
|
"""
|
|
db = get_db()
|
|
|
|
query = """
|
|
SELECT w.*, n.slug as target_slug
|
|
FROM webmentions w
|
|
LEFT JOIN notes n ON w.target_note_id = n.id
|
|
WHERE w.id = ?
|
|
"""
|
|
|
|
row = db.execute(query, (webmention_id,)).fetchone()
|
|
|
|
if not row:
|
|
return None
|
|
|
|
wm = dict(row)
|
|
# Parse timestamps
|
|
if isinstance(wm.get('received_at'), str):
|
|
wm['received_at'] = datetime.fromisoformat(wm['received_at'].replace('Z', '+00:00'))
|
|
if wm.get('validated_at') and isinstance(wm['validated_at'], str):
|
|
wm['validated_at'] = datetime.fromisoformat(wm['validated_at'].replace('Z', '+00:00'))
|
|
|
|
return wm
|
|
|
|
|
|
def count_webmentions(status_filter: Optional[str] = None) -> int:
|
|
"""
|
|
Count webmentions, optionally filtered by status.
|
|
|
|
Args:
|
|
status_filter: Filter by moderation_status
|
|
|
|
Returns:
|
|
Count of webmentions
|
|
"""
|
|
db = get_db()
|
|
|
|
if status_filter:
|
|
query = "SELECT COUNT(*) FROM webmentions WHERE moderation_status = ?"
|
|
result = db.execute(query, (status_filter,)).fetchone()
|
|
else:
|
|
query = "SELECT COUNT(*) FROM webmentions WHERE moderation_status != 'deleted'"
|
|
result = db.execute(query).fetchone()
|
|
|
|
return result[0] if result else 0
|
|
|
|
|
|
def update_webmention_status(webmention_ids: List[int], status: str) -> int:
|
|
"""
|
|
Update moderation status for multiple webmentions.
|
|
|
|
Args:
|
|
webmention_ids: List of webmention IDs
|
|
status: New moderation_status ('approved', 'hidden', 'pending')
|
|
|
|
Returns:
|
|
Number of webmentions updated
|
|
"""
|
|
if not webmention_ids:
|
|
return 0
|
|
|
|
if status not in ('approved', 'hidden', 'pending'):
|
|
raise ValueError(f"Invalid status: {status}")
|
|
|
|
db = get_db()
|
|
|
|
# Use parameterized query with placeholders
|
|
placeholders = ','.join('?' * len(webmention_ids))
|
|
query = f"""
|
|
UPDATE webmentions
|
|
SET moderation_status = ?,
|
|
updated_at = CURRENT_TIMESTAMP
|
|
WHERE id IN ({placeholders})
|
|
"""
|
|
|
|
# Notes whose displayed mentions change (valid ones switching between
|
|
# approved and not)
|
|
changed_notes = _visibility_changes(
|
|
db, webmention_ids,
|
|
"validation_status = 'valid' AND (moderation_status = 'approved') != (? = 'approved')",
|
|
[status],
|
|
)
|
|
|
|
params = [status] + webmention_ids
|
|
cursor = db.execute(query, params)
|
|
db.commit()
|
|
|
|
_notify_responses_changed(changed_notes)
|
|
return cursor.rowcount
|
|
|
|
|
|
def delete_webmentions(webmention_ids: List[int]) -> int:
|
|
"""
|
|
Delete webmentions by IDs.
|
|
|
|
The row stays as a tombstone (moderation_status 'deleted', v2.13.0), so
|
|
a re-sent webmention or the next backfeed check doesn't bring it back.
|
|
|
|
Args:
|
|
webmention_ids: List of webmention IDs to delete
|
|
|
|
Returns:
|
|
Number of webmentions deleted
|
|
"""
|
|
if not webmention_ids:
|
|
return 0
|
|
|
|
db = get_db()
|
|
|
|
changed_notes = _visibility_changes(
|
|
db, webmention_ids, "validation_status = 'valid' AND moderation_status = 'approved'", [],
|
|
)
|
|
|
|
placeholders = ','.join('?' * len(webmention_ids))
|
|
query = (f"UPDATE webmentions SET moderation_status = 'deleted', updated_at = CURRENT_TIMESTAMP "
|
|
f"WHERE id IN ({placeholders}) AND moderation_status != 'deleted'")
|
|
|
|
cursor = db.execute(query, webmention_ids)
|
|
db.commit()
|
|
|
|
_notify_responses_changed(changed_notes)
|
|
return cursor.rowcount
|
|
|
|
|
|
def _visibility_changes(db, webmention_ids: List[int], condition: str, params: list) -> List[int]:
|
|
"""Target note IDs of the given webmentions that match condition"""
|
|
placeholders = ','.join('?' * len(webmention_ids))
|
|
rows = db.execute(
|
|
f"SELECT DISTINCT target_note_id FROM webmentions "
|
|
f"WHERE id IN ({placeholders}) AND {condition}",
|
|
list(webmention_ids) + params,
|
|
).fetchall()
|
|
return [row[0] for row in rows]
|
|
|
|
|
|
def _notify_responses_changed(note_ids: List[int]) -> None:
|
|
"""Re-send webmentions for notes whose shown responses changed (best effort)"""
|
|
if not note_ids:
|
|
return
|
|
try:
|
|
from starpunk.webmention_sender import note_responses_changed
|
|
note_responses_changed(Path(current_app.config['DATABASE_PATH']), note_ids)
|
|
except Exception as e:
|
|
current_app.logger.warning(f"Failed to re-send webmentions after moderation: {e}")
|
|
|
|
|
|
def get_reply_context(url: str) -> Optional[Dict[str, Any]]:
|
|
"""
|
|
Get cached reply context for a URL.
|
|
|
|
Args:
|
|
url: The in_reply_to URL
|
|
|
|
Returns:
|
|
Reply context dict or None if not found
|
|
"""
|
|
db = get_db()
|
|
|
|
query = "SELECT * FROM reply_contexts WHERE url = ?"
|
|
row = db.execute(query, (url,)).fetchone()
|
|
|
|
if not row:
|
|
return None
|
|
|
|
context = dict(row)
|
|
|
|
# Parse timestamps
|
|
if context.get('fetched_at') and isinstance(context['fetched_at'], str):
|
|
context['fetched_at'] = datetime.fromisoformat(context['fetched_at'].replace('Z', '+00:00'))
|
|
if context.get('created_at') and isinstance(context['created_at'], str):
|
|
context['created_at'] = datetime.fromisoformat(context['created_at'].replace('Z', '+00:00'))
|
|
if context.get('published_at') and isinstance(context['published_at'], str):
|
|
context['published_at'] = datetime.fromisoformat(context['published_at'].replace('Z', '+00:00'))
|
|
|
|
return context
|