Type What You Remember

Nobody remembers a filename. They remember the blue hair, and that it was from that one game. The file is called IMG_4471.png. A filename search was never going to find it.

skowt.cc is an asset database for game art. Contributors upload assets, a lot of them from gacha games, and other people pull them back down, often a few hundred at once. It has run since 2022 First as a raw file index, then as wanderer.moe, now as skowt. Same database the whole way through. on one database and one bias: keep the request path cheap, and never make the API do work it can hand to something else.

The bias is older than the design. I started it at 15 as an h5ai directory listing on a VPS running on student credits. People couldn't use it, so it got a real frontend in SvelteKit, and since I was broke, the frontend was also the storage: every asset pulled in with import.meta.glob at build time and shipped inside the site, about 500MB of it. No server, because a server was a bill.

The first money I had went on an API, a Worker that did nothing but index R2. The habit stuck. Every request should be as close to free as it can be, because for years free was the budget.

It sounds like a lot of effort for something this niche. It isn't, for two reasons. The site kept getting bigger, and at some point a thing that handles more has to handle whatever gets thrown at it, so the design either grows up or falls over. And skowt is where I try things out on real people. It has never made money, but it has a userbase, and that is the one thing a side project can't buy: something ships, and within a day I know whether anyone wanted it. This is how it holds up now, and why the search is the part I'm proudest of.

The shape

It's a monorepo. apps/web is a TanStack Start SPA, apps/server is an Elysia process on Bun with a tRPC adapter mounted on it, and apps/asset-redirect is a Cloudflare Worker that owns the public read path. Everything lives in R2 under plain file names, originals included, and the worker exists so nobody can reach an original by guessing its name: every image read resolves at the edge, gets redirected or refused there, and never enters the API. Two more apps do inference: a small CPU sidecar that turns a search query into a vector, and a GPU image on rented hardware that does the per-asset model work.

Three stores, each holding what it's best at. Relational data lives in Turso, which is hosted libSQL, a SQLite fork with a server in front. That choice pays off twice, and hurts once: search can be a virtual table in the same database rather than a separate service, and every read is a network round trip. Files live in Cloudflare R2. Redis holds the ephemeral state: sessions, rate-limit windows, download batches, and a cache in front of the hot reads.

Uploads

The API never handles file bytes. An upload is a three-step handshake and the server only touches the first and last step.

A presigned PUT pins nothing. Not the content type, not the byte count. The browser can declare a 2MB PNG and push 400MB of whatever it likes at the URL, so commitUpload treats the object as hostile and re-derives the truth off one read.

const head = await bucket.head(key) // 1if (head.size > MAX_BYTES) return reject(key, "too large")

const bytes = await bucket.get(key)
const kind = sniff(await bytes.slice(0, 32)) // 2if (!kind?.mime.startsWith("image/")) return reject(key, "not an image")

const hash = sha256(bytes) // 3if (await findDuplicateByHash(hash, pending.id)) // live rows only
  return reject(key, "duplicate")

const variants = await derive(bytes) // 4if (!variants.thumb || !variants.preview) return reject(key, "variants failed")

It stats the size, which is the only real size enforcement in the flow. It reads the header bytes and rejects anything that isn't an image, whatever the browser said. It hashes the content and checks it against every live asset, so the same bytes can't enter twice. And it generates the thumbnail and 1024px preview fail-closed: a commit that can't produce both is rejected rather than letting a broken original in. Every rejection purges the object and the pending row, so the moderation queue never shows a ghost with no bytes behind it.

The lookup is the fast path, there so a duplicate gets a friendly message. The guarantee is a partial unique index on asset(hash) over live rows, WHERE status IN ('approved', 'pending'). It covers live rows only because denied and deleted uploads keep their hash, and uploading those bytes again is allowed. Two identical files committing at once can both pass the lookup, but only one gets past the index. The other gets the same 409 as any duplicate, naming the asset that won, and cleans up everything it wrote: the pending row, the original, and the thumbnail and preview.

Untrusted uploads write to a limbo/ prefix that the edge worker refuses to route at all. A pending upload physically sits somewhere unservable until a moderator copies it to the public prefix. That split is enforced by storage layout, not by an access check I have to remember to write.

The read path

For a long time downloads had no gate. Anyone could construct a CDN URL and skip the counter, and the thumbnails leaned on Cloudflare's image resizing, which ran into the hundreds some months. The whole read path now sits behind a Worker, and the public read policy is one pure function.

export function decide(host: string, path: string): Verdict {
  if (host !== "pack.skowt.cc") return { kind: "deny" }
  const [prefix, ...rest] = path.split("/")
  if (prefix === "derived" && VARIANTS.has(rest.at(-1)!)) // 1    return { kind: "stream", key: path }
  if (prefix === "originals") // 2    return { kind: "redirect", to: previewFor(rest) }
  return { kind: "deny" } // 3}

The allowlist is derived variants only. A raw original 301s to its preview. limbo/ has no branch, a variant that doesn't exist has no branch, and everything else 404s before the bucket is ever read. Every read that does happen is cached at the edge, because each one is billed.

The redirect, not a 404, is deliberate. Previews were always meant to be public, and years of asset URLs are sitting on Pinterest and in Google, which is traffic I want. So every URL that was ever posted still resolves. It just lands on the preview now, and the original is only reachable through a presigned GET.

Actual downloads are presigned now. downloads.generate verifies the ids, records the batch in Redis, bumps the counters, and mints a short-lived presigned GET per asset. Recording and delivery are one call, so an honest client can't skip the counter.

Uploads were presigned from the start. Downloads were not, and for years there was no point. Nothing required a login, and the page had to render the images from somewhere, so the original URL was one Inspect Element away for anyone who cared. A presigned link would only have stopped the polite. It started to mean something once the worker made the direct URLs dead, because then the presigned GET was the only path left to the bytes.

Whole-game packs bake nothing. downloads.generatePack builds a manifest from the live database at request time and returns one HMAC-signed URL. The worker verifies it, pulls the manifest back from the API, and streams a store-mode zip. Store mode means the archive size is computable up front, so the browser gets a real content-length and an honest progress bar instead of chunked encoding's shrug.

Search

Searching by filename is one filter on the asset query. Searching for what's actually in the picture is a different problem, and the way to get there is to start with the filename search and keep breaking it.

The first version was the one everybody writes.

return like(asset.name, `%${term}%`)

A LIKE with a wildcard on both ends is a full scan, every row, every query, and it's case-sensitive in ways that surprise people. It worked for years because the table was small. Then it wasn't.

The fix is an FTS5 virtual table with the trigram tokenizer, living inside the same libSQL database. Trigram is the specific pick because it reproduces the substring feel of LIKE while being indexed and case-insensitive, so swapping LIKE for MATCH is behaviour-preserving.

CREATE VIRTUAL TABLE IF NOT EXISTS asset_fts
  USING fts5(asset_id UNINDEXED, name, tokenize='trigram');

The index tracks the asset table through SQL triggers, and the update trigger is scoped AFTER UPDATE OF name on purpose, so the constant view-count and download-count writes never touch it. None of this fits Drizzle, which can't model a virtual table or a trigger, so the DDL is idempotent and four paths apply it and agree.

Then someone types two letters. A term under three characters can't be trigram-tokenised at all, so the query layer keeps the scan around and switches to the index at three.

if (term.length >= 3) { // 1  return sql`${asset.id} IN (
    SELECT asset_id FROM asset_fts WHERE name MATCH ${term} // 2  )`
}
return like(asset.name, `%${term}%`) // 3

At three characters the subquery runs MATCH once and the planner uses the FTS index, because the subquery is non-correlated and never materialises an id list into the outer query. Below three it falls back to the scan, which is fine, because a two-letter search returns half the database whatever you do.

Before making it better, the version above is already broken in a way that doesn't show up in testing. MATCH has its own query grammar: AND, OR, NEAR, prefix *, - negation. Passing raw input to it means a user can inject an operator, or crash the query with a stray *. The escape wraps the whole term in double quotes and doubles any quote inside, which forces FTS5 to read every character as a literal.

function escapeFtsMatch(term: string): string {
  return '"' + term.replace(/"/g, '""') + '"'
}

That's filename search, done. It has nothing to say about IMG_4471.png.

Two lanes

The reason the next part exists is a Discord channel. My server had one called #asset-hunt, and it got used a lot: does anybody know an asset with such and such, where do I get this from, half of it asked in the wrong channel anyway. People kept asking for tags, and I wasn't going to have anyone sit there tagging tens of thousands of images by hand, me least of all. I wanted "hey, does anyone know" to be a question the database could answer itself, and when the monorepo rewrite happened, that was the thing I decided to solve.

A description gets answered by two indexes at once. The caption lane is a second FTS5 table, this one over captions and tags a vision model wrote for every asset, tokenised with porter instead of trigram, because a caption is an English sentence and "sword" should match "swords". The semantic lane is cosine similarity over image embeddings: the query is embedded into the same vector space the pictures live in, and the nearest vectors come back ranked.

Neither lane is much good alone, and the reason is the shape of what each stores. An embedding is dense and approximate. Every asset scores against every query, so there's always a full ranked list, and the bottom of it is noise with a number attached. A caption index is sparse and exact. A word is either written on that asset or it isn't, and when it is, that's a much harder fact than a cosine of 0.31.

So caption hits rank first and the semantic lane fills in behind, catching the phrasings nobody's caption happened to use. When both lanes turn up the same asset, the blend keeps the semantic score rather than inventing one. Captions also carry the entire weight for the half of the assets whose filenames are machine ids, hashes and export numbers from whatever tool ripped them. No tokeniser saves you there. The caption index is the only place real words exist for those assets.

A vector is only comparable to itself

This is the part that's easy to get wrong. A vector means nothing on its own. It's only meaningful next to vectors from the same model, at the same precision, normalised the same way. Change any of that and you haven't made retrieval slightly worse, you've started comparing two different spaces.

Backfilling the initial 43,000 took genuinely forever, and the first pass ran partly on my own 5070 at home, quantised down to fit, because I wasn't going to pay for a full-precision run before knowing whether anyone would use the thing. The telemetry answered that fast. People leaned on it far harder than I expected, so I went all out.

Every asset is embedded with siglip2-base-patch16-512 at fp16, and the fp16 matters as much as the model id. A q8-quantised build of the same model, measured against fp16 on the same images, lands anywhere from cosine 0.70 to 0.93. Nothing errors on a number like that. It's still 768 floats, search still returns a full page, and the results are quietly wrong in the only way you ever find out about: searching for something you know is in there and not seeing it.

So parity is a rule, not a preference. The GPU worker that embeds new uploads runs the same model id, dtype and normalisation as the run that did the backfill, and its smoke test embeds an image that already has a database row and asserts the cosine against that row is 1.0000 exactly. Not a tolerance, because anything short of the same vector means the two sides disagree about something.

The scan

The obvious place to run the similarity is SQL, and it holds up right until two people search at once.

SELECT asset_id, vector_distance_cos(embedding, ?) AS d
FROM asset_vector WHERE model = ?
ORDER BY d LIMIT 60;

Every query drags ~120MB of vectors through the engine and the scans queue behind each other. p50 looked healthy at ~48ms. Production p99 was 19.5 seconds, with a 43-second worst case in the traces.

But 120MB is 120MB. The whole set fits in memory, so the scan moved out of the database and into a process-local buffer.

const dim = 768
const vectors = new Float32Array(rows.length * dim) // 1for (const [i, row] of rows.entries())
  vectors.set(normalise(row.embedding), i * dim)

export function nearest(q: Float32Array, k = 60) {
  const scores = new Float32Array(rows.length)
  for (let i = 0; i < rows.length; i++) { // 2    let dot = 0
    for (let j = 0; j < dim; j++) dot += vectors[i * dim + j]! * q[j]!
    scores[i] = dot
  }
  return topK(scores, k) // 3}

One row-major Float32Array holds every vector, normalised once at load. A search is a brute-force dot product across it and a top-k pass over the scores. No approximate-nearest-neighbour structure, no vector service, no index tuning. Arithmetic over a contiguous buffer is a thing CPUs are extremely good at.

Freshness is the only interesting part. Embeddings change rarely, so searches serve the current snapshot immediately and never block on being up to date. A background probe reads a handful of counts into a fingerprint at most once every 15 seconds. A fingerprint that moved kicks off a rebuild, also in the background. A new row is live in search about 15 seconds after it's written, and nobody's query ever waits on a rebuild. Same instinct as not running Meilisearch: the data already fits somewhere I control, so it doesn't need a process of its own.

Enrichment

Both search lanes read derived state. A vector and a caption are functions of the image and the model, and every asset needs them computed exactly once. That work wants a GPU, and skowt owns the GPU rather than renting an API in front of someone else's: one Docker image with both models, deployed as a serverless endpoint on Modal with RunPod as the fallback, Both run the same image, so a fallback never changes the vector space. scaling to zero and billing per second, so it costs nothing between uploads.

The question is who decides an asset needs the work. Here's the first answer.

setInterval(async () => {
  const missing = await db.select({ id: asset.id }).from(asset)
    .where(notExists(vectorFor(asset.id, MODEL))) // 1  for (const a of missing) await enrich(a.id) // 2}, 5 * 60_000)

Every five minutes, find every asset with no row under the active model tag and enrich it. This is correct. It is also slow: worst case, an upload sits unsearchable for the full five minutes, and uploaders notice.

So, a queue. commitUpload enqueues the asset id, a worker calls the endpoint and writes both rows, and the asset is searchable about six seconds after commit.

await enrichQueue.add("enrich", { assetId }, { jobId: assetId })

jobId is the asset id, so enqueueing something already in flight is a no-op. Fast, and now there are two things that can go wrong instead of one.

Before fixing anything, here's the version that makes it worse. A job dies after the vector is written and before the caption is. The obvious patch is retries, and a dead-letter queue for the ones that keep dying.

new Worker("enrich", enrich, {
  concurrency: 2,
  settings: { backoffStrategy: exponential },
})
queue.on("failed", async (job) => {
  if (job.attemptsMade >= 3) await deadLetters.add(job.name, job.data)
})

Now there's a dead-letter queue to drain, a half-finished asset with one row and not the other, and a job record whose idea of what happened has to be kept in step with the database's. Flush Redis and all of that memory is gone, along with every queued job. Two sources of truth, and a person in the middle reconciling them.

The actual design is the first version and the second version together, with the second one demoted. The sweep never went away. It still runs every five minutes, and its query is still the whole definition of pending: an asset needs a vector if there is no row for it under the active model tag. The queue exists only for latency. Jobs retry three times and then drop on purpose, because retrying past that is a worse implementation of the sweep, and the sweep owns the long tail.

There's no job record to keep in step with reality and no dead-letter queue to drain, because the absence of the row is the to-do item. A job that dies halfway leaves the row missing, so the next sweep picks it up with no idea anything went wrong. Flushing Redis throws away every queued job and costs a few minutes. Losing the entire queue costs latency and never data, which is the only reason I was willing to put a queue in at all.

Both stores carry a model column and every row is written with the tag that produced it. Nothing is rewritten in place, which makes a model upgrade dull: backfill the new tag next to the old rows, flip one constant, drop the old rows whenever convenient. If the new space turns out worse, the constant flips back and the old vectors are still sitting there.

The cheap stuff

The asset query is the hot path. It runs on every game page, filter, sort and scroll, and two things carry it: a Redis cache keyed by the full parameter set, with a bounded in-memory fallback if Redis is unreachable, and keyset pagination instead of LIMIT/OFFSET. Keyset pins an exact boundary row and resumes past it with a tuple comparison, so deep pages don't get linearly slower and an insert between page loads can't hand you a duplicate or a gap.

Sessions run through Redis too. Turso sits across the network, so a session check that reads the session row and then the user row is two round trips at ~57ms each, paid on every authenticated request. better-auth takes a secondary storage adapter, so that collapses to one sub-millisecond GET. It isn't a dumb TTL cache either: it invalidates on sign-out and revocation, and a permission change patches the live cached session rather than waiting for a re-login.

Rate limiting is a real sliding window, not a fixed bucket. Each window is a sorted set scored by millisecond timestamp, and the trim, count and admit run as one atomic Lua script, so two concurrent requests can't both read a count under the limit and both get in.

Telemetry is the same flavour of cheap. Every request emits exactly one structured wide event at response time, built up in place as it moves through Elysia's hooks and shipped to Better Stack with the standard OTel HTTP attributes next to cheap domain identity: the user id, which procedures ran, how many database queries the request made. "Every request where this user hit a procedure that ran more than 50 database queries" is a single filter, no trace to open, and a query count over 50 flags an N+1 on its own.

The end

As of writing, that's ~43,000 assets across 23 games, 27 million-plus downloads and 190 million-plus views, run by one person plus the Discord regulars I trust not to get phished. Expanding past images is the next pressure the design has to absorb, and the presigned path already doesn't care what the bytes are, the edge worker already refuses to serve anything it doesn't recognise, and the MIME map is one entry away from learning a new type.

That's most of it. I've left out the moderation flow, the Discord bot's share of ingest, and how the caption model was chosen, which is a post of its own.