Before
¶ Just 5 fields
The old hand-built verify response.
profile_id
email
full_name
avatar_url
user_status
Source: docs/utopia-sso-profile-enrichment.md
¶ UtopiaSpace Profile Enrichment
A copy-paste guide that makes the verify Edge Function return the full profile (name, picture, company, department, team, position, units, phone…) instead of just 5 fields.
Full profile, not 5 fields
Safe fields only
UtopiaSpace (Supabase) side only
No ReplyBox code change
Called byreplybox-sso-verify
Runs asSECURITY DEFINER
Granted toservice_role only
Deleted profilereturns NULL
Only safe fields are selected into the output — bank/IC PII is never included.
After
¶ The full, safe profile
Rich payload ReplyBox stores as a snapshot and renders with friendly labels.
profile_id
email
full_name
short_name
user_status
avatar_url
phone_num
company_name
units
departments
team
position
Two parts, both on the UtopiaSpace (Supabase) side
¶ SQL, Edge Function, then check it in ReplyBox
Scope
¶ A separate function; nothing else is touched
me_profile_json is left completely untouched. replybox_profile_json is a separate, self-contained function: it takes an explicit p_user (the verify function has no auth.uid() session) and only ever selects safe fields into its output.
The ReplyBox side already captures and renders whatever verify returns (stores the full payload as a snapshot, displays the extra fields with friendly labels). No ReplyBox code change needed.
Returned to ReplyBox
profile_id
email
full_name
short_name
user_status
avatar_url
phone_num
company_name
units
departments
team
position
Never included
ic_num
bank_name
bank_num
bank_holder
bank_*
personal_space_pin
1
Part 1 · UtopiaSpace
¶ SQL — run once in the Supabase SQL Editor
Creates a standalone, ReplyBox-facing profile JSON, addressed by profile id. SECURITY DEFINER (no auth.uid() session in the HMAC-authed verify function) and locked to service_role.
Rename
id → profile_id
Renameprofile_picture_url → avatar_url
WhyMatches the contract ReplyBox already reads.
Soft-deleteddeleted_at IS NULL, so a soft-deleted profile returns NULL (verify rejects it).
replybox_profile_json
-- Standalone, ReplyBox-facing profile JSON, addressed by profile id.
-- SECURITY DEFINER (no auth.uid() session in the HMAC-authed verify function) and
-- locked to service_role. Selects ONLY safe fields — never bank_*, ic_num, PIN.
-- Renames id -> profile_id and profile_picture_url -> avatar_url to match the
-- contract ReplyBox already reads. me_profile_json is NOT modified.
create or replace function public.replybox_profile_json(p_user uuid)
returns jsonb
language sql
stable
security definer
set search_path = public
as $$
WITH me AS (
-- deleted_at IS NULL so a soft-deleted profile returns NULL (verify rejects it).
SELECT p.* FROM profiles p WHERE p.id = p_user AND p.deleted_at IS NULL
),
unit_names AS (
SELECT jsonb_agg(u.name ORDER BY u.name) AS units_json
FROM business_units u JOIN me ON u.id = ANY(me.unit_ids)
),
dept_names AS (
SELECT jsonb_agg(d.name ORDER BY d.name) AS depts_json
FROM departments d JOIN me ON d.id = ANY(me.dept_ids)
),
company_name AS (
SELECT c.name AS company_name FROM companies c JOIN me ON c.id = me.company_id
),
team_name AS (
SELECT t.name AS team_name FROM teams t JOIN me ON t.id = me.team_id
),
position_title AS (
SELECT pos.title AS position_title FROM positions pos JOIN me ON pos.id = me.position_id
)
SELECT jsonb_build_object(
'profile_id', me.id,
'email', me.email,
'full_name', me.full_name,
'short_name', me.short_name,
'user_status', me.user_status,
'avatar_url', me.profile_picture_url, -- ReplyBox resolves the path to a URL
'phone_num', me.phone_num,
'company_name', cn.company_name,
'units', COALESCE(un.units_json, '[]'::jsonb),
'departments', COALESCE(dn.depts_json, '[]'::jsonb),
'team', tn.team_name,
'position', pt.position_title
)
FROM me
LEFT JOIN unit_names un ON TRUE
LEFT JOIN dept_names dn ON TRUE
LEFT JOIN company_name cn ON TRUE
LEFT JOIN team_name tn ON TRUE
LEFT JOIN position_title pt ON TRUE;
$$;
revoke all on function public.replybox_profile_json(uuid) from public, anon, authenticated;
grant execute on function public.replybox_profile_json(uuid) to service_role;
Verify the SQL (optional)
select public.replybox_profile_json('458b086d-5b73-4c50-a6d6-49c21301ee71');
-- Expect: profile_id, email, full_name, short_name, user_status, avatar_url,
-- phone_num, company_name, units, departments, team, position.
-- Must NOT contain: ic_num, bank_name, bank_num, bank_holder.Optional sanity check — replace the uuid with a real profile id.
Expect the 12 safe keys listed above.
Must NOT contain
ic_num, bank_name, bank_num, bank_holder.
2
¶ Edge Function —
Part 2 · UtopiaSpace
¶ Edge Function — replybox-sso-verify
Replace your entire step 5 (the profile load + deleted/active gate + the old hand-built response) with the block below. It calls the function, keeps the deleted/active gate (the function filters deleted_at, so null = deleted/missing → reject), and returns the rich profile.
Keep the HMAC check
Keep the ticket burn
Keep the
replybox.access recheck
Replace step 5
New step 5
// 5) Build the response from the curated, ReplyBox-safe profile JSON.
// replybox_profile_json filters deleted_at, so null = deleted/missing.
const { data: profile, error: profileError } = await admin.rpc(
"replybox_profile_json",
{ p_user: profileId },
);
if (profileError) {
return jsonPlain({ error: profileError.message }, 500);
}
if (!profile || profile.user_status !== "active") {
return jsonPlain({ error: "forbidden", reason: "inactive_profile" }, 403);
}
return jsonPlain(profile);
});Notes
1This function uses
jsonPlain (not jsonResponse) — match your file's helper.
2Delete the old if (profile.deleted_at || …) block and the old return jsonPlain({ profile_id: profile.id, … }) return — they referenced the previous row shape and are replaced above.
Then redeploy the function (keep "Verify JWT" off for it, as before).
3
Part 3 · ReplyBox
¶ See it in ReplyBox
After the SQL + Edge Function are live:
1ReplyBox → Settings → Profile → UtopiaSpace → Re-sync (re-runs verify and pulls the richer payload).
2The section shows your profile picture plus Company, Department(s), Team, Position, Units, Phone, Short name.
Profile pictureUtopiaSpace
Company
Department(s)
Team
Position
Units
Phone
Short name
Known keys get friendly labels; any field you add later is humanized automatically.
Knobs
¶ SQL only — no code change
¶ Add a field
Add a line to jsonb_build_object in replybox_profile_json. It flows to ReplyBox automatically. Never add bank_* / ic_num / personal_space_pin.
¶ Remove a field
Delete its line.
¶ Roles / memberships
Not included — it's an array of objects and won't render in ReplyBox's generic field list without extra UI work. Ask if you want it.
Security notes
¶ Readable by the verify function, and nobody else
Regular users can't call replybox_profile_json to read other people's profiles.
SECURITY DEFINER, locked downWorks without a user session, and is revoked from
public/anon/authenticated and granted only to service_role (the verify function).
Safe output onlySelects only safe fields into the output, so bank/ID PII never crosses into ReplyBox even though the me CTE reads the full row.
search_pathset search_path = public prevents search-path hijacking (required for SECURITY DEFINER).
Untouchedme_profile_json is unchanged.