Administration — Workflows

Administration — Workflows

Last verified: 26/07/2026 — Workflow 8 updated for ID-372 {372.2} type-scoped claims: claim_next_job widened with p_job_types / p_exclude_job_types so the cron route and scripts/bid_worker.py claim disjoint job-type sets. Prior: S223 (05/05/2026) — added Workflow 8: Background Queue Tick covering S221 W1 + S222 W2 + S223 W3 (process-queue cron, cancel route, claim_next_job backoff window, reap_stuck_jobs RPC). Prior: S210 A5-administration (29 April 2026) against S195-S209 + kh-prod-readiness-S10/S11/S12/S13.

Overview

System-level workflows that drive the admin surface: scheduled cron pipelines, taxonomy sync, env-flip and CLI invocation, GDPR export, governance auto-renewal, and the CI gate trio (E2E smoke + MCP eval + migration replay) plus the advisor lint guard.

Auth-related plumbing is documented in docs/product-functionality/administration/technical.md (getAuthorisedClient → authFailureResponse, proxy + publicRoutes, RLS via get_user_role()). Pipeline run recording is canonicalised through recordPipelineRun() in every flow below.

Workflow 1: Cron Pipeline Execution

Trigger: Vercel Cron emits GET <path> Authorization: Bearer <CRON_SECRET> per the schedule in vercel.json crons[]. Owner: lib/cron-auth.ts (auth) + lib/pipeline/record-run.ts (recording) + each app/api/cron/<job>/route.ts (handler).

Flow

[Vercel Cron] → [verifyCronAuth] → [createServiceClient]
              → [tryQuery candidates] → [Update rows via sb()]
              → [De-dup notifications] → [Build payloads]
              → [createBulkNotifications] → [recordPipelineRun]
              → [200 OK / 401 / 500]

Detailed steps

1. Verify cron secret
   • File: lib/cron-auth.ts
   • Function: verifyCronAuth(request)
   • Input: NextRequest with Authorization: Bearer <CRON_SECRET>
   • Output: boolean — false triggers HTTP 401.
2. Acquire RLS-bypass client
   • File: lib/supabase/server.ts
   • Function: createServiceClient()
   • Output: Service-role SupabaseClient<Database>.
3. Find candidates
   • File: e.g. app/api/cron/review-cadence/route.ts
   • Pattern: tryQuery(supabase.from(...).lt(...).is(...).or(...), 'context')
   • On error: log + recordPipelineRun({status: 'failed'}) + 500.
4. Apply updates row-by-row via sb()
   • On per-row failure: increment hadFailures; final status is completed_with_errors (Sentry warning) rather than swallowing.
5. Notification idempotency
   • File: lib/notifications.ts
   • Function: getExistingNotificationIds(supabase, type, ids, sinceUtc)
   • Pre-filters items already notified today (00:00 UTC anchor).
6. Build notification payloads
   • Per-item path or batch-summary path (>20 items: one summary per recipient — owner-id keyed, with '__admins__' sentinel for unowned items, resolved via getUsersByRole(supabase, ['admin'])).
7. Bulk insert notifications
   • Function: createBulkNotifications(supabase, payloads)
   • On failure: log + mark hadFailures.
8. Record pipeline run
   • File: lib/pipeline/record-run.ts
   • Function: recordPipelineRun({ supabase, pipelineName, status, itemsProcessed, errorMessage?, result? })
   • status: 'running' | 'completed' | 'completed_with_errors' | 'failed'
   • result: JSONB capturing items_flagged, notifications_created, etc.
   • itemsCreated: string[] (uuid[]) when applicable.
9. Return JSON
   • 200 with { success, items_flagged, notifications_created, ... }.
   • 401 on cron auth failure.
   • 500 on unhandled exceptions (always after recording the failed run).

State transitions (pipeline_runs)

Current state	Event	Next state	Side effects
running	All updates + notifications OK	completed	Sentry not fired
running	Per-row update failures	completed_with_errors	Sentry warning fired by recordPipelineRun
running	Candidate query / handler exception	failed	Sentry error + 500 response

Error handling

Error condition	Handling	User feedback
Missing CRON_SECRET env var	verifyCronAuth logs + returns false	401 Unauthorised
Bearer token mismatch	verifyCronAuth returns false	401 Unauthorised
tryQuery returns ok: false	Log + recordPipelineRun failed	500 Internal Server Error
sb() throws SupabaseError	Caught in handler try/catch; appended to failureMessages	completed_with_errors row + 200 with success: false

Database operations

Operation	Table	Columns	RLS
SELECT	content_items	candidate predicates per cron	Service-role bypass
UPDATE	content_items	governance_review_status, governance_review_due, verified_at, next_review_date	Service-role bypass
SELECT	notifications	dedup keyed on (type, entity_id, since UTC midnight)	Service-role bypass
INSERT	notifications	(user_id, type, entity_type, entity_id, title, message)	Service-role bypass
INSERT	pipeline_runs	(pipeline_name, status, items_processed, result, error_message, items_created)	Service-role bypass

---

Workflow 2: Auto-Renewal on Governance Approval (S201 §5.5 Phase 2 T2)

Trigger: Reviewer approves a flagged content item, either via POST /api/governance/review or the MCP governance.review tool. Owner: lib/governance/approval.ts helper consumed by both surfaces; mirrored cadence logic in app/api/governance/review/route.ts and lib/mcp/tools/governance.ts.

Flow

[Reviewer chooses 'approve'] → [Compute next_review_date]
                              → [Update content_items]
                              → [Stamp verified_at]
                              → [Notify owner]

Detailed steps

1. Compute next review date
   • Function: computeNextReviewDate(currentNext, cadenceDays)
   • Logic: next_review_date = GREATEST(currentNext, today) + review_cadence_days. NULL cadence_days short-circuits — no advance.
2. Update content item
   • Update set: governance_review_status = 'approved', governance_reviewer_id = userId, governance_review_due = null, verified_at = now(), optionally next_review_date = computed.
3. Best-effort notification
   • Builder mirrors the cron flow; failure logged but does NOT roll back the approval (transactional boundary is the row update only).

State transitions (governance_review_status)

Current state	Event	Next state	Side effects
pending	Reviewer approves	approved	verified_at stamped; next_review_date advanced
pending	Reviewer requests changes	changes_requested	Reviewer ID stamped
pending	Reviewer reverts	reverted	governance_review_due cleared
approved	next_review_date lapses	review_overdue	Set by review-cadence cron
review_overdue	Reviewer approves	approved	Same auto-renewal applies

This is the symmetric pair to Workflow 1 — items flagged by the cron come back through this approval flow to clear and renew.

---

Workflow 3: Taxonomy Sync (admin-triggered)

Trigger: Admin presses “Sync taxonomy” in the TaxonomyDriftBanner (only visible while DB taxonomy hash diverges from taxonomy_sync_state.last_sync_hash). Owner: app/api/admin/taxonomy-sync/route.ts (dispatch), .github/workflows/taxonomy-sync.yml (worker), and app/api/admin/taxonomy-sync/callback/route.ts (closer).

Flow

[Admin click] → [POST /api/admin/taxonomy-sync]
              → [Compute hash → compare against state]
              → IN_SYNC → [recordPipelineRun completed (no-op)] → 200
              → DRIFT  → [Insert running pipeline_runs row]
                       → [dispatchTaxonomySync(runId)]
                       → 200 { dispatched: true, run_id }
              ⤥
[GH workflow] → [Regenerate classification.md + snapshot + plugin]
              → [Commit to repo]
              → [POST /api/admin/taxonomy-sync/callback { run_id, ok }]
              → [Update pipeline_runs status]
              → [Update taxonomy_sync_state.last_sync_hash]

Detailed steps

1. Compute current hash
   • File: lib/taxonomy/sync-trigger.ts
   • Function: computeTaxonomyHash({ domains, subtopics })
   • Hashes the classification-relevant fields only (not display ordering).
2. Compare against stored hash
   • Read singleton row from taxonomy_sync_state.
   • Match → record no-op pipeline_runs row + return.
3. On drift, insert running row via raw sb()
   • recordPipelineRun() only accepts terminal statuses; the route uses raw sb() to insert with status: 'running'.
4. Dispatch to GitHub
   • File: lib/integrations/github-dispatch.ts
   • Function: dispatchTaxonomySync(runId)
   • Output: { ok: boolean } plus error context on failure.
5. Workflow regenerates artefacts
   • Steps: load taxonomy from DB → render classification.md → write snapshot JSON → rebuild plugin → commit + push.
6. Workflow callback
   • File: app/api/admin/taxonomy-sync/callback/route.ts
   • Auth: workflow PAT (no user session)
   • Action: flip pipeline_runs.status and update taxonomy_sync_state.last_sync_hash + last_synced_at.

Error handling

Error condition	Handling	User feedback
Hashes match	recordPipelineRun(completed, items_processed: 0)	200 { dispatched: false }
dispatchTaxonomySync fails	Update pipeline_runs to failed + Sentry + 502 with actionable error	502 with hint
Workflow itself fails	Callback flips pipeline_runs → failed; banner remains (hash unchanged)	Drift banner stays visible

---

Workflow 4: Env-Flip (Default-Staging) — CLI Invocation

Trigger: Operator runs a CLI script. Owner: .env.local (single source of truth — .env retired in kh-prod-readiness-S6) + per-script --env flag (top-10 scripts) + docs/runbooks/local-development.md.

Flow

[Operator] → bun/python <script>      → [reads .env.local → STAGING]
[Operator] → SUPABASE_URL=<prod>       → [overrides .env.local for that invocation]
                bun/python <script> --env=prod
                                      → [script asserts SUPABASE_URL contains 'rovrymhhffssilaftdwd']
                                      → [proceeds against prod]

Detailed steps

1. Default invocation hits staging. .env.local points at branch turayklvaunphgbgscat post-WP-S5.2.
2. Pattern A — --env=prod flag (top-10 scripts). Operator provides prod env vars at invocation time; script’s --env=prod flag asserts the supplied URL really is the prod project ref. Script aborts with a typed exit code on mismatch.
3. Pattern B — explicit invocation only. The remaining always-prod scripts (per spec D-21=(c)) document the invocation pattern; no flag guard. Future batch flag adoption captured in spec §9 D-21.
4. CI scripts read env from GitHub Environment scope (Production for build/CI, Staging for E2E + MCP eval). Case-sensitive scope names per docs/runbooks/github-environments.md.

Verification commands

Check	Command
Confirm staging is the default	bun run kb-search "test" returns staging results
Cross-env parity probe	bun run scripts/db-row-count-diff.ts --source=prod --target=staging
user_profiles parity	bun run scripts/verify-user-profiles-parity.ts --env=auto
CLI link sanity (post-flip)	cat supabase/.temp/project-ref before supabase db push

---

Workflow 5: GDPR Data Subject Export

Trigger: Operator receives a verified UK GDPR Article 15 / Article 20 request. Owner: scripts/export-user-data.ts + docs/handover/gdpr-data-export.md. SLA: One calendar month (Article 12 §3); extendable once by two months for complex requests with prior written notice.

Flow

[Subject email] → [Operator verifies identity (≥2 factors)]
                → [bun run scripts/export-user-data.ts --env=prod
                    --user-id <uuid> --output ./exports/
                    --article=15|20]
                → [Reads ~20 PII tables under service-role key]
                → [Writes JSON files + CSV summaries + Markdown index
                   + SHA-256 manifest to <output>/<uuid>-<ts>/]
                → [Operator delivers bundle via secure channel]
                → [Operator logs request in DSAR register]

Detailed steps

1. Identity verification (manual) — minimum two of email control, account knowledge, photo ID. Photo ID deleted post-verification.
2. Run export script — see runbook §3 for full PII inventory and per-flag scope distinction (Article 15 = full, Article 20 = portability subset).
3. Inspect bundle — manifest.json lists every file with SHA-256 for tamper-evidence; Markdown index summarises contents in plain English.
4. Deliver — encrypted email or signed download. Document in DSAR register.

Exit codes

Code	Meaning
0	Success — bundle written
1	Subject not found (no auth.users row)
2	Export error (DB unreachable, write permission, etc.)

---

Workflow 6: CI Gate Trio (E2E Smoke + MCP Eval + Migration Replay)

Trigger: PR opened/updated, or push to main / production-readiness. Owner: .github/workflows/ci.yml (quality + e2e-smoke + mcp-eval) + .github/workflows/migration-replay.yml.

Flow

[PR / push] → [quality job — lint, format, knip, build, test]
            ⤥ parallel
            → [e2e-smoke job — Playwright @smoke against staging]
            ⤥ parallel
            → [mcp-eval matrix l1/l3/l4 — MCP server vs staging]
            ⤥ when supabase/{migrations,seed.sql,config.toml} change
            → [migration-replay — ephemeral preview branch +
                supabase db push --linked]
            → [GitHub status checks reported]
            → [Branch protection enforces (post-Pro upgrade)]

Detailed steps

1. Quality job (ci.yml#quality) — runs on Production env scope. lint → format:check → knip baseline guard → build → test:build → vitest (changed-only on PR; full on push) → conditional pytest. Build output regression scan (__tests__/build/env-substitution.test.ts) protects against the S7 P0 safeParse(process.env) substitution-defeat class.
2. E2E smoke job (ci.yml#e2e-smoke) — runs on Staging env scope. bunx playwright test --project=smoke against the staging dev server. continue-on-error: true until staging eval-fixture seed lands.
3. MCP eval job (ci.yml#mcp-eval) — Staging env scope. Matrix [l1, l3, l4]. Skip-flag policy: full mode on push-to-main and MCP-touch PRs (paths-filter); --skip-ai / --skip-search on other PRs to keep AI cost zero.
4. Migration replay (migration-replay.yml) — Production env scope. Creates an ephemeral Supabase preview branch via Management API, replays every migration via supabase db push --linked, deletes the branch (always cleanup). Catches squash-divergence at PR time.
5. Cleanup — migration-replay.yml runs cleanup on if: always() so branch leakage on PR-cancel is minimal.

Excluded actions

anthropics/claude-code-action@v1 is intentionally excluded from Dependabot updates (per docs/runbooks/ci.md §8). Routines via Claude.ai seats handle the equivalent surface.

---

Workflow 8: Background Queue Tick (S221–S223 §5.4)

Trigger: Vercel Cron emits GET /api/cron/process-queue Authorization: Bearer <CRON_SECRET> every 5 minutes (*/5 * * * * in vercel.json), maxDuration=50. Owner: app/api/cron/process-queue/route.ts (handler) + lib/queue/visibility-timeout.ts (reaper) + lib/queue/dispatch.ts (handler switch) + lib/queue/auth.ts (re-validation) + lib/queue/failure.ts (transient/permanent classification + backoff).

Two-consumer topology (ID-372 {372.2}, 26/07/2026). The queue has a SECOND consumer: scripts/bid_worker.py (Coolify poller, 2 s cadence) handles template_fill + analyse_form and fails every other type. The cron route handles form_draft_all + batch_reclassify and permanently fails every other type via its no_handler_registered default. Without type scoping, each consumer destroyed the other’s jobs whenever it won the claim race — 24 form_draft_all rows falsely failed on Platform staging (17–26/07/2026) before the fix. Migration 20260726231847_* widens claim_next_job with optional p_job_types (include list; empty claims nothing — fails closed) and p_exclude_job_types (exclude list; empty excludes nothing); NULL for either preserves the prior behaviour byte-for-byte. The cron passes p_exclude_job_types = WORKER_JOB_TYPES (deliberately an exclude list so its PermanentJobError default stays the loud dead-letter for genuinely unhandled types); bid_worker.py passes p_job_types = WORKER_JOB_TYPES. WORKER_JOB_TYPES is canonically ['template_fill', 'analyse_form'] in lib/queue/worker-job-types.ts and mirrored as a Python literal in scripts/bid_worker.py; a py↔ts parity test pins the two lists together so an edit to either without the other fails CI.

Flow

[Vercel Cron] → [verifyCronAuth]
              → [reapStuckJobs(supabase)]   -- reap_stuck_jobs RPC
              → loop:
                  [claim_next_job RPC]      -- gates updated_at <= NOW()
                  [reValidateAuthContext]   -- failed if role demoted
                  [runJobByType(job)]       -- dispatch.ts switch
                  [success → status='completed', completed_at, result]
                  [transient → handleJobFailure(transient): attempts++,
                                updated_at = NOW() + backoff, status='pending']
                  [permanent → handleJobFailure(permanent): status='failed']
                  [retry exhausted → status='dead_lettered']
              → [tick budget exhausted → return; next tick continues]

Detailed steps

1. Cron auth. verifyCronAuth(request) from lib/cron-auth.ts checks the bearer token against CRON_SECRET. 401 on mismatch.
2. Reap stuck jobs. Calls reap_stuck_jobs(p_timeout_seconds) RPC (S223 W3-A migration 20260505153750_*). The RPC performs UPDATE processing_queue SET status='pending', attempts = attempts + 1 WHERE status='processing' AND started_at < NOW() - make_interval(secs => <p_timeout_seconds>) and RETURNs the count. Default timeout 5 minutes; bounded by Vercel Lambda hard cap (15min) so anything stuck >5min is genuinely orphaned.
3. Claim loop. Each iteration: claim_next_job() RPC selects the oldest pending row with updated_at <= NOW() (S223 W3-A backoff window — gates re-claim until backoff window expires) using FOR UPDATE SKIP LOCKED. Returns SETOF processing_queue (0 or 1 rows). Per ID-372 {372.2} the cron invocation passes p_exclude_job_types = WORKER_JOB_TYPES (see the topology note above) so it never claims a job the Python bid worker owns; an optional ?idempotency_key_prefix= query param narrows the tick to one tranche (ID-128 {128.21}).
4. Re-validate auth. reValidateAuthContext(serviceClient, userId, enqueuedRole, requiredRole) reads the user’s current role from user_roles and faileds the job if role rank dropped below requiredRole (e.g. editor → viewer between enqueue and claim). Error message verbatim: "enqueueing user role no longer authorised: enqueued=<r1>, current=<r2>, required=<r3>".
5. Dispatch. runJobByType(job, supabase) in lib/queue/dispatch.ts switches on job.job_type and calls the handler. Handlers throw PermanentJobError for unrecoverable failures (envelope schema mismatch, unknown user, quality-gate refusal); other errors are classified as transient by isTransientError.
6. Failure handling. handleJobFailure(supabase, job, err):
   • Transient + attempts < max_attempts: attempts++, error_message=<reason>, status='pending', updated_at = NOW() + backoff where backoff = (attempts * 30s) + random(0..5s) (linear with jitter).
   • Transient + attempts === max_attempts: status='dead_lettered' (terminal, surfaces in admin Sentry alert per spec §6.1).
   • Permanent: status='failed', error_message=<reason>, no retry. attempts is still incremented (one attempt was made).
7. Tick budget. Worker stops claiming after ~40s elapsed (10s headroom for finalisation). The next */5 tick continues from the queue head.

Acceptance criteria + integration coverage

12 ACs in docs/specs/background-queue-infra-spec.md §8 cover happy path + retry + transient/permanent + visibility timeout + idempotency + auth-context re-validation + cancellation + dead-letter + concurrency + pipeline_runs linkage. Integration tests at __tests__/integration/queue/{lifecycle,concurrency}.integration.test.ts drive the production lib/queue/* + cron route through the staging Supabase branch (turayklvaunphgbgscat) and assert on observable DB state transitions only — no mocked supabase, no mocked queue lib.

Companion APIs (UI consumers)

• GET /api/jobs/[id]/status — single-row poll for in-progress widgets.
• PATCH /api/jobs/[id]/cancel — cancel a pending job. Race-safe filter .in('status', ['pending']); 409 Conflict if the job has already transitioned to processing.

Failure-mode reference

Symptom	Likely cause	Recovery
Cron returns 401 every tick	CRON_SECRET env mis-configured	Vercel project env settings; per spec §9 R7
Queue depth grows unbounded	Tick budget < per-job runtime; not enough ticks	Bump cron schedule from */5 to * (every minute) per spec §9 R2
dead_lettered rows accumulate	Persistent transient failures (Anthropic 429 burst, Supabase blip)	Investigate via admin query; manual replay or discard
processing_queue row growth → table bloat	No archive policy yet (per spec §9 R4)	Authored default: weekly DELETE WHERE completed_at < NOW() - 30 days; per-candidate spec lands archive job

---

Workflow 7: Supabase Advisor Lint Guard (S13 WP-G4.6)

Trigger: Nightly cron 02:00 UTC, push to main touching supabase/migrations/**, or workflow_dispatch. Owner: .github/workflows/supabase-advisors.yml → scripts/run-supabase-advisors.ts.

Flow

[Trigger] → [Fetch security + performance findings via Management API]
          → [Normalise into baseline-shaped records]
          → [Diff against committed baseline]
          → [New findings → exit 1 (CI fail)]
          → [No new → exit 0]
          → [Stale baseline entries → log informational]

Detailed steps

1. Fetch findings — direct fetch() against GET /v1/projects/<ref>/advisors/security and ?type=performance. No supabase-js (Bun-204 hang gotcha); no new npm deps.
2. Normalise + cache_key — script computes a stable cache key per finding so reruns are idempotent.
3. Diff vs baseline — baseline lives at docs/audits/kh-production-readiness-phase-1/supabase-advisor-baseline.json. New findings → exit 1; baseline-only entries → informational.
4. Operator action on failure — fix the underlying advisory (the common case) or rebase the baseline via --capture-baseline (for intentional, reviewed findings).

Exit codes

Code	Meaning
0	Pass — no new findings vs baseline
1	New findings present (PR-blocking)
2	Infrastructure failure (API unreachable, auth, baseline IO, etc.)

---

Automated Processes

Process	Schedule (UTC)	Route / Script	Purpose
Freshness transitions	15 3 * * *	/api/cron/freshness-transitions	Detect freshness state changes; bridge to governance per domain
Review cadence	45 3 * * *	/api/cron/review-cadence	Flag items past next_review_date; notify owners/admins
Classification quality audit	0 4 * * 0 (Sun)	/api/cron/classification-quality	Audit low-confidence classifications
Coverage alerts	0 5 * * 1 (Mon)	/api/cron/coverage-alerts	Domain coverage threshold checks
Content gaps	30 5 * * 1 (Mon)	/api/cron/content-gaps	Scan template requirement gaps; create notifications
Quality score recalc	0 5 * * 0 (Sun)	/api/cron/quality-score	Periodic quality score recalculation
Intelligence poll	*/15 * * * *	/api/cron/intelligence-poll	Poll Sector Intelligence feeds (15-min cadence)
Intelligence cleanup	0 3 * * 0 (Sun)	/api/cron/intelligence-cleanup	Clean up stale intelligence (90-day retention)
Background queue tick	*/5 * * * *	/api/cron/process-queue	Reap stuck jobs + claim next + dispatch handler (S221–S223 §5.4)
Supabase advisor lint	0 2 * * *	.github/workflows/supabase-advisors.yml	Diff Management API findings vs baseline; fail on new entries
Dependabot bun/pip/actions	Mondays	.github/dependabot.yml	Weekly dependency PRs (bun + pip + github-actions ecosystems)

Integration Points

External system	Direction	Protocol	Purpose
Supabase Auth (GoTrue)	Read	HTTPS	auth.admin.listUsers() for last_sign_in_at (soft-fail to NULL)
Supabase Postgres	Read/Write	PostgREST + RPC	Primary data store; service-role for cron, RLS-scoped for app reads
Supabase Management API	Read	HTTPS + PAT	Advisor lint, ephemeral preview branch lifecycle
GitHub Actions (workflow_dispatch)	Write	HTTPS + PAT	Taxonomy sync, migration replay, advisor lint scheduling
Vercel Cron	Inbound	HTTPS + Bearer	Triggers /api/cron/* routes
Anthropic Claude	Request	HTTPS	Classification, summaries, two-pass entity validation
OpenAI	Request	HTTPS	Embeddings (text-embedding-3-large)
Sentry	Outbound	HTTPS	Error tagging by requestId + release SHA

Current Limitations

• Cron handlers do not retry on transient failures within a single run; the completed_with_errors status surfaces partial completion but the next scheduled invocation is the recovery window.
• Auto-renewal (computeNextReviewDate) short-circuits on NULL cadence; items without an explicit review_cadence_days do not advance, even after approval.
• Taxonomy sync workflow callback path is admin/PAT-only; dashboard wiring of the before-user-created Supabase Auth event remains manual (post-reset hazard).
• E2E smoke / MCP eval / migration replay run with continue-on-error until the staging eval-fixture seed lands (roadmap §9.16.10).
• No automated content-owner reassignment on user deactivation.
• The advisor lint baseline is single-region (security + performance); if Supabase ships compliance / deprecation categories, the script must be extended (the codebase has the union-type extension point marked).