## Problem After indexing into new-prefix tables completes, the system must mark the migration as active. The indexer should also automatically drop tables for schema versions outside the `max_retained_versions` retention window. ## Proposal ### Completion detection The indexer (holding the migration lock) determines when all enabled namespaces and code scopes have been re-indexed into the new-prefix tables by comparing checkpoint state: - Query new-prefix `checkpoint` table for namespace completion - Query new-prefix `code_indexing_checkpoint` table for code scope completion - Compare against the list of enabled namespaces (from Siphon `knowledge_graph_enabled_namespaces`) When all scopes are covered: 1. Update `gkg_schema_version`: set new version to `active`, old version to `retired` 2. Log at `info`: `schema migration to v{N} complete` 3. Increment metric: `gkg_schema_migration_completed_total` ### Automatic cleanup via retention window After marking a migration complete, the indexer checks the `max_retained_versions` setting (default: 2) and drops tables for any version outside the window: ``` Example with max_retained_versions=2, after migrating to v2: v2 → active (keep) v1 → retired (keep — within window, rollback target) v0 → retired (OUTSIDE window → drop tables, mark "dropped") ``` Cleanup logic: 1. List all versions in `gkg_schema_version` ordered by version descending 2. Keep the top `max_retained_versions` entries 3. For each version outside the window with status `retired`: a. Enumerate its prefixed table names from `config/graph.sql` b. `DROP TABLE IF EXISTS <prefix><table> SYNC` for each c. Update version status to `dropped` in `gkg_schema_version` ### Safety - Only drops tables for versions with status `retired` — never `active` or `migrating` - `DROP TABLE IF EXISTS` is idempotent — safe to retry - Cleanup runs under the migration lock — no concurrent cleanup attempts - Rollback safety: within the retention window (default 2), the previous version's tables always exist ### Observability - Metric: `gkg_schema_migration_completed_total` — counter for successful migrations - Metric: `gkg_schema_cleanup_total` (labels: `version`, `result=success|failure`) — counter for table cleanup - Log: table drop operations at `info` level with version and table name ## Acceptance criteria - [ ] Migration marked as `active` when all scopes re-indexed into new tables - [ ] Previous version marked as `retired` - [ ] Indexer automatically drops tables outside `max_retained_versions` window - [ ] Only `retired` versions are eligible for cleanup (never `active`/`migrating`) - [ ] Cleanup is idempotent and safe (`DROP TABLE IF EXISTS ... SYNC`) - [ ] `max_retained_versions` setting respected (default: 2) - [ ] Integration test: full lifecycle — migration start → completion → automatic cleanup - [ ] Metrics: migration completion and cleanup counters ## Dependencies - Issue 3: Table-prefix-aware indexer - Issue 4: Table-prefix-aware web server ## Blocks - Issue 6: E2E staging validation