899ad5e879
Images were findable only by the filename someone typed. `media_assets` gives them a title, caption, alt text, a category on the shared tree and tags, with a weighted tsvector so they are searchable now (migration y7e8f9a0b1c2). The embedding column is filled from the caption today. A vision-capable model can fill it from the image itself later without another migration — and because `embedding_model` stamps every vector, a text-embedded caption and a vision-embedded image stay distinguishable instead of being silently mixed in one index. Adding "media" to the embeddable kinds is all the retry task, the full regeneration and the health report needed. `media_tag_links.tag_id` carries no ORM-level foreign key: `question_tags` is created by raw DDL rather than a model, so the constraint lives in the migration where the table actually exists. Tests: 113 backend green. Co-Authored-By: Claude Opus 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_01PpfzbZ1QTLMeVYxM2kyq8m
57 lines
2.2 KiB
Python
57 lines
2.2 KiB
Python
"""Media as a searchable corpus, ready for a vision embedder.
|
|
|
|
Images are currently found only by the filename someone typed. This gives them a
|
|
caption, alt text and tags to search on now, and an embedding column that a
|
|
vision-capable model can fill later without another migration — `embedding_model`
|
|
records which model produced each vector, so a text-embedded caption and a
|
|
vision-embedded image are distinguishable rather than silently mixed.
|
|
|
|
Revision ID: y7e8f9a0b1c2
|
|
Revises: x6d7e8f9a0b1
|
|
"""
|
|
from alembic import op
|
|
|
|
revision = "y7e8f9a0b1c2"
|
|
down_revision = "x6d7e8f9a0b1"
|
|
branch_labels = None
|
|
depends_on = None
|
|
|
|
|
|
def upgrade():
|
|
op.execute("""
|
|
CREATE TABLE IF NOT EXISTS media_assets (
|
|
id SERIAL PRIMARY KEY,
|
|
path VARCHAR(500) UNIQUE NOT NULL,
|
|
title VARCHAR(300),
|
|
caption TEXT,
|
|
alt_text TEXT,
|
|
kind VARCHAR(20) NOT NULL DEFAULT 'image',
|
|
category_id INTEGER REFERENCES question_categories(id) ON DELETE SET NULL,
|
|
user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
|
|
embedding vector(1024),
|
|
embedding_model VARCHAR(120),
|
|
embedded_at TIMESTAMP,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
search_vector tsvector GENERATED ALWAYS AS (
|
|
setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
|
|
setweight(to_tsvector('english', coalesce(caption, '')), 'B') ||
|
|
setweight(to_tsvector('english', coalesce(alt_text, '')), 'C')
|
|
) STORED
|
|
)
|
|
""")
|
|
op.execute("CREATE INDEX IF NOT EXISTS ix_media_search ON media_assets USING GIN (search_vector)")
|
|
op.execute("CREATE INDEX IF NOT EXISTS ix_media_model ON media_assets(embedding_model)")
|
|
op.execute("""
|
|
CREATE TABLE IF NOT EXISTS media_tag_links (
|
|
id SERIAL PRIMARY KEY,
|
|
media_id INTEGER NOT NULL REFERENCES media_assets(id) ON DELETE CASCADE,
|
|
tag_id INTEGER NOT NULL REFERENCES question_tags(id) ON DELETE CASCADE,
|
|
CONSTRAINT uq_media_tag UNIQUE (media_id, tag_id)
|
|
)
|
|
""")
|
|
op.execute("CREATE INDEX IF NOT EXISTS ix_media_tag_media ON media_tag_links(media_id)")
|
|
|
|
|
|
def downgrade():
|
|
op.execute("DROP TABLE IF EXISTS media_tag_links")
|
|
op.execute("DROP TABLE IF EXISTS media_assets")
|