FORQA CTO Plan · 2026-05-10

Multi-Tenant SaaS Implementation Plan

CTO directive: do not onboard more than one external customer company into the same Supabase database until tenant isolation is implemented, tested, and owned.

Date: 2026-05-10

Prepared for: Jack + Mark

Lane: FORQA CTO / implementation planning

Scope: Planning/docs only. No code, repo, database, Supabase config, deploy, credential, or production data changes were made.

Primary input: Jack_OS/RESEARCH/FORQA_Multi_Tenant_SaaS_CTO_Blocker_Analysis_2026-05-10.md

0. CTO directive

FORQA should not onboard more than one external customer company into the same Supabase database until tenant isolation is implemented, tested, and owned.

The implementation path should be:

  1. Protect beta learning now: keep existing beta/design-partner usage single-tenant or isolated per customer environment.
  2. Build tenancy foundation deliberately: tenant tables, memberships, tenant-scoped schema, tenant-aware APIs, RLS, integrations, tests, and release gates.
  3. Only then move to shared multi-tenant SaaS: after cross-tenant negative tests pass in CI/staging and production smoke tests prove auth/tenant boundaries.

Recommended architecture: hybrid bridge.

1. Evidence paths inspected

Jack OS / lane docs

Local read-only repo cache

Key evidence summary:

2. Non-negotiable principles

  1. Fail closed without tenant context. Any endpoint that returns or mutates tenant-owned data must fail if no tenant can be resolved and authorized.
  2. Tenant boundary exists in the database, not just the frontend. UI filters are convenience only, not security.
  3. Service role must not be a casual read path. If service-role clients remain, they must sit behind mandatory tenant-scoped helper functions and tests.
  4. External IDs are tenant-local. HubSpot/Sage IDs, names, slugs, pipeline IDs, stage IDs, preset names, and lock names can collide across tenants.
  5. Every sync is tenant-scoped. Source credentials, target pipelines, locks, logs, truncation/replacement, and forecast snapshot creation must all include tenant identity.
  6. Cross-tenant tests are release gates. Multi-tenant readiness is not a design doc; it is a passing test suite.
  7. Sage remains a roadmap/design input until implemented. The current inspected repo appears HubSpot-line-item-led. Future Sage actuals must enter the tenant model from day one.

3. Target tenant model

3.1 Core objects

create table tenants (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  slug text not null unique,
  status text not null default 'active'
    check (status in ('trial', 'active', 'suspended', 'churned', 'deleted')),
  plan text,
  primary_contact_email text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table tenant_memberships (
  tenant_id uuid not null references tenants(id) on delete cascade,
  user_id uuid not null references app_users(id) on delete cascade,
  role text not null
    check (role in ('owner', 'admin', 'finance', 'sales_manager', 'rep', 'viewer')),
  status text not null default 'active'
    check (status in ('invited', 'active', 'disabled')),
  invited_by uuid references app_users(id),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  primary key (tenant_id, user_id)
);

3.2 User model

Keep app_users as the identity profile table, but make authorization tenant-specific.

Current app_users.role should be treated as legacy/global only. It can remain temporarily for migration compatibility, but authorization should move to tenant_memberships.role.

Recommended future fields:

alter table app_users
  add column if not exists global_role text default 'user'
    check (global_role in ('user', 'support', 'platform_admin')),
  add column if not exists disabled_at timestamptz;

Only platform_admin should access cross-tenant admin tooling, and those actions must be explicitly audited.

3.3 Tenant-owned tables

Add tenant_id to all business/customer-owned tables:

3.4 External ID uniqueness

Replace global unique constraints with tenant-scoped constraints. Examples:

-- unsafe in shared SaaS
unique (hubspot_id)
unique (hubspot_deal_id)
unique (hubspot_line_item_id)
unique (name)

-- safe pattern
unique (tenant_id, hubspot_id)
unique (tenant_id, hubspot_deal_id)
unique (tenant_id, hubspot_line_item_id)
unique (tenant_id, name)

Where uniqueness should be optional because external IDs can be null:

create unique index deals_tenant_hubspot_deal_id_uq
on deals (tenant_id, hubspot_deal_id)
where hubspot_deal_id is not null;

3.5 Tenant-aware foreign keys

Use simple tenant_id columns plus normal FKs first. Where corruption risk is high, add composite safeguards.

Example for deal_stages belonging to a pipeline within the same tenant:

alter table pipelines add constraint pipelines_tenant_id_id_uq unique (tenant_id, id);

alter table deal_stages
  add constraint deal_stages_pipeline_same_tenant_fk
  foreign key (tenant_id, pipeline_id)
  references pipelines (tenant_id, id);

This prevents accidentally linking Tenant A stage to Tenant B pipeline.

4. Target auth and authorization design

4.1 Request flow

Every tenant-owned Edge Function should follow this sequence:

  1. Validate bearer token with current Entra/Supabase auth mechanism.
  2. Ensure/update app_users record.
  3. Resolve requested tenant from one of:

- X-FORQA-Tenant-ID header,

- route parameter /tenants/:tenantId/...,

- subdomain mapping,

- user default tenant when and only when user has exactly one active membership.

  1. Verify active membership in tenant_memberships.
  2. Check capability for action.
  3. Execute query through tenant-scoped wrapper.
  4. Write audit event for writes/admin/sync/integration actions.

Suggested helper signature:

type Capability =
  | 'forecast:read'
  | 'budget:read'
  | 'budget:write'
  | 'filters:read'
  | 'presets:write'
  | 'settings:write'
  | 'sync:run'
  | 'sync:read'
  | 'integrations:manage'
  | 'users:manage'
  | 'tenant:admin';

async function requireTenantContext(req, supabase, capability): Promise<{
  userId: string;
  tenantId: string;
  role: TenantRole;
}>;

4.2 Role/capability matrix

CapabilityOwnerAdminFinanceSales managerRepViewer
Forecast dashboard readyesyesyesyeslimited/optionalyes
Filter options readyesyesyesyeslimited/optionalyes
Deal insights readyesyesyesyesown/team optionalyes
Budget readyesyesyesyesno/optionalyes/optional
Budget writeyesyesyesnonono
Stage probability/settings writeyesyesno/optionalno/optionalnono
Filter preset writeyesyesyesyesyesno/optional
Manual sync runyesyesno/optionalnonono
Integration credential manageyesyesnononono
Users/invites manageyesyesnononono
Tenant billing/planyesno/optionalnononono

Mark decision required: decide whether reps should see all tenant commercial data or only own/team data. If own/team data is required, the schema must support ownership/manager hierarchy and all forecast aggregate queries become more complex.

4.3 Tenant selection UX

Recommended initial UX:

Avoid relying only on email domain for authorization. Domains are useful for invite/onboarding defaults but not for secure tenant resolution.

5. RLS and database enforcement

Use RLS for tenant-owned tables where possible, especially read paths. Example:

create or replace function current_user_tenant_ids()
returns setof uuid
language sql
stable
security definer
as $$
  select tm.tenant_id
  from tenant_memberships tm
  join app_users au on au.id = tm.user_id
  where tm.status = 'active'
    and au.auth_uid = auth.uid();
$$;

create policy "tenant members can read forecast"
on forecast_phased_revenue
for select
to authenticated
using (tenant_id in (select current_user_tenant_ids()));

Important: the exact identity mapping depends on whether FORQA keeps Entra-token-only auth or moves to Supabase Auth JWTs. If auth.uid() is not available for Entra identities, use an explicit mapping claim or keep RLS for direct Supabase clients and enforce tenant membership in Edge Functions until auth is unified.

5.2 Service role rules

Because getServiceRoleClient() bypasses RLS, service-role operations must be constrained by code structure:

Suggested wrapper:

function forTenant(supabase, tenantId: string) {
  return {
    forecastRows: () => supabase.from('forecast_phased_revenue').select('*').eq('tenant_id', tenantId),
    deals: () => supabase.from('deals').select('*').eq('tenant_id', tenantId),
    budgets: () => supabase.from('budget_targets').select('*').eq('tenant_id', tenantId),
  };
}

5.3 Tables that may remain global

Very few tables should be global:

Lookup tables like pipelines, owners, service_types, regions, and entities should be tenant-owned because their values are sourced from customer CRM/accounting systems and can collide or differ by customer.

6. Supabase environment strategy

6.1 Environments

EnvironmentPurposeDataAuthTenancy expectation
Localdeveloper workseeded syntheticrelaxed only where necessarydisposable two-tenant fixtures
Devintegration branch testingsyntheticauth enabled if practicalmigrations/tests validate tenant model
Stagingrelease candidateseeded/anonymised realisticproduction-likemandatory two+ tenant isolation tests
Productionlive customersreal datastrictno shared external data without gates

6.2 Project strategy

Recommended bridge:

6.3 Deployment requirements

Before multi-tenant production claims:

7. Per-tenant integrations

7.1 Integration tables

create table tenant_integrations (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references tenants(id) on delete cascade,
  provider text not null check (provider in ('hubspot', 'sage')),
  status text not null default 'not_connected'
    check (status in ('not_connected', 'connected', 'error', 'paused', 'revoked')),
  display_name text,
  config jsonb not null default '{}'::jsonb,
  last_successful_sync_at timestamptz,
  last_failed_sync_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (tenant_id, provider, display_name)
);

create table integration_credentials (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references tenants(id) on delete cascade,
  integration_id uuid not null references tenant_integrations(id) on delete cascade,
  provider text not null,
  secret_ref text not null,
  status text not null default 'active'
    check (status in ('active', 'rotating', 'revoked')),
  rotated_at timestamptz,
  created_at timestamptz not null default now()
);

Store secret references, not raw credentials, in Postgres unless Mark explicitly chooses a vetted encrypted storage pattern.

7.2 HubSpot sync changes

Current risk: global TARGET_PIPELINES and global sync writes.

Required changes:

7.3 Sage future-proofing

Even before Sage implementation, reserve tenant-aware patterns:

Mark must decide whether Sage is actuals-only read, reconciliation support, or write-back. Write-back materially increases permissions, audit, and rollback complexity.

8. Migration path from current beta

Phase 0 — Decision/freeze (1-2 days)

Goal: prevent unsafe data co-mingling while implementation starts.

Tasks:

Acceptance criteria:

Phase 1 — Tenancy foundation (sprint 1)

Goal: introduce tenants/memberships without breaking existing app.

Tasks:

Acceptance criteria:

Phase 2 — Add nullable tenant_id and API scoping (sprint 1-2)

Goal: tenant scope every read/write while preserving migration safety.

Tasks:

Acceptance criteria:

Phase 3 — Constraints, RLS, and fail-closed enforcement (sprint 2-3)

Goal: convert soft tenant scoping into hard guarantees.

Tasks:

Acceptance criteria:

Phase 4 — Tenant-scoped integrations and sync (sprint 3-4)

Goal: make HubSpot/Sage integration architecture SaaS-safe.

Tasks:

Acceptance criteria:

Phase 5 — CI, staging, release gates (sprint 4)

Goal: make tenancy regression-proof.

Tasks:

Acceptance criteria:

Phase 6 — Controlled production rollout (after gates)

Goal: safely introduce true shared-tenancy.

Tasks:

Acceptance criteria:

9. Developer ticket backlog

Epic A — Tenancy ADR and beta freeze

A1. Write Tenancy ADR

Acceptance: ADR records chosen bridge strategy, final shared-DB goal, auth provider assumptions, tenant selection method, Mark sign-off, and no-co-mingling rule.

A2. Confirm current customer data residency

Acceptance: clear statement whether current beta data contains one company or multiple companies in one DB; if multiple, incident/risk plan exists.

Epic B — Schema foundation

B1. Add tenants and memberships migration

Acceptance: migration creates tables, constraints, indexes, seed default tenant path.

B2. Backfill app users to default tenant

Acceptance: all existing users have active membership in staging rehearsal; disabled/unknown users handled.

B3. Add tenant_id nullable to business tables

Acceptance: all identified tenant-owned tables have nullable tenant_id; indexes created.

B4. Backfill tenant_id

Acceptance: zero null tenant IDs after backfill in staging; row counts match before/after.

B5. Convert unique constraints

Acceptance: external ID/name constraints are tenant-scoped; duplicate IDs across tenants allowed in tests.

B6. Enforce NOT NULL + tenant-aware FK safeguards

Acceptance: tenant-owned rows cannot be inserted without tenant; cross-tenant FK links rejected.

Epic C — Auth and API authorization

C1. Implement requireTenantContext

Acceptance: validates token, ensures user, resolves tenant, checks membership/status, checks capability, returns typed context.

C2. Add tenant header/request plumbing

Acceptance: frontend sends tenant ID; multi-tenant users can switch; backend does not trust frontend without membership check.

C3. Scope forecast API

Acceptance: api-forecast returns only tenant rows; wrong tenant returns 403.

C4. Scope filters API

Acceptance: api-filters returns only tenant options, clients, last sync, and forecast dimensions.

C5. Scope budget APIs

Acceptance: budget read/write scoped by tenant; write requires budget capability.

C6. Scope filter presets

Acceptance: presets are tenant + user scoped; no default leakage.

C7. Scope settings/probability writes

Acceptance: settings writes require admin capability; only affect current tenant.

C8. Scope sync health

Acceptance: tenant sees only own sync logs/status.

C9. Scope deal insights

Acceptance: all deal/stage/stagnation/owner queries are tenant-scoped.

Epic D — RLS and service-role reduction

D1. Replace permissive RLS policies

Acceptance: no tenant-owned table has authenticated USING (true) read policy in final migrations.

D2. Add RLS tests

Acceptance: authenticated Tenant A context cannot select Tenant B rows.

D3. Create tenant repository/helper layer

Acceptance: endpoint code uses approved helper for tenant-owned tables.

D4. Add static guard for raw tenant table access

Acceptance: CI flags new raw service-role table access without tenant helper/explicit waiver.

Epic E — Per-tenant integrations/sync

E1. Add tenant integration tables

Acceptance: HubSpot integration config can be stored per tenant; secret refs are not raw tokens.

E2. Move target pipelines to tenant config

Acceptance: no global hard-coded target pipeline array controls all tenants.

E3. Tenant-scope HubSpot sync

Acceptance: every sync query/upsert/delete/log/snapshot includes tenant ID.

E4. Tenant-scope locks

Acceptance: Tenant A sync lock does not block Tenant B unless global provider throttle intentionally applies.

E5. Add sync isolation tests

Acceptance: same HubSpot deal IDs in two tenants do not collide or overwrite.

Epic F — CI, QA, release and rollback

F1. Two-tenant fixture generator

Acceptance: creates Tenant A/B with overlapping IDs/names, user in one tenant, user in both tenants, disabled membership.

F2. Edge Function integration tests

Acceptance: no-token, invalid-token, wrong-tenant, disabled-membership, correct-tenant cases covered per endpoint.

F3. E2E tenant switching tests

Acceptance: UI displays only selected tenant data and does not retain stale data after switch.

F4. Migration rehearsal script

Acceptance: clean DB and staging-like DB migration both pass with row-count checks.

F5. Production smoke test script/runbook

Acceptance: release owner can verify auth and tenancy after deploy without touching customer data unnecessarily.

F6. Rollback plan

Acceptance: each migration has rollback or restore strategy; backup and owner documented.

10. Test and QA matrix

10.1 Auth/tenant tests

10.2 Forecast and filters

10.3 Budgets/settings/presets

10.4 Sync

10.5 RLS/direct DB

10.6 Security/operational

11. Release gates

Gate 0 — before further external co-residency

Gate 1 — staging tenant foundation

Gate 2 — staging isolation

Gate 3 — integration isolation

Gate 4 — production readiness

12. Rollback and incident plan

12.1 Rollback philosophy

Schema changes should be staged to avoid irreversible cutovers:

  1. Add new tables/nullable columns.
  2. Backfill.
  3. Deploy dual-read/dual-write or tenant-scoped reads.
  4. Validate.
  5. Enforce constraints.
  6. Remove legacy assumptions later.

12.2 Per-phase rollback

12.3 Cross-tenant exposure incident runbook

If a cross-tenant leak is suspected:

  1. Disable affected endpoints or tenant access.
  2. Preserve logs and audit events.
  3. Identify tenants/users/records affected.
  4. Rotate impacted integration credentials if exposure includes source data/secrets.
  5. Notify Mark/Jack and legal/commercial owner.
  6. Patch and add regression test reproducing the leak.
  7. Review release gate failure.

13. Security checklist

Before shared multi-tenant go-live:

14. Suggested agent/subagent team structure

Coordinator: Fred / Jack OS

Lane 1: FORQA CTO architect

Lane 2: Database/RLS implementer

Lane 3: Edge Function/API implementer

Lane 4: Integration/sync implementer

Lane 5: QA/security reviewer

Lane 6: Release manager

15. Approval gates for Jack/Mark

Ask before any of the following:

Required human approvals:

  1. Tenancy strategy approval: shared DB, project-per-tenant bridge, or managed isolated environments.
  2. Auth provider approval: Entra for all tenants vs Supabase Auth/magic link/OAuth strategy.
  3. Role visibility approval: all tenant members see all data vs rep/manager/finance segmentation.
  4. Pilot safety approval: whether to onboard additional customers before shared tenancy is complete.
  5. Production migration approval: backup window, owner, rollback plan, smoke tests.

16. Mark decision questions

  1. Is the immediate next beta single-company/design-partner only, or true multi-company SaaS?
  2. Are any existing beta companies currently co-resident in one Supabase project/database?
  3. Is Mark comfortable using isolated Supabase projects per pilot as a short-term bridge?
  4. Should the final architecture be shared database with tenant_id, project-per-tenant managed service, or hybrid?
  5. Should users be able to belong to multiple tenants?
  6. How should tenant be selected: subdomain, URL path, org switcher, email/domain, or one tenant per login?
  7. Is Microsoft Entra the long-term auth provider for all SME customers, or only current/internal usage?
  8. Should all users inside a customer see all forecast/commercial data?
  9. If not, what should reps, managers, finance, admins, and viewers each see?
  10. Who may edit budgets?
  11. Who may edit stage probabilities/settings?
  12. Who may run manual syncs?
  13. Who owns production deployment verification?
  14. Who owns credential incident response already flagged in previous CTO review?
  15. Which Sage product/version is in scope: Sage Accounting, Sage 50, Sage 200, or other?
  16. Is Sage actuals-only, reconciliation support, or write-back?
  17. What matching key links HubSpot companies/deals to Sage customers/invoices?
  18. What is the go/no-go date for shared multi-tenant readiness?
  19. What risk level is acceptable for the next two weeks: safe pilot learning or long-term SaaS foundation work?
  20. Who is the human release owner for production migration and rollback?
  1. Hold a 45-minute Mark tenancy decision meeting. Use section 16 as agenda.
  2. Write and sign the Tenancy ADR. Do this before code changes.
  3. Freeze shared co-residency. Do not add another external company to the current shared DB unless isolated or approved as same-dataset design partner.
  4. Assign owners for prior P0s. Credential incident response, production function deployment proof, JWT smoke tests.
  5. Create first implementation PR only after approval. Start with tenants/memberships/default tenant and non-breaking helper scaffolding.

Bottom line: FORQA can keep learning with carefully isolated pilots, but shared multi-company SaaS requires tenant isolation as a core engineering project, not a frontend filter or small patch.