# Careers System – Quick Reference (Supabase Edition)

## Add or Update a Job

### 1. Apply Latest Schema

```bash
cd mawidi-site
supabase migration up
```

### 2. Insert / Edit Job (SQL snippet)

```sql
INSERT INTO public.jobs (
  career_id,
  slug,
  title_translations,
  department,
  department_label,
  location_translations,
  contract_type,
  contract_type_label,
  level,
  level_label,
  compensation_translations,
  benefits,
  skills,
  requirements,
  responsibilities,
  nice_to_have,
  workday_expectations,
  application_instructions,
  status,
  opening_at,
  closing_at
)
VALUES (
  (SELECT id FROM public.careers WHERE slug = 'mawidi'),
  'growth-marketer',
  '{"en":"Growth Marketing Lead","ar":"قائد التسويق للنمو"}',
  'marketing',
  '{"en":"Marketing","ar":"التسويق"}',
  '{"en":"Remote","ar":"عن بُعد"}',
  'full-time',
  '{"en":"Full-Time","ar":"دوام كامل"}',
  'senior',
  '{"en":"Senior","ar":"مستوى أول"}',
  '{"en":"Subject to review","ar":"يتم تحديده لاحقًا"}',
  '{"en":["Performance marketing","Lifecycle automation"],"ar":["التسويق بالأداء","أتمتة دورة حياة العميل"]}',
  '{"en":["Analytics","Copywriting"],"ar":["التحليلات","كتابة المحتوى"]}',
  '{"en":["5+ years in B2B SaaS growth","Strong data chops"],"ar":["5+ سنوات في نمو SaaS","مهارات تحليلية قوية"]}',
  '{"en":["Own paid + organic growth experiments"],"ar":["قيادة تجارب النمو المدفوعة والعضوية"]}',
  '{"en":["Experience in GCC markets"],"ar":["خبرة بأسواق الخليج"]}',
  '{"en":["Weekly growth syncs","Monthly strategy share-out"],"ar":["اجتماع نمو أسبوعي","عرض شهري للإستراتيجية"]}',
  '{"en":"Attach a one-pager on your best growth play.","ar":"أرفق ملخصًا لأفضل حملة نمو قمت بها."}',
  'open',
  now(),
  null
)
ON CONFLICT (slug) DO UPDATE
SET
  title_translations = EXCLUDED.title_translations,
  department = EXCLUDED.department,
  department_label = EXCLUDED.department_label,
  location_translations = EXCLUDED.location_translations,
  contract_type = EXCLUDED.contract_type,
  contract_type_label = EXCLUDED.contract_type_label,
  level = EXCLUDED.level,
  level_label = EXCLUDED.level_label,
  compensation_translations = EXCLUDED.compensation_translations,
  benefits = EXCLUDED.benefits,
  skills = EXCLUDED.skills,
  requirements = EXCLUDED.requirements,
  responsibilities = EXCLUDED.responsibilities,
  nice_to_have = EXCLUDED.nice_to_have,
  workday_expectations = EXCLUDED.workday_expectations,
  application_instructions = EXCLUDED.application_instructions,
  status = EXCLUDED.status,
  opening_at = EXCLUDED.opening_at,
  closing_at = EXCLUDED.closing_at;
```

### 3. Verify

- Refresh `/en/careers` or `/ar/careers`
- Optional: `psql` → `SELECT slug, status FROM public.jobs ORDER BY opening_at DESC;`

## Close / Archive a Job

```sql
UPDATE public.jobs
SET status = 'closed', closing_at = now()
WHERE slug = 'growth-marketer';
```

## Seed or Reset Local Data

```bash
supabase db reset        # runs migrations + supabase/seed.sql
```

## Fetch Jobs in Code

```ts
import { fetchOpenJobs } from "@/lib/careers";

const jobs = await fetchOpenJobs();
```

## Application Flow Overview

1. Client validates required fields + PDF (≤10 MB)
2. `POST /api/apply-job` (multipart form)
3. Route verifies job status and email → uploads PDF to `job-cvs`
4. Record inserted into `job_applications`
5. JSON success response returned to client

## Supabase Buckets & Policies

- Bucket: `job-cvs` (private; service-role access only)
- RLS: public read on `careers` + open `jobs`; anon insert on `job_applications`
- Admin credential required for CV retrieval (use Supabase Studio or signed URLs)

## Env Vars Required

```
NEXT_PUBLIC_SUPABASE_URL
NEXT_PUBLIC_SUPABASE_ANON_KEY
SUPABASE_SERVICE_ROLE_KEY
```

> Add to `.env.local` (see `lib/supabase.ts` for usage)

## Manual QA Cheatsheet

- Ensure `fetchOpenJobs()` returns expected rows
- Submit application → confirm:
  - `job_applications` row created
  - File exists in `storage.objects` (`job-cvs/...`)
- Attempt non-PDF upload → expect 400 error
- Set job status to `closed` → card hidden on frontend

## Troubleshooting

| Issue                      | Fix                                                                         |
| -------------------------- | --------------------------------------------------------------------------- |
| No jobs showing            | Check `jobs.status`, `opening_at`, `closing_at` values; ensure env vars set |
| Upload fails               | Verify Supabase storage service-role key + bucket policies                  |
| Lint error `supabase` null | Ensure env vars exist; admin key only on server                             |
| Seed not applied           | Run `supabase db reset` from `mawidi-site` directory                        |

## Useful Commands

```bash
# Tail API route during dev
npm run dev

# Inspect latest applications
psql "$SUPABASE_DB_URL" -c "SELECT applicant_name, applicant_email, submitted_at FROM public.job_applications ORDER BY submitted_at DESC LIMIT 10;"

# Remove test CVs
supabase storage rm job-cvs --prefix "growth-marketer/"
```