Three-item opportunity review queue
Status: specified and SQL prepared. Prompt/profile changes are committed. Working-database execution and PostgreSQL end-to-end verification have not been confirmed.
Problem and intended outcome
The user reported that reviewing six new listings and updating decisions was too much work. Surface up to three candidates per new discovery batch while preserving inspectable reserves. This bounds the default decision workload; it does not establish reduced review time.
Scope and tradeoffs
Selected: persist batch/rank fields and derive a read-only queue. Discovery defaults to three strong new candidates; expanded discovery requires a user request.
Alternatives considered while documenting the design: review all six (fails the workload constraint), discard lower-ranked records (loses inspectable opportunities), or replace reviewed items automatically (creates a moving target). These are design tradeoffs, not a comparative experiment.
Deferred: automatic replenishment, workflow orchestration, reminders, multi-user behavior, and versioned AI assessments.
Data and operation
jobs holds source evidence, application status and listing availability. job_analysis holds AI fit/priority/reasoning, human fit/notes, review_batch_id and ai_review_rank. job_review_queue derives labels.
Operation is manual: AI generates SQL; the user executes it in DBeaver, exports records for analysis, executes reviewed analysis SQL, and records decisions. No automated database connection is claimed.
Rules
- A batch contains new jobs from one search run. Reassessed older jobs do not consume its quota.
- Closed or likely_removed jobs skip new analysis and show Unavailable. Prior assessments remain. Closure is not candidate misfit.
- Unknown availability can enter provisional review with visible caveats; unknown does not mean confirmed open.
- Transition Targets receive distinct positive ranks. Not a Fit receives no rank and shows AI screened out.
- Rank by fit priority, supporting evidence, career direction, required gaps, salary/location fit and availability confidence. If otherwise indistinguishable, smaller job_id is the final tie-break. Persist the order; the view does not compute fit scores.
- Ranks 1-3 show Review now; ranks 4+ show Reserve. Three is a maximum, not a quota to fill with weak candidates.
- A human fit_stage or downstream application status makes a row Reviewed. Notes alone do not establish completion.
- Availability takes display precedence; underlying fit and decisions remain visible.
- Reviewed or unavailable top-three jobs are not automatically replaced. A rank-4 reserve stays Reserve.
- A partial unique index rejects duplicate ranks within a batch. AI analysis preserves human fields.
- New batches retain old queues. Queries explicitly select a batch; all-batch views may show more than three Review now rows overall.
- Missing classification shows Awaiting AI analysis; missing rank shows Unranked. Jobs without an analysis row are absent from the joined view and require a completeness query.
- Users can inspect reserves and override recommendations. Overrides do not silently rerank a batch; another review group requires a user request.
Acceptance examples
| Scenario | Expected behavior |
|---|---|
| Five eligible ranks plus an AI Not a Fit | Three Review now, two Reserve, one screened out |
| Rank 1 is unavailable | Unavailable; reserve not promoted |
| Two eligible candidates | At most two Review now |
| Duplicate rank in a batch | Unique index rejects it |
| Human reviews rank 1 | Reviewed; rank 4 stays Reserve |
| AI reasoning changes | Human fit and notes preserved |
| Missing classification or rank | Awaiting AI analysis or Unranked |
| Missing analysis row | Absent from queue; completeness check identifies it |
| New batch | Previous batch retained; explicit filtering |
| Repeat migration | Designed with IF NOT EXISTS and CREATE OR REPLACE; PostgreSQL rerun remains unverified |
Verification status
Nine reference checks passed in SQLite using the actual view SELECT with PostgreSQL cast/trim syntax adapted. This is not PostgreSQL engine validation or an end-to-end user test. A rollback-only PostgreSQL fixture script is prepared, not executed here.
Evaluation and history
Six records becoming three default review items describes presentation scope, not a 50% time saving. Measure batch review minutes, perceived workload, useful top-three candidates, and promising opportunities in reserves. No measured results are available.
Reassessment currently replaces AI fields. Human decisions remain, but prior AI reasoning is not versioned in the database. Export snapshots are retained separately. Versioned assessments are a later improvement.
Next validation
Confirm the working-database migration and batch labels; run PostgreSQL fixtures; test the queue with the user; record actual results.
Data flow
- Discover and capture evidence.
- Store records and check availability.
- Analyze fit and persist batch/rank.
- Derive three review items and reserves.
- Record human decisions.
- Prepare applications with further review.