CRM & Loyalty — Data model

17 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
  CRM_LOYALTY_CONSENT_LEDGER {
    integer id PK
    text company_id FK
    integer party_id FK
    text purpose
    text channel
    integer granted
    integer consented_at
    integer withdrawn_at
  }
  CRM_LOYALTY_DSAR {
    integer id PK
    text company_id FK
    integer party_id FK
    text request_type
    text status
    integer requested_at
    integer due_at
    integer completed_at
  }
  CRM_LOYALTY_LEAD {
    integer id PK
    text company_id FK
    integer merged_into FK
    integer converted_party_id FK
    text source_channel
    text raw_name
    text raw_company
    text raw_phone
  }
  CRM_LOYALTY_MEMBERSHIP {
    integer id PK
    text company_id FK
    integer party_id FK
    integer program_id FK
    text member_code
    integer joined_at
    text status
    text tier_code
  }
  CRM_LOYALTY_OPPORTUNITY {
    integer id PK
    text company_id FK
    integer party_id FK
    integer pipeline_id FK
    integer stage_id FK
    integer origin_lead_id FK
    text doc_no
    text status
  }
  CRM_LOYALTY_OPPORTUNITY_STAGE_EVENT {
    integer id PK
    text company_id FK
    integer opportunity_id FK
    integer from_stage_id FK
    integer to_stage_id FK
    text transition_kind
    text actor
    integer occurred_at
  }
  CRM_LOYALTY_PARTY_FACET {
    integer id PK
    text company_id FK
    integer party_id FK
    text facet_kind
    text lifecycle_status
    text source_ref
    text attributes
    integer created_at
  }
  CRM_LOYALTY_PIPELINE {
    integer id PK
    text company_id FK
    text name
    integer is_default
    text currency_code
    text status
    integer created_at
  }
  CRM_LOYALTY_PIPELINE_STAGE {
    integer id PK
    text company_id FK
    integer pipeline_id FK
    integer sequence
    text name
    numeric default_probability
    integer is_terminal_hint
    text entry_requirements
  }
  CRM_LOYALTY_POINT_BURN_ALLOCATION {
    integer id PK
    text company_id FK
    integer burn_entry_id FK
    integer earn_entry_id FK
    numeric points_consumed
    integer created_at
  }
  CRM_LOYALTY_POINT_LEDGER {
    integer id PK
    text company_id FK
    integer membership_id FK
    integer reverses_id FK
    integer kernel_document_id FK
    text entry_kind
    numeric points
    integer occurred_at
  }
  CRM_LOYALTY_PROGRAM {
    integer id PK
    text company_id FK
    text code
    text name
    text program_kind
    text trigger_mode
    text status
    text date_from
  }
  CRM_LOYALTY_RETENTION_POLICY {
    integer id PK
    text company_id FK
    text purpose
    text channel
    integer retention_days
    text action_after_retention
    text legal_basis
    text effective_from
  }
  CRM_LOYALTY_REWARD {
    integer id PK
    text company_id FK
    integer program_id FK
    text reward_code
    text name
    text reward_kind
    numeric points_cost
    numeric reward_value
  }
  CRM_LOYALTY_REWARD_REDEMPTION {
    integer id PK
    text company_id FK
    integer membership_id FK
    integer reward_id FK
    integer burn_entry_id FK
    numeric points_spent
    numeric reward_value
    integer occurred_at
  }
  CRM_LOYALTY_SEGMENT {
    integer id PK
    text company_id FK
    text code
    text name
    text criteria
    text status
    integer created_at
    integer updated_at
  }
  CRM_LOYALTY_TIER {
    integer id PK
    text company_id FK
    integer program_id FK
    text tier_code
    text name
    numeric threshold_points
    numeric earn_multiplier
    integer sequence
  }
  CRM_LOYALTY_LEAD ||--o{ CRM_LOYALTY_LEAD : merged_into
  CRM_LOYALTY_PROGRAM ||--o{ CRM_LOYALTY_MEMBERSHIP : program
  CRM_LOYALTY_PIPELINE ||--o{ CRM_LOYALTY_OPPORTUNITY : pipeline
  CRM_LOYALTY_PIPELINE_STAGE ||--o{ CRM_LOYALTY_OPPORTUNITY : stage
  CRM_LOYALTY_LEAD ||--o{ CRM_LOYALTY_OPPORTUNITY : origin_lead
  CRM_LOYALTY_OPPORTUNITY ||--o{ CRM_LOYALTY_OPPORTUNITY_STAGE_EVENT : opportunity
  CRM_LOYALTY_PIPELINE_STAGE ||--o{ CRM_LOYALTY_OPPORTUNITY_STAGE_EVENT : from_stage
  CRM_LOYALTY_PIPELINE_STAGE ||--o{ CRM_LOYALTY_OPPORTUNITY_STAGE_EVENT : to_stage
  CRM_LOYALTY_PIPELINE ||--o{ CRM_LOYALTY_PIPELINE_STAGE : pipeline
  CRM_LOYALTY_POINT_LEDGER ||--o{ CRM_LOYALTY_POINT_BURN_ALLOCATION : burn_entry
  CRM_LOYALTY_POINT_LEDGER ||--o{ CRM_LOYALTY_POINT_BURN_ALLOCATION : earn_entry
  CRM_LOYALTY_MEMBERSHIP ||--o{ CRM_LOYALTY_POINT_LEDGER : membership
  CRM_LOYALTY_POINT_LEDGER ||--o{ CRM_LOYALTY_POINT_LEDGER : reverses
  CRM_LOYALTY_PROGRAM ||--o{ CRM_LOYALTY_REWARD : program
  CRM_LOYALTY_MEMBERSHIP ||--o{ CRM_LOYALTY_REWARD_REDEMPTION : membership
  CRM_LOYALTY_REWARD ||--o{ CRM_LOYALTY_REWARD_REDEMPTION : reward
  CRM_LOYALTY_POINT_LEDGER ||--o{ CRM_LOYALTY_REWARD_REDEMPTION : burn_entry
  CRM_LOYALTY_PROGRAM ||--o{ CRM_LOYALTY_TIER : program

Cross-module references

• CRM_LOYALTY_CONSENT_LEDGER.company_id → COMPANY (core)
• CRM_LOYALTY_CONSENT_LEDGER.party_id → PARTY (core)
• CRM_LOYALTY_DSAR.company_id → COMPANY (core)
• CRM_LOYALTY_DSAR.party_id → PARTY (core)
• CRM_LOYALTY_LEAD.company_id → COMPANY (core)
• CRM_LOYALTY_LEAD.converted_party_id → PARTY (core)
• CRM_LOYALTY_MEMBERSHIP.company_id → COMPANY (core)
• CRM_LOYALTY_MEMBERSHIP.party_id → PARTY (core)
• CRM_LOYALTY_OPPORTUNITY.company_id → COMPANY (core)
• CRM_LOYALTY_OPPORTUNITY.party_id → PARTY (core)
• CRM_LOYALTY_OPPORTUNITY_STAGE_EVENT.company_id → COMPANY (core)
• CRM_LOYALTY_PARTY_FACET.company_id → COMPANY (core)
• CRM_LOYALTY_PARTY_FACET.party_id → PARTY (core)
• CRM_LOYALTY_PIPELINE.company_id → COMPANY (core)
• CRM_LOYALTY_PIPELINE_STAGE.company_id → COMPANY (core)
• CRM_LOYALTY_POINT_BURN_ALLOCATION.company_id → COMPANY (core)
• CRM_LOYALTY_POINT_LEDGER.company_id → COMPANY (core)
• CRM_LOYALTY_POINT_LEDGER.kernel_document_id → DOCUMENT (core)
• CRM_LOYALTY_PROGRAM.company_id → COMPANY (core)
• CRM_LOYALTY_RETENTION_POLICY.company_id → COMPANY (core)
• CRM_LOYALTY_REWARD.company_id → COMPANY (core)
• CRM_LOYALTY_REWARD_REDEMPTION.company_id → COMPANY (core)
• CRM_LOYALTY_SEGMENT.company_id → COMPANY (core)
• CRM_LOYALTY_TIER.company_id → COMPANY (core)

Data dictionary

erp_crm_loyalty_consent_ledger · 14 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
party_id	integer	FK	erp_party
purpose	text
channel	text
granted	integer
consented_at	integer
withdrawn_at	integer
actor	text
evidence_ref	text
evidence_ip	text
text_version	text
idempotency_key	text
created_at	integer

erp_crm_loyalty_dsar · 12 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
party_id	integer	FK	erp_party
request_type	text
status	text
requested_at	integer
due_at	integer
completed_at	integer
evidence_ref	text
response_ref	text
actor	text
created_at	integer

erp_crm_loyalty_lead · 19 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
source_channel	text
raw_name	text
raw_company	text
raw_phone	text
raw_email	text
raw_line_user_id	text
owner	text
status	text
disqualify_reason	text
score	numeric
dedup_fingerprint	text
merged_into	integer	FK	erp_crm_loyalty_lead
converted_party_id	integer	FK	erp_party
converted_opportunity_id	integer
attribution_touches	text
created_at	integer
updated_at	integer

erp_crm_loyalty_membership · 13 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
party_id	integer	FK	erp_party
program_id	integer	FK	erp_crm_loyalty_program
member_code	text
joined_at	integer
status	text
tier_code	text
external_system	text
external_member_id	text
channel_identities	text
created_at	integer
updated_at	integer

erp_crm_loyalty_opportunity · 20 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
doc_no	text
party_id	integer	FK	erp_party
pipeline_id	integer	FK	erp_crm_loyalty_pipeline
stage_id	integer	FK	erp_crm_loyalty_pipeline_stage
status	text
expected_value	numeric
currency_code	text
probability	numeric
manual_probability	integer
expected_close	text
owner	text
team	text
lost_reason	text
competitor_note	text
origin_lead_id	integer	FK	erp_crm_loyalty_lead
won_order_ref	text
created_at	integer
updated_at	integer

erp_crm_loyalty_opportunity_stage_event · 9 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
opportunity_id	integer	FK	erp_crm_loyalty_opportunity
from_stage_id	integer	FK	erp_crm_loyalty_pipeline_stage
to_stage_id	integer	FK	erp_crm_loyalty_pipeline_stage
transition_kind	text
actor	text
occurred_at	integer
note	text

erp_crm_loyalty_party_facet · 9 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
party_id	integer	FK	erp_party
facet_kind	text
lifecycle_status	text
source_ref	text
attributes	text
created_at	integer
updated_at	integer

erp_crm_loyalty_pipeline · 7 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
name	text
is_default	integer
currency_code	text
status	text
created_at	integer

erp_crm_loyalty_pipeline_stage · 11 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
pipeline_id	integer	FK	erp_crm_loyalty_pipeline
sequence	integer
name	text
default_probability	numeric
is_terminal_hint	integer
entry_requirements	text
rot_after_seconds	integer
auto_actions	text
created_at	integer

erp_crm_loyalty_point_burn_allocation · 6 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
burn_entry_id	integer	FK	erp_crm_loyalty_point_ledger
earn_entry_id	integer	FK	erp_crm_loyalty_point_ledger
points_consumed	numeric
created_at	integer

erp_crm_loyalty_point_ledger · 17 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
membership_id	integer	FK	erp_crm_loyalty_membership
entry_kind	text
points	numeric
occurred_at	integer
expiry_at	integer
origin_type	text
origin_id	text
origin_module	text
idempotency_key	text
reverses_id	integer	FK	erp_crm_loyalty_point_ledger
value_at_grant	numeric
note	text
actor	text
kernel_document_id	integer	FK	erp_document
created_at	integer

erp_crm_loyalty_program · 19 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
code	text
name	text
program_kind	text
trigger_mode	text
status	text
date_from	text
date_to	text
expiry_mode	text
expiry_days	integer
point_unit_name	text
currency_code	text
currency_per_point	numeric
liability_transaction_kind	text
redemption_credit_kind	text
portal_visible	integer
created_at	integer
updated_at	integer

erp_crm_loyalty_retention_policy · 9 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
purpose	text
channel	text
retention_days	integer
action_after_retention	text
legal_basis	text
effective_from	text
created_at	integer

erp_crm_loyalty_reward · 11 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
program_id	integer	FK	erp_crm_loyalty_program
reward_code	text
name	text
reward_kind	text
points_cost	numeric
reward_value	numeric
currency_code	text
active	integer
created_at	integer

erp_crm_loyalty_reward_redemption · 13 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
membership_id	integer	FK	erp_crm_loyalty_membership
reward_id	integer	FK	erp_crm_loyalty_reward
burn_entry_id	integer	FK	erp_crm_loyalty_point_ledger
points_spent	numeric
reward_value	numeric
occurred_at	integer
origin_type	text
origin_id	text
idempotency_key	text
actor	text
created_at	integer

erp_crm_loyalty_segment · 8 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
code	text
name	text
criteria	text
status	text
created_at	integer
updated_at	integer

erp_crm_loyalty_tier · 9 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
program_id	integer	FK	erp_crm_loyalty_program
tier_code	text
name	text
threshold_points	numeric
earn_multiplier	numeric
sequence	integer
created_at	integer