CRM Pipeline

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

Database Schema
Pipeline Stages
Enum Types
Agent Integration
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:

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:

Check if the firm is a known non-investor (hard-block list). If so, mark pipeline_stage = 'lost'.
Look up the firm in a local validation cache (keyed by MD5 of lowercased company name). If cached, reuse the result.
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.
Classify the contact as vc or angel based on title and company name patterns.
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).
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:

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:

python3 scripts/investor_relations_agent.py --check

This runs:

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:

Fondo VCDB SF contacts
Fondo VCDB global contacts
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

## 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.

CRM Pipeline​

Table of Contents​

Database Schema​

crm_contacts​

crm_campaigns​

crm_campaign_contacts​

crm_activities​

crm_reminders​

crm_message_templates​

Pipeline Stages​

Enum Types​

crm_pipeline_stage​

crm_contact_source​

crm_connection_status​

crm_campaign_status​

crm_campaign_channel​

crm_campaign_contact_status​

crm_template_category​

crm_activity_type​

Agent Integration​

Investor Relations (IR) Agent​

Chief of Staff (CoS) Inbox Triage​

Pipeline Stats​

Import Workflow​

CSV Sources​

Field Mapping​

Tag Generation​

Import Defaults​

Post-Import: Mark Sent Contacts​

Usage​

Entity Relationship Diagram​

CRM Pipeline

Table of Contents

Database Schema

crm_contacts

crm_campaigns

crm_campaign_contacts

crm_activities

crm_reminders

crm_message_templates

Pipeline Stages

Enum Types

crm_pipeline_stage

crm_contact_source

crm_connection_status

crm_campaign_status

crm_campaign_channel

crm_campaign_contact_status

crm_template_category

crm_activity_type

Agent Integration

Investor Relations (IR) Agent

Chief of Staff (CoS) Inbox Triage

Pipeline Stats

Import Workflow

CSV Sources

Field Mapping

Tag Generation

Import Defaults

Post-Import: Mark Sent Contacts

Usage

Entity Relationship Diagram