Marketing — Data model

13 tables · auto-generated from migrations-v2/*.sql — do not edit by hand.

ER diagram

%%{init: {"theme":"base","themeVariables":{"darkMode":true,"background":"#1e1e1e","primaryColor":"#2d2d30","primaryBorderColor":"#569cd6","primaryTextColor":"#d4d4d4","lineColor":"#6796c6","secondaryColor":"#3a3d41","secondaryBorderColor":"#dcdcaa","secondaryTextColor":"#d4d4d4","tertiaryColor":"#252526","tertiaryBorderColor":"#569cd6","noteBkgColor":"#3a3d41","noteTextColor":"#dcdcaa","clusterBkg":"#252526","clusterBorder":"#569cd6","titleColor":"#569cd6","attributeBackgroundColorOdd":"#252526","attributeBackgroundColorEven":"#2d2d30"},"er":{"useMaxWidth":false}}}%%
erDiagram
  MARKETING_ATTRIBUTION_LINK {
    integer id PK
    text company_id FK
    integer campaign_id FK
    integer campaign_member_id FK
    integer party_id FK
    text attributed_doctype
    text attributed_doc_id
    text conversion_state
  }
  MARKETING_CAMPAIGN {
    integer id PK
    text company_id FK
    integer parent_campaign_id FK
    text code
    text name
    text objective
    text status
    text starts_on
  }
  MARKETING_CAMPAIGN_COST {
    integer id PK
    text company_id FK
    integer campaign_id FK
    integer kernel_document_id FK
    integer kernel_journal_entry_id FK
    text cost_type
    numeric amount
    text currency_code
  }
  MARKETING_CAMPAIGN_MEMBER {
    integer id PK
    text company_id FK
    integer campaign_id FK
    integer party_id FK
    integer segment_id FK
    text channel
    text channel_address
    text member_status
  }
  MARKETING_CONSENT_RECORD {
    integer id PK
    text company_id FK
    integer party_id FK
    text lead_ref
    text purpose
    text action
    text evidence
    text policy_version
  }
  MARKETING_LEAD {
    integer id PK
    text company_id FK
    integer source_form_id FK
    integer matched_party_id FK
    integer campaign_id FK
    text doc_no
    text captured_fields
    text utm_snapshot
  }
  MARKETING_LEAD_CAPTURE_FORM {
    integer id PK
    text company_id FK
    integer campaign_id FK
    text code
    text field_schema
    text consent_config
    integer double_opt_in_required
    text target
  }
  MARKETING_SEGMENT {
    integer id PK
    text company_id FK
    text code
    text name
    text segment_kind
    text filter_expr
    text include_segment_ids
    text exclude_segment_ids
  }
  MARKETING_SEGMENT_SNAPSHOT {
    integer id PK
    text company_id FK
    integer segment_id FK
    integer campaign_id FK
    text reason
    integer member_count
    text snapshot_json
    integer taken_at
  }
  MARKETING_SUPPRESSION_ENTRY {
    integer id PK
    text company_id FK
    text channel
    text address
    text reason
    text source_ref
    integer effective_from
    integer released_at
  }
  MARKETING_TOUCH_EVENT {
    integer id PK
    text company_id FK
    integer party_id FK
    integer campaign_id FK
    integer tracked_link_id FK
    text visitor_key
    text source_code
    text medium_code
  }
  MARKETING_TRACKED_LINK {
    integer id PK
    text company_id FK
    integer campaign_id FK
    text code
    text target_url
    text utm_json
    text owner_ref
    integer active
  }
  MARKETING_UTM_TAXONOMY {
    integer id PK
    text company_id FK
    text kind
    text code
    text display_name
    integer active
    integer created_at
  }
  MARKETING_CAMPAIGN ||--o{ MARKETING_ATTRIBUTION_LINK : campaign
  MARKETING_CAMPAIGN_MEMBER ||--o{ MARKETING_ATTRIBUTION_LINK : campaign_member
  MARKETING_CAMPAIGN ||--o{ MARKETING_CAMPAIGN : parent_campaign
  MARKETING_CAMPAIGN ||--o{ MARKETING_CAMPAIGN_COST : campaign
  MARKETING_CAMPAIGN ||--o{ MARKETING_CAMPAIGN_MEMBER : campaign
  MARKETING_SEGMENT ||--o{ MARKETING_CAMPAIGN_MEMBER : segment
  MARKETING_LEAD_CAPTURE_FORM ||--o{ MARKETING_LEAD : source_form
  MARKETING_CAMPAIGN ||--o{ MARKETING_LEAD : campaign
  MARKETING_CAMPAIGN ||--o{ MARKETING_LEAD_CAPTURE_FORM : campaign
  MARKETING_SEGMENT ||--o{ MARKETING_SEGMENT_SNAPSHOT : segment
  MARKETING_CAMPAIGN ||--o{ MARKETING_SEGMENT_SNAPSHOT : campaign
  MARKETING_CAMPAIGN ||--o{ MARKETING_TOUCH_EVENT : campaign
  MARKETING_TRACKED_LINK ||--o{ MARKETING_TOUCH_EVENT : tracked_link
  MARKETING_CAMPAIGN ||--o{ MARKETING_TRACKED_LINK : campaign

Cross-module references

• MARKETING_ATTRIBUTION_LINK.company_id → COMPANY (core)
• MARKETING_ATTRIBUTION_LINK.party_id → PARTY (core)
• MARKETING_CAMPAIGN.company_id → COMPANY (core)
• MARKETING_CAMPAIGN_COST.company_id → COMPANY (core)
• MARKETING_CAMPAIGN_COST.kernel_document_id → DOCUMENT (core)
• MARKETING_CAMPAIGN_COST.kernel_journal_entry_id → JOURNAL_ENTRY (core)
• MARKETING_CAMPAIGN_MEMBER.company_id → COMPANY (core)
• MARKETING_CAMPAIGN_MEMBER.party_id → PARTY (core)
• MARKETING_CONSENT_RECORD.company_id → COMPANY (core)
• MARKETING_CONSENT_RECORD.party_id → PARTY (core)
• MARKETING_LEAD.company_id → COMPANY (core)
• MARKETING_LEAD.matched_party_id → PARTY (core)
• MARKETING_LEAD_CAPTURE_FORM.company_id → COMPANY (core)
• MARKETING_SEGMENT.company_id → COMPANY (core)
• MARKETING_SEGMENT_SNAPSHOT.company_id → COMPANY (core)
• MARKETING_SUPPRESSION_ENTRY.company_id → COMPANY (core)
• MARKETING_TOUCH_EVENT.company_id → COMPANY (core)
• MARKETING_TOUCH_EVENT.party_id → PARTY (core)
• MARKETING_TRACKED_LINK.company_id → COMPANY (core)
• MARKETING_UTM_TAXONOMY.company_id → COMPANY (core)

Data dictionary

erp_marketing_attribution_link · 14 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
campaign_id	integer	FK	erp_marketing_campaign
campaign_member_id	integer	FK	erp_marketing_campaign_member
party_id	integer	FK	erp_party
attributed_doctype	text
attributed_doc_id	text
conversion_state	text
attributed_revenue	numeric
revenue_currency_code	text
attribution_model	text
attributed_at	integer
evidence	text
created_by	text

erp_marketing_campaign · 18 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
code	text
name	text
objective	text
status	text
starts_on	text
ends_on	text
currency_code	text
budget_amount	numeric
default_utm_source	text
default_utm_medium	text
owner_ref	text
parent_campaign_id	integer	FK	erp_marketing_campaign
custom_fields	text
created_by	text
created_at	integer
updated_at	integer

erp_marketing_campaign_cost · 14 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
campaign_id	integer	FK	erp_marketing_campaign
cost_type	text
amount	numeric
currency_code	text
posting_date	text
description	text
source_ref	text
status	text
kernel_document_id	integer	FK	erp_document
kernel_journal_entry_id	integer	FK	erp_journal_entry
created_by	text
created_at	integer

erp_marketing_campaign_member · 12 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
campaign_id	integer	FK	erp_marketing_campaign
party_id	integer	FK	erp_party
segment_id	integer	FK	erp_marketing_segment
channel	text
channel_address	text
member_status	text
source	text
added_at	integer
added_by	text
custom_fields	text

erp_marketing_consent_record · 10 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
party_id	integer	FK	erp_party
lead_ref	text
purpose	text
action	text
evidence	text
policy_version	text
captured_channel	text
occurred_at	integer

erp_marketing_lead · 12 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
doc_no	text
source_form_id	integer	FK	erp_marketing_lead_capture_form
matched_party_id	integer	FK	erp_party
campaign_id	integer	FK	erp_marketing_campaign
captured_fields	text
utm_snapshot	text
state	text
handoff_ref	text
score_hint	numeric
created_at	integer

erp_marketing_lead_capture_form · 10 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
code	text
campaign_id	integer	FK	erp_marketing_campaign
field_schema	text
consent_config	text
double_opt_in_required	integer
target	text
active	integer
created_at	integer

erp_marketing_segment · 12 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
code	text
name	text
segment_kind	text
filter_expr	text
include_segment_ids	text
exclude_segment_ids	text
estimated_size	integer
last_evaluated_at	integer
active	integer
created_at	integer

erp_marketing_segment_snapshot · 8 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
segment_id	integer	FK	erp_marketing_segment
campaign_id	integer	FK	erp_marketing_campaign
reason	text
member_count	integer
snapshot_json	text
taken_at	integer

erp_marketing_suppression_entry · 8 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
channel	text
address	text
reason	text
source_ref	text
effective_from	integer
released_at	integer

erp_marketing_touch_event · 13 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
visitor_key	text
party_id	integer	FK	erp_party
campaign_id	integer	FK	erp_marketing_campaign
tracked_link_id	integer	FK	erp_marketing_tracked_link
source_code	text
medium_code	text
content_code	text
term_code	text
touch_kind	text
context_json	text
occurred_at	integer

erp_marketing_tracked_link · 9 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
code	text
campaign_id	integer	FK	erp_marketing_campaign
target_url	text
utm_json	text
owner_ref	text
active	integer
created_at	integer

erp_marketing_utm_taxonomy · 7 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
kind	text
code	text
display_name	text
active	integer
created_at	integer