Data Model¶

Entity-level sketch of RBO V2's Postgres schema. No SQL DDL — that's implementation. This page captures the entities, their relationships, the partial-unique-index quirks, and the visibility/sharing semantics that the auth-and-tenancy chapter relies on.

Confidence: High. The identity model and sharing model are pinned by ADR-0004 (which supersedes ADR-0003). Catalogue and Order shape are pinned by Iris's Topic-3 lock-ins in the requirements log.

Change history: the previous Player-centric model and player_shares table are superseded. See ADR-0004 for the rationale. This chapter reflects the post-0004 schema.

Entity overview¶

                        ┌──────────────────┐
                        │   Person          │  (platform-level identity)
                        │   id              │
                        │   email (uniq,    │
                        │     nullable)     │
                        │   gotrue_user_id  │
                        │   display_first/  │
                        │     last_name     │
                        │   default_locale  │
                        │   notification_   │
                        │     prefs         │
                        │   claim_token     │
                        └────────┬──────────┘
                                 │ 1
                                 │
                                 │ N
                        ┌────────┴──────────┐                ┌──────────────┐
                        │  ClientProfile    │ N           1  │  Stringer    │  (= tenant root)
                        │  id               │────────────────│  id          │
                        │  stringer_id  ────┼────►           │  email       │
                        │  person_id        │                │  role        │
                        │  nickname         │                │  default_    │
                        │  internal_notes   │                │     locale   │
                        │  default_tension  │                └──────┬───────┘
                        │     _memo         │                       │ 1
                        │  is_self_for_     │                       │
                        │     stringer      │                       │
                        └─────────┬─────────┘                       │
                                  │ 1                               │
                                  │                                 │
                                  │ N                               ▼ N
                        ┌──────────────────────────────┐    ┌──────────────────┐
                        │ Order  (unified)             │    │ Racket           │
                        │  stringer_id                 │    │ (visibility:     │
                        │  client_profile_id           │    │  private | shared│
                        │  racket_id                   │    │  | one-off)      │
                        │  main/cross string spec      │    └──────────────────┘
                        │  lifecycle dates             │    ┌──────────────────┐
                        │  pricing                     │    │ String           │
                        └────────┬─────────────────────┘    │ (visibility:     │
                                 │                          │  private | shared│
                                 │                          │  | pending)      │
                                 ▼ N                        └──────────────────┘
                        ┌──────────────────┐
                        │ order_shares     │
                        │   order_id       │           (per-job grants)
                        │   granter_kind   │           Rule #1: stringer-to-stringer
                        │   granter_id     │           Rule #2: client-to-stringer
                        │   grantee_       │
                        │     stringer_id  │
                        │   created/       │
                        │     revoked_at   │
                        └──────────────────┘

                        ┌──────────────────────────┐
                        │ person_stringer_share    │
                        │   person_id              │
                        │   target_stringer_id     │   Rule #3: client-global,
                        │   created/revoked_at     │           future-inclusive
                        └──────────────────────────┘

                        ┌────────────────────┐  ┌──────────────────────────┐
                        │ share_audit        │  │ CatalogueSubmission      │
                        │  event_kind        │  │  catalogue_kind: racket  │
                        │  actor_kind/id     │  │              | string    │
                        │  target_kind/id    │  │  catalogue_row_id        │
                        │  request_id, at    │  │  submitted_by_stringer_id│
                        │  meta (JSONB)      │  │  status                  │
                        │  (append-only)     │  │  reviewed_by_admin_id    │
                        └────────────────────┘  └──────────────────────────┘

                        ┌────────────────────┐
                        │ person_merges      │  (designed now, built later)
                        │  surviving_person  │
                        │  merged_person     │
                        │  merged_at, by, why│
                        └────────────────────┘

Person (platform-level identity)¶

A Person is one row per real human, owned by the platform — no stringer_id. 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 the email (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 and as the only PII surface visible under Rule #1 redaction.
`default_locale`	EN \| DE. Wins over browser `Accept-Language` for the V3 client portal — see i18n.
`notification_prefs`	JSONB, channel-keyed. (V3 dispatcher.)
`claim_token`	Nullable single-use token. Issued at draft creation; consumed when the Person magic-links in to claim the record.
`merged_into`	Nullable self-FK. NULL for live Persons; set when soft-merged into another (see Person merges).
`created_at`, `updated_at`

Privacy invariant: Person carries only data the human consents to make platform-public (display name, claimed email, locale). Stringer-private metadata never lives here.

Identity-matching rule when a stringer adds a new client:

Input	Resolution
Email present, matches a `Person` with `email_verified_at IS NOT NULL`	Suggest match; stringer confirms; new ClientProfile attaches to existing Person.
Email present, matches a `Person` with `email_verified_at IS NULL`	Soft suggestion only. Default: create new draft Person.
Email present, no match	New Person with email + `claim_token`.
No email	New draft Person, `email IS NULL`, no `claim_token`.

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

ClientProfile (stringer-scoped view of a Person)¶

The per-stringer view holding everything stringer-private. One per (stringer, 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").
`internal_notes`	Stringer-private free text.
`default_tension_memo`	Stringer-private (e.g. "always wants 25/24").
`is_self_for_stringer`	BOOLEAN NOT NULL DEFAULT FALSE. The stringer's own profile.
`created_at`, `updated_at`

Constraints:

UNIQUE (stringer_id, person_id) — at most one ClientProfile per (stringer, person).
UNIQUE (stringer_id) WHERE is_self_for_stringer = TRUE — at most one self-profile per stringer (Postgres native partial unique index).

Privacy invariant (load-bearing — see ADR-0004): all stringer-private metadata about a client lives on ClientProfile. None of it leaks via a shared Person. This invariant is enforced by a schema-reflection regression test that asserts column placement at model-definition time.

Stringer (tenant root)¶

Every per-stringer-scoped table carries stringer_id FK pointing here.

id (PK)
email (unique; gotrue authoritative — RBO mirrors for joins)
gotrue_user_id (nullable until first login; bound when gotrue resolves a JWT to this Stringer per ADR-0006 (in-flight))
role enum: admin | stringer | client
display_name
default_locale enum: en | de
timestamps

Constraint: there is one admin in V2 (Stefan). Enforced by application logic, not a DB constraint, since the admin-set could grow later.

Why three role values in V2 (client reserved for V3): the V3 client portal lights up a third role client per v3-vision and the corresponding schema hook (#38 — B5: reserve 'client' value in role enum). V2 code never assigns the client role and never reads it; the value exists in the enum type so V3 can light up the portal without an Alembic enum-add migration on a live multi-stringer DB. Per Stefan's pre-answer (2026-05-02), default to 3 values now.

Note: Stringer is the tenant root. Person is a separate, platform-level identity. A human who is both a stringer and gets work done by another stringer (e.g. Stefan in the future, when another stringer starts servicing him) has both a Stringer row AND a Person row, linked indirectly via gotrue_user_id matching. RBO does not auto-link them in V2.

Racket¶

Unchanged from the prior chapter. A Racket is an instance — Stefan's specific Babolat Pure Aero 98 from 2023 with serial number PA98_2023_25 is one Racket row.

id (PK)
owner_client_profile_id (FK — the ClientProfile whose Person owns the physical racket)
manufacturer, model, version (free text in V2)
head_size_sqin, string_pattern
serial_or_instance_id (nullable)
visibility enum: private_to_stringer | shared | one_off
created_by_stringer_id (FK)
submission_id (nullable FK to CatalogueSubmission)
timestamps

Rename note for Pax: the prior chapter named this owner_player_id. The new name is owner_client_profile_id. There is no Player table any more.

Visibility semantics¶

private_to_stringer — only the creating stringer sees this Racket.
shared — admin-promoted to the platform-wide model catalogue.
one_off — terminal state for "very old random racket" entries.

The visibility filter on Racket reads:

WHERE created_by_stringer_id = :me OR visibility = 'shared'

String¶

Unchanged from the prior chapter.

id (PK)
manufacturer, model, gauge
visibility enum: private_to_stringer | pending | shared
created_by_stringer_id (FK)
submission_id (nullable FK to CatalogueSubmission)
timestamps

The visibility filter is identical in shape to Racket's:

WHERE created_by_stringer_id = :me OR visibility = 'shared'

Order (unified)¶

The single Order entity covers both self-jobs and client-jobs. The self/client discriminator lives on ClientProfile.is_self_for_stringer, NOT on Order.

id (PK)
stringer_id (FK NOT NULL)
client_profile_id (FK NOT NULL — must belong to the same stringer_id)
racket_id (FK NOT NULL — must be owned by the same client_profile_id)
Main string spec:
main_string_id (FK, nullable — set when picked from catalogue)
main_string_one_off_text (nullable — free text)
main_tension_kg, main_price_chf, main_byo BOOLEAN
main_color (nullable)
Cross string spec: symmetrical (cross_string_id, cross_string_one_off_text, cross_tension_kg, cross_price_chf, cross_byo, cross_color).
Self-flavor optional fields:
method (nullable)
dynamic_tension_after (nullable)
Lifecycle dates (nullable): ordered_at, strung_at, returned_at, paid_at. Causal order enforced by the app.
Pricing: labor_chf, strings_chf, total_chf (denormalized for stable receipts).
Other: comments (free text)
timestamps

The Person whose order this is can be reached via order.client_profile.person_id. Receipts use client_profile.person.display_first_name + display_last_name (modulo any per-stringer override, which would be a future add-on; not in V2).

"Pick from catalogue OR one-off free-text" pattern¶

Validation rule (app-level): exactly one of (main_string_id, main_string_one_off_text) is non-null on save; same for cross.

Indexes pinned now¶

orders(stringer_id, client_profile_id, ordered_at DESC) — supports the "search client → copy last order" prefill and the listing page's "last order by client" widget.
orders(stringer_id, strung_at DESC) — primary listing query.
orders(stringer_id, paid_at) WHERE paid_at IS NULL — partial index for the "open payments" filter.

order_shares (per-job grants — Rule #1 and Rule #2)¶

Per-order grant of read access to one specific past job. Two rules, one table.

Field	Notes
`id` (PK)
`order_id` (FK NOT NULL)	The specific past order.
`granter_kind`	Enum: `stringer` \| `person`.
`granter_stringer_id`	FK, nullable. Set iff `granter_kind=stringer`.
`granter_person_id`	FK, nullable. Set iff `granter_kind=person`.
`grantee_stringer_id` (FK NOT NULL)	Who can read.
`created_at` (NOT NULL)
`revoked_at`	Nullable. NULL = active.

CHECK constraint: exactly one of granter_stringer_id, granter_person_id is non-null and matches granter_kind.

Granter-validity invariant (chokepoint write-time check or DB trigger): - granter_kind = stringer → granter_stringer_id = order.stringer_id. - granter_kind = person → granter_person_id = order.client_profile.person_id.

Index: order_shares(grantee_stringer_id, order_id) WHERE revoked_at IS NULL — supports the OR-clause in the predicate.

Rules served:

Rule #1 (stringer-to-stringer per-job): granter_kind = stringer. Stringer A hands order O to Stringer B (e.g. "I'm on holiday").
Rule #2 (client-to-stringer per-job): granter_kind = person. The Person whose order it is hands order O to Stringer B (e.g. via the V3 client portal).

person_stringer_share (Rule #3 — client-global, future-inclusive)¶

The only future-inclusive grant. Per (Person, target Stringer).

Field	Notes
`id` (PK)
`person_id` (FK NOT NULL)	The granting Person.
`target_stringer_id` (FK NOT NULL)	Which stringer sees all of this Person's orders.
`created_at` (NOT NULL)
`revoked_at`	Nullable. NULL = active.

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

Index: person_stringer_share(target_stringer_id, person_id) WHERE revoked_at IS NULL.

Rule #3 served: an active row admits Stringer X to read every Order whose client_profile.person_id = P, including future orders strung by stringers who don't exist yet at grant time. This is the only future-inclusive mechanism in the model and is documented as such.

share_audit (append-only)¶

Single audit table for all grant-related events.

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; correlates with structured logs.
`at` (NOT NULL)
`meta`	JSONB; e.g. for `shared_read`: `{admitting_grant_kind, admitting_grant_id}`.

shared_read is logged at chokepoint-admit time, NOT in the handler — one row per request per share-admitted order. Append-only by convention; future hardening can add a Postgres trigger refusing UPDATE/DELETE.

person_merges (designed now, built later)¶

Records that one duplicate Person was merged into another. Soft-merge — Person rows are not deleted.

Field	Notes
`id` (PK)
`surviving_person_id` (FK NOT NULL)
`merged_person_id` (FK NOT NULL)	Marked merged; `Person.merged_into = surviving_person_id`.
`merged_at` (NOT NULL)
`merged_by_admin_id` (FK NOT NULL)
`reason`	Free text.

Implementation chosen: on-write fan-out at merge time (UPDATE every client_profiles.person_id, every order_shares.granter_person_id, every person_stringer_share.person_id from merged_person_id to surviving_person_id, then mark persons.merged_into). Reads stay simple. Cost: one transaction at merge time.

The merge admin UI is V2.x; the data shape is committed now to keep the chokepoint, the audit, and the share semantics consistent.

CatalogueSubmission¶

Unchanged. Tracks the lifecycle of a stringer-created Racket or String moving from private_to_stringer to shared.

id (PK), catalogue_kind, catalogue_row_id, submitted_by_stringer_id, status, reviewed_by_admin_id, reviewed_at, notes, timestamps.

What's NOT in V2¶

No notifications dispatch table (V3).
No magic_link_tokens table (V3 — gotrue handles).
No payment/invoice tables (out of scope per Topic 4 W1).
No payments/invoicing.
No Person merge admin UI yet — table shape lands now; UI lands V2.x.

Migration shape (one-shot ETL — V2 cutover, for Vera)¶

V1 has one stringer (Stefan); the migration is trivial under the new model.

Create Stefan's Stringer row with role = 'admin', default_locale = 'en'.
Create Stefan as a Person (with email) + a ClientProfile under Stefan with is_self_for_stringer = TRUE.
For each distinct (Last Name, First Name) in Client-Stringing-Orders:
Create one Person (email if known and verified separately, else email IS NULL, no claim_token).
Create one ClientProfile under Stefan, with is_self_for_stringer = FALSE. Migrate any V1-flagged note text into internal_notes.
Seed no order_shares rows. Seed no person_stringer_share rows. V1 is single-tenant; nothing to share.
Catalogue seeding (Strings, Rackets, CatalogueSubmissions) is unchanged from the prior plan.
Walk Lists.My Racquets → Racket rows owned by Stefan's self-ClientProfile.
Insert orders pointing at the new client_profile_id. No data loss vs. the prior model.

Validate row counts (329 + 416) and per-year revenue against V1 Stats sheet within rounding.