Capability Report: Canonical Query Layer
Date: 2026-04-08 Author: Q (Quality System) Project: Unified Canonical Architecture (Layer 5) Status: Operational
What Was Built
Nine canonical query tools in scripts/canonical/ that provide unified, cross-system intelligence for every entity type in the Content Vault. These tools replaced direct system queries (raw Sanity GROQ, raw SQLite, filesystem ls/grep, HubSpot API calls) with a single entry point per entity domain. Twenty-five slash commands were rewired to consume these tools instead of querying source systems directly.
A reference skill at skills/canonical/canonical-query-reference.md documents all 9 tools, their flags, usage patterns, and the decision framework for when to use canonical queries versus direct system access.
What Problem It Solves
Before the query layer, every command and agent that needed intelligence about shows, episodes, articles, people, methodology, or organizational health had to know which system to query, how to query it, and how to interpret the results. A media command might query Sanity for show metadata, then YouTube for episode counts, then the filesystem for transcript status -- three different interfaces producing three partial pictures that the agent had to reconcile inline.
This created three problems:
Inconsistent counts. Different commands querying different systems for the same entity produced different numbers. Sanity said 11 shows; YouTube said 14 playlists; the filesystem said 9 show directories.
Duplicated query logic. The same Sanity GROQ query for "active shows" appeared in multiple command definitions, each slightly different.
No alignment detection. Nobody could answer "does this entity exist in all the systems it should?" because no single query crossed system boundaries.
The canonical query layer solves all three by querying the Content Vault (which has already reconciled cross-system data) and providing audit flags that surface discrepancies.
The 9 Query Tools
1. show-query.js (6.9KB)
Entity: Show
What it replaced: Direct Sanity show queries, filesystem ls of show directories, YouTube playlist API calls.
| Flag | Output |
|---|---|
--active |
Active shows with schedule, hosts, episode counts |
--audit |
Cross-system alignment (Sanity, Broadcast, Vault, Obsidian, Website) |
{slug} |
Single show deep dive -- hosts, frequency, format, cross-system presence |
2. episode-query.js (8.0KB)
Entity: Episode
What it replaced: Direct Sanity episode queries, YouTube video API calls, raw canonical_episodes SQLite queries.
| Flag | Output |
|---|---|
--audit |
Data quality -- missing dates, unlinked recordings, transcript gaps |
--show {slug} |
Last 20 episodes for a specific show with Sanity/Mux status |
--lifecycle |
Pipeline distribution: scheduled / recorded / linked / transcribed / synthesized / distributed |
3. article-query.js (4.3KB)
Entity: Article
What it replaced: scripts/sanity/query.js --recent-articles and raw Sanity blogPost queries.
| Flag | Output |
|---|---|
--audit |
Publishing cadence, author coverage, Sanity alignment |
--by-author |
Article counts grouped by author |
{slug} |
Single article detail with methodology connections |
4. friend-query.js (13.3KB)
Entity: Friend (people -- human and AI)
What it replaced: Separate HubSpot Contact queries, Sanity contributor queries, filesystem people/ reads, .claude/agents/ reads.
| Flag | Output |
|---|---|
--audit |
People alignment -- missing HubSpot IDs, nature classification gaps |
--nature human |
All humans with their system presence |
--nature ai |
All AI agents with operational status |
{slug} |
Unified person view (HubSpot + Vault + Sanity + Filesystem + Obsidian) |
5. methodology-query.js (8.3KB)
Entity: Methodology Content (traps, Value Path stages, Core Beliefs, Unified Views, Three-Org, Value Realities)
What it replaced: Direct reads of skills/methodology/ files, Sanity valueReality/trap/valuePathStage queries, Obsidian note lookups.
| Flag | Output |
|---|---|
--audit |
All methodology content with system coverage map |
--drift |
Items missing from one or more expected systems |
--type {type} |
Filter by type: trap, value_path_stage, core_belief, unified_view, three_org, value_reality |
6. organization-query.js (4.6KB)
Entity: Organization (clients, companies)
What it replaced: Direct clients/ filesystem reads, HubSpot Company searches, portal config lookups.
| Flag | Output |
|---|---|
--audit |
Alignment -- filesystem-only clients, missing HubSpot records, portal config gaps |
{slug} |
Single organization cross-system view (HubSpot + filesystem + portal + sessions) |
7. customer-health-query.js (4.6KB)
Entity: Session, Signal, Event
What it replaced: Direct clients/*/sessions/ filesystem reads, HubSpot signal queries, calendar event lookups.
| Flag | Output |
|---|---|
--audit |
Full customer health view -- sessions, signals, events across systems |
--sessions {org} |
Session history for a specific client |
8. company-health-query.js (3.5KB)
Entity: Engagement, Investment, Offering, Deliverable What it replaced: Direct HubSpot Deal/Service/Investment/Deliverable queries for health assessment.
| Flag | Output |
|---|---|
--audit |
Revenue entity health across all four Company Health entities |
9. unified-audit.js (4.9KB)
Entity: All 23 entity types
What it replaced: The static /mnt/d/data/content-census-report.md file and ad-hoc SQLite count queries.
| Flag | Output |
|---|---|
| (no flags) | Full canonical census -- record counts per table, population status, empty table assessment |
Architectural Principle: Single Entry Point Per Entity Domain
Every entity domain has exactly one query tool. There is no second path for the same intelligence.
- Need show intelligence?
show-query.js. Not Sanity. Not YouTube. Notls. - Need methodology alignment?
methodology-query.js. Notskills/methodology/. Not Sanity. Not Obsidian. - Need people data?
friend-query.js. Not HubSpot Contacts. Not.claude/agents/. Notpeople/configs.
This principle eliminates count discrepancies because there is one authoritative answer. It also makes audit possible -- every tool has an --audit flag that compares Vault records against expected system presence. Drift detection is built into the query layer, not bolted on after.
Exception: write operations. The canonical query layer is read-only intelligence. Pipeline scripts (run-pipeline.ts, check-shows.ts) and gateway agents (Ledger for HubSpot, Canon for Sanity) handle writes. The query reference skill documents this boundary explicitly.
Commands Rewired (25+)
The following commands now use canonical query tools instead of direct system queries:
Daily Rhythm: /daily-ops, /daily-recap, /weekly-plan, /weekly-review
Media: /media-prep, /media-recap, /media-brief, /media-check, /show-prep, /show-health
Relationship Intelligence: /relationship-brief, /relationship-pulse, /sentinel-check, /meeting-prep, /post-session, /deal-prep
Revenue/Investment: /revenue-brief, /investment-brief, /commerce-brief
BU Briefs: /academy-brief, /collective-brief, /apps-brief
Content: /content-multiply, /interest-brief
Each rewired command has a corresponding -legacy.md file preserved as a snapshot fallback, generated by the command evolution hook.
Legacy Paths That Remain (and Why)
These direct queries were NOT replaced because they serve operational write purposes, not intelligence:
| Legacy Path | Why It Stays |
|---|---|
scripts/sanity/query.js --prep-status |
Checks if an episode exists in Sanity CMS for prep workspace creation |
scripts/sanity/query.js --show={slug} --json |
Reads Sanity episode fields for writing prep content back |
check-shows.ts --date={date} |
Pipeline execution: creates episodes, checks Mux streams |
run-pipeline.ts --date={date} |
Pipeline execution: links recordings to episodes |
scripts/hubspot/get-social-schedule.js |
External API: LinkedIn posting schedule |
scripts/google/get-calendar.js |
External API: Google Calendar events |
scripts/google/get-inbox.js |
External API: Gmail inbox |
What was retired:
scripts/sanity/query.js --recent-articles-- replaced byarticle-query.js --audit- Raw
sqlite3queries against Content Vault -- replaced by canonical query tools /mnt/d/data/content-census-report.mdstatic file -- replaced byunified-audit.js(live)- Direct Sanity show/episode queries for intelligence -- replaced by
show-query.js/episode-query.js
How to Verify It Works
# Run the full census
node /mnt/d/Projects/value-first-operations/scripts/canonical/unified-audit.js
# Check show alignment across systems
node /mnt/d/Projects/value-first-operations/scripts/canonical/show-query.js --audit
# Check methodology drift
node /mnt/d/Projects/value-first-operations/scripts/canonical/methodology-query.js --drift
# Check people alignment
node /mnt/d/Projects/value-first-operations/scripts/canonical/friend-query.js --audit
# Verify a specific entity
node /mnt/d/Projects/value-first-operations/scripts/canonical/show-query.js ai-daily
# Confirm reference skill exists and is current
cat /mnt/d/Projects/value-first-operations/skills/canonical/canonical-query-reference.md | head -5
What This Enables Going Forward
The query layer is the foundation for the remaining 6 functional requirements (FR-7 through FR-13):
FR-7 (Rendered Surface Query Layer): Website pages currently pull methodology content from hardcoded TypeScript files (manifesto-data.ts, trap-data.ts, stage-data.ts, manifesto-commitments.ts). The canonical query tools prove the pattern -- query a unified source, get structured data back. FR-7 extends this pattern to build-time and SSR queries for rendered pages.
FR-8 (Agent Query Interface): The 9 tools are already the agent query interface. FR-8 formalizes this -- Vault exposes query functions that agents call without knowing underlying storage.
FR-9 (Surface Refactoring): With FR-7 in place, 40+ website surfaces (10 manifesto presentations, trap pages, Value Path pages, article templates, learn/foundation pages) switch from hardcoded imports to canonical queries. The query layer makes this refactoring possible.
FR-10 (Hardcoded Source Removal): Once surfaces are refactored, the hardcoded source files (manifesto-commitments.ts, trap-data.ts, stage-data.ts, show-presets.json) are deleted. The query layer is the replacement.
FR-11 (Functional Validation): Every tool's --audit flag is the validation mechanism. Running all 9 audits constitutes a full system health check.
FR-12 (Quality Documentation): This report is the first FR-12 deliverable for the canonical architecture.
FR-13 (Drift Detection Design): The --audit and --drift flags already detect drift. FR-13 determines whether drift detection runs proactively (built into daily rhythm commands) or reactively (on-demand). The mechanism exists; the operational pattern is the design question.
Ownership
- Query tools:
scripts/canonical/(9 files, 68.3KB total) - Reference skill:
skills/canonical/canonical-query-reference.md - Population script:
scripts/canonical/populate-methodology.js - Write authority: Vault agent (Content Vault writes)
- Query design authority: V (architecture)
- Quality authority: Q (validation, drift detection)