OxiDB's recommendation engine: real-time co-occurrence counters and LLR

"Customers who bought this also bought" is one of the most common questions a shop puts to its database, and until now nearly every database answered it the same way: not at all. The application self-joins its order lines, scores the pairs and writes the result somewhere, usually from a nightly batch job, so the list is stale the moment it exists. OxiDB's fourth engine, oxidb-rec, exists for exactly that gap. It is a real-time item-to-item recommendation engine that turns baskets into counters as they arrive, computes recommendations at request time and scores them with Dunning's log-likelihood ratio (LLR). In this post I walk through the engine from the inside: the data model, time buckets, why LLR is the default (with a worked example by hand), the on-disk layout, the wire protocol and the limits. Every example comes from a synthetic bookstore dataset of 12,003 orders that I loaded into a real OxiDB server; every number shown is from those sessions.

Why a separate engine?

The reasoning is written down in ADR-0025, and the core of it fits in one sentence: this work is not shaped like a query, it is shaped like an index.

• The unit is a basket. Not a document or a row but a set, whose meaning lives entirely in which other items sit beside each member.
• The update is a pair-count increment. Roughly |B|²/2 increments for a basket of |B| items, independent of catalogue size. Nothing about it wants a planner.
• The read is a partial sort over one sparse row. The document engine can only express that as a full collection scan.
• The scoring is domain mathematics. It has one right answer and many wrong ones, and if you leave it to the caller every caller gets it wrong in the same way (I will show how below).

Forcing this into the document engine means storing an N×N matrix as documents, 100 million mostly-zero cells at 10k items. Forcing it into the SQL engine means the nightly batch job we are trying to escape.

Two clarifications up front. This is not vector search: OxiDB already has an HNSW vector index (full-text and vector search post), which answers "what resembles this" from an embedding someone else computed. oxidb-rec answers "what is actually bought next to this" from observed behaviour. No model, no embedding, no training, and it is right from the first order. It is not a graph query either: with (customer)-[bought]->(item), two hops from a bestseller is millions of paths (geospatial and graph post; graphs shine on sparse neighbourhoods). This is aggregation over a dense neighbourhood, and the right structure for it is an adjacency counter.

The algorithm is not new: it is Amazon's item-to-item collaborative filtering (Linden, Smith and York, 2003) plus Dunning's LLR (1993), the pairing used by Apache Mahout's ItemSimilarityJob. In production it is usually a pipeline of Spark, an indicator matrix and Elasticsearch. What all of those have in common is a refresh cycle between the order and the recommendation. I built this as an engine to delete that cycle, not to write the mathematics one more time.

Enabling and configuring it

The engine is off by default and follows the same pattern as the TSDB and SQL engines: one environment variable turns it on, it has its own files, its own WAL and its own recovery, and shares only the process, the port, authentication and RBAC with the other engines.

This is how I started the server for this post. My dataset is weekly, so I set the bucket width to one week (604800 seconds), and I lowered the checkpoint threshold to 256 KiB so checkpoints would actually happen:

OXIDB_ADDR=127.0.0.1:16100 \
OXIDB_DATA=./data \
OXIDB_REC=1 OXIDB_SQL=1 \
OXIDB_REC_BUCKET_SECS=604800 \
OXIDB_REC_CHECKPOINT_BYTES=262144 \
./oxidb-server

The engine opens lazily: the directory is created on the first rec request, and the log shows:

[oxidb] recommendation engine enabled (default db at ./data/rec)

Without OXIDB_REC the answer is explicit:

{"ok": false, "error": "recommendation engine is not enabled (set OXIDB_REC=1)"}

The wire protocol: one command, five operations

OxiDB's native protocol is length-prefixed JSON over TCP: a 4-byte little-endian length followed by the JSON body. Requests for the recommendation engine carry "engine": "rec" (or the reserved "cmd": "rec"), and an op field selects the action. The routing sits at the same junction in the handler as SQL and TSDB; requests without an engine field still take the document path byte for byte as before.

Query parameters: scoring (llr by default; cosine, jaccard, lift, count), half_life (in buckets, default 2, 0 = no decay), min_support (default 1), limit (default 10). ts is optional on every request, in epoch seconds; without it the server's wall clock is used. The engine itself has no clock, "now" enters in the bridge, which keeps tests and WAL replay deterministic.

A bare 15-line Python client is enough for the examples:

import json, socket, struct

class Oxi:
    def __init__(self, port=16100):
        self.s = socket.create_connection(("127.0.0.1", port))
    def call(self, req):
        b = json.dumps(req).encode()
        self.s.sendall(struct.pack("<I", len(b)) + b)
        n = struct.unpack("<I", self._read(4))[0]
        return json.loads(self._read(n))
    def _read(self, n):
        buf = b""
        while len(buf) < n:
            buf += self.s.recv(n - len(buf))
        return buf
    def rec(self, **kw):
        return self.call({"engine": "rec", "cmd": "rec", **kw})

The dataset: eight weeks of a bookstore

For a realistic test I generated eight weeks of a synthetic online bookstore with 1,500 orders a week (fixed seed, reproducible script), and planted some associations on purpose:

• Series: about half of the people who buy dune also buy dune-messiah, some buy children-of-dune. There is the the-hobbit → lotr-fellowship → two-towers → return-of-the-king chain, rust-book → rust-atomics / zero-to-production, sapiens → homo-deus → 21-lessons, and so on.
• Themes: science fiction, fantasy, programming, history, cooking, children's books. Books from the same theme carry a weak but real association.
• Content-independent bestsellers: bookmark-set is added to more than a third of all orders, gift-wrap and reading-light at lower rates. What they are added next to makes no difference at all.
• An association that changes over time: the-martian is bought with artemis for the first six weeks and with the newly released project-hail-mary in the last two.
• Coincidences: haiku-anthology and knitting-patterns, which appear nowhere else in the catalogue, were bought together in a single order. dune-tarot-deck was bought twice, both times with dune.
• A long tail: 400 Zipf-distributed titles (title-001 ... title-400).

The result: 12,003 orders, 448 distinct items, 3.2 items per basket on average. I sent them in time order, over one connection, as sequential track calls:

for r in rows:
    o.rec(op="track", model="purchase", basket_id=r["basket_id"],
          items=r["items"], ts=r["ts"])

12003 track calls, 12003 counted, 0.35 s, 34173 baskets/s

That was measured on an Apple M4 Mac over a local connection at a quiet moment. When I repeated the same load while other processes on the machine were doing heavy fsync work, it took 9 to 15 seconds, and sample showed almost all of the time inside the write() system call. Every track writes one WAL line and flushes it to the OS, so ingest speed is sensitive to disk contention; the engine's own work is cheap.

stats afterwards:

{
 "catalogue_items": 448,
 "bucket_secs": 604800,
 "buckets": 8,
 "generation": 5,
 "wal_bytes": 73966,
 "models": {
  "purchase": {
   "items": 448,
   "pairs": 19362,
   "baskets": 12003,
   "baskets_skipped": 0,
   "saturated_counters": 0,
   "approx_bytes": 1776848
  }
 }
}

generation: 5 means the 256 KiB threshold triggered five automatic checkpoints during the load. pairs: 19362 counts pair rows in both directions (9,681 distinct pairs), and approx_bytes is a rough estimate of the counter maps' memory, about 1.8 MB.

First recommendations: raw counts next to LLR

Now the actual question: what should we recommend to someone buying dune? First by raw co-occurrence count (scoring: "count"), then with the default LLR. To keep the comparison clean I turned decay off with half_life: 0:

{"engine": "rec", "cmd": "rec", "op": "related", "model": "purchase",
 "item": "dune", "scoring": "count", "half_life": 0, "limit": 6, "ts": 1771459199}

{"ok": true, "data": {"recommendations": [
  {"item": "dune-messiah", "score": 440.0},
  {"item": "children-of-dune", "score": 396.0},
  {"item": "bookmark-set", "score": 250.0},
  {"item": "foundation", "score": 144.0},
  {"item": "snow-crash", "score": 143.0},
  {"item": "hyperion", "score": 136.0}]}}

The same request with "scoring": "llr":

{"ok": true, "data": {"recommendations": [
  {"item": "dune-messiah", "score": 2835.72581039503},
  {"item": "children-of-dune", "score": 2510.829224631947},
  {"item": "snow-crash", "score": 209.64968031286844},
  {"item": "foundation", "score": 202.3380715753883},
  {"item": "hyperion", "score": 173.2231380022422},
  {"item": "neuromancer", "score": 164.29420415742788}]}}

By raw count, bookmark-set is third: it appears with dune in 250 orders. It is not in the LLR list at all; science fiction from the same theme takes its place. The bookmark set is in 4,491 orders, one in every three. Being in a third of the dune orders too is not an association, it is the base rate. Raw counts cannot tell the difference, so they put the bestseller in every list. The same pattern repeats for other books:

the-hobbit  count: lotr-fellowship 472, bookmark-set 290, two-towers 283, ...
the-hobbit  llr:   lotr-fellowship 2808.7, two-towers 1597.1, return-of-the-king 1025.1, ...
rust-book   count: rust-atomics 238, zero-to-production 188, bookmark-set 154, ...
rust-book   llr:   rust-atomics 1704.7, zero-to-production 1316.8, ddia 338.5, database-internals 312.6, ...

For the-hobbit, LLR lines up the whole trilogy while raw count squeezes the bookmark set in between. To go beyond a couple of examples, I checked where bookmark-set ranks for each of 27 books:

Under raw count the bookmark set is first in 16 of the 27 lists and in the top three in all of them. Under LLR it is never in the top five; its best rank is 8, and in eight lists it is beyond 100. A shop that writes "people who bought this also bought a bookmark" on every product page is technically correct and completely useless.

How LLR works: the 2×2 table and a calculation by hand

The question LLR asks is: given the base rates, how surprised should we be by this co-occurrence? For each candidate pair (x, y) it builds a 2×2 contingency table:

N is the total number of baskets, n(x) the baskets containing x, co(x,y) the baskets containing both. Dunning's G² statistic compares the observed table with the table you would expect if x and y were independent. An expected cell is row total times column total divided by N, and:

G² = 2 · Σ O · ln(O / E)        (over the four cells, a term is 0 when O = 0)

The engine computes the same thing in the equivalent entropy form:

fn xlogx(x: f64) -> f64 { if x <= 0.0 { 0.0 } else { x * x.ln() } }
fn entropy2(a: f64, b: f64) -> f64 { xlogx(a + b) - xlogx(a) - xlogx(b) }

pub fn llr(co: f64, n_x: f64, n_y: f64, total: f64) -> f64 {
    let k11 = co;
    let k12 = (n_x - co).max(0.0);
    let k21 = (n_y - co).max(0.0);
    let k22 = (total - n_x - n_y + co).max(0.0);
    let row_e = entropy2(k11 + k12, k21 + k22);
    let col_e = entropy2(k11 + k21, k12 + k22);
    let cell_e = entropy4(k11, k12, k21, k22);
    (2.0 * (row_e + col_e - cell_e)).max(0.0)
}

It is f64 all the way through, because counts become fractional once decay is applied, and the result is clamped at zero, since floating-point error near independence can push it slightly negative.

Let's compute two pairs from the dataset by hand. I counted the raw numbers separately from the data file in Python: N = 12,003, dune is in 707 baskets, dune-messiah in 440 (all of them also contain dune), and bookmark-set in 4,491, 250 of which contain dune.

dune × dune-messiah. If they were independent, the expected co-occurrence would be 707 × 440 / 12,003 = 25.9; the observed value is 440, seventeen times more. The four terms: 440·ln(440/25.9) = 1246.03; 267·ln(267/681.1) = -250.03; k21 = 0, so that term is 0; 11,296·ln(11,296/10,881.9) = 421.86. The sum is 1417.86, twice that is 2835.7.

dune × bookmark-set. Expected co-occurrence 707 × 4,491 / 12,003 = 264.5; observed 250, slightly fewer than expected. The terms are -14.12, 14.76, 14.55 and -14.51; the sum is 0.68, twice that is 1.36.

I compared these with what the server returned: 2835.72581039503 for dune-messiah and 1.3646498105954379 for bookmark-set. My independent Python calculation matched exactly. By raw count it is 440 against 250, the same league; by LLR it is 2835 against 1.4, no relationship at all. bookmark-set ranks 51st of 217 candidates in the LLR list.

The traps in cosine and lift

The engine supports all five scoring modes, and because the counters are stored raw, switching modes is a parameter rather than a rebuild. That makes it easy to see where the other modes break, on the same data.

Cosine is co / sqrt(n(x)·n(y)). Take the planted coincidence: haiku-anthology and knitting-patterns each occur once in the whole catalogue, in the same order.

{"op": "related", "model": "purchase", "item": "haiku-anthology", "scoring": "cosine", "half_life": 0}
{"ok": true, "data": {"recommendations": [
  {"item": "knitting-patterns", "score": 1.0},
  {"item": "bookmark-set", "score": 0.014922049367861507}]}}

1.0 is the highest score cosine can give. For comparison, the strongest real association in the shop, dune → dune-messiah, gets 0.789 under cosine. So cosine considers a single coincidence more "related" than a series relationship backed by 440 orders, simply because the denominator is as small as the numerator. LLR gives the same coincidence 20.8, two orders of magnitude below dune-messiah's 2835. It is not exactly zero, because one observation against an expectation of 0.00008 is statistically something. min_support exists to drop it completely:

{"op": "related", "model": "purchase", "item": "haiku-anthology", "half_life": 0, "min_support": 2}
{"ok": true, "data": {"recommendations": []}}

Lift is co·N / (n(x)·n(y)). The top three of the lift list for dune:

{"item": "children-of-dune", "score": 16.97736916548798},
{"item": "dune-messiah", "score": 16.97736916548798},
{"item": "dune-tarot-deck", "score": 16.97736916548798}

All three tie, because all three were always bought with dune. Lift does not care whether that happened 440 times or twice, so dune-tarot-deck (two sales in total) lands next to the sequels. Under LLR, dune-tarot-deck is tenth with 11.3: there is evidence, but not much. Jaccard produced orderings close to LLR on this dataset. In the ADR's validation on real data, cosine also largely agreed with LLR on head items and diverged on low-volume ones. The difference shows up exactly where evidence is thin, which is where most recommendation lists live.

The cart page: for_basket

Recommending from a single item is the weakest useful form. Conversion happens on the cart page, and for_basket is part of v1 for that reason. Each candidate's score is summed across the basket's members; the basket's own items and the exclude list never appear:

{"op": "for_basket", "model": "purchase", "items": ["dune", "rust-book"],
 "exclude": ["dune-messiah"], "limit": 6, "ts": 1771459199}

{"ok": true, "data": {"recommendations": [
  {"item": "children-of-dune", "score": 1006.2434574795043},
  {"item": "rust-atomics", "score": 719.6690210232482},
  {"item": "zero-to-production", "score": 509.19681360899995},
  {"item": "ddia", "score": 129.83592528033478},
  {"item": "database-internals", "score": 107.594666573641},
  {"item": "clean-code", "score": 102.12004133801383}]}}

I excluded dune-messiah (say the customer already owns it), and the list serves both interests. I did not pass half_life here, so the default decay of 2 buckets applies, which is why the scores are lower than in the related example.

Ask about an unknown item and the engine returns an empty list:

{"op": "related", "model": "purchase", "item": "no-such-book"}
{"ok": true, "data": {"recommendations": []}}

That is deliberate: an engine with no evidence should not disguise "I have no evidence" as "here is a recommendation". If you want a fallback such as bestsellers, the application chooses it.

Inside the engine: write and read paths

So far we have used the engine from the outside. The following sections are about its internals; the diagram below shows at a glance how a track request becomes counters, how a related request becomes a scored list, and what lives on disk:

Time: eight buckets, lazy shifting and half_life

The counters are not single numbers; they are split into time buckets. Every row is a fixed-size array:

pub struct Row {
    pub counts: [u32; BUCKETS],   // BUCKETS = 8
    pub epoch: u32,               // the period counts[0] belongs to
}

The period is simply ts / bucket_secs. With the default 30-day bucket the window is 240 days; in my weekly setup it is eight weeks. [u32; 8] instead of a Vec is deliberate: at a few million pairs, a Vec's 24-byte header alone would outweigh the counts it points to. A row is 36 bytes.

The most interesting design decision is that shifting is lazy. Instead of scanning the whole map and moving every counter one slot when a new period starts, each row carries its own epoch and is shifted on its next touch (a write or a read) by however many periods it is behind: an O(8) array rotation per row. In the ADR's first draft the shift happened eagerly at the period boundary, which is a full scan under the write lock. Monthly at the default width, but at millions of pairs an arbitrary request would have carried a multi-second stall. Lazy shifting removes that pause entirely; the cost is one u32 comparison per row access. Rows nobody touches stay stale, but the checkpoint walks them anyway and does not write rows that have decayed to all zeros. The checkpoint doubles as the garbage collector.

To watch the window work, I asked the same query with ts values in the future: now, four weeks later and eight weeks later:

ts = now          → dune-messiah 440, children-of-dune 396
ts = +4 weeks     → dune-messiah 228, children-of-dune 201
ts = +8 weeks     → []

Four weeks on, the first four weeks' buckets have dropped out of the window; eight weeks on, nothing is left. No deletes, no sweep, just period arithmetic.

On the read side, buckets are weighted by an exponential half-life: a bucket that is age periods old is multiplied by 0.5^(age / half_life). half_life is a query parameter, so "rising together right now" is a query, not a separate pipeline. Here it is on the-martian from the dataset:

half_life 0   → artemis 1387.0, project-hail-mary 584.1, snow-crash 207.7, hyperion 168.1
half_life 2   → project-hail-mary 506.7, artemis 338.6, snow-crash 84.4
half_life 1   → project-hail-mary 452.9, artemis 110.8, snow-crash 54.8, hyperion 44.5
half_life 0.5 → project-hail-mary 380.2, snow-crash 39.8, children-of-dune 33.6, hyperion 29.4

Without decay, six weeks of artemis history win. With the default half_life: 2, the new book from the last two weeks moves ahead, but artemis stays in the list. At half a bucket artemis drops out of the top four entirely, and the price is noise (children-of-dune showing up for the-martian), because the effective sample shrinks to the last few weeks. The right half_life depends on your domain: short for fashion, long for B2B. In no case is a rebuild needed.

Counters and memory layout

Each model (a named event space such as purchase, view, cart) holds:

pub struct Model {
    baskets: Row,                                  // total baskets per period
    item_counts: HashMap<u32, Row>,                // baskets containing x
    pair_counts: HashMap<u32, HashMap<u32, Row>>,  // baskets containing x and y
    seen: [HashSet<u64>; BUCKETS],                 // basket ids seen per period
    seen_epoch: u32,
    baskets_total: u64, baskets_skipped: u64, saturated: u64,
}

Items arrive as strings and are interned to u32. There is one interner per database, shared by all models, because the catalogue is the same catalogue. Views and purchases are different signals with different base rates, so they are counted in separate models and never pooled.

Pair rows are stored in both directions. That doubles counter memory but makes related(x) a single row lookup, which is the hot path. The alternative (only x < y ordering) halves memory and turns every query into a scan. The ADR budgets roughly distinct_pairs × 2 × 36 bytes plus map overhead, about 72 MB at one million distinct pairs. Memory grows with catalogue diversity, not order count, which is why stats reports it up front as approx_bytes.

Counters saturate on u32 overflow instead of wrapping, and stats reports any saturation as saturated_counters. A count pinned at the ceiling skews a score; a wrapped one inverts it.

Two ingest guards and idempotence

Basket size cap (default 50). A 500-line order is 125,000 increments and is almost always a bulk import or a test fixture: no preference signal, but it would dominate the counters. A basket over the cap is not silently dropped, it is counted:

{"op": "track", "model": "purchase", "basket_id": 900001, "items": ["title-001", "...60 items..."]}
{"ok": true, "data": {"counted": false}}

After that, stats shows "baskets_skipped": 1.

Duplicates within a basket count as one occurrence. Dedup runs before the size check, so a 60-line order of 40 distinct items is within the cap:

{"op": "track", "model": "view", "basket_id": 1, "items": ["dune", "dune", "dune-messiah"]}
{"ok": true, "data": {"counted": true}}
{"op": "related", "model": "view", "item": "dune", "scoring": "count"}
{"ok": true, "data": {"recommendations": [{"item": "dune-messiah", "score": 1.0}]}}

Idempotence. track is idempotent on basket id: the same order submitted twice counts once. I re-sent the last order in the dataset:

{"op": "track", "model": "purchase", "basket_id": 111237, "items": ["title-007"], "ts": 1771458725}
{"ok": true, "data": {"counted": false}}

The seen-set behind this is bucketed like everything else: basket ids are kept per period, and when a period leaves the window its ids leave with it. The idempotence window therefore equals the counting window, and the set is bounded by one window of baskets rather than growing for the model's whole lifetime. An unbounded seen-set would have been this engine's own resident-index mistake.

On disk: MANIFEST, snapshot and WAL

Each database's rec/ directory holds three kinds of files, the same discipline as the TSDB engine (time series post):

MANIFEST        {"generation": N}: the authoritative generation
snap.N.rec      interner + all model counters + seen-set
wal.N.log       baskets ingested since that snapshot, one JSON per line

The directory after the load:

-rw-r--r--  16       MANIFEST
-rw-r--r--  1008926  snap.5.rec
-rw-r--r--  73966    wal.5.log

A WAL record is exactly the basket the client sent:

{"model":"purchase","basket_id":110632,"items":["21-lessons","bookmark-set","homo-deus","sapiens","silk-roads","the-ottomans","title-007","title-041"],"ts_secs":1771203773}

A checkpoint writes snap.N+1 to a temp file, fsyncs and renames it, opens the new WAL, then atomically renames the MANIFEST. That rename is the single commit point. A crash before it recovers from N; after it, the old generation is removed. On open, any snap.* and wal.* files the MANIFEST does not name are swept, otherwise every crash would leave an orphaned pair behind.

Recovery is snapshot load plus WAL replay, and idempotence pays off a second time here: since the seen-set is inside the snapshot, replaying records the snapshot already contains is a no-op. The WAL needs no commit records and no truncation discipline. A torn final line (a crash mid-append) is skipped rather than treated as an error; that record was never acknowledged as durable. When I stopped and restarted the server, stats came back identical and related("dune") returned the same scores.

A manual checkpoint and disk usage:

{"engine": "rec", "cmd": "rec", "op": "checkpoint", "ts": 1771459199}
{"ok": true, "data": {"ok": true}}
{"cmd": "disk_usage"}
{"ok": true, "data": {"path": ".../data", "total_bytes": 1075232, "engines": {"documents": 260, "documents_mmap": 260, "sql": 0, "tsdb": 0, "rec": 1074972, "blobs": 0, "oximem": 0, "messaging": 0, "fts": 0, "pitr_archive": 0, "system": 0}}}

One more guard: the bucket width is stored in the snapshot and checked on open. Periods are ts / bucket_secs, so opening with a different width would silently reinterpret every counter's period. When I tried to open a directory written with weekly buckets using OXIDB_REC_BUCKET_SECS=86400, the engine refused (I shortened the end of the message):

failed to open rec engine for database "oxidb": bucket_secs mismatch: snapshot has 604800, config wants 86400 ...

To be clear about durability: the WAL is flushed to the OS after every record but not fsynced. That is safe against a process crash; on a power loss, baskets since the last checkpoint can be lost. The auto-checkpoint (OXIDB_REC_CHECKPOINT_BYTES) bounds replay time. For recommendation counters this is a deliberate v1 trade: a few seconds of missing orders is invisible in any list.

Performance

Again an Apple M4 Mac, local connection, single-connection Python client, 2,000 repetitions each:

related x2000: 36 us per call (round trip)
for_basket x2000: 48 us per call (round trip)
ping x2000: 13 us per call

An empty ping takes 13 µs, so the server-side cost of a single-item related query is around 20 µs: scoring a 217-candidate row, sorting it and serializing JSON. A three-item for_basket takes 48 µs. These are numbers from my small dataset; row length grows linearly with the candidate count. The real-data validation in the project's ADR (UCI Online Retail, 541k order lines) compares list quality across scoring modes rather than speed.

Access from SQL and stored procedures

The engine has a second front door: db.rec_* methods inside LANGUAGE COBRA stored procedures (SQL engine post). They go through the same dispatch function as the wire, so a procedure and a client get the same validation and the same answer. I wrapped the cart-page recommendation in a procedure:

# CREATE PROCEDURE cart_recs(a TEXT, b TEXT) LANGUAGE COBRA
def run(db, a, b)
    let out = db.rec_for_basket({"model": "purchase", "items": [a, b], "limit": 3, "ts": 1771459199})
    return out["recommendations"]
end

After compiling it with cobra build --portable and loading it as base64 with CREATE PROCEDURE ... LANGUAGE COBRA AS '...':

{"engine": "sql", "cmd": "sql", "sql": "CALL cart_recs('the-hobbit', 'sapiens')"}
{"ok": true, "data": [{"columns": ["item", "score"], "types": [null, null],
  "rows": [["lotr-fellowship", 1130.2998390605499], ["homo-deus", 742.2844136386848], ["two-towers", 664.9649628103361]]}]}

The recommendations come back as SQL rows. The same procedure that writes to an orders table could also feed the basket to the recommendation engine with db.rec_track.

Limits and honest notes

Not replicated in a cluster. In a Raft cluster, track is a write, but it is not replicated through Raft; it shares that gap with TSDB. Each node accumulates only the baskets it received, and related answers can differ from node to node. The ADR records this as a decision rather than a discovery. The two properties that make the fix cheap are already there: track is idempotent (at-least-once delivery is enough) and the state is a commutative counter (replicas converge under any interleaving).

Not personalisation. Co-occurrence answers "what goes with this", not "what should this person see next".

LLR is two-sided. G² does not distinguish "more than expected" from "less than expected", and the engine's implementation has no sign check. In my dataset, bookmark-set came 8th for pragmatic-programmer: co-occurrence 139, independence expectation 164.3. The association is actually negative, but the score is positive, if small (6.59). Because the score stays small it does not show up in short lists; it can appear in the tail of long ones.

Differences between the ADR and v1. The ADR describes the snapshot as a binary format; in v1, snap.N.rec is JSON (my 1 MB snapshot was a readable JSON file). The fallback: "popular" parameter and the backup/restore/retention operations mentioned in the ADR are not on this version's wire surface; the bridge knows track, related, for_basket, stats and checkpoint. An unknown operation is rejected with a clear error:

{"engine": "rec", "cmd": "rec", "op": "retention"}
{"ok": false, "error": "unknown rec op: \"retention\" (track, related, for_basket, stats, checkpoint)"}

A bug I caught while writing this post: late baskets. I accidentally ran my load script twice, and on the second pass all 12,003 baskets came back counted: true again. The cause: Row::roll_to and the seen-set shift assume the new period is greater than or equal to the current epoch. That is guarded only by a debug_assert!; in a release build, the now - epoch subtraction underflows as u32 into a huge number, which is read as "the whole window is behind". So a single basket whose ts is older than the model's current period clears the entire seen-set, zeroes the rows it touches (including the total-baskets row) and moves their epochs backwards. A small reproduction on a clean server:

before: {"recommendations": [{"item": "coffee", "score": 30.431653267886233}]}
late basket: {'counted': True}
after:  {"recommendations": []}

Here 40 baskets were in the current week and a single basket arrived with a ts from the previous week; afterwards the LLR list for milk went empty, because N had effectively dropped to 1. In the same session, re-sending a basket id that had already been counted returned counted: true. With data that arrives in time order (for example a live shop that omits ts and relies on the server clock) this never triggers, and every result above comes from data loaded in time order. But importing historical orders out of order, or writing from several sources with different clocks, is not safe today. The right fix is to add a late event to the correct slot (counts[epoch - now]) if it is still inside the window, drop it otherwise, and never move an epoch backwards. That is the first thing for the next release; until then, my advice is to load historical data sorted by ts.

How it fits with the rest of OxiDB

oxidb-rec is OxiDB's fourth engine and follows, without deviation, the pattern ADR-0010 set for the second one: its own crate (no dependency on the oxidb document crate or on oxidb-sql, so it can be embedded standalone), its own files, its own WAL, an additive engine discriminator. Multi-database support works like the other engines: every database has its own engine instance, and drop_database removes it too. In RBAC, the read role may run related, for_basket and stats; track and checkpoint require write access. It takes no part in cross-engine transactions: a recommendation counter that is a few seconds behind an order is not an inconsistency anyone can observe, and paying for cross-engine atomicity here would buy nothing.

In practice the setup looks like this: the code that writes the order to the document engine or the SQL engine sends a track over the same connection. The next product page request already sees the new order. There is nothing to refresh, no scheduled job to miss, no delay to explain. That is the whole reason I built this as an engine and not a nightly job.

Try it

Prebuilt OxiDB releases are on the GitHub releases page, along with the Docker image and the single-file server binary. All you need for the recommendation engine is OXIDB_REC=1. Send your own order history with track in time order, then put the scoring: "count" and scoring: "llr" lists side by side for a few products. The difference usually shows on the first product page.