Consul Content Automation Audit (Architecture-Only)

Audit date: 2026-02-28
Scope: Full content stack (admin CMS, Supabase content layer, rendering surfaces, updates MDX subsystem, SEO/AI discovery routes, guide gating/PDF subsystem)
Method: Repository code and checked-in documentation only (no Supabase dashboard inspection)

Executive Summary

Consul currently runs a split content automation architecture with two primary pipelines:

1. Supabase-backed pipeline for blog, workflows, comparisons, and role pages.
   Content is authored and edited through the in-repo admin UI, persisted to consul_posts (plus keyword/auth tables), and rendered by Next.js App Router pages that fetch from lib/supabase/content.ts. Public routes use ISR with route-specific revalidate windows and on-demand revalidation plumbing.
   Evidence: app/blog/page.tsx:11, app/blog/[slug]/page.tsx:12, app/workflows/page.tsx:11, app/comparisons/page.tsx:14, app/for/[role]/page.tsx:12, lib/supabase/content.ts:45.

2. Filesystem MDX pipeline for /updates.
   Update entries are read directly from app/updates/content/*.mdx via synchronous filesystem reads in lib/updates.ts, then rendered in updates routes and included in sitemap generation.
   Evidence: lib/updates.ts:11, lib/updates.ts:39, lib/updates.ts:45, app/updates/page.tsx:8, app/updates/[slug]/page.tsx:12, app/sitemap.ts:2, app/sitemap.ts:63.

Core automation behavior today:

• Admin auth and access control combine Supabase session checks (middleware.ts) with server-side admin_users verification in admin layout/login action.
  Evidence: middleware.ts:5, middleware.ts:55, app/admin/(dashboard)/layout.tsx:15, app/admin/(dashboard)/layout.tsx:25, lib/supabase/admin-login-action.ts:27, lib/supabase/admin-login-action.ts:42.
• Authoring and autosave are client-driven from the editor (2s debounce), writing through server actions to Supabase and syncing keywords.
  Evidence: components/admin/editor.tsx:71, components/admin/editor.tsx:77, lib/supabase/admin-content.ts:102, lib/supabase/admin-content.ts:195.
• Publish toggling updates is_published in consul_posts, but the editor's revalidation request shape differs from the revalidation API's expected webhook contract.
  Evidence: components/admin/editor.tsx:155, components/admin/editor.tsx:168, app/api/revalidate/route.ts:56, app/api/revalidate/route.ts:77, app/api/revalidate/route.ts:87.
• SEO/AI discovery surfaces (sitemap.xml, robots.txt, llms.txt, llms-full.txt) are generated from mixed sources (Supabase + updates MDX + guides registry).
  Evidence: app/sitemap.ts:2, app/sitemap.ts:72, app/sitemap.ts:118, app/robots.ts:7, app/llms.txt/route.ts:23, app/llms-full.txt/route.ts:19.
• Guide gating uses cookie-based access (guide_access_<slug>) with lead capture in guide_leads and optional ZeroBounce verification; PDF export enforces the same cookie gate.
  Evidence: lib/guide-leads.ts:41, lib/guide-leads.ts:109, lib/guide-leads.ts:124, app/guides/[slug]/page.tsx:66, app/api/guides/[slug]/pdf/route.ts:36.

Architecture Visual Breakdown

This section is a system-diagram-first walkthrough of how the content automation stack is assembled and how data moves.

Diagram A: System Context (External + Internal Boundaries)

Diagram B: Runtime Container View (Inside Next.js)

Diagram C: Source-of-Truth Map by Surface

Diagram D: Publish + Invalidation Pipeline (Current Behavior)

Diagram E: Public Read Path (Supabase vs Updates FS)

Step-by-Step Mental Model (Fast Understanding)

1. Admins authenticate via Supabase auth and are authorized via admin_users.
2. Editor writes content directly to Supabase tables (consul_posts + keyword tables).
3. Public SEO/content routes (blog/workflows/comparisons/for) read from Supabase only published rows.
4. Updates routes are separate and read MDX files from repository filesystem.
5. Sitemap is hybrid: combines Supabase content, updates MDX, authors, and guides registry.
6. Revalidation route is webhook-shaped; editor-triggered revalidate payload currently uses a different contract.
7. Guides use cookie-gated access with optional lead persistence and optional email verification.
8. PDF generation for guides is gated by the same cookie and rendered through Playwright/Chromium.

System Boundary and Components

1) Admin CMS Subsystem

Scope files

• app/admin/*
• components/admin/*
• lib/supabase/admin-auth.ts
• lib/supabase/admin-login-action.ts
• lib/supabase/admin-content.ts

Responsibilities

• Authenticate editors through Supabase auth.
• Gate admin dashboard to admin_users.
• List/filter content records.
• Create/update/delete posts.
• Toggle publish state.
• Manage metadata/keywords.
• Surface local validation metrics in editor UI.

Key implementation anchors

• Admin route/session guard: middleware.ts:5, middleware.ts:64.
• Dashboard-level admin_users check: app/admin/(dashboard)/layout.tsx:22-32.
• Login action with optional admin list verification: lib/supabase/admin-login-action.ts:33-51.
• CRUD server actions on consul_posts: lib/supabase/admin-content.ts:58-157.
• Keyword synchronization workflow: lib/supabase/admin-content.ts:195-214.
• Editor autosave + publish toggle UI behavior: components/admin/editor.tsx:71-179.

2) Content Read Layer (Supabase)

Scope file

• lib/supabase/content.ts

Responsibilities

• Read published content by type from consul_posts.
• Fetch post+author+keywords in joined queries.
• Convert DB records to route/component-facing typed objects.
• Provide slug lists for static params.

Key implementation anchors

• Published-only filtering: lib/supabase/content.ts:48, lib/supabase/content.ts:78, lib/supabase/content.ts:115, lib/supabase/content.ts:220.
• Joined read pattern (consul_posts + consul_authors + keyword junction): lib/supabase/content.ts:66-79, lib/supabase/content.ts:70-74.
• High-level route-facing getters: lib/supabase/content.ts:440-622.
• Optional relationship RPC helper: lib/supabase/content.ts:198-207.

3) Rendering Surfaces

Scope files

• app/blog/*
• app/workflows/*
• app/comparisons/*
• app/for/*

Responsibilities

• Render list/detail pages using Supabase content.
• Generate metadata and static params from Supabase slugs.
• Apply ISR windows per surface.
• Render MDX content with shared component maps.

Key implementation anchors

• Blog: app/blog/page.tsx:11, app/blog/page.tsx:16, app/blog/[slug]/page.tsx:12, app/blog/[slug]/page.tsx:31.
• Workflows: app/workflows/page.tsx:11, app/workflows/page.tsx:16, app/workflows/[slug]/page.tsx:13, app/workflows/[slug]/page.tsx:29.
• Comparisons: app/comparisons/page.tsx:14, app/comparisons/page.tsx:17, app/comparisons/[slug]/page.tsx:14, app/comparisons/[slug]/page.tsx:23.
• ICP: app/for/[role]/page.tsx:12, app/for/[role]/page.tsx:19, app/for/[role]/page.tsx:25-31.
• Reserved static route override for fractional executives: app/for/fractional-executives/page.tsx:41.

4) Filesystem Updates Subsystem

Scope files

• lib/updates.ts
• app/updates/*
• app/updates/content/*.mdx

Responsibilities

• Parse MDX update entries from disk.
• Generate list, detail pages, metadata, and static params.

Key implementation anchors

• Filesystem source root: lib/updates.ts:11.
• Sync file reads/parsing: lib/updates.ts:39-47.
• Updates list route usage: app/updates/page.tsx:8, app/updates/page.tsx:28.
• Updates detail route usage: app/updates/[slug]/page.tsx:12, app/updates/[slug]/page.tsx:21-23, app/updates/[slug]/page.tsx:136-137.

5) SEO / AI Discovery Surfaces

Scope files

• app/sitemap.ts
• app/robots.ts
• app/llms.txt/route.ts
• app/llms-full.txt/route.ts
• app/api/indexnow/route.ts

Responsibilities

• Publish crawl/index surface metadata across search and AI channels.
• Build sitemap from mixed dynamic/static sources.
• Emit robots rules and sitemap pointer.
• Emit AI-readable text indexes.
• Provide an IndexNow submission endpoint.

Key implementation anchors

• Mixed-source sitemap generation: app/sitemap.ts:2-11, app/sitemap.ts:63, app/sitemap.ts:72, app/sitemap.ts:118.
• Robots policy: app/robots.ts:7-40.
• LLM index routes: app/llms.txt/route.ts:23-30, app/llms-full.txt/route.ts:19-25.
• IndexNow endpoint contract: app/api/indexnow/route.ts:21, app/api/indexnow/route.ts:43, app/api/indexnow/route.ts:83.

6) Guide Gating Subsystem

Scope files

• app/guides/*
• components/guides/*
• lib/guides.ts
• lib/guide-leads.ts
• app/api/guides/[slug]/pdf/route.ts

Responsibilities

• Define guide registry metadata and slugs.
• Gate content with cookie access.
• Capture lead records and optional email verification.
• Generate gated PDF output.

Key implementation anchors

• Guide registry and slug list: lib/guides.ts:17, lib/guides.ts:57, lib/guides.ts:61.
• Gate cookie set + lead upsert: lib/guide-leads.ts:109, lib/guide-leads.ts:124.
• Route-level gate enforcement: app/guides/[slug]/page.tsx:65-70.
• PDF endpoint gate enforcement: app/api/guides/[slug]/pdf/route.ts:35-39.

Data Model and Environment Contract

Content Entities and Joins (from code usage)

Environment Variable Contract (as implemented)

Naming/Contract Mismatches Present in Repo

• Revalidation naming mismatch across code/docs: REVALIDATION_SECRET (API route) vs NEXT_PUBLIC_REVALIDATE_SECRET (editor) vs REVALIDATE_SECRET (README).
  Evidence: app/api/revalidate/route.ts:78, components/admin/editor.tsx:172, README.md:247.
• README lists GENERATIVE_SUPABASE_SERVICE_KEY, but code paths shown in this audit consume SUPABASE_SERVICE_ROLE_KEY for admin writes and GENERATIVE_SUPABASE_ANON_KEY for content read.
  Evidence: README.md:246, lib/supabase/admin-auth.ts:44, lib/supabase/client.ts:13.

End-to-End Flow Maps

Flow 1: Admin Login and Authorization Gate

Trigger: User opens /admin/login and submits credentials (app/admin/login/page.tsx:14-29).
Function boundaries: middleware.ts + loginAction + admin dashboard layout checks.
Data stores: Supabase auth session + admin_users table.
Cache behavior: No ISR/cache mutation; session and DB read checks only.
Output routes: /admin/login or /admin.

Step breakdown

1. Middleware checks session on all /admin/* paths and redirects unauthenticated users to /admin/login (middleware.ts:5-10, middleware.ts:57-59).
2. Login action performs signInWithPassword (lib/supabase/admin-login-action.ts:27).
3. If SUPABASE_SERVICE_ROLE_KEY exists, login action checks admin_users by normalized email (lib/supabase/admin-login-action.ts:34-45).
4. Dashboard layout repeats admin_users membership check before rendering protected pages (app/admin/(dashboard)/layout.tsx:22-32).

Flow 2: Draft/Edit/Autosave Content in Admin Editor

Trigger: Editor state changes in title/content/metadata.
Function boundaries: components/admin/editor.tsx -> lib/supabase/admin-content.ts.
Data stores: consul_posts, consul_keywords, consul_post_keywords.
Cache behavior: No direct cache invalidation on autosave.
Output routes: Admin editor route remains in-place (/admin/editor/[id]).

Step breakdown

1. Editor updates local state and schedules autosave with 2-second debounce (components/admin/editor.tsx:71-79).
2. Existing posts call updatePost; new posts call createPost then route-replace to /admin/editor/{id} (components/admin/editor.tsx:107-150).
3. Server actions write post metadata/content and computed word/reading-time fields (lib/supabase/admin-content.ts:61-85, lib/supabase/admin-content.ts:110-135).
4. Keyword sync deletes existing junction rows, upserts keywords, then upserts post-keyword junctions (lib/supabase/admin-content.ts:199-211).

Flow 3: Publish/Unpublish and Cache Invalidation

Trigger: Publish button in editor (components/admin/editor.tsx:203-212).
Function boundaries: handlePublish -> handleSave -> updatePost + app/api/revalidate/route.ts.
Data stores: consul_posts.
Cache behavior: Path-based invalidation via revalidatePath when route receives valid webhook-authenticated payload.
Output routes: Affected public paths (/blog/*, /workflows/*, /comparisons/*, /for/*) and list pages.

Step breakdown

1. handlePublish flips local is_published and invokes handleSave (components/admin/editor.tsx:155-160).
2. Save persists publish fields through updatePost (lib/supabase/admin-content.ts:117-121).
3. Editor then sends POST /api/revalidate with Authorization header and { tag } body (components/admin/editor.tsx:168-175).
4. Revalidate route expects x-webhook-secret and a webhook payload containing type/table/record/old_record; it filters to table === "consul_posts" (app/api/revalidate/route.ts:56-87).
5. On valid webhook requests, route computes paths and calls revalidatePath for detail and list pages (app/api/revalidate/route.ts:99-121).
6. Manual GET fallback exists with ?secret=...&path=... (app/api/revalidate/route.ts:147-166).

Flow 4: Public Read/Render + ISR by Route

Trigger: Public HTTP request to content routes.
Function boundaries: Route files in app/* call lib/supabase/content.ts getters.
Data stores: consul_posts + joined author/keyword tables.
Cache behavior: ISR on each route according to local revalidate constant.
Output routes: /blog, /blog/[slug], /workflows, /workflows/[slug], /comparisons, /comparisons/[slug], /for/[role].

Route ISR windows and slug generation

Evidence: app/blog/page.tsx:16, app/blog/[slug]/page.tsx:22, app/workflows/page.tsx:16, app/workflows/[slug]/page.tsx:23, app/comparisons/page.tsx:17, app/comparisons/[slug]/page.tsx:23, app/for/[role]/page.tsx:19, app/for/[role]/page.tsx:25-30.

Flow 5: Filesystem Updates (MDX Changelog)

Trigger: Request to /updates or /updates/[slug].
Function boundaries: app/updates/* routes -> lib/updates.ts.
Data store: Filesystem MDX, not Supabase for updates route rendering.
Cache behavior: Static params for detail pages from file slugs; no route-level ISR constant declared in updates routes.
Output routes: /updates, /updates/[slug].

Step breakdown

1. Updates utility sets source directory to app/updates/content (lib/updates.ts:11).
2. Utility reads and parses each .mdx file synchronously (lib/updates.ts:39-47).
3. /updates page loads full list with getAllUpdates (app/updates/page.tsx:28).
4. /updates/[slug] route builds static params from getAllUpdateSlugs and renders with getUpdateBySlug (app/updates/[slug]/page.tsx:21-23, app/updates/[slug]/page.tsx:37, app/updates/[slug]/page.tsx:136).

Flow 6: Guide Lead Capture, Cookie Gate, and PDF

Trigger: Access to /guides/[slug] and optional PDF download action.
Function boundaries: Route gate check, client form, server action, PDF API route.
Data stores: Cookie store + optional guide_leads upsert.
Cache behavior: Cookie-gated, user-specific access behavior; PDF response has private cache header.
Output routes: /guides/[slug], /api/guides/[slug]/pdf.

Step breakdown

1. Guide page checks for guide_access_<slug> cookie and gates content if absent (app/guides/[slug]/page.tsx:65-70).
2. Form submits to submitGuideLead server action (components/guides/GuideGateForm.tsx:34).
3. Server action optionally verifies email with ZeroBounce (lib/guide-leads.ts:41-74), sets gate cookie (lib/guide-leads.ts:109-115), and upserts into guide_leads (lib/guide-leads.ts:124-139).
4. Content access can be granted even if DB write fails because cookie is set first (lib/guide-leads.ts:141-151).
5. PDF route enforces same cookie and uses Playwright/Chromium rendering pipeline (app/api/guides/[slug]/pdf/route.ts:35-39, app/api/guides/[slug]/pdf/route.ts:42-53, app/api/guides/[slug]/pdf/route.ts:113-140).

Automation Coverage Matrix

Observed Architectural Drift (Non-Scored)

D1) README content-system description diverges from current runtime implementation

Observed drift

• README describes filesystem MDX as source of truth for blog/workflows/comparisons/ICP and a sync script workflow.
• Current route code for those surfaces reads from Supabase directly via lib/supabase/content.ts.

Evidence anchors

• Docs expectation: README.md:112, README.md:118-123, README.md:183, README.md:191.
• Runtime implementation: app/blog/page.tsx:11, app/workflows/page.tsx:11, app/comparisons/page.tsx:14, app/for/[role]/page.tsx:12, lib/supabase/content.ts:45.
• Supporting repo signal: package scripts do not expose a sync command (package.json:5-10), while the only checked-in script in scripts/ is image generation (scripts/generate-blog-images.py:1-7).

D2) Revalidation contract mismatch between editor trigger, API route, and README examples

Observed drift

• Editor sends Authorization: Bearer ... and { tag } in POST /api/revalidate.
• Revalidate route expects x-webhook-secret and a Supabase webhook payload shape (type/table/record/old_record), and only processes table === "consul_posts".
• README's curl example also uses Authorization + tag, matching editor behavior but not route behavior.

Evidence anchors

• Editor call shape: components/admin/editor.tsx:168-175.
• Route auth/payload contract: app/api/revalidate/route.ts:56-87, app/api/revalidate/route.ts:77-81.
• README example: README.md:227-233, README.md:247.

D3) Mixed content sources are intentional in code but unevenly documented

Observed drift

• Blog/workflow/comparison/icp surfaces are Supabase-driven.
• Updates are filesystem-driven.
• Sitemap aggregates both, plus guides and authors.
• /for/fractional-executives is a static component route excluded from dynamic ICP slug generation.

Evidence anchors

• Supabase routes: app/blog/page.tsx:11, app/workflows/page.tsx:11, app/comparisons/page.tsx:14, app/for/[role]/page.tsx:12.
• Filesystem updates: lib/updates.ts:11, app/updates/page.tsx:8.
• Aggregated sitemap: app/sitemap.ts:2-11, app/sitemap.ts:63, app/sitemap.ts:72, app/sitemap.ts:118.
• Static ICP exception: app/for/[role]/page.tsx:25-31, app/for/fractional-executives/page.tsx:41-63.

D4) Relationship-based related-content automation is partially wired in runtime

Observed drift

• lib/supabase/content.ts exposes RPC-based getRelatedPosts and marks relatedWorkflows as relationship-populated.
• Blog and workflow pages currently rely on local fallback/sibling logic rather than the relationship RPC path.

Evidence anchors

• Relationship helper and placeholder: lib/supabase/content.ts:198-207, lib/supabase/content.ts:368.
• Blog local related-posts logic: app/blog/[slug]/page.tsx:90-115.
• Workflow fallback to "other workflows" when relatedWorkflows is absent: app/workflows/[slug]/page.tsx:71-73.

D5) Validation scoring persistence path described in docs is not visible in current admin write path

Observed drift

• README states validation scores are computed and stored (including consul_post_validations).
• Admin write actions calculate word_count/reading_time and sync keywords, but do not write seo_score, geo_score, or a validation table.
• Dashboard table reads seo_score/geo_score fields and editor computes client-side validation.

Evidence anchors

• Docs claims: README.md:171-191.
• Admin write path fields: lib/supabase/admin-content.ts:61-85, lib/supabase/admin-content.ts:110-135.
• Types/UI reading score fields: lib/supabase/admin-types.ts:27-29, components/admin/content-table.tsx:152-153.
• Client-side scoring UI: components/admin/validation-panel.tsx:29-33, components/admin/validation-panel.tsx:124-128.

D6) IndexNow endpoint exists as automation capability but is not wired into publish workflow in this repo

Observed drift

• An IndexNow API route is implemented.
• Publish action path in editor only invokes save + /api/revalidate; no IndexNow invocation in that flow.

Evidence anchors

• IndexNow endpoint implementation: app/api/indexnow/route.ts:43-131.
• Publish flow implementation: components/admin/editor.tsx:155-175.

Important Public APIs/Interfaces/Types

This audit phase introduces no code changes and no interface changes.

• No public API route contracts were modified.
• No TypeScript types/interfaces were modified.
• No database schema migrations were applied.
• Output is documentation-only: /docs/audits/2026-02-28-content-automation-audit.md.

Validation Scenarios Applied to This Audit

1. Every flow map includes all required fields:

• Trigger
• Function boundary
• Data store
• Cache behavior
• Output route

2. Every content surface is mapped to one source model:

• Supabase-driven surfaces (/blog*, /workflows*, /comparisons*, /for/[role])
• Filesystem-driven updates (/updates*)
• Hybrid/cookie-gated guide flow (/guides/[slug] + guide_leads + cookie gate)

3. Every environment variable listed in this report is traceable to a specific code reference in this repo (see Environment Contract and Appendix).

4. Every drift statement includes at least two evidence anchors (implementation and/or docs/config references).

5. No secret values are reproduced in this report; only variable names and code locations are documented.

Assumptions and Defaults

1. Scope includes all content-adjacent systems in this repository (full stack).
2. This report is architecture-only and does not rank findings by severity.
3. Source of truth for this audit is repository code and checked-in docs only.
4. System state captured as of 2026-02-28.

Appendix: Evidence Index

Claim-to-Evidence Mapping

File-by-File Reference List

• app/admin/(dashboard)/layout.tsx:15-32
• app/admin/(dashboard)/page.tsx:15-21
• app/admin/login/page.tsx:14-29
• app/api/guides/[slug]/pdf/route.ts:23-39
• app/api/guides/[slug]/pdf/route.ts:42-53
• app/api/guides/[slug]/pdf/route.ts:113-140
• app/api/indexnow/route.ts:21-29
• app/api/indexnow/route.ts:43-131
• app/api/revalidate/route.ts:56-87
• app/api/revalidate/route.ts:99-121
• app/api/revalidate/route.ts:147-166
• app/blog/[slug]/page.tsx:12-17
• app/blog/[slug]/page.tsx:22
• app/blog/[slug]/page.tsx:90-115
• app/blog/page.tsx:11
• app/blog/page.tsx:16
• app/comparisons/[slug]/page.tsx:14-17
• app/comparisons/[slug]/page.tsx:23
• app/comparisons/page.tsx:14
• app/comparisons/page.tsx:17
• app/for/[role]/page.tsx:12
• app/for/[role]/page.tsx:19
• app/for/[role]/page.tsx:25-31
• app/for/fractional-executives/page.tsx:41-63
• app/guides/[slug]/page.tsx:21-23
• app/guides/[slug]/page.tsx:65-70
• app/llms-full.txt/route.ts:19-25
• app/llms.txt/route.ts:23-30
• app/robots.ts:7-40
• app/sitemap.ts:2-11
• app/sitemap.ts:63-69
• app/sitemap.ts:72-78
• app/sitemap.ts:118-124
• app/updates/[slug]/page.tsx:12
• app/updates/[slug]/page.tsx:21-23
• app/updates/[slug]/page.tsx:136-137
• app/updates/page.tsx:8
• app/workflows/[slug]/page.tsx:13-16
• app/workflows/[slug]/page.tsx:23
• app/workflows/[slug]/page.tsx:71-73
• app/workflows/page.tsx:11
• app/workflows/page.tsx:16
• components/admin/content-table.tsx:152-153
• components/admin/editor.tsx:71-79
• components/admin/editor.tsx:155-175
• components/guides/GuideGateForm.tsx:34
• lib/content-validation.ts:123-131
• lib/content-validation.ts:188-200
• lib/guide-leads.ts:41-74
• lib/guide-leads.ts:109-115
• lib/guide-leads.ts:124-151
• lib/supabase/admin-auth.ts:38-45
• lib/supabase/admin-content.ts:58-85
• lib/supabase/admin-content.ts:102-147
• lib/supabase/admin-content.ts:195-214
• lib/supabase/admin-login-action.ts:33-45
• lib/supabase/admin-types.ts:27-29
• lib/supabase/client.ts:12-15
• lib/supabase/content.ts:45-49
• lib/supabase/content.ts:66-79
• lib/supabase/content.ts:70-74
• lib/supabase/content.ts:198-207
• lib/supabase/content.ts:368
• lib/supabase/content.ts:440-442
• lib/supabase/content.ts:548-550
• lib/supabase/content.ts:575-577
• lib/supabase/content.ts:602-604
• lib/updates.ts:11
• lib/updates.ts:39-47
• middleware.ts:5-10
• middleware.ts:55-59
• package.json:5-10
• README.md:112
• README.md:118-123
• README.md:171-191
• README.md:227-233
• README.md:245-247
• CLAUDE.md:27
• CLAUDE.md:55
• scripts/generate-blog-images.py:1-7