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 or sent email creates one activity row and one activity_email row
  • the same email may also create zero or more activity_email_attachment and activity_email_link rows
  • 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
  • for PERSON rows, display_name is derived from party_person.first_name and party_person.last_name; manual person forms do not edit it directly
  • 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 ''

Notes:

  • the canonical editable person name is first_name plus last_name
  • additional name text is folded into last_name; the unused middle_name compatibility column is removed by contract changeset 0060

party_person_name_legacy

Purpose:

  • one-time preservation table for person names before the migration that made person display names derived from first_name and last_name

Columns:

  • party_id BIGINT PRIMARY KEY
  • old_display_name TEXT NOT NULL DEFAULT ''
  • old_first_name TEXT NOT NULL DEFAULT ''
  • old_middle_name TEXT NOT NULL DEFAULT ''
  • old_last_name TEXT NOT NULL DEFAULT ''
  • migrated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

Notes:

  • this table is used only for audit and Liquibase rollback of the person-name normalization migration
  • application code should not read it during normal workflows

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 ''
  • registration_country_code TEXT NOT NULL DEFAULT ''
  • show_by_default BOOLEAN NOT NULL DEFAULT TRUE
  • import_email_communication BOOLEAN NOT NULL DEFAULT TRUE
  • default_email_sales_user_id BIGINT NULL REFERENCES public.app_user (id) ON DELETE SET NULL

Notes:

  • registration_country_code je ISO 3166-1 alpha-2 zeme, ve ktere je firma registrovana; prazdna hodnota znamena, ze historicky zaznam zatim nebyl overeny
  • show_by_default = FALSE skryje organizaci z beznych seznamu, odvozenych company lookupu a company search dokumentu, ale neblokuje primy pristup na detail firmy. Odkazy na souvisejici firmy u emailove zpravy, vlakna a reply tasku zobrazuji i skryte firmy v ramci opravneni uzivatele.
  • default_email_sales_user_id urcuje vychoziho obchodnika pro emailovou komunikaci konkretni spolecnosti; pokud neni vyplneny nebo uzivatel neni dostupny pro Jira assignment, aplikace pouzije globalni konfiguraci a dalsi fallbacky

import_email_communication gates database import of complete email threads from shared and personal Gmail mailboxes. It defaults to true for existing and new companies. Resolve every company using the canonical exact active company email, active person email through CONTACT_FOR, and manually configured domain rules. The policy includes hidden companies, so show_by_default = FALSE cannot bypass an import restriction. Skip a thread only when its nonempty company set has all flags false; any true flag permits the entire thread. Personal imports still exclude the mailbox owner and internal addresses and require an existing visible company match. This flag does not stop Gmail downloads/cache writes, sending, or access to existing history and attachments. It does not change company visibility or permissions.

vat_jurisdiction

Purpose:

  • versioned catalog of supported VAT jurisdiction prefixes and their countries
  • expose the local field label used by company clients

Columns:

  • code VARCHAR(2) PRIMARY KEY
  • country_code CHAR(2) NOT NULL
  • local_name TEXT NOT NULL

Constraints and seed rules:

  • both codes contain exactly two uppercase ASCII letters and local_name is nonblank
  • the 2026-08-21 seed follows the European Commission VIES jurisdiction list and local-name catalog
  • EL maps to country GR, XI maps to country GB, and CZ.local_name is DIČ

party_vat_registration

Purpose:

  • store a party's jurisdiction-scoped VAT registrations without treating a VAT value as globally owned by one party
  • retain the submitted representation alongside its canonical comparison form

Columns:

  • id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  • party_id BIGINT NOT NULL REFERENCES public.party (id) ON DELETE CASCADE
  • vat_jurisdiction_code VARCHAR(2) NOT NULL REFERENCES public.vat_jurisdiction (code) ON DELETE RESTRICT
  • value_raw TEXT NOT NULL
  • value_normalized TEXT NOT NULL
  • created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
  • updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

Constraints:

  • UNIQUE (party_id, vat_jurisdiction_code)
  • nonblank raw value, normalized value matching ^[A-Z]{2}[A-Z0-9]+$, and a normalized prefix equal to vat_jurisdiction_code
  • a non-unique index on value_normalized

Item 34.2 dual-writes valid manual, lead-promotion, and CME-promotion values to this model and to the legacy organization scalar. It does not backfill historical scalar-only rows. Equal normalized values on different parties are allowed and never represent ownership or an automatic match.

Item 34.3 backfilled historical values only from the organization scalar. It preferred a valid scalar on the active merge target; otherwise it accepted one distinct valid merged-source value per effective party and jurisdiction. Equivalent merged sources were counted as permitted deduplication, conflicting ones were reported and discarded, and target conflicts aborted the complete apply. The operation was dry-run by default and read only the organization scalar and VAT registration collection. It did not access legacy party_identifier.TAX_IDENTIFIER rows, CME snapshots, or supplier facts.

Item 34.5 removes legacy TAX identifiers from company read models, UI identity rows, search aggregation, backfill reporting, and wholesale matching. Wholesale VAT lookup now reads party_vat_registration. Immutable supplier evidence and AI extraction schemas may retain the TAX_IDENTIFIER classification, but it is not a company-identity source or a manual review option.

Item 34.6 deployed that cutoff and applied only contract changeset 0068 after an isolated restore test and an exact 318-row production gate. The cleanup audit snapshots all 318 deleted rows and records 318 -> 0; the count remained zero after a company save and scheduled CME synchronization. The organization scalar and VAT registration collection remained intact for the later collection cutover.

Item 34.10 removes the scalar company JSON/form/DTO adapter and the completed one-off backfill command. Item 34.11 recovery-tested a fresh production backup and applied only contract changeset 0069: its preconditions verified that every syntactically valid historical scalar was represented on its effective party, with zero registrations on merged parties and zero legacy TAX_IDENTIFIER rows. Production now has 4,929 parties, 3,895 organizations, 1,068 VAT registrations, and no organization scalar column. CME source snapshots keep their separate party_cme_source.tax_identifier field.

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_outreach_policy

Purpose:

  • store explicit commercial outreach policy for one organization

Columns:

  • party_id BIGINT PRIMARY KEY REFERENCES public.party (id) ON DELETE CASCADE
  • status_code TEXT NOT NULL DEFAULT 'ACTIVE_LEAD'
  • note TEXT NOT NULL DEFAULT ''
  • set_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()

Recommended values for status_code:

  • ACTIVE_LEAD
  • DO_NOT_CONTACT
  • DISQUALIFIED

Notes:

  • this table is the source of truth for explicit commercial decisions such as do not contact
  • show_by_default = FALSE may still be used as a UI-level hide flag, but not as the business reason itself
  • operators should always attach a human note when switching a company to DO_NOT_CONTACT

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
  • GLN
  • DOMAIN
  • WEBSITE
  • EXTERNAL_ID
  • VENDOR_CODE
  • LEGACY_UNRESOLVED_COMPANY_REGISTRATION_NUMBER pro auditni karantenu historickych hodnot; nesmi se pouzivat pro matching nebo ownership
  • NATIONAL_BUSINESS_IDENTIFIER pro narodni firemni identifikator mimo CR
  • COMMERCIAL_REGISTER_NUMBER pro overeny zahranicni obchodni rejstrik; value_normalized ma issuer scope <ISO zeme>:<REJSTRIK>:<ODDIL>:<CISLO>

TAX_IDENTIFIER is not a supported authoritative value in this table. Contract changeset 0068 stores an exact JSONB snapshot plus pre/post counts in party_tax_identifier_cleanup_audit and then deletes the legacy rows. Its local rollback restores every original row and drops the audit table. Production item 34.6 applied it after the zero-consumer audit and fresh recovery-tested backup; the production audit records 318 -> 0, and subsequent company/CME writes did not recreate a legacy row.

DOMAIN is a manual assertion that the organization owns the exact domain. It must not be inferred from an imported email address or created as a side effect of email-task creation. RP-4121 changeset 0070 snapshots and deletes the known-invalid shared-provider values googlemail.com, volny.cz, and atlas.cz; this one-off data cleanup is not a runtime blacklist.

Constraint:

  • UNIQUE (identifier_type_code, value_normalized)
  • COMPANY_REGISTRATION_NUMBER ma tvar osmi ASCII cislic
  • partial unique index dovoluje nejvyse jeden COMPANY_REGISTRATION_NUMBER na party

Current ICO write contract:

  • create/edit, lead import a CME sync odstrani whitespace a novou nebo zmenenou neprazdnou hodnotu COMPANY_REGISTRATION_NUMBER prijme jen jako osm ASCII cislic
  • stejna DB transakce udrzuje kanonicky identifikator; konflikt vlastnictvi rollbackne celou mutaci
  • historicka nevalidni nebo kolizni hodnota je oddelena v auditni karantene
  • importni writer neprepise jine neprazdne authoritative ICO; malformed nebo kolizni raw hodnota zustane v lead/CME source auditu s chybou
  • merge povoli nejvyse jednu ruznou autoritativni hodnotu pres obe firmy a dve ruzne hodnoty odmitne pred presunem vazeb
  • expand migrace zachova unresolved legacy text v karantennim value_raw a vlozi jen platne jednoznacne hodnoty do kanonickeho typu
  • kanonicky kod ceskeho ICO je COMPANY_REGISTRATION_NUMBER; zahranicni obchodni rejstrik je oddeleny issuer-scoped typ a nesmi se validovat jako ICO
  • rucni UI uklada slovenske ICO jako NATIONAL_BUSINESS_IDENTIFIER s SK:ICO:<CISLO>, polske KRS/REGON/NIP jako PL:KRS:<CISLO>, PL:REGON:<CISLO> a PL:NIP:<CISLO> a nemecky Handelsregister jako DE:<SOUD>:<HRA|HRB>:<CISLO>; pole jsou soucasti editace firmy a ukladaji se atomicky se zemi registrace
  • party_organization uz nema legacy ICO sloupec; ceske ICO se uklada pouze jako party_identifier

party_cme_source

Purpose:

  • represent an imported CME DODAVATEL or OSLOVENI row and optionally link it to one local organization
  • preserve the CME salesperson and reservation/contact timestamps separately from the local default email salesperson
  • preserve active CME supplier contacts as an unverified company_contacts JSON array on the CME source row; synchronization never promotes those values into local contact_mech / party_contact_mech records
  • keep CME identity exclusively in (source_type, source_id); CME IDs do not belong in party_identifier

The primary key is (source_type, source_id). Active rows have missing_since IS NULL; unmatched or ambiguous rows may have party_id IS NULL and an explanation in last_error. Each company_contacts item contains the CME contact ID, name, email, phone and note. A later synchronization replaces this snapshot but does not edit or remove contacts that were previously imported into the local contact model.

Party Requirement Tracking

This view extends the party/activity model with requirement tracking for supplier onboarding and similar workflows.

requirement_type

Purpose:

  • list supported requirement codes tracked for organizations

Columns:

  • code TEXT PRIMARY KEY
  • name TEXT NOT NULL
  • sort_order INTEGER NOT NULL UNIQUE
  • created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

Recommended values for code:

  • PRICE_LIST
  • TOP10
  • BUSINESS_TERMS
  • SUPPLIER_IDENTIFIER

ENLISTMENT_TABLE and FEED are intentionally not requirement codes anymore. Their only source of truth is the newest task_jira_supplier_onboarding row linked through task_jira.party_id, ordered by task.created_at DESC, task.id DESC.

requirement_state_type

Purpose:

  • list supported aggregate states for one requirement on one party

Columns:

  • code TEXT PRIMARY KEY
  • name TEXT NOT NULL
  • sort_order INTEGER NOT NULL UNIQUE
  • created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

Recommended values for code:

  • UNKNOWN
  • REQUESTED
  • CANDIDATE_RECEIVED
  • CONFIRMED_RECEIVED
  • REJECTED
  • NEEDS_REVIEW

requirement_evidence_source_type

Purpose:

  • list supported evidence sources

Columns:

  • code TEXT PRIMARY KEY
  • name TEXT NOT NULL
  • sort_order INTEGER NOT NULL UNIQUE
  • created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

Recommended values for code:

  • EMAIL_THREAD
  • EMAIL_MESSAGE
  • EMAIL_ATTACHMENT
  • MANUAL
  • IMPORT

requirement_evidence_verdict_type

Purpose:

  • list supported verdicts for one evidence item

Columns:

  • code TEXT PRIMARY KEY
  • name TEXT NOT NULL
  • sort_order INTEGER NOT NULL UNIQUE
  • created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

Recommended values for code:

  • PRESENT
  • NOT_PRESENT
  • UNCLEAR

party_requirement_state

Purpose:

  • store the current aggregate state of one tracked requirement for one party

Columns:

  • id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  • party_id BIGINT NOT NULL REFERENCES public.party (id) ON DELETE CASCADE
  • requirement_code TEXT NOT NULL REFERENCES public.requirement_type (code)
  • state_code TEXT NOT NULL REFERENCES public.requirement_state_type (code)
  • last_evidence_id BIGINT NULL REFERENCES public.party_requirement_evidence (id) ON DELETE SET NULL
  • is_manual_override BOOLEAN NOT NULL DEFAULT FALSE
  • manual_note TEXT NOT NULL DEFAULT ''
  • fulfilled_at TIMESTAMPTZ NULL
  • resolved_delivery_mode_code TEXT NOT NULL DEFAULT ''
  • resolved_value_type_code TEXT NOT NULL DEFAULT ''
  • resolved_value_raw TEXT NOT NULL DEFAULT ''
  • resolved_value_normalized TEXT NOT NULL DEFAULT ''
  • updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
  • created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

Constraint:

  • UNIQUE (party_id, requirement_code)

Notes:

  • this table is the source of truth for current requirements other than ZT and feed, most notably SUPPLIER_IDENTIFIER
  • manual overrides belong here; the evidence rows stay immutable audit records
  • SUPPLIER_IDENTIFIER also stores the final value selected from fact rows
  • ZT and feed are never read from this table

party_requirement_evidence

Purpose:

  • store one evidence item that supports or rejects a requirement state

Columns:

  • id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  • party_id BIGINT NOT NULL REFERENCES public.party (id) ON DELETE CASCADE
  • requirement_code TEXT NOT NULL REFERENCES public.requirement_type (code)
  • source_type_code TEXT NOT NULL REFERENCES public.requirement_evidence_source_type (code)
  • verdict_code TEXT NOT NULL REFERENCES public.requirement_evidence_verdict_type (code)
  • activity_email_id BIGINT NULL REFERENCES public.activity_email (activity_id) ON DELETE SET NULL
  • external_thread_id TEXT NOT NULL DEFAULT ''
  • attachment_filename TEXT NOT NULL DEFAULT ''
  • attachment_relative_path TEXT NOT NULL DEFAULT ''
  • attachment_sha256 TEXT NOT NULL DEFAULT ''
  • confidence NUMERIC(5,4) NULL
  • reason TEXT NOT NULL DEFAULT ''
  • extract_json JSONB NOT NULL DEFAULT '{}'::jsonb
  • model_name TEXT NOT NULL DEFAULT ''
  • model_version TEXT NOT NULL DEFAULT ''
  • prompt_version TEXT NOT NULL DEFAULT ''
  • created_by TEXT NOT NULL DEFAULT 'system'
  • created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

Recommended constraint:

  • CHECK (confidence IS NULL OR (confidence >= 0 AND confidence <= 1))

Notes:

  • evidence may point to a concrete imported email through activity_email_id
  • the thread id still stays inline because V1 still has no dedicated thread table
  • evidence may denormalize selected attachment metadata even though activity_email_attachment exists, because evidence rows should remain immutable audit records
  • extract_json stores structured AI output, not the source of truth for the aggregate state
  • no evidence rows are created or read for ZT or feed

party_requirement_eval_queue

Purpose:

  • queue parties waiting for requirement-state re-evaluation

Columns:

  • id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  • party_id BIGINT NOT NULL REFERENCES public.party (id) ON DELETE CASCADE
  • requirement_code TEXT NOT NULL REFERENCES public.requirement_type (code)
  • reason TEXT NOT NULL DEFAULT ''
  • priority INTEGER NOT NULL DEFAULT 100
  • not_before TIMESTAMPTZ NOT NULL DEFAULT NOW()
  • attempt_count INTEGER NOT NULL DEFAULT 0
  • locked_at TIMESTAMPTZ NULL
  • created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

Constraint:

  • UNIQUE (party_id, requirement_code)

Notes:

  • used by the background worker to re-evaluate only affected companies after a new email, prompt change, or manual reset
  • avoids minute-based rescans over every company and every past email
  • ZT and feed are never enqueued; current automation processes only SUPPLIER_IDENTIFIER

Extracted Email Facts

This view stores concrete values extracted from one email, attachment, or AI pass without collapsing them directly into the final company state.

party_supplier_identifier_fact

Purpose:

  • store one extracted supplier identifier candidate for one party

Columns:

  • id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  • party_id BIGINT NOT NULL REFERENCES public.party (id) ON DELETE CASCADE
  • activity_email_id BIGINT NOT NULL REFERENCES public.activity_email (activity_id) ON DELETE CASCADE
  • evidence_id BIGINT NULL REFERENCES public.party_requirement_evidence (id) ON DELETE SET NULL
  • external_thread_id TEXT NOT NULL DEFAULT ''
  • attachment_filename TEXT NOT NULL DEFAULT ''
  • attachment_relative_path TEXT NOT NULL DEFAULT ''
  • attachment_sha256 TEXT NOT NULL DEFAULT ''
  • identifier_type_code TEXT NOT NULL
  • value_raw TEXT NOT NULL DEFAULT ''
  • value_normalized TEXT NOT NULL DEFAULT ''
  • confidence NUMERIC(5,4) NULL
  • reason TEXT NOT NULL DEFAULT ''
  • extract_json JSONB NOT NULL DEFAULT '{}'::jsonb
  • created_by TEXT NOT NULL DEFAULT 'system'
  • created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

Recommended values for identifier_type_code:

  • same code family as party_identifier.identifier_type_code
  • typical values for supplier onboarding:
  • COMPANY_REGISTRATION_NUMBER
  • TAX_IDENTIFIER
  • GLN
  • VENDOR_CODE
  • EXTERNAL_ID

Recommended constraints:

  • CHECK (confidence IS NULL OR (confidence >= 0 AND confidence <= 1))
  • CHECK ((value_raw <> '' OR value_normalized <> '') AND identifier_type_code <> '')

Notes:

  • one email may produce multiple identifier facts
  • once confirmed, a selected identifier may later be promoted into party_identifier, but the extracted fact rows remain immutable source data
  • the final company attribute still belongs to party_requirement_state

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
  • 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:

  • 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

Primary key:

  • (party_contact_mech_id, purpose_code)

Party Relationships

party_relationship

Purpose:

  • link one party to another party with direction 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
  • job_title TEXT NOT NULL DEFAULT ''
  • role_description 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

For CONTACT_FOR, job_title (Pracovní pozice) and role_description (Popis role) describe the person specifically at the linked company. Both are optional plain text; existing relationships start with empty values. The API accepts a single-line title up to 255 characters and a multiline description up to 5,000 characters. One person can have independent roles at multiple companies.

Migration 0093 enforces one active CONTACT_FOR per person/company pair with a partial unique index (thru_date IS NULL). For existing duplicate active pairs, the migration retains the oldest relationship (from_date, then id) and closes the others without deleting them. party_relationship_role_migration_audit stores their IDs and closing timestamps for rollback; it is not used by application workflows. Closing a relationship retains both fields. A later new relationship starts with empty fields. These dates describe the relationship, not a full history of role edits.

Creating a person from a company accepts these fields in the same transaction. PUT /api/companies/{companyId}/people/{personId}/role replaces both fields on the active relationship and requires collaboration access to that company. Company and person details expose the values on the corresponding related-record links. Company merging copies the fields, fills empty target values, and refuses conflicting nonempty values on matching relationships instead of discarding them.

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
  • stores both imported incoming messages and sent messages materialized after a successful Gmail send

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
  • source_app_user_id BIGINT NULL REFERENCES public.app_user (id) ON DELETE SET NULL
  • source_mailbox_email TEXT NOT NULL DEFAULT ''

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
  • the shared operational mailbox keeps provider gmail; a personal mailbox uses provider gmail:app-user:<id>, so the existing (provider, external_message_id) key cannot collide across mailboxes
  • source_app_user_id and source_mailbox_email record personal-mailbox provenance; existing shared-mailbox rows keep NULL and an empty string
  • a personal-mailbox row is written only when the complete thread matches an existing company by the canonical exact email, active CONTACT_FOR email, or manually configured domain rules; this check is enforced by the import repository, not left to a caller
  • unknown personal-mailbox addresses remain only in activity_participant.address_raw; the import must not create a new contact mechanism, person, company, or domain from them
  • the personal mailbox owner's address also stays raw-only and is never used as a company/party link, even if that address already exists in the contact model
  • newly imported personal-mailbox messages enqueue Jira reply-task automation; the latest imported message in that mailbox/thread determines Drafting Reply (incoming) or Waiting for Reply (outgoing), including retries
  • personal reply tasks are assigned to source_app_user_id using that app user's Jira identity; a missing/unresolvable identity leaves an error in the existing retry queue, never a fallback assignee or intentionally unassigned task
  • queue processing also backfills missing tasks for already imported personal threads; existing linked tasks are updated only when new messages are imported
  • personal imports still do not mutate derived supplier requirement facts
  • personal attachment DB metadata prefixes relative_path with accounts/app-user-<id>/, preventing the legacy global unique index from moving metadata between equal thread/file paths in different Gmail accounts
  • V1 does not introduce a separate activity_thread table
  • body_html stores decoded text/html MIME body parts only; it is not raw RFC822 storage and does not include separate inline CID image metadata
  • summaries, search, AI context, deterministic facts, and link extraction keep using body_text

email_import_skipped_thread

A durable list of threads skipped by the company import policy (migration 0086). It contains no message bodies or attachment bytes:

  • provider TEXT NOT NULL
  • external_thread_id TEXT NOT NULL
  • company_party_id BIGINT NOT NULL REFERENCES party_organization(party_id) ON DELETE CASCADE
  • source_app_user_id BIGINT NULL REFERENCES app_user(id) ON DELETE CASCADE
  • source_mailbox_email TEXT NOT NULL DEFAULT ''
  • retry_after TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
  • primary key (provider, external_thread_id, company_party_id)

The importer stores one row per matching company in the same transaction as the skip. When any linked company permits import, the worker replays the complete cached thread through the same importer, including messages missing from an already imported thread. Successful import deletes its queued links in that transaction; an error rolls back and leaves the thread for retry after five minutes. No Gmail history cursor is reset. Missing/incomplete cache content is retried, never replaced by a partial thread or synthesized message IDs.

Cache reads use the Google module's HTTP API and require no live Gmail grant. Personal replay still requires sync_inbox and uses the recorded app user and mailbox email; Google checks that the cache belongs to that mailbox. Existing (provider, external_message_id) uniqueness and account-specific attachment paths make repeated replay idempotent and preserve mailbox separation.

activity_email_attachment

Purpose:

  • store attachment metadata for one imported email message
  • bridge activity_email to the Gmail-owned, account-scoped file attachment cache

Columns:

  • id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  • activity_email_id BIGINT NOT NULL REFERENCES public.activity_email (activity_id) ON DELETE CASCADE
  • filename TEXT NOT NULL DEFAULT ''
  • mime_type TEXT NOT NULL DEFAULT 'application/octet-stream'
  • size_bytes BIGINT NOT NULL DEFAULT 0
  • relative_path TEXT NOT NULL DEFAULT ''
  • content_hash TEXT NOT NULL DEFAULT ''
  • created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

Recommended constraints:

  • CHECK (size_bytes >= 0)
  • CHECK (relative_path <> '')

Notes:

  • this table stores attachment metadata only, not binary content
  • in EMAIL_SYNC_BACKEND=gmail_service, binary payload stays in the Gmail-owned cache under reply-pilot-google/data/accounts/<GMAIL_CACHE_ACCOUNT_ID>/emails/attachments/ and BE proxies bytes from the Gmail module over HTTP
  • relative_path remains a logical compatibility path such as emails/attachments/<thread>/<file>; it is not a BE-owned filesystem path

Purpose:

  • store explicit URLs extracted from one imported email message

Columns:

  • id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  • activity_email_id BIGINT NOT NULL REFERENCES public.activity_email (activity_id) ON DELETE CASCADE
  • source_code TEXT NOT NULL DEFAULT 'BODY_TEXT'
  • url_raw TEXT NOT NULL DEFAULT ''
  • url_normalized TEXT NOT NULL DEFAULT ''
  • scheme TEXT NOT NULL DEFAULT ''
  • host TEXT NOT NULL DEFAULT ''
  • path TEXT NOT NULL DEFAULT ''
  • context_snippet TEXT NOT NULL DEFAULT ''
  • created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

Recommended values for source_code:

  • BODY_TEXT

Recommended constraints:

  • CHECK (url_raw <> '')
  • CHECK (url_normalized <> '')

Notes:

  • V1 stores only explicit http and https links extracted from body_text
  • url_raw preserves the exact string seen in the email, while url_normalized is the stable lookup key used by later extractors
  • these are generic link metadata; they do not create or change ZT/feed state

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

Email Thread Company Resolution

email_thread_address_company_resolution

Purpose:

  • provide the canonical dynamic read model for Inbox, task, and company-detail email queries that use standard company visibility
  • keep unresolved external addresses visible without creating a person, company, domain, or stored thread/company override

Columns:

  • provider TEXT
  • external_thread_id TEXT
  • email_address TEXT
  • party_id BIGINT NULL

Rules:

  • collect deduplicated non-internal FROM, TO, CC, and BCC addresses from all imported messages in the provider/thread
  • resolve only an exact active company email, an exact active person email through a current CONTACT_FOR, or an exact manually asserted company DOMAIN
  • company-name text, activity.primary_party_id, and participant party_id alone are not resolution signals
  • zero, one, and multiple company matches are all valid
  • an address with no match remains as one row with party_id IS NULL

This view includes only companies with show_by_default = TRUE. The related-company links returned by listRelatedCompaniesByEmailThread resolve the same exact email, active CONTACT_FOR, and domain rules directly, including hidden companies. Links are still restricted by company access permissions and, when an email activity is provided, by its mailbox provider. Search visibility and import policy are separate.

email_thread_assignment_review

Purpose:

  • record that one unresolved provider/thread was manually reviewed without an assignment

Columns:

  • provider TEXT NOT NULL
  • external_thread_id TEXT NOT NULL
  • reviewed_by_user_id BIGINT NOT NULL REFERENCES public.app_user (id)
  • reviewed_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP

The primary key is (provider, external_thread_id). Review state is deliberately per thread rather than per address, so a later unresolved thread from the same address appears in the queue. Adding an exact email contact makes the resolver recompute naturally; no stored thread/company pair is written.

Lead Import Workflow

This view stores server-side batch imports of supplier leads prepared outside Reply Pilot, for example from reply-pilot-wholesale-scout.

lead_import_batch

Purpose:

  • store one imported JSONL file prepared for operator review

Columns:

  • id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  • source_filename TEXT NOT NULL
  • source_sha256 TEXT NOT NULL
  • source_path TEXT NOT NULL
  • state_code TEXT NOT NULL DEFAULT 'REVIEW'
  • created_by_user_id BIGINT NULL REFERENCES public.app_user (id) ON DELETE SET NULL
  • total_item_count INTEGER NOT NULL DEFAULT 0
  • processed_item_count INTEGER NOT NULL DEFAULT 0
  • created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
  • completed_at TIMESTAMPTZ NULL

Recommended values for state_code:

  • REVIEW
  • COMPLETED
  • FAILED

Notes:

  • the source file lives on backend storage under a server-managed directory
  • DB stores the audit trail and review state, not the original file bytes

lead_import_item

Purpose:

  • store one supplier candidate from an imported JSONL batch

Columns:

  • id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  • batch_id BIGINT NOT NULL REFERENCES public.lead_import_batch (id) ON DELETE CASCADE
  • line_number INTEGER NOT NULL
  • state_code TEXT NOT NULL DEFAULT 'PENDING'
  • supplier_name TEXT NOT NULL DEFAULT ''
  • legal_name TEXT NOT NULL DEFAULT ''
  • website TEXT NOT NULL DEFAULT ''
  • source_record_json JSONB NOT NULL DEFAULT '{}'::jsonb
  • matched_party_id BIGINT NULL REFERENCES public.party (id) ON DELETE SET NULL
  • matched_reason TEXT NOT NULL DEFAULT ''
  • imported_party_id BIGINT NULL REFERENCES public.party (id) ON DELETE SET NULL
  • jira_task_id BIGINT NULL REFERENCES public.task (id) ON DELETE SET NULL
  • jira_key TEXT NOT NULL DEFAULT ''
  • ai_email_subject TEXT NOT NULL DEFAULT ''
  • ai_email_body TEXT NOT NULL DEFAULT ''
  • ai_model TEXT NOT NULL DEFAULT ''
  • ai_generated_at TIMESTAMPTZ NULL
  • operator_note TEXT NOT NULL DEFAULT ''
  • last_error TEXT NOT NULL DEFAULT ''
  • created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
  • updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

Recommended values for state_code:

  • PENDING
  • IMPORTED
  • DO_NOT_CONTACT
  • SKIPPED
  • ERROR

Notes:

  • matched_party_id stores the best prefill match against an existing company
  • imported_party_id stores the actual target company after operator decision
  • source_record_json keeps the immutable imported payload, while AI draft and operator decision live in explicit columns

Jira Task Layer

Jira remains the source of truth for workflow status and task text. Reply Pilot keeps a local cache in task / task_jira so the app can join Jira work with companies, email threads, users, and lead import decisions without querying Jira for every local linkage decision. Reply Pilot also caches Jira summary and plain-text description for read models and search; Jira remains canonical.

task

Purpose:

  • root table for local task records

Columns:

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

Recommended values for task_type_code:

  • jira

task_jira_work_type

Purpose:

  • lookup for the Jira work type represented by one task_jira cache row

Columns:

  • code TEXT PRIMARY KEY
  • name TEXT NOT NULL
  • created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

Recommended values for code:

  • supplier_onboarding
  • email_thread_reply
  • reply_pilot_task
  • organize_meeting

task_jira

Purpose:

  • cache one Jira work item and its optional company link for work types that own that relationship

Columns:

  • task_id BIGINT PRIMARY KEY REFERENCES public.task (id) ON DELETE CASCADE
  • jira_work_type_code TEXT NOT NULL REFERENCES public.task_jira_work_type (code)
  • jira_key TEXT NOT NULL DEFAULT ''
  • status TEXT NOT NULL DEFAULT ''
  • party_id BIGINT NULL REFERENCES public.party (id) ON DELETE SET NULL
  • user_id BIGINT NULL REFERENCES public.app_user (id) ON DELETE SET NULL
  • resolution TEXT NOT NULL DEFAULT ''
  • jira_updated_at TIMESTAMPTZ NULL
  • jira_synced_at TIMESTAMPTZ NULL
  • jira_sync_error TEXT NOT NULL DEFAULT ''
  • jira_assignee_account_id TEXT NOT NULL DEFAULT ''
  • jira_assignee_display_name TEXT NOT NULL DEFAULT ''
  • jira_assignee_email TEXT NOT NULL DEFAULT ''
  • jira_summary TEXT NOT NULL DEFAULT ''
  • jira_description_text TEXT NOT NULL DEFAULT ''

Notes:

  • status, resolution, and assignee fields are Jira cache values; Jira owns the canonical values
  • Jira summary and plain-text description are cached here for read models and search; Jira owns the canonical text
  • party_id is the company link for supplier onboarding, general Reply Pilot tasks, and meeting requests
  • party_id is always NULL for email_thread_reply; those tasks do not own an independent company relationship

task_jira_supplier_onboarding

Purpose:

  • subtype row for a supplier onboarding Jira work item

Columns:

  • task_id BIGINT PRIMARY KEY REFERENCES public.task_jira (task_id) ON DELETE CASCADE
  • feed_state_code TEXT NOT NULL REFERENCES public.task_feed_state (code) DEFAULT 'missing'
  • enlistment_table BOOLEAN NOT NULL DEFAULT FALSE
  • created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

Notes:

  • all pre-existing local Jira tasks are migrated into this subtype
  • Jira Cloud work type migration of existing tickets is an operator bulk-move step, not an automatic DB migration
  • this subtype is the only source of truth for company ZT/feed state
  • company list, detail, filters, and Solr use the newest linked onboarding task by task.created_at; task.id breaks timestamp ties
  • when no onboarding task exists, the effective values are enlistment_table = FALSE and feed_state_code = 'missing'
  • values change only through an explicit Supplier Onboarding task edit

task_jira_email_thread_reply

Purpose:

  • subtype row for an email-thread reply Jira work item

Columns:

  • task_id BIGINT PRIMARY KEY REFERENCES public.task_jira (task_id) ON DELETE CASCADE
  • external_thread_id TEXT NOT NULL
  • provider TEXT NOT NULL DEFAULT 'gmail' (expand migration 0087)
  • created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

Notes:

  • the task has no stored company link; its current companies are the distinct non-null party_id values in email_thread_address_company_resolution for its (provider, external_thread_id)
  • zero, one, or multiple current companies are valid, and changes in canonical thread resolution immediately change task visibility and display
  • task_jira.party_id remains NULL; company links already present in historic Jira descriptions are not parsed, rewritten, displayed, or used for authorization
  • lookup and deduplication use (provider, external_thread_id); existing tasks retain provider gmail, while personal tasks use gmail:app-user:<id>
  • company-default assignee changes and shared-mailbox assignment repair exclude personal tasks; automation restores the mailbox owner when a new message arrives
  • email thread link uses (provider, external_thread_id) until the model grows a dedicated email thread table
  • incoming email moves only linked email_thread_reply work items to Drafting Reply
  • sending a reply, or forwarding from the existing email thread, moves only linked email_thread_reply work items to Waiting for Reply

task_jira_organize_meeting

Purpose:

  • subtype row for an Organize a Meeting Jira work item
  • store the optional company contact selected when the request is created

Columns:

  • task_id BIGINT PRIMARY KEY REFERENCES public.task_jira (task_id) ON DELETE CASCADE
  • contact_party_id BIGINT NULL REFERENCES public.party (id) ON DELETE SET NULL

Notes:

  • company link remains in task_jira.party_id
  • creation time remains available from the root task.created_at
  • NULL means no contact person was selected
  • a non-null contact must have an active CONTACT_FOR relationship to the company when the request is created

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. store normalized plain text in body_text
  4. store decoded HTML MIME body content in body_html when present
  5. if attachment metadata is already available through the email cache repository, create one activity_email_attachment row per attachment; in gmail_service runtime the binary payload stays in the Gmail module cache
  6. extract explicit http(s) links from activity_email.body_text and create activity_email_link rows
  7. for each address in from, reply-to, to, cc, and bcc:
  8. normalize the email address
  9. look up an existing contact_mech through contact_mech_email.email
  10. if it does not exist, create contact_mech and contact_mech_email
  11. try to resolve an existing party through party_contact_mech
  12. create one activity_participant row with the proper role code
  13. if no matching party exists yet, keep the participant linked only to contact_mech_id
  14. 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
  • lead import matches an existing contact person inside one organization by the normalized display name; a shared email address must not merge people with different names
  • lead import keeps contact methods assigned to named contact_people on those people; only top-level contact methods not assigned to a named person are linked directly to the organization
  • repeated lead import must not create another active CONTACT_FOR relationship for the same person and organization
  • manual contact removal should set party_contact_mech.thru_date; it should not delete the shared contact_mech or historical activities
  • manual removal of a person from a company should set party_relationship.thru_date on the active CONTACT_FOR relationship; it should not delete the party_person
  • 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
  • party_vat_registration (party_id, vat_jurisdiction_code) unique index
  • party_vat_registration (value_normalized) non-unique index
  • activity_email (provider, external_message_id) unique index
  • activity_email (external_thread_id)
  • activity_email_attachment (relative_path) unique index
  • activity_email_attachment (activity_email_id)
  • activity_email_attachment (content_hash)
  • activity_email_link (activity_email_id, source_code, url_normalized) unique index
  • activity_email_link (activity_email_id)
  • activity_email_link (host)
  • party_requirement_state (requirement_code, state_code, updated_at DESC)
  • party_requirement_evidence (party_id, requirement_code, created_at DESC)
  • party_requirement_evidence (activity_email_id)
  • party_requirement_evidence (external_thread_id)
  • party_requirement_evidence (attachment_sha256)
  • party_requirement_eval_queue (not_before, priority, created_at)
  • party_requirement_eval_queue (locked_at)
  • party_supplier_identifier_fact (party_id, identifier_type_code, created_at DESC)
  • party_supplier_identifier_fact (activity_email_id)
  • party_supplier_identifier_fact (evidence_id)
  • party_supplier_identifier_fact (identifier_type_code, value_normalized)
  • party_outreach_policy (status_code)
  • lead_import_batch (state_code, created_at DESC)
  • lead_import_item (batch_id, state_code, line_number)
  • lead_import_item (matched_party_id)
  • lead_import_item (imported_party_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.

The retired manual email-thread/company feature also left the legacy tables email_thread_company_link, email_thread_company_exclusion, and activity_email_thread_company_event in databases that had briefly applied its migrations. Contract changeset 0054 removes all three. Current company resolution uses activity_email, activity_participant, contact mechanisms, and active CONTACT_FOR relationships instead.

Contract changeset 0085 retires the former ZT/feed requirement projection. It archives affected requirement types, state, evidence, queue rows, and all feed facts in rp5162_* audit tables before deleting the live rows and party_feed_fact. Its rollback recreates the dropped schema and restores the archived rows. It intentionally does not migrate values into onboarding tasks; a full Solr reindex is required after rollout.

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