Kazde szukanie slowa w tytulach bylo sekwencyjnym skanem 3,46 mln wierszy. Blokowalo
to ranking over-attribution: metryka "ile scen ma te nazwe w tytule, ale NIE jest do
niej przypisanych" wymaga jednego skanu NA KANDYDATA, a kandydatow jest ~800. Proba
zostala ubita po 10 minutach.
GIN + gin_trgm_ops obsluguje LIKE, ILIKE i regexy, a pg_trgm sam normalizuje wielkosc
liter, wiec indeks na surowym title wystarcza. Efekt: 26 ms zamiast sekund na
zapytanie, ranking 800 kandydatow w 48 sekund zamiast godzin. Indeks 315 MB.
Zbudowany CONCURRENTLY w autocommit_block: zwykle CREATE INDEX trzymaloby lock
blokujacy zapisy na scenes przez cala budowe i zatrzymalo ingest.
Ranking od razu pokazuje dwie rozne rzeczy. Czyste smieci ("Pornhub" 105.9, "Monster"
92, "Pretty" 63, "Nice" 42) oraz SAME NAZWISKA (Devine, Starr, Dior, Cruz, West,
Knight, Johnson, James) - wpisy powstale z rozbicia imienia i nazwiska, ktore potem
lapia kazda scene z tym nazwiskiem, nalezaca do zupelnie innej osoby.
Usuniete przy okazji: "Pornhub" (108 scen, 0 z kanonu, wszystkie to "full video on
pornhub") i "Precious" (119 scen, 0 z kanonu, sam przymiotnik).
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
scene_performers nie zapisywalo, KTO przypisal performera do sceny. Przez to nie dalo
sie odroznic obsady z kanonu od performera doklejonego przez tube-search, ktory
dopasowuje wynik po JEDNYM tokenie zapytania. Stad przypadek "Rapture": 30 z 72 jej
przypisan bylo falszywych (film "The Rapture" z Mimi Rogers, literowka "hymen
raptured", tytuly scen) i trzeba bylo je przegladac recznie, bo nie bylo sygnalu.
Kolumna trzyma NAJBARDZIEJ wiarygodne zrodlo, jakie przypisalo. Regula idzie tylko w
gore: NULL wypelnia cokolwiek, kanon nadpisuje scraper, scraper NIGDY nie zamazuje
kanonu. Inaczej dowolny pozniejszy tube-search zatarlby informacje, ze obsade
potwierdzilo TPDB. To samo przy merge_scenes: przy kolizji przenosimy lepsza
provenance na keepera, zeby scalanie nie degradowalo potwierdzonej obsady.
Backfill wypelnil 4 147 128 wierszy (96,3 procent) wnioskowaniem, ktore jest pewne:
scena z refami z DOKLADNIE jednego zrodla ma cala obsade stamtad. Sceny wielozrodlowe
(67 tys.) zostaja z NULL-em, bo tam zgadywanie zepsuloby sens kolumny. Wsadowo, z
commitem na paczke: pojedynczy UPDATE na 4 mln wierszy trzymalby locki na
scene_performers i zablokowal ingest.
Rozklad: tube-scraper 3 219 677, tpdb 747 540, stashdb 180 355.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
Audyt duplikatow po phashu miniatur wykazal dwa rozne zjawiska, ktore wygladaly
jak jedno.
1. Zdegenerowane phashe. 73 wartosci wystepowaly przy >=10 scenach kazda, lacznie
przy 4375 scenach; rekordzistka byla dzielona przez 892 sceny o 892 roznych
tytulach i 1886 roznych performerach. To zaslepki i czarne klatki, nie odciski
scen. find_by_phash_within bierze najblizsza wartosc z calej tabeli, wiec taka
zaslepka zawsze wygrywala z prawdziwym duplikatem (dist 0). Do tej pory bronila
nas bramka dur_prox i nic sie nie skleilo, ale to zabezpieczenie drugiej linii.
Blacklista jest tabela, nie jednorazowym DELETE, bo sam DELETE nic nie daje:
zaslepka wraca przy kolejnym ingescie. Trzeba pamietac, ze wartosc jest
bezuzyteczna. Job co 24h dopisuje nowe i czysci odciski. Po czyszczeniu zero
grup >=10, najwieksza pozostala ma 9.
2. Realne duplikaty. 4647 scalonych. Przyczyna byla jedna: 97 procent par ma
perverzije po dokladnie jednej stronie, bo pisze tytuly z prefiksem studia i
performera, a reszta tubow daje goly tytul. Wbrew mojej pierwszej diagnozie NIE
trzeba tu ruszac scoringu tytulu (token_set_ratio i tak radzi sobie z prefiksem,
a sciezka phash idzie przed composite): 80 procent par to dlug sprzed 60+ dni, a
biezacy wyciek to okolo 1 dziennie. Nadmiarowe wiersze: 18452 na 9449.
merge_phash_exact_dupes.py dostal wykluczenie blacklisty. Bez tego byl grozny:
sam klaster 892 scen dawal ~397 tys. par do rozwazenia.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
Ten sam tag z różnych tubów tworzył osobne wpisy (Blowjob/Blow Job, Big Tits/
Bigtits, deep-throat/deepthroat, creampie-1/creampie, mojibake CJK 'Japanese'…)
bo resolve_tag brał slug/slugify(name) verbatim. ~11k nadmiarowych tagów.
- resolve_tag: przed utworzeniem nowego tagu szuka istniejącego kanonicznego po
alnum-kluczu nazwy (_resolve_by_altkey) i reużywa go → warianty nie regenerują.
- Indeks funkcyjny ix_tags_name_altkey (migracja 0028) pod ten lookup.
- scripts/merge_altkey_tags.py: jednorazowy backfill istniejących duplikatów
(kanoniczny = max scene_count, scene_tags/movie_tags/blacklisted_tags przepięte
z dedupem na PK, dropy skasowane, taxonomy_counts odświeżone).
The zload CDN URL (remote_control.php) is a valid faststart progressive mp4 that
ExoPlayer plays fine when fresh: 206, no IP/referer binding, ~2h token TTL,
verified from a residential IP. Real-device failures (player_error/gone
telemetry) come from the static URL going stale: a rotated or dead CDN node, or
an expired token.
Instead of falling back to the tube page in a WebView (removed, it papered over
the real issue), PlayerScreen now re-resolves the scene fresh via the backend on
initial-load error for backend-native KVS tubes (watchporn). That yields a live
node plus a fresh token and stays on the native direct stream (0 VPS bandwidth).
For these origins a CDN 'gone' (404) means stale URL, not deleted, so we
re-resolve on gone too; a genuinely deleted post raises HosterDead (410) and
falls through to the normal fallback chain.
Also: add playback_events.error_detail (raw ExoPlayer message) to pin down the
exact failure of tubes we cannot reproduce on the emulator. Keep FLAG_SECURE on
release builds only (!__DEV__) so debug builds stay screenshottable for local UI
verification.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
Addresses the ultra-review findings on this branch:
Player (PlayerScreen.tsx): the new recoveryPending mirrored the fallback-chain guards
by hand and could deadlock into a permanent "Reconnecting" spinner with no way to Mark
broken — for gone (410) sources on IP-bound tubes (re-resolve bails before setting
reResolveDone) and for any post-load error on those tubes (re-resolve is initial-load
only). Derive one reResolveApplicable flag (IP-bound AND initial-load AND not-gone) and
use it for both the chain gate and the spinner, so gone/post-load errors fall through to
proxy/WebView or the terminal error card. Seek-recovery now falls through to the chain
when player.replace() throws instead of returning.
Quick-play (SceneDetail): the autoplay route param persisted and autoPlay={i===0} re-fired
when the source list reordered (e.g. after Mark broken drops the dead source), bouncing the
user into the player. Consume it once via onAutoPlayConsumed -> nav.setParams({autoplay:false}).
Backfill semantics: performer-driven direct-scraper "backward fill" now tags scenes
backfill=True (search-by-name pulls the whole old catalog); merge coalesces backfill
(keep AND drop) so a fresh scene merged into a dead dup keeps NEW; deep-crawl only tags
backfill on a tube's FIRST sweep (swept_once) so re-sweep catalog growth stays genuine;
pilot script tags backfill.
Perf/migration: migration 0026 is now idempotent (IF NOT EXISTS; prod got the column via
manual ALTER) and adds ix_scene_performers_performer_id (favorites count filtered
performer_id with no index); index also created on prod.
Cleanup: deleted dead FavoriteSceneRow (unused import in two screens, stale isNew without
the backfill guard); removed em-dashes from all lines this branch added (user CLAUDE.md
rule), including the user-facing changelog / Settings / player-overlay strings.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Browse scrapers backfilling old catalogs stamp the tube's import/post date as
release_date, so old content (e.g. 83 MissaX classics via perverzija, ~3600/3 days
across eporner/youporn/etc.) fake-ranked as newest and flooded the favorites "+N".
NULLing the dates was a non-starter — the stub filter would hide 251k performer-less
scenes. Instead: a Scene.backfill flag marks bulk catalog imports; they stay visible but
never count as "new".
- scenes.backfill column (+ index, migration 0026); resolve_scene/_process_scene thread it.
- deep_crawl tags scenes from pages beyond the "latest" threshold (>2) as backfill;
latest pages + TPDB/StashDB delta stay genuine. Cursor reset re-sweeps page 1 so real
new content is always caught fresh.
- favorites +N (performers + studios) excludes backfill within the top-200 window.
- SceneOut exposes `backfill`; mobile NEW badge + NEW-first re-sort skip it (badge==count).
- Retroactive: tagged 439k existing scenes in bulk (>10 non-canonical / studio / day)
clusters. Device check: favorite-studios +N 11626 (naive) -> 236.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Rates each source on three axes the user asked for:
- freshness: how recently/often new content arrives (newest age + 7d volume)
- richness: metadata coverage (thumbnail/tags/performers/description/studio/duration)
- plays: does it actually play — from real playback telemetry when available,
else a proxy from the resolve mechanism. 0★ = offline (gates the overall stars,
so a fresh+rich source that doesn't play still ranks bottom — the hqfap/4k69 case)
Backend:
- playback_events: fire-and-forget telemetry POST from the app per playback attempt
(origin + success/error + time-to-first-frame), append-only, 30d retention
- source_stats: per-origin computed scores, refreshed by a scheduler job (6h);
/sources joins it and sorts by stars
- models + local migration 0025; new GOON_SCHED_SOURCE_STATS_HOURS setting
Mobile:
- Sites rows show ★ rating; tap the stars for a breakdown (axes + metadata %, plus
whether "plays" is measured or estimated)
- PlayerScreen reports playback success/failure per source (native path only —
symmetric, conservative); origin threaded through Scene/Movie play callsites
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
User-report (mobilism): scenes are often poorly titled, so saved keyword queries are a useful extra retrieval strategy. New saved_searches table (device-scoped via X-Device-Id, unique per device+query, 50/device cap) + GET/POST/DELETE /saved-searches. Migration 0024. Verified CRUD on prod: add trims+dedups idempotently, empty rejected 422, delete idempotent.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Reports were anonymous and one-way. Tie each report to the submitting device
(X-Device-Id), add an admin response back-channel, and let the app fetch replies for
its own device:
- migration 0023: bug_reports gains device_id, response, responded_at, response_seen.
- create_bug_report captures device_id.
- GET /bug-reports/mine (device-scoped) returns this device's reports + unseen count.
- POST /bug-reports/mine/seen clears the unseen flag.
- POST /bug-reports/{id}/reply sets the admin response (authored during triage).
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Public instance has no accounts, so all user state was GLOBAL in DB — new users
saw/overwrote each other's (and Jan's) favorites, watched badges and blacklists
(bug 2026-06-10). Add device_id (VARCHAR 64) to 9 state tables with composite PK
(device_id, entity_id); app sends X-Device-Id header (get_device_id dep). All
favorites/scene-favorites/blacklist/watch + scene&movie list/detail (is_favorite,
watched, blacklist-hide) now filter by device. Existing rows backfilled to
'legacy-shared'; POST /me/adopt-legacy reassigns them to the caller once. Old
clients (no header) map to legacy-shared so they keep working until OTA updates.
Migration 0022: add col, backfill, composite PK. Verified on prod: 967 progress
rows preserved, device isolation holds (new device sees none of legacy state).
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
Completes the literal-tag_id perf fix — the planner's MCV stats on tag_id are what
let it pick the index-walk for common tags. Default target (100) covers only the
top ~100 tags; 1000 extends correct cardinality estimates to mid-tier tags.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
The filtered scene-list endpoints (default feed sends min_duration_sec=60, plus
has_playback / tag / q filters) took ~4.5s — and an idle server. Profiling showed
the entire cost was the bounded COUNT subquery over the EXISTS filters: Postgres
would not reliably early-terminate at the cap under psycopg bound params, scanning
the whole matching set (~858k for has_playback). Counting over the PK and using a
literal LIMIT helped some cases but the plan stayed unstable.
Fix: stop computing an exact count for filtered lists entirely. The mobile client
paginates by has_more (per_page+1 fetch), never by total — total is only the "N+"
UI counter. Derive total as a lower bound from the page + has_more after the fetch.
This removes the count query from every filtered request.
Result (end-to-end, authenticated): default feed 4.5s -> ~0.1s, has_playback
4.4s -> ~0.1s, q/studio/normal-tag filters all <0.3s. Also added index
scene_tags(tag_id, scene_id) (PK led with scene_id, so tag->scenes did a seq scan).
Remaining: a single enormous tag (e.g. "anal", ~163k scenes) ordered by recency
still gathers-all-then-sorts in the fetch (~5s); normal tags are <0.5s. Tracked
in #22 for a denormalized recency-ordered approach.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Counts for /tags, /performers, /studios and /favorites were computed live
per-request by aggregating scene_tags / scene_performers with an EXISTS to
playback_sources. As the catalog grew to ~1.7M scenes (6.3M scene_tags) this
ran ~4.3s for /tags?order=popular (x2 incl. the total count) and ~950ms for
the default /scenes count, making those screens load in several seconds.
- migration 0019: add scene_count (+ DESC index) to tags/performers/studios
- background job _job_refresh_taxonomy_counts (every 3h) recomputes the counts
in one UPDATE..FROM each (IS DISTINCT FROM to skip unchanged rows)
- /tags, /performers, /studios scenes path now read the column + ORDER BY the
indexed scene_count; for_movies paths keep live aggregation (small tables)
- favorites read denormalized scene_count instead of a grouped EXISTS aggregate
- /scenes default count: 10-min in-process TTL cache (header is approximate)
Measured: /tags?order=popular&per_page=500 ~8s -> 66ms incl. serialization.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Goon — self-hosted aggregator for adult-content scene metadata.
Indexes scenes from TPDB, StashDB, and 30+ public adult tube sites.
Cross-source deduplication via perceptual hash + Levenshtein distance.
FastAPI backend + APScheduler worker + React Native (Expo) mobile client.
FOSS, ad-free, donation-funded. See README for details.
