BEL CRM Schema Write DB

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"')

Timestamp Handling:

-- Current timestamp
created_at = CURRENT_TIMESTAMP

-- Date truncation
DATE_TRUNC('quarter', CURRENT_DATE)

-- Interval calculations
event_date >= CURRENT_DATE - INTERVAL '30 days'

Get ID After Insert (Two-Step Pattern):

-- Step 1: INSERT (write_query) - NO RETURNING!
INSERT INTO person (name, email, created_at, updated_at)
VALUES ('John Doe', 'john@example.com', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

-- Step 2: GET ID (read_query) - separate call
SELECT id FROM person WHERE email = 'john@example.com' 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

1. Always Cast JSONB: When inserting JSONB data, cast with ::jsonb:

   '["tag1", "tag2"]'::jsonb
   

2. 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', 'john@example.com', now(), now());
   
   -- Step 2: GET ID (read_query)
   SELECT id FROM person WHERE email = 'john@example.com' ORDER BY created_at DESC LIMIT 1;
   

3. 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)
   

4. Handle NULL Values: Use IS NULL / IS NOT NULL for checking null values, not = NULL

5. JSONB Tag Searches: Use ? for single tag, ?| for any of multiple tags, ?& for all tags

6. Update Timestamps: Always set updated_at = CURRENT_TIMESTAMP when updating records

7. Pipeline Calculations: Calculate weighted pipeline value:

   SUM(value_eur * probability / 100.0) as weighted_value
   

8. See bel-crm-sql-rules: For complete list of SQL limitations and correct patterns.

Resources

references/schema.md

Complete database schema documentation including:

• All table structures
• Column definitions and data types
• Foreign key relationships
• JSONB field examples
• Index recommendations

references/sql_examples.md

Comprehensive PostgreSQL query examples:

• INSERT operations for all tables
• UPDATE patterns including JSONB manipulation
• SELECT queries with JOINs and aggregations
• Date/time queries
• Complex reporting queries
• Common patterns (upsert, bulk insert, conditional updates)