The venture spine: one query, and how it stays fresh

The typed graph holds the studio's spine

Two things were missing once the edges existed: a way to ASK for a whole neighbourhood without hand-writing a recursive CTE, and something to keep the edges from rotting. This document covers both.

---

1. POST /api/brain/graph/spine

Everything about one venture (or portfolio, repo, person) in one call, grouped by role, with a true total next to a capped sample.

GET /api/brain/graph/spine?namespace=&entity=&depth=&perGroup=&window=&roles= takes the same parameters, so it is curl-able. MCP tool: graph_spine.

Request

field	type	default	meaning
namespace	string	—	brain (required)
entity	string	—	entity NAME (exact, then case-insensitive) or id (required)
depth	int	2 (max 4)	hops. 2 = venture→repo→code/doc/activity. Use 3 for a portfolio.
hubs	string[]	repo,venture,feed	the only entity types expanded PAST the first hop
roles	string[]	all	restrict which groups come back
perGroup	int	10 (max 200)	cap per group. Never changes total.
window	string	—	relative bound: 24h, 7d, 2w, 3m
since/until	RFC3339	—	explicit bound (overrides window for since)
timeRoles	string[]	activity,meeting,channel,feed	the roles the window applies to; ["*"] = all

Two design choices worth knowing:

• hubs is what stops the walk exploding. Past hop 1 only structural hub types are expanded. portfolio is deliberately NOT a default hub: expanding it mid-walk lets a venture climb to its portfolio and come back down with its SIBLINGS' repos, presented as its own. Pass it explicitly if you want that.
• The window only binds event-bearing roles. "SADA last week" must still list SADA's repos — they are its repos, not repos created last week. The response echoes window.appliedTo so the semantics are never implicit.

Response

total is the real population; shown/capped describe the sample. This is the lesson the Graph Explorer taught the hard way — its legend showed the SAMPLING QUOTA (17) as if it were the population. A capped list must never be able to masquerade as a total, so both numbers are always present.

A role that was explicitly requested and came back empty is returned with total: 0 rather than omitted: "no meetings last week" and "I forgot to look for meetings" are different answers.

An unknown entity returns 404 with a candidates list (substring matches, portfolios and ventures first).

Worked example

Add "window":"7d" and activity drops 44 → 7 while repo and person stay put, which is the point.

---

2. Keeping it fresh — where each job runs

job	what it does	where it runs	cadence
flowos-sync.py	FlowOS prod → brain memories (HTTPS)	/opt/flowos-brain-sync, Proxmox 159.195.203.241	systemd timer, 1 min
flowos-analytics.py + flowos-activity-rollups.py	DuckDB analytics plane → rollup memories (HTTPS)	/opt/cabrain-analytics, same host	systemd timer, 1 h
code-index.py	repomix code/doc chunks (HTTPS)	/opt/cabrain-code-index, same host	systemd timer, 30 min
graph-sync-run.sh	the graph builders: nodes, edges, repo links, spine, rollup relink	the Coder workspace (~/.cabrain-graph-sync)	supervised loop, 10 min

Why the graph builders are NOT on the Proxmox box

They INSERT into entities / entity_edges, and there is no HTTP surface for graph writes, so they need a direct brain DSN. Everything else on that host writes over HTTPS and needs none.

An earlier attempt to move them there hit password authentication failed for user cabrain against 10.10.10.30:5432 and concluded the credentials were wrong. They were not — 10.10.10.30 is the wrong database. It is Proxmox LXC 102 togo-db, i.e. FlowOS PROD (onestudio_hub), which of course has no cabrain role. The brain lives in the pg container on the stack_stacknet docker network inside Docker Desktop on the operator's machine (172.18.0.22 internally, published to the workspace as host.docker.internal:55432, egress 196.137.11.160). From the Proxmox host, ports 5432, 55432 and 8080 on that egress address are all closed — measured, not assumed. No credential fixes a route that does not exist.

The Coder workspace is the only host that reaches both: the brain through the Docker Desktop gateway, and FlowOS prod through an SSH tunnel to the Proxmox box which graph-sync-run.sh opens for itself.

The workspace has no systemd and no cron (PID 1 is the coder agent), so the timer is a flock-guarded supervised loop (graph-sync-loop.sh). Start it with:

Config (DSNs, token, tunnel target, interval) is ~/.cabrain-graph-sync/env, mode 600. No secret is in the repo — the scripts read the environment only.

If the graph ever needs to run somewhere durable and unattended, the real fix is an HTTP graph-write endpoint so the builders can go over HTTPS like everything else; then they can move to /opt next to the other three timers.

The rollup relink step

/api/brain/retain does not update in place — a repeat sourceRef SUPERSEDES, inserting a row with a NEW id while memory_entities still points at the old one. The rollup job on the Proxmox host has no brain DSN, so every rollup it refreshes comes back orphaned from the entity graph (its last run orphaned 26). flowos-rollup-relink.py runs at the end of every graph pass and re-attaches them from each rollup's own metadata — no FlowOS access needed.

---

3. Two data bugs this work fixed at the source

Future-stamped rollups. flowos-activity-rollups.py stamped validAt with the period END, so the week in progress landed in the future (48 rollups at 2026-08-02 while the run was on 2026-07-31). Every recall bounded by until=now silently excluded the newest week, and an agent asked about "last week" concluded the brain had no activity since May. end_ts() now clamps to now. Past periods are unaffected.

English-only rollups. Recall is vector + BM25, so an Arabic question scored highest against the Arabic text sitting inside indexed SOURCE CODE and the rollups never surfaced. The weekly rollup:leaderboard:studio:<isoweek> memory is bilingual (English + Arabic), carries the FULL ranking rather than a top-5, and names the zero-commit roster members explicitly — dropping them turns a real "least active" answer into "no data". Only the newest week is labelled "last week / اخر اسبوع"; older ones say "a past week", because 13 near-identical summaries otherwise score within 0.02 of each other and "last week" lands on a random one.