BEL CRM SQL Rules

CRITICAL: PostgreSQL MCP Server Limitations

The mcp__postgresql__ tools have specific limitations. Violating these rules causes SQL errors.

FORBIDDEN SQL Patterns (WILL FAIL)

1. RETURNING Clause - FORBIDDEN

-- FORBIDDEN - WILL FAIL with syntax error
INSERT INTO person (name, email) VALUES ('John', 'john@example.com') RETURNING id;

-- FORBIDDEN - WILL FAIL
UPDATE company_site SET name = 'New Name' WHERE id = 1 RETURNING *;

-- FORBIDDEN - WILL FAIL
DELETE FROM event WHERE id = 5 RETURNING id;

Why: The write_query tool parses SQL and rejects RETURNING clauses.

2. ON CONFLICT (UPSERT) - FORBIDDEN

-- FORBIDDEN - WILL FAIL with "Only INSERT, UPDATE, or DELETE operations are allowed"
INSERT INTO company_site (name) VALUES ('Acme')
ON CONFLICT (name) DO UPDATE SET updated_at = CURRENT_TIMESTAMP;

-- FORBIDDEN - Even if column HAD a unique constraint
INSERT INTO person (email) VALUES ('test@example.com')
ON CONFLICT (email) DO NOTHING;

Why: The write_query tool does not support ON CONFLICT syntax.

Additional Note: The CRM tables do NOT have UNIQUE constraints on name columns anyway!

company_site.name is NOT unique
person.name is NOT unique
sales_opportunity.title is NOT unique

3. Multiple Statements - FORBIDDEN

-- FORBIDDEN - WILL FAIL
INSERT INTO company_site (name) VALUES ('A'); INSERT INTO company_site (name) VALUES ('B');

Why: Execute one statement per tool call.

4. Transaction Commands - FORBIDDEN

-- FORBIDDEN
BEGIN; INSERT INTO...; COMMIT;

CORRECT SQL Patterns (USE THESE)

Pattern 1: Simple INSERT (No RETURNING)

-- CORRECT - Simple INSERT
INSERT INTO company_site (name, address_city, created_at, updated_at)
VALUES ('Neue Firma GmbH', 'Berlin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

To get the ID after insert:

-- CORRECT - Query for the ID in a SEPARATE read_query call
SELECT id FROM company_site WHERE name = 'Neue Firma GmbH' ORDER BY created_at DESC LIMIT 1;

Pattern 2: Check-Then-Insert (Instead of UPSERT)

Step 1: Check if exists (read_query)

SELECT id, name FROM company_site WHERE name ILIKE '%Acme%' LIMIT 1;

Step 2a: If found - UPDATE (write_query)

UPDATE company_site SET updated_at = CURRENT_TIMESTAMP, notes = 'Updated info' WHERE id = 5;

Step 2b: If not found - INSERT (write_query)

INSERT INTO company_site (name, address_city, created_at, updated_at)
VALUES ('Acme GmbH', 'Munich', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

Pattern 3: Simple UPDATE

-- CORRECT
UPDATE person SET job_title = 'CEO', updated_at = CURRENT_TIMESTAMP WHERE id = 42;

Pattern 4: Simple DELETE

-- CORRECT
DELETE FROM event WHERE id = 123;

Pattern 5: Get ID After Insert

Two-step process:

-- Step 1: INSERT (write_query)
INSERT INTO person (name, email, company_site_id, created_at, updated_at)
VALUES ('Max Mustermann', 'max@example.com', 5, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

-- Step 2: GET ID (read_query) - execute AFTER insert succeeds
SELECT id FROM person WHERE email = 'max@example.com' ORDER BY created_at DESC LIMIT 1;

Tool Selection Guide

| Operation | Tool | Notes | |-----------|------|-------| | SELECT | read_query | All SELECT statements | | INSERT | write_query | No RETURNING, no ON CONFLICT | | UPDATE | write_query | No RETURNING | | DELETE | write_query | No RETURNING | | Get ID after INSERT | read_query | Separate call after INSERT |

Common Mistakes and Fixes

Mistake 1: Using RETURNING to get ID

-- WRONG
INSERT INTO person (name) VALUES ('John') RETURNING id;

Fix:

-- Step 1: write_query
INSERT INTO person (name, created_at, updated_at) VALUES ('John', now(), now());

-- Step 2: read_query
SELECT id FROM person WHERE name = 'John' ORDER BY created_at DESC LIMIT 1;

Mistake 2: Using ON CONFLICT for upsert

-- WRONG
INSERT INTO company_site (name) VALUES ('Test')
ON CONFLICT (name) DO UPDATE SET updated_at = now();