GTM Data Model Design

When to Use

Use when designing or extending the data layer for GTM tools and automations.

Core Entities

Companies

companies (
  id uuid PRIMARY KEY,
  name text NOT NULL,
  domain text UNIQUE,
  industry text,
  employee_count int,
  revenue_range text,
  tech_stack text[],
  icp_score int,
  enriched_at timestamptz,
  created_at timestamptz DEFAULT now()
)

Contacts

contacts (
  id uuid PRIMARY KEY,
  company_id uuid REFERENCES companies,
  email text UNIQUE,
  first_name text,
  last_name text,
  title text,
  linkedin_url text,
  lead_score int DEFAULT 0,
  status text DEFAULT 'new',
  created_at timestamptz DEFAULT now()
)

Campaign Events

campaign_events (
  id uuid PRIMARY KEY,
  contact_id uuid REFERENCES contacts,
  campaign_id uuid,
  channel text, -- email, linkedin, phone
  event_type text, -- sent, opened, replied, clicked, bounced
  metadata jsonb,
  external_id text UNIQUE, -- idempotency
  occurred_at timestamptz,
  created_at timestamptz DEFAULT now()
)

Design Principles

• Use UUIDs for all primary keys
• Soft delete with deleted_at column
• external_id on event tables for idempotency
• JSONB for flexible metadata
• Array columns for tags/stacks
• Indexes on foreign keys and lookup columns
• RLS policies for multi-tenant access