Multi-tenancy contract

Mecanismos concretos para implementar policies/multi-tenant-by-default.kmd. Spec é normativo: todo módulo da Koder Stack que armazena dado-de-usuário deve passar nos checks T1–T9 abaixo.

Identity model

koder_user_id   BIGINT NOT NULL    -- FK to services/foundation/id.user(id)
workspace_id    BIGINT             -- nullable; FK to id.workspace(id)

koder_user_id é canonical PK partial em toda tabela com PII. workspace_id amplifica scope: nullable significa "pessoal"; non-null significa "do workspace, todos os members veem por membership".

Tabela canonical de membership (Koder ID):

CREATE TABLE workspace_member (
  workspace_id BIGINT NOT NULL,
  koder_user_id BIGINT NOT NULL,
  role TEXT NOT NULL,             -- 'owner' | 'admin' | 'member' | 'viewer'
  joined_unix BIGINT NOT NULL,
  PRIMARY KEY (workspace_id, koder_user_id)
);

Qualquer query cross-workspace passa por essa tabela. Não cacheia membership client-side — é hot path do auth, fica em services/foundation/id com cache de 60s server-side.

Org ↔ internet-domain cardinality (RFC-019)

An Org owns one primary (default) domain plus N verified alias domains (N-domains-per-org; the account is the org — no super-account layer). Canonical table:

CREATE TABLE org_domains (
  org_id              BIGINT  NOT NULL REFERENCES koder_id.organizations(id),
  domain              TEXT    NOT NULL,            -- normalized: lowercase, IDNA
  is_primary          BOOLEAN NOT NULL DEFAULT FALSE,
  verification_method TEXT    NOT NULL DEFAULT 'dns_txt',
  verification_token  TEXT    NOT NULL,
  verified_at         BIGINT,                      -- null = pending
  created_at          BIGINT  NOT NULL,
  PRIMARY KEY (org_id, domain)
);
CREATE UNIQUE INDEX ux_org_domains_domain  ON org_domains (domain);            -- globally unique
CREATE UNIQUE INDEX ux_org_domains_primary ON org_domains (org_id) WHERE is_primary;  -- one primary

• org → domain = 1:N, exactly one is_primary. domain → org = N:1,

  a verified domain belongs to exactly one org (globally unique).

• A domain is an attribute of the org, never an isolation axis — RLS

  never keys on a domain; the tenant boundary stays org_id.

• The org's primary domain is its default_domain for

  identity/login-resolution.kmd (bare-local-part expansion); alias domains are full-email-only. See RFC-019 for the full decision record.

Scope model — 4 categories + system-wide

The koder_user_id + workspace_id identity model above is the tenant scope — every product table that holds PII goes here. Foundation services (auth, audit, behavior, device, telephony, SSO …) also need isolation, but along axes that are not the product-tenant axis. The Koder Stack therefore defines four isolation scopes plus an explicit no-RLS bucket. Org → Workspace → Project (RFC-017) is the product-side nesting: org_id is the billing/identity boundary, workspace_id the governance boundary, and project_id the delivery boundary where resources live:

Selection rules:

1. Tenant-scoped is the default for product data. Tables that

   surface in a product UI carry koder_user_id.

2. Org-scoped applies when the primary owner is an org, not a

   user — even if individual user IDs are recorded. Audit events for an org are visible to every admin of that org regardless of who triggered the event.

3. *ser-scoped applies when the row is per-user inside a

   foundation service*and there is no org or product tenant in play. Behavior risk profiles, QR pairing sessions, sync state.

4. *roject-scoped applies to product resources owned by a

   delivery Project*(RFC-017) — repos, datasets, deployments, agents, files, batches. It is the narrowest product axis, nested under workspace_id; a leak between two projects of the same workspace is an isolation failure. The full materialized path on such a row is koder_user_id + org_id + workspace_id + project_id.

5. System-wide is the explicit exception. It must be justified

   in the table's migration comment (e.g. "global rate-limit counters; no per-tenant cardinality"). Drift toward this scope without justification is a spec violation.

Sharing overlay (not a fifth scope). The four scopes above answer who owns a row. Cooperative sharing answers who else may see it: workspace_member widens visibility within a governance boundary, and tribe_member (tribus-RFC-001) widens it across home tenants by explicit grant. Both are membership-EXISTS overlays layered on top of a base scope — see "Tribe-scoped sharing overlay" below. Neither ever weakens default-deny: no grant ⇒ 404.

Mixed-scope tables (e.g. device carrying both user_id and org_id columns) either split into two migrations_rls files or use a composite policy:

USING (org_id   = current_setting('app.current_org_id',   true)
    OR user_id = current_setting('app.current_user_id', true))

The composite form is acceptable when both isolation axes apply to the same row simultaneously; otherwise prefer the split.

PAT scope grammar

PAT (Personal Access Token) emitido pelo Koder ID carrega scope. Sintaxe canonical (herdado do Flow RFC-003 credentials/backups):

<verb>:<resource>[:<modifier>]

verbs:    read | write | admin
resources: user | workspace | repo | credentials | backups | …
modifier:  optional, e.g. "self" or "<id>"

Exemplos:

• read:user — ler perfil próprio
• write:credentials — escrever credentials no scope que o PAT herdou
• read:workspace:<id> — ler dados específicos de um workspace
• admin:user — privileged self-management

PAT é scoped to a single koderuserid (o owner). Workspace access é resolvido via workspace_member na hora do request, não via PAT scope. PATs não atravessam tenants.

RLS template (Postgres / kdb-next)

Toda tabela com PII tem RLS. Helper migration:

-- 1. Schema com tenant fields
CREATE TABLE my_resource (
  id BIGSERIAL,
  koder_user_id BIGINT NOT NULL REFERENCES koder_id.user(id),
  workspace_id BIGINT REFERENCES koder_id.workspace(id),
  payload JSONB NOT NULL,
  created_unix BIGINT NOT NULL DEFAULT extract(epoch from now()),
  PRIMARY KEY (koder_user_id, id)
);

-- 2. Index on tenant + recent
CREATE INDEX ix_my_resource_user_recent
  ON my_resource (koder_user_id, created_unix DESC);

-- 3. RLS enable + policy
ALTER TABLE my_resource ENABLE ROW LEVEL SECURITY;

CREATE POLICY p_owner ON my_resource
  USING (koder_user_id = current_setting('app.current_tenant_id', true)::BIGINT);

CREATE POLICY p_workspace_member ON my_resource
  USING (workspace_id IS NOT NULL
         AND EXISTS (
           SELECT 1 FROM koder_id.workspace_member m
           WHERE m.workspace_id = my_resource.workspace_id
             AND m.koder_user_id = current_setting('app.current_tenant_id', true)::BIGINT
         ));

Connection setup (per request):

// Tenant-scoped (default for product data with PII)
err := kdb.WithTenantTx(ctx, store, auth.UserID, func(ctx context.Context, s kdb.Store) error {
    return repo.List(ctx)
})

The helper opens a transaction, runs SELECT set_config('app.current_tenant_id', $1, true), and dispatches the callback through a *sql.Tx. Empty scope ID is fail-closed (returns an error rather than running unscoped).

Bypass admin path (rare): an explicit RESET app.current_tenant_id is privilege of the role koder_admin only. Audit log obrigatório em qualquer reset.

Project-scoped variant

For product resources owned by a delivery Project nested under a Workspace (RFC-017): repos, datasets, deployments, agents, files, batches. A row is visible if its project_id matches the scoped project or if the caller is a member of the project's workspace:

ALTER TABLE project_resource ENABLE ROW LEVEL SECURITY;

CREATE POLICY p_project ON project_resource
  USING (project_id = current_setting('app.current_project_id', true)::BIGINT);

CREATE POLICY p_project_member ON project_resource
  USING (EXISTS (
           SELECT 1
           FROM koder_id.project p
           JOIN koder_id.workspace_member m
             ON m.workspace_id = p.workspace_id
           WHERE p.id = project_resource.project_id
             AND m.koder_user_id = current_setting('app.current_tenant_id', true)::BIGINT
         ));

err := kdb.WithProjectTx(ctx, store, projectID, func(ctx context.Context, s kdb.Store) error {
    return repo.List(ctx)
})

Org-scoped variant

For foundation tables owned by an org (audit events, MFA policies, access policies):

ALTER TABLE org_resource ENABLE ROW LEVEL SECURITY;

CREATE POLICY p_org ON org_resource
  USING (org_id = current_setting('app.current_org_id', true)::BIGINT);

err := kdb.WithOrgTx(ctx, store, auth.OrgID, func(ctx context.Context, s kdb.Store) error {
    return repo.List(ctx)
})

User-scoped variant

For foundation tables owned by a single user (behavior events, QR sessions, sync state, telephony OTPs, user-scoped webhooks):

ALTER TABLE user_resource ENABLE ROW LEVEL SECURITY;

CREATE POLICY p_user ON user_resource
  USING (user_id = current_setting('app.current_user_id', true)::BIGINT);

err := kdb.WithUserTx(ctx, store, auth.UserID, func(ctx context.Context, s kdb.Store) error {
    return repo.List(ctx)
})

All three helpers share the same body in services/foundation/id/engine/pkg/kdb/rls.go::withScopeTx — fail-closed on empty scope, ctx-stash propagated to nested calls, test coverage in rls_scope_test.go.

Tribe-scoped sharing overlay (grant-based, cross-tenant)

The cooperative-work primitive (services/foundation/tribus, tribus-RFC-001) introduces the one sanctioned cross-tenant sharing path: a tribe is a shareable container whose membership is decoupled from each member's home tenant. A row attached to a tribe is visible to any caller holding a tribe_member grant — regardless of whose koder_user_id owns it. Same mechanism as p_workspace_member (visibility by membership), except workspace_member shares within a governance boundary while tribe_member shares across home tenants by explicit grant.

ALTER TABLE tribe_resource ENABLE ROW LEVEL SECURITY;

-- owner still sees their own rows via p_owner (above)
CREATE POLICY p_tribe_member ON tribe_resource
  USING (EXISTS (
           SELECT 1 FROM koder_tribus.tribe_member m
           WHERE m.tribe_id = tribe_resource.tribe_id
             AND m.koder_user_id = current_setting('app.current_tenant_id', true)::BIGINT
         ));

Default-deny is preserved. Absent a tribe_member row the policy yields nothing → the caller gets 404, not 403 (T3 still holds). The overlay only ever widens visibility for explicitly-granted members; it never weakens the baseline. Anonymous anyone_with_link grants (tribus T1) are tribe_member rows with koder_user_id IS NULL, resolved by the share-link handler before the tenant GUC is set — not by RLS.

This is an overlay, not a fifth primary scope: a tribe_resource is still tenant-scoped at rest (it has an owner); the tribe grant only adds a cross-tenant read path on top.

KV / cache template (Redis-style)

Toda key tem prefixo de tenant:

<namespace>:<tenant-key>:<resource-key>

examples:
  rate_limit:user:<uid>:5h_window     → counter
  session:user:<uid>:<session_id>     → JSON
  presence:workspace:<wid>:<uid>      → boolean

Helper:

func TenantKey(uid int64, parts ...string) string {
    return fmt.Sprintf("user:%d:%s", uid, strings.Join(parts, ":"))
}

Key sem prefixo → bug crítico (cross-tenant leak via cache).

S3 / object storage template

Path:

<bucket>/<koder_user_id>/<workspace_id|"personal">/<resource_id>/<file>

IAM / signed-URL: per-request, restricted to the tenant prefix.

Test contract — T1..T12

Todo módulo multi-tenant tem suite que cobre:

Tenant-scoped baseline (T1..T9 — every product table)

Org/user-scoped extension (T10..T12 — foundation services)

Required for any table whose RLS policy is keyed by org_id or user_id (not koder_user_id). Sibling of T3+T7 but along the secondary axis.

Grant-scoped extension (T13..T14 — cooperative sharing / tribe)

Required for any table shared via the cross-tenant grant overlay (tribe_member, tribus-RFC-001). Siblings of T5/T6, but along the grant axis instead of the workspace axis.

System-wide tables (rate-limit buckets, SSO client registry, SSO revocation list) intentionally have no T10..T12 row — they are not scope-protected by RLS and instead rely on admin auth. Their migration MUST carry a -- system-wide: <justification> comment that audit will read.

Cada implementação ships com tests/multi-tenant/T1..T9_test.go (produto) ou tests/multi-tenant/T10..T12_test.go (foundation org/user-scoped). Audit: PR sem o subconjunto aplicável verde bloqueia merge (ver policies/regression-tests.kmd co-enforcement).

Error model

Crítico: 404, não 403, em cross-tenant cases. 403 vaza existência ("este id existe mas você não pode ler" → atacante sabe que existe).

Audit log

Toda operação mutating que toca PII grava audit row:

CREATE TABLE audit_log (
  id BIGSERIAL PRIMARY KEY,
  actor_user_id BIGINT NOT NULL,    -- the PAT owner
  target_user_id BIGINT,            -- tenant being acted on (often = actor)
  action TEXT NOT NULL,             -- 'create' | 'update' | 'delete' | 'read_admin'
  resource TEXT NOT NULL,           -- 'credentials' | 'usage' | …
  resource_id BIGINT,
  payload JSONB,
  created_unix BIGINT NOT NULL
);
CREATE INDEX ix_audit_actor ON audit_log (actor_user_id, created_unix DESC);
CREATE INDEX ix_audit_target ON audit_log (target_user_id, created_unix DESC);

Audit row é best-effort write (failure logs but doesn't abort the action; ver flow#056b policy).

Sharding model (futuro, hyperscale)

Quando uma tabela passar de ~10M rows ou ~100K tenants ativos:

• Range-shard por koder_user_id (TiKV PD faz isso automático em

  kdb-next)

• Hash-shard via hash(koder_user_id) % N (alternativa em Postgres

  com Citus / pg_partman)

• Geo-shard por região do tenant (multi-region future, ver

  stack-RFC-001 §faseamento)

Não pré-otimizar. Trigger: monitoring sinalizar p99 latência > 50ms ou table size > 1TB.

Edge cases

User rename / handle change

koder_user_id é immutable — handle (@username) muda; ID não. Toda referência cross-table usa koder_user_id (BIGINT), nunca handle.

Workspace transfer

Workspace muda de owner: workspace.owner_id muda; workspace_id permanece. Resources com workspace_id = X continuam acessíveis pelos members atuais.

Account deletion (GDPR-style)

Quando user pede delete:

1. Set user.deleted_unix = NOW() (soft delete)
2. Cron job de retention varre tabelas e deleta rows

   WHERE koder_user_id = X AND <table-specific retention>

3. Audit row em audit_log registra "user_deleted" antes da limpeza
4. Retention default: 30 dias (configurável per-tenant pra

   compliance).

Account merge

Out of scope — Koder Stack não suporta merge automático de accounts. Admin-only manual operation se necessário.

Spec audit

Aplicabilidade automática (futuro: koder-spec-audit multi-tenancy):

• Escaneia migrations: tabelas com PII columns (email, name,

  password*, key*) sem koder_user_id nem user_id / org_id (3-scope model) → flag

• Escaneia routers: endpoints sem auth middleware → flag
• Escaneia código: SELECT * FROM <pii-table> sem WHERE → flag
• Escaneia env vars: shared cache keys sem prefixo → flag
• Escaneia migrations sem ALTER TABLE … ENABLE ROW LEVEL SECURITY

  para tabelas que carregam coluna koder_user_id / user_id / org_id → flag

• Escaneia migrations system-wide (-- system-wide: comment): cada

  tabela sem RLS deve ter uma justification clause de uma linha, ou o flag aponta um candidato a auto-elevar para org-scoped.

Severity: error (block release) na primeira release que adopt o audit; advisory antes.