Duplicate indices when a database is already indexed by the official OHDSI CDM DDL script

[nicoloesch]

Problem

OMOP_Alchemy creates its own secondary indices on OMOP CDM v5.4 tables via omop_index(), using the naming convention ix_<table>_<col1>_<col2>.... The official OHDSI project publishes its own standard PostgreSQL DDL script for the same CDM tables and columns, but under a different naming convention (idx_<table>_<col> / idx_<table>_id_<n>, e.g. idx_person_id on person(person_id)).

If a user already has a database populated and indexed via the official OHDSI script, and then runs omop-alchemy indexes enable, OMOP_Alchemy has no way to recognize that an equivalent index already exists under a different name. It creates a second, redundant index on the same columns. Separately, omop-alchemy indexes disable (used to speed up bulk vocabulary loads by dropping secondary indices first) can't safely drop such a foreign-named index either, since it has no way to restore it afterward once the load completes.

Expected behavior

• indexes enable should recognize an index that already exists under a different name but covers the same columns with the same uniqueness, and skip creating a duplicate.
• indexes disable should still be able to drop such a foreign-named index for the bulk-load speed benefit, but must be able to restore it under its original name afterward. This includes when disable and enable are run as two separate CLI invocations, not just paired within a single process.
• omop-alchemy reconcile-schema / doctor should report a foreign-named equivalent index as an informational note, not as two unrelated "missing" + "unexpected" issues, and should not treat it as schema drift.

Reproduction

1. Create a database and apply the official OHDSI CDM v5.4 PostgreSQL index DDL script (creates indices like idx_person_id, idx_condition_person_id_1, etc.).
2. Point OMOP_Alchemy at that database and run omop-alchemy indexes enable.
3. Observe a second, redundant index created on the same columns under OMOP_Alchemy's own ix_-prefixed name.