Bel Crm DB

CRITICAL: SQL Limitations - READ FIRST!

Before writing ANY SQL, know these PostgreSQL MCP server limitations:

Example - CORRECT pattern for INSERT:

-- Step 1: write_query (NO RETURNING!)
INSERT INTO company_site (name, created_at, updated_at)
VALUES ('Acme GmbH', now(), now());

-- Step 2: read_query (get ID if needed)
SELECT id FROM company_site WHERE name = 'Acme GmbH' ORDER BY created_at DESC LIMIT 1;

For complete SQL rules, see the bel-crm-sql-rules skill.

---

You will probably only use those tools from postgresql mcp server:

• mcp__postgresql__read_query,
• mcp__postgresql__write_query

This is the SCHEMA you will work on:

Hier ist das vollständige Datenbankschema:

📊 Datenbank-Schema Übersicht

Die Datenbank enthält 6 Tabellen für ein CRM-System:

---

1. adressen (Legacy-Adresstabelle)

Persönliche Kontaktdaten (deutschsprachig):

---

1. company_site (Unternehmensstandorte)

Firmeninformationen und Standorte:

---

1. person (Kontaktpersonen)

Ansprechpartner in Unternehmen:

---

1. sales_opportunity (Verkaufschancen)

Sales-Pipeline und Opportunities:

---

1. event (Aktivitäten/​Events)

Aktivitätsverlauf (Meetings, Calls, etc.):

---

1. data_files (Dateien & E-Mail-Anhänge)

Speichert Dateien (PDFs, Bilder, Office-Dokumente, E-Mail-Anhänge) mit Base64-Encoding:

Beispiel - Komplette E-Mail speichern (mit Body):

INSERT INTO data_files (
    person_id, filename, file_type, mime_type,
    source, source_email_id, email_metadata,
    email_body_text, email_body_html,
    extracted_text, extraction_method, extraction_status
) VALUES (
    1,
    'Re: Contract Draft',  -- Subject als filename
    'email',
    'message/​rfc822',
    'email_message',
    'AAMkAGI2TG93AAA=',
    '{
      "subject": "Re: Contract Draft",
      "from": {"name": "John Doe", "email": "[email protected]"},
      "to": [{"email": "[email protected]"}],
      "cc": [{"email": "[email protected]"}],
      "received_at": "2025-01-15T10:30:00Z",
      "importance": "high",
      "hasAttachments": true,
      "conversationId": "AAQkAGI2..."
    }'::jsonb,
    'Hallo, anbei der überarbeitete Vertragsentwurf...',  -- Plain text
    '<html><body><p>Hallo, anbei der überarbeitete Vertragsentwurf...</​p></​body></​html>',
    'Hallo, anbei der überarbeitete Vertragsentwurf...',  -- Für Suche
    'email_body',
    'completed'
) RETURNING id, filename;

Beispiel - E-Mail-Anhang speichern (verknüpft mit E-Mail):

-- Erst E-Mail speichern, dann Anhänge mit gleicher source_email_id
INSERT INTO data_files (
    person_id, filename, file_type, mime_type, file_size_bytes,
    source, source_email_id, file_data, email_metadata
) VALUES (
    1, 'contract.pdf', 'pdf', 'application/​pdf', 245678,
    'email_attachment', 'AAMkAGI2TG93AAA=',  -- Gleiche ID wie E-Mail!
    '<base64-encoded-content>',
    '{"subject": "Re: Contract Draft", "from": {"email": "[email protected]"}}'::jsonb
) RETURNING id, filename;

Alle Dateien einer E-Mail finden (E-Mail + Anhänge):

SELECT id, filename, file_type, source, file_size_bytes
FROM data_files
WHERE source_email_id = 'AAMkAGI2TG93AAA='
ORDER BY file_type = 'email' DESC, filename;

Beispiel - Datei aus _DATA_FROM_USER hochladen:

INSERT INTO data_files (
    company_site_id, filename, file_type, mime_type, file_size_bytes,
    source, source_path, file_data
) VALUES (
    5, 'proposal.pdf', 'pdf', 'application/​pdf', 512000,
    'user_upload', '_DATA_FROM_USER/​proposal.pdf',
    '<base64-encoded-content>'
) RETURNING id, filename;

Volltextsuche in extrahiertem Text:

SELECT id, filename, person_id, company_site_id
FROM data_files
WHERE to_tsvector('german', extracted_text) @@ to_tsquery('german', 'Vertrag & Angebot');

---

1. schema_migrations (System-Tabelle)

Datenbank-Migrationen:

---

🔗 Beziehungen (Foreign Keys)

company_site (1) ──┬── (N) person ├── (N) sales_opportunity ├── (N) event └── (N) data_files

person (1) ────────┬── (N) sales_opportunity ├── (N) event └── (N) data_files

sales_opportunity (1) ─┬── (N) event └── (N) data_files

event (1) ──────────── (N) data_files

---

⚠️ WICHTIG: KEINE UNIQUE CONSTRAINTS AUF name!

Die Tabellen haben KEINE UNIQUE-Constraints auf name:

• company_site.name ist NICHT unique
• person.name ist NICHT unique
• sales_opportunity.title ist NICHT unique

NIEMALS ON CONFLICT (name) verwenden! Das führt zu Fehlern!

Stattdessen:

1. ERST prüfen ob Eintrag existiert: SELECT id FROM company_site WHERE name ILIKE '%..%'
2. DANN entweder UPDATE (wenn gefunden) oder INSERT (wenn nicht gefunden)

Beispiel - Firma erstellen oder finden:

-- SCHRITT 1: Prüfen ob existiert
SELECT id, name FROM company_site WHERE name ILIKE '%Kölner Stadt%' LIMIT 1;

-- SCHRITT 2a: Falls gefunden → UPDATE
UPDATE company_site SET updated_at = CURRENT_TIMESTAMP, notes = 'Updated...' WHERE id = <gefundene_id>;

-- SCHRITT 2b: Falls nicht gefunden → INSERT
INSERT INTO company_site (name, address_city, created_at, updated_at)
VALUES ('Neue Firma GmbH', 'Berlin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
RETURNING id;

---

💡 Besonderheiten

• JSONB-Felder: tags, metadata, email_metadata für flexible Erweiterungen
• Timestamps: Auto-Update via CURRENT_TIMESTAMP
• Dual-System: Alte adressen-Tabelle + neues CRM-Schema
• Multi-Entity Events: Events können mit Person, Firma ODER Opportunity verknüpft sein
• Dateispeicherung: Base64-kodiert in TEXT-Feld (max 100MB)
• Volltextsuche: GIN-Index auf extracted_text für deutsche Suche
• Duplikat-Erkennung: SHA-256 Hash auf file_hash

---

📎 Datei-Workflows

E-Mail-Anhänge extrahieren (MS365)

1. E-Mails suchen:

   mcp__ms365__list-mail-messages mit filter oder search
   

2. Anhänge auflisten:

   mcp__ms365__list-mail-attachments(messageId)
   

3. Anhang-Inhalt holen (bereits Base64!):

   mcp__ms365__get-mail-attachment(messageId, attachmentId)
   → contentBytes ist bereits Base64-kodiert
   

4. In CRM speichern:

   INSERT INTO data_files (person_id, filename, file_type, source, file_data, email_metadata)
   VALUES (...) RETURNING id;
   

User-Upload aus _DATA_FROM_USER

1. Datei lesen und Base64-kodieren (in Python/​Node)
2. SHA-256 Hash berechnen für Duplikat-Check
3. In CRM mit source = 'user_upload' speichern

Text-Extraktion