[Feature Request]: Add Job Status History Tracking — Log Every Status Transition with Timestamp and Reason

[divyanshim27]

Summary

Arachnode tracks job application state (new, applied, ignored) in the jobs table but only stores the current status — there is no history of how or when that status changed. For a job-hunt automation tool, being able to review your application timeline ("When did I apply? How long before I got a response?") is a critical analytical capability that is currently absent.

Problem

• The jobs table stores a single status column that is overwritten on each state change.
• There is no status_history or audit log table recording transitions with timestamps.
• Users cannot see when they changed a job from new to applied, or when they decided to ignore a listing.
• The dashboard metric for "applied jobs" is a count only — there is no timeline view of application activity.
• The roadmap notes: "Extend dashboard layout capturing historical metric trend data visualizations scaling historical outreach efforts correctly" — this directly requires a status history foundation.

Impact

• Users cannot perform retrospective analysis of their job hunt (e.g., "I applied to 20 jobs in April — how many responded?").
• Without history, the dashboard's analytics cannot show trends over time — only point-in-time snapshots.
• The core insight value of a job-tracking tool is in its history, not just its current state.

Proposed Solution

1. Add job_status_history table (as a new migration):

CREATE TABLE IF NOT EXISTS job_status_history (
    id SERIAL PRIMARY KEY,
    job_id UUID NOT NULL REFERENCES jobs(id) ON DELETE CASCADE,
    previous_status VARCHAR(20),
    new_status VARCHAR(20) NOT NULL,
    changed_at TIMESTAMP DEFAULT NOW(),
    reason TEXT
);

2. Record status changes in the aggregator service:

# In aggregator-service/db.py
async def update_job_status(conn, job_id: str, new_status: str, reason: str = None):
    current = await conn.fetchval('SELECT status FROM jobs WHERE id = $1', job_id)
    await conn.execute(
        'UPDATE jobs SET status = $1 WHERE id = $2',
        new_status, job_id
    )
    await conn.execute(
        '''INSERT INTO job_status_history (job_id, previous_status, new_status, reason)
           VALUES ($1, $2, $3, $4)''',
        job_id, current, new_status, reason
    )

3. Add /api/jobs/{job_id}/history endpoint in the gateway:

@app.get("/api/jobs/{job_id}/history")
async def get_job_history(job_id: str):
    return await aggregator_proxy.get(f"/jobs/{job_id}/history")

4. Display history in dashboard on job card click (expandable timeline).

Additional Notes

• I will add an asyncpg migration script for the new table.
• The reset.sh script will be updated to also truncate job_status_history.
• The dashboard will show a mini timeline view when a job card is expanded: New → Applied (Apr 3) → (Awaiting Response).

Please assign this issue to me.

Labels: feature, enhancement, backend, database, analytics, GSSoC 2026

[divyanshim27]

Hey @sharmavaibhav31 ,
Please assign it to me under GSSoC 2026.
/assign /claim /claim-assignment