knowledge

Knowledge

Knowledge is a log of facts: one JSONB value per topic, thing and property. Each write appends a version; nothing is updated or deleted. Call sql without query first to see the columns and the readable topic ids.

• facts: the current version of each fact. Use it for "what is".
• fact_history: every version, with replaces_id pointing at the one it replaced. Use it for "what was", "who changed" and "when".
• text is value as plain text ("active" becomes active). Compare and cast it: text = 'active', text::numeric > 2000. Never value::text, which keeps JSON quotes and silently matches nothing.
• Always filter by topic, and join two properties of one thing on topic and thing. The same thing key in another topic is a different thing.
• Select writer_kind, writer_id and written_at when you say who recorded something. An aggregate has no single writer.
• Parameters are JSON. Send a list as a JSON array and read it as JSON: thing IN (SELECT jsonb_array_elements_text($2::jsonb)). = ANY($2) and $2::text[] are refused.

SELECT text, writer_id, written_at FROM facts
WHERE topic = $1 AND thing = 'trip/kyoto' AND property = 'hotel';

SELECT s.thing, b.text::numeric AS budget FROM facts s
JOIN facts b ON b.topic = s.topic AND b.thing = s.thing AND b.property = 'budget'
WHERE s.topic = $1 AND s.property = 'status' AND s.text = 'active';

SELECT thing FROM facts
WHERE topic = $1 AND property = 'profile' AND value @> '{"country": "US"}';

SELECT id, text, replaces_id, writer_id, written_at FROM fact_history
WHERE topic = $1 AND thing = 'trip/kyoto' AND property = 'hotel' ORDER BY id;

A refusal names what isn't allowed: rewrite with plain SELECT, joins, aggregates, window functions and common text, date and JSON functions. Catalogs, settings and other tables are not there to find.

Writing

Before an INSERT, read writing: replacing a fact, onlyIfUnchanged, receipts and uncertain writes.