Access control and visibility

The whole product promise is “you decide which circle sees which detail”. In v3 that promise is enforced in Postgres, in one place, and tested there.

Three layers

1. Row-level security (RLS) on every table. RLS is enabled on creation and the default is deny. Policies grant an authenticated user access to rows they own, and nothing else. service_role bypasses RLS and is used only by edge functions and triggers.
2. security definer functions for anything that crosses users. Sending or accepting an invitation, computing a snapshot, searching, deleting an account and exporting data all touch rows of two or more accounts. They run as functions owned by postgres with set search_path = public, are granted to authenticated only where a user may call them directly, and validate every argument.
3. Snapshots as the only cross-user read path. A user never selects from another user’s names, phones, or any other detail table. They read connections.snapshot, which was written by the function under the other user’s rules. There is no query in the app that could accidentally join to another profile’s details, because no policy allows the rows to be returned.

Ownership policies

Every profile-scoped table (sharing_circles, detail_types, names, phones, emails, addresses, dates, photos, connections, sync_services and sync_links) carries a profile_id and the same policy shape:

create policy "owner reads names" on public.names for select to authenticated
  using (public.owns_profile(profile_id));
create policy "owner creates names" on public.names for insert to authenticated
  with check (public.owns_profile(profile_id));
-- the same for update (using + with check) and delete

owns_profile(uuid) is itself security definer, so the policy on a detail table does not recurse into the policy on profiles.

profiles and accounts are keyed by account_id = auth.uid(). notifications and devices are keyed by account_id. invitations let both participants read the row and nothing more: the table is select only, so sending, accepting, declining, cancelling, ignoring and changing the circle all go through send_invitation, accept_invitation, decline_invitation, cancel_invitation, ignore_invitation and set_invitation_circle. The sender’s read of a row does not include ignored_at (the column is left out of what invitation_parties and the sent list return), so a sender never learns that a request was ignored. connections are readable by their owner, who may update only nickname, favorite_index and relation; circle_id moves through set_connection_circle and the snapshot columns through the refresh triggers.

RLS says which rows a client may change and cannot say which columns, so the second half of every rule is a column-level grant. update is granted column by column and never covers a column the database owns: accounts exposes onboarding_completed, preferred_language, theme, contact_detail_preference, contact_sort, phone_format and the four notify_* switches; profiles exposes avatar_photo_id, search_opt_out and managed_relation; accounts also exposes telemetry_consent; photos exposes type_id, circle_ids and sort_order but never storage_path, width, height, bytes or sha256, which are written once at upload; sharing_circles exposes name, color, sort_order and notify_on_update; detail_types exposes name; each detail table exposes its own editable columns but never profile_id and never is_verified; notifications exposes read_at; devices exposes push_token, app_version, locale and last_seen_at. A write to a column outside the grant is refused by Postgres with 42501 before any policy is consulted.

audit_events, rate_limits, search_tokens and email_verifications have RLS enabled and no policies and no grants: only security definer functions and service_role reach them. disposable_email_domains is the same, except that supabase_auth_admin has a select grant and a matching policy so the sign-up hook can read it.

Error codes

Every function raises through one helper, raise_app_error(code), which sets SQLSTATE P0001 and puts the bare code in the message. P0001 tells a client “this is an Incico rule, not a database fault”, and the message is a stable token it maps to its own localised text rather than something to show a user. The full set:

not_owner              not_found              blocked
already_connected      invitation_pending     invalid_argument
rate_limited           circle_has_members     too_many_photos       too_many_details
self_invitation        main_profile_undeletable
invitation_expired     unauthorized
too_many_services      service_not_found

The order the guards run in is part of the contract. A caller that owns nothing hears not_owner before it learns whether the profile it named exists at all, a blocked pair hears blocked whichever side placed the block, and an invitation that is not the caller’s is not_found exactly like one that never existed. None of the codes can be used to probe for strangers.

Visibility computation

Given a connection row where profile A (owner) is connected to profile B (other), and B placed A in circle c, A’s snapshot of B is:

names       where c = any(circle_ids)  or is_default
phones      where c = any(circle_ids)
emails      where c = any(circle_ids)
addresses   simple:  where c = any(circle_ids)  -> whole address
            fields:  each field f where c = any(field_scopes->f)
dates       day+month where c = any(daymonth_circle_ids); year only where c = any(year_circle_ids)
photos      where c = any(circle_ids)          -> storage paths, not URLs
avatar_path the avatar's photo, but only where c = any(circle_ids)

plus B’s default name as default_name (always shared once connected, otherwise A would see an empty card) and B’s profiles.version. The function build_snapshot(viewer_profile uuid, subject_profile uuid) returns jsonb implements exactly this, returns null when there is no connection or either side has blocked the other, and is the single definition of “what may A see”. The document it produces is in Data model. It is unit-tested with pgTAP against the scenario matrix in Testing strategy.

When snapshots refresh

Refreshes are synchronous, in the same transaction as the write. A queue would mean a contact card that is briefly wrong about who may see an address, which is not a trade this product makes; the cost is paid by the person editing their own details.

refresh_snapshots_for(profile_id) bumps profiles.version first (its row lock also serialises two concurrent refreshes), then recomputes snapshot, snapshot_hash and snapshot_at for every connection whose other side is this profile, and inserts a contact_updated notification for each connection whose hash actually changed. Because the hash is taken over the snapshot without its version key, an edit only one circle can see notifies only that circle. It is called from:

• statement-level triggers on all six detail tables, for insert, update and delete, with transition tables, so one statement touching four rows rebuilds once rather than four times;
• the avatar trigger on profiles.avatar_photo_id;
• the rename trigger on detail_types.name — a snapshot carries the type’s name, not its id, so a viewer holding “Mobile” has to be told when its owner renames it;
• indirectly, deleting a circle: the delete strips the circle id out of every detail that pointed at it, and those are ordinary updates that the detail triggers see.

Renaming or recolouring a circle fires nothing, because a circle’s name and colour never travel in a snapshot.

Moving a contact to a different circle changes what they can see of you, so connections.circle_id has its own trigger calling refresh_reverse_snapshot, which rewrites the other row and notifies them with circle_changed, and only if their view actually changed. There is no version bump: your details did not change, only who may see which of them. The circle’s name is never disclosed to them.

Notifications coalesce. While an unread contact_updated or circle_changed for the same connection is still sitting there, a new one of that kind is dropped, so a contact editing five details in a minute produces one row and one push. Invitations and removals never coalesce. A viewer only hears about updates at all when notify_on_update is set on their own circle: it is the viewer saying “tell me when the people in this circle change something”.

Photos

Photo objects are private. The storage.objects policy for bucket profile-photos allows select when the requester owns the profile folder or when can_view_photo(auth.uid(), object_name) is true. That function parses <profile_id>/<photo_id>.<ext> back into a photo row, finds the connection between viewer and subject, and checks the circle rule above. A pending, unexpired invitation counts like a connection for the sender’s photos shared with from_circle_id, so the recipient can see who is asking. Two details are load bearing. The row’s storage_path must equal the whole object name, not merely prefix it: a prefix match would let <profile>/<photo_id>.jpg/hidden.zip borrow the permission of the real photo, turning it into a folder its owner could fill with anything. And the circle is read from the subject’s connection row, over every profile the viewer’s account owns, so a managed profile that is in the circle is enough.

Clients download via the SDK, which sends the JWT; no signed URLs leak into snapshots. Uploads, updates and deletes are allowed only inside a folder named after one of the caller’s own profiles, only image/jpeg and image/webp, and at most 2 MB (bucket file-size limit). The exports bucket is select only, on <account_id>/*: a user who could write there could plant a file and have the app hand it back as their export.

Functions callable by users

Function	Guards
search_profiles(tokens text[])	authenticated; 1..20 tokens, each email:/phone: + 64 hex or name: + a 3..50 character alphanumeric word; rate limit 60/h; excludes the caller’s own profiles, opted-out profiles, deleted accounts and pairs blocked either way; returns at most 20 rows of id, default name and avatar path; audit event records the token count only
cross_reference(tokens text[])	authenticated; 1..200 tokens, each email:/phone: + 64 hex — a name token is refused outright; rate limit 30/h on xref:<uid>; the same four exclusions as search_profiles, asked of the shared profile_is_findable(); returns the matched token beside the id, default name and avatar path of the profile it belongs to, so a device holding a thousand hashes can say which of its own contacts a hit was for; audit event records the token count only
send_invitation(p_from_profile, p_to_profile, p_circle, p_relation, p_message)	caller owns p_from_profile; not self; target exists and has not left; circle belongs to the sender; not blocked; no existing connection or pending invitation in either direction; rate limit 30/h keyed on the account; message max 280 chars
accept_invitation(p_invitation, p_my_circle, p_relation)	caller owns to_profile; status pending and not past expires_at; sender’s account still exists; circle belongs to the accepter; creates both connections and both snapshots in one transaction and returns the accepter’s connection id
decline_invitation(id), cancel_invitation(id)	caller is recipient / sender; an already answered invitation gets not_found, the same answer a stranger gets. Neither notifies: a refusal that pushed a message would turn “no” into a second conversation
ignore_invitation(p_invitation)	caller is the recipient (to_profile_id owned by auth.uid()) and the invitation is pending; stamps ignored_at = now(); idempotent; no notification, no audit of the content (A103)
set_invitation_circle(p_invitation, p_circle)	caller is the sender, the invitation is pending, p_circle belongs to the sending profile; replaces from_circle_id (round 4 sent invitation)
remove_connection(connection_id)	caller owns the connection; deletes both rows; notifies nobody (A102)
delete_profile(p_profile)	caller owns the profile; the main profile is refused with main_profile_undeletable; both connection rows of every profile connected to it, the photos and the profile go in one transaction. A plain client delete on profiles no longer exists
replace_detail_type(p_type, p_replacement)	caller owns both types; they share a profile and a category; a system type is refused (invalid_argument); every detail wearing the old type is moved to the replacement and the old type is deleted in one transaction
block_profile(p_my_profile, p_blocked_profile, p_reason)	caller owns p_my_profile; not self; removes any connection without notifying anyone (A102); cancels or declines their open invitations in both directions; inserts the block without announcing it
set_connection_circle(connection_id, circle_id)	caller owns the connection and the circle; triggers reverse-direction refresh
blocked_profiles()	the profiles any of the caller’s own profiles blocked, one row each, with the default name and avatar path (null for an account that left) — the only way to name a profile the profiles policy hides (A85)
unblock_profile(p_profile)	deletes the caller’s own blocks on that profile, from every own profile that placed one, and writes an unblock audit row; a profile the caller has not blocked, including one another account blocked, is not_found; restores findability, not the connection, and notifies nobody
list_sessions()	the caller’s own auth.sessions rows as id, created_at, last_active_at, user_agent, is_current (compared with the JWT’s session_id claim); never the IP address (A84)
revoke_session(p_session)	deletes one of the caller’s own sessions, and with it its refresh tokens; the current session is invalid_argument, a stranger’s or unknown one not_found; writes a session_revoked audit row
mark_notifications_read(ids uuid[])	rows of another account are skipped rather than refused; returns how many of the caller’s own it marked
request_account_deletion()	stamps accounts.deleted_at once (a second call does not move it) and writes an audit row; the removal itself is the delete-account edge function
search_token_for_email(text), search_token_for_phone(text)	the canonical hashes, granted so the app and the tests use one definition instead of reimplementing it
can_view_photo(uuid, text), try_uuid(text), owns_profile(uuid)	helpers the storage policies call as the requesting user

service_role additionally reaches delete_account_data, export_account_data, bump_email_verification_attempts and expire_invitations. The last is the scheduled sweep that stamps run-out invitations as expired, which is the only place that status is ever written; accepting one is refused on its own. It is service-role only because a user who could call it could age out other people’s invitations at will. Scheduling it with pg_cron is deferred to phase 4; until then an expired invitation is refused at accept time and lingers in the two lists.

before_user_created_hook(jsonb) is reachable only by supabase_auth_admin. It refuses a sign-up from a disposable e-mail domain by returning the error object GoTrue understands; it never raises, because a hook that errors refuses every sign-up rather than one.

Anything not in this list is a plain table operation under the ownership policies and the column grants.

Account deletion uses one advisory guard worth knowing about. The main profile cannot be deleted while its account exists, and delete_account_data announces that the account itself is going with a transaction-local set_config('incico.deleting_account', 'on', true), which the guard trigger reads. It is local to the transaction, so a failed deletion cannot leave the guard disarmed.

Closed in phase 3. A plain client delete of a managed profile used to remove its connections by cascade without telling the people connected to it. delete_profile then sent each of them the contact_removed an ordinary removal sent (since A102, 2026-10-29, no removal is announced to anyone), and the plain delete on profiles is revoked, so the function is the only path. Two smaller gaps recorded with it are still open: nothing stops a profile inviting or blocking another profile of the same account, and phone numbers are searchable without verification (assumption A10).

Edge functions

Edge functions run with service_role. The three user-facing ones verify the caller’s JWT themselves (verify_jwt = true at the gateway plus an explicit getUser() check) and never trust an id in the body; push-fanout has no user and authenticates its caller with a shared secret instead:

Function	Why an edge function
push-fanout	Called by the notify_push trigger on notifications insert; checks the x-webhook-secret header, looks up devices, sends FCM/APNs and deletes dead tokens; answers with {sent, dead, failed, skipped}. It is the one function deployed --no-verify-jwt, because its caller is the database, not a user
delete-account	Deletes storage objects, then rows (delete_account_data), then the auth.users row; writes an audit event; sends a confirmation e-mail in the account’s language (A92). Idempotent, so the app and the website can retry until they get a 200
export-account	Builds a profile.json + contacts.vcf zip of the caller’s own account, prunes any earlier archive (an old link in somebody’s inbox is the same personal data), stores it under exports/<account_id>/ and returns a 24-hour signed link, also by e-mail in the account’s language

delete-account and export-account are also called from the website’s account page (A91), so they answer the CORS preflight: an OPTIONS from https://www.incico.eu, https://incico.eu or an origin in the WEB_ORIGINS secret gets 204 with the CORS headers, any other origin 403, and every answer to an allowed origin carries Access-Control-Allow-Origin for that origin (never *). WEB_ORIGINS is a comma-separated list of https origins in which one * may stand for a single DNS label, for the Netlify deploy previews (https://deploy-preview-*--<site>.netlify.app). A request without an Origin header, which is every request from the app, is unaffected. The mail language is accounts.preferred_language, else the language the app wrote into the Auth user’s metadata, else English. | verify-email-detail | Sends a 6-digit code to a newly added e-mail address and checks it (Supabase Auth only verifies the sign-in e-mail). Three sends an hour per address, codes valid 15 minutes, five wrong guesses burn the code, and only the salted hash is stored. With no mailer configured it answers 503 mail_not_configured, and a provider that refuses or cannot be reached gives 503 mail_failed; either way it stores nothing |

Comparison with v1 and v2

Risk	v1	v2	v3
Client can request another user’s raw details	API filters; bug = leak	mock API returned all	no policy returns them
Audience validation	ValidateAudience = false	n/a	Supabase JWT verified by PostgREST and functions
Search enumeration	plaintext SearchText, 3-char prefix	endpoint	hashed exact-match tokens, rate-limited
Sharing circle deleted while in use	exception in API	n/a	foreign key + trigger
Account deletion cascade	application code in two places	endpoint	one function, one transaction, audited