Faster Customer Engagement Timelines with a Targeted Database Index

Release: v1.0.11
Category: Performance / Database

What Changed

Version 1.0.11 ships a targeted database performance fix for the CRM module. A composite index has been added to the customer_engagement_events table:

CREATE INDEX ON customer_engagement_events (customer_id, occurred_at);

This index directly supports the listEngagementEvents query, which retrieves a customer's engagement history ordered by most recent event first (ORDER BY occurred_at DESC).

Why This Matters

Before this release, the CRM schema had no indexes at all. Every call to listEngagementEvents performed a sequential scan across the entire customer_engagement_events table, then sorted the results. As event data accumulates — across customers, tenants, and automated engagement actions — this becomes an increasingly expensive operation.

With the new composite index, the database engine can:

1. Seek directly to the rows belonging to a specific customer_id.
2. Read them in occurred_at order without a separate sort step, since the index stores values in that order.

The result is a query that scales with the number of events per customer, not the total number of events in the system.

Affected Query Pattern

-- Pattern targeted by this index
SELECT *
FROM customer_engagement_events
WHERE customer_id = :customer_id
ORDER BY occurred_at DESC
LIMIT :limit;

Who Benefits

• All CRM users viewing customer engagement timelines.
• High-volume tenants with large event histories will see the most pronounced improvement.
• Automated agents (health scoring, churn prediction, proactive engagement) that read engagement history as part of their processing loop.

Deployment Notes

• This is a schema migration — the index is created automatically on deploy.
• No application code changes. No breaking changes.
• Index creation on large existing tables may take a brief moment on first deploy; it does not lock the table on modern Postgres versions (built using CREATE INDEX CONCURRENTLY semantics where applicable).