bvandeusen
f8105046dc
Explore's neighbour grid (/api/gallery/similar → gallery_service.similar) now takes an Explore-only exclude_wip flag that drops `wip` system-tagged images from the candidates, alongside the banner/editor presentation tags. The gallery's own "similar" button is unchanged (keeps wip, #1274) — only the Explore store passes exclude_wip=1. The anchor itself may still be a WIP; only neighbours are filtered. Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
899 lines
37 KiB
Python
899 lines
37 KiB
Python
"""Cursor-paginated gallery queries.
|
||
|
||
Cursor format: opaque base64-encoded "<iso8601_effective_date>:<image_id>".
|
||
|
||
Pagination key is (effective_date DESC, id DESC) where effective_date is
|
||
COALESCE(post.post_date, image_record.created_at) so the gallery surfaces
|
||
images by ORIGINAL publish date when known, falling back to FC's scan
|
||
date. Important for migrated content: ~57k IR images scanned in a single
|
||
week would otherwise all share the same created_at and pile up in one
|
||
month bucket. The effective_date spreads them across the years they
|
||
were originally published.
|
||
|
||
Decoding rejects malformed cursors with a ValueError; the API layer
|
||
translates that to HTTP 400.
|
||
"""
|
||
|
||
from dataclasses import dataclass
|
||
from datetime import datetime
|
||
from urllib.parse import quote
|
||
|
||
from sqlalchemy import Select, and_, distinct, exists, func, or_, select
|
||
from sqlalchemy.ext.asyncio import AsyncSession
|
||
from sqlalchemy.orm import aliased
|
||
|
||
from ..models import (
|
||
Artist,
|
||
ImageProvenance,
|
||
ImageRecord,
|
||
Post,
|
||
Source,
|
||
Tag,
|
||
TagPositiveConfirmation,
|
||
)
|
||
from ..models.tag import PRESENTATION_SYSTEM_TAGS, WIP_SYSTEM_TAG, image_tag
|
||
from .pagination import decode_cursor, encode_cursor
|
||
from .tag_query import (
|
||
fandom_join_alias,
|
||
image_in_any_tag_scope,
|
||
image_in_tag_scope,
|
||
serialize_tag,
|
||
tag_columns,
|
||
)
|
||
|
||
# Reserved `platform` filter value selecting images with NO platformed
|
||
# provenance (filesystem imports). Returned by facets() as a null-valued
|
||
# bucket; the frontend maps that null back to this sentinel in the URL so the
|
||
# bucket is selectable. Underscore-wrapped so it can't collide with a real
|
||
# gallery-dl platform name (patreon/pixiv/...).
|
||
UNSOURCED_PLATFORM = "__unsourced__"
|
||
|
||
|
||
def _effective_date_col():
|
||
"""The materialized gallery sort key: image_record.effective_date
|
||
(alembic 0035) = COALESCE(primary post's post_date, created_at),
|
||
maintained at write time by the importer.
|
||
|
||
Canonical sort/group/filter key across the gallery so images attached
|
||
to a post surface at their original publish date, not their FC import
|
||
date — and, now that it's a single indexed column rather than a
|
||
COALESCE across the Post outer join, the cursor scroll is an index
|
||
range scan instead of a full re-sort per page.
|
||
"""
|
||
return ImageRecord.effective_date
|
||
|
||
|
||
# Sort key -> the materialized column the gallery orders + cursors on. Both are
|
||
# indexed (DESC, id DESC), so every sort is a forward index range scan.
|
||
# newest/oldest → effective_date (primary post's date, else download)
|
||
# posted_new/_old → earliest_post_date (earliest publish across ALL posts)
|
||
_SORT_COLUMNS = {
|
||
"newest": ImageRecord.effective_date,
|
||
"oldest": ImageRecord.effective_date,
|
||
"posted_new": ImageRecord.earliest_post_date,
|
||
"posted_old": ImageRecord.earliest_post_date,
|
||
}
|
||
_ASCENDING_SORTS = {"oldest", "posted_old"}
|
||
|
||
|
||
def _sort_column(sort: str):
|
||
"""The materialized date column a gallery sort orders/cursors on (falls back
|
||
to effective_date for any unknown sort)."""
|
||
return _SORT_COLUMNS.get(sort, ImageRecord.effective_date)
|
||
|
||
|
||
def _outer_join_primary_post(stmt: Select) -> Select:
|
||
"""LEFT JOIN Post on ImageRecord.primary_post_id so the COALESCE
|
||
above sees Post.post_date when available. Images without a post
|
||
survive the join as NULL on the Post side; COALESCE handles it."""
|
||
return stmt.outerjoin(Post, Post.id == ImageRecord.primary_post_id)
|
||
|
||
|
||
@dataclass(frozen=True)
|
||
class GalleryImage:
|
||
id: int
|
||
path: str
|
||
sha256: str
|
||
mime: str
|
||
width: int | None
|
||
height: int | None
|
||
created_at: datetime # FC's row-insert time
|
||
effective_date: datetime # COALESCE(post.post_date, created_at)
|
||
posted_at: datetime | None # post.post_date if known, else None
|
||
thumbnail_url: str
|
||
artist: dict | None = None
|
||
|
||
|
||
@dataclass(frozen=True)
|
||
class GalleryPage:
|
||
images: list[GalleryImage]
|
||
next_cursor: str | None
|
||
date_groups: list[tuple[int, int, list[int]]] # (year, month, [image_id...])
|
||
|
||
|
||
@dataclass(frozen=True)
|
||
class TimelineBucket:
|
||
year: int
|
||
month: int
|
||
count: int
|
||
|
||
|
||
@dataclass(frozen=True)
|
||
class GalleryFacets:
|
||
total: int # images matching the FULL active filter
|
||
platforms: list[dict] # [{"value": str|None, "count": int}], null = unsourced
|
||
untagged: int # how many the Untagged flag would isolate
|
||
no_artist: int # how many the No-artist flag would isolate
|
||
date_min: datetime | None
|
||
date_max: datetime | None
|
||
|
||
|
||
def thumbnail_url(thumbnail_path: str | None, sha256_hex: str, mime: str) -> str:
|
||
"""Return the URL to fetch a thumbnail.
|
||
|
||
Prefers the stored thumbnail_path verbatim — Quart serves /images/*
|
||
1:1 from the volume (frontend.py:20-36), so the URL IS the disk
|
||
path. Falls back to deriving from (sha256, mime) only when the
|
||
record's thumbnail_path is NULL (thumbnailer hasn't run yet); that
|
||
URL will 404 until backfill catches it, same as before the path
|
||
was tracked.
|
||
|
||
Pre-2026-05-30 this was derived only from (sha256, mime), which
|
||
disagreed with the actual on-disk extension when the thumbnailer
|
||
chose its format from transparency rather than MIME — every PNG
|
||
source without alpha (extension was .jpg on disk) and every WebP
|
||
source with alpha (extension was .png on disk) silently 404'd
|
||
despite the thumbnail file existing.
|
||
"""
|
||
if thumbnail_path:
|
||
return thumbnail_path
|
||
# Fallback for records with no thumbnail recorded yet — preserves
|
||
# prior behavior (URL exists but 404s until backfill regenerates).
|
||
ext = ".png" if mime in ("image/png", "image/gif") else ".jpg"
|
||
bucket = sha256_hex[:3]
|
||
return f"/images/thumbs/{bucket}/{sha256_hex}{ext}"
|
||
|
||
|
||
def image_url(path: str) -> str:
|
||
"""Return the URL to fetch the full-size original from /images.
|
||
|
||
The on-disk `path` mirrors the source tree (artist/post folders), so it can
|
||
contain characters that are special in a URL — most importantly '#' (post
|
||
titles like 'BLUE#59'), but also spaces, '?', '%'. The serve_image route
|
||
URL-decodes its <path:subpath> fine, but only if the browser sends the whole
|
||
path; an unencoded '#' is parsed as a fragment, so '#59/01_timelapse.jpg'
|
||
never reaches the server and the original 404s while the (hash-named)
|
||
thumbnail still loads. Percent-encode the path, keeping '/' as the segment
|
||
separator. Operator-flagged 2026-06-12."""
|
||
rel = path.split("/images/", 1)[-1]
|
||
return f"/images/{quote(rel, safe='/')}"
|
||
|
||
|
||
def _require_single_filter(
|
||
tag_ids, post_id, artist_id, tag_or_groups=None, tag_exclude=None,
|
||
) -> None:
|
||
"""post_id is the post-detail view — it can't combine with the
|
||
composable filters. tag_ids / tag_or_groups / tag_exclude + artist_id
|
||
(+ media_type) compose freely (AND)."""
|
||
if post_id is not None and (
|
||
tag_ids or artist_id is not None or tag_or_groups or tag_exclude
|
||
):
|
||
raise ValueError(
|
||
"post_id cannot be combined with tag or artist filters"
|
||
)
|
||
|
||
|
||
def _apply_scope(
|
||
stmt, *, tag_ids, post_id, artist_id, media_type,
|
||
tag_or_groups=None, tag_exclude=None,
|
||
platform=None, untagged=False, no_artist=False,
|
||
date_from=None, date_to=None, hidden_tag_ids=None,
|
||
):
|
||
"""Apply the composable gallery filters to a statement.
|
||
|
||
All clauses are correlated EXISTS / scalar predicates on ImageRecord, so
|
||
they AND together without row-multiplication and don't require any join to
|
||
be present on `stmt` (the artist/platform paths alias Post/Source inside
|
||
their own EXISTS).
|
||
|
||
Tag filtering is one structured model (#6): AND-of-OR plus exclusions.
|
||
- tag_ids: image must carry ALL of them — one correlated EXISTS per tag
|
||
(the AND-of-singletons "include" common case; light editor + back-compat).
|
||
- tag_or_groups: list of OR-groups; the image must carry AT LEAST ONE tag
|
||
from EACH group — one EXISTS(tag_id IN group) per group, AND'd across
|
||
groups. (advanced editor)
|
||
- tag_exclude: image must carry NONE of these — a single NOT EXISTS(tag_id
|
||
IN exclude). (light "exclude" chips + advanced NOT)
|
||
- post_id / artist_id: provenance EXISTS (post_id is exclusive, guarded
|
||
by _require_single_filter).
|
||
- media_type: 'image' | 'video' narrows by mime prefix.
|
||
- platform: EXISTS a provenance→source with that platform; the
|
||
UNSOURCED_PLATFORM sentinel inverts it (NO platformed provenance).
|
||
- untagged: NOT EXISTS any image_tag row.
|
||
- no_artist: ImageRecord.artist_id IS NULL.
|
||
- date_from / date_to: half-open [from, to) bounds on effective_date.
|
||
"""
|
||
# Every tag clause goes through image_in_tag_scope/_any: a fandom tag also
|
||
# matches images carrying any of its characters (Tag.fandom_id). Include,
|
||
# OR-group, and exclude are all symmetric on that membership.
|
||
for tid in tag_ids or []:
|
||
stmt = stmt.where(image_in_tag_scope(tid))
|
||
for group in tag_or_groups or []:
|
||
if not group:
|
||
continue # an empty OR-group would match nothing; treat as absent
|
||
stmt = stmt.where(image_in_any_tag_scope(group))
|
||
if tag_exclude:
|
||
stmt = stmt.where(~image_in_any_tag_scope(tag_exclude))
|
||
# Presentation chrome (banner / editor screenshot) is hidden from the default
|
||
# gallery — an implicit exclude the caller supplies unless the operator asked
|
||
# to include hidden or is explicitly filtering for a presentation tag
|
||
# (milestone 141). `wip` is NOT hidden. Resolved to ids by _hidden_tag_ids.
|
||
if hidden_tag_ids:
|
||
stmt = stmt.where(~image_in_any_tag_scope(hidden_tag_ids))
|
||
prov = _provenance_clause(post_id, artist_id)
|
||
if prov is not None:
|
||
stmt = stmt.where(prov)
|
||
if media_type == "image":
|
||
stmt = stmt.where(ImageRecord.mime.like("image/%"))
|
||
elif media_type == "video":
|
||
stmt = stmt.where(ImageRecord.mime.like("video/%"))
|
||
if platform is not None:
|
||
stmt = stmt.where(_platform_clause(platform))
|
||
if untagged:
|
||
stmt = stmt.where(
|
||
~exists().where(image_tag.c.image_record_id == ImageRecord.id)
|
||
)
|
||
if no_artist:
|
||
stmt = stmt.where(ImageRecord.artist_id.is_(None))
|
||
eff = _effective_date_col()
|
||
if date_from is not None:
|
||
stmt = stmt.where(eff >= date_from)
|
||
if date_to is not None:
|
||
stmt = stmt.where(eff < date_to)
|
||
return stmt
|
||
|
||
|
||
def _platform_clause(platform):
|
||
"""Correlated EXISTS on a provenance row whose Source carries `platform`.
|
||
The UNSOURCED_PLATFORM sentinel inverts to NOT EXISTS(any sourced
|
||
provenance) — i.e. filesystem-imported content with no platform."""
|
||
src = aliased(Source)
|
||
if platform == UNSOURCED_PLATFORM:
|
||
return ~exists().where(
|
||
ImageProvenance.image_record_id == ImageRecord.id,
|
||
ImageProvenance.source_id == src.id,
|
||
)
|
||
return exists().where(
|
||
ImageProvenance.image_record_id == ImageRecord.id,
|
||
ImageProvenance.source_id == src.id,
|
||
src.platform == platform,
|
||
)
|
||
|
||
|
||
def _provenance_clause(post_id, artist_id):
|
||
"""Correlated EXISTS clause (NOT a join) so an image with multiple
|
||
matching provenance rows is returned exactly once and the
|
||
(effective_date DESC, id DESC) cursor ordering is unaffected."""
|
||
if post_id is not None:
|
||
return exists().where(
|
||
ImageProvenance.image_record_id == ImageRecord.id,
|
||
ImageProvenance.post_id == post_id,
|
||
)
|
||
if artist_id is not None:
|
||
# Use Post.artist_id (alembic 0030 denormalized column) instead
|
||
# of joining through ImageProvenance.source_id → Source.artist_id.
|
||
# The denormalization is the always-present linkage; the source
|
||
# path now drops NULL-source provenance rows (filesystem-imported
|
||
# content) which would otherwise vanish from artist-filtered
|
||
# gallery views.
|
||
# ALIAS Post: the gallery query outer-joins Post on
|
||
# ImageRecord.primary_post_id (`_outer_join_primary_post`).
|
||
# SQLAlchemy would otherwise correlate a bare `Post` reference
|
||
# in this EXISTS subquery to that outer Post (which is NULL for
|
||
# images with no primary post), and the filter would silently
|
||
# match nothing.
|
||
post_inner = aliased(Post)
|
||
return exists().where(
|
||
ImageProvenance.image_record_id == ImageRecord.id,
|
||
ImageProvenance.post_id == post_inner.id,
|
||
post_inner.artist_id == artist_id,
|
||
)
|
||
return None
|
||
|
||
|
||
def _gallery_images(rows, artists: dict[int, dict]) -> list[GalleryImage]:
|
||
"""Build GalleryImage list from (record, posted_at, eff_date) rows + the
|
||
artist hydration map. Shared by scroll() and similar()."""
|
||
return [
|
||
GalleryImage(
|
||
id=record.id,
|
||
path=record.path,
|
||
sha256=record.sha256,
|
||
mime=record.mime,
|
||
width=record.width,
|
||
height=record.height,
|
||
created_at=record.created_at,
|
||
effective_date=eff_date,
|
||
posted_at=posted_at,
|
||
thumbnail_url=thumbnail_url(record.thumbnail_path, record.sha256, record.mime),
|
||
artist=artists.get(record.id),
|
||
)
|
||
for record, posted_at, eff_date in rows
|
||
]
|
||
|
||
|
||
def _diversify_similar(src, rows, limit, *, dup_threshold=8, lam=0.40):
|
||
"""Trim a nearest-cosine candidate pool down to `limit` diverse picks.
|
||
|
||
1. pHash collapse: drop any candidate whose perceptual hash is within
|
||
`dup_threshold` Hamming bits of the anchor or an already-kept candidate —
|
||
so a reposted banner (and the anchor's own clones) appears at most once.
|
||
2. MMR (Maximal Marginal Relevance): greedily pick the candidate maximising
|
||
`lam * sim_to_anchor - (1 - lam) * max_sim_to_already_picked`. This keeps
|
||
the most relevant up top but pushes the selection to SPAN clusters
|
||
instead of returning 40 variations of one image.
|
||
|
||
`lam` is the variance dial: lower = weight the diversity penalty harder, so
|
||
the rail reaches further across clusters (operator wanted MORE variance,
|
||
2026-07-01 — dropped 0.55→0.40, dup 6→8, paired with a wider pool in
|
||
`similar()`).
|
||
|
||
Falls back to nearest-order (`rows[:limit]`) on any failure or a small pool.
|
||
"""
|
||
if len(rows) <= 1:
|
||
return rows[:limit]
|
||
try:
|
||
import imagehash
|
||
import numpy as np
|
||
except Exception:
|
||
return rows[:limit]
|
||
|
||
# --- 1. pHash near-duplicate collapse (videos/NULL phash pass through) ---
|
||
kept = []
|
||
seen = []
|
||
if src.phash:
|
||
try:
|
||
seen.append(imagehash.hex_to_hash(src.phash))
|
||
except Exception:
|
||
pass
|
||
for row in rows:
|
||
ph = row[0].phash
|
||
if ph:
|
||
try:
|
||
h = imagehash.hex_to_hash(ph)
|
||
if any((h - k) <= dup_threshold for k in seen):
|
||
continue
|
||
seen.append(h)
|
||
except Exception:
|
||
pass
|
||
kept.append(row)
|
||
if len(kept) <= limit:
|
||
return kept
|
||
|
||
# --- 2. MMR re-rank on the L2-normalised SigLIP embeddings ---
|
||
try:
|
||
a = np.asarray(src.siglip_embedding, dtype=np.float32)
|
||
a = a / (np.linalg.norm(a) or 1.0)
|
||
V = np.vstack([
|
||
np.asarray(row[0].siglip_embedding, dtype=np.float32) for row in kept
|
||
])
|
||
V = V / np.clip(np.linalg.norm(V, axis=1, keepdims=True), 1e-8, None)
|
||
except Exception:
|
||
return kept[:limit]
|
||
|
||
rel = V @ a # (N,) cosine to the anchor
|
||
n = len(kept)
|
||
picked_mask = np.zeros(n, dtype=bool)
|
||
max_sim = np.zeros(n, dtype=np.float32) # max sim to anything picked yet
|
||
order = []
|
||
for _ in range(min(limit, n)):
|
||
scores = lam * rel - (1.0 - lam) * max_sim
|
||
scores[picked_mask] = -np.inf
|
||
i = int(np.argmax(scores))
|
||
order.append(i)
|
||
picked_mask[i] = True
|
||
max_sim = np.maximum(max_sim, V @ V[i])
|
||
return [kept[i] for i in order]
|
||
|
||
|
||
async def _artists_for(session, image_ids: list[int]) -> dict[int, dict]:
|
||
"""Map image_id -> {"name","slug"} via the canonical
|
||
image_record.artist_id (FC-2d-vii-c). Bounded by page size."""
|
||
if not image_ids:
|
||
return {}
|
||
stmt = (
|
||
select(ImageRecord.id, Artist.name, Artist.slug)
|
||
.join(Artist, Artist.id == ImageRecord.artist_id)
|
||
.where(ImageRecord.id.in_(image_ids))
|
||
)
|
||
return {
|
||
img_id: {"name": name, "slug": slug}
|
||
for img_id, name, slug in (await session.execute(stmt)).all()
|
||
}
|
||
|
||
|
||
class GalleryService:
|
||
def __init__(self, session: AsyncSession):
|
||
self.session = session
|
||
|
||
async def _hidden_tag_ids(
|
||
self, include_hidden, tag_ids, tag_or_groups,
|
||
) -> list[int] | None:
|
||
"""Presentation-chrome tag ids to implicitly exclude from a gallery query,
|
||
or None. None when the caller asked to include hidden, when the operator
|
||
is explicitly filtering FOR a presentation tag (they clearly want to see
|
||
it), or when no presentation tags exist. (milestone 141)"""
|
||
if include_hidden:
|
||
return None
|
||
rows = await self.session.execute(
|
||
select(Tag.id).where(
|
||
Tag.is_system.is_(True),
|
||
Tag.name.in_(PRESENTATION_SYSTEM_TAGS),
|
||
)
|
||
)
|
||
pres = [r[0] for r in rows]
|
||
if not pres:
|
||
return None
|
||
explicit = set(tag_ids or [])
|
||
for group in tag_or_groups or []:
|
||
explicit.update(group)
|
||
if explicit & set(pres):
|
||
return None
|
||
return pres
|
||
|
||
async def scroll(
|
||
self,
|
||
cursor: str | None,
|
||
limit: int = 50,
|
||
tag_ids: list[int] | None = None,
|
||
post_id: int | None = None,
|
||
artist_id: int | None = None,
|
||
media_type: str | None = None,
|
||
sort: str = "newest",
|
||
tag_or_groups: list[list[int]] | None = None,
|
||
tag_exclude: list[int] | None = None,
|
||
platform: str | None = None,
|
||
untagged: bool = False,
|
||
no_artist: bool = False,
|
||
date_from: datetime | None = None,
|
||
date_to: datetime | None = None,
|
||
include_hidden: bool = False,
|
||
) -> GalleryPage:
|
||
if limit < 1 or limit > 200:
|
||
raise ValueError("limit must be between 1 and 200")
|
||
_require_single_filter(
|
||
tag_ids, post_id, artist_id, tag_or_groups, tag_exclude,
|
||
)
|
||
hidden = await self._hidden_tag_ids(
|
||
include_hidden, tag_ids, tag_or_groups,
|
||
)
|
||
|
||
# eff is the ACTIVE sort column (effective_date or earliest_post_date);
|
||
# the cursor, ordering and year/month grouping all key off it, so the
|
||
# 'post date' sort paginates + buckets by original publish transparently.
|
||
eff = _sort_column(sort)
|
||
stmt = select(ImageRecord, Post.post_date, eff.label("eff"))
|
||
stmt = _outer_join_primary_post(stmt)
|
||
stmt = _apply_scope(
|
||
stmt, tag_ids=tag_ids, post_id=post_id,
|
||
artist_id=artist_id, media_type=media_type,
|
||
tag_or_groups=tag_or_groups, tag_exclude=tag_exclude,
|
||
platform=platform, untagged=untagged, no_artist=no_artist,
|
||
date_from=date_from, date_to=date_to, hidden_tag_ids=hidden,
|
||
)
|
||
|
||
descending = sort not in _ASCENDING_SORTS
|
||
if cursor:
|
||
cur_ts, cur_id = decode_cursor(cursor)
|
||
# The cursor is just (last eff, last id); the request's sort
|
||
# decides which side of it the next page lies on.
|
||
if descending:
|
||
stmt = stmt.where(
|
||
or_(eff < cur_ts, and_(eff == cur_ts, ImageRecord.id < cur_id))
|
||
)
|
||
else:
|
||
stmt = stmt.where(
|
||
or_(eff > cur_ts, and_(eff == cur_ts, ImageRecord.id > cur_id))
|
||
)
|
||
|
||
if descending:
|
||
stmt = stmt.order_by(eff.desc(), ImageRecord.id.desc())
|
||
else:
|
||
stmt = stmt.order_by(eff.asc(), ImageRecord.id.asc())
|
||
stmt = stmt.limit(limit + 1)
|
||
rows = (await self.session.execute(stmt)).all()
|
||
|
||
next_cursor = None
|
||
if len(rows) > limit:
|
||
last_record, _last_posted_at, last_eff = rows[limit - 1]
|
||
next_cursor = encode_cursor(last_eff, last_record.id)
|
||
rows = rows[:limit]
|
||
|
||
artists = await _artists_for(
|
||
self.session, [r[0].id for r in rows]
|
||
)
|
||
images = _gallery_images(rows, artists)
|
||
return GalleryPage(
|
||
images=images,
|
||
next_cursor=next_cursor,
|
||
date_groups=_group_by_year_month(images),
|
||
)
|
||
|
||
async def timeline(
|
||
self,
|
||
tag_ids: list[int] | None = None,
|
||
post_id: int | None = None,
|
||
artist_id: int | None = None,
|
||
media_type: str | None = None,
|
||
tag_or_groups: list[list[int]] | None = None,
|
||
tag_exclude: list[int] | None = None,
|
||
platform: str | None = None,
|
||
untagged: bool = False,
|
||
no_artist: bool = False,
|
||
date_from: datetime | None = None,
|
||
date_to: datetime | None = None,
|
||
include_hidden: bool = False,
|
||
) -> list[TimelineBucket]:
|
||
eff = _effective_date_col()
|
||
year_col = func.date_part("year", eff).label("yr")
|
||
month_col = func.date_part("month", eff).label("mo")
|
||
stmt = select(
|
||
year_col, month_col, func.count(ImageRecord.id).label("cnt")
|
||
)
|
||
stmt = _outer_join_primary_post(stmt)
|
||
_require_single_filter(
|
||
tag_ids, post_id, artist_id, tag_or_groups, tag_exclude,
|
||
)
|
||
hidden = await self._hidden_tag_ids(
|
||
include_hidden, tag_ids, tag_or_groups,
|
||
)
|
||
stmt = _apply_scope(
|
||
stmt, tag_ids=tag_ids, post_id=post_id,
|
||
artist_id=artist_id, media_type=media_type,
|
||
tag_or_groups=tag_or_groups, tag_exclude=tag_exclude,
|
||
platform=platform, untagged=untagged, no_artist=no_artist,
|
||
date_from=date_from, date_to=date_to, hidden_tag_ids=hidden,
|
||
)
|
||
stmt = stmt.group_by(year_col, month_col).order_by(year_col.desc(), month_col.desc())
|
||
rows = (await self.session.execute(stmt)).all()
|
||
return [TimelineBucket(year=int(r.yr), month=int(r.mo), count=int(r.cnt)) for r in rows]
|
||
|
||
async def jump_cursor(
|
||
self, year: int, month: int, tag_ids: list[int] | None = None,
|
||
post_id: int | None = None, artist_id: int | None = None,
|
||
media_type: str | None = None, sort: str = "newest",
|
||
tag_or_groups: list[list[int]] | None = None,
|
||
tag_exclude: list[int] | None = None,
|
||
platform: str | None = None, untagged: bool = False,
|
||
no_artist: bool = False, date_from: datetime | None = None,
|
||
date_to: datetime | None = None, include_hidden: bool = False,
|
||
) -> str | None:
|
||
"""Returns a cursor that, when passed to scroll() with the same sort,
|
||
positions at the first image of the given year-month. None if the
|
||
bucket is empty.
|
||
"""
|
||
from sqlalchemy import extract
|
||
|
||
eff = _effective_date_col()
|
||
stmt = select(ImageRecord, eff.label("eff")).where(
|
||
extract("year", eff) == year,
|
||
extract("month", eff) == month,
|
||
)
|
||
stmt = _outer_join_primary_post(stmt)
|
||
_require_single_filter(
|
||
tag_ids, post_id, artist_id, tag_or_groups, tag_exclude,
|
||
)
|
||
hidden = await self._hidden_tag_ids(
|
||
include_hidden, tag_ids, tag_or_groups,
|
||
)
|
||
stmt = _apply_scope(
|
||
stmt, tag_ids=tag_ids, post_id=post_id,
|
||
artist_id=artist_id, media_type=media_type,
|
||
tag_or_groups=tag_or_groups, tag_exclude=tag_exclude,
|
||
platform=platform, untagged=untagged, no_artist=no_artist,
|
||
date_from=date_from, date_to=date_to, hidden_tag_ids=hidden,
|
||
)
|
||
descending = sort != "oldest"
|
||
if descending:
|
||
stmt = stmt.order_by(eff.desc(), ImageRecord.id.desc())
|
||
else:
|
||
stmt = stmt.order_by(eff.asc(), ImageRecord.id.asc())
|
||
first = (await self.session.execute(stmt.limit(1))).first()
|
||
if first is None:
|
||
return None
|
||
record, eff_date = first
|
||
# Cursor is exclusive; nudge the id one past the boundary row (in the
|
||
# scan direction) so the row itself is the first result of scroll().
|
||
boundary = record.id + 1 if descending else record.id - 1
|
||
return encode_cursor(eff_date, boundary)
|
||
|
||
async def facets(
|
||
self, *, tag_ids: list[int] | None = None,
|
||
post_id: int | None = None, artist_id: int | None = None,
|
||
media_type: str | None = None,
|
||
tag_or_groups: list[list[int]] | None = None,
|
||
tag_exclude: list[int] | None = None,
|
||
platform: str | None = None,
|
||
untagged: bool = False, no_artist: bool = False,
|
||
date_from: datetime | None = None, date_to: datetime | None = None,
|
||
include_hidden: bool = False,
|
||
) -> GalleryFacets:
|
||
"""Live facet counts scoped to the current filter. Each facet GROUP is
|
||
computed with all OTHER active filters applied but its OWN selection
|
||
ignored ("minus-self"), so sibling options stay visible/switchable.
|
||
No outer join is needed — every clause is a correlated EXISTS or a
|
||
column predicate on ImageRecord.
|
||
"""
|
||
_require_single_filter(
|
||
tag_ids, post_id, artist_id, tag_or_groups, tag_exclude,
|
||
)
|
||
hidden = await self._hidden_tag_ids(
|
||
include_hidden, tag_ids, tag_or_groups,
|
||
)
|
||
common = {
|
||
"tag_ids": tag_ids, "post_id": post_id,
|
||
"artist_id": artist_id, "media_type": media_type,
|
||
"tag_or_groups": tag_or_groups, "tag_exclude": tag_exclude,
|
||
"hidden_tag_ids": hidden,
|
||
}
|
||
|
||
# total — the full active filter (the headline result count).
|
||
total = (await self.session.execute(
|
||
_apply_scope(
|
||
select(func.count(ImageRecord.id)), **common,
|
||
platform=platform, untagged=untagged, no_artist=no_artist,
|
||
date_from=date_from, date_to=date_to,
|
||
)
|
||
)).scalar_one()
|
||
|
||
# platforms — scope minus the platform selection. Inner-join
|
||
# provenance→source and COUNT(DISTINCT image) per platform (a
|
||
# cross-posted image counts under each of its platforms).
|
||
plat_scope = {
|
||
**common, "untagged": untagged, "no_artist": no_artist,
|
||
"date_from": date_from, "date_to": date_to,
|
||
}
|
||
src = aliased(Source)
|
||
plat_stmt = (
|
||
select(src.platform, func.count(distinct(ImageRecord.id)))
|
||
.select_from(ImageRecord)
|
||
.join(ImageProvenance, ImageProvenance.image_record_id == ImageRecord.id)
|
||
.join(src, src.id == ImageProvenance.source_id)
|
||
)
|
||
plat_stmt = _apply_scope(plat_stmt, **plat_scope).group_by(src.platform)
|
||
platforms = [
|
||
{"value": p, "count": c}
|
||
for p, c in (await self.session.execute(plat_stmt)).all()
|
||
]
|
||
# Unsourced (filesystem) bucket — same minus-platform scope.
|
||
unsourced = (await self.session.execute(
|
||
_apply_scope(
|
||
select(func.count(ImageRecord.id)), **plat_scope,
|
||
platform=UNSOURCED_PLATFORM,
|
||
)
|
||
)).scalar_one()
|
||
if unsourced:
|
||
platforms.append({"value": None, "count": unsourced})
|
||
|
||
# curation flags — each minus its OWN flag.
|
||
untagged_count = (await self.session.execute(
|
||
_apply_scope(
|
||
select(func.count(ImageRecord.id)), **common,
|
||
platform=platform, no_artist=no_artist,
|
||
date_from=date_from, date_to=date_to, untagged=True,
|
||
)
|
||
)).scalar_one()
|
||
no_artist_count = (await self.session.execute(
|
||
_apply_scope(
|
||
select(func.count(ImageRecord.id)), **common,
|
||
platform=platform, untagged=untagged,
|
||
date_from=date_from, date_to=date_to, no_artist=True,
|
||
)
|
||
)).scalar_one()
|
||
|
||
# date bounds — scope minus the date params (those drive the picker).
|
||
eff = _effective_date_col()
|
||
dmin, dmax = (await self.session.execute(
|
||
_apply_scope(
|
||
select(func.min(eff), func.max(eff)), **common,
|
||
platform=platform, untagged=untagged, no_artist=no_artist,
|
||
)
|
||
)).one()
|
||
|
||
return GalleryFacets(
|
||
total=total, platforms=platforms,
|
||
untagged=untagged_count, no_artist=no_artist_count,
|
||
date_min=dmin, date_max=dmax,
|
||
)
|
||
|
||
async def similar(
|
||
self, image_id: int, limit: int = 100, *,
|
||
tag_ids: list[int] | None = None, artist_id: int | None = None,
|
||
media_type: str | None = None,
|
||
tag_or_groups: list[list[int]] | None = None,
|
||
tag_exclude: list[int] | None = None,
|
||
platform: str | None = None,
|
||
untagged: bool = False, no_artist: bool = False,
|
||
date_from: datetime | None = None, date_to: datetime | None = None,
|
||
exclude_wip: bool = False,
|
||
) -> list[GalleryImage] | None:
|
||
"""Visual "more like this": images near `image_id`'s SigLIP embedding
|
||
(pgvector, HNSW-indexed — alembic 0036), then DIVERSIFIED so the result
|
||
doesn't collapse into one cluster. No ML inference here.
|
||
|
||
Pure nearest-cosine piles up near-identical images — a reposted banner
|
||
fills the whole grid, and once you wander into a B&W / comic-panel
|
||
cluster every neighbour is more of the same with no way back to colour
|
||
(operator-reported 2026-06-30). So we pull a WIDER candidate pool, then:
|
||
1. collapse near-duplicate pHashes (and drop clones of the anchor),
|
||
2. MMR re-rank — pick for closeness-to-anchor but penalise similarity
|
||
to what's already picked, so the result SPANS clusters.
|
||
|
||
Returns None if the source doesn't exist (→ 404), [] if it has no
|
||
embedding. Composes with the scope filters (AND); REPLACES the date sort.
|
||
"""
|
||
if limit < 1 or limit > 200:
|
||
raise ValueError("limit must be between 1 and 200")
|
||
src = await self.session.get(ImageRecord, image_id)
|
||
if src is None:
|
||
return None
|
||
if src.siglip_embedding is None:
|
||
return []
|
||
|
||
# Over-fetch so diversification has clusters to spread across — without a
|
||
# wide pool there's nothing but the near-dupes to choose from. Widened
|
||
# (5×→8×, cap 200→400) so the stronger MMR has genuinely distinct
|
||
# neighbourhoods to reach into for more variance (operator, 2026-07-01).
|
||
pool_n = min(400, max(limit * 8, 100))
|
||
distance = ImageRecord.siglip_embedding.cosine_distance(src.siglip_embedding)
|
||
eff = _effective_date_col()
|
||
stmt = select(ImageRecord, Post.post_date, eff.label("eff"))
|
||
stmt = _outer_join_primary_post(stmt)
|
||
# Presentation images (banner / editor-screenshot system tags, #128)
|
||
# cluster on UI chrome rather than content, so near any one of them
|
||
# they'd fill the grid. Excluded from CANDIDATES only — the anchor
|
||
# itself may be a banner. `wip` stays surfaced here by default (real art;
|
||
# only the training pipelines exclude it), but the Explore rabbit-hole
|
||
# passes exclude_wip to also drop work-in-progress (operator, 2026-07-08).
|
||
excluded_system_tags = PRESENTATION_SYSTEM_TAGS
|
||
if exclude_wip:
|
||
excluded_system_tags = (*PRESENTATION_SYSTEM_TAGS, WIP_SYSTEM_TAG)
|
||
presentation = (
|
||
select(image_tag.c.image_record_id)
|
||
.join(Tag, Tag.id == image_tag.c.tag_id)
|
||
.where(
|
||
Tag.is_system.is_(True),
|
||
Tag.name.in_(excluded_system_tags),
|
||
)
|
||
)
|
||
stmt = stmt.where(
|
||
ImageRecord.siglip_embedding.is_not(None),
|
||
ImageRecord.id != image_id,
|
||
ImageRecord.id.not_in(presentation),
|
||
)
|
||
stmt = _apply_scope(
|
||
stmt, tag_ids=tag_ids, post_id=None,
|
||
artist_id=artist_id, media_type=media_type,
|
||
tag_or_groups=tag_or_groups, tag_exclude=tag_exclude,
|
||
platform=platform, untagged=untagged, no_artist=no_artist,
|
||
date_from=date_from, date_to=date_to,
|
||
)
|
||
stmt = stmt.order_by(distance.asc()).limit(pool_n)
|
||
rows = (await self.session.execute(stmt)).all()
|
||
rows = _diversify_similar(src, rows, limit)
|
||
artists = await _artists_for(self.session, [r[0].id for r in rows])
|
||
return _gallery_images(rows, artists)
|
||
|
||
async def get_image_with_tags(self, image_id: int) -> dict | None:
|
||
record = await self.session.get(ImageRecord, image_id)
|
||
if record is None:
|
||
return None
|
||
# Self-join Tag to resolve a character's fandom NAME (not just id) so the
|
||
# modal chip can label it without an N+1 (shared tag_query helpers).
|
||
fandom_alias = fandom_join_alias()
|
||
# source drives the auto-applied badge; confirmed = operator affirmed the
|
||
# tag (positive + retraction-shielded, milestone 139).
|
||
confirmed = exists().where(
|
||
TagPositiveConfirmation.image_record_id == image_id,
|
||
TagPositiveConfirmation.tag_id == Tag.id,
|
||
).label("confirmed")
|
||
tag_stmt = (
|
||
select(*tag_columns(fandom_alias), image_tag.c.source, confirmed)
|
||
.select_from(
|
||
Tag.__table__
|
||
.join(image_tag, image_tag.c.tag_id == Tag.id)
|
||
.outerjoin(fandom_alias, Tag.fandom_id == fandom_alias.c.id)
|
||
)
|
||
.where(image_tag.c.image_record_id == image_id)
|
||
.order_by(Tag.kind.asc(), Tag.name.asc())
|
||
)
|
||
tags = (await self.session.execute(tag_stmt)).all()
|
||
# Fetch the canonical post.post_date for this image (if any) so
|
||
# the modal can show "Posted on <date>" alongside import date.
|
||
posted_at = None
|
||
if record.primary_post_id is not None:
|
||
posted_at = (await self.session.execute(
|
||
select(Post.post_date).where(Post.id == record.primary_post_id)
|
||
)).scalar_one_or_none()
|
||
neighbors = await self._neighbors(record)
|
||
# Direct artist FK — used by the modal's ProvenancePanel as a
|
||
# fallback when ImageProvenance is empty (i.e., filesystem-
|
||
# imported images without a post-track provenance row). The
|
||
# source of truth for richer post-level data is still
|
||
# ImageProvenance/Post; this is just the "we at least know who
|
||
# made it" line.
|
||
artist = None
|
||
if record.artist_id is not None:
|
||
artist = await self.session.get(Artist, record.artist_id)
|
||
return {
|
||
"id": record.id,
|
||
"path": record.path,
|
||
"sha256": record.sha256,
|
||
"mime": record.mime,
|
||
"width": record.width,
|
||
"height": record.height,
|
||
"size_bytes": record.size_bytes,
|
||
"integrity_status": record.integrity_status,
|
||
# Phase 3: lets the modal hide the "Related"/find-similar surface
|
||
# for images that have no embedding yet (videos / pending ML).
|
||
"has_embedding": record.siglip_embedding is not None,
|
||
"created_at": record.created_at.isoformat(),
|
||
"posted_at": posted_at.isoformat() if posted_at else None,
|
||
"thumbnail_url": thumbnail_url(record.thumbnail_path, record.sha256, record.mime),
|
||
"image_url": image_url(record.path),
|
||
"artist": (
|
||
{"id": artist.id, "name": artist.name, "slug": artist.slug}
|
||
if artist is not None else None
|
||
),
|
||
"tags": [serialize_tag(t) for t in tags],
|
||
"neighbors": neighbors,
|
||
}
|
||
|
||
async def _neighbors(self, record: ImageRecord) -> dict:
|
||
# The boundary image's sort key is materialized on the row now
|
||
# (alembic 0035) — read it directly instead of re-deriving COALESCE
|
||
# via an extra Post lookup.
|
||
boundary_eff = record.effective_date
|
||
|
||
eff = _effective_date_col()
|
||
prev_stmt = _outer_join_primary_post(
|
||
select(ImageRecord.id).where(
|
||
or_(
|
||
eff > boundary_eff,
|
||
and_(
|
||
eff == boundary_eff,
|
||
ImageRecord.id > record.id,
|
||
),
|
||
)
|
||
)
|
||
).order_by(eff.asc(), ImageRecord.id.asc()).limit(1)
|
||
next_stmt = _outer_join_primary_post(
|
||
select(ImageRecord.id).where(
|
||
or_(
|
||
eff < boundary_eff,
|
||
and_(
|
||
eff == boundary_eff,
|
||
ImageRecord.id < record.id,
|
||
),
|
||
)
|
||
)
|
||
).order_by(eff.desc(), ImageRecord.id.desc()).limit(1)
|
||
prev_id = (await self.session.execute(prev_stmt)).scalar_one_or_none()
|
||
next_id = (await self.session.execute(next_stmt)).scalar_one_or_none()
|
||
return {"prev_id": prev_id, "next_id": next_id}
|
||
|
||
|
||
def _group_by_year_month(
|
||
images: list[GalleryImage],
|
||
) -> list[tuple[int, int, list[int]]]:
|
||
"""Group by effective_date's year/month so migrated content surfaces
|
||
in the publish-date buckets, not the FC-scan-date bucket."""
|
||
groups: list[tuple[int, int, list[int]]] = []
|
||
for img in images:
|
||
y, m = img.effective_date.year, img.effective_date.month
|
||
if groups and groups[-1][0] == y and groups[-1][1] == m:
|
||
groups[-1][2].append(img.id)
|
||
else:
|
||
groups.append((y, m, [img.id]))
|
||
return groups
|