| """ |
| Application tracking models - Kanban board for job applications. |
| """ |
| import uuid |
| from datetime import datetime |
| from enum import Enum |
|
|
| from sqlalchemy import DateTime, ForeignKey, Integer, String, Text, func |
| from sqlalchemy.dialects.postgresql import JSONB, UUID |
| from sqlalchemy.orm import Mapped, mapped_column, relationship |
|
|
| from app.core.database import Base |
|
|
|
|
| class ApplicationStatus(str, Enum): |
| WISHLIST = "wishlist" |
| APPLYING = "applying" |
| APPLIED = "applied" |
| SCREENING = "screening" |
| INTERVIEWING = "interviewing" |
| OFFER = "offer" |
| ACCEPTED = "accepted" |
| REJECTED = "rejected" |
| WITHDRAWN = "withdrawn" |
| GHOSTED = "ghosted" |
|
|
|
|
| class InterviewType(str, Enum): |
| PHONE_SCREEN = "phone_screen" |
| TECHNICAL = "technical" |
| BEHAVIORAL = "behavioral" |
| SYSTEM_DESIGN = "system_design" |
| ONSITE = "onsite" |
| HIRING_MANAGER = "hiring_manager" |
| TAKE_HOME = "take_home" |
| FINAL = "final" |
| OTHER = "other" |
|
|
|
|
| class Application(Base): |
| __tablename__ = "applications" |
|
|
| id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) |
| user_id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True) |
| job_id: Mapped[uuid.UUID | None] = mapped_column(UUID(as_uuid=True), ForeignKey("jobs.id", ondelete="SET NULL"), nullable=True, index=True) |
| custom_job_title: Mapped[str | None] = mapped_column(String(512), nullable=True) |
| custom_company_name: Mapped[str | None] = mapped_column(String(256), nullable=True) |
| custom_job_url: Mapped[str | None] = mapped_column(String(2048), nullable=True) |
| status: Mapped[str] = mapped_column(String(30), default=ApplicationStatus.WISHLIST, nullable=False, index=True) |
| priority: Mapped[int] = mapped_column(Integer, default=0) |
| applied_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True) |
| response_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True) |
| resume_version_id: Mapped[uuid.UUID | None] = mapped_column(UUID(as_uuid=True), nullable=True) |
| cover_letter: Mapped[str | None] = mapped_column(Text, nullable=True) |
| referral_contact: Mapped[str | None] = mapped_column(String(256), nullable=True) |
| referral_notes: Mapped[str | None] = mapped_column(Text, nullable=True) |
| offered_salary: Mapped[int | None] = mapped_column(Integer, nullable=True) |
| offered_equity: Mapped[str | None] = mapped_column(String(100), nullable=True) |
| notes: Mapped[str | None] = mapped_column(Text, nullable=True) |
| tags: Mapped[list | None] = mapped_column(JSONB, nullable=True) |
| extra_data: Mapped[dict | None] = mapped_column("metadata", JSONB, nullable=True) |
| created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now()) |
| updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now()) |
|
|
| user: Mapped["User"] = relationship(back_populates="applications") |
| job: Mapped["Job"] = relationship(back_populates="applications") |
| events: Mapped[list["ApplicationEvent"]] = relationship(back_populates="application", cascade="all, delete-orphan") |
| interviews: Mapped[list["Interview"]] = relationship(back_populates="application", cascade="all, delete-orphan") |
| contacts: Mapped[list["Contact"]] = relationship(back_populates="application") |
|
|
|
|
| class ApplicationEvent(Base): |
| """Timeline events for an application.""" |
| __tablename__ = "application_events" |
|
|
| id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) |
| application_id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), ForeignKey("applications.id", ondelete="CASCADE"), nullable=False, index=True) |
| event_type: Mapped[str] = mapped_column(String(50), nullable=False) |
| description: Mapped[str | None] = mapped_column(Text, nullable=True) |
| old_value: Mapped[str | None] = mapped_column(String(100), nullable=True) |
| new_value: Mapped[str | None] = mapped_column(String(100), nullable=True) |
| extra_data: Mapped[dict | None] = mapped_column("metadata", JSONB, nullable=True) |
| created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now()) |
|
|
| application: Mapped["Application"] = relationship(back_populates="events") |
|
|
|
|
| class Interview(Base): |
| __tablename__ = "interviews" |
|
|
| id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) |
| application_id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), ForeignKey("applications.id", ondelete="CASCADE"), nullable=False, index=True) |
| interview_type: Mapped[str] = mapped_column(String(30), nullable=False) |
| scheduled_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True) |
| duration_minutes: Mapped[int | None] = mapped_column(Integer, nullable=True) |
| location: Mapped[str | None] = mapped_column(String(512), nullable=True) |
| interviewer_names: Mapped[list | None] = mapped_column(JSONB, nullable=True) |
| notes: Mapped[str | None] = mapped_column(Text, nullable=True) |
| prep_notes: Mapped[str | None] = mapped_column(Text, nullable=True) |
| outcome: Mapped[str | None] = mapped_column(String(30), nullable=True) |
| feedback: Mapped[str | None] = mapped_column(Text, nullable=True) |
| created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now()) |
|
|
| application: Mapped["Application"] = relationship(back_populates="interviews") |
|
|
|
|
| class Contact(Base): |
| """Contacts associated with applications (recruiters, hiring managers).""" |
| __tablename__ = "contacts" |
|
|
| id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) |
| application_id: Mapped[uuid.UUID | None] = mapped_column(UUID(as_uuid=True), ForeignKey("applications.id", ondelete="SET NULL"), nullable=True, index=True) |
| user_id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True) |
| name: Mapped[str] = mapped_column(String(256), nullable=False) |
| role: Mapped[str | None] = mapped_column(String(100), nullable=True) |
| company: Mapped[str | None] = mapped_column(String(256), nullable=True) |
| email: Mapped[str | None] = mapped_column(String(320), nullable=True) |
| phone: Mapped[str | None] = mapped_column(String(30), nullable=True) |
| linkedin_url: Mapped[str | None] = mapped_column(String(512), nullable=True) |
| notes: Mapped[str | None] = mapped_column(Text, nullable=True) |
| created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now()) |
|
|
| application: Mapped["Application"] = relationship(back_populates="contacts") |
|
|
