# Careers System – Technical Specification (Supabase)

## Architecture Overview

```
app/[lang]/careers/page.tsx  (Server Component)
└── fetchOpenJobs()          (lib/careers.ts)
    └── Supabase REST (public client, RLS enforced)
        └── public.jobs / public.careers tables

CareersClient.tsx            (Client Component)
├── Receives hydrated jobs
├── Renders job cards (benefits, skills, workday)
└── Application modal
    └── Submits multipart form → /api/apply-job (Route Handler)

/api/apply-job/route.ts      (Server Route)
├── Validates payload + job status (Supabase anon client)
├── Uploads PDF → storage bucket `job-cvs` (service role)
└── Inserts row into `public.job_applications`
```

## Database Schema (Supabase Migrations)

File: `supabase/migrations/20251015152213_create_careers_tables.sql`

### `public.careers`

| Column                                         | Type        | Notes                        |
| ---------------------------------------------- | ----------- | ---------------------------- |
| `id`                                           | UUID PK     | Default `gen_random_uuid()`  |
| `slug`                                         | text        | Unique (e.g. `mawidi`)       |
| `title_translations`                           | JSONB       | `{ en: string, ar: string }` |
| `hero_lead_translations`                       | JSONB       |                              |
| `hero_description_translations`                | JSONB       |                              |
| `about_company`, `how_we_work`, `how_to_apply` | JSONB       | Composite objects            |
| `created_at`, `updated_at`                     | timestamptz | Auto-updated trigger         |

Row-level security: `SELECT` allowed to all.

### `public.jobs`

| Column                                                                                                                               | Type                     | Notes                                                    |
| ------------------------------------------------------------------------------------------------------------------------------------ | ------------------------ | -------------------------------------------------------- |
| `id`                                                                                                                                 | UUID PK                  | Default `gen_random_uuid()`                              |
| `career_id`                                                                                                                          | FK → `public.careers.id` | `ON DELETE SET NULL`                                     |
| `slug`                                                                                                                               | text unique              | Used in URLs/forms                                       |
| `title_translations`, `department_label`, `location_translations`, `contract_type_label`, `level_label`, `compensation_translations` | JSONB                    | Bilingual strings                                        |
| `department`, `contract_type`, `level`, `status`                                                                                     | text                     | `status` enum-like (`draft/open/paused/closed/archived`) |
| `benefits`, `skills`, `requirements`, `responsibilities`, `nice_to_have`, `workday_expectations`                                     | JSONB                    | `{ en: string[], ar: string[] }`                         |
| `application_instructions`                                                                                                           | JSONB                    | Additional copy                                          |
| `opening_at`, `closing_at`                                                                                                           | timestamptz              | Listing window                                           |
| `created_at`, `updated_at`                                                                                                           | timestamptz              | Trigger updates `updated_at`                             |

Indexes: `(status, opening_at, closing_at)`, `(department)`.

RLS: `SELECT` allowed when `status = 'open'`, `opening_at <= now`, `closing_at` null or future.

### `public.job_applications`

| Column                                                            | Type                               | Notes                      |
| ----------------------------------------------------------------- | ---------------------------------- | -------------------------- | ----- |
| `id`                                                              | UUID PK                            |
| `job_id`                                                          | FK → `public.jobs.id` (`SET NULL`) |
| `applicant_name`, `applicant_email`, `applicant_phone`            | text                               | Required                   |
| `applicant_linkedin`, `applicant_portfolio`, `cover_note`         | text                               | Optional                   |
| `cv_bucket`, `cv_path`, `cv_filename`, `cv_bytes`, `cv_mime_type` | text/int                           | Storage metadata           |
| `submission_language`                                             | text                               | `'en'                      | 'ar'` |
| `metadata`                                                        | JSONB                              | `{ ipAddress, userAgent }` |
| `submitted_at`, `created_at`                                      | timestamptz                        | Defaults `now()`           |

Indexes: `(job_id)`, `(submitted_at DESC)`.

RLS: `INSERT` allowed to `anon` + `authenticated`; no public `SELECT`.

### Storage Bucket `job-cvs`

Created/managed in same migration:

- `public = false`
- `file_size_limit = 10 MB`
- `allowed_mime_types = ['application/pdf']`
- Policies grant `INSERT/SELECT/DELETE` to `service_role` only.

## Seed Data

File: `supabase/seed.sql`

- Inserts default `careers` row (`slug = 'mawidi'`)
- Seeds 4 open roles (Engineering, Product, Marketing, Success)
- Uses `ON CONFLICT DO UPDATE` for idempotent re-runs

Apply locally: `supabase db reset`

## Runtime Clients

File: `lib/supabase.ts`

- `supabase`: public client (`NEXT_PUBLIC_SUPABASE_*`)
- `supabaseAdmin`: service client (requires `SUPABASE_SERVICE_ROLE_KEY`)
- `JOB_APPLICATION_UPLOAD_CONFIG`: 1 file, 10 MB, PDF only
- `JOB_CV_BUCKET`: `'job-cvs'`

File: `lib/careers.ts`

- `fetchOpenJobs()`: wraps Supabase query with translation fallbacks & sorting
- Returns `JobListing` objects consumed by server component

## API Route `/api/apply-job`

Key validations:

1. Required fields (`jobId`, `fullName`, `email`, `phone`)
2. Email regex
3. Resume File:
   - Required
   - MIME type in `JOB_APPLICATION_UPLOAD_CONFIG.allowedMimeTypes`
   - Size ≤ `MAX_FILE_SIZE`
4. Job status check:
   - `status === 'open'`
   - `opening_at <= now`
   - `closing_at` null or future

Workflow:

1. Upload resume using admin storage client (`supabaseAdmin.storage.from(JOB_CV_BUCKET).upload`)
2. Insert into `job_applications` with metadata
3. On failure, delete uploaded file to avoid orphaned objects
4. Return `{ success, applicationId }`

Error handling:

- 400 for validation issues
- 404 when job missing
- 409 when job closed
- 500 for storage/DB errors (with cleanup)
- 503 if Supabase env vars misconfigured

## Frontend Behavior

`app/[lang]/careers/page.tsx`

- Server component; fetches translations (`careersEn/careersAr`) and `fetchOpenJobs()`
- Passes localized copy and job list to client component

`CareersClient.tsx`

- Sorts jobs by `openingAt` (desc)
- Normalizes bilingual arrays/strings with fallback to other locale
- Provides empty-state messaging when no open roles
- Validates PDF type/size client-side, displays localized error messages
- Submits `FormData` to API, handles loading/success/error states

## Environment & Tooling

Required env vars:

```
NEXT_PUBLIC_SUPABASE_URL
NEXT_PUBLIC_SUPABASE_ANON_KEY
SUPABASE_SERVICE_ROLE_KEY
```

CLI commands:

- `supabase migration new <name>`
- `supabase migration up`
- `supabase db reset` (applies migrations + `supabase/seed.sql`)

Testing references:

- Automated: Playwright specs (to be implemented per `docs/careers-feature-test-plan.md`)
- Manual: QA checklist covering storage access, RLS, bilingual display

## Security Considerations

- CV bucket is private; only service-role key reads/writes
- `job_applications` insert-only for anon users (cannot read submissions)
- Input validation prevents mismatched MIME or oversize uploads
- IP + user agent logged for moderation (stored in metadata JSON)
- Future: add rate limiting + spam protection (e.g. hCaptcha)