Domain Model — Contact Intelligence · Contact Intelligence

Last Updated: 2026-06-29 Status: Active

1. Tables Overview

CI uses 12 PostgreSQL tables (all prefixed ci_):

Table	Purpose
`ci_contacts`	Root entity — one row per end-user identity per channel per tenant
`ci_contact_state`	Hot state (mood, streak, relationship stage, churn risk) — 1:1 with contact
`ci_contact_memory`	Per-contact memory store with pgvector embeddings
`ci_contact_entities`	Legacy rules-based entity nodes (kept for backward compat)
`ci_contact_channels`	Multi-channel identity links (NEW — Phase 3)
`ci_entities`	LLM-extracted entity graph nodes (NEW — Phase 3)
`ci_entity_relationships`	Entity graph edges (NEW — Phase 3)
`ci_emotional_snapshots`	Emotional arc storage: session + weekly rollup (NEW — Phase 3)
`ci_relationship_snapshots`	Weekly relationship trajectory snapshots (NEW — Phase 3)
`ci_outreach_triggers`	Scheduled proactive messages
`ci_outreach_config`	Per-tenant outreach settings
`ci_interaction_logs`	Lightweight per-message signal log

2. Core Tables

2.1 `ci_contacts`

The root entity. Represents a single end-user interacting with a tenant's AI bot.

Column	Type	Default	Description
`id`	text PK	—	Primary key
`tenant_id`	text	—	Owning business
`external_id`	text	—	Channel-specific identifier (phone number, telegram chat ID, widget session ID)
`channel`	text	—	`whatsapp` \| `telegram` \| `widget` \| `instagram`
`display_name`	text	null	Name extracted from first interaction or channel profile
`outreach_disabled`	boolean	false	When `true`, NO proactive messages ever. Set when contact says "stop". Legal requirement — no override.
`deleted_at`	timestamp	null	Soft delete for GDPR — row stays but PII is scrubbed
`core_summary`	text	null	L1 behavioral guidance. 1–3 sentence LLM summary. Null until first weekly consolidation. Regenerated every Sunday by `consolidateContact()`.
`cold_start_used_until`	integer	5	Session count threshold. Below this → generic cold-start string. Above this → `core_summary` (or null).
`crisis_level`	text	`'none'`	`none` \| `distress` \| `crisis`. Gates CE safety escalation. Set by session-close LLM extraction.
`channel_count`	integer	1	Number of linked channels. Used by multi-channel merge logic.
`last_consolidated_at`	timestamp	null	When the weekly consolidation job last ran for this contact.
`created_at`	timestamp	now	First interaction
`updated_at`	timestamp	now	Last profile modification

Unique index: (tenant_id, external_id, channel) — one identity per channel per tenant.

2.2 `ci_contact_state`

Hot, frequently-updated state. One row per contact, upserted on every interaction.

Column	Type	Default	Description
`contact_id`	text PK → ci_contacts	—	FK + primary key
`tenant_id`	text	—	Owning business
`mood`	text	`'neutral'`	`happy` \| `neutral` \| `frustrated` \| `sad` \| `anxious` \| `excited` \| `confused`
`mood_confidence`	real	0.5	LLM confidence score for mood classification
`energy`	text	`'medium'`	`high` \| `medium` \| `low`
`last_mood_at`	timestamp	null	When mood was last classified
`conversation_style`	text	`'balanced'`	`formal` \| `casual` \| `balanced` \| `terse` \| `verbose`
`relationship_stage`	text	`'new'`	`new` \| `building` \| `established` \| `deep` \| `fading` \| `dormant`
`active_streak`	integer	0	Consecutive days with at least one message
`longest_streak`	integer	0	All-time longest streak (used in churn risk formula)
`total_sessions`	integer	0	Lifetime session count
`total_messages`	integer	0	Lifetime message count
`avg_session_length`	real	0	Average session length in minutes
`churn_risk`	real	0	0.0–1.0, recomputed on every ingest
`last_active_at`	timestamp	null	Last message timestamp
`updated_at`	timestamp	now	Last state update

2.3 `ci_contact_memory`

A stored piece of knowledge about an end-user. Same architecture as AI Brain memory, but per-contact with slower decay.

Column	Type	Default	Description
`id`	text PK	—	Primary key
`contact_id`	text → ci_contacts	—	FK (cascade delete)
`tenant_id`	text	—	Owning business
`memory_type`	text	—	`fact` \| `preference` \| `episode` \| `pattern`
`content`	text	—	The memory (natural language)
`embedding`	vector(1536)	null	Semantic embedding. Null until async generation completes.
`importance`	real	0.5	0.0–1.0. Decays weekly. Reinforcement resets the clock.
`access_count`	integer	0	Times retrieved for context
`decay_rate`	real	0.005	Per-day importance decay. `fact`: 0.003, `preference`: 0.005, `episode`: 0.008, `pattern`: 0.003
`entity_ids`	text[]	null	Linked entity IDs (legacy `ci_contact_entities` IDs)
`source_type`	text	—	`conversation` \| `observation` \| `consolidation`
`source_id`	text	null	CE conversation ID that generated this memory
`source`	text	`'ai_extracted'`	`ai_extracted` \| `owner_manual` \| `contact_corrected`
`confidence`	real	0.7	0.0–1.0. `contact_corrected` memories always get 1.0.
`source_session_id`	text	null	CE session ID that produced this memory
`superseded_by`	text	null	ID of the newer memory that replaced this one. Active memories have `null`. Deleted memories have `"DELETED"`.
`last_reinforced_at`	timestamp	null	Decay is skipped for 14 days after reinforcement
`expires_at`	timestamp	null	Set by consolidation when importance drops below 0.05
`accessed_at`	timestamp	now	Last retrieval timestamp
`created_at`	timestamp	now	Record creation

Index: partial index on (contact_id) WHERE superseded_by IS NULL — used for all active-memory queries.

3. New Tables (Phase 3)

3.1 `ci_contact_channels`

Multi-channel identity links. A contact can have multiple channels linked under one profile.

Column	Type	Default	Description
`id`	text PK	—	Primary key
`contact_id`	text → ci_contacts	—	FK (cascade delete)
`tenant_id`	text	—	Owning business
`channel_type`	text	—	`whatsapp` \| `telegram` \| `website_widget` \| `instagram`
`external_id`	text	—	Channel-specific identifier
`confidence`	real	1.0	1.0 = explicit link, 0.8 = probabilistic match
`linked_at`	timestamp	now	When this channel was linked
`updated_at`	timestamp	now	—

Unique index: (tenant_id, channel_type, external_id) — one channel identity per tenant.

LLM-extracted entity graph nodes. Richer than the legacy ci_contact_entities table — supports motivation, goal, aversion, and behavior types, and links to ci_entity_relationships for graph traversal.

Column	Type	Default	Description
`id`	text PK	—	Primary key
`contact_id`	text → ci_contacts	—	FK (cascade delete)
`tenant_id`	text	—	Owning business
`type`	text	—	`person` \| `goal` \| `event` \| `behavior` \| `aversion` \| `motivation` \| `topic` \| `place`
`name`	text	—	Entity name (e.g., "Bruno", "Infosys", "biryani")
`properties`	jsonb	null	Type-specific metadata (e.g., `{ "breed": "golden retriever" }`)
`confidence`	real	0.8	How certain we are this entity is real
`last_seen_at`	timestamp	now	When this entity was last mentioned
`created_at`	timestamp	now	First extraction
`updated_at`	timestamp	now	—

Unique index: (contact_id, type, name) — one entity per (type, name) per contact.

3.3 `ci_entity_relationships`

Directed edges in the entity graph.

Column	Type	Default	Description
`id`	text PK	—	Primary key
`from_entity_id`	text → ci_entities	—	FK (cascade delete)
`to_entity_id`	text → ci_entities	—	FK (cascade delete)
`relationship`	text	—	`motivated_by` \| `triggered_by` \| `linked_to` \| `opposes` \| `part_of` \| `is_related_to`
`properties`	jsonb	null	Additional context about the relationship
`confidence`	real	0.8	LLM confidence in this relationship
`created_at`	timestamp	now	—

3.4 `ci_emotional_snapshots`

Emotional arc storage. One row per session (written at session-close) and one row per week (rolled up by consolidation).

Column	Type	Default	Description
`id`	text PK	—	Primary key
`contact_id`	text → ci_contacts	—	FK (cascade delete)
`tenant_id`	text	—	Owning business
`period_type`	text	—	`session` \| `weekly`
`period_start`	timestamp	—	Start of this period
`dominant_mood`	text	—	`happy` \| `neutral` \| `anxious` \| `sad` \| `frustrated` \| `excited` \| `uncertain`
`mood_score`	real	—	−1.0 to 1.0. Negative = negative mood, positive = positive.
`variance`	real	0	Mood variability within the period
`trend_direction`	text	null	`improving` \| `declining` \| `stable` \| `volatile` — weekly only
`key_triggers`	jsonb	null	Array of trigger labels (e.g., `["work_stress", "progress_win"]`)
`session_count`	integer	1	Number of sessions rolled up (weekly snapshots only)
`created_at`	timestamp	now	—

Unique index: (contact_id, period_type, period_start)

3.5 `ci_relationship_snapshots`

Weekly relationship trajectory. Written by the consolidation job every Sunday.

Column	Type	Default	Description
`id`	text PK	—	Primary key
`contact_id`	text → ci_contacts	—	FK (cascade delete)
`tenant_id`	text	—	Owning business
`period_start`	timestamp	—	Start of the week this snapshot covers
`stage`	text	—	`new` \| `building` \| `established` \| `deep` \| `fading` \| `dormant`
`relationship_score`	real	—	0.0–1.0 composite engagement score
`trajectory`	text	—	`moving_up` \| `stable` \| `moving_down` \| `volatile`
`velocity`	text	—	`slow` \| `moderate` \| `fast`
`signals`	jsonb	null	Raw signals: `{ session_freq_trend, depth_score, initiation_rate, days_since_last }`
`created_at`	timestamp	now	—

Unique index: (contact_id, period_start)

4. Outreach & Log Tables

4.1 `ci_outreach_triggers`

Column	Type	Default	Description
`id`	text PK	—	Primary key
`contact_id`	text → ci_contacts	—	FK (cascade delete)
`tenant_id`	text	—	Owning business
`trigger_type`	text	—	`scheduled` \| `inactivity` \| `milestone` \| `recurring`
`status`	text	`'pending'`	`pending` \| `fired` \| `cancelled` \| `failed` \| `expired`
`scheduled_at`	timestamp	—	When to fire
`fired_at`	timestamp	null	When it actually fired
`message`	text	null	Generated or template message
`context`	jsonb	null	Event context (e.g., `{ "milestone": "100_messages" }`)
`attempts`	integer	0	Send attempts
`max_attempts`	integer	3	Attempt cap
`last_error`	text	null	Last failure reason
`created_at`	timestamp	now	—

4.2 `ci_outreach_config`

Per-tenant outreach settings. Primary key is tenant_id — one row per tenant.

Column	Type	Default	Description
`tenant_id`	text PK	—	Owning business
`enabled`	boolean	true	Global outreach on/off switch
`inactivity_days`	integer	3	Days of silence before inactivity trigger
`max_inactivity_attempts`	integer	2	Max inactivity messages per inactive period
`min_gap_days`	integer	3	Minimum days between any two outreach messages to the same contact
`max_per_day`	integer	3	Max messages per day across all contacts
`quiet_hours_start`	integer	22	Hour (0–23) when quiet hours begin
`quiet_hours_end`	integer	8	Hour (0–23) when quiet hours end
`timezone`	text	`'Asia/Kolkata'`	Timezone for quiet hours evaluation
`updated_at`	timestamp	now	Last modification

4.3 `ci_interaction_logs`

Lightweight per-message signal log. Not the full conversation — just extracted signals.

Column	Type	Description
`id`	text PK	Primary key
`contact_id`	text → ci_contacts	FK (cascade delete)
`tenant_id`	text	Owning business
`conversation_id`	text	CE conversation ID
`message_role`	text	`user` \| `assistant`
`mood`	text	Classified mood for this message
`energy`	text	Classified energy
`memories_extracted`	integer	Inline extractions from this message
`memories_retrieved`	integer	Memories loaded for this message's response
`created_at`	timestamp	Message timestamp

5. Entity Relationships

6. State Machines

6.1 RelationshipStage

Computed by computeRelationshipStage() in src/lib/relationship.ts on every ingest.

Priority (highest to lowest):

dormant — 30+ days inactive (overrides everything)
fading — 7–29 days inactive (overrides positive progression)
deep — 50+ total messages AND 14+ day active streak
established — 20+ total messages AND 5+ day active streak
building — 5+ total messages
new — below all thresholds

new → building (5+ msgs)
    → established (20+ msgs & 5+ day streak)
    → deep (50+ msgs & 14+ day streak)
 
Any stage → fading (7–29 days inactive)
Any stage → dormant (30+ days inactive)

Re-engagement (any ingest) moves the contact back to the positive progression tier based on their current message/streak counts. A dormant contact who sends a message immediately computes a stage from their total message history.

6.2 OutreachTrigger Status

pending → fired        (scanner fired the message via CHAT_ENGINE_SERVICE)
pending → cancelled    (business owner cancelled / contact said "stop")
pending → failed       (delivery error after max_attempts)
pending → expired      (scheduled_at too far in the past, no longer relevant)
failed  → pending      (retry scheduled, attempts < max_attempts)

7. Key Domain Invariants

One ContactState per Contact. ci_contact_state.contact_id is the primary key. State is upserted, never duplicated.
Outreach respects "stop" absolutely. When contacts.outreach_disabled = true, the scanner skips all triggers for that contact. No override.
Active memories have superseded_by = null. All queries for "what does CI know" filter WHERE superseded_by IS NULL. Superseded and deleted memories are retained for audit but never served.
Memory importance floor is 0.05. Memories below this threshold have expires_at set during weekly consolidation. They are never hard-deleted immediately — this gives a recovery window.
Contact deletion cascades via FK, not application logic. The contacts route hard-deletes ci_contact_memory, ci_contact_entities, and ci_interaction_logs explicitly. The new CI tables (ci_entities, ci_emotional_snapshots, ci_relationship_snapshots, ci_contact_channels) rely on Postgres ON DELETE CASCADE from their FK to ci_contacts.
Inactivity outreach has hard limits. max_inactivity_attempts (default 2) caps the number of re-engagement messages per inactive period. After the limit, no more outreach until the contact re-engages.