bvandeusen
a8f624a0f1
The native Patreon backfill flooded the feed with bare 'Post <id>' shells (1589 for Anduo). Root cause: PostAttachment.sha256 was GLOBALLY unique, so a non-art file reused across posts only ever linked to the first one, and _capture_attachment created the Post before that dedup check — leaving later posts with no image and no attachment. Duplicate IMAGES had the mirror gap: attach_in_place returned duplicate_hash/duplicate_phash before _apply_sidecar, so the second post got no provenance row, and the feed only rendered via primary_post_id (one post per image). Operator requirement: a duplicate item must show on EVERY post it appears in. Unify the fix as link-not-suppress: - importer: on duplicate_hash / duplicate_phash(larger_exists), append an image_provenance row for the new post (keep primary on the first). Both the download path (attach_in_place) and the filesystem path (_import_media). - post_feed_service: render thumbnails by image_provenance UNION primary_post_id, so a cross-posted image shows on every post (and legacy primary-only images still show). - PostAttachment: per-post uniqueness — drop UNIQUE(sha256), add partial UNIQUE(post_id, sha256) + partial UNIQUE(sha256) WHERE post_id IS NULL (migration 0043); _capture_attachment dedups per-(post,sha) over the shared sha-addressed blob, so no post is left bare. - cleanup: new prune-bare-posts maintenance action (cleanup_service _bare_post_conditions shared by preview/count/delete per preview/apply parity; admin endpoint; PostMaintenanceCard). Deletes posts with zero image links (primary or provenance) AND zero attachments. Run after the feed fix so a hidden provenance link spares the post instead of deleting it. Tests: dup image shows on both posts; dup attachment shows on both posts; feed renders provenance-linked duplicates; prune-bare delete-path == preview. Operator redeploys (migration 0043) then runs the prune to clear the shells. Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
63 lines
2.3 KiB
Python
63 lines
2.3 KiB
Python
"""post_attachment: per-post sha uniqueness (empty-post flood fix)
|
|
|
|
Revision ID: 0043
|
|
Revises: 0042
|
|
Create Date: 2026-06-08
|
|
|
|
PostAttachment.sha256 was GLOBALLY unique, so a non-art file the creator attaches
|
|
to many posts (a standard pdf/zip/link-card) only ever got ONE row — on the first
|
|
post — leaving every later post a bare shell (no image, no attachment). The native
|
|
Patreon backfill of Anduo surfaced 1589 such shells (operator-flagged 2026-06-08).
|
|
|
|
Switch to PER-POST uniqueness: the on-disk blob stays sha-deduped, but each post
|
|
gets its own row. Replace the unique sha256 index with a plain lookup index plus
|
|
two partial uniques — (post_id, sha256) for real posts and (sha256) for the
|
|
NULL-post filesystem case (still one row per file there).
|
|
|
|
Existing data has ≤1 row per sha (the old global unique), so the new partial
|
|
uniques can't be violated on upgrade — no data backfill needed here. The bare-post
|
|
shells themselves are removed by the separate prune-empty-posts cleanup tool.
|
|
"""
|
|
from typing import Sequence, Union
|
|
|
|
import sqlalchemy as sa
|
|
from alembic import op
|
|
|
|
revision: str = "0043"
|
|
down_revision: Union[str, None] = "0042"
|
|
branch_labels: Union[str, Sequence[str], None] = None
|
|
depends_on: Union[str, Sequence[str], None] = None
|
|
|
|
|
|
def upgrade() -> None:
|
|
# Drop the global unique index; recreate it as a plain (non-unique) lookup
|
|
# index so sha-based reads keep their index (matches the model's index=True).
|
|
op.drop_index("ix_post_attachment_sha256", table_name="post_attachment")
|
|
op.create_index(
|
|
"ix_post_attachment_sha256", "post_attachment", ["sha256"],
|
|
)
|
|
op.create_index(
|
|
"uq_post_attachment_post_sha", "post_attachment",
|
|
["post_id", "sha256"], unique=True,
|
|
postgresql_where=sa.text("post_id IS NOT NULL"),
|
|
)
|
|
op.create_index(
|
|
"uq_post_attachment_null_post_sha", "post_attachment",
|
|
["sha256"], unique=True,
|
|
postgresql_where=sa.text("post_id IS NULL"),
|
|
)
|
|
|
|
|
|
def downgrade() -> None:
|
|
op.drop_index(
|
|
"uq_post_attachment_null_post_sha", table_name="post_attachment"
|
|
)
|
|
op.drop_index(
|
|
"uq_post_attachment_post_sha", table_name="post_attachment"
|
|
)
|
|
op.drop_index("ix_post_attachment_sha256", table_name="post_attachment")
|
|
op.create_index(
|
|
"ix_post_attachment_sha256", "post_attachment", ["sha256"],
|
|
unique=True,
|
|
)
|