Database Schema

Entity Relationship Diagram

erDiagram
    %% Core Authentication
    auth_users {
        uuid id PK
        string email
        jsonb raw_user_meta_data
    }

    %% User Management
    profiles {
        uuid id PK,FK "references auth.users"
        text name
        text avatar_url
        text push_token
        boolean is_premium
        uuid couple_id FK
        timestamptz created_at
        timestamptz updated_at
    }

    couples {
        uuid id PK
        text invite_code UK
        timestamptz created_at
    }

    %% Content
    question_packs {
        uuid id PK
        text name
        text description
        text icon
        boolean is_premium
        boolean is_public
        content_review_status content_status "allowed required for customer access"
        int sort_order
        timestamptz created_at
    }

    questions {
        uuid id PK
        uuid pack_id FK
        text text
        text partner_text "for two-part questions"
        text question_type "swipe/text_answer/audio/photo/who_likely"
        jsonb config "type-specific configuration"
        int intensity "1-5"
        text[] allowed_couple_genders "couple composition filter"
        text[] target_user_genders "individual user filter"
        text[] required_props "props/accessories required"
        content_review_status content_status "allowed required for customer access"
        timestamptz created_at
    }

    %% Couple Pack Selection
    couple_packs {
        uuid couple_id PK,FK
        uuid pack_id PK,FK
        boolean enabled
        timestamptz created_at
    }

    %% User Interaction
    responses {
        uuid id PK
        uuid user_id FK
        uuid question_id FK
        uuid couple_id FK "nullable: null is a sealed solo answer, claimed at pairing"
        answer_type answer "yes/no/maybe"
        jsonb response_data "non-swipe answer payload"
        timestamptz created_at
    }

    matches {
        uuid id PK
        uuid couple_id FK
        uuid question_id FK
        match_type match_type "yes_yes/yes_maybe/maybe_maybe/both_answered"
        jsonb response_summary "both non-swipe payloads by user"
        boolean is_new
        timestamptz created_at
    }

    messages {
        uuid id PK
        uuid match_id FK
        uuid user_id FK
        text content
        text media_path
        timestamptz read_at
        timestamptz created_at
    }

    %% Subscriptions
    subscriptions {
        uuid id PK
        uuid user_id FK
        text revenuecat_app_user_id
        text product_id
        subscription_status status
        text[] entitlement_ids
        timestamptz purchased_at
        timestamptz expires_at
        text original_transaction_id
        text store
        boolean is_sandbox
        text cancel_reason
        timestamptz grace_period_expires_at
        timestamptz created_at
        timestamptz updated_at
    }

    revenuecat_webhook_events {
        uuid id PK
        text event_id UK
        text event_type
        text app_user_id
        timestamptz processed_at
        jsonb payload
    }

    %% Feedback
    feedback {
        uuid id PK
        uuid user_id FK
        feedback_type type "bug/feature_request/general"
        text title
        text description
        text screenshot_url
        jsonb device_info
        feedback_status status
        text admin_notes
        timestamptz created_at
        timestamptz updated_at
    }

    %% Relationships
    auth_users ||--|| profiles : "extends"
    couples ||--o{ profiles : "has members"
    couples ||--o{ couple_packs : "selects packs"
    couples ||--o{ responses : "has responses"
    couples ||--o{ matches : "has matches"

    question_packs ||--o{ questions : "contains"
    question_packs ||--o{ couple_packs : "selected by"

    questions ||--o{ responses : "answered by"
    questions ||--o{ matches : "generates"

    profiles ||--o{ responses : "submits"
    profiles ||--o{ feedback : "submits"
    profiles ||--o{ subscriptions : "has"

    matches ||--o{ messages : "has chat"
    auth_users ||--o{ messages : "sends"

Enums

Enum	Values
`answer_type`	`yes`, `no`, `maybe`
`question_type`	`swipe`, `text_answer`, `audio`, `photo`, `who_likely`
`match_type`	`yes_yes`, `yes_maybe`, `maybe_maybe`, `both_answered`
`feedback_type`	`bug`, `feature_request`, `general`
`feedback_status`	`new`, `reviewed`, `in_progress`, `resolved`, `closed`
`subscription_status`	`active`, `cancelled`, `expired`, `billing_issue`, `paused`
`content_review_status`	`unreviewed`, `allowed`, `archived`
`content_entity_type`	`categories`, `question_packs`, `questions`, `dare_packs`, `dares`

Catalogue review metadata

categories, question_packs, questions, dare_packs, and dares carry content_status, content_review_reason, content_reviewed_at, and content_reviewed_by. Insert triggers force new rows to unreviewed with empty review metadata even when a caller supplies forged values. Status changes and same-status re-reviews require a non-blank reason, only super-admin users may make an authenticated decision, reviewer identity/time are database-managed, and visible content edits reset an unchanged decision to unreviewed.

content_reviews is the append-only status-change history. Only super-admins may read it through the authenticated API. Customer catalogue RLS and the legacy recommendation/teaser RPCs fail closed: a category, pack, and question or dare must all be allowed. unreviewed and archived content remains available to administrators but is not returned to customer clients. Service-role response functions independently reject questions unless both question and pack are allowed.

Key Flows

Question interactions

questions.question_type defaults to swipe; questions.config holds type-specific settings. A non-swipe response keeps its payload in responses.response_data. When a non-swipe interaction resolves as both_answered, matches.response_summary holds both payloads keyed by user ID. The complete interaction and match contract is maintained in Question interaction types.

Response & Match Flow

User submits a response to the standalone API at POST /v1/responses
Response saved to responses table (UPSERT on user_id, question_id)
API checks for the partner's response on the same question
API applies the question interaction contract to create, update, or remove a match
Match triggers push notification to both partners
Match unlocks chat thread in messages table

Without a couple yet, step 3 has no partner response to check: the response is saved with couple_id IS NULL (a sealed answer) and no match is created. See Solo answering and sealed answers.

Couple Pairing Flow

First user creates couple, gets invite_code
Second user joins via manage-couple edge function with invite code
Both profiles' couple_id set to same couple
Every sealed answer (couple_id IS NULL) belonging to either member is claimed into the couple, and matches are computed for every question both members have now answered

Pack Selection Flow

Couples can enable/disable packs via couple_packs junction table
get_recommended_questions() filters by enabled packs and the universal allowed-content boundary
Two-part questions show partner_text to second responder

Question Targeting System

Questions can be targeted at specific audiences using two complementary filters:

Field	Purpose	Level	Example
`allowed_couple_genders`	Couple composition filter	Couple	`['male+female', 'female+female']`
`target_user_genders`	Individual user filter	User	`['female', 'non-binary']`

How targeting works:

Couple filter (allowed_couple_genders): Questions only shown to couples matching the composition (e.g., same-sex specific questions)
User filter (target_user_genders): Controls who sees the question as initiator (first responder)
- If user's gender matches → they can see and answer first
- If user's gender doesn't match → they only see it after partner answers (via partner_text)

Example flow:

Question:
  text: "Have your partner suck your tits"
  partner_text: "Suck your partner's tits"
  target_user_genders: ['female']

1. Female sees "Have your partner suck your tits" → swipes YES
2. Male sees "Suck your partner's tits" (partner_text, unlocked by her answer)
3. Both answered → Match created!

This ensures gender-appropriate phrasing while still allowing both partners to respond.

8.6 KiB Raw Permalink Blame History