Coverage for backend / app / data_tables / models.py: 100%

1""" 

2This module defines the database table models for the application using SQLAlchemy ORM. Each class represents a table in 

3the database, with its fields defining the table's columns and relationships. The module utilizes a `CommonBase` class 

4to provide a shared structure for all models, including common attributes like `id`, `created_at`, and `created_by`.""" 

5 

6from sqlalchemy import ( 

7 Column, 

8 Integer, 

9 String, 

10 ForeignKey, 

11 Float, 

12 Boolean, 

13 TIMESTAMP, 

14 text, 

15 CheckConstraint, 

16 Table, 

17 UniqueConstraint, 

18 JSON, 

19) 

20from sqlalchemy.ext.hybrid import hybrid_property 

21from sqlalchemy.orm import relationship 

22from sqlalchemy.sql import expression 

23 

24from app.base_models import CommonBase, Owned 

25from app.database import Base 

26 

27# ------------------------------------------------------ MAPPINGS ------------------------------------------------------ 

28 

29 

30job_keyword_mapping = Table( 

31 "job_keyword_mapping", 

32 Base.metadata, 

33 Column("job_id", Integer, ForeignKey("job.id", ondelete="CASCADE"), primary_key=True), 

34 Column("keyword_id", Integer, ForeignKey("keyword.id", ondelete="CASCADE"), primary_key=True), 

35) 

36 

37interview_interviewer_mapping = Table( 

38 "interview_interviewer_mapping", 

39 Base.metadata, 

40 Column("interview_id", Integer, ForeignKey("interview.id", ondelete="CASCADE"), primary_key=True), 

41 Column("person_id", Integer, ForeignKey("person.id", ondelete="CASCADE"), primary_key=True), 

42) 

43 

44job_contact_mapping = Table( 

45 "job_contact_mapping", 

46 Base.metadata, 

47 Column("job_id", Integer, ForeignKey("job.id", ondelete="CASCADE"), primary_key=True), 

48 Column("person_id", Integer, ForeignKey("person.id", ondelete="CASCADE"), primary_key=True), 

49) 

50 

51speculative_application_contact_mapping = Table( 

52 "speculative_application_contact_mapping", 

53 Base.metadata, 

54 Column( 

55 "speculative_application_id", 

56 Integer, 

57 ForeignKey("speculative_application.id", ondelete="CASCADE"), 

58 primary_key=True, 

59 ), 

60 Column("person_id", Integer, ForeignKey("person.id", ondelete="CASCADE"), primary_key=True), 

61) 

62 

63 

64# -------------------------------------------------------- DATA -------------------------------------------------------- 

65 

66 

67class Keyword(Owned, Base): 

68 """Represents keywords associated with job postings. 

69 

70 Attributes: 

71 ----------- 

72 - `name` (str): The keyword name. 

73 

74 Relationships: 

75 -------------- 

76 - `jobs` (list of Job): List of jobs associated with the keyword. 

77 

78 Constraints: 

79 ------------ 

80 - Combination of owner_id and name must be unique to prevent duplicate keywords for the same user.""" 

81 

82 name = Column(String, nullable=False) 

83 

84 # Relationships 

85 jobs = relationship("Job", secondary=job_keyword_mapping, back_populates="keywords") 

86 

87 # Constraints 

88 __table_args__ = (UniqueConstraint("owner_id", "name", name="uq_owner_keyword_name"),) 

89 

90 

91class Aggregator(Owned, Base): 

92 """Represents an aggregator website (e.g. LinkedIn, Indeed). 

93 

94 Attributes: 

95 ----------- 

96 - `name` (str): The website's name. 

97 - `url` (str): The website's URL. 

98 

99 Relationships: 

100 -------------- 

101 - `jobs` (list of Job): List of jobs associated with the aggregator. 

102 - `job_applications` (list of Job): List of jobs associated with the aggregator. 

103 

104 Constraints: 

105 ------------ 

106 - Combination of owner_id and name must be unique to prevent duplicate aggregators for the same user.""" 

107 

108 name = Column(String, nullable=False) 

109 url = Column(String, nullable=False) 

110 

111 # Relationships 

112 jobs = relationship("Job", foreign_keys="Job.source_aggregator_id", back_populates="source_aggregator") 

113 job_applications = relationship( 

114 "Job", foreign_keys="Job.application_aggregator_id", back_populates="application_aggregator" 

115 ) 

116 

117 # Constraints 

118 __table_args__ = (UniqueConstraint("owner_id", "name", name="uq_owner_aggregator_name"),) 

119 

120 

121class Company(Owned, Base): 

122 """Represents a company or organisation. 

123 

124 Attributes: 

125 ----------- 

126 - `name` (str, unique): Name of the company. 

127 - `description` (str, optional): Description or details about the company. 

128 - `url` (str, optional): Web link to the company's website. 

129 

130 Relationships: 

131 -------------- 

132 - `jobs` (list of Job): List of jobs associated with the company. 

133 - `persons` (list of Person): List of people linked to the company. 

134 

135 Constraints: 

136 ------------ 

137 - Combination of owner_id and name must be unique to prevent duplicate companies for the same user.""" 

138 

139 name = Column(String, nullable=False) 

140 description = Column(String, nullable=True) 

141 url = Column(String, nullable=True) 

142 

143 # Relationships 

144 jobs = relationship("Job", back_populates="company", foreign_keys="[Job.company_id]") 

145 persons = relationship("Person", back_populates="company") 

146 speculative_applications = relationship("SpeculativeApplication", back_populates="company") 

147 recruited_jobs = relationship( 

148 "Job", back_populates="recruitment_company", foreign_keys="[Job.recruitment_company_id]" 

149 ) 

150 

151 # Constraints 

152 __table_args__ = (UniqueConstraint("owner_id", "name", name="uq_owner_company_name"),) 

153 

154 

155class Location(Owned, Base): 

156 """Represents geographical locations. 

157 

158 Attributes: 

159 ----------- 

160 - `postcode` (str, optional): Postcode of the location. 

161 - `city` (str, optional): City of the location. 

162 - `country` (str, optional): Country where the location resides. 

163 - `name` (str): Computed property combining city, country, and postcode 

164 

165 Relationships: 

166 -------------- 

167 - `jobs` (list of Job): List of jobs associated with the location. 

168 - `interviews` (list of Interview): List of interviews associated with the location. 

169 

170 Constraints: 

171 ------------ 

172 - At least one of postcode, city, or country must be provided. 

173 - Combination of owner_id, city, postcode, and country must be unique to prevent duplicate locations for the same user. 

174 """ 

175 

176 postcode = Column(String, nullable=True) 

177 city = Column(String, nullable=True) 

178 country = Column(String, nullable=True) 

179 

180 # Foreign keys 

181 geolocation_id = Column(Integer, ForeignKey("geolocation.id", ondelete="SET NULL"), nullable=True) 

182 

183 # Relationships 

184 jobs = relationship("Job", back_populates="location") 

185 interviews = relationship("Interview", back_populates="location") 

186 geolocation = relationship("Geolocation") 

187 

188 @hybrid_property 

189 def name(self) -> str: 

190 """Computed property that combines city, country, and postcode into a readable location name""" 

191 

192 parts = [] 

193 if self.city: 

194 parts.append(self.city) 

195 if self.country: 

196 parts.append(self.country) 

197 if self.postcode: 

198 parts.append(self.postcode) 

199 

200 return ", ".join(parts) 

201 

202 __table_args__ = ( 

203 CheckConstraint( 

204 "postcode IS NOT NULL OR city IS NOT NULL OR country IS NOT NULL", 

205 name=f"location_data_required", 

206 ), 

207 UniqueConstraint("owner_id", "city", "postcode", "country", name="uq_owner_location_unique"), 

208 ) 

209 

210 

211class Geolocation(Base, CommonBase): 

212 """Cache for geocoded location data to avoid redundant API calls. 

213 

214 Attributes: 

215 ----------- 

216 - `query` (str, unique): The location query string used for geocoding 

217 - `latitude` (float): Latitude coordinate 

218 - `longitude` (float): Longitude coordinate 

219 - `postcode` (str, optional): Postcode of the location 

220 - `city` (str, optional): City of the location 

221 - `country` (str, optional): Country of the location""" 

222 

223 query = Column(String, nullable=False, unique=True, index=True) 

224 latitude = Column(Float, nullable=True) 

225 longitude = Column(Float, nullable=True) 

226 data = Column(JSON, nullable=True) 

227 postcode = Column(String, nullable=True) 

228 city = Column(String, nullable=True) 

229 country = Column(String, nullable=True) 

230 

231 

232class File(Owned, Base): 

233 """Represents files uploaded by the users. 

234 

235 Attributes: 

236 ----------- 

237 - `filename` (str): Name of the file. 

238 - `content` (bytes): Content of the file. 

239 - `type` (str): MIME type of the file. 

240 - `size` (int): Size of the file in bytes.""" 

241 

242 filename = Column(String, nullable=False) 

243 content = Column(String, nullable=False) 

244 type = Column(String, nullable=False) 

245 size = Column(Integer, nullable=False) 

246 

247 

248class Person(Owned, Base): 

249 """Represents a person 

250 

251 Attributes: 

252 ----------- 

253 - `first_name` (str): First name of the person. 

254 - `last_name` (str): Last name of the person. 

255 - `email` (str, optional): Email address of the person. 

256 - `phone` (str, optional): Phone number of the person. 

257 - `role` (str, optional): Role or position held by the person within the company. 

258 - `linkedin_url` (str, optional): LinkedIn profile URL of the person. 

259 - `is_recruiter` (bool): Indicates whether the person is a recruiter. 

260 - `name` (str): Computed property combining first and last name. 

261 

262 Foreign keys: 

263 ------------- 

264 - `company_id` (int): Foreign key linking the person to a company. 

265 

266 Relationships: 

267 -------------- 

268 - `company` (Company): Relationship to access the associated company. 

269 - `interviews` (list of Interview): List of interviews performed by the person within the company. 

270 - `jobs` (list of Job): List of jobs linked to the person within the company.""" 

271 

272 first_name = Column(String, nullable=False) 

273 last_name = Column(String, nullable=False) 

274 email = Column(String, nullable=True) 

275 phone = Column(String, nullable=True) 

276 role = Column(String, nullable=True) 

277 linkedin_url = Column(String, nullable=True) 

278 is_recruiter = Column(Boolean, nullable=False, server_default=expression.false()) 

279 

280 # Foreign keys 

281 company_id = Column(Integer, ForeignKey("company.id", ondelete="SET NULL"), nullable=True, index=True) 

282 

283 # Relationships 

284 company = relationship("Company", back_populates="persons") 

285 interviews = relationship("Interview", secondary=interview_interviewer_mapping, back_populates="interviewers") 

286 jobs = relationship("Job", secondary=job_contact_mapping, back_populates="contacts") 

287 speculative_applications = relationship( 

288 "SpeculativeApplication", secondary=speculative_application_contact_mapping, back_populates="contacts" 

289 ) 

290 recruited_jobs = relationship("Job", back_populates="recruiter") 

291 

292 @hybrid_property 

293 def name(self) -> str: 

294 """Computed property that combines the first and last name""" 

295 

296 return f"{self.first_name} {self.last_name}" 

297 

298 

299class Job(Owned, Base): 

300 """Represents job postings within the application. 

301 

302 Attributes: 

303 ----------- 

304 - `title` (str): The job title. 

305 - `description` (str, optional): Description or details about the job. 

306 - `salary_min` (float, optional): Minimum salary offered for the job (in GBP). 

307 - `salary_max` (float, optional): Maximum salary offered for the job (in GBP). 

308 - `url` (str, optional): Web link to the job posting. 

309 - `personal_rating` (int, optional): Personalised rating given to the job. 

310 - `note` (str, optional): Additional note about the job posting. 

311 - `deadline` (datetime, optional): Deadline for the job application. 

312 - `source_type` (str): Type of source used to post the job (e.g. job board, company website, etc.). 

313 - `followup_snooze_datetime` (datetime, optional): Date and time to snooze follow-up reminders. 

314 - `attendance_type` (str, optional): Type of attendance offered for the job (on-site, remote, hybrid). 

315 - `application_date` (datetime, optional): Date when the application was submitted. 

316 - `application_url` (str, optional): URL used to submit the application. 

317 - `application_status` (str, optional): Current status of the job application 

318 - `applied_via` (str, optional): Method used to apply for the job. 

319 - `application_note` (str, optional): Additional note about the job application. 

320 

321 Foreign keys: 

322 ------------- 

323 - `company_id` (int, optional): Identifier for the company offering the job. 

324 - `location_id` (int, optional): Identifier for the geographical location where the job is located. 

325 - `duplicate_id` (int, optional): Identifier for a duplicate job posting. 

326 - `source_aggregator_id` (int, optional): Identifier for the aggregator website where the job was posted. 

327 - `application_aggregator_id` (int, optional): Identifier for the aggregator website used to apply for the job. 

328 - `cv_id` (int, optional): Identifier for the CV file used in the job application. 

329 - `cover_letter_id` (int, optional): Identifier for the cover letter file used in the job application. 

330 

331 Relationships: 

332 -------------- 

333 - `company` (Company): Company object associated with the job posting. 

334 - `location` (Location): Location object associated with the job posting. 

335 - `keywords` (list of Keyword): List of keywords associated with the job posting. 

336 - `contacts` (list of Person): List of people linked to the company that may be interested in the job posting. 

337 - `source_aggregator` (Aggregator): Source of the job posting (e.g. LinkedIn, Indeed, etc.). 

338 - `interviews` (list of Interview): List of interviews associated with the job application. 

339 - `updates` (list of JobApplicationUpdate): List of updates associated with the job application. 

340 - `application_aggregator` (Aggregator): Source used to apply for the job. 

341 - `application_cv` (File): CV file used in the job application. 

342 - `application_cover_letter` (File): Cover letter file used in the job application. 

343 

344 Constraints: 

345 ------------ 

346 - `personal_rating` must be between 1 and 5 if provided. 

347 - `salary_min` must be less than or equal to `salary_max` if both are provided. 

348 - `attendance_type` must be one of 'on-site', 'remote', or 'hybrid' if provided. 

349 - `application_status` must be one of 'applied', 'interview', 'offer', 'rejected' or 'withdrawn' if provided. 

350 - `applied_via` must be one of 'aggregator', 'email', 'phone', or 'other' if provided.""" 

351 

352 title = Column(String, nullable=False) 

353 description = Column(String, nullable=True) 

354 salary_min = Column(Float, nullable=True) 

355 salary_max = Column(Float, nullable=True) 

356 salary_currency = Column(String, nullable=True) 

357 url = Column(String, nullable=True) 

358 personal_rating = Column(Integer, nullable=True) 

359 note = Column(String, nullable=True) 

360 deadline = Column(TIMESTAMP(timezone=True), nullable=True) 

361 followup_snooze_datetime = Column(TIMESTAMP(timezone=True), nullable=True) 

362 attendance_type = Column(String, nullable=True) 

363 source_type = Column(String, nullable=True) 

364 

365 # Application-specific fields 

366 application_date = Column(TIMESTAMP(timezone=True), nullable=True) 

367 application_url = Column(String, nullable=True) 

368 application_status = Column(String, nullable=True) 

369 applied_via = Column(String, nullable=True) 

370 application_note = Column(String, nullable=True) 

371 

372 # Foreign keys 

373 company_id = Column(Integer, ForeignKey("company.id", ondelete="SET NULL"), nullable=True, index=True) 

374 location_id = Column(Integer, ForeignKey("location.id", ondelete="SET NULL"), nullable=True, index=True) 

375 duplicate_id = Column(Integer, ForeignKey("job.id", ondelete="SET NULL"), nullable=True, index=True) 

376 source_aggregator_id = Column(Integer, ForeignKey("aggregator.id", ondelete="SET NULL"), nullable=True, index=True) 

377 application_aggregator_id = Column( 

378 Integer, ForeignKey("aggregator.id", ondelete="SET NULL"), nullable=True, index=True 

379 ) 

380 recruiter_id = Column(Integer, ForeignKey("person.id", ondelete="SET NULL"), nullable=True, index=True) 

381 recruitment_company_id = Column(Integer, ForeignKey("company.id", ondelete="SET NULL"), nullable=True, index=True) 

382 cv_id = Column(Integer, ForeignKey("file.id", ondelete="SET NULL"), nullable=True, index=True) 

383 cover_letter_id = Column(Integer, ForeignKey("file.id", ondelete="SET NULL"), nullable=True, index=True) 

384 

385 # Relationships 

386 company = relationship("Company", back_populates="jobs", foreign_keys=[company_id]) 

387 location = relationship("Location", back_populates="jobs") 

388 keywords = relationship("Keyword", secondary=job_keyword_mapping, back_populates="jobs", lazy="selectin") 

389 contacts = relationship("Person", secondary=job_contact_mapping, back_populates="jobs", lazy="selectin") 

390 source_aggregator = relationship("Aggregator", foreign_keys=[source_aggregator_id], back_populates="jobs") 

391 interviews = relationship("Interview", back_populates="job") 

392 updates = relationship("JobApplicationUpdate", back_populates="job") 

393 application_aggregator = relationship( 

394 "Aggregator", foreign_keys=[application_aggregator_id], back_populates="job_applications" 

395 ) 

396 recruiter = relationship("Person", foreign_keys=[recruiter_id], back_populates="recruited_jobs") 

397 recruitment_company = relationship( 

398 "Company", foreign_keys=[recruitment_company_id], back_populates="recruited_jobs" 

399 ) 

400 application_cv = relationship("File", foreign_keys=[cv_id], lazy="select") 

401 application_cover_letter = relationship("File", foreign_keys=[cover_letter_id], lazy="select") 

402 

403 __table_args__ = ( 

404 CheckConstraint("personal_rating >= 1 AND personal_rating <= 5", name=f"valid_rating_range"), 

405 CheckConstraint("salary_min <= salary_max", name=f"valid_salary_range"), 

406 CheckConstraint("attendance_type IN ('on-site', 'remote', 'hybrid')", name="valid_attendance_type_values"), 

407 CheckConstraint( 

408 "application_status IN ('applied', 'interview', 'offer', 'rejected', 'withdrawn')", 

409 name="valid_application_status_values", 

410 ), 

411 CheckConstraint( 

412 "applied_via IN ('aggregator', 'email', 'company_website', 'phone', 'other')", 

413 name="valid_applied_via_values", 

414 ), 

415 ) 

416 

417 

418class Interview(Owned, Base): 

419 """Represents interviews for job applications. 

420 

421 Attributes: 

422 ----------- 

423 - `date` (datetime): The date and time of the interview. 

424 - `type` (str): Type of the interview (HR, technical, management, ...) 

425 - `note` (str, optional): Additional notes or comments about the interview. 

426 - `attendance_type` (str, optional): The attendance type of the interview (on-site, remote). 

427 

428 Foreign keys: 

429 ------------- 

430 - `location_id` (int): Identifier for the location of the interview. 

431 - `job_id` (int): Identifier for the job application associated with the interview. 

432 

433 Relationships: 

434 -------------- 

435 - `job` (Job): Job object related to the interview. 

436 - `interviewers` (list of Person): List of people who participated in the interview. 

437 - `location` (Location): Location object related to the interview. 

438 

439 Constraints: 

440 ------------ 

441 - `attendance_type` must be one of 'on-site' or 'remote' if provided.""" 

442 

443 date = Column(TIMESTAMP(timezone=True), server_default=text("now()"), nullable=False) 

444 type = Column(String, nullable=False) 

445 note = Column(String, nullable=True) 

446 attendance_type = Column(String, nullable=True) 

447 

448 # Foreign keys 

449 location_id = Column(Integer, ForeignKey("location.id", ondelete="SET NULL"), nullable=True, index=True) 

450 job_id = Column(Integer, ForeignKey("job.id", ondelete="CASCADE"), nullable=False, index=True) 

451 

452 # Relationships 

453 location = relationship("Location", back_populates="interviews") 

454 job = relationship("Job", back_populates="interviews") 

455 interviewers = relationship("Person", secondary=interview_interviewer_mapping, back_populates="interviews") 

456 

457 __table_args__ = (CheckConstraint("attendance_type IN ('on-site', 'remote')", name="valid_attendance_type_values"),) 

458 

459 

460class JobApplicationUpdate(Owned, Base): 

461 """Represents an update to a job application. 

462 

463 Attributes: 

464 ----------- 

465 - `date` (datetime): The date and time of the update. 

466 - `note` (str, optional): Additional notes or comments about the update. 

467 - `type` (str): The type of the update (received, sent). 

468 

469 Foreign keys: 

470 ------------- 

471 - job_id (int): Identifier for the job application associated with the update. 

472 

473 Relationships: 

474 -------------- 

475 - `job` (Job): Job object related to the update. 

476 

477 Constraints: 

478 ------------ 

479 - `type` must be one of 'received' or 'sent'.""" 

480 

481 date = Column(TIMESTAMP(timezone=True), server_default=text("now()"), nullable=False) 

482 note = Column(String, nullable=True) 

483 type = Column(String, nullable=False) 

484 

485 # Foreign keys 

486 job_id = Column(Integer, ForeignKey("job.id", ondelete="CASCADE"), nullable=False) 

487 

488 # Relationships 

489 job = relationship("Job", back_populates="updates") 

490 

491 __table_args__ = (CheckConstraint("type IN ('received', 'sent')", name="valid_update_type_values"),) 

492 

493 

494class SpeculativeApplication(Owned, Base): 

495 """Represents a speculative application. 

496 

497 Attributes: 

498 ----------- 

499 - `date` (datetime, optional): The date and time of the application. 

500 - `note` (str, optional): Additional notes or comments about the application. 

501 - `contact_email` (str, optional): Email address used for the application. 

502 

503 Foreign keys: 

504 ------------- 

505 - `company_id` (int): Identifier for the company associated with the application. 

506 

507 Relationships: 

508 -------------- 

509 - `company` (Company): Company object related to the application. 

510 - `contact` (Person): Persons object related to the application.""" 

511 

512 date = Column(TIMESTAMP(timezone=True), nullable=True) 

513 note = Column(String, nullable=True) 

514 contact_email = Column(String, nullable=True) 

515 

516 # Foreign keys 

517 company_id = Column(Integer, ForeignKey("company.id", ondelete="CASCADE"), nullable=False) 

518 

519 # Relationships 

520 company = relationship("Company", back_populates="speculative_applications") 

521 contacts = relationship( 

522 "Person", 

523 secondary=speculative_application_contact_mapping, 

524 back_populates="speculative_applications", 

525 lazy="selectin", 

526 )