InternPilot — Milestones 2 through 6

This build includes:

• Milestone 2 — Authentication: register, login, logout, password hashing, profile editing, session management, form validation, flash messages.
• Milestone 3 — Dashboard: sidebar navigation, summary cards, Applications Statistics row, three Chart.js visuals, Recent Activity feed, Upcoming Deadlines widget.
• Milestone 4 — Internship Tracker: create/edit/delete applications, search, status filter, inline status updates, full detail page.
• Milestone 5 — Resume Management: upload/rename/replace/delete resumes, set a default resume, download, use from the Tracker.
• Milestone 6 — Recruiter & Contact Management (Mini CRM): add/edit/ delete recruiter contacts, search/filter/sort, link recruiters to applications via a real foreign key, and see recruiter/company insights on the Dashboard.

Setup (including upgrading an existing database)

python -m venv venv
source venv/bin/activate        # Windows: venv\Scripts\activate
pip install -r requirements.txt
cp .env.example .env
python -c "import secrets; print(secrets.token_hex(32))"   # paste into SECRET_KEY
python run.py

If you already have instance/internpilot.db from Milestones 1–5, you do NOT need to delete it. See "Database Migration" below — the app upgrades it automatically the first time you run it after extracting this ZIP.

What's implemented

Authentication, Dashboard, Internship Tracker, Resume Management

Unchanged in behavior from Milestones 2–5 (see each milestone's section in earlier project history) — all integration points below are additive.

Recruiter & Contact Management (Milestone 6)

Feature	Where
Recruiter model (name, company, designation, email, phone, linkedin, notes, timestamps)	app/models/recruiter.py
One User → many Recruiters → many Applications	Recruiter.user relationship; Application.recruiter backref
Create / Edit / Delete	app/blueprints/recruiters/routes.py
Search (name/company/email) + company filter + sort (alphabetical/recently added)	routes.py::list_recruiters
Recruiter list page (table: name, company, position, email, applications assigned)	templates/recruiters/index.html
Add/Edit form (shared)	templates/recruiters/create.html
Detail page + linked applications list	templates/recruiters/detail.html
Delete with reassignment warning ("moved to No Recruiter")	routes.py::delete_recruiter + extended partials/delete_confirm_modal.html
Duplicate-email-per-user, email/phone/LinkedIn format validation	app/blueprints/recruiters/forms.py
Every query scoped to current_user.id	_get_owned_recruiter_or_404, all list/filter queries

Application Model Changes

Per the milestone spec, Application.recruiter_name (free text) is replaced with Application.recruiter_id (a real foreign key to recruiters.id) — unlike Resume in Milestone 5, there is no parallel legacy text field kept here; this was an explicit, intentional replacement. See "Database Migration" for how this is handled safely on an existing database.

The "Recruiter" field on Create/Edit Application is now a dropdown of the user's saved recruiters (_populate_recruiter_choices in applications/routes.py), matching the existing "Resume Used" dropdown pattern. If the user has no recruiters yet, the form shows "No recruiters added yet." with an Add Recruiter button instead of an empty dropdown.

Deleting a recruiter does not delete any application — recruiter_id is simply cleared on every application that referenced it (delete_recruiter in recruiters/routes.py), and the confirmation modal tells the user exactly how many applications will be affected before they confirm.

Database Migration (no manual steps required)

This project intentionally doesn't use Flask-Migrate/Alembic (see the Software Design Document) — but Milestone 6 is the first change that adds a column to an already-existing table (applications.recruiter_id), which db.create_all() alone cannot do (it only creates tables that don't exist yet, never alters existing ones).

app/utils/db_bootstrap.py is a small, dependency-free helper (stdlib sqlite3 only) that runs automatically, once, right after db.create_all() in the app factory:

1. Opens the SQLite file directly.
2. Checks PRAGMA table_info(applications) for a recruiter_id column.
3. If missing, runs ALTER TABLE applications ADD COLUMN recruiter_id INTEGER REFERENCES recruiters(id) and logs that it did so.
4. If already present (fresh install, or already upgraded), does nothing.

This means:

• A brand-new install gets recruiter_id for free — the model already defines it, so db.create_all() creates the column correctly from scratch.
• An existing Milestone 1–5 database gets the column added on next startup, automatically, with zero data loss. Any old recruiter_name text values are left in place on disk (SQLite doesn't mind an unused extra column) — they're simply no longer read by the app, since the model no longer maps that column.
• Running the app repeatedly is always safe — every check is idempotent.
• If SQLALCHEMY_DATABASE_URI isn't a sqlite:/// URI (a future Postgres migration, say), this module intentionally does nothing — a real migration tool would be the right call at that point instead.

Tested, not just written: db_bootstrap.py was run against an actual SQLite file simulating a pre-Milestone-6 database (applications table without recruiter_id, with existing rows and an old recruiter_name column) and verified to add the column, preserve all existing data and the old column untouched, and be a safe no-op on repeat runs, on a fresh database, and on a non-SQLite URI.

Dashboard Changes

• Total Recruiters — added as a 5th metric to the existing "Applications Statistics" card (which switched from a fixed 4-column grid to a responsive row-cols-2 row-cols-md-5 grid to fit cleanly) — no new card, no restructuring of any other section.
• Recent Recruiters — recruiter creation/edits are merged into the existing Recent Activity feed (analytics_service.get_recent_activity), same pattern as resume uploads in Milestone 5.
• Applications per Recruiter and Top Companies Applied To — one new row ("Recruiter Insights") appended after the existing Recent Activity / Upcoming Deadlines row, which is otherwise untouched.
• The "no real data yet" sample-data banner now also checks for recruiters, and sample recruiter names in the dummy-data path are correctly not rendered as links (their ids aren't real).

Analytics Changes

app/services/analytics_service.py gained four new functions, all backed by real, aggregate SQL queries (GROUP BY + COUNT, not Python-side counting):

• get_applications_by_company(user_id) — full breakdown, every company.
• get_top_companies(user_id, limit=5) — top N by application count.
• get_applications_by_recruiter(user_id, limit=None) — applications assigned per recruiter, most-assigned first.
• get_recruiter_activity(user_id, limit=6) — most recently added/ updated recruiters.

The Dashboard currently surfaces summarized versions of the first two pairs (get_top_companies, get_applications_by_recruiter) in the new Recruiter Insights section; all four functions are ready to back a full, dedicated Analytics page in a future milestone without any further backend work.

A known, documented limitation: this project has no field-level audit log, so "assigning a recruiter to an application" doesn't get its own distinct Recent Activity message — an application's activity entry always reads "marked as <status>" regardless of which field actually changed. Recruiter records themselves being added or edited do get distinct, correctly-labeled activity entries. A real audit trail is listed as a Future Improvement.

Manual Steps After Extracting the ZIP

None required. Specifically:

• No database deletion or recreation needed — see "Database Migration" above.
• No new pip packages — requirements.txt is unchanged from Milestone 5.
• No new folders to create — uploads/resumes/ and instance/ are still the only runtime-created paths, both already handled by the app factory.
• Just pip install -r requirements.txt (if not already done) and python run.py, same as every prior milestone.

Folder structure

internpilot/
├── app/
│   ├── __init__.py                  # registers recruiters_bp; calls apply_lightweight_migrations()
│   ├── models/
│   │   ├── application.py           # recruiter_name → recruiter_id (FK), new `recruiter` relationship
│   │   └── recruiter.py             # NEW — Milestone 6
│   ├── blueprints/
│   │   ├── applications/             # forms/routes/templates updated for the recruiter_id dropdown
│   │   ├── dashboard/                 # routes.py now also checks for recruiters; passes top_companies/top_recruiters
│   │   └── recruiters/                # NEW — Milestone 6
│   ├── services/
│   │   ├── dummy_data.py             # + total_recruiters, get_top_companies(), get_top_recruiters()
│   │   └── analytics_service.py      # + total_recruiters, recruiter events in Recent Activity, 4 new analytics functions
│   ├── utils/
│   │   └── db_bootstrap.py           # NEW — Milestone 6 (lightweight SQLite schema upgrade, no Alembic)
│   ├── static/css/style.css          # + Recruiter Management styles
│   └── templates/
│       ├── partials/
│       │   ├── sidebar.html           # Recruiters link enabled
│       │   └── delete_confirm_modal.html  # + optional warning line (recruiter reassignment)
│       ├── dashboard/index.html      # + Total Recruiters stat, Recruiter Insights row
│       ├── applications/             # form.html + detail.html updated for recruiter linking
│       └── recruiters/               # NEW — index.html, create.html, detail.html
├── instance/                         # SQLite DB (auto-upgraded in place, gitignored)
├── uploads/resumes/                  # unchanged from Milestone 5
├── requirements.txt                  # unchanged
├── run.py
└── .env.example