app/lib/search/zero-result.server.ts

Functions

Imports

It imports app/db/index.ts, app/db/schema.ts, app/lib/search/zero-result.mjs. Imported by app/lib/operator/api.server.ts, app/routes/admin.tools.tsx, app/routes/search.tsx.

Source

1import { desc, sql } from "drizzle-orm";
2
3import { getDb } from "~/db";
4import { zeroResultQueries } from "~/db/schema";
5import {
6  normaliseZeroResultQuery,
7  ZERO_RESULT_RETENTION_SECONDS,
8} from "./zero-result.mjs";
9
10// Stores the query and counters only, never an IP, user agent or session. The write never fails a search.
11
12/** Upsert on the query, so table size tracks distinct misses rather than traffic. */
13export async function recordZeroResult(env: Env, raw: string): Promise<void> {
14  const query = normaliseZeroResultQuery(raw);
15  if (query === null) return;
16
17  const db = getDb(env);
18  await db
19    .insert(zeroResultQueries)
20    .values({ query })
21    .onConflictDoUpdate({
22      target: zeroResultQueries.query,
23      set: {
24        count: sql`${zeroResultQueries.count} + 1`,
25        lastSeen: sql`(unixepoch())`,
26      },
27    });
28}
29
30/** On last_seen, not first_seen: a long-running gap asked about last week is live demand. */
31export async function purgeZeroResults(env: Env, now = new Date()): Promise<number> {
32  const cutoff = Math.floor(now.getTime() / 1000) - ZERO_RESULT_RETENTION_SECONDS;
33  const result = await getDb(env)
34    .run(sql`DELETE FROM zero_result_queries WHERE last_seen < ${cutoff}`);
35  return result.meta?.changes ?? 0;
36}
37
38export async function topZeroResults(env: Env, limit = 50) {
39  return getDb(env)
40    .select()
41    .from(zeroResultQueries)
42    .orderBy(desc(zeroResultQueries.count), desc(zeroResultQueries.lastSeen))
43    .limit(limit);
44}