Contacts, companies, and contact-company relationships for customer management
Contacts, companies, and the many-to-many relationship between them. Contacts represent external people you do business with (not application users). Companies represent the organizations those contacts belong to.
| Column | Type | Description |
|---|---|---|
| id | uuid | Primary key |
| org_id | uuid | FK to organizations |
| owner_id | uuid | FK to users. The user responsible for this contact |
| first_name | text | Contact first name |
| last_name | text | Contact last name |
| text | Email address | |
| phone | text | Phone number |
| type | contact_type | Lifecycle stage: lead, prospect, customer, partner, or other |
| title |
| text |
| Job title |
| source | text | How this contact was acquired (e.g. website, referral, trade show) |
| avatar_url | text | URL to avatar image |
| address_line1 | text | Street address line 1 |
| address_line2 | text | Street address line 2 |
| city | text | City |
| state | text | State or province |
| postal_code | text | Postal or zip code |
| country | text | Country |
| created_at | timestamptz | Row creation timestamp |
| updated_at | timestamptz | Last update timestamp (auto-set by trigger) |
| metadata | jsonb | Arbitrary JSON |
| Column | Type | Description |
|---|---|---|
| id | uuid | Primary key |
| org_id | uuid | FK to organizations |
| owner_id | uuid | FK to users. The user responsible for this company |
| name | text | Company name |
| domain | text | Company web domain |
| industry | text | Industry vertical |
| size | text | Company size bracket (e.g. 1-10, 11-50, 51-200) |
| website | text | Full website URL |
| phone | text | Phone number |
| address_line1 | text | Street address line 1 |
| address_line2 | text | Street address line 2 |
| city | text | City |
| state | text | State or province |
| postal_code | text | Postal or zip code |
| country | text | Country |
| annual_revenue | numeric | Estimated annual revenue in cents or smallest currency unit |
| created_at | timestamptz | Row creation timestamp |
| updated_at | timestamptz | Last update timestamp (auto-set by trigger) |
| metadata | jsonb | Arbitrary JSON |
| Column | Type | Description |
|---|---|---|
| id | uuid | Primary key |
| org_id | uuid | FK to organizations |
| contact_id | uuid | FK to contacts |
| company_id | uuid | FK to companies |
| title | text | Contact's role or title at this company |
| is_primary | boolean | Whether this is the contact's current primary company |
| started_at | date | When the contact started at this company |
| ended_at | date | When the contact left this company |
| created_at | timestamptz | Row creation timestamp |
| updated_at | timestamptz | Last update timestamp (auto-set by trigger) |
| metadata | jsonb | Arbitrary JSON |
Unique constraint on (contact_id, company_id).
| Value | Description |
|---|---|
| lead | Initial contact, not yet qualified |
| prospect | Qualified and actively being pursued |
| customer | Converted to a paying customer |
| partner | Business partner or collaborator |
| other | Does not fit the other categories |
All tables have RLS enabled and are scoped to the current user's organization via get_user_org_id().
identity -- organizations, users, and RLS helper functionsGet all contacts at a specific company:
SELECT c.first_name, c.last_name, c.email, cc.title
FROM contacts c
JOIN contact_companies cc ON cc.contact_id = c.id
WHERE cc.company_id = '<company_id>'
AND cc.is_primary = true
ORDER BY c.last_name;
Search contacts by type with company info:
SELECT
c.first_name || ' ' || coalesce(c.last_name, '') AS name,
c.email,
c.type,
co.name AS company
FROM contacts c
LEFT JOIN contact_companies cc ON cc.contact_id = c.id AND cc.is_primary = true
LEFT JOIN companies co ON co.id = cc.company_id
WHERE c.type = 'lead'
ORDER BY c.created_at DESC;
List all companies with their primary contact count:
SELECT
co.name,
co.industry,
co.size,
count(cc.id) AS contact_count
FROM companies co
LEFT JOIN contact_companies cc ON cc.company_id = co.id AND cc.is_primary = true
WHERE co.org_id = get_user_org_id()
GROUP BY co.id, co.name, co.industry, co.size
ORDER BY contact_count DESC;
Find contacts without a company association:
SELECT c.first_name, c.last_name, c.email, c.type, c.source
FROM contacts c
LEFT JOIN contact_companies cc ON cc.contact_id = c.id
WHERE c.org_id = get_user_org_id()
AND cc.id IS NULL
ORDER BY c.created_at DESC;
Get a contact's full company history:
SELECT
co.name AS company,
cc.title,
cc.started_at,
cc.ended_at,
cc.is_primary
FROM contact_companies cc
JOIN companies co ON co.id = cc.company_id
WHERE cc.contact_id = '<contact_id>'
ORDER BY cc.started_at DESC NULLS FIRST;