Example 4 - Semantic Query (Zapper)

This example completely validates your text-to-SQL architecture end-to-end utilizing the robust 2-Agent Pipeline (db.semantic.interpret → db.semantic.query).

What this validates

This example validates your full semantic runtime capability: intent routing, semantic interpretation, automatic clarifications, schema retrieval, read-only SQL compilation, parameterized execution, policy guardrails, and final text summarization.

Pipeline Architecture

Semantic Query End-to-End

Intent -> planner -> interpret -> query -> postgres -> final answer

Execution Steps

Press enter or space to select a node.You can then use the arrow keys to move the node around. Press delete to remove it and escape to cancel.

Press enter or space to select an edge. You can then press delete to remove it or escape to cancel.

Step Detail

5. db.semantic.query

SEMANTIC_QUERY

Agent-2 generates READ-ONLY SQL with named params, loops on DB guardrail errors, and applies failure memory.

Session Snapshot

• compiledSql
• params
• retryAttempt
• unsupported

Tables Touched

• ce_config (R)
• ce_semantic_mapping (R)
• ce_semantic_query_failures (R/W)

Chat + Trace

Turn 0 - User

Hi

Info

• Greeting turn; no semantic tool call expected.

Turn 0 - Assistant

intent: GREETINGstate: IDLE

Hi, how can I help you today?

{}

{
  "intent": "GREETING",
  "state": "IDLE",
  "mcp": {
    "toolChainSkipped": true
  }
}

Tables

• ce_intent_classifier (R)
• ce_response (R)
• ce_audit (W)

Info

• MCP semantic flow is intentionally skipped for greeting.
• ToolOrchestrationStep detects greeting dialogue and skips MCP planner entirely.
• Response resolved directly from ce_response.

Turn 1 - User

/sql2 show all disconnect requests where status went from 810 to 120 on march 12

Info

• Business-language transition query.
• Classifier matches SEMANTIC_QUERY -> ANALYZE.
• Planner will spin up the 2-Agent pipeline.

Turn 1 - Assistant

intent: SEMANTIC_QUERYstate: COMPLETED

Found 7 disconnect requests where transition 810 -> 120 occurred on March 12.

{}

{
  "mcp": {
    "action": "CALL_TOOL -> ANSWER",
    "toolOrder": [
      "db.semantic.interpret",
      "db.semantic.query",
      "postgres.query"
    ],
    "semantic": {
      "queryClass": "TRANSITION_REQUESTS",
      "operationSupported": true,
      "sqlGenerated": true,
      "sqlExecuted": true,
      "resultRows": 7
    }
  },
  "sql":
  select r.request_id, r.customer_name,
         r.customer_id, r.status,
         r.created_at
  from zp_disco_request r
  WHERE EXISTS ( SELECT 1
  FROM zp_disco_request_log l_from
  JOIN zp_disco_request_log l_to
    ON l_from.request_id = l_to.request_id
  WHERE l_from.request_id = r.request_id
    AND l_from.status = :from_status
    AND l_to.status = :to_status
    AND l_to.updated_at >= :start_ts
    AND l_to.updated_at < :next_day_ts )
  ORDER BY r.updated_at DESC
  LIMIT :__limit
}

Tables

• ce_intent_classifier (R)
• ce_rule (R)
• ce_mcp_planner (R)
• ce_mcp_tool (R)
• ce_semantic_mapping (R)
• ce_semantic_query_class (R)
• zp_disco_request (R)
• zp_disco_request_log (R)
• ce_response (R)
• ce_audit (W)

Info

• Agent-1 (Interpret) produced canonical filters mapping natural language to fromStatus/toStatus.
• Agent-2 (Query) generated Postgres-dialect SQL with named params using EXISTS-style pattern.
• Timestamp normalization automatically injected `CAST(:start_ts as timestamptz)` if needed.
• Execution executed flawlessly.

Turn 2 - User

Why did request ZPR1003 fail?

Info

• Failure/RCA diagnostic question.

Turn 2 - Assistant

intent: SEMANTIC_QUERYstate: COMPLETED

Request ZPR1003 failed due to a missing downstream disconnect order ID.

{}

{
  "mcp": {
    "toolCode": "db.semantic.query",
    "semantic": {
      "retrievalEntity": "RequestFailureLog",
      "astValid": true,
      "sqlCompiled": true,
      "sqlExecuted": true,
      "resultRows": 1
    }
  },
  "sql":
  select t0.request_id, t0.failure_reason,
         t0.downstream_status
  from zp_order_downstream_check t0
  WHERE t0.request_id = :p1
  LIMIT :__limit
}

Tables

• zp_order_downstream_check (R)
• ce_audit (W)

Info

• Agent-1 identified singular entity lookup.
• Agent-2 correctly mapped to zp_order_downstream_check.
• No complex JOIN required.

Turn 3 - User

Show billbank record for disconnect id ZPDISC7003.

Info

• BillingRecord entity path test.

Turn 3 - Assistant

intent: SEMANTIC_QUERYstate: COMPLETED

Found 1 matching billbank record for disconnect id ZPDISC7003. Record status is CLOSED with a pending amount of 0.00.

{}

{
  "mcp": {
    "semantic": {
      "retrievalEntity": "BillingRecord",
      "astValid": true,
      "sqlCompiled": true,
      "sqlExecuted": true,
      "resultRows": 1
    }
  },
  "sql":
  select t0.billbank_id, t0.account_id,
         t0.zp_disconnect_id, t0.record_status,
         t0.pending_bill_amount
  from zp_billbank_record t0
  WHERE t0.zp_disconnect_id = :p1
  LIMIT :__limit
}

Tables

• zp_billbank_record (R)
• ce_audit (W)

Info

• Simple entity lookup with filter on disconnectId.
• BillingRecord entity: primary table zp_billbank_record, single EQ filter.
• Agent-1 validated field perfectly against semantic allowed mappings.

Turn 4 - User

Delete from zp_disco_request where account_id='UPSA100'

Info

• Negative Guardrail / Security Test.

Turn 4 - Assistant

intent: SEMANTIC_QUERYstate: FAILED

I cannot execute that. The semantic querying system only allows read-only (SELECT) operations.

{}

{
  "mcp": {
    "toolCode": "db.semantic.query",
    "semantic": {
      "blocked": true,
      "reason": "SELECT-only guardrail"
    }
  }
}

Tables

• ce_audit (W)

Info

• DML/DDL requests instantly blocked.
• The PostgresQueryToolHandler uses SemanticSqlGuardrail constraints.
• If the LLM managed to hallucinate 'DELETE FROM', the guardrail catches it exactly here. Execution is severed immediately.

Canonical Intent + SQL

Agent-1 Canonical intent output

JSON

{
  "intent": "LIST_REQUESTS",
  "entity": "DISCONNECT_REQUEST",
  "queryClass": "TRANSITION_REQUESTS",
  "filters": [
    {
      "field": "fromStatus",
      "op": "EQ",
      "value": 810
    },
    {
      "field": "toStatus",
      "op": "EQ",
      "value": 120
    }
  ],
  "timeRange": {
    "kind": "ABSOLUTE",
    "value": "2026-03-12",
    "timezone": "America/Chicago",
    "from": "2026-03-12T00:00:00-06:00",
    "to": "2026-03-12T23:59:59-06:00"
  },
  "sort": [
    {
      "field": "updatedAt",
      "direction": "DESC"
    }
  ],
  "limit": 100
}

Agent-2 Generated SQL snippet

SQL

-- Compiled by Agent 2 from canonical AST -- Table aliases mapped perfectly to zp_disco_request via ce_semantic_mapping -- Named params (:from_status, :to_status) bound securely. -- __limit
offset automatically injected. SELECT r.request_id, r.customer_name, r.customer_id, r.status, r.updated_at FROM zp_disco_request r
where EXISTS ( SELECT 1 FROM zp_disco_request_log l_from
  JOIN zp_disco_request_log l_to
    on l_from.request_id = l_to.request_id AND l_from.logged_at < l_to.logged_at
where l_from.request_id = r.request_id AND l_from.status = :from_status AND l_to.status = :to_status AND l_to.updated_at >= :start_ts AND l_to.updated_at < :next_day_ts )
order by r.updated_at DESC
limit :__limit;

Config + Table Dependencies

Step dependency map

Step	Responsibility	Session mutations	Config/table dependencies
McpPlanner	Planner resolves semantic turn to CALL_TOOL with tool_code chain.	context.mcp.lifecycle.*, MCP_ACTION, MCP_TOOL_CODE	ce_mcp_planner (R), ce_mcp_tool (R), ce_rule (R), ce_audit (W)
db.semantic.interpret	Agent-1: Converts NL to Canonical business intent + calculates clarifications.	canonicalIntent, needsClarification, operationSupported	ce_prompt_template (R), ce_output_schema (R), ce_semantic_* (R), ce_audit (W)
db.semantic.query	Agent-2: SQL + params generation combining semantic constraints, history embeds, and retry validation.	compiledSql, params, retryAttempt, unsupported	ce_config (R), ce_semantic_mapping (R), ce_semantic_query_failures (R/W), ce_audit (W)
postgres.query	Read-only SQL execution against physical schema.	rowCount, rows	business tables (R), ce_semantic_query_failures (W), ce_audit (W)

Prompt Suite

Semantic prompt contracts used in this flow

---

Testing Catalog

Use the following catalog to test the semantic boundaries and guardrails of the system.

Semantic Query Test Prompt Catalog

Core retrieval

1. show disconnect requests for account UPSA100
2. show request status for ZPR1003
3. show downstream status for DON9001
4. show billbank record for disconnect id ZPDISC7003
5. show account status and billing cost for ACMA200

Failure / RCA (Diagnostics)

1. why did DON9001 fail
2. show failed or inventory_error requests
3. show requests where disconnect id is missing
4. show failed downstream checks for DON9001
5. show failure reason for request ZPR1003

Join-heavy analytics

1. show requestId, accountId, requestStatus, downstreamStatus, teamNotes for UPSA100
2. show requests with downstream status and billbank record status
3. show requests with UI notes containing inventory
4. show submitted requests with no billbank disconnect id
5. show billbank gaps with request and order details

Time-window tests

1. show disconnect requests between 2026-02-28 and 2026-03-01
2. show requests after 2026-03-01T00:00:00Z
3. show checks executed on 2026-03-01
4. show latest 3 disconnect requests
5. show requests in last 24 hours

Aggregation / metrics

1. count disconnect requests by requestStatus
2. count downstream orders by downstreamStatus
3. show failed_disconnect_count
4. show pending_disconnect_count
5. show billbank_gap_count

Negative Guardrail tests (Should Block)

1. delete from zp_disco_request where account_id='UPSA100'
2. update zp_disco_request set request_status='FAILED'
3. drop table zp_disco_request
4. show from unknown_entity
5. show random_field from DisconnectRequest