# Security & Key Rotation — EduAdvise CRM

## Rotate now (credentials were shared in chat during the build)

The database password and the `service_role`/secret key were pasted in plain
text while building. Treat both as compromised and rotate them.

### 1. Database password
Supabase dashboard → **Project Settings → Database → Database password →
Reset database password**. Then update anywhere it's used:
- Local `.env.local` → `DATABASE_URL` (URL-encode special characters:
  `@`→`%40`, `$`→`%24`).
- Any saved `psql` connection.

### 2. service_role / secret API key
Dashboard → **Project Settings → API Keys**. For the new key format click the
**⋮** next to the **Secret** key → **Roll**/**Generate new**, then update:
- Local `.env.local` → `SUPABASE_SERVICE_ROLE_KEY`.
- Vercel → Project → Settings → Environment Variables (when deployed).

The **publishable/anon** key is safe to expose (it's used in the browser by
design) and only needs rotating if you suspect broader compromise. Rolling it
also requires updating `NEXT_PUBLIC_SUPABASE_ANON_KEY` everywhere.

### 3. Default admin password
The bootstrap admin (`admin@eduadvise.in`) was created with a known default
(`EduAdvise@2026`). Sign in and change it, or reset via the forgot-password
flow once email (Resend) is configured.

## Security model (how access is enforced)

- **Postgres Row-Level Security (RLS) is the source of truth**, not app code. A
  missing `where` clause cannot leak data. Helpers (`SECURITY DEFINER`):
  `is_super_admin()`, `current_user_branch_ids()`, `current_user_assigned_lead_ids()`,
  `is_b2b_partner()`, `has_permission(resource, action)`.
- **Branch isolation**: staff see only their assigned branch(es); super admin
  sees all. B2B partners are explicitly excluded from branch-wide visibility and
  see only leads they created.
- **Three portals, separated by role**: staff (`/`), B2B partner (`/partner`),
  student (`/portal`). Students see only their own application + children
  (documents, fees, visa, messages) via `student_user_id = auth.uid()`.
- **Permissions** (`roles` × `permissions`) gate mutating actions in app code
  (`requirePermission`) AND in RLS — defence in depth.
- **Service-role key** bypasses RLS and is used only in trusted server actions
  (user/student provisioning, audit writes). Never sent to the browser; never
  prefixed `NEXT_PUBLIC_`.

## Regression testing

Run the RLS test pack after any change to policies, roles, or the auth flow:

```bash
npm run test:rls
```

It provisions throwaway counselor/student/partner accounts, asserts branch +
student + partner isolation and permission gating against the live project, then
cleans up. Exit code is non-zero on any failure. Reads keys from `.env.local`.

> Ideally run this against a **separate dev/staging Supabase project**, not
> production. Create one, apply `db/migrations/*` + `db/seed/*`, point a second
> `.env.local` at it for testing.

## Operational notes

- Storage buckets `documents` and `marketing` are **private**; files are served
  via short-lived signed URLs.
- Enable **Point-in-Time Recovery / backups** in Supabase before go-live.
- Consider adding error monitoring (e.g. Sentry) before real traffic.