Job Location Consolidation

Job Location Consolidation#

The job location consolidation feature (internally known as Samengevoegde vacatures — “Consolidated Vacancies”) groups vacancies that are identical except for their location into a single consolidated entry. When a single job is posted at multiple locations, users see one listing with a “multiple locations” indicator and a dialog to browse and apply to specific locations.

This feature is gated behind the vacancy-consolidation-enabled feature flag and backed by a PostgreSQL materialized view that pre-computes consolidation groups.

JIRA reference: CMS-1919

Architecture Overview#

The consolidation system spans the full stack:

Backend (Haskell/Yesod)

  • FloHam.Cms.Model.VacancyConsolidation: Persistent model, materialized view lifecycle

  • FloHam.Cms.Model.Extra.VacancyConsolidation: Query functions, haversine distance sorting

  • Handler.Api.VacancyConsolidationCollection: REST API endpoint

  • Render.Data.VacancyConsolidation: Dialog rendering logic

  • Render.Component.ExtraApplicationsVacancyConsolidation: Bulk-apply form

  • Render.Component.VacancyOverview: Search result deduplication

CMS (Elm)

  • Api.Endpoint.vacancyConsolidations: API endpoint definition

  • VacancyConsolidationItem: Auto-generated Elm type via haskell-to-elm

Database

  • vacancy_consolidation: PostgreSQL materialized view

  • domain.consolidation_exclude_filters: Per-domain filter exclusion configuration

Write path — How consolidation data is built:

        flowchart LR
    A[Vacancy Import] --> B[vacancy + location tables]
    B --> C[REFRESH MATERIALIZED VIEW CONCURRENTLY]
    D[domain.consolidation_exclude_filters] -.-> C
    C --> E[vacancy_consolidation view]

Read path — How consolidation data is consumed:

        flowchart LR
    E[vacancy_consolidation view] --> H1[Vacancy Overview]
    E --> H2[Vacancy Detail dialog]
    E --> H3[Extra Applications form]
    E --> H4[CMS Elm API]
    H1 --> R1[Exclude non-representative vacancies]
    H2 --> R2[Show locations sorted by distance]
    H3 --> R3[Bulk-apply to up to 5 locations]
    H4 --> R4[VacancyConsolidationItem JSON]

Feature Flag#

The feature is controlled by the vacancy-consolidation-enabled feature flag.

Property

Value

JSON key

vacancy-consolidation-enabled

Haskell lens

vacancyConsolidationEnabled

Default

False

Category

Search

Source

src/FloHam/Cms/Settings/FeatureFlags.hs

When disabled, all consolidation functions return empty results and no dialog is rendered. The materialized view still exists in the database but is not queried.

Consolidation Logic#

Two or more vacancies are consolidated into a single group when they share all of the following attributes:

  • Title (uses publication_title if present, otherwise title)

  • Subtitle (if present)

  • Status (Published, Draft, etc.)

  • Spotlight flag (is_spotlight)

  • Working hours (min_hours and max_hours)

  • Filter options (the set of vacancy_filter_option IDs, excluding location-based filters)

And they differ in:

  • Location (location_id) — this is what creates the consolidation opportunity

Note

Descriptions are not part of the grouping key. The representative vacancy’s description is used for display. This allows minor location-specific copy differences without breaking consolidation.

Representative Vacancy Selection#

Within each consolidation group, one vacancy is designated as the representative. This is the vacancy that appears in search results and whose details are shown by default.

Selection criteria (in order of precedence):

  1. Most recently updated (updated DESC NULLS LAST)

  2. Highest database ID (id DESC) — as a tiebreaker

Per-Domain Filter Exclusions#

Each domain has a consolidation_exclude_filters column that controls which filter types are excluded from the consolidation grouping key. Filters of these types are ignored when determining whether two vacancies should be consolidated.

Default excluded filter types:

  • City

  • Country

  • Location

  • LocationName

  • Province

  • PublicationDate

  • EndDate

Rationale: Location-based filters (City, Province, etc.) would prevent consolidation if included, since vacancies at different locations naturally have different location filters. Publication and end dates can vary legitimately (e.g., rolling applications) without making the jobs fundamentally different.

Other filter types (Department, Experience, Education, etc.) are included in the grouping key — vacancies with different departments should not be consolidated.

Configuration: The excluded filters are stored as a PostgreSQL text array on the domain table and applied per-domain during materialized view creation.

-- Per-domain filter exclusion applied in the materialized view
AND (vf.filter_type IS NULL
     OR NOT (vf.filter_type::text = ANY(d.consolidation_exclude_filters)))

Database Layer#

Materialized View: vacancy_consolidation#

The core of the consolidation feature is a PostgreSQL materialized view that pre-computes vacancy groups. Using a materialized view avoids expensive grouping queries at request time.

Columns:

#

Column

Type

Description

1

id

VacancyId (PK)

Representative vacancy ID

2

representative_vacancy_id

VacancyId

Same as id, for API consistency

3

domain_id

DomainId

Domain this group belongs to

4

title

Text

Shared title (COALESCE(publication_title, title))

5

subtitle

Text (nullable)

Shared subtitle

6

description

Text

Representative vacancy’s description

7

status

VacancyStatus

Shared vacancy status

8

is_spotlight

Bool

Shared spotlight flag

9

location_count

Int

Number of locations in the group

10

grouped_vacancy_ids

JSONB

Array of VacancyLocationPair objects (see below)

11

option_ids

Int[]

Sorted array of filter option IDs (the consolidation key)

12

consolidation_key

Text

MD5 hash of all grouping fields

13

inserted

UTCTime

Representative vacancy’s insert timestamp

14

updated

UTCTime (nullable)

Representative vacancy’s update timestamp

Warning

Column order in the materialized view must match the VacancyConsolidation Persistent model definition exactly. Adding or reordering columns requires updating both the SQL and the Haskell model.

Indexes:

-- Required for REFRESH MATERIALIZED VIEW CONCURRENTLY
CREATE UNIQUE INDEX vacancy_consolidation_unique_idx ON vacancy_consolidation(id);

-- Performance indexes
CREATE INDEX idx_vacancy_consolidation_domain ON vacancy_consolidation(domain_id);
CREATE INDEX idx_vacancy_consolidation_representative ON vacancy_consolidation(representative_vacancy_id);
CREATE INDEX idx_vacancy_consolidation_key ON vacancy_consolidation(consolidation_key);

VacancyLocationPair Structure#

Each entry in the grouped_vacancy_ids JSONB array represents one vacancy-location pair within a consolidation group:

{
  "vacancyId": 12345,
  "locationName": "Amsterdam",
  "locationId": 67,
  "latitude": 52.37,
  "longitude": 4.89
}

The Haskell type mirrors this structure:

data VacancyLocationPair = VacancyLocationPair
  { vacancyId    :: VacancyId
  , locationName :: Text
  , locationId   :: LocationId
  , latitude     :: Maybe Double
  , longitude    :: Maybe Double
  }

Coordinates are stored for in-memory haversine distance calculations when sorting locations by proximity to a user-selected location.

SQL Query Structure#

The materialized view creation uses two Common Table Expressions (CTEs):

CTE 1 — cleaned_options: Normalizes filter option values by extracting text content from the text-value tagged JSON structure.

CTE 2 — vacancy_options: Pre-computes the sorted array of relevant filter option IDs for each vacancy, applying per-domain filter exclusions. This is the consolidation key.

Main SELECT: Groups vacancies by domain, title, subtitle, status, spotlight flag, working hours, and option IDs. Uses HAVING COUNT(*) > 1 to include only groups with multiple locations.

-- Simplified structure of the materialized view query
WITH
  cleaned_options AS (
    -- Normalize filter option values
    SELECT id, filter_id, vacancy_ids_mirror, ...
    FROM vacancy_filter_option
  ),
  vacancy_options AS (
    -- Per-vacancy option IDs with domain-specific exclusions
    SELECT v.id, ARRAY_AGG(DISTINCT co.id ORDER BY co.id) AS option_ids
    FROM vacancy v
    LEFT JOIN cleaned_options co ON v.id = ANY(co.vacancy_ids_mirror)
    LEFT JOIN vacancy_filter vf ON vf.id = co.filter_id
    LEFT JOIN domain d ON d.id = v.domain_id
    WHERE v.deleted IS NULL
      AND v.location_id IS NOT NULL
      AND (vf.filter_type IS NULL
           OR NOT (vf.filter_type::text = ANY(d.consolidation_exclude_filters)))
    GROUP BY v.id
  )
SELECT
  (array_agg(v.id ORDER BY v.updated DESC NULLS LAST, v.id DESC))[1] as id,
  ...
  COUNT(*)::int as location_count,
  jsonb_agg(jsonb_build_object(
    'vacancyId', v.id, 'locationName', l.name, ...
  )) as grouped_vacancy_ids,
  vo.option_ids,
  md5(CONCAT(v.domain_id::text, '|', ...)) as consolidation_key,
  ...
FROM vacancy v
INNER JOIN vacancy_options vo ON v.id = vo.vacancy_id
INNER JOIN location l ON v.location_id = l.id
WHERE v.deleted IS NULL AND v.location_id IS NOT NULL
GROUP BY v.domain_id, COALESCE(v.publication_title, v.title),
         v.subtitle, v.status, v.is_spotlight, vo.option_ids,
         v.min_hours, v.max_hours
HAVING COUNT(*) > 1;

Consolidation Key#

The consolidation_key is an MD5 hash of the concatenated grouping fields, separated by |:

MD5(domain_id | title | subtitle | status | is_spotlight | option_ids | min_hours | max_hours)

This provides a compact, efficient lookup key for identifying consolidation groups.

View Lifecycle#

The materialized view supports three operations:

refreshMaterializedView

Refreshes the view using REFRESH MATERIALIZED VIEW CONCURRENTLY. This updates the pre-computed data without blocking read queries. Requires a unique index on the view. Called after vacancy data imports.

recreateMaterializedView

Drops and recreates the entire view. Used when the view definition changes (e.g., when per-domain filter exclusions are modified). Sequence: drop, create, add unique index.

dropMaterializedView

Drops the view entirely. Used as part of recreateMaterializedView.

Migrations#

Two database migrations establish the consolidation infrastructure:

  1. 20250924100001_add_vacancy_consolidation_materialized_view.sql — Creates the initial materialized view with hardcoded filter exclusions and performance indexes.

  2. 20251021120544_move_consolidation_exclude_filters_to_domain.sql — Adds the consolidation_exclude_filters column to the domain table, enabling per-domain configuration. The view creation was updated in Haskell to use these per-domain settings.

Persistent Model#

The Haskell model maps directly to the materialized view:

-- src/FloHam/Cms/Model/VacancyConsolidation.hs
VacancyConsolidation
  Id VacancyId
  representativeVacancyId VacancyId
  domainId DomainId
  title Text
  subtitle Text Maybe
  description Text
  status VacancyStatus
  isSpotlight Bool
  locationCount Int
  groupedVacancyIds [VacancyLocationPair]
  optionIds [VacancyFilterOptionId]
  consolidationKey Text
  inserted UTCTime
  updated UTCTime Maybe
  deriving Show Eq

Template Haskell generates lenses for all fields via makeLensesFor, enabling idiomatic lens-based access:

consolidation ^. title
consolidation ^. locationCount
consolidation ^. groupedVacancyIds

Query Functions#

All query functions live in FloHam.Cms.Model.Extra.VacancyConsolidation.

selectConsolidationsByDomain#

selectConsolidationsByDomain :: Key Domain -> DB [Entity VacancyConsolidation]

Returns all published consolidation groups for a domain, ordered by location count descending (largest groups first), then by representative vacancy ID descending.

Used by the vacancy overview to build the exclusion set and by the API endpoint.

Performance: <3ms (indexed on domain_id).

selectConsolidationByVacancyIdCached#

selectConsolidationByVacancyIdCached
  :: FeatureFlags -> Key Vacancy -> Key Domain
  -> Handler (Maybe (Entity VacancyConsolidation))

Finds the consolidation group containing a specific vacancy, whether it is the representative or a grouped member. Results are cached per-request using Yesod’s cacheByGet/cacheBySet.

Returns Nothing if the feature flag is disabled or the vacancy is not part of any group.

Cache key format: consolidation_by_vacancy_#{vacancyK}_domain_#{domainK}

selectConsolidationByVacancyId#

selectConsolidationByVacancyId
  :: Key Vacancy -> Key Domain
  -> DB (Maybe (Entity VacancyConsolidation))

The uncached underlying query. Uses two strategies to find a match:

  1. Direct match on representative_vacancy_id

  2. JSONB containment operator (@>) to search within the grouped_vacancy_ids array

-- JSONB search expression
vacancyIdText = "[{\"vacancyId\": " <> tshow (fromSqlKey vacancyK) <> "}]"

Performance: <5ms.

selectLocationsSorted#

selectLocationsSorted :: Key Domain -> Key Vacancy -> DB [VacancyLocationPair]

Returns the other locations in a consolidation group, sorted by distance from the current vacancy’s location. Excludes the current vacancy from results.

Sorting behaviour:

  • If the current vacancy has coordinates: sort by haversine distance (ascending)

  • If coordinates are unavailable: sort alphabetically by location name

  • Locations without coordinates are placed after those with distances, sorted alphabetically

Haversine Distance Calculation#

The system uses the haversine formula to calculate great-circle distances between two geographic coordinates:

haversine :: Double -> Double -> Double -> Double -> Double
haversine lat1 lon1 lat2 lon2 =
  earthRadius * curr
  where
    toRadians deg = deg * pi / 180
    earthRadius = 6371.0  -- kilometers
    dLat = toRadians (lat2 - lat1)
    dLon = toRadians (lon2 - lon1)
    a = square (sin (dLat / 2))
      + cos (toRadians lat1) * cos (toRadians lat2) * square (sin (dLon / 2))
    curr = 2 * atan2 (sqrt a) (sqrt (1 - a))

This produces distances in kilometers. Coordinates are extracted from the VacancyLocationPair JSONB data, enabling efficient in-memory sorting without additional database queries.

API Endpoint#

GET /api/{domainId}/vacancy-consolidations#

Returns all consolidation groups for a domain as a JSON array.

Handler: Handler.Api.VacancyConsolidationCollection.getVacancyConsolidationCollectionR

Behaviour:

  1. Check vacancy-consolidation-enabled feature flag

  2. If disabled: return 200 OK with empty array []

  3. If enabled: query all consolidation groups for the domain

  4. For each group, load the representative vacancy and map to response item

Response type:

data VacancyConsolidationItem = VacancyConsolidationItem
  { itemVacancyKey       :: Key Vacancy
  , itemTitle             :: Text
  , itemPublicationTitle  :: Maybe Text
  , itemExternalId        :: Text
  }

Example response:

[
  {
    "itemVacancyKey": 12345,
    "itemTitle": "Software Developer",
    "itemPublicationTitle": null,
    "itemExternalId": "EXT-001"
  }
]

The VacancyConsolidationItem type is auto-generated for Elm via haskell-to-elm, producing matching encoders and decoders.

Elm endpoint:

vacancyConsolidations : Id.DomainId -> Endpoint (HasGet {})
vacancyConsolidations domainId =
    endpoint [ Id.toString domainId, "vacancy-consolidations" ]

Vacancy Overview Integration#

The consolidation feature integrates with the vacancy overview to prevent duplicate listings.

applyVacancyConsolidationExclusions#

applyVacancyConsolidationExclusions
  :: ProjectSettings -> Key Domain -> SearchParams
  -> Handler ([Entity VacancyConsolidation], SearchParams)

This function is called during vacancy overview rendering to:

  1. Query all consolidation groups for the domain

  2. Collect all vacancy IDs that are not the representative in each group

  3. Add these IDs to the excludedVacancies set in SearchParams

This ensures only the representative vacancy appears in search results. Other locations remain accessible through the consolidation dialog.

Logic:

For each consolidation group:
  allVacancyIds = [vacancyId from each VacancyLocationPair]
  representativeId = group.representativeVacancyId
  excludedIds = allVacancyIds - representativeId

SearchParams.excludedVacancies = union of all excludedIds

When a user searches with a location filter, the distance map is recalculated against all vacancies in consolidation groups, so the closest location determines the distance shown on the representative vacancy card.

Rendering Components#

Consolidation Dialog#

The consolidation dialog is a modal that displays all available locations for a consolidated vacancy group. It is rendered by Render.Data.VacancyConsolidation using a RenderConfig record:

data RenderConfig = RenderConfig
  { isVacancyOverview    :: Bool
  , componentUuid        :: Maybe Text
  , consolidationEntity  :: Entity VacancyConsolidation
  , currentVacancyId     :: Key Vacancy
  , openDialogId         :: Maybe (Key Vacancy)
  , activeVacancyId      :: Maybe (Key Vacancy)
  , distanceMap          :: Map (Key Vacancy) DistanceInKm
  , selectedLocation     :: Text
  }

Two entry points:

renderBanner

Used in banner vacancy components. Takes a component UUID, calculates the distance map from URL search parameters, and renders the dialog.

renderVacancyOverview

Used in vacancy overview listings. Receives the distance map from the overview’s existing search infrastructure.

Dialog states:

The dialog adapts its UI based on the number of locations and device type:

Location Count

Mobile Behaviour

Desktop Behaviour

< 5

Single state: search + locations

Single state: search + locations

5 – 9

Two states: search only, then locations

Single state: search + locations

>= 10

Two states: search only, then locations

Two states: search only, then locations

In two-state mode:

  • State 1 (search-only): Shows only a location search field

  • State 2 (locations-shown): Shows locations with a go-back button

State transitions are managed via CSS data-* attributes (data-threshold, data-state, data-searched-location) and media queries.

Location sorting in the dialog:

  • If a location is selected (via URL parameter): locations sorted by haversine distance (ascending), with items lacking coordinates sorted alphabetically after

  • If no location is selected: sorted alphabetically by location name

  • Maximum 10 locations displayed

Location search: The dialog includes a keyword search widget configured for location-only search (SearchFields.LocationSearch). When used in the vacancy overview context, it integrates with the overview’s existing search infrastructure. When used in a banner context, it posts to the current page URL.

Templates:

  • templates/component/vacancy-consolidation-dialog.hamlet — Main dialog structure

  • templates/location-with-distance.hamlet — Individual location item with optional distance display

Extra Applications Component#

Render.Component.ExtraApplicationsVacancyConsolidation renders an “apply to multiple locations” form on vacancy detail pages.

Behaviour:

  1. Load the consolidation group for the current vacancy

  2. Retrieve up to 5 other locations via selectLocationsSorted

  3. Render each as a checkbox in a form

  4. On submit, create applications for all selected locations

  5. Display a configurable thank-you message

Template: templates/component/extra-applications-vacancy-consolidation.hamlet

This component is only rendered when the current vacancy belongs to a consolidation group with other locations.

Performance Characteristics#

Operation

Target

Notes

Materialized view refresh

~10–20s

Runs during import, uses CONCURRENTLY to avoid blocking readers

selectConsolidationsByDomain

<3ms

Indexed on domain_id

selectRepresentativeVacancies

<6ms

Uses existing vacancy table indexes

selectConsolidationByVacancyId

<5ms

JSONB @> containment operator

selectLocationsSorted

<2ms

In-memory haversine sort, no additional DB query

Cached consolidation lookup

<1ms

Per-request Handler cache hit

Source File Reference#

Model & Data Layer

  • src/FloHam/Cms/Model/VacancyConsolidation.hs — Persistent model, view lifecycle operations

  • src/FloHam/Cms/Model/Extra/VacancyConsolidation.hs — Query functions, haversine calculation

  • src/FloHam/Cms/Model/Domain.hs — Domain model with consolidationExcludeFilters field

API Layer

  • src/Handler/Api/VacancyConsolidationCollection.hs — GET endpoint

Rendering Layer

  • src/Render/Data/VacancyConsolidation.hs — Dialog rendering and location sorting

  • src/Render/Component/ExtraApplicationsVacancyConsolidation.hs — Bulk-apply component

  • src/Render/Component/VacancyOverview.hs — Search result deduplication (applyVacancyConsolidationExclusions)

Templates

  • templates/component/vacancy-consolidation-dialog.hamlet — Dialog UI

  • templates/component/extra-applications-vacancy-consolidation.hamlet — Bulk-apply form

  • templates/location-with-distance.hamlet — Location item with distance

Database Migrations

  • db/migrations/20250924100001_add_vacancy_consolidation_materialized_view.sql — Initial view

  • db/migrations/20251021120544_move_consolidation_exclude_filters_to_domain.sql — Per-domain config

Frontend

  • components/shared/Api/Endpoint.elm — API endpoint definition

Tests

  • test/FloHam/Cms/Model/VacancyConsolidationSpec.hs — Comprehensive test suite

Testing#

The test suite in VacancyConsolidationSpec covers:

Consolidation with exclude filters

  • Consolidation without any exclude filters

  • Single excluded filter option

  • Multiple excluded filter options

  • All vacancies excluded by a common filter

  • Combined checked and excluded filters

  • Empty exclude filter list

Vacancy search integration

  • Excluding vacancies by specific filter option

  • Combining exclude filters with keyword search

  • Proper exclusion from consolidated vacancies

Edge cases

  • Non-existent filter option in exclude list

  • Exclude filters combined with location filters

  • Exclude filters taking precedence over checked filters

Test fixtures set up:

  • A test domain

  • Two locations (Amsterdam at 52.37/4.89, Rotterdam at 51.92/4.47)

  • Three filter types (Department, Experience, Education)

  • Four vacancies with varying filter combinations

  • Materialized view refresh after fixture creation