Skip to content
Canonical

Intelligence Workspaces — PRODUCT

Intelligence Workspaces — PRODUCT

Section titled “Intelligence Workspaces — PRODUCT”

Status: [CURRENT-CANONICAL] — NEW-S243 + Wave 0.5 audit (S244) extended Shape B to 3 columns + surfaced 1 missed read site + ratified type-interface drift resolution. Spec destination ratified by Liam at S243 interim (Item 15 of docs/plans/phase-0-investigation/pre-s244-project-feedback.md). Substrate from docs/specs/reserved-workspace-seats/{PRODUCT,TECH}.md (S-1..S-8 seat-shape invariants) + S243 Item 12 audit (live prod DB snapshot of the 4 prod intelligence workspaces) + S244 Wave 0.5 live-DB + codebase audit (admin-only relevance_threshold SI-L5 setting promoted to typed column; type-interface drift surfaced in hooks/intelligence/use-intelligence-workspaces.ts).

How to use this doc

Section titled “How to use this doc”

This file holds user-perspective invariants for the intelligence_workspaces satellite table — the seat reserved per docs/specs/reserved-workspace-seats/PRODUCT.md S-1 for the intelligence application type, with three typed columns (company_profile_id, guide_id, relevance_threshold) promoted from JSONB per the Shape B pattern. Companion TECH.md carries the per-invariant DDL + migration retrofit + verification queries.

Invariants are numbered globally (S-1, S-2, …) so TECH.md references by ID. Each carries [RATIFIED-S2XX] with source citation. Invariants below extend (not replace) the reserved-workspace-seats spec — RWS S-1..S-8 govern the seat shape; the invariants here govern the three typed columns specific to the intelligence seat.

Source-of-truth pointers

Section titled “Source-of-truth pointers”
  • docs/specs/reserved-workspace-seats/{PRODUCT,TECH}.md — RWS S-1..S-8 (seat-shape invariants for all 5 non-procurement satellites, including this one).
  • docs/plans/phase-0-investigation/pre-s244-project-feedback.md Item 12 (intelligence_workspaces JSONB Shape B promotion audit — 3 of 4 prod intel workspaces have company_profile_id; 2 of 4 have guide_id) + Item 15 (NEW Phase 1 spec gate for intelligence/sales-proposal/product-guide workspaces).
  • docs/specs/id-31-canonical-pipeline-implementation-plan/PLAN.md §5 row “intelligence-workspaces” + §4.2 sub-task 8 (T2 combined-PR migration consumer).
  • docs/plans/phase-0-investigation/architecture/04-workspace-types.md §3.2 (intelligence as one of 6 baseline core-provenance application types) + §4.2 (satellite pattern: PK + FK + RLS upfront; per-app columns via feature spec).
  • docs/plans/phase-0-investigation/architecture/07-collapse-list.md §3.4 (procurement Shape B precedent — JSONB → typed-column promotion pattern).
  • docs/specs/rls-pattern/PRODUCT.md P-1 (auto-RLS event trigger) + P-2 (per-role grants helper) — leveraged for S-3 + S-4 below.

Audience

Section titled “Audience”

Engineers writing the T2 combined-PR migration’s Shape B promotion sub-task (PLAN.md §4.2 sub-task 8); reviewers verifying the migration’s correctness against this spec; intelligence-feature engineers reading the three JSONB-buried fields via the typed surface post-migration; CI guard authors verifying no JSONB-fallback regressions.

Invariants

Section titled “Invariants”

S-1 — Three typed columns on the intelligence_workspaces satellite

Section titled “S-1 — Three typed columns on the intelligence_workspaces satellite”

The intelligence_workspaces satellite table (created upfront per RWS S-1) carries three application-specific typed columns beyond the RWS-S-2 PK and RWS-S-3 workspace_id FK:

  1. company_profile_id uuid NULL REFERENCES company_profiles(id) — FK to live company_profiles table.
  2. guide_id uuid NULL REFERENCES guides(id) — FK to live guides table.
  3. relevance_threshold real NULL CHECK (relevance_threshold IS NULL OR (relevance_threshold >= 0.1 AND relevance_threshold <= 1.0)) — admin-only SI-L5 setting controlling per-workspace article-scoring threshold (currently read at lib/intelligence/pipeline.ts:92-95; write path admin-gated at app/api/intelligence/workspaces/[id]/route.ts:78-126; Zod validator at lib/validation/schemas.ts:1150-1154). NOT an FK — it is a CHECK-constrained numeric setting whose range mirrors the Zod schema (z.number().min(0.1).max(1.0).optional()).

All three columns are nullable because the audit of the 4 live prod intelligence workspaces showed partial coverage — not every intelligence workspace carries every ID, and no admin has configured relevance_threshold yet (0 prod rows currently set — see S-2 audit data).

[RATIFIED-S243] for columns 1-2 — per pre-s244-project-feedback.md Item 12 (live DB audit ratification) + PLAN.md §5 row “intelligence-workspaces” (RATIFIED-S243 Item 15). [RATIFIED-S244 Wave 0.5] for column 3 — per Wave 0.5 live-DB + codebase audit confirming relevance_threshold is a live admin-only setting (SI-L5 invariant) with write path + UI + Zod schema + tests already in place; 0 prod rows currently carry the JSONB key but the surface is live. The Shape B promotion mirrors the procurement satellite’s 6-column promotion per 07-collapse-list.md §3.4 (typed columns over JSONB for queryable / behaviour-gating fields).

Cross-refs: [RWS S-1], [RWS S-7] (the per-app columns landed via this feature spec, not the v1 reserved-seats migration), [07-collapse-list §3.4], [PLAN.md §4.2 sub-task 8], [lib/validation/schemas.ts:1150-1154], [app/api/intelligence/workspaces/[id]/route.ts:78-126], [lib/intelligence/pipeline.ts:92-95].

S-2 — Backfill from live prod state preserves the 4 existing intelligence workspaces

Section titled “S-2 — Backfill from live prod state preserves the 4 existing intelligence workspaces”

When the T2 combined-PR migration applies, the existing 4 prod intelligence workspaces are backfilled from their current workspaces.domain_metadata JSONB blobs into the typed columns. The 20/05/2026 live-DB audit (per pre-s244-project-feedback.md Item 12, re-verified during this spec’s authoring + extended by Wave 0.5 S244 audit for the relevance_threshold key) recorded:

Workspace namecompany_profile_id in JSONBguide_id in JSONBrelevance_threshold in JSONB
Education Sector Monitoryes (7f50c92d-…)nono
[SI-GNEWS-DEDUP-…] GNews Dedup Workspacenonono
MAT Auditingyes (7f50c92d-…)yes (d42b2651-…)no
NHS Digital Cyber alertsyes (7f50c92d-…)yes (7d32f790-…)no

Counts: 3 of 4 have company_profile_id; 2 of 4 have guide_id; 0 of 4 have relevance_threshold. After backfill, all 4 satellite rows exist; the typed columns hold the values previously buried in JSONB, with NULLs where the JSONB key was absent. No row carries relevance_threshold — the typed column lands NULL on all 4 satellite rows (this is expected: no admin has configured it yet, but the write path is live and the column must exist to receive future writes).

[RATIFIED-S243] for company_profile_id + guide_id audit data — per pre-s244-project-feedback.md Item 12. [RATIFIED-S244 Wave 0.5] for the relevance_threshold no-op backfill confirmation. Verbatim audit-data confirmation in companion TECH.md T-2 validation.

Cross-refs: [Item 12], [PLAN.md §4.2 sub-task 8].

S-3 — JSONB keys stripped from workspaces.domain_metadata post-backfill

Section titled “S-3 — JSONB keys stripped from workspaces.domain_metadata post-backfill”

After the typed-column backfill (S-2) completes, the migration strips all three keys (company_profile_id, guide_id, relevance_threshold) from workspaces.domain_metadata JSONB for all rows where workspaces.type = 'intelligence' (pre-procurement-rename — see RWS S-8 / 04-workspace-types.md §7.1 for naming convention notes; this spec uses the post-rename application_type='intelligence' framing once T2 combined-PR completes the FK swap, but the JSONB strip query targets the pre-FK-swap predicate within the same migration transaction).

The result post-migration: workspaces.domain_metadata for intelligence rows MUST NOT contain the keys company_profile_id, guide_id, or relevance_threshold. Post-migration domain_metadata for intel rows is an empty {} JSONB object (or carries only non-intelligence-spec keys if any are introduced by adjacent specs in the same transaction — none currently). The typed columns on intelligence_workspaces become the single source of truth. The strip is performed in the same transaction as the backfill to prevent dual-source ambiguity.

[RATIFIED-S243] for company_profile_id + guide_id strip — per pre-s244-project-feedback.md Item 12 framing (Shape B promotion strips JSONB after typed-column backfill — same as procurement Shape B per 07-collapse-list.md §3.4). [RATIFIED-S244 Wave 0.5] for relevance_threshold strip — extends the strip to match the full Shape B promotion ratified for column 3.

Cross-refs: [07-collapse-list §3.4], [Item 12].

S-4 — RLS auto-enabled on intelligence_workspaces table

Section titled “S-4 — RLS auto-enabled on intelligence_workspaces table”

When the migration applies, the intelligence_workspaces table has row-level security enabled by the rls_auto_enable() event trigger per docs/specs/rls-pattern/PRODUCT.md P-1. The seat migration also emits explicit ALTER TABLE ... ENABLE ROW LEVEL SECURITY as a belt-and-braces safeguard per RWS S-4 fall-back clause.

[RATIFIED-S243] — inheritance from RWS S-4 + RLS-PATTERN P-1. The RLS-PATTERN combined migration (supabase/migrations/20260514150238_*.sql) has APPLIED-S239 per PLAN.md §4.3 — the auto-trigger is live, so the seat migration’s explicit ALTER is idempotent.

Cross-refs: [RWS S-4], [RLS-PATTERN P-1].

S-5 — Per-role grants applied via RLS-PATTERN T-2 helper

Section titled “S-5 — Per-role grants applied via RLS-PATTERN T-2 helper”

The migration calls SELECT grant_standard_public_table_access('public.intelligence_workspaces'::regclass) per docs/specs/rls-pattern/PRODUCT.md P-2 + RWS S-6. This applies the standard 3-role grants block (anon SELECT; authenticated SELECT/INSERT/UPDATE/DELETE; service_role SELECT/INSERT/UPDATE/DELETE).

[RATIFIED-S243] — inheritance from RWS S-6 + RLS-PATTERN P-2. The grants helper has APPLIED-S239 per PLAN.md §4.3.

Cross-refs: [RWS S-6], [RLS-PATTERN P-2].

S-6 — Existing intelligence surface continues to function across the migration

Section titled “S-6 — Existing intelligence surface continues to function across the migration”

The migration MUST NOT break the existing intelligence feature surface (app/api/intelligence/, lib/intelligence/, app/intelligence/[workspaceId]/page.tsx, components/intelligence/workspace-settings.tsx, scripts/batch-rescore-articles.ts) which currently reads workspaces.domain_metadata.company_profile_id, .guide_id, and .relevance_threshold at multiple call sites (full inventory in companion TECH.md T-5).

Helper-first hybrid (ratified S244 Wave 0.5). The migration approach is fixed (no longer “drafter judgement”):

  • A typed read-path helper getIntelligenceWorkspaceContext(supabase, workspaceId) lands in lib/intelligence/workspace-context.ts before the T2 migration applies. It returns typed { companyProfileId, guideId, relevanceThreshold } reading from the satellite table via JOIN through workspace_id.
  • The hot-path code sweep (5 app/api/intelligence/* routes + lib/intelligence/pipeline.ts + app/intelligence/[workspaceId]/page.tsx + components/intelligence/workspace-settings.tsx + the API response shape + the Zod schema + the IntelligenceWorkspace interface in hooks/intelligence/use-intelligence-workspaces.ts:13-18) is bundled in the same PR as T2. This eliminates the “JSONB returns undefined for an unknown window” transitional state for the live web surface.
  • The CLI batch tool scripts/batch-rescore-articles.ts:157-158 is deferred to a follow-on PR marked TODO(T2-followup). This is acceptable because the CLI is run on demand (not in serving paths) and its silent-degradation footprint is bounded: a rescore run launched between T2 apply and the follow-on PR returns “no profile” and effectively no-ops the company-context filter; recovery is a re-run after the follow-on lands.

Type-interface drift (critical risk). hooks/intelligence/use-intelligence-workspaces.ts:13-18 declares IntelligenceWorkspace.domain_metadata as a typed shape carrying all three fields. Post-T2 strip, the API response no longer matches this interface. Per full Shape B promotion ratified at S244 Wave 0.5: drop the domain_metadata shape from the interface entirely; surface company_profile_id, guide_id, and relevance_threshold as typed top-level fields on the API response, fed from the JOIN through the satellite table. The single internal consumer (app/intelligence/[workspaceId]/page.tsx) is rewritten in the same PR — no external API clients exist to preserve back-compat for.

The PRODUCT invariant: a feature regression test for the existing intelligence surface MUST pass before and after the migration applies on staging. No silent capability loss between the FK + RLS being in place and the new typed-column reads being wired. The helper-first hybrid + bundled code sweep makes this trivially satisfiable for the live web surface; the CLI follow-on is the only deferred site.

[RATIFIED-S243] for the no-regression invariant — derived from pre-s244-project-feedback.md Item 12 implicit requirement (Shape B promotion gates T2; T2 must not break live intelligence functionality) + CLAUDE.md “Silent failures in Supabase calls” gotcha (silent JSONB-fallback regression is the exact failure mode the typed promotion is meant to prevent). [RATIFIED-S244 Wave 0.5] for the helper-first hybrid migration approach + the type-interface drift resolution.

Cross-refs: [Item 12], [CLAUDE.md Silent failures gotcha], [hooks/intelligence/use-intelligence-workspaces.ts:13-18], [scripts/batch-rescore-articles.ts:157-158].

S-7 — Typed column target / range validity at migration time

Section titled “S-7 — Typed column target / range validity at migration time”

Of the three typed columns, two are FK references and one is a CHECK-constrained numeric setting:

  • company_profile_id references company_profiles(id). Per RWS-S-3 inheritance, FK shape uses REFERENCES <target>(id) with appropriate ON DELETE behaviour (see companion TECH.md T-1 for the exact ON DELETE clause). Target table exists in production schema per docs/reference/SCHEMA-QUICK-REFERENCE.md §24.
  • guide_id references guides(id). Same FK pattern. Target table exists per docs/reference/SCHEMA-QUICK-REFERENCE.md §30 “Supporting Tables → guides”.
  • relevance_threshold has NO FK target — it is a CHECK-constrained numeric setting whose range matches the live Zod validator at lib/validation/schemas.ts:1150-1154 (z.number().min(0.1).max(1.0).optional()). The DB CHECK constraint enforces relevance_threshold IS NULL OR (relevance_threshold >= 0.1 AND relevance_threshold <= 1.0) so any write outside the Zod-validated range is rejected at the storage layer as a belt-and-braces.

The spec author verified the 3 live FK target records resolve correctly during S243 audit re-verification (one company_profile row, two guide rows referenced by the 4 prod intel workspaces). No relevance_threshold value exists in production yet (0/4 rows carry the JSONB key per S-2) so range-violation surface area is zero at migration time — but the CHECK constraint protects future writes.

The migration MUST verify FK target validity during backfill — any orphaned reference (a JSONB-encoded ID pointing to a deleted company_profile or guide row) MUST surface as a migration error rather than silently failing the typed-column write. Range violations on relevance_threshold cannot occur during S-2 backfill because no row carries the key today; future writes via the admin PATCH route are Zod-validated upstream of the DB.

[RATIFIED-S243] for FK targets — derived from live-DB audit (20/05/2026 verification by spec author) + Postgres FK constraint semantics. [RATIFIED-S244 Wave 0.5] for the CHECK-constrained relevance_threshold column — derived from live Zod schema at lib/validation/schemas.ts:1150-1154 + admin-gating at app/api/intelligence/workspaces/[id]/route.ts:90-95.

Cross-refs: [SCHEMA-QUICK-REFERENCE §24], [SCHEMA-QUICK-REFERENCE §30 guides], [Item 12], [lib/validation/schemas.ts:1150-1154].

S-8 — Future intelligence columns added via feature-spec ALTER

Section titled “S-8 — Future intelligence columns added via feature-spec ALTER”

The Shape B promotion in this spec lifts the three JSONB-buried fields identified by the S243 audit + S244 Wave 0.5 audit. The intelligence application surface may have additional v1.1+ columns surfaced as queryable / FK / RLS-relevant fields (e.g. sector-filter overrides, RSS-source defaults, scoped-search keywords) — those land via ALTER TABLE public.intelligence_workspaces ADD COLUMN ... migrations authored by the v1.1+ intelligence feature spec, NOT by this Phase 1 / S243 + S244 spec.

[RATIFIED-S243] — per RWS S-7 ALTER discipline + PLAN.md §5 framing (“Per-app columns added by feature spec at build time”). v1.1+ intelligence feature scope is OUT OF SCOPE for this spec.

Cross-refs: [RWS S-7], [PLAN.md §5].

Out of scope (v1 / Phase 1)

Section titled “Out of scope (v1 / Phase 1)”
  • Net-new intelligence features. Anything beyond promoting the three existing JSONB-buried fields to typed columns. Sector-filter overrides, RSS-source defaults, scoped-search keywords — all v1.1+ feature-spec scope per S-8.
  • JSONB-only fallback / dual-source reads. Post-migration, intelligence_workspaces typed columns are the single source of truth. No code path may read workspaces.domain_metadata.company_profile_id, .guide_id, or .relevance_threshold after Phase 1 close.
  • Refactoring the existing intelligence feature surface beyond what S-6 mandates. The migration ensures the surface continues to function; comprehensive refactor of app/api/intelligence/ + lib/intelligence/ to the typed columns is a follow-on task (T9-adjacent per PLAN.md ordering) not gated by this spec.
  • Backfill of guide_id-NULL or company_profile_id-NULL rows. The migration backfills exactly what the JSONB carries; rows with no guide_id or no company_profile_id in JSONB stay NULL in the typed columns. Subsequent population is feature-build-time scope.
  • procurement_workspaces Shape B. Owned by the procurement feature spec (PLAN.md §5 procurement-workspaces NEW spec); this spec covers only the intelligence satellite.
  • Other reserved-seat per-app columns. sales_proposal_workspaces, product_guide_workspaces, competitor_research_workspaces, training_onboarding_workspaces — each gets its own feature spec at build time per RWS S-7.

v1.1 candidates (DEFERRED — not blocking)

Section titled “v1.1 candidates (DEFERRED — not blocking)”
  • v1.1+ intelligence feature ALTER columns. Per S-8 — sector-filter overrides, RSS-source defaults, scoped-search keywords, etc., as the v1.1 intelligence feature spec materialises. (relevance_threshold is NOT a v1.1 candidate — promoted to typed column in this spec per S244 Wave 0.5 ratification.)
  • Cross-satellite intelligence aggregation views. Views joining workspaces + intelligence_workspaces + company_profiles + guides for cross-workspace reporting — deferred until at least 2 intelligence workspaces with overlapping company_profiles ship.

End of PRODUCT spec. Implementation references in ./TECH.md.