Bug: flask db upgrade fails on a fresh Postgres database (3 migration bugs)

[DevPatils]

Running the full Alembic migration chain against a genuinely fresh, empty
Postgres database (e.g. make docker-postgres + make migrate-upgrade
from scratch) fails. Found three separate, independent bugs while
bootstrapping a local dev environment — all three reproduced and confirmed
firsthand.

1. Duplicate constraint name across two tables

migrations/versions/7bf4eac76958_add_rule_id_column.py creates a unique
constraint named uq_pair on both cre_links and cre_node_links:

with op.batch_alter_table("cre_links", schema=None) as batch_op:
batch_op.create_unique_constraint("uq_pair", ["group", "cre"])

with op.batch_alter_table("cre_node_links", schema=None) as batch_op:
batch_op.create_unique_constraint("uq_pair", ["cre", "node"]) # collides

2. Two migration heads were never merged

9b1c2d3e4f50 ("make embeddings foreign keys nullable...") and
ab12cd34ef56 ("add embedding model metadata columns") both branch off
e1f2a3b4c5d6 independently — same author, ~9 days apart, likely two
parallel branches that never got rebased against each other before merge.
No merge revision was ever created. flask db upgrade heads (plural) does
apply both without erroring, but the plain flask db upgrade command fails
with "Multiple head revisions are present," and any future migration still
needs a proper merge point to build on.

3. Missing column migration

application/database/db.py defines document_metadata as a real column
on both Node and CRE (mapped via metadata_json, ~line 94 and ~119),
but no migration anywhere creates this column. Confirmed via \d cre /
\d node in psql — column genuinely absent after a full fresh migration
run. Crashes the moment any code queries it:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedColumn) column cre.document

Reproduction (confirmed firsthand, twice)
1.Fresh Postgres database (dropped/recreated, no existing schema)
2.flask db upgrade heads on unmodified main
3.Fails immediately at bug #1
4. Isolated test: fixing only bug #1 lets migrations finish (single command succeeds), but flask db heads still shows 2 heads (bug #2), and document_metadata is still missing (bug #3) — confirmed via direct Postgres inspection
5. With all three fixed: flask db heads shows exactly 1 head, document_metadata present on both tables, --upstream_sync completes and loads real data successfully

Migration bug Verified:

Migration.Bug.mp4