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.

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.

Planned tables

profile_educations (planned, scaffolded)

Column	Type
id	UUID
profile_id	UUID FK → profiles.id
level	enum education_level
in_progress	boolean

profile_languages (planned, scaffolded)

Join table:

Column	Type
profile_id	UUID FK → profiles.id
language_id	int FK → languages.id

friendships (planned)

Column	Type	Notes
id	UUID PK
requester_id	UUID	FK → auth.users.id
addressee_id	UUID	FK → auth.users.id
status	text	pending, accepted, rejected, blocked
created_at	timestamptz

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);

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.