Contributor review

Purpose

Records a client’s evaluation of a Contributor who led a session they attended. The review targets the contributor’s global User identity — not a per-gym companyMember — so the contributor’s reputation is portable across every company they work for. A confirmed Booking row is required as eligibility proof; no booking means no review.

Identity & key fields

• Primary key: id (uuid, default gen_random_uuid()).
• targetUserId (uuid, NOT NULL) — soft cross-schema ref to users.users.id. The contributor being reviewed.
• reviewerUserId (uuid, nullable) — soft cross-schema ref to users.users.id. NULL means the reviewer’s user row was later redacted; rendered as “Former user” in UI.
• bookingId (uuid, NOT NULL) — soft cross-schema ref to bookings.bookings.id. Eligibility proof; also the UNIQUE partner for targetUserId.
• rating (smallint, NOT NULL) — integer 1..5. CHECK constraint at DB level.
• body (text, nullable) — optional free-text, max 2000 chars enforced at API.
• hiddenAt (timestamptz, nullable) — soft-hide flag for future moderation. Queries MUST filter hidden_at IS NULL.
• createdAt, updatedAt (timestamptz, NOT NULL).

UNIQUE constraint contributor_reviews_booking_target_unique on (bookingId, targetUserId) — one review per booking per contributor.

Invariants

• rating BETWEEN 1 AND 5 — DB CHECK constraint contributor_reviews_rating_check (enforced in tktspace-backend/libs/shared/data-access-db/src/lib/schema/activities.schema.ts).
• UNIQUE (bookingId, targetUserId) — prevents duplicate reviews for the same booking+contributor pair; also resolves the AC-10 background-retry race via INSERT … ON CONFLICT DO NOTHING (enforced in tktspace-backend/libs/shared/data-access-db/src/lib/schema/activities.schema.ts, race resolution in contributor-reviews.service.ts).
• targetUserId is a cross-schema soft ref to users.users(id) — no Drizzle .references() per ADR cross-schema-references-without-fk. Service validates EXISTS users.users WHERE id = :targetUserId before INSERT (AC-2 step 6). Reads LEFT JOIN and skip orphans (enforced in tktspace-backend/libs/features/activities/src/lib/services/contributor-reviews.service.ts).
• reviewerUserId is a cross-schema soft ref to users.users(id) — nullable; NULL = former user. No FK per ADR. The user-deletion service is responsible for setting reviewer_user_id = NULL when redacting a user (product spec only — see specs/contributor-reviews-ratings.md § Decisions locked).
• bookingId is a cross-schema soft ref to bookings.bookings(id) — no FK per ADR. Service validates booking exists and caller owns it before INSERT (AC-2 step 2). Cascade caveat: bookings.bookings.customer_id → company_customer is ON DELETE CASCADE inside the bookings schema; when a company-customer is hard-deleted, its bookings cascade-delete, leaving review rows pointing at a vanished bookingId. The customer-deletion service SHOULD write hidden_at = now() for affected reviews (product spec only — see specs/contributor-reviews-ratings.md § AC-1 Invariants).
• hiddenAt is monotonic v1 — set once, never cleared. The partial index contributor_reviews_target_visible_idx (WHERE hidden_at IS NULL) relies on this. A future “unhide” flow must redesign the index (product spec only — see specs/contributor-reviews-ratings.md § Decisions locked § hidden_at monotonicity).
• Edit window — PATCH is only allowed within 7 days of createdAt. Enforced server-side in contributor-reviews.service.ts (product spec only — see specs/contributor-reviews-ratings.md AC-3).
• Every read query MUST filter hidden_at IS NULL — future moderation soft-hide is assumed (enforced in tktspace-backend/libs/features/activities/src/lib/services/contributor-reviews.service.ts).

Lifecycle

No status column. The effective visibility gate is hidden_at.

INSERT (POST /api/client/contributor-reviews)    → row exists, hidden_at IS NULL (visible)
PATCH  (within 7d of createdAt)                  → updatedAt bumped; row stays visible
DELETE (within 7d of createdAt)                  → row hard-deleted
hidden_at set by moderation / deletion service   → row excluded from all reads (soft-hidden)

The “edited” badge: UI renders an “edited Nd ago” pill only when updatedAt − createdAt > 60s (product spec only — see specs/contributor-reviews-ratings.md § Decisions locked).

Relationships

• User (target / contributor) (ENT-021) — targetUserId, soft cross-schema ref. N:1. The contributor whose reputation this review contributes to.
• User (reviewer) (ENT-021) — reviewerUserId, soft cross-schema ref, nullable (NULL = “Former user”). N:1, optional.
• Booking (ENT-003) — bookingId, soft cross-schema ref. N:1. The booking that proves eligibility; UNIQUE partner with targetUserId.
• Contributor (ENT-010) — read-only join: contributor eligibility is validated via sessionCoaches or activity-level contributors, joined through companyMember.user_id = targetUserId. No FK on this table.

API surfaces

Surface	Exposed	Notes
client	yes — POST /contributor-reviews (AC-2), PATCH /contributor-reviews/{id} (AC-3), DELETE /contributor-reviews/{id} (AC-4), GET /contributors/{userId}/rating-summary (AC-5), GET /contributors/{userId}/reviews (AC-6), GET /bookings/{bookingId}/reviews-eligibility (AC-7). Also embedded as ratingSummary: RatingSummaryPreviewDto per contributor on GET /activities/{id} (AC-9).	Swagger UI
business	yes — read-only: GET /companies/{companyId}/contributors/{userId}/reviews (AC-8). Guarded by CompanyRolesGuard + Permission.READ_REVIEWS. Cache-Control: no-store.	Swagger UI
super-admin	no	—

Per-surface field differences:

• Client ContributorReviewDto includes bookingId, targetUserId, createdAt, updatedAt, and author preview (globalName, avatarUrl). Write variants (ContributorReviewCreateBodyDto, ContributorReviewPatchBodyDto) are client-only.
• Business ContributorReviewDto is declared independently (same field names, intentional duplication per “never share types between surfaces” rule). No bookingId/sessionId in the business response — admins filter by company scope; the deep booking link is not needed for moderation v1.
• RatingSummaryDto (full, with histogram) is on the client surface (GET /contributors/{userId}/rating-summary). The activity-detail embed uses RatingSummaryPreviewDto (narrower: average, count only — no histogram).
• COACH role does NOT receive Permission.READ_REVIEWS — coaches read their own reviews via the public client surface (AC-6). OWNER, ADMIN, MANAGER receive it.

Known gotchas / open questions

• No DB cascades. All three references (targetUserId, reviewerUserId, bookingId) are bare uuid columns — no FK, no ON DELETE rule. The application layer (user-deletion service, customer-deletion service) is responsible for writing hidden_at/null on those paths. A forgotten UPDATE in the deletion path will leave orphan or unmasked review rows until the next audit.
• contributor_reviews_target_visible_idx is a partial index (WHERE hidden_at IS NULL). Verify the pinned Drizzle version emits the WHERE clause correctly in drizzle-kit generate; if not, the generated migration must be hand-edited with a one-line drift comment -- drizzle-kit drift: partial index added by hand (per specs/contributor-reviews-ratings.md AC-1 caveat and feedback_drizzle_migrations).
• Offline contributors (companyMember.userId IS NULL) are structurally unreviewable. AC-9 omits ratingSummary entirely for them (field absent, not null). Consumers must branch on field presence, not on count === 0.
• userActivityFavorites pre-existing ADR violation. That row does declare a cross-schema FK to users.users(id). Do NOT treat it as a pattern to copy — it is a known ADR violation tracked for a separate cleanup ticket. This entity’s schema deliberately does not replicate it.
• Edit/delete window is 7 days, server-side only. Clock skew between client and server is not handled — the server’s now() is authoritative. The UI should grey out the edit button 6 days 23 hours after createdAt as a soft guide, but the server is the gate.
• OPEN: the ADR proposes a new libs/features/reviews/ feature library, but the implementation landed inside libs/features/activities/. The source paths above reflect the actual implementation. If the lib is later extracted, the sources: frontmatter on this doc must be updated.

Recommendations

Forward-looking improvements — not currently in place.

• Materialised aggregate table (user_public_profile_stats or similar) for reviewCount / ratingAverage — deferred until contributor_reviews row count crosses ~1M. The on-read LATERAL JOIN is fast enough today.
• Contributor reply — reply_body / reply_at columns on this table or a sibling contributor_review_replies table. Planned follow-up.
• Moderation surface on super-admin — read + hide UI for cross-company oversight. hiddenAt column is already present; a future ticket adds the endpoints.
• Push notification for “rate your contributor” — the deep-link target (tktspace://bookings/{bookingId}/review) is wired; the push emission belongs to a separate bullmq-queues-notifications-sessions follow-up ticket.
• reviewer_user_id index — no index today (spec AC-1 note: no current query filters by reviewer alone). If a “list my reviews” endpoint lands in a follow-up, add a standalone index on reviewer_user_id.