Data Model

The conceptual data model for V2. Field names are illustrative, not normative — final naming is for the implementation team. Cross-cuts: V2 scope (locked decisions), client identity & sharing (authoritative for identity + sharing), use cases, glossary.

Identity model. This page describes the conceptual entities. The platform-level identity primitive is Person; the per-stringer view of a Person is ClientProfile. The original V2 baseline used a single Player entity (one row per (stringer, human)); that was superseded by client identity & sharing on 2026-05-02. Schema-level shape is owned by Theo — see ADR-0004 and docs/architecture/data-model.md.

Entities at a glance

Stringer (admin-onboarded)
  └── ClientProfile (per-stringer view of a Person)
        └── Racket (per-ClientProfile, optional serial/instance ID)
              └── Order (unified self+client, lifecycle dates)

Identity (platform-level):
  Person  ◄── one per real human; ClientProfiles reference it.

Catalogue:
  Racket (catalogue-private | catalogue-shared | one-off)
  String (catalogue + manual override)

Workflow:
  Catalogue submission (request-queue → admin promote/reject)
  Sharing — three rules (see client identity & sharing)

Data isolation is strict per Stringer by default. The cross-stringer surfaces are:

• the shared catalogue (Strings + shared Rackets);
• the catalogue submission queue (visible to admin);
• the three sharing rules for stringing-job data — Rule 1 stringer-initiated per-job (V2-scoped, was M18), Rule 2 client-initiated per-job (V3 portal), Rule 3 client global preference (V3 portal). See client identity & sharing for the authoritative requirements.

Person (platform-level identity)

One row per real human. Owned by the platform — no stringer_id.

Field	Notes
id	Stable internal ID.
email	Optional. Required for promotion to a verified Person via the magic-link claim flow (see Email — required vs. optional).
email_verified_at	Set when the Person verifies their email (gotrue magic-link click). NULL on stringer-created drafts that have not yet been claimed.
display_first_name, display_last_name	Required. The person's preferred public name (used on receipts; the only PII surface visible under Rule 1 redaction — see client identity & sharing — visibility/redaction matrix).
default_locale	EN | DE. Reserved for the V3 client-facing portal: when set, wins over the client's browser Accept-Language header for their own portal UI; falls back to browser locale otherwise. Not used in V2. See NFR-7.
notification_prefs	Per-channel opt-in/out (email default; SMS later). Channel-keyed JSONB.
claim_token	Optional single-use token issued at draft-creation; consumed when the Person magic-links in to claim the record.
created_at, updated_at	Audit.

Privacy invariant: Person carries only data the human consents to make platform-public (display name, claimed email, locale, notification prefs). Stringer-private metadata never lives here — it lives on ClientProfile. See client identity & sharing for the rationale.

Identity-matching when adding a client: strict verified-email match only. Never auto-match on name, phone, or any other field. See client identity & sharing — identity matching for the full rule and edge cases.

Email — required vs. optional

A subtle but load-bearing rule:

• Optional at the ClientProfile level. A stringer may add a client without an email — useful for walk-in clients, kids whose parents handle email, or anyone who simply hasn't shared one. The Person row created in this case has email IS NULL and no claim_token; it is a draft Person owned only by the inviting stringer.
• Required for promotion to an identified (verified) Person. Cross-stringer identity matching, the V3 client portal login, and any client-initiated sharing (Rule 2 / Rule 3) all require the Person to have a verified email. A draft Person with no email cannot be claimed and remains stringer-scoped forever — this is the platform's privacy-safe default state.

Stringers can backfill an email onto a draft Person at any time; doing so issues a claim_token and unlocks the magic-link claim flow.

ClientProfile (stringer-scoped view of a Person)

Per-stringer record carrying everything stringer-private about a given Person.

Field	Notes
id	Stable internal ID.
stringer_id	Owning stringer (FK).
person_id	The Person this profile is about (FK).
nickname	Stringer-private (e.g. "the lefty kid", "Mr. RPM").
internal_notes	Stringer-private free-text notes.
default_tension_memo	Stringer-private (e.g. "always wants 25/24, dislikes Solinco").
is_self_for_stringer	Boolean. 0..1 per stringer. Marks the ClientProfile that represents the stringer themselves (the "self-Player" slot from the locked decisions — preserved by name in V2 scope, but it is now a ClientProfile, not a separate Player entity).
notification_prefs	Optional per-client overrides on top of Person.notification_prefs (e.g. "this client prefers SMS over email even though their default Person pref is email").
created_at, updated_at	Audit.

Uniqueness: at most one ClientProfile per (stringer, person) pair. At most one ClientProfile per stringer with is_self_for_stringer = TRUE.

Notes

• Locked decision 1 (the "self vs client" distinction) lives here, on the ClientProfile, via is_self_for_stringer.
• Same human client of two different stringers = one Person (matched on verified email) + two ClientProfile rows (one per stringer). Cross-stringer visibility of stringing-job data is via the three sharing rules — see client identity & sharing. Identity matching is verified-email only; never on name or phone.

Racket

A single entity covers Stefan's own rackets and every client's racket (locked decision 4).

Field	Notes
id	Stable internal ID.
owner_client_profile_id	FK → ClientProfile. Stefan's rackets are owned by his self-ClientProfile; client rackets by the respective client's ClientProfile.
make, model, version	E.g. Wilson / Blade 98 / V8.
head_size, string_pattern	E.g. 98 sq in / 16x19.
serial_number / instance_id	Optional. Disambiguates same-model duplicates owned by one ClientProfile.
visibility	Enum: catalogue-private (default — visible only to creating stringer), catalogue-shared (admin-promoted; in global catalogue), one-off (attached only to a specific order, never offered as a catalogue entry).
year	Optional (matches V1 Lists.My Racquets).
current	In-rotation flag (matches V1; primarily used for self-rackets).
created_at, updated_at	Audit.

Notes

• A racket model in the catalogue (catalogue-shared) is a template. Each ClientProfile's specific instance is a separate Racket row owned by that ClientProfile. Optional serial_number / instance_id distinguishes "Stefan's PA98 #1" from "Stefan's PA98 #2".
• V1 Lists.My Racquets migrates as Racket rows with owner_client_profile_id = Stefan's self-ClientProfile.
• Client rackets in V1 are free-text — migrated as catalogue-private rows owned by the respective client ClientProfile.

String

Catalogue-driven, with manual override.

Field	Notes
id	Stable internal ID.
manufacturer	E.g. Luxilon.
model	E.g. ALU Power.
gauge	E.g. 1.25.
visibility	Same enum as Racket: catalogue-private / catalogue-shared / one-off.
created_by_stringer_id	Origin (used by the moderation queue).
created_at, updated_at	Audit.

Manual override. When a stringer enters a string spec not in the catalogue, they can save it as catalogue-private or one-off. They can later mark it request shared → it enters the catalogue submission queue.

Order

The unified entity (locked decisions 1–3). One row per stringing job.

Field	Notes
id	Stable internal ID.
stringer_id	Owning stringer (FK).
client_profile_id	FK → ClientProfile. If client_profile.is_self_for_stringer, it's a self-job. The Person whose order it is reaches via order.client_profile.person_id.
racket_id	FK → Racket (must be owned by client_profile_id).
Lifecycle dates	All present on every order; blank-allowed for self-jobs (locked decision 3).
ordered_at	Date the order was placed.
strung_at	Date stringing was completed. Receipt is final at this moment.
returned_at	Date returned to the player.
paid_at	Date payment received.
Main string
main_string_id	FK → String (or null if free-text override).
main_string_text	Free-text fallback (manual override).
main_tension	Numeric (kg).
main_byo	Boolean (locked decision: per-side flag, M11).
main_price	Numeric (CHF). Excluded from totals if main_byo.
Cross string	Same shape as main. cross_* mirror main_*. UI defaults cross = main.
Pricing
labor	Numeric (CHF).
strings_subtotal	Derived: sum of non-BYO side prices.
total	Derived: labor + strings_subtotal.
Optional self-fields (locked decision 2; nullable on every order)
main_color, cross_color	Per-side string color.
method	E.g. "Standard".
dynamic_tension_after	Post-stringing DT reading.
comments	Free text.
created_at, updated_at	Audit.

Business rules

• Causal date ordering: ordered_at ≤ strung_at ≤ returned_at ≤ paid_at where present (V1's implicit rule, now enforced by V2).
• Receipt PDF is regeneratable from the row at any time (M13).
• "Done" derives from non-null returned_at AND paid_at.

Stringer

Admin-onboarded only — no self-signup (M3).

Field	Notes
id	Stable internal ID.
email	Onboarding magic-link target.
display_name	Self-filled on first login.
notification_template	Stringer-level default for notification messaging. Per-client overrides on ClientProfile.
default_locale	EN or DE (M19). Stringer-saved preference; wins over the browser Accept-Language header for UI strings when the stringer is logged in. See NFR-7.
password_hash	Optional. Set when the stringer opts to enable password sign-in (M21). Null for stringers who only use magic-link.
is_admin	True for Stefan. Admins moderate the catalogue queue.
created_at, updated_at	Audit.

Receipt-backing business-identity fields (business_name, phone, business_address, logo, optional receipt_thankyou_text) are scoped on the receipt content page; they land on this entity. Onboarding collects them via the stringer-lifecycle requirements (filed separately as racket-book#84).

Catalogue submission

Backs the request-queue moderation flow (M17, UC-7).

Field	Notes
id	Stable internal ID.
target_type	string or racket.
target_id	FK → String or Racket (currently catalogue-private).
submitted_by_stringer_id	Origin.
submitted_at	Timestamp.
status	pending / promoted / rejected.
decided_by_admin_id, decided_at, decision_note	Audit.

On promotion, the underlying Racket / String row's visibility flips to catalogue-shared. Admin (Stefan) sees a notification + unread-badge count of pending submissions.

Sharing — three rules (replaces the original "Player share" table)

The original V2 baseline modelled cross-stringer sharing as a single player_shares table (one snapshot per share, stringer-initiated only). That model was superseded on 2026-05-02 by the three sharing rules in client identity & sharing:

• Rule 1 — Stringer-initiated job share (V2-scoped, was M18). Per-job, past only, redacted per the visibility/redaction matrix.
• Rule 2 — Client-initiated job share (V3 portal). Per-job, past only, full client identity exposed, pricing visible.
• Rule 3 — Client global preference (V3 portal). All past + future jobs, the only future-inclusive mechanism.

Schema for the share tables (order_shares, person_stringer_share, share_audit) is owned by Theo — see ADR-0004 and docs/architecture/data-model.md. All three rules grant read-only access; revocation is server-side instant; already-emitted artifacts (printed receipts, exported XLSX/JSON) cannot be recalled. Every grant create + revoke + read of a shared job is audit-logged.

V3 schema hooks (data-only in V2)

Per v3-vision § V2 hooks to consider landing now: a small set of columns is reserved on V2 entities so V3 features (sign-off workflow, client portal, notification dispatch) light up without a retroactive migration on a live multi-stringer dataset. No V2 UI surfaces these columns; no V2 code path reads or writes them. They ship in alembic/versions/0012_v3_schema_hooks_bundle.py.

Hook	Column	Type	Default	Notes
B1 (#34)	ClientProfile.signoff_pref	VARCHAR(16) (CHECK)	'require'	Client-side sign-off preference ('require' | 'skip'). Lives on ClientProfile because v3-vision § Q4 pins the preference as per-stringer (Player rows are per-stringer). V3 portal C1 surfaces this.
B2 (#35)	ClientProfile.stringer_signoff_override	VARCHAR(16) (CHECK)	NULL	Stringer's per-client override of the stringer-level default. NULL = "fall through to Stringer.signoff_default". Decision rule: effective = override ?? stringer_default.
B3 (#36)	Stringer.signoff_default	VARCHAR(16) (CHECK)	'require'	Stringer-level default. Falls through when the per-client override is NULL.
B4 (#37)	Order.signed_off_at	timestamptz	NULL	Stamped by the V3 client portal at sign-off confirmation. Per v3-vision § Q12, the V3-launch backfill sets signed_off_at = strung_at on already-Strung V2 orders.
B4 (#37)	Order.signoff_required	boolean	false	Snapshot of the OR-logic at order-creation time so post-creation pref edits don't retroactively change whether existing orders need sign-off. V3-launch backfill flips this to true on existing rows.
B5 (#38)	Stringer.role enum value 'client'	enum	n/a	The Postgres stringer_role enum already includes 'client' (since 0001_baseline_identity_and_sharing.py). V2 code never assigns the value; reserved for the V3 client portal.
B6 (#39)	Person.gotrue_user_id	UUID (UNIQUE, indexed)	NULL	Already exists on Person since baseline (0001_baseline_identity_and_sharing.py L179). The V3 client portal binds this to the gotrue user UUID on first magic-link sign-in. No FK — the gotrue users table may live in a separate schema/database; FK decision deferred to V3.
B8 (#41)	Person.notification_prefs JSONB key set	docs-only	{}	The blob is JSONB so V3 channels add keys without a schema migration. The canonical key set is documented in docs/architecture/notification-prefs.md as the single source of truth. V2 reads email only; V3 reads every documented key.
B9 (#42)	Stringer.notification_template	TEXT	NULL	Stringer-level notification template, edited in the V3 dispatcher UI. Per Stefan's "Do it empty." lock 2026-05-01: column ships NULL in V2; V3 fills the editor.

Design rationale (sign-off value-set): 'require' / 'skip' are shipped as VARCHAR(16) columns with CHECK constraints, NOT as a Postgres ENUM. Two reasons: (a) the value-set is small and stable, so a CHECK reads identically to an enum in \d+; (b) CHECK constraints evolve more cheaply than enums under PgBouncer transaction-pooling — a future "add a third value" is a one-line ALTER TABLE ... DROP/ADD CONSTRAINT rather than ALTER TYPE ... ADD VALUE (which is non-transactional in some Postgres configs and refuses to run inside a transaction block).

The V3 OR-logic (from v3-vision § B1):

Sign-off is required iff client_pref == 'require' OR stringer_effective == 'require'. Mutual opt-out is the only state that skips it.

stringer_effective = stringer_signoff_override ?? Stringer.signoff_default