Database Schema

GTM Clarity uses PostgreSQL (Neon serverless) with Drizzle ORM. The schema consists of 17 tables across 15 schema files, all scoped by tenant_id for multi-tenant isolation.

Entity Relationship Diagram

Base Column Helpers

All tables use shared column helpers from src/server/db/schema/base.ts:

tenantColumns

Added to every table for multi-tenant isolation:

Column	Type	Nullable	Description
tenant_id	text	No	Foreign key to tenants.id

timestampColumns

Added to most tables for tracking:

Column	Type	Default	Description
created_at	timestamptz	now()	Row creation time
updated_at	timestamptz	now()	Last modification time

softDeleteColumns

Added to entities that support soft delete:

Column	Type	Default	Description
deleted_at	timestamptz	null	Deletion timestamp (null = active)

Table Reference

tenants

Root table for multi-tenant isolation. Maps 1:1 with Clerk organizations.

Column	Type	Constraints	Description
id	text	PK, UUID	Internal tenant ID
clerk_org_id	text	NOT NULL, UNIQUE	Clerk organization ID
name	text	NOT NULL	Organization display name
slug	text	NOT NULL	URL-safe identifier
settings	jsonb	DEFAULT {}	Tenant configuration overrides
created_at	timestamptz	NOT NULL, DEFAULT now()	Creation timestamp
updated_at	timestamptz	NOT NULL, DEFAULT now()	Last update timestamp

---

accounts

CRM accounts with firmographic data used for fit scoring.

Column	Type	Constraints	Description
id	text	PK, UUID	Account ID
tenant_id	text	NOT NULL	Tenant scope
name	text	NOT NULL	Company name
domain	text		Website domain
industry	text		Industry classification
employee_count	integer		Number of employees
annual_revenue	numeric		Annual revenue
source	text		Data source identifier
external_id	text		ID in source system
source_data	jsonb		Raw source record
created_at	timestamptz	NOT NULL	Creation time
updated_at	timestamptz	NOT NULL	Last update time
deleted_at	timestamptz		Soft delete marker

Indexes: tenant_id, (tenant_id, external_id) UNIQUE, domain

---

people

Contacts and leads linked to accounts.

Column	Type	Constraints	Description
id	text	PK, UUID	Person ID
tenant_id	text	NOT NULL	Tenant scope
account_id	text	FK accounts.id	Parent account
email	text		Email address
first_name	text		First name
last_name	text		Last name
title	text		Job title
phone	text		Phone number
source	text		Data source
external_id	text		ID in source system
source_data	jsonb		Raw source record
created_at	timestamptz	NOT NULL	Creation time
updated_at	timestamptz	NOT NULL	Last update time
deleted_at	timestamptz		Soft delete marker

Indexes: tenant_id, account_id, email, (tenant_id, external_id) UNIQUE

---

opportunities

Sales opportunities linked to accounts.

Column	Type	Constraints	Description
id	text	PK, UUID	Opportunity ID
tenant_id	text	NOT NULL	Tenant scope
account_id	text	FK accounts.id, NOT NULL	Parent account
name	text	NOT NULL	Opportunity name
stage	text		Deal stage
amount	numeric		Deal value
close_date	timestamptz		Expected close date
source	text		Data source
external_id	text		ID in source system
source_data	jsonb		Raw source record
created_at	timestamptz	NOT NULL	Creation time
updated_at	timestamptz	NOT NULL	Last update time
deleted_at	timestamptz		Soft delete marker

Indexes: tenant_id, account_id, (tenant_id, external_id) UNIQUE, stage

---

activities

Multi-channel engagement events used for engagement scoring.

Column	Type	Constraints	Description
id	text	PK, UUID	Activity ID
tenant_id	text	NOT NULL	Tenant scope
person_id	text	FK people.id	Person who performed the activity
account_id	text	FK accounts.id	Associated account
type	text	NOT NULL	Activity type (e.g., email_open, page_view)
channel	text		Channel (e.g., email, web, events)
occurred_at	timestamptz	NOT NULL	When the activity happened
properties	jsonb		Additional event properties
created_at	timestamptz	NOT NULL	Record creation time
updated_at	timestamptz	NOT NULL	Last update time

Indexes: tenant_id, person_id, account_id, (tenant_id, occurred_at), type, channel

---

engagements

Computed scores for entities (people, accounts, opportunities).

Column	Type	Constraints	Description
id	text	PK, UUID	Engagement record ID
tenant_id	text	NOT NULL	Tenant scope
entity_type	text	NOT NULL	person, account, or opportunity
entity_id	text	NOT NULL	ID of the scored entity
fit_score	numeric		Fit score (0-100)
engagement_score	numeric		Engagement score (0-100)
combined_score	numeric		Weighted combined score
tier	text		Assigned tier (hot, warm, cool, cold)
scored_at	timestamptz	NOT NULL, DEFAULT now()	When the score was computed
score_version	integer	DEFAULT 1	Scoring config version
score_breakdown	jsonb		Detailed scoring components
created_at	timestamptz	NOT NULL	Record creation time
updated_at	timestamptz	NOT NULL	Last update time

Indexes: (tenant_id, entity_type, entity_id) UNIQUE, (tenant_id, tier), scored_at

---

connectors

Connector instances with encrypted credentials.

Column	Type	Constraints	Description
id	text	PK, UUID	Connector ID
tenant_id	text	NOT NULL	Tenant scope
type	text	NOT NULL	salesforce, customerio, delivrai, o365, fathom
name	text	NOT NULL	Display name
config	jsonb		Encrypted configuration
status	text	DEFAULT disconnected	Connection status
last_sync_at	timestamptz		Last successful sync
last_sync_cursor	text		Pagination cursor
last_sync_error	text		Last error message
field_mappings	jsonb		Legacy field mappings
created_at	timestamptz	NOT NULL	Creation time
updated_at	timestamptz	NOT NULL	Last update time

Indexes: tenant_id, (tenant_id, type) UNIQUE

---

scoring_configs

Per-tenant scoring configuration with versioning.

Column	Type	Constraints	Description
id	text	PK, UUID	Config ID
tenant_id	text	NOT NULL	Tenant scope
type	text	NOT NULL	fit, engagement, or combined
config	jsonb	NOT NULL	Full configuration object
version	integer	DEFAULT 1	Version number
is_active	boolean	DEFAULT true	Active flag
created_at	timestamptz	NOT NULL	Creation time
updated_at	timestamptz	NOT NULL	Last update time

Indexes: (tenant_id, type) UNIQUE, tenant_id

---

field_mappings

Source-to-canonical field mapping configuration.

Column	Type	Constraints	Description
id	text	PK, UUID	Mapping ID
tenant_id	text	NOT NULL	Tenant scope
connector_id	text	FK connectors.id, NOT NULL	Parent connector
entity_type	text	NOT NULL	Entity being mapped
mappings	jsonb	NOT NULL	Array of FieldMappingEntry
is_default	boolean	DEFAULT false	System default flag
created_at	timestamptz	NOT NULL	Creation time
updated_at	timestamptz	NOT NULL	Last update time

Indexes: (tenant_id, connector_id, entity_type) UNIQUE, tenant_id

---

writeback_configs

Score writeback configuration per connector.

Column	Type	Constraints	Description
id	text	PK, UUID	Config ID
tenant_id	text	NOT NULL	Tenant scope
connector_id	text	FK connectors.id, NOT NULL	Target connector
entity_type	text	NOT NULL	Entity type for writeback
field_map	jsonb	NOT NULL	Array of WritebackFieldMap
enabled	boolean	DEFAULT false	Writeback enabled flag
last_writeback_at	timestamptz		Last successful writeback
last_writeback_error	text		Last error
created_at	timestamptz	NOT NULL	Creation time
updated_at	timestamptz	NOT NULL	Last update time

Indexes: (tenant_id, connector_id, entity_type) UNIQUE, tenant_id

---

sync_logs

Sync operation history and audit trail.

Column	Type	Constraints	Description
id	text	PK, UUID	Log entry ID
tenant_id	text	NOT NULL	Tenant scope
connector_id	text	FK connectors.id, NOT NULL	Connector that synced
sync_type	text	NOT NULL	full or incremental
status	text	NOT NULL, DEFAULT running	running, completed, failed
started_at	timestamptz	NOT NULL, DEFAULT now()	Start time
completed_at	timestamptz		Completion time
records_created	integer	DEFAULT 0	New records
records_updated	integer	DEFAULT 0	Updated records
records_deleted	integer	DEFAULT 0	Deleted records
records_failed	integer	DEFAULT 0	Failed records
error_message	text		Error text
error_details	jsonb		Structured error details
cursor	text		Sync cursor at completion

Indexes: (tenant_id, connector_id), (connector_id, started_at)

---

audit_log

Append-only compliance audit trail.

Column	Type	Constraints	Description
id	text	PK, UUID	Entry ID
tenant_id	text	NOT NULL	Tenant scope
user_id	text	NOT NULL	Actor's Clerk user ID
action	text	NOT NULL	Action performed
resource	text	NOT NULL	Resource type
resource_id	text		Affected resource ID
changes	jsonb		Before/after diff
metadata	jsonb		Additional context
created_at	timestamptz	NOT NULL, DEFAULT now()	Action timestamp

Indexes: (tenant_id, created_at), (tenant_id, resource), user_id

---

buying_groups

Buying groups per account, optionally scoped by solution.

Column	Type	Constraints	Description
id	text	PK, UUID	Group ID
tenant_id	text	NOT NULL	Tenant scope
account_id	text	FK accounts.id, NOT NULL	Parent account
opportunity_id	text	FK opportunities.id	Linked opportunity
solution_tag	text		Solution scope (null = account-level)
name	text		Group name
completeness_score	numeric		Role completeness (0-100)
role_template_id	text		Applied role template
created_at	timestamptz	NOT NULL	Creation time
updated_at	timestamptz	NOT NULL	Last update time

Indexes: tenant_id, account_id, opportunity_id, (tenant_id, account_id, solution_tag) UNIQUE

---

buying_group_members

Junction table linking people to buying groups with role assignment.

Column	Type	Constraints	Description
id	text	PK, UUID	Member ID
tenant_id	text	NOT NULL	Tenant scope
buying_group_id	text	FK buying_groups.id, NOT NULL	Parent group
person_id	text	FK people.id	Person in the group
role	text		Detected or confirmed role
role_confidence	numeric		Detection confidence (0-1)
role_source	text		How role was determined
is_confirmed	boolean	DEFAULT false	Manually confirmed
is_discovered	boolean	DEFAULT false	Found via contact discovery
discovery_source	text		Source of discovery
discovered_email	text		Email found during discovery
created_at	timestamptz	NOT NULL	Creation time
updated_at	timestamptz	NOT NULL	Last update time

Indexes: tenant_id, buying_group_id, person_id, (buying_group_id, person_id) UNIQUE

---

role_templates

Configurable role templates for buying group completeness scoring.

Column	Type	Constraints	Description
id	text	PK, UUID	Template ID
tenant_id	text	NOT NULL	Tenant scope
name	text	NOT NULL	Template name
solution_tag	text		Solution scope
roles	jsonb	NOT NULL	Array of { role, required, weight }
is_default	boolean	DEFAULT false	Default template flag
created_at	timestamptz	NOT NULL	Creation time
updated_at	timestamptz	NOT NULL	Last update time

Indexes: tenant_id

---

fathom_meetings

Fathom.video meeting records stored separately from activities (transcripts are too large for the activities table).

Column	Type	Constraints	Description
id	text	PK, UUID	Meeting record ID
tenant_id	text	NOT NULL	Tenant scope
fathom_meeting_id	text	NOT NULL	Fathom's meeting ID
activity_id	text	FK activities.id	Linked activity record
title	text		Meeting title
started_at	timestamptz		Meeting start time
ended_at	timestamptz		Meeting end time
participants_json	jsonb		Array of { name, email, role }
transcript_json	jsonb		Array of { speaker, text, startTime, endTime }
summary_text	text		Meeting summary
sentiment_score	text		Sentiment analysis (future)
created_at	timestamptz	NOT NULL	Creation time
updated_at	timestamptz	NOT NULL	Last update time

Indexes: tenant_id, (fathom_meeting_id, tenant_id) UNIQUE

---

o365_config

O365 connector privacy and DPA configuration.

Column	Type	Constraints	Description
id	text	PK, UUID	Config ID
tenant_id	text	NOT NULL	Tenant scope
connector_id	text	FK connectors.id	Parent connector
dpa_accepted_at	timestamptz		DPA acceptance time (null = blocked)
dpa_accepted_by_user_id	text		User who accepted
allowed_domains	jsonb	DEFAULT []	Domain allowlist for extraction
tenant_o365_domain	text		Tenant's own domain (excluded)
created_at	timestamptz	NOT NULL	Creation time
updated_at	timestamptz	NOT NULL	Last update time

Indexes: tenant_id, connector_id UNIQUE

Drizzle ORM Usage

GTM Clarity uses Drizzle ORM for type-safe database access:

# Run pending migrations
npm run drizzle migrate

# Open interactive DB browser
npm run drizzle studio

tip

Use Drizzle Studio (npm run drizzle studio) during development to visually inspect and query your database at https://local.drizzle.studio.

Related Pages

• Architecture -- How the schema fits into the overall system
• Tenant Setup -- Tenant isolation model
• Connector Management -- Connector and sync_logs tables
• Scoring Configuration -- scoring_configs table details