SKILL.md8.6 KBView on GitHub ---
name: query-db
description: Query the Cedar database for information. Use when the user wants to inspect data, debug issues, understand events/executions/tool calls, or run read-only SQL. Explains schema division and the events/agent-executions/tool-calls infrastructure.
---
# Query Cedar Database
Query the database for information using psql. **Read-only only** – never modify data. Always ask for approval before running SQL commands.
## Schema Location
The schema lives in `apps/server/src/db/` and is composed of several files:
| File | Purpose |
| --------------------- | --------------------------------------------------------------------------------------------- |
| `schema.ts` | Main entry – orgs, users, connections, email snippets. Re-exports from other schema files. |
| `aop-schema.ts` | Agent Operating Procedures (AOPs), **agent_executions**, **agent_tool_calls**, **user_tasks** |
| `crm-schema.ts` | CRM – conversations, **crm_events** (polymorphic), type-specific event tables |
| `analytics-schema.ts` | `analytics_draft_actions` (draft lifecycle, links to agent_executions via runId) |
| `tracking-schema.ts` | Email tracking events |
| `chat-schema.ts` | Chat-related tables |
| `kb-schema.ts` | Knowledge base |
Connection string: check env vars. For local psql use `DATABASE_URL` .
---
## Events Table Infrastructure
Events use a **polymorphic pattern**: one base table + type-specific extension tables.
### Base Table: `crm_events`
- **id** (uuid, PK)
- **user_id**, **conversation_id**
- **event_type** – one of: `'email' | 'slack_message' | 'meeting' | 'call' | 'note' | 'task' | 'custom' | 'external_crm' | 'calendar'`
- **title**, **direction**, **summary**, **is_significant**
- **occurred_at**, **created_at**, **updated_at**
### Type-Specific Tables (1:1 with crm_events via event_id)
| event_type | Table | Key fields |
| --------------- | ------------------------- | -------------------------------------------------------------------------------------- |
| `email` | `crm_email_events` | thread_id, message_id, subject, from_email, full_content_key, email_classification |
| `slack_message` | `crm_slack_events` | slack_workspace_id, slack_channel_id, slack_batch_id, messages (JSONB), sender_emails |
| `meeting` | `crm_meeting_events` | external_id, meeting_url, meeting_time, participants, transcription_key, recording_url |
| `call` | `crm_call_events` | phone_number, duration_seconds |
| `note` | `crm_note_events` | content, note_type |
| `custom` | `crm_custom_events` | custom_event_type, custom_fields |
| `calendar` | `crm_calendar_events` | google_event_id, start_time, end_time, attendees |
**Join pattern:** `crm_events e LEFT JOIN crm_email_events ee ON ee.event_id = e.id` (and similarly for other types).
### External CRM updates (canonical source)
External CRM sync snapshots are written to `crm_conversation_updates`, not `crm_events`.
Use this table for deals pipeline checks and latest CRM state comparisons.
```sql
SELECT
ccu.conversation_id,
ccu.occurred_at,
COALESCE(ccu.external_provider, ccu.external_crm_data->>'provider') AS provider,
COALESCE(ccu.external_deal_id, ccu.external_crm_data->>'dealId') AS deal_id,
ccu.external_crm_data->'dealData' AS deal_data
FROM crm_conversation_updates ccu
WHERE ccu.user_id = '<user_id>'
AND ccu.source = 'external'
AND ccu.action_type = 'external_crm_sync'
ORDER BY ccu.occurred_at DESC
LIMIT 50;
```
---
## Agent Executions & Tool Calls
### `agent_executions`
One row per agent run. Primary key is `run_id` (text).
| Column | Description |
| -------------------------------------- | ------------------------------------------------------ | ----------------- | ------------- | ------------------- | -------------- | ---------------------- | ------------------ | ---------------- | --------- |
| run_id | PK, unique per execution |
| user_id | Owner |
| aop_id | Agent Operating Procedure used (nullable) |
| event_id | FK → crm_events.id – the event that triggered this run |
| conversation_id | FK → crm_conversations.id |
| status | `'pending' | 'executing' | 'completed' | 'canceled' | 'final' | 'failed'` |
| event_type | Trigger type: `'email' | 'meeting' | 'slack' | 'external_crm' | 'label_sync'` |
| source | How it was triggered: `'pub-sub' | 'background-sync' | 'client-sync' | 'client-email-send' | 'initial-sync' | 'conversation-refresh' | 'execute-task-now' | 'scheduled-task' | 'replay'` |
| prompt, summary, output | Agent input/output |
| scheduled_for, scheduled_by_run_id | For deferred runs |
| task_id | FK → user_tasks.id (task-centric model) |
| created_at, completed_at, cancelled_at | Timestamps |
### `agent_tool_calls`
One row per tool invocation. FK: `run_id` → `agent_executions.run_id`.
| Column | Description |
| ---------- | ---------------------------------------------------------------------------------------- |
| id | PK |
| run_id | FK → agent_executions |
| tool_name | e.g. `'draft-email'`, `'update-external-crm-workflow'`, `'assign-event-to-conversation'` |
| arguments | JSONB – input to the tool |
| result | JSONB – tool output (e.g. `{ success: true, ... }`) |
| created_at | When the tool ran |
### Flow
1. Event (email/meeting/slack) is created → `crm_events` + type-specific table.
2. Agent run is started → `agent_executions` row with `event_id`, `conversation_id`, `status = 'executing'`.
3. Agent calls tools → `agent_tool_calls` rows with `run_id`.
4. Run finishes → `agent_executions.status` → `'completed'` or `'failed'`, `output` populated.
### Useful Queries
```sql
-- Executions for a conversation
SELECT ae.run_id, ae.status, ae.source, ae.event_type, ae.created_at
FROM agent_executions ae
WHERE ae.conversation_id = '<uuid>'
ORDER BY ae.created_at DESC;
-- Tool calls for a run
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;
-- Executions with their triggering event
SELECT ae.run_id, ae.status, e.event_type, e.title, e.occurred_at
FROM agent_executions ae
JOIN crm_events e ON e.id = ae.event_id
WHERE ae.user_id = '<user_id>'
ORDER BY ae.created_at DESC;
```
---
## Related Tables
- **user_tasks** – Tasks (email/Slack drafts, etc.). `creation_run_id` → agent_executions; agent_executions.task_id → user_tasks.
- **analytics_draft_actions** – Draft lifecycle; `run_id` → agent_executions.
- **agent_operating_procedures** – AOP definitions; agent_executions.aop_id → aops.
---
## Rules
1. **Read-only** – never INSERT, UPDATE, DELETE.
2. **Ask before running** – show the planned SQL and get approval.
3. **Use schema files** – refer to `apps/server/src/db/` for exact column names and types.