Database Schema

Database Schema

Trystpilot uses PostgreSQL with two primary tables (profiles, reviews) plus supporting enums and a materialized-view-compatible aggregation pattern. Zip codes are stored internally for deduplication but are never exposed in API responses — only the derived city/region label is public. All UUIDs are generated by PostgreSQL’s gen_random_uuid(). The schema lives in lib/db/schema.sql.

erDiagram profiles { uuid id PK varchar alias_name varchar city varchar zip_code "internal only — never public" profile_category category decimal overall_rating int review_count decimal reputation_score moderation_status moderation_status timestamp created_at } reviews { uuid id PK uuid profile_id FK varchar anonymous_hash_id int rating_overall int communication_rating int empathy_rating int trustworthiness_rating int respect_rating int compatibility_rating relationship_duration relationship_duration text review_text decimal flagged_score moderation_status moderation_status timestamp created_at } removal_requests { uuid id PK uuid profile_id FK text reason varchar contact_hash "hashed, never stored plain" request_status status timestamp created_at } abuse_reports { uuid id PK uuid review_id FK varchar reporter_hash text reason report_status status timestamp created_at } profiles ||--o{ reviews : "has" profiles ||--o{ removal_requests : "subject of" reviews ||--o{ abuse_reports : "reported via"

Enum Types

Enum	Values
profile_category	short_term, long_term, long_distance, situationship, fwb
moderation_status	pending, approved, rejected, quarantined
relationship_duration	less_than_6mo, 6_to_12mo, 1_to_2yr, 2_to_5yr, 5_plus_yr
request_status	pending, approved, rejected
report_status	pending, reviewed, actioned

Reputation Score Computation

The reputation score is recomputed on every approved review insert/update:

score = (overall × 0.35)
      + (communication × 0.20)
      + (trustworthiness × 0.20)
      + (empathy × 0.15)
      + (compatibility × 0.10)

Multiplied by a recency factor per review: 1.0 if ≤ 30 days old, decaying linearly to 0.5 at ≥ 180 days.

Known bug: respect_rating is collected but not included in the formula above. Fix target: Phase 1 (M1-T6 in ROADMAP).