A customer has Gold membership and three completed purchases. That's enough to qualify for a rewards offer.
Then one of those purchases is refunded.
The business decision has changed. But what happens to the data that powers the Rewards API—and the AI agent relying on that API?
This is a useful example of a broader data architecture challenge: How do we keep operational read models current as source data changes?
The architectural pattern
Consider a simple flow:
PostgreSQL → Change Data Capture → Incremental SQL → Maintained Eligibility View → Rewards API → AI Agent
Here's what happens:
1. The source changes. A refund is recorded in PostgreSQL, changing the business state that determines customer eligibility.
2. CDC captures the change. Rather than repeatedly reading all purchase and membership records, the system consumes changes from PostgreSQL's logical replication stream.
3. Incremental SQL maintains the result. An engine such as Materialize updates the affected eligibility calculation as source changes are processed.
For example, consider an illustrative schema with orders, memberships, and refunds. Assume each customer has one membership record and each refunded order has one refund record.
CREATE MATERIALIZED VIEW customer_eligibility AS SELECT m.customer_id, m.status AS membership_status, COUNT(o.order_id) FILTER ( WHERE o.status = 'COMPLETED' AND r.order_id IS NULL ) AS completed_purchases, ( m.status = 'GOLD' AND COUNT(o.order_id) FILTER ( WHERE o.status = 'COMPLETED' AND r.order_id IS NULL ) >= 3 ) AS eligible FROM memberships m LEFT JOIN orders o ON o.customer_id = m.customer_id LEFT JOIN refunds r ON r.order_id = o.order_id GROUP BY m.customer_id, m.status; When a refund record arrives, that order no longer contributes to the completed-purchase count. The count falls from three to two, changing eligibility from true to false.
The important idea is that the query declares the desired business result, while the incremental computation engine maintains that result as inputs change.
4. The API reads the maintained view. The Rewards API queries the eligibility view served by Materialize, rather than recalculating the entire result from the operational tables on each request.
5. The AI agent uses the API. When the agent requests eligibility through an authorized API tool, it receives the latest state currently available through that API.
This is an illustrative design—not a claim that every source change becomes instantly visible. The end-to-end delay must still be measured.
What matters in production?
Three questions deserve attention.
1. Can we trust the change stream?
CDC must reliably capture updates and deletes and recover from interruptions. PostgreSQL replication lag, WAL retention, and recovery behavior need operational monitoring.
2. How fresh is the result?
Define a freshness target and measure the delay between a source change and the API-visible result. If the system cannot establish a trustworthy answer, it should not silently turn “unavailable” into false.
3. Who owns the final business decision?
A continuously maintained eligibility view supports fast reads. But if redeeming a reward commits financial value, the authoritative rewards service should still validate eligibility transactionally.
These questions matter more than simply getting the first SQL query to work.
The transferable pattern
The bigger architectural lesson goes beyond PostgreSQL or Materialize:
Change Data Capture → Incremental Computation → Continuously Maintained Read Model
This pattern can support many changing business contexts:
- Pricing and promotions
- Inventory availability
- Customer status and entitlements
- Operational information exposed to AI agents
Batch processing, custom streaming, and continuously maintained read models all have legitimate uses. The right choice depends on freshness requirements, business complexity, cost, recovery behavior, and decision ownership.
The real goal is not to adopt another technology. It's to make trustworthy business information available where decisions are made.
For a more detailed walkthrough, read my Clinic #01 Deep Dive on Beyond the Blueprint.
This is an educational architecture example, not a production benchmark. The SQL uses an illustrative schema and has not been presented as a tested deployment script.