Garry's Opinionated OpenClaw/Hermes Agent Brain
TypeScript
/**
* Timeline and Life Chronicle reads: one SQL implementation for both engines
* (refactor wave 1, W1-extended). Statement text is PostgresEngine's master
* text (SQL-text golden `sql-text/timeline.json`); PGLite runs the same
* statements (docs/designs/refactor-wave-1/w1-inventory.md, "timeline").
*
* The JSONB batch insert keeps master's executeRaw path (executeRawJsonb over
* the executor); the engine re-resolves the executor on each batchRetry
* attempt. Every read was unscoped on master (EO4 inventory):
* `LegacyUnscopedRead`.
*/
import type { TimelineBatchInput } from '../engine.ts';
import type {
PageReadScope,
TimelineEntry, TimelineInput, TimelineOpts,
ChronicleTimelineRow, ChronicleTimelineOpts, LastSeenResult,
} from '../types.ts';
import { executeRawJsonb } from '../sql-query.ts';
import { sanitizeForJsonb, buildTimelineRows } from '../batch-rows.ts';
import { finalizeLastSeen } from '../chronicle/last-seen.ts';
import { privatePagesFilterForCaller, privateTimelineEventFilterForCaller } from '../search/private-visibility.ts';
import { PageMissingError } from '../engine-errors.ts';
import type { SqlExecutor } from './executor.ts';
import type { LegacyUnscopedRead } from './brands.ts';
import { projectionEligibleSql } from '../eligibility/sql.ts';
import { sqlFragment, trustedSql } from './fragment.ts';
export async function addTimelineEntry(
exec: SqlExecutor,
slug: string,
entry: TimelineInput,
opts?: { skipExistenceCheck?: boolean; sourceId?: string },
): Promise<boolean> {
const sourceId = opts?.sourceId ?? 'default';
// #4109: page resolution and insertion share ONE statement snapshot, so a
// concurrent hard delete linearizes before the lookup (missing) or after
// the insert (success) instead of surfacing a raw FK violation; FOR KEY
// SHARE holds the referenced row through the insert. ON CONFLICT DO
// NOTHING via the (page_id, date, md5(summary), source) unique index
// (#3737: md5-keyed so long summaries fit the btree row cap).
// #3827: the `inserted` flag makes the outcome observable — with the
// page_exists throw below (default) a false return unambiguously means
// "deduplicated", and under skipExistenceCheck the caller asserts the
// page exists. Source-qualify the page-id lookup so multi-source brains
// don't fan timeline rows out across every source containing the slug.
// Free-text body fields are NUL + lone-surrogate sanitized (#2011) so a
// surrogate from sliced/imported content can't reach the (later) ::jsonb
// batch path or corrupt the row; identity fields (slug, date) are left raw.
const [result] = (await exec.run(sqlFragment`
WITH page_state AS (
SELECT id FROM pages WHERE slug = ${slug} AND source_id = ${sourceId} FOR KEY SHARE
), inserted AS (
INSERT INTO timeline_entries (page_id, date, source, summary, detail)
SELECT id, ${entry.date}::date, ${sanitizeForJsonb(entry.source || '')}, ${sanitizeForJsonb(entry.summary)}, ${sanitizeForJsonb(entry.detail || '')}
FROM page_state
ON CONFLICT (page_id, date, md5(summary), source) DO NOTHING
RETURNING 1
)
SELECT
EXISTS(SELECT 1 FROM page_state) AS page_exists,
EXISTS(SELECT 1 FROM inserted) AS inserted
`)).rows;
if (!result?.page_exists && !opts?.skipExistenceCheck) {
throw new PageMissingError('addTimelineEntry', 'page', slug, sourceId);
}
return result?.inserted === true;
}
export async function addTimelineEntriesBatch(exec: SqlExecutor, entries: TimelineBatchInput[]): Promise<number> {
// #1861: JSONB jsonb_to_recordset instead of unnest(${arr}::text[]). Meeting
// summary/detail/source are free text with the same array-literal crash
// hazard as link context. See links.ts addLinksBatch for the full rationale.
// `date` stays text in the recordset and is cast v.date::date in the SELECT,
// exactly as the old unnest shape did.
const rows = buildTimelineRows(entries);
const result = await executeRawJsonb(
exec,
`INSERT INTO timeline_entries (page_id, date, source, summary, detail)
SELECT p.id, v.date::date, v.source, v.summary, v.detail
FROM jsonb_to_recordset(($1::jsonb)->'rows')
AS v(slug text, date text, source text, summary text, detail text, source_id text)
JOIN pages p ON p.slug = v.slug AND p.source_id = v.source_id AND p.deleted_at IS NULL
ON CONFLICT (page_id, date, md5(summary), source) DO NOTHING
RETURNING 1`,
[],
[{ rows }],
);
return result.length;
}
export async function getTimeline(exec: LegacyUnscopedRead, slug: string, opts?: TimelineOpts): Promise<TimelineEntry[]> {
const limit = opts?.limit || 100;
// #2200 (D5A): collapse the former 8-branch (sourceId × after × before)
// cartesian tree into ONE query built from composed WHERE fragments — the
// same postgres.js `sqlFragment`` idiom getPage/getBacklinks/listLinkSources use.
// Scope precedence: federated sourceIds[] > scalar sourceId > unscoped. The
// federated arm unions entries across every same-slug page in the grant.
// (PGLite builds the equivalent via its dynamic where[]/params[] array —
// different idiom by design, same behavior; lockstep is on result, not builder.)
const sourceCond =
opts?.sourceIds && opts.sourceIds.length > 0
? sqlFragment`AND p.source_id = ANY(${opts.sourceIds}::text[])`
: opts?.sourceId
? sqlFragment`AND p.source_id = ${opts.sourceId}`
: sqlFragment``;
const afterCond = opts?.after ? sqlFragment`AND te.date >= ${opts.after}::date` : sqlFragment``;
const beforeCond = opts?.before ? sqlFragment`AND te.date <= ${opts.before}::date` : sqlFragment``;
// Cat7-1: no index leads with slug, so an unscoped `p.slug = $1` scans all
// of pages_source_slug_key whenever statistics steer the planner to drive
// from pages. The unscoped arm probes (source_id, slug) once per source
// instead, in a LATERAL fenced with OFFSET 0 so no plan can reorder it.
const pagesJoin = opts?.sourceIds?.length || opts?.sourceId
? sqlFragment`timeline_entries te JOIN pages p ON p.id = te.page_id`
: sqlFragment`sources s
CROSS JOIN LATERAL (SELECT * FROM pages WHERE source_id = s.id AND slug = ${slug} OFFSET 0) p
JOIN timeline_entries te ON te.page_id = p.id`;
const rows = (await exec.run(sqlFragment`
SELECT te.* FROM ${pagesJoin}
WHERE p.slug = ${slug} ${sourceCond} ${afterCond} ${beforeCond}
${opts?.excludePrivate ? trustedSql(`AND ${privatePagesFilterForCaller('p')}
AND ${privateTimelineEventFilterForCaller('te')}`) : sqlFragment``}${opts?.eligibility ? sqlFragment` AND ${trustedSql(projectionEligibleSql('timeline_entries', 'te', opts.eligibility))}` : sqlFragment``}
ORDER BY te.date DESC, te.id DESC LIMIT ${limit}`)).rows;
return rows as unknown as TimelineEntry[];
}
// ── v0.42.x Life Chronicle (#2390) timeline reads ───────────────────────
// Shared shape: timeline_entries JOIN depth page (deleted_at IS NULL) LEFT
// JOIN event page; hide soft-deleted event projections (read-time, not just
// doctor); order by COALESCE(event effective_date, date) for intra-day
// sequence. Source scope: federated sourceIds[] > scalar sourceId > unscoped.
// ep=true: the ep LEFT JOIN carries the same scope so out-of-scope event fields null out (#2200 origin-join shape).
function chronicleSourceCond(opts?: PageReadScope, ep = false) {
const privacy = opts?.excludePrivate && !ep
? trustedSql(`AND ${privatePagesFilterForCaller('p')} AND ${privateTimelineEventFilterForCaller('te')}`)
: sqlFragment``;
if (opts?.sourceIds && opts.sourceIds.length > 0)
return ep ? sqlFragment`AND ep.source_id = ANY(${opts.sourceIds}::text[])` : sqlFragment`AND p.source_id = ANY(${opts.sourceIds}::text[]) ${privacy}`;
if (opts?.sourceId) return ep ? sqlFragment`AND ep.source_id = ${opts.sourceId}` : sqlFragment`AND p.source_id = ${opts.sourceId} ${privacy}`;
return privacy;
}
export async function getTimelineForDate(exec: LegacyUnscopedRead, date: string, opts?: ChronicleTimelineOpts): Promise<ChronicleTimelineRow[]> {
const limit = opts?.limit ?? 200;
// ISO week (date_trunc('week') → Monday) or the single day.
const lower = opts?.week ? sqlFragment`date_trunc('week', ${date}::date)::date` : sqlFragment`${date}::date`;
const upper = opts?.week ? sqlFragment`(date_trunc('week', ${date}::date) + interval '6 days')::date` : sqlFragment`${date}::date`;
const rows = (await exec.run(sqlFragment`
SELECT te.date::text AS date, te.summary, te.detail, te.source, te.trust_tier,
te.page_id, p.slug AS page_slug,
te.event_page_id, ep.slug AS event_slug,
ep.effective_date::text AS effective_date,
ep.frontmatter->'event'->>'kind' AS kind
FROM timeline_entries te
JOIN pages p ON p.id = te.page_id AND p.deleted_at IS NULL
LEFT JOIN pages ep ON ep.id = te.event_page_id ${chronicleSourceCond(opts, true)}
WHERE te.date >= ${lower} AND te.date <= ${upper}
AND (te.event_page_id IS NULL OR ep.deleted_at IS NULL)
${chronicleSourceCond(opts)}
ORDER BY COALESCE(ep.effective_date, te.date::timestamptz) ASC, te.id ASC
LIMIT ${limit}`)).rows;
return rows as unknown as ChronicleTimelineRow[];
}
export async function getSince(exec: LegacyUnscopedRead, date: string, opts?: ChronicleTimelineOpts): Promise<ChronicleTimelineRow[]> {
const limit = opts?.limit ?? 200;
const kindCond = opts?.kind ? sqlFragment`AND ep.frontmatter->'event'->>'kind' = ${opts.kind}` : sqlFragment``;
const rows = (await exec.run(sqlFragment`
SELECT te.date::text AS date, te.summary, te.detail, te.source, te.trust_tier,
te.page_id, p.slug AS page_slug,
te.event_page_id, ep.slug AS event_slug,
ep.effective_date::text AS effective_date,
ep.frontmatter->'event'->>'kind' AS kind
FROM timeline_entries te
JOIN pages p ON p.id = te.page_id AND p.deleted_at IS NULL
LEFT JOIN pages ep ON ep.id = te.event_page_id ${chronicleSourceCond(opts, true)}
WHERE te.date >= ${date}::date
AND (te.event_page_id IS NULL OR ep.deleted_at IS NULL)
${kindCond}
${chronicleSourceCond(opts)}
ORDER BY COALESCE(ep.effective_date, te.date::timestamptz) ASC, te.id ASC
LIMIT ${limit}`)).rows;
return rows as unknown as ChronicleTimelineRow[];
}
export async function getOnThisDay(exec: LegacyUnscopedRead, opts?: PageReadScope & { date?: string; limit?: number }): Promise<ChronicleTimelineRow[]> {
const limit = opts?.limit ?? 50;
const target = opts?.date ? sqlFragment`${opts.date}::date` : sqlFragment`current_date`;
const rows = (await exec.run(sqlFragment`
SELECT te.date::text AS date, te.summary, te.detail, te.source, te.trust_tier,
te.page_id, p.slug AS page_slug,
te.event_page_id, ep.slug AS event_slug,
ep.effective_date::text AS effective_date,
ep.frontmatter->'event'->>'kind' AS kind
FROM timeline_entries te
JOIN pages p ON p.id = te.page_id AND p.deleted_at IS NULL
LEFT JOIN pages ep ON ep.id = te.event_page_id ${chronicleSourceCond(opts, true)}
WHERE EXTRACT(MONTH FROM te.date) = EXTRACT(MONTH FROM ${target})
AND EXTRACT(DAY FROM te.date) = EXTRACT(DAY FROM ${target})
AND te.date < ${target}
AND (te.event_page_id IS NULL OR ep.deleted_at IS NULL)
${chronicleSourceCond(opts)}
ORDER BY te.date DESC, te.id ASC
LIMIT ${limit}`)).rows;
return rows as unknown as ChronicleTimelineRow[];
}
export async function getLastSeen(exec: LegacyUnscopedRead, entitySlug: string, opts?: PageReadScope & { asof?: string }): Promise<LastSeenResult> {
// "Seen" = the entity's own page has a timeline row, OR an event's `who`
// array references the entity: the exact slug, or a wikilink to exactly
// that slug ([[slug]] / [[slug|label]]). Never a substring — a slug that
// prefixes another slug must not inherit its sightings — and LIKE
// metacharacters in the slug are escaped.
// "Last seen" is a PAST relation: the chronicle legitimately stores
// future events (calendar-event is eligibility-eligible), so bound to
// <= asof/today or a scheduled event reads as "seen today". Mirrors
// getOnThisDay's `te.date < target` bound.
// The newest sighting is the newest projected day (te.date is already the
// chronicle.tz local day); the event instant only orders within a day, so
// a late-evening event never outranks a plain row dated the next day.
const seenThrough = opts?.asof ? sqlFragment`${opts.asof}::date` : sqlFragment`current_date`;
const likeSlug = entitySlug.replace(/[\\%_]/g, (c) => '\\' + c);
const rows = (await exec.run(sqlFragment`
SELECT te.date::text AS last_date, ep.slug AS last_event_slug
FROM timeline_entries te
JOIN pages p ON p.id = te.page_id AND p.deleted_at IS NULL
LEFT JOIN pages ep ON ep.id = te.event_page_id ${chronicleSourceCond(opts, true)}
WHERE (te.event_page_id IS NULL OR ep.deleted_at IS NULL)
AND te.date <= ${seenThrough}
AND (
p.slug = ${entitySlug}
OR (ep.id IS NOT NULL AND EXISTS (
SELECT 1 FROM jsonb_array_elements_text(
CASE WHEN jsonb_typeof(ep.frontmatter->'event'->'who') = 'array'
THEN ep.frontmatter->'event'->'who' ELSE '[]'::jsonb END
) AS w(name)
WHERE w.name = ${entitySlug}
OR w.name LIKE ${'%[[' + likeSlug + ']]%'} ESCAPE '\\'
OR w.name LIKE ${'%[[' + likeSlug + '|%'} ESCAPE '\\'
))
)
${chronicleSourceCond(opts)}
ORDER BY te.date DESC, COALESCE(ep.effective_date, te.date::timestamptz) DESC, te.id DESC
LIMIT 1`)).rows;
const row = rows[0] as { last_date?: string; last_event_slug?: string } | undefined;
return finalizeLastSeen(entitySlug, row?.last_date ?? null, row?.last_event_slug ?? null, opts?.asof);
}
export async function upsertEventProjection(exec: SqlExecutor, opts: { depthSlug: string; eventSlug: string; date: string; summary: string; detail?: string; sourceId?: string }): Promise<{ projected: boolean }> {
const sourceId = opts.sourceId ?? 'default';
const rows = (await exec.run(sqlFragment`
INSERT INTO timeline_entries (page_id, date, source, summary, detail, event_page_id)
SELECT dp.id, ${opts.date}::date, ${'life-chronicle:event:' + opts.eventSlug}, ${opts.summary}, ${opts.detail ?? ''}, ep.id
FROM pages dp, pages ep
WHERE dp.slug = ${opts.depthSlug} AND dp.source_id = ${sourceId}
AND ep.slug = ${opts.eventSlug} AND ep.source_id = ${sourceId}
ON CONFLICT (event_page_id, date) WHERE event_page_id IS NOT NULL
DO UPDATE SET summary = EXCLUDED.summary, detail = EXCLUDED.detail,
page_id = EXCLUDED.page_id, source = EXCLUDED.source
RETURNING id`)).rows;
return { projected: rows.length > 0 };
}