alrahma_sunday_school/docs/Job_postings_feature_plan.md

# Job Postings Feature — Implementation Plan

## 1. Overview

This feature has two sides:

- **Admin side** — create/manage job position templates, publish open positions, review applicant submissions.
- **Client side** — browse open positions, view details, and submit an application (with resume upload) that triggers a confirmation email.

---

## 2. Data Model

### JobTemplate
| Field | Type | Notes |
|---|---|---|
| `template_id` | UUID | Primary key |
| `title` | string | |
| `description` | text | |
| `department` | string | |
| `location` | string | |
| `employment_type` | string | e.g. full-time, part-time, contract |
| `requirements` | text | |
| `salary_range` | string | optional |
| `version` | int | increments on each edit |
| `is_active` | bool | soft-archive instead of delete |
| `created_by` | user ref | |
| `created_at` / `updated_at` | timestamp | |

### JobPosition
| Field | Type | Notes |
|---|---|---|
| `position_id` | UUID | **Unique ID per job, required** |
| `template_id` | UUID (nullable) | set if created from a template |
| `title`, `description`, `department`, `location`, `employment_type`, `requirements`, `salary_range` | — | copied from template at creation time (not a live reference) |
| `status` | enum | draft / open / closed / filled |
| `created_at` / `updated_at` | timestamp | |
| `posted_by` | user ref | |

### Application
| Field | Type | Notes |
|---|---|---|
| `application_id` | UUID | |
| `position_id` | FK | which job they applied to |
| `first_name`, `last_name` | string | required |
| `email` | string | required, validated |
| `phone` | string | required |
| `resume_file_url` | string | pointer to stored file |
| `submitted_at` | timestamp | |
| `status` | enum | new / reviewed / contacted / rejected / hired |
| `admin_notes` | text | for admin follow-up tracking |

---

## 3. Admin Side

### 3.1 Create Open Position
- Form fields: title, description, department, location, employment type, requirements, salary range.
- Option to start **blank** or **from a template**.
- On save: generate unique `position_id`, default status `draft` → publish sets `open`.

### 3.2 Job Templates (CRUD)
- List / create / edit / archive templates.
- **Editing behavior**: support both
  - **Overwrite** current version, or
  - **Save as new version** (keeps history)
- Version history viewable/revertible.
- Archiving a template never affects positions already created from it (fields are copied, not linked live).

### 3.3 Create Position from Template
- Admin selects a template → fields pre-fill a new position form → admin edits as needed → save generates a new `position_id`.

### 3.4 Review Applications
- Dashboard listing all submissions, filterable by position, status, or date.
- Detail view: applicant info, resume preview/download, position applied to.
- Status + notes fields so admin can track follow-up (contacted, rejected, hired).
- Nice-to-have: CSV export, direct email link to applicant.

---

## 4. Client Side

### 4.1 Open Positions Listing
- Public page listing all positions where `status = open`.
- Card/list view: title, department, location, short summary.

### 4.2 Position Detail Page
- Full description, requirements, etc.
- "Apply" call-to-action.

### 4.3 Application Form
- Fields: First Name, Last Name, Email, Phone, Resume upload (PDF/DOC).
- Client-side validation: required fields, email format, file type/size limits.
- Submits to create an `Application` tied to `position_id`.

### 4.4 Confirmation Email
- Sent automatically to the applicant's email on successful submission.
- References the position title and sets expectation of review/follow-up.
- Optional: parallel notification email to admin/HR inbox.

---

## 5. API Endpoints (suggested)

**Admin**
```
POST   /admin/templates
PUT    /admin/templates/:id
GET    /admin/templates

POST   /admin/positions          (optional template_id)
PUT    /admin/positions/:id
GET    /admin/positions

GET    /admin/applications       (filter by position, status)
PATCH  /admin/applications/:id   (update status/notes)
```

**Client (public)**
```
GET    /positions                (open only)
GET    /positions/:id
POST   /positions/:id/apply      (multipart form, includes resume)
```

---

## 6. Infrastructure Notes

- **File storage**: resumes go to object storage (S3 or equivalent); store the file URL/key on the Application record, not the binary in the database.
- **Email**: use a transactional email provider (SendGrid, SES, Postmark) for confirmation + admin notification emails, driven by templates.
- **Auth**: admin routes require authenticated/role-gated access; client routes remain public.

---

## 7. Database Migrations

Assumes PostgreSQL syntax (adjust types for MySQL/SQLite as needed). Each migration is additive and ordered so foreign keys resolve correctly.

### Migration 001 — create `job_templates`
```sql
-- up
CREATE TABLE job_templates (
    template_id     UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title           VARCHAR(255) NOT NULL,
    description     TEXT,
    department      VARCHAR(255),
    location        VARCHAR(255),
    employment_type VARCHAR(50),
    requirements    TEXT,
    salary_range    VARCHAR(100),
    version         INT NOT NULL DEFAULT 1,
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    created_by      UUID REFERENCES users(id),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_job_templates_active ON job_templates (is_active);

-- down
DROP TABLE IF EXISTS job_templates;
```

### Migration 002 — create `job_template_versions` (version history)
```sql
-- up
CREATE TABLE job_template_versions (
    version_id      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    template_id     UUID NOT NULL REFERENCES job_templates(template_id) ON DELETE CASCADE,
    version         INT NOT NULL,
    title           VARCHAR(255) NOT NULL,
    description     TEXT,
    department      VARCHAR(255),
    location        VARCHAR(255),
    employment_type VARCHAR(50),
    requirements    TEXT,
    salary_range    VARCHAR(100),
    saved_by        UUID REFERENCES users(id),
    saved_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_template_versions_template_id ON job_template_versions (template_id);

-- down
DROP TABLE IF EXISTS job_template_versions;
```

### Migration 003 — create `job_positions`
```sql
-- up
CREATE TYPE position_status AS ENUM ('draft', 'open', 'closed', 'filled');

CREATE TABLE job_positions (
    position_id     UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    template_id     UUID REFERENCES job_templates(template_id) ON DELETE SET NULL,
    title           VARCHAR(255) NOT NULL,
    description     TEXT,
    department      VARCHAR(255),
    location        VARCHAR(255),
    employment_type VARCHAR(50),
    requirements    TEXT,
    salary_range    VARCHAR(100),
    status          position_status NOT NULL DEFAULT 'draft',
    posted_by       UUID REFERENCES users(id),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_job_positions_status ON job_positions (status);

-- down
DROP TABLE IF EXISTS job_positions;
DROP TYPE IF EXISTS position_status;
```

### Migration 004 — create `applications`
```sql
-- up
CREATE TYPE application_status AS ENUM ('new', 'reviewed', 'contacted', 'rejected', 'hired');

CREATE TABLE applications (
    application_id  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    position_id     UUID NOT NULL REFERENCES job_positions(position_id) ON DELETE CASCADE,
    first_name      VARCHAR(100) NOT NULL,
    last_name       VARCHAR(100) NOT NULL,
    email           VARCHAR(255) NOT NULL,
    phone           VARCHAR(30) NOT NULL,
    resume_file_url TEXT NOT NULL,
    status          application_status NOT NULL DEFAULT 'new',
    admin_notes     TEXT,
    submitted_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_applications_position_id ON applications (position_id);
CREATE INDEX idx_applications_status ON applications (status);
CREATE INDEX idx_applications_email ON applications (email);

-- down
DROP TABLE IF EXISTS applications;
DROP TYPE IF EXISTS application_status;
```

### Migration 005 — updated_at auto-touch triggers (optional, Postgres)
```sql
-- up
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_job_templates_updated_at
BEFORE UPDATE ON job_templates
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TRIGGER trg_job_positions_updated_at
BEFORE UPDATE ON job_positions
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- down
DROP TRIGGER IF EXISTS trg_job_templates_updated_at ON job_templates;
DROP TRIGGER IF EXISTS trg_job_positions_updated_at ON job_positions;
DROP FUNCTION IF EXISTS set_updated_at();
```

### Migration order & notes
1. `job_templates` → 2. `job_template_versions` → 3. `job_positions` → 4. `applications` → 5. triggers.
2. `users` table is assumed to already exist (for `created_by` / `posted_by`); drop those FK constraints if no auth/user table exists yet.
3. Run each migration's `up` in order; `down` scripts reverse in the opposite order for rollback.
4. If using a migration tool (Knex, Prisma, Sequelize, Alembic, Rails ActiveRecord, etc.), split each numbered migration above into that tool's file format/naming convention — the SQL logic stays the same.

---

## 8. Build Phases

| Phase | Scope |
|---|---|
| 1 | Data models + admin CRUD for Positions (no templates yet) |
| 2 | Template CRUD + versioning + "create position from template" |
| 3 | Client-facing listing + detail pages |
| 4 | Application form + file upload + submission handling |
| 5 | Confirmation email + admin notification email |
| 6 | Admin review dashboard (applications list, status, notes) |
| 7 | Polish: validation, admin auth/permissions, testing |

---

## 9. Open Questions

- How is admin access authenticated/restricted (login system, roles)?
- Should duplicate applications (same email + position) be blocked or allowed?
- What resume file types/size limits are acceptable?
- Should closed positions remain visible (marked "closed") or disappear from the client list entirely?