---
title: "CRM Pipeline"
description: "Internal CRM system for managing investor contacts, outreach campaigns, and pipeline tracking, with agent-driven automation"
canonical: "https://docs.ainative.studio/docs/business-ops/crm-pipeline"
last-updated: "2026-10-13T21:42:35.442Z"
---

# CRM Pipeline

Source: https://docs.ainative.studio/docs/business-ops/crm-pipeline

> Internal CRM system for managing investor contacts, outreach campaigns, and pipeline tracking, with agent-driven automation

## CRM Pipeline

Internal CRM system for managing investor contacts, outreach campaigns, and pipeline tracking. Built on Railway PostgreSQL with agent-driven automation.

---

## Table of Contents

- [Database Schema](#database-schema)
- [Pipeline Stages](#pipeline-stages)
- [Enum Types](#enum-types)
- [Agent Integration](#agent-integration)
- [Import Workflow](#import-workflow)

---

## Database Schema

### crm_contacts

Primary contact table. Each record represents an investor, partner, or lead.

| Column | Type | Default | Description |
|--------|------|---------|-------------|
| `id` | UUID | `gen_random_uuid()` | Primary key |
| `user_id` | UUID | required | Owner (FK to `users`) |
| `tenant_id` | UUID | null | Optional tenant scope (FK to `tenants`) |
| `name` | VARCHAR(255) | required | Contact full name |
| `linkedin_url` | VARCHAR(500) | required | LinkedIn profile URL |
| `linkedin_member_id` | VARCHAR(255) | null | LinkedIn member ID |
| `email` | VARCHAR(255) | null | Email address |
| `phone` | VARCHAR(50) | null | Phone number |
| `company` | VARCHAR(255) | null | Company / fund name |
| `title` | VARCHAR(255) | null | Job title |
| `location` | VARCHAR(255) | null | Geographic location |
| `industry` | VARCHAR(255) | null | Industry vertical |
| `website` | VARCHAR(500) | null | Company website |
| `notes` | TEXT | `''` | Free-text notes (appended by agents) |
| `tags` | JSONB | `[]` | Tag array (e.g., `["ai", "devtools", "vc"]`) |
| `pipeline_stage` | `crm_pipeline_stage` | `'new'` | Current pipeline position |
| `score` | INTEGER | null | Contact quality score (0-100) |
| `source` | `crm_contact_source` | `'manual'` | How the contact was acquired |
| `source_details` | VARCHAR(500) | null | Additional source context |
| `connection_status` | `crm_connection_status` | `'not_connected'` | LinkedIn connection state |
| `interaction_count` | INTEGER | `0` | Total interactions logged |
| `last_contacted_at` | TIMESTAMPTZ | null | Timestamp of last outreach |
| `next_follow_up_at` | TIMESTAMPTZ | null | Scheduled follow-up date |
| `created_at` | TIMESTAMPTZ | `NOW()` | Record creation time |
| `updated_at` | TIMESTAMPTZ | `NOW()` | Last modification time |

**IR Validation Columns** (added for the Investor Relations agent):

| Column | Type | Default | Description |
|--------|------|---------|-------------|
| `investor_validated` | BOOLEAN | `FALSE` | Whether the org has been validated as a real investor |
| `investor_validation_reason` | TEXT | null | Why validation passed or failed |
| `fund_active` | BOOLEAN | `TRUE` | Whether the fund is still actively investing |
| `fund_active_reason` | TEXT | null | Evidence for fund activity status |
| `fit_score` | INTEGER | null | AINative fit score (1-100), set by IR agent |
| `org_type` | VARCHAR(50) | null | Classification: `confirmed_investor`, `likely_investor`, `non_investor`, `unknown` |
| `validated_at` | TIMESTAMPTZ | null | When validation was performed |
| `portfolio_conflicts` | JSONB | `[]` | List of portfolio companies that are direct competitors |

**Indexes:**

- `idx_crm_contacts_user_id` -- owner lookup
- `idx_crm_contacts_tenant_id` -- tenant scope
- `idx_crm_contacts_pipeline_stage` -- pipeline filtering
- `idx_crm_contacts_company` -- company search
- `idx_crm_contacts_next_follow_up` -- partial index on non-null follow-ups
- `idx_crm_contacts_user_linkedin` -- unique constraint on (user_id, linkedin_url)

**Constraints:**

- `chk_crm_contacts_score`: score must be NULL or between 0 and 100

---

### crm_campaigns

Outreach campaigns that group contacts for batch processing.

| Column | Type | Default | Description |
|--------|------|---------|-------------|
| `id` | UUID | `gen_random_uuid()` | Primary key |
| `user_id` | UUID | required | Campaign owner (FK to `users`) |
| `tenant_id` | UUID | null | Optional tenant scope |
| `name` | VARCHAR(255) | required | Campaign name |
| `description` | TEXT | `''` | Campaign description |
| `status` | `crm_campaign_status` | `'draft'` | Current campaign state |
| `channel` | `crm_campaign_channel` | `'linkedin'` | Outreach channel |
| `template_id` | UUID | null | Message template reference |
| `target_tags` | JSONB | `[]` | Tags used to filter target contacts |
| `daily_limit` | INTEGER | `20` | Max contacts per day (1-100) |
| `start_date` | TIMESTAMPTZ | null | Campaign start date |
| `end_date` | TIMESTAMPTZ | null | Campaign end date |
| `paused_at` | TIMESTAMPTZ | null | When campaign was paused |
| `created_at` | TIMESTAMPTZ | `NOW()` | Record creation time |
| `updated_at` | TIMESTAMPTZ | `NOW()` | Last modification time |

**Constraints:**

- `chk_crm_campaigns_daily_limit`: daily_limit must be between 1 and 100

---

### crm_campaign_contacts

Junction table linking campaigns to contacts with per-contact delivery status.

| Column | Type | Default | Description |
|--------|------|---------|-------------|
| `id` | UUID | `gen_random_uuid()` | Primary key |
| `campaign_id` | UUID | required | FK to `crm_campaigns` (CASCADE delete) |
| `contact_id` | UUID | required | FK to `crm_contacts` (CASCADE delete) |
| `status` | `crm_campaign_contact_status` | `'pending'` | Delivery status for this contact |
| `channel` | `crm_campaign_channel` | `'linkedin'` | Channel used for this contact |
| `message` | TEXT | null | Actual message sent |
| `sent_at` | TIMESTAMPTZ | null | When message was sent |
| `opened_at` | TIMESTAMPTZ | null | When message was opened |
| `replied_at` | TIMESTAMPTZ | null | When reply was received |
| `created_at` | TIMESTAMPTZ | `NOW()` | Record creation time |

**Constraints:**

- `uq_crm_campaign_contacts`: unique on (campaign_id, contact_id)

---

### crm_activities

Activity log for all interactions with a contact, both manual and agent-driven.

| Column | Type | Default | Description |
|--------|------|---------|-------------|
| `id` | UUID | `gen_random_uuid()` | Primary key |
| `contact_id` | UUID | required | FK to `crm_contacts` (CASCADE delete) |
| `campaign_id` | UUID | null | FK to `crm_campaigns` (SET NULL on delete) |
| `user_id` | UUID | required | Who performed the action (FK to `users`) |
| `agent_registration_id` | UUID | null | Which agent performed the action (FK to `agent_registrations`) |
| `trace_id` | VARCHAR(255) | null | Trace ID for agent observability |
| `activity_type` | `crm_activity_type` | required | Type of activity |
| `description` | TEXT | required | Human-readable description |
| `metadata` | JSONB | `{}` | Arbitrary structured data |
| `created_at` | TIMESTAMPTZ | `NOW()` | When the activity occurred |

---

### crm_reminders

Follow-up reminders tied to specific contacts.

| Column | Type | Default | Description |
|--------|------|---------|-------------|
| `id` | UUID | `gen_random_uuid()` | Primary key |
| `contact_id` | UUID | required | FK to `crm_contacts` (CASCADE delete) |
| `user_id` | UUID | required | Reminder owner (FK to `users`) |
| `title` | VARCHAR(500) | required | Reminder description |
| `due_at` | TIMESTAMPTZ | required | When the reminder is due |
| `completed` | BOOLEAN | `FALSE` | Whether the reminder has been completed |
| `completed_at` | TIMESTAMPTZ | null | When it was completed |
| `created_at` | TIMESTAMPTZ | `NOW()` | Record creation time |

**Indexes:**

- `idx_crm_reminders_due_at` -- partial index where `completed = FALSE`

---

### crm_message_templates

Reusable message templates for outreach campaigns.

| Column | Type | Default | Description |
|--------|------|---------|-------------|
| `id` | UUID | `gen_random_uuid()` | Primary key |
| `user_id` | UUID | required | Template owner |
| `tenant_id` | UUID | null | Optional tenant scope |
| `name` | VARCHAR(255) | required | Template name |
| `subject` | VARCHAR(500) | null | Email subject line |
| `body` | TEXT | required | Message body (supports variable interpolation) |
| `variables` | JSONB | `[]` | List of template variables |
| `category` | `crm_template_category` | `'custom'` | Template category |
| `tone` | VARCHAR(50) | `'professional'` | Tone: professional, casual, etc. |
| `times_used` | INTEGER | `0` | Usage counter |
| `avg_reply_rate` | FLOAT | null | Historical reply rate |
| `created_at` | TIMESTAMPTZ | `NOW()` | Record creation time |
| `updated_at` | TIMESTAMPTZ | `NOW()` | Last modification time |

---

## Pipeline Stages

Contacts move through the pipeline in a defined progression. The IR agent and CoS agent automate most transitions.

```
new --> contacted --> replied --> meeting_scheduled --> negotiation --> won
                  |           |                                    |
                  |           +---> nurture                        +---> lost
                  |
                  +---> lost (bounced)
```

| Stage | Description | Set By |
|-------|-------------|--------|
| `new` | Freshly imported, not yet contacted | Import script |
| `contacted` | Outreach email sent | IR agent |
| `replied` | Investor responded to outreach | CoS inbox triage / IR agent |
| `meeting_scheduled` | Meeting booked (Calendly or manual) | Manual / agent |
| `negotiation` | Active deal discussion | Manual |
| `won` | Deal closed | Manual |
| `lost` | Bounced email, rejected, or disqualified | IR agent / CoS agent |
| `nurture` | Not ready now, keep warm | Manual / agent |

---

## Enum Types

All enum types are created idempotently via `sync-production-schema.py`.

### crm_pipeline_stage

```
'new', 'contacted', 'replied', 'meeting_scheduled', 'negotiation', 'won', 'lost', 'nurture'
```

### crm_contact_source

```
'manual', 'linkedin_api', 'agent_import', 'csv_import', 'mcp_tool'
```

### crm_connection_status

```
'not_connected', 'pending', 'connected', 'following'
```

### crm_campaign_status

```
'draft', 'active', 'paused', 'completed', 'cancelled'
```

### crm_campaign_channel

```
'linkedin', 'email', 'multi'
```

### crm_campaign_contact_status

```
'pending', 'sent', 'opened', 'replied', 'bounced', 'skipped'
```

### crm_template_category

```
'connection_request', 'follow_up', 'introduction', 'meeting_request', 'thank_you', 'referral', 'custom'
```

### crm_activity_type

```
'note_added', 'tag_changed', 'stage_changed', 'reminder_set',
'connection_sent', 'connection_accepted', 'message_sent',
'message_received', 'email_sent', 'email_opened', 'email_replied',
'meeting_scheduled', 'meeting_completed', 'deal_created', 'deal_updated'
```

---

## Agent Integration

### Investor Relations (IR) Agent

Script: `scripts/investor_relations_agent.py`

The IR agent runs on a cron schedule (4x daily) and processes contacts in small batches of 25.

**Read phase** -- pull unprocessed contacts:

```sql
SELECT id, name, email, company, title, website, linkedin_url,
       location, tags, source_details
FROM crm_contacts
WHERE pipeline_stage = 'new'
AND email IS NOT NULL AND email != ''
ORDER BY created_at ASC
LIMIT 25
```

**Validate + Score** -- for each contact:

1. Check if the firm is a known non-investor (hard-block list). If so, mark `pipeline_stage = 'lost'`.
2. Look up the firm in a local validation cache (keyed by MD5 of lowercased company name). If cached, reuse the result.
3. If not cached, perform a lightweight HTTP check on the firm's website looking for investment signals (`"portfolio"`, `"we invest"`, `"fund size"`, etc.) and non-investor signals.
4. Classify the contact as `vc` or `angel` based on title and company name patterns.
5. Compute a fit score (base 50, +15 for confirmed investor, +10 for likely investor, +15 for priority firm, +10 for named email address, +5 for senior title).
6. Skip contacts scoring below 50 (left as `new` for future re-scoring).

**Send phase** -- generate and send personalized email:

- VC contacts receive a fund-focused pitch referencing their firm.
- Angel contacts receive a founder-focused pitch with smaller check language.
- Unknown contacts receive a generic introduction.
- Emails are sent directly via `gmail-dwd.py send` (no drafts, no A2P queue).

**Write phase** -- update CRM after send:

```sql
UPDATE crm_contacts
SET pipeline_stage = 'contacted',
    last_contacted_at = NOW(),
    interaction_count = COALESCE(interaction_count, 0) + 1,
    fit_score = <score>,
    org_type = <investor_type>,
    updated_at = NOW()
WHERE id = <contact_id>
```

**Bounce + Reply detection** (runs at the start of every batch):

- **Bounces**: Queries Gmail for `from:mailer-daemon@googlemail.com newer_than:3d`, extracts bounced email addresses from snippets, and sets `pipeline_stage = 'lost'` with a `[BOUNCED]` note.
- **Replies**: Queries Gmail for inbox messages matching AINative subject lines, extracts the sender address, and sets `pipeline_stage = 'replied'` for any contact currently in `contacted` stage.

**CoS handoff**: The IR agent reads `~/.openclaw/workspace/memory/ir-handoff.md` before each run. Any emails listed under a "DO NOT EMAIL" section are excluded from the batch.

### Chief of Staff (CoS) Inbox Triage

Script: `scripts/cos_inbox_triage.py`

The CoS agent classifies incoming emails into tiers:

| Tier | Description |
|------|-------------|
| `HUMAN_ACTION` | Real person, needs response |
| `HUMAN_FYI` | Real person, informational only |
| `PARTNER` | Vendor/partner notification |
| `NEWSLETTER` | Opted-in digests |
| `TRANSACTIONAL` | Receipts, invoices |
| `COLD_OUTREACH` | Mass cold sales email |
| `SPAM` | Junk |

When a reply from an investor is detected (classified as `HUMAN_ACTION`), the CoS agent can update the CRM contact from `contacted` to `replied`. This feeds back into the pipeline so the IR agent does not re-contact them.

### Pipeline Stats

The IR agent exposes a `--check` mode that prints current pipeline counts:

```bash
python3 scripts/investor_relations_agent.py --check
```

This runs:

```sql
SELECT pipeline_stage, COUNT(*) FROM crm_contacts
GROUP BY pipeline_stage ORDER BY count DESC
```

---

## Import Workflow

Script: `scripts/import_vcdb_to_crm.py`

### CSV Sources

The import script reads from multiple CSV files and deduplicates on email address:

1. Fondo VCDB SF contacts
2. Fondo VCDB global contacts
3. `vc_public_contacts.csv` (scraped public VC contact data)

### Field Mapping

| CSV Field | CRM Column |
|-----------|------------|
| `Contact Name` or `Contact` | `name` |
| `Email` | `email` |
| `Fund Name` or `Fund` | `company` |
| `Title` | `title` |
| `Website` | `website` |
| `LinkedIn` or `LinkedIn Link` | `linkedin_url` |
| `Location` | `location` |
| `Fund focus` | Used for tag generation |
| `Fund stage` | Stored in metadata |
| `Fund type` | Used for tag generation |
| `Fund Description` | `notes` (first 500 chars) |

### Tag Generation

Tags are automatically derived from the fund focus and fund type fields:

- `"artificial intelligence"` or `"ai/ml"` in focus --> `ai` tag
- `"developer tools"` in focus --> `devtools` tag
- `"saas"` in focus --> `saas` tag
- `"fintech"` in focus --> `fintech` tag
- `"accelerator"` in fund type --> `accelerator` tag
- `"venture"` in fund type --> `vc` tag

### Import Defaults

All imported contacts are created with:

- `pipeline_stage = 'new'`
- `source = 'csv_import'`
- `connection_status = 'not_connected'`
- `interaction_count = 0`

### Post-Import: Mark Sent Contacts

After importing, the script cross-references the A2P review queue (`~/.openclaw/workspace/memory/a2p-review-queue.jsonl`) to find contacts that have already been emailed. These are updated to `pipeline_stage = 'contacted'`.

It also checks Gmail for bounced emails (`from:mailer-daemon@googlemail.com`) and marks those contacts as `pipeline_stage = 'lost'`.

### Usage

```bash
## Full import: load CSVs + mark already-sent contacts
python3 scripts/import_vcdb_to_crm.py

## Preview what would be imported (no writes)
python3 scripts/import_vcdb_to_crm.py --dry-run

## Only update pipeline_stage for already-sent contacts (skip CSV import)
python3 scripts/import_vcdb_to_crm.py --mark-sent
```

---

## Entity Relationship Diagram

```
users
  |
  +--< crm_contacts >--< crm_campaign_contacts >--< crm_campaigns
  |       |                                              |
  |       +--< crm_activities >--------------------------+
  |       |
  |       +--< crm_reminders
  |
  +--< crm_campaigns
  |
  +--< crm_message_templates
```

- A user owns contacts, campaigns, templates, and reminders.
- Contacts belong to campaigns through the `crm_campaign_contacts` junction.
- Activities reference a contact and optionally a campaign and agent registration.
- Reminders are scoped to a single contact.