Building a Video Recommendation Engine with SurrealDB Graph Queries

For three years I ran video recommendations on the same stack that powers everything else at ViralVidVault: PHP 8.4, SQLite in WAL mode, LiteSpeed, and a thin layer of Cloudflare Workers at the edge. It worked fine for "videos in the same category" and "most viewed this week." It fell apart the moment I tried to answer the question that actually drives engagement on a viral-video site: given that this person watched these six clips, what should they see next?

That is a graph question, not a table question. The signal lives in the edges — who watched what, which videos share viewers, which creators cluster together — and every attempt to model it in SQLite turned into a swamp of self-joins over a watch_events table that got slower with every million rows. Three-hop traversals ("viewers of videos similar to the ones you liked") needed recursive CTEs that I could not cache and could not explain to my future self.

So I moved the recommendation layer — and only that layer — to SurrealDB. This post is the honest write-up: the data model, the graph queries that replaced my join hell, how I keep it GDPR-compliant for a European audience, and the PHP glue that ties it back into the existing site. No rewrite-everything evangelism. SQLite still holds the canonical video catalog. SurrealDB holds the behavior graph.

Why a graph model and not another join

The core recommendation idea is item-to-item collaborative filtering: two videos are related if the same people watch both. In a relational schema you express "videos co-watched with video X" like this:

SELECT w2.video_id, COUNT(*) AS overlap
FROM watch_events w1
JOIN watch_events w2 ON w1.session_id = w2.session_id
WHERE w1.video_id = :target
  AND w2.video_id != :target
GROUP BY w2.video_id
ORDER BY overlap DESC
LIMIT 20;

That is one hop, and it is already a self-join over your largest table. Now add "but weight by how recent the watch was," "exclude videos the person already saw," and "prefer creators they follow," and you are three nested subqueries deep. Each new hop multiplies the join cost. On SQLite the query planner stopped using my indexes past two hops and started doing full scans. I had 40M watch events. It was not going to end well.

Graph databases invert the cost model. Instead of matching rows at query time, the relationship is the stored structure. In SurrealDB you traverse edges with an arrow syntax that reads like the question you are asking, and a two- or three-hop walk stays a graph walk instead of degrading into a cartesian product.

The data model

SurrealDB is schemaless by default, but for a recommendation engine you want discipline, so I define the tables explicitly. There are two node tables — user and video — and the relationship between them is the watched edge. Creators get their own node so I can traverse "other videos by creators this person watches."

-- SurrealQL schema definition
DEFINE TABLE video SCHEMAFULL;
DEFINE FIELD title       ON video TYPE string;
DEFINE FIELD category    ON video TYPE string;
DEFINE FIELD region      ON video TYPE string;      -- ISO country, e.g. 'DE'
DEFINE FIELD published   ON video TYPE datetime;
DEFINE FIELD creator     ON video TYPE record<creator>;
DEFINE INDEX video_cat   ON video FIELDS category;

DEFINE TABLE user SCHEMAFULL;
DEFINE FIELD anon_id     ON user TYPE string;         -- rotating pseudonymous id
DEFINE FIELD consent     ON user TYPE bool DEFAULT false;
DEFINE FIELD region      ON user TYPE string;

-- the edge table: users -> watched -> videos
DEFINE TABLE watched SCHEMAFULL TYPE RELATION FROM user TO video;
DEFINE FIELD watched_at  ON watched TYPE datetime DEFAULT time::now();
DEFINE FIELD seconds     ON watched TYPE int;          -- dwell time
DEFINE FIELD completed   ON watched TYPE bool;

The important line is the RELATION table. In SurrealDB an edge is a first-class record with its own fields, so I can store how someone watched a video — dwell time, completion — directly on the relationship. That metadata is what turns a naive co-watch count into something that actually predicts what a person wants next. A three-second bounce and a full watch both create a watched edge in a relational model unless you add columns; here the edge carries the weight natively.

Recording a watch is a RELATE statement. This is what my ingestion Worker sends after a play event clears the consent check:

RELATE user:⟨anon_abc123⟩ -> watched -> video:⟨v_98f2⟩
  SET watched_at = time::now(),
      seconds = 47,
      completed = true;

The recommendation query that replaced the join swamp

Here is the query I was chasing for months in SQLite, expressed as a graph traversal. Read it inside-out: start at the target video, walk backwards along watched edges to reach every user who watched it, then walk forwards from those users to every other video they watched. That is the co-watch neighborhood in two hops.

-- "Videos co-watched with video:v_98f2"
SELECT
    id,
    title,
    category,
    count() AS overlap
FROM (
    SELECT ->watched->video AS recs
    FROM video:⟨v_98f2⟩<-watched<-user
)
SPLIT recs
WHERE recs.id != video:⟨v_98f2⟩
GROUP BY recs
ORDER BY overlap DESC
LIMIT 20;

The arrows carry the meaning. <-watched<-user walks from the video back to the users who watched it. ->watched->video walks from those users out to their other videos. No join keys, no query planner guessing — the traversal follows physical edge pointers. On my dataset this two-hop query runs in single-digit milliseconds where the equivalent SQLite self-join took 400–900ms and refused to cache.

But raw co-watch counts are dumb. A video watched by everyone (the current global viral hit) shows up as "related" to everything, which is useless. I weight by recency and dwell time on the edge, and I penalize globally popular videos so the recommendations stay specific. SurrealQL lets me do the weighting in the same pass:

-- weighted co-watch: recency + dwell, popularity-penalized
LET $target = video:⟨v_98f2⟩;

SELECT
    recs.id AS video,
    recs.title AS title,
    math::sum(
        weight * time::now().sub(edge.watched_at).hours().max(1).pow(-0.3)
    ) AS score
FROM (
    SELECT
        ->watched AS edge,
        ->watched->video AS recs,
        IF ->watched.completed THEN 1.0 ELSE 0.4 END AS weight
    FROM $target<-watched<-user
)
SPLIT recs
WHERE recs.id != $target
  AND recs.region IN ['DE','FR','NL','ES','IT','PL','SE']  -- EU catalog
GROUP BY video
ORDER BY score DESC
LIMIT 20;

A few things worth calling out:

• The edge weight is intrinsic. IF ->watched.completed reads a field off the relationship itself. Completed watches count 2.5x a bounce. You cannot do that cleanly without edge properties.
• Recency decay uses time::now().sub(watched_at) raised to a negative power, so a co-watch from an hour ago dominates one from last month. Viral video interest is spiky; a 30-day-old signal is nearly noise.
• Region filter keeps the EU catalog EU-facing, which matters both for relevance and for the data-residency posture I describe below.

Three hops, when two is not enough

Cold-start videos — the ones just uploaded — have no co-watch history yet, so the two-hop query returns nothing. For those I fall back to a creator-affinity traversal: find the creators whose videos this person watches, then surface other videos by those creators, even brand-new ones.

-- creator-affinity fallback for cold-start videos
SELECT
    ->watched->video->creator->[?]<-creator<-video AS candidates
FROM user:⟨anon_abc123⟩
SPLIT candidates
WHERE candidates.published > time::now() - 7d
ORDER BY candidates.published DESC
LIMIT 15;

That is a three-hop walk — user → watched videos → their creators → other videos by those creators — and it stays readable. The equivalent recursive CTE in SQLite was 40 lines and I never fully trusted it. Here the path is the query. This is the single biggest reason I stopped fighting the relational model for this workload: the query maps one-to-one onto the sentence I would say out loud to describe the recommendation.

Wiring it into a PHP 8.4 stack

The rest of the site is PHP. SurrealDB speaks HTTP and WebSocket; for request-scoped queries I use the HTTP /sql endpoint with a small client. Nothing exotic — curl, a bearer token, and PHP 8.4's typed properties and readonly for the value objects.

<?php
declare(strict_types=1);

final class SurrealClient
{
    public function __construct(
        private readonly string $endpoint,
        private readonly string $namespace,
        private readonly string $database,
        private readonly string $token,
    ) {}

    /** @return array<int, array<string, mixed>> */
    public function query(string $surql, array $vars = []): array
    {
        $ch = curl_init("{$this->endpoint}/sql");
        curl_setopt_array($ch, [
            CURLOPT_POST           => true,
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_TIMEOUT_MS     => 250,   // fail fast; recs are non-critical
            CURLOPT_HTTPHEADER     => [
                'Accept: application/json',
                'Content-Type: application/json',
                "Authorization: Bearer {$this->token}",
                "Surreal-NS: {$this->namespace}",
                "Surreal-DB: {$this->database}",
            ],
            CURLOPT_POSTFIELDS => json_encode([
                'query' => $surql,
                'vars'  => $vars,
            ], JSON_THROW_ON_ERROR),
        ]);

        $raw = curl_exec($ch);
        if ($raw === false) {
            throw new RuntimeException('SurrealDB unreachable: ' . curl_error($ch));
        }
        curl_close($ch);

        $decoded = json_decode($raw, true, 512, JSON_THROW_ON_ERROR);
        // SurrealDB returns one result object per statement
        return $decoded[0]['result'] ?? [];
    }
}

The recommendation service wraps that client and — this is the part that keeps LiteSpeed happy — treats recommendations as a best-effort side dish. If SurrealDB is slow or down, the page still renders with a category-based fallback pulled from SQLite. A recommendation timeout must never take down a video page.

<?php
declare(strict_types=1);

final class Recommender
{
    private const CACHE_TTL = 900; // 15 min

    public function __construct(
        private readonly SurrealClient $surreal,
        private readonly CategoryFallback $fallback,
        private readonly string $cacheDir,
    ) {}

    /** @return list<string> ordered video ids */
    public function relatedTo(string $videoId, string $region): array
    {
        $key = "{$this->cacheDir}/rec_{$videoId}_{$region}.json";
        if (is_file($key) && (time() - filemtime($key)) < self::CACHE_TTL) {
            return json_decode((string) file_get_contents($key), true);
        }

        try {
            $rows = $this->surreal->query(
                'SELECT recs.id AS video, math::sum(weight) AS score
                 FROM (SELECT ->watched->video AS recs,
                              IF ->watched.completed THEN 1.0 ELSE 0.4 END AS weight
                       FROM type::thing("video", $vid)<-watched<-user)
                 SPLIT recs
                 WHERE recs.id != type::thing("video", $vid)
                   AND recs.region = $region
                 GROUP BY video ORDER BY score DESC LIMIT 20',
                ['vid' => $videoId, 'region' => $region],
            );
            $ids = array_map(static fn(array $r): string => $r['video'], $rows);
        } catch (\Throwable $e) {
            error_log('rec fallback: ' . $e->getMessage());
            $ids = $this->fallback->byCategory($videoId, 20);
        }

        if ($ids !== []) {
            file_put_contents($key, json_encode($ids), LOCK_EX);
        }
        return $ids;
    }
}

Notice type::thing("video", $vid) — that is how you safely build a record id from a bound variable instead of string-concatenating it into the query. Treat it like a prepared statement; never interpolate raw user input into SurrealQL.

The file cache is deliberately the same pattern the rest of the site uses (I cache rendered fragments to disk and let LiteSpeed serve them). Recommendations for a given video are identical across users in the same region, so a 15-minute disk cache absorbs almost all the traffic and SurrealDB only sees cache-miss queries.

Keeping it GDPR-compliant

Running a European viral-video site means the behavior graph is the most sensitive thing I store, and "user watched these videos" is personal data the moment it is tied to an identifiable person. My rules:

• No watch edge without consent. The Cloudflare Worker that ingests play events checks the consent cookie before it ever calls RELATE. No consent, no edge. The recommendation then falls back to non-personalized "popular in your region."
• Pseudonymous, rotating ids. The user node's anon_id is not an account id or an IP. It is a rotating token that resets on a schedule, so the graph cannot be trivially re-identified. The graph structure survives rotation well enough for co-watch signal because the edges still cluster.
• Right to erasure is a single traversal. This is where the graph model pays off for compliance, not just relevance. Deleting a person means deleting their node and every edge attached to it — one statement:

-- GDPR Article 17: erase a user and all their behavior edges
DELETE user:⟨anon_abc123⟩;

In SurrealDB, deleting a record deletes its graph edges with it, so there is no orphaned watched row lingering in a join table waiting to leak. In my old SQLite schema, erasure meant hunting through watch_events, session_map, and three derived aggregate tables, and it was easy to miss one. Here the blast radius of a deletion is exactly the subgraph, and it is provable.

• Data residency. The SurrealDB instance runs in an EU region, and the region filters in every query keep EU behavior data flowing to EU users. Nothing about the recommendation path touches infrastructure outside the bloc.

What actually improved

Honest numbers from the cutover, measured over a month against the old category-similarity recommender:

• Related-video click-through went up roughly 34%. The co-watch signal is simply better than "same category" for viral content, where the interesting connections cut across categories.
• Two-hop query latency dropped from 400–900ms (SQLite self-join, uncached) to 4–9ms (SurrealDB traversal), which is what made real-time personalization viable at all.
• Erasure requests went from a 20-minute manual checklist to a one-line delete I could safely automate.

And what did not improve, because I want this to be useful and not a sales pitch:

• Operational surface grew. I now run a second datastore. SQLite needed zero babysitting; SurrealDB needs backups, version upgrades, and a health check. For a solo-ish operation that is a real cost.
• Cold start is still hard. New users with no edges get non-personalized results. The graph helps videos cold-start via creator affinity, but people cold-start via nothing but region and consent status.
• Consistency is looser. SQLite in WAL mode gives me rock-solid transactional guarantees for the catalog. I keep the catalog there for exactly that reason and only push behavior to the graph, where eventual consistency is fine.

Takeaways

If your recommendation logic keeps turning into deeper and deeper self-joins, that is the signal that you are storing a graph inside a table. Moving that one workload — and only that workload — to a graph database bought me queries that read like the question, sub-10ms traversals, and a GDPR erasure story I can actually defend. I did not rewrite the site. SQLite still owns the catalog, LiteSpeed still serves the pages, Cloudflare Workers still guard the edge. SurrealDB just owns the edges between videos and the people who watch them, which is where recommendation quality was hiding the whole time.

Start small: model one edge table, port one query, keep a fallback, and measure the click-through before you commit to anything bigger.