SKILL.md11.1 KBView on GitHub ---
name: cedar-reporting
description: Query the Cedar database for product usage analytics, customer health metrics, and org/user-level reporting. Use when asked about Salesforce update counts, chat message analysis, intelligence field coverage, CRM event metrics, agent execution stats, or any question about how a specific customer or rep is using Cedar.
---
# Cedar Reporting
Read-only SQL queries via `psql "$DATABASE_URL"`. Always ask for approval before running. Source env with `source .env 2>/dev/null` first.
---
## Key tables
| Table | What it contains |
|---|---|
| `"user"` | Users — join on `u.email` or `u.id` |
| `organizations` | Orgs — `o.name` |
| `agent_executions` | One row per agent run — `run_id`, `user_id`, `conversation_id`, `event_type`, `status` |
| `agent_tool_calls` | One row per tool call — `run_id`, `tool_name`, `arguments`, `result` |
| `chat_messages` | Chat messages — `role` ('user'/'assistant'), `content`, `chat_thread_id` |
| `chat_threads` | Chat threads — `user_id` |
| `crm_events` | All CRM events — `event_type`, `user_id`, `conversation_id` |
| `crm_meeting_events` | Meeting detail — `event_id`, `external_id`, `participants` |
| `crm_conversation_field_values` | Extracted field values — `field_id`, `is_list_field`, `source_user_id`, `org_id` |
| `org_aops` | Org-level AOP config — `organization_id`, `name`, `custom_field_definitions` |
| `org_agent_operating_procedures` | Legacy org intel config — `org_id`, `name`, `field_definitions` |
---
## Common queries
### Find user by email
```sql
SELECT id, name, email, organization_id FROM "user" WHERE email = '<email>';
```
### Most recent meeting event for a user
```sql
SELECT e.id, e.title, e.occurred_at, me.meeting_time, me.participants
FROM crm_events e
JOIN crm_meeting_events me ON me.event_id = e.id
JOIN "user" u ON u.id = e.user_id
WHERE u.email = '<email>'
ORDER BY e.occurred_at DESC LIMIT 1;
```
### Agent execution correlated with an event
```sql
SELECT ae.run_id, ae.status, ae.created_at, ae.completed_at
FROM agent_executions ae
WHERE ae.event_id = '<event_id>'
ORDER BY ae.created_at DESC;
```
### Check if same meeting exists for multiple users (deduplicate by external_id)
```sql
SELECT e.id, e.user_id, u.email, e.occurred_at, ae.run_id, ae.status
FROM crm_events e
JOIN crm_meeting_events me ON me.event_id = e.id
JOIN "user" u ON u.id = e.user_id
LEFT JOIN agent_executions ae ON ae.event_id = e.id
WHERE me.external_id = '<meeting_id>'
ORDER BY u.email;
```
### Tool calls for an execution
```sql
SELECT atc.tool_name, atc.arguments, atc.result, atc.created_at
FROM agent_tool_calls atc
WHERE atc.run_id = '<run_id>'
ORDER BY atc.created_at;
```
---
## Salesforce CRM update metrics
### Count successful SF updates (non-empty) per user
```sql
SELECT
u.email,
COUNT(*) as successful_sf_updates,
COUNT(DISTINCT ae.conversation_id) as unique_deals
FROM agent_tool_calls atc
JOIN agent_executions ae ON ae.run_id = atc.run_id
JOIN "user" u ON u.id = ae.user_id
WHERE u.email IN ('<email>', '<email>') -- or filter by org
AND atc.tool_name = 'update-external-crm-workflow'
AND (atc.result->>'success')::boolean = true
AND jsonb_array_length(atc.result->'updatedFields') > 0
GROUP BY u.email
ORDER BY successful_sf_updates DESC;
```
**Important**: Always check `jsonb_array_length(atc.result->'updatedFields') > 0` — `success: true` with an empty array means nothing was actually written.
### Per-deal update history (check for duplicates/loops)
```sql
SELECT atc.created_at, ae.run_id, atc.result->'updatedFields' as updated_fields
FROM agent_tool_calls atc
JOIN agent_executions ae ON ae.run_id = atc.run_id
WHERE ae.conversation_id = '<conversation_id>'
AND atc.tool_name = 'update-external-crm-workflow'
AND (atc.result->>'success')::boolean = true
AND jsonb_array_length(atc.result->'updatedFields') > 0
ORDER BY atc.created_at;
```
---
## Chat message analysis
### Get all user messages for a set of users
```sql
SELECT u.email, cm.content
FROM chat_messages cm
JOIN chat_threads ct ON ct.id = cm.chat_thread_id
JOIN "user" u ON u.id = ct.user_id
WHERE u.email IN ('<email>', '<email>')
AND cm.role = 'user'
AND cm.deleted_at IS NULL
ORDER BY u.email, cm.created_at DESC;
```
### Message count per user
```sql
SELECT u.email, COUNT(*) as message_count
FROM chat_messages cm
JOIN chat_threads ct ON ct.id = cm.chat_thread_id
JOIN "user" u ON u.id = ct.user_id
WHERE u.email IN ('<email>', '<email>')
AND cm.role = 'user' AND cm.deleted_at IS NULL
GROUP BY u.email;
```
---
## Sales intelligence report per rep
For each user, run an exhaustive search using the `sales-intelligence` skill filtering only for that user's ID and generate a concise report covering:
- How they have positioned against competitors
- How they have tackled pricing positioning
- What talk tracks or moves have advanced deals across stages
**Workflow:**
1. Look up the user's ID: `SELECT id FROM "user" WHERE email = '<email>'`
2. Load the `sales-intelligence` skill via `load-skill`
3. Call `search-intelligence` three times in parallel (one per category), passing `sourceUserId` and `topK: 20`:
- `fieldId: "_ii_competitive"`, query: `"competitive positioning, competitor mentions, how we handle competition"`
- `fieldId: "_ii_pricing"`, query: `"pricing positioning, pricing techniques, objections, anchoring, discounting"`
- `fieldId: "_ii_stage_move"`, query: `"stage progression, what moved deals forward, talk tracks that worked"`
4. Synthesize results into a concise report with one section per category, noting patterns, what landed, what didn't, and confidence signals (`extractionConfidence`, `outcomeStage`)
---
## Pre-pilot success criteria
When onboarding a new user or org, include a section that defines their success criteria in this format:
```
[Full Name]
Goal: [One sentence — what they want to feel/achieve in their workflow]
Use cases:
- [Specific workflow they want Cedar to handle]
- [Specific workflow they want Cedar to handle]
- [Specific workflow they want Cedar to handle]
Challenges:
- [Pain point driving this use case]
- [Pain point driving this use case]
- [Pain point driving this use case]
Target results:
- [Concrete outcome that would make this a success for them]
- [Concrete outcome that would make this a success for them]
- [Concrete outcome that would make this a success for them]
```
**Example:**
```
Will Kostrzewsky
Goal: Feel organized across his open pipeline so next steps are always clear and 1:1s with Thiago are easy to navigate.
Use cases:
- Clear next steps visibility across all open deals
- Easy follow-up execution so the ball is never in his court
- Pipeline snapshot for Thiago's 1:1 reviews
Challenges:
- Pipeline feels disorganized — hard to know where each deal stands at a glance
- Follow-ups fall through the cracks; time kills deals and there's no safety net
- Prepping for Thiago's pipeline reviews requires manual digging
Target results:
- Always know the next step on every deal without having to dig — organized pipeline at a glance
- Automated next-step reminders so deals never stall on his side
- Walk into any 1:1 with Thiago knowing exactly where every deal stands
```
---
## Org AOP configuration
### Check what AOPs exist for an org
```sql
SELECT oa.id, oa.name,
CASE WHEN oa.custom_field_definitions IS NULL THEN 'null'
WHEN oa.custom_field_definitions = '{}'::jsonb THEN 'empty'
ELSE (SELECT string_agg(k, ', ') FROM jsonb_object_keys(oa.custom_field_definitions) k) END as fields
FROM org_aops oa
JOIN organizations o ON o.id = oa.organization_id
WHERE o.name = 'OrgName'
ORDER BY oa.name;
```
### Check user-level AOP custom field definitions
```sql
SELECT aop.name, aop.custom_field_definitions
FROM agent_operating_procedures aop
JOIN "user" u ON u.id = aop.user_id
WHERE u.email = '<email>'
ORDER BY aop.name;
```
---
## Notes
- `org_aops.custom_field_definitions` is the active table for intelligence extraction (keyed by field_id)
- `org_agent_operating_procedures.field_definitions` is the legacy table (array format) — still written for backwards compat but not read by the extraction pipeline
- Intelligence fields (`_ii_*`) must have `type: 'list'` in `org_aops.custom_field_definitions` to accumulate — `type: 'text'` causes the extraction to skip them
- `org_aops.name` is stored in display case (e.g. `'Deals'`) — use `ilike` when matching against normalized names
---
## Report template
Use this structure when generating a full usage summary for an org:
```markdown
# Cedar Usage Summary — [Org Name]
*Report date: [Date]*
---
## Team at a Glance
| Rep | Response Time | Post-Meeting | Follow-up Timing | SF Updates | Deals | Emails Sent | Similarity |
|---|---|---|---|---|---|---|---|
| [Name] | [Xh Xm] | [Xh Xm] | [Xd Xh] | [n] / [n] deals | — | [n] | [X%] |
---
## [Rep Name] [emoji]
### Pre-Pilot Success Criteria
**Goal:** [One sentence — what they want to feel or achieve in their workflow]
**Use cases:**
- [Specific workflow Cedar handles for them]
- [Specific workflow Cedar handles for them]
- [Specific workflow Cedar handles for them]
**Challenges:**
- [Pain point — ideally in their words]
- [Pain point]
- [Pain point]
**Target results:**
- [Concrete outcome — ideally a quote or paraphrase]
- [Concrete outcome]
- [Concrete outcome]
---
### Cedar Stats
- **[n]** Salesforce field updates across **[n] deals**
- **[n] emails** sent · **[X%] similarity** ([X%] for post-meeting follow-ups)
- **How [Name] uses Cedar:** [Use case 1] · [Use case 2] · [Use case 3]
---
### Sales Intelligence Report
#### 1. 🥊 Competitive Positioning ([n] examples)
**vs. [Competitor] — [n] examples, [outcome summary]**
**[Frame Name]** *([landed / partially landed / deferred])*
[1–2 sentences on the positioning move and how it plays out]
> *"[Direct quote from call]"*
[What worked / what didn't / pattern to watch]
⚠️ **Pattern to fix:** [If applicable]
✅ **Key insight:** [If applicable]
**🏆 Best competitive line:**
> *"[Most effective quote]"*
---
#### 2. 💰 Pricing Positioning ([n] examples)
**Standard pricing:** $[X]/[unit]/month + $[X] platform fee
**✅ What's landing:**
| Technique | Outcome | Context |
|---|---|---|
| [Technique] | ✅ [Reaction] | [Brief context] |
**❌ What's failing:**
| Technique | Outcome | Why |
|---|---|---|
| [Technique] | ❌ [Reaction] | [Why it failed] |
**🏆 Best pricing move:**
> *"[Quote]"* → *"[Prospect reaction]"*
**Key insight:** [1–2 sentence takeaway]
---
#### 3. 🚀 Stage Progression Talk Tracks ([n] examples)
**✅ Moves that worked:**
**1. [Move name]** *([From stage → To stage], confidence: [X.XX])*
> *"[Quote that drove the move]"*
[What happened as a result]
**❌ Move that failed:**
**[Move name]** *([outcome])*
[What happened and why]
> **Lesson:** [Actionable takeaway]
---
#### 📊 Summary Scorecard
| Category | Signals | Top Strength | Key Gap |
|---|---|---|---|
| Competitive | [n] examples | [Strongest move] | [Biggest gap] |
| Pricing | [n] examples | [Strongest move] | [Biggest gap] |
| Stage Moves | [n] examples | [Strongest move] | [Biggest gap] |
**Key pattern:** [1–2 sentences on the rep's overall behavioral pattern]
---
*Generated by Cedar · [Date]*
```