Reqcore / Tickets / #139 feat: implement applicant portal schema and authentication utilities

Originally posted by: coderabbitai[bot]

📝 Walkthrough

## Walkthrough This pull request introduces a complete applicant portal system enabling job candidates to authenticate via Google OAuth, access their submitted applications, view interview schedules, track pipeline progress, and monitor activity timelines through both authenticated dashboard and token-based sharing links. ## Changes |Cohort / File(s)|Summary| |---|---| |**Configuration**
`.env.example`|Updated Google OAuth redirect URIs documentation to include production domain path and localhost development URLs.| |**Portal Layout & Pages**
`app/layouts/portal.vue`, `app/pages/portal/index.vue`, `app/pages/portal/auth/sign-in.vue`, `app/pages/portal/applications/[id].vue`, `app/pages/portal/t/[token].vue`|Added portal layout with header/footer, sign-in page with Google OAuth button, dashboard showing applicant's applications with pipeline/interview/activity summaries, application detail page with full dashboard (interviews, documents, timeline), and token-based public access page with auto-refresh and unauthenticated sign-in prompt.| |**Portal UI Components**
`app/components/portal/InterviewCard.vue`, `app/components/portal/PipelineProgress.vue`, `app/components/portal/StatusBadge.vue`, `app/components/portal/StatusTimeline.vue`|Added reusable components for displaying interview details with timezone-aware scheduling, pipeline progress with stage icons/connectors/pulse animation, status badges with color-coded styling, and activity timelines with icon selection and localized timestamps.| |**Application Flow Integration**
`app/pages/jobs/[slug]/apply.post.ts` (via apply.vue), `app/pages/jobs/[slug]/confirmation.vue`|Modified apply handler to capture portal token from response and redirect to `/portal/t/{token}` when available; updated confirmation page to display portal URL with copy-to-clipboard functionality and dashboard link for authenticated users.| |**Portal Authentication API**
`server/api/portal/auth/google/index.get.ts`, `server/api/portal/auth/google/callback.get.ts`, `server/api/portal/auth/session.get.ts`, `server/api/portal/auth/sign-out.post.ts`|Implemented Google OAuth initiation (state encoding, dynamic redirect URI), callback handling (token exchange, profile fetch, account creation, session creation, secure cookie setting), session validation, and sign-out with cookie/database cleanup.| |**Portal Data API**
`server/api/portal/dashboard.get.ts`, `server/api/portal/applications/[id].get.ts`, `server/api/portal/token/[token].get.ts`|Added authenticated dashboard endpoint returning applicant summary and applications list, application detail endpoint with authorization checks, and token-based public access endpoint with IP rate limiting and token format validation.| |**Portal Utilities**
`server/utils/portal-auth.ts`, `server/utils/portal-dashboard.ts`|Implemented secure token generation/validation, applicant account CRUD with Google ID deduplication, session management with expiration, and dashboard data assembly including pipeline/timeline/interview/document aggregation with timezone/relative time formatting.| |**Portal Database Schema**
`server/database/schema/portal.ts`, `server/database/migrations/0021_blushing_thing.sql`, `server/database/migrations/0022_mute_green_goblin.sql`, `server/database/migrations/meta/_journal.json`, `server/database/schema/app.ts`, `server/database/schema/index.ts`|Added applicant account, session, and portal token tables with foreign keys and indexes; added viewed\\_at/viewed\\_by tracking to application table; extended schema exports.| |**Recruiter Application Tracking**
`server/api/applications/[id].get.ts`|Added fire-and-forget "first view" metadata recording when recruiter accesses application.| ## Sequence Diagram(s) :::mermaid sequenceDiagram participant Candidate as Candidate
(Browser) participant Portal as Portal API
(Node.js) participant Google as Google OAuth
Service participant DB as Database participant Email as Account DB Candidate->>Portal: GET /api/portal/auth/google?returnTo=... Portal->>Portal: Encode state (returnTo, portalToken) Portal-->>Candidate: Redirect to Google OAuth URL Candidate->>Google: Authenticate & authorize Google-->>Candidate: Redirect to callback with code Candidate->>Portal: GET /api/portal/auth/google/callback?code=...&state=... Portal->>Portal: Decode state from base64url Portal->>Google: POST token exchange (code) Google-->>Portal: Return access token Portal->>Google: GET userinfo with access token Google-->>Portal: Return profile (email, name, picture) Portal->>Email: Query/create applicant account by googleId Email-->>Portal: Return applicant account Portal->>DB: Insert applicant session with 72h expiry DB-->>Portal: Return session token Portal->>Portal: Set secure httpOnly cookie (portal_session) Portal-->>Candidate: Redirect to returnTo or /portal Candidate->>Candidate: Portal accessible :::mermaid sequenceDiagram participant Candidate as Candidate
(Browser) participant Portal as Portal API
(Node.js) participant DB as Database participant Cache as Cache Layer Candidate->>Portal: GET /api/portal/dashboard Portal->>Portal: Read portal_session cookie alt Session Missing Portal-->>Candidate: 401 Sign in required else Session Exists Portal->>DB: Validate applicant session token alt Session Expired Portal->>Portal: Delete stale cookie Portal-->>Candidate: 401 Session expired else Session Valid Portal->>Cache: Query cached dashboard (key: email) Cache-->>Portal: Return applicant + applications Portal->>DB: Fetch interview counts per application Portal->>DB: Fetch pipeline stages per application Portal-->>Candidate: Return dashboard JSON Candidate->>Candidate: Render applications grid end end :::mermaid sequenceDiagram participant Public as Anonymous User
(Browser) participant Portal as Portal API
(Node.js) participant RateLimit as Rate Limiter
(IP-based) participant DB as Database participant Cache as Cache Public->>RateLimit: GET /api/portal/token/[token] RateLimit->>RateLimit: Check quota (30 req/min) alt Rate Limited RateLimit-->>Public: 429 Too Many Requests else Within Limit RateLimit->>Portal: Request allowed Portal->>Portal: Validate token format (64-char hex) alt Invalid Format Portal-->>Public: 400 Invalid token format else Valid Format Portal->>DB: Lookup & validate portal token alt Token Expired/Missing Portal-->>Public: 404 Invalid or expired else Token Valid Portal->>DB: Fetch application dashboard
(application, interviews, docs, timeline) DB-->>Portal: Return dashboard data Portal->>DB: Update last_accessed_at (async) Portal-->>Public: Return dashboard JSON Public->>Public: Render application detail end end end ## Estimated code review effort 🎯 4 (Complex) | ⏱️ ~60 minutes ## Possibly related PRs - **[reqcore-inc/reqcore#66](https://github.com/reqcore-inc/reqcore/issues/66)**: Modifies the same `server/api/public/jobs/[slug]/apply.post.ts` handler to generate and return portal tokens alongside resume/cover-letter validation. - **[reqcore-inc/reqcore#103](https://github.com/reqcore-inc/reqcore/issues/103)**: Adds interview domain enhancements (database schema, API fields like `scheduledAt`, `duration`, `location`) that directly support the interview card display and dashboard assembly in this portal implementation. ## Poem > 🐰✨ A portal for journeys, from apply to hire! > OAuth flows dancing, through Google we aspire, > Timelines and pipelines in color so bright, > Candidates tracking their prospects in sight! 🎯🌟

🚥 Pre-merge checks | ✅ 1 | ❌ 2

### ❌ Failed checks (2 warnings) | Check name | Status | Explanation | Resolution | | :----------------: | :--------- | :----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | Description check | ⚠️ Warning | The description lists key changes but is incomplete: the 'Summary' section repeats template placeholder text, and all validation checklist items are unchecked despite the PR being substantial. | Fill in the 'Summary' section with actual answers to 'What does this PR change?' and 'Why is this needed?', and check the relevant validation items (especially multi-tenant scoping/auth verification given the OAuth and session management changes). | | Docstring Coverage | ⚠️ Warning | Docstring coverage is 69.23% which is insufficient. The required threshold is 80.00%. | Write docstrings for the functions missing them to satisfy the coverage threshold. |

✅ Passed checks (1 passed)

| Check name | Status | Explanation | | :---------: | :------- | :--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Title check | ✅ Passed | The title 'feat: implement applicant portal schema and authentication utilities' accurately summarizes the main change—adding the portal infrastructure including schema, auth, and utilities. |

_{✏️ Tip: You can configure your own custom pre-merge checks in the settings.}

✨ Finishing Touches

📝 Generate docstrings

- [ ] Create stacked PR - [ ] Commit on current branch

🧪 Generate unit tests (beta)

- [ ] Create PR with unit tests - [ ] Commit unit tests in branch `experimental/applicant-dashboard`

Thanks for using CodeRabbit! It's free for OSS, and your support helps us grow. If you like it, consider giving us a shout-out.

❤️ Share

- [X](https://twitter.com/intent/tweet?text=I%20just%20used%20%40coderabbitai%20for%20my%20code%20review%2C%20and%20it%27s%20fantastic%21%20It%27s%20free%20for%20OSS%20and%20offers%20a%20free%20trial%20for%20the%20proprietary%20code.%20Check%20it%20out%3A&url=https%3A//coderabbit.ai) - [Mastodon](https://mastodon.social/share?text=I%20just%20used%20%40coderabbitai%20for%20my%20code%20review%2C%20and%20it%27s%20fantastic%21%20It%27s%20free%20for%20OSS%20and%20offers%20a%20free%20trial%20for%20the%20proprietary%20code.%20Check%20it%20out%3A%20https%3A%2F%2Fcoderabbit.ai) - [Reddit](https://www.reddit.com/submit?title=Great%20tool%20for%20code%20review%20-%20CodeRabbit&text=I%20just%20used%20CodeRabbit%20for%20my%20code%20review%2C%20and%20it%27s%20fantastic%21%20It%27s%20free%20for%20OSS%20and%20offers%20a%20free%20trial%20for%20proprietary%20code.%20Check%20it%20out%3A%20https%3A//coderabbit.ai) - [LinkedIn](https://www.linkedin.com/sharing/share-offsite/?url=https%3A%2F%2Fcoderabbit.ai&mini=true&title=Great%20tool%20for%20code%20review%20-%20CodeRabbit&summary=I%20just%20used%20CodeRabbit%20for%20my%20code%20review%2C%20and%20it%27s%20fantastic%21%20It%27s%20free%20for%20OSS%20and%20offers%20a%20free%20trial%20for%20proprietary%20code)

_{Comment @coderabbitai help to get the list of available commands and usage tips.}

feat: implement applicant portal schema and authentication utilities

Open Source Applicant Tracking System (ATS)

Milestone

Searches

Help

#139 feat: implement applicant portal schema and authentication utilities

Summary

Type of change

Validation

DCO

Summary by CodeRabbit

Discussion