Auth & Tenancy¶

This is the load-bearing chapter. Every other chapter assumes the rules below.

Confidence: High. Updated 2026-05-02 to reflect ADR-0004 — the Person/ClientProfile split and the three-grant sharing model. The chokepoint mechanism (single SQLAlchemy session-event hook, ContextVar binding, loud-failure on unbound) is unchanged from ADR-0003; only the predicate broadens.

Two systems, clear boundary¶

Concern	Owner	Where it runs
Identity (email + password, password hash, magic-link token mint/verify, JWT signing)	gotrue (Atlas's auth runtime)	gotrue container, talking to the `auth` schema inside `rbo_prod` / `rbo_test`
Authorization (who can see/edit which row)	RBO	FastAPI app, enforced at the SQLAlchemy session layer

RBO never sees a password. RBO sees a JWT issued by gotrue, validates it (signing key per app-env per Atlas §4 Auth), and reads the sub claim → looks up the Stringer row by email/UUID → carries that stringer_id for the rest of the request.

In V3, the same JWT path serves a Person (via Person.gotrue_user_id) when the client portal lights up. V2 does not build the V3 client paths but the schema and the chokepoint are ready for them.

Auth model (V2): dual-method for stringers¶

V2 stringers authenticate with either of two methods, both bound to the same gotrue identity:

Method	When it's used	Notes
Magic-link	Initial registration (clicking the invite email Atlas/Stefan sends), and any later session where the stringer prefers it	gotrue mints a one-time link, emailed via Resend SMTP (per keystone ADR-0005)
Email + password	Available from the moment the stringer sets a password	gotrue stores the password hash; RBO never sees the cleartext

Both methods bind to the same gotrue user record and produce the same JWT-in-cookie session. The stringer chooses per-session.

V3 clients (client portal) authenticate as Persons via magic-link initially. Whether V3 clients later gain a password option is V3-scoped.

Auth flows (V2)¶

 1. Admin (Stefan) creates the Stringer row in RBO, sets the email.
 2. RBO calls gotrue's invite endpoint with that email.
 3. gotrue mints a magic-link and emails it (Resend SMTP).
 4. Stringer clicks the link.
 5. gotrue verifies the token, creates the gotrue user record, issues a JWT.
 6. JWT lands in an HttpOnly Secure cookie.
 7. RBO renders the stringer's empty workspace.
 8. Stringer optionally sets a password from account-settings.

 1. Stringer hits rbo.wagen.io/login.
 2. RBO renders the form: email + password, with a "send me a magic-link" alternative.
 3a. Password path: form posts to gotrue (directly or via RBO proxy);
     gotrue verifies, issues a JWT.
 3b. Magic-link path: gotrue emails the link; click → JWT.
 4. JWT lands in an HttpOnly Secure cookie (same cookie either way).
 5. Subsequent requests: RBO middleware validates JWT, resolves Stringer row,
    binds `current_stringer_id` into a ContextVar for the request lifetime.

Stringer onboarding is admin-only (Topic 3) — there is no public sign-up.

Magic-link slot (V3 client portal)¶

In V3 the client (a Person) can authenticate via magic-link to view their own history across stringers. Architectural prep in V2:

gotrue already supports magic-link (V2 stringers use it).
The session model is the same JWT-in-cookie path. V3 binds current_person_id (instead of current_stringer_id) in a sibling ContextVar.
Authorization for the client role: read-only access to their own orders across stringers, filtered by client_profile.person_id = current_person_id. NOT by stringer_id. The chokepoint hook recognizes "Person-bound" sessions and applies a Person-scoped predicate instead of the Stringer-scoped one.
The client can issue Rule #2 and Rule #3 grants from the V3 portal. The grant write is authorized because current_person_id = order.client_profile.person_id (Rule #2) or current_person_id = person_stringer_share.person_id (Rule #3).

V2 does not build the V3 client paths, but the schema and the chokepoint accommodate them with no migration.

Tenancy: row-level, enforced at the ORM session layer¶

This is the central architectural commitment — see ADR-0001 and ADR-0004.

The rule¶

Every per-stringer-scoped table carries stringer_id as a NOT NULL FK to stringers.id. Every query goes through the chokepoint, which composes the predicate. Period.

Per-stringer-scoped tables in V2: client_profiles, rackets, strings, orders, catalogue_submissions. (order_shares and person_stringer_share are scoped via JOIN — they have no direct stringer_id because they straddle stringers by design.) Cross-stringer tables: stringers itself, persons, share_audit, person_merges. Platform-shared catalogue rows (Racket/String with visibility = 'shared') remain owned by created_by_stringer_id — visibility is layered on top.

Enforcement: SQLAlchemy session event hook (single chokepoint)¶

Same mechanism as before:

Every per-stringer-scoped ORM model declares __tenant_column__ = "stringer_id" (or inherits TenantBase).
The request middleware sets current_stringer_id (or, in V3, current_person_id) in a ContextVar once auth completes.
do_orm_execute intercepts every query, inspects the FROM clause, and applies the right predicate per model class.

If neither current_stringer_id nor current_person_id is bound, the query is refused (loud exception, logged).

The chokepoint predicate per resource class¶

Class	Tables	Predicate (Stringer-bound session)
Per-stringer private	`client_profiles`, `catalogue_submissions`	`stringer_id = :me`
Per-stringer + shared catalogue	`rackets`, `strings`	`created_by_stringer_id = :me OR visibility = 'shared'`
Per-stringer + cross-tenant share	`orders`	see below
Cross-tenant by design	`order_shares`, `person_stringer_share`	scoped via JOIN to `orders`
Platform	`persons`	reachable-via-visible-order plus own ClientProfiles
Audit	`share_audit`	own actions OR own (admin) reads

`orders` — the three-grant predicate¶

orders.stringer_id = :me
  OR orders.id IN (
       SELECT order_id FROM order_shares
        WHERE grantee_stringer_id = :me
          AND revoked_at IS NULL
     )
  OR orders.client_profile_id IN (
       SELECT cp.id
         FROM client_profiles cp
         JOIN person_stringer_share pss ON pss.person_id = cp.person_id
        WHERE pss.target_stringer_id = :me
          AND pss.revoked_at IS NULL
     )

Sub-clauses:

Own: the row belongs to me (stringer_id = :me).
Per-job grant: Rule #1 (stringer-to-stringer) or Rule #2 (client-to-stringer) — both live in order_shares; the predicate doesn't care which granter_kind admitted it. Distinguishing matters only for serialization (see "Visibility / redaction" below).
Person-global grant: Rule #3 — Person P granted me access to all their orders (past + future) via person_stringer_share.

current_stringer_id unbound → query refused. Same loud-failure mode as the prior chokepoint.

`client_profiles` and `persons` — derived predicates¶

client_profiles: stringer_id = :me OR id IN (SELECT client_profile_id FROM <visible orders>). ClientProfiles I own, plus ClientProfiles attached to orders that are visible to me by share.
persons: id IN (SELECT person_id FROM <visible client_profiles>). Persons reachable via my visible ClientProfiles.

These are derived queries that compose with the orders predicate; the chokepoint composes them as one SQL fragment per FROM target.

Writes are strictly own-tenant¶

The chokepoint refuses any UPDATE or DELETE on orders, client_profiles, rackets (where created_by_stringer_id != :me), strings (likewise), catalogue_submissions, etc. unless stringer_id = :me. Shared-in rows are read-only forever.

This matters: a stringer admitted to read an order via Rule #1, #2, or #3 cannot mutate it. Single line in the chokepoint, single test.

Grant tables (order_shares, person_stringer_share) accept writes only when:

order_shares Rule #1: the writing session's current_stringer_id = order.stringer_id AND granter_kind = stringer AND granter_stringer_id = current_stringer_id.
order_shares Rule #2: the writing session's current_person_id = order.client_profile.person_id AND granter_kind = person AND granter_person_id = current_person_id.
person_stringer_share Rule #3: the writing session's current_person_id = person_id.

These authorization checks are write-time chokepoint logic, not DB constraints (the DB CHECK enforces shape; the chokepoint enforces who can write what).

Visibility / redaction (serialization layer, NOT row layer)¶

The chokepoint admits or refuses rows. The serializer decides which fields of an admitted row appear in the response, based on the reason it was admitted.

Visibility class	First name	Last name / phone / email	Pricing (`labor_chf`, `strings_chf`, `total_chf`)	`comments` (free text)	Technical fields (racket, string, tension, color, DT, method)
Owner (`order.stringer_id = :me`)	shown	shown	shown	shown	shown
Rule #1 (stringer-to-stringer per-job)	shown	redacted	redacted	redacted	shown
Rule #2 (client-initiated per-job)	shown	shown	shown (default — open question)	shown	shown
Rule #3 (client-initiated global)	shown	shown	shown (default — open question)	shown	shown

comments is redacted under Rule #1 because it is unstructured free text and may contain client PII or stringer-internal notes that cannot be sanitized at grant time. (Aligned with requirements.)

Open question (flagged): under Rule #2 / Rule #3, pricing default is "shown" (the client granted access to their own data; pricing is theirs). Iris/Stefan to confirm. One line change in the serializer either way.

Implementation shape (informative, for Pax): one Pydantic schema per (resource × visibility-class). The handler picks the schema from a single per-request "visibility reason" computed by the chokepoint. One serialization-layer test per (resource × visibility-class) asserts the field set.

This keeps the row-level and field-level concerns orthogonal: the row is the row; how it serializes depends on why you can see it. Mixing them at the row layer (separate columns per recipient class, or separate views) was rejected — see ADR-0004 Options.

Revocation¶

Server-side, instant on the next query. No app-memory caching of grants beyond the request scope. The chokepoint re-evaluates the predicate on every query.
Grants are never hard-deleted; setting revoked_at is the only way to take them out of effect. The audit trail survives.
Already-issued artifacts are out of band. Revocation does not retroactively un-issue PDFs already downloaded, CSVs already exported, or screenshots already taken. This is documented at ADR-0004 and is FADP-relevant.

Admin role¶

Admin (Stefan, role enum admin) gets two special privileges:

Read across stringers for catalogue moderation (rackets, strings, catalogue_submissions) — bypass_tenant=True per-session attribute. The bypass is logged.
Write to promote a private_to_stringer row to shared.

Admin does not get bypass_tenant for orders, client_profiles, or persons. Cross-stringer order reads happen via the same Rules #1/#2/#3 path as anyone else. The exception is the share_audit surface itself: admin reads the audit table for the FADP audit-trail UI; this is an explicit admin privilege, distinct from bypass_tenant.

The bypass remains the only way to get an unscoped read for catalogue tables; it's logged on every query it permits.

The partial unique indexes¶

-- One self-ClientProfile per stringer
CREATE UNIQUE INDEX client_profiles_one_self_per_stringer
  ON client_profiles (stringer_id)
  WHERE is_self_for_stringer = TRUE;

-- One ClientProfile per (stringer, person)
CREATE UNIQUE INDEX client_profiles_one_per_pair
  ON client_profiles (stringer_id, person_id);

-- At most one active person_stringer_share per (person, target)
CREATE UNIQUE INDEX person_stringer_share_one_active
  ON person_stringer_share (person_id, target_stringer_id)
  WHERE revoked_at IS NULL;

Postgres native partial unique indexes do all of this in one line each.

Audit (`share_audit`)¶

Every grant create + revoke is recorded. Every chokepoint admission of a share-visible read is recorded (shared_read) with meta carrying which grant admitted it. Single table; append-only by convention.

This is the FADP-relevant log. At our scale (handful of stringers, dozens of orders per session) the volume is fine. If it ever isn't, sample (record only first read per request per (grantee, granter, order) tuple per day) — defer.

The audit table is also the trigger source for grant-related notifications (see ADR-0004 § Notification hooks).

Why row-level, not DB-per-tenant or schema-per-tenant¶

Option	Verdict	Why
Row-level (chosen)	Yes	One migration path, one backup, easy admin views, fits Atlas's db-per-app-per-env policy.
Schema-per-tenant	No	Multiplies migration work; Postgres schemas are not really designed for sub-tenants.
DB-per-tenant	No	Conflicts with Atlas's locked db-per-app policy.
Postgres RLS	No	Requires `SET LOCAL` per request; fights PgBouncer transaction pooling. Re-evaluated under ADR-0004; same conclusion.

Risks and mitigations¶

Risk	Mitigation
Silent cross-tenant data leak (a query escapes the chokepoint).	(a) Single chokepoint at the session layer. (b) Mandated integration test asserting every per-stringer-scoped table refuses unscoped reads — ADR-0004 restates this. (c) Code-review rule: any direct `session.execute(text(...))` call needs a comment justifying the scope.
Stringer-private metadata leaks into a shared Person.	The Person/ClientProfile split is the structural fix. Schema-reflection regression test (ADR-0004 test 3) asserts column placement at model-definition time.
Rule-#3 grants surprise the granting client ("I forgot I gave Stringer X global access; now they see a job from Stringer Z").	UX problem — V3 client portal must list active Rule-#3 grants distinctly ("all past + future"). Architecturally we just record them.
Pricing-on-client-share open question.	Default "shown"; one line change to flip. Iris/Stefan to confirm before V3 client portal ships.
Admin bypass abuse / leak.	Bypass logs every query. At one-admin scale this is auditable by Stefan.
JWT signing key rotation breaks existing sessions.	Acceptable — admin re-issues.
Person merge admin tool not built yet.	Data shape committed now (ADR-0004); UI is V2.x. The first duplicate that appears triggers building it; the chokepoint is already merge-aware via on-write fan-out semantics.

What this chapter does NOT cover¶

Password complexity, MFA, account lockout — gotrue's domain.
Magic-link rate-limiting — gotrue's domain.
Session timeout policy — gotrue config; RBO honors the JWT.
The JWT wire-level contract (claims, cookie attrs, refresh flow, key rotation) — locked in ADR-0006.
The implementation of the chokepoint hook — that's Pax's job, against the spec in this chapter, ADR-0004, and ADR-0006.