AI Agent Memory — Memory DWH Ingestion Catalog
Decision context (2026-07-22): for this quarter the AI Agent Memory initiative does not read from CEBE — a dedicated Memory DWH is stood up instead. CEBE remains the strategic backbone; this DWH is the interim serving store. To keep the future cutover cheap, target schemas below deliberately mirror the CEBE ANCHOR's module event catalog field sets — when CEBE is live, the migration is a source swap, not a schema redesign.
Every source below is code-verified (repo + table/API + file:line). Four domains, per Dimas: (1) past conversations from the contact's previous rooms, (2) customer profile, (3) associated deals, (4) associated tickets.
0. Identity spine (the join problem, solved first)
Everything in the DWH must key to one customer. What exists today:
| Key | Where it lives | Evidence | Note |
|---|---|---|---|
qontak_customer_id | hub-core contacts.qontak_customer_id — a real, indexed column | hub-core/spec/dummy/db/schema.rb:459, index (organization_id, qontak_customer_id) :473 | Primary DWH customer key. |
qontak_customer_id | chatbot contacts.parameters['qontak_customer_id'] — jsonb key, NOT a column | chatbot/app/core/repositories/contacts/find_or_create_by.rb:223-236 | Ingestion must extract from jsonb; resolved at runtime from param → existing → ContactService phone lookup. |
contact_master_id + contact_handlers | hub-core cross-channel unification (contact_ids[], account_uniq_ids[], channels[]) | schema.rb:458, contact_handlers :335-348 | Collapses one person across WhatsApp/IG/email into one handler — use to dedupe. |
account_uniq_id | hub-core rooms.account_uniq_id (NOT NULL), contacts.account_uniq_id, room_tickets.account_uniq_id | rooms :1535-1569 | The channel identity — joins rooms and tickets to a contact without CRM. |
| Phone fallback | ContactService GET /api/v1/system/contacts/search?phone[]=&company_sso_id= | chatbot/lib/contact_service/contacts/search.rb:21-33 | Last-resort resolution; also how chatbot backfills the jsonb key. |
DWH rule: dim_customer is keyed on (organization_id, qontak_customer_id), built from
hub-core contacts + contact_handlers; rows lacking qontak_customer_id fall back to
contact_handler_id as a provisional key and are flagged identity_confidence: channel_only.
This is the same identity boundary the Phase-1 PRD sets (no fuzzy matching at serve time).
1. Past conversations (previous rooms of the contact)
Sources
| # | Source | Key fields | Evidence | Ingestion note |
|---|---|---|---|---|
| 1.1 | hub-core rooms (Postgres, uuid PK) — the real conversation entity | id, status (unassigned/assigned/resolved/deleted), type (CustomerService/Comment/Review/Group/DirectSend STI), account_uniq_id, organization_id, channel_integration_id, resolved_at, resolved_by_id/type, division_id, created_at, extra (jsonb) | hub-core/spec/dummy/db/schema.rb:1535-1569; STI + status enums models/room.rb:19-25,60-66 | CDC or nightly batch. Filter type = CustomerServiceRoom for v1. |
| 1.2 | hub-core messages (Postgres, uuid PK, range-partitioned by created_at) | room_id, type (STI), sender_type/sender_id, participant_id, text, file, url, status (created/sent/delivered/read/failed/deleted), raw_message (jsonb), reply_id, is_hidden, created_at | schema :859-898; status enum models/message.rb:62-69; partition caveat: hub-core AGENTS.md | Every extract query MUST carry a created_at range or it scans all partitions. High volume — see the "summaries-first" recommendation below. |
| 1.3 | hub-core participants — room ↔ contact/agent/bot link | room_id, contact_able_id/type (Customer/Agent/Bot STI), contact_id, active_status, handover_id | schema :1345-1362 | The join path room → contact → identity spine. Also partitioned — same caveat. |
| 1.4 | chatbot rooms — bot-side room mirror with the outcome | contact_id, channel_room_id, is_closed, closed_reason (indexed — RESOLVE_AI / ASSIGN_AGENT_AI / RESOLVE containment spine), closed_at, assign_channel_agent_id, path_id/path_name, channel_type_code | chatbot/db/schema.rb:1761-1811 | Enrichment: gives each past conversation its resolution outcome (was it AI-contained?). Join to hub-core rooms via channel_room_id ↔ rooms.external_id. |
| 1.5 | chatbot histories — bot turn log | room_id, contact_id, input_content (message text), message_id, intent_id/intent_name, path_id, parameters (jsonb), channel_type_code, created_at | chatbot/db/schema.rb:567-613 | Optional v1 — mostly redundant with 1.2 for memory purposes; useful for bot-turn analytics. |
| 1.6 | chatbot_gpt omnichannel_room_summaries — AI summary + sentiment per room | room_id (unique where not deleted), contact_id, company_id, summary, sentiment, channel, room_created_at, is_auto_summarized, gpt vendor/model + token usage | chatbot/db/chatbot_gpt_schema.rb:252-285 | The highest-value memory artifact per byte — compact, already synthesized, contact-keyed. Coverage caveat: only summarized rooms; measure coverage % before relying on it. |
Target DWH shape
dim_room— one row per past room: hub-core room id + chatbot outcome enrichment (closed_reason, resolved_at, channel, division).fact_conversation_summary— one row per summarized room (source 1.6): summary, sentiment.fact_message(optional / phase 2 of the DWH) — raw messages. Recommendation: do NOT ingest full message bodies in v1. For memory serving, "summaries of prior rooms + last-N messages of the most recent prior room" beats full-history ingestion on volume, PII surface, and prompt-budget — and the summaries table already exists. If raw messages are needed later, ingest per-partition withcreated_atwindows and a rolling retention (e.g. 12 months, matching CEBE's backfill horizon).
2. Customer profile
Sources
| # | Source | Key fields | Evidence | Ingestion note |
|---|---|---|---|---|
| 2.1 | hub-core contacts | qontak_customer_id, contact_master_id, contact_handler_id, full_name, phone_number, extra (jsonb → email/username), account_uniq_id, channel, channel_integration_id, is_blocked, authority, organization_id | schema :437-476; builder surfaces email/username from extra (builders/contact.rb:6-21) | Primary profile source. CDC or daily batch. |
| 2.2 | hub-core contact_handlers — unified person across channels | phone_number, email, username, full_name, channels[], contact_ids[], channel_integration_ids[], account_uniq_ids[] | schema :335-348 | The dedupe layer for dim_customer. |
| 2.3 | hub-core contact_fields + contact_custom_fields — per-org custom attributes | definitions: name, type, validation (jsonb), cdp_field_property_id; values: contact_handler_id, contact_custom_field_id, name, value (jsonb), date_value | schema :294-307, :321-333 | Custom fields are per-org and typed — land as key-value (dim_customer_attribute), not wide columns. Note cdp_field_property_id already anticipates CDP alignment. |
| 2.4 | chatbot contacts.crm_contact (jsonb) — cached CRM contact | known keys read in code: first_name, last_name, additional_fields, crm_gender_name, creator_id/username/name/email | written contacts/update.rb:16-17; populated via QontakCrmService::Contacts::Contact#find_by_phone_number (process_incoming_message_with_resolve.rb:565-566) | Cache, not source of truth — freshness is "whenever the contact last messaged". Use only as fallback enrichment; prefer 2.1–2.3 + CRM API. |
| 2.5 | ContactService (internal contacts service) | GET /api/v1/system/contacts/{id}?company_sso_id= · GET /api/v1/system/contacts/search?phone[]= | chatbot/lib/contact_service/contacts/find_by_id.rb:16-25, search.rb:21-33 | API source for point lookups; not a bulk-ingest path. |
Target DWH shape
dim_customer— identity spine (org +qontak_customer_id+ handler), standard fields (name, phone, email, channels, blocked/authority), first/last seen.dim_customer_attribute— custom fields as typed key-value rows (source 2.3).
3. Associated deals
Reality check: no deal table exists in chatbot or hub-core, and no "list deals by contact" endpoint exists anywhere in these repos. Deals live in the external Qontak CRM service; the local repos only hold per-room links and write/read hooks:
| # | Source | Key fields | Evidence | Ingestion note |
|---|---|---|---|---|
| 3.1 | hub-core qontak_crm_objects — room ↔ deal link | room_id, organization_id, external_id (CRM deal id), external_url, object_type (deal|ticket) | schema :1461-1471; models/qontak_crm_object.rb:8-16 | Ingest as the room→deal edge; join deals to conversations (and via room → contact). |
| 3.2 | CRM API — single deal | GET /api/v{v}/deals/{deal_id} (hub-core get_deal); chatbot node qontak_crm_deal_read → GET /api/mobile/v2.7/crm/deals?unique_deal_id= | hub-core/app/apps/crm/services/deal/apis.rb:8, repositories/room/get_deal.rb:14,42-44; chatbot/.../mekari_qontak_crm/execute.rb:18-25 | Per-id enrichment only — not a bulk path. |
| 3.3 | CRM deal webhook — hub-core already receives deal events | deal_receiver.rb | hub-core/app/apps/crm/repositories/webhook/deal_receiver.rb | Proves a CRM→consumer deal event feed exists. The cheapest bulk path: point an equivalent webhook/event feed at the DWH. |
| 3.4 | Contact→CRM linkage | chatbot deal-create auto-enriches crm_lead_ids via GET /api/v3.1/contacts?phone= | execute.rb:262-296 | The deal↔customer association key on the CRM side is crm_lead_ids (CRM contact ids). |
Target DWH shape + the dependency
fact_deal— mirror the CEBE Deals catalog fields so cutover is free:deal_id,pipeline_id,stage_id,deal_size,owner_id,status(open/won/lost),close_date,loss_reason,customer_association(→qontak_customer_id), timestamps.- Blocking dependency (CRM squad): bulk deal ingestion needs ONE of — (a) CRM DB replica/CDC access, (b) a deal event/webhook feed into the DWH (the receiver pattern in 3.3 shows this exists on the CRM side), or (c) a new "list deals by contact/org since X" API. Without one of these, deals are only reachable per-room-link + per-id enrichment (3.1 + 3.2), which covers deals created from conversations but misses deals created directly in CRM.
4. Associated tickets
Tickets are better served than deals — hub-core has a link table with a by-contact query already in production:
| # | Source | Key fields | Evidence | Ingestion note |
|---|---|---|---|---|
| 4.1 | hub-core room_tickets — room ↔ ticket link + cached fields | room_id, organization_id, account_uniq_id, name, no, description, external_id (CRM ticket id), external_url, external_status, template_id, created/submitted by/at | schema :1507-1533 | Primary local ticket edge; account_uniq_id joins straight to the contact — no CRM call needed for the association. |
| 4.2 | By-contact ticket history (existing!) | RoomTicket.where(account_uniq_id:, organization_id:) → paginate → enrich each via CRM get_ticket(external_id) | hub-core/app/apps/crm/repositories/room/get_tickets_by_account_uniq_id.rb:14,47-61 | The exact access pattern the DWH replicates in batch. |
| 4.3 | CRM API — single ticket | GET /api/v{v}/tickets/{ticket_id}; chatbot node qontak_crm_ticket_read → GET /api/mobile/v2.8/tickets/{id} | ticket/apis.rb:57; execute.rb:21-23,311-353 | Per-id enrichment for status/stage detail. |
| 4.4 | CRM ticket webhook — receiver exists | ticket_receiver.rb | hub-core/app/apps/crm/repositories/webhook/ticket_receiver.rb | Same play as deals: event feed → DWH keeps external_status fresh without polling. |
| 4.5 | Ticket field reference (from the explicit create payload) | name, user_id, ticket_stage_id, ticket_priority_id, ticket_category_id, description, due_date, crm_lead_ids[], crm_company_id, additional_fields[] | hub-core/app/apps/crm/repositories/ticket/create_to_crm.rb:54-68 | The field vocabulary for fact_ticket. |
Target DWH shape
fact_ticket— mirror the CEBE Ticketing catalog:ticket_id,pipeline_id,stage_id,priority,category,assigned_id,status,created_at,resolved_at,due_date,customer_association(viaaccount_uniq_id→ identity spine),room_id(originating conversation, when any).- Same CRM-side gap as deals for tickets created outside conversations:
room_ticketsonly knows conversation-born tickets. Full coverage needs the webhook/replica path (4.4).
5. Cross-cutting requirements
| Concern | Requirement |
|---|---|
| Multi-tenancy | Every table carries organization_id; note the type mismatch — hub-core uses uuid orgs, chatbot uses bigint + company_id strings in the gpt schema. The DWH normalizes to one org key and stores the source ids. |
| PII | PII columns: phone, email, name, message text, summaries. Apply the same masking/allow-list policy the Phase-1 PRD demands of the CEBE AI-consumer role — the serving layer exposes only the allow-listed fields to DSAI. Raw message bodies (if ever ingested) are the largest PII surface — another reason for summaries-first. |
| Freshness | Conversations/summaries: ≤ 1h lag is enough (memory reads at next conversation start, not mid-conversation). Profile: daily. Deals/tickets: webhook-driven near-real-time where available, else daily batch + per-id refresh on read. |
| Partitioned tables | hub-core messages + participants extracts must window on created_at (range partitions) — full-scan extracts will not finish. |
| Retention | 12-month rolling window (matches CEBE's backfill horizon), summaries retained longer (compact). Erasure: deleting a customer cascades by qontak_customer_id — one key, one delete path. |
| Serving view | customer_memory_profile — one row per (org, customer): profile fields + custom attributes + last-N conversation summaries w/ sentiment + open/recent deals + open/recent tickets + containment stats (closed_reason rollup). This is the read shape the retriever/DSAI consumes — identical to what the Phase-1 PRD's MEM-S02 expects, so the PRD's behavior spec is unchanged by the source swap. |
6. CEBE event-catalog field spec (the fields to ingest, per event)
Source: CEBE ANCHOR §6 Module Event Catalog (repo:
cdp/customer-event-based-engine-cebe/prds/prd-customer-event-based-engine-cebe-anchor.md). These event shapes are the Memory DWH's target schema; this quarter the interim pipeline populates the same fields from the §1–§4 sources. Modules 4 (Campaign/Ads), 5 (Commerce/Booking), and 7 (Loyalty) are excluded — not part of the four memory domains.
Common envelope (every event): company_sso_id · company_id · qontak_customer_id ·
actor · event_timestamp · source_module · trigger_from · event_name ·
event_description · event_attributes
6.1 Past conversations ← Module 1 (Communication)
| Event | Fields to ingest | Memory use | Interim source (this quarter) |
|---|---|---|---|
conversation.started | channel, room_id, phone, email, bsuid | Conversation index + channel preference | hub-core rooms + participants |
conversation.assigned | agent_id, assignment_type (human/bot) | Prior handler | hub-core rooms.status/participants; chatbot rooms.assign_channel_agent_id |
message.received | message_id, message_type, content_preview | Last-topic recall (preview only — full text deliberately not in the catalog) | hub-core messages (windowed on created_at) |
conversation.resolved | resolved_by, resolution_time_seconds, handled_by (human/bot) | Outcome per past conversation | hub-core rooms.resolved_at/resolved_by_*; chatbot rooms.closed_reason → handled_by |
conversation.escalated | escalation_reason, bot_session_id | "Escalated before, on X" | chatbot rooms.closed_reason = ASSIGN_AGENT_AI + histories |
bot.contained | bot_flow_id, containment_flag | AI-containment history | chatbot rooms.closed_reason = RESOLVE_AI |
csat.submitted / nps.submitted | score, comment, agent_id | Sentiment / at-risk signal | hub-core review fields (messages.review_star) / CSAT source |
call.* (only if voice in scope) | duration_seconds, call_type, recording_url | Optional — skip v1 | — |
Catalog gap (flag to CDP squad): no conversation-summary event exists in §6, and the
highest-value memory artifact is the per-room summary + sentiment. Ingest
omnichannel_room_summaries alongside as an extension — proposed event shape
conversation.summarized: room_id, summary, sentiment — and propose it for the CEBE
schema so the future cutover keeps parity.
6.2 Customer profile ← Module 6 (CDP)
| Event | Fields to ingest |
|---|---|
customer.created | qontak_customer_id, default_fields[], custom_fields[], created_at, created_by |
customer.updated | qontak_customer_id, change_fields[], updated_at, updated_by |
segment.entered / segment.exited | qontak_customer_id, segment_id, segment_name |
Profile serves as the current-state materialization of these events (dim_customer +
dim_customer_attribute), built this quarter from hub-core contacts/contact_handlers/
contact_fields (§2).
6.3 Associated deals ← Module 3 (Deals)
| Event | Fields to ingest |
|---|---|
deal.created | deal_id, pipeline_id, stage_id, deal_size, owner_id, {other_fields}, customer_association |
deal.stage_changed | deal_id, old_stage, new_stage, days_in_stage, timestamp, changed_by, customer_association |
deal.won | deal_id, deal_size, close_date, {other_fields}, customer_association, product_association |
deal.lost | deal_id, deal_size, lost_date, loss_reason, {other_fields}, customer_association, product_association |
deal.rotten_time_breached | deal_id, deal_size, breach_duration_seconds — low memory value, optional |
customer_association resolves to qontak_customer_id (code-side: crm_lead_ids).
Interim source: CRM webhook/replica per §3 (the bulk-access dependency).
6.4 Associated tickets ← Module 2 (Ticketing)
| Event | Fields to ingest |
|---|---|
ticket.created | ticket_id, pipeline_id, stage_id, assigned_id, creator_id, {other_fields}, customer_association |
ticket.assigned | ticket_id, agent_id, team_id, assigned_at |
ticket.stage_changed | ticket_id, old_stage, new_stage, changed_by, timestamp |
ticket.resolved | ticket_id, resolved_at, resolution_type (first_contact/escalated), agent_id |
ticket.sla_breached | ticket_id, sla_type, breach_duration_seconds — lets the agent acknowledge a delayed ticket |
Interim source: hub-core room_tickets + CRM get_ticket + ticket webhook per §4.
7. Open items
- CRM bulk access for deals + tickets (replica/CDC vs webhook feed vs new API) — the one genuinely missing source; owner: CRM squad conversation. Deals are the weakest domain today.
- Summary coverage % — what fraction of resolved rooms have an
omnichannel_room_summariesrow; if low, decide whether the DWH pipeline backfills summaries (batch summarization job) or memory serves last-N raw messages instead. - Identity coverage % — share of hub-core contacts with non-null
qontak_customer_id(same feasibility ask as Phase-1 PRD Open Question #5). - Data/BI sizing — messages volume per org per month; decides whether
fact_messageis ever viable or summaries-only is final. - PRD alignment — the Phase-1 PRD currently names CEBE retriever + DSAI-direct as the source; this quarter's source is the Memory DWH instead. The behavior spec (MEM-S01..S04) and serving contract survive unchanged; the Dependencies section and G0 gate need a targeted update once this direction is confirmed with engineering.