Backend

Nigraan's Supabase backend, covering the PostgreSQL tables, the review gate, row-level security, database functions, accounts and migrations.

Nigraan's backend is one Supabase project, shared by the website and the mobile app:

PostgreSQL holds all the data;
PostgREST turns the tables and functions into an HTTPS API;
Supabase Auth handles accounts, email confirmation and password resets.

There is no custom application server. The rules about who may see or change what are written into the database itself, with row-level security (RLS) policies, triggers and functions.

Tables

Table	Holds	Written by
`map_records`	Community hazard reports and relief listings: place, hazard type, coordinates, description, review decision, uploader, optional photo and voice note.	Signed-in users (new reports); reviewers (decisions)
`profiles`	One row per account: name, email, role.	The account holder at sign-up; administrators
`official_reports`	NDMA and PDMA figures: deaths and injuries by district and province, and weather advisories.	The ingest job only
`official_relief`	NDMA relief figures: camps, people sheltered and rescued, goods delivered.	The ingest job only
`river_flood_stages`, `reservoir_levels`	River flood stages and reservoir levels.	The ingest job only
`osm_facilities`	One-row snapshot of the OpenStreetMap facility layer (hospitals, clinics, water points and more).	The facilities job only
`badge_requests`, `user_badges`	Relief-camp badge requests and awarded badges.	Database functions only
`user_notifications`	In-app notifications (badge requests, awards, declines).	Database functions; users mark their own as read

The review gate

A community record is public only once it has been approved. Approval stamps reviewed_at (when) and reviewed_by (who). A refusal keeps the record but hides it, with an optional rejection_reason the reporter can read. The database enforces two rules:

a record is either published or refused, never both;
only a reviewer can change a record's decision, even on a record someone owns.

The apps' public reads also leave out relief listings whose expires_at has passed.

Duplicate reports of one incident are linked with duplicate_of, so the map shows them as one marker.

Who can read what

Row-level security decides, for every request, which rows the caller may see:

Caller	`map_records` they can read
Anyone, signed in or not	Approved records only
The person who filed a report	Their own reports, at any stage, so they can see a refusal and its reason
Validators, institutional users, administrators	Every record, for review

The official tables and the facility snapshot are readable by everyone and writable only by the server-side jobs.

Database functions

Some operations need more than a plain table read or write, so they are SECURITY DEFINER functions that check the caller's role themselves:

Function	Does
`reporter_attribution(ids)`	Names and roles of the people who uploaded and approved records, for the Source line. Never returns emails, and never names a public user.
`badge_queue()`, `badge_decide(...)`	The badge review: list waiting requests; approve or decline one. Validators and administrators only.
`admin_set_badge(...)`	Award, change or remove a badge directly. Administrators only.
`my_badge()`	The caller's own badge and progress.
`admin_remove_user(...)`	Delete an account and its login. Administrators only.

A trigger on map_records counts each public user's approved relief camps and opens a badge request when they reach 3, 6, 9 or 12.

Photos and voice notes

Attachments are stored inside the record as base64 data URLs, in the photo and voice_note columns, so they queue offline on the phone together with the report. The database caps each at 900,000 characters. List queries never select them; they are fetched one record at a time when someone opens them.

Accounts and roles

Sign-up and sign-in use Supabase Auth with email and password. Each account's role (public, validator, partner, institutional, donor or admin) is kept in profiles and read by the policies and functions above. See Roles and security.

Migrations

The schema is versioned as numbered SQL files in frontend/supabase/migrations/, applied in order on top of frontend/supabase/schema.sql. Each file explains in its header what it changes and why. A few columns the mobile app depends on are created by the mobile team's own migrations, so this repository alone does not rebuild production exactly. Copy production's schema when you need an exact copy, for example for a test environment.

The `backend/` folder

The repository also contains a Python FastAPI skeleton in backend/. It is not deployed and its endpoints are placeholders; the live site does not use it.

← All technical documentation