| name | crewrift-event-warehouse |
| description | Use to build and query the Crewrift event warehouse — a policy-indexed DuckDB/Parquet dataset of per-tick gameplay events (kills, follows, proximity, votes, tasks, chat) over many episodes — for deep, mechanistic, cross-episode questions about a policy's behaviour. Triggers: 'how often is crewborg near a crewmate without killing', 'who trails crewborg', 'where does it abandon tasks', 'does it vote real imposters', 'build the warehouse for this round', 'query the events', 'stream an experience request into a warehouse'. For a FRESH experience request, stream_eval.py builds the warehouse WHILE the episodes run (the default; builds are incremental). This is the deep-dig mainstay after crewrift-survey flags something. |
Crewrift Event Warehouse
The deep-dig mainstay. The survey (crewrift-survey) tells you what happened from the result
JSONs; the warehouse tells you how — it replays every episode, extracts per-tick gameplay
events (movement, proximity, following, kills, bodies, votes, chat, tasks, visibility), re-keys each
from episode slot to policy / role, and collates them into a queryable DuckDB/Parquet dataset
so you can ask cross-episode, by-policy, by-role behavioural questions in SQL.
The tool is vendored at players/crewborg/tools/event-warehouse/
(two packages: crewrift-event-warehouse + its crewrift-event-reporter), run via uv — no global
install. Its own README is the
full reference; this skill is the operator's path.
Use it when a survey/A-B/diagnose question needs the actual behaviour: kill conversion, stalking,
being-trailed, task abandonment, vote correctness, suss accuracy. Don't use it for a quick
batch overview — that's the fast crewrift-survey.
Build it
build_warehouse.py is the one-shot: episodes → report_request.json → built warehouse, with the
version-skew check baked in.
B=players/crewborg/skills/crewrift-event-warehouse/scripts/build_warehouse.py
uv run python "$B" --episodes /tmp/eps --out /tmp/wh --expand-replay /tmp/expand-<commit>
uv run python "$B" --xreq xreq_A --xreq xreq_B --round round_9 --out /tmp/wh --expand-replay /tmp/expand-<commit>
uv run python "$B" --episode <uuid> --episode ereq_xyz --out /tmp/wh --expand-replay /tmp/expand-<commit>
uv run python "$B" --policy crewborg -n 200 --out /tmp/wh --expand-replay /tmp/expand-<commit>
It prints the manifest summary and flags trace_warning episodes (the #1 failure, below).
Streaming (the default for fresh experience requests): don't wait for the
xreq to finish — stream_eval.py overlaps fetch + build, so the warehouse is
ready as the last episode ends:
S=players/crewborg/skills/crewrift-event-warehouse/scripts/stream_eval.py
uv run python "$S" --xreq xreq_A [--xreq xreq_B] --out /tmp/wh --expand-replay /tmp/expand-<commit>
Builds are incremental: episodes already in the manifest as ok are never
re-expanded, so repeated builds over a growing episode dir only pay for the
new ones (this also makes re-running build_warehouse.py after a partial
failure cheap). A version-skewed --expand-replay is warned after the FIRST
batch, minutes in.
⚠️ The one hard part — expand_replay version coupling
The extractor re-steps the sim and hash-checks every tick; on a version mismatch it aborts
mid-replay, leaving only metadata + a trace_warning — so a skewed build looks like sparse
output, not an error. The binary must be built from the exact crewrift commit the arena ran when
it recorded the replays — not master (which drifts ahead and hash-fails).
Vendoring here is what makes this tractable: the expander source is in this same repo
(tools/expand_replay.nim). To get the right binary:
- Find the arena's deployed version:
coworld episodes --round <id> --json → each episode's
coworld_version (e.g. crewrift:0.1.54).
- Build
expand_replay from tools/expand_replay.nim at that commit (e.g. 0.1.54 ⇒ 42fed21):
nim c -d:release -d:useMalloc --opt:speed --out:/tmp/expand-42fed21 tools/expand_replay.nim
(after nimby --global sync nimby.lock).
- Verify: it should exit 0 with
trace_complete:true on a real replay from that round.
- Pass it as
--expand-replay /tmp/expand-42fed21.
Always check the trace_warning count first — if it's more than a trickle, fix the binary before
trusting any query. See the README's "version coupling" section for the full recipe.
Query it
Two ways. The dataset is a star schema — an events fact table + an episode_players dimension:
cd players/crewborg/tools/event-warehouse/crewrift-event-warehouse
uv run crewrift-event-warehouse serve --out /tmp/wh
import duckdb; con = duckdb.connect()
con.execute("CREATE VIEW events AS SELECT * FROM read_parquet('/tmp/wh/events/**/*.parquet', hive_partitioning=true)")
con.execute("CREATE VIEW episode_players AS SELECT * FROM read_parquet('/tmp/wh/episode_players.parquet')")
- How to read the event table — every event
key and its value JSON fields, and the
slot-join rules: references/event-catalog.md. Read this before
writing SQL; it's the difference between a correct query and a guess.
- Recipes for the real questions — rooms/routes, who-trails-whom, am I getting trailed,
near-a-crewmate-without-killing, task abandonment by room, vote correctness, suss-rate, role
outcomes:
references/recipes.md.
The two rules that keep queries correct (full detail in the catalog):
slot >= 0 for any player aggregate — global rows (slot = -1: proximity_interval,
map_geometry, phase, metadata) have NULL identity.- Embedded slots in
value (a victim, a follow target) are raw ints — self-join
episode_players on (episode_id, that_slot) to get that party's role/policy. The field name
varies per key ($.victim_slot, $.target, $.target_slot, $.player_a/b) — match it exactly.
Chat & LLM-enabled extraction
Chat is the only free text the warehouse holds — the chat event's value.text (voting-phase
messages, keyed by speaker). Pull it raw:
SELECT episode_id, ts, policy_name, role, json_extract_string(value,'$.text') AS said
FROM events WHERE key='chat' AND slot>=0 ORDER BY episode_id, ts;
Raw text is hard to aggregate, so the warehouse can be LLM-enabled to interpret chat and emit new
structured events. The shipped example is suss — "who is each message accusing?":
cd players/crewborg/tools/event-warehouse/crewrift-event-warehouse
uv run crewrift-event-warehouse suss --out /tmp/wh
It labels each chat with its suss target (Bedrock Haiku), resolves that to the target's
slot/role/policy per episode, and writes a native events/key=chat_suss partition — which is what
makes suss-rate ("when crewborg accuses someone, is it really an imposter?") queryable
(recipes.md #6). It's idempotent and cached (distinct texts only).
Adding your own LLM chat extraction (the suss pattern)
suss.py is the template for any "interpret chat → new event" extension — sentiment,
claims/alibis, vouching, defenses, naming a room. Copy its four steps:
- Distinct texts —
SELECT DISTINCT json_extract_string(value,'$.text') over the chat
partition. Chat is heavily templated, so thousands of events collapse to ~hundreds of strings.
- Classify once, cache — batch the distinct texts through Bedrock (temperature 0, JSON-array
out) and cache to a
<key>_cache.json, so re-runs are cheap (--refresh to redo).
- Resolve to identity (no LLM) — if your label names a player, map color→slot from
player_joined labels (red(Name)), then slot→role/policy via episode_players. A pure join.
- Write a partition — keyed by the speaker (
slot), your labels in value, to
events/key=<your_key>/…parquet using EVENTS_SCHEMA. It's then queryable like any other event.
Code to copy: crewrift_event_warehouse/suss.py
(distinct_texts → classify_texts → episode_color_maps/slot_identity → build_suss_partition).
Extend it (objective events)
New behavioural questions are usually a new query, not new code. For new objective (non-chat)
events — a new proximity/visibility/movement pattern — the extractor is the place: raw events in
tools/expand_replay.nim, derived events in the reporter's analysis.py.
See also
crewrift-survey — the fast batch pass that flags what to deep-dive here.coworld-episode-artifacts — pulls the episodes (with replays) the warehouse builds from.crewrift-replays.md — the single-episode expand_replay
path + the same version-coupling caveat.
