AI_CONTEXT layer 6 relationship mapping for pilot datasets

Problem

The context catalog currently profiles tables in isolation — there is no cross-table relationship mapping that captures how tables join, which columns are foreign keys to other tables, or what the join graph looks like. AI agents generating multi-table queries must guess join paths or rely on column name heuristics, which frequently produces incorrect joins against tables with ambiguous naming. Without layer 6 relationship metadata, the context catalog provides no guidance on how tables relate to each other.

Context

• AI_CONTEXT includes joinable relationships with confidence and source provenance.
• Join graph quality is validated on pilot schemas with known expected joins.
• Fallback behavior is documented when relationship confidence is insufficient.

Possible Solutions

Plan

• Implement relationship inference pass and metadata serialization.
• Add support for declared relationships and confidence ranking.
• Validate against internal datasets and correct top false positives/negatives.
• Expose relationship metadata in MCP-readable context output.

Implementation Progress

• Implemented relationship_detector.py — pure-function Layer 6 that detects FK→PK join candidates from profiled table metadata
• Combines naming convention inference (*_id → target table) with profile evidence (key roles, value range containment, uniqueness) into weighted confidence scores
• RelationshipEdge frozen dataclass with to_dict() / to_json_dict() serialization and is_recommended threshold
• Wired into TableInspection (optional field) and MCP catalog() endpoint via _detect_catalog_relationships
• 25 tests covering dataclass shape, naming detection, profile evidence, confidence scoring, determinism, MCP enrichment, and cached-profile integration

Review Feedback

• cbox review — 2 rounds, all blocking issues resolved

• Review verdict: APPROVED

• [x] Review cleared