How We Cut 90%+ of Database Queries from a Single Mutation

Release v0.1.2 · Performance · Database

---

The Problem: An N+1 Query in changelog.markRead

As we scale the platform, one of the most important things we can do is catch inefficient database access patterns early — before they become production pain points.

In v0.1.2 we fixed a classic N+1 query problem hiding inside the changelog.markRead tRPC mutation.

What was happening

Every time a user marked their changelog as read, the server was doing the following:

1. Fetch all changelog entries — one SELECT query.
2. Loop over every entry and, for each one:
   • SELECT to check whether a read-record already exists for this user + entry.
   • INSERT the read-record if it didn't exist.

In code terms, this looked roughly like:

// ❌ Before — N+1 pattern
const entries = await db.select().from(changelogEntries);

for (const entry of entries) {
  const existing = await db
    .select()
    .from(changelogReads)
    .where(
      and(eq(changelogReads.userId, userId), eq(changelogReads.entryId, entry.id))
    );

  if (!existing.length) {
    await db.insert(changelogReads).values({ userId, entryId: entry.id });
  }
}

For a changelog with N entries, this is 1 + 2N database round-trips over HTTP to Neon. With 10 entries: 21 queries. With 100 entries: 201 queries — and the time cost scales with every new release note we publish.

---

The Fix: Bulk Upsert with ON CONFLICT DO NOTHING

The solution is to remove the loop entirely and let the database do the heavy lifting with a single bulk insert:

// ✅ After — 2 queries total, regardless of N
const entries = await db.select().from(changelogEntries);

await db
  .insert(changelogReads)
  .values(entries.map((entry) => ({ userId, entryId: entry.id })))
  .onConflictDoNothing();

This works because changelog_reads already has a unique constraint on (user_id, entry_id). When we attempt to insert a record that already exists, the database simply skips it — no error, no duplicate data. The existing constraint doubles as the conflict target, so no schema changes were needed.

Total queries: 2 — one to fetch entries, one to bulk-upsert reads.

---

Impact at a Glance

Changelog entries	Queries before	Queries after	Reduction
5	11	2	82%
10	21	2	90%
50	101	2	98%
100	201	2	99%

Because Neon runs over HTTP (each query is an independent request with connection overhead), the latency savings compound quickly. What might have been 200+ ms for a large changelog now completes in a handful of milliseconds.

---

Why This Matters for the Platform

This fix is small in diff size but meaningful in principle. As we add more changelog entries over time — and as more users simultaneously trigger markRead on login or first visit — an unbounded query loop would have degraded quietly in the background.

Catching N+1 patterns early, before they become incidents, is part of how we keep the platform fast and reliable for landlords, agents, and tenants who depend on it.

---

Files Changed

• src/lib/routers/changelog.ts

---

Released in v0.1.2.