Data model

The schema keeps v1’s proven entities (profile, typed details, connections, sharing circles, sync state) and v2’s refinements (separate day/month and year sharing for dates, field-level address sharing, custom types, favourites, searchability per detail), expressed as normal Postgres tables under row-level security. All ids are uuid (default gen_random_uuid()), all timestamps timestamptz, and every table has created_at and updated_at maintained by a trigger except the write-once ones noted below (audit_events, rate_limits, email_verifications, disposable_email_domains).

Table privileges are spelled out rather than inherited. Each migration revokes everything from anon and authenticated first, then grants back select, insert and delete only where a policy exists, and grants update column by column. Columns the database owns are simply left out of the grant: is_verified on a phone or e-mail, profile_id on any detail, profiles.version and display_name, accounts.deleted_at, connections.circle_id and the snapshot columns, is_system anywhere. Writing one of them through PostgREST fails with 42501 before any policy is consulted, which is the intended answer; the security definer functions run as postgres and keep full access.

Identity

Table	Purpose
auth.users	Supabase Auth. Holds e-mail, password hash, identities (Google, Apple). Never extended with app columns.
public.accounts	One row per auth.users row, created by a trigger on sign-up. user_id uuid pk references auth.users, onboarding_completed bool, preferred_language text null check in ('en','nl') (null is “Device language”, the default for a new account, A81), theme text check in ('system','light','dark'), contact_detail_preference text check in ('email','phone'), contact_sort text default 'nickname' check in ('nickname','name'), phone_format text default 'national' check in ('national','international') (A82), notify_invitations, notify_contact_updates and notify_circle_changes bool default false (the per-kind push switches, A83; notify_contact_removed is dropped, A102), deleted_at timestamptz null, telemetry_consent bool default false.

An account owns one or more profiles. The first profile is the main profile and cannot be deleted while the account exists. Additional profiles are managed profiles (for example a child) and can be deleted.

Profiles and details

Table	Columns (beyond id, timestamps)	Notes
profiles	account_id, kind text check in ('main','managed'), display_name text (denormalised default name), avatar_photo_id uuid null, version bigint (bumped on every detail change), search_opt_out bool default false, managed_relation text null check in ('child','parent','partner','other')	version is what external sync compares. managed_relation is the label chosen in Add profile and is null on the main profile (a check enforces both); it is display-only.
sharing_circles	profile_id, name text, color text (hex), sort_order int, is_system bool, notify_on_update bool default true	Each new profile is seeded with Family, Friends, Work. Users can add, rename, recolour and delete circles; a circle with members cannot be deleted.
detail_types	profile_id, category text check in ('name','phone','email','address','date','photo'), name text, is_system bool, unique (profile_id, category, name)	Seeded per profile: Personal and Work for names, e-mails and photos; Mobile, Home and Work for phones; Home and Work for addresses; Birthday and Anniversary for dates. Users add custom types per category. A system type may be renamed but never deleted, because both clients and every stored snapshot refer to it by name.
names	profile_id, value text, type_id, is_default bool, is_searchable bool, circle_ids uuid[]	At most one default name per profile (partial unique index), mirrored onto profiles.display_name by a trigger. Starts with a letter, then letters, spaces, hyphens, apostrophes and periods; 1..100 chars.
phones	profile_id, raw text, e164 text, country_code text, type_id, is_verified bool default false, is_searchable bool, circle_ids uuid[]	e164 is computed with libphonenumber on the client and re-validated by a check constraint pattern ^\+[1-9][0-9]{6,14}$.
emails	profile_id, value citext, type_id, is_verified bool, is_searchable bool, circle_ids uuid[]	citext, so uniqueness and lookups are case-insensitive. The sign-in e-mail is inserted as a verified Personal e-mail at onboarding: it is the one insert a trigger lets start out verified, because Supabase Auth has already proved it. Changing the address afterwards throws the proof away.
addresses	profile_id, type_id, country_code text, line1 text, line2 text null, house_number text null, postal_code text null, city text, region text null, district text null, sharing_mode text check in ('simple','fields'), circle_ids uuid[], field_scopes jsonb	In simple mode circle_ids applies to the whole address. In fields mode field_scopes maps each field name to a circle-id array, so a user can share only city and country with Friends but the full address with Family (v2 “fieldSharing”).
dates	profile_id, type_id, day int, month int, year int null, daymonth_circle_ids uuid[], year_circle_ids uuid[]	Year can be hidden from circles that may see the day and month (v1 DateYearScopes, v2 yearSharedCircles). A year circle must also be a day-and-month circle; validate_detail_row refuses the rest with invalid_argument.
photos	profile_id, type_id, storage_path text unique, width int, height int, bytes int, sha256 text, circle_ids uuid[], sort_order int	Max 4 per profile (trigger, which locks the profile row first so two concurrent inserts cannot both pass the count). Objects live in the private profile-photos bucket under <profile_id>/<photo_id>.jpg or .webp, resized to 1024 px on the client before upload; a trigger derives that path from the row’s own ids and refuses anything else.

The per-profile limits in Profile and details are triggers, not client rules: 20 names, 20 phone numbers, 20 e-mail addresses, 10 addresses, 30 dates and 4 photos. One function, limit_details(), holds the first five; it locks the profile row before counting, so two concurrent inserts cannot both pass, and raises too_many_details.

Why circle_ids uuid[] rather than a join table: every read of a detail needs its full circle set, the sets are tiny (1..10), and the visibility check is a single circle_id = any(circle_ids) with a GIN index. A join table would add nothing but joins. v1 used a character bitmask keyed by circle code, which limited a profile to 26 circles and leaked the encoding into the client; the array is explicit and unlimited.

Connections and invitations

Table	Columns	Notes
connections	owner_profile_id, other_profile_id, circle_id uuid not null (the circle the owner placed the other party in, on delete restrict), relation text check in ('none','parent','child','partner','sibling'), nickname text null, favorite_index int null, snapshot jsonb, snapshot_hash text, snapshot_at timestamptz, other_version bigint	Two rows per connection, one per direction. unique (owner_profile_id, other_profile_id), and the two ids must differ. There is no status column: the row is the connection. A request that has not been answered is an invitations row, and a blocked pair has no connection at all.
invitations	from_profile_id, to_profile_id, from_circle_id uuid null (on delete set null), relation, message text null (≤ 280), status text check in ('pending','accepted','declined','cancelled','expired'), expires_at, responded_at timestamptz null, ignored_at timestamptz null (A103: set by the recipient’s ignore_invitation, never shown to the sender)	Accepting creates both connections rows in one transaction. Expire after 30 days. At most one may be pending per (from, to) pair (partial unique index); a declined one stays for the history. A check keeps from_circle_id filled for exactly as long as the row is pending, which is why deleting a circle is refused while an invitation still needs it but only nulls the reference on invitations that were already answered.
blocks	profile_id, blocked_profile_id, reason text null (≤ 280)	A blocked profile cannot find or invite the blocker. The block is one-sided in the data and two-sided in effect.

The snapshot is the JSON contact card the owner is allowed to see of the other party: the names, phones, e-mails, addresses (or address fields), dates (with or without year) and photo references whose circle set contains the circle the other party placed the owner in. It is recomputed by refresh_snapshots_for(profile_id) whenever that profile’s details, avatar, detail-type names, circles or circle assignments change.

Snapshot JSON

build_snapshot(viewer_profile, subject_profile) produces exactly this document, and connections.snapshot is the stored copy of it:

{
  "version": 42,
  "default_name": "Anna de Vries",
  "avatar_path": "3f9.../7c1....jpg",
  "names":     [{ "id": "...", "value": "Anna", "type": "Personal" }],
  "phones":    [{ "id": "...", "e164": "+31612345678", "raw": "06 12345678",
                  "country_code": "NL", "type": "Mobile", "is_verified": false }],
  "emails":    [{ "id": "...", "value": "anna@example.com", "type": "Personal",
                  "is_verified": true }],
  "addresses": [{ "id": "...", "type": "Home", "country_code": "NL", "city": "Utrecht" }],
  "dates":     [{ "id": "...", "type": "Birthday", "day": 14, "month": 3, "year": 1984 }],
  "photos":    [{ "id": "...", "path": "3f9.../7c1....jpg", "width": 1024, "height": 768,
                  "sort_order": 0 }]
}

Every list is present and empty rather than absent. default_name always travels, because a card without a name is unreadable; avatar_path is null unless the avatar’s own photo is shared with the viewer’s circle. An address field that is not shared is absent, not null, so a viewer cannot tell “not shared” from “not filled in”, and the year key appears only when the year is both known and shared. Arrays are ordered by created_at then id, so the same facts always produce the same document. Paths are storage paths, never URLs: a signed URL would outlive the sharing decision.

snapshot_hash is the sha256 of that document with the version key removed (snapshot - 'version'). The version changes on every refresh by definition, so hashing it would make every edit look like a change to everybody and would notify contacts about details they cannot see. What is fingerprinted is therefore the visible content; other_version records the version the stored content came from, so it only moves when the content does. The hash drives both change notifications and external sync diffs.

Search

Table	Columns	Notes
search_tokens	profile_id, detail_id uuid, kind text check in ('email','phone','name'), token text, primary key (detail_id, token)	Every token carries its own kind as a prefix, so the stored value is email:<hex>, phone:<hex> or name:<word>. email tokens are sha256(lower(btrim(value))) and phone tokens sha256(btrim(e164)), both hex. name tokens are the lower-cased words of a searchable name, split on everything that is not a letter or a digit, so “Anne-Marie O’Hara” yields anne, marie and hara; words shorter than 3 or longer than 50 characters are dropped. detail_id has no foreign key because the three source tables cannot all be referenced from one column. Maintained by triggers on names, phones, emails and on profiles.search_opt_out.

Only details with is_searchable = true produce tokens, and an e-mail needs is_verified = true as well: anyone can type anyone’s address, and an unverified one in the index would let an impostor be found under somebody else’s name. A phone number is indexed without that proof because phone verification is not in the launch scope. A profile with search_opt_out = true produces no tokens at all.

Search never scans the details tables. The client normalises and hashes the query the same way, calls the search_profiles(tokens text[]) function, and receives at most 20 matches (profile id, default name, avatar path) with no other details, ordered by name. An e-mail or phone token matches on its own; several name tokens are an AND, so every word supplied has to hit the same profile. This keeps enumeration of e-mail addresses impossible: you can confirm a hash you already know, which is what “find my friend” needs, but you cannot list users.

search_profiles is not the only reader of the index. cross_reference(tokens text[]) is a second granted lookup over the same search_tokens rows, for the address-book cross-reference of Connected services: up to 200 tokens a call, 30 calls an hour, email and phone tokens only, and at most 1000 rows. It differs from search in one way that matters — it echoes the token each hit came from, because a device holding a thousand hashes cannot otherwise tell which of its own contacts a profile belongs to — and in no other: both apply the same four exclusions through the shared profile_is_findable(profile, uid) helper, so a profile that is opted out, blocked either way, departed or the caller’s own is invisible to both. The comment in 20260912100900_search.sql that says nothing but search_profiles reads the index predates that function and no longer holds; the migration is applied and is left alone, so this paragraph is the correction.

External sync (device contacts, Google, Microsoft)

Sync state lives on the phone, because tokens and address books do, but a small server-side table lets the user see the same state on a second device:

Table	Columns	Notes
sync_services	profile_id, provider text check in ('device','google','microsoft'), label text (1–40), account_hint text null (≤ 120, the address or account name the provider shows), device_id text not null (1–100), circle_ids uuid[] (which circles are written to this address book), allow_delete bool default false, last_sync_at timestamptz null	Tokens are not here, and neither are provider change cursors (A21). device_id is a client-chosen opaque string, never a vendor or advertising identifier: the only question it answers is “is this the phone I am holding”. A trigger keeps circle_ids to circles of the same profile, and a second one strips a circle’s id from every service when that circle is deleted, exactly as the detail tables are stripped. Ten services per profile (too_many_services).
sync_links	service_id, profile_id, connection_id uuid null, external_id text null (≤ 200), synced_hash text null (≤ 128), hidden bool default false, last_seen_at timestamptz null, unique (service_id, external_id) and unique (service_id, connection_id) where connection_id is not null	One row per decision the reconciliation has already made, so a second device and a fresh install do not have to guess. Each null means one thing: external_id null is “nothing written to this address book yet” (the hidden “do not add”), connection_id null is “the contact is gone”, and a check constraint refuses both at once. The bucket is not stored: it is computed on the device from this row, the connection and the service entry, by the rule docs/vectors/sync-buckets.json pins.

Notifications and devices

Table	Columns	Notes
notifications	account_id, kind text check in ('invitation_received','invitation_accepted','contact_updated','circle_changed','system'), payload jsonb, read_at timestamptz null	Inserted by triggers; the source of both the in-app list and push. Pruned after 90 days.
devices	account_id, platform text check in ('android','ios'), push_token text, app_version text, locale text, last_seen_at	unique (platform, push_token). Token is replaced, never appended, on re-registration.

Audit, abuse control and verification

These four tables have RLS enabled and no policy and no grant at all, which is the strongest statement the schema can make: even a grant added by accident would still return nothing. The functions that write them are security definer and run as postgres, so the missing policies cost them nothing.

Table	Purpose
audit_events	account_id null, kind, target, ip_hash, created_at. Written by security definer functions for invitations, searches, cross-references (kind cross_reference, the token count and nothing else), blocks, account deletion and exports. No foreign key on account_id and no updated_at: an audit row has to outlive the account it describes, and is written once and never changed.
rate_limits	key text pk, window_start, count. A fixed window, bumped by a single upsert so two concurrent callers cannot both pass. Used by search_profiles (search:<account>, 60/h), cross_reference (xref:<account>, 30/h) and send_invitation (invite:<account>, 30/h), all keyed on the account so managed profiles cannot multiply the budget.
email_verifications	id, email_id (on delete cascade), code_hash, salt, expires_at, attempts, created_at. One row per six-digit code sent by verify-email-detail. The code itself is never stored, only sha256(salt ‖ code), so a dump cannot be replayed. No updated_at, deliberately: a new code is a new row, never a rotation in place. Deleting the address takes its pending codes with it.
disposable_email_domains	domain text pk. A short, hand-kept list read by the before user created auth hook. Only supabase_auth_admin may select from it (grant plus a policy), because publishing the list would only say which throwaway service still works.

Enumerations at a glance

profiles.kind            main | managed
detail_types.category    name | phone | email | address | date | photo
addresses.sharing_mode   simple | fields
connections.relation     none | parent | child | partner | sibling
invitations.status       pending | accepted | declined | cancelled | expired
sync_services.provider   device | google | microsoft
notifications.kind       invitation_received | invitation_accepted | contact_updated | circle_changed | system
devices.platform         android | ios
search_tokens.kind       email | phone | name
accounts.theme           system | light | dark
accounts.contact_detail_preference  email | phone

What changed versus v1 and v2

Topic	v1	v2	v3
Circle membership on a detail	char bitmask string	sharedCircles: string[]	circle_ids uuid[] with GIN index
Circle limit	26 (a-z codes)	unlimited	unlimited
Shared contact card	Connection.DetailsJson computed in API	Contact.jsonValue from mock API	connections.snapshot computed by a Postgres function
External OAuth tokens	plaintext columns	none (mock)	never stored server-side
Search	SearchResult table with plaintext search text	POST /contacts/search	hashed tokens, rate-limited function
Identity	ASP.NET Identity + IdentityServer	placeholder	Supabase Auth
Multiple profiles	yes (main + managed)	yes (profileIds)	yes (profiles.kind)
Date year hiding	yes	yes	yes
Field-level address sharing	per-field scopes	sharingMode + fieldSharing	sharing_mode + field_scopes