Compass Database Schema Documentation

Overview

This document provides comprehensive documentation for the Compass database schema, which is built on PostgreSQL using Supabase. The schema is designed to support a transparent dating platform that facilitates deep, authentic connections based on shared values, interests, and personality compatibility.

Database Structure

The database consists of multiple interconnected tables that store user profiles, messaging data, compatibility scores, events, and other community features. All tables use appropriate indexing and Row Level Security (RLS) policies for performance and security.

Core Tables

Users Table

The primary table storing basic user information.

CREATE TABLE users (
    created_time TIMESTAMPTZ DEFAULT now() NOT NULL,
    data JSONB NOT NULL,
    id TEXT DEFAULT random_alphanumeric(12) NOT NULL,
    name TEXT NOT NULL,
    name_username_vector tsvector GENERATED ALWAYS AS (
        to_tsvector('english', (name || ' '::text) || username)
    ) STORED,
    username TEXT NOT NULL,
    CONSTRAINT users_pkey PRIMARY KEY (id)
);

Columns:

created_time: Timestamp when user account was created
data: JSONB field containing additional user metadata
id: Unique identifier for the user
name: User's display name
name_username_vector: Generated tsvector for full-text search on name and username
username: Unique username for the user

Indexes:

Primary key on id
Index on username
Index on created_time
Index on name
GIN index on name_username_vector for full-text search

Profiles Table

Contains detailed profile information for users.

CREATE TABLE profiles (
    age INTEGER NULL,
    bio JSONB,
    bio_length integer null,
    birth_date DATE,
    born_in_location TEXT,
    city TEXT,
    city_latitude NUMERIC(9, 6),
    city_longitude NUMERIC(9, 6),
    comments_enabled BOOLEAN DEFAULT TRUE NOT NULL,
    company TEXT,
    country TEXT,
    created_time TIMESTAMPTZ DEFAULT now() NOT NULL,
    diet TEXT[],
    disabled BOOLEAN DEFAULT FALSE NOT NULL,
    drinks_per_month INTEGER,
    education_level TEXT,
    ethnicity TEXT[],
    gender TEXT,
    geodb_city_id TEXT,
    has_kids INTEGER,
    headline TEXT,
    height_in_inches float4,
    id BIGINT GENERATED ALWAYS AS IDENTITY NOT NULL,
    image_descriptions jsonb,
    is_smoker BOOLEAN,
    last_modification_time TIMESTAMPTZ DEFAULT now() NOT NULL,
    looking_for_matches BOOLEAN DEFAULT TRUE NOT NULL,
    allow_direct_messaging BOOLEAN DEFAULT TRUE NOT NULL,
    allow_interest_indicating BOOLEAN DEFAULT TRUE NOT NULL,
    occupation TEXT,
    occupation_title TEXT,
    photo_urls TEXT[],
    pinned_url TEXT,
    political_beliefs TEXT[],
    political_details TEXT,
    pref_age_max INTEGER NULL,
    pref_age_min INTEGER NULL,
    pref_gender TEXT[],
    pref_relation_styles TEXT[],
    pref_romantic_styles TEXT[],
    raised_in_city TEXT,
    raised_in_country TEXT,
    raised_in_geodb_city_id TEXT,
    raised_in_lat NUMERIC(9, 6),
    raised_in_lon NUMERIC(9, 6),
    raised_in_radius INTEGER,
    raised_in_region_code TEXT,
    referred_by_username TEXT,
    region_code TEXT,
    relationship_status TEXT[],
    religion TEXT[],
    religious_belief_strength INTEGER,
    religious_beliefs TEXT,
    twitter TEXT,
    university TEXT,
    user_id TEXT NOT NULL,
    visibility profile_visibility DEFAULT 'member'::profile_visibility NOT NULL,
    wants_kids_strength INTEGER DEFAULT 0,
    website TEXT,
    CONSTRAINT profiles_pkey PRIMARY KEY (id)
);

Columns:

age: User's age. Derived, never written by hand — a trigger recomputes it from birth_date on every write, and refresh_profile_ages() (run daily by the search-alert job, or on its own via /internal/refresh-profile-ages) moves it on for anyone whose birthday has passed. Filters and indexes still use this column; it is the read shape of birth_date.
birth_date: Source of truth for age. Always the 1st of July of the birth year — we ask members for a year, never a full date of birth, and a CHECK constraint enforces it so the table cannot hold a real birthday whatever a caller sends. Excluded from list endpoints and from get-user-and-profile; see common/src/profiles/birth-date.ts.
bio: JSONB field containing detailed biography information
bio_length: Length of biography text
born_in_location: Location where user was born
city: Current city of residence
city_latitude/city_longitude: Geographic coordinates of current city
comments_enabled: Whether comments are enabled on profile
company: Current company user works for
country: Current country of residence
created_time: Timestamp when profile was created
diet: Array of dietary preferences
disabled: Whether profile is disabled
drinks_per_month: Number of alcoholic drinks consumed per month
education_level: Highest level of education achieved
ethnicity: Array of ethnic backgrounds
gender: Gender identity
geodb_city_id: ID of city in geolocation database
has_kids: Whether user has children (0 = no, 1 = yes)
headline: Short headline describing user
height_in_inches: User's height in inches
id: Auto-generated primary key
image_descriptions: JSONB containing descriptions of uploaded images
is_smoker: Whether user smokes
last_modification_time: Timestamp of last profile update
looking_for_matches: Whether user is actively seeking connections
allow_direct_messaging: Whether direct messaging is allowed
allow_interest_indicating: Whether other users can indicate interest
occupation: User's occupation
occupation_title: Specific job title
photo_urls: Array of URLs to uploaded photos
pinned_url: URL to pinned photo
political_beliefs: Array of political affiliations
political_details: Detailed political views
pref_age_max/pref_age_min: Preferred age range for matches
pref_gender: Array of preferred genders for matches
pref_relation_styles: Array of preferred relationship styles
pref_romantic_styles: Array of preferred romantic styles
raised_in_city/country: Location where user was raised
raised_in_geodb_city_id: ID of birth city in geolocation database
raised_in_lat/lon: Geographic coordinates of birth city
raised_in_radius: Radius around birth location
raised_in_region_code: Region code of birth location
referred_by_username: Username of referring user
region_code: Current region code
relationship_status: Array indicating relationship preferences
religion: Array of religious affiliations
religious_belief_strength: Strength of religious beliefs (scale)
religious_beliefs: Detailed religious views
twitter: Twitter handle
university: University attended
user_id: Foreign key to users table
visibility: Visibility level ('public' or 'member')
wants_kids_strength: Desire for children (0-10 scale)
website: Personal website URL

Relationships:

Foreign key constraint on user_id referencing users.id with CASCADE delete

Indexes:

Primary key on id
Index on user_id
Unique index on user_id
Index on last_modification_time
Index on bio_length
Spatial indexes on latitude/longitude coordinates
GIN indexes on array fields (diet, political_beliefs, etc.)
Indexes on various profile attributes for filtering

Triggers:

Automatically updates last_modification_time on profile updates

Private Users Table

Stores sensitive user information with restricted access.

CREATE TABLE private_users (
  data JSONB NOT NULL,
  id TEXT NOT NULL,
  CONSTRAINT private_users_pkey PRIMARY KEY (id)
);

Columns:

data: JSONB field containing private user data
id: Primary key matching user ID

Relationships:

Foreign key constraint on id referencing users.id with CASCADE delete

Security:

Row Level Security (RLS) policies restrict access to user's own data only

Messaging Tables

Private User Messages Table

Stores direct messages between users.

CREATE TABLE private_user_messages (
  channel_id TEXT NOT NULL,
  content TEXT NOT NULL,
  created_time TIMESTAMPTZ DEFAULT now () NOT NULL,
  data JSONB NOT NULL,
  id TEXT NOT NULL,
  reply_id TEXT,
  sender_id TEXT NOT NULL,
  updated_time TIMESTAMPTZ DEFAULT now () NOT NULL,
  CONSTRAINT private_user_messages_pkey PRIMARY KEY (id)
);

Columns:

channel_id: Identifier for the message thread/channel
content: Message text content
created_time: Timestamp when message was sent
data: JSONB field for additional message metadata
id: Unique message identifier
reply_id: Identifier of message being replied to (if applicable)
sender_id: ID of user who sent the message
updated_time: Timestamp of last message update

Indexes:

Primary key on id
Indexes on channel_id, sender_id, and timestamps for efficient querying

Private User Message Channels Table

Manages message threads/channels between users.

CREATE TABLE private_user_message_channels (
  created_time TIMESTAMPTZ DEFAULT now () NOT NULL,
  data JSONB NOT NULL,
  id TEXT NOT NULL,
  CONSTRAINT private_user_message_channels_pkey PRIMARY KEY (id)
);

Compatibility System Tables

Compatibility Questions Table

Stores predefined compatibility questions for matching users.

CREATE TABLE compatibility_prompts (
  id TEXT NOT NULL,
  created_time TIMESTAMPTZ NOT NULL DEFAULT now (),
  question TEXT NOT NULL,
  explanation TEXT NOT NULL,
  importance_score INTEGER NOT NULL DEFAULT 1,
  category TEXT,
  is_required BOOLEAN NOT NULL DEFAULT FALSE,
  display_order INTEGER NOT NULL DEFAULT 0,
  CONSTRAINT compatibility_prompts_pkey PRIMARY KEY (id)
);

Compatibility Answers Table

Stores user responses to compatibility questions.

CREATE TABLE compatibility_answers (
  id TEXT NOT NULL,
  created_time TIMESTAMPTZ NOT NULL DEFAULT now (),
  prompt_id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  answer TEXT,
  importance INTEGER NOT NULL,
  explanation TEXT,
  CONSTRAINT compatibility_answers_pkey PRIMARY KEY (id)
);

Compatibility Scores Table

Pre-calculated compatibility scores between pairs of users (user_id_1 < user_id_2). score is the overall score — dealbreaker gate, then a weighted geometric mean of profile factors and the prompt score (common/src/profiles/compatibility-factors.ts, compatibility-score.md). A row exists iff the pair is gender-compatible, at least one side is visible to the other, and no dealbreaker fires. No per-factor breakdown is stored; the browser recomputes it from the two profiles plus prompt_score*.

CREATE TABLE compatibility_scores (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  created_time TIMESTAMPTZ NOT NULL DEFAULT now (),
  modified_time TIMESTAMPTZ NOT NULL DEFAULT now (), -- trigger-maintained
  user_id_1 TEXT NOT NULL REFERENCES users (id) ON DELETE CASCADE,
  user_id_2 TEXT NOT NULL REFERENCES users (id) ON DELETE CASCADE,
  score DOUBLE PRECISION, -- overall, with ±0.0005 tie-breaking noise
  score_low DOUBLE PRECISION, -- likely range of the overall
  score_high DOUBLE PRECISION,
  shared_answers INTEGER, -- prompts both answered
  prompt_score REAL, -- the prompt-only factor and its range; NULL when no shared answers
  prompt_score_low REAL,
  prompt_score_high REAL
);

CREATE UNIQUE INDEX compatibility_scores_user_pair_unique ON compatibility_scores (user_id_1, user_id_2);

Social Features Tables

Profile Likes Table

Tracks when users "like" other profiles.

CREATE TABLE profile_likes (
  user_id TEXT NOT NULL,
  target_user_id TEXT NOT NULL,
  created_time TIMESTAMPTZ NOT NULL DEFAULT now (),
  CONSTRAINT profile_likes_pkey PRIMARY KEY (user_id, target_user_id)
);

Profile Ships Table

Tracks "ships" (strong compatibility matches) between users.

CREATE TABLE profile_ships (
  user_id TEXT NOT NULL,
  target_user_id TEXT NOT NULL,
  created_time TIMESTAMPTZ NOT NULL DEFAULT now (),
  CONSTRAINT profile_ships_pkey PRIMARY KEY (user_id, target_user_id)
);

Deleting an account removes likes and ships that name that member, in either direction. Their ID also leaves search_alert_sends.matched_user_ids, which delete-me clears itself because an array cannot have a foreign key. It adds to deleted_match_count so the alert can still say how many people are gone.

Profile Stars Table

Allows users to "star" favorite profiles.

CREATE TABLE profile_stars (
  user_id TEXT NOT NULL,
  target_user_id TEXT NOT NULL,
  created_time TIMESTAMPTZ NOT NULL DEFAULT now (),
  CONSTRAINT profile_stars_pkey PRIMARY KEY (user_id, target_user_id)
);

Profile Comments Table

Stores comments on user profiles.

CREATE TABLE profile_comments (
  id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  on_user_id TEXT NOT NULL,
  content TEXT NOT NULL,
  created_time TIMESTAMPTZ NOT NULL DEFAULT now (),
  updated_time TIMESTAMPTZ NOT NULL DEFAULT now (),
  deleted BOOLEAN NOT NULL DEFAULT FALSE,
  CONSTRAINT profile_comments_pkey PRIMARY KEY (id)
);

Donations Table

One row per donation a member made to Compass, entered by hand from /admin/donations (there is no payment webhook). The profile page shows a supporter badge with the sum of a member's donations over the trailing 365 days, computed at read time by get-donor-badge; the member can hide it with profiles.donor_badge_hidden (Settings → Data & Privacy). Rules and helpers live in common/src/donations.ts.

CREATE TABLE donations (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES users (id) ON DELETE CASCADE,
  amount_cents INT NOT NULL CHECK (amount_cents > 0),
  donated_at TIMESTAMPTZ NOT NULL DEFAULT now (),
  note TEXT,
  admin_id TEXT REFERENCES users (id) ON DELETE SET NULL,
  created_time TIMESTAMPTZ NOT NULL DEFAULT now ()
);

RLS is on and anon/authenticated have no grants: only the API reads it, so the opt-out is always honoured.

Governance Tables

Proposals (/vote), the votes cast on them, and their discussion threads.

Votes / Vote Results Tables

votes holds a proposal (title + rich-text description + status); vote_results holds one row per person per proposal (choice in -1/0/1, plus a priority for "for" votes). Neither is readable by the client roles: tallies come through the security-definer get_votes_with_results(), which also nulls the author of an anonymous proposal.

The author can edit the title and description while the proposal is still open (draft, under_review, voting_open); edit-vote stamps votes.edited_time and archives the previous title and description in vote_edits, which is public-read like vote_comment_edits. A proposal is what people voted on, so the earlier versions have to be checkable by anyone — the "(edited)" marker on the card and the page opens them. vote_edits carries no author column, so it does not leak the author of an anonymous proposal.

Deleting an account removes that member's ballots from vote_results, so tallies drop with them. Their proposals stay, with votes.creator_id set to null, because other members' ballots and comments hang off them.

Vote Comments Table

Discussion on a proposal. Mirrors profile_comments (same denormalized author columns) with two additions: vote_id and stance.

CREATE TABLE vote_comments (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  vote_id BIGINT NOT NULL REFERENCES votes (id) ON DELETE CASCADE,
  user_id TEXT NOT NULL REFERENCES users (id) ON DELETE CASCADE,
  reply_to_comment_id BIGINT REFERENCES vote_comments (id) ON DELETE CASCADE,
  content JSONB NOT NULL,
  stance TEXT CHECK (
    stance IN ('for', 'against', 'both', 'question', 'answer')
  ),
  created_time TIMESTAMPTZ NOT NULL DEFAULT now (),
  hidden BOOLEAN NOT NULL DEFAULT FALSE,
  user_avatar_url TEXT NOT NULL,
  user_name TEXT NOT NULL,
  user_username TEXT NOT NULL
);

stance is deliberately independent of the author's row in vote_results — someone can argue against a proposal they voted for, and the UI shows both side by side.

Threading is one level deep: the API collapses a reply-to-a-reply onto its top-level parent.

Vote Subscriptions Table

Per-person notification state for a proposal's discussion. Not public-read — the client reads its own row through the API.

CREATE TABLE vote_subscriptions (
  user_id TEXT NOT NULL REFERENCES users (id) ON DELETE CASCADE,
  vote_id BIGINT NOT NULL REFERENCES votes (id) ON DELETE CASCADE,
  muted BOOLEAN NOT NULL DEFAULT FALSE,
  last_notified_time TIMESTAMPTZ,
  created_time TIMESTAMPTZ NOT NULL DEFAULT now (),
  PRIMARY KEY (user_id, vote_id)
);

Everyone who has voted on a proposal is a notification recipient when a new comment lands, so this table carries both the opt-out (muted) and the once-per-day throttle (last_notified_time). The throttle lives here rather than being derived from user_notifications so it applies to email-only recipients too.

Events System Tables

Events Table

Stores community events.

CREATE TABLE events (
  id TEXT NOT NULL,
  created_time TIMESTAMPTZ NOT NULL DEFAULT now (),
  creator_id TEXT NOT NULL,
  title TEXT NOT NULL,
  description TEXT NOT NULL,
  start_time TIMESTAMPTZ NOT NULL,
  end_time TIMESTAMPTZ NOT NULL,
  location TEXT,
  location_lat NUMERIC(9, 6),
  location_lng NUMERIC(9, 6),
  max_attendees INTEGER,
  visibility TEXT NOT NULL DEFAULT 'public',
  data JSONB NOT NULL,
  CONSTRAINT events_pkey PRIMARY KEY (id)
);

User Events Table

Tracks user attendance at events.

CREATE TABLE user_events (
  user_id TEXT NOT NULL,
  event_id TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'going',
  created_time TIMESTAMPTZ NOT NULL DEFAULT now (),
  CONSTRAINT user_events_pkey PRIMARY KEY (user_id, event_id)
);

Notification System Tables

User Notifications Table

Stores user notifications.

CREATE TABLE user_notifications (
  id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  reason TEXT NOT NULL,
  data JSONB NOT NULL,
  created_time TIMESTAMPTZ NOT NULL DEFAULT now (),
  updated_time TIMESTAMPTZ NOT NULL DEFAULT now (),
  seen BOOLEAN NOT NULL DEFAULT FALSE,
  CONSTRAINT user_notifications_pkey PRIMARY KEY (id)
);

Moderation Tables

Reports Table

Stores user reports for moderation.

CREATE TABLE reports (
  id TEXT NOT NULL,
  created_time TIMESTAMPTZ NOT NULL DEFAULT now (),
  user_id TEXT NOT NULL,
  target_id TEXT NOT NULL,
  target_type TEXT NOT NULL,
  reason TEXT NOT NULL,
  comment TEXT,
  dismissed BOOLEAN NOT NULL DEFAULT FALSE,
  CONSTRAINT reports_pkey PRIMARY KEY (id)
);

Search and Filtering

The database includes extensive indexing for efficient searching and filtering:

Full-text Search:
- tsvector columns for name, bio, and other text fields
- GIN indexes for efficient full-text queries
Spatial Indexes:
- Latitude/longitude indexes for location-based searches
Array Indexes:
- GIN indexes on array fields (diet, political beliefs, etc.)
Range Indexes:
- B-tree indexes on numeric ranges (age, compatibility scores)

Security

Row Level Security (RLS)

All tables implement RLS policies to ensure data privacy:

Public Read: Anonymous users can read public profiles
Private Access: Users can only access their own private data
Member Access: Authenticated users have broader access

Client read access

The browser talks to PostgREST with the anon key only: there is no per-user Supabase JWT, so firebase_uid() is null in every client query and "readable by the client" means "readable by anyone", in bulk, up to PostgREST's row cap. A public read ... using (true) policy is therefore only right for data that is public by design (prompts, options, votes and their results, the canonical taxonomies). Every table that says something about a member — profiles, users, compatibility_scores, user_activity, profile_stars, profile_likes, profile_ships, bookmarked_searches, compatibility_answers, profile_comments, user_notifications, every private_* table — has had its anon/authenticated SELECT grant revoked (migrations 20260730_cap_profiles_users_reads.sql, 20260731_lock_activity_stars_compat.sql, 20261028_lock_likes_ships.sql, 20261028_lock_client_read_tables.sql, 20261105_lock_user_notifications.sql). The client reads them one of two ways:

A capped security definer RPC (get_display_users, get_profile_by_user_id, get_user_activity, get_profile_stars) when the read is the same for every caller: it requires a filter, so it cannot be turned into a full-table pull, but it does not know who is asking.
An API endpoint (useAPIGetter) when the answer depends on the viewer: get-profile-answers and get-profile-comments apply canReadProfileContent (members-only visibility, disabled and banned profiles, blocks in either direction, and for comments the hidden flag and blocked authors); get-bookmarked-searches returns the caller's own rows only.

user_events is the one table the client writes directly: analytics go in through the anon key, so anon holds INSERT and nothing else, and the anyone can insert policy is its only policy (supabase/migrations/20261107133247_user_events_insert_only.sql). Only the backend's service role reads it.

When adding a table, do not write a public read policy unless the data really is public. Reach for an endpoint first, and an RPC only for a read that is identical for everyone.

Longer term, the reason all of this lives outside the database is that RLS cannot see the Firebase user. Two ways to change that, in order of how much they move:

Mint a Supabase JWT from the Firebase token so the client is authenticated with a real firebase_uid(). Supabase accepts third-party JWTs (Firebase is a supported provider); the web would pass the Firebase ID token to the Supabase client and self-scoped creator_id = firebase_uid() policies would start doing their job, without an API hop. Every policy that currently assumes anon has to be rechecked, and firebase_uid() has to read the claim the way the token carries it.
Move auth to Supabase Auth entirely. Gives RLS a first-class auth.uid(), at the cost of migrating every account, the Capacitor shells, and everything keyed on Firebase uids (which are also the Supabase users.id).

Authentication

User authentication is managed through Firebase Auth, with user IDs synchronized between Firebase and the database.

Performance Optimization

Indexing Strategy

Primary Keys: All tables have primary key constraints with automatic indexes
Foreign Keys: Indexed foreign key relationships for join performance
Query Patterns: Indexes optimized for common query patterns (recent activity, filtering, searching)
Composite Indexes: Multi-column indexes for complex filtering scenarios

Caching Considerations

Timestamp-based caching invalidation using last_modification_time
Materialized views for expensive computed fields
Strategic denormalization for frequently accessed data

Data Integrity

Constraints

Foreign Key Constraints: Maintain referential integrity between related tables
Check Constraints: Validate data correctness at the database level
Unique Constraints: Prevent duplicate records where inappropriate
Not Null Constraints: Ensure required fields are always populated

Triggers

Timestamp Updates: Automatic updating of modification timestamps
Search Index Updates: Real-time updates to full-text search indexes
Data Validation: Pre-insert/update validation of data consistency

Migration Strategy

Database schema changes are managed by the Supabase CLI through files in /supabase/migrations/. See database-migrations.md.

Backup and Recovery

Regular database backups are performed with:

Point-in-time recovery capabilities
Cross-region replication for disaster recovery
Automated backup retention policies

Monitoring

Database performance is monitored through:

Query performance analysis
Index usage statistics
Connection pool utilization
Storage capacity tracking

Last Updated: March 2026

FilesExpand file tree

database-schema.md

Latest commit

History

database-schema.md

File metadata and controls

Compass Database Schema Documentation

Overview

Database Structure

Core Tables

Users Table

Profiles Table

Private Users Table

Messaging Tables

Private User Messages Table

Private User Message Channels Table

Compatibility System Tables

Compatibility Questions Table

Compatibility Answers Table

Compatibility Scores Table

Social Features Tables

Profile Likes Table

Profile Ships Table

Profile Stars Table

Profile Comments Table

Donations Table

Governance Tables

Votes / Vote Results Tables

Vote Comments Table

Vote Subscriptions Table

Events System Tables

Events Table

User Events Table

Notification System Tables

User Notifications Table

Moderation Tables

Reports Table

Search and Filtering

Security

Row Level Security (RLS)

Client read access

Authentication

Performance Optimization

Indexing Strategy

Caching Considerations

Data Integrity

Constraints

Triggers

Migration Strategy

Backup and Recovery

Monitoring