Skip to content

ADR-0004: Client identity (Person/ClientProfile split) and the three-grant sharing model

  • Status: Accepted
  • Date: 2026-05-02
  • Decider(s): Theo (SA), with decisions confirmed by Stefan
  • Supersedes: ADR-0003

Context

ADR-0003 committed RBO V2 to row-level tenancy with one deliberate cross-tenant breach: player_shares, allowing stringer A to share an entire Player row read-only with stringer B. The model treated each human client as a per-stringer Player row with no platform-level identity.

While building out V2 we re-thought this and surfaced four problems:

  1. Privacy leak on player_shares. Player carries stringer-private metadata (nicknames, internal notes, default-tension memos). Sharing the row would leak that metadata to the recipient stringer. There is no per-row column-set permission to safely "share, but hide my notes."
  2. No platform-level identity. A client who switches stringers, or who is a client of two stringers, exists as two unrelated Player rows. There is no way for the client themselves to view their own history across stringers, no way to give consent, and no way to address the FADP-relevant question of "whose data is this, really."
  3. Asymmetric grant authority. The V3 client portal needs the client to grant cross-stringer visibility (e.g. "Stringer Y, please show Stringer X my history so they can take over my service"). player_shares only models stringer-as-granter.
  4. Future-inclusive grants. Clients want to be able to say "Stringer X may see all my future jobs, regardless of who strings them." The per-row share model has no slot for that.

This ADR replaces the Player entity with a Person + ClientProfile split, replaces player_shares with two new share tables carrying three grant types, and re-derives the chokepoint predicate. Tenancy enforcement (single chokepoint at the SQLAlchemy session layer) is unchanged in shape; only the predicate broadens.

Options

Identity model

  • (I-1) Keep Player (status quo from ADR-0003). Simplest. Loses platform-level identity. Forces client-grant model into stringer-row semantics. Carries the privacy leak above.
  • (I-2) Single Person table per-stringer-namespaced. Slightly cleaner than Player but doesn't solve the cross-stringer identity question.
  • (I-3) Person + ClientProfile split (chosen). Person is platform-level identity. ClientProfile is the per-stringer view, holding everything stringer-private. Two-table cost; clean privacy boundary; client-portal-ready.
  • (I-4) Person + per-stringer JSONB metadata column on Person. Avoids the second table. Defeats predicate-based redaction, makes auditing hard, blurs the privacy boundary that's the whole point.

Grant model

  • (G-1) One unified shares table, polymorphic granter_kind. One table, several flag combinations. Becomes a config-row landmine — "what does it mean if granter_kind=person AND target_order_id IS NULL?" Hides the future-inclusive case behind a NULL.
  • (G-2) Two tables: order_shares (per-job, past-only) + person_stringer_share (per-(person,stringer), future-inclusive). (chosen) Each table has one shape, one invariant, one query. Future-inclusive case is expressed by table, not by NULL.
  • (G-3) Three tables (one per rule). Truer to the rules but introduces redundancy: rule #1 and rule #2 are isomorphic except for granter_kind. Two tables suffice.

Visibility / redaction

  • (R-1) Row-level redaction (separate columns / separate views per recipient class). Pollutes the schema; changing a redaction rule requires a migration.
  • (R-2) Serialization-layer redaction (chosen). The row is the row. The response shape depends on which grant type made the row visible. One central serializer per resource keeps the rules in one place and tested.
  • (R-3) Column-level grants in Postgres. Heavyweight; PgBouncer-hostile (RLS-style); no payoff at our scale.

Authorization mechanism

  • (A-1) Postgres RLS. Rejected for the same reasons as in ADR-0003: requires SET LOCAL per request; fights PgBouncer's transaction pooling. (See project_pgbouncer_constraint.md.)
  • (A-2) SQLAlchemy session-event chokepoint (kept from ADR-0003). Single hook composes the new predicate. Remains the only chokepoint.

Decision

Identity: split into Person + ClientProfile

Person is a platform-level identity record. One row per real human. Login binds here.

Field Notes
id (PK)
email UNIQUE, nullable until claimed (a stringer creating a client without an email is allowed).
email_verified_at Nullable. Set when gotrue confirms ownership (magic-link click).
gotrue_user_id Nullable. Linked when the Person logs in.
display_first_name, display_last_name The person's preferred public name (used on receipts when no override exists; used as the only PII surface visible under Rule #1 redaction).
default_locale EN | DE.
notification_prefs JSONB, channel-keyed.
claim_token Nullable. Single-use token issued at draft creation; consumed when the Person logs in via a stringer-issued magic-link to claim the record.
created_at, updated_at

Person carries no stringer_id — it is owned by the platform.

ClientProfile is the stringer-scoped view of a Person.

Field Notes
id (PK)
stringer_id (FK, NOT NULL) The owning stringer.
person_id (FK, NOT NULL) The Person this profile is about.
nickname Stringer-private (e.g. "the lefty kid", "Mr. RPM").
internal_notes Stringer-private free text.
default_tension_memo Stringer-private (e.g. "always wants 25/24, dislikes Solinco").
is_self_for_stringer BOOLEAN NOT NULL DEFAULT FALSE. Replaces Player.is_stringer_self.
created_at, updated_at

Uniqueness: UNIQUE (stringer_id, person_id) — one ClientProfile per (stringer, person) pair.

Self-Profile partial unique index: UNIQUE (stringer_id) WHERE is_self_for_stringer = TRUE — at most one self-profile per stringer.

Order retains stringer_id and gains client_profile_id (FK to ClientProfile). Orders no longer reference Player at all. The Person is reachable transitively via order.client_profile.person_id.

Privacy invariant (load-bearing):

All stringer-private metadata about a client lives on ClientProfile. Person carries only the data the human consents to make platform-public (their displayed name, their email if claimed, their locale).

The chokepoint and the serializer both rely on this invariant. A regression test (see "Required tests" below) enforces it.

Identity matching when adding a client: strict verified-email match only

When stringer X enters a new client:

Input Resolution
Email is supplied AND matches an existing Person with email_verified_at IS NOT NULL Suggest the match. Stringer confirms; new ClientProfile attaches to the existing Person.
Email is supplied AND matches an existing Person with email_verified_at IS NULL Soft suggestion only — show "we have an unverified Person with this email; do you want to attach?" Stringer chooses. Default behavior: create a new draft Person.
Email is supplied AND no match Create a new Person with the email and a claim_token. The Person is unclaimed until they magic-link in.
Email is not supplied Create a draft Person with email IS NULL, no claim_token (cannot be claimed without an email).

Never auto-match on name, phone, or any other field. Names collide; phones change. Email + verification is the only durable identity binding.

Post-hoc duplicates (two Persons turn out to be the same human after the fact) require a Person.merge admin operation. Designed now (see "Person merge" section below); built later.

The three grant types

Two tables; three semantic rules.

order_shares — per-job (past only)

Field Notes
id (PK)
order_id (FK NOT NULL) The specific past job being shared.
granter_kind Enum: stringer | person.
granter_id FK — to stringers.id if granter_kind=stringer, to persons.id if granter_kind=person. (Two FKs, one nullable each, with a CHECK enforcing exactly one is non-null — keeps referential integrity.)
grantee_stringer_id (FK NOT NULL) Who can read.
created_at NOT NULL.
revoked_at Nullable. NULL = active.
  • Rule #1 — stringer-to-stringer per-job share: granter_kind = stringer, granter_stringer_id = order.stringer_id. The owning stringer hands a specific order's view to another stringer.
  • Rule #2 — client-to-stringer per-job share: granter_kind = person, granter_person_id = order.client_profile.person_id. The Person whose order it is hands a specific past job to another stringer (e.g. via the V3 client portal).

Granter-validity invariant: for granter_kind=stringer, granter_id must equal order.stringer_id; for granter_kind=person, granter_id must equal the Person on the order's ClientProfile. Enforced by trigger or by application-level chokepoint write-time check.

person_stringer_share — global, future-inclusive (per (person, target stringer))

Field Notes
id (PK)
person_id (FK NOT NULL) The granter.
target_stringer_id (FK NOT NULL) The recipient stringer who will see all of this Person's orders.
created_at NOT NULL.
revoked_at Nullable. NULL = active.
  • Rule #3 — client-global share (per target stringer): the only mechanism that picks up future orders. If Person P grants person_stringer_share(P, X), and tomorrow Stringer Z strings for P, Stringer X automatically sees that new order.

Uniqueness: UNIQUE (person_id, target_stringer_id) WHERE revoked_at IS NULL — partial unique. A revoked grant can be re-issued.

Bulk "share everything-so-far" UI

A common client-side gesture is "share my entire history with Stringer X." Two implementations:

  • (B-1) Compose N rule-#2 grants. Walk the Person's orders, insert one order_shares row per order. One transaction. Granular revocation. Chosen.
  • (B-2) Special grant kind. Add a fourth grant type "all past, no future." Adds a table; gains nothing semantic.

We pick (B-1). Trade-off documented: bulk grants are heavier on row count but truer to the per-job revocation primitive.

Authorization predicate (single chokepoint)

The session-event chokepoint from ADR-0003 stays. Its orders predicate broadens:

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
     )

Equivalent predicates apply to derived reads (receipts, attachments) — they hang off order_id and inherit the predicate via JOIN to orders.

For client_profiles and persons the predicate is "any Person/ClientProfile reachable from a visible Order, plus my own ClientProfiles" — composed by the same hook.

Writes remain strictly stringer_id = :me. The chokepoint refuses any UPDATE or DELETE on orders/client_profiles where stringer_id != :me. Shared-in rows are read-only forever.

current_stringer_id unbound → query refused. Same loud-failure mode as ADR-0003.

Visibility / redaction (serialization layer)

Redaction is performed when the row is serialized for the response, NOT when the row is fetched. Same Order row may serialize differently depending on why the requesting stringer can see it.

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 — see open question) shown shown
Rule #3 (client-initiated global) shown shown shown (default — see open question) shown shown

comments is redacted under Rule #1 because it is unstructured free text written by the granting stringer and may contain client PII or stringer-internal notes that cannot be sanitized at grant time. Per-grant manual sanitization is not a defensible UX. (This aligns with Iris's requirements doc; see docs/requirements/client-identity-and-sharing.md.)

Open question (flagged for Iris/Stefan): under Rule #2 / Rule #3 the data is the client's own; the client granted access. Default is "show pricing." If Iris or Stefan disagree, this defaults to "redacted" with one line change in the serializer.

Implementation shape (informative, for Pax):

  • One Pydantic schema per (resource, visibility-class). E.g. OrderOwnerView, OrderRule1View, OrderClientGrantView.
  • The handler picks the schema based on the reason the row is visible. The reason is computed once per request, in the same chokepoint that admits the row.
  • One serialization-layer test per (resource × visibility-class) asserts which fields appear.

Revocation semantics

  • Server-side, instant on next query. No app-memory caching of grants beyond the request scope. The next query against the chokepoint re-evaluates the predicate.
  • Already-issued artifacts are out of band. PDFs already downloaded, CSV exports already taken, screenshots already screenshotted — none are recalled by revocation. The ADR explicitly states: revocation does not retroactively un-issue artifacts. This is FADP-relevant; recording it here protects everyone from the misconception.
  • Revocation is recorded, not deleted. Grant rows are never hard-deleted; setting revoked_at is the only way to take a grant out of effect. This preserves the audit trail.

Audit

A single append-only share_audit table records every grant create, every revoke, and every read of a row visible by virtue of a grant.

Field Notes
id (PK, monotonic)
event_kind Enum: grant_created | grant_revoked | shared_read.
actor_kind Enum: stringer | person | system.
actor_id FK by convention.
target_kind Enum: order_share | person_stringer_share | order | client_profile.
target_id FK by convention.
request_id UUID; for correlation with structured logs.
at NOT NULL timestamptz.
meta JSONB. Optional context (e.g. for shared_read: which grant row admitted the read).

shared_read is logged at chokepoint-admit time, NOT in the handler. One row per request per visible-by-share order. At our scale (handful of stringers, dozens of orders per session) the volume is fine.

The table is append-only by convention; future hardening can add a Postgres trigger refusing UPDATE/DELETE.

Notification hooks (architectural slot, not ADR-mandated UX)

Iris's requirements doc specifies several notifications around grants. Architecturally, two of them have non-trivial trigger points and warrant naming here so Pax knows where to hang the handler:

  1. "Originating stringer notified when their new job becomes Rule-3 visible to a third stringer." Trigger: Order INSERT. Hook: query person_stringer_share for active rows targeting order.client_profile.person_id; for each match, enqueue a notification to order.stringer_id naming the grantee. This is a post-write hook running inside the same transaction-scope (so the notification is consistent with the order's existence).
  2. "Grantee stringer / granter client notified on grant create / revoke." Trigger: INSERT on order_shares / person_stringer_share, or UPDATE setting revoked_at. Hook: enqueue notification keyed off the share_audit row written in the same transaction.

Notification delivery (channel, template, throttling) is V3-scoped and handled by the V3 dispatcher. V2 records the events in share_audit and (if the V2 in-app notification surface ships) renders them in-app. The architectural commitment here is just: the trigger points exist and are reachable from the chokepoint; no new infra is needed beyond share_audit.

Person merge (designed now, built later)

Need: stringer X enters client "John Smith" (no email). Months later, John Smith logs in via a magic-link in another flow and the platform realizes there's a duplicate Person (or two stringers each created their own draft Person for the same human).

Data shape:

person_merges
  id (PK)
  surviving_person_id (FK)
  merged_person_id    (FK — soft-marked merged, NOT deleted)
  merged_at
  merged_by_admin_id
  reason (text)

Invariant on read: any reference to merged_person_id follows a chain to surviving_person_id. Implementation can be either:

  • (a) An on-write migration: at merge time, UPDATE every client_profiles.person_id and every order_shares.granter_id (where granter_kind=person) and every person_stringer_share.person_id from merged_person_id to surviving_person_id, then mark persons.merged_into = surviving_person_id. Reads stay simple.
  • (b) A read-time indirection: persons.merged_into is a self-FK; queries always resolve via COALESCE(merged_into, id).

We prefer (a) — simpler reads, simpler tests. The cost is a one-time write fan-out; at our scale this is fine.

Why design now: because forgetting it now lets ClientProfile.person_id, order_shares.granter_id, and person_stringer_share.person_id diverge in semantics, and back-fitting is much harder than getting the FK shape right up front. We are NOT building the admin UI now — that's V2.x.

FADP positioning

  • Every grant carries granter_kind — we always know whether a stringer or the client themselves granted access.
  • Client-initiated grants are de-facto consent records (Rule #2, Rule #3). The audit trail of grant_created events is the consent log.
  • Revocation is supported and instant (server-side); the documented limitation about already-issued artifacts is the only honesty-required caveat.
  • Persons can be deleted on request (right to erasure). Implementation: soft-delete on Person (set deleted_at, scrub PII to placeholders); orders survive (financial record, FADP-permitted retention) but their PII becomes "redacted by request." This is implementation detail beyond this ADR's scope; the schema accommodates it.

Required tests (this ADR mandates them)

  1. The ADR-0003 universal-tenancy test stays. Every tenant-scoped table refuses unscoped reads. (Inherited; ADR-0003's test is restated here as still required, simply against the new table set.)
  2. Per-grant-class predicate test. For each of (owner, rule #1, rule #2, rule #3), seed a fixture and assert the chokepoint admits the right rows and refuses the wrong ones.
  3. Privacy-invariant test (Person/ClientProfile split). Assert that Person columns do NOT include any of: nickname, internal_notes, default_tension_memo, is_self_for_stringer. Assert that ClientProfile does NOT carry email, gotrue_user_id, default_locale, notification_prefs. This is the regression test for the "stringer-private metadata never leaks via shared Person" property — implemented as schema reflection, not a request-level test, because it must fail at model definition time.
  4. Serialization-layer test. For each (resource × visibility-class), assert the response shape contains exactly the documented fields and no others.
  5. Write-protection test. Assert that a stringer admitted to read an order via Rule #1, #2, or #3 cannot UPDATE or DELETE it. The chokepoint must reject.
  6. Revocation test. Grant → read succeeds. Revoke. Read fails on the next query. No restart, no cache flush.
  7. Bulk-share test. N orders, one bulk-share gesture → N order_shares rows. Revoke one → only that order disappears for the grantee.

Consequences

Good

  • Privacy boundary is structural. Stringer-private metadata cannot leak into a cross-stringer share because it's on a different table. The split is the proof.
  • Client portal becomes possible without re-architecture. Person.gotrue_user_id is the linkage; a client logs in and sees their orders across stringers via the same chokepoint, with current_stringer_id simply unset (a Person, not a Stringer, is the auth subject).
  • Three grant types compose cleanly. Two tables, one chokepoint predicate, one serializer-class table. The future-inclusive case (Rule #3) is expressed by table, not by NULL flags.
  • Future-inclusive Rule #3 works for stringers who don't yet exist. A Person grants person_stringer_share(P, X) today; if Stringer Z (who joins next year) ever strings for Person P, that order is automatically visible to Stringer X under the same predicate. Documented explicitly.
  • Audit trail is single-table, append-only, three event-kinds. Cheap; queryable; FADP-defensible.
  • PgBouncer-compatible. No advisory locks, no SET LOCAL, no LISTEN/NOTIFY, no Postgres RLS. The chokepoint is pure SQLAlchemy session machinery binding a ContextVar. This is a commitment of the platform, not just a happy accident — see project_pgbouncer_constraint.md.
  • Migration from ADR-0003 design is mechanical for V1. V1 is one stringer (Stefan); every V1 client becomes one Person + one ClientProfile under Stefan; no share tables seeded. Zero ambiguity.

Costs we accept

  • Two tables where there used to be one. Every "show me a client" read traverses one extra JOIN. At our scale, immeasurable.
  • order_shares.granter_id is polymorphic. Solved with two FK columns + CHECK constraint; the schema is uglier than a single FK. Acceptable; auditable.
  • shared_read audit row per request per visible-by-share order. At our scale, fine. If volume becomes an issue, sample (e.g. record only first read per request per (grantee, granter, order) tuple per day). Defer the optimization.
  • The Person merge tool is a real future obligation. Designed now, built later — but the obligation is real and we should not be surprised when the first duplicate appears.
  • Pricing visibility on client-initiated shares is an open question. Default chosen (shown); flagged for Iris/Stefan to confirm or flip. One line change either way; documented at the top of this ADR.
  • Rule-#3 future-inclusive grants are a privacy power tool for the client. A Person who forgets they granted Rule-#3 to Stringer X may be surprised when Stringer X sees a job done by Stringer Z. UI affordance: when the client lists active grants in V3, Rule-#3 grants are flagged distinctly ("all past + future"). Architecturally we just record them; the surprise-mitigation is Iris's UX problem.
  • The chokepoint predicate gets longer. Three sub-clauses now (own, order-share, person-stringer-share). Still one place; still one regression-test surface. The cost is one paragraph more to read in the chokepoint code.
  • person_shares is gone. Anywhere outside this repo that referenced "player_shares" as the cross-tenant escape hatch needs an update; inside this repo, ADR-0003 is superseded by this ADR and points here.

Migration impact (for Vera, V1 → V2)

Trivial.

  1. Stefan is one Stringer (role = admin).
  2. Stefan's self-Player becomes one Person ("Stefan Wagen", with email) + one ClientProfile under Stefan with is_self_for_stringer = TRUE.
  3. Each distinct V1 client becomes one Person (email if known, else email IS NULL, no claim_token) + one ClientProfile under Stefan.
  4. Orders point at the new ClientProfile via client_profile_id.
  5. No order_shares rows seeded. No person_stringer_share rows seeded. V1 has one stringer; there's nothing to share.
  6. Catalogue tables (Racket, String, CatalogueSubmission) are unchanged by this ADR.

Vera does not need to design any of this — it's documented here so the migration brief can lift it directly.

Alternatives considered (and why not)

  • Don't split — keep Player, just add column-level "private" flags. Defeats the chokepoint's elegance; every column would need its own filter; tests multiply. Rejected.
  • Build the V3 client portal first, derive the schema from it. Tempting but inverted. The schema must support V3 without forcing it now. Person/ClientProfile is V3-ready without committing V2 to a client UI.
  • Use Postgres RLS. Re-rejected for the same PgBouncer reason as ADR-0003.
  • Make all sharing client-initiated only (delete Rule #1). Stefan explicitly wants stringer-to-stringer per-job sharing for the "I'm going on holiday — please string for my clients while I'm away" case. Rule #1 stays.

Cross-references

  • The platform constraint that motivates the split's privacy invariant: stringer-private metadata MUST live on ClientProfile, never on Person.
  • The PgBouncer constraint shaping the chokepoint mechanism: see project_pgbouncer_constraint.md.
  • ADRs: this supersedes ADR-0003. Companion: ADR-0001 (stack), ADR-0002 (receipts).
  • Architecture chapters rewritten alongside this ADR: docs/architecture/data-model.md, docs/architecture/auth-and-tenancy.md.
  • Iris's parallel requirements update — to be cross-linked from the MR.
  • Pax's follow-up implementation issue (SQLAlchemy models + Alembic migration) — to be opened after this ADR merges.