BEL CRM Schema Write DB

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', '[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

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', '[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;
   

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)