Unified Customer Timelines: Combining Support Tickets, Purchases, and Product Usage into One Feed
Stop switching between helpdesk, billing, and analytics tabs. Build a unified customer timeline with a canonical event model, identity resolution, idempotent Kafka ingestion, and a partitioned PostgreSQL feed.

Ask a support agent why a customer is angry and the honest answer is usually spread across three browser tabs. The helpdesk shows a ticket escalated twice this week. The billing system shows an invoice that failed on Monday and a downgrade on Tuesday. The product analytics tool shows that the customer's weekly active users fell off a cliff right after a release. Each system is correct about its own slice, and none of them can explain the story on its own.
A unified customer timeline fixes this by putting every meaningful thing that happened to an account into one chronologically ordered, filterable feed. Done well, it becomes the backbone of a custom CRM: account health scoring, renewal prep, support triage, and churn analysis all read from the same stream instead of from three integrations that disagree.
This article walks through how to build one properly. We cover the canonical event model, identity resolution, ingestion with idempotency, a PostgreSQL storage design, the read queries that power the feed, and the operational traps that make timelines quietly wrong. Code is written for PostgreSQL 15+, Kafka with Debezium, and TypeScript, but the patterns carry over to other stacks. Any numbers below are illustrative assumptions for sizing, not benchmarks.
Why Three Systems Produce Three Truths#
Most teams start by embedding widgets: a Zendesk sidebar in the CRM, a Stripe link on the account page, a Mixpanel deep link. This fails for structural reasons.
- Different identities. The helpdesk knows an email address, billing knows a
cus_customer ID, product analytics knows an anonymous device ID that later merged into a user ID. Nobody agrees on what "the customer" is. - Different clocks and time semantics. A ticket has
created_atandupdated_at. An invoice has a billing period, a due date, and a paid-at time. A usage event has a client timestamp that may be wrong by minutes. - Different granularity. One purchase is one row; one hour of product usage is thousands of events. Mixing them without a policy produces a feed where a single login burst buries a refund.
- Different retention and access rules. Support transcripts may contain personal data that sales should not see. Billing data may be restricted to finance roles.
A timeline is not a join across these systems. It is a derived, append-mostly log with its own schema, built to be read in time order. That distinction drives every design decision that follows.
The Canonical Event Model#
The core idea is to normalize everything into one envelope while preserving the source payload for drill-down. Resist the urge to model each source's fields as columns. The envelope should carry only what the feed needs to sort, filter, group, and render.
| Field | Purpose | Example |
|---|---|---|
event_id | Deterministic, source-derived ID for idempotency | zendesk:ticket:48213:status:solved:1760001000 |
account_id | Internal tenant/company the event belongs to | acc_01HV... |
contact_id | Optional person within the account | con_01HV... |
occurred_at | When it happened in the real world | 2026-10-06T14:02:11Z |
ingested_at | When the timeline learned about it | 2026-10-06T14:02:19Z |
source | System of origin | zendesk, stripe, product |
kind | Namespaced event type | support.ticket.escalated |
severity | Weight for UI and health scoring | info, notable, critical |
summary | Pre-rendered one-line description | Ticket #48213 escalated to Tier 2 |
payload | Source-specific details as JSONB | {"priority":"high"} |
occurred_at orders the feed; ingested_at lets you debug lag, reprocess ranges, and detect late-arriving data. Conflating them is the most common modeling error we see.A Declarative Mapping Layer#
Rather than hard-coding transformations in each consumer, keep mappings as configuration. This makes it reviewable and lets non-authors add a new event type without touching pipeline code.
400 font-semibold">class=400 font-semibold">class="text-emerald-300">"text-slate-500 italic"># timeline-mappings.yaml
version: 3
sources:
zendesk:
identity:
account_from: organization.external_id
contact_from: requester.email
events:
- match: { 400 font-semibold">type: ticket, change: created }
kind: support.ticket.created
severity: info
summary: 400 font-semibold">class="text-emerald-300">"Ticket 400 font-semibold">class="text-slate-500 italic400 font-semibold">class="text-emerald-300">">#{{ ticket.id }} opened: {{ ticket.subject }}"
occurred_at: ticket.created_at
- match: { 400 font-semibold">type: ticket, change: priority, to: urgent }
kind: support.ticket.escalated
severity: notable
summary: 400 font-semibold">class="text-emerald-300">"Ticket 400 font-semibold">class="text-slate-500 italic400 font-semibold">class="text-emerald-300">">#{{ ticket.id }} marked urgent"
occurred_at: audit.created_at
- match: { 400 font-semibold">type: ticket, change: status, to: solved }
kind: support.ticket.solved
severity: info
summary: 400 font-semibold">class="text-emerald-300">"Ticket 400 font-semibold">class="text-slate-500 italic400 font-semibold">class="text-emerald-300">">#{{ ticket.id }} solved"
occurred_at: audit.created_at
stripe:
identity:
account_from: customer.metadata.account_id
events:
- match: { 400 font-semibold">type: invoice.payment_failed }
kind: billing.payment_failed
severity: critical
summary: 400 font-semibold">class="text-emerald-300">"Payment of {{ amount_due | money }} failed"
occurred_at: created
- match: { 400 font-semibold">type: customer.subscription.updated, plan_changed: 400">true }
kind: billing.plan_changed
severity: notable
summary: 400 font-semibold">class="text-emerald-300">"Plan changed to {{ plan.nickname }}"
occurred_at: created
product:
identity:
account_from: group_id
contact_from: user_id
rollups:
- kind: usage.daily_summary
window: 1d
severity: info
The rollups key under product is deliberate. Raw usage events never enter the feed directly; we return to that below.
Identity Resolution: The Hard Part#
Everything downstream depends on correctly attributing an event to an account. Get this wrong and the timeline shows another customer's invoice, which is worse than showing nothing.
An Identity Graph, Not a Lookup Column#
Maintain an explicit mapping table from external identifiers to internal accounts and contacts. Each row records which system asserted the link and with what confidence.
400 font-semibold">CREATE 400 font-semibold">TABLE identity_link (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
source TEXT NOT NULL, -- 400 font-semibold">class="text-emerald-300">'stripe', 400 font-semibold">class="text-emerald-300">'zendesk', 400 font-semibold">class="text-emerald-300">'product'
external_type TEXT NOT NULL, -- 400 font-semibold">class="text-emerald-300">'customer_id', 400 font-semibold">class="text-emerald-300">'org_id', 400 font-semibold">class="text-emerald-300">'email', 400 font-semibold">class="text-emerald-300">'user_id'
external_id TEXT NOT NULL,
account_id UUID NOT NULL REFERENCES account(id),
contact_id UUID REFERENCES contact(id),
method TEXT NOT NULL, -- 400 font-semibold">class="text-emerald-300">'explicit', 400 font-semibold">class="text-emerald-300">'email_domain', 400 font-semibold">class="text-emerald-300">'manual'
confidence NUMERIC(3,2) NOT NULL CHECK (confidence BETWEEN 0 AND 1),
valid_from TIMESTAMPTZ NOT NULL DEFAULT now(),
valid_to TIMESTAMPTZ,
UNIQUE (source, external_type, external_id, valid_from)
);
400 font-semibold">CREATE 400 font-semibold">INDEX identity_link_lookup
ON identity_link (source, external_type, external_id)
400 font-semibold">WHERE valid_to IS NULL;
Three rules keep this table trustworthy:
- Prefer explicit links. Write your internal
account_idinto Stripe customer metadata and the helpdesk organization's external ID at provisioning time. Deterministic beats inferred. - Use inference only below an auto-attach threshold. Matching on email domain is fine for suggestions, but free-mail domains such as gmail.com must never auto-attach. Route low-confidence matches to a review queue.
- Make links temporal. Accounts merge, contacts change companies, and subscriptions move between workspaces. With
valid_fromandvalid_to, you can re-resolve history instead of rewriting it blindly.
Ingestion Architecture#
Each source delivers data differently, so the ingestion layer should accept all of them and converge on a single stream before normalization.
Zendesk webhooks ──┐
Stripe webhooks ──┤ ┌─> identity resolver ─┐
App Postgres CDC ──┼─> Kafka: raw.<src> ──┤ ├─> Kafka: timeline.events
Product events ──┘ (per-source topics)└─> mapper (YAML) ─────┘ │
▼
timeline writer (idempotent)
│
▼
PostgreSQL (partitioned)
Webhooks are convenient but lossy: providers retry, reorder, and occasionally drop. For sources where you own the database, change data capture is more reliable. A Debezium connector for the application database looks like this:
{
400 font-semibold">class="text-emerald-300">"name": 400 font-semibold">class="text-emerald-300">"app-orders-cdc",
400 font-semibold">class="text-emerald-300">"config": {
400 font-semibold">class="text-emerald-300">"connector.400 font-semibold">class": 400 font-semibold">class="text-emerald-300">"io.debezium.connector.postgresql.PostgresConnector",
400 font-semibold">class="text-emerald-300">"database.hostname": 400 font-semibold">class="text-emerald-300">"app-db.internal",
400 font-semibold">class="text-emerald-300">"database.port": 400 font-semibold">class="text-emerald-300">"5432",
400 font-semibold">class="text-emerald-300">"database.user": 400 font-semibold">class="text-emerald-300">"debezium",
400 font-semibold">class="text-emerald-300">"database.dbname": 400 font-semibold">class="text-emerald-300">"app",
400 font-semibold">class="text-emerald-300">"plugin.name": 400 font-semibold">class="text-emerald-300">"pgoutput",
400 font-semibold">class="text-emerald-300">"slot.name": 400 font-semibold">class="text-emerald-300">"timeline_orders",
400 font-semibold">class="text-emerald-300">"publication.name": 400 font-semibold">class="text-emerald-300">"timeline_pub",
400 font-semibold">class="text-emerald-300">"table.include.list": 400 font-semibold">class="text-emerald-300">"400 font-semibold">public.orders,400 font-semibold">public.refunds,400 font-semibold">public.licenses",
400 font-semibold">class="text-emerald-300">"topic.prefix": 400 font-semibold">class="text-emerald-300">"raw.app",
400 font-semibold">class="text-emerald-300">"snapshot.mode": 400 font-semibold">class="text-emerald-300">"initial",
400 font-semibold">class="text-emerald-300">"tombstones.on.delete": 400 font-semibold">class="text-emerald-300">"400">false",
400 font-semibold">class="text-emerald-300">"heartbeat.interval.ms": 400 font-semibold">class="text-emerald-300">"10000"
}
}
Set a heartbeat. On a quiet table, an unadvanced replication slot makes PostgreSQL retain WAL indefinitely, which is a common way to fill a disk.
For SaaS sources, treat webhooks as a trigger rather than a source of truth where possible. Verify the signature, enqueue the raw body immediately, return 2xx fast, and let a worker fetch the current object from the provider API if the payload is thin. Stripe, for example, signs webhooks and recommends verifying them with the endpoint secret.
Idempotent Writes#
Every layer here is at-least-once, so the writer must tolerate duplicates. Derive event_id deterministically from the source object and the change, never from a random UUID.
400 font-semibold">import { createHash } 400 font-semibold">from 400 font-semibold">class="text-emerald-300">"node:crypto";
400 font-semibold">export 400 font-semibold">function eventId(source: 400">string, objectId: 400">string, change: 400">string, version: 400">string | 400">number): 400">string {
400 font-semibold">return createHash(400 font-semibold">class="text-emerald-300">"sha256")
.update([source, objectId, change, String(version)].join(400 font-semibold">class="text-emerald-300">"|"))
.digest(400 font-semibold">class="text-emerald-300">"hex")
.slice(0, 32);
}
Then the insert becomes a no-op on replay:
400 font-semibold">INSERT INTO timeline_event
(event_id, account_id, contact_id, occurred_at, ingested_at,
source, kind, severity, summary, payload)
VALUES ($1, $2, $3, $4, now(), $5, $6, $7, $8, $9)
ON CONFLICT (event_id, occurred_at) DO NOTHING;
Note the composite conflict target. On a partitioned table, a unique constraint must include the partition key, which leads directly to the storage design.
Storage Design in PostgreSQL#
At moderate scale, PostgreSQL is a better fit for the timeline than a dedicated event store. You need transactional writes, rich filters, and joins to account metadata, and you rarely need more than tens of millions of rows per tenant-year.
Range-Partitioned Event Table#
400 font-semibold">CREATE 400 font-semibold">TABLE timeline_event (
event_id TEXT NOT NULL,
account_id UUID NOT NULL,
contact_id UUID,
occurred_at TIMESTAMPTZ NOT NULL,
ingested_at TIMESTAMPTZ NOT NULL DEFAULT now(),
source TEXT NOT NULL,
kind TEXT NOT NULL,
severity TEXT NOT NULL DEFAULT 400 font-semibold">class="text-emerald-300">'info'
CHECK (severity IN (400 font-semibold">class="text-emerald-300">'info',400 font-semibold">class="text-emerald-300">'notable',400 font-semibold">class="text-emerald-300">'critical')),
summary TEXT NOT NULL,
payload JSONB NOT NULL DEFAULT 400 font-semibold">class="text-emerald-300">'{}',
PRIMARY KEY (event_id, occurred_at)
) PARTITION BY RANGE (occurred_at);
400 font-semibold">CREATE 400 font-semibold">TABLE timeline_event_2026_10
PARTITION OF timeline_event
FOR VALUES 400 font-semibold">FROM (400 font-semibold">class="text-emerald-300">'2026-10-01') TO (400 font-semibold">class="text-emerald-300">'2026-11-01');
-- The index that powers the feed: one account, newest first.
400 font-semibold">CREATE 400 font-semibold">INDEX timeline_event_feed
ON timeline_event (account_id, occurred_at DESC, event_id);
-- Filtered views, e.g. only critical events across accounts.
400 font-semibold">CREATE 400 font-semibold">INDEX timeline_event_critical
ON timeline_event (occurred_at DESC)
400 font-semibold">WHERE severity = 400 font-semibold">class="text-emerald-300">'critical';
Partitioning by month gives cheap retention (detach and archive old partitions) and keeps each index small enough to stay hot. Use pg_partman or a scheduled job to create partitions ahead of time; a missing partition causes insert failures at the first event of the new month.
Sizing as a Worked Example#
Assume 5,000 active accounts, an average of 40 timeline events per account per month after rollups, and about 600 bytes per row including indexes. That is 200,000 rows and roughly 120 MB per month, or about 1.4 GB per year. These are planning assumptions, not measurements, but they show why a single well-indexed PostgreSQL instance is usually enough. Raw usage events at, say, 20,000 per account per month would instead be 100 million rows monthly, which is why rollups matter.
Taming Product Usage Noise#
Usage telemetry is the volume problem. If every click is a timeline row, the feed is useless and the table is enormous. The policy we recommend has three tiers:
| Tier | Where it lives | Enters the feed? |
|---|---|---|
| Raw events (clicks, page views) | Analytics warehouse or ClickHouse | No |
| Daily and weekly rollups per account | timeline_event as usage.daily_summary | Yes, collapsed by default |
| Milestones (first API call, feature adopted, seat limit hit) | timeline_event | Yes, prominent |
400 font-semibold">INSERT INTO timeline_event
(event_id, account_id, occurred_at, source, kind, severity, summary, payload)
400 font-semibold">SELECT
400 font-semibold">class="text-emerald-300">'product:daily:' || account_id || 400 font-semibold">class="text-emerald-300">':' || day::text,
account_id,
day + interval 400 font-semibold">class="text-emerald-300">'23 hours 59 minutes',
400 font-semibold">class="text-emerald-300">'product',
400 font-semibold">class="text-emerald-300">'usage.daily_summary',
CASE WHEN active_users = 0 THEN 400 font-semibold">class="text-emerald-300">'notable' ELSE 400 font-semibold">class="text-emerald-300">'info' END,
format(400 font-semibold">class="text-emerald-300">'%s active users, %s sessions, %s key actions', active_users, sessions, key_actions),
jsonb_build_object(400 font-semibold">class="text-emerald-300">'active_users', active_users, 400 font-semibold">class="text-emerald-300">'sessions', sessions, 400 font-semibold">class="text-emerald-300">'key_actions', key_actions)
400 font-semibold">FROM daily_account_usage
400 font-semibold">WHERE day = current_date - 1
ON CONFLICT (event_id, occurred_at) DO 400 font-semibold">UPDATE
SET summary = EXCLUDED.summary,
payload = EXCLUDED.payload;
Because the ID is deterministic, re-running the job after a late warehouse correction updates the row rather than duplicating it. That is the one place where we deliberately use DO UPDATE.
Milestones are different: they are detected by comparing against thresholds, such as "first time the account exceeds 80 percent of its seat limit". Emit them once, with an ID that encodes the milestone name, and they become the most useful rows in a renewal conversation.
Reading the Feed#
The feed query has to be fast, stable under concurrent inserts, and filterable. Use keyset pagination on (occurred_at, event_id), never OFFSET.
400 font-semibold">SELECT event_id, occurred_at, source, kind, severity, summary, payload
400 font-semibold">FROM timeline_event
400 font-semibold">WHERE account_id = $1
AND ($2::text[] IS NULL OR source = ANY($2))
AND ($3::timestamptz IS NULL OR (occurred_at, event_id) < ($3, $4))
400 font-semibold">ORDER BY occurred_at DESC, event_id DESC
LIMIT 50;
The composite comparison (occurred_at, event_id) < ($3, $4) is a row-value comparison that PostgreSQL can satisfy from the timeline_event_feed index. With partition pruning on the time range, a typical account page touches one or two partitions.
Expose it through a narrow API that returns an opaque cursor:
400 font-semibold">type Cursor = { t: 400">string; id: 400">string };
400 font-semibold">const encode = (c: Cursor) => Buffer.400 font-semibold">from(JSON.stringify(c)).toString(400 font-semibold">class="text-emerald-300">"base64url");
400 font-semibold">const decode = (s?: 400">string): Cursor | 400">null =>
s ? JSON.parse(Buffer.400 font-semibold">from(s, 400 font-semibold">class="text-emerald-300">"base64url").toString()) : 400">null;
app.get(400 font-semibold">class="text-emerald-300">"/accounts/:id/timeline", requireRole(400 font-semibold">class="text-emerald-300">"support", 400 font-semibold">class="text-emerald-300">"sales", 400 font-semibold">class="text-emerald-300">"csm"), 400 font-semibold">async (req, res) => {
400 font-semibold">const cursor = decode(req.query.cursor as 400">string | 400">undefined);
400 font-semibold">const sources = allowedSources(req.user, req.query.sources as 400">string[] | 400">undefined);
400 font-semibold">const { rows } = 400 font-semibold">await db.query(FEED_SQL, [
req.params.id, sources, cursor?.t ?? 400">null, cursor?.id ?? 400">null,
]);
400 font-semibold">const last = rows[rows.length - 1];
res.json({
events: rows,
nextCursor: rows.length === 50 ? encode({ t: last.occurred_at, id: last.event_id }) : 400">null,
});
});
Note allowedSources. Authorization belongs in the query path, not just the UI: a sales user should not receive support payloads containing transcripts even if the front end hides them.
Ordering, Late Data, and Corrections#
Timelines look simple until time misbehaves. Plan for these cases explicitly.
- Late-arriving events. A webhook retried six hours later carries an old
occurred_at. Because the feed sorts onoccurred_at, the event slots into history, which is correct, but any cached page is now stale. Include a lightweightlatest_ingested_atper account so clients can detect that the feed changed underneath them. - Clock skew in product events. Client-side timestamps drift. Prefer server receipt time for ordering and store the client time in
payload. - Corrections and deletions. If a ticket is deleted for privacy reasons, emit a tombstone event or redact the
summaryandpayloadfields in place. Do not leave personal data in a "history" table because it is append-only. - Timezones. Store UTC, render in the viewer's zone, and group rollup days by the account's timezone.
Privacy, Access, and Retention#
A unified view concentrates sensitive data, so treat access control as a first-class feature.
- Classify by kind. Assign each
kinda sensitivity level in the mapping file and enforce it in the API and with PostgreSQL row-level security for internal tools that query the database directly. - Store summaries, not content. The feed should say "Ticket #48213 escalated", not quote the customer's message. Link to the source system for the detail, where the source's own permissions apply.
- Support erasure. Keep a
contact_idon events so a data subject request can find and redact them. Without it, you are searching free text. - Set retention per kind. Usage rollups might be kept for 24 months, while billing events follow your financial retention policy.
Operational Checklist#
Before calling the timeline production-ready, verify the following.
| Concern | Check |
|---|---|
| Idempotency | Replaying a full day from Kafka produces zero new rows |
| Lag | Alert when now() - max(ingested_at) per source exceeds an agreed threshold |
| Identity | A dashboard counts events routed to the unresolved queue, by source |
| Schema drift | Mapping validation runs in CI against recorded sample payloads |
| Dead letters | Unmappable events go to a DLQ topic with the raw payload, never dropped silently |
| Backfill | A tested job can rebuild the timeline for a date range from raw topics |
| Replication slots | Alert on retained WAL size for CDC slots |
Conclusion#
A unified customer timeline is a small, well-bounded system: a canonical event envelope, a trustworthy identity graph, idempotent ingestion, a partitioned PostgreSQL table, and rollups that keep noisy telemetry out of the feed. The hard problems are not technical novelty. They are identity correctness, honest handling of time, and disciplined access control. Get those right and the same stream powers support triage, renewal prep, and health scoring without three competing integrations.
If you are planning a custom CRM or customer portal and want a second opinion on the event model, identity strategy, or storage design, the knetwork team offers architecture consultations. Bring your current systems and constraints, and we can map out a timeline architecture that fits them.
Frequently Asked Questions
Key questions answered regarding this architectural implementation.
Planning a high-concurrency overhaul or critical migration?
Move from theoretical documentation to production verification. You talk directly with the principal architects who build, benchmark, and deploy these systems—never a business development rep or account manager.
Chat with an Architect
Direct access during working hours. Outline your stack, latency constraints, or bottlenecks for rapid feedback.
Request an Engineering Review
Share your specification, RFP, or architecture diagram. We reply with technical scope and feasibility.
Danisur Rahman
Lead AuthorPrincipal Distributed Systems Architect • KNetwork Systems
Principal architect specializing in enterprise distributed systems, edge caching, and hardware integration pipelines. Leads engineering audits, high-concurrency database optimizations, and zero-trust VPC deployments across high-growth ventures.
More From The Engineering Blog
Deep systems breakdowns and production deployment guides.
Server-Side Event Tracking: Recovering Missing Conversion Data Lost to Ad Blockers
A full-stack guide to server-side event tracking (sGTM): bypass client-side ad-blockers, defeat Safari ITP cookie expiration, slash 1.2MB of bloated tracking scripts, and recover up to 30% of lost conversion revenue.
Self-Healing Webhook Bridges: Syncing Third-Party Vendor Data into Custom Portals Without Loss
Webhooks are at-least-once, unordered and best-effort. Build a durable inbox, version-guarded processor and reconciliation loop that keeps your custom portal converged with vendor data even when deliveries fail.
Enjoyed this technical breakdown?
Subscribe to receive new architectural guides, system teardowns, and engineering benchmarks directly in your inbox.