1. Findings summary (state of the art, and what actually matters for LifeOS)

The best modern systems converge on the same core primitives: **(a) a contact/person record with flexible attributes, (b) multiple communication identities per person, (c) a timeline of interactions + notes, (d) lightweight relationship modeling, and (e) reminders/follow-ups**. Monica formalizes “many fields of many types” via **contact field types** (email, phone, Twitter, etc.) and lets a contact have as many as needed. ([Monicahq][1]) Attio takes this further with a deliberately flexible “objects/records/attributes” model and first-class **bidirectional relationship attributes** that link records across objects (including People↔People, People↔Company) and make linked attributes queryable everywhere. ([Attio Docs][2]) Folk’s model centers on **groups** + **custom fields**, with notes/reminders attached to the contact (and shared across groups). ([help.folk.app][3]) Dex (personal CRM) is strongest on “staying warm”: reminders, context, and sync from systems like LinkedIn/Gmail/Calendar. ([getdex.com][4])

Where the best tools start to separate is “relationship intelligence.” Attio explicitly exposes **communication intelligence** attributes like **first interaction, last interaction, and connection strength** (graded categories) and states it uses interaction data plus factors like **recency** and **response frequency** to compute connection strength; it also emphasizes **manual overrides should not be overwritten** by enrichment. ([Attio][5]) That’s exactly the right direction for LifeOS: treat computed intelligence as a *derived projection*, but preserve manual truth and provenance.

Where essentially all of them fall short for your use case: they’re built around a UI and a siloed CRM database—not a **self-hosted, provenance-first warehouse** that unifies email/meetings/finances/health/notes into one event spine and one semantic memory layer. Your “AI-only interface + long-horizon knowledge graph + rebuildable embeddings” requirement pushes you toward a design where the **canonical person graph lives in Postgres**, interactions link to your **core.event spine**, and semantic retrieval is driven by **doc.document + doc.chunk** with Weaviate as a serving index.

---

2. Schema recommendation (PostgreSQL people module, AI-native, spine-integrated)

### 2.1 Core tables (requested set) and what each does

* **people.person**
  Canonical “real-world entity” (usually a human, sometimes an org). Supports merge/redirect without breaking references.

* **people.identity**
  All identifiers observed across sources (emails, phones, LinkedIn URL, Fireflies speaker label, Google Contacts resource name, QBO vendor/customer IDs). Includes **confidence**, **status**, and “strong identifier” uniqueness.

* **people.relationship**
  Directed edges with time validity + provenance (met-at event, worked-together range, now investor, etc.). Attio’s bidirectional relationship attribute idea is a good mental model, but in Postgres you implement it as rows + indexes. ([Attio][6]) Monica’s explicit relationship type taxonomy (family/love/etc.) is also a useful inspiration. ([Monicahq][7])

* **people.person_attribute**
  Typed attributes with provenance + confidence + valid time ranges (kids_birth_year, job_title, company, “introduced_by”, etc.). You keep attributes flexible without turning the whole person record into JSON blob.

* **people.note**
  Notes are *not* stored twice. Note text lives in **doc.document** (so it gets chunked/embedded); this table links a person to that document and adds metadata (pinned, tags).

* **people.interaction_link**
  The join table between person entities and the **core.event** interaction timeline (emails, meetings, calls, transactions, etc.), with direction/weights to support “warmth” computations—without duplicating email/meeting content.

### 2.2 Key integration points with your existing LifeOS modules

* **core.person** (Mark + wife):
  Every people-module record that needs RLS hooks uses **owner_person_id → core.person(person_id)** and a `visibility_scope` where relevant.

* **core.event**:
  Every interaction is anchored here. `people.interaction_link` references `core.event(event_id)` and copies `occurred_at` (validated by trigger) for fast indexing.

* **doc.document + doc.chunk**:
  Notes, person summaries, “who is Sarah?” snapshots all become documents. You embed at the document/chunk layer and filter retrieval by person using **people.person_document_link**.

* **comms.email_message / comms.email_address**:
  You resolve email addresses to people via `people.email_address_link` and/or `people.identity` (identity_type=`email`). Attio’s “record page includes every email, meeting, note” is exactly what you recreate by linking events/documents rather than copying. ([Attio][8])

### 2.3 Design decisions baked into the schema

* **Two-layer model:** canonical person (`people.person`) + owner-specific profile (`people.person_profile`) for RLS, “close friend” flags, follow-up cadence, etc.
* **Merge-safe identifiers:** merged persons remain addressable; you resolve canonical via a function.
* **Strong-vs-weak identities:** emails/phones/LinkedIn URLs can be unique; names and transcript speaker strings are not.
* **Derived intelligence is cached, not canonical:** warmth/last_contact are stored in profile and/or a rollup table but are always reproducible from interaction links.

---

3. Entity resolution strategy (dedup/merge across Gmail, LinkedIn, Fireflies, manual)

### 3.1 What production systems do (and what works at personal scale)

Use a **hybrid** of:

1. **Deterministic linking** on strong identifiers (email, phone, LinkedIn URL, Google Contacts resource name, QBO vendor/customer id).
2. **Probabilistic matching** for weak/partial identities (name-only, speaker labels, partial emails, companies), producing scored candidates for review.

At the algorithm level, the modern “workhorse” is still probabilistic record linkage in the Fellegi–Sunter family, implemented in tools like **Splink** (Fellegi–Sunter based, blocking + match probability scoring) and **Dedupe** (ML + active learning for entity resolution). ([GitHub][9]) A good “state of the art” view is: probabilistic foundations + Bayesian/semi-supervised/supervised extensions depending on labeling availability. ([PMC][10])

### 3.2 What to store in the database (so merges are reversible and auditable)

* **people.identity**: every observed identifier + provenance + confidence + status
* **people.match_candidate**: candidate pairs with `score`, `method`, and `evidence_json` (features: name similarity, shared domain, co-attendance, etc.)
* **people.person_merge**: merge actions (who merged, when, why, evidence)
* **people.person.merged_into_person_id**: the actual redirect pointer

This lets your AI do:

* “Sarah J. in Gmail looks like Sarah Jones in LinkedIn (0.91). Merge?”
* If merged: history preserved, references remain resolvable, and you can unmerge if needed.

---

4. Interaction intelligence (warmth / last contact / frequency)

### 4.1 What to compute

Compute from `people.interaction_link` (not from emails directly):

* **first_interaction_at**
* **last_interaction_at**
* **last_meaningful_interaction_at** (weight or `is_meaningful`)
* **counts by window** (7/30/90/180/365)
* **channel mix** (email vs meeting vs call)
* **reciprocity proxies** (inbound/outbound balance; response cadence if you want later)

Attio’s “connection strength” approach is a good template: categorical strength based on interactions with factors like **recency** and **response frequency**. ([Attio][5])

### 4.2 Where to store it

* Store **raw interactions** in `people.interaction_link` (canonical for relationship intelligence).
* Store **cached rollups** in `people.person_profile` (and/or a rollup view) for fast conversational queries.

Pattern that works well in AI-only systems:

* **On write** (new interaction_link): update `person_profile.last_interaction_at` cheaply (trigger-safe).
* **On schedule** (n8n/cron): recompute warmth + windows in batch (more accurate, more flexible).

---

5. Decisions to make (painful to reverse later)

1) **Person vs Organization scope**
   Do you want the “people graph” to include companies/vendors/customers as first-class entities now?

* If yes: keep `entity_kind IN ('person','organization')` in `people.person` (included below).
* If no: split into `people.person` + `org.organization` later (more refactors).

2. **Merge semantics**
   Do you want merges to be **redirect-based** (keep old IDs, resolve to canonical) or **rewrite-based** (update every FK to the winner and delete the loser)?

* Redirect-based is safer and reversible; rewrite-based is simpler for queries but harder to undo.

3. **Strong identity types list**
   Which identity types must be globally unique when `is_strong=true`? (email/phone/linkedin_url/etc.)
   Changing this later can invalidate historical assumptions.

4. **Relationship directionality conventions**
   For symmetric relations (friend/spouse), will you store one directed row, or two reciprocal rows?
   Pick one convention now; changing later causes subtle query bugs.

5. **Warmth algorithm stability**
   Do you want warmth to be purely derived (view) or cached (table columns) as “official”?
   If cached becomes “official,” you must version the algorithm if it changes.

---

6. File output — `10_schema_people.sql`

```sql
-- 10_schema_people.sql
-- People/person graph module for LifeOS
-- Canonical entity graph in Postgres; interactions link to core.event; notes/documents link to doc.document for chunking/embeddings.

CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE SCHEMA IF NOT EXISTS people;

-- Ensure updated_at trigger exists (idempotent).
CREATE OR REPLACE FUNCTION core.set_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$;

-- ---------------------------
-- Canonical person entity
-- ---------------------------
CREATE TABLE IF NOT EXISTS people.person (
  person_id               uuid PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Supports vendors/customers/companies without forcing a separate org module on day one.
  entity_kind             text NOT NULL DEFAULT 'person'
                          CHECK (entity_kind IN ('person', 'organization')),

  -- If this person is actually one of the LifeOS owners (Mark or wife), link to core.person.
  core_person_id          uuid UNIQUE
                          REFERENCES core.person(person_id) ON DELETE RESTRICT,

  -- Merge/redirect support: if non-null, this row is a historical alias and resolves to merged_into_person_id.
  merged_into_person_id   uuid
                          REFERENCES people.person(person_id) ON DELETE SET NULL,

  status                  text NOT NULL DEFAULT 'active'
                          CHECK (status IN ('active', 'merged', 'archived')),

  display_name            text NOT NULL,
  given_name              text,
  family_name             text,
  middle_name             text,
  nickname                text,
  pronouns                text,

  birth_date              date,
  timezone                text NOT NULL DEFAULT 'America/New_York',

  primary_location_text   text,

  profile_photo_object_key text, -- SeaweedFS object key (optional)
  summary                 text,  -- short human/LLM summary (optional; derived summaries should be versioned in docs)

  created_at              timestamptz NOT NULL DEFAULT now(),
  updated_at              timestamptz NOT NULL DEFAULT now(),

  CHECK (merged_into_person_id IS NULL OR merged_into_person_id <> person_id),
  CHECK (
    (merged_into_person_id IS NULL AND status IN ('active','archived'))
    OR
    (merged_into_person_id IS NOT NULL AND status = 'merged')
  )
);

CREATE INDEX IF NOT EXISTS people_person_name_trgm_idx
  ON people.person USING GIN (display_name gin_trgm_ops);

CREATE INDEX IF NOT EXISTS people_person_family_given_idx
  ON people.person (family_name, given_name);

CREATE INDEX IF NOT EXISTS people_person_merged_into_idx
  ON people.person (merged_into_person_id);

DROP TRIGGER IF EXISTS trg_people_person_set_updated_at ON people.person;
CREATE TRIGGER trg_people_person_set_updated_at
BEFORE UPDATE ON people.person
FOR EACH ROW
EXECUTE FUNCTION core.set_updated_at();

-- Keep status consistent with merged_into_person_id.
CREATE OR REPLACE FUNCTION people.sync_person_merge_status()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF NEW.merged_into_person_id IS NOT NULL THEN
    NEW.status := 'merged';
  ELSIF NEW.status = 'merged' THEN
    NEW.status := 'active';
  END IF;
  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_people_person_sync_merge_status ON people.person;
CREATE TRIGGER trg_people_person_sync_merge_status
BEFORE INSERT OR UPDATE ON people.person
FOR EACH ROW
EXECUTE FUNCTION people.sync_person_merge_status();

-- Resolve canonical person id (follows merge chain).
CREATE OR REPLACE FUNCTION people.resolve_person_id(p_person_id uuid)
RETURNS uuid
LANGUAGE plpgsql
STABLE
AS $$
DECLARE
  v_current uuid := p_person_id;
  v_next uuid;
  v_steps int := 0;
BEGIN
  LOOP
    SELECT merged_into_person_id INTO v_next
    FROM people.person
    WHERE person_id = v_current;

    IF NOT FOUND THEN
      RETURN p_person_id;
    END IF;

    IF v_next IS NULL THEN
      RETURN v_current;
    END IF;

    v_current := v_next;
    v_steps := v_steps + 1;

    IF v_steps > 50 THEN
      RETURN v_current; -- cycle protection
    END IF;
  END LOOP;
END;
$$;

-- ---------------------------
-- Owner-specific person profile (RLS hook + derived intelligence cache)
-- ---------------------------
CREATE TABLE IF NOT EXISTS people.person_profile (
  owner_person_id          uuid NOT NULL REFERENCES core.person(person_id) ON DELETE RESTRICT,
  person_id                uuid NOT NULL REFERENCES people.person(person_id) ON DELETE CASCADE,

  visibility_scope         text NOT NULL DEFAULT 'private'
                           CHECK (visibility_scope IN ('private','shared')),

  -- Manual organization / prioritization fields (AI can set these via tools).
  closeness_tier           smallint CHECK (closeness_tier BETWEEN 1 AND 5), -- 1 = closest
  followup_interval_days   integer CHECK (followup_interval_days IS NULL OR followup_interval_days >= 0),
  do_not_contact           boolean NOT NULL DEFAULT false,
  pinned                   boolean NOT NULL DEFAULT false,

  -- Cached interaction intelligence (derived; safe to recompute).
  first_interaction_at     timestamptz,
  last_interaction_at      timestamptz,
  last_meaningful_interaction_at timestamptz,

  interactions_30d         integer NOT NULL DEFAULT 0,
  interactions_180d        integer NOT NULL DEFAULT 0,

  warmth_score             numeric(8,3), -- algorithm-defined; version in code if you change it
  connection_strength      text CHECK (
                             connection_strength IS NULL OR
                             connection_strength IN ('no_connection','very_weak','weak','good','strong','very_strong')
                           ),

  created_at               timestamptz NOT NULL DEFAULT now(),
  updated_at               timestamptz NOT NULL DEFAULT now(),

  PRIMARY KEY (owner_person_id, person_id)
);

CREATE INDEX IF NOT EXISTS people_person_profile_owner_last_idx
  ON people.person_profile (owner_person_id, last_interaction_at DESC);

CREATE INDEX IF NOT EXISTS people_person_profile_owner_warmth_idx
  ON people.person_profile (owner_person_id, warmth_score DESC);

DROP TRIGGER IF EXISTS trg_people_person_profile_set_updated_at ON people.person_profile;
CREATE TRIGGER trg_people_person_profile_set_updated_at
BEFORE UPDATE ON people.person_profile
FOR EACH ROW
EXECUTE FUNCTION core.set_updated_at();

-- ---------------------------
-- Identities (emails, phones, LinkedIn, Google Contacts, Fireflies speakers, etc.)
-- ---------------------------
CREATE TABLE IF NOT EXISTS people.identity (
  identity_id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  person_id            uuid NOT NULL REFERENCES people.person(person_id) ON DELETE CASCADE,

  -- Use stable keys like: email, phone, linkedin_url, google_contacts_resource, fireflies_speaker, qbo_vendor_id, etc.
  identity_type        text NOT NULL CHECK (identity_type ~ '^[a-z0-9_]+$'),

  identity_status      text NOT NULL DEFAULT 'active'
                       CHECK (identity_status IN ('active','candidate','deprecated')),

  -- Raw as observed + normalized for matching (normalization rules live in ingestion code).
  value_raw            text NOT NULL,
  value_norm           text NOT NULL,

  -- Strong identifiers (email/phone/linkedin) should be unique globally when active.
  is_strong            boolean NOT NULL DEFAULT false,
  is_primary           boolean NOT NULL DEFAULT false,
  is_verified          boolean NOT NULL DEFAULT false,

  confidence           numeric(6,5) NOT NULL DEFAULT 1.0
                       CHECK (confidence >= 0 AND confidence <= 1),

  source_system        text,  -- gmail, linkedin_csv, fireflies, google_contacts, manual, etc.
  source_ref           text,  -- provider-specific id

  first_seen_at        timestamptz NOT NULL DEFAULT now(),
  last_seen_at         timestamptz,
  seen_count           integer NOT NULL DEFAULT 0 CHECK (seen_count >= 0),

  created_at           timestamptz NOT NULL DEFAULT now(),
  updated_at           timestamptz NOT NULL DEFAULT now(),

  UNIQUE (person_id, identity_type, value_norm)
);

-- Global uniqueness for strong identities (only when active).
CREATE UNIQUE INDEX IF NOT EXISTS people_identity_strong_unique_uq
  ON people.identity (identity_type, value_norm)
  WHERE is_strong AND identity_status = 'active';

CREATE INDEX IF NOT EXISTS people_identity_lookup_idx
  ON people.identity (identity_type, value_norm);

CREATE INDEX IF NOT EXISTS people_identity_person_idx
  ON people.identity (person_id);

DROP TRIGGER IF EXISTS trg_people_identity_set_updated_at ON people.identity;
CREATE TRIGGER trg_people_identity_set_updated_at
BEFORE UPDATE ON people.identity
FOR EACH ROW
EXECUTE FUNCTION core.set_updated_at();

-- ---------------------------
-- Email address crosswalk (fast path from comms.email_address -> people.person)
-- ---------------------------
CREATE TABLE IF NOT EXISTS people.email_address_link (
  email_address_id     uuid PRIMARY KEY REFERENCES comms.email_address(email_address_id) ON DELETE CASCADE,
  person_id            uuid NOT NULL REFERENCES people.person(person_id) ON DELETE RESTRICT,
  identity_id          uuid REFERENCES people.identity(identity_id) ON DELETE SET NULL,

  confidence           numeric(6,5) NOT NULL DEFAULT 1.0 CHECK (confidence >= 0 AND confidence <= 1),

  created_at           timestamptz NOT NULL DEFAULT now(),
  updated_at           timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS people_email_address_link_person_idx
  ON people.email_address_link (person_id);

DROP TRIGGER IF EXISTS trg_people_email_address_link_set_updated_at ON people.email_address_link;
CREATE TRIGGER trg_people_email_address_link_set_updated_at
BEFORE UPDATE ON people.email_address_link
FOR EACH ROW
EXECUTE FUNCTION core.set_updated_at();

-- Ensure identity_id (if set) matches person_id and is an email identity.
CREATE OR REPLACE FUNCTION people.validate_email_address_link()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  v_pid uuid;
  v_type text;
BEGIN
  IF NEW.identity_id IS NULL THEN
    RETURN NEW;
  END IF;

  SELECT person_id, identity_type INTO v_pid, v_type
  FROM people.identity
  WHERE identity_id = NEW.identity_id;

  IF v_pid IS NULL THEN
    RAISE EXCEPTION 'people.identity % not found for email_address_link', NEW.identity_id;
  END IF;

  IF v_pid <> NEW.person_id THEN
    RAISE EXCEPTION 'email_address_link person_id % does not match identity.person_id %', NEW.person_id, v_pid;
  END IF;

  IF v_type <> 'email' THEN
    RAISE EXCEPTION 'email_address_link identity_type must be email, got %', v_type;
  END IF;

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_people_email_address_link_validate ON people.email_address_link;
CREATE TRIGGER trg_people_email_address_link_validate
BEFORE INSERT OR UPDATE ON people.email_address_link
FOR EACH ROW
EXECUTE FUNCTION people.validate_email_address_link();

-- ---------------------------
-- Relationships (directed edges with temporal validity + provenance)
-- ---------------------------
CREATE TABLE IF NOT EXISTS people.relationship (
  relationship_id      uuid PRIMARY KEY DEFAULT gen_random_uuid(),

  owner_person_id      uuid NOT NULL REFERENCES core.person(person_id) ON DELETE RESTRICT,

  from_person_id       uuid NOT NULL REFERENCES people.person(person_id) ON DELETE RESTRICT,
  to_person_id         uuid NOT NULL REFERENCES people.person(person_id) ON DELETE RESTRICT,

  -- Examples: friend, spouse, parent_of, works_at, investor_in, introduced, mentor_of, vendor_of, customer_of
  relationship_type    text NOT NULL CHECK (relationship_type ~ '^[a-z0-9_]+$'),

  is_active            boolean NOT NULL DEFAULT true,
  valid_from           timestamptz,
  valid_to             timestamptz,

  -- Optional: anchor relationship to a specific event (met at, introduction happened, etc.)
  context_event_id     uuid REFERENCES core.event(event_id) ON DELETE SET NULL,

  strength             numeric(6,5) CHECK (strength IS NULL OR (strength >= 0 AND strength <= 1)),
  confidence           numeric(6,5) NOT NULL DEFAULT 1.0 CHECK (confidence >= 0 AND confidence <= 1),

  source_system        text,
  source_ref           text,

  notes                text,

  created_at           timestamptz NOT NULL DEFAULT now(),
  updated_at           timestamptz NOT NULL DEFAULT now(),

  CHECK (from_person_id <> to_person_id),
  CHECK (valid_to IS NULL OR valid_from IS NULL OR valid_to >= valid_from)
);

CREATE INDEX IF NOT EXISTS people_relationship_owner_from_idx
  ON people.relationship (owner_person_id, from_person_id);

CREATE INDEX IF NOT EXISTS people_relationship_owner_to_idx
  ON people.relationship (owner_person_id, to_person_id);

CREATE INDEX IF NOT EXISTS people_relationship_type_idx
  ON people.relationship (relationship_type);

CREATE INDEX IF NOT EXISTS people_relationship_active_idx
  ON people.relationship (owner_person_id, is_active);

DROP TRIGGER IF EXISTS trg_people_relationship_set_updated_at ON people.relationship;
CREATE TRIGGER trg_people_relationship_set_updated_at
BEFORE UPDATE ON people.relationship
FOR EACH ROW
EXECUTE FUNCTION core.set_updated_at();

-- ---------------------------
-- Attributes (typed, temporal, provenance-aware)
-- ---------------------------
CREATE TABLE IF NOT EXISTS people.person_attribute (
  attribute_id        uuid PRIMARY KEY DEFAULT gen_random_uuid(),

  owner_person_id     uuid NOT NULL REFERENCES core.person(person_id) ON DELETE RESTRICT,
  person_id           uuid NOT NULL REFERENCES people.person(person_id) ON DELETE CASCADE,

  visibility_scope    text NOT NULL DEFAULT 'private'
                      CHECK (visibility_scope IN ('private','shared')),

  attribute_key       text NOT NULL CHECK (attribute_key ~ '^[a-z0-9_]+$'),

  value_text          text,
  value_num           numeric,
  value_bool          boolean,
  value_date          date,
  value_json          jsonb,
  unit                text,

  valid_from          timestamptz,
  valid_to            timestamptz,

  confidence          numeric(6,5) NOT NULL DEFAULT 1.0 CHECK (confidence >= 0 AND confidence <= 1),

  source_system       text,
  source_ref          text,

  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now(),

  CHECK (valid_to IS NULL OR valid_from IS NULL OR valid_to >= valid_from)
);

CREATE INDEX IF NOT EXISTS people_person_attribute_lookup_idx
  ON people.person_attribute (owner_person_id, person_id, attribute_key);

CREATE INDEX IF NOT EXISTS people_person_attribute_valid_idx
  ON people.person_attribute (owner_person_id, person_id, valid_from DESC);

CREATE INDEX IF NOT EXISTS people_person_attribute_json_gin_idx
  ON people.person_attribute USING GIN (value_json);

DROP TRIGGER IF EXISTS trg_people_person_attribute_set_updated_at ON people.person_attribute;
CREATE TRIGGER trg_people_person_attribute_set_updated_at
BEFORE UPDATE ON people.person_attribute
FOR EACH ROW
EXECUTE FUNCTION core.set_updated_at();

-- ---------------------------
-- Notes (note content lives in doc.document for chunking/embedding)
-- ---------------------------
CREATE TABLE IF NOT EXISTS people.note (
  note_id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),

  owner_person_id    uuid NOT NULL REFERENCES core.person(person_id) ON DELETE RESTRICT,
  person_id          uuid NOT NULL REFERENCES people.person(person_id) ON DELETE CASCADE,

  event_id           uuid NOT NULL UNIQUE REFERENCES core.event(event_id) ON DELETE CASCADE,
  document_id        uuid NOT NULL REFERENCES doc.document(document_id) ON DELETE RESTRICT,

  note_type          text NOT NULL DEFAULT 'manual'
                     CHECK (note_type IN ('manual','ai_summary','meeting_brief','profile_snapshot','other')),

  pinned             boolean NOT NULL DEFAULT false,
  tags               text[] NOT NULL DEFAULT '{}'::text[],

  created_at         timestamptz NOT NULL DEFAULT now(),
  updated_at         timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS people_note_owner_person_time_idx
  ON people.note (owner_person_id, person_id, created_at DESC);

CREATE INDEX IF NOT EXISTS people_note_tags_gin_idx
  ON people.note USING GIN (tags);

DROP TRIGGER IF EXISTS trg_people_note_set_updated_at ON people.note;
CREATE TRIGGER trg_people_note_set_updated_at
BEFORE UPDATE ON people.note
FOR EACH ROW
EXECUTE FUNCTION core.set_updated_at();

-- Validate that core.event.person_id == owner_person_id and doc.document.person_id == owner_person_id.
CREATE OR REPLACE FUNCTION people.validate_note_links()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  v_event_owner uuid;
  v_doc_owner uuid;
BEGIN
  SELECT person_id INTO v_event_owner
  FROM core.event
  WHERE event_id = NEW.event_id;

  IF v_event_owner IS NULL THEN
    RAISE EXCEPTION 'core.event % not found for people.note', NEW.event_id;
  END IF;

  IF v_event_owner <> NEW.owner_person_id THEN
    RAISE EXCEPTION 'people.note owner_person_id % must match core.event.person_id %', NEW.owner_person_id, v_event_owner;
  END IF;

  SELECT person_id INTO v_doc_owner
  FROM doc.document
  WHERE document_id = NEW.document_id;

  IF v_doc_owner IS NULL THEN
    RAISE EXCEPTION 'doc.document % not found for people.note', NEW.document_id;
  END IF;

  IF v_doc_owner <> NEW.owner_person_id THEN
    RAISE EXCEPTION 'people.note owner_person_id % must match doc.document.person_id %', NEW.owner_person_id, v_doc_owner;
  END IF;

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_people_note_validate_links ON people.note;
CREATE TRIGGER trg_people_note_validate_links
BEFORE INSERT OR UPDATE ON people.note
FOR EACH ROW
EXECUTE FUNCTION people.validate_note_links();

-- ---------------------------
-- Interaction link (ties people to core.event timeline; no duplication of email/meeting content)
-- ---------------------------
CREATE TABLE IF NOT EXISTS people.interaction_link (
  interaction_link_id  uuid PRIMARY KEY DEFAULT gen_random_uuid(),

  owner_person_id      uuid NOT NULL REFERENCES core.person(person_id) ON DELETE RESTRICT,
  person_id            uuid NOT NULL REFERENCES people.person(person_id) ON DELETE RESTRICT,

  event_id             uuid NOT NULL REFERENCES core.event(event_id) ON DELETE CASCADE,

  interaction_type     text NOT NULL CHECK (interaction_type IN (
                         'email','meeting','call','message','in_person','transaction','note','other'
                       )),

  direction            text NOT NULL DEFAULT 'unknown'
                       CHECK (direction IN ('inbound','outbound','bidirectional','unknown')),

  -- Used for warmth scoring; keep it simple and tune in code.
  weight               smallint NOT NULL DEFAULT 1 CHECK (weight >= 0 AND weight <= 100),
  is_meaningful        boolean NOT NULL DEFAULT false,

  -- Denormalized for fast window queries; validated to match core.event.occurred_at.
  occurred_at          timestamptz NOT NULL,

  -- Optional pointer to the originating object (comms.email_message, finance.transaction, etc.).
  source_object_type   text,
  source_object_id     uuid,

  source_system        text,
  source_ref           text,

  created_at           timestamptz NOT NULL DEFAULT now(),

  UNIQUE (owner_person_id, person_id, event_id)
);

CREATE INDEX IF NOT EXISTS people_interaction_owner_person_time_idx
  ON people.interaction_link (owner_person_id, person_id, occurred_at DESC);

CREATE INDEX IF NOT EXISTS people_interaction_event_idx
  ON people.interaction_link (event_id);

CREATE INDEX IF NOT EXISTS people_interaction_owner_time_idx
  ON people.interaction_link (owner_person_id, occurred_at DESC);

-- Validate owner matches event owner and set occurred_at from core.event.occurred_at.
CREATE OR REPLACE FUNCTION people.validate_interaction_link()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  v_event_owner uuid;
  v_event_time timestamptz;
BEGIN
  SELECT person_id, occurred_at INTO v_event_owner, v_event_time
  FROM core.event
  WHERE event_id = NEW.event_id;

  IF v_event_owner IS NULL THEN
    RAISE EXCEPTION 'core.event % not found for people.interaction_link', NEW.event_id;
  END IF;

  IF v_event_owner <> NEW.owner_person_id THEN
    RAISE EXCEPTION 'interaction_link owner_person_id % must match core.event.person_id %', NEW.owner_person_id, v_event_owner;
  END IF;

  NEW.occurred_at := v_event_time;
  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_people_interaction_validate ON people.interaction_link;
CREATE TRIGGER trg_people_interaction_validate
BEFORE INSERT OR UPDATE ON people.interaction_link
FOR EACH ROW
EXECUTE FUNCTION people.validate_interaction_link();

-- ---------------------------
-- Document link (enables person-filtered semantic retrieval)
-- ---------------------------
CREATE TABLE IF NOT EXISTS people.person_document_link (
  owner_person_id     uuid NOT NULL REFERENCES core.person(person_id) ON DELETE RESTRICT,
  person_id           uuid NOT NULL REFERENCES people.person(person_id) ON DELETE CASCADE,
  document_id         uuid NOT NULL REFERENCES doc.document(document_id) ON DELETE CASCADE,

  link_type           text NOT NULL DEFAULT 'related'
                      CHECK (link_type IN ('related','note','email','meeting_transcript','profile_snapshot','other')),

  event_id            uuid REFERENCES core.event(event_id) ON DELETE SET NULL,

  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now(),

  PRIMARY KEY (owner_person_id, person_id, document_id)
);

CREATE INDEX IF NOT EXISTS people_person_document_link_person_idx
  ON people.person_document_link (owner_person_id, person_id, created_at DESC);

DROP TRIGGER IF EXISTS trg_people_person_document_link_set_updated_at ON people.person_document_link;
CREATE TRIGGER trg_people_person_document_link_set_updated_at
BEFORE UPDATE ON people.person_document_link
FOR EACH ROW
EXECUTE FUNCTION core.set_updated_at();

-- Validate doc ownership aligns with owner_person_id (doc.document.person_id).
CREATE OR REPLACE FUNCTION people.validate_person_document_link()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  v_doc_owner uuid;
BEGIN
  SELECT person_id INTO v_doc_owner
  FROM doc.document
  WHERE document_id = NEW.document_id;

  IF v_doc_owner IS NULL THEN
    RAISE EXCEPTION 'doc.document % not found for people.person_document_link', NEW.document_id;
  END IF;

  IF v_doc_owner <> NEW.owner_person_id THEN
    RAISE EXCEPTION 'person_document_link owner_person_id % must match doc.document.person_id %', NEW.owner_person_id, v_doc_owner;
  END IF;

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_people_person_document_link_validate ON people.person_document_link;
CREATE TRIGGER trg_people_person_document_link_validate
BEFORE INSERT OR UPDATE ON people.person_document_link
FOR EACH ROW
EXECUTE FUNCTION people.validate_person_document_link();

-- ---------------------------
-- Match candidates + merges (entity resolution audit trail)
-- ---------------------------
CREATE TABLE IF NOT EXISTS people.match_candidate (
  match_candidate_id   uuid PRIMARY KEY DEFAULT gen_random_uuid(),

  created_by_person_id uuid NOT NULL REFERENCES core.person(person_id) ON DELETE RESTRICT,

  person_id_a          uuid NOT NULL REFERENCES people.person(person_id) ON DELETE CASCADE,
  person_id_b          uuid NOT NULL REFERENCES people.person(person_id) ON DELETE CASCADE,

  method               text NOT NULL CHECK (method IN ('deterministic','splink','dedupe','manual','other')),
  score                numeric(6,5) NOT NULL CHECK (score >= 0 AND score <= 1),

  evidence_json        jsonb NOT NULL DEFAULT '{}'::jsonb,

  status               text NOT NULL DEFAULT 'proposed'
                       CHECK (status IN ('proposed','accepted_merge','rejected','needs_review')),

  decided_at           timestamptz,
  decided_by_person_id uuid REFERENCES core.person(person_id) ON DELETE SET NULL,

  created_at           timestamptz NOT NULL DEFAULT now(),
  updated_at           timestamptz NOT NULL DEFAULT now(),

  CHECK (person_id_a <> person_id_b)
);

CREATE INDEX IF NOT EXISTS people_match_candidate_status_idx
  ON people.match_candidate (status, created_at DESC);

CREATE INDEX IF NOT EXISTS people_match_candidate_pair_idx
  ON people.match_candidate (person_id_a, person_id_b);

DROP TRIGGER IF EXISTS trg_people_match_candidate_set_updated_at ON people.match_candidate;
CREATE TRIGGER trg_people_match_candidate_set_updated_at
BEFORE UPDATE ON people.match_candidate
FOR EACH ROW
EXECUTE FUNCTION core.set_updated_at();

CREATE TABLE IF NOT EXISTS people.person_merge (
  person_merge_id       uuid PRIMARY KEY DEFAULT gen_random_uuid(),

  merged_by_person_id   uuid NOT NULL REFERENCES core.person(person_id) ON DELETE RESTRICT,

  source_person_id      uuid NOT NULL REFERENCES people.person(person_id) ON DELETE RESTRICT,
  target_person_id      uuid NOT NULL REFERENCES people.person(person_id) ON DELETE RESTRICT,

  match_candidate_id    uuid REFERENCES people.match_candidate(match_candidate_id) ON DELETE SET NULL,

  reason                text,
  evidence_json         jsonb NOT NULL DEFAULT '{}'::jsonb,

  merged_at             timestamptz NOT NULL DEFAULT now(),

  created_at            timestamptz NOT NULL DEFAULT now(),

  CHECK (source_person_id <> target_person_id)
);

CREATE INDEX IF NOT EXISTS people_person_merge_target_idx
  ON people.person_merge (target_person_id, merged_at DESC);

-- Merge helper: records audit row + sets redirect pointer.
CREATE OR REPLACE FUNCTION people.merge_people(
  p_source_person_id uuid,
  p_target_person_id uuid,
  p_merged_by_person_id uuid,
  p_reason text DEFAULT NULL,
  p_evidence_json jsonb DEFAULT '{}'::jsonb,
  p_match_candidate_id uuid DEFAULT NULL
)
RETURNS uuid
LANGUAGE plpgsql
AS $$
DECLARE
  v_merge_id uuid;
BEGIN
  IF p_source_person_id = p_target_person_id THEN
    RAISE EXCEPTION 'Cannot merge a person into itself';
  END IF;

  -- Write audit record
  INSERT INTO people.person_merge (
    merged_by_person_id,
    source_person_id,
    target_person_id,
    match_candidate_id,
    reason,
    evidence_json
  )
  VALUES (
    p_merged_by_person_id,
    p_source_person_id,
    p_target_person_id,
    p_match_candidate_id,
    p_reason,
    COALESCE(p_evidence_json, '{}'::jsonb)
  )
  RETURNING person_merge_id INTO v_merge_id;

  -- Redirect source -> target
  UPDATE people.person
  SET merged_into_person_id = p_target_person_id
  WHERE person_id = p_source_person_id;

  RETURN v_merge_id;
END;
$$;

-- ---------------------------
-- Convenience view: basic interaction intelligence (derived on-demand)
-- ---------------------------
CREATE OR REPLACE VIEW people.v_person_intelligence AS
SELECT
  il.owner_person_id,
  il.person_id,
  MIN(il.occurred_at) AS first_interaction_at,
  MAX(il.occurred_at) AS last_interaction_at,
  MAX(il.occurred_at) FILTER (WHERE il.is_meaningful) AS last_meaningful_interaction_at,
  COUNT(*) FILTER (WHERE il.occurred_at >= now() - interval '30 days') AS interactions_30d,
  COUNT(*) FILTER (WHERE il.occurred_at >= now() - interval '180 days') AS interactions_180d,
  SUM(il.weight) FILTER (WHERE il.occurred_at >= now() - interval '180 days') AS weighted_interactions_180d
FROM people.interaction_link il
GROUP BY il.owner_person_id, il.person_id;
```

If you want, I can also add **one optional convenience function**: `people.upsert_profile_from_intelligence(owner_person_id, person_id)` to copy the view’s aggregates into `people.person_profile` (useful for scheduled recompute jobs).

[1]: https://www.monicahq.com/api/contactfieldtypes "https://www.monicahq.com/api/contactfieldtypes"
[2]: https://docs.attio.com/docs/objects-and-lists "https://docs.attio.com/docs/objects-and-lists"
[3]: https://help.folk.app/en/articles/9790806-folk-data-model "https://help.folk.app/en/articles/9790806-folk-data-model"
[4]: https://getdex.com/ "https://getdex.com/"
[5]: https://attio.com/help/reference/managing-your-data/enriched-data "https://attio.com/help/reference/managing-your-data/enriched-data"
[6]: https://attio.com/help/reference/managing-your-data/attributes/relationship-attributes "https://attio.com/help/reference/managing-your-data/attributes/relationship-attributes"
[7]: https://www.monicahq.com/api/relationshiptypes "https://www.monicahq.com/api/relationshiptypes"
[8]: https://attio.com/help/reference/attio-101/attios-data-model/understanding-records "https://attio.com/help/reference/attio-101/attios-data-model/understanding-records"
[9]: https://github.com/moj-analytical-services/splink "https://github.com/moj-analytical-services/splink"
[10]: https://pmc.ncbi.nlm.nih.gov/articles/PMC11636688/ "https://pmc.ncbi.nlm.nih.gov/articles/PMC11636688/"