S170-FINAL — KB Protection v2 Report

Date: 2026-04-04 | Session: S170-FINAL

Summary

Upgraded KB Protection from v1 (INSERT history, unbounded growth) to v2 (UPSERT history, bounded forever). Added PG permission guards. Fixed cron-env.sh permissions.

Changes

1. fn_kb_snapshot: INSERT -> UPSERT

• Before: Every UPDATE/DELETE appended a new history row -> unbounded growth
• After: INSERT ON CONFLICT (document_key) DO UPDATE -> exactly 1 row per document, forever
• 856 docs -> 856 history rows max, regardless of update frequency

2. UNIQUE constraint on kb_documents_history.document_key

• uq_kb_history_document_key added
• Enforces 1-row-per-doc invariant at PG kernel level

3. History deduplication (one-time)

• Before: 15 rows, 5 distinct keys
• After: 5 rows = 5 distinct keys
• Backup created, verified, then dropped

4. PG Permission guards

• REVOKE DELETE, TRUNCATE ON kb_documents_history FROM incomex
• REVOKE DELETE, TRUNCATE ON kb_audit_log FROM incomex
• UPSERT (INSERT ON CONFLICT UPDATE) unaffected — does not use DELETE

5. fn_kb_restore simplified

• Old: searched by revision parameter
• New: fetches the single row per document_key (revision param kept for compat, ignored)

6. dot-kb-verify.sh v2.0.0

• Added Check 7: Permission guard + history integrity + size monitor
• Permission drift -> CRITICAL alert
• Multi-row per doc -> WARN (should never happen with UPSERT)

7. cron-env.sh permissions

• Changed to 600 (root-only read)

Verification Results

#	Check	Result
1	cron-env.sh perms	-rw------- root root
2	History 1 row/doc	5 = 5 distinct
3	UNIQUE constraint	uq_kb_history_document_key
4	fn_kb_snapshot UPSERT	ON CONFLICT present
5	REVOKE applied	0 DELETE/TRUNCATE grants
6	Direct DELETE blocked	ERROR permission denied
7	UPSERT e2e	2 updates, still 1 history row
8	dot-kb-verify 7 checks	All 7 ran

Architecture v2

UPDATE doc -> trg_kb_truncation_guard (warn if shrink >80%)
           -> trg_kb_snapshot (UPSERT old content to history, 1 row/doc)
           -> WRITE executes
           -> trg_kb_audit (log metadata)

History: BOUNDED = 1 row per doc. Never grows beyond doc count.
Audit: metadata only, ~7MB/year at current rate.
Backup: nightly Google Drive (77MB, 14 retention).
Restore: fn_kb_restore(key) -> instant from history.

v1 vs v2

Metric	v1	v2
History rows per doc	Unbounded	Exactly 1
856 docs x 100 updates/day	+85,600 rows/day	856 rows forever
Cleanup needed	Yes (complex)	Never
DELETE permission	Required (for cleanup)	REVOKED
Permission drift detection	None	Check 7 CRITICAL

DOT Status

• DOT_KB_PROTECT (B, event): v2 UPSERT — complete
• DOT_KB_VERIFY (A, cron): v2.0.0, 7 checks — complete
• DOT_KB_RESTORE (B, on-demand): simplified — complete
• Domain: kb (3 DOTs)

S170 Debt Status

• TD-KB-HIST-CLEANUP: CLOSED (not needed — UPSERT)
• TD-KB-AUDIT-CLEANUP: CLOSED (audit ~7MB/year)
• TD-KB-MEM-MONITOR: CLOSED (Check 6)
• cron-env permissions: CLOSED (chmod 600)
• S170 NOP = 0