Knowledge > Processes > Knowledge Derivation Pipeline

Knowledge Derivation Pipeline

pnpm derive propagates changes from canonical source files (YAML and Markdown in knowledge/) to all downstream targets (TypeScript files, marketing pages, database records, Google Drive). It ensures that when pricing changes in one place, it changes everywhere.

---

Overview

The system has three concepts:

1. Sources -- canonical YAML or Markdown files in knowledge/ (e.g., data/pricing.yaml)
2. Targets -- downstream files or systems that must reflect the source (e.g., src/lib/pricing.ts, product_knowledge table, marketing pages)
3. Manifest -- knowledge/manifest.yaml maps every source to its targets, including the script to run and the operation type

---

Manifest structure

The manifest (knowledge/manifest.yaml) defines which sources derive to which targets:

sources:
  data/pricing.yaml:
    derives:
      - target: churchwiseai-web/src/lib/pricing.ts
        script: derive-pricing
        type: regenerate

      - target: supabase:product_knowledge
        script: derive-pricing
        type: upsert
        filter: "category IN ('billing', 'churchwiseai')"

      - target: churchwiseai-web/src/app/pricing/page.tsx
        script: derive-pricing
        type: verify

      - target: "gdrive:03-Strategy/Pricing/"
        script: derive-gdrive
        type: nightly

Target types

Type	What it does	When changes are applied
regenerate	Generates the entire target file from the source YAML	Immediately on commit
verify	Checks that expected values exist in the target file (does not modify it)	Read-only -- flags drift
upsert	Generates SQL INSERT/UPDATE statements for the product_knowledge table	Prints SQL for manual execution
update-section	Replaces a specific marked section in a target file	Immediately on commit
nightly	Queued for Google Drive sync (runs separately, not during derive)	Skipped during derive

Current sources and their targets

Source	Target count	Key targets
data/pricing.yaml	17 targets	pricing.ts (regenerate), product_knowledge (upsert), 10+ marketing pages (verify), PRICING.md (regenerate), voice agent prompts (verify), PewSearch/ITW pricing pages (verify)
data/features.yaml	3 targets	pricing.ts (regenerate), product_knowledge (upsert), Google Drive (nightly)
data/products.yaml	3 targets	brand.ts (verify), product_knowledge (upsert), Google Drive (nightly)
data/policies.yaml	4 targets	terms page (verify), privacy page (verify), product_knowledge (upsert), Google Drive (nightly)
narrative/vision.md	3 targets	CLAUDE.md brand-architecture section (update-section), product_knowledge (upsert), Google Drive (nightly)
narrative/competitive.md	2 targets	compare/[slug] pages (verify), Google Drive (nightly)
narrative/sales-playbook.md	2 targets	product_knowledge (upsert), Google Drive (nightly)
narrative/brand.md	1 target	Google Drive (nightly)
narrative/strategy.md	1 target	Google Drive (nightly)
narrative/customer-journey.md	1 target	Google Drive (nightly)
narrative/operations.md	1 target	Google Drive (nightly)
data/tools.yaml	5 targets	chatbot-tools.ts (verify), voice church/sales/core tools (verify), features.yaml tool counts (verify)

---

Full pipeline: pnpm derive data/pricing.yaml

When run without flags, the pipeline runs all four phases automatically.

Phase 1: Dry-run (preview changes)

FOR each target defined for this source in the manifest:

  Load the source YAML file and parse it

  IF target type = "regenerate":
    1. Call the generator function (e.g., generatePricingTs(pricingYaml, featuresYaml))
    2. Read the current file on disk
    3. Normalize line endings (CRLF -> LF for consistent comparison on Windows)
    4. Compare generated output with current file
    5. IF identical: report "UP TO DATE"
    6. IF different: report "DRIFT DETECTED" and show a line-by-line diff
       (Diff output is capped at 30 lines to keep it readable)

  IF target type = "verify":
    1. Read the target file
    2. Extract expected values from the source YAML
       Example: for pricing, extract all price strings ("$14.95", "$34.95", etc.)
    3. Check that each expected value appears in the target file content
    4. Report per-value: PASS (found), FAIL (missing), WARN (ambiguous)

  IF target type = "upsert":
    1. Call the SQL generator (e.g., generateProductKnowledgeSQL())
    2. Count the number of INSERT/UPDATE statements
    3. Report: "X SQL statement(s) would be executed"

  IF target type = "update-section":
    1. Read the target file
    2. Read the narrative source content
    3. Call updateSection(currentContent, sectionId, narrativeContent)
    4. Compare the result with the current file
    5. IF identical: "UP TO DATE"
    6. IF different: "WOULD UPDATE"

  IF target type = "nightly":
    SKIP (handled by Drive sync cron, not derive)

After all targets are processed, write a lockfile (.derive-lock.json) with:

• Source file name
• Source file MD5 hash
• Timestamp
• All results (pass/fail/skip per target)

Phase 2: Commit (apply changes)

FOR each target:

  IF target type = "regenerate":
    Generate the content and WRITE it to disk
    Report: "WRITTEN"

  IF target type = "verify":
    Re-run the verification checks (read-only, same as dry-run)
    Report: pass/fail counts

  IF target type = "upsert":
    IF target is "supabase:*" AND --write-db was NOT passed:
      Report: "X SQL statement(s) would be generated -- SKIPPED (pass --write-db)"
      (default derive runs never touch Supabase, even indirectly)
    ELSE:
      Generate the SQL statements
      PRINT the SQL to console for manual execution
      (v1 does not auto-execute SQL -- operator runs it via Supabase MCP or SQL editor)
      Report: "X SQL statement(s) generated -- execute manually via Supabase"

  IF target type = "update-section":
    Generate the updated content and WRITE it to disk
    Report: "WRITTEN"

  IF target type = "nightly":
    SKIP

Phase 3: Verify (confirm changes took effect)

Re-run dry-run checks on all regenerate and verify targets
to confirm the committed changes are correct.

FOR upsert targets: SKIP (manual SQL execution cannot be verified automatically)
FOR update-section targets: re-run the dry-run comparison

Phase 4: Append to changelog

Build a summary entry with timestamp and per-target status:
  "## 2026-03-25 14:30 -- data/pricing.yaml"
  "- churchwiseai-web/src/lib/pricing.ts: regenerate PASS"
  "- supabase:product_knowledge: upsert PASS (3 SQL statements)"
  "- churchwiseai-web/src/app/pricing/page.tsx: verify PASS (8/8 checks passed)"

Append to knowledge/changelog.md

---

Lockfile mechanics

The lockfile (.derive-lock.json) prevents stale commits:

WHEN --dry-run is run:
  Write lockfile with: { source, hash, timestamp, results }

WHEN --commit is run:
  1. Read lockfile
  2. IF lockfile doesn't exist: ERROR "Run --dry-run first"
  3. IF lockfile source doesn't match requested source: ERROR
  4. IF lockfile is older than 10 minutes: ERROR "Re-run --dry-run"
  5. IF source file MD5 has changed since dry-run: ERROR "Source changed -- re-run --dry-run"
  6. PROCEED with commit

The 10-minute expiry prevents applying changes based on a stale preview.

---

CLI usage

pnpm derive data/pricing.yaml              Full pipeline (dry-run + commit + verify + changelog)
pnpm derive data/pricing.yaml --dry-run    Phase 1 only: preview what would change
pnpm derive data/pricing.yaml --commit     Phase 2-4: apply from lockfile
pnpm derive --check                        Drift detection: scan ALL sources, exit 1 if drift
pnpm derive --all                          Full pipeline for every source in manifest
pnpm derive --all --write-db               Also reach the manual-execution step for supabase:* upserts
pnpm derive ... --allow-dirty-targets "<reason>"   Override the dirty-target-tree guard (logged)

Drift detection mode (--check)

WHEN --check is run:
  FOR each source in manifest:
    1. Filter out nightly-only targets
    2. Run dry-run on remaining targets
    3. Collect failures

  IF any source has failures:
    Print drift details
    EXIT 1 (non-zero exit code for CI integration)
  ELSE:
    Print "All sources in sync"
    EXIT 0

This can be used in CI/CD to block deploys when knowledge has drifted.

---

Generator scripts

Each script handles a specific source type:

Script	Source	What it generates
derive-pricing	pricing.yaml + features.yaml	pricing.ts (full file), product_knowledge SQL, price verification
derive-products	products.yaml	brand.ts verification, product_knowledge SQL
derive-policies	policies.yaml	terms/privacy page verification, policy knowledge SQL
derive-narrative	vision.md, competitive.md, etc.	CLAUDE.md section updates, narrative knowledge SQL
derive-tools	tools.yaml	Tool schema verification across chatbot and voice codebases
derive-gdrive	all sources	Google Drive sync (nightly cron, not run during derive)

Cross-source dependencies

Some generators need multiple source files:

• generatePricingTs() requires BOTH pricing.yaml AND features.yaml
• The script auto-loads the companion file regardless of which source triggered the derive

---

Safety properties

1. Verify targets are read-only -- they never modify the target file, only report drift
2. Upsert targets print SQL but don't execute it -- the operator reviews and runs manually
3. Regenerate targets overwrite the entire file -- the generator is the source of truth
4. Line endings are normalized (CRLF to LF) before comparison to avoid false drift on Windows
5. Lockfile expiry (10 minutes) prevents applying stale changes
6. Source hash check prevents committing when the source changed after dry-run
7. No concurrent execution lock -- agents should use separate git branches to avoid conflicts
8. Dirty-target-tree guard and worktree guard (added 2026-09-11, scripts/lib/target-guard.ts) -- see below
9. --write-db gate on Supabase upserts (added 2026-09-11) -- see below

---

Write-mode guardrails (2026-09-11)

resolveTargetPath() joins every relative target onto CODEBASES_ROOT (the parent of wherever the running knowledge checkout lives). Locally that's always C:\dev, so a relative target like knowledge/INDEX.md or churchwiseai-web/src/lib/pricing.ts resolves to the SHARED checkouts at C:\dev\knowledge\... / C:\dev\churchwiseai-web\... no matter which checkout is running the script. Running pnpm derive --all from an isolated knowledge worktree does not isolate its writes -- it still edits the shared trees, potentially clobbering another agent's uncommitted work. This happened twice on 2026-08-03 and recurred on 2026-09-11 (see the memory note feedback_derive_writes_into_other_agents_trees.md). Three guards close this:

1. Worktree-cwd guard

Before doing anything else, pnpm derive checks whether it was invoked from a linked git worktree rather than the main checkout (git rev-parse --git-dir vs --git-common-dir for the knowledge root -- they diverge only in a linked worktree). If so, every write mode (bare pipeline, --dry-run, --commit, --all) aborts immediately with an explanation. --check is read-only and always allowed, from any checkout.

2. Dirty-target-tree guard

Before any write, derive resolves every regenerate / update-section target this invocation will touch, finds the git repo root each one lives in (walking up from the resolved path to the nearest .git), and runs git status --porcelain + git branch --show-current against each. If any target repo has uncommitted changes, or isn't on its deploy branch (from git symbolic-ref refs/remotes/origin/HEAD), the ENTIRE run aborts before writing anything, naming exactly which repo/branch/files blocked it. For a multi-source run (--all), this check covers every source up front, not just the first one.

Override only after confirming it's actually safe:

pnpm derive --all --allow-dirty-targets "confirmed with <agent>, their WIP doesn't touch these files"

The override is logged to changelog.md (repo, reason, and the specific dirty/wrong-branch findings) so bypassing the guard stays visible -- mirroring the --allow-unmerged pattern used elsewhere in the portfolio for deploy overrides.

3. --write-db gate on Supabase upserts

upsert targets (supabase:product_knowledge) already only print SQL for manual execution -- derive has never auto-executed it. The gate adds an explicit consent step on top: without --write-db, a commit for a supabase:* target is skipped with a "would be generated" message instead of reaching the manual-execution print. Default derive runs never touch Supabase, even indirectly, without this flag.

What's NOT covered

These guards protect target repos and Supabase. They do not add a lock against two agents running pnpm derive in the SAME checkout at the same time -- that's still "no concurrent execution lock" (safety property 7). Use separate branches/worktrees for source edits, and only run write-mode derive from the main checkout when no other agent is active there.