Users & Profiles

auth.users — managed by Supabase

Authentication identity lives entirely in Supabase Auth's auth.users table. We do not own this table, do not migrate it, and do not duplicate its columns into public.

Column	Type	Notes
id	UUID	The canonical user identifier — referenced by every domain table
email	varchar
phone	text
email_confirmed_at	timestamptz
created_at	timestamptz
updated_at	timestamptz

Roles (user, moderator, admin) are encoded as JWT claims (e.g. app_role), not as a column on auth.users. The application layer signs that claim into the JWT during/after login; clients can never set it.

profiles — shipped

The dating-side view of a user. Created explicitly via POST /profiles after sign-up — there is no on_auth_user_created trigger because most fields are NOT NULL and require user input.

Source: profile.schema.ts.

Profile photos and onboarding

profile_photos stores the private Storage key, gallery position, and an optional thumbnail key. The profile's avatar_photo_id independently selects the avatar. The API supports at most six photos, signed URLs, and complete gallery reordering.

Survey fields are nullable during sign-up. profiles.is_completed is set by POST /profiles/complete once the required survey answers and at least one photo are present. Legal document versions are recorded when supplied but are not currently a gate.

Column	Type	Notes
id	UUID PK	Drizzle defaultRandom()
user_id	UUID, unique, NOT NULL	1:1 with auth.users.id
first_name	text
bio	text | null
dating_purpose	text	FK target: dating_purposes.code
show_dating_purpose	boolean
hometown	text	Free-text for now
birth_date	date
religion	text	FK target: religions.code
zodiac	enum zodiac	Server-derived from birth_date
height	integer	cm, 55..272
family_plan	text	FK target: family_plans.code
alcohol_attitude	enum attitude	positive / neutral / negative
smoking_attitude	enum attitude	same
financial_state	text	FK target: financial_state.code
body_type	text	FK target: body_type.code

Postgres enums

CREATE TYPE attitude AS ENUM ('positive', 'neutral', 'negative');
CREATE TYPE zodiac AS ENUM (
  'aquarius','pisces','aries','taurus','gemini','cancer',
  'leo','virgo','libra','scorpio','sagittarius','capricorn'
);
CREATE TYPE education_level AS ENUM (
  'basic_general','average_general','secondary_special',
  'bachelor','specialty','master','postgraduate'
);

Adding values to a Postgres enum is one-way and requires a separate ALTER TYPE … ADD VALUE migration.

Reference / lookup tables — shipped

All share the (id serial PK, code text) shape via the referenceColumns factory and are seeded from src/infra/db/seeds/.

Table	Seed values
dating_purposes	dating, relationship, friendship, chatting
body_type	slim, normal, fit, sportive, muscular, stocky, chubby
family_plans	no-dont-want, no-not-soon, no-maybe-in-future, no-but-want-to, already-have
financial_state	unemployed, studying, irregular-income, low-income, middle-income, wealthy
religions	catholicism, orthodoxy, islam, protestantism, buddhism, hinduism, atheism, agnosticism, judaism

Add new values via a migration that simply INSERT … ON CONFLICT DO NOTHING.

languages

Column	Type	Notes
id	serial PK
code	varchar(2), unique	ISO 639-1
name_ru	text, unique	Russian name
native_name	text, unique	Localized native name

Source: languages.schema.ts.

profile_educations — shipped

Column	Type	Notes
id	UUID PK	defaultRandom()
profile_id	UUID FK → profiles.id	ON DELETE CASCADE
level	enum education_level
in_progress	boolean

Source: profile.schema.ts.

profile_languages — shipped

Join table between profiles and languages.

Column	Type	Notes
profile_id	UUID FK → profiles.id	ON DELETE CASCADE
language_id	int FK → languages.id

Source: profile.schema.ts.

friendships — shipped

Column	Type	Notes
id	UUID PK	defaultRandom()
requester_id	UUID	FK → auth.users.id
addressee_id	UUID	FK → auth.users.id
status	enum friendship_status	pending, accepted, rejected, blocked
blocked_by	UUID	nullable, set only when status = blocked
created_at	timestamptz
updated_at	timestamptz

Source: friendship.schema.ts.

Postgres enum

CREATE TYPE friendship_status AS ENUM ('pending', 'accepted', 'rejected', 'blocked');

Indexes and constraints

-- Order-independent unique pair so (A,B) and (B,A) collide
CREATE UNIQUE INDEX friendships_pair_uniq
  ON friendships(LEAST(requester_id, addressee_id), GREATEST(requester_id, addressee_id));

CREATE INDEX friendships_status_idx ON friendships(status);
CREATE INDEX friendships_requester_idx ON friendships(requester_id);
CREATE INDEX friendships_addressee_idx ON friendships(addressee_id);

-- Constraints
CHECK (requester_id <> addressee_id)  -- no self-friendship
CHECK (blocked_by IS NULL OR status = 'blocked')  -- blocked_by only when blocked
CHECK (blocked_by IS NULL OR blocked_by IN (requester_id, addressee_id))  -- blocker must be participant

Relationship overview

Row-Level Security

The full convention is documented in api/docs/RLS-NOTES.md. Highlights:

• RLS enabled on every table we own. Denied-by-default.
• Lookup tables (dating_purposes, body_type, etc.) — readable by anon + authenticated. They contain zero PII and the signup form needs them before the user has a JWT.
• profiles — authenticated may SELECT/INSERT/UPDATE only their own row (auth.uid() = user_id). anon SELECT is REVOKEd so PostgREST does not even list the table.
• Cross-user reads (matching, feed, friend graph) are deliberately not granted at the DB layer. They go through the API server running as service_role after applying business-rule authorization in code.