Custom CRM SolutionsUnified Customer Timelines: Combining Support Tickets, Purchases, and Product Usage into One Feed

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.

D

Danisur Rahman

Verified
Principal Distributed Systems Architect•Oct 9, 2026•14 min read
Unified Customer Timelines: Combining Support Tickets, Purchases, and Product Usage into One 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_at and updated_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.

FieldPurposeExample
event_idDeterministic, source-derived ID for idempotencyzendesk:ticket:48213:status:solved:1760001000
account_idInternal tenant/company the event belongs toacc_01HV...
contact_idOptional person within the accountcon_01HV...
occurred_atWhen it happened in the real world2026-10-06T14:02:11Z
ingested_atWhen the timeline learned about it2026-10-06T14:02:19Z
sourceSystem of originzendesk, stripe, product
kindNamespaced event typesupport.ticket.escalated
severityWeight for UI and health scoringinfo, notable, critical
summaryPre-rendered one-line descriptionTicket #48213 escalated to Tier 2
payloadSource-specific details as JSONB{"priority":"high"}
Two timestamps are non-negotiable. 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.

yaml
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.

sql
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:

  1. Prefer explicit links. Write your internal account_id into Stripe customer metadata and the helpdesk organization's external ID at provisioning time. Deterministic beats inferred.
  2. 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.
  3. Make links temporal. Accounts merge, contacts change companies, and subscriptions move between workspaces. With valid_from and valid_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.

sh
 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:

json
{
  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.

typescript
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:

sql
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#

sql
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:

TierWhere it livesEnters the feed?
Raw events (clicks, page views)Analytics warehouse or ClickHouseNo
Daily and weekly rollups per accounttimeline_event as usage.daily_summaryYes, collapsed by default
Milestones (first API call, feature adopted, seat limit hit)timeline_eventYes, prominent
A rollup job aggregates from the warehouse and writes one deterministic row per account per day:

sql
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.

sql
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:

typescript
400 font-semibold">type Cursor = { t: 400">string; id: 400">string };

400 font-semibold">const encode = (c: Cursor) =&gt; 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 =&gt;
  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) =&gt; {
  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 on occurred_at, the event slots into history, which is correct, but any cached page is now stale. Include a lightweight latest_ingested_at per 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 summary and payload fields 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.

  1. Classify by kind. Assign each kind a 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.
  2. 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.
  3. Support erasure. Keep a contact_id on events so a data subject request can find and redact them. Without it, you are searching free text.
  4. 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.

ConcernCheck
IdempotencyReplaying a full day from Kafka produces zero new rows
LagAlert when now() - max(ingested_at) per source exceeds an agreed threshold
IdentityA dashboard counts events routed to the unresolved queue, by source
Schema driftMapping validation runs in CI against recorded sample payloads
Dead lettersUnmappable events go to a DLQ topic with the raw payload, never dropped silently
BackfillA tested job can rebuild the timeline for a date range from raw topics
Replication slotsAlert on retained WAL size for CDC slots
The backfill requirement deserves emphasis. Your mapping file will change. If the timeline is derived from retained raw topics (or an object-store archive of them), you can fix a bug in the mapper and rebuild. If it is the only copy of the data, every mapping mistake is permanent.

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.

Senior Systems Architecture Advisory
Available for Q4 Advisory & Audits

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.

Direct WhatsApp ChannelFastest

Chat with an Architect

Direct access during working hours. Outline your stack, latency constraints, or bottlenecks for rapid feedback.

Open WhatsApp Discussion
Formal Architecture Brief1-Day SLA

Request an Engineering Review

Share your specification, RFP, or architecture diagram. We reply with technical scope and feasibility.

Mutual NDA Protected1 Working Day Response100% IP & Code Ownership
Explore All Contact Channels
D

Danisur Rahman

Lead Author

Principal Distributed Systems Architect • KNetwork Systems

Request Technical Review

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.

Distributed BackendsEvent StreamingPrivate RAGIoT Telemetry
The Engineering Dispatch

Enjoyed this technical breakdown?

Subscribe to receive new architectural guides, system teardowns, and engineering benchmarks directly in your inbox.