# Schema Consolidation — Issues Found (2026-03-27)

## Status: BLOCKED — needs fixes before re-run

Backup: `backups/v3_pre_consolidation_20260327_1643.db` (85MB)
DB restored to pre-consolidation state. All other session work intact.

## Blocker 1: PK Conflicts on RU Mirror Tables

RU tables use the SAME primary keys as EN tables (they're translations, not new data):
- `a2_имена_аллаха`: allah_id 1-99 = same as `names_of_allah` allah_id 1-99
- `a4_производные`: deriv_id = same as `a4_derivatives` deriv_id
- `a5_перекрёстные_ссылки`: xref_id = same as `a5_cross_refs` xref_id

**Fix options:**
1. **UPDATE existing rows** — add RU content to the EN row (e.g., add `ru_meaning` column to `names_of_allah`). Preserves PK.
2. **Offset PKs** — insert RU rows with PK + 100000 offset. Avoids conflict but breaks ID meaning.
3. **Separate lang column** — generate NEW integer PKs for RU rows, add `lang='RU'` column. Original RU PK stored in `orig_ru_id` column.

**Recommended: Option 1** for Names of Allah (same 99 names, just add RU fields). **Option 3** for derivatives/cross-refs (genuinely different data rows).

## Blocker 2: Orphaned Views

Several views reference tables that don't exist or have been renamed:
- `m1_phonetic_shifts` → references `phonetic_shifts` (doesn't exist — data is in `shift_lookup`)
- `a3_quran_refs` → is a VIEW, not a table
- `a6_country_names` → is a VIEW, not a table
- `a1_записи` → is a VIEW (data already in `entries`)
- `a1_entries` → is a VIEW

**Fix:** Drop orphaned views BEFORE dropping triggers. Current script drops triggers first, which causes ALTER TABLE to fail when it touches a table referenced by a view.

**Correct order:**
1. Save all triggers + views (SQL)
2. Drop ALL views
3. Drop ALL triggers
4. Run migration
5. Recreate views (new definitions)
6. Recreate triggers (only for surviving tables)

## Blocker 3: UNIQUE Constraints from Hardening

`harden_v4_schema.py` added UNIQUE indexes:
- `uq_entries_en_root` on `entries(en_term, root_id)`
- `uq_bitig_orig2` on `bitig_a1_entries(orig2_term, root_letters)`
- `uq_eu_lang_term` on `european_a1_entries(lang, term)`
- `uq_lat_term` on `latin_a1_entries(lat_term)`
- `uq_roots_letters` on `roots(root_letters)`

These may block RU data insertion if values collide. Need to check each before INSERT.

## Migration Script

`consolidate_v5_clean.py` — handles Phases 1-3 but needs the above fixes.
`consolidate_schema_v5.py` — original version, same issues.

## What Was Completed This Session

1. Domain-specific QUF (12 lattice layers) — 97% pass, 102K rows, 27 tables
2. Extended QUF to 130 remaining tables — 40% pass
3. 4 new AMR AI modules (jism, hisab, tarikh, istakhbarat) — all with domain QUF colours
4. Schema hardening (indexes, views, health check)
5. amr_lawh.py QUF filtering wired
6. Automated backup script created
7. Banned term "theological" removed from all code
8. 12-layer lattice architecture defined (replaces 8 academic categories)

## Next Session: Consolidation

1. Fix Blocker 1: per-table PK strategy (UPDATE for names, new PKs for derivatives)
2. Fix Blocker 2: drop views BEFORE triggers
3. Fix Blocker 3: handle UNIQUE constraints
4. Re-run consolidation
5. Update code references (amr_jism.py, uslap_quf.py, uslap_handler.py, etc.)
6. Re-run domain QUF on consolidated structure