A new campaign's first results cost zero API quota, because Postgres already has the titles

Searching a platform API for a brand's keywords is the slow, expensive, rate-limited part of Nakodo. YouTube's Data API gives a project 10,000 quota units a day and charges 100 of them for a single search call, so a day's quota is at most a hundred searches for the entire product, and we cap ours below that to leave room for everything else that spends units.

Meanwhile, every creator any campaign has ever found is sitting in our own Postgres, with their recent video titles.

Two brands selling slim wallets want substantially the same creators. The second one does not need to spend search quota to find them. It needs a text query against a table we already have.

Prefix matching is why we build the query by hand

Postgres full-text search has three entry points for turning a string into a query, and the choice matters:

• to_tsquery(config, text) takes an expression: lexemes joined with &, |, !, parentheses, and :* for prefix matching. Hand it a plain sentence and it is a syntax error, not a search.
• plainto_tsquery takes a sentence and ANDs the words. No prefix matching.
• websearch_to_tsquery takes a sentence with quotes and or and -, never errors, and is the right default for a search box. No prefix matching either.

We want prefix matching, because a keyword like "wallet" should hit "wallets" without us shipping a stemmer for every language a video title might be in. So to_tsquery it is, which means building a valid expression from untrusted text:

// "best slim wallet for men" -> "slim:* & wallet:* & men:*". Only letters and
// digits reach to_tsquery, so the query can't be malformed.
export function keywordQuery(keyword: string): string | null {
  const words = keyword
    .toLowerCase()
    .split(/[^\p{L}\p{N}]+/u)
    .filter((w) => w.length > 1 && !STOPWORDS.has(w) && !/^\d+$/.test(w));
  if (words.length === 0 || (words.length === 1 && words[0].length < 4)) return null;
  return [...new Set(words)].map((w) => `${w}:*`).join(" & ");
}

The split is on "anything that is not a Unicode letter or number", which is the whole safety story. Every operator character in the tsquery language, every quote, every parenthesis, is a separator here and cannot survive into the output. This is not SQL injection (the query string is still a bound parameter), it is query-language injection, and the symptom is a 500 from a keyword with an apostrophe in it rather than a breach. It is the kind of bug that sits undiscovered until a French brand signs up.

Two other guards earn their place. A keyword that reduces to nothing returns null and is skipped rather than matching everything. A single word under four characters also returns null: cat:* as the only term would match every title containing "catalogue", "category" and "catering", and a 200-row page of noise is worse than no match at all.

'simple', not 'english'

The query runs against the simple text search configuration:

sql`to_tsvector('simple', ${videos.title}) @@ to_tsquery('simple', ${query})`

english would give us stemming, so "wallets" and "wallet" would unify without a prefix match, and would drop English stopwords for free. It would also be a declaration that every video title in the table is English, which is false: campaigns target markets in German, French, Spanish, Italian, Dutch and more, and english stemming applied to German titles produces confident nonsense.

simple does nothing except lowercase and split. Which means the stopwords are ours to handle, and they look like this:

// Words that say nothing about the topic, in the languages briefs use most.
const STOPWORDS = new Set(
  ("a an and the of for to in on with without vs versus or by at from my your our best top new review reviews " +
   "how what why guide tips ideas unboxing honest worth buy buying cheap budget first look test tested " +
   "der die das und für mit von im ein eine test vergleich " +
   "le la les des du de et pour avec un une meilleur avis " +
   "el los las del y para con un una mejor reseña " +
   "il lo gli e per con migliore recensione " +
   "het een en voor met beste").split(/\s+/),
);

Note the second half of the English group: review, unboxing, honest, worth, best, tested. Those are not stopwords in any linguistic sense. They are stopwords here, because they appear in a third of all product video titles ever made and in a lot of the keywords a research pass produces. "honest review slim wallet" should match on "slim" and "wallet", and a query that also demands "honest" and "review" is strictly narrower for no gain.

A domain stopword list is a thing worth keeping separately from a language one, and the comment above it should say which it is.

The match query

One query per enabled keyword, four at a time:

const perKeyword = await mapLimit(enabled, 4, async ({ keyword }) => {
  const query = keywordQuery(keyword);
  if (!query) return { keyword, rows: [] };
  const rows = await db
    .select({
      channelId: videos.channelId,
      videoIds: sql<string[]>`array_agg(${videos.id})`,
      titles: sql<string[]>`array_agg(${videos.title})`,
    })
    .from(videos)
    .innerJoin(channels, eq(channels.id, videos.channelId))
    .where(and(
      sql`to_tsvector('simple', ${videos.title}) @@ to_tsquery('simple', ${query})`,
      gt(videos.publishedAt, publishedAfter),
      isNotNull(channels.fetchedAt),
      inArray(channels.platform, platforms),
      sql`not exists (select 1 from ${campaignChannels} cc
                      where cc.campaign_id = ${campaignId} and cc.channel_id = ${videos.channelId})`,
    ))
    .groupBy(videos.channelId)
    .limit(PER_KEYWORD);
  return { keyword, rows };
});

array_agg on both ids and titles means one row per channel carrying its evidence, so the application does not have to group anything. The titles come back because they are needed immediately, for the brand's negative keywords:

const ranked = [...matches]
  .filter(([, m]) => !m.titles.some((t) => containsAny(t, campaign.brief!.negativeKeywords)))
  .sort(([, a], [, b]) => b.videoIds.size - a.videoIds.size || b.keywords.size - a.keywords.size)
  .slice(0, MAX_MATCHES);

Ranked by how many of their videos matched, then by how many distinct keywords they matched. A channel with six matching videos is a better bet than one with a single lucky title, and a channel that matched three different keywords is a better bet than one that matched the same keyword three times. Both numbers are Set sizes, because the same video can arrive from two keywords.

isNotNull(channels.fetchedAt) filters to channels we have actually read, not ones that exist only as a foreign key from a video row. not exists skips anyone the campaign already has, which matters because this runs more than once in a campaign's life.

Everything matched is inserted with its provenance and queued for the same enrichment, filtering and scoring as a search result:

const rows = await db.insert(campaignChannels).values(
  ranked.map(([channelId, m]) => ({
    campaignId,
    channelId,
    source: "library" as const,
    matchedKeywords: [...m.keywords],
    matchedVideoIds: [...m.videoIds],
  })),
).onConflictDoNothing().returning({ channelId: campaignChannels.channelId });

A library match is not a shortcut past the judgement. It is a shortcut past the search. The per-campaign work (is this creator a fit for this brand, what do their comments look like, is there a contact address) all still happens, per campaign, and none of it is shared between brands. What is shared is the public record of the creator: their channel, their recent titles, their numbers.

The cache lifetime is in somebody else's terms of service

Here is the part that makes this a product constraint rather than a caching decision.

The YouTube API's terms allow API data to be stored for up to 30 days. That is not a performance number you tune, it is a number you obey, and it is published on our own privacy page because creators are entitled to know it.

So the maintenance job does this:

export const YOUTUBE_FRESH_DAYS = 25;
export const SOCIAL_FRESH_DAYS = 30;

const RETENTION_DAYS = 30;
const SOCIAL_RETENTION_DAYS = 180;

A YouTube creator worth keeping (in a conversation, a fit in any campaign, or with a published contact address) is refreshed from day 25, uploads included. Five days of slack is not arbitrary: it is room for a few failed job attempts, a quota-starved day and a weekend, so that nothing drifts past 30 while waiting for a retry. Everything not worth keeping is deleted at 30 days, with its videos, contacts, comment analysis and search results.

Instagram and TikTok have no such rule, so the numbers there come from a different argument: data over 30 days old is re-read when a campaign needs it because follower counts move, and a creator nothing has looked at for 180 days is deleted because this is personal data and "we might want it later" is not a reason to keep it.

The same isStale function the match query feeds into is what enrichment uses to decide whether to call the API at all:

const isStale = (channelId: string, at: Date | null) =>
  !at || at.getTime() < Date.now() - (isYouTube(channelId) ? YOUTUBE_FRESH_DAYS : SOCIAL_FRESH_DAYS) * DAY_MS;

One predicate, two numbers, used by the refresher, the enricher and the matcher. When the terms change, one constant changes.

What this buys, and what it costs

A new campaign on a crowded niche gets its first page of real creators within a minute of setup, with zero API quota spent, because the second brand in a category inherits the first brand's search bill. A new campaign in a niche nobody has searched gets nothing from the library and goes straight to the APIs, which is the correct behaviour and completely invisible.

The costs are honest ones. The library can only match on titles, so it is blind to a creator whose relevant work is in the description or the spoken content. It is biased towards whatever our existing customers sell. And it needs the retention job to be correct, because a cache governed by a third party's terms is a compliance surface, not just a performance one.

Worth it, for a query that is one @@ operator and a nine-line string builder.

If you want to see what the matching is standing in for, how to find YouTube influencers is the manual version, and how to vet a YouTube creator is what happens to every match after it arrives.