Replacing a Per-Pageload Database Query With a Build-Time Snapshot

Every visitor was fetching the same table. Moving that read to build time made it a CDN problem instead of a database problem, and cost nothing downstream.

BY AAYUSH KUMAR  ·  Architecture  ·  9 MIN READ

The shape of the original problem

The arthis.land homepage is a wall of bricks — hundreds of DOM nodes panned and zoomed at 60fps, each brick carrying a label, a colour, a size and sometimes a thumbnail. That metadata lives in a Supabase table called bricks. The original data flow was the obvious one: the page boots, the Supabase client loads, it selects the table, React re-renders with real content.

It worked. It also meant that every single visitor executed the same query and received the same rows. Nothing about that response was personal, nothing about it was permission-scoped, and nothing about it changed between one visitor and the next. The table serialises to roughly 200–300 kB gzipped. That is the number that matters, because it is paid per page load.

Run the arithmetic at the traffic the site is built to survive. At one million page views a day, 200–300 kB gz each, you are looking at something like 7.5 TB of Supabase egress per month and around two million API calls a day — and every one of those calls is a database asking itself the same question and getting the same answer. There is no cache tier in that path that helps, because PostgREST responses are not something a CDN edge will hold onto by default, and the query is issued from JavaScript against an API origin rather than from the document.

The fix is not an optimisation of the query. The query is fine. The problem is that the read is happening at the wrong time.

Move the read to build time

The deploy pipeline already regenerates files before bundling — rooms.config.js is scanned out of the room folders on disk. Adding one more generator was cheap. During the build, a script queries Supabase once, writes the rows verbatim to bricks.snapshot.json, hashes the bytes, and emits a tiny generated bricks.config.js holding the fingerprinted URL:

// bricks.config.js — generated, do not edit
window.ARTHIS_BRICKS_SNAPSHOT = '/bricks.snapshot.json?v=8f3c1a9e';

The fingerprint is a hash of the snapshot's own bytes, so new data means a new URL. That single property is what unlocks everything else: the file can be served immutable with a year-long max-age, because a changed file is never the same URL. A CDN edge answers it without touching the origin, the origin never touches the database, and the database never learns the page was loaded. The cache headers that make this work are the other half of the trick; a fingerprinted file served with revalidation headers is just a slower version of the original problem.

The knock-on saving is larger than the payload. The Supabase client is about 55 kB gzipped, loaded through a dynamic import specifically to keep it off the critical path. When the snapshot answers, that import never fires at all. The client is not deferred, it is simply never fetched. On the normal path the wall now costs one static JSON file and nothing else.

The rule that makes it safe: identical shapes

The design decision that carries the whole pattern is this: both sources return exactly the same shape — whatever PostgREST would have returned, field for field, type for type, in the same array of the same row objects. The snapshot is not a summary, not a trimmed projection, not a "lite" format. It is a copy of the response.

That constraint sounds like a missed optimisation. It is the opposite. Because the shapes are identical, nothing downstream can tell which source answered. The enrichment code does not branch. The validation layer does not branch. React does not know the difference. Which means the snapshot is safe to add — it cannot introduce a shape bug, because there is no second shape — and, more importantly, it is safe to delete. Rip the snapshot out tomorrow and the live query path is still there, still correct, still exercised by the fallback. A performance layer you can remove without an archaeology session is a performance layer you can actually maintain.

The fallback chain is short on purpose:

async function loadBricks() {
  // Snapshot first: an immutable URL an edge can answer without
  // waking the origin. A 404 here is normal on a fresh checkout.
  try {
    const res = await fetch(window.ARTHIS_BRICKS_SNAPSHOT, { cache: 'force-cache' });
    if (res.ok) {
      const rows = await res.json();
      if (Array.isArray(rows) && rows.length) return rows;
    }
  } catch (_) {
    // Swallowed deliberately: a missing or malformed snapshot is a
    // reason to ask the database, not a reason to break the wall.
  }

  // Live query. Identical shape by contract - if these two ever
  // diverge, the bug is here, not downstream.
  const supabase = await window.getSupabase();   // ~55 kB gz, only now
  const { data, error } = await supabase.from('bricks').select('*');
  if (error) return [];
  return data || [];
}

Note what is not in that function: validation. It returns raw rows from either source and hands them on. That is deliberate, and it is the next section.

The snapshot is untrusted input, because it is a copy of untrusted input

There is a seductive argument that a file you generated yourself during your own build, from your own database, is trusted. It is wrong, and the reason is worth stating precisely.

Row Level Security decides who may write. It does not decide what shape the bytes are. Those are different problems and only one of them has a policy engine behind it.

RLS will stop a stranger inserting a row. It will not stop a legitimate writer — including you, at 2am, through the dashboard — putting a null in a numeric column, a size_w of 900000, or a javascript: URL in an image field. All three arrive as perfectly valid JSON. A 900000-unit brick is 81 million pixels wide and will take the layout with it. A javascript: URL assigned to src is a script execution primitive handed over by your own build output. And in a front end with no error boundary between the data layer and the tree, a single throw in that path does not degrade the page — it reaches React as a blank white screen.

So every field, from either source, runs the same gauntlet: safeNum, safeText, safeImageUrl, safeHex. The important behaviour is what they do on failure:

// Bad rows are DROPPED, not defaulted. A brick with a broken
// size is a missing brick; a brick silently coerced to 1x1 is a
// bug you will be hunting in three months with no evidence left.
function safeNum(v, min, max) {
  const n = typeof v === 'number' ? v : parseFloat(v);
  if (!Number.isFinite(n) || n < min || n > max) return null;
  return n;
}

function safeImageUrl(v) {
  if (typeof v !== 'string' || v.length > 512) return null;
  // Allow-list, never a deny-list: javascript:, data:, vbscript:
  // and whatever is invented next all fail the same test.
  if (!/^(https:\/\/|\/)[^\s"'<>]+$/.test(v)) return null;
  return v;
}

Dropping rather than defaulting is the part people argue with. Defaulting feels friendlier. It is not: a defaulted row looks correct, renders, and hides the fact that the data is wrong. A dropped row leaves a visible hole and no misinformation. Given a wall with hundreds of bricks, one missing brick is a bug report; hundreds of quietly coerced ones are a mystery.

The snapshot generator, correspondingly, validates nothing. It is a dumb pipe from PostgREST to disk. Any validation it performed would be a second, drifting copy of the rules — and the one thing worse than no validation is two sets of it that disagree. There is exactly one place that decides what a valid brick is, it lives in brick-data.js, and it runs in the browser where the render happens.

A failed snapshot must not fail the build

The build calls the network. Networks fail. The correct behaviour when Supabase is unreachable during a deploy is to log loudly, keep the previous bricks.snapshot.json, and exit zero.

The reasoning is a comparison of outcomes, not a preference. If the snapshot step fails the build, a Supabase blip blocks every deploy — including the deploy that fixes something unrelated and urgent. If it succeeds with stale data, visitors get a wall that is a few hours out of date, and any brick that changed in the meantime is still correct on the live-query path for anyone whose snapshot 404s. A stale wall beats a blocked deploy.

What the build log must say is the difference between that being a decision and being an accident. It should print the row count, the byte size, the fingerprint, and — when it falls back — an unmistakable line saying the snapshot was not regenerated, with the age of the file it kept. Silence here is how a build quietly ships six-week-old data for a month.

Before and after

The staleness bargain, stated honestly

You are trading freshness for egress, and it is worth naming who pays. Visitors see the wall as it was at the last deploy. For a city of bricks that changes when its author adds a room, that is fine — the deploy is the change event. For the person who edited a brick label and wants to see it live, it is not fine at all: they must deploy, or hard-refresh past a fingerprint that has not moved. That person is usually me, which makes it a tolerable cost and a genuinely annoying one.

The mitigation is not a cache-busting hack. It is that the live path never went away. Delete the snapshot file and every visitor is back on the database within one page load, at full freshness, with the same shape and the same validation. The snapshot is a cache you can throw in the bin.

None of this affects first paint, which is worth being clear about: the wall is drawn from a procedural build before any of this runs. Enrichment is a second pass on a page that is already interactive — see painting before the network answers for why that ordering matters more than the payload size does, and the wall this data feeds for what the rendering layer is actually doing with these rows.

When this pattern is wrong

This works because the data is public, identical for everyone, and changes on a schedule you control. Remove any one of those and it stops working.

• Per-user data. A snapshot is one file for all visitors. The moment the response depends on who is asking, you are either building a snapshot per user — which is a database with worse ergonomics — or leaking one user's rows to everyone. RLS was doing real work in that case; a static file has no policy engine.
• Anything that must be live. Stock levels, seat availability, scores, prices, presence. The staleness window here is "until the next deploy", which is unbounded. If a wrong value has a cost, do not snapshot it.
• Write-heavy surfaces. If the page writes back, the snapshot is immediately inconsistent with the thing the user just did, and you will end up patching the snapshot in memory to paper over it — at which point you have two sources of truth and a merge problem you did not have before.
• Data that outgrows a single file. 200–300 kB gz is a file. 50 MB is not; that wants pagination, an index, or a real query, and shipping it as one blob turns a bandwidth problem into a parse-time problem on a phone.

The test is simple enough to apply in a minute: if every visitor would receive byte-identical bytes, and you can name the moment the data changes, the read belongs at build time. If either half of that is false, leave it in the database and spend the effort on the query instead.