execute_sql trust receipt drops the join key: relationships emit on:null for simple-FK edges (from_column/to_column never surfaced)

[sandeep-agami]

Summary

The execute_sql trust receipt's relationships[] entries emit "on": null for simple-FK relationships and never surface the join columns, so the receipt shows a join edge with no verifiable join predicate.

A Relationship is authored in exactly one of two forms — the simple FK form (from_column + to_column) OR the on: SQL escape hatch — enforced by the model validator. Any relationship in the common simple form therefore has on = None. assemble_receipt copies only r.on into the receipt and never emits from_column/to_column, so the join key is dropped.

Where

packages/agami-core/src/semantic_model/runtime.py, in assemble_receipt, the relationship-append block (~L1299–1311):

receipt["relationships"].append({
    "name": f"{r.from_table}_to_{r.to_table}",
    "from_to": label,
    "cardinality": r.relationship,
    "confidence": r.confidence,
    "review_state": r.review_state,
    "origin": "fk" if r.confidence == "confirmed" else "introspect_heuristic",
    "signed_off_by": r.signed_off_by,
    "signed_off_role": r.signed_off_role,
    "signed_off_at": r.signed_off_at,
    "cross_schema": r.cross_schema,
    "on": r.on,          # <-- None for the simple (from_column/to_column) form
    # from_column / to_column are never emitted
})

The Relationship._completeness validator (packages/agami-core/src/semantic_model/models.py) enforces exactly one of (from_column + to_column) OR on:. So on: null in the receipt guarantees from_column/to_column are populated on the model — the receipt is dropping information it already has.

Repro

Any join over a simple-FK relationship, e.g.:

SELECT a.name, SUM(o.amount) AS won_revenue
FROM opportunities o JOIN accounts a ON a.id = o.account_id
WHERE o.is_won GROUP BY a.name ORDER BY won_revenue DESC LIMIT 10

receipt.relationships[0] comes back as:

{ "from_to": "opportunities → accounts", "cardinality": "many_to_one",
  "review_state": "approved", "confidence": "proposed", "on": null }

The join key (account_id = id) is known to the model but absent from the receipt. In a model whose relationships are all simple-FK (logical FKs, no declared constraints — the common case), on is null for 100% of relationships.

Impact

The trust receipt exists to be verifiable provenance — "what did this answer touch." A relationship whose on is permanently null and whose columns are never surfaced can't be verified from the receipt: you see the edge (opportunities → accounts, many_to_one, approved) but not on what key. The join predicate is trivially derivable and is exactly the thing a reviewer needs.

Fix directions

• In assemble_receipt, when the relationship uses the simple form, synthesize the predicate:
  "on": f"{from_qualified}.{from_column} = {to_qualified}.{to_column}".
• Or add from_column / to_column (schema-qualified) to the emitted relationship dict so the receipt is self-describing.
• Regression test: a join query over a simple-FK relationship yields a receipt relationship with a non-null, correct join predicate (or explicit from/to columns).

[ashwin-agami]

@sandeep-agami - As discussed, assigning to you