A Simple Expense Report That Turned Into a Microservices Architecture Challenge

Sai Ram

10 min read

·

2 days ago

--

Listen

Share

Press enter or click to view image in full size

When a simple aggregation wasn’t enough: solving cross-service reporting, zero-expense users, and data synchronization.

While working through the reporting requirements of our expense module, we came across a seemingly straightforward requirement.

We needed to build an expense report for an organization where an administrator could see all its users along with their total expenses.

The report needed to support different filters, such as date ranges and other relevant expense criteria.

There was one additional requirement that made the report slightly more interesting:

Even if a user had never created an expense, that user should still appear in the report with a total expense of zero.

That’s it.

A list of users, their total expenses, and filters.

Simple enough, right?

At first glance, the solution seemed obvious: get the expenses, group them by user, calculate the total, and return the results.

A simple MongoDB aggregation should handle this.

Something like:

• Match expenses based on the organization and filters.
• Group expenses by user ID.
• Calculate the total amount.
• Return the aggregated results.

But there was one detail that changed the entire approach.

Users and expenses were not stored in the same database.

They belonged to two completely different microservices.

And suddenly, our simple aggregation problem became an architectural problem.

The first challenge: Two services, two databases, one report

Our architecture had two separate services:

• User Service: Responsible for user information, organization membership, and user details.
• Expense Service: Responsible for expense records, expense calculations, and reporting.

Each service owned its own database.

This separation made sense from a microservices perspective. Each service controlled its own data and business responsibilities.

But the reporting requirement needed information from both.

We needed the list of users from the User Service and their corresponding expense totals from the Expense Service.

The first approach that came to mind was to start with the expenses.

We could query the Expense Service, aggregate all expense records by user ID, and then request the corresponding user details from the User Service.

For example:

User ID  Total Expense

User A   ₹15,000

User B   ₹8,000

User C   ₹12,000

Then we could fetch the details of those three users and construct the final response.

It looked reasonable.

But then we remembered the actual requirement.

What about users who had never created an expense?

They wouldn’t exist in the aggregation result.

User D, who had spent absolutely nothing, would never appear in the report.

We could not solve a report that required all users by starting with a dataset that contained only users who had expenses.

That was our first dead end.

The second approach: Start with users instead

What if we reversed the process?

Instead of starting with expenses, we could fetch all the users belonging to the organization.

Then, for every user, we could calculate their total expenses.

This would solve the zero-expense problem because we would have the complete user list before calculating the totals.

But another problem appeared.

Imagine an organization with 50,000 users.

Fetching all users and then requesting expense information for each one would be extremely expensive.

We could introduce pagination, but that would not solve the fundamental problem.

We would still need to repeatedly communicate between services to retrieve expense information for each page of users.

Even if we optimized the requests using batch queries, we would still have to manage multiple service calls, large result sets, and additional network overhead.

The report would become increasingly expensive as the organization grew.

We had solved the missing-user problem, but introduced a scalability problem.

The third approach: Can pagination solve this?

We explored another possibility.

What if we first fetched expenses using pagination, then retrieved the corresponding users, and gradually included users who had no expenses?

At first, this sounded like a reasonable compromise.

But think about what happens when the expense records are exhausted.

There could still be thousands of users who never created an expense.

We would have to fetch the remaining users, identify which ones had no expense records, and append them to the report with a total of zero.

Now we had two independently paginated datasets.

One based on expenses.

The other based on users.

Combining them into a single, correctly ordered, consistently paginated report would require additional coordination.

And as the data volume increased, we would have to maintain increasingly complicated filtering and pagination logic.

We were trying to solve a reporting problem by joining two independently owned datasets at runtime.

That was becoming unnecessarily complicated.

We needed a different approach.

The turning point: What if the Expense Service already knew about users?

This led us to a different architectural decision.

Instead of fetching user details from the User Service every time we generated a report, what if we maintained a copy of the basic user information inside the Expense Service?

Not the entire user record.

Just the information required for expense reporting.

For example:

• User ID.
• Organization ID.
• User name.
• Email.
• Relevant reporting attributes.
• Last updated timestamp.

Whenever a user was created, the corresponding basic information would also become available in the Expense Service.

Now our reporting query had everything it needed in one database.

We could start with the users stored in the Expense Service, join them with the expense records, and calculate the totals.

The aggregation would look something like this:

1. Match users belonging to the requested organization.
2. Apply the required user filters.
3. Perform a lookup against the expenses collection using the user ID.
4. Apply expense-specific filters inside the lookup.
5. Calculate the total expense for each user.
6. Return zero when no matching expense records exist.
7. Apply pagination and sorting to the resulting user records.

This was much cleaner.

The Expense Service could now generate the report without repeatedly calling the User Service.

More importantly, users with no expenses would naturally appear because the aggregation started from the user collection rather than the expense collection.

We had effectively moved the complexity from runtime querying into data synchronization.

But that introduced another problem.

What happens when user information changes?

The fourth challenge: Denormalization comes with a price

Imagine an administrator updates a user’s name in the User Service.

The original user record gets updated successfully.

But the Expense Service still contains the old name.

Now the expense report displays stale information.

We had solved the reporting problem, but introduced a data consistency problem.

This is the trade-off that comes with denormalization.

We intentionally store some of the same information in multiple services to make reads faster and simpler.

But once we do that, we become responsible for keeping those copies synchronized.

We discussed two possible approaches.

Approach 1: Scheduled synchronization using updatedAt

The first approach was relatively simple.

Whenever a user record changes, the User Service updates its updatedAt timestamp.

The Expense Service maintains its own synchronization checkpoint.

We could run a background worker every hour.

The worker would:

1. Read the last successful synchronization time.
2. Fetch users from the User Service whose updatedAt is greater than that timestamp.
3. Retrieve the changed user records in batches.
4. Update the corresponding user projections in the Expense Service.
5. Save the new synchronization checkpoint after successful processing.

For example:

Time       Event

9:00 AM    Synchronization completes

9:15 AM    User A updates their name

9:40 AM    User B changes their email

10:00 AM   Next synchronization starts

10:05 AM   Expense Service receives both updates

The advantage was simplicity.

We didn’t need a messaging infrastructure or additional event-processing components.

The User Service remained the source of truth, and the Expense Service periodically refreshed its local copy.

But there was an obvious trade-off.

If a user updated their information at 10:01 AM, the Expense Service might not reflect that change until the next synchronization cycle.

In our example, that could mean waiting almost an hour.

For reporting, this might be acceptable.

But we also needed to consider reliability.

What if the worker failed halfway through processing?

What if the service restarted?

What if records were updated while the worker was fetching the next batch?

We would need reliable checkpoints, pagination, retries, and a safe synchronization window.

A simple updatedAt > lastRunTime query is not enough by itself. We would also need to account for overlapping execution windows, timestamp precision, and failures so that updates are not silently skipped.

The approach was manageable, but it came with a synchronization delay.

Approach 2: Event-driven synchronization

Then we considered another approach.

What if we didn’t wait for the next scheduled synchronization?

What if the User Service notified the Expense Service immediately whenever user information changed?

That led us to event-driven architecture.

The flow would look like this:

1. An administrator updates a user in the User Service.
2. The User Service saves the updated record.
3. A user-updated event is published to a message queue.
4. The Expense Service consumes that event.
5. The Expense Service updates its local user projection.

For example, whenever a user’s name or email changes, the User Service could publish an event containing:

• Event ID.
• User ID.
• Organization ID.
• Updated user information.
• Event version or update timestamp.

The Expense Service would consume the event and update its corresponding record.

Now, instead of waiting for an hourly synchronization, the Expense Service could receive the update within a few seconds under normal operating conditions.

That was much closer to what we wanted.

But event-driven architecture introduced its own set of challenges.

And this time, the complexity was not in querying data.

It was in making sure that events were processed reliably.

The hidden problems behind event-driven architecture

At first, publishing an event and consuming it sounded simple.

But distributed systems rarely fail in the way we expect them to.

Consider a few scenarios.

1. What if the same event arrives twice?

Message queues can deliver the same message more than once.

If the Expense Service processes the same user update twice, we should not create duplicate records or trigger unnecessary side effects.

We needed idempotent event processing.

Using the user ID and event version, or a processed-event identity, we could ensure that replaying an event did not produce an incorrect result.

2. What if the User Service updates the database but fails to publish the event?

This is one of the more dangerous failure scenarios.

Imagine the user update succeeds, but the service crashes before publishing the event.

The User Service now contains the latest information, but the Expense Service never receives the update.

A reliable solution would be the transactional outbox pattern.

The User Service writes both the user update and an outbox event within the same database transaction.

A background publisher then reads pending outbox records and publishes them to the message queue.

Once publishing is confirmed, the outbox record can be marked as processed.

This prevents the database update and event publication from becoming two unrelated operations that can fail independently.

3. What if the Expense Service is unavailable?

Suppose the message queue receives an event, but the Expense Service is temporarily down.

We cannot simply discard the event.

The queue must retain it until the consumer becomes available.

We also need retry policies and dead-letter handling for messages that repeatedly fail.

4. What if events arrive out of order?

Imagine a user changes their name twice.

The first event contains the old updated name, and the second event contains the new one.

If the second event is processed first and the first event arrives afterward, a naive update could overwrite the latest name with an older value.

We need to use event versions or another reliable ordering mechanism so that older updates cannot overwrite newer ones.

These were not reasons to avoid event-driven architecture.

They were the engineering responsibilities that came with choosing it.

So which approach should we choose?

At this point, we had two reasonable approaches.

Both solved the same fundamental problem: keeping the Expense Service’s user projection synchronized with the User Service.

But they offered different consistency guarantees.

Consideration           Scheduled sync              Event-driven

Update latency          Up to the sync interval     Usually seconds

Infrastructure          Background worker           Message queue and consumer

Implementation          Relatively simple           More complex

Failure handling        Checkpoints and retries     Retries, outbox, dead-letter handling

Duplicate handling      Safe repeated upserts       Idempotent event processing

Event ordering          Less relevant               Must be handled

Operational complexity  Lower                       Higher

If an expense report could tolerate user information being delayed by an hour, scheduled synchronization could be a perfectly reasonable choice.

If user changes needed to appear almost immediately, event-driven synchronization would be the better fit.

We should not introduce a message broker simply because event-driven architecture sounds sophisticated.

Likewise, we should not choose hourly synchronization if the business expects near-real-time information.

The architecture should follow the consistency requirements, not the other way around.

What started as a simple aggregation became a data architecture decision

Looking back, the original requirement was quite small:

Show every user in an organization, calculate their total expenses, and display zero for users without expenses.

But the actual problem involved much more than aggregation.

We had to think about:

1. Cross-service data access: How to combine information owned by independent microservices.
2. Reporting architecture: How to support users with and without expense records.
3. Denormalization: Why storing a read-optimized copy of user information can simplify reporting.
4. Data consistency: How to prevent stale user information from appearing in reports.
5. Scheduled synchronization: How to propagate changes using timestamps, checkpoints, and background workers.
6. Event-driven architecture: How to propagate changes through asynchronous messaging.
7. Reliability: How to handle duplicate events, failed deliveries, retries, and out-of-order updates.

The biggest lesson I took away from this implementation was:

Microservices make data ownership cleaner, but they don’t make cross-service reporting easier.

Sometimes, the right solution is not to join everything at runtime.

Sometimes, maintaining a carefully designed local projection of another service’s data is a much better engineering decision.

And once we make that decision, synchronization becomes part of the architecture, not an afterthought.

What looked like a simple aggregation problem eventually became a discussion about distributed data, eventual consistency, and the trade-offs between scheduled and event-driven systems.

That’s the interesting part of backend engineering.

The difficult problems don’t always come from complicated business requirements.

Sometimes, they come from trying to answer a very simple question with data that lives in completely different places.