Stop querying live: stored results and a nightly sync job that can't hurt anyone

The problem

After adding connection pools and a per-server budget (see "One pool per database, one budget per server"), the data source service no longer flooded the shared database. Batches waited in our own queue instead of being refused. The random nulls were gone.

But something still felt wrong. Every time a configurator was cached, the service still opened connections to a shared server that belongs to ten other applications, to run queries whose answers almost never change: Yes/No lists, material tables, finish options. We had made the problem smaller, not made it disappear.

Two questions changed the design:

1. How often does this data actually change? Rarely. Some sources change a few times a year.
2. Who should pay the cost of fetching it? Not the user waiting for a page.

If the data barely changes, the user's request has no reason to touch the external server at all.

The details

From "fetch on read" to "store and serve"

The new model:

• Each source's last good result is stored on its own row, in our own database.
• Requests are served from the stored result, with zero connections to the external server.
• A background job refreshes stored results, slowly, on a schedule nobody waits for.
• When a source is edited, its result is refreshed right away, so users see their change immediately.

This is a classic cache-at-rest pattern, but the hard part isn't storing a value. It's everything around it: what happens when a refresh fails, when two refreshes start together, when the server restarts mid-job, or when someone keeps clicking "refresh all".

New columns, all additive

cachedResult      Json?       -- the last good result
cachedResultAt    DateTime?   -- when it was stored
cachedResultHash  String?     -- fingerprint, to detect real changes
isBroken          Boolean  @default(false)
brokenSince       DateTime?   -- date of the FIRST failure in a row
lastErrorCode     String?     -- coarse label only, e.g. QUERY_FAILED

Every column is nullable or has a default, so adding them to a table with 177 existing rows is safe. Plus a small history table, source_syncs, recording each run: status, trigger (cron, manual or edit), codes, progress and per-code results.

The challenges

A failed refresh must never destroy good data

The nightly job runs at midnight. If the external server is full at that moment, a refresh fails. If a failure overwrote the stored result, one bad minute would wipe out good data, and we'd be back to empty dropdowns.

The column starts empty

On the first deploy, no source has a stored result yet. Serving "nothing stored" as null would recreate the original bug on day one.

Background jobs die with the process

The sync runs inside the application process. Every deploy restarts that process. A job killed mid-run leaves a row saying RUNNING forever.

Concurrency, again

What if two people click "refresh all" at the same time? Or the nightly cron fires while a manual run is still going? Two full runs in parallel would double the load on the shared server, exactly what we're trying to avoid.

Public endpoints and impatient users

The manual refresh is reachable by many users. Without a limit, anyone could make the service re-query every source again and again.

The solution

1. Serving: stored first, live only as a fallback

async resolve(code: string, record: Source | null, live = false) {
  if (!record) return failure('NOT_FOUND');

  if (isHttpEndpoint(record)) return this.fetchEndpointLive(record); // never stored

  if (!live && record.cachedResultAt) {
    return ok(record.cachedResult);           // the normal path: no external connection
  }

  // Nothing stored yet, or a forced refresh (?live=true)
  const { item, data } = await this.refreshOne.refresh(record); // stores on the way
  return item.ok ? ok(data) : failure(item.error ?? 'QUERY_FAILED');
}

• Normal path: read the stored result. For a batch of 23 codes, that's one query on our own database to load all 23 rows.
• First fill: if nothing is stored yet, the source runs live once (through the throttled pool) and the result is stored. So the first deploy is safe before the first nightly run.
• Forced refresh: ?live=true re-runs the query and stores the new result. It's useful for debugging.

Measured: a batch served from storage takes about 0.17 seconds and opens zero connections to the shared server.

2. Refreshing one source: never overwrite good data

async refresh(record: Source) {
  const outcome = await this.runLive.execute(record); // pool + retry on transient errors

  if (outcome.ok) {
    const hash = stableHash(outcome.data);
    await this.repo.storeResult(record.id, { data: outcome.data, hash, at: new Date() });
    // also clears isBroken, brokenSince and lastErrorCode
    return { ok: true, changed: hash !== record.cachedResultHash, data: outcome.data };
  }

  // Failure: KEEP the old cachedResult, only flag the row
  await this.repo.markBroken(record.id, {
    errorCode: outcome.label,
    since: record.brokenSince ?? new Date(), // keep the date of the FIRST failure
  });
  return { ok: false, changed: false, error: outcome.label, data: null };
}

This is the most important rule of the whole design:

A failed refresh never overwrites the stored result. It only marks the source as broken.

Users keep seeing the last good data. Meanwhile:

• isBroken = true makes the problem visible (there's a ?broken=true filter on the source list),
• brokenSince keeps the date of the first failure, so "failing since October 3rd" stays visible across many nightly retries instead of resetting every night,
• the next successful refresh clears all of it. Broken sources heal themselves; the nightly job retries them too.

The changed flag compares a fingerprint of the new data with the stored one. How that fingerprint is built is its own post ("Did the data really change?").

3. The sync job: slow on purpose

const CONCURRENCY = 2;   // two queries at a time, never a burst
const PAUSE_MS = 250;    // short pause after each query

const queue = [...codes];
const worker = async () => {
  for (let code = queue.shift(); code; code = queue.shift()) {
    results.push(await this.refreshCode(code));
    saveProgress();
    await sleep(PAUSE_MS);
  }
};
await Promise.all(Array.from({ length: Math.min(CONCURRENCY, codes.length) }, worker));

Nobody waits for this job, so speed doesn't matter. Being gentle does. Two workers pull codes from a shared queue, with a short pause after each query. Measured: 177 sources refreshed in 44 seconds, never more than 3 connections.

The job starts detached: the endpoint creates a sync row, schedules the run with setImmediate, and immediately returns a syncId. The client polls GET /sync/:id to follow progress.

Progress is saved after every code, and the writes are chained one after another. With two workers, two progress updates could otherwise land out of order and make "done: 41 / 177" go backwards to 40.

If the job crashes halfway, every code it didn't reach is recorded as failed ("interrupted before this source was processed"), so the final status stays honest.

4. The schedule

@Cron(process.env.SOURCE_SYNC_CRON ?? '0 0 * * *', { timeZone: process.env.SOURCE_SYNC_TIME_ZONE })
async nightly() { … }

Once a night, at local midnight. Stored results can be up to 24 hours old, which was accepted explicitly because these sources barely change, and because edits refresh immediately.

5. Refresh-on-edit

When a user creates or updates a source's query or mapping, that source is refreshed in the background, right after the save:

• the save request doesn't wait for the external query,
• edited codes are refreshed one at a time, so a bulk edit stays gentle,
• the user sees the new result within seconds, not tomorrow.

6. Only one sync at a time

Starting a sync checks for an active one inside a serializable transaction:

return db.$transaction(async (tx) => {
  const active = await tx.sourceSync.findFirst({ where: { status: { in: ['PENDING', 'RUNNING'] } } });
  if (active) return { active };
  return { created: await tx.sourceSync.create({ data: { … } }) };
}, { isolationLevel: 'Serializable' });

• If one is active → 409 Conflict: "A source sync is already in progress. Wait for it to finish."
• If two requests race exactly together, PostgreSQL's serializable isolation rejects the loser with a serialization failure. That is translated into the same friendly 409.
• If the nightly cron finds a manual run in progress, it simply skips that night: the running job already refreshes everything it would.

7. Rate-limit full refreshes

A manual full refresh is only allowed if the last one started more than 5 minutes ago; otherwise the response is 429 Too Many Requests, with the exact time the next one is allowed. Five minutes still allows an honest retry right after a run finishes. The nightly cron is exempt: it runs once a day by design.

8. Survive restarts

@Injectable()
export class FailStaleSyncs implements OnApplicationBootstrap {
  async onApplicationBootstrap() {
    const count = await this.repo.failAllActive(); // PENDING / RUNNING → FAILED
    if (count > 0) logger.warn(`Marked ${count} interrupted source sync(s) as FAILED.`);
  }
}

A job killed by a deploy can never finish. Without this cleanup, its RUNNING row would block every future sync with a 409. On boot, any leftover active row is marked FAILED, and the next sync can start normally.

Lessons learned

• The fastest query is the one you don't run. If data changes rarely, serve it from storage and refresh it in the background.
• Move cost away from the user's path. Users should wait for your database, not for someone else's.
• Never let a failure overwrite good data. Keep the last good value and flag the problem.
• Remember when it started failing. brokenSince that survives retries turns noise into a useful signal.
• Plan the empty state. A live fallback for "nothing stored yet" made the first deploy safe.
• Background jobs should be slow and polite. Low concurrency plus a pause beats a burst every time.
• Design for restarts, races and repeat clicks. Stale-run cleanup, serializable "one active run" and a minimum interval each prevent a real failure.
• Make progress honest. Ordered progress writes and "interrupted" results keep the status trustworthy.

FAQ

When should I store query results instead of querying live?

When the data changes much less often than it's read, when the source is slow, shared or fragile, or when users shouldn't depend on another system being available. Accept a known staleness (for example 24 hours) and refresh on edits.

How do I prevent two background jobs from running at the same time?

Check for an active run and create the new one in the same transaction, ideally with serializable isolation or a unique constraint, so two concurrent starts can't both succeed. Return a clear 409 to the loser.

What happens to in-process background jobs when the app restarts?

They're killed. Mark any job still "running" as failed when the app boots, otherwise it may block future runs forever. For long or critical jobs, consider a dedicated queue system.

Should a failed cache refresh clear the cache?

Usually not. Keep the last good value, record the failure and when it started, and retry later. Clearing the cache turns a temporary outage into missing data.