bvandeusenandClaude Opus 5
aa71cbbdbf
CI / lint (push) Successful in 3s
CI / extension-version (push) Successful in 4s
CI / frontend-build (push) Successful in 22s
CI / backend-lint-and-test (push) Successful in 30s
CI / integration (push) Successful in 3m41s
Build images / sign-extension (push) Successful in 3s
Build images / build-agent (push) Successful in 7s
Build images / build-web (push) Successful in 6s
Build images / build-ml (push) Successful in 27s
Integration caught it: 36 tests failing with NoResultFound, all on
_system_tag(db, "banner") and its siblings. 0075 seeds three hygiene
system tags — wip, banner, editor screenshot — and the first version of
the baseline carried only the two settings singletons.
This is the same defect class the baseline's own docstring warns about,
which I then walked into anyway. The reason is worth recording: my scan
for data statements used a regex requiring INSERT to sit immediately
after the opening quote, so it saw
op.execute("INSERT INTO ml_settings (id) VALUES (1)")
and missed 0075, which builds the statement through sa.text() across
several lines with bound parameters. The narrow pattern found two of
three seeds and reported itself complete.
The wider scan — grep for insert/bulk_insert across every revision in
725bf15 — turns up six data-writing migrations, and they separate
mechanically:
INSERT ... VALUES (literals) = SEED. Product data. Carry it.
0002 import_settings, 0003 ml_settings, 0075 system tags
INSERT ... SELECT ... FROM tbl = BACKFILL. Derives from existing
rows, inserts nothing on an empty database, correctly omitted.
0034 artist_visit, 0040 and 0047 series_chapter
That rule is now in the docstring, because the next person collapsing a
chain needs the rule more than they need the answer.
0075's adopt-before-insert guard is kept as WHERE NOT EXISTS. It cannot
fire on the empty database this file runs against — it existed because an
operator might already have hand-tagged `wip` — but it makes the
statement re-runnable for free.
Worth stating plainly: baseline.yml passed on the version without these
rows, and would pass again. It compares schema, and a baseline missing
every seed still produces a byte-identical schema. The integration suite
is what caught this, which is the argument for the first-run check that
#3271 should carry.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_017QHszn9H8VBvx5Ke8x1hvw
837 lines
59 KiB
Python
837 lines
59 KiB
Python
"""The whole schema, in one migration.
|
|
|
|
This replaces alembic revisions 0001..0089 — the entire build-out of the
|
|
project, 89 files and ~6,000 lines that a new installation used to replay in
|
|
order to arrive at a schema this file creates in one pass. Nothing about the
|
|
resulting database changes; what goes away is the requirement that a stranger
|
|
re-run our development history to get it.
|
|
|
|
## Why the revision id is 0089
|
|
|
|
`revision = "0089"` and `down_revision = None` are both deliberate, and the
|
|
combination is the entire migration strategy for existing installations.
|
|
|
|
An already-deployed database has `alembic_version = '0089'`, because it ran the
|
|
real 0089. This file claims that same id, so alembic reads the version table,
|
|
sees head already reached, and does nothing at all. No stamp is needed — which
|
|
matters because `alembic stamp` writes a version string without validating
|
|
anything about the schema it is writing it against, and a stamp that is wrong
|
|
is indistinguishable from one that is right until the next migration fails.
|
|
|
|
An empty database has no version row, so alembic runs this file and then
|
|
records `0089`. Both paths converge on the same schema and the same version,
|
|
and neither requires anyone to assert anything by hand.
|
|
|
|
The next migration written after this one is `0090`, exactly as it would have
|
|
been. The numbering is continuous across the collapse on purpose.
|
|
|
|
## What was added to the generated output, and why
|
|
|
|
`alembic revision --autogenerate` produced almost all of this from the models,
|
|
which is only true because #3275 first made the models actually describe the
|
|
schema. Before that reconciliation the generator silently omitted eleven
|
|
indexes and three uniqueness guarantees, and an earlier attempt at this squash
|
|
had to be reverted for exactly that reason.
|
|
|
|
Four things still had to be added by hand, because they are not in the models:
|
|
|
|
1. **`CREATE EXTENSION vector`** (from 0001) and **`tsm_system_rows`** (0004).
|
|
Extensions are database objects, not table metadata, so no model can carry
|
|
them. `IF NOT EXISTS` because a re-run must not fail.
|
|
|
|
2. **Three seed inserts** — the two settings singletons (0002, 0003) and the
|
|
three hygiene system tags (0075). Some migrations did not only build schema;
|
|
they inserted rows the product needs in order to function, and nothing in
|
|
the application ever creates them. Every consumer reads them with
|
|
`scalar_one()`, which RAISES `NoResultFound` on an empty result rather than
|
|
returning None, so their absence is a crash and not a degradation.
|
|
|
|
Distinguishing these from the other data statements in the chain is the
|
|
whole trick, and the rule turns out to be mechanical:
|
|
|
|
* `INSERT ... VALUES (...)` with literal values is a SEED. It creates
|
|
something the product ships. It must be carried.
|
|
* `INSERT ... SELECT ... FROM <table>` is a BACKFILL. It derives rows
|
|
from rows that already exist, so on an empty database it inserts
|
|
nothing and carrying it would be pointless. 0034 (artist_visit), 0040
|
|
and 0047 (series_chapter) are all of this shape and are correctly
|
|
absent here.
|
|
|
|
This category is invisible to every automated check this project has:
|
|
`baseline.yml` compares SCHEMA, and a baseline missing all three seeds still
|
|
produces a byte-identical schema and a perfectly green diff. What caught the
|
|
system tags was the integration suite — 36 tests failing on
|
|
`NoResultFound` — after a first version of this file shipped with only the
|
|
two settings rows. A first-run check against the real application is the
|
|
only thing that finds this class of defect.
|
|
|
|
3. **The `pgvector` import.** Autogenerate emits qualified
|
|
`pgvector.sqlalchemy.vector.VECTOR(...)` references without importing the
|
|
package, so the file it writes cannot execute — `NameError: name 'pgvector'
|
|
is not defined`, observed on run 4988.
|
|
|
|
The other data statements in the old chain were deliberately NOT carried over.
|
|
0023's `DELETE FROM tag WHERE kind IN (...)`, and 0047's `series_page` /
|
|
`series_chapter` deletes, are historical cleanups that operate on rows an empty
|
|
database does not have.
|
|
|
|
## Downgrade
|
|
|
|
There is none. A baseline's downgrade would be "drop the entire schema", which
|
|
is not a migration but a data-loss event wearing one as a disguise. Restore
|
|
from a backup instead — that is what backup_run exists for.
|
|
|
|
Revision ID: 0089
|
|
Revises:
|
|
Create Date: 2026-09-01
|
|
|
|
"""
|
|
from typing import Sequence, Union
|
|
|
|
from alembic import op
|
|
import sqlalchemy as sa
|
|
import pgvector.sqlalchemy.vector
|
|
from sqlalchemy.dialects import postgresql
|
|
|
|
revision: str = "0089"
|
|
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:
|
|
# Extensions first: image_record.siglip_embedding is a vector column and
|
|
# cannot be created before the type exists. From 0001 and 0004.
|
|
op.execute("CREATE EXTENSION IF NOT EXISTS vector")
|
|
op.execute("CREATE EXTENSION IF NOT EXISTS tsm_system_rows")
|
|
|
|
op.create_table('app_setting',
|
|
sa.Column('key', sa.String(length=64), nullable=False),
|
|
sa.Column('value', sa.Text(), nullable=False),
|
|
sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.PrimaryKeyConstraint('key', name=op.f('pk_app_setting'))
|
|
)
|
|
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(), server_default='false', nullable=False),
|
|
sa.Column('auto_check', sa.Boolean(), server_default='true', nullable=False),
|
|
sa.Column('check_interval_seconds', sa.Integer(), nullable=True),
|
|
sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.PrimaryKeyConstraint('id', name=op.f('pk_artist')),
|
|
sa.UniqueConstraint('slug', name=op.f('uq_artist_slug'))
|
|
)
|
|
op.create_table('backup_run',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('kind', sa.String(length=16), nullable=False),
|
|
sa.Column('status', sa.String(length=16), server_default='pending', nullable=False),
|
|
sa.Column('tag', sa.String(length=64), nullable=True),
|
|
sa.Column('triggered_by', sa.String(length=32), nullable=False),
|
|
sa.Column('started_at', sa.DateTime(timezone=True), nullable=False),
|
|
sa.Column('finished_at', sa.DateTime(timezone=True), nullable=True),
|
|
sa.Column('sql_path', sa.Text(), nullable=True),
|
|
sa.Column('tar_path', sa.Text(), nullable=True),
|
|
sa.Column('size_bytes', sa.BigInteger(), nullable=True),
|
|
sa.Column('error', sa.Text(), nullable=True),
|
|
sa.Column('manifest', sa.JSON(), server_default='{}', nullable=False),
|
|
sa.Column('restored_from_id', sa.Integer(), nullable=True),
|
|
sa.ForeignKeyConstraint(['restored_from_id'], ['backup_run.id'], name=op.f('fk_backup_run_restored_from_id_backup_run'), ondelete='SET NULL'),
|
|
sa.PrimaryKeyConstraint('id', name=op.f('pk_backup_run'))
|
|
)
|
|
op.create_index(op.f('ix_backup_run_finished_at'), 'backup_run', ['finished_at'], unique=False)
|
|
op.create_index('ix_backup_run_kind_started', 'backup_run', ['kind', sa.literal_column('started_at DESC')], unique=False)
|
|
op.create_index(op.f('ix_backup_run_restored_from_id'), 'backup_run', ['restored_from_id'], unique=False)
|
|
op.create_index(op.f('ix_backup_run_started_at'), 'backup_run', ['started_at'], unique=False)
|
|
op.create_index('ix_backup_run_status_finished', 'backup_run', ['status', sa.literal_column('finished_at DESC')], unique=False)
|
|
op.create_index(op.f('ix_backup_run_tag'), 'backup_run', ['tag'], unique=False)
|
|
op.create_index('ix_backup_run_tag_partial', 'backup_run', ['tag'], unique=False, postgresql_where=sa.text('tag IS NOT NULL'))
|
|
op.create_table('credential',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('platform', sa.String(length=64), nullable=False),
|
|
sa.Column('credential_type', sa.String(length=32), nullable=False),
|
|
sa.Column('encrypted_blob', sa.LargeBinary(), nullable=False),
|
|
sa.Column('captured_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.Column('expires_at', sa.DateTime(timezone=True), nullable=True),
|
|
sa.Column('last_verified', sa.DateTime(timezone=True), nullable=True),
|
|
sa.PrimaryKeyConstraint('id', name=op.f('pk_credential')),
|
|
sa.UniqueConstraint('platform', name=op.f('uq_credential_platform'))
|
|
)
|
|
op.create_table('head_auto_apply_run',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('dry_run', sa.Boolean(), server_default='false', nullable=False),
|
|
sa.Column('params', postgresql.JSONB(astext_type=sa.Text()), nullable=False),
|
|
sa.Column('status', sa.String(length=16), server_default='running', nullable=False),
|
|
sa.Column('started_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.Column('finished_at', sa.DateTime(timezone=True), nullable=True),
|
|
sa.Column('n_applied', sa.Integer(), nullable=True),
|
|
sa.Column('report', postgresql.JSONB(astext_type=sa.Text()), nullable=True),
|
|
sa.Column('error', sa.Text(), nullable=True),
|
|
sa.Column('last_progress_at', sa.DateTime(timezone=True), nullable=True),
|
|
sa.PrimaryKeyConstraint('id', name=op.f('pk_head_auto_apply_run'))
|
|
)
|
|
op.create_index(op.f('ix_head_auto_apply_run_status'), 'head_auto_apply_run', ['status'], unique=False)
|
|
op.create_table('head_training_run',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('params', postgresql.JSONB(astext_type=sa.Text()), nullable=False),
|
|
sa.Column('status', sa.String(length=16), server_default='running', nullable=False),
|
|
sa.Column('started_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.Column('finished_at', sa.DateTime(timezone=True), nullable=True),
|
|
sa.Column('n_trained', sa.Integer(), nullable=True),
|
|
sa.Column('n_skipped', sa.Integer(), nullable=True),
|
|
sa.Column('error', sa.Text(), nullable=True),
|
|
sa.Column('last_progress_at', sa.DateTime(timezone=True), nullable=True),
|
|
sa.PrimaryKeyConstraint('id', name=op.f('pk_head_training_run'))
|
|
)
|
|
op.create_index(op.f('ix_head_training_run_status'), 'head_training_run', ['status'], unique=False)
|
|
op.create_table('import_batch',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('triggered_by', sa.String(length=32), nullable=False),
|
|
sa.Column('source_path', sa.Text(), nullable=False),
|
|
sa.Column('scan_mode', sa.String(length=16), nullable=False),
|
|
sa.Column('started_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.Column('finished_at', sa.DateTime(timezone=True), nullable=True),
|
|
sa.Column('total_files', sa.Integer(), server_default='0', nullable=False),
|
|
sa.Column('imported', sa.Integer(), server_default='0', nullable=False),
|
|
sa.Column('skipped', sa.Integer(), server_default='0', nullable=False),
|
|
sa.Column('failed', sa.Integer(), server_default='0', nullable=False),
|
|
sa.Column('attachments', sa.Integer(), server_default='0', nullable=False),
|
|
sa.Column('refreshed', sa.Integer(), server_default='0', nullable=False),
|
|
sa.Column('status', sa.String(length=16), server_default='running', nullable=False),
|
|
sa.PrimaryKeyConstraint('id', name=op.f('pk_import_batch'))
|
|
)
|
|
op.create_index(op.f('ix_import_batch_status'), 'import_batch', ['status'], unique=False)
|
|
op.create_table('import_settings',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('import_scan_path', sa.Text(), server_default='/import', nullable=False),
|
|
sa.Column('min_width', sa.Integer(), server_default='0', nullable=False),
|
|
sa.Column('min_height', sa.Integer(), server_default='0', nullable=False),
|
|
sa.Column('skip_transparent', sa.Boolean(), server_default='false', nullable=False),
|
|
sa.Column('transparency_threshold', sa.Float(), server_default='0.9', nullable=False),
|
|
sa.Column('skip_single_color', sa.Boolean(), server_default='false', nullable=False),
|
|
sa.Column('single_color_threshold', sa.Float(), server_default='0.95', nullable=False),
|
|
sa.Column('single_color_tolerance', sa.Integer(), server_default='30', nullable=False),
|
|
sa.Column('phash_threshold', sa.Integer(), server_default='10', nullable=False),
|
|
sa.Column('download_rate_limit_seconds', sa.Float(), server_default='3', nullable=False),
|
|
sa.Column('download_validate_files', sa.Boolean(), server_default='true', nullable=False),
|
|
sa.Column('download_schedule_default_seconds', sa.Integer(), server_default='28800', nullable=False),
|
|
sa.Column('download_event_retention_days', sa.Integer(), server_default='90', nullable=False),
|
|
sa.Column('download_failure_warning_threshold', sa.Integer(), server_default='5', nullable=False),
|
|
sa.Column('backup_db_nightly_enabled', sa.Boolean(), server_default='false', nullable=False),
|
|
sa.Column('backup_db_nightly_hour_utc', sa.Integer(), server_default='3', nullable=False),
|
|
sa.Column('backup_db_keep_last_n', sa.Integer(), server_default='14', nullable=False),
|
|
sa.Column('backup_images_keep_last_n', sa.Integer(), server_default='3', nullable=False),
|
|
sa.Column('series_suggest_enabled', sa.Boolean(), server_default='true', nullable=False),
|
|
sa.Column('series_suggest_threshold', sa.Float(), server_default='0.5', nullable=False),
|
|
sa.Column('extdl_mega_enabled', sa.Boolean(), server_default='true', nullable=False),
|
|
sa.Column('extdl_gdrive_enabled', sa.Boolean(), server_default='true', nullable=False),
|
|
sa.Column('extdl_mediafire_enabled', sa.Boolean(), server_default='true', nullable=False),
|
|
sa.Column('extdl_dropbox_enabled', sa.Boolean(), server_default='true', nullable=False),
|
|
sa.Column('extdl_pixeldrain_enabled', sa.Boolean(), server_default='true', nullable=False),
|
|
sa.Column('translation_enabled', sa.Boolean(), server_default='false', nullable=False),
|
|
sa.Column('interpreter_base_url', sa.Text(), server_default='', nullable=False),
|
|
sa.Column('translation_target_lang', sa.Text(), server_default='en', nullable=False),
|
|
sa.Column('translation_min_confidence', sa.Float(), server_default=sa.text('0.9'), nullable=False),
|
|
sa.Column('wip_title_tagging_enabled', sa.Boolean(), server_default='true', nullable=False),
|
|
sa.Column('wip_soft_title_tagging_enabled', sa.Boolean(), server_default='false', nullable=False),
|
|
sa.CheckConstraint('id = 1', name=op.f('ck_import_settings_singleton')),
|
|
sa.PrimaryKeyConstraint('id', name=op.f('pk_import_settings'))
|
|
)
|
|
op.create_table('library_audit_run',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('rule', sa.String(length=32), nullable=False),
|
|
sa.Column('params', postgresql.JSONB(astext_type=sa.Text()), nullable=False),
|
|
sa.Column('status', sa.String(length=16), server_default='running', nullable=False),
|
|
sa.Column('started_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.Column('finished_at', sa.DateTime(timezone=True), nullable=True),
|
|
sa.Column('scanned_count', sa.Integer(), server_default='0', nullable=False),
|
|
sa.Column('matched_count', sa.Integer(), server_default='0', nullable=False),
|
|
sa.Column('matched_ids', postgresql.JSONB(astext_type=sa.Text()), server_default=sa.text("'[]'::jsonb"), nullable=False),
|
|
sa.Column('error', sa.Text(), nullable=True),
|
|
sa.Column('resume_after_id', sa.Integer(), server_default='0', nullable=False),
|
|
sa.Column('last_progress_at', sa.DateTime(timezone=True), nullable=True),
|
|
sa.PrimaryKeyConstraint('id', name=op.f('pk_library_audit_run'))
|
|
)
|
|
op.create_index(op.f('ix_library_audit_run_rule'), 'library_audit_run', ['rule'], unique=False)
|
|
op.create_index(op.f('ix_library_audit_run_status'), 'library_audit_run', ['status'], unique=False)
|
|
op.create_table('ml_settings',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('cpu_embed_enabled', sa.Boolean(), server_default='true', nullable=False),
|
|
sa.Column('video_frame_interval_seconds', sa.Float(), server_default='4', nullable=False),
|
|
sa.Column('video_max_frames', sa.Integer(), server_default='64', nullable=False),
|
|
sa.Column('head_min_positives', sa.Integer(), server_default='8', nullable=False),
|
|
sa.Column('head_auto_apply_precision', sa.Float(), server_default='0.97', nullable=False),
|
|
sa.Column('head_auto_apply_enabled', sa.Boolean(), server_default='true', nullable=False),
|
|
sa.Column('head_auto_apply_min_positives', sa.Integer(), server_default='30', nullable=False),
|
|
sa.Column('ccip_match_threshold', sa.Float(), server_default='0.85', nullable=False),
|
|
sa.Column('ccip_auto_apply_enabled', sa.Boolean(), server_default='true', nullable=False),
|
|
sa.Column('ccip_auto_apply_threshold', sa.Float(), server_default='0.92', nullable=False),
|
|
sa.Column('presentation_auto_apply_enabled', sa.Boolean(), server_default='true', nullable=False),
|
|
sa.Column('presentation_auto_apply_threshold', sa.Float(), server_default=sa.text('0.90'), nullable=False),
|
|
sa.Column('presentation_conflict_threshold', sa.Float(), server_default=sa.text('0.50'), nullable=False),
|
|
sa.Column('process_auto_apply_enabled', sa.Boolean(), server_default='false', nullable=False),
|
|
sa.Column('process_auto_apply_threshold', sa.Float(), server_default='0.90', nullable=False),
|
|
sa.Column('process_conflict_threshold', sa.Float(), server_default='0.50', nullable=False),
|
|
sa.Column('embedder_model_version', sa.String(length=128), server_default='siglip2-so400m-patch16-512', nullable=False),
|
|
sa.Column('embedder_model_name', sa.String(length=128), server_default='google/siglip2-so400m-patch16-512', nullable=False),
|
|
sa.Column('detector_person_enabled', sa.Boolean(), server_default='true', nullable=False),
|
|
sa.Column('detector_person_weights', sa.String(length=512), server_default='yolo11n.pt', nullable=False),
|
|
sa.Column('detector_person_conf', sa.Float(), server_default=sa.text('0.35'), nullable=False),
|
|
sa.Column('detector_anatomy_enabled', sa.Boolean(), server_default='true', nullable=False),
|
|
sa.Column('detector_anatomy_weights', sa.String(length=512), server_default='https://github.com/aperveyev/booru_yolo/raw/main/models/yolov11m_aa22.pt', nullable=False),
|
|
sa.Column('detector_anatomy_conf', sa.Float(), server_default=sa.text('0.30'), nullable=False),
|
|
sa.Column('detector_panel_enabled', sa.Boolean(), server_default='true', nullable=False),
|
|
sa.Column('detector_panel_weights', sa.String(length=512), server_default='mosesb/best-comic-panel-detection::best.pt', nullable=False),
|
|
sa.Column('detector_panel_conf', sa.Float(), server_default=sa.text('0.30'), nullable=False),
|
|
sa.Column('detector_max_figures', sa.Integer(), server_default='8', nullable=False),
|
|
sa.Column('detector_max_components', sa.Integer(), server_default='8', nullable=False),
|
|
sa.Column('detector_max_panels', sa.Integer(), server_default='8', nullable=False),
|
|
sa.Column('detector_max_regions', sa.Integer(), server_default='128', nullable=False),
|
|
sa.Column('detector_dedupe_iou', sa.Float(), server_default=sa.text('0.85'), nullable=False),
|
|
sa.Column('ccip_ref_signature', sa.String(length=128), nullable=True),
|
|
sa.Column('ccip_prototype_cap', sa.Integer(), server_default='64', nullable=False),
|
|
sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.CheckConstraint('id = 1', name=op.f('ck_ml_settings_singleton')),
|
|
sa.PrimaryKeyConstraint('id', name=op.f('pk_ml_settings'))
|
|
)
|
|
op.create_table('tag',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('name', sa.String(length=255), nullable=False),
|
|
sa.Column('kind', sa.Enum('artist', 'character', 'fandom', 'general', 'series', 'archive', 'post', name='tag_kind'), server_default='general', nullable=False),
|
|
sa.Column('fandom_id', sa.Integer(), nullable=True),
|
|
sa.Column('is_system', sa.Boolean(), server_default=sa.text('false'), nullable=False),
|
|
sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.CheckConstraint("(fandom_id IS NULL) OR (kind = 'character')", name=op.f('ck_tag_fandom_requires_character')),
|
|
sa.ForeignKeyConstraint(['fandom_id'], ['tag.id'], name=op.f('fk_tag_fandom_id_tag'), ondelete='SET NULL'),
|
|
sa.PrimaryKeyConstraint('id', name=op.f('pk_tag'))
|
|
)
|
|
op.create_index(op.f('ix_tag_fandom_id'), 'tag', ['fandom_id'], unique=False)
|
|
op.create_index('uq_tag_name_kind_fandom', 'tag', ['name', 'kind', sa.literal_column('COALESCE(fandom_id, 0)')], unique=True)
|
|
op.create_table('task_run',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('celery_task_id', sa.String(length=64), nullable=False),
|
|
sa.Column('queue', sa.String(length=32), nullable=False),
|
|
sa.Column('task_name', sa.String(length=128), nullable=False),
|
|
sa.Column('target_id', sa.Integer(), nullable=True),
|
|
sa.Column('started_at', sa.DateTime(timezone=True), nullable=False),
|
|
sa.Column('finished_at', sa.DateTime(timezone=True), nullable=True),
|
|
sa.Column('duration_ms', sa.Integer(), nullable=True),
|
|
sa.Column('status', sa.String(length=16), server_default='running', nullable=False),
|
|
sa.Column('error_type', sa.String(length=128), nullable=True),
|
|
sa.Column('error_message', sa.Text(), nullable=True),
|
|
sa.Column('retry_count', sa.Integer(), nullable=True),
|
|
sa.Column('worker_hostname', sa.String(length=128), nullable=True),
|
|
sa.Column('args_summary', sa.String(length=255), nullable=True),
|
|
sa.PrimaryKeyConstraint('id', name=op.f('pk_task_run'))
|
|
)
|
|
op.create_index(op.f('ix_task_run_celery_task_id'), 'task_run', ['celery_task_id'], unique=False)
|
|
op.create_index(op.f('ix_task_run_finished_at'), 'task_run', ['finished_at'], unique=False)
|
|
op.create_index('ix_task_run_name_started', 'task_run', ['task_name', sa.literal_column('started_at DESC')], unique=False)
|
|
op.create_index('ix_task_run_queue_started', 'task_run', ['queue', sa.literal_column('started_at DESC')], unique=False)
|
|
op.create_index(op.f('ix_task_run_started_at'), 'task_run', ['started_at'], unique=False)
|
|
op.create_index('ix_task_run_status_started', 'task_run', ['status', sa.literal_column('started_at DESC')], unique=False)
|
|
op.create_table('artist_visit',
|
|
sa.Column('artist_id', sa.Integer(), nullable=False),
|
|
sa.Column('last_viewed_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.ForeignKeyConstraint(['artist_id'], ['artist.id'], name=op.f('fk_artist_visit_artist_id_artist'), ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('artist_id', name=op.f('pk_artist_visit'))
|
|
)
|
|
op.create_table('ccip_prototype_state',
|
|
sa.Column('tag_id', sa.Integer(), nullable=False),
|
|
sa.Column('fingerprint', sa.String(length=64), nullable=False),
|
|
sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.ForeignKeyConstraint(['tag_id'], ['tag.id'], name=op.f('fk_ccip_prototype_state_tag_id_tag'), ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('tag_id', name=op.f('pk_ccip_prototype_state'))
|
|
)
|
|
op.create_table('head_metric',
|
|
sa.Column('tag_id', sa.Integer(), nullable=False),
|
|
sa.Column('n_misfires', sa.Integer(), server_default='0', nullable=False),
|
|
sa.Column('n_underfires', sa.Integer(), server_default='0', nullable=False),
|
|
sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.ForeignKeyConstraint(['tag_id'], ['tag.id'], name=op.f('fk_head_metric_tag_id_tag'), ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('tag_id', name=op.f('pk_head_metric'))
|
|
)
|
|
op.create_table('head_metrics_snapshot',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('tag_id', sa.Integer(), nullable=True),
|
|
sa.Column('name', sa.String(length=255), nullable=False),
|
|
sa.Column('snapshot_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.Column('n_auto_applied', sa.Integer(), server_default='0', nullable=False),
|
|
sa.Column('n_misfires', sa.Integer(), server_default='0', nullable=False),
|
|
sa.Column('n_underfires', sa.Integer(), server_default='0', nullable=False),
|
|
sa.Column('ap', sa.Float(), nullable=True),
|
|
sa.Column('precision_cv', sa.Float(), nullable=True),
|
|
sa.Column('recall', sa.Float(), nullable=True),
|
|
sa.Column('n_pos', sa.Integer(), nullable=True),
|
|
sa.ForeignKeyConstraint(['tag_id'], ['tag.id'], name=op.f('fk_head_metrics_snapshot_tag_id_tag'), ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('id', name=op.f('pk_head_metrics_snapshot'))
|
|
)
|
|
op.create_index(op.f('ix_head_metrics_snapshot_snapshot_at'), 'head_metrics_snapshot', ['snapshot_at'], unique=False)
|
|
op.create_index(op.f('ix_head_metrics_snapshot_tag_id'), 'head_metrics_snapshot', ['tag_id'], unique=False)
|
|
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(), server_default='true', nullable=False),
|
|
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('error_type', sa.String(length=32), nullable=True),
|
|
sa.Column('check_interval_override', sa.Integer(), nullable=True),
|
|
sa.Column('consecutive_failures', sa.Integer(), server_default='0', nullable=False),
|
|
sa.Column('backfill_runs_remaining', sa.Integer(), server_default='0', nullable=False),
|
|
sa.ForeignKeyConstraint(['artist_id'], ['artist.id'], name=op.f('fk_source_artist_id_artist'), ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('id', name=op.f('pk_source')),
|
|
sa.UniqueConstraint('artist_id', 'platform', 'url', name='uq_source_artist_platform_url')
|
|
)
|
|
op.create_index(op.f('ix_source_artist_id'), 'source', ['artist_id'], unique=False)
|
|
op.create_index(op.f('ix_source_error_type'), 'source', ['error_type'], unique=False)
|
|
op.create_table('tag_alias',
|
|
sa.Column('alias_string', sa.String(length=255), nullable=False),
|
|
sa.Column('alias_category', sa.String(length=32), nullable=False),
|
|
sa.Column('canonical_tag_id', sa.Integer(), nullable=False),
|
|
sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.ForeignKeyConstraint(['canonical_tag_id'], ['tag.id'], name=op.f('fk_tag_alias_canonical_tag_id_tag'), ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('alias_string', 'alias_category', name=op.f('pk_tag_alias'))
|
|
)
|
|
op.create_index('ix_tag_alias_canonical', 'tag_alias', ['canonical_tag_id'], unique=False)
|
|
op.create_table('tag_head',
|
|
sa.Column('tag_id', sa.Integer(), nullable=False),
|
|
sa.Column('embedding_version', sa.String(length=128), nullable=False),
|
|
sa.Column('weights', pgvector.sqlalchemy.vector.VECTOR(dim=1152), nullable=False),
|
|
sa.Column('bias', sa.Float(), nullable=False),
|
|
sa.Column('suggest_threshold', sa.Float(), nullable=False),
|
|
sa.Column('auto_apply_threshold', sa.Float(), nullable=True),
|
|
sa.Column('n_pos', sa.Integer(), nullable=False),
|
|
sa.Column('n_neg', sa.Integer(), nullable=False),
|
|
sa.Column('ap', sa.Float(), nullable=False),
|
|
sa.Column('precision_cv', sa.Float(), nullable=False),
|
|
sa.Column('recall', sa.Float(), nullable=False),
|
|
sa.Column('trained_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.Column('train_fingerprint', sa.String(length=128), nullable=True),
|
|
sa.Column('metrics', postgresql.JSONB(astext_type=sa.Text()), nullable=True),
|
|
sa.ForeignKeyConstraint(['tag_id'], ['tag.id'], name=op.f('fk_tag_head_tag_id_tag'), ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('tag_id', name=op.f('pk_tag_head'))
|
|
)
|
|
op.create_table('patreon_failed_media',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('source_id', sa.Integer(), nullable=False),
|
|
sa.Column('filehash', sa.String(length=128), nullable=False),
|
|
sa.Column('attempts', sa.Integer(), server_default='1', nullable=False),
|
|
sa.Column('last_error', sa.Text(), nullable=True),
|
|
sa.Column('first_failed_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.Column('last_failed_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.ForeignKeyConstraint(['source_id'], ['source.id'], name=op.f('fk_patreon_failed_media_source_id_source'), ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('id', name=op.f('pk_patreon_failed_media')),
|
|
sa.UniqueConstraint('source_id', 'filehash', name='uq_patreon_failed_media_source_id')
|
|
)
|
|
op.create_index(op.f('ix_patreon_failed_media_source_id'), 'patreon_failed_media', ['source_id'], unique=False)
|
|
op.create_table('patreon_seen_media',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('source_id', sa.Integer(), nullable=False),
|
|
sa.Column('filehash', sa.String(length=128), nullable=False),
|
|
sa.Column('post_id', sa.String(length=64), nullable=True),
|
|
sa.Column('seen_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.ForeignKeyConstraint(['source_id'], ['source.id'], name=op.f('fk_patreon_seen_media_source_id_source'), ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('id', name=op.f('pk_patreon_seen_media')),
|
|
sa.UniqueConstraint('source_id', 'filehash', name='uq_patreon_seen_media_source_id')
|
|
)
|
|
op.create_index(op.f('ix_patreon_seen_media_source_id'), 'patreon_seen_media', ['source_id'], unique=False)
|
|
op.create_table('pixiv_failed_media',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('source_id', sa.Integer(), nullable=False),
|
|
sa.Column('filehash', sa.String(length=128), nullable=False),
|
|
sa.Column('attempts', sa.Integer(), server_default='1', nullable=False),
|
|
sa.Column('last_error', sa.Text(), nullable=True),
|
|
sa.Column('first_failed_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.Column('last_failed_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.ForeignKeyConstraint(['source_id'], ['source.id'], name=op.f('fk_pixiv_failed_media_source_id_source'), ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('id', name=op.f('pk_pixiv_failed_media')),
|
|
sa.UniqueConstraint('source_id', 'filehash', name='uq_pixiv_failed_media_source_id')
|
|
)
|
|
op.create_index(op.f('ix_pixiv_failed_media_source_id'), 'pixiv_failed_media', ['source_id'], unique=False)
|
|
op.create_table('pixiv_seen_media',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('source_id', sa.Integer(), nullable=False),
|
|
sa.Column('filehash', sa.String(length=128), nullable=False),
|
|
sa.Column('post_id', sa.String(length=64), nullable=True),
|
|
sa.Column('seen_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.ForeignKeyConstraint(['source_id'], ['source.id'], name=op.f('fk_pixiv_seen_media_source_id_source'), ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('id', name=op.f('pk_pixiv_seen_media')),
|
|
sa.UniqueConstraint('source_id', 'filehash', name='uq_pixiv_seen_media_source_id')
|
|
)
|
|
op.create_index(op.f('ix_pixiv_seen_media_source_id'), 'pixiv_seen_media', ['source_id'], unique=False)
|
|
op.create_table('post',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('source_id', sa.Integer(), nullable=True),
|
|
sa.Column('artist_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('description', sa.Text(), nullable=True),
|
|
sa.Column('attachment_count', sa.Integer(), nullable=True),
|
|
sa.Column('post_title_translated', sa.Text(), nullable=True),
|
|
sa.Column('description_translated', sa.Text(), nullable=True),
|
|
sa.Column('translated_source_lang', sa.String(length=8), nullable=True),
|
|
sa.Column('translation_engine_version', sa.String(length=128), nullable=True),
|
|
sa.Column('translated_at', sa.DateTime(timezone=True), nullable=True),
|
|
sa.Column('translation_override', sa.String(length=16), server_default='auto', nullable=False),
|
|
sa.Column('downloaded_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.CheckConstraint("translation_override IN ('auto', 'force', 'original')", name=op.f('ck_post_translation_override')),
|
|
sa.ForeignKeyConstraint(['artist_id'], ['artist.id'], name=op.f('fk_post_artist_id_artist'), ondelete='CASCADE'),
|
|
sa.ForeignKeyConstraint(['source_id'], ['source.id'], name=op.f('fk_post_source_id_source'), ondelete='SET NULL'),
|
|
sa.PrimaryKeyConstraint('id', name=op.f('pk_post')),
|
|
sa.UniqueConstraint('source_id', 'external_post_id', name='uq_post_source_external_id')
|
|
)
|
|
op.create_index(op.f('ix_post_artist_id'), 'post', ['artist_id'], unique=False)
|
|
op.create_index(op.f('ix_post_source_id'), 'post', ['source_id'], unique=False)
|
|
op.create_index('uq_post_artist_external_id_null_source', 'post', ['artist_id', 'external_post_id'], unique=True, postgresql_where=sa.text('source_id IS NULL'))
|
|
op.create_table('subscribestar_failed_media',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('source_id', sa.Integer(), nullable=False),
|
|
sa.Column('filehash', sa.String(length=128), nullable=False),
|
|
sa.Column('attempts', sa.Integer(), server_default='1', nullable=False),
|
|
sa.Column('last_error', sa.Text(), nullable=True),
|
|
sa.Column('first_failed_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.Column('last_failed_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.ForeignKeyConstraint(['source_id'], ['source.id'], name=op.f('fk_subscribestar_failed_media_source_id_source'), ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('id', name=op.f('pk_subscribestar_failed_media')),
|
|
sa.UniqueConstraint('source_id', 'filehash', name='uq_subscribestar_failed_media_source_id')
|
|
)
|
|
op.create_index(op.f('ix_subscribestar_failed_media_source_id'), 'subscribestar_failed_media', ['source_id'], unique=False)
|
|
op.create_table('subscribestar_seen_media',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('source_id', sa.Integer(), nullable=False),
|
|
sa.Column('filehash', sa.String(length=128), nullable=False),
|
|
sa.Column('post_id', sa.String(length=64), nullable=True),
|
|
sa.Column('seen_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.ForeignKeyConstraint(['source_id'], ['source.id'], name=op.f('fk_subscribestar_seen_media_source_id_source'), ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('id', name=op.f('pk_subscribestar_seen_media')),
|
|
sa.UniqueConstraint('source_id', 'filehash', name='uq_subscribestar_seen_media_source_id')
|
|
)
|
|
op.create_index(op.f('ix_subscribestar_seen_media_source_id'), 'subscribestar_seen_media', ['source_id'], unique=False)
|
|
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), server_default=sa.text('now()'), nullable=False),
|
|
sa.Column('finished_at', sa.DateTime(timezone=True), nullable=True),
|
|
sa.Column('bytes_downloaded', sa.BigInteger(), server_default='0', nullable=False),
|
|
sa.Column('files_count', sa.Integer(), server_default='0', nullable=False),
|
|
sa.Column('error', sa.Text(), nullable=True),
|
|
sa.Column('metadata', postgresql.JSONB(astext_type=sa.Text()), server_default=sa.text("'{}'::jsonb"), nullable=False),
|
|
sa.ForeignKeyConstraint(['post_id'], ['post.id'], name=op.f('fk_download_event_post_id_post'), ondelete='SET NULL'),
|
|
sa.ForeignKeyConstraint(['source_id'], ['source.id'], name=op.f('fk_download_event_source_id_source'), ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('id', name=op.f('pk_download_event'))
|
|
)
|
|
op.create_index(op.f('ix_download_event_post_id'), 'download_event', ['post_id'], unique=False)
|
|
op.create_index(op.f('ix_download_event_source_id'), 'download_event', ['source_id'], unique=False)
|
|
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('duration_seconds', sa.Float(), nullable=True),
|
|
sa.Column('integrity_status', sa.String(length=24), server_default='unknown', nullable=False),
|
|
sa.Column('thumbnail_path', sa.Text(), nullable=True),
|
|
sa.Column('source_url', sa.Text(), nullable=True),
|
|
sa.Column('source_filehash', sa.String(length=32), 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('artist_id', sa.Integer(), nullable=True),
|
|
sa.Column('siglip_embedding', pgvector.sqlalchemy.vector.VECTOR(dim=1152), nullable=True),
|
|
sa.Column('siglip_model_version', sa.String(length=128), nullable=True),
|
|
sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.Column('effective_date', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.Column('earliest_post_date', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.ForeignKeyConstraint(['artist_id'], ['artist.id'], name='fk_image_record_artist_id', ondelete='SET NULL'),
|
|
sa.ForeignKeyConstraint(['primary_post_id'], ['post.id'], name=op.f('fk_image_record_primary_post_id_post'), ondelete='SET NULL'),
|
|
sa.PrimaryKeyConstraint('id', name=op.f('pk_image_record')),
|
|
sa.UniqueConstraint('path', name=op.f('uq_image_record_path')),
|
|
sa.UniqueConstraint('sha256', name='uq_image_record_sha256')
|
|
)
|
|
op.create_index(op.f('ix_image_record_artist_id'), 'image_record', ['artist_id'], unique=False)
|
|
op.create_index('ix_image_record_earliest_post_date', 'image_record', [sa.literal_column('earliest_post_date DESC'), sa.literal_column('id DESC')], unique=False)
|
|
op.create_index('ix_image_record_effective_date', 'image_record', [sa.literal_column('effective_date DESC'), sa.literal_column('id DESC')], unique=False)
|
|
op.create_index(op.f('ix_image_record_integrity_status'), 'image_record', ['integrity_status'], unique=False)
|
|
op.create_index(op.f('ix_image_record_phash'), 'image_record', ['phash'], unique=False)
|
|
op.create_index(op.f('ix_image_record_primary_post_id'), 'image_record', ['primary_post_id'], unique=False)
|
|
op.create_index('ix_image_record_siglip_hnsw', 'image_record', ['siglip_embedding'], unique=False, postgresql_using='hnsw', postgresql_ops={'siglip_embedding': 'vector_cosine_ops'})
|
|
op.create_index(op.f('ix_image_record_source_filehash'), 'image_record', ['source_filehash'], unique=False)
|
|
op.create_table('post_attachment',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('post_id', sa.Integer(), nullable=True),
|
|
sa.Column('artist_id', sa.Integer(), nullable=True),
|
|
sa.Column('sha256', sa.String(length=64), nullable=False),
|
|
sa.Column('path', sa.Text(), nullable=False),
|
|
sa.Column('original_filename', sa.Text(), nullable=False),
|
|
sa.Column('ext', sa.String(length=32), nullable=False),
|
|
sa.Column('mime', sa.String(length=128), nullable=True),
|
|
sa.Column('size_bytes', sa.BigInteger(), nullable=False),
|
|
sa.Column('captured_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.ForeignKeyConstraint(['artist_id'], ['artist.id'], name=op.f('fk_post_attachment_artist_id_artist'), ondelete='SET NULL'),
|
|
sa.ForeignKeyConstraint(['post_id'], ['post.id'], name=op.f('fk_post_attachment_post_id_post'), ondelete='SET NULL'),
|
|
sa.PrimaryKeyConstraint('id', name=op.f('pk_post_attachment'))
|
|
)
|
|
op.create_index(op.f('ix_post_attachment_artist_id'), 'post_attachment', ['artist_id'], unique=False)
|
|
op.create_index(op.f('ix_post_attachment_post_id'), 'post_attachment', ['post_id'], unique=False)
|
|
op.create_index(op.f('ix_post_attachment_sha256'), 'post_attachment', ['sha256'], unique=False)
|
|
op.create_index('uq_post_attachment_null_post_sha', 'post_attachment', ['sha256'], unique=True, postgresql_where=sa.text('post_id IS NULL'))
|
|
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_table('series_suggestion',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('post_id', sa.Integer(), nullable=False),
|
|
sa.Column('series_tag_id', sa.Integer(), nullable=False),
|
|
sa.Column('score', sa.Float(), nullable=False),
|
|
sa.Column('signals', sa.JSON(), nullable=True),
|
|
sa.Column('status', sa.String(length=16), server_default='pending', nullable=False),
|
|
sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.ForeignKeyConstraint(['post_id'], ['post.id'], name=op.f('fk_series_suggestion_post_id_post'), ondelete='CASCADE'),
|
|
sa.ForeignKeyConstraint(['series_tag_id'], ['tag.id'], name=op.f('fk_series_suggestion_series_tag_id_tag'), ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('id', name=op.f('pk_series_suggestion')),
|
|
sa.UniqueConstraint('post_id', 'series_tag_id', name='uq_series_suggestion_post_series')
|
|
)
|
|
op.create_index(op.f('ix_series_suggestion_post_id'), 'series_suggestion', ['post_id'], unique=False)
|
|
op.create_index(op.f('ix_series_suggestion_series_tag_id'), 'series_suggestion', ['series_tag_id'], unique=False)
|
|
op.create_index(op.f('ix_series_suggestion_status'), 'series_suggestion', ['status'], unique=False)
|
|
op.create_table('external_link',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('post_id', sa.Integer(), nullable=False),
|
|
sa.Column('artist_id', sa.Integer(), nullable=True),
|
|
sa.Column('host', sa.String(length=16), nullable=False),
|
|
sa.Column('url', sa.Text(), nullable=False),
|
|
sa.Column('label', sa.Text(), nullable=True),
|
|
sa.Column('status', sa.String(length=16), server_default='pending', nullable=False),
|
|
sa.Column('attempts', sa.Integer(), server_default=sa.text('0'), nullable=False),
|
|
sa.Column('last_error', sa.Text(), nullable=True),
|
|
sa.Column('attachment_id', sa.Integer(), nullable=True),
|
|
sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.Column('completed_at', sa.DateTime(timezone=True), nullable=True),
|
|
sa.Column('duration_seconds', sa.Float(), nullable=True),
|
|
sa.CheckConstraint("host IN ('mega', 'gdrive', 'mediafire', 'dropbox', 'pixeldrain')", name=op.f('ck_external_link_host')),
|
|
sa.CheckConstraint("status IN ('pending', 'downloading', 'downloaded', 'failed', 'skipped', 'dead')", name=op.f('ck_external_link_status')),
|
|
sa.ForeignKeyConstraint(['artist_id'], ['artist.id'], name=op.f('fk_external_link_artist_id_artist'), ondelete='SET NULL'),
|
|
sa.ForeignKeyConstraint(['attachment_id'], ['post_attachment.id'], name=op.f('fk_external_link_attachment_id_post_attachment'), ondelete='SET NULL'),
|
|
sa.ForeignKeyConstraint(['post_id'], ['post.id'], name=op.f('fk_external_link_post_id_post'), ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('id', name=op.f('pk_external_link'))
|
|
)
|
|
op.create_index(op.f('ix_external_link_artist_id'), 'external_link', ['artist_id'], unique=False)
|
|
op.create_index('ix_external_link_attachment_id', 'external_link', ['attachment_id'], unique=False)
|
|
op.create_index('ix_external_link_status', 'external_link', ['status'], unique=False)
|
|
op.create_index('uq_external_link_post_url', 'external_link', ['post_id', 'url'], unique=True)
|
|
op.create_table('gpu_job',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('image_record_id', sa.Integer(), nullable=False),
|
|
sa.Column('task', sa.String(length=32), nullable=False),
|
|
sa.Column('status', sa.String(length=16), server_default='pending', nullable=False),
|
|
sa.Column('lease_token', sa.String(length=64), nullable=True),
|
|
sa.Column('leased_at', sa.DateTime(timezone=True), nullable=True),
|
|
sa.Column('lease_expires_at', sa.DateTime(timezone=True), nullable=True),
|
|
sa.Column('attempts', sa.Integer(), server_default='0', nullable=False),
|
|
sa.Column('error', sa.Text(), nullable=True),
|
|
sa.Column('triage_status', sa.String(length=16), nullable=True),
|
|
sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.ForeignKeyConstraint(['image_record_id'], ['image_record.id'], name=op.f('fk_gpu_job_image_record_id_image_record'), ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('id', name=op.f('pk_gpu_job'))
|
|
)
|
|
op.create_index(op.f('ix_gpu_job_image_record_id'), 'gpu_job', ['image_record_id'], unique=False)
|
|
op.create_index('ix_gpu_job_leased_expires', 'gpu_job', ['lease_expires_at'], unique=False, postgresql_where=sa.text("status = 'leased'"))
|
|
op.create_index('ix_gpu_job_pending', 'gpu_job', ['id'], unique=False, postgresql_where=sa.text("status = 'pending'"))
|
|
op.create_index(op.f('ix_gpu_job_status'), 'gpu_job', ['status'], unique=False)
|
|
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=True),
|
|
sa.Column('from_attachment_id', sa.Integer(), nullable=True),
|
|
sa.Column('captured_metadata', sa.JSON(), nullable=True),
|
|
sa.Column('captured_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.ForeignKeyConstraint(['from_attachment_id'], ['post_attachment.id'], name='fk_image_provenance_from_attachment', ondelete='SET NULL'),
|
|
sa.ForeignKeyConstraint(['image_record_id'], ['image_record.id'], name=op.f('fk_image_provenance_image_record_id_image_record'), ondelete='CASCADE'),
|
|
sa.ForeignKeyConstraint(['post_id'], ['post.id'], name=op.f('fk_image_provenance_post_id_post'), ondelete='CASCADE'),
|
|
sa.ForeignKeyConstraint(['source_id'], ['source.id'], name=op.f('fk_image_provenance_source_id_source'), ondelete='SET NULL'),
|
|
sa.PrimaryKeyConstraint('id', name=op.f('pk_image_provenance')),
|
|
sa.UniqueConstraint('image_record_id', 'post_id', name='uq_image_provenance_image_post')
|
|
)
|
|
op.create_index(op.f('ix_image_provenance_from_attachment_id'), 'image_provenance', ['from_attachment_id'], unique=False)
|
|
op.create_index(op.f('ix_image_provenance_image_record_id'), 'image_provenance', ['image_record_id'], unique=False)
|
|
op.create_index(op.f('ix_image_provenance_post_id'), 'image_provenance', ['post_id'], unique=False)
|
|
op.create_index(op.f('ix_image_provenance_source_id'), 'image_provenance', ['source_id'], unique=False)
|
|
op.create_table('image_region',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('image_record_id', sa.Integer(), nullable=False),
|
|
sa.Column('kind', sa.String(length=16), nullable=False),
|
|
sa.Column('frame_time', sa.Float(), nullable=True),
|
|
sa.Column('rx', sa.Float(), nullable=False),
|
|
sa.Column('ry', sa.Float(), nullable=False),
|
|
sa.Column('rw', sa.Float(), nullable=False),
|
|
sa.Column('rh', sa.Float(), nullable=False),
|
|
sa.Column('score', sa.Float(), nullable=True),
|
|
sa.Column('detector_version', sa.String(length=64), nullable=True),
|
|
sa.Column('crop_version', sa.String(length=64), nullable=True),
|
|
sa.Column('embedding_version', sa.String(length=128), nullable=True),
|
|
sa.Column('ccip_embedding', pgvector.sqlalchemy.vector.VECTOR(dim=768), nullable=True),
|
|
sa.Column('siglip_embedding', pgvector.sqlalchemy.vector.VECTOR(dim=1152), nullable=True),
|
|
sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.ForeignKeyConstraint(['image_record_id'], ['image_record.id'], name=op.f('fk_image_region_image_record_id_image_record'), ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('id', name=op.f('pk_image_region'))
|
|
)
|
|
op.create_index(op.f('ix_image_region_image_record_id'), 'image_region', ['image_record_id'], unique=False)
|
|
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), server_default='manual', nullable=False),
|
|
sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.ForeignKeyConstraint(['image_record_id'], ['image_record.id'], name=op.f('fk_image_tag_image_record_id_image_record'), ondelete='CASCADE'),
|
|
sa.ForeignKeyConstraint(['tag_id'], ['tag.id'], name=op.f('fk_image_tag_tag_id_tag'), ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('image_record_id', 'tag_id', name=op.f('pk_image_tag'))
|
|
)
|
|
op.create_index('ix_image_tag_tag_id', 'image_tag', ['tag_id'], unique=False)
|
|
op.create_table('import_task',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('batch_id', sa.Integer(), nullable=False),
|
|
sa.Column('source_path', sa.Text(), nullable=False),
|
|
sa.Column('task_type', sa.String(length=16), nullable=False),
|
|
sa.Column('status', sa.String(length=16), server_default='pending', nullable=False),
|
|
sa.Column('recovery_count', sa.Integer(), server_default='0', nullable=False),
|
|
sa.Column('refetched', sa.Boolean(), server_default='false', nullable=False),
|
|
sa.Column('result_image_id', sa.Integer(), nullable=True),
|
|
sa.Column('error', sa.Text(), nullable=True),
|
|
sa.Column('size_bytes', sa.BigInteger(), nullable=True),
|
|
sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.Column('started_at', sa.DateTime(timezone=True), nullable=True),
|
|
sa.Column('finished_at', sa.DateTime(timezone=True), nullable=True),
|
|
sa.ForeignKeyConstraint(['batch_id'], ['import_batch.id'], name=op.f('fk_import_task_batch_id_import_batch'), ondelete='CASCADE'),
|
|
sa.ForeignKeyConstraint(['result_image_id'], ['image_record.id'], name=op.f('fk_import_task_result_image_id_image_record'), ondelete='SET NULL'),
|
|
sa.PrimaryKeyConstraint('id', name=op.f('pk_import_task'))
|
|
)
|
|
op.create_index(op.f('ix_import_task_batch_id'), 'import_task', ['batch_id'], unique=False)
|
|
op.create_index('ix_import_task_created_at_desc', 'import_task', [sa.literal_column('created_at DESC')], unique=False)
|
|
op.create_index('ix_import_task_result_image_id', 'import_task', ['result_image_id'], unique=False)
|
|
op.create_index(op.f('ix_import_task_status'), 'import_task', ['status'], unique=False)
|
|
op.create_table('presentation_review',
|
|
sa.Column('image_record_id', sa.Integer(), nullable=False),
|
|
sa.Column('tag_id', sa.Integer(), nullable=False),
|
|
sa.Column('conflict_tag_id', sa.Integer(), nullable=True),
|
|
sa.Column('conflict_score', sa.Float(), nullable=False),
|
|
sa.Column('mode', sa.String(length=16), server_default='chrome', nullable=False),
|
|
sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.Column('resolved_at', sa.DateTime(timezone=True), nullable=True),
|
|
sa.ForeignKeyConstraint(['conflict_tag_id'], ['tag.id'], name=op.f('fk_presentation_review_conflict_tag_id_tag'), ondelete='SET NULL'),
|
|
sa.ForeignKeyConstraint(['image_record_id'], ['image_record.id'], name=op.f('fk_presentation_review_image_record_id_image_record'), ondelete='CASCADE'),
|
|
sa.ForeignKeyConstraint(['tag_id'], ['tag.id'], name=op.f('fk_presentation_review_tag_id_tag'), ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('image_record_id', 'tag_id', name=op.f('pk_presentation_review'))
|
|
)
|
|
op.create_index('ix_presentation_review_conflict_tag_id', 'presentation_review', ['conflict_tag_id'], unique=False)
|
|
op.create_index('ix_presentation_review_resolved_at', 'presentation_review', ['resolved_at'], unique=False)
|
|
op.create_index('ix_presentation_review_tag_id', 'presentation_review', ['tag_id'], unique=False)
|
|
op.create_table('series_page',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('series_tag_id', sa.Integer(), nullable=False),
|
|
sa.Column('image_id', sa.Integer(), nullable=False),
|
|
sa.Column('status', sa.String(length=16), server_default='placed', nullable=False),
|
|
sa.Column('page_number', sa.Integer(), nullable=True),
|
|
sa.Column('stated_page', sa.Integer(), nullable=True),
|
|
sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.ForeignKeyConstraint(['image_id'], ['image_record.id'], name=op.f('fk_series_page_image_id_image_record'), ondelete='CASCADE'),
|
|
sa.ForeignKeyConstraint(['series_tag_id'], ['tag.id'], name=op.f('fk_series_page_series_tag_id_tag'), ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('id', name=op.f('pk_series_page')),
|
|
sa.UniqueConstraint('image_id', name='uq_series_page_image')
|
|
)
|
|
op.create_index(op.f('ix_series_page_series_tag_id'), 'series_page', ['series_tag_id'], unique=False)
|
|
op.create_table('tag_positive_confirmation',
|
|
sa.Column('image_record_id', sa.Integer(), nullable=False),
|
|
sa.Column('tag_id', sa.Integer(), nullable=False),
|
|
sa.Column('confirmed_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.ForeignKeyConstraint(['image_record_id'], ['image_record.id'], name=op.f('fk_tag_positive_confirmation_image_record_id_image_record'), ondelete='CASCADE'),
|
|
sa.ForeignKeyConstraint(['tag_id'], ['tag.id'], name=op.f('fk_tag_positive_confirmation_tag_id_tag'), ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('image_record_id', 'tag_id', name=op.f('pk_tag_positive_confirmation'))
|
|
)
|
|
op.create_index(op.f('ix_tag_positive_confirmation_tag_id'), 'tag_positive_confirmation', ['tag_id'], unique=False)
|
|
op.create_table('tag_suggestion_rejection',
|
|
sa.Column('image_record_id', sa.Integer(), nullable=False),
|
|
sa.Column('tag_id', sa.Integer(), nullable=False),
|
|
sa.Column('rejected_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.ForeignKeyConstraint(['image_record_id'], ['image_record.id'], name='fk_tsr_image_record_id_image_record', ondelete='CASCADE'),
|
|
sa.ForeignKeyConstraint(['tag_id'], ['tag.id'], name='fk_tsr_tag_id_tag', ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('image_record_id', 'tag_id', name=op.f('pk_tag_suggestion_rejection'))
|
|
)
|
|
op.create_index('ix_tag_suggestion_rejection_tag', 'tag_suggestion_rejection', ['tag_id'], unique=False)
|
|
op.create_table('character_prototype',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('tag_id', sa.Integer(), nullable=False),
|
|
sa.Column('ccip_embedding', pgvector.sqlalchemy.vector.VECTOR(dim=768), nullable=False),
|
|
sa.Column('region_id', sa.Integer(), nullable=True),
|
|
sa.ForeignKeyConstraint(['region_id'], ['image_region.id'], name=op.f('fk_character_prototype_region_id_image_region'), ondelete='SET NULL'),
|
|
sa.ForeignKeyConstraint(['tag_id'], ['tag.id'], name=op.f('fk_character_prototype_tag_id_tag'), ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('id', name=op.f('pk_character_prototype'))
|
|
)
|
|
op.create_index(op.f('ix_character_prototype_region_id'), 'character_prototype', ['region_id'], unique=False)
|
|
op.create_index(op.f('ix_character_prototype_tag_id'), 'character_prototype', ['tag_id'], unique=False)
|
|
op.create_table('series_chapter',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('series_tag_id', sa.Integer(), nullable=False),
|
|
sa.Column('anchor_page_id', sa.Integer(), nullable=False),
|
|
sa.Column('title', sa.Text(), nullable=True),
|
|
sa.Column('stated_part', sa.Integer(), nullable=True),
|
|
sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.ForeignKeyConstraint(['anchor_page_id'], ['series_page.id'], name='fk_series_chapter_anchor_page', ondelete='CASCADE'),
|
|
sa.ForeignKeyConstraint(['series_tag_id'], ['tag.id'], name=op.f('fk_series_chapter_series_tag_id_tag'), ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('id', name=op.f('pk_series_chapter')),
|
|
sa.UniqueConstraint('anchor_page_id', name='uq_series_chapter_anchor_page')
|
|
)
|
|
op.create_index(op.f('ix_series_chapter_series_tag_id'), 'series_chapter', ['series_tag_id'], unique=False)
|
|
|
|
# The singleton settings rows. NOT schema — see the note above; the app
|
|
# reads these with scalar_one() and never creates them, so a fresh
|
|
# install without these two rows raises NoResultFound on first use.
|
|
# From 0002 and 0003.
|
|
op.execute("INSERT INTO import_settings (id) VALUES (1)")
|
|
op.execute("INSERT INTO ml_settings (id) VALUES (1)")
|
|
|
|
# The three hygiene system tags, from 0075. These are PRODUCT data, not
|
|
# operator configuration — 0075's own docstring says so: "the fix keys on
|
|
# SYSTEM tags the product ships". The presentation and process auto-apply
|
|
# sweeps look them up with scalar_one(), so without these rows those
|
|
# features raise NoResultFound rather than degrading.
|
|
#
|
|
# 0075 adopted an existing same-name general tag before inserting, because
|
|
# an operator might already have tagged `wip` by hand. That cannot happen
|
|
# on the empty database this file runs against, but the guard is kept: it
|
|
# costs nothing and makes the statement safe to re-run.
|
|
for _name in ("wip", "banner", "editor screenshot"):
|
|
op.execute(
|
|
sa.text(
|
|
"INSERT INTO tag (name, kind, is_system) "
|
|
"SELECT :name, 'general', true WHERE NOT EXISTS ("
|
|
" SELECT 1 FROM tag WHERE lower(name) = lower(:name)"
|
|
")"
|
|
).bindparams(name=_name)
|
|
)
|
|
|
|
|
|
def downgrade() -> None:
|
|
"""Deliberately not implemented.
|
|
|
|
Downgrading a baseline means dropping every table in the database. That is
|
|
not a migration, and offering it as one invites someone to run it. Restore
|
|
from a backup instead.
|
|
"""
|
|
raise NotImplementedError(
|
|
"0089 is the baseline; there is nothing below it. Restore from a backup."
|
|
)
|