Reserved Workspace Seats — TECH

Reserved Workspace Seats — TECH

Status: [CURRENT-CANONICAL] — NEW-S240. Companion to PRODUCT.md. Per-invariant implementation references grounded in current code + target migration shape; gates noted inline.

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 v1 reserved-seats migration applies.
• Gate: any STILL-OPEN dependency.
• Validation: how the invariant is verified (CI guard / parity test / manual).

Source-of-truth pointers

• ./PRODUCT.md — numbered invariants S-1..S-8.
• docs/plans/phase-0-investigation/architecture/04-workspace-types.md §4.1 + §4.2 + §4.3 — pattern + extensibility procedure.
• docs/specs/rls-pattern/TECH.md T-1 (auto-RLS event trigger) + T-2 (grants helper) + T-3 (combined migration).
• supabase/migrations/20260514150238_enable_rls_auto_event_trigger_and_grants_pattern.sql — RLS-PATTERN combined migration (APPLY GATED ON LIAM REVIEW); reserved-seats migration ordering depends on this landing first.
• Existing satellite table procurement_workspaces (post-rename from bid_workspaces per 0.9-decision-graph.md §11.3 row 4) — pattern precedent for FK shape + RLS pattern.

Audience

Engineers writing the reserved-seats migration; reviewers verifying migration compliance; feature-spec authors planning their ALTER TABLE ADD COLUMN patterns.

---

T-1 — Five reserved seats exist at v1 apply time (implements S-1)

Current state: Greenfield. None of the 5 seat tables exist in either staging (turayklvaunphgbgscat) or production (rovrymhhffssilaftdwd) schemas as of S240.

Target state: Single migration creates all 5 seats in one transaction. Migration filename convention: supabase/migrations/<timestamp>_create_reserved_workspace_seats.sql per the Supabase migration timestamp convention used across the project.

Migration body shape:

BEGIN;

CREATE TABLE public.intelligence_workspaces (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  workspace_id uuid NOT NULL UNIQUE REFERENCES workspaces(id) ON DELETE CASCADE,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE public.sales_proposal_workspaces (
  -- identical shape
);

CREATE TABLE public.product_guide_workspaces (
  -- identical shape
);

CREATE TABLE public.competitor_research_workspaces (
  -- identical shape
);

CREATE TABLE public.training_onboarding_workspaces (
  -- identical shape
);

-- Apply grants + RLS policies (see T-4, T-5, T-6)
-- ...

COMMIT;

Lands in: supabase/migrations/<timestamp>_create_reserved_workspace_seats.sql (NEW, draft pending — S241+ deliverable).

Gate: RLS-PATTERN combined migration (20260514150238_*.sql) MUST apply first so the rls_auto_enable() event trigger + grant_standard_public_table_access(regclass) helper are present when this migration runs. If migration ordering inverts, seats need explicit ALTER TABLE ... ENABLE ROW LEVEL SECURITY and explicit per-role grants in lieu of the helper.

Validation:

• Apply migration to staging; assert SELECT count(*) FROM information_schema.tables WHERE table_schema='public' AND table_name IN ('intelligence_workspaces','sales_proposal_workspaces','product_guide_workspaces','competitor_research_workspaces','training_onboarding_workspaces') returns 5.
• Single-transaction property: drop any one mid-migration and confirm the others also do not exist (proves the BEGIN/COMMIT envelope holds).
• Schema-parity workflow (.github/workflows/schema-parity.yml) flags any prod ↔ staging drift once both apply.

---

T-2 — Primary key column (implements S-2)

Current state: Greenfield. Pattern precedent: procurement_workspaces.id uuid PRIMARY KEY DEFAULT gen_random_uuid() (verify via supabase/types/database.types.ts once seat-rename migration applies — current types still carry bid_workspaces).

Target state: Each seat table carries id uuid PRIMARY KEY DEFAULT gen_random_uuid(). Naming + type + default match the platform-standard pattern used across workspaces, q_a_pairs, and the bid satellite.

Gate: None — DDL only.

Validation:

• SELECT column_name, data_type, column_default FROM information_schema.columns WHERE table_name = '<seat>' AND column_name = 'id' returns id | uuid | gen_random_uuid().

---

T-3 — Foreign key to workspaces.id (implements S-3)

Current state: Greenfield. Pattern precedent: procurement_workspaces.workspace_id carries the same shape; verifiable via pg_constraint once that rename lands.

Target state: Each seat carries workspace_id uuid NOT NULL UNIQUE REFERENCES workspaces(id) ON DELETE CASCADE. The UNIQUE constraint enforces the 1:1 cardinality per S-3 / Q-OQR1-113-A.

Edge cases:

• If a workspace deletes, the satellite row cascades (no orphan satellites possible).
• If the satellite row is deleted manually, the workspace remains intact (no reverse cascade).

Gate: None — DDL only.

Validation:

• SELECT * FROM information_schema.table_constraints WHERE table_name = '<seat>' AND constraint_type IN ('FOREIGN KEY', 'UNIQUE') returns expected rows.
• Insert a workspace, insert its satellite, delete the workspace, assert satellite row count is 0.

---

T-4 — RLS auto-enable via event trigger (implements S-4)

Current state: Greenfield seat tables — auto-RLS event trigger from RLS-PATTERN P-1/T-1 not yet applied either.

Target state: Each seat table has pg_class.relrowsecurity = true immediately after CREATE TABLE returns. Mechanism: rls_auto_enable() event trigger fires on the CREATE TABLE command tag for each seat.

Belt-and-braces safeguard: Per S-4 fall-back clause, the seat migration also emits explicit ALTER TABLE public.<seat> ENABLE ROW LEVEL SECURITY immediately after each CREATE TABLE to handle the corner case where the seat migration runs before the RLS-PATTERN combined migration applies. The explicit ALTER is idempotent against the event-trigger enable.

Pattern:

CREATE TABLE public.intelligence_workspaces (...);
ALTER TABLE public.intelligence_workspaces ENABLE ROW LEVEL SECURITY;
-- (idempotent — event trigger would have enabled it already if RLS-PATTERN migration applied first)

Gate: None — belt-and-braces pattern works in both orderings.

Validation:

• Apply migration; SELECT relname, relrowsecurity FROM pg_class WHERE relname IN ('intelligence_workspaces', ...) returns true for all 5.

---

T-5 — Per-tenant RLS policies (implements S-5)

Current state: Greenfield. Pattern precedent: existing satellite tables (notably the post-rename procurement_workspaces) install RLS policies that delegate to the parent workspaces row via an EXISTS subquery.

Target state: Each seat carries 4 RLS policies — select, insert, update, delete — that JOIN through to workspaces via the workspace_id FK to inherit the parent’s tenant scope.

Policy shape (SELECT example):

CREATE POLICY intelligence_workspaces_select
ON public.intelligence_workspaces
FOR SELECT
USING (
  EXISTS (
    SELECT 1
    FROM workspaces w
    WHERE w.id = intelligence_workspaces.workspace_id
      AND (
        -- tenant access check delegated to workspaces-level pattern;
        -- exact predicate matches workspaces' own RLS USING clause
      )
  )
);

The exact workspaces-level access predicate (role-based via get_user_role() per CLAUDE.md) is canonical in the workspaces table’s own RLS policies; this seat-level policy delegates rather than duplicating.

Lands in: Same migration as T-1 (5 seats × 4 policies = 20 policy statements).

Gate: Requires workspaces table RLS policies stable — confirmed in current production schema.

Validation:

• Per seat: connect as authenticated role with tenant A; insert workspace + satellite for tenant A; assert visible. Switch to tenant B; assert satellite not visible.
• Migration test fixture covers this for the procurement seat already (post-rename); copy pattern across 5 reserved seats.

---

T-6 — Per-role grants via RLS-PATTERN T-2 helper (implements S-6)

Current state: Greenfield seats; RLS-PATTERN T-2 grants helper greenfield until the combined migration applies.

Target state: Migration calls SELECT grant_standard_public_table_access('public.<seat>'::regclass) for each of the 5 seats immediately after CREATE TABLE.

Pattern:

CREATE TABLE public.intelligence_workspaces (...);
ALTER TABLE public.intelligence_workspaces ENABLE ROW LEVEL SECURITY;  -- T-4
SELECT grant_standard_public_table_access('public.intelligence_workspaces'::regclass);  -- T-6
-- per-seat RLS policies follow (T-5)

Fall-back if RLS-PATTERN migration not applied: Emit explicit per-role grants block (3 GRANT statements per seat) in lieu of the helper call. The fall-back is verbose (15 GRANT statements for 5 seats) but works without depending on the RLS-PATTERN helper.

Gate: Same as T-1 — RLS-PATTERN combined migration ideally applies first. Fall-back covers the inverted-ordering case.

Validation:

• Post-apply, SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE table_name = '<seat>' returns the expected 3-role pattern: anon SELECT, authenticated SELECT/INSERT/UPDATE/DELETE, service_role SELECT/INSERT/UPDATE/DELETE.

---

T-7 — Per-app columns via feature-spec ALTER discipline (implements S-7)

Current state: Pattern enforced through review discipline rather than CI guard at v1. Feature specs authoring reserved-seat columns must use ALTER TABLE public.<seat> ADD COLUMN ... migrations, not CREATE TABLE.

Target state: Same pattern. The reserved-seat migration creates the seat shells; feature-spec migrations evolve column lists. A CI guard checking that no migration re-creates an existing reserved seat is a v1.1 candidate (not blocking — code review catches it).

Gate: None — review discipline.

Validation:

• Inspection: review post-reserved-seat migrations to confirm they ALTER against seats rather than DROP TABLE + CREATE TABLE.
• v1.1 candidate: extend migration-revoke-guard.yml (or a sibling workflow) to lint for CREATE TABLE public.<reserved-seat-name> outside the v1 reserved-seats migration file.

---

T-8 — Naming convention frozen (implements S-8)

Current state: Names are documented in architecture/04-workspace-types.md §3 (Q-OQR1-03 vocabulary table) and PRODUCT.md S-1.

Target state: Migration filename + table names match PRODUCT.md S-1 verbatim. Any rename requires a separate ratification cycle (precedent: bid_workspaces → procurement_workspaces per 0.9-decision-graph.md §11.3 row 4 — required S235 ratification + Q-OQR1-16 combined-PR scope).

Gate: None — names are settled.

Validation:

• Inspection: migration file diff vs PRODUCT.md S-1 name list.

---

Cross-doc cross-references

Doc	What it references
docs/plans/phase-0-investigation/architecture/04-workspace-types.md §4.2	Forward-reference to this PRODUCT + TECH pair (per S240 user decision — seat schema home).
docs/specs/rls-pattern/{PRODUCT,TECH}.md P-1/P-2/T-1/T-2	Dependency — RLS-PATTERN combined migration provides the auto-trigger + grants helper that this spec leverages.
Future per-application feature specs (docs/specs/<application>/...)	Each application’s feature spec carries the ALTER TABLE public.<seat> ADD COLUMN ... migrations per S-7 discipline.
docs/reference/SCHEMA-QUICK-REFERENCE.md §workspaces	Post-apply update — add 5 reserved-seat rows to the workspaces-related schema sections.

Source-doc supersession

Not applicable — NEW spec. No predecessor. Heritage substrate documented in 00-synthesis-v2.md §3.4 (application_types vocabulary ratification) + architecture/04-workspace-types.md §4 (pattern ratification).

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/architecture/04-workspace-types.md §4 (full)	14-15/05/2026 (S239 + S240 ratifications)	[CURRENT-CANONICAL] for the satellite pattern + S240 upfront-seats decision.	Forward-ref source for S-1..S-8 framing.
docs/plans/phase-0-investigation/10-feedback-investigation-findings/00-synthesis-v2.md §3.4 + §3.9	14/05/2026 (S236 refreshes)	[CURRENT-CANONICAL] for Q-OQR1-03 + Q-OQR1-04 ratifications.	Substrate for S-1 (5 application_types names) + S-7 (typed-columns over JSONB).
docs/specs/rls-pattern/{PRODUCT,TECH}.md	14/05/2026 (S239 NEW)	[CURRENT-CANONICAL] for the RLS + grants pattern this spec leverages.	Substrate for S-4 + S-6 (auto-trigger + grants helper).
phase-b-prerequisite-1-onthology-pipeline.md §5 closure (Q-OQR1-113-A)	14/05/2026 (S235 ratifications)	[CURRENT-CANONICAL] for the satellite-per-application_type ratification.	Substrate for S-3 cardinality + S-8 naming convention.

---

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