Garry's Opinionated OpenClaw/Hermes Agent Brain
Postgres features map
This map lists every Postgres-specific feature gbrain uses: pgvector, full-text search, pg_trgm, JSONB, recursive CTEs, triggers, LISTEN/NOTIFY, advisory and row locks, the SKIP LOCKED job queue, RLS and roles, transactions and isolation, and runtime features. For each feature it gives the file paths, the behavior that depends on it, and candidate embedded Rust replacements with tradeoffs and parity risks.
- Reference:
garrytan/gbrainat commit8bbfd0b5e8(master, 2026-11-16). Everypath:linecitation is relative to the gbrain repo root at that commit. - Method: two research passes (query and data features; concurrency, access control and runtime features).
rgcounts are line matches acrosssrc/, not file counts. Items a pass could not verify say so in place.
Contents
| Section | Covers |
|---|---|
| Query and data features | pgvector, FTS (tsvector, ts_rank, CJK), pg_trgm, JSONB, recursive CTEs, upserts, batch patterns, window functions, timestamps, hybrid RRF fusion |
| Concurrency, access control and runtime features | triggers, LISTEN/NOTIFY, advisory locks, row locks, SKIP LOCKED queue, RLS and roles, transactions, timeouts, cancellation, pooling, introspection, the multi-process model |
Feature inventory at a glance
| Feature | Main sites | What depends on it | Leading Rust candidate | Main parity risk |
|---|---|---|---|---|
pgvector cosine (<=>), vector/halfvec, HNSW |
search/vector-statement.ts, search/embedding-column.ts, content_chunks, takes, facts |
vector and hybrid search, takes and facts recall | exact f32 scan first; HNSW crate (usearch, hnsw_rs) or sqlite-vec later | ANN top-k differs at the margin; f16 rounding; dimension reconciliation at connect |
tsvector + websearch_to_tsquery + ts_rank |
search/keyword-statement.ts, fts-language.ts, trigger on content_chunks/pages |
keyword search, phrase factor, hybrid inputs | SQLite FTS5 or tantivy | BM25 is not ts_rank; Snowball stems and stop words differ; tsvector size caps |
| CJK ILIKE fallback | engine-sql/cjk-search.ts, search/cjk-keyword-sql.ts |
keyword search on CJK text | substring scan with a deadline, or an n-gram tokenizer | case folding, LENGTH() counting, 2,000-candidate cap |
pg_trgm %, similarity() |
engine-sql/pages.ts (findByTitleFuzzy), entities/resolve.ts |
fuzzy title and entity resolution | port the pg_trgm trigram algorithm in Rust | padding and threshold (0.3) rules must match exactly |
JSONB (@>, ->>, jsonb_to_recordset, jsonb_set) |
frontmatter filters, batch writes, config layers, job data |
where filters, batch link and timeline writes |
SQLite JSON1 or serde_json in code | number vs string equality, key order, containment semantics |
WITH RECURSIVE |
12 files (table below) | graph traversal, job trees, config layers, chunk occurrences | Rust BFS in code, or SQLite recursive CTEs | row caps and tie order before the cap |
| plpgsql triggers (about 25) | src/schema.sql |
search vectors, revision counters, projection queues, fail-closed writer guards | explicit write-path steps in Rust; a few SQLite triggers | a missed trigger silently breaks search or purge |
| LISTEN/NOTIFY | trigger notify_minion_job_change, minions/sync-queue-wake.ts |
wakes idle job workers | in-process channel; PRAGMA data_version or socket across processes |
none if the poll backstop stays |
| advisory locks (key 42 + about 15 key families) | schema-migration-lock.ts, postgres-engine/init-schema-lock.ts, engine-sql/facts.ts, page-body/* |
migration safety, fact dedup, quotas, derivation scope | file lock + single writer; keyed in-process mutexes | cross-process semantics; stale-holder reaping |
row locks, SKIP LOCKED |
minions/queue.ts, lockPageKeys (about 76 files), db-lock.ts |
job exactly-once, page write serialization | single writer transaction; lease columns with fenced updates | claim_generation and lock_token fencing must stay exact |
| RLS, roles | DO block in schema.sql (BYPASSRLS only); no policies or grants |
hosted Supabase deny-by-default | application authorization only | none for the contract; doctor checks become not applicable |
| transactions, isolation, timeouts, cancellation | postgres-engine/* |
consistent reads, 8 s keyword bound, abort | SQLite WAL + BEGIN IMMEDIATE, sqlite3_interrupt, deadlines |
same refusals (KeywordSkippedAfterTimeoutError) under deadline |
Every recursive CTE site
The query pass covered the graph walks in depth. This is the full list of WITH RECURSIVE sites in src/ (excluding generated files):
| Site | What it walks |
|---|---|
src/core/engine-sql/links.ts:673 |
traverseGraph BFS over links (bfsWalkCtes) |
src/core/engine-sql/links.ts:784 |
traversePathsDetailed edge walk with truncation flag |
src/core/engine-sql/graph-rank.ts:123 |
readable-page graph for entity ranking |
src/core/search/read-enrichment.ts:54 |
relationalFanout walk with visited and path arrays, depth clamped to 3 |
src/core/minions/queue.ts:1061 |
descendant jobs of a cancelled job |
src/core/minions/legacy-cancel.ts:42 |
descendant jobs for legacy cancel |
src/core/minions/authorize-legacy.ts:57 |
connected job set for legacy authorization |
src/core/minions/job-insight.ts:204 |
job tree with owner client id |
src/core/persistence/terminal-classify.ts:489 |
persistence request lineage |
src/core/persistence/capacity-forecast.ts:118 |
skip-scan walk over persistence sequences |
src/core/source-config-sql.ts:15 |
layered source config merge (root_layers) |
src/core/page-body/projection.ts:66 |
loose index scan over chunk occurrence ids (STALE_CHUNKS_SQL) |
src/core/onboard/checks.ts:667 |
loose index scan over (source_id, type) pairs |
Several of these (the last two, and capacity-forecast.ts) are skip-scan tricks for the Postgres planner. In Rust they become a plain DISTINCT or an index range scan.
Query and data features
Scope: pgvector, full-text search, pg_trgm, JSONB, recursive CTEs, and the other Postgres SQL features the embedded store must emulate. Source: garrytan/gbrain at commit 8bbfd0b5e8. Line numbers are from reads in this pass. Counts from rg are line matches across src/, not file counts, and are ballpark only.
Summary of what matters for the Rust rewrite
- Keyword search is Postgres
websearch_to_tsquery+ts_rank/ts_rank_cdover storedtsvectorcolumns, with weights A/B/C, a Snowball English config, and a CJK ILIKE fallback. Ranking is the hardest parity item. - Vector search is pgvector cosine (
<=>) overvector(N)/halfvec(N)columns, HNSW-indexed, withhnsw.ef_searchandhnsw.iterative_scantuned per query. An exact (sequential) path also exists and is used as fallback. - Hybrid search fuses keyword and vector page lists in JavaScript with RRF (k = 60). That code is portable as-is; only its inputs need parity.
- The graph (links) is traversed with
WITH RECURSIVEBFS using arrayvisitedsets, row caps, andDISTINCT ON/window dedup. A Rust engine can do this in code, but must keep the same caps and ordering. - Several correctness-relevant behaviours live in the database: NULLS NOT DISTINCT unique keys, partial indexes, generated columns,
ON CONFLICT, text truncation to tsvector caps, and statement timeouts. - The planner-specific tuning (
OFFSET 0fences,MATERIALIZEDCTEs, planner-stats row-delta ANALYZE) exists only to steer the Postgres/PGLite planner. Most of it has no meaning in a new engine, but the observable results (ordering, caps, dedup) must be kept.
1. pgvector: types, dimensions, operators, indexes
1.1 Extension and column types
| Item | Where | Notes |
|---|---|---|
CREATE EXTENSION vector, pg_trgm, pgcrypto |
src/schema.sql:3-6 |
pgcrypto only for gen_random_uuid on old PG. |
content_chunks.embedding vector(1536), default model text-embedding-3-large |
src/schema.sql:360-361 |
Fresh-install DDL. The configured default model is voyage:voyage-4, 1024 dims (src/core/ai/defaults.ts:15-16). Dims are reconciled at runtime, see 1.4. |
content_chunks.embedding_image vector(1024) (Voyage multimodal) |
src/schema.sql:400 |
Partial HNSW index idx_chunks_embedding_image (schema.sql:412-415). |
content_chunks.embedding_multimodal vector(1024) |
src/schema.sql:403 |
|
facts.embedding is vector or halfvec(N) |
src/core/engine-sql/facts.ts:37-46, 232 |
Cast chosen by probing the live column type (::vector vs ::halfvec). |
takes.embedding with HNSW idx_takes_embedding_hnsw |
src/core/schema-migrations/v142-takes-embedding-dimension-matches-config.ts:18-46 |
Dropped and nulled on dim change. |
Column type allowlist vector or halfvec |
src/core/search/embedding-column.ts:82, 221, 408-409, 565-571 |
Builtin embedding descriptor; a dimensions placeholder of 1536 is hardcoded at 565 and says it is unused in the cast. |
Write cast '<literal>'::vector or ::halfvec(N) |
src/core/search/embedding-column.ts:249-281 |
Bound as a text literal (toPgVectorLiteral), cast in SQL. |
vectorLiteralSql, toPgVectorLiteral |
src/core/engine-sql/facts.ts:105, src/core/pglite-engine.ts:133 |
Vectors are serialized as text, so no binary protocol. |
Rust note: vectors are Float32Array in JS. A Rust store must hold f32 (and f16 for halfvec) and must round-trip the same bytes the text literal carries, or cosine values drift slightly.
1.2 Distance operators
| Use | Where |
|---|---|
Cosine distance cc.embedding <=> $q, score = 1 - distance |
src/core/search/vector-statement.ts:404-410 (raw_score), :417-423 (exact scan variant ORDER BY distance) |
Index-walk ann CTE ORDER BY cc.col <=> q LIMIT n*overfetch |
src/core/search/vector-statement.ts:415-447 (indexWalkCandidates) |
Joined candidate CTE hnsw_candidates |
src/core/search/vector-statement.ts:401-411 |
Scope scan CTE (scope_scan AS MATERIALIZED) |
src/core/search/vector-statement.ts:447 onward |
Facts vector search (embedding <=>) |
src/core/engine-sql/facts.ts:585-620 |
Cosine rescore in SQL: cosineByChunkIdsStatement |
src/core/search/cosine-rescore.ts:1-20, src/core/engine-sql/chunks.ts |
Only <=> (cosine) is used. No <-> (L2) or <#> (inner product) is used in src/. vector_cosine_ops is the only opclass in DDL (src/core/vector-index.ts:24, src/schema.sql:408).
Behavioural note: a stored chunk with zero norm or a different dimension scores 0 in the JS rescore path (cosine-rescore.ts:1-20). The SQL path must match that.
1.3 HNSW indexes and parameters
| Item | Where | Value |
|---|---|---|
Index DDL hnsw (embedding vector_cosine_ops) |
src/core/vector-index.ts:24, src/schema.sql:408 |
Default HNSW build params (m=16, ef_construction=64 in pgvector). Not overridden in DDL. |
Max dims for HNSW, vector |
src/core/vector-index.ts:20-21, 27-35 |
2000. Above this, no index: exact scan only. |
Max dims for HNSW, halfvec |
src/core/vector-index.ts:21, 35 |
4000. |
hnswIndexExpected(type, dims) |
src/core/vector-index.ts:57-58 |
Gates whether an index is built and whether the planner-check reports missing index. |
hnsw.ef_search default |
src/core/vector-index.ts:124-125 |
40 (pgvector default). Ceiling 1000 (HNSW_EF_SEARCH_MAX). |
hnsw.ef_search per query |
src/core/vector-index.ts:136-140 (hnswEfSearchFor(n) = min(max(n, 40), 1000)) |
Sized to the candidate window. |
hnsw.iterative_scan |
src/core/search/hnsw-iterative-scan.ts:1-30 |
Modes relaxed_order (default), strict_order, off. Env GBRAIN_HNSW_ITERATIVE_SCAN or config search.hnsw_iterative_scan. |
hnsw.max_scan_tuples |
src/core/search/vector-settings.ts:16, 31-32 |
20000 when iterative. |
| Settings applied per statement | src/core/search/vector-settings.ts:16-33; src/core/postgres-engine/search-settings.ts:53-57 (set_config(..., true) in one transaction) |
Transaction-local. |
| Requires pgvector >= 0.8 for iterative scan | src/core/search/vector-settings.ts:~36-38 (supportsHnswIterativeScan) |
Version check on the extension. |
Index build memory: maintenance_work_mem set from row count (PGLite) |
src/core/vector-index.ts:66-120 (PGLITE_HNSW_GRAPH_BUDGET_BYTES = 1536 MB) |
PGLite holds the whole graph in memory during build. |
Concurrent rebuild CREATE INDEX CONCURRENTLY + temp name + swap |
src/core/vector-index.ts:250-303 |
Zombie-index cleanup at :169-243. |
Index bloat/prewarm (shared_buffers advice) |
src/core/vector-prewarm.ts:78-106, 152 |
Reads pg_relation_size for hnsw indexes. |
Parity note: HNSW is approximate. The results of a Rust HNSW crate will not equal pgvector's. The code already has an exact fallback (src/core/search/exact-scan.ts, src/core/search/exact-lookup.ts, and exact flag in vector-statement.ts:401) and a recall-aware hasMore witness. Tests that compare ordering should compare against the exact path, not against pgvector's output.
1.4 Dimension changes, reindex, and model switches
| Item | Where |
|---|---|
Dim check at connect/doctor: parse vector(N) / halfvec(N) from format_type |
src/core/embedding-dim-check.ts:142-157, 587-594 |
Recipe on mismatch: drop index, UPDATE ... SET embedding = NULL, ALTER COLUMN TYPE vector(N), recreate index |
src/core/embedding-dim-check.ts:252-272, :620-660 (halfvec / vector swap is "same-dim cast, lossless") |
| Migration to match takes dim to config | src/core/schema-migrations/v142-takes-embedding-dimension-matches-config.ts:18-46 |
| Embedding migration (re-embed) | src/core/embedding-migration.ts (60 matches), src/core/embedding-migration-*.ts |
Mismatch error from pgvector expected N dimensions, not M |
src/core/embedding-dim-check.ts:786 |
Embedded replacement: the current design relies on ALTER COLUMN TYPE and index re-creation. In a Rust store the column is just a blob with a dimension field per table or per index, so dim-change is a rebuild. Parity risk: the "keep embeddings, change dims" recipe offered to users (embedding-dim-check.ts:178) is only valid if the new store can read old vectors.
1.5 Embedding model and dimension sources
| Item | Where |
|---|---|
Default model voyage:voyage-4, dims 1024 |
src/core/ai/defaults.ts:15-18 |
Fallback text-embedding-3-large |
src/core/embedding.ts:115 |
| Per-model native dimensions table | src/core/ai/types.ts:42-57 |
Config embedding_dimensions, embedding_model |
src/core/ai/types.ts:495-499, src/core/ai/engine-gateway.ts:76-78 |
| Embedding guard / cost | src/core/ai/embedding-guard.ts |
Chunk-level model column content_chunks.model default text-embedding-3-large |
src/schema.sql:361 |
Multiple models: chunks carry model and embedding_model / embedding_input_hash (schema.sql:361, 380). Facts have embedding_model with partial index idx_facts_embedding_model (src/core/schema-migrations/v237-facts-question-recall-indexes.ts:40). Search filters by model where relevant (facts.ts:605). Only one vector column per table is queried at a time (embedding-column.ts descriptor picks the column; modality filter cc.modality = 'image' at vector-statement.ts:377).
1.6 Vector result shaping (not SQL, but affects parity)
- Per-page pooling and candidate windows:
src/core/search/vector-pool.ts(146 lines),src/core/search/vector-statement.ts:404-510(ORDER BY score DESC NULLS LAST, page_id ASC, chunk_id ASC, line ~508). - Overfetch factor for index walk:
vector-statement.ts:~418;hnsw_candidateslimitinnerLimitParam * overfetch.
Rust embedded replacement candidates for vectors:
| Candidate | Fit | Tradeoffs / parity risk |
|---|---|---|
sqlite-vec (vec0 virtual table, vec_distance_cosine) on SQLite (rusqlite) |
Exact kNN is straightforward. Keeps one embedded DB file. | Exact brute-force by default; ANN indexing status must be verified for the version pinned. Distance formula must match pgvector cosine (1 - cos), and f32 vs f16 storage differs. |
| usearch (C++ with Rust bindings), hnsw_rs, instant-distance | HNSW with cosine metric. | Approximate; recall differs from pgvector at the same ef. Needs its own persistence and must be kept consistent with rows (delete/update semantics, tombstones). Filtering after ANN (pgvector iterative_scan behaviour) must be reimplemented. |
| hora | HNSW / IVF-like. Less active. | Maintenance risk. |
| LanceDB / lance | Columnar vector store with ANN indexes (IVF-PQ, HNSW). | Heavier dependency; async API; filter-with-ANN semantics differ; no SQL. |
| DuckDB with VSS extension | SQL-compatible, HNSW via extension. | Extension stability and HNSW persistence concerns; not in-process-light. |
| Exact brute-force over f32 arrays in Rust (SIMD) | Simplest, exact. | Fine for small brains; scales linearly. Parity-perfect with exact pgvector ordering (up to float rounding). |
halfvec replacement: store f16 (half crate) |
Keeps disk size same as halfvec. | Rounding differs from pgvector halfvec cosine; rank ties may flip. |
Recommended parity test: compare the top-k of the Rust path against vector_cosine_ops exact order on a fixture (the exact branch already exists as a reference).
2. Full-text search
2.1 Columns, indexes, and triggers
| Object | Where | Notes |
|---|---|---|
pages.search_vector tsvector |
src/schema.sql:1183 (ALTER TABLE pages ADD COLUMN IF NOT EXISTS search_vector tsvector) |
Page-grain keyword vector: title (A), timeline (C), timeline_entries (C). |
idx_pages_search GIN on pages.search_vector |
src/schema.sql:1185 |
|
idx_pages_title_search GIN on ts_filter(search_vector, '{a}') |
src/schema.sql:1187 (also pglite-schema.generated.ts:662) |
Expression GIN, weight-A slice, used for title-only matches. ts_filter is a Postgres function (weight filter), not user-defined here. |
update_page_search_vector() trigger function |
src/schema.sql:1196-1224 (also generated body in src/core/fts-language.ts:197-223) |
title as weight A; first 65,536 chars of timeline (weight C); first 32,768 chars of joined timeline_entries (weight C). Comment at :1190-1194 says the cap avoids the 1 MB tsvector limit. |
Trigger trg_pages_search_vector |
src/schema.sql:1230-1234; re-created by src/core/page-state/projection-schema.ts (trg_pages_search_vector BEFORE INSERT OR UPDATE OF ..., schema.sql:2685) |
Column list in UPDATE OF depends on projection. |
content_chunks.search_vector TSVECTOR |
src/schema.sql:394 |
Chunk-grain. |
idx_chunks_search_vector GIN |
src/schema.sql:418 |
|
update_chunk_search_vector() trigger (`NEW.search_vector := A(doc_comment,symbol_name_qualified) |
B(chunk_text)`) | |
gbrain_fts_input(text) (CJK/ASCII boundary spacer, regex) |
src/schema.sql:431-437; source src/core/fts-language.ts:143-150 |
regexp_replace twice on CJK class [一-鿿-ゟ゠-ヿ가-] vs [A-Za-z0-9]. Applied before every to_tsvector. |
factsFtsDocument / idx_facts_fts expression GIN on facts |
src/core/fts-language.ts:120-128 (`to_tsvector(''::regconfig, fact |
Trigger ordering and visibility: the chunk trigger does not re-run on embedding-only updates because it fires on UPDATE OF the text columns (comment at schema.sql:439-443). Rust must mirror: keyword vector recomputed only when chunk_text, doc_comment, or symbol_name_qualified changes.
2.2 Text search configuration
| Item | Where |
|---|---|
Config name from env GBRAIN_FTS_LANGUAGE, default english |
src/core/fts-language.ts:27, 55-75 |
Validation ^[a-z][a-z0-9_]*$, used for SQL interpolation |
src/core/fts-language.ts:27, 64-71 |
Rewrite schema literal to_tsvector('english', to chosen config |
src/core/fts-language.ts:105-111 |
Per-language reindex with marker key fts.reindex_in_progress |
src/core/fts-language.ts:38; command src/commands/reindex-search-vector.ts (29 matches) |
| Migration that stamped trigger functions | src/core/schema-migrations/v123-configurable-fts-language.ts |
| Page keyword vector SQL builder | src/core/fts-language.ts:172-174 (pageSearchVectorSql) |
Doc: multi-language FTS guide mentions unaccent + Portuguese stemmer config pt_br |
src/core/fts-language.ts:13-16 (refers to docs/guides/multi-language-fts.md) |
Important: the default is English Snowball stemming and English stop words. Changing GBRAIN_FTS_LANGUAGE changes stemming for all rows and requires reindex. An embedded Rust engine needs its own analyzer config and the same reindex trigger semantics.
2.3 Query functions and ranking
| Function | Use | Where |
|---|---|---|
websearch_to_tsquery(cfg, q) |
Chunk keyword match (ASCII path) | src/core/search/keyword-statement.ts:70-72; src/core/search/title-statement.ts:55-68 (boundWebsearchQuery, gbrain_fts_input wrapped) |
ts_rank(search_vector, tsq) |
Chunk score, times a phrase factor | src/core/search/keyword-statement.ts:74 |
ts_rank_cd (cover density) |
Title match ranking | src/core/search/title-statement.ts:68 |
ts_filter(search_vector, '{a}') |
Restrict to weight A (title) | src/core/search/title-statement.ts:67-68, src/core/engine-sql/titles.ts:36 |
phraseto_tsquery phrase boost (x2 factor, KEYWORD_PHRASE_FACTOR = 2) |
CASE WHEN vector @@ phraseto_tsquery(cfg, q) THEN 2 ELSE 1 |
src/core/search/sql-ranking.ts:477-483; used when opts.keywordPhrase set (keyword-statement.ts:74) |
Score factor per source/slug (buildSourceFactorCase) |
((rank) * factor)::float8 |
src/core/search/keyword-statement.ts:80-82 |
Title OR variants (given-name variants #7471) |
`(websearch_to_tsquery(...) | |
| Strict match, then OR fallback when strict is short | src/core/search/keyword-statement.ts (strict only for count), src/core/search/keyword-or-narrow.ts, keyword-or-rarity.ts |
The keyword arm of hybrid runs OR fallback, direct callers strict. |
Dedup to best chunk per page: SELECT DISTINCT ON (page_id) ... ORDER BY page_id, score DESC, chunk_id ASC |
src/core/search/keyword-statement.ts:150 |
Keyset pagination after (score DESC, page_id ASC) at :152-155; score is float8 so cursors round-trip. |
| Keyword count capped at 10,000 + 1 | src/core/search/keyword-statement.ts:35, 108-115 |
|
ts_headline |
not used (0 matches) | Snippets come from chunk text. |
Ranking: ts_rank default normalization 0; weights D/C/B/A = 0.1/0.2/0.4/1.0 are Postgres defaults. The code does not pass a custom weight array (not found in src/). A Rust engine must compute the same per-lexeme term frequency and weight arithmetic to match ordering; BM25 will not match it numerically.
Hybrid (src/core/search/hybrid.ts, hybrid/*): keyword rank is only used as the list order in RRF; the raw ts_rank value does not reach fusion, except as a sort key inside each arm (keyword-statement.ts:154). So a BM25 engine that preserves the list order produces the same RRF fused ranking, but the per-page ranking within keyword arm and the score numbers exposed in score_details change.
2.4 Tokenization and stemming
- Postgres parser + Snowball English stemmer + English stop words (default config). Behaviour of
to_tsvector('english', ...): lowercase, stop-word removal, Porter2-style stemming, numbers and emails tokenized by the default parser. Exact token boundaries differ from any Rust tokenizer. Example differences: hyphenated words, URLs, andRedis7-style mixed alnum (the code splits CJK/ASCII boundaries withgbrain_fts_inputprecisely because the Postgres parser keeps升级PostgreSQL17as one lexeme, seefts-language.ts:132-142). - Caps: tsvector lexeme strings max 1 MB; page vector truncated to 65,536 + 32,768 characters (
fts-language.ts:157-162). Text beyond the cap is not keyword-searchable (chunks still are). Parity-relevant: a Rust engine that indexes the full timeline would return extra keyword hits. - Sanitization: page timeline is sanitized before indexing ("never the raw column ... private or withdrawn fence text",
fts-language.ts:166-171).
2.5 CJK keyword search
| Item | Where |
|---|---|
Detect CJK: CJK_SLUG_CHARS = 一-鿿 -ゟ ゠-ヿ 가- |
src/core/cjk.ts:19-21, 74-76 |
| CJK terms split on whitespace, Korean particle stripped | src/core/cjk.ts:207-210, 248-250 |
CJK chunk predicate: every term as LIKE (caseless) or ILIKE, ESCAPE '\\', joined with AND |
src/core/search/keyword-statement.ts:56-63; src/core/search/cjk-keyword-sql.ts (header 1-20) |
CJK ranking: term frequency (len - len(replace(text, term, ''))) / len(term), sum across terms, plus contiguous-phrase bonus and position() tiebreak |
src/core/search/keyword-statement.ts:64-67; src/core/search/cjk-keyword-sql.ts:1-20 |
CJK full path, OR fallback when strict falls short (orFallback, #6043) |
src/core/engine-sql/cjk-search.ts:15-18, 91-94 |
Candidate cap CJK_CANDIDATE_CAP = 2000 (capped unordered LIKE scan) |
src/core/engine-sql/cjk-search.ts:55 |
PGLite capped path after PGLITE_CJK_CAPPED_CHUNKS = 50,000 (planner reltuples) |
src/core/engine-sql/cjk-search.ts:56, 150-153, 169-178 |
Deadline split: 2/3 full scoring, 1/3 capped retry; statement_timeout via set_config |
src/core/engine-sql/cjk-search.ts:112-148, 182-188 |
| Doc: "ILIKE scan ... not index-accelerated; install pgroonga or zhparser" | src/core/engine-sql/cjk-search.ts:35-38 |
Key facts: the CJK path does not use tsvector at all. It is a substring scan. Ranking is term-frequency arithmetic, which is reproducible exactly in Rust (use char-count semantics; note LENGTH() on text counts characters, not bytes). ILIKE case-folding on CJK characters is a no-op, but on mixed Latin terms it is locale/ASCII-based in Postgres; SQLite's LIKE is ASCII case-insensitive only. A Rust engine must lowercase per Postgres ILIKE rules or match only ASCII folding, and check that term.toLowerCase()===term.toUpperCase() routing (keyword-statement.ts:61) is preserved.
Rust candidates for CJK:
| Candidate | Fit | Risk |
|---|---|---|
FTS5 trigram tokenizer (SQLite) |
Substring match over any script; works for CJK (3-grams). | Trigram needs >=3 chars; 1-2 char CJK terms (common in Chinese/Japanese) would not match via the index; must fall back to LIKE. Case-folding only ASCII. |
tantivy with lindera (Japanese, Korean) or jieba-rs (Chinese) tokenizer |
Real word segmentation, BM25. | Segmentation output differs from the current substring behaviour (升级 vs 升级PostgreSQL). Ranking is BM25, not the TF formula. Different recall. |
| Keep the substring scan in Rust over the chunk table (exact replica of ILIKE + TF formula) | Exact parity with the current behaviour. | No index; O(corpus) per query. Current code already has a capped mode for large corpora. |
2.6 Rust FTS candidates
| Candidate | Maps to | Parity notes |
|---|---|---|
SQLite FTS5 (rusqlite bundled) with porter over unicode61 |
tsvector + websearch_to_tsquery (via custom parser) + ts_rank (via bm25()) |
FTS5 porter is Porter (not Snowball English): stems differ on some words. Stop words are not removed by default. Positions and per-column weights exist via bm25(t, w1, w2, ...) (column weights, not per-lexeme weights). Phrase search is native ("..."). OR/AND/NOT syntax differs from websearch syntax: must translate. |
| tantivy | BM25 index with stemmer (English), custom analyzers, lindera/jieba. | BM25 score != ts_rank. Query syntax needs translation (tantivy query parser vs websearch). Multi-field boost replaces setweight. Per-document 1 MB cap does not exist, so truncation must be done explicitly. |
| DuckDB FTS extension | BM25 in SQL. | Extension stability; BM25 variant differs. |
| Hand-rolled inverted index in Rust over redb or fjall | Full control of tokenization and TF scoring. | Largest effort; must rebuild on each config change; must mirror ts_rank normalization exactly if scores are exposed. |
Recommended for ranking parity: keep ts_rank-like scoring in Rust (own TF/weight formula on the same token stream), because downstream code reads score values and phrase factor (x2), source boosts, and RRF weights. If scores are not exposed to agents, BM25 is acceptable and only list order matters for RRF.
3. pg_trgm (fuzzy title/slug matching)
| Use | Where | Notes |
|---|---|---|
| Extension | src/schema.sql:4 |
|
GIN trigram index on pages.title (idx_pages_trgm) |
src/schema.sql:282 |
|
GIN trigram index on pages.slug (idx_pages_slug_trgm) |
src/schema.sql:286 |
|
Fuzzy title lookup similarity(title, $partial) with title % $partial OR slug ILIKE '%partial%', ORDER BY sim DESC LIMIT 5 |
src/core/engine-sql/pages.ts:775-786 |
Used for fuzzy slug resolution. |
Fuzzy title match by name: title % name, similarity(title,name) >= minSimilarity (default 0.55), tie-break by slug |
src/core/engine-sql/pages.ts:989-1030 |
Comment at :990-992: % lets the GIN index prune candidates when the threshold is at least pg_trgm default 0.3; below 0.3 the explicit comparison runs. |
dirPrefix LIKE filter (slug LIKE 'prefix/%') |
src/core/engine-sql/pages.ts:1006-1008 |
|
| Title-match module | src/core/search/title-match.ts (153 lines), src/core/engine-sql/titles.ts (145 lines) |
Also uses websearch FTS (see section 2). |
| Entity resolution | src/core/entities/resolve.ts (17 matches for similarity-like SQL) |
Not verified line-by-line in this pass. |
Semantics to reproduce: pg_trgm similarity = |trigrams(a) ∩ trigrams(b)| / |trigrams(a) ∪ trigrams(b)|, with words lowercased, non-alnum splitting, each word padded with two leading spaces and one trailing space. word_similarity is not used (0 matches). % uses the global pg_trgm.similarity_threshold (default 0.3), which is a session GUC: a Rust implementation must pin the same threshold or it will return different candidate sets. The code relies on the % pre-filter only as an optimization, so for minSimilarity >= 0.3 a brute-force Rust similarity over all titles gives the same winners, provided the tie-break is replicated (ORDER BY sim DESC, slug).
Rust candidates:
- Implement trigram similarity directly over an in-memory or indexed trigram table (small: titles and slugs). Exact, no extension needed.
- Alternatively use an FTS5 trigram tokenizer plus a Jaccard computation in code. Parity risk: FTS5 trigram tokenizer does not pad words the same way as pg_trgm.
strsimcrate has Jaro/Levenshtein, not pg_trgm trigram Jaccard; do not use it as a drop-in.
4. JSONB
4.1 Columns and where JSONB is stored
| Column | Where |
|---|---|
pages.frontmatter JSONB NOT NULL DEFAULT '{}', GIN idx_pages_frontmatter (jsonb_ops, default) |
src/schema.sql:112, 281 |
sources.config JSONB NOT NULL DEFAULT '{}'::jsonb |
src/schema.sql:32 |
gbrain_try_jsonb(text) -> jsonb (returns NULL on invalid JSON) |
src/schema.sql:60-67 |
links.edge_metadata JSONB, links uniqueness NULLS NOT DISTINCT |
src/schema.sql:479, 503, 582-583 |
raw_data.data JSONB, timeline / page_versions.frontmatter JSONB |
src/schema.sql:635, 683 |
minion_jobs.data, result, progress, stacktrace JSONB DEFAULT '[]', submission_authority JSONB |
src/schema.sql:1254-1276 |
op_checkpoints.completed_keys JSONB with jsonb_typeof = 'array' CHECK |
src/schema.sql:884-885 |
facts/takes JSON fields, mcp_request_log.params |
src/schema.sql:757 |
oauth_clients.federated_read GIN (jsonb ops) |
src/schema.sql:809 |
calibration_profiles.active_bias_tags GIN |
src/schema.sql:1683 |
grant_audit.before_grant/after_grant JSONB |
src/schema.sql:819-820 |
persistence_* JSONB columns |
src/schema.sql:2401-2640 (generated region) |
4.2 Operators and functions used
| Feature | Where | Behaviour |
|---|---|---|
@> containment (frontmatter filter) |
src/core/search/frontmatter-where.ts:1-20 |
p.frontmatter @> $n::text::jsonb per accepted form; matches scalar equality or list containment (a list field contains a value). Served by GIN idx_pages_frontmatter. A string like "2024" also matches the number 2024 (adds JSON number form). |
->> extraction |
src/core/engine-sql/pages.ts (message_id), src/core/search/keyword-statement.ts:145-160 (p.frontmatter->>'message_id', 'thread_id', 'subject') |
Text extraction, NULLIF for empty. Regex trim `regexp_replace(..., '^[[:space:]]+ |
jsonb_set, jsonb_agg, jsonb_build_object, jsonb_typeof, jsonb_array_elements(_text), jsonb_to_recordset |
Widely used (see counts); e.g. src/core/batch-rows.ts:1-20 (bulk insert via jsonb_to_recordset(($1::jsonb)->'rows') to avoid array literal problems), src/core/schema-migrations/*, src/commands/repair-jsonb.ts (28 matches) |
jsonb_build_object is used to build DETAIL strings in trigger error messages (src/schema.sql:323). |
jsonb_path |
9 matches; examples in src/commands/repair-jsonb.ts and src/core/ (not reviewed one by one) |
|
? / `? |
` key existence | 0 matches |
jsonb_path_query family |
not separately verified | |
gbrain_try_jsonb |
src/schema.sql:60-67 |
Portable text -> jsonb with NULL on error (#7112). |
Rust note: JSON stored as TEXT in SQLite with json_extract/json_each, or as serde_json::Value in a KV store. Containment @> must be re-implemented (no built-in). Key ordering in jsonb is canonical (sorted, no duplicates); json is not. Any code that compares or hashes frontmatter must canonicalize. Number vs string equality: Postgres jsonb numeric 2024 and string "2024" are different; the code handles both forms explicitly.
4.3 Index and performance
- Only
pages.frontmatterhas a GIN index.sources.config,links.edge_metadata, and the job tables do not. - The frontmatter filter is ANDed into keyword/vector WHERE clauses (
keyword-statement.ts:96,title-statement.tsfilters at ~`:70-80`).
5. Recursive CTEs (graph and walks)
| Use | Where | Behaviour |
|---|---|---|
BFS by layers over links (bfsWalkCtes), visited array via `bfs.visited |
n.next, depth bound maxLayer, DISTINCT` on frontier |
|
walk(id, depth, pos) with row_number() OVER (ORDER BY b.depth, u.ord), row cap |
src/core/engine-sql/links.ts:645; cap TRAVERSE_WALK_ROW_CAP = 20000 (src/core/engine-constants.ts:48) |
Ordering is depth then insertion order u.ord. Cap cut point depends on pos ordering: parity-sensitive. |
| Edge extraction at depth < depth, one-past-shallower endpoint rule | src/core/engine-sql/links.ts:714-809 |
Returns each edge once with depth+1; sorts by depth, from_slug, to_slug, from_source_id, to_source_id (:786). Dedup key `from |
get_links/get_backlinks neighbour expansion |
src/core/engine-sql/links.ts (30 matches) |
Not individually reviewed. |
Retirement chain fold (chain CTE, hop cap MAX_RETIREMENT_HOPS = 5) |
src/core/engine-sql/graph-rank.ts:123-160; constant src/core/entities/resolve.ts:217 |
Uses DISTINCT ON (c.start_id) ... ORDER BY c.start_id, c.hop to pick the shallowest fold target (graph-rank.ts:138). |
Rank entities aggregate (edges, ends, neighbors) |
src/core/engine-sql/graph-rank.ts:120-185 |
DISTINCT triples, MATERIALIZED CTEs, NULLS/IS DISTINCT FROM on mention links. Cost ~3 s for 200k pages and 985k links on Postgres 16 (:33-34). |
| Read enrichment backlink walk | src/core/search/read-enrichment.ts:54 |
|
| Source config recursion | src/core/source-config-sql.ts:15 |
|
| Page-type pairs | src/core/onboard/checks.ts:667 |
|
| Stale-chunk occurrence walk | src/core/page-body/projection.ts:66 (STALE_CHUNKS_SQL) |
|
| Minion job tree / cancel / descendants | src/core/minions/job-insight.ts:204, src/core/minions/authorize-legacy.ts:57, src/core/minions/queue.ts:1061, src/core/minions/legacy-cancel.ts:42 |
Parent/child job trees. |
| Capacity forecast walk | src/core/persistence/capacity-forecast.ts:118 |
|
| Terminal lineage | src/core/persistence/terminal-classify.ts:489 |
Rust replacement: SQLite supports WITH RECURSIVE with UNION ALL, but not array ||/ANY or row_number tie semantics identical to PG for ORDER BY ... u.ord. The cycle guard in the code is an array visited (links.ts:627-632). In Rust, BFS in code with a HashSet is simpler and faster. Parity points: the layer order, the row cap (which rows are kept when the cap hits), and the "one-past-shallower endpoint" depth rule.
6. Other SQL features that an embedded store must emulate
6.1 Upsert, returning, and uniqueness
| Feature | Count / key sites | Notes |
|---|---|---|
INSERT ... ON CONFLICT ... DO UPDATE (upsert) |
very widespread (ON CONFLICT ~459 line matches across src/); examples src/core/engine-sql/pages.ts, links.ts, facts.ts, takes.ts |
SQLite supports UPSERT (3.24+). Must check DO UPDATE ... WHERE and EXCLUDED. semantics. |
RETURNING |
~545 line matches | SQLite 3.35+ supports RETURNING. |
UNIQUE NULLS NOT DISTINCT (from_page_id, to_page_id, link_type, link_source, origin_page_id) |
src/schema.sql:579-583 (PG15+). Also link_transitions at src/schema.sql:3336. |
Parity risk: SQLite (and PG default) treat NULLs as distinct in UNIQUE. The Rust store must enforce NULL-equal uniqueness in code or with a COALESCE expression index. |
UNIQUE (origin_page_id, producer, ref_kind, target_source_id, target_ref) |
src/schema.sql:605-608 |
|
Composite keys with source_id |
throughout (pages.source_id, links) |
6.2 Batch patterns: ANY, UNNEST, jsonb_to_recordset
| Pattern | Where |
|---|---|
col = ANY($n::text[]), p.type = ANY(...), p.slug != ALL(...) |
src/core/search/keyword-statement.ts:95, 97, 101; title-statement.ts |
unnest($n::text[]) bulk insert (older; replaced for free text) |
~287 line matches unnest(. The batch helper explains why the array literal approach was replaced (src/core/batch-rows.ts:1-20). |
jsonb_to_recordset(($1::jsonb)->'rows') bulk insert |
src/core/batch-rows.ts:1-20 (used by addLinksBatch, addTimelineEntriesBatch, addTakesBatch) |
| Bind-parameter cap (65535) motivates batch JSON | src/core/batch-rows.ts:1-20 |
Rust: plain prepared statements in a transaction are sufficient; the array-literal parse issue does not exist in a native binding.
6.3 Window functions, DISTINCT ON, LATERAL, FILTER, GROUPING
| Feature | Count (line matches) | Key sites |
|---|---|---|
DISTINCT ON |
43 | src/core/search/keyword-statement.ts:150 (best chunk per page), src/core/engine-sql/graph-rank.ts:138 (fold target). Replace with ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) = 1. |
Window OVER (...) |
37 | row_number() at src/core/engine-sql/links.ts:645; SQLite supports window functions since 3.25. |
LATERAL |
117 | Mostly in vector-statement.ts:~430-445 (CROSS JOIN LATERAL (SELECT ... OFFSET 0) key probes behind OFFSET 0 fences, #6728). SQLite has no LATERAL; rewrite as correlated subquery or join with the planner forced by the Rust code. |
FILTER (WHERE ...) aggregate |
253 | Used across aggregates; SQLite supports FILTER (3.30+). |
GROUP BY ... GROUPING SETS |
6 | Not studied in depth. |
string_agg |
25 | src/core/schema-migrations and src/core/belief/*; `summary |
MATERIALIZED CTE hints |
65 | Planner fences only; no semantic effect in a new engine. |
6.4 Generated and identity columns, sequences
| Feature | Where | Notes |
|---|---|---|
SERIAL / BIGSERIAL primary keys |
many (e.g. src/schema.sql:100 sources.id SERIAL, :355 content_chunks.id SERIAL, :596 BIGSERIAL) |
Integer ids are visible to users in cursors and some CLI output; keep numeric ids. SQLite INTEGER PRIMARY KEY (rowid) is the analogue; AUTOINCREMENT differs in reuse behaviour. |
page_generation_clock_seq sequence + GREATEST seed |
src/schema.sql:253-275 |
Monotonic counter via sequence last_value; a Rust engine needs an equivalent monotonic counter that survives replay. |
UUID DEFAULT gen_random_uuid() |
src/schema.sql:736, :1531, :2404, :2410, :2856-2862 |
Replace with Rust uuid v4. |
GENERATED ALWAYS AS (COALESCE(staging_id, '00000000-...')) STORED |
src/schema.sql:2195 (page-body staging key) |
SQLite generated columns (3.31+) support STORED. Must reproduce the sentinel UUID. |
GENERATED ALWAYS AS (octet_length(files::text)+octet_length(metadata::text)) STORED |
src/schema.sql:2889 |
octet_length of text is UTF-8 byte length; SQLite length() is characters: use length(CAST(x AS BLOB)). |
gbrain_count_modifications statement triggers with REFERENCING NEW TABLE |
src/core/planner-stats-schema.ts:24-51 (PGLite only) |
Transition tables are not in SQLite; use per-row triggers or app-level counters. |
6.5 Timestamps and interval math
- Columns are
TIMESTAMPTZwithDEFAULT now()(e.g.src/schema.sql:31, 44, 57, 112-140).intervalappears about 826 times as literal intervals (INTERVAL '72 hours'insrc/schema.sql:292,make_interval,now() - interval). Count is oversrc/and includes TS string literals. COALESCE(p.effective_date, p.updated_at, p.created_at)used as the date axis in keyword filters (keyword-statement.ts:98), with::text::timestamptzcast on bound ISO strings (:99-100).EXTRACT(48 matches;date_trunc10 matches (src/core/persistence/*and eval).NOW()89 matches.
Rust replacement: store timestamps as integer microseconds or RFC 3339 text in UTC. Parity risks: PG microsecond precision, now() is transaction-start time (not statement time), and comparisons of text timestamps must be on normalized UTC strings. interval arithmetic needs a small date-math module (time or chrono).
6.6 Regex and string operators
| Feature | Where | Notes |
|---|---|---|
regexp_replace(..., 'g') |
src/core/fts-language.ts:146 (CJK boundary), src/core/search/keyword-statement.ts:145 (trim), src/core/engine-sql/pages.ts, src/schema.sql:283 (regexp_replace(slug, '^.*/', '') in an expression index for basename lookup) |
Needs a Rust regex (regex crate) or SQLite REGEXP registered function. POSIX class [[:space:]] needs translation. |
~ (POSIX match) |
src/schema.sql:560 (link_source ~ '^[a-z][a-z0-9]*(-[a-z0-9]+)*$'), src/core/fts-language.ts:149 (FTS_CJK_BOUNDARY_REGEX) |
CHECK constraint must be enforced in Rust at write time. |
LIKE ... ESCAPE '\' |
src/core/engine-sql/pages.ts:510, keyword-statement.ts:61, cjk-search.ts |
SQLite supports ESCAPE; case-folding differs for non-ASCII. |
ILIKE |
44 matches | SQLite has no ILIKE: emulate with lower(x) LIKE lower(y) (ASCII-only lower in SQLite) or a Rust function. |
COLLATE |
32 matches | Check for "C" vs locale collations used in ORDER BY; mismatch changes sort order for mixed case. |
char_length, LENGTH, REPLACE, position |
src/core/search/cjk-keyword-sql.ts |
LENGTH() counts characters in PG; SQLite length() counts characters for TEXT too, but BLOB differs. |
unaccent |
1 match (doc only, src/core/fts-language.ts:13) |
Not installed in the schema. Multi-language docs suggest unaccent in custom configs. |
citext |
0 | Not used. |
6.7 Advisory locks, row locks, and concurrency primitives (affects the embedded store design)
| Feature | Count | Where |
|---|---|---|
pg_advisory_lock / pg_try_advisory_xact_lock |
~49 matches | src/core/db-lock.ts, src/core/persistence/*, src/core/cycle/*. Used for cross-process write locks. |
FOR UPDATE SKIP LOCKED |
~31 | src/core/minions/queue.ts (job claim). |
FOR UPDATE |
~225 | Many (queues, sync). |
SET LOCAL statement_timeout |
src/core/engine-sql/cjk-search.ts:138, src/core/vector-prewarm.ts:152, src/core/search/vector-settings.ts |
Per-query deadlines (statement_timeout via set_config). An embedded engine needs an interrupt-based equivalent (e.g. SQLite progress handler / sqlite3_interrupt). |
pg_stat_activity probes |
src/core/vector-index.ts:169-243 (zombie CREATE INDEX detection) |
Replaced by a single-process lock in the embedded engine. |
6.8 Planner statistics and ANALYZE
| Item | Where | Notes |
|---|---|---|
Row-delta planner stats for PGLite (no autovacuum): triggers record modification counts in planner_stats_deltas, state in planner_stats_state |
src/core/planner-stats-schema.ts:1-51 (tables pages, links, facts, takes, content_chunks, timeline_entries) |
PG keeps autovacuum; triggers only applied to PGLite (sqlFor.pglite). |
Threshold max(500, 10% reltuples) |
src/core/planner-stats.ts:100-101 |
Decides when to ANALYZE. |
| Per-table ANALYZE + watermark publication | src/core/planner-stats.ts:103-135 (has_stats, stale), :137-161 (expression-index stats) |
|
hnsw plan proof, vector_plan doctor check |
src/commands/doctor/checks/vector-plan.ts (32 matches) |
Checks that the vector query uses the HNSW index. Not portable as-is; a Rust engine would test the chosen path (index vs exact) instead. |
reltuples used as a corpus-size proxy |
src/core/engine-sql/cjk-search.ts:150-153 |
The PGLite CJK capping decision (PGLITE_CJK_CAPPED_CHUNKS). Rust should use its own row count. |
| Planner GUC tweaks | src/core/postgres-engine/jit-off-statement.ts (jit off), search-settings.ts |
Not needed in embedded. |
Rust replacement: SQLite ANALYZE exists and writes sqlite_stat1, but the planner fences (OFFSET 0, MATERIALIZED) exist only to stop the PG planner from making bad choices. A new engine should not replicate them; it should keep the observable result (same rows, same ordering, same caps).
7. Hybrid search and fusion (src/core/search/hybrid*)
| Item | Where | Notes |
|---|---|---|
RRF constant RRF_K = 60 |
src/core/search/hybrid.ts:58 |
Default; override via rrfK. |
| Pipeline comment | src/core/search/hybrid.ts:2-9 |
Keyword + vector -> RRF -> normalize -> boost -> cosine rescore -> dedup. Compiled-truth boost 2.0x (:8). Cosine blend 0.7 rrf + 0.3 cosine (:9). |
rrfFusion, rrfFusionWeighted |
src/core/search/hybrid.ts:1456-1545 |
Divide by observed max; then boost. Weighted form w / (k + rank). |
Per-list accumulation with page-level vote key (source_id, slug) |
src/core/search/rrf-page-fusion.ts:19-80 (rrfPageKey at :31-33, w / (k + rank) at :80) |
Lead chunk carries the page's summed score. |
| Pre-fusion candidate floor | src/core/search/hybrid.ts:61-80 (D-3002) |
Keeps the pool deep enough for RRF. |
| Fusion list composition with budget normalization | src/core/search/fusion-lists.ts:1-30, 65-80, 174-235 |
|
| Keyword arm + OR fallback + CJK arm (deadline) | src/core/search/hybrid/arms.ts (568 lines, 66 matches) |
|
| Vector arm pooling | src/core/search/vector-pool.ts, src/core/search/hybrid/arms.ts |
|
Page-level RRF (rrf-page-fusion.ts) |
yes | |
| Dedup | src/core/search/dedup.ts |
RRF is pure arithmetic in JS: it ports unchanged to Rust. Parity depends only on the order each arm returns. Keyword order = score DESC, page_id ASC (keyword-statement.ts:152-155). Vector order = score DESC NULLS LAST, page_id ASC, chunk_id ASC (vector-statement.ts:508). Tie-breaking must match these or RRF rank numbers shift.
8. Feature-to-user-visible-behaviour map
| Feature | User-visible effect |
|---|---|
websearch_to_tsquery + ts_rank |
search/query keyword ranking, keyword_total count, cursor pages |
ts_rank_cd on weight-A slice |
Title match ranking, get_page title fuzzy |
| CJK ILIKE + TF scoring | Chinese/Japanese/Korean keyword hits |
pg_trgm similarity |
Fuzzy slug/title resolution (resolveSlug fallback), entity matching, put_page auto-link reconciliation |
| HNSW cosine | Semantic search ordering, hasMore, recall-dependent results |
iterative_scan |
Filtered vector search completeness |
| RRF | Final hybrid ordering |
frontmatter @> |
--where key=value filter on search/query/list |
| Recursive BFS + caps | traverse, neighbour graph, rank_entities |
NULLS NOT DISTINCT uniqueness |
Duplicate link suppression |
| Row-delta ANALYZE | Only performance (and plan-proof doctor checks) |
Parity risks noted
- ts_rank vs BM25: scores exposed in
score_detailsand used for phrase factor (x2) and source-boost multiplication (keyword-statement.ts:74-82) will change if BM25 is used. Only list order survives into RRF. - Postgres English Snowball stemmer and stop words vs FTS5 porter / tantivy English stemmer: different stems and stop words; query results differ for some terms. Keep config name
GBRAIN_FTS_LANGUAGE, and rebuild indexes on change. gbrain_fts_inputCJK/ASCII boundary spacing (fts-language.ts:143-150) must be reproduced exactly before tokenization orRedis7/升级PostgreSQL17queries break (#6370).- tsvector 1 MB cap is enforced by truncating page text to 65,536 + 32,768 characters (
fts-language.ts:161-162,schema.sql:1222-1224). A Rust index without the same truncation returns extra hits; with truncation it matches. - Chunk search_vector trigger fires only on text columns (
schema.sql:439-443); a Rust writer must not recompute keyword vectors on embedding-only updates, or ordering changes when only vectors change (not parity-visible but costly). websearch_to_tsquerysyntax (quoted phrases,or,-negation) must be re-implemented by translation;plainto_tsquery/phraseto_tsquery(6 and 3 matches) are used for phrase factor and need equivalents.- CJK ILIKE: case-folding and
LENGTH()character counting must match (cjk-keyword-sql.ts:1-20,keyword-statement.ts:56-67). LIKE on SQLite is ASCII-only case-insensitive. - HNSW is approximate and the cosine metric is
1 - <=>. Exact order from pgvectorvector_cosine_opsis the reference; ANN crates will change top-k at the margin. Keep the exact path as a parity oracle (exact-scan.ts,vector-statement.tsexact variant). hnsw.ef_searchwindow = max(candidate, 40) capped at 1000 (vector-index.ts:124-140). A Rust ANN with a different knob will return a different candidate set at the same limit.- halfvec (f16) vs vector (f32) columns: rounding differs; the dim-change path swaps types losslessly for same-dim (
embedding-dim-check.ts:648-660). Rust must keep the two storage types or accept the rounding difference. - pgvector dims: fresh-install DDL says
vector(1536)(schema.sql:360) but the default model is 1024 dims (ai/defaults.ts:16). Dim is reconciled at connect (embedding-dim-check.ts). A Rust store must reproduce the reconciliation, not the DDL literal. - pg_trgm:
%threshold 0.3 is a session GUC (pages.ts:990-992); fuzzy title winner tie-break isORDER BY sim DESC, slug(pages.ts:996). Trigram padding rules must match pg_trgm exactly for identical rankings. - JSONB:
@>containment and number-vs-string equality (frontmatter-where.ts:1-20); jsonb canonical key order and deduplication. Use a canonical JSON type in Rust. - Recursive BFS row cap
TRAVERSE_WALK_ROW_CAP = 20000(engine-constants.ts:48) andMAX_RETIREMENT_HOPS = 5(entities/resolve.ts:217): cut points depend on therow_number()ordering (links.ts:645). A Rust BFS must emit rows in the same layer order before capping. NULLS NOT DISTINCTunique constraints (schema.sql:579-583,:3336) do not exist in SQLite; duplicate-suppression behaviour for NULL origin must be enforced in code.DISTINCT ONwithORDER BY page_id, score DESC, chunk_id ASC(keyword-statement.ts:150) is the "best chunk per page" rule. ROW_NUMBER equivalence requires the same tie order.- Keyset pagination on float8
scorepluspage_id(keyword-statement.ts:152-155): the Rust score must be f64 and computed identically, or cursors skip or repeat rows. - Statement timeouts (
SET LOCAL statement_timeout,cjk-search.ts:112-148) and the 2/3 + 1/3 deadline split: an embedded engine needs interruptible queries, and the capped fallback (CJK_CANDIDATE_CAP = 2000) must match in result cap behaviour. - Planner-driven behaviours (
OFFSET 0fences,MATERIALIZED, planner-stats ANALYZE,reltuplesused as a corpus size incjk-search.ts:150-171) do not port. Replacereltupleswith a real row count. - Timestamp and interval semantics:
now()is transaction time;COALESCE(effective_date, updated_at, created_at)date axis (keyword-statement.ts:98) and::text::timestamptzcasts on bound strings. Keep a single UTC representation. SERIAL/BIGSERIALinteger ids are exposed through cursors and sort tie-breaks (page_id ASC). A new engine must keep stable, monotonic integer ids and not reuse them after delete.- Advisory locks,
FOR UPDATE SKIP LOCKED(~31 matches,src/core/minions/queue.ts), andpg_stat_activityzombie-index probes need an embedded-process equivalent (single-writer lock or DB-level transactions).
Concurrency, access control and runtime features
Scope: concurrency and runtime features of the Postgres and PGLite engines in garrytan/gbrain (commit 8bbfd0b5e8). Paths are relative to that repo unless absolute. Line numbers were read in this pass. Where I only sampled a file, I say so.
Headline: gbrain does not use Postgres as a passive store. It leans on triggers (plpgsql guards and derived-state maintainers), transaction-scoped advisory locks, row locks (FOR UPDATE, FOR SHARE, FOR KEY SHARE), SKIP LOCKED for a job queue, a TTL lease table, and LISTEN/NOTIFY for worker wake-up. Access control is mostly application code plus GUC-based write guards inside triggers. There are no RLS policies, no GRANT/REVOKE statements and no SECURITY DEFINER functions in the repo. Several correctness properties depend on the server behaving the way Postgres does, not on gbrain code alone.
0. Summary of mechanisms
| Mechanism | Used for | Where (main) |
|---|---|---|
| plpgsql triggers (about 25 trigger functions) | derived-state maintenance, revision counters, fail-closed write guards, job-queue protocol, NOTIFY | src/schema.sql:182-3064+ |
pg_notify('minion_jobs', ...) |
wake idle job workers | src/schema.sql:1813-1825 |
pg_advisory_lock(42) / pg_try_advisory_lock(42) (session) |
schema migration / init-schema lock | src/core/db.ts:600-611, src/core/postgres-engine/init-schema-lock.ts:98 |
pg_advisory_xact_lock(hashtext(...)) (transaction) |
per-key serialization: queue admission, quotas, budgets, generations, origin, page-body chunks, facts by entity | see section 3 |
gbrain_cycle_locks row lease (INSERT ... ON CONFLICT ... WHERE ttl < now()) |
cycle lock, sync lock, maintenance leases; pooler-safe | src/core/db-lock.ts:267-328, src/core/lease-schema.ts |
FOR UPDATE SKIP LOCKED |
minion job claim, timeout/stall reapers, segment derive | src/core/minions/queue.ts:1511, 1585, 1720; src/core/page-body/ (see 5) |
Fenced UPDATE on lock_token + status='active' |
job complete/fail/renew, lease fencing | src/core/minions/queue.ts:1766, 1885, 2014, 2060, 2110 |
LOCK TABLE ... NOWAIT (SHARE UPDATE EXCLUSIVE) |
defer ANALYZE instead of cancelling autovacuum | src/core/analyze-lock.ts:11-24 |
SET LOCAL / set_config(..., true) |
per-transaction statement_timeout, lock_timeout, jit, GUC guards | many; see 8 |
| Session-level startup GUCs | statement_timeout 5 min, idle_in_transaction 5 min | src/core/db.ts:346-347, 440-456 |
current_setting('gbrain.*', true) guards |
write allowlists, protocol versions | src/schema.sql:2556, 2564, 2598, 2607, 2953, 3022, 3045 |
ALTER FUNCTION ... SET search_path = ..., pg_temp |
function search_path hardening | src/core/function-search-path.ts:37-123 |
1. Triggers and trigger functions (Postgres DDL: src/schema.sql)
Unless noted, every function below is LANGUAGE plpgsql, carries SET search_path = pg_catalog, public, pg_temp (function-search-path.ts, #7632), and is SECURITY INVOKER (the default). No SECURITY DEFINER anywhere (see 6).
| Function | Trigger / event | Lines | What it maintains or enforces |
|---|---|---|---|
gbrain_try_jsonb(text) |
none (IMMUTABLE helper) | 64-65 | Safe text-to-jsonb cast used by generated indexes |
bump_page_generation_fn() |
bump_page_generation_trg on pages |
182-212 | Page generation counter (body not read in detail) |
bump_page_generation_clock_fn() |
bump_page_generation_clock_trg |
267-278 | Calls nextval('page_generation_clock_seq'); sequence created and set at 250-263 with the comment that nextval avoids a tuple-level hotspot |
gbrain_forget_page_retrievals() |
pages_forget_retrievals AFTER DELETE, FOR EACH STATEMENT |
313-334 | Deletes retrieval log rows for deleted pages |
update_chunk_search_vector() |
chunk_search_vector_trigger, FOR EACH ROW |
432-458 | Maintains tsvector on chunks (uses gbrain_fts_input, 432) |
update_page_search_vector() |
trg_pages_search_vector BEFORE INSERT/UPDATE |
1196-1234; re-created dynamically with the column list at 2685 | Page tsvector maintenance |
enforce_minion_queue_protocol() |
minion_queue_protocol BEFORE INSERT OR UPDATE ON minion_jobs |
1789-1810 | Rejects rows without submission_authority; forces claim_generation to advance by exactly 1 on every claim into 'active'; spend protocol check (spend_claim_token) |
notify_minion_job_change() |
minion_job_notify AFTER INSERT OR UPDATE OF status |
1813-1825 | pg_notify('minion_jobs', json) with id, status, name, queue, prev_status |
gbrain_advance_page_revision() |
pages_knowledge_revision BEFORE UPDATE |
1904-1936 | Advances knowledge_revision, unless current_setting('gbrain.materializing_revision') matches OLD (line 1921; set by page-body/rechunk.ts:166-181) |
gbrain_advance_tag_revision() |
tags_knowledge_revision AFTER I/U/D on tags |
1937-1961 | Tag-side revision bump |
gbrain_mark_derivation_scope_entry() |
page_body_manifest_derivation_scope_retire, ..._delete; pages_derivation_scope_delete |
2298-2344 | Takes `pg_advisory_xact_lock_shared(hashtextextended('gbrain:derivation-scope:' |
gbrain_refuse_purging_derivation() |
page_segment_derivations_refuse_purging BEFORE INSERT OR UPDATE OF output, receipt |
2345-2382 | Refuses writes to derivation rows when the purge guard applies (body only partly read; the name indicates a purge interlock) |
gbrain_require_managed_writer() |
managed_writer_guard BEFORE I/U/D, created dynamically on many tables |
2522-2645 (trigger created at 2635) | Fail-closed writer guard: reads persistence_brain.enabled (P0001 writer_coordinator_required if the row is not visible), checks gbrain.write_sources allowlist (2556, 2598), gbrain.topology_change (2564), gbrain.git_protocol (2607), git_canonical_brain.strict_sources |
gbrain_count_git_canonical_sources() |
git_canonical_sources_count_{insert,update,delete} on sources |
2647-2668 | Counts git-canonical sources into git_canonical_brain |
gbrain_queue_page_projection() |
pages_projection_queue AFTER I/U/D on pages |
2697-2766 | Enqueues projection work for search/attribution derivations |
gbrain_require_persistence_protocol(int) |
called by triggers | 2951-2956 | Requires gbrain.persistence_protocol = '2' when required >= 2 |
gbrain_guard_request_protocol() |
gbrain_request_protocol BEFORE I/U/D on persistence_requests |
2957-2998 | Protocol and state guards for write requests |
gbrain_guard_effect_protocol() |
gbrain_effect_protocol BEFORE I/U/D on persistence_effects |
2999-3010 | Same for effects |
gbrain_guard_protocol_activation() |
gbrain_protocol_activation BEFORE UPDATE on persistence_brain |
3011-3037 | Refuses enabled / protocol-floor changes unless gbrain.writer_quiesced = 'true' (3022) |
gbrain_guard_skill_publication() |
gbrain_skill_publication on shared_skill_packs/heads/revisions |
3038-3063 | Publication guard (allowlist via gbrain.write_sources, 3045) |
gbrain_lease_shared_skill_delivery() |
trigger (body not read) | 3064+ | Shared-skill delivery lease |
Also present in the file and tracked by the function-search-path list (src/core/function-search-path.ts:37-52): gbrain_access_* (readers, folder rules, lineage, origin, refresh-serves trigger, mark-restricted, etc.), gbrain_guard_guest_records, gbrain_guard_inserted_facts (insert-guard-schema.ts:86-113), gbrain_preserve_fact_withdrawal, gbrain_refuse_purged_{fact,page,take}, gbrain_release_refresh_barrier, gbrain_graduation_fence, gbrain_bump_trust_generation (eligibility/generation-schema.ts:78), gbrain_snapshot_version_trust, gbrain_stamp_{revision,version,write}_attribution, gbrain_stamp_trust_tier, gbrain_clear_atom_page_state, gbrain_concept_inputs_private, gbrain_count_modifications. The list has 56 names. Their bodies were not read in this pass; sc-1/sc-2 cover the tables they touch.
Other trigger-like guards defined outside schema.sql (module-level DDL emitted by TS fragments):
- src/core/facts/insert-guard-schema.ts:86-113:
FOR SHARE OF son sources, thenset_config(FACT_WITHDRAWAL_STAMP_SETTING, 'on', true). - src/core/facts/purge-schema.ts:92-128:
PERFORM id FROM sources ... FOR SHARE. - src/core/facts/withdrawal-schema.ts:68: same FOR SHARE pattern.
- src/core/persistence/writer-guard-schema.ts (128 lines) and src/core/shared-skills/persistence-schema.ts:7-120: copies of the protocol and allowlist guards for the persistence subsystem.
- src/core/derived-state-maintainers.ts:3-13: states that the maintainer triggers run SECURITY INVOKER and "No SECURITY DEFINER (#7659)".
Behavior that depends on triggers:
- Crash safety: none directly; triggers run inside the statement's transaction, so they commit or roll back with the row.
- Multi-process writers: the
managed_writer_guardand protocol triggers are the only enforcement that a non-coordinator writer cannot change canonical rows. They key off GUCs that a session sets itself (set_config(..., true)), so the guard blocks accidental writers, not hostile ones. - Exactly-once: the job protocol trigger (1789-1810) is what stops old workers from claiming upgraded jobs.
2. LISTEN/NOTIFY
| Actor | Role | Location |
|---|---|---|
Trigger notify_minion_job_change |
NOTIFY on every insert and status update of minion_jobs |
src/schema.sql:1813-1825 |
| Trigger is absent from the PGLite blob | PGLite has no NOTIFY; the trigger function name is only listed in the function-search-path inventory | src/core/pglite-schema.generated.ts (grep: no minion_job_notify creation); schema.sql:1812 comment "Postgres only, not PGLite" |
sql.listen('minion_jobs' channel) |
LISTEN for the idle claim loop | src/core/minions/sync-queue-wake.ts:6-98 (listen at 87, probe NOTIFY at 91, LISTEN_PROBE_MS = 3000 at 25) |
| Probe NOTIFY | verifies the LISTEN is live; if no notification arrives within 3 s, the worker polls | src/core/minions/sync-queue-wake.ts:11, 91 |
| Idle waiter integration | wakes jobs work claim loop |
src/commands/jobs/work.ts:267; src/core/minions/idle-wait.ts |
wait-for-completion |
comment says it does not depend on NOTIFY | src/core/minions/wait-for-completion.ts:3 |
Notes:
- A LISTEN is only attempted on a direct session. Behind a transaction pooler,
sync-queue-wake.ts:9-10says a LISTEN outlives the transaction that ran it, so notifications reach a different client or nobody. The fallback is a 1 s read of the sync queue (behavior-change-notice.ts:983). - Notifications are not durable. The claim loop's own poll is the backstop (sync-queue-wake.ts:16).
- A probe NOTIFY is sent from the same connection (
engine.executeRaw('SELECT pg_notify($1, $2)'), sync-queue-wake.ts:91). - No other module calls
pg_notify. Theserver.listenhits in src are Node HTTP/Unix socket listeners, not Postgres LISTEN.
3. Advisory locks
Session-level (held until unlock or disconnect):
| Key | Acquire / release | Purpose | Location |
|---|---|---|---|
42 (int, brain-global) |
pg_advisory_lock(42) / pg_advisory_unlock(42) |
legacy initSchema path in db.ts | src/core/db.ts:600, 611 |
42 |
pg_try_advisory_lock(42) poll with 500 ms interval, 10 min deadline, 5 s heartbeat |
initSchema lock for the engine; names holder pid from pg_locks | src/core/postgres-engine/init-schema-lock.ts:13-98, 140-170 (diagnostic query at 166-169) |
42 session fallback |
used only when every pool has one connection (GBRAIN_POOL_SIZE=1 or a short-lived command) | src/core/postgres-engine.ts:687-700; init-schema-lock.ts:227-239 |
Transaction-level (released at COMMIT or ROLLBACK):
| Key | Purpose | Location |
|---|---|---|
42 via pg_try_advisory_xact_lock(42) inside BEGIN ISOLATION LEVEL READ COMMITTED, with idle_in_transaction_session_timeout = 0 and application_name='gbrain-schema-migration-lock' |
schema migration lock held across the whole migration run; assertHeld checked before each migration |
src/core/postgres-engine/init-schema-lock.ts:189-226; src/core/schema-migration-lock.ts:1-38 |
| `hashtext('minion_admission:' | queue | |
| `hashtext('minion_quota:' | queue)` | |
hashtext('minion_maxwaiting:'...) |
max-waiting cap | src/core/minions/queue.ts:460-488 (comment at 460-473: the cap is enforced under this lock) |
client-id hash (clientLockKey) |
budget check-and-reserve | src/core/minions/budget-meter.ts:30-38, 99, 255 |
$1::bigint from rateLeaseLockKey |
rate-lease reservation | src/core/minions/rate-leases.ts:36, 81 |
| `hashtextextended('gbrain:derivation-scope:' | src, 0)` shared (trigger) and exclusive (segment-derive) | |
| `hashtextextended('gbrain:page-body-chunks:' | pageId, 0)` | |
| `hashtextextended(source | ':' | |
hashtext(generationKeyLock(key)) |
generation fence and publish | src/core/generations/fence.ts:128; publish.ts:71; managed.ts:151; adapters/fact-rows.ts:124, 147 |
hashtext(registerClientNameLockKey(name)) |
agent client registration | src/commands/agent-register.ts:546, 818; src/commands/serve-http-registration.ts:199; src/commands/mcp-provision.ts:36 |
| `hashtextextended('gbrain:origin:' | [source,key], 0)` | |
| `hashtext('memory_proposals:' | principal)` | |
| `hashtext('gbrain:mention-entries:' | sourceId)` | |
hashtext(...) on the output writer |
output writer serialization | src/core/output/writer.ts:186-193 (comment says hashtext, not hashtextextended, must behave the same on both engines) |
hashtext(AGENT_WRITEBACK_KEY) |
writeback settings | src/core/facts/writeback-settings.ts:122 |
hashtext('gbrain:chronicle:daily_reservation') |
daily chronicle reservation | src/core/chronicle/ledger.ts:97 |
pg_advisory_xact_lock($1::int4, $2::int4) |
graduation target nonce | src/core/persistence/graduation-target.ts:332 |
hashtextextended('gbrain:access-serves', 0) |
access serve refresh (migration v309) | src/core/schema-migrations/v309-requester-scope.ts:40 |
Other lock primitives:
src/core/db-lock.ts:8states why gbrain avoids session-level advisory locks: they are per-session and PgBouncer transaction mode drops session state. The migrate.ts comment (cycle_locks_table) says the same. The xact-level keys above are used in the opposite way, so they survive transaction pooling.src/core/cycle.ts:34: the cycle lock is a TTL row, notpg_try_advisory_lock, for the same reason.
4. Row locks
| Lock | Where | Target |
|---|---|---|
FOR UPDATE (page identity) |
src/core/engine-sql/chunks.ts:118, 1056; src/core/engine-sql/page-body.ts:294; src/core/engine-sql/pages.ts:453; src/core/facts/conversation-incremental.ts:364; src/core/repair/readers.ts:218 | pages by (source_id, slug) or id |
lockPageKeys / lockUnheldPageKeys / withHeldPageKeys |
src/core/page-state/guards.ts:1-60 (sorted by sourceId then slug; pipelined FOR SHARE/FOR UPDATE on sources); called from roughly 76 files (count by rg -l) |
per-page guard for writes, sorted to avoid deadlock; the comment at line 16 says source locks precede auth/request locks |
FOR KEY SHARE on pages |
src/core/engine-sql/links.ts:124-196 (link insert holds the endpoints' key so a page delete waits); src/core/engine-sql/timeline.ts:51; src/core/engine-sql/page-body.ts:195 | prevent delete during insert of dependent rows |
FOR SHARE on pages |
src/core/facts/reconcile-watermark.ts:81; src/core/mentions/pass.ts:562; src/core/questions/refresh.ts:352-374 | read-consistent check |
FOR SHARE on sources |
src/core/attendance-repair.ts:329; src/core/embed-facts.ts:191; src/core/facts/backstop.ts:730; src/core/facts/insert-guard-schema.ts:86; src/core/facts/purge-schema.ts:128; src/core/facts/withdrawal-schema.ts:68; src/core/shared-skills/policy.ts:141-196 | writes wait for incarnation/purge |
FOR UPDATE on sources |
src/core/facts/withdrawal.ts:107; src/core/facts/purge.ts:511; src/core/guests/sources.ts:69, 89; src/core/url-source-origin.ts:229; src/core/repair/segmented-mirror.ts:160; src/core/access/attest-sources.ts:67-69; src/core/shared-skills/retention.ts:143 | purge and attestation take exclusive |
FOR NO KEY UPDATE / FOR UPDATE on access state |
src/core/access/policy.ts:112-113 | access policy rows, sources first |
FOR UPDATE on config |
src/core/heal-audit.ts:57; src/core/line-grammar-config.ts:42; src/core/repair/content-receipt-root.ts:113; src/core/embedding-migration-budget.ts:104 | read-modify-write of config keys |
FOR UPDATE on facts / takes / timeline |
src/core/repair/extractor-facts.ts:376; src/core/repair/unwithdrawn-facts.ts:312; src/core/repair/conversation-labels.ts:373; src/core/repair/purged-page-facts.ts:206; src/core/facts/purge-reasons.ts:85; src/core/trust/fence-guard.ts:73, 197, 226; src/core/trust/propagation.ts:44 | batch writers take rows before editing |
FOR UPDATE on oauth clients/codes/tokens |
src/core/oauth-grants.ts:136, 274, 293, 351; src/core/harness/client-setup.ts:46 | code exchange and refresh single-use |
FOR UPDATE on write gates |
src/core/trust/owner-actions.ts:194; src/core/trust/release-publish.ts:246 | hold resolution |
FOR UPDATE on budget and spend |
src/core/page-spend.ts:85; src/core/budget/daily-ledger.ts:106 | per-page and per-day ledger |
FOR UPDATE on questions and proposals |
src/core/questions/refresh.ts:451; src/core/questions/store.ts:105 (opt-in forUpdate); src/core/trust/proposals.ts:125 (opt-in) |
pinned questions |
FOR UPDATE SKIP LOCKED |
src/core/minions/queue.ts:1511 (claim), 1585 (timeouts), 1720 (wall clock), 2211 and 2228 (stalled reaper); src/core/engine-sql/page-body.ts:485; src/core/shared-skills/retention.ts:95 (FOR UPDATE OF r SKIP LOCKED) |
queue claim, reapers, retention batches |
FOR KEY SHARE on lease row |
src/core/db-lock.ts:226 | fence that the lease row is still owned (#7442) |
FOR KEY SHARE on schema-guard rows |
src/schema.sql:3072; src/core/shared-skills/schema.ts:65 | guard triggers |
NOWAIT: only one use, src/core/analyze-lock.ts:11-24: LOCK TABLE <t> IN SHARE UPDATE EXCLUSIVE MODE NOWAIT. SQLSTATE 55P03 means defer the ANALYZE (#7083). It is Postgres-only (tx.kind !== 'postgres' returns early).
lock_timeout as a bound on waits (not NOWAIT): src/core/postgres-engine/schema-lock-timeout.ts:1-165 (session lock_timeout, default 60 s, GBRAIN_SCHEMA_LOCK_TIMEOUT_SECONDS; on 55P03 it samples pg_blocking_pids and throws schema_lock_blocked); src/core/planner-stats.ts:209 (lock_timeout='2s'); src/core/persistence/git-durability-administration.ts:352; src/core/attendance-repair.ts:326; src/core/repair/missing-tables.ts:65; src/core/migrate.ts:230 (per migration).
5. Job queue (minions) with SKIP LOCKED, leases and retries
Files: src/core/minions/queue.ts (2574 lines; the main module), worker.ts (1792), supervisor.ts (1497), lock-renewal-tick.ts (680), child-worker-supervisor.ts (845), watchdog-attribution.ts, submission-authority.ts (380), admission.ts (383), idempotency-coalesce.ts (78), backoff.ts (26), handler-timeouts.ts (158), global-maintenance-stall.ts (117), src/commands/jobs/*.ts (work.ts 435, supervisor.ts 442, submit.ts 331, stats.ts 324).
Schema: minion_jobs (schema.sql:1248-1300). Key columns: status (CHECK at line ~1293: waiting, active, completed, failed, delayed, dead, cancelled, waiting-children, paused), priority, claim_generation BIGINT, attempts_made, attempts_started (CHECK attempts_made <= attempts_started), stalled_counter, max_stalled (default 5), lock_token TEXT, lock_until TIMESTAMPTZ, lock_duration_ms, timeout_ms, timeout_at, idempotency_key, parent_job_id (FK, ON DELETE SET NULL), private_queue_owner_job_id, private_queue_lease_until, submission_authority, spend_authorization, spend_claim_token.
Indexes: idx_minion_jobs_claim (queue, priority, created_at) WHERE status='waiting' (1304); idx_minion_jobs_stalled (lock_until) WHERE status='active' (1306); idx_minion_jobs_timeout (timeout_at) WHERE status='active' AND timeout_at IS NOT NULL (1309); uniq_minion_jobs_idempotency (idempotency_key) WHERE idempotency_key IS NOT NULL (1311); private-queue recovery and owner indexes (1315-1320).
| Operation | Lines (queue.ts) | SQL shape | Semantics |
|---|---|---|---|
| submit | 336-690 | SELECT ... FOR UPDATE on parent (561); INSERT ... ON CONFLICT (idempotency_key) WHERE NOT NULL DO NOTHING RETURNING * (623-627) |
idempotent submit; a conflicting concurrent submit returns 0 rows (669) and the caller reads the winner |
| claim | 1463-1520 | UPDATE minion_jobs SET status='active', claim_generation = claim_generation+1, lock_token=$1, lock_until = now() + clamp(lock_duration_ms, 5 s, 1 h), attempts_started+1, started_at = COALESCE(started_at, now()) WHERE id = (SELECT id ... WHERE queue=$3 AND status='waiting' AND submission_authority IS NOT NULL AND name=ANY($4) ... ORDER BY priority, created_at FOR UPDATE SKIP LOCKED LIMIT 1) RETURNING * |
exactly one claimant per row; ordering by priority then created_at; uses executeRawDirect (direct session pool, 1467-1470 comment) |
| renewLock (heartbeat) | 2096-2110 | UPDATE minion_jobs SET lock_until = now()+$1ms WHERE id=$2 AND lock_token=$3 AND status='active' |
fenced by token; cancellable; a late renewal can only extend a lock nobody else has taken (2092-2094) |
| lock-renewal tick | src/core/minions/lock-renewal-tick.ts (whole file) | timer wrapper with Promise.race timeout and "verify-before-evict" at the soft deadline (lockDuration - safetyMargin) |
a timed-out renew is not proof of loss |
| completeJob | 1745-1775 | parent FOR UPDATE, then UPDATE ... WHERE id=$2 AND status='active' AND lock_token=$3 RETURNING * in retryOnStalePlan |
fenced; a stale completion returns null |
| failJob | 1853-1890 | same fencing; attempts_made = attempts_made+1; sets delay_until from backoff; status = delayed (retry) or failed / dead |
exhausting max_attempts dead-letters |
| releaseLeaseFullJob | 2000-2016 | status→delayed, backoff, does NOT bump attempts (rate-lease bounce) |
fenced on lock_token |
| releaseConfigurationJob | 2041-2061 | fenced release to waiting, returns 'released' / 'no_op' / 'unconfirmed' | |
| handleStalled | 2166-~2250 | candidates WHERE status='active' AND lock_until < now() - grace; DELETE of stale lease-beacon rows whose gbrain_cycle_locks row is gone (2170-2171); co-runner requeue (watchdog-attribution.ts); dead-letter when stalled_counter+1 >= max_stalled (lockParentsOrdered first) |
stall means the lock expired; grace from GBRAIN_MINION_STALL_RECLAIM_GRACE_MS (queue.ts:88) |
| handleTimeouts | 1547-1585 | timeout_at < now() AND lock_until > now() → dead 'timeout exceeded', FOR UPDATE SKIP LOCKED |
stall → retry, timeout → dead; lock_until > now() distinguishes them |
| handleWallClockTimeouts | 1687-1722 | timeout = 2 * lockDuration * max_stalled fallback; FOR UPDATE SKIP LOCKED |
|
| promoteDelayed | 2153-2164 | UPDATE ... SET status='waiting' WHERE status='delayed' AND delay_until <= now() |
retried by lockRetry (issue #1678) |
| retryJob | 1227-1245 | resets attempts, stall counter, lock fields | operator retry |
| private queue lease renewal | 823-853 | SET private_queue_lease_until = GREATEST(COALESCE(...), now()+lease) |
lease only extends |
| killJobs (shared tail) | 1614-1672 | parents locked first, then SELECT id ... ORDER BY id FOR UPDATE (1673), notify parents |
Lease tokens: minion_jobs.lock_token is a per-claim random token (claim(lockToken, ...) at 1463). The gbrain_cycle_locks.acquisition_token UUID (lease-schema.ts:1-5, applied in schema.sql:1530-1532 as a GENERATED region from lease-schema.ts) is a separate lease identity for cycle, sync and maintenance locks; the beacon queue LEASE_BEACON_QUEUE stores that UUID in lock_token so a drain lease can be reaped (queue.ts:2170-2171 and the comment on migration v152).
Retry semantics: max_attempts default 3 (queue.ts:635) and backoff_type exponential with jitter 0.2 (635-638). attempts_made counts completed failures; attempts_started counts claims (chk_attempts_order at schema.sql:1296-1297). Retries are bounded by retryOnStalePlan (retry-matcher.ts:132) for lost plan races and lockRetry/withRetry only for idempotent promoteDelayed (queue.ts:2115-2140). claim and renewLock deliberately do not retry (comment at 2119-2125).
Exactly-once: a job is executed by at most one worker at a time because claim is FOR UPDATE SKIP LOCKED under one UPDATE. "Exactly-once effect" is not guaranteed: a stalled job is re-claimed, so handlers must be idempotent. The protocol trigger (schema.sql:1789-1810) rejects a claim that does not advance claim_generation by exactly 1, so an old binary cannot claim.
Wake-up: see section 2 (NOTIFY). The claim loop also polls.
Multi-process: the worker runs in jobs work (CLI), serve, child processes (child-worker-supervisor.ts, run-child.ts) and the supervisor (supervisor.ts). All of them use the same SQL claim, so the database is the coordination point. PGLite is a single process (see 11).
Dedup and admission: src/core/minions/admission.ts (383) and submission-authority.ts (380) combine the advisory-locked count (queue.ts:385-488) with the ON CONFLICT idempotency key.
6. Roles, RLS, grants, SECURITY DEFINER, search_path
RLS:
- src/schema.sql:1830-1861: enables RLS on 37 named tables (pages, content_chunks, links, tags, raw_data, timeline_entries, page_versions, ingest_log, config, files, minion_jobs, sources, access_tokens, mcp_request_log, minion_inbox, minion_attachments, subagent_*, gbrain_cycle_locks, dream_verdicts, eval_*, calibration_profiles, take_*, oauth_clients, oauth_tokens, oauth_codes, ...) only when the current role has BYPASSRLS or is superuser (the check at 1841). If not, it prints a WARNING (1861) and enables nothing.
- The same conditional pattern appears for each later table: schema.sql:2389-2393, 2674-2677 (git_canonical_brain), 3088-3094, 3158-3169 (decide_*), 3202-3291, 3312-3313 (fact_relink_attempts), 3340-3369 (link_*), 3418-3419 (core_edit_notices), 3438-3465 (fact_purges, take_purges, page_purges), 3479-3494 (derivation_inputs, needs_rederive), 3548-3550 (write_gate_*), 3617-3623 (pinned_questions, question_evidence), 3667-3725 (belief_*).
- Comment at schema.sql:1830-1832: "Enabling RLS with no policies means the anon key can't read anything. Only enable if the current role actually has BYPASSRLS privilege."
- There is no
CREATE POLICYanywhere in src (grep: zero matches). So RLS is "on with no policies": the table is denied to every role without BYPASSRLS. Access for those roles is the separate GRANT model (none in repo, see below). - Error classification for these denials: src/core/pg-access-classify.ts:59, 236 (SQLSTATE 28000 / 42501 mapped to
permission_denied, "RLS or missing GRANT").
GRANT / REVOKE: none in src (grep: zero statements). Privileges are assumed to come from the hosting platform (Supabase roles, the postgres role). Migrations v278, v281, v283 say they do not change privileges (v278-writer-fence-fail-closed.ts:25, v281:27, v283:35).
SECURITY DEFINER: none. derived-state-maintainers.ts:13 says so explicitly (#7659). All trigger functions are SECURITY INVOKER.
search_path hardening: src/core/function-search-path.ts:
LEGACY_SEARCH_PATH_SETTINGS(25):pg_catalog, publicandpg_catalog.PINNED_FUNCTION_NAMES(37-52): 56 function names gbrain installs in public. The list is used by hardening, the doctor check and tests (test/function-search-path-pglite.test.ts, test/e2e/function-search-path-postgres.test.ts, scripts/check-search-path.sh).hardenFunctionSearchPaths(engine)(100):ALTER FUNCTION ... SET search_path = ..., pg_tempfor owned functions only (thepg_has_role(current_user, p.proowner, 'USAGE')check at schema.sql:3788). Runs as the last statement of the schema blob (per the comment at 6-18) and at the end of every migration run.- Why pg_temp is appended: a pinned path that does not name pg_temp lets a temp table shadow the ledgers a guard reads (fact_purges, page_purges, take_purges, fact_withdrawals). Comment at function-search-path.ts:6-10 (#7632).
- Schema blob list: schema.sql:3787 repeats the names.
Access control model (application level):
- src/core/scope.ts (35 matches of the scope patterns), src/core/access/* (policy, members, attest-sources, open-pages, folder rules), src/core/grants/* (model, service, legacy-token, profiles, shared-exposure, lifecycle), src/core/oauth-grants.ts, src/core/oauth-provider.ts, src/core/persistence/authority.ts and ownership.ts, src/core/minions/submission-authority.ts. These decide who may read or write a page, source, job or shared skill. They run in the gbrain process, not in Postgres.
- Postgres-side authority is limited to the GUC guards listed in section 1, which read values set by the same gbrain process. A session with direct database access can set those GUCs itself.
Supabase assumptions:
- The
postgresrole with BYPASSRLS is assumed (schema.sql:1830-1832; connection-manager.ts:6-7 mentions the Supabase pooler and its 2-minute statement_timeout). - Pooler URL conventions: src/core/supabase-pooler-url.ts (deriveDirectUrl, deriveSessionPoolerUrl, isSupabasePoolerUrl), used by connection-manager.ts:46 and 118-125.
application_nameisgbrain <kind>:<pid>:<nonce8>(db.ts:350-357) so the owner can be found in pg_stat_activity; the comment says this is partial behind a transaction pooler.
7. Transactions, isolation, timeouts, cancellation, retries
Transaction entry points:
beginReadWrite(pool, fn)(src/core/postgres-engine/read-write-transactions.ts:37-47):pool.begin('read write', fn). On a standby (SQLSTATE 0A000 "read-write mode during recovery") it reopens without the mode.readWriteTransactions(conn)(read-write-transactions.ts:49-56): wraps a pool so everybeginis read-write.retryReadOnlyAutocommit(conn, run)(read-write-transactions.ts:63-76): on 25006 (read_only_sql_transaction) re-runs the statement insideBEGIN READ WRITEonce; a 25001 retry error reports the original.reservedTransactions(reserved)(src/core/postgres-engine/reserved-transactions.ts:37-111): BEGIN READ WRITE, savepoints nameds0..sN, pipelines BEGIN with the first statement unless the pool is a transaction pooler (max_pipeline0, #6543); a COMMIT that does not answer COMMIT throws 25P02 (reserved-transactions.ts:68).withTransactionAbort(signal, run)(src/core/postgres-engine/transaction-abort.ts:55-87): on abort,cancelAndDiscard()sends a CancelRequest, waits for settle, then discards. ThrowsAbortErrorwithcauseandcancelFailure(CANCEL_UNCONFIRMED).reserveWithCancellation(reserve, signal)(src/core/postgres-engine/cancellation.ts:14-43): races the connection reserve against the signal. A driver withoutdiscardfails withpostgres_cancellation_unavailable(cancellation.ts:7-12).
Bounded statements:
runBoundedStatement(src/core/postgres-engine/bounded-statement.ts:29-59):BEGIN READ WRITE; SET LOCAL statement_timeout = <ms>; [SET LOCAL jit = off]pipelined with the statement and COMMIT, on one reserved connection. Used for #6278 preparation reads.runDeadlineRead(bounded-statement.ts:62-110):BEGIN READ ONLY; SET LOCAL statement_timeout = <time left>, withstuckdiscard aftersettleMsif COMMIT does not return (#8651).jitOff(src/core/postgres-engine/jit-off-statement.ts:1-60): readscurrent_setting('jit')once per pool, addsSET LOCAL jit = offonly when JIT is on. Restores the caller's value inside a caller's transaction.- Keyword timeout (src/core/postgres-engine/keyword-statement-timeout.ts:1-40): transaction-local
statement_timeoutaround keyword/title/CJK search, default 8000 ms, range 1000-60000 (lines 24-26). Resolved once per connection. - Keyword negative cache (src/core/postgres-engine/keyword-timeout-cache.ts): after a 57014 on a keyword statement, the same statement (hashed with its parameters) is refused for
search.keyword_timeout_negative_cache_seconds(default 600). - CJK search: src/core/engine-sql/cjk-search.ts:12, 138 (
set_config('statement_timeout', ..., true)). - Planner refresh: src/core/planner-stats.ts:208-209 (
statement_timeout='60s',lock_timeout='2s'). - Vector prewarm: src/core/vector-prewarm.ts:152 (
SET LOCAL statement_timeout = 0). - Migrations: src/core/migrate.ts:211-230 (per-migration
SET LOCAL statement_timeout = migrationStatementTimeoutMs()andSET LOCAL lock_timeout = resolveSchemaLockTimeoutMs(), insideengine.transactionunlesstransaction: false). - Session defaults: src/core/db.ts:346-347 (
statement_timeoutandidle_in_transaction_session_timeout= 5 min), delivered as startup parameters at db.ts:440-456 and the connection option at db.ts:544. A transaction pooler drops them (db.ts:335-340), so the guarantee is absent through port 6543. - DDL pool (src/core/connection-manager.ts:97-100, 441-446):
statement_timeout30 min, idle-in-transaction 5 min. - Initial lock acquisition for init:
SET LOCAL idle_in_transaction_session_timeout = 0(init-schema-lock.ts:201) so the migration lock transaction can sit idle.
Isolation levels:
- READ COMMITTED explicitly for the migration lock transaction (init-schema-lock.ts:199) with the comment that a snapshot would make CREATE INDEX CONCURRENTLY wait on it.
- REPEATABLE READ only in src/core/ops/pages.ts:159 and src/core/ops/valid-time-read.ts:318 (
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ). - No SERIALIZABLE anywhere in src (grep: zero). Serialization conflicts are not retried by code.
Retry and error classification:
isRetryableConnError(src/core/retry-matcher.ts:157-165): connection-class SQLSTATEs (08xxx, 57P01-03, 53300), excludes 57014 and lock timeouts.isStatementTimeoutError57014;isLockTimeoutError55P03 (retry-matcher.ts:145).withRetryandBULK_RETRY_OPTS(src/core/retry.ts, 359 lines):maxRetries=3, delayMs=1000, delayMaxMs=10000, jitter='decorrelated'.- Deadlock: src/core/migrate.ts:258-370 retries once after 250 ms on 40P01 (SQLSTATE check at line 368, also by message at 370).
- Persistence transient codes: src/core/persistence/transient-retry.ts:62 lists 40001, 40P01, 55P03, 57014, 53300, 57P01-03, 08xxx.
- Repair/item classification: src/core/connectors/item-holds.ts:110 maps 57014 to
statement_timeout, 55P03 tolock_timeout, 40001 and 40P01 todatabase_contention. - Engine error predicates: src/core/engine-errors.ts (PageMissingError only; no SQLSTATE here). The 42703 predicate guard is scripts/check-schema-error-predicates.ts (ALLOWLIST and
isMissingSchemaObject).
SQLSTATEs branched on in src (non-migration, approximate counts from rg): 57014 (about 20 sites), 42501 (about 20), P0001 (about 20, raised by gbrain guard triggers), 55P03 (about 10), 40P01 (3), 25006 (1), 25001 (1), 25P02 (1), 0A000 (2), 42P01 (about 5), 42703 (about 5), 42P07 (1), 23505 (1), 23503 (3), 53300 (1), 57P01/57P02/57P03 (1 each), 08006 (2). The list is in the grep output of section 7; the authoritative list is rg -n "'[0-9A-Z]{5}'" src.
Cancellation:
- Query cancel is
CancelRequestthrough the driver'scancelAndDiscard, notpg_cancel_backend. Nopg_cancel_backendcall exists in src.pg_terminate_backendappears only in operator-facing text (init-schema-lock.ts:143-145, migrate.ts:69, 141, 567). - The worker's abort path is
renewLockwith anAbortSignal(queue.ts:2092-2094). The statement is cancelled and the reservation discarded. - Pool stuck watchdog: db.ts:400-440 (
inflight_max= statement_timeout + grace;onstuck).
The engine.transaction callback form is the normal write path. The engine-level wiring was not re-verified line by line in this pass; the read-write wrapper is beginReadWrite (read-write-transactions.ts:37).
8. Connection-level and runtime features
Pools and modes:
ConnectionManager(src/core/connection-manager.ts): three pools.read(lines 263-276:max = resolvePoolSize,idle_timeout 20 s,max_lifetimefromresolveMaxLifetimeSeconds);direct(425-445:prepare: true, the DDL and session-mode path,application_name);ddl(statement_timeout 30 min). Pool sizes:GBRAIN_POOL_SIZE(db.ts:248-253, default 10),GBRAIN_DIRECT_POOL_SIZE(connection-manager.ts:167-172, default 3 resident / 1 otherwise).- Transaction pooler detection: port 6543 is a transaction pooler (connection-manager.ts:118-124
isTransactionPoolerUrl; db.ts:65-72AUTO_DETECT_PORTS). On that port:prepare: false(db.ts:65-79) andmax_pipeline: 0(db.ts:137-146) unless the URL overrides. max_lifetimejittered 30-60 min per connection (db.ts:300-305);GBRAIN_POOL_MAX_LIFETIME_Soverrides (db.ts:277-298).- Connection timeout:
resolveUrlConnectTimeout(db.ts:166-240) readsconnect_timeoutfrom the URL. - Pool stats: src/core/postgres-engine/pool-stats.ts (driver queue counters: max, open, busy, full, reserved, connecting, queued, inflight_oldest_ms; the comment at line 9 says a transaction pooler's queue is invisible to the server).
- Pool stall watch: src/core/postgres-engine/pool-stall-watch.ts:1-76; 30 s tick (
POOL_STALL_TICK_MS, line 19); warnspg_pool_stalledwhen connections are checked out and no statement completed for two ticks;pg_pool_recoveredon recovery. - Pool gauge: src/core/pool-gauge.ts (CheckoutGauge: acquire, checkedOut, release).
- Pooler notices: src/core/pooler-notice.ts (warnPoolerPrepareOffOnce).
- Direct vs pooled routing for each operation:
executeRawDirect(queue.ts:1467-1470, 1497; renewLock 2099-2102) runs on the session pool;executeRawuses the read pool. The comments say the heartbeat must not run on the transaction pooler.
Introspection used at runtime:
pg_stat_activityandpg_locks: init-schema-lock.ts:66-73, 166-169 (holder pid for the advisory lock); schema-lock-timeout.ts:60-63 (pg_blocking_pids); migrate.ts:102-136 (idle-in-transaction blockers older than 5 min, excludes the migration lock's application_name).current_setting('server_version_num'): db.ts:211; migration-preludes.ts:123; graduation-target.ts:203; schema-migrations/v011-links-provenance-columns.ts:26.current_setting('jit'): jit-off-statement.ts:37, 48.pg_size_bytes(current_setting('max_wal_size'))etc.: pglite-engine/checkpoint-guard.ts:118 (PGLite only).pg_proc.proconfig,pg_roles,pg_class.relrowsecurity,pg_has_role: schema.sql:1841, 1852, 3787-3788; function-search-path.ts:77 (legacySearchPathFunctions).pg_stat_*: src/core/planner-stats.ts, src/core/search/projection-statistics.ts (36 matches), src/core/embedding-dim-check.ts, src/core/schema-verify.ts, src/core/owned-table-shapes.ts. These read planner statistics and table shapes; detailed use is covered in the query-features part (pf-1) and schema parts.information_schema: used by schema verification (grep count per file was highest in test helpers and src/core/schema-verify.ts).- COPY: no
COPYstatement in src. Bulk loads use multi-row INSERT / UNNEST (covered in be-2 and pf-1). - VACUUM: only
VACUUM <tables>in src/core/facts/purge-verify.ts:561 (PGLite purge path) and a text suggestionVACUUM (VERBOSE)for Postgres (purge-verify.ts:586-587, 730). No autovacuum tuning in code. - ANALYZE: planner-stats.ts (PGLite row-delta ANALYZE, header lines 2-23,
planner.auto_analyzesetting,import.analyze_every_pagesdefault 500 at line 37); Postgres ANALYZE is wrapped inlockTableForAnalyze(analyze-lock.ts:11-24) so it is deferred rather than waiting (#7083, behavior-change-notice.ts:985). - CREATE INDEX CONCURRENTLY: src/core/vector-index.ts:290 (
CREATE INDEX CONCURRENTLY IF NOT EXISTS <tempName>then swap); src/core/embedding-ann-build.ts:100-103 (drop-invalid then CREATE CONCURRENTLY on a reserved backend, session timeout lifted because CONCURRENTLY cannot run in a transaction; comment at 101); src/core/backfill-base.ts and src/core/repair/request-indexes.ts; many migrations withtransaction: false(see sc-3 and sc-4 for the list). - Sequences: SERIAL and BIGSERIAL primary keys throughout schema.sql (for example 100, 355, 596, 814, 916);
page_generation_clock_seq(schema.sql:250-269) via nextval, which is non-transactional in Postgres. - Error codes at connection level: 08xxx treated as retryable (retry-matcher.ts:157-165); 08006 in src/core/audit/lock-renewal-audit.ts.
Prepared statements: prepare: false through the transaction pooler (db.ts:65-79); prepare: true on the direct pool (connection-manager.ts:433). runBoundedStatement takes a prepare option (bounded-statement.ts:29-59, default true) and passes it through to the statement.
9. Introspection, planner and maintenance interactions (cross-cutting)
- A brain that the planner misjudges (no ANALYZE) reads slowly. The engine owns ANALYZE timing: after embed drains, imports (every 500 pages), and by row delta on PGLite. These call sites are in src/core/planner-stats.ts and src/core/embed-*.
- ANALYZE and autovacuum share SHARE UPDATE EXCLUSIVE. An ANALYZE that waits cancels a non-wraparound autovacuum after deadlock_timeout (analyze-lock.ts header). gbrain prevents that with NOWAIT.
CREATE INDEX CONCURRENTLYcannot run in a transaction and waits for older snapshots. The migration lock uses READ COMMITTED for that reason (init-schema-lock.ts:199).- Schema replays (CREATE INDEX, ALTER TABLE ENABLE ROW LEVEL SECURITY) take ACCESS EXCLUSIVE. The RLS block at schema.sql:1841-1858 is conditional so a re-run does not take the lock (#5227 note at 1843-1845).
10. Multi-process concurrency model (central risk)
Processes that can touch one brain at the same time: the CLI (each gbrain <cmd> is a separate process with its own pool or PGLite handle), gbrain serve (MCP server, HTTP), gbrain jobs work and the supervisor (child processes), cron or launchd jobs (autopilot, cycle), and hook commands (src/commands/hook.ts, short CLI processes). Evidence: src/core/minions/child-worker-supervisor.ts, src/commands/jobs/supervisor.ts, src/core/autopilot-lock.ts, src/core/cli-socket/server.ts, src/core/persistence/ipc.ts (Unix socket IPC, 519 lines), src/core/persistence/coordinator.ts (898 lines, 'writer coordinator').
Postgres: each process has its own connection pools. Coordination is by:
- Advisory locks (key 42 schema; per-key xact locks), section 3.
- TTL lease rows (
gbrain_cycle_locks), section 5 and db-lock.ts. Holder pid, host, token; steal only after TTL expiry (db-lock.ts:267-277), fenced refresh (db-lock.ts:299-317), reap dead same-host holders after a grace window (db-lock.ts:~60-90, "PID reuse" comment). - Row locks and
SKIP LOCKED, section 4-5. - The persistence coordinator (persistence_brain singleton, persistence_requests, effects, writer guards; section 1). It appears to be the design for "one writer per brain", with
persistence.single_consumeras a switch (behavior-change-notice.ts:983).
PGLite: one process at a time per data dir. Lock is a kernel lock through a native binding (src/core/persistence/native-lock.ts:1-25, tryAcquireNativeLock, "Stable OS locks for cooperating writers on one owner host. No TTL takeover"), with metadata (pid, process start time, boot id, pid namespace) in <dataDir>/.gbrain-lock/lock (pglite-lock.ts:11-100). Heartbeat every 30 s (pglite-lock.ts:11). Contention returns PgliteBusyError (pglite_busy, retryable) (pglite-lock.ts:25-30). A legacy .gbrain-lock folder with an unverifiable owner returns a non-retryable LegacyOwnerUnverifiedError (pglite-lock.ts:33-42).
Consequences:
- On Postgres, several processes run concurrently and rely on the database locks above. This is the supported multi-writer mode.
- On PGLite, only one gbrain process can hold the data dir; a second CLI command, or the MCP server while a CLI runs, gets
pglite_busy(or waits, depending on the caller's wait budget). The MCP server and cron are therefore single-owner. - Hooks and cron jobs inherit the same constraint; the code surfaces it as a typed error, not as silent corruption.
- The single-writer design is also why the PGLite checkpoint guard and in-process write queue exist (section 11).
11. PGLite-specific runtime behavior
| Feature | Behavior | Location |
|---|---|---|
| Single connection | all statements go through one PGlite handle; _dbWork.admit serializes writes and page transactions |
src/core/pglite-engine.ts:744-762, 795, 1209-1222 (transaction uses db.transaction and the admit gate) |
| No advisory locks | PGLite takes no advisory lock in initSchema (comment in init-schema-lock.ts:1-3, executor.ts:66, facts.ts:7). The schema lock is the in-process inProcessSchemaLocks WeakMap |
src/core/schema-migration-lock.ts:9-38 |
| No row-level concurrency | FOR UPDATE and SKIP LOCKED are accepted but meaningless with one connection; the same SQL runs and succeeds. The queue claim is still FOR UPDATE SKIP LOCKED |
src/core/minions/queue.ts:1511 |
| Checkpoint guard | before an outermost write transaction, runs CHECKPOINT when WAL since redo passes half of the automatic trigger (256 MB cap). Prevents a nested checkpoint that spins at 100% CPU on PGLite 0.4.x |
src/core/pglite-engine/checkpoint-guard.ts:1-171 (threshold 17-20, writesWal at 27-31, probe reuse 50 ms at ~36-45) |
| No statement timeout | keyword searches run with no statement timeout | src/core/postgres-engine/keyword-statement-timeout.ts (header, "PGLite runs these statements with no statement timeout") |
| No autovacuum; manual VACUUM | purge verification runs VACUUM <tables> on PGLite and suggests VACUUM (VERBOSE) on Postgres |
src/core/facts/purge-verify.ts:561-595, 730 |
| Planner ANALYZE by row delta | PGLite has no autovacuum, so gbrain runs ANALYZE itself | src/core/planner-stats.ts:2-23 |
| WASM heap | pglite.wasm caps its heap at 2 GiB; HNSW build is restricted | src/core/vector-index.ts:66 onwards |
| Init failure classification | wasm-abort, corrupt, bunfs verdicts for WASM init errors |
src/core/pglite-engine.ts:403-470 |
| WAL repair | pglite-repair backups and restores pg_wal and pg_control |
src/core/pglite-engine.ts:505-520; src/core/pglite-resetwal.ts |
| Schema blob | no notify_minion_job_change trigger, no minion_job_notify trigger in the PGLite template (src/core/pglite-schema.generated.ts); function names are still in the pinned list |
src/core/pglite-schema.generated.ts |
| Savepoint-free transactions | PGLite transaction helper composablePgliteTransaction and transactionMemo (page-state) |
src/core/page-state/transactions.ts |
12. Access control and authorization summary
- Database-level: RLS enabled, no policies, bypass by the gbrain role (section 6). No GRANT/REVOKE in code. The platform provides roles.
- Application-level:
src/core/access/*,src/core/grants/*,src/core/scope.ts,src/core/persistence/authority.ts,src/core/minions/submission-authority.tsdecide read and write rights. All checks run in process before SQL, plus GUC allowlists inside triggers. - Token-level: src/core/oauth-grants.ts (FOR UPDATE on codes and tokens, single-use exchange), src/core/token-mint.ts, src/core/grants/legacy-token.ts.
- Job-level authority:
submission_authoritymust be set (trigger at schema.sql:1792) andspend_authorizationgates spend-bearing jobs (trigger at 1795-1800).
13. Candidate embedded Rust replacements
Notation: "parity" is what the agent-facing contract needs. The contract only needs the observable results, not the Postgres locks.
| Feature | Rust candidates | Tradeoffs | Parity risks |
|---|---|---|---|
| Single-writer transactions (BEGIN, savepoints, read-write vs read-only) | rusqlite (bundled SQLite) with WAL + BEGIN IMMEDIATE for writers and one reader pool; redb (MVCC, one write txn at a time); fjall (LSM with keyspaces) |
SQLite gives SQL and savepoints; BEGIN IMMEDIATE serializes all writers DB-wide (coarser than the per-key xact locks here); redb has no SQL, so all queries are hand-coded | Savepoints map to SAVEPOINT; READ COMMITTED vs REPEATABLE READ snapshots differ (WAL read txn is a snapshot per read transaction); the two REPEATABLE READ sites and the READ COMMITTED migration lock have no direct counterpart |
| Advisory locks (42 and per-key xact keys) | In-process keyed mutex (dashmap of tokio/parking_lot mutexes) for per-key work; BEGIN IMMEDIATE for cross-process writes; file lock on the DB path (fs4 / fd-lock) for the schema lock | Keyed mutexes are not cross-process; file locks are advisory and need stale-holder reaping (reuse the pid + start-time + boot-id logic in pglite-lock.ts) | Shared vs exclusive derivation-scope locks (page-body/schema.ts:279) need an RW lock per source; hash collisions in hashtext keys disappear |
| Row locks (FOR UPDATE / FOR SHARE / KEY SHARE / NOWAIT) | Under single-writer, all of these become "the write transaction holds"; NOWAIT becomes busy_timeout = 0; FOR SHARE on sources (writers wait for purges) becomes a per-source RW lock |
Coarser serialization than Postgres row locks: writers on different sources wait on each other unless the app keeps per-source locks | Deadlock-free ordering in lockPageKeys (page-state/guards.ts) is a Postgres requirement; with one writer lock it is moot but any per-key locking added later needs the same sorting |
| SKIP LOCKED job queue | Queue table with lease columns (lock_token, lock_until, claim_generation, attempts_*) claimed with UPDATE ... WHERE id = (SELECT ... LIMIT 1) RETURNING inside BEGIN IMMEDIATE; or a separate job store (redb table keyed by (queue, priority, created_at, id)) |
SQLite has no SKIP LOCKED but single-writer serialization gives the same exactly-one claim; throughput is bounded by the writer | Lease timing uses now() and interval math; the Rust port needs one clock source (monotonic for lease duration, wall for persisted timestamps); claim_generation trigger rules must become app-level invariants |
| Fenced lease token (gbrain_cycle_locks, acquisition_token) | same table in SQLite with INSERT ... ON CONFLICT(id) DO UPDATE ... WHERE ttl < ? (SQLite supports upsert with WHERE since 3.24) |
Same semantics; holder pid and host are diagnostic | FOR KEY SHARE fence (db-lock.ts:226) has no SQLite equivalent; a fence check inside the same write transaction is equivalent under single-writer |
| LISTEN/NOTIFY (wake idle workers) | tokio::sync::watch or broadcast inside one process; cross-process: PRAGMA data_version polling on a read connection, or inotify/notify crate on a sentinel file or the WAL file; Unix socket (the existing persistence/ipc.ts pattern) |
Inotify is not reliable on network filesystems; data_version is a poll, cheap (no SQL write) | Loss tolerance is already part of the design (poll backstop, sync-queue-wake.ts:16) so a lossy wake is acceptable |
| Triggers (about 25 plpgsql functions) | SQLite triggers (BEFORE/AFTER, RAISE(ABORT, msg)) for row maintenance; app-level hooks for the GUC guards and cross-table checks | SQLite triggers can not call functions, read other GUCs or call pg_notify; the GUC allowlists (gbrain.write_sources, persistence_protocol) must become app-level checks or a config table read inside the trigger | The managed-writer and protocol triggers are fail-closed: the Rust port must fail the same writes, with the same error codes (P0001 messages) if the contract shows them |
| Advisory xact lock for derivation scope (shared trigger) | write-side "shared" holder = normal write transaction; purge = BEGIN IMMEDIATE | Equivalent when one writer | None if writers are serialized |
| RLS / GRANT / roles | Application authorization in Rust (typed access context passed to every query, as src/core/scope.ts and access/* already do); file permissions on the DB file; no DB roles | Loses defense in depth for a second SQL client; any process with file access sees everything | Hosted Supabase RLS deny-by-default (schema.sql:1830-1832) has no embedded counterpart; the anon key path does not exist in an embedded store. The doctor checks for RLS (pg-access-classify.ts) become moot |
| SECURITY DEFINER / search_path hardening | Not needed in an embedded store; SQLite has no schemas to shadow and no pg_temp equivalent (temp tables still shadow main if not qualified; use main. qualifiers) |
Shadowing risk is a Postgres-only failure mode | A temp table named fact_purges must not shadow in SQLite either; qualify names or disable temp_store for the guard connections |
| Statement timeout / lock_timeout / idle_in_transaction | SQLite busy_timeout for lock waits; sqlite3_interrupt plus a progress handler for statement deadlines (rusqlite InterruptHandle, progress_handler); idle transaction timeout via an app-side timer that rolls back |
The Postgres timeouts are server-side; the SQLite ones are client-side and need the interrupt to run | Keyword search 8 s bound (keyword-statement-timeout.ts) and the 57014 negative cache must be re-expressed as a deadline on the interrupt; the cache semantics are app-level and portable |
| Cancellation (cancelAndDiscard, CancelRequest) | Cancellation token + InterruptHandle::interrupt(); dropping the connection rolls back |
In SQLite the interrupt aborts the statement and the transaction is rolled back by the caller | "CANCEL_UNCONFIRMED" class (transaction-abort.ts:55-87) disappears; keep the observable error shape (AbortError with cause) |
| Retries on 40P01 / 40001 / 55P03 | Under single-writer these become SQLITE_BUSY / SQLITE_LOCKED (busy after busy_timeout); retry on BUSY with bounded backoff | No deadlock exists in one-writer design; the retry matcher still needs a BUSY class | Error-class names used by doctor/item-holds (item-holds.ts:110) must map SQLITE_BUSY to the same labels |
| Pools, PgBouncer, pooler routing | Not needed: one connection per process plus a read pool (rusqlite connection pool or r2d2) | Removes PgBouncer parity entirely; the executeRawDirect vs executeRaw split disappears | Pool stall watch (pool-stall-watch.ts) and in-flight watchdog (db.ts:400-440) lose their subject; keep a stall detector on the writer mutex if the contract exposes pg_pool_stalled |
| Introspection (pg_stat_*, pg_locks, pg_blocking_pids, information_schema) | sqlite_master, PRAGMA table_info, PRAGMA index_list, PRAGMA stats; lock holder = lock-file metadata | Different shape; doctor and schema-verify rewrites | Doctor output and schema-verify messages name Postgres views; keep JSON shape where the CLI contract needs it |
| CREATE INDEX CONCURRENTLY, VACUUM, ANALYZE | CREATE INDEX (blocks writers, brief), PRAGMA optimize / ANALYZE, VACUUM (needs no open transaction; blocks writers) or auto_vacuum=incremental |
The concurrent index build is not needed for correctness; it is needed only to avoid writer stall on large brains | The ANALYZE deferral (analyze-lock.ts) becomes moot; index build duration becomes a visible stall |
| Sequences (SERIAL, nextval, page_generation_clock_seq) | rowid / AUTOINCREMENT; a counter table updated in its own tiny write txn | Postgres sequences are non-transactional (gaps on rollback, not undone); SQLite rowids roll back with the transaction | If any consumer relies on non-rollback uniqueness across aborted transactions (page_generation_clock_seq, 250-269), a separate durable counter is needed |
| COPY | none needed; batched INSERT with prepared statements | none | none observed |
| JSONB operators and trigger-backed JSON validation | SQLite JSON1 functions | out of scope here (pf-1 owns JSONB parity) | |
| Process model (CLI + MCP + cron + hooks on one brain) | (a) single writer daemon that owns the DB file and serves CLI/MCP over a Unix socket (reuse persistence/ipc.ts and cli-socket/server.ts design); (b) SQLite WAL multi-process with BEGIN IMMEDIATE and busy_timeout (CLI and MCP each open the file, writers queue); (c) redb or fjall: exclusive open per process, so only (a) works | (a) gives exact single-writer semantics and one lease owner; (b) needs no daemon but every process must handle SQLITE_BUSY and long write transactions block others; (c) cannot be shared across processes at all | This is the central risk: today on Postgres many processes write concurrently; on PGLite they cannot. Pick (a) or (b) deliberately. (c) reproduces the PGLite single-owner failure (pglite_busy) for every concurrent command |
Suggested first cut (for discussion): SQLite WAL with one writer connection and BEGIN IMMEDIATE, a table-based job queue with lease columns and fenced updates (portable from queue.ts), PRAGMA data_version for cross-process wake, a keyed in-process mutex for per-source work, and application-level authorization. Keep the schema-level guards as app checks and a small set of SQLite triggers for the pure row maintenance (search vector, revision counters).
14. Parity risks noted
- Multi-process writers: Postgres supports concurrent writers from CLI, MCP, cron and hooks; PGLite supports one process. The Rust port must pick a model (daemon owner, SQLite multi-writer with BUSY handling, or exclusive file lock) and say which commands fail or wait when another process holds the brain. Any choice that is not a daemon re-creates the pglite_busy failure for concurrent commands.
- Job exactly-once: the claim relies on
FOR UPDATE SKIP LOCKEDplusclaim_generationchecked by a trigger. Without a DB-level trigger, the protocol invariant (advance by exactly 1 on claim) must be an app invariant and tested. - Lease fencing: every completion and renewal is
WHERE lock_token = $token AND status = 'active'. Keep this exact predicate; a non-fenced update lets a stalled worker finish someone else's job. - Time semantics: lock expiry is
lock_until < now() - graceandtimeout_at < now() AND lock_until > now(). Postgresnow()is the transaction start time. Rust must not evaluate these twice with different clocks inside one transaction. - Non-durable wake: NOTIFY is lossy; the poll backstop must remain. A
PRAGMA data_versionpoll or a file watch is also lossy under load; keep the poll. - Fail-closed guards: the managed-writer, protocol and allowlist triggers reject writes with SQLSTATE P0001 and typed messages (for example
writer_coordinator_required,git_protocol_required). Parity requires the same refusal, not only the same data. - Derived-state triggers: about 10 trigger functions update derived tables (search vector, revision counters, projection queue, derivation scope). Missing one silently breaks search or purge. Port them as explicit write-path functions with tests that compare outputs with the trigger version.
- Statement deadlines: keyword search has an 8 s server-side bound (default) and a 600 s negative cache; the Rust port needs an interrupt-based deadline with the same refusal (KeywordSkippedAfterTimeoutError) so callers see the same skip.
- Isolation: REPEATABLE READ is used in two read paths for a consistent snapshot (ops/pages.ts:159, ops/valid-time-read.ts:318). A single-connection read transaction in SQLite gives an equivalent snapshot only if the read transaction is opened first.
- Access control: no RLS, no GRANT. Authorization is application code. An embedded store exposes the file to any local process; the doctor checks for BYPASSRLS and grants have no Rust counterpart and should be removed or reported as not applicable.
- Sequences:
page_generation_clock_seq(schema.sql:250-269) uses nextval so concurrent writers do not serialize on a tuple. With SQLite and one writer the hotspot disappears, but the monotonic-across-rollback property is lost unless a separate counter is kept. - Maintenance: ANALYZE/VACUUM/CREATE INDEX CONCURRENTLY have no SQLite equivalent with the same non-blocking property. Index builds on a large brain will stall writers.
- Connection-level SQLSTATEs: about 15 distinct SQLSTATEs are branched on. Keep an internal error enum with a mapping table; do not expose SQLite codes in the contract.
- PGLite checkpoint guard and WASM heap limits: they are PGLite-specific. The Rust port does not need them, but the migration from PGLite data dirs (pglite-repair, pglite-resetwal) is a separate parity item.
- Migration lock: the schema lock is held across the whole migration run in a READ COMMITTED transaction (init-schema-lock.ts:189-226). The Rust port needs a file lock or BEGIN IMMEDIATE held for the same span, with the same stale-holder reaping and the same operator-facing message.
- Session defaults through pooler: statement_timeout and idle_in_transaction are startup parameters that PgBouncer drops (db.ts:335-340). The fix in production is ALTER ROLE. The Rust port has no such gap but must not silently drop its bounds.
- Tests that cover this area: test/e2e/pgbouncer-transaction-pipeline-6930.test.ts, test/init-schema-lock.test.ts, test/e2e/init-schema-lock-blocked.test.ts, test/e2e/persistence-chaos.test.ts, test/e2e/analyze-yields-to-autovacuum-postgres.test.ts, test/e2e/rls-every-table-postgres.test.ts, test/e2e/function-search-path-postgres.test.ts, test/function-search-path-pglite.test.ts, test/jit-off-statement.test.ts, test/core/retry.test.ts, test/migrate.test.ts, test/e2e/postgres-stuck-connection-postgres.test.ts, test/e2e/core-memory-locks-postgres.test.ts. These are the parity suites to port first.
Not covered in depth (time-boxed): the bodies of the about 56 pinned trigger functions in schema.sql 1899-3794 (listed by name), the full persistence/ subsystem (about 150 files, only the lock, coordinator and ipc entry points were read), and the complete SQLSTATE inventory (the grep counts above are approximate).