Activity Model

Overview

This document describes the recommended data model for activities, contacts, people, and organizations in Reply Pilot.

This page is the source of truth for the activity/contact data model. When the schema changes in this area, update this page and regenerate the diagrams with rp.

The model is intentionally split into two layers:

  • identity layer: who the company, person, email address, or phone number is
  • activity layer: what happened, when it happened, and who participated

The design is inspired by the OFBiz approach built around Party, ContactMech, PartyRelationship, and CommunicationEvent, but simplified for Reply Pilot and adapted to PostgreSQL with standard BIGINT primary keys.

The main design goal is:

  • store every incoming or manually created event separately
  • store every discovered email address or phone number separately
  • link activities to parties and contact mechanisms without losing raw source data
  • support deduplication over email addresses, phone numbers, people, and organizations
  • keep the timeline query simple

Typical examples:

  • an incoming email creates one activity row and one activity_email row
  • every address from from, reply-to, to, cc, and bcc is stored as a separate contact_mech
  • participants of the email are linked through activity_participant
  • a phone call creates one activity row and one activity_call row
  • the caller and callee are linked through activity_participant

Design Principles

  • party represents a business identity, either a person or an organization
  • contact_mech represents a communication endpoint, for example an email address or a phone number
  • party_contact_mech links a party to a contact mechanism
  • party_relationship links one party to another party
  • activity is the shared timeline record
  • activity_* tables store per-type details
  • activity_participant links an activity to parties and contact mechanisms
  • deduplication belongs to the contact mechanism layer, not to activity
  • code domains such as participant roles and relationship types stay as application constants in V1
  • V1 does not introduce a separate activity_thread table
  • a single activity must be insertable even when a party is still unknown

Identity Layer

party

Purpose:

  • root identity table for both people and organizations

Columns:

  • id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  • party_type_code TEXT NOT NULL
  • display_name TEXT NOT NULL DEFAULT ''
  • status_code TEXT NOT NULL DEFAULT 'ACTIVE'
  • merged_into_party_id BIGINT NULL REFERENCES public.party (id) ON DELETE SET NULL
  • created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
  • updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

Recommended values for party_type_code:

  • PERSON
  • ORGANIZATION
  • TEAM
  • UNKNOWN

Notes:

  • party is the stable identity layer used everywhere else
  • merges should not delete rows immediately; use merged_into_party_id

party_person

Purpose:

  • person-specific details

Columns:

  • party_id BIGINT PRIMARY KEY REFERENCES public.party (id) ON DELETE CASCADE
  • first_name TEXT NOT NULL DEFAULT ''
  • last_name TEXT NOT NULL DEFAULT ''
  • middle_name TEXT NOT NULL DEFAULT ''

party_organization

Purpose:

  • organization-specific details

Columns:

  • party_id BIGINT PRIMARY KEY REFERENCES public.party (id) ON DELETE CASCADE
  • legal_name TEXT NOT NULL DEFAULT ''
  • normalized_legal_name TEXT NOT NULL DEFAULT ''
  • company_registration_number TEXT NOT NULL DEFAULT ''
  • tax_identifier TEXT NOT NULL DEFAULT ''
  • show_by_default BOOLEAN NOT NULL DEFAULT TRUE

Notes:

  • show_by_default = FALSE skryje organizaci z beznych seznamu, odvozenych company lookupu a company search dokumentu, ale neblokuje primy pristup na detail firmy

party_role

Purpose:

  • attach business roles to a party

Columns:

  • id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  • party_id BIGINT NOT NULL REFERENCES public.party (id) ON DELETE CASCADE
  • role_type_code TEXT NOT NULL
  • created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

Recommended values for role_type_code:

  • CUSTOMER
  • SUPPLIER
  • CONTACT
  • EMPLOYEE
  • LEAD

Constraint:

  • UNIQUE (party_id, role_type_code)

party_identifier

Purpose:

  • store deduplication and lookup identifiers for a party

Columns:

  • id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  • party_id BIGINT NOT NULL REFERENCES public.party (id) ON DELETE CASCADE
  • identifier_type_code TEXT NOT NULL
  • value_raw TEXT NOT NULL
  • value_normalized TEXT NOT NULL
  • created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

Recommended values for identifier_type_code:

  • COMPANY_REGISTRATION_NUMBER
  • TAX_IDENTIFIER
  • DOMAIN
  • WEBSITE
  • EXTERNAL_ID
  • CME_DODAVATEL_ID

Constraint:

  • UNIQUE (identifier_type_code, value_normalized)

party_cme_match

Purpose:

  • keep the latest known CME lookup result for one organization

Columns:

  • party_id BIGINT PRIMARY KEY REFERENCES public.party (id) ON DELETE CASCADE
  • match_state TEXT NOT NULL DEFAULT 'NO_MATCH'
  • cme_record_type TEXT NOT NULL DEFAULT ''
  • integration_state TEXT NOT NULL DEFAULT ''
  • cme_dodavatel_id BIGINT NULL
  • match_reason TEXT NOT NULL DEFAULT ''
  • matched_by_ico BOOLEAN NOT NULL DEFAULT FALSE
  • matched_by_dic BOOLEAN NOT NULL DEFAULT FALSE
  • matched_by_domain BOOLEAN NOT NULL DEFAULT FALSE
  • matched_by_name BOOLEAN NOT NULL DEFAULT FALSE
  • matched_ico TEXT NOT NULL DEFAULT ''
  • matched_dic TEXT NOT NULL DEFAULT ''
  • matched_domain TEXT NOT NULL DEFAULT ''
  • matched_name TEXT NOT NULL DEFAULT ''
  • checked_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
  • last_error TEXT NOT NULL DEFAULT ''

Recommended values for match_state:

  • NO_MATCH
  • MATCHED
  • AMBIGUOUS
  • ERROR

Notes:

  • this table stores current integration state, not a full audit log
  • integration_state mirrors current CME interpretation such as SUPPLIER_INTEGRATED, DODAVATEL_ONLY, or ONBOARDING_ONLY
  • stable CME supplier ids still belong into party_identifier as CME_DODAVATEL_ID

party_cme_check_queue

Purpose:

  • queue organizations waiting for CME verification

Columns:

  • party_id BIGINT PRIMARY KEY REFERENCES public.party (id) ON DELETE CASCADE
  • reason TEXT NOT NULL DEFAULT ''
  • priority INTEGER NOT NULL DEFAULT 100
  • requested_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
  • not_before TIMESTAMPTZ NOT NULL DEFAULT NOW()
  • attempt_count INTEGER NOT NULL DEFAULT 0
  • locked_at TIMESTAMPTZ NULL

Notes:

  • used by the background worker to process only pending or stale organizations
  • avoids minute-based full scans over the whole party_organization table

Contact Mechanisms

This view focuses on party and contact tables only.

Contact model schema

contact_mech

Purpose:

  • generic communication endpoint
  • shared root record used for activity linking and contact subtype storage

Columns:

  • id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  • contact_mech_type_code TEXT NOT NULL
  • created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
  • updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

Recommended values for contact_mech_type_code:

  • EMAIL
  • PHONE
  • WEB
  • ADDRESS

Notes:

  • contact_mech must exist even when no matching party exists yet
  • V1 does not define a dedicated contact mechanism lifecycle beyond the fields described in this schema

contact_mech_email

Purpose:

  • email-specific fields

Columns:

  • contact_mech_id BIGINT PRIMARY KEY REFERENCES public.contact_mech (id) ON DELETE CASCADE
  • email TEXT NOT NULL

Constraints:

  • UNIQUE (email)

Notes:

  • email must be stored in normalized form and is the exact deduplication key
  • the original imported header value stays in activity_participant.address_raw

contact_mech_phone

Purpose:

  • phone-specific fields

Columns:

  • contact_mech_id BIGINT PRIMARY KEY REFERENCES public.contact_mech (id) ON DELETE CASCADE
  • phone_raw TEXT NOT NULL
  • phone_e164 TEXT NOT NULL
  • country_code TEXT NOT NULL DEFAULT ''

Constraints:

  • UNIQUE (phone_e164)

Notes:

  • phone_e164 is the exact deduplication key for phone numbers

party_contact_mech

Purpose:

  • assign a contact mechanism to a party

Columns:

  • id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  • party_id BIGINT NOT NULL REFERENCES public.party (id) ON DELETE CASCADE
  • contact_mech_id BIGINT NOT NULL REFERENCES public.contact_mech (id) ON DELETE CASCADE
  • verified BOOLEAN NOT NULL DEFAULT FALSE
  • is_primary BOOLEAN NOT NULL DEFAULT FALSE
  • from_date TIMESTAMPTZ NOT NULL DEFAULT NOW()
  • thru_date TIMESTAMPTZ NULL
  • created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

Constraints:

  • UNIQUE (party_id, contact_mech_id)

Notes:

  • shared mailboxes such as sales@company.com can be linked directly to an organization party and, when needed, also to a person party
  • a person can have multiple email addresses and phone numbers

party_contact_mech_purpose

Purpose:

  • assign business meaning to the party/contact linkage

Columns:

  • id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  • party_contact_mech_id BIGINT NOT NULL REFERENCES public.party_contact_mech (id) ON DELETE CASCADE
  • purpose_code TEXT NOT NULL
  • created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

Recommended values for purpose_code:

  • PRIMARY_EMAIL
  • WORK_EMAIL
  • BILLING_EMAIL
  • PRIMARY_PHONE
  • WORK_PHONE

Constraint:

  • UNIQUE (party_contact_mech_id, purpose_code)

Party Relationships

party_relationship

Purpose:

  • link one party to another party with direction, role, and validity

Columns:

  • id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  • from_party_id BIGINT NOT NULL REFERENCES public.party (id) ON DELETE CASCADE
  • to_party_id BIGINT NOT NULL REFERENCES public.party (id) ON DELETE CASCADE
  • relationship_type_code TEXT NOT NULL
  • from_role_type_code TEXT NOT NULL DEFAULT ''
  • to_role_type_code TEXT NOT NULL DEFAULT ''
  • from_date TIMESTAMPTZ NOT NULL DEFAULT NOW()
  • thru_date TIMESTAMPTZ NULL
  • created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

Recommended values for relationship_type_code:

  • EMPLOYMENT
  • CONTACT_FOR
  • SUPPLIER_RELATIONSHIP
  • CUSTOMER_RELATIONSHIP
  • REPORTS_TO

Examples:

  • person -> organization via EMPLOYMENT
  • person -> organization via CONTACT_FOR
  • organization -> organization via SUPPLIER_RELATIONSHIP

Activity Layer

This view focuses on activity tables only.

Activity model schema

activity_type

Purpose:

  • list of supported activity types

Columns:

  • id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  • code TEXT NOT NULL UNIQUE
  • name TEXT NOT NULL
  • created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

Initial seed:

  • email
  • call
  • meeting
  • note

activity

Purpose:

  • shared timeline record for every event

Columns:

  • id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  • activity_type_id BIGINT NOT NULL REFERENCES public.activity_type (id)
  • primary_party_id BIGINT NULL REFERENCES public.party (id) ON DELETE SET NULL
  • occurred_at TIMESTAMPTZ NOT NULL
  • title TEXT NOT NULL DEFAULT ''
  • summary TEXT NOT NULL DEFAULT ''
  • status_code TEXT NOT NULL DEFAULT 'ACTIVE'
  • created_by_user_id BIGINT NULL REFERENCES public.app_user (id) ON DELETE SET NULL
  • created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
  • updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

Notes:

  • primary_party_id is optional because an activity may arrive before the system knows the right person or organization
  • in V1 it stays nullable, and activity_participant is the primary source of party/contact linkage
  • occurred_at is the primary timeline sort key

Activity Detail Tables

Each detail table must use a strict one-to-one relationship:

  • activity_id BIGINT PRIMARY KEY REFERENCES public.activity (id) ON DELETE CASCADE

activity_email

Purpose:

  • email message details

Columns:

  • activity_id BIGINT PRIMARY KEY REFERENCES public.activity (id) ON DELETE CASCADE
  • provider TEXT NOT NULL DEFAULT 'gmail'
  • external_message_id TEXT NOT NULL
  • external_thread_id TEXT NOT NULL DEFAULT ''
  • internet_message_id TEXT NOT NULL DEFAULT ''
  • subject TEXT NOT NULL DEFAULT ''
  • body_text TEXT NOT NULL DEFAULT ''
  • body_html TEXT NOT NULL DEFAULT ''
  • sent_at TIMESTAMPTZ NULL
  • received_at TIMESTAMPTZ NULL

Constraints:

  • UNIQUE (provider, external_message_id)

Important rule:

  • one activity_email row should represent one email message, not one entire email thread
  • the thread is represented by external_thread_id
  • V1 does not introduce a separate activity_thread table

activity_call

Purpose:

  • phone or online call details

Columns:

  • activity_id BIGINT PRIMARY KEY REFERENCES public.activity (id) ON DELETE CASCADE
  • started_at TIMESTAMPTZ NOT NULL
  • ended_at TIMESTAMPTZ NULL
  • direction_code TEXT NOT NULL DEFAULT ''
  • notes TEXT NOT NULL DEFAULT ''

Recommended values for direction_code:

  • INBOUND
  • OUTBOUND

activity_meeting

Purpose:

  • meeting details

Columns:

  • activity_id BIGINT PRIMARY KEY REFERENCES public.activity (id) ON DELETE CASCADE
  • started_at TIMESTAMPTZ NOT NULL
  • ended_at TIMESTAMPTZ NULL
  • location TEXT NOT NULL DEFAULT ''
  • meeting_url TEXT NOT NULL DEFAULT ''
  • notes TEXT NOT NULL DEFAULT ''

activity_note

Purpose:

  • manually entered note details

Columns:

  • activity_id BIGINT PRIMARY KEY REFERENCES public.activity (id) ON DELETE CASCADE
  • note_text TEXT NOT NULL

Activity Participants

activity_participant

Purpose:

  • link an activity to parties and contact mechanisms
  • preserve raw imported values from email headers or call sources
  • connect the activity layer to the contact_mech model

Columns:

  • id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  • activity_id BIGINT NOT NULL REFERENCES public.activity (id) ON DELETE CASCADE
  • participant_role_code TEXT NOT NULL
  • party_id BIGINT NULL REFERENCES public.party (id) ON DELETE SET NULL
  • contact_mech_id BIGINT NULL REFERENCES public.contact_mech (id) ON DELETE SET NULL
  • display_name_raw TEXT NOT NULL DEFAULT ''
  • address_raw TEXT NOT NULL DEFAULT ''
  • sort_order INTEGER NOT NULL DEFAULT 0
  • is_internal BOOLEAN NOT NULL DEFAULT FALSE
  • created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

Recommended values for participant_role_code:

  • FROM
  • REPLY_TO
  • TO
  • CC
  • BCC
  • CALLER
  • CALLEE
  • ORGANIZER
  • ATTENDEE
  • AUTHOR

Recommended constraints:

  • CHECK (party_id IS NOT NULL OR contact_mech_id IS NOT NULL OR address_raw <> '')

Notes:

  • contact_mech_id is the critical link for matching imported email addresses or phone numbers to the deduplicated contact layer
  • party_id is optional because the correct person or organization may be resolved later
  • display_name_raw and address_raw preserve the original source value

This view shows how activities link to contact and party records.

Activity contact linkage schema

Incoming Email Flow

This is the main integration point between activity and contact_mech.

When an incoming email arrives:

  1. create one activity row with type email
  2. create one activity_email row for the concrete email message
  3. for each address in from, reply-to, to, cc, and bcc:
  4. normalize the email address
  5. look up an existing contact_mech through contact_mech_email.email
  6. if it does not exist, create contact_mech and contact_mech_email
  7. try to resolve an existing party through party_contact_mech
  8. create one activity_participant row with the proper role code
  9. if no matching party exists yet, keep the participant linked only to contact_mech_id
  10. when a person or organization is resolved later, update or add the party_contact_mech linkage without rewriting the activity history

This approach ensures that:

  • every email address is stored independently
  • to, cc, and bcc are searchable
  • deduplication works across multiple activities
  • later identity resolution does not destroy source history

Incoming Call Flow

When a phone call is recorded:

  1. create one activity row with type call
  2. create one activity_call row
  3. normalize the caller and callee phone numbers
  4. look up an existing contact_mech through contact_mech_phone.phone_e164
  5. if it does not exist, create contact_mech and contact_mech_phone
  6. link both sides through activity_participant
  7. resolve party_id only when available

Deduplication Strategy

Exact Deduplication

Exact matching should be based on:

  • contact_mech_email (email)
  • contact_mech_phone (phone_e164)
  • party_identifier (identifier_type_code, value_normalized)

Soft Deduplication

Candidate duplicate people or organizations can be suggested using:

  • matching name plus same email domain
  • matching person name plus shared company relationship
  • matching normalized organization name plus domain
  • repeated reuse of the same contact_mech by an unresolved party candidate

Merge Strategy

Recommended merge strategy:

  • do not hard-delete duplicates immediately
  • set party.merged_into_party_id
  • optionally add the same pattern later for contact_mech

Integrity Rules

  • one detail row per activity
  • one activity email row per external message
  • one contact mechanism per normalized email or phone value
  • one party_contact_mech row per unique party/contact pair
  • an activity participant may point to a party, a contact mechanism, or both
  • activity inserts and detail inserts must happen in one transaction
  • imported raw values must be preserved even after later normalization
  • activity (occurred_at DESC)
  • activity (primary_party_id, occurred_at DESC)
  • activity_participant (activity_id, participant_role_code, sort_order)
  • activity_participant (contact_mech_id)
  • activity_participant (party_id)
  • contact_mech_email (email) unique index
  • contact_mech_phone (phone_e164) unique index
  • party_contact_mech (party_id, contact_mech_id) unique index
  • party_identifier (identifier_type_code, value_normalized) unique index
  • activity_email (provider, external_message_id) unique index
  • activity_email (external_thread_id)

Migration Status

The legacy company, company_contact, and communication_history tables were transitional only. The repo has already switched writes to the party/contact_mech/activity model and the legacy tables were removed after verification.

Historical migration path was:

  1. create the new party, contact_mech, and activity tables
  2. migrate each company into party with party_type_code = 'ORGANIZATION'
  3. migrate each company contact email into:
  4. contact_mech
  5. contact_mech_email
  6. party_contact_mech
  7. migrate each communication-history row into:
  8. activity
  9. activity_email
  10. move the legacy Gmail thread id into activity_email.external_thread_id
  11. if the old data was only thread-based, keep the first migrated version thread-based and later split it into message-based import
  12. switch the backend write path to the new tables
  13. remove the legacy tables after production verification

MVP Scope

The first implementation should include:

  • party
  • party_organization
  • contact_mech
  • contact_mech_email
  • party_contact_mech
  • activity_type
  • activity
  • activity_email
  • activity_participant
  • incoming email import linked to contact_mech

The first implementation does not need:

  • full postal address support
  • advanced role taxonomies
  • automatic duplicate merges
  • strict trigger-based enforcement between activity type and detail table