Docs: how to migrate a database to TEI (text↔region links via embed_annotation_ids --from-graphs)

[lanterno]

Migrating an existing database to TEI (with text↔region links)

This explains how to take any older Archetype/DigiPal database (where transcriptions/translations are stored as legacy data-dpt HTML) and migrate it to TEI P5 XML, including the text↔region links that connect a phrase in the text to its annotation on the image.

It documents the management commands and the one-shot runbook script. Everything is reversible (the original data-dpt is retained in manuscripts_imagetext.content_dpt_legacy).

Background — where the links live

The text↔region link can exist in two places depending on how old the dump is:

1. In the text — ImageText.content (data-dpt) already has data-graph-id="N" on its spans. Newer exports look like this.
2. Only in the graphs — annotations_graph.annotation.properties.elementid holds the legacy DigiPal selector tuple, e.g. [["", "person"], ["type", "name"], ["@text", "walt-olifard"]], and the text has no data-graph-id yet. Older dumps look like this.

There is no separate TextAnnotation table in the current schema — the legacy link survives as the per-graph elementid payload.

Which case am I in?

Run against the target DB (adjust DB name/user):

-- > 0 here  => links are already in the text (case 1)
SELECT count(*) FROM manuscripts_imagetext WHERE content LIKE '%data-graph-id%';

-- > 0 here  => links are in the graphs and NOT yet in the text (case 2; needs the embed step)
SELECT count(*) FROM annotations_graph WHERE annotation->'properties' ? 'elementid';

Option A — one-shot offline runbook (recommended for production backups)

The agreed production workflow is to migrate a backup, not the live DB. From api/, with the compose postgres container up:

scripts/migrate_backup_to_tei.sh  prod_backup.sql  migrated_backup.sql

It loads the backup into a throwaway scratch DB, brings the schema up to date, runs the TEI conversion + elementid re-encode, runs the integrity/validity gates, and dumps a verified migrated_backup.sql. If a gate fails it stops before writing output. See api/docs/tei-backup-migration.md for details.

⚠️ Case 2 dumps (links only in the graphs) need the embed step 2b below first — the runbook script otherwise assumes the data-dpt already carries data-graph-id. Run the manual steps in Option B, or add the embed step before the TEI conversion.

Option B — manual, step by step

All commands run inside the api container, e.g. docker compose run --rm api python manage.py <cmd>. Each migration command supports --dry-run (default) and --apply; preview first, then apply.

# 1. Bring the schema to the current codebase
#    (adds manuscripts_imagetext.content_dpt_legacy, the reversible retention column)
just migrate

# 2b. CASE 2 ONLY — embed text↔region links from the graph elementid jsonb into the
#     data-dpt BEFORE converting to TEI. Skip for case 1 (links already in the text).
python manage.py embed_annotation_ids --from-graphs --dry-run   # preview match counts
python manage.py embed_annotation_ids --from-graphs --apply     # writes data-graph-id into spans

# 3. Convert ImageText.content data-dpt -> TEI XML (data-graph-id becomes corresp).
#    Only rows that round-trip byte-for-byte are flipped; the rest are left as data-dpt
#    and reported. Originals kept in content_dpt_legacy.
python manage.py migrate_imagetext_to_tei --apply

# 4. Re-encode each TEXT graph's elementid to its reverse element link
#    (the selector data the W3C + IIIF layers need). Legacy tuple kept under
#    properties.legacy_dpt_elementid.
python manage.py reencode_graph_elementid --apply

# 5. Integrity gate — aborts if any text->region link points at a missing,
#    non-TEXT, or cross-image Graph.
python manage.py check_text_links

# 6. Validity gate — every ImageText.content must be well-formed TEI XML.
python manage.py verify_tei

# 7. Rebuild Meilisearch (search index is NOT part of a DB dump).
just sync-all-search-indexes

The --from-graphs flag

embed_annotation_ids accepts either a CSV (--csv path.csv with headers id,annotation_id,elementid) or --from-graphs, which sources the elementid selectors directly from annotations_graph.annotation.properties.elementid — no CSV export needed. The dry-run prints how many graphs matched a text span; unmatched graphs (legacy text-hint drift) are left untouched.

Reversibility

python manage.py migrate_imagetext_to_tei --reverse   # restore data-dpt from content_dpt_legacy
python manage.py reencode_graph_elementid --reverse    # restore the legacy elementid tuple
just sync-all-search-indexes                           # re-index after a rollback

The legacy column is dropped only after a retention window (ROADMAP Phase H.11).

What "good" looks like

• verify_tei → invalid: 0
• check_text_links → missing: 0, non_text: 0, cross_image: 0
• API serves TEI with corresp="#gid-N"; the frontend renders clickable data-graph-id spans that highlight the matching region on the image.