Intelligence Workspaces — TECH

Status: [CURRENT-CANONICAL] — NEW-S243 + S244 Wave 0.5 audit-driven amendment. Companion to PRODUCT.md. Per-invariant implementation references grounded in current code + target migration shape; gates noted inline. S244 Wave 0.5 amendments: full Shape B = 3 typed columns (added relevance_threshold); T-5 read-path inventory expanded from 13 refs / 6 files to 24+ refs / 13+ files; helper-first hybrid migration approach ratified; type-interface drift in hooks/intelligence/use-intelligence-workspaces.ts:13-18 resolved by surfacing typed top-level API response fields.

How to use this doc

This file carries implementation references for each S-N invariant in PRODUCT.md. Each T-N entry contains:

Current state: code / migration file:line that today implements the invariant, or “greenfield” if no code exists.
Target state: what the platform must do after the T2 combined-PR migration applies (specifically PLAN.md §4.2 sub-task 8).
Gate: any STILL-OPEN dependency.
Validation: how the invariant is verified (live-DB query / migration test / manual).

Source-of-truth pointers

./PRODUCT.md — numbered invariants S-1..S-8.
docs/specs/reserved-workspace-seats/TECH.md T-1..T-8 — seat-shape DDL precedent + RLS + grants patterns inherited here.
docs/specs/id-38-rls-pattern/TECH.md T-1 (auto-RLS event trigger — APPLIED-S239) + T-2 (grants helper — APPLIED-S239).
docs/specs/id-31-canonical-pipeline-implementation-plan/PLAN.md §4.2 sub-task 8 — the T2 sub-task that consumes this spec.
docs/plans/phase-0-investigation/architecture/07-collapse-list.md §3.4 — procurement Shape B precedent (JSONB → typed-column promotion).
docs/plans/phase-0-investigation/pre-s244-project-feedback.md Item 12 — audit data (4 prod intel workspaces; 3/4 carry company_profile_id, 2/4 carry guide_id).
docs/reference/SCHEMA-QUICK-REFERENCE.md §24 company_profiles + §30 “guides” sub-section — FK target table shapes.
Existing intelligence surface (read-path call sites that the migration must not break): app/api/intelligence/workspaces/route.ts (lines 39, 124, 168, 190, 252-258, 271); app/api/intelligence/workspaces/[id]/route.ts (lines 39, 104); app/api/intelligence/workspaces/[id]/prompts/preview/route.ts (lines 102, 107); app/api/intelligence/workspaces/[id]/flags/analyse/route.ts (line 124); app/intelligence/[workspaceId]/page.tsx (line 59); lib/intelligence/pipeline.ts (line 98); lib/intelligence/guide-generator.ts (lines 92, 120, 137, 150).

Audience

Engineers writing the T2 combined-PR migration’s sub-task 8 (PLAN.md §4.2); reviewers verifying migration compliance; intelligence-feature engineers updating the read-paths to consume typed columns post-migration.

T-1 — Three typed columns on `intelligence_workspaces` satellite (implements S-1)

Current state: Greenfield. intelligence_workspaces table does not exist in either staging (turayklvaunphgbgscat) or production (rovrymhhffssilaftdwd) schemas — the RWS upfront-seats migration that creates the table is part of the T2 combined-PR (PLAN.md §4.2 sub-task 7) and runs immediately before this sub-task in the same migration transaction.

Target state: After the T2 combined-PR migration applies, intelligence_workspaces carries three typed columns alongside the RWS-S-2 PK and RWS-S-3 workspace_id FK:

company_profile_id uuid NULL REFERENCES company_profiles(id) ON DELETE SET NULL
guide_id uuid NULL REFERENCES guides(id) ON DELETE SET NULL
relevance_threshold real NULL CHECK (relevance_threshold IS NULL OR (relevance_threshold >= 0.1 AND relevance_threshold <= 1.0))

FK ON DELETE choice (justification, columns 1+2): ON DELETE SET NULL is chosen over ON DELETE RESTRICT because the intelligence workspace can semantically continue to exist if its company profile or guide is deleted — the workspace simply becomes unbound from that context, mirroring the current JSONB-side behaviour where the workspace persists if the JSONB key is removed. ON DELETE CASCADE would over-couple — a company_profile deletion should not cascade to destroying its intelligence workspace satellite rows.

CHECK constraint choice (justification, column 3): relevance_threshold is a numeric admin setting (not a relational FK); its valid range is enforced upstream by the Zod validator at lib/validation/schemas.ts:1150-1154 (z.number().min(0.1).max(1.0).optional()). The DB-side CHECK mirrors that range as belt-and-braces against any future write path bypassing Zod (e.g. direct SQL admin tooling).

Migration body shape (added to the RWS reserved-seats CREATE TABLE block per RWS T-1, lands within the same T2 combined-PR migration file):

1
-- Within the T2 combined-PR migration, AFTER the RWS reserved-seats block creates
2
-- the intelligence_workspaces shell (per RWS T-1):
3

4
ALTER TABLE public.intelligence_workspaces
5
  ADD COLUMN company_profile_id uuid NULL REFERENCES public.company_profiles(id) ON DELETE SET NULL,
6
  ADD COLUMN guide_id uuid NULL REFERENCES public.guides(id) ON DELETE SET NULL,
7
  ADD COLUMN relevance_threshold real NULL
8
    CHECK (relevance_threshold IS NULL OR (relevance_threshold >= 0.1 AND relevance_threshold <= 1.0));

Alternative valid form (if the reserved-seats block is amended to inline these columns rather than ALTER): include the three column definitions directly in the original CREATE TABLE public.intelligence_workspaces (...) body. The drafter chooses based on the T2 migration’s ergonomics; either shape is conformant.

Lands in: Single T2 combined-PR migration file (filename per supabase migration new); PLAN.md §4.2 sub-task 8 owns these DDL lines.

Gate: RWS reserved-seats block must precede this in the same migration so the table exists for the ALTER (T2 ordering, not a separate migration).

Validation:

SELECT column_name, data_type, is_nullable FROM information_schema.columns WHERE table_schema='public' AND table_name='intelligence_workspaces' AND column_name IN ('company_profile_id','guide_id','relevance_threshold') returns 3 rows; first two uuid + YES nullable; third real + YES nullable.
SELECT conname, confrelid::regclass FROM pg_constraint WHERE conrelid='public.intelligence_workspaces'::regclass AND contype='f' includes FK constraints targeting company_profiles and guides.
SELECT conname, pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid='public.intelligence_workspaces'::regclass AND contype='c' includes the CHECK constraint definition matching the [0.1, 1.0] range.

T-2 — Backfill from JSONB into typed columns (implements S-2)

Current state: 4 prod intelligence workspaces carry company_profile_id / guide_id in workspaces.domain_metadata JSONB; no row currently carries relevance_threshold (0/4 — admin has not configured it yet, but write path is live). The typed columns do not yet exist. Live audit (20/05/2026) re-confirms pre-s244-project-feedback.md Item 12 counts + S244 Wave 0.5 extension for relevance_threshold.

Target state: After the T2 combined-PR migration applies, all 4 satellite rows exist with typed columns populated from the corresponding JSONB blobs. Specifically:

Workspace `id`	`company_profile_id` (typed)	`guide_id` (typed)	`relevance_threshold` (typed)
`cb724cab-c238-46a5-aa7e-a20bc35c4395` (“Education Sector Monitor”)	`7f50c92d-b3bc-4ae3-befc-2db01b3d67e1`	`NULL`	`NULL`
`da1e1d49-25af-44b6-86b4-c8db2958cc45` (“[SI-GNEWS-DEDUP-…] GNews Dedup Workspace”)	`NULL`	`NULL`	`NULL`
`96638cb0-caa5-413f-b20d-555e1e729a7b` (“MAT Auditing”)	`7f50c92d-b3bc-4ae3-befc-2db01b3d67e1`	`d42b2651-5f71-4ce5-931d-3f0755ad193d`	`NULL`
`b4fbcd93-3828-41cc-b99b-52e169afaf92` (“NHS Digital Cyber alerts”)	`7f50c92d-b3bc-4ae3-befc-2db01b3d67e1`	`7d32f790-1e3f-49fd-86b9-2c5fd112d5bc`	`NULL`

Counts after backfill: 4 satellite rows total; 3 with non-NULL company_profile_id; 2 with non-NULL guide_id; 0 with non-NULL relevance_threshold. All FK target records verified live in company_profiles and guides at 20/05/2026 spec-author audit. The relevance_threshold backfill is a no-op against current prod state (the SELECT pulls NULL from the absent JSONB key) — the column exists to receive future admin writes.

Migration body shape:

1
-- 1. First create the satellite rows themselves (workspace_id FK is NOT NULL UNIQUE
2
--    per RWS-S-3, so one row per intelligence workspace).
3
--    The exact predicate depends on T2 ordering of the `workspaces.type` ->
4
--    `workspaces.application_type_id` FK swap (PLAN.md §4.2 sub-task 1):
5
--    BEFORE the swap, filter by `workspaces.type = 'intelligence'`;
6
--    AFTER the swap, filter by joining `application_types` where key='intelligence'.
7
--    The T2 migration is single-transaction, so use whichever predicate matches
8
--    the current state of `workspaces` at the point this INSERT runs.
9

10
INSERT INTO public.intelligence_workspaces (workspace_id, company_profile_id, guide_id, relevance_threshold)
11
SELECT
12
  w.id AS workspace_id,
13
  (w.domain_metadata->>'company_profile_id')::uuid AS company_profile_id,
14
  (w.domain_metadata->>'guide_id')::uuid AS guide_id,
15
  CASE
16
    WHEN w.domain_metadata ? 'relevance_threshold'
17
    THEN (w.domain_metadata->>'relevance_threshold')::real
18
    ELSE NULL
19
  END AS relevance_threshold
20
FROM public.workspaces w
21
WHERE w.type = 'intelligence';  -- ADJUST predicate per T2 sub-task ordering
22

23
-- 2. Verify row count and value parity (in same transaction; an assertion is
24
--    acceptable here via a DO block with RAISE EXCEPTION on mismatch).
25
DO $$
26
DECLARE
27
  v_total int;
28
  v_with_profile int;
29
  v_with_guide int;
30
  v_with_threshold int;
31
BEGIN
32
  SELECT count(*) INTO v_total FROM public.intelligence_workspaces;
33
  SELECT count(*) INTO v_with_profile FROM public.intelligence_workspaces WHERE company_profile_id IS NOT NULL;
34
  SELECT count(*) INTO v_with_guide FROM public.intelligence_workspaces WHERE guide_id IS NOT NULL;
35
  SELECT count(*) INTO v_with_threshold FROM public.intelligence_workspaces WHERE relevance_threshold IS NOT NULL;
36
  IF v_total <> 4 OR v_with_profile <> 3 OR v_with_guide <> 2 OR v_with_threshold <> 0 THEN
37
    RAISE EXCEPTION 'intelligence_workspaces backfill assertion failed: total=%, with_profile=%, with_guide=%, with_threshold=% (expected 4/3/2/0 per S243 audit + S244 Wave 0.5)',
38
      v_total, v_with_profile, v_with_guide, v_with_threshold;
39
  END IF;
40
END $$;

Note on assertion brittleness: The 4/3/2/0 assertion encodes the 20/05/2026 audit snapshot (S243 audit for company_profile_id + guide_id; S244 Wave 0.5 audit for relevance_threshold = 0). If a new intelligence workspace is created OR an admin configures relevance_threshold on any existing row in production between this spec landing and the T2 migration applying, the assertion fails (correctly) and the drafter MUST re-verify the counts + update the assertion. This is intentional — silent count drift would defeat the audit-trail purpose of the typed-column promotion.

Gate: T2 combined-PR ordering — the workspaces.application_type_id FK swap (PLAN.md §4.2 sub-task 1) and the intelligence_workspaces table creation (RWS sub-task 7) must precede this sub-task in the same migration transaction.

Validation:

Staging-apply: query the table post-migration, expect 4 rows with the IDs above.
Production-apply: re-run the assertion DO block as a standalone verification query immediately post-apply.
Cross-check via mcp__supabase__execute_sql: SELECT w.name, iw.company_profile_id, iw.guide_id, iw.relevance_threshold FROM public.intelligence_workspaces iw JOIN public.workspaces w ON w.id = iw.workspace_id ORDER BY w.name; — assert the 4 rows match the table above.

T-3 — JSONB strip post-backfill (implements S-3)

Current state: workspaces.domain_metadata carries company_profile_id and guide_id JSONB keys for the 3 + 2 rows respectively per T-2 audit data; no row currently carries relevance_threshold (0/4 per S244 Wave 0.5 audit) — the third strip is a no-op against current prod state but mandatory for future-write safety.

Target state: After the T2 combined-PR migration applies, workspaces.domain_metadata for all application_type='intelligence' rows MUST NOT contain company_profile_id, guide_id, or relevance_threshold keys. Post-migration domain_metadata for intel rows is an empty {} JSONB object. The typed columns on intelligence_workspaces are the single source.

Migration body shape (runs immediately after T-2 backfill, same transaction):

1
UPDATE public.workspaces
2
SET domain_metadata = (domain_metadata - 'company_profile_id' - 'guide_id' - 'relevance_threshold')
3
WHERE type = 'intelligence'  -- ADJUST predicate per T2 sub-task ordering, same as T-2
4
  AND (
5
    domain_metadata ? 'company_profile_id'
6
    OR domain_metadata ? 'guide_id'
7
    OR domain_metadata ? 'relevance_threshold'
8
  );
9

10
-- Verify strip
11
DO $$
12
DECLARE
13
  v_remaining int;
14
BEGIN
15
  SELECT count(*) INTO v_remaining FROM public.workspaces
16
  WHERE type = 'intelligence'
17
    AND (
18
      domain_metadata ? 'company_profile_id'
19
      OR domain_metadata ? 'guide_id'
20
      OR domain_metadata ? 'relevance_threshold'
21
    );
22
  IF v_remaining <> 0 THEN
23
    RAISE EXCEPTION 'intelligence_workspaces JSONB strip incomplete: % rows still carry stripped keys', v_remaining;
24
  END IF;
25
END $$;

The - JSONB operator removes a key if present and is a no-op if absent — safe to apply across all 4 rows (the GNews Dedup workspace has none of the three keys; no current row has relevance_threshold).

Pattern source: Mirrors 07-collapse-list.md §3.4 procurement Shape B pattern (JSONB → typed-column promotion strips JSONB after typed write succeeds).

Gate: T-2 backfill assertion must pass before this strip runs (same transaction; the assertion’s RAISE EXCEPTION aborts the transaction including any partial strip).

Validation:

Post-apply: SELECT count(*) FROM public.workspaces WHERE type='intelligence' AND (domain_metadata ? 'company_profile_id' OR domain_metadata ? 'guide_id' OR domain_metadata ? 'relevance_threshold') returns 0.
Spot-check: SELECT name, domain_metadata FROM public.workspaces WHERE type='intelligence' ORDER BY name — confirm the 3 rows that previously had company_profile_id no longer contain that key; same for the 2 rows with guide_id; all 4 rows show {} (empty domain_metadata) post-strip.

T-4 — RLS auto-enable + per-role grants (implements S-4 + S-5)

Current state: RLS-PATTERN combined migration (supabase/migrations/20260514150238_*.sql) APPLIED-S239 per PLAN.md §4.3 — both rls_auto_enable() event trigger and grant_standard_public_table_access(regclass) helper are live in staging and production.

Target state: After the T2 combined-PR migration applies, intelligence_workspaces has pg_class.relrowsecurity = true (via auto-trigger + belt-and-braces explicit ALTER per RWS T-4) and the standard 3-role grants applied via the helper (per RWS T-6).

Migration body shape (inherited from RWS T-4 + T-6 patterns; lands as part of the RWS reserved-seats sub-task 7 block, NOT this sub-task 8 specifically — included here for completeness of the end-to-end picture):

1
-- Inherited from RWS T-4 + T-6 patterns (RWS sub-task 7 of T2):
2
CREATE TABLE public.intelligence_workspaces (
3
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
4
  workspace_id uuid NOT NULL UNIQUE REFERENCES public.workspaces(id) ON DELETE CASCADE,
5
  -- typed columns added by THIS sub-task 8 (T-1 above):
6
  company_profile_id uuid NULL REFERENCES public.company_profiles(id) ON DELETE SET NULL,
7
  guide_id uuid NULL REFERENCES public.guides(id) ON DELETE SET NULL,
8
  relevance_threshold real NULL
9
    CHECK (relevance_threshold IS NULL OR (relevance_threshold >= 0.1 AND relevance_threshold <= 1.0)),
10
  created_at timestamptz NOT NULL DEFAULT now(),
11
  updated_at timestamptz NOT NULL DEFAULT now()
12
);
13
ALTER TABLE public.intelligence_workspaces ENABLE ROW LEVEL SECURITY;  -- belt-and-braces per RWS S-4
14
SELECT public.grant_standard_public_table_access('public.intelligence_workspaces'::regclass);

The intelligence-specific per-tenant RLS policies (delegating to the workspaces parent row via EXISTS predicate) are governed by RWS T-5 — same pattern as the other 5 reserved-seat satellites.

Gate: None. RLS-PATTERN APPLIED-S239 + RWS reserved-seats sub-task 7 precedes this sub-task in T2 ordering.

Validation:

SELECT relname, relrowsecurity FROM pg_class WHERE relname='intelligence_workspaces' returns relrowsecurity = true.
SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE table_name='intelligence_workspaces' ORDER BY grantee, privilege_type returns the 3-role pattern (anon SELECT; authenticated SELECT/INSERT/UPDATE/DELETE; service_role SELECT/INSERT/UPDATE/DELETE).
Per-tenant validation: connect as authenticated role for tenant A; insert workspace + satellite for tenant A; assert visible. Switch to tenant B; assert satellite not visible. (Reuses RWS T-5 validation pattern.)

T-5 — Existing intelligence surface continuity (implements S-6)

Current state (full read/write/UI/schema/interface/test inventory — expanded at S244 Wave 0.5 from 13 refs / 6 files to 24+ refs / 13+ files):

`company_profile_id` + `guide_id` sites

File	Line(s)	What it does
`app/api/intelligence/workspaces/route.ts`	39, 124, 168	READ — `meta?.company_profile_id` (list + lookup) + scoped query `eq('id', parsed.data.company_profile_id)`
`app/api/intelligence/workspaces/route.ts`	190, 252-258, 271	WRITE — `domain_metadata: { company_profile_id, guide_id }` on workspace create/update
`app/api/intelligence/workspaces/[id]/route.ts`	39, 104	READ — `meta?.company_profile_id`; comment at L104 explicitly warns “avoid clobbering company_profile_id, guide_id, etc.” (JSONB merge sensitivity)
`app/api/intelligence/workspaces/[id]/prompts/preview/route.ts`	102, 107	READ — Load company context via `meta?.company_profile_id`
`app/api/intelligence/workspaces/[id]/flags/analyse/route.ts`	124	READ — `meta.company_profile_id`
`app/intelligence/[workspaceId]/page.tsx`	59	READ — `workspace?.domain_metadata?.guide_id`
`lib/intelligence/pipeline.ts`	98	READ — `domainMetadata.company_profile_id`
`scripts/batch-rescore-articles.ts`	157-158	MISSED IN S243 INVENTORY — surfaced at S244 Wave 0.5. READ — `(workspace?.domain_metadata as Record<string, unknown>)?.company_profile_id`. CLI batch rescore tool. Silent-degradation risk post-strip — deferred to follow-on per “Helper-first hybrid” approach below.
`lib/intelligence/guide-generator.ts`	92, 120, 137, 150	NOT IN SCOPE — local `guide_id` variable in payload construction (different scope; not a `domain_metadata` JSONB read).

`relevance_threshold` sites (added at S244 Wave 0.5)

File	Line(s)	What it does
`lib/intelligence/pipeline.ts`	92-95	READ — pipeline behaviour gate; falls back to `DEFAULT_RELEVANCE_THRESHOLD = 0.5` when `domainMetadata.relevance_threshold` is not a valid number.
`components/intelligence/workspace-settings.tsx`	41-42	READ (UI display) — `workspace?.domain_metadata?.relevance_threshold` rendered on settings slider.
`components/intelligence/workspace-settings.tsx`	83	WRITE — `updateMutation.mutate({ relevance_threshold: thresholdValue })`.
`app/api/intelligence/workspaces/[id]/route.ts`	78-126	WRITE — admin PATCH; SI-L5 invariant gated at L90-95 (`role !== 'admin' → 403`); merged into JSONB at L99-126 via fetch-then-spread pattern.
`lib/validation/schemas.ts`	1150-1154	SCHEMA — Zod validator: `z.number().min(0.1).max(1.0).optional()`.
`hooks/intelligence/use-intelligence-workspaces.ts`	17	INTERFACE — `IntelligenceWorkspace.domain_metadata.relevance_threshold?: number`.
`hooks/intelligence/use-intelligence-workspaces.ts`	47	INTERFACE — `IntelligenceWorkspaceUpdateInput.relevance_threshold?: number`.
`__tests__/components/intelligence/workspace-settings.test.tsx`	65, 214	TEST — mocks `domain_metadata.relevance_threshold` in workspace fixture; asserts mutation payload at L214.
`__tests__/api/intelligence/workspaces.test.ts`	426-490 (entire SI-L5 describe block — `it('admin can update…'` at L429, `it('editor cannot…'` at L486, range-validation cases, combined-payload case)	TEST — end-to-end SI-L5 write-path coverage; asserts JSONB merge shape at L469 (`body.domain_metadata.relevance_threshold === 0.7`).

Type-interface drift (CRITICAL — full Shape B promotion resolves)

hooks/intelligence/use-intelligence-workspaces.ts:13-18 declares:

1
domain_metadata: {
2
  company_profile_id: string;
3
  guide_id?: string;
4
  relevance_threshold?: number;
5
};

Post-T2 JSONB strip (T-3), the API response from /api/intelligence/workspaces and /api/intelligence/workspaces/[id] no longer carries any of these JSONB keys. The interface is structurally drifted from the live response shape.

Resolution (ratified S244 Wave 0.5): drop the domain_metadata typed shape from the IntelligenceWorkspace interface entirely; surface company_profile_id, guide_id, and relevance_threshold as typed top-level fields on the API response shape, fed from the intelligence_workspaces satellite via JOIN. The single internal consumer app/intelligence/[workspaceId]/page.tsx:59 is rewritten in the same PR. No external API clients exist to preserve back-compat for — this is a fully internal contract.

Target state — helper-first hybrid (RATIFIED S244 Wave 0.5)

Post-migration, every site above is migrated per the helper-first hybrid approach:

Helper lands first (commit precedes T2 SQL apply): lib/intelligence/workspace-context.ts ships getIntelligenceWorkspaceContext(supabase, workspaceId):

1
// lib/intelligence/workspace-context.ts (signature documented per S244 Wave 0.5 ratification)
2
import type { SupabaseClient } from '@supabase/supabase-js';
3
import type { Database } from '@/supabase/types/database.types';
4

5
export interface IntelligenceWorkspaceContext {
6
  companyProfileId: string | null;
7
  guideId: string | null;
8
  relevanceThreshold: number | null;
9
}
10

11
/**
12
 * Reads the 3 typed columns on intelligence_workspaces via JOIN through workspace_id.
13
 * Returns null on every field when no satellite row exists (defensive — should not occur
14
 * post-T2 for any workspaces.type='intelligence' row).
15
 * Single source of truth for company_profile_id / guide_id / relevance_threshold;
16
 * NO fallback read of workspaces.domain_metadata JSONB.
17
 */
18
export async function getIntelligenceWorkspaceContext(
19
  supabase: SupabaseClient<Database>,
20
  workspaceId: string,
21
): Promise<IntelligenceWorkspaceContext> { /* ... */ }

Hot-path code sweep bundled in the T2 PR — the 5 app/api/intelligence/* route call sites + lib/intelligence/pipeline.ts:92-95, 98 + app/intelligence/[workspaceId]/page.tsx:59 + components/intelligence/workspace-settings.tsx:41-42, 83 + the API response shape + the IntelligenceWorkspace interface in hooks/intelligence/use-intelligence-workspaces.ts:13-18 + the admin PATCH write path at app/api/intelligence/workspaces/[id]/route.ts:78-126 (rewrites the JSONB-merge to a direct typed-column UPDATE on intelligence_workspaces) + the Zod schema position (it stays where it is — Zod still validates the input payload; the storage destination changes). All call sites consume getIntelligenceWorkspaceContext or the direct typed satellite query.
CLI follow-on with TODO(T2-followup) discipline — scripts/batch-rescore-articles.ts:157-158 is left on the JSONB read pattern at T2 merge, marked with a TODO(T2-followup) comment pointing at this T-5 entry. The CLI is run on demand (not in serving paths) and has bounded silent-degradation surface: a rescore launched between T2 apply and the CLI follow-on returns “no profile” and effectively no-ops the company-context filter; recovery is a re-run after the follow-on lands.

Critical risks:

Type-interface drift between T2 SQL apply and consumer rewrite (single PR window). Mitigated by bundling all hot-path consumers in the T2 PR — TypeScript catches the interface change at compile time so no consumer can silently 404.
Test-fixture migration scope. Roughly 6 test files mock the JSONB shape in their workspace fixtures (full list captured under “Risks and mitigations” below); these fixtures must be updated to match the new typed top-level shape in the same PR.

Migration sequencing strategy (ratified):

Step 0 (pre-T2 merge): Land lib/intelligence/workspace-context.ts helper as its own small commit on the T2 PR branch. Verify it builds + types correctly against the existing (pre-migration) schema using stub/feature-flagged behaviour, OR keep the helper internally-untested until step 1 lands (TDD-from-DB).
Step 1 (T2 SQL apply + bundled code sweep, single PR): T2 combined-PR migration adds 3 typed columns + backfills + strips JSONB. In the same PR, all hot-path consumers swap to getIntelligenceWorkspaceContext or direct satellite queries; the IntelligenceWorkspace interface drops the domain_metadata shape; tests are updated to mock the new top-level shape.
Step 2 (follow-on PR, before Phase 1 close): Migrate scripts/batch-rescore-articles.ts:157-158 from the JSONB-read pattern to getIntelligenceWorkspaceContext. Remove the TODO(T2-followup) comment.

Gate: None at spec level — this T-N constrains the migration approach. Step 0 commit precedes the T2 SQL apply within the same PR branch.

Validation:

Feature regression test (UI): open one of the 3 prod intelligence workspaces with company_profile_id (e.g. “MAT Auditing”) in staging post-T2; verify the company profile loads on the workspace page.
Open the 2 guide-bound workspaces; verify the guide loads.
Open the GNews Dedup workspace (NULL on all three); verify it renders without errors (no profile / no guide bound, falls back to DEFAULT_RELEVANCE_THRESHOLD for pipeline scoring).
Admin-PATCH regression (SI-L5): admin user sets relevance_threshold = 0.7 on “MAT Auditing”; verify the typed column updates; verify pipeline now scores against 0.7. Editor user attempts same; verify 403.
Negative-case: grep -rn "domain_metadata.*company_profile_id\|domain_metadata.*guide_id\|domain_metadata.*relevance_threshold" app/ lib/ components/ hooks/ returns 0 hits after the T2 PR merges (CLI scripts/ is the sole expected match until the follow-on PR lands).
Final negative-case (after follow-on PR): the grep above returns 0 hits across app/, lib/, components/, hooks/, scripts/.

T-6 — FK target table existence + CHECK constraint verification (implements S-7)

Current state: Both FK targets exist in production schema:

company_profiles — docs/reference/SCHEMA-QUICK-REFERENCE.md §24 (lines 669-697). Has id uuid PRIMARY KEY DEFAULT gen_random_uuid().
guides — docs/reference/SCHEMA-QUICK-REFERENCE.md §30 “Supporting Tables → guides” (lines 1058-1077). Has id uuid PRIMARY KEY DEFAULT gen_random_uuid().

relevance_threshold has NO FK target — it is a CHECK-constrained numeric setting. The DB-side CHECK clause (relevance_threshold IS NULL OR (relevance_threshold >= 0.1 AND relevance_threshold <= 1.0)) enforces the same [0.1, 1.0] range as the live Zod validator at lib/validation/schemas.ts:1150-1154 (z.number().min(0.1).max(1.0).optional()). The two enforcement layers (Zod upstream, CHECK at storage) are intentionally redundant: Zod rejects invalid payloads at the route handler; CHECK protects against future write paths bypassing Zod (direct SQL admin tooling, migration scripts, etc.).

Live FK target records confirmed via spec-author audit 20/05/2026:

company_profiles.id = '7f50c92d-b3bc-4ae3-befc-2db01b3d67e1' → “Phew Design Limited” (slug phew-design).
guides.id = 'd42b2651-5f71-4ce5-931d-3f0755ad193d' → “MAT Auditing Intelligence Guide” (slug intelligence-mat-auditing).
guides.id = '7d32f790-1e3f-49fd-86b9-2c5fd112d5bc' → “NHS Digital Cyber alerts Intelligence Guide” (slug intelligence-nhs-digital-cyber-alerts).

Target state: No gap-flag required — both FK target tables exist and all 3 referenced UUIDs resolve to live records. The Postgres FK constraint added in T-1 enforces this automatically; the T-2 backfill INSERT will fail with FK violation if any orphaned JSONB-encoded ID exists in workspaces.domain_metadata. Separately, the CHECK constraint added in T-1 enforces the relevance_threshold range — but no row currently carries relevance_threshold in JSONB (0/4 per S244 Wave 0.5 audit) so range-violation surface area is zero at backfill time.

Gate: None.

Validation:

Pre-migration FK verification (run before T2 applies to production): SELECT (w.domain_metadata->>'company_profile_id')::uuid AS profile_id, (w.domain_metadata->>'guide_id')::uuid AS guide_id FROM public.workspaces w WHERE w.type='intelligence'; cross-referenced against SELECT id FROM public.company_profiles + SELECT id FROM public.guides — every non-NULL JSONB-encoded ID must appear in the target table. Re-verified 20/05/2026; 3/3 + 2/2 referenced records resolve.
Pre-migration CHECK verification (S244 Wave 0.5): SELECT count(*) FROM public.workspaces WHERE type='intelligence' AND domain_metadata ? 'relevance_threshold' AND ((domain_metadata->>'relevance_threshold')::real < 0.1 OR (domain_metadata->>'relevance_threshold')::real > 1.0) returns 0 (no current row violates the [0.1, 1.0] range — verified 20/05/2026 via 0/4 count). If a future write between this spec landing and T2 apply violates the range (would require bypassing Zod), the backfill INSERT fails on CHECK violation — drafter must scrub the offending row.
Post-migration: Postgres FK + CHECK constraints guarantee no orphan or out-of-range typed-column writes.

T-7 — Future intelligence ALTER columns (implements S-8)

Current state: No additional intelligence-specific columns in scope for this spec beyond the three promoted by S-1 / T-1. v1.1+ intelligence feature work (e.g. sector-filter overrides, RSS-source defaults, scoped-search keywords) lives in lib/intelligence/ and app/api/intelligence/ but does not yet have feature-spec backing. (Note: relevance_threshold is NOT a future column — it is promoted to typed column in this spec per S244 Wave 0.5 ratification; previous drafts that listed it under T-7 were superseded.)

Target state: v1.1+ intelligence feature spec authors add columns via ALTER TABLE public.intelligence_workspaces ADD COLUMN ... migrations, NOT by amending the Phase 1 / S243 + S244 migration this spec governs.

Pattern (for future authors):

1
ALTER TABLE public.intelligence_workspaces
2
  ADD COLUMN <new_col> <type> NULL;  -- nullable unless explicit reason otherwise
3
-- followed by feature-spec-side backfill + read-path updates per RWS S-7 ALTER discipline

Gate: None at this spec level. v1.1+ intelligence feature spec defines its own gates.

Validation:

Review discipline: any v1.1+ migration touching intelligence_workspaces must NOT use DROP TABLE + CREATE TABLE; must use ALTER TABLE ... ADD COLUMN.
The RWS T-7 v1.1 candidate (CI guard linting for CREATE TABLE public.<reserved-seat-name> outside the v1 reserved-seats migration file) would catch this regression class.

Cross-doc cross-references

Doc	What it references
`docs/specs/id-31-canonical-pipeline-implementation-plan/PLAN.md` §4.2 sub-task 8	Consumes this TECH.md as the migration drafting reference for the intelligence Shape B promotion.
`docs/specs/reserved-workspace-seats/{PRODUCT,TECH}.md` S-1..S-8 + T-1..T-8	Parent spec — governs the seat shape this spec extends with 3 typed columns.
`docs/specs/rls-pattern/{PRODUCT,TECH}.md` P-1, P-2 + T-1, T-2	Substrate — auto-RLS event trigger + grants helper (APPLIED-S239) leveraged by S-4 + S-5.
`docs/plans/phase-0-investigation/architecture/07-collapse-list.md` §3.4	Procurement Shape B precedent — pattern mirrored for intelligence.
`docs/plans/phase-0-investigation/architecture/04-workspace-types.md` §3.2 + §4.2	Application_types vocabulary (`intelligence` is one of 6 baseline `core` rows) + satellite pattern.
`docs/reference/SCHEMA-QUICK-REFERENCE.md` §24 (`company_profiles`) + §30 (“guides”)	FK target table shapes — confirmed both exist.
Future v1.1+ intelligence feature spec	Receives the ALTER discipline for adding intelligence-specific columns (S-8 + T-7).

Risks and mitigations

Risk	Severity	Mitigation
JSONB strip leaves dual-source ambiguity if a read path is not updated. Existing intelligence surface degrades to “no profile / no guide / no threshold” silently between T2 apply and code-sweep.	MEDIUM	Helper-first hybrid (T-5 ratified): land `lib/intelligence/workspace-context.ts` helper as a pre-T2 commit on the same PR branch + bundle all hot-path consumer rewrites in the T2 PR. CLI follow-on (`scripts/batch-rescore-articles.ts:157-158`) marked with `TODO(T2-followup)` and explicitly scoped. Feature regression tests (T-5 validation) catch degradation in staging.
Type-interface drift in `hooks/intelligence/use-intelligence-workspaces.ts:13-18` post-T2. The `IntelligenceWorkspace.domain_metadata: { company_profile_id; guide_id?; relevance_threshold? }` declaration no longer matches the API response shape after JSONB strip (T-3).	MEDIUM	Full Shape B promotion = update the interface in the SAME PR as T2: drop the `domain_metadata` typed shape; surface `company_profile_id`, `guide_id`, `relevance_threshold` as typed top-level fields on the API response, fed by JOIN through the satellite table. TypeScript compile-time enforcement catches any unmigrated consumer. Single internal consumer (`app/intelligence/[workspaceId]/page.tsx:59`) rewritten in same PR; no external API clients to preserve back-compat for.
Test fixture migration scope. Roughly 6 test files mock the JSONB `domain_metadata` shape in their workspace fixtures (notably `__tests__/components/intelligence/workspace-settings.test.tsx:65, 214` + `__tests__/api/intelligence/workspaces.test.ts:426-490` SI-L5 block, plus the broader `__tests__/api/intelligence/` + `__tests__/lib/intelligence/` + `__tests__/app/api/intelligence/` directories).	LOW	Fixture updates land in the SAME PR as T2 + the consumer code sweep. Test failures pre-merge surface any missed fixture. CI quality-test job (`quality-test` matrix in `ci.yml`) catches mismatches before merge.
Backfill assertion brittleness. If a new prod intelligence workspace is created OR an admin configures `relevance_threshold` between this spec landing and T2 applying, the 4/3/2/0 count assertion fires.	LOW	Intentional — silent count drift would defeat audit-trail purpose. Drafter re-verifies counts (now 4/3/2/0 per S244 Wave 0.5) immediately before T2 production-apply; adjusts assertion if a legitimate config landed.
FK target deletion between spec verification and T2 production-apply. If a `company_profile` or `guide` referenced by a current intelligence workspace is deleted in production before T2 applies, the backfill INSERT fails.	LOW	Pre-migration verification query (T-6 validation step) checks FK target resolution immediately before T2 production-apply. If an orphan is found, the drafter scrubs the offending JSONB key from `workspaces.domain_metadata` (or sets the typed column NULL via explicit override in the backfill SELECT) before re-applying.
`relevance_threshold` CHECK violation at backfill (theoretical). If a non-Zod-validated write puts an out-of-range value into JSONB between this spec landing and T2 apply, the backfill INSERT fails on the CHECK constraint.	LOW	Pre-migration CHECK verification query (T-6 validation step) confirms 0 rows out-of-range (verified 0/4 at 20/05/2026 — none carry the key). Zod gating on `app/api/intelligence/workspaces/[id]/route.ts` is the upstream defence.
`workspaces.type` vs `workspaces.application_type_id` predicate ordering. The T2 combined-PR swaps the discriminator column in the same transaction; the backfill query’s predicate must match the state of `workspaces` at the point the INSERT runs.	LOW	T-2 + T-3 migration body explicitly notes “ADJUST predicate per T2 sub-task ordering”; the T2 drafter resolves at SQL-drafting time. Both predicates are equivalent (one workspace row per name); pick whichever is current.
Silent supabase-call failures in read-path update. Per CLAUDE.md “Silent failures in Supabase calls” gotcha — replacement reads from `intelligence_workspaces` JOIN (and the new `getIntelligenceWorkspaceContext` helper internals) must use `sb()` / `tryQuery()` from `@/lib/supabase/safe`, not raw client calls.	LOW	Pattern enforced by ESLint rules `local/no-unchecked-supabase-error` + code review. Helper signature documented in T-5 mandates a `SupabaseClient<Database>` injection so the caller controls the safety wrapper.

Testing and validation

This section maps each PRODUCT.md S-N invariant to its concrete test or verification step.

Invariant	Verification
S-1 (Three typed columns exist, nullable; two FK + one CHECK-constrained)	`information_schema.columns` query (T-1 Validation) returns 3 rows; `pg_constraint` query confirms 2 FK + 1 CHECK constraints (T-1 Validation).
S-2 (Backfill preserves 4 existing rows; 3/2/0 non-NULL counts for company_profile_id / guide_id / relevance_threshold)	`DO $$` assertion in T-2 migration body fires `RAISE EXCEPTION` if counts mismatch the 4/3/2/0 snapshot. Post-apply: cross-check via `mcp__supabase__execute_sql` query against the 4-row table in T-2 Target state.
S-3 (Three JSONB keys stripped post-backfill; domain_metadata = `{}` for intel rows)	`DO $$` assertion in T-3 migration body checks all 3 keys absent. Post-apply: `count` query returns 0 (T-3 Validation); spot-check confirms `domain_metadata = '{}'` on all 4 intel rows.
S-4 (RLS enabled)	`pg_class.relrowsecurity` query returns `true` (T-4 Validation).
S-5 (Per-role grants applied)	`information_schema.role_table_grants` query returns expected 3-role pattern (T-4 Validation).
S-6 (Existing intelligence surface continuity, including admin SI-L5 PATCH)	Feature regression test in staging — open each of the 4 prod intelligence workspaces post-T2; verify expected company-profile / guide context loads + admin can set relevance_threshold (editor cannot) per T-5 Validation. `grep` sweep across `app/`/`lib/`/`components/`/`hooks/` confirms no remaining JSONB reads of the 3 stripped keys post-T2; full-tree sweep (including `scripts/`) returns 0 after the CLI follow-on PR lands.
S-7 (FK targets resolve + CHECK range matches Zod)	Pre-migration FK verification query (T-6 Validation) cross-references JSONB-encoded IDs against live target tables; Postgres FK constraint enforces at INSERT time. Pre-migration CHECK verification query confirms no out-of-range `relevance_threshold` values exist in JSONB (verified 0/4 at 20/05/2026).
S-8 (Future columns via ALTER)	Review discipline; no automated test required at this spec level.

Migration apply discipline (per CLAUDE.md “Supabase & Schema” gotchas):

DDL via CLI only — supabase migration new + supabase db push, never mcp__supabase__apply_migration. The T2 combined-PR migration file is authored locally, applied first to staging (turayklvaunphgbgscat) via supabase db push --linked turayklvaunphgbgscat, verified per the assertion blocks above, then applied to production via supabase db push --linked rovrymhhffssilaftdwd.
CLI in sandbox: run with dangerouslyDisableSandbox: true + POSTGRES_PASSWORD env var per CLAUDE.md Supabase gotcha.
Pre-push verification: cat supabase/.temp/project-ref before each push to confirm target.
Post-apply: supabase gen types typescript --project-id rovrymhhffssilaftdwd --schema public > supabase/types/database.types.ts to regenerate types; the typed columns appear as intelligence_workspaces.company_profile_id + .guide_id properties on the row type.

Source-doc supersession

Not applicable — NEW spec. No predecessor. Heritage substrate:

docs/plans/phase-0-investigation/pre-s244-project-feedback.md Item 12 + Item 15 (S243 interim ratifications — 20/05/2026).
Live-DB audit (20/05/2026, spec-author verification re-confirming Item 12 counts).
docs/specs/reserved-workspace-seats/{PRODUCT,TECH}.md (S240 NEW spec pair) — seat-shape parent.
docs/plans/phase-0-investigation/architecture/07-collapse-list.md §3.4 (S239 Wave 1 canonical) — procurement Shape B precedent.

Heritage docs feeding this sub-doc

Per docs/specs/core-docs-pathway-assessment/architecture-sub-doc-construction-guide.md §4.1 — three-tier status taxonomy.

Doc	Date	Status	Useful for
`docs/plans/phase-0-investigation/pre-s244-project-feedback.md` Item 12 + Item 15	20/05/2026 (S243 interim)	`[CURRENT-CANONICAL]` — ratification source for this spec	Audit data (4/3/2 row counts) + Phase 1 spec gate (Item 15).
`docs/specs/reserved-workspace-seats/{PRODUCT,TECH}.md`	15/05/2026 (S240 NEW)	`[CURRENT-CANONICAL]` — parent spec	Seat-shape invariants inherited (S-1..S-8 → this spec extends with S-1..S-8 specific to intelligence).
`docs/specs/rls-pattern/{PRODUCT,TECH}.md`	14/05/2026 (S239 NEW)	`[CURRENT-CANONICAL]` — RLS + grants substrate	Auto-trigger + grants helper (APPLIED-S239) leveraged in S-4 + S-5.
`docs/plans/phase-0-investigation/architecture/07-collapse-list.md` §3.4	14/05/2026 (S239 Wave 1)	`[CURRENT-CANONICAL]` — procurement Shape B precedent	Pattern source for typed-column promotion + JSONB strip discipline.
`docs/plans/phase-0-investigation/architecture/04-workspace-types.md` §3.2 + §4.2	14-15/05/2026 (S239 Wave 2 + S240)	`[CURRENT-CANONICAL]` — application_types vocabulary + satellite pattern	Substrate for `intelligence` as one of 6 baseline `core`-provenance app types + the upfront-seats decision.
`docs/reference/SCHEMA-QUICK-REFERENCE.md` §24 + §30 (guides)	Ongoing	`[CURRENT-CANONICAL]` for FK target tables	FK target table shape verification (T-6).

End of TECH spec. Numbered invariants in ./PRODUCT.md.

Intelligence Workspaces — TECH

Intelligence Workspaces — TECH

How to use this doc

Source-of-truth pointers

Audience

T-1 — Three typed columns on intelligence_workspaces satellite (implements S-1)

T-2 — Backfill from JSONB into typed columns (implements S-2)

T-3 — JSONB strip post-backfill (implements S-3)

T-4 — RLS auto-enable + per-role grants (implements S-4 + S-5)

T-5 — Existing intelligence surface continuity (implements S-6)

company_profile_id + guide_id sites

relevance_threshold sites (added at S244 Wave 0.5)

Type-interface drift (CRITICAL — full Shape B promotion resolves)

Target state — helper-first hybrid (RATIFIED S244 Wave 0.5)

T-6 — FK target table existence + CHECK constraint verification (implements S-7)

T-7 — Future intelligence ALTER columns (implements S-8)

Cross-doc cross-references

Risks and mitigations

Testing and validation

Source-doc supersession

Heritage docs feeding this sub-doc

T-1 — Three typed columns on `intelligence_workspaces` satellite (implements S-1)

`company_profile_id` + `guide_id` sites

`relevance_threshold` sites (added at S244 Wave 0.5)