Investigating Agentforce D360

-- App-layer generation records — reusable for any WHERE filter.
-- DMO: GenAIAppGeneration__dlm
--
-- Placeholders (substituted by scripts/dc.py.load_sql):
--   WHERE_CLAUSE  — the filter expression, no "WHERE" keyword
--   ORDER_BY      — full "ORDER BY <col>" or empty string
--
-- Sibling of GenAIGeneration__dlm: where GenAIGeneration is the raw
-- gateway response, GenAIAppGeneration appears to be the app/feature-
-- layer view of the same generation (separate `id__c` from `generationId__c`).
--
-- No `ssot__` prefix — fields end in `__c` directly.
--
-- Join to a session: the same Step → Generation bridge used for
-- GenAIGeneration also works here. Pull session steps, collect
-- non-empty `ssot__GenerationId__c` values, then query here with
-- `generationId__c IN (...)`. The App-Generation row points at the
-- same underlying gateway generation.
--
-- The DMO is provisioned by default on orgs with generative AI audit
-- enabled, but row population depends on whether the org uses app-layer
-- regeneration / update flows. Verify presence with `sf ssot/metadata`
-- before relying on it.

SELECT
    id__c,
    generationId__c,
    generationUpdate__c,
    generationUpdateId__c,
    feature__c,
    timestamp__c,
    orgId__c,
    cloud__c
FROM GenAIAppGeneration__dlm
WHERE {{WHERE_CLAUSE}}
{{ORDER_BY}};


-- ============================================================================
-- EXAMPLE WHERE clauses (pass via where_clause=)
-- ============================================================================

-- App-generations for a set of step generation ids
-- WHERE    → generationId__c IN ('<gen_id1>','<gen_id2>',...)
-- ORDER BY → ORDER BY timestamp__c

-- App-generations in a time window for one org
-- WHERE    → orgId__c = '<org_id_18>'
--            AND timestamp__c >= '<iso_window_start>'
--            AND timestamp__c <  '<iso_window_end>'
-- ORDER BY → ORDER BY timestamp__c

-- GenAI content category (per-detector rows) — reusable WHERE.
-- DMO: GenAIContentCategory__dlm
--
-- Placeholders (substituted by scripts/dc.py.load_sql):
--   WHERE_CLAUSE  — the filter expression, no "WHERE" keyword
--   ORDER_BY      — full "ORDER BY <col>" or empty string
--
-- Two shapes of parent FK:
--   - Direct on a generation: non-TOXICITY detectors (InstructionAdherence,
--     TaskResolution, PII, PROMPT_DEFENSE) — parent__c = generationId__c
--   - Via a quality row: TOXICITY sub-categories —
--     parent__c = GenAIContentQuality.id__c
--
-- NOTE: No `ssot__` prefix — fields end in `__c` directly.

SELECT
    id__c,
    parent__c,
    detectorType__c,
    category__c,
    value__c,
    timestamp__c,
    orgId__c,
    cloud__c
FROM GenAIContentCategory__dlm
WHERE {{WHERE_CLAUSE}}
{{ORDER_BY}};


-- ============================================================================
-- EXAMPLE WHERE clauses (pass via where_clause=)
-- ============================================================================

-- Non-TOXICITY detectors for a set of generations (direct join)
-- WHERE    → parent__c IN ('<gen_id1>','<gen_id2>')
--            AND detectorType__c != 'TOXICITY'

-- TOXICITY sub-categories for a set of quality rows
-- WHERE    → parent__c IN ('<quality_id1>','<quality_id2>')
--            AND detectorType__c = 'TOXICITY'

-- InstructionAdherence scores only
-- WHERE    → parent__c IN ('<gen_id1>','<gen_id2>')
--            AND detectorType__c = 'InstructionAdherence'

-- GenAI content quality (per-generation quality rows) — reusable WHERE.
-- DMO: GenAIContentQuality__dlm
--
-- Placeholders (substituted by scripts/dc.py.load_sql):
--   WHERE_CLAUSE  — the filter expression, no "WHERE" keyword
--   ORDER_BY      — full "ORDER BY <col>" or empty string
--
-- Joined to a generation via `parent__c = generationId__c`. One row per
-- INPUT/OUTPUT side. `isToxicityDetected__c` is populated only on OUTPUT rows.
--
-- NOTE: No `ssot__` prefix — fields end in `__c` directly.

SELECT
    id__c,
    parent__c,
    isToxicityDetected__c,
    contentType__c,
    feature__c,
    timestamp__c,
    orgId__c,
    cloud__c
FROM GenAIContentQuality__dlm
WHERE {{WHERE_CLAUSE}}
{{ORDER_BY}};


-- ============================================================================
-- EXAMPLE WHERE clauses (pass via where_clause=)
-- ============================================================================

-- Quality rows for a set of generations
-- WHERE    → parent__c IN ('<gen_id1>','<gen_id2>',...)
-- ORDER BY → ORDER BY timestamp__c

-- Only rows where toxicity was detected (OUTPUT rows)
-- WHERE    → parent__c IN ('<gen_id1>','<gen_id2>')
--            AND isToxicityDetected__c = 'true'

-- Only OUTPUT-side rows
-- WHERE    → parent__c IN ('<gen_id1>','<gen_id2>')
--            AND contentType__c = 'OUTPUT'

-- Session discovery — find candidate sessions by time/agent/channel/outcome/grep.
-- Produces a short row-per-session shape for the picker rendered by
-- scripts/discover_sessions.py. NOT used by the trace pipeline — once the user
-- picks a UUID, the full pipeline runs fetch_dc.py against the 24-DMO waterfall.
--
-- Placeholders (substituted by scripts/dc.py.load_sql):
--   SELECT_LIST  — either `s.ssot__Id__c, s.ssot__StartTimestamp__c, s.ssot__EndTimestamp__c,
--                          s.ssot__AiAgentChannelType__c, s.ssot__AiAgentSessionEndType__c`
--                 OR `DISTINCT <same columns>` when JOINs are present (DC SQL requires
--                 ORDER BY columns to appear in a DISTINCT projection).
--   JOINS        — zero or more JOIN clauses, newline-separated, or empty string:
--                   * `JOIN ssot__AiAgentSessionParticipant__dlm p ON s.ssot__Id__c = p.ssot__AiAgentSessionId__c`
--                     (required when filtering by --agent)
--                   * `JOIN ssot__AiAgentInteraction__dlm i ON s.ssot__Id__c = i.ssot__AiAgentSessionId__c
--                      JOIN ssot__AiAgentInteractionMessage__dlm m ON i.ssot__Id__c = m.ssot__AiAgentInteractionId__c`
--                     (required when filtering by --grep)
--   WHERE_CLAUSE — composed by the caller. No "WHERE" keyword. Always non-empty
--                 (at minimum the time-range predicate). All user-supplied string
--                 literals are single-quote-escaped by doubling quotes (O'Brien → O''Brien).
--   LIMIT        — integer, 1..N. Default in caller is 20.
--
-- Field reference:
--   time range → s.ssot__StartTimestamp__c >= '<startISO>' AND s.ssot__StartTimestamp__c < '<endISO>'
--   outcome    → s.ssot__AiAgentSessionEndType__c = '<USER_ENDED|ESCALATED|TRANSFERRED|TIMEOUT|NOT_SET>'
--   channel    → s.ssot__AiAgentChannelType__c = '<Builder|SCRT2 - EmbeddedMessaging|Voice|...>'
--   agent      → p.ssot__AiAgentApiName__c = '<AgentApiName>'  (requires participant JOIN)
--   grep       → m.ssot__ContentText__c LIKE '%<escaped-pattern>%'  (requires interaction+message JOIN)
--
-- All STDM timestamps are UTC.

SELECT {{SELECT_LIST}}
FROM ssot__AIAgentSession__dlm s
{{JOINS}}
WHERE {{WHERE_CLAUSE}}
ORDER BY s.ssot__StartTimestamp__c DESC
LIMIT {{LIMIT}};

-- Free-text / structured detail attached to a feedback event.
-- DMO: GenAIFeedbackDetail__dlm
--
-- Placeholders (substituted by scripts/dc.py.load_sql):
--   WHERE_CLAUSE  — the filter expression, no "WHERE" keyword
--   ORDER_BY      — full "ORDER BY <col>" or empty string
--
-- Zero or more rows per feedback. `feedbackText__c` is what the user typed;
-- `appFeedback__c` is an app-layer tag (e.g. the reason bucket).
-- Joined via `parent__c = GenAIFeedback.feedbackId__c`.
--
-- NOTE: No `ssot__` prefix — fields end in `__c` directly.

SELECT
    feedbackDetailId__c,
    parent__c,
    feedbackText__c,
    appFeedback__c,
    feature__c,
    timestamp__c,
    orgId__c,
    cloud__c
FROM GenAIFeedbackDetail__dlm
WHERE {{WHERE_CLAUSE}}
{{ORDER_BY}};


-- ============================================================================
-- EXAMPLE WHERE clauses (pass via where_clause=)
-- ============================================================================

-- Details for a set of feedback rows
-- WHERE    → parent__c IN ('<feedback_id1>','<feedback_id2>',...)
-- ORDER BY → ORDER BY timestamp__c

-- Free-text feedback only (DC Query returns "" for unset text, not NULL)
-- WHERE    → parent__c IN ('<feedback_id1>','<feedback_id2>')
--            AND feedbackText__c IS NOT NULL AND feedbackText__c != ''

-- User feedback on generations — one row per feedback event.
-- DMO: GenAIFeedback__dlm
--
-- Placeholders (substituted by scripts/dc.py.load_sql):
--   WHERE_CLAUSE  — the filter expression, no "WHERE" keyword
--   ORDER_BY      — full "ORDER BY <col>" or empty string
--
-- Captures thumbs-up/thumbs-down (and richer `action__c`) that a user gave
-- a specific generation. Joined via `generationId__c = Generation.generationId__c`.
-- `feedbackId__c` is the PK that GenAIFeedbackDetail rows point at.
--
-- NOTE: No `ssot__` prefix — fields end in `__c` directly.

SELECT
    feedbackId__c,
    generationId__c,
    generationUpdateId__c,
    generationGroupId__c,
    userId__c,
    feedback__c,
    action__c,
    source__c,
    feature__c,
    appType__c,
    timestamp__c,
    orgId__c,
    cloud__c
FROM GenAIFeedback__dlm
WHERE {{WHERE_CLAUSE}}
{{ORDER_BY}};


-- ============================================================================
-- EXAMPLE WHERE clauses (pass via where_clause=)
-- ============================================================================

-- Feedback for a set of generations (typical session trace path)
-- WHERE    → generationId__c IN ('<gen_id1>','<gen_id2>',...)
-- ORDER BY → ORDER BY timestamp__c

-- Feedback by a specific user in a time window
-- WHERE    → userId__c = '<user_id>'
--            AND timestamp__c >= '<iso_cutoff>'

-- Only thumbs-down
-- WHERE    → generationId__c IN ('<gen_id1>','<gen_id2>')
--            AND feedback__c = 'DOWN'

-- Gateway object records — structured attachments on requests/feedback.
-- DMO: GenAIGtwyObjRecord__dlm
--
-- Placeholders (substituted by scripts/dc.py.load_sql):
--   WHERE_CLAUSE  — the filter expression, no "WHERE" keyword
--   ORDER_BY      — full "ORDER BY <col>" or empty string
--
-- Polymorphic attachment table. `parent__c` points at different parents
-- depending on `type__c`:
--   - `parent__c = GenAIGatewayRequest.gatewayRequestId__c` (grounded-record
--     attachments) — the forward-only path used by this skill's waterfall.
--   - `parent__c = GenAIFeedback.feedbackId__c` (feedback attachments) —
--     present only when the session has feedback rows.
-- The waterfall queries the gateway-request case (wave 3); feedback attachments
-- appear through the session only when feedback is present.
--
-- NOTE: No `ssot__` prefix — fields end in `__c` directly.

SELECT
    id__c,
    parent__c,
    recordId__c,
    type__c,
    name__c,
    value__c,
    metadata__c,
    feature__c,
    timestamp__c,
    orgId__c,
    cloud__c
FROM GenAIGtwyObjRecord__dlm
WHERE {{WHERE_CLAUSE}}
{{ORDER_BY}};


-- ============================================================================
-- EXAMPLE WHERE clauses (pass via where_clause=)
-- ============================================================================

-- Records attached to a set of feedback rows
-- WHERE    → parent__c IN ('<feedback_id1>','<feedback_id2>',...)
-- ORDER BY → ORDER BY timestamp__c

-- Records attached to a set of gateway request ids
-- WHERE    → parent__c IN ('<req_id1>','<req_id2>')

-- Per-request LLM call diagnostics — reusable for any WHERE filter.
-- DMO: GenAIGtwyRequestLLM__dlm
--
-- Placeholders (substituted by scripts/dc.py.load_sql):
--   WHERE_CLAUSE  — the filter expression, no "WHERE" keyword
--   ORDER_BY      — full "ORDER BY <col>" or empty string
--
-- Child of GenAIGatewayRequest__dlm. Captures LLM invocation
-- diagnostics (latency, status, endpoint, region, model). Populated
-- only when Trust Layer gateway LLM telemetry is on.
--
-- The DMO is provisioned by default but rows only appear when the
-- gateway LLM telemetry is emitted. Join column pattern mirrors
-- GenAIGtwyRequestMetadata__dlm (same parent__c shape).
--
-- No `ssot__` prefix — fields end in `__c` directly. Note that org id
-- is `salesforceOrgId__c` on this DMO, not the usual `orgId__c`.
--
-- Joined via `parent__c = GatewayRequest.gatewayRequestId__c`.

SELECT
    id__c,
    parent__c,
    endpoint__c,
    region__c,
    genAILLM__c,
    llmCallStatus__c,
    llmCallLatency__c,
    llmErrorTrace__c,
    metadata__c,
    feature__c,
    salesforceOrgId__c,
    timestamp__c,
    cloud__c
FROM GenAIGtwyRequestLLM__dlm
WHERE {{WHERE_CLAUSE}}
{{ORDER_BY}};


-- ============================================================================
-- EXAMPLE WHERE clauses (pass via where_clause=)
-- ============================================================================

-- All LLM diagnostic rows for a set of gateway request ids
-- WHERE    → parent__c IN ('<req_id1>','<req_id2>',...)
-- ORDER BY → ORDER BY timestamp__c

-- Only failed LLM calls
-- WHERE    → parent__c IN ('<req_id1>','<req_id2>')
--            AND llmCallStatus__c != 'success'

-- Additional per-request metadata — reusable for any WHERE filter.
-- DMO: GenAIGtwyRequestMetadata__dlm
--
-- Placeholders (substituted by scripts/dc.py.load_sql):
--   WHERE_CLAUSE  — the filter expression, no "WHERE" keyword
--   ORDER_BY      — full "ORDER BY <col>" or empty string
--
-- Child of GenAIGatewayRequest__dlm. Holds typed metadata rows for a
-- request — observed values include `metadataType__c = 'ToolCall'` and
-- `feature__c = 'plannerservice'`, so this is where planner/tool-call
-- metadata on a gateway request lives.
--
-- No `ssot__` prefix — fields end in `__c` directly.
--
-- Joined via `parent__c = GatewayRequest.gatewayRequestId__c`.
-- Join direction verified live: sampled row's parent__c matched exactly
-- one row in GenAIGatewayRequest__dlm. The table is usually heavily
-- populated on orgs with Trust Layer gateway logging enabled.

SELECT
    id__c,
    parent__c,
    metadataType__c,
    metadata__c,
    feature__c,
    timestamp__c,
    orgId__c,
    cloud__c
FROM GenAIGtwyRequestMetadata__dlm
WHERE {{WHERE_CLAUSE}}
{{ORDER_BY}};


-- ============================================================================
-- EXAMPLE WHERE clauses (pass via where_clause=)
-- ============================================================================

-- All metadata rows for a set of gateway request ids
-- WHERE    → parent__c IN ('<req_id1>','<req_id2>',...)
-- ORDER BY → ORDER BY timestamp__c

-- Only ToolCall-type metadata
-- WHERE    → parent__c IN ('<req_id1>','<req_id2>')
--            AND metadataType__c = 'ToolCall'

-- Gateway request tags — k/v metadata attached to a gateway request.
-- DMO: GenAIGatewayRequestTag__dlm
--
-- Placeholders (substituted by scripts/dc.py.load_sql):
--   WHERE_CLAUSE  — the filter expression, no "WHERE" keyword
--   ORDER_BY      — full "ORDER BY <col>" or empty string
--
-- Many rows per request. Common tags include:
--   - `prompt_template_dev_name` → which prompt template was used
--   - `user_utterance`           → raw user input that triggered this request
--
-- Joined via `parent__c = GatewayRequest.gatewayRequestId__c`.
-- No `ssot__` prefix — fields end in `__c` directly.

SELECT
    id__c,
    parent__c,
    tag__c,
    tagValue__c,
    timestamp__c,
    orgId__c,
    cloud__c
FROM GenAIGatewayRequestTag__dlm
WHERE {{WHERE_CLAUSE}}
{{ORDER_BY}};


-- ============================================================================
-- EXAMPLE WHERE clauses (pass via where_clause=)
-- ============================================================================

-- All tags for a set of request ids
-- WHERE    → parent__c IN ('<req_id1>','<req_id2>',...)
-- ORDER BY → ORDER BY timestamp__c

-- Only `prompt_template_dev_name` tags
-- WHERE    → parent__c IN ('<req_id1>','<req_id2>')
--            AND tag__c = 'prompt_template_dev_name'

-- Find requests where the user utterance matched a pattern
-- WHERE    → tag__c = 'user_utterance'
--            AND tagValue__c LIKE '%refund%'

-- Gateway requests — one row per LLM request at the GenAI Gateway.
-- DMO: GenAIGatewayRequest__dlm
--
-- Placeholders (substituted by scripts/dc.py.load_sql):
--   WHERE_CLAUSE  — the filter expression, no "WHERE" keyword
--   ORDER_BY      — full "ORDER BY <col>" or empty string
--
-- Richer than GenAIGeneration — carries the actual prompt text, token counts,
-- model params (temperature/penalties), session/user IDs, bot version, and
-- the masked-prompt variant.
--
-- NOTE: No `ssot__` prefix — fields end in `__c` directly.
--
-- sessionId__c storage format (verified live): the value is stored as a
-- literal 40-char string INCLUDING surrounding double-quotes, e.g.
--   sessionId__c = "<session_uuid>"
-- Non-session features (prompt-builder previews, eval harnesses, etc.) store
-- the literal sentinel "no_session". Exact-match queries MUST include the
-- double-quotes:
--   WHERE sessionId__c = '"<session_uuid>"'
-- Or use LIKE with wildcards (robust against format variants):
--   WHERE sessionId__c LIKE '%<session_uuid>%'
-- Raw-UUID exact match returns 0 rows — the quotes are part of the stored value.
--
-- Forward join path from a session:
--   Session.ssot__Id__c  →  GatewayRequest.sessionId__c (LIKE or quoted match)
-- This is the authoritative and only supported entry point. GatewayRequest is
-- then the parent for all downstream audit-chain children — Response (via
-- generationRequestId__c), Tag/ObjRecord/Metadata/LLM (via parent__c).
-- See `scripts/fetch_dc.py` wave 3 and `references/dc_dmo_fields.md` "Cross-DMO
-- join map" for the full forward tree.

SELECT
    gatewayRequestId__c,
    generationGroupId__c,
    sessionId__c,
    userId__c,
    botVersionId__c,
    plannerId__c,
    feature__c,
    appType__c,
    model__c,
    provider__c,
    promptTemplateDevName__c,
    promptTemplateVersionNo__c,
    prompt__c,
    maskedPrompt__c,
    parameters__c,
    temperature__c,
    frequencyPenalty__c,
    presencePenalty__c,
    stopSequences__c,
    numGenerations__c,
    promptTokens__c,
    completionTokens__c,
    totalTokens__c,
    enableInputSafetyScoring__c,
    enableOutputSafetyScoring__c,
    enablePiiMasking__c,
    timestamp__c,
    orgId__c,
    cloud__c
FROM GenAIGatewayRequest__dlm
WHERE {{WHERE_CLAUSE}}
{{ORDER_BY}};


-- ============================================================================
-- EXAMPLE WHERE clauses (pass via where_clause=)
-- ============================================================================

-- Requests for one session (direct FK; note the mandatory double-quoted form).
-- Two equivalent WHERE forms — both verified live, both return the same rows:
-- WHERE    → sessionId__c = '"<session_id>"'            (exact match on quoted string)
-- WHERE    → sessionId__c LIKE '%<session_id>%'          (format-tolerant)
-- ORDER BY → ORDER BY timestamp__c

-- Requests for a specific set of gatewayRequestIds (e.g. narrowing after a
-- session fetch, or lookup by ids harvested from another query)
-- WHERE    → gatewayRequestId__c IN ('<req_id1>','<req_id2>',...)

-- All requests for a bot version in a time window
-- WHERE    → botVersionId__c = '<version_id>'
--            AND timestamp__c >= '<iso_cutoff>'
-- ORDER BY → ORDER BY timestamp__c DESC

-- Requests using a specific prompt template
-- WHERE    → promptTemplateDevName__c = '<template_dev_name>'
--            AND timestamp__c >= '<iso_cutoff>'

-- Gateway responses — one row per LLM call response at the GenAI Gateway.
-- DMO: GenAIGatewayResponse__dlm
--
-- Placeholders (substituted by scripts/dc.py.load_sql):
--   WHERE_CLAUSE  — the filter expression, no "WHERE" keyword
--   ORDER_BY      — full "ORDER BY <col>" or empty string
--
-- FK shape (small table; documented for reference only):
--   generationRequestId__c   = GatewayRequest.gatewayRequestId__c
--   generationResponseId__c  = Step.ssot__GenAiGatewayResponseId__c
--                            = Generation.generationResponseId__c
--
-- Forward join path from a session:
--   Session → GatewayRequest (sessionId__c LIKE)
--           → GatewayResponse (generationRequestId__c IN {gw_req_ids})
-- This is the canonical and only supported direction. 1:1 invariant holds
-- in live data — every GatewayRequest for a session produces one Response
-- row (modulo in-flight calls at fetch time).
--
-- NOTE: No `ssot__` prefix — fields end in `__c` directly.

SELECT
    generationResponseId__c,
    generationRequestId__c,
    parameters__c,
    timestamp__c,
    orgId__c,
    cloud__c
FROM GenAIGatewayResponse__dlm
WHERE {{WHERE_CLAUSE}}
{{ORDER_BY}};


-- ============================================================================
-- EXAMPLE WHERE clauses (pass via where_clause=)
-- ============================================================================

-- Forward: Responses for the session's gateway requests (primary use case)
-- WHERE    → generationRequestId__c IN ('<req_id1>','<req_id2>',...)
-- ORDER BY → ORDER BY timestamp__c

-- Ad-hoc lookup by specific response ids (not used by the waterfall)
-- WHERE    → generationResponseId__c IN ('<resp_id1>','<resp_id2>',...)

-- GenAI gateway generations — reusable for any WHERE filter.
-- DMO: GenAIGeneration__dlm
--
-- Placeholders (substituted by scripts/dc.py.load_sql):
--   WHERE_CLAUSE  — the filter expression, no "WHERE" keyword
--   ORDER_BY      — full "ORDER BY <col>" or empty string
--
-- One row per LLM call at the gateway (Trust Layer).
--
-- NOTE: No `ssot__` prefix on this DMO — fields end in `__c` directly.
--
-- Forward join to a session: this DMO has NO session/trace/turn column.
-- The only supported path is Step.ssot__GenerationId__c → generationId__c,
-- driven forward from the session:
--   Session → Interaction (ssot__AiAgentSessionId__c)
--           → Step        (ssot__AiAgentInteractionId__c)
--           → Generation  (step.ssot__GenerationId__c IN {generationId__c})
-- Pull step rows for the session's interactions first, collect non-empty
-- `ssot__GenerationId__c` values (LLM_STEP rows populate it; others are
-- NOT_SET), then query here with `generationId__c IN (...)`.

SELECT
    generationId__c,
    generationResponseId__c,
    responseText__c,
    maskedResponseText__c,
    responseParameters__c,
    feature__c,
    timestamp__c,
    orgId__c,
    cloud__c
FROM GenAIGeneration__dlm
WHERE {{WHERE_CLAUSE}}
{{ORDER_BY}};


-- ============================================================================
-- EXAMPLE WHERE clauses (pass via where_clause=)
-- ============================================================================

-- Generations for a set of step generation ids
-- WHERE    → generationId__c IN ('<gen_id1>','<gen_id2>',...)
-- ORDER BY → ORDER BY timestamp__c

-- Generations in a time window for one org
-- WHERE    → orgId__c = '<org_id_18>'
--            AND timestamp__c >= '<iso_window_start>'
--            AND timestamp__c <  '<iso_window_end>'
-- ORDER BY → ORDER BY timestamp__c

-- Filter by feature (e.g. Copilot vs guardrails)
-- WHERE    → feature__c = 'CopilotForDigitalChannels'

-- Session interactions (turns + session-end event) — reusable for any WHERE.
-- DMO: ssot__AIAgentInteraction__dlm
--
-- Placeholders (substituted by scripts/dc.py.load_sql):
--   WHERE_CLAUSE  — the filter expression, no "WHERE" keyword
--   ORDER_BY      — full "ORDER BY <col>" or empty string
--
-- One row per turn plus one SESSION_END row per session.
-- Type enum: TURN | SESSION_END.
--
-- Casing gotcha: DMO name uses `AIAgent` (uppercase AI). Field names use
-- `AiAgent` (lowercase i). See references/dc_dmo_fields.md.
--
-- trace_id gotcha: `ssot__TelemetryTraceId__c` is often empty on real orgs
-- (verified live). The runtime trace_id lives inside
-- `ssot__AttributeText__c` as HTML-escaped JSON, key `internalTraceId`.
-- Consumers must `html.unescape()` + regex-extract. Used to join with
-- GenAIGeneration (generationId via Step) and TelemetryTraceSpan.

SELECT
    ssot__Id__c,
    ssot__AiAgentSessionId__c,
    ssot__AiAgentInteractionType__c,
    ssot__TopicApiName__c,
    ssot__StartTimestamp__c,
    ssot__EndTimestamp__c,
    ssot__PrevInteractionId__c,
    ssot__SessionOwnerId__c,
    ssot__IndividualId__c,
    ssot__InternalOrganizationId__c,
    ssot__TelemetryTraceId__c,
    ssot__TelemetryTraceSpanId__c,
    ssot__AttributeText__c
FROM ssot__AIAgentInteraction__dlm
WHERE {{WHERE_CLAUSE}}
{{ORDER_BY}};


-- ============================================================================
-- EXAMPLE WHERE clauses (pass via where_clause=)
-- ============================================================================

-- All interactions for one session
-- WHERE    → ssot__AiAgentSessionId__c = '<session_id>'
-- ORDER BY → ORDER BY ssot__StartTimestamp__c

-- TURN rows only (exclude SESSION_END)
-- WHERE    → ssot__AiAgentSessionId__c = '<session_id>'
--            AND ssot__AiAgentInteractionType__c = 'TURN'

-- Interactions handled by a specific topic across sessions
-- WHERE    → ssot__TopicApiName__c = 'Order_Management'
--            AND ssot__StartTimestamp__c >= '2026-01-01T00:00:00.000Z'

-- User/agent messages — reusable for any WHERE filter.
-- DMO: ssot__AiAgentInteractionMessage__dlm
--
-- Placeholders (substituted by scripts/dc.py.load_sql):
--   WHERE_CLAUSE  — the filter expression, no "WHERE" keyword
--   ORDER_BY      — full "ORDER BY <col>" or empty string
--
-- One row per user/agent message. Type enum: Input | Output.
--
-- This DMO has a direct session FK (`ssot__AiAgentSessionId__c`) — verified
-- live against Data Cloud v66.0. Scope by session directly; no need to join through
-- interactions. It also has a participant FK and a parent-message FK for
-- threading, plus `MessageStartTimestamp__c` / `MessageEndTimestamp__c` for
-- voice-modality durations (richer than the single `MessageSentTimestamp__c`).

SELECT
    ssot__Id__c,
    ssot__AiAgentSessionId__c,
    ssot__AiAgentInteractionId__c,
    ssot__AiAgentSessionParticipantId__c,
    ssot__ParentMessageId__c,
    ssot__ContentText__c,
    ssot__AiAgentInteractionMessageType__c,
    ssot__AiAgentInteractionMsgContentType__c,
    Modality__c,
    ssot__MessageSentTimestamp__c,
    MessageStartTimestamp__c,
    MessageEndTimestamp__c,
    ssot__InternalOrganizationId__c
FROM ssot__AiAgentInteractionMessage__dlm
WHERE {{WHERE_CLAUSE}}
{{ORDER_BY}};


-- ============================================================================
-- EXAMPLE WHERE clauses (pass via where_clause=)
-- ============================================================================

-- Messages for one session (direct FK — preferred)
-- WHERE    → ssot__AiAgentSessionId__c = '<session_id>'
-- ORDER BY → ORDER BY ssot__MessageSentTimestamp__c

-- Messages for a specific interaction
-- WHERE    → ssot__AiAgentInteractionId__c = '<interaction_id>'
-- ORDER BY → ORDER BY ssot__MessageSentTimestamp__c

-- Only user inputs for a session
-- WHERE    → ssot__AiAgentSessionId__c = '<session_id>'
--            AND ssot__AiAgentInteractionMessageType__c = 'Input'

-- Voice-modality messages (use start/end timestamps for duration)
-- WHERE    → ssot__AiAgentSessionId__c = '<session_id>'
--            AND Modality__c = 'Voice'

-- MessagingSession id → AI-agent session id lookup.
-- DMO: ssot__AIAgentSession__dlm
--
-- Given a Salesforce MessagingSession id (0Mw... prefix, 15 or 18 chars),
-- find every ssot__AIAgentSession__dlm row with matching
-- RelatedMessagingSessionId. Used by scripts/resolve_session.py to map a
-- messaging id to the canonical AI-agent session UUID that every other
-- script keys on.
--
-- Placeholders (substituted by scripts/dc.py.load_sql):
--   MSG_ID  — the MessagingSession id (pre-validated by the caller:
--             is_messaging_id() enforces the `0Mw` key prefix plus an
--             exact 15 or 18 char length before this template is loaded.
--             A raw-UUID or free-text id can never reach this template.)
--
-- Returned rows:
--   * zero rows  → caller raises SystemExit ("no messaging session found")
--   * one row    → caller returns ssot__Id__c as the UUID
--   * many rows  → caller prints every candidate with timestamps + end_type
--                  + channel and exits non-zero so the user can pick one
--                  and re-invoke with the specific UUID.
--
-- The `RelatedMessagingSessionId__c != 'NOT_SET'` clause is defensive —
-- a real msg_id cannot equal the literal 'NOT_SET', but the guard lets
-- the template be copy-pasted for other filters that might otherwise
-- accidentally match the sentinel.

SELECT
    ssot__Id__c,
    ssot__StartTimestamp__c,
    ssot__EndTimestamp__c,
    ssot__AiAgentSessionEndType__c,
    ssot__AiAgentChannelType__c
FROM ssot__AIAgentSession__dlm
WHERE ssot__RelatedMessagingSessionId__c = '{{MSG_ID}}'
  AND ssot__RelatedMessagingSessionId__c != 'NOT_SET'
ORDER BY ssot__StartTimestamp__c DESC;

-- Moment ↔ Interaction junction — which turns belong to a moment.
-- DMO: ssot__AiAgentMomentInteraction__dlm
--
-- Placeholders (substituted by scripts/dc.py.load_sql):
--   WHERE_CLAUSE  — the filter expression, no "WHERE" keyword
--   ORDER_BY      — full "ORDER BY <col>" or empty string
--
-- Agent Optimization add-on DMO. Provisioned only when Agent Optimization
-- is enabled. Junction between AiAgentMoment and AIAgentInteraction.
-- Observed live: one Moment per Interaction (N:1 direction). The junction
-- schema supports true many-to-many; the assembler emits Moment.interaction_ids[]
-- back-refs to preserve the schema-correct shape even when live data is 1:N.

SELECT
    ssot__Id__c,
    ssot__AiAgentMomentId__c,
    ssot__AiAgentInteractionId__c,
    ssot__StartTimestamp__c,
    ssot__InternalOrganizationId__c
FROM ssot__AiAgentMomentInteraction__dlm
WHERE {{WHERE_CLAUSE}}
{{ORDER_BY}};


-- ============================================================================
-- EXAMPLE WHERE clauses (pass via where_clause=)
-- ============================================================================

-- Junction rows for a set of moments
-- WHERE    → ssot__AiAgentMomentId__c IN ('<mom_id1>','<mom_id2>',...)
-- ORDER BY → ORDER BY ssot__StartTimestamp__c

-- Junction rows for a set of interactions (reverse lookup)
-- WHERE    → ssot__AiAgentInteractionId__c IN ('<int_id1>','<int_id2>')

-- Session-level agent moment rollup — reusable for any WHERE filter.
-- DMO: ssot__AiAgentMoment__dlm
--
-- Placeholders (substituted by scripts/dc.py.load_sql):
--   WHERE_CLAUSE  — the filter expression, no "WHERE" keyword
--   ORDER_BY      — full "ORDER BY <col>" or empty string
--
-- When present, carries agent identity + a request/response summary for
-- the session. This is the only STDM DMO outside Participant that exposes
-- AiAgentApiName__c. Moments are absent on orgs without Agent Optimization
-- enabled; the assembler falls back to Participant (AGENT role) for agent
-- identity in that case.

SELECT
    ssot__Id__c,
    ssot__AiAgentSessionId__c,
    ssot__AiAgentApiName__c,
    ssot__AiAgentVersionApiName__c,
    ssot__RequestSummaryText__c,
    ssot__ResponseSummaryText__c,
    ssot__StartTimestamp__c,
    ssot__EndTimestamp__c,
    ssot__InternalOrganizationId__c
FROM ssot__AiAgentMoment__dlm
WHERE {{WHERE_CLAUSE}}
{{ORDER_BY}};


-- ============================================================================
-- EXAMPLE WHERE clauses (pass via where_clause=)
-- ============================================================================

-- Moment(s) for one session — usually 0 or 1 row
-- WHERE    → ssot__AiAgentSessionId__c = '<session_id>'

-- All sessions handled by a specific agent API name in a date range
-- WHERE    → ssot__AiAgentApiName__c = 'MyAgent'
--            AND ssot__StartTimestamp__c >= '2026-01-01T00:00:00.000Z'
-- ORDER BY → ORDER BY ssot__StartTimestamp__c

-- Session participants from Data Cloud — reusable for any WHERE filter.
-- DMO: ssot__AiAgentSessionParticipant__dlm
--
-- Placeholders (substituted by scripts/dc.py.load_sql):
--   WHERE_CLAUSE  — the filter expression, no "WHERE" keyword
--   ORDER_BY      — full "ORDER BY <col>" or empty string
--
-- One row per participant per session. Roles: USER, AGENT.
-- AiAgentApiName__c is populated on AGENT rows only.
--
-- Casing gotcha: DMO name uses `AiAgent` (lowercase i), unlike the Session
-- DMO which uses `AIAgent` (uppercase AI). See references/dc_dmo_fields.md.

SELECT
    ssot__Id__c,
    ssot__AiAgentSessionId__c,
    ssot__ParticipantId__c,
    ssot__AiAgentApiName__c,
    ssot__AiAgentType__c,
    ssot__AiAgentTemplateApiName__c,
    ssot__AiAgentVersionApiName__c,
    ssot__AiAgentSessionParticipantRole__c,
    ssot__ParticipantObject__c,
    ssot__StartTimestamp__c,
    ssot__EndTimestamp__c,
    ssot__IndividualId__c,
    ssot__InternalOrganizationId__c,
    ssot__ParticipantAttributeText__c
FROM ssot__AiAgentSessionParticipant__dlm
WHERE {{WHERE_CLAUSE}}
{{ORDER_BY}};


-- ============================================================================
-- EXAMPLE WHERE clauses (pass via where_clause=)
-- ============================================================================

-- Participants for one session
-- WHERE    → ssot__AiAgentSessionId__c = '<session_id>'
-- ORDER BY → ORDER BY ssot__StartTimestamp__c

-- Only AGENT rows (carry agent identity)
-- WHERE    → ssot__AiAgentSessionId__c = '<session_id>'
--            AND ssot__AiAgentSessionParticipantRole__c = 'AGENT'

-- All sessions handled by a specific agent
-- WHERE    → ssot__AiAgentApiName__c = 'MyAgent'
--            AND ssot__StartTimestamp__c >= '2026-01-01T00:00:00.000Z'

-- Sessions from Data Cloud — reusable for any WHERE filter.
-- DMO: ssot__AIAgentSession__dlm
--
-- Placeholders (substituted by scripts/dc.py._load):
--   WHERE_CLAUSE  — the filter expression, no "WHERE" keyword
--   ORDER_BY      — full "ORDER BY <col>" or empty string
--
-- See EXAMPLE QUERIES below for common WHERE patterns.
--
-- This query extracts session-level data including:
--   - Session ID and timestamps
--   - Channel type (how user connected)
--   - How the session ended (Completed, Abandoned, Escalated, etc.)
--   - Related messaging session (if applicable)
--
-- NOTE: Agent name is NOT on Session table. Join with Moment to get agent info.

SELECT
    ssot__Id__c,
    ssot__AiAgentChannelType__c,
    ssot__StartTimestamp__c,
    ssot__EndTimestamp__c,
    ssot__AiAgentSessionEndType__c,
    ssot__RelatedMessagingSessionId__c,
    ssot__RelatedVoiceCallId__c,
    ssot__InternalOrganizationId__c,
    ssot__SessionOwnerId__c,
    ssot__SessionOwnerObject__c,
    ssot__IndividualId__c,
    ssot__PreviousSessionId__c,
    ssot__VariableText__c
FROM ssot__AIAgentSession__dlm
WHERE {{WHERE_CLAUSE}}
{{ORDER_BY}};


-- ============================================================================
-- EXAMPLE QUERIES (pass to sessions_sql via where_clause= / order_by=)
-- ============================================================================

-- One session by id (this skill's primary use case)
-- WHERE    → ssot__Id__c = '<session_uuid>'
-- ORDER BY → ORDER BY ssot__StartTimestamp__c

-- Last 7 days of sessions
-- WHERE    → ssot__StartTimestamp__c >= '<iso_cutoff_7d_ago>'
-- ORDER BY → ORDER BY ssot__StartTimestamp__c

-- Date range
-- WHERE    → ssot__StartTimestamp__c >= '2026-01-01T00:00:00.000Z'
--            AND ssot__StartTimestamp__c < '2026-02-01T00:00:00.000Z'
-- ORDER BY → ORDER BY ssot__StartTimestamp__c

-- Failed / escalated sessions only
-- WHERE    → ssot__AiAgentSessionEndType__c IN ('Escalated', 'Abandoned', 'Failed')
--            AND ssot__StartTimestamp__c >= '2026-01-01T00:00:00.000Z'
-- ORDER BY → ORDER BY ssot__StartTimestamp__c

-- Sessions by channel (e.g. embedded messaging only)
-- WHERE    → ssot__AiAgentChannelType__c = 'SCRT2 - EmbeddedMessaging'
--            AND ssot__StartTimestamp__c >= '2026-01-01T00:00:00.000Z'
-- ORDER BY → ORDER BY ssot__StartTimestamp__c

-- Session count by end type (aggregate — SELECT list changes too; separate template)
-- SELECT
--     ssot__AiAgentSessionEndType__c,
--     COUNT(*) as session_count
-- FROM ssot__AIAgentSession__dlm
-- WHERE ssot__StartTimestamp__c >= '2026-01-01T00:00:00.000Z'
-- GROUP BY ssot__AiAgentSessionEndType__c;

-- Sessions by agent (requires Moment join — separate query shape, not this template)
-- SELECT DISTINCT s.*
-- FROM ssot__AIAgentSession__dlm s
-- JOIN ssot__AiAgentMoment__dlm m
--     ON m.ssot__AiAgentSessionId__c = s.ssot__Id__c
-- WHERE m.ssot__AiAgentApiName__c = 'MyAgent'
--   AND s.ssot__StartTimestamp__c >= '2026-01-01T00:00:00.000Z';

-- Planner steps — reusable for any WHERE filter.
-- DMO: ssot__AIAgentInteractionStep__dlm
--
-- Placeholders (substituted by scripts/dc.py.load_sql):
--   WHERE_CLAUSE  — the filter expression, no "WHERE" keyword
--   ORDER_BY      — full "ORDER BY <col>" or empty string
--
-- One row per planner step within an interaction. Type enum (verified live):
-- LLM_STEP | ACTION_STEP | TOPIC_STEP | TRUST_GUARDRAILS_STEP | SESSION_END.
--
-- NOTE: InputValueText__c / OutputValueText__c are HTML-escaped JSON.
-- Callers should `html.unescape()` then `json.loads()` after fetch.
-- No direct session FK — scope forward via ssot__AiAgentInteractionId__c
-- (harvested from the session's Interaction rows).
--
-- ssot__GenAiGatewayRequestId__c / ssot__GenAiGatewayResponseId__c are
-- included in the SELECT for completeness but are NOT used as join keys
-- anywhere in the waterfall. Gateway audit rows are fetched forward from
-- Session → GatewayRequest (via sessionId__c) — see gateway_requests.sql.
-- ssot__ErrorMessageText__c uses the sentinel 'NOT_SET' (never NULL) to
-- mean "no error"; filter with `!= 'NOT_SET'`, not `IS NOT NULL`.

SELECT
    ssot__Id__c,
    ssot__AiAgentInteractionId__c,
    ssot__AiAgentInteractionStepType__c,
    ssot__Name__c,
    ssot__InputValueText__c,
    ssot__OutputValueText__c,
    ssot__PreStepVariableText__c,
    ssot__PostStepVariableText__c,
    ssot__GenerationId__c,
    ssot__ErrorMessageText__c,
    ssot__StartTimestamp__c,
    ssot__EndTimestamp__c,
    ssot__PrevStepId__c,
    ssot__InternalOrganizationId__c,
    ssot__TelemetryTraceSpanId__c,
    ssot__AttributeText__c,
    ssot__GenAiGatewayRequestId__c,
    ssot__GenAiGatewayResponseId__c
FROM ssot__AIAgentInteractionStep__dlm
WHERE {{WHERE_CLAUSE}}
{{ORDER_BY}};


-- ============================================================================
-- EXAMPLE WHERE clauses (pass via where_clause=)
-- ============================================================================

-- Steps for a specific interaction
-- WHERE    → ssot__AiAgentInteractionId__c = '<interaction_id>'
-- ORDER BY → ORDER BY ssot__StartTimestamp__c

-- Steps for a session (via interaction id IN list)
-- WHERE    → ssot__AiAgentInteractionId__c IN ('<id1>','<id2>',...)

-- Only action steps (exclude LLM + topic planning)
-- WHERE    → ssot__AiAgentInteractionId__c IN ('<id1>','<id2>')
--            AND ssot__AiAgentInteractionStepType__c = 'ACTION_STEP'

-- Steps with errors (sentinel value; NOT null)
-- WHERE    → ssot__AiAgentInteractionId__c IN ('<id1>','<id2>')
--            AND ssot__ErrorMessageText__c != 'NOT_SET'

-- Tag ↔ target association — which tag was applied to a moment/session/interaction.
-- DMO: ssot__AiAgentTagAssociation__dlm
--
-- Placeholders (substituted by scripts/dc.py.load_sql):
--   WHERE_CLAUSE  — the filter expression, no "WHERE" keyword
--   ORDER_BY      — full "ORDER BY <col>" or empty string
--
-- Agent Optimization add-on DMO. THE applied-tag row — one per annotation.
-- Points at a tag value (`ssot__AiAgentTagId__c`) + a target
-- (moment | session | interaction) + the tag-definition-association that
-- authorized it. `ssot__AssociationReasonText__c` carries the LLM's
-- rationale for why it assigned this tag.

SELECT
    ssot__Id__c,
    ssot__AiAgentTagId__c,
    ssot__AiAgentTagDefinitionAssociationId__c,
    ssot__AiAgentMomentId__c,
    ssot__AiAgentSessionId__c,
    ssot__AiAgentInteractionId__c,
    ssot__IsPassed__c,
    ssot__AssociationReasonText__c,
    ssot__CreatedDate__c,
    ssot__InternalOrganizationId__c
FROM ssot__AiAgentTagAssociation__dlm
WHERE {{WHERE_CLAUSE}}
{{ORDER_BY}};


-- ============================================================================
-- EXAMPLE WHERE clauses (pass via where_clause=)
-- ============================================================================

-- All tag associations for one session
-- WHERE    → ssot__AiAgentSessionId__c = '<session_id>'
-- ORDER BY → ORDER BY ssot__CreatedDate__c

-- Tags applied to a set of moments
-- WHERE    → ssot__AiAgentMomentId__c IN ('<mom_id1>','<mom_id2>')

-- Only passed evaluations (quality pass/fail)
-- WHERE    → ssot__AiAgentSessionId__c = '<session_id>'
--            AND ssot__IsPassed__c = true

-- Associations by tag id (reverse lookup)
-- WHERE    → ssot__AiAgentTagId__c IN ('<tag_id1>','<tag_id2>')

-- Tag definition ↔ agent association — which tags are available for an agent.
-- DMO: ssot__AiAgentTagDefinitionAssociation__dlm
--
-- Placeholders (substituted by scripts/dc.py.load_sql):
--   WHERE_CLAUSE  — the filter expression, no "WHERE" keyword
--   ORDER_BY      — full "ORDER BY <col>" or empty string
--
-- Agent Optimization add-on DMO. Binds a TagDefinition to a specific agent
-- (by API name) and prompt template. This is what `AiAgentTagAssociation`
-- actually points at via `ssot__AiAgentTagDefinitionAssociationId__c`.

SELECT
    ssot__Id__c,
    ssot__AiAgentTagDefinitionId__c,
    ssot__AiAgentApiName__c,
    ssot__AiPromptTemplateId__c,
    ssot__IsActive__c,
    ssot__CreatedDate__c,
    ssot__InternalOrganizationId__c
FROM ssot__AiAgentTagDefinitionAssociation__dlm
WHERE {{WHERE_CLAUSE}}
{{ORDER_BY}};


-- ============================================================================
-- EXAMPLE WHERE clauses (pass via where_clause=)
-- ============================================================================

-- Active associations for a specific agent
-- WHERE    → ssot__AiAgentApiName__c = '<agent_api_name>'
--            AND ssot__IsActive__c = true

-- Associations pointing at a set of tag definitions
-- WHERE    → ssot__AiAgentTagDefinitionId__c IN ('<def_id1>','<def_id2>',...)

-- Resolve by association id (from AiAgentTagAssociation)
-- WHERE    → ssot__Id__c IN ('<assoc_id1>','<assoc_id2>',...)

-- Tag definitions — the schema/vocabulary for tags applied to moments or sessions.
-- DMO: ssot__AiAgentTagDefinition__dlm
--
-- Placeholders (substituted by scripts/dc.py.load_sql):
--   WHERE_CLAUSE  — the filter expression, no "WHERE" keyword
--   ORDER_BY      — full "ORDER BY <col>" or empty string
--
-- Agent Optimization add-on DMO. Defines a tag type (name, data type,
-- input scope, engine type, source) that can then be applied via
-- AiAgentTag + AiAgentTagAssociation.

SELECT
    ssot__Id__c,
    ssot__DeveloperName__c,
    ssot__Name__c,
    ssot__Description__c,
    ssot__TagIdentifier__c,
    ssot__DataType__c,
    ssot__Status__c,
    ssot__VersionNumber__c,
    ssot__EngineType__c,
    ssot__SourceType__c,
    ssot__SourceTagReferenceName__c,
    ssot__InputScope__c,
    ssot__CreatedDate__c,
    ssot__InternalOrganizationId__c
FROM ssot__AiAgentTagDefinition__dlm
WHERE {{WHERE_CLAUSE}}
{{ORDER_BY}};


-- ============================================================================
-- EXAMPLE WHERE clauses (pass via where_clause=)
-- ============================================================================

-- All active tag definitions
--   Live orgs use 'Available' (not 'Active') — verified via live
--   describe. Group by ssot__Status__c to confirm per-org.
--   If 'Available' returns 0 rows, the waterfall retries unfiltered
--   (`ssot__Id__c IS NOT NULL`). Callers replicating this template
--   outside fetch_dc.py should apply the same fallback or expect
--   empty catalogs on orgs where Status uses a different enum.
-- WHERE    → ssot__Status__c = 'Available'
-- ORDER BY → ORDER BY ssot__Name__c

-- By developer name
-- WHERE    → ssot__DeveloperName__c = 'IntentCategory'

-- Definitions for a set of ids (resolves foreign keys from AiAgentTag)
-- WHERE    → ssot__Id__c IN ('<def_id1>','<def_id2>',...)

-- Tag instances — a specific tag value under a tag definition.
-- DMO: ssot__AiAgentTag__dlm
--
-- Placeholders (substituted by scripts/dc.py.load_sql):
--   WHERE_CLAUSE  — the filter expression, no "WHERE" keyword
--   ORDER_BY      — full "ORDER BY <col>" or empty string
--
-- Agent Optimization add-on DMO. A `AiAgentTag` belongs to a definition
-- (e.g. definition "IntentCategory" with tag value "Refund Request").
-- The tag is applied to a moment/session/interaction via AiAgentTagAssociation.

SELECT
    ssot__Id__c,
    ssot__AiAgentTagDefinitionId__c,
    ssot__Value__c,
    ssot__Description__c,
    ssot__OrderNumber__c,
    ssot__IsActive__c,
    ssot__IsFallback__c,
    ssot__CreatedDate__c,
    ssot__InternalOrganizationId__c
FROM ssot__AiAgentTag__dlm
WHERE {{WHERE_CLAUSE}}
{{ORDER_BY}};


-- ============================================================================
-- EXAMPLE WHERE clauses (pass via where_clause=)
-- ============================================================================

-- Tags for a specific definition (all allowed values)
-- WHERE    → ssot__AiAgentTagDefinitionId__c = '<def_id>'
--            AND ssot__IsActive__c = true
-- ORDER BY → ORDER BY ssot__OrderNumber__c

-- Resolve tags by id (from AiAgentTagAssociation.ssot__AiAgentTagId__c)
-- WHERE    → ssot__Id__c IN ('<tag_id1>','<tag_id2>',...)

-- Agent Platform Tracing spans — OpenTelemetry span tree per trace.
-- DMO: ssot__TelemetryTraceSpan__dlm
--
-- Placeholders (substituted by scripts/dc.py.load_sql):
--   WHERE_CLAUSE  — the filter expression, no "WHERE" keyword
--   ORDER_BY      — full "ORDER BY <col>" or empty string
--
-- Provisioned only when **Agent Platform Tracing** is enabled (Setup →
-- Einstein Audit, Analytics, and Monitoring Setup). Captures spans from
-- Apex, Flows, Prompt Builder, Invocable Actions, Planner, AI Gateway,
-- LLM Gateway, DC Query Federator.
--
-- Join to STDM: `ssot__TelemetryTrace__c = Interaction.ssot__TelemetryTraceId__c`.
-- Parent/child within a trace: `ssot__TelemetryParentSpanId__c = <another span>.ssot__Id__c`.

SELECT
    ssot__Id__c,
    ssot__TelemetryTrace__c,
    ssot__TelemetryParentSpanId__c,
    ssot__OperationName__c,
    ssot__ServiceName__c,
    ssot__SpanKind__c,
    ssot__StatusCode__c,
    ssot__StartDateTime__c,
    ssot__EndDateTime__c,
    ssot__DurationNumber__c,
    ssot__TelemetrySpanAttributeText__c,
    ssot__InternalOrganizationId__c
FROM ssot__TelemetryTraceSpan__dlm
WHERE {{WHERE_CLAUSE}}
{{ORDER_BY}};


-- ============================================================================
-- EXAMPLE WHERE clauses (pass via where_clause=)
-- ============================================================================

-- All spans for one trace (joins to Interaction.ssot__TelemetryTraceId__c)
-- WHERE    → ssot__TelemetryTrace__c = '<trace_id>'
-- ORDER BY → ORDER BY ssot__StartDateTime__c

-- All spans for a set of traces (whole session)
-- WHERE    → ssot__TelemetryTrace__c IN ('<trace_id1>','<trace_id2>',...)

-- Only root spans (no parent)
-- WHERE    → ssot__TelemetryTrace__c = '<trace_id>'
--            AND ssot__TelemetryParentSpanId__c = null

-- Only spans from a specific service
-- WHERE    → ssot__TelemetryTrace__c = '<trace_id>'
--            AND ssot__ServiceName__c = 'Atlas Reasoning Engine'

-- Slow spans — duration > 1s (nanoseconds in DurationNumber)
-- WHERE    → ssot__TelemetryTrace__c = '<trace_id>'
--            AND ssot__DurationNumber__c > 1000000000

# Shared module for the investigating-agentforce-d360 skill.
# Three siblings live in this package: paths, fs_guard, sql.

"""Shared CLI override helper for d360 entry scripts.

D360 has 5 independent entry scripts (``fetch_dc.py``, ``assemble_dc.py``,
``render_dc.py``, ``resolve_session.py``, ``discover_sessions.py``) — each
needs the same ``--data-dir`` / ``--cache-dir`` flags and the same
3-level namespace rebind. This module factors that duplication into one
helper.

Usage in each entry script::

    from _shared.cli_override import add_cli_flags, apply_overrides

    parser = argparse.ArgumentParser(...)
    parser.add_argument(...)
    add_cli_flags(parser)
    args = parser.parse_args()
    apply_overrides(args, caller_globals=globals())

The ``caller_globals=globals()`` parameter is the magic that lets us
rebind the entry script's own ``DATA_ROOT`` / ``CACHE_ROOT`` snapshot —
without it, only ``paths.X`` and ``config.X`` would update, and the
entry script's local ``DATA_ROOT`` would still point at the default.
"""
from __future__ import annotations

import argparse
from pathlib import Path

_SKILL_NAME = "investigating-agentforce-d360"


def add_cli_flags(parser: argparse.ArgumentParser) -> None:
    """Add --data-dir / --cache-dir flags to the given parser.

    Defaults are runtime-agnostic (``~/.vibe/...``); other
    runtimes (AFV OOTB, Codex, Cursor, OpenCode) override at invocation
    time.
    """
    parser.add_argument(
        "--data-dir",
        type=Path,
        default=None,
        help=(
            "Override data root (default: "
            "~/.vibe/data/investigating-agentforce-d360)."
        ),
    )
    parser.add_argument(
        "--cache-dir",
        type=Path,
        default=None,
        help=(
            "Override cache root (default: "
            "~/.vibe/cache/investigating-agentforce-d360)."
        ),
    )


def apply_overrides(args: argparse.Namespace, caller_globals: dict) -> None:
    """Apply --data-dir / --cache-dir overrides across all 3 namespace levels.

    Must be called BEFORE any pipeline code reads ``DATA_ROOT`` or
    ``CACHE_ROOT``.

    Levels rebound:
      1. ``paths.DATA_ROOT`` (the source of truth)
      2. ``config.DATA_ROOT`` (the re-export)
      3. The caller's own local ``DATA_ROOT`` (via ``caller_globals``)

    Why 3 levels? Python's ``from X import Y`` snapshots ``Y`` into the
    caller's local namespace at import time. Mutating ``X.Y`` later does
    NOT update the caller's local binding. Without the 3rd-level rebind,
    any function in the caller that references ``DATA_ROOT`` still sees
    the default path even after ``paths.DATA_ROOT`` was overridden.
    """
    if not (args.data_dir or args.cache_dir):
        return

    from _shared import runtime, paths
    import config

    if args.data_dir:
        runtime.set_data_root_override(args.data_dir)
        new_data = runtime.resolve_data_root(_SKILL_NAME)
        paths.DATA_ROOT = new_data
        config.DATA_ROOT = new_data
        # Rebind caller's local snapshot, if it captured DATA_ROOT.
        if "DATA_ROOT" in caller_globals:
            caller_globals["DATA_ROOT"] = new_data

    if args.cache_dir:
        runtime.set_cache_root_override(args.cache_dir)
        new_cache = runtime.resolve_cache_root(_SKILL_NAME)
        paths.CACHE_ROOT = new_cache
        if hasattr(config, "CACHE_ROOT"):
            config.CACHE_ROOT = new_cache
        if "CACHE_ROOT" in caller_globals:
            caller_globals["CACHE_ROOT"] = new_cache

#!/usr/bin/env python3
r"""Filesystem-safety and input-validation guard.

One script, six check types. Emits a STATUS=INVALID_INPUT RESULT block on
failure + tees to $ERROR_TEE on disk + exits 1.

Guiding principle: the agent MUST suffix every call with `|| exit 1` — Python
`sys.exit(1)` only terminates this subprocess, not the parent bash. A bare
call without `|| exit 1` silently continues past a failed guard, which is
the worst possible failure mode for a security check.

Check types:
    symlink     — path must NOT be a symlink (rejects pre-planted attacker bait)
    owned       — path must be owned by current UID (rejects foreign-owned dirs)
    uuid          — value must match ^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$
    org_id_15     — value must match ^[A-Za-z0-9]{15}$ (Salesforce org ID slice)
    api_name      — value must match ^[A-Za-z0-9_]+$ (Salesforce API identifier)
    api_version   — value must match ^v[0-9]+\.[0-9]+$ (e.g. v60.0)
    agent_version — value must match ^v[0-9]+$ (e.g. v5 — Agentforce agent version, no dot-minor)
    not_empty     — value must be non-empty string

Python-importable API:
    validate_api_name(value, label="...")      — raises ValidationError on bad input
    validate_api_version(value, label="...")   — raises ValidationError on bad input
    validate_agent_version(value, label="...") — raises ValidationError on bad input
    validate_org_id_15(value, label="...")     — raises ValidationError on bad input
    ValidationError                            — carries (label, reason)

The Python API is used by in-process callers (SOQL loader, path builders in
config.py) that need a raise-based boundary rather than the process-exit
CLI behavior. Regex is shared — do NOT duplicate API_NAME_RE / API_VERSION_RE.

Usage:
    python3 fs_guard.py <value> <label> <check> || exit 1

    # Input validation
    python3 fs_guard.py "$AGENT_API_NAME" agent_api_name api_name || exit 1
    python3 fs_guard.py "$ORG_ALIAS"      org_alias      not_empty || exit 1

    # Filesystem safety
    python3 fs_guard.py "$WORK_DIR"  WORK_DIR  symlink || exit 1
    python3 fs_guard.py "$WORK_DIR"  WORK_DIR  owned   || exit 1
    python3 fs_guard.py "$ORG_ID_15" ORG_ID_15 org_id_15 || exit 1

Inputs:
    argv[1]     value or path to check
    argv[2]     label (appears in ERROR_DETAIL; used to identify which guard tripped)
    argv[3]     check type (one of the 6 above)
    env         $ERROR_TEE (optional): path to disk-tee file. Defaults to
                $HOME/.vibe/data/investigating-agentforce-d360/_last_error_result.txt.
                Also reads $AGENT_API_NAME / $ORG_ID_18 / $ORG_ID_15 for
                RESULT-block context if set.

Outputs:
    on failure: STATUS=INVALID_INPUT RESULT block on stdout + tee to disk, exit 1
    on success: silent, exit 0
    on bad argv: exit 1 with a minimal ERROR_DETAIL ("fs_guard internal: ...")
"""
import os
import pathlib
import re
import sys

# Anchored \A...\Z (not ^...$) — Python's `$` matches before a trailing
# newline, so "00DTESTORG12345\n" passes ^...$ + .match(). Every call
# site below MUST use .fullmatch() for the same reason; .match() is
# unsafe even with \A...\Z anchors. Mirrors the pattern in
# _shared/paths.py SESSION_ID_RE.
UUID_RE = re.compile(r"\A[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}\Z")  # @rule-suppress starter-sec-002 — re.compile, not eval/exec
ORG_ID_15_RE = re.compile(r"\A[A-Za-z0-9]{15}\Z")  # @rule-suppress starter-sec-002 — re.compile, not eval/exec
API_NAME_RE = re.compile(r"\A[A-Za-z0-9_]+\Z")  # @rule-suppress starter-sec-002 — re.compile, not eval/exec
# P0-5: api_version check — matches `v60.0`, `v66.0`, etc. Used before any
# Path composition that embeds the api_version segment.
API_VERSION_RE = re.compile(r"\Av[0-9]+\.[0-9]+\Z")  # @rule-suppress starter-sec-002 — re.compile, not eval/exec
# Agent-version check — matches Agentforce version identifiers (`v1`, `v5`,
# `v12`). Deliberately stricter than api_name (rejects free-form names like
# `release_1`) and looser than api_version (rejects `v66.0` which is a REST
# API version, never an agent version). Lives on its own so callers that
# embed agent_version in a filesystem path get the tight regex, not the
# permissive api_name fallback.
AGENT_VERSION_RE = re.compile(r"\Av[0-9]+\Z")  # @rule-suppress starter-sec-002 — re.compile, not eval/exec
VALID_CHECKS = {"symlink", "owned", "uuid", "org_id_15", "api_name", "api_version", "agent_version", "not_empty"}


# -----------------------------------------------------------------------------
# Python-importable API (P0-1, P0-5)
# -----------------------------------------------------------------------------
# In-process callers (soql_loader.load_soql, config.build_*_dir) need a
# raise-based validation boundary, distinct from the CLI script's exit-1
# behavior. The regexes are shared with the CLI checks above — do NOT
# duplicate. If a regex changes, both surfaces update in lockstep.


class ValidationError(ValueError):
    """Raised by the Python-importable validators on bad input.

    Carries the label (which field failed) + reason. Callers (e.g.
    soql_loader.load_soql) convert this into `_unresolved[]` entries or
    RESULT-level INVALID_INPUT responses as appropriate.
    """

    def __init__(self, label: str, reason: str) -> None:
        self.label = label
        self.reason = reason
        super().__init__(f"{label}: {reason}")


def validate_api_name(value, label: str = "value") -> None:
    """Validate `value` against ^[A-Za-z0-9_]+$ (Salesforce API identifier).

    P0-1: used by soql_loader.load_soql at the substitution boundary to catch
    injection attempts in names pulled from Bot XML, Flow.Metadata, ApexClass
    SymbolTable, etc.

    P0-5: used by config.build_*_dir helpers for path components that must
    not contain `..`, `/`, or other traversal characters.

    Raises ValidationError on any of: None, non-string, empty, regex miss.
    """
    if value is None:
        raise ValidationError(label, "is None")
    if not isinstance(value, str):
        raise ValidationError(label, f"must be str, got {type(value).__name__}")
    if not value:
        raise ValidationError(label, "must not be empty")
    if not API_NAME_RE.fullmatch(value):
        # Preview first 20 chars so logs/_unresolved entries carry enough
        # context to debug, without dumping an unbounded attacker-controlled
        # string into output.
        preview = value[:20]
        raise ValidationError(
            label,
            f"does not match [A-Za-z0-9_]+ (preview={preview!r})",
        )


def validate_api_version(value, label: str = "api_version") -> None:
    """Validate `value` against ^v[0-9]+\\.[0-9]+$ (Salesforce API version).

    P0-5: used by config.build_*_dir helpers. api_version is returned by
    `sf org display` and later embedded in cache paths; reject anything that
    could escape the cache subtree.
    """
    if value is None:
        raise ValidationError(label, "is None")
    if not isinstance(value, str):
        raise ValidationError(label, f"must be str, got {type(value).__name__}")
    if not value:
        raise ValidationError(label, "must not be empty")
    if not API_VERSION_RE.fullmatch(value):
        preview = value[:20]
        raise ValidationError(
            label,
            f"does not match v<major>.<minor> (preview={preview!r})",
        )


def validate_agent_version(value, label: str = "agent_version") -> None:
    """Validate `value` against ^v[0-9]+$ (Agentforce agent version).

    Strictly `v<digits>` with no dot-minor — matches the shape Agentforce
    actually uses (`v1`, `v5`, `v12`). Rejects `release_1`, `v66.0`, `FOO`,
    and anything else that could silently slip past a permissive api_name
    check and land in a filesystem path.

    P0-5: used by path-builder helpers for the agent_version segment.
    """
    if value is None:
        raise ValidationError(label, "is None")
    if not isinstance(value, str):
        raise ValidationError(label, f"must be str, got {type(value).__name__}")
    if not value:
        raise ValidationError(label, "must not be empty")
    if not AGENT_VERSION_RE.fullmatch(value):
        preview = value[:20]
        raise ValidationError(
            label,
            f"does not match v<digits> (preview={preview!r})",
        )


def validate_org_id_15(value, label: str = "org_id_15") -> None:
    """Validate `value` against ^[A-Za-z0-9]{15}$ (Salesforce 15-char org ID).

    P0-5: stricter than validate_api_name — enforces exact 15 chars AND
    no underscores. `org_id_15` always comes from a Salesforce-generated
    field, never from free-form input.
    """
    if value is None:
        raise ValidationError(label, "is None")
    if not isinstance(value, str):
        raise ValidationError(label, f"must be str, got {type(value).__name__}")
    if not value:
        raise ValidationError(label, "must not be empty")
    if not ORG_ID_15_RE.fullmatch(value):
        preview = value[:20]
        raise ValidationError(
            label,
            f"must be exactly 15 alphanumeric chars (preview={preview!r})",
        )


def scrub(s: str) -> str:
    # Same rules as sanitize.py; duplicated locally so this script has no
    # intra-skill imports. Keeps the agent-script boundary clean.
    bad = set("`$\"\\\r\t\0\n")
    return "".join(c for c in (s or "") if c not in bad)


def emit_failure(reason: str, label: str) -> None:
    agent_api_name = scrub(os.environ.get("AGENT_API_NAME", ""))
    org_id_18 = scrub(os.environ.get("ORG_ID_18", ""))
    org_id_15 = scrub(os.environ.get("ORG_ID_15", ""))
    label_safe = scrub(label)
    reason_safe = scrub(reason)

    block = (
        "=== RESULT ===\n"
        "STATUS=INVALID_INPUT\n"
        f"ERROR_DETAIL={label_safe}: {reason_safe}\n"
        f"AGENT_API_NAME={agent_api_name}\n"
        f"ORG_ID_18={org_id_18}\n"
        f"ORG_ID_15={org_id_15}\n"
    )

    # Default ERROR_TEE lives under the skill-scoped data root. Runtime-agnostic
    # default mirrors runtime.resolve_data_root() in the sibling runtime module
    # (duplicated rather than imported because fs_guard.py is also invoked
    # standalone from SKILL.md bash, where the override hook isn't active).
    tee_default = str(
        pathlib.Path.home()
        / ".vibe"
        / "data"
        / "investigating-agentforce-d360"
        / "_last_error_result.txt"
    )
    tee_path = os.environ.get("ERROR_TEE") or tee_default
    try:
        p = pathlib.Path(tee_path)
        p.parent.mkdir(parents=True, exist_ok=True)
        p.write_text(block)
    except OSError:
        pass

    sys.stdout.write(block)
    sys.exit(1)


def check_symlink(value: str, label: str) -> None:
    if pathlib.Path(value).is_symlink():
        emit_failure(f"path is a symlink (refusing to follow): {value}", label)


def check_owned(value: str, label: str) -> None:
    p = pathlib.Path(value)
    if not p.exists():
        return
    try:
        st = p.stat()
    except OSError as e:
        emit_failure(f"stat failed: {e}", label)
        return
    if st.st_uid != os.getuid():
        emit_failure(f"foreign-owned (uid {st.st_uid} != current {os.getuid()}): {value}", label)


def check_uuid(value: str, label: str) -> None:
    if not UUID_RE.fullmatch(value):
        emit_failure("does not match UUID pattern (8-4-4-4-12 lowercase hex)", label)


def check_org_id_15(value: str, label: str) -> None:
    if not ORG_ID_15_RE.fullmatch(value):
        emit_failure("must be exactly 15 alphanumeric characters", label)


def check_api_name(value: str, label: str) -> None:
    if not API_NAME_RE.fullmatch(value):
        emit_failure("does not match [A-Za-z0-9_]+ (Salesforce API name rules)", label)


def check_api_version(value: str, label: str) -> None:
    # P0-5: api_version must be `vNN.N` — no slashes, no dots beyond the one,
    # no path-traversal sequences.
    if not API_VERSION_RE.fullmatch(value):
        emit_failure("does not match v<major>.<minor> (e.g. v60.0)", label)


def check_agent_version(value: str, label: str) -> None:
    # Agent version must be `v<digits>` — no dots, no slashes.
    if not AGENT_VERSION_RE.fullmatch(value):
        emit_failure("does not match v<digits> (e.g. v5)", label)


def check_not_empty(value: str, label: str) -> None:
    if not value:
        emit_failure("must not be empty", label)


CHECKS = {
    "symlink": check_symlink,
    "owned": check_owned,
    "uuid": check_uuid,
    "org_id_15": check_org_id_15,
    "api_name": check_api_name,
    "api_version": check_api_version,
    "agent_version": check_agent_version,
    "not_empty": check_not_empty,
}


def main() -> int:
    if len(sys.argv) != 4:
        sys.stdout.write(
            "=== RESULT ===\n"
            "STATUS=INVALID_INPUT\n"
            "ERROR_DETAIL=fs_guard internal: wrong argv count (need value, label, check)\n"
        )
        return 1
    value, label, check = sys.argv[1], sys.argv[2], sys.argv[3]

    for arg in (value, label, check):
        if any(c == "\0" or (ord(c) < 0x20 and c not in "\t\n") for c in arg):
            emit_failure("argv contains control characters", label or "argv")

    if check not in VALID_CHECKS:
        emit_failure(f"unknown check type '{check}' (valid: {', '.join(sorted(VALID_CHECKS))})", label)

    CHECKS[check](value, label)
    return 0


if __name__ == "__main__":
    sys.exit(main())

"""Canonical SQL-escaping helpers for investigating-agentforce-d360.

Single source of truth for DC-SQL string-literal escaping. Behavioral contract
is fixed — do not change the escape strategy without updating every caller and
its tests.
"""
from __future__ import annotations


def _escape_sql_literal(s: str) -> str:
    """Double single quotes per DC SQL escaping rule. Handles O'Brien →
    O''Brien, `'; DROP --` → `''; DROP --` (still harmless because it's
    wrapped in surrounding single quotes)."""
    return s.replace("'", "''")

"""Test bootstrap — adds sibling ``scripts/`` to sys.path.

Every ``test_*.py`` in this directory imports this module first
(as ``from . import _bootstrap``) so the sibling modules
(`config`, `storage`, `paths`, etc.) resolve.
"""
from __future__ import annotations

import sys
from pathlib import Path

# scripts/tests/_bootstrap.py → scripts/
_SCRIPTS_DIR = Path(__file__).resolve().parent.parent
if str(_SCRIPTS_DIR) not in sys.path:
    sys.path.insert(0, str(_SCRIPTS_DIR))