feat: Import contacts from external PostgreSQL database

[baderdean]

Feature Request

Add ability to import contacts from external PostgreSQL databases using custom SQL queries with field mapping (similar to CSV import flow).

Requirements

Data Source

• External PostgreSQL databases (not project's Supabase)
• Support for any PostgreSQL instance with connection credentials
• Expected volume: thousands to 100k rows

Query Interface

• Advanced mode only: Custom SQL query input (hidden by default)
• Strict validation: Only SELECT statements allowed
• Block: DROP, DELETE, UPDATE, INSERT, CREATE, ALTER, TRUNCATE, GRANT, etc.
• Query validation before execution

Credential Management

• Save credentials securely as reusable "mining source" (type: postgres)
• Use existing encryption: pgp_sym_encrypt with LEADMINER_API_HASH_SECRET
• Store: host, port, database, username, password (or connection string)

Field Mapping

• Reuse CSV import's field mapping dialog pattern
• Map SQL result columns to contact fields: email, name, given_name, family_name, alternate_name, location, works_for, job_title, same_as, image
• Smart auto-detection: first email column → email field
• Preview first 5 rows before import

Technical Architecture

Recommended: Express Backend Endpoint

• Rationale: Direct PostgreSQL inserts are faster than Edge Functions for large datasets
• Reuse existing TasksManagerFile.ts pattern
• Stream/batch processing for 50-100k rows
• Progress tracking via SSE (Server-Sent Events)
• Redis Streams for job queuing

API Design:

POST /api/imap/mine/postgres/:userId
{
  "sourceId": "uuid",           // Saved postgres connection
  "query": "SELECT email, name, company FROM leads WHERE...",
  "mapping": {                  // Column to field mapping
    "email": "email",
    "name": "name",
    "company": "works_for"
  }
}

Security Considerations:

1. SQL injection prevention via strict query validation (whitelist only SELECT)
2. Read-only database user recommended
3. Credential encryption at rest
4. Query timeout limits
5. Row count limits (configurable)

UI/UX Design

New Component: ImportPostgresDialog.vue

• Similar structure to ImportFileDialog.vue
• Two-step wizard:
  1. Connection: Form for host, port, db, user, password + "Save connection" checkbox
  2. Query & Mapping: Advanced SQL input (toggle), column mapping table

Source Panel Integration:

• Add "Import from PostgreSQL" button next to CSV import in SourcePanel.vue

Database Schema

Extend mining_sources table to support type postgres:

{
  "type": "postgres",
  "credentials": {
    "host": "pg.example.com",
    "port": 5432,
    "database": "leads",
    "username": "import_user",
    "password": "encrypted"
  }
}

Implementation Notes

Reusable Components

• ImportFileDialog.vue - Field mapping UI pattern
• PgMiningSources.ts - Credential encryption/decryption
• TasksManagerFile.ts - File processing task pattern
• SSE progress tracking from existing mining flow

SQL Validation Strategy

Options:

1. Parse with pg-query-parser (node-postgres)
2. Regex whitelist: /^\\s*SELECT\\s+/i with blacklist for forbidden keywords
3. Read-only connection: PostgreSQL user with only SELECT privileges

Recommended: Combination of 2 + 3 for defense in depth

Error Handling

• Connection failures (network, auth)
• Invalid SQL syntax
• Query returns no rows
• Query timeout
• Row count exceeded
• Mapping validation errors

Acceptance Criteria

• User can save PostgreSQL connection credentials securely
• User can write and execute custom SELECT queries
• Only SELECT queries are allowed (validated)
• Field mapping UI reuses CSV import pattern
• Preview shows first 5 rows with mapped columns
• Import supports up to 100k rows with progress tracking
• Contacts are saved to existing contacts table
• Works for external PostgreSQL databases (not Supabase)
• Advanced SQL mode is hidden by default
• Credentials encrypted with pgp_sym_encrypt

Priority

Medium

Labels

enhancement, feature, import, postgres