Merging multiple sources of truth safely

A lot of systems answer questions that don’t have one single source of truth. A search result has to reconcile a live index with whatever changed since the last crawl. A read replica has to be treated as “probably current, but verify for anything that matters.” A materialized view is a cached answer to a question the underlying tables could re-answer more slowly but more correctly. In every one of these, the system is combining data that updates at different speeds and can disagree with each other at any given moment, and the correctness of the whole system depends on getting the merge right, not on any one source being individually correct.

The failure mode is always the same shape: two sources are each reasonable on their own, but nobody designed for what happens when they disagree, so the system silently trusts whichever one happened to run last, or whichever one is checked first in the code. That’s not a bug in either source. It’s a missing decision about priority and staleness that the merge logic needed to make explicit and didn’t.

A concrete version: is this time slot open

A scheduling system answering “is this person free at this time” is a small, self-contained example of the same problem. There’s no single table that holds the answer. There are at least three sources, and they update at genuinely different rates:

In SmartCal’s calculateAvailability() (src/lib/availability/calculate-availability.ts), all three get fetched in parallel rather than one at a time:

const [eventType, connectedCalendars, cachedBlocks, hostUser] = await Promise.all([
  prisma.eventType.findUnique({
    where: { id: eventTypeId },
    include: {
      availabilityRule: true,
      bookings: {
        where: {
          status: { in: ['CONFIRMED', 'PENDING'] },
          startsAt: { lt: timeRange.end },
          endsAt: { gt: timeRange.start },
        },
      },
    },
  }),
  prisma.connectedCalendar.findMany({ where: { userId, provider: 'GOOGLE', syncEnabled: true } }),
  prisma.calendarBusyBlock.findMany({
    where: {
      userId,
      startsAt: { lt: timeRange.end },
      endsAt: { gt: timeRange.start },
      OR: [{ expiresAt: null }, { expiresAt: { gte: new Date() } }],
    },
  }),
  prisma.user.findUnique({ where: { id: userId } }),
]);

Fetching everything at once is a performance choice, not a correctness one, and it’s worth separating the two. Correctness here depends entirely on what happens after the fetch: which source wins when two of them disagree about the same window of time, and what happens when one of the sources can’t be trusted right now.

Live data doesn’t replace the cache, it supplements it

The live calendar lookup, a real network call to an external calendar provider, happens in a second step, one request per connected calendar, using Promise.allSettled instead of Promise.all:

const calendarResults = await Promise.allSettled(
  connectedCalendars.map((calendar) =>
    getFreeBusy(userId, calendar.id, timeRange.start, timeRange.end)
      .then((freeBusy) => ({ calendarId: calendar.id, busy: freeBusy.busy }))
  )
);

for (const result of calendarResults) {
  if (result.status === 'fulfilled') {
    for (const busy of result.value.busy) {
      busyBlocks.push({ id: `${result.value.calendarId}-${busy.start}`, startsAt: new Date(busy.start), endsAt: new Date(busy.end), isTransparent: false });
    }
  }
}

allSettled is the right choice here for availability: a slow or failing external calendar shouldn’t take down the whole check, it should just mean that one calendar’s live data doesn’t make it into this particular answer. But the cached blocks are pushed onto the same busyBlocks list unconditionally, regardless of whether the live fetch for that same calendar succeeded:

for (const block of cachedBlocks) {
  busyBlocks.push({ id: block.id, startsAt: block.startsAt, endsAt: block.endsAt, isTransparent: block.isTransparent });
}

That ordering is what makes the design actually safe. The cache isn’t a fallback that only gets consulted when the live call fails, it’s always in the answer, and the live call adds to it rather than replacing it. If the network call succeeds, the availability check has both the cache and fresher data layered on top. If the network call fails, the cache alone still bounds how wrong the answer can be, by however stale the cache is allowed to get before its TTL expires. The general pattern worth naming: when a live source can fail but a cached source can’t (a database read essentially always succeeds), don’t gate the cached source’s inclusion on the live source’s success. Merge them additively, and let the live source only ever add information, never gate access to the cache.

Why buffers have to apply uniformly across sources

Once busy time from all three sources lands in one list, the interval check treats them uniformly, applying the same before/after buffer to a booking made directly in the system and to a block synced from an external calendar:

const bufferBefore = (rule?.bufferBefore ?? eventType.bufferTime ?? 0) * 60 * 1000;
const bufferAfter = (rule?.bufferAfter ?? eventType.bufferTime ?? 0) * 60 * 1000;

for (const booking of existingBookings) {
  const bufferedStart = booking.startsAt.getTime() - bufferBefore;
  const bufferedEnd = booking.endsAt.getTime() + bufferAfter;
  if (intervalStart < bufferedEnd && intervalEnd > bufferedStart) return false;
}

for (const busyBlock of busyBlocks) {
  if (busyBlock.isTransparent) continue;
  const busyStart = busyBlock.startsAt.getTime() - bufferBefore;
  const busyEnd = busyBlock.endsAt.getTime() + bufferAfter;
  if (intervalStart < busyEnd && intervalEnd > busyStart) return false;
}

This is the part of a multi-source merge that’s easy to get subtly wrong: applying a business rule (the buffer) to one source of busy time and forgetting the other, because the two sources arrived through different code paths and different data models. A host configuring a 15-minute buffer wants breathing room around any meeting, regardless of which system it was booked through. A merge that buffers only its own bookings and not the synced ones isn’t half-implementing the buffer feature, it’s implementing a different feature than the one that was asked for, and the gap between those two only shows up when someone’s calendar has a meeting from the other source sitting right at the edge of a slot.

The general shape of the problem

Everywhere a system caches part of its own truth, whether that’s a search index behind a live database, a materialized view behind live tables, or a read replica behind a primary, the same three questions decide whether the merge is safe:

1. When two sources disagree about the same fact, which one wins, and is that decision made once in the merge logic or implicitly wherever the data happens to get read?
2. Does a cached source get treated as a real answer, or only as a fallback when the live source fails? Getting that backwards either discards a live signal for no reason or leaves the system exposed the moment the live source has a bad day.
3. Do rules that are supposed to apply globally (buffers, permissions, filters) actually get applied to every source uniformly, or only to whichever source the rule was written against first?

None of these questions has a universally correct answer. A system might legitimately prefer live data over cache, or cache over live data, depending on what it’s protecting against. What isn’t optional is making the choice explicit and applying it consistently across every source that can produce an answer to the same question. The alternative isn’t a crash or a visible error, it’s a plausible-looking answer that’s wrong in whatever way the unexamined priority order happened to produce, which is exactly the kind of bug that survives testing and shows up in production instead.