| name | schema-sync-agent-assisted |
| description | Four-layer schema sync procedure for the Ghost Contributors NL2SQL system after a database migration. Covers YAML contract, ChromaDB RAG chunks, NL2SQL prompt, and RAG prompt with a step-by-step repair sequence. |
Schema Sync — Agent Assisted
Context
This system has four layers that break simultaneously when the database schema changes:
| Layer | File / System | What breaks |
|---|
| YAML contract | agentic_system/schema/github_events.yaml | Contract no longer matches live DB |
| NL2SQL prompt | agentic_system/agents_core/nl2sql/prompts/system.md | Agent generates SQL with old predicates → 0 rows, no error |
| RAG prompt | agentic_system/agents_core/rag/prompts/system.md | Agent describes wrong field semantics to the user |
| ChromaDB chunks | schema_docs, qa_examples collections | RAG retrieves stale context with wrong field names |
Repair procedure
Step 1 — Introspect live schema
Query ClickHouse to see what the database actually has:
SELECT name, type FROM system.columns WHERE table = 'github_events' ORDER BY position
Use connection settings from agentic_system/config.py (host, port, user, password, database).
Step 2 — Diff against YAML contract
Load agentic_system/schema/github_events.yaml and compare column names and types.
Identify: removed columns, added columns, type changes.
Step 3 — Patch YAML contract
Update agentic_system/schema/github_events.yaml:
- Add entries for new columns with correct type and a clear post-migration description.
- Update the type field for any column whose type changed.
- Update descriptions for migration-sensitive columns (especially merge-related fields).
Step 4 — Patch agent prompts
In both system.md prompt files:
- Replace old field name references with new names.
- Replace old SQL predicates (e.g.
field = 1) with new equivalents (e.g. field IS NOT NULL).
- Update any type annotations in the schema table within the prompt.
- Update state labels (
pre-migration → post-migration).
Step 5 — Patch ChromaDB chunks
Connect to ChromaDB at http://localhost:8000 (settings from agentic_system/config.py).
For each collection (schema_docs, qa_examples):
- Get all items:
POST /api/v2/tenants/default_tenant/databases/default_database/collections/{id}/get with {"include": ["documents", "metadatas"]}
- Find items where
metadata.stale == True.
- Apply the same text replacements to the document content.
- Re-embed using the deterministic hash function (64 dimensions, same algorithm as
db/vectordb/init/seed_vectors.py).
- Upsert back:
POST .../upsert with updated documents, embeddings, and metadata (stale: false, schema_state: post_migration_synced).
Step 6 — Validate
uv run python scripts/validate_schema.py
Expected: No drift — live schema matches YAML contract exactly.
Also verify neither prompt file contains the old field predicate syntax.
Rollback
To restore the pre-sync state: revert the YAML and prompt file changes, then run make seed-vectors to restore ChromaDB chunks from source.
