Schema reconciliation + index hygiene, and the weekly base-image refresh #243
No Assignees
Notifications
Due Date
No due date set.
Dependencies
No dependencies set.
Reference: bvandeusen/FabledCurator#243
Reference in New Issue
Block a user
Eighteen commits. Two threads: milestone 326 step 4 (the scheduled base-image refresh) and milestone 328's schema work.
The schema work —
0088and0089The squash of the 87-revision alembic chain was attempted, reverted, and turned into something more useful: a CI job that builds a schema from the migration chain and another from the ORM models, and diffs them. Nobody had ever compared the two. They disagreed in ~130 places.
Almost all of it was the model being wrong about a database that was already right — 107 missing
server_defaults, eleven indexes and three uniqueness guarantees that existed only in a migration. The severity is not the individual gaps: it is thatalembic revision --autogeneratehad been unsafe to use on this project, because run against the old models it would have proposed dropping all of them.That is fixed.
SCHEMAS MATCH — every difference above is column ORDER alone.0088carries the only DDL the reconciliation needed: oneCREATE INDEXforix_tag_fandom_id, plus renaming six CHECK constraints whose names carried their table prefix twice (ck_ml_settings_ck_ml_settings_singletonand friends —base.py's convention applies even to an already-named CheckConstraint, and six migrations pre-prefixed the name). Catalog-only; nothing touches a row.0089comes from a second sweep, run after the diff hit zero — because a models-vs-chain match proves the two agree, not that the schema is right. Everything in0089was wrong in both:image_taghadPRIMARY KEY (image_record_id, tag_id)and no other index, leavingtag_idunindexed. That is the gallery's tag filter (tag_query.pybuildsimage_tag.c.tag_id == tid) and theON DELETE CASCADEfromtag, both scanning the largest table in the schema. Six more FKs were unindexed.ix_image_record_sha256— an exact duplicate of the indexuq_image_record_sha256already builds, on the highest-insert-rate table.CONCURRENTLYthroughout, so theimage_tagbuild does not hold anACCESS EXCLUSIVElock over every write. The cost:0089cannot run in a transaction and so is not atomic. Every statement isIF NOT EXISTS/IF EXISTS, making a re-run after a partial failure safe; the docstring carries the query for finding an INVALID index left by an interrupted build.The sweep also found clean: all 43 tables have a primary key, all 51 FKs declare an explicit
ON DELETE, and the three enum CHECKs match the code that writes them.Milestone 326 step 4 — weekly base-image refresh
A Sunday 06:00 UTC cron rebuilds the three images from
mainwithpull: true, so base-image CVE fixes land without a code change. Forgejo registersschedule:from the default branch, so a scheduled run arrives withgithub.refondev— hence a top-levelBUILD_REFoverride and a guard on every checkout that fails the run rather than publishing a channel tag from the wrong branch. Verified end-to-end on run 4934.Two corrections in the record
0088's docstring and issue #3275 both originally claimedimage_record.sha256was not unique and that duplicate rows were possible in production. That was false — read off0001line 151 without reading line 149 two lines above it. Uniqueness has held since the initial schema. Corrected in both places; the duplicate-check query put to the operator was withdrawn.ci.ymlwas also red ondevacross three commits (a ruffI001) because I was watching a dispatched workflow and not the push runs firing beside it. Filed as #3296.Verification
baseline.yml mode: models— run 5061: schemas match.ci.yml— run 5060: lint, extension-version, backend tests, frontend build, integration all green on08418d5. The integration job runsalembic upgrade head, so0088and0089both executed against a real pgvector Postgres.0089re-sweep of the resulting dump: unindexed FKs 0 of 51, duplicate indexes 0, prefix-redundant indexes 0.Scribe: #3275, #3296, #3300, #3301, milestone 328.
BUILD_REF is read through the `env` context inside `with:`, which this runner is not known to evaluate. `${{ steps.* }}` and `${{ secrets.* }}` in `with:`/`env:` are proven here; `env` is not, and run 4915's checkout log (`git checkout -B dev refs/remotes/origin/dev`) cannot tell an honoured `refs/heads/dev` from an empty value falling back to the same place — the two are indistinguishable on every path except the one that matters. If it does resolve empty, the weekly refresh checks out dev and pushes its source to :latest, which is production. Every lane stays green and the first symptom is production running code that was never merged. So each of the four jobs now asserts its own checkout before doing anything, gated on `github.event_name` — the `github` context is demonstrably evaluated in `if:`, so the guard cannot be disabled by the same uncertainty it covers. A red weekly job is an acceptable outcome; shipping dev to production is not.Milestone 328 step 1 needs a baseline generated from the models, and step 2 must not stamp the operator's live database until that baseline is proven to reproduce what the 87-revision chain produced. `alembic stamp` validates nothing, so an unproven baseline fails silently now and loudly later, on real data. There is no local Python environment and rules 10/12 point away from standing one up, so the comparison runs in CI, where a pgvector Postgres is already built from the chain on every integration run and nothing is at risk. It builds two databases and diffs their pg_dump --schema-only output: one from `alembic upgrade head` on the revisions read out of git at `chain_ref`, one from the current tree. Reading the chain from git via a worktree — rather than from the working tree — is what keeps this usable AFTER the old revisions are deleted, so it is the proof for step 1 and the pre-flight for step 2 rather than a one-shot script. Both sides use `alembic upgrade head`, never metadata.create_all, per rule 82 — and that rule's reasoning is exactly the hazard here. `create_all` emits plain CREATE TABLE and skips everything else, which is why the optional autogenerated candidate CANNOT be trusted as the answer. Three things in this schema are invisible to SQLAlchemy metadata: CREATE EXTENSION vector (0001) CREATE EXTENSION tsm_system_rows (0004) the HNSW index on image_record.siglip_embedding, raw SQL because alembic's create_index cannot express USING hnsw (...) (0036) plus any CHECK constraint or server_default a migration added without the model declaring it — 4 model files declare CheckConstraints against 6 migrations that touch them. The candidate is a starting point to hand finish; the diff is what proves nothing was missed. Results are printed to the job log rather than uploaded: ci-requirements records that this runner cannot do actions/upload-artifact@v4+, and the repo dropped the action entirely in 2026-05. Run it first with the chain still present, as a control — the diff compares the chain against itself and must come back clean. A clean diff after the squash only means something if the harness was shown to be capable of producing one beforehand. Temporary. Delete once the baseline is stamped.Run 4964 passed the control (1121 normalised lines, schemas identical) but its candidate print was silently truncated. `cat` of the ~33KB generated file stopped mid-line inside sa.Column('mime', sa.String(length=128) and the runner carried straight on to the next traced command with the step still green. The captured text was 484 lines and 29 tables, and looked entirely plausible — which is exactly what makes it dangerous: a schema definition cut in half is still syntactically suggestive, and nothing in the log says it was cut. Now emitted as base64 at a fixed 120-column width, followed by a sha256, a byte count and a base64 line count. Short lines instead of long ones, and more importantly the receiving end can PROVE it got the whole file rather than trusting that it did. Also found in that output, and the reason the candidate could never have been committed as-is: it references pgvector.sqlalchemy.vector.VECTOR(dim=1152) for head_training_run.weights and image_record.siglip_embedding, but autogenerate does not add the corresponding import. The file would die with NameError on the first run. That is the fourth item on the list of things the generator cannot be trusted with, alongside the two CREATE EXTENSIONs and the HNSW index.Milestone 328's acceptance test compared a database built by the real 0001..0087 chain against one built from the models, and found ~130 places where they disagree. This closes them. Almost all were the MODEL being wrong, so almost all of this is model edits with no DDL — the database already had these things, nothing in it changes, and no deploy is needed for this part: * 92 columns gained server_default. The models carried Python-side `default=` only, so the ORM filled the value and the column had no database default. Anything inserting outside the ORM behaved differently from production. * Eleven indexes that existed only in migrations are now declared: the three backup_run reporting indexes, the two date-ordered image_record browse indexes, import_task and presentation_review, and the three task_run history indexes. All use text() for their DESC ordering and postgresql_where for the partial one. * Two UNIQUE indexes that autogenerate silently proposed DROPPING, because neither is expressible as a UniqueConstraint: uq_tag_name_kind_fandom — an EXPRESSION index over (name, kind, COALESCE(fandom_id, 0)) uq_post_artist_external_id_null_source — PARTIAL, WHERE source_id IS NULL post.py already had a comment describing the second one. The comment was right; nothing declared it. * The two external_link enum CHECKs (host, status) — rule 36 territory, and absent from the model entirely. * Two indexes were named explicitly. A bare index=True generated ix_tag_alias_canonical_tag_id where the database has ix_tag_alias_canonical, so autogenerate proposed a drop+create of an index that was already there under another name. Same for tag_suggestion_rejection. Only ONE thing needed DDL, as 0088: tag.fandom_id is declared index=True but no migration ever created that index. Deliberately NOT here: image_record.sha256. The model says unique=True; 0001 created a plain index. Duplicates are possible today and the ORM believes otherwise. The fix depends on whether duplicates already exist — if they do, that is a dedupe decision, not a constraint — so it waits on an answer about live data. The real severity of #3275 is not the squash. It is that --autogenerate has been unsafe on this project: run against the old models it would have proposed dropping eleven indexes and two uniqueness guarantees.mode: models applies the raw autogenerated candidate, and it cannot run: sa.Column('weights', pgvector.sqlalchemy.vector.VECTOR(dim=1152), ...) NameError: name 'pgvector' is not defined Alembic emits the qualified reference without emitting the import. Observed on run 4988, which turns this from a thing I predicted by reading the candidate into a thing demonstrated by executing it. Repaired in the workflow rather than counted as a schema difference: the comparison asks whether the MODELS describe the schema, and this is a defect in the generator. The same fixup has to be applied by hand to any baseline generated this way, which is why it is item 4 on the collapsed baseline's hand-written list.Closes the residue the first reconciliation pass left, and corrects a factual error I put into the record. sha256 was NOT missing a uniqueness guarantee. I read `op.create_index("ix_image_record_sha256", ...)` at 0001 line 151 and concluded duplicates were possible, without reading line 149 two lines above it: sa.UniqueConstraint("sha256", name="uq_image_record_sha256"), Uniqueness has held since the initial schema. The database expresses it as a CONSTRAINT plus a separate non-unique lookup index; the model said `unique=True, index=True`, which is one UNIQUE index under a different name. Same guarantee, different objects — which is exactly why the two schemas did not line up. The model now declares both objects. No DDL. 0088's docstring, which repeated the claim, is corrected in place. Two real divergences, both the MODEL over-claiming: * source: uq_source_artist_platform_url (alembic 0010) was declared nowhere in the models — source.py had no __table_args__ at all — so autogenerate would have proposed DROPPING it. * head_metrics_snapshot.tag_id: model said NOT NULL, 0060 created it nullable. Left nullable; the FK already cascades. Seven constraints renamed to what the chain actually created, rather than what base.py's naming convention renders: uq_series_page_image, uq_series_chapter_anchor_page, fk_series_chapter_anchor_page, fk_image_record_artist_id, fk_image_provenance_from_attachment, and the two hand-shortened fk_tsr_* names from 0003. Float server_defaults now mirror their own migration, per column. The chain is MIXED: a plain string renders DEFAULT '0.90'::double precision, sa.text() renders DEFAULT 0.90, and the migrations used both. Seven columns take text(); the rest stay strings. Two literals also disagreed outright — process_{auto_apply,conflict}_threshold said 0.9/0.5 against the migration's 0.90/0.50. baseline.yml gains two things. A repair for a SECOND generator defect in the same class as the missing pgvector import: base.py's ck convention contains %(constraint_name)s, so it applies even to a NAMED CheckConstraint — autogenerate writes the already-rendered name into the migration and running it applies the convention again, yielding ck_ml_settings_ck_ml_settings_singleton. That is round-tripping damage, not a claim the models make, so it is undone rather than counted. And the diff now runs twice. Column ORDER differs permanently between a schema built by 87 ADD COLUMNs and one built in a single shot — the operator's database keeps chain order forever, a fresh install gets model order — so a check that failed on it could never pass. The second pass SORTS column lines within each CREATE TABLE instead of deleting them, which cannot hide a column present on one side only, or one whose type, nullability or default differs. Ordered diff is reported as information; the order-insensitive one is the verdict. Co-Authored-By: Claude Opus 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_017QHszn9H8VBvx5Ke8x1hvwRun 5026 got the models-vs-chain diff to 7 lines. Three findings, and one of them reverses an assumption I made in the previous commit. The doubled CHECK names are what the DATABASE has, not what the generator invented. base.py's convention is ck_%(table_name)s_%(constraint_name)s, which — unlike uq/fk/ix — applies even to a constraint that already has a name, so four migrations that passed an already-prefixed name got it prefixed twice: ck_import_settings_ck_import_settings_singleton ck_ml_settings_ck_ml_settings_singleton ck_post_ck_post_translation_override ck_tag_ck_tag_fandom_requires_character The workflow repair added last commit is still correct and still needed — autogenerate really does re-double a name on the round trip — but it was making the MODELS side clean against a chain that is dirty. The comment in ml_settings.py claiming its bare name "matches migration 0003" was simply false; 0003 produces the doubled form. Nothing reads a CHECK constraint by name, so this has never done harm. But it is precisely the development-era residue the collapsed baseline exists to leave behind, and a public schema should not ship it — so 0088 renames the deployed constraints and all six models now declare bare names. RENAME CONSTRAINT is catalog-only: no scan, no rewrite, no revalidation, which is why this is safe on post and tag. Guarded on pg_constraint scoped by conrelid, so it is a no-op on a database built from the models. ix_tag_fandom_id showed as a difference only because chain_ref was pinned to0a5bbe8, which predates 0088 — the comparison was measuring the models against a chain missing the migration that closes the gap. chain_ref now defaults to blank, meaning "the chain in this ref". Pin it to a commit only after the collapse, when the tree no longer carries the revisions. Co-Authored-By: Claude Opus 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_017QHszn9H8VBvx5Ke8x1hvw