AI Agent Memory — Memory DWH Schema & Data Spec for DSAI
Audience: DSAI PM / engineering. Purpose: the Memory DWH table schemas that will hold
the AI Agent's "customer memory" in Phase 1, and the read contract we're asking DSAI to build
against. Shared by BOT squad (Dimas Fauzi Hidayat).
1. Context in three sentences
The AI Agent Memory initiative gives the autonomous agent customer-level context before it
responds: past conversations, profile, deals, and tickets for the customer it is talking to.
This quarter the data is served from a dedicated Memory DWH (not CEBE — CEBE is the
long-term backbone; every column below carries its CEBE event-field lineage so the later
cutover changes the source, not the schema). DSAI is the consumer: it reads the serving
view once at conversation start and injects it into the agent's reasoning.
2. Read contract at a glance
| Aspect | Contract |
|---|
| Trigger | Once per conversation, at the first agent response — never per turn |
| Key | (organization_id, qontak_customer_id) — if identity is unresolved (null), skip memory entirely; no fuzzy matching |
| Latency budget | Hard 2,000 ms on the read; on timeout the agent answers without memory (auto-skip) — the reply path is never blocked |
| Caching | Fetched context is thread-cached; turns 2..n reuse it, no re-query |
| Flag | Org-level ai_agent_memory (default OFF) — flag state arrives in the prediction payload; no read when OFF |
| Degradation | Timeout / error / empty profile / null identity → answer exactly as today; failure reason recorded in telemetry |
| Data minimization | DSAI receives only allow-listed, masked fields — never raw payment data or government IDs; allow-list to be finalized together (§6) |
| Telemetry | Every response reports memory_status (hit / miss_no_identity / miss_empty_profile / skipped_flag_off / skipped_timeout / error) + read latency + modules present, returned in the response envelope |
Full behavior spec: the Phase-1 PRD (MEM-S01..S04) — linked in §7.
3. Schema conventions (all tables)
| Convention | Value |
|---|
| Standard columns on every table | organization_id STRING · company_sso_id STRING · qontak_customer_id STRING (FK → dim_customer) · source_module STRING · ingested_at TIMESTAMP · _source_pk STRING (lineage to the source row) |
| Tenancy | Every query is scoped by organization_id; cluster/sort keys = (organization_id, qontak_customer_id) |
| Partitioning | Fact tables partitioned by their primary date column (noted per table) |
| Types | Warehouse-generic: STRING / TEXT / INTEGER / NUMERIC / BOOLEAN / TIMESTAMP / DATE / JSON / ARRAY |
| Lineage | The Source column per field = CEBE event.field it mirrors (schema origin: CEBE ANCHOR §6) + this quarter's interim source system |
4. Table schemas — the four domains
4.1 dim_customer — customer profile (grain: 1 row per org + customer)
| Column | Type | Null | Source |
|---|
qontak_customer_id | STRING | NO — natural key w/ organization_id | customer.created.qontak_customer_id / hub-core contacts.qontak_customer_id |
organization_id | STRING | NO | envelope |
contact_handler_id | STRING | YES | hub-core contacts.contact_handler_id (cross-channel unification) |
full_name | STRING | YES | customer.created.default_fields[] / hub-core contacts.full_name |
phone_number | STRING | YES (PII) | default_fields[] / contacts.phone_number |
email | STRING | YES (PII) | default_fields[] / contacts.extra.email |
username | STRING | YES | contacts.extra.username |
channels | ARRAY<STRING> | YES | hub-core contact_handlers.channels[] |
is_blocked | BOOLEAN | NO, default false | hub-core contacts.is_blocked |
identity_confidence | STRING (resolved | channel_only) | NO | derived — channel_only rows are excluded from serving |
created_at / created_by | TIMESTAMP / STRING | YES | customer.created |
updated_at / updated_by | TIMESTAMP / STRING | YES | customer.updated |
first_seen_at / last_seen_at | TIMESTAMP | YES | derived from fact_conversation |
4.2 dim_customer_attribute — custom fields (grain: 1 row per customer × field)
| Column | Type | Null | Source |
|---|
qontak_customer_id, organization_id | STRING | NO | FK |
field_id | STRING | NO | hub-core contact_fields.contact_custom_field_id (carries cdp_field_property_id) |
field_name | STRING | NO | custom_fields[] / contact_custom_fields.name |
field_type | STRING | NO | contact_custom_fields.type |
value | JSON | YES | custom_fields[] / contact_fields.value |
date_value | DATE | YES | contact_fields.date_value |
updated_at | TIMESTAMP | NO | customer.updated.change_fields[] |
4.3 dim_customer_segment — segment membership (grain: customer × segment)
| Column | Type | Null | Source |
|---|
qontak_customer_id, organization_id | STRING | NO | FK |
segment_id / segment_name | STRING | NO | segment.entered |
entered_at | TIMESTAMP | NO | segment.entered |
exited_at | TIMESTAMP | YES — NULL = currently active | segment.exited |
4.4 fact_conversation — past conversations (grain: 1 row per room; partition: started_at)
| Column | Type | Null | Source |
|---|
room_id | STRING | NO — PK | conversation.started.room_id / hub-core rooms.id |
qontak_customer_id, organization_id | STRING | NO | envelope (via participants → identity spine) |
channel | STRING | NO | conversation.started.channel |
started_at | TIMESTAMP | NO — partition key | conversation.started / rooms.created_at |
assigned_at / agent_id / assignment_type (human | bot) | TIMESTAMP / STRING / STRING | YES | conversation.assigned |
resolved_at / resolved_by | TIMESTAMP / STRING | YES | conversation.resolved / rooms.resolved_at |
resolution_time_seconds | INTEGER | YES | conversation.resolved |
handled_by | STRING (human | bot) | YES | conversation.resolved.handled_by / chatbot rooms.closed_reason |
containment_flag / bot_flow_id | BOOLEAN / STRING | YES | bot.contained / chatbot closed_reason = RESOLVE_AI |
escalation_reason / bot_session_id | STRING | YES | conversation.escalated / closed_reason = ASSIGN_AGENT_AI |
csat_score / csat_comment | NUMERIC / STRING | YES | csat.submitted |
nps_score | NUMERIC | YES | nps.submitted |
summary | TEXT | YES (PII) | conversation.summarized extension / omnichannel_room_summaries.summary — the primary memory artifact |
sentiment | STRING | YES | same / omnichannel_room_summaries.sentiment |
summarized_at | TIMESTAMP | YES | omnichannel_room_summaries.summarized_at |
message_count | INTEGER | YES | derived from messages |
4.5 fact_message — optional, Phase 2 of the DWH (grain: 1 row per customer message; partition: created_at)
| Column | Type | Null | Source |
|---|
message_id | STRING | NO — PK | message.received.message_id |
room_id, qontak_customer_id, organization_id | STRING | NO | FK |
message_type | STRING | NO | message.received.message_type |
content_preview | STRING (truncated) | YES (PII) | message.received.content_preview — full bodies are never ingested |
sender_type | STRING | NO | hub-core messages.sender_type |
created_at | TIMESTAMP | NO — partition key | hub-core messages.created_at |
4.6 fact_deal — associated deals (grain: 1 row per deal, current state; partition: created_at)
| Column | Type | Null | Source |
|---|
deal_id | STRING | NO — PK | deal.created.deal_id |
qontak_customer_id, organization_id | STRING | NO | customer_association (CRM crm_lead_ids → identity spine) |
pipeline_id / stage_id | STRING | NO / NO — current stage | deal.created + latest deal.stage_changed.new_stage |
deal_size / currency | NUMERIC / STRING | YES | deal.created.deal_size |
owner_id | STRING | YES | deal.created.owner_id |
status | STRING (open | won | lost) | NO | derived from deal.won / deal.lost |
close_date | DATE | YES | deal.won.close_date |
lost_date / loss_reason | DATE / STRING | YES | deal.lost |
product_ids | ARRAY<STRING> | YES | product_association |
other_fields | JSON | YES | {other_fields} (CRM custom fields) |
created_at / updated_at | TIMESTAMP | NO / NO | deal.created / latest event |
Optional companion: fact_deal_stage_history (1 row per deal.stage_changed: deal_id,
old_stage, new_stage, days_in_stage, changed_by, changed_at) — analytics-grade;
not read by the serving view.
4.7 fact_ticket — associated tickets (grain: 1 row per ticket, current state; partition: created_at)
| Column | Type | Null | Source |
|---|
ticket_id | STRING | NO — PK | ticket.created.ticket_id |
qontak_customer_id, organization_id | STRING | NO | customer_association / hub-core room_tickets.account_uniq_id → identity spine |
room_id | STRING | YES — originating conversation | hub-core room_tickets.room_id |
pipeline_id / stage_id | STRING | NO / NO — current stage | ticket.created + latest ticket.stage_changed.new_stage |
priority_id / category_id | STRING | YES | ticket.created.{other_fields} |
assigned_id / team_id / assigned_at | STRING / STRING / TIMESTAMP | YES | ticket.assigned |
creator_id | STRING | YES | ticket.created.creator_id |
status | STRING (open | resolved) | NO | derived from ticket.resolved |
resolution_type | STRING (first_contact | escalated) | YES | ticket.resolved.resolution_type |
resolved_at / due_date | TIMESTAMP / DATE | YES | ticket.resolved / ticket.created |
sla_breached / sla_type / breach_duration_seconds | BOOLEAN / STRING / INTEGER | YES | ticket.sla_breached — lets the agent acknowledge a delayed ticket proactively |
other_fields | JSON | YES | {other_fields} |
created_at | TIMESTAMP | NO — partition key | ticket.created |
(Campaign/Ads, Commerce/Booking, and Loyalty are out of scope for memory Phase 1.)
5. The serving view DSAI reads — customer_memory_profile
One read → one object per (organization_id, qontak_customer_id), assembled from the tables above:
| Block | Shape | Built from |
|---|
profile | STRUCT: name, channels, segments, allow-listed attributes | dim_customer + dim_customer_attribute + dim_customer_segment |
conversation_history | ARRAY<STRUCT>, last N (default 5), newest first: summary, sentiment, handled_by, containment_flag, csat_score, started_at, channel | fact_conversation |
open_deals | ARRAY<STRUCT>: deal_id, stage_id, deal_size, status, updated_at (open first, then recent won/lost) | fact_deal |
open_tickets | ARRAY<STRUCT>: ticket_id, stage_id, status, sla_breached, due_date, created_at | fact_ticket |
stats | STRUCT: total_conversations, containment_rate, avg_csat, tickets_open, tickets_resolved, total_won_value | aggregates over the facts |
modules_present | ARRAY<STRING> — which domains had data (partial profiles are normal; the agent must not fabricate absent domains) | derived |
6. What we need from DSAI
- Memory client + context injection — read
customer_memory_profile at conversation start
within the 2,000 ms budget, inject into the agent's reasoning, thread-cache for later turns.
Injection mechanics (system-context block vs. retrieval tool) = your call, decided in the RFC.
- Telemetry in the response envelope —
memory_status, read latency, modules_present,
cached per response (feeds the adoption metric and rollout gates).
- Field allow-list review — confirm which columns from §4/§5 the model may receive
(PII-masking policy; PII-flagged columns above are the review set), and the thread retention
policy for the cached profile.
- A written contract + Q4 capacity commitment — interface, latency SLO, timeline. This is
the BUILD entry gate (G0) on our side: no build starts without it.
7. References
- Phase-1 PRD (behavior spec MEM-S01..S04): Confluence · repo
chatbot/ai-agent-memory/prds/phase-1-read-side-cebe-memory.md
- Initiative ANCHOR: Confluence
- CEBE event catalog (schema lineage origin): CEBE ANCHOR §6
- Full ingestion catalog (sources + identity spine, BOT-internal): repo
chatbot/ai-agent-memory/documentations/memory-dwh-ingestion-catalog.md