BEL Insert File to CRM and Link It

Overview

This skill provides a streamlined workflow for inserting files into the CRM database's data_files table. It handles all technical details including file encoding, hash calculation, MIME type detection, and database insertion through a robust Python script.

The skill is designed to avoid polluting the agent's context window by executing file operations through an external script rather than inline code generation.

When to Use This Skill

Use this skill when:

• The user asks to insert/upload/save a file to the CRM database
• The user wants to link a file (image, PDF, Excel, document) to a company, person, event, or opportunity
• The user mentions "save to CRM", "upload to database", "store in CRM"
• The user provides files and asks to associate them with CRM entities
• The user mentions company_site, person, event, or sales_opportunity with file operations

Core Capabilities

1. Automatic File Processing

The scripts/insert_file_to_crm.py script handles:

• Base64 encoding: Converts binary file data to text for database storage
• SHA256 hashing: Calculates integrity hash for deduplication and verification
• MIME type detection: Automatically determines file type from extension
• File type categorization: Maps MIME types to categories (image, document, spreadsheet, other)
• SQL generation: Creates properly formatted INSERT statements
• Database execution: Executes SQL through psql command

2. Supported File Types

Images: .jpg, .jpeg, .png, .gif Documents: .pdf, .docx, .doc, .txt Spreadsheets: .xlsx, .xls, .csv Other: Any binary file (handled as 'application/octet-stream')

3. Flexible Linking

Files can be linked to one or more CRM entities:

• Companies (company_site_id)
• People (person_id)
• Events (event_id)
• Sales Opportunities (sales_opportunity_id)

At least one link must be specified when inserting a file.

Workflow

Step 1: Identify the File and Link

Determine:

• File path: Where is the file located?
• Link target: Which CRM entity should the file be linked to?
  • Company ID (most common)
  • Person ID
  • Event ID
  • Opportunity ID

Step 2: Gather Optional Metadata

Ask the user or infer:

• Description: Human-readable description of the file
• Tags: Comma-separated tags for categorization (e.g., "contract,signed" or "workshop,photo")

If the user doesn't provide these, make reasonable inferences:

• Description: "Workshop photo" for event images, "CRM data spreadsheet" for Excel files
• Tags: Derive from file content or context

Step 3: Execute the Script

Run scripts/insert_file_to_crm.py with appropriate arguments:

python3 scripts/insert_file_to_crm.py <file_path> \
    --company-id <id> \
    --description "<description>" \
    --tags "<tag1>,<tag2>,<tag3>"

Required arguments:

• file_path: Path to the file to insert
• At least one of: --company-id, --person-id, --event-id, --opportunity-id

Optional arguments:

• --description: Description of the file
• --tags: Comma-separated tags
• --dry-run: Generate SQL without executing (for testing)

Step 4: Verify Success

The script outputs:

• ✅ Success message with inserted file ID
• ❌ Error message if insertion fails

Confirm with the user that the file was inserted successfully.

Examples

Example 1: Insert Workshop Photo for Company

python3 scripts/insert_file_to_crm.py \
    DATA_FROM_USER/workshop_photo.jpg \
    --company-id 1 \
    --description "Workshop/Seminar-Foto: Diskussionsrunde mit mehreren Teilnehmern" \
    --tags "workshop,meeting,photo"

Example 2: Insert Excel File with CRM Data

python3 scripts/insert_file_to_crm.py \
    DATA_FROM_USER/leads.xlsx \
    --company-id 1 \
    --description "CRM Lead-Management Datenbank mit 139 Kontakten" \
    --tags "crm,leads,contacts"

Example 3: Insert Contract for Person

python3 scripts/insert_file_to_crm.py \
    contracts/signed_contract.pdf \
    --person-id 42 \
    --company-id 1 \
    --description "Signed customer contract" \
    --tags "contract,signed,legal"

Example 4: Dry Run (Test Without Inserting)

python3 scripts/insert_file_to_crm.py \
    DATA_FROM_USER/document.pdf \
    --company-id 1 \
    --description "Test document" \
    --dry-run

Schema Reference

For detailed schema information, refer to references/data_files_schema.md, which includes:

• Complete table structure
• Column definitions and data types
• JSONB tag structure
• Common MIME types
• Example queries

Tips

1. Context-Efficient: Always use the Python script instead of generating inline code. This keeps the agent's context window clean.

2. MIME Type Detection: The script automatically detects MIME types. No need to specify them manually.

3. Multiple Files: To insert multiple files, run the script multiple times (potentially in parallel with shell background jobs).

4. Tag Strategy: Use consistent tags for easier querying:

   • Document type: "contract", "invoice", "report"
   • Status: "draft", "signed", "final"
   • Category: "workshop", "meeting", "sales"

5. Error Handling: If insertion fails, the script provides clear error messages. Common issues:

   • File not found
   • Missing link (no company/person/event/opportunity ID)
   • Database connection error
   • Permission issues

6. Schema Access: Use the bel-crm-schema-write-db skill if complex SQL queries are needed beyond simple file insertion.

Resources

scripts/insert_file_to_crm.py

Python script that handles all file processing and database insertion. Execute this script to insert files - do not rewrite the logic inline.

references/data_files_schema.md

Complete schema documentation for the data_files table, including column definitions, JSONB structures, and example queries.