Data model

Every table below lives in the public schema and has row-level security enabled. This page describes what the data is. For who may read and write it, see the table reference.

How the tables relate

auth.users  (managed by GoTrue, not by this project)
    |
    | user_id
    |
    +-----> profiles                 one per member
    +-----> user_roles               only for members with elevated access
    +-----> announcement_preferences one per member, created on sign-up
    +-----> reunion_announcements    via created_by

role_hierarchy   standalone lookup: role name to rank
role_changes     audit log, written only by set_user_role
newsletter_subscribers   standalone, deliberately not linked to profiles

There are no foreign keys pointing at profiles. Nothing references a profile by its identifier, which is why a member's alumni_id can be corrected without breaking anything else.

profiles

One row per member. The central table of the application.

Column	Notes
id	Primary key
user_id	The authenticated account. Unique, so one account holds at most one profile
name_en, name_ar	Both required. The directory searches across both
batch_year	Graduating year
specialization	Enum: pure maths, pure statistics, pure CS, CS and statistics, CS and maths, IT
country	Where the member is now, which drives announcement targeting
email	Entered on the profile form. Not the account's login address
alumni_id	Generated, never supplied by the client. Format FMSI-YY-NNNN
username	Unique. Used in profile URLs
quote, job_title, affiliation	Optional
linkedin_url, other_socials	Optional; socials are a JSON object
photo_url	Path within the storage bucket, not a full URL
default_avatar	Which silhouette to draw when there is no photo
status	pending, verified, or rejected

Two fields are worth knowing about in detail.

email is not the login address. It is a form field on the profile, so the address a member publishes to other alumni need not be the one they signed in with. The account's real address lives in auth.users, which this application does not manage.

status controls public visibility. Only verified rows appear to the public. New profiles are forced to pending by policy, and members cannot change their own status. Approval is a deliberate human step.

The alumni identifier

alumni_id is generated by a database trigger, never sent by the client, and follows the format FMSI-<two-digit batch year>-<four-digit sequence>.

The allocator takes an advisory lock before reading the current maximum, so two people signing up at the same moment cannot receive the same number. It matches on the identifier's prefix rather than on batch_year, which matters: an earlier version matched on batch_year, and when a profile's year drifted from the year embedded in its identifier, the generator stopped seeing that row and reissued its number to someone else.

When a member's batch year changes, the identifier is regenerated to match. When it does not change, the identifier is locked to its previous value. Both rules live in the same update trigger.

A freed number is reused. The allocator takes the maximum over live rows only, so deleting the newest profile in a year releases that number for the next sign-up.

reunion_announcements

Despite the name, this table backs every kind of community post, not only reunions.

Column	Notes
title, details	Required
type	post, reunion, meetup, workshop, conference
event_date	Only meaningful for event types; a plain post has no date
country	Optional targeting
image_path	Optional image in the post-images bucket
link_url, button_label	Optional call-to-action button
created_by	The account that published it

The type column was added after the table already held rows, so older rows have a null type. Code reading this column must tolerate that.

link_url is attacker-controlled input rendered as a link, and is parsed and restricted to http and https before use. Anything else, including javascript: and data:, is rejected. Do not render this value without that check.

user_roles, role_hierarchy, role_changes

Three tables that together implement administrative access.

user_roles holds at most one row per member, and only for members with elevated access. No row means an ordinary member. The absence of a row is the user role.

role_hierarchy maps each role to a rank: user 0, editor 10, admin 20, owner 30. Ordering deliberately lives in this table rather than in the enum's declaration order, because enum order cannot be changed later without rebuilding the type, and a database that received the labels in a different order would compare differently with no error.

role_changes records every change: who was changed, by whom, from what, to what, and when. It is written only by the function that performs role changes, and is readable only by owners.

announcement_preferences

One row per account, created automatically by a trigger when the account is created. It records whether the member wants announcements at all, which types, and which countries. An empty country array means all countries.

newsletter_subscribers

Email addresses that asked to be notified. Deliberately decoupled from profiles: subscribing requires no directory profile and no account, so someone can follow the community without joining it.

Anyone may insert. Nobody below administrator may read, so the subscriber list is not exposed to members.

Storage buckets

Two buckets, neither created by a migration. Both were created by hand in Supabase Studio, and a fresh deployment needs them created the same way before uploads work.

alumni-photos holds profile photos, one folder per account. Writes are scoped to the uploader's own folder.

post-images holds images attached to announcements. Writes require the editor role and are deliberately not folder-scoped: the image belongs to the post rather than to whoever uploaded it, so any editor can replace it. Scoping it per uploader would orphan images whenever a different editor edited the post.