> For the complete documentation index, see [llms.txt](https://docs.keewano.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.keewano.com/lua-scripting/guide.md).

# Lua Guide

Custom reports that read each user's event stream in order — the questions SQL can't phrase.

## Overview

Keewano stores each user's complete history as an ordered stream of events. Lua scripting is the native way to query that stream: you write a small program that the database runs inside itself, once over every user, and it hands you the answer. It's the interface for questions about what a user did **in order** — funnels, sessions, custom journeys, monetization analysis — the questions a row-based query language has no way to phrase.

It sits alongside [SQL Query Studio](/sql-query-studio/guide.md). SQL is the familiar, everyday interface and covers the reporting core; Lua is the full-power one, reaching everything the engine can do. If you know SQL, you already have the mental model you need — here is the shift:

| In SQL you think…           | In Keewano Lua you think…                                    |
| --------------------------- | ------------------------------------------------------------ |
| A table of rows             | A per-user array of events, processed in order               |
| `SELECT … GROUP BY user_id` | The runtime calls your `process_user` once per user          |
| `SUM` / `COUNT` / `AVG`     | Each group of users has its own running total you update     |
| `SELECT … FROM partials`    | A final `reduce` merges every group's total into one answer  |
| `JOIN` across tables        | Doesn't exist — everything you need is already in the stream |
| `WINDOW` functions          | You walk the event array in order and carry state as you go  |

{% hint style="success" %}
**The one idea:** SQL gives you rows; Keewano gives you per-user event arrays. Anything that asks "what did this user do, in what order" is what Lua is for.
{% endhint %}

This guide is written for someone with a SQL background. It teaches the model and the core building blocks, then works through real example scripts. You don't need to read it front to back — the [Worked examples](#worked-examples) stand on their own once you have the shape of a script.

***

## The shape of a script

Every script defines the same four things at the top level. The names are fixed, and if any is missing the script is rejected before any data is touched.

* **`partial`** — a container where you accumulate whatever you're computing. Users are split into groups; each group gets its own partial.
* **`process_user(user, events)`** — a function the system calls once for every user. Inside it you read that user's events and update `partial`.
* **`reduce(partials)`** — a function the system calls once at the end, with the list of every group's partial. You combine them and write the result into `output`.
* **`output`** — the container whose contents become the final result the caller receives, as JSON.

![One script, two stages: map over every user, then reduce to one answer](https://3698466984-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FWQi6lbHOA8tyhBBRtvuQ%2Fuploads%2Fgit-blob-7658d8d27d3e682ba79455636a3564055dbd5e52%2Flua-map-reduce.png?alt=media)

In Lua, the skeleton you'll actually type:

```lua
partial = {}
output  = {}

function process_user(user, events)
  -- runs once per user in the group; mutate `partial`
end

function reduce(partials)
  -- runs once at the end; mutate `output`
end
```

{% hint style="warning" %}
Keep state **bounded**. A counter, a running sum, a fixed histogram, a sketch — never a growing per-user table. That single discipline is what lets the same script run over millions of users without exhausting memory.
{% endhint %}

***

## Walking a user's timeline

Inside `process_user`, `events` is that user's stream in time order. You read it by index, like a plain array:

* `events.count` — how many events the user has.
* `events[i]` — the event at position `i` (0-based, so the last is `events[events.count - 1]`).

An event answers questions only about itself — `ev.id` (which event it is), `ev.data` (its numeric value), `ev.timestamp` (UTC seconds), `ev.index` (its own position). Moving through the stream is your job, done by controlling `i`.

### Scan for what you want — don't visit every event

You can step one-by-one with `i + 1`, but when you're looking for events of a particular kind, jump straight to them with a **finder**. Each finder is a single scan in native code — often 10× faster than a Lua loop over every event.

Every finder takes a range: a start index and a mandatory stop bound as the last argument. A miss returns `events.count`, so the same `i < events.count` test that ends your loop also tells you the search is done. The essentials:

| Finder                                            | Jumps to…                                                  |
| ------------------------------------------------- | ---------------------------------------------------------- |
| `events:find_next(start, id, stop)`               | the next event with this id                                |
| `events:find_next_data(start, id, data, stop)`    | the next event with this id and value                      |
| `events:find_next_with_ids(start, {ids}, stop)`   | the next event matching any of up to 8 ids, one scan       |
| `events:find_next_income(start, stop)`            | the next revenue event (purchase / ad / subscription)      |
| `events:find_next_gap_in_time(start, secs, stop)` | the first event after a silence — the next session's start |

Each has a backward twin (`find_prev_…`). Counting a user's purchases is the canonical loop — find, act, find again past the one you found:

```lua
partial.purchases = 0                       -- init once at top level
local PURCHASE = kdb.resolve_event_id("Purchase")

function process_user(user, events)
  local i = events:find_next(0, PURCHASE, events.count)
  while i < events.count do
    partial.purchases = partial.purchases + 1
    i = events:find_next(i + 1, PURCHASE, events.count)
  end
end
```

The stop bound is what makes windowed questions cheap and correct. "Did this session contain a click?" never scans past the session's end, and never accidentally matches the next session:

```lua
-- s, e delimit one session
if events:find_next(s, BUTTON_CLICK, e) < e then
  -- clicked inside THIS session
end
```

Splitting a stream into sessions is two lines: loop `find_next_gap_in_time(s, 1800, events.count)` and each returned index starts the next session.

### Per-user metadata

Alongside the events, `user` carries constant, free-to-read metadata: `user.id`, `user.reg_time`, `user.last_activity`, `user.campaign`, `user.country`, `user.platform`, `user.is_paying_user`, `user.is_tester` / `user.is_bot`, and more. These don't touch the event array.

***

## Events with more than one field

Some events carry more than one field — a purchase has an amount and a product; a reward has an item and a quantity. In the stored stream these are laid out as a first event followed by detail events. Together they're a **chain**, and you describe the layout once with `kdb.chain`:

```lua
quest_reward = kdb.chain {
  at     = "Quest Done",         -- the chain's first event
  reward = "Quest Reward Item",
  qty    = "Quest Reward Qty",
}
```

You never declare field types — the platform reads how each value is encoded (number, text, USD) from your event metadata. A text field compares to text directly; a money field reads as a USD number. From one descriptor you get a typed getter (`events[i]:get_quest_reward()`) and its own finders (`find_next_quest_reward`).

### Reading a chain — ask, then read

Two steps: ask the event for the chain (you get a handle, or `nil` if it isn't one), and only if it's non-nil, read the fields. Move between chains with the chain's own finder, and skip a whole chain's body with `end_pos`:

```lua
partial.qty = 0

function process_user(user, events)
  local n = events.count
  local i = events:find_next_quest_reward(0, n)
  while i < n do
    local q = events[i]:get_quest_reward()     -- ask: is this the chain?
    if q then                                   -- yes → read its fields
      partial.qty = partial.qty + q.qty
    end
    i = events:find_next_quest_reward(q and q.end_pos or i + 1, n)
  end
end
```

{% hint style="warning" %}
The `if q then` guard is required — reading a chain's fields off an index the getter didn't confirm stops the run with a clear error rather than returning garbage.
{% endhint %}

### Multi-item chains

A chain can carry a variable-length run of items — a loot drop or a barter with several item/quantity pairs. Declare the repeating tuple with `kdb.array(...)`, then bind the group to a local and index it:

```lua
loot_item = { name = "Loot Item", count = "Loot Count" }
loot_drop = kdb.chain { at = "Loot Dropped", items = kdb.array(loot_item) }

-- inside process_user, for a bound handle c:
local f = c.items                    -- bind the group once
for k = 0, f.count - 1 do
  partial.loot = partial.loot + f[k].count
end
```

### Built-in chains for revenue and economy

Common monetization and economy events come predefined in a built-in module — one top-level line includes them:

```lua
require "game_analytics"
```

It provides the revenue chains every monetized product shares — `get_purchase()`, `get_ad_revenue()`, `get_subscription()`, each with `usd` and `name` — plus item-grant and exchange chains. A unified `get_income()` matches any of the three revenue kinds and adds a `kind` field to tell them apart, with `find_next_income` scanning to all three in one pass:

```lua
function process_user(user, events)
  local i = events:find_next_income(0, events.count)
  while i < events.count do
    local m = events[i]:get_income()
    if m then partial.revenue = partial.revenue + m.usd end   -- m.kind: 0 purchase, 1 ad, 2 subscription
    i = events:find_next_income(m and m.end_pos or i + 1, events.count)
  end
end
```

***

## Names in, text out

`ev.id` and `ev.data` are numbers. Two resolvers convert the strings you know (an event's display name, a value's text) to those numbers — entirely at upload, never at query time, so they cost nothing in the hot loop:

```lua
local BOSS   = kdb.resolve_event_id("Boss Fight")    -- folds to a number at upload
local WANTED = kdb.resolve_event_data("dragon")

if ev.id == BOSS and ev.data == WANTED then ... end
```

Arguments must be knowable at upload — a string literal or a path into `params` — which also lets a caller pass event names in as parameters. Comparing a raw string against `ev.id` is rejected at upload with a hint to the right resolver, so a typo never silently matches nothing.

### Getting text back out

The reverse direction: your `reduce` computed "the top event is id 100" and the report should say "Boss Fight." Two functions produce output markers the platform swaps for display text after the query runs — so numbers never leak into the result, and renamed events show their current name:

```lua
function reduce(partials)
  output.top_event = kdb.event_name(best_id)               -- → "Boss Fight"
  output.top_value = kdb.event_data_text(best_id, best_d)  -- → "dragon"
end
```

{% hint style="info" %}
Markers are **reduce-only**. Keep numeric ids in your partials during the map stage and translate once in `reduce` — calling these inside `process_user` is an upload error.
{% endhint %}

***

## Working at scale

Some questions would otherwise need every raw value held in memory at once. A quantile sketch avoids that — it's fixed-size and mergeable, so each group builds its own and `reduce` combines them.

### Percentiles without storing every value

A quantile sketch swallows numbers one at a time and answers "what was the 95th percentile?" later — approximately, but with small bounded error. Two sketches merge without re-reading data. Create one with `kdb.kll.new()`; feed it with `:update(n)`, combine with `:merge(other)`, and read with `:quantile(q)` where `q` is 0–1:

```lua
partial.session_lengths = kdb.kll.new()
local SESSION_END = kdb.resolve_event_id("Session End")

function process_user(user, events)
  local i = events:find_next(0, SESSION_END, events.count)
  while i < events.count do
    partial.session_lengths:update(events[i].data)
    i = events:find_next(i + 1, SESSION_END, events.count)
  end
end

function reduce(partials)
  local merged = kdb.kll.new()
  for _, p in ipairs(partials) do merged:merge(p.session_lengths) end
  output.p50 = merged:quantile(0.50)
  output.p95 = merged:quantile(0.95)
  output.p99 = merged:quantile(0.99)
end
```

### The output

`output` can hold numbers, strings, booleans, and nested tables of those. Anything else is dropped.

***

## Reusable modules

A `require` is a compile-time include — the platform registers the module's definitions at upload, exactly as if you'd written them yourself. Beyond the built-in `game_analytics`, you can register your own module for your project: a reusable set of custom chains, and functions shared across all your reports.

A shared function can read the event stream — just pass the stream in like any argument. This lets you write a helper once and reuse it everywhere:

```lua
-- module "economy_helpers"
require "game_analytics"

function purchase_total(ev, from, to)      -- `ev` is the stream, passed in by callers
  local total = 0
  local i = ev:find_next_purchase(from, to)
  while i < to do
    local p = ev[i]:get_purchase()
    if p then total = total + p.usd end
    i = ev:find_next_purchase(i + 1, to)
  end
  return total
end
```

A report then just does `require "economy_helpers"` and calls `purchase_total(events, 0, events.count)`. The economy module in the [Worked examples](#worked-examples) shows this pattern with a custom chain and two helpers reused across reports.

***

## Worked examples

Real scripts, each self-contained. Read them once you have the shape of a script — they put the building blocks together on concrete questions. The comments in each explain the reasoning inline.

### Revenue and spend percentiles

The canonical first example: per user, sum their revenue and, for payers, record the total; then report payer count, revenue, and the median and P99 of spend — the percentiles coming from a KLL sketch so no raw values are kept.

```lua
require "game_analytics"

partial = {}
partial.payers = 0
partial.revenue = 0.0
partial.spend = kdb.kll.new()

output = {}

--- Map stage: runs once per user on the DataNode.
---@param user kdb.user
---@param events kdb.events
function process_user(user, events)
    local spent = 0.0
    local i = events:find_next_income(0, events.count)
    while i < events.count do
        local m = events[i]:get_income()
        if m then
            spent = spent + m.usd
        end
        i = events:find_next_income(m and m.end_pos or i + 1, events.count)
    end

    if spent > 0 then
        partial.payers = partial.payers + 1
        partial.revenue = partial.revenue + spent
        partial.spend:update(spent)
    end
end

--- Reduce stage: runs once on UJServer over every DataNode's partial.
---@param partials table[]
function reduce(partials)
    local payers = 0
    local revenue = 0.0
    local spend = kdb.kll.new()

    for _, p in ipairs(partials) do
        payers = payers + p.payers
        revenue = revenue + p.revenue
        spend:merge(p.spend)
    end

    output.payers = payers
    output.revenue = revenue
    output.median_spend = spend:quantile(0.5)
    output.p99_spend = spend:quantile(0.99)
end
```

### Revenue on a specific day (using params)

Inputs arrive through `params`. Here a UTC day's start and end come in as unix seconds; the script sums revenue whose timestamp falls in that window.

```lua
-- Total revenue on one specific UTC day, passed in via `params`
-- (params.day_start, params.day_end — unix seconds, UTC). params is decoded
-- before the top level runs, so hoist its fields into upvalues here.

require "game_analytics"

partial = {}
partial.revenue = 0.0

output = {}

local day_start = params.day_start
local day_end = params.day_end
assert(day_start and day_end, "params.day_start and params.day_end (unix seconds, UTC) are required")

---@param user kdb.user
---@param events kdb.events
function process_user(user, events)
    local i = events:find_next_income(0, events.count)
    while i < events.count do
        local ev = events[i]
        local m = ev:get_income()
        if m then
            local ts = ev.timestamp
            if ts >= day_start and ts < day_end then
                partial.revenue = partial.revenue + m.usd
            end
        end
        i = events:find_next_income(m and m.end_pos or i + 1, events.count)
    end
end

---@param partials table[]
function reduce(partials)
    local revenue = 0.0
    for _, p in ipairs(partials) do
        revenue = revenue + p.revenue
    end
    output.revenue = revenue
end
```

### Top 10 most frequent events

Counts every event id per user, merges the count maps, then selects the top 10 in `reduce` — turning computed ids into display names with output markers at the very end.

```lua
-- Top 10 most frequent events across all users — output markers on ids the
-- script COMPUTES at query time (nothing known at upload). partials carry
-- only numbers (id → count maps); the ids turn into display names once, in
-- reduce, via kdb.event_name.

partial = {}
partial.counts = {}

output = {}

---@param user kdb.user
---@param events kdb.events
function process_user(user, events)
    local counts = partial.counts
    local i = 0
    while i < events.count do
        local id = events[i].id
        local c = counts[id]
        if c then
            counts[id] = c + 1
        else
            counts[id] = 1
        end
        i = i + 1
    end
end

---@param partials table[]
function reduce(partials)
    local merged = {}
    for _, p in ipairs(partials) do
        for id, n in pairs(p.counts) do
            local c = merged[id]
            if c then
                merged[id] = c + n
            else
                merged[id] = n
            end
        end
    end

    -- No table.sort in the sandbox: select the top 10 by repeated max scan
    -- (the merged map has at most one entry per event id, so this is tiny).
    output.top = {}
    for rank = 1, 10 do
        local best_id = nil
        local best_n = 0
        for id, n in pairs(merged) do
            if n > best_n then
                best_id = id
                best_n = n
            end
        end
        if not best_id then
            break
        end
        merged[best_id] = nil
        output.top[rank] = { event = kdb.event_name(best_id), count = best_n }
    end
end
```

### Event breakdown (params round-trip)

The full parameter round-trip: a caller passes a list of (event, value) pairs; names go in through the resolvers, counts come out with display text restored.

```lua
-- Occurrence counts for a caller-supplied list of (event, value) pairs —
-- the full params round trip: strings go IN through the resolvers (folded to
-- numbers at upload, hidden precomputed tables), and come back OUT through
-- output markers (replaced with display text after the query).
--
-- params.filters = {
--     { event = "Boss Fight", value = "dragon" },
--     { event = "Level Up",   value = "cave"   },
-- }

partial = {}
partial.hits = {}
partial.users = {}

output = {}

assert(params.filters, "params.filters ({event=, value=}, ...) is required")

for k, _ in ipairs(params.filters) do
    partial.hits[k] = 0
    partial.users[k] = 0
end

---@param user kdb.user
---@param events kdb.events
function process_user(user, events)
    for k, f in ipairs(params.filters) do
        -- Both resolvers fold at upload: `id` and `d` are plain reads from
        -- hidden number tables, and the finder scans in native code.
        local id = kdb.resolve_event_id(f.event) --[[@as integer]]
        local d = kdb.resolve_event_data(f.value) --[[@as integer]]
        local n = 0
        local i = events:find_next_data(0, id, d, events.count)
        while i < events.count do
            n = n + 1
            i = events:find_next_data(i + 1, id, d, events.count)
        end
        if n > 0 then
            partial.hits[k] = partial.hits[k] + n
            partial.users[k] = partial.users[k] + 1
        end
    end
end

---@param partials table[]
function reduce(partials)
    output.rows = {}
    for k, f in ipairs(params.filters) do
        local hits = 0
        local users = 0
        for _, p in ipairs(partials) do
            hits = hits + p.hits[k]
            users = users + p.users[k]
        end
        local id = kdb.resolve_event_id(f.event) --[[@as integer]]
        -- Output markers: the platform swaps these for the current display
        -- text after the query runs — numbers never leak into the report.
        output.rows[k] = {
            event = kdb.event_name(id),
            value = kdb.event_data_text(id, kdb.resolve_event_data(f.value) --[[@as integer]]),
            hits = hits,
            users = users,
        }
    end
end
```

### A custom economy module

A customer's own module: a custom `gacha_pull` chain and two shared helpers (`pulls_in_range`, `spend_in_range`), reused across reports via `require "economy"`.

```lua
-- Example customer module: custom chains + shared helpers, reused across
-- reports via require "economy". Event refs are name strings resolved
-- against the game's own events at upload.

require "game_analytics"

gacha_item = { name = "Gacha Item Name", count = "Gacha Item Count" }

gacha_pull = kdb.chain {
    at = "Gacha Pull",
    banner = "Gacha Banner",
    items = kdb.array(gacha_item),
}

function pulls_in_range(events, from, to)
    local n = 0
    local i = events:find_next_gacha_pull(from, to)
    while i < to do
        local c = events[i]:get_gacha_pull()
        if c then n = n + 1 end
        i = events:find_next_gacha_pull(c and c.end_pos or i + 1, to)
    end
    return n
end

function spend_in_range(events, from, to)
    local usd = 0
    local i = events:find_next_income(from, to)
    while i < to do
        local m = events[i]:get_income()
        if m then usd = usd + m.usd end
        i = events:find_next_income(m and m.end_pos or i + 1, to)
    end
    return usd
end
```

And a report that uses it — how many pulls do high-spending users make?

```lua
-- Whale gacha behavior: how many gacha pulls do high spenders make?
-- Uses the economy module's custom chain and helpers.

require "economy"

partial = {}
partial.users = 0
partial.gacha_whales = 0
partial.whale_pulls = 0

function process_user(user, events)
    local n = events.count
    partial.users = partial.users + 1

    if spend_in_range(events, 0, n) >= 100 then
        partial.gacha_whales = partial.gacha_whales + 1
        partial.whale_pulls = partial.whale_pulls + pulls_in_range(events, 0, n)
    end
end

function reduce(partials)
    local users, whales, pulls = 0, 0, 0
    for _, p in ipairs(partials) do
        users = users + p.users
        whales = whales + p.gacha_whales
        pulls = pulls + p.whale_pulls
    end
    output.gacha_whales = whales
    output.whale_share = users > 0 and whales / users or 0
    output.avg_whale_pulls = whales > 0 and pulls / whales or 0
end
```

***

## Where to go next

This guide covers the model and the building blocks, and the worked examples put them together. When you need the exact surface — every finder and its range rules, the full chain grammar and its validation, the resolver rules, the sandbox, the hard limits, and the error messages with their fixes — reach for the companion document.

[**Lua Scripting over KeewanoDB — Reference**](/lua-scripting/reference.md) is the lookup companion to this guide: the complete API surface, organised for jumping straight to what you need. Everything here is a subset of it, chosen to teach; nothing there contradicts what you've read.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://docs.keewano.com/lua-scripting/guide.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.