This skill should be used when working with the CRM PostgreSQL database for sales, contacts, companies, and opportunities. Use this skill when the user asks to insert, update, query, or analyze data in the CRM database (may be called crm or db), or when SQL queries need to be created for company_site, person, sales_opportunity, or event tables. This skill provides comprehensive schema knowledge and PostgreSQL-specific SQL examples tailored to the CRM data model.
majiayu000200 星標2026年1月22日
職業
分類
SQL 數據庫
技能內容
CRITICAL: Read SQL Rules First!
Before writing ANY SQL, consult the bel-crm-sql-rules skill!
Key limitations of the PostgreSQL MCP server:
NO RETURNING clause - will cause syntax error
NO ON CONFLICT - will fail (also: no UNIQUE constraints on name columns!)
Use read_query to get IDs after INSERT
Overview
This skill provides comprehensive knowledge of the CRM PostgreSQL database schema and generates correct, PostgreSQL-specific SQL queries for managing companies, contacts, sales opportunities, and activity events.
The CRM database uses a relational model with JSONB fields for flexible tagging and metadata, temporal tracking with timestamps, and foreign key relationships between companies, people, opportunities, and events.
When to Use This Skill
Use this skill when:
Creating INSERT statements for new CRM records
相關技能
Writing UPDATE queries to modify existing data
Constructing SELECT queries with JOINs across CRM entities
Working with JSONB fields (tags, metadata)
Querying relationships between companies, contacts, and opportunities
Building reports from sales pipeline data
Tracking activities and events
The user mentions database operations on: company_site, person, sales_opportunity, or event tables
The user may SHORTEN that by: company_site (c:), person (p:), sales_opportunity (so:), event (e:)
Core Capabilities
1. Schema Knowledge
Load references/schema.md to understand:
Complete table structures with all columns and data types
Foreign key relationships between entities
JSONB field usage for tags and metadata
Timestamp fields for created_at/updated_at tracking
Entity relationship diagram
Use schema knowledge to:
Validate column names and data types before generating SQL
Understand which tables to JOIN for specific queries
Handle optional vs. required fields correctly
Use appropriate PostgreSQL data types (JSONB, NUMERIC, TIMESTAMP)
2. SQL Query Generation
Load references/sql_examples.md for PostgreSQL-specific patterns:
INSERT Operations:
Insert new companies with full address and metadata
Create person records linked to companies
Add sales opportunities with probability tracking
Record events with JSONB metadata
UPDATE Operations:
Update company information and annual revenue
Change person job titles and departments
Update opportunity status and close dates
Add/remove JSONB tags
Modify nested JSONB metadata
SELECT Queries:
Get companies with contact counts
Fetch person details with company information
Query open opportunities with details
Calculate pipeline value by status
Search by JSONB tags using ?, ?|, ?& operators
Get activity timelines for opportunities
Complex multi-table JOINs
3. PostgreSQL-Specific Features
JSONB Operations:
-- Check if tag exists
WHERE tags ? 'enterprise'
-- Check if any tag exists
WHERE tags ?| ARRAY['prospect', 'customer']
-- Add a tag
SET tags = tags || '["new-tag"]'::jsonb
-- Remove a tag
SET tags = tags - 'old-tag'
-- Update nested metadata
SET metadata = jsonb_set(metadata, '{outcome}', '"positive"')
-- Step 1: INSERT (write_query) - NO RETURNING!
INSERT INTO person (name, email, created_at, updated_at)
VALUES ('John Doe', '[email protected]', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
-- Step 2: GET ID (read_query) - separate call
SELECT id FROM person WHERE email = '[email protected]' ORDER BY created_at DESC LIMIT 1;
Workflow
Step 1: Understand the Request
Identify what the user wants to accomplish:
Which table(s) are involved?
Is this an INSERT, UPDATE, SELECT, or DELETE?
What relationships need to be considered?
Are JSONB fields involved?
Step 2: Load Schema Reference
If unfamiliar with the table structure, read references/schema.md to understand:
Column names and data types
Required vs. optional fields
Foreign key relationships
JSONB field structures
Step 3: Load SQL Examples
Read references/sql_examples.md to find similar query patterns:
Look for analogous operations in the examples
Adapt the pattern to the specific request
Use PostgreSQL-specific syntax
Step 4: Generate the SQL
Create the SQL query using:
Correct PostgreSQL syntax (see bel-crm-sql-rules for limitations!)
Proper data types (especially JSONB casting with ::jsonb)
NO RETURNING clauses - use separate read_query to get IDs
NO ON CONFLICT - check existence first, then INSERT or UPDATE
Appropriate JOINs when querying multiple tables
JSONB operators for tag/metadata queries
Step 5: Explain the Query
Provide context for the user:
What the query does
Which tables are involved
Any assumptions made
Expected output format
Common Patterns
Creating a Complete Company Record
When adding a new company, include:
Required: name
Optional but recommended: address fields, industry, website
Tags as JSONB array: '["prospect", "technology"]'::jsonb
Do NOT use RETURNING - query for ID separately if needed
Linking People to Companies
When adding a person:
Set company_site_id to link to company
Include job_title and department for context
Add tags like "decision-maker", "technical", "champion"
Query for ID after INSERT if you need to confirm
Tracking Sales Opportunities
When creating opportunities:
Link to both person_id and company_site_id
Set realistic probability (0-100)
Use common status values: 'open', 'qualified', 'proposal', 'won', 'lost'
Include expected_close_date
Add descriptive tags for filtering
Recording Events
When logging activities:
Set appropriate type: 'email', 'call', 'meeting', 'note'
Link to person_id, company_site_id, or opportunity_id as appropriate
Use metadata JSONB for structured details (duration, outcome, next_action)
Set event_date to when activity occurred, not when recorded
Querying with JOINs
When fetching related data:
Use LEFT JOIN to include records without relationships
Use INNER JOIN to filter to only records with relationships
Include relevant columns from joined tables
Consider using subqueries for aggregations (COUNT, SUM)
Tips
Always Cast JSONB: When inserting JSONB data, cast with ::jsonb:
'["tag1", "tag2"]'::jsonb
NEVER Use RETURNING: The PostgreSQL MCP server does not support RETURNING clauses. To get the ID after insert:
-- Step 1: INSERT (write_query)
INSERT INTO person (name, email, created_at, updated_at) VALUES ('John', '[email protected]', now(), now());
-- Step 2: GET ID (read_query)
SELECT id FROM person WHERE email = '[email protected]' ORDER BY created_at DESC LIMIT 1;
NEVER Use ON CONFLICT: The MCP server doesn't support upserts. Check existence first:
-- Step 1: Check (read_query)
SELECT id FROM company_site WHERE name ILIKE '%Acme%' LIMIT 1;
-- Step 2: INSERT if not found, UPDATE if found (write_query)
Handle NULL Values: Use IS NULL / IS NOT NULL for checking null values, not = NULL
JSONB Tag Searches: Use ? for single tag, ?| for any of multiple tags, ?& for all tags
Update Timestamps: Always set updated_at = CURRENT_TIMESTAMP when updating records