"""initial unified schema Revision ID: 0001 Revises: Create Date: 2026-05-13 """ from typing import Sequence, Union import sqlalchemy as sa from alembic import op from pgvector.sqlalchemy import Vector revision: str = "0001" down_revision: Union[str, None] = None branch_labels: Union[str, Sequence[str], None] = None depends_on: Union[str, Sequence[str], None] = None def upgrade() -> None: op.execute("CREATE EXTENSION IF NOT EXISTS vector") op.create_table( "artist", sa.Column("id", sa.Integer(), nullable=False), sa.Column("name", sa.String(length=255), nullable=False), sa.Column("slug", sa.String(length=255), nullable=False), sa.Column("notes", sa.Text(), nullable=True), sa.Column("is_subscription", sa.Boolean(), nullable=False, server_default=sa.false()), sa.Column("auto_check", sa.Boolean(), nullable=False, server_default=sa.true()), sa.Column("check_interval_seconds", sa.Integer(), nullable=True), sa.Column( "created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now(), ), sa.PrimaryKeyConstraint("id", name="pk_artist"), sa.UniqueConstraint("name", name="uq_artist_name"), sa.UniqueConstraint("slug", name="uq_artist_slug"), ) op.create_table( "source", sa.Column("id", sa.Integer(), nullable=False), sa.Column("artist_id", sa.Integer(), nullable=False), sa.Column("platform", sa.String(length=64), nullable=False), sa.Column("url", sa.Text(), nullable=False), sa.Column("enabled", sa.Boolean(), nullable=False, server_default=sa.true()), sa.Column("config_overrides", sa.JSON(), nullable=True), sa.Column("last_checked_at", sa.DateTime(timezone=True), nullable=True), sa.Column("last_error", sa.Text(), nullable=True), sa.Column("check_interval_override", sa.Integer(), nullable=True), sa.ForeignKeyConstraint( ["artist_id"], ["artist.id"], name="fk_source_artist_id_artist", ondelete="CASCADE" ), sa.PrimaryKeyConstraint("id", name="pk_source"), ) op.create_index("ix_source_artist_id", "source", ["artist_id"]) op.create_table( "credential", sa.Column("id", sa.Integer(), nullable=False), sa.Column("platform", sa.String(length=64), nullable=False), sa.Column("kind", sa.String(length=32), nullable=False), sa.Column("encrypted_blob", sa.LargeBinary(), nullable=False), sa.Column("status", sa.String(length=32), nullable=False, server_default="active"), sa.Column( "captured_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now(), ), sa.Column("expires_at", sa.DateTime(timezone=True), nullable=True), sa.PrimaryKeyConstraint("id", name="pk_credential"), sa.UniqueConstraint("platform", name="uq_credential_platform"), ) op.create_table( "post", sa.Column("id", sa.Integer(), nullable=False), sa.Column("source_id", sa.Integer(), nullable=False), sa.Column("external_post_id", sa.String(length=128), nullable=False), sa.Column("post_url", sa.Text(), nullable=True), sa.Column("post_title", sa.Text(), nullable=True), sa.Column("post_date", sa.DateTime(timezone=True), nullable=True), sa.Column("raw_metadata", sa.JSON(), nullable=True), sa.Column( "downloaded_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now(), ), sa.ForeignKeyConstraint( ["source_id"], ["source.id"], name="fk_post_source_id_source", ondelete="CASCADE" ), sa.PrimaryKeyConstraint("id", name="pk_post"), sa.UniqueConstraint("source_id", "external_post_id", name="uq_post_source_external_id"), ) op.create_index("ix_post_source_id", "post", ["source_id"]) op.create_table( "image_record", sa.Column("id", sa.Integer(), nullable=False), sa.Column("path", sa.Text(), nullable=False), sa.Column("sha256", sa.String(length=64), nullable=False), sa.Column("phash", sa.String(length=32), nullable=True), sa.Column("size_bytes", sa.BigInteger(), nullable=False), sa.Column("mime", sa.String(length=64), nullable=False), sa.Column("width", sa.Integer(), nullable=True), sa.Column("height", sa.Integer(), nullable=True), sa.Column("thumbnail_path", sa.Text(), nullable=True), sa.Column( "origin", sa.Enum( "downloaded", "imported_filesystem", "uploaded", name="origin_enum", ), nullable=False, ), sa.Column("primary_post_id", sa.Integer(), nullable=True), sa.Column("wd14_predictions", sa.JSON(), nullable=True), sa.Column("wd14_model_version", sa.String(length=128), nullable=True), sa.Column("siglip_embedding", Vector(1152), nullable=True), sa.Column("siglip_model_version", sa.String(length=128), nullable=True), sa.Column("centroid_scores", sa.JSON(), nullable=True), sa.Column( "created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now(), ), sa.Column( "updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now(), ), sa.ForeignKeyConstraint( ["primary_post_id"], ["post.id"], name="fk_image_record_primary_post_id_post", ondelete="SET NULL", ), sa.PrimaryKeyConstraint("id", name="pk_image_record"), sa.UniqueConstraint("path", name="uq_image_record_path"), sa.UniqueConstraint("sha256", name="uq_image_record_sha256"), ) op.create_index("ix_image_record_sha256", "image_record", ["sha256"]) op.create_index("ix_image_record_phash", "image_record", ["phash"]) op.create_index("ix_image_record_primary_post_id", "image_record", ["primary_post_id"]) op.create_table( "image_provenance", sa.Column("id", sa.Integer(), nullable=False), sa.Column("image_record_id", sa.Integer(), nullable=False), sa.Column("post_id", sa.Integer(), nullable=False), sa.Column("source_id", sa.Integer(), nullable=False), sa.Column("captured_metadata", sa.JSON(), nullable=True), sa.Column( "captured_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now(), ), sa.ForeignKeyConstraint( ["image_record_id"], ["image_record.id"], name="fk_image_provenance_image_record_id_image_record", ondelete="CASCADE", ), sa.ForeignKeyConstraint( ["post_id"], ["post.id"], name="fk_image_provenance_post_id_post", ondelete="CASCADE", ), sa.ForeignKeyConstraint( ["source_id"], ["source.id"], name="fk_image_provenance_source_id_source", ondelete="CASCADE", ), sa.PrimaryKeyConstraint("id", name="pk_image_provenance"), ) op.create_index("ix_image_provenance_image_record_id", "image_provenance", ["image_record_id"]) op.create_index("ix_image_provenance_post_id", "image_provenance", ["post_id"]) op.create_index("ix_image_provenance_source_id", "image_provenance", ["source_id"]) op.create_table( "tag", sa.Column("id", sa.Integer(), nullable=False), sa.Column("name", sa.String(length=255), nullable=False), sa.Column("namespace", sa.String(length=64), nullable=True), sa.Column( "created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now(), ), sa.PrimaryKeyConstraint("id", name="pk_tag"), sa.UniqueConstraint("name", name="uq_tag_name"), ) op.create_index("ix_tag_name", "tag", ["name"]) op.create_index("ix_tag_namespace", "tag", ["namespace"]) op.create_table( "image_tag", sa.Column("image_record_id", sa.Integer(), nullable=False), sa.Column("tag_id", sa.Integer(), nullable=False), sa.Column("source", sa.String(length=32), nullable=False, server_default="manual"), sa.Column( "created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now(), ), sa.ForeignKeyConstraint( ["image_record_id"], ["image_record.id"], name="fk_image_tag_image_record_id_image_record", ondelete="CASCADE", ), sa.ForeignKeyConstraint( ["tag_id"], ["tag.id"], name="fk_image_tag_tag_id_tag", ondelete="CASCADE" ), sa.PrimaryKeyConstraint("image_record_id", "tag_id", name="pk_image_tag"), ) op.create_table( "download_event", sa.Column("id", sa.Integer(), nullable=False), sa.Column("source_id", sa.Integer(), nullable=False), sa.Column("post_id", sa.Integer(), nullable=True), sa.Column("status", sa.String(length=32), nullable=False), sa.Column( "started_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now(), ), sa.Column("finished_at", sa.DateTime(timezone=True), nullable=True), sa.Column("bytes_downloaded", sa.BigInteger(), nullable=False, server_default="0"), sa.Column("files_count", sa.Integer(), nullable=False, server_default="0"), sa.Column("error", sa.Text(), nullable=True), sa.ForeignKeyConstraint( ["source_id"], ["source.id"], name="fk_download_event_source_id_source", ondelete="CASCADE", ), sa.ForeignKeyConstraint( ["post_id"], ["post.id"], name="fk_download_event_post_id_post", ondelete="SET NULL", ), sa.PrimaryKeyConstraint("id", name="pk_download_event"), ) op.create_index("ix_download_event_source_id", "download_event", ["source_id"]) op.create_index("ix_download_event_post_id", "download_event", ["post_id"]) def downgrade() -> None: op.drop_table("download_event") op.drop_table("image_tag") op.drop_table("tag") op.drop_table("image_provenance") op.drop_table("image_record") op.execute("DROP TYPE IF EXISTS origin_enum") op.drop_table("post") op.drop_table("credential") op.drop_table("source") op.drop_table("artist") op.execute("DROP EXTENSION IF EXISTS vector")