Search Requirements

Jod provides two services as of 20 Aug 2025:

Full Time Job Posting (Careers a.k.a JodBoard)
Adhoc Daily Manpower Fulfilment (JodGig)

We represent these by two tables:

gig_jobs
careers_jobs

One requirement is to show both jobs in a single list in the frontend. Especially so when a user does search.

Showing relevant jobs

A user looking for warehouse type of jobs would search "warehouse".

We would not know what type of job the user is looking for before he/she searches.

We also cannot assume the user would use filters, because generally everyone is used to getting accurate results from google search and large e-commerce platforms.

If we only limited the search query "warehouse" (i.e. ?search_query=warehouse) to either Gig jobs or Career jobs, we are limiting visibility of possibly relevant jobs

Assuming we have warehouse jobs in both gig and careers, if a user searches for "warehouse", we would want to show the following.

Jobs

|--------------------------------|
| Warehouse Packer (Adhoc)       |
| $10/hour                       |
| 27 Aug 2025: 1pm - 9pm         |
| ------------------------------ |
| Warehouse Manager (Full Time)  |
| $1000/month                    |
| ------------------------------ |

Getting relevance based on attributes

A trivial solution would be to query the database based on the job titles:

SELECT *
FROM careers_jobs
WHERE title ILIKE "%warehouse%"

This will give us limited results. For example, if we had a careers_job row with the following columns:

title: "Logistics Manager" and the
description: "Manage packages in an e-commerce warehouse"

Based on the description, we can tell that this job is relevant to the search query "warehouse"

However, adding AND description ILIKE "%warehouse%" to the query will be expensive.

careers_jobs.description is a long text
searching text is slow, let alone searching through hundreds of thousands of rows.