DPP-UNI-1 Schema

De Publieke Partner – Universal Job Posting Schema v1.5

ERD DPP-UNI-1 v1.5

Overzicht

Gestandaardiseerde structuur voor Nederlandse publieke sector freelance/interim vacatures gescraped van DAS-platformen.

Voor een niet-technisch overzicht, zie: /docs/handboek/techniek/data-model-overzicht

Conventies

Aspect	Database	Public-facing
Datum/tijd	ISO 8601 (`YYYY-MM-DDTHH:mm:ssZ`)	`DD-MM-YYYY HH:MM`
Decimalen	`.` (`100000.00`)	`,` (`100.000,00`) nl-NL
IDs	`BIGSERIAL` (auto-increment)	-
Null	Onbekend/niet gespecificeerd	-

Timestamp Conventie

Alle tabellen gebruiken:

Field	Type	Description
`created_at`	TIMESTAMP	Record aangemaakt (`DEFAULT CURRENT_TIMESTAMP`)
`updated_at`	TIMESTAMP	Record laatst gewijzigd (`DEFAULT CURRENT_TIMESTAMP`)

Enums

job_status_enum

Value	Description
`draft`	Concept - nog niet gepubliceerd
`published`	Gepubliceerd - live
`closed`	Gesloten - deadline verstreken
`archived`	Gearchiveerd - niet meer actief

org_sector_enum

Value	Description
`public`	Publiek (overheid)
`private`	Privaat
`nonprofit`	Non-profit
`other`	Anders

platform_type_enum

Value	Description
`das`	Dynamisch Aankoop Systeem
`other`	Andere bron

Core Tables

1. job

Kern vacature tabel.

Field	Type	Description
Platform
`platform_id`	BIGINT	FK naar platform
`platform_job_id`	TEXT	Originele ID bronsysteem
Job Info
`job_dpp_reference_id`	TEXT	DPP referentie
`schema_version`	NUMERIC(5,2)	Schema versie
`job_scraped_datetime`	TIMESTAMP	Wanneer gescraped
`job_source_url`	TEXT	Originele URL
`job_title`	TEXT NOT NULL	Functietitel
`job_description_html`	TEXT	Omschrijving (HTML)
`job_requirements`	JSONB	Eisen (array)
`job_selection_criteria`	JSONB	Gunningscriteria
`job_status`	ENUM	Status
Dates
`job_start_datetime`	TIMESTAMP	Startdatum
`job_end_datetime`	TIMESTAMP	Einddatum
`job_closure_datetime`	TIMESTAMP	Sluitingsdatum
`job_published_at`	TIMESTAMP	Gepubliceerd
`job_closed_at`	TIMESTAMP	Gesloten
Classification
`sector_id`	BIGINT	FK naar sector
`education_level`	TEXT	Opleidingsniveau

v1.5 Note: role_id and theme_id removed. Use job_role and job_theme junction tables for M:M. | Contract | | | | contract_min_rate_eur | NUMERIC(10,2) | Min uurtarief | | contract_max_rate_eur | NUMERIC(10,2) | Max uurtarief | | contract_duration_months | INT | Duur (maanden) | | contract_min_hours_per_week | INT | Min uren/week | | contract_max_hours_per_week | INT | Max uren/week | | contract_extension | JSONB | Verlengingen | | contract_gdpr_requirements | TEXT[] | AVG vereisten | | contract_submission_requirements | JSONB | Indieningsvereisten | | contract_das_requirements | JSONB | DAS vereisten | | Location | | | | work_location_id | BIGINT | FK naar work_location |

contract_extension Sub-schema

{
  "status": "yes",
  "count": 2,
  "extension_months": 6
}

Field	Values
`status`	`yes`, `no`, `possible`, `onbekend`
`count`	Aantal verlengingen
`extension_months`	Maanden per verlenging

contract_submission_requirements

{
  "motivation_required": true,
  "max_candidates": 3,
  "allowed_file_types": ["pdf", "docx"],
  "max_cv_pages": 5,
  "max_motivation_chars": 2000
}

2. platform

DAS bronnen.

Field	Type	Description	Example
`name`	TEXT	Platform naam	STRIIVE
`type`	ENUM	Type	`das`
`description`	TEXT	Beschrijving
`website`	TEXT	URL	https://striive.com

Actieve Platformen: STRIIVE, Flextender, Yacht, Maandag

3. headquarters

Opdrachtgever organisaties.

Field	Type	Description	Example
`name`	TEXT	Naam	Gemeente Utrecht
`street`	TEXT	Straat	Stadhuisplein
`street_number`	TEXT	Nummer	1
`street_number_suffix`	TEXT	Toevoeging	A
`postal_code`	TEXT	Postcode	3511 AA
`city`	TEXT	Stad	Utrecht
`phone`	VARCHAR(20)	Telefoon	+31 30 286 0000
`website`	TEXT	Website	https://www.utrecht.nl
`logo_url`	TEXT	Logo (intern)
`source_logo_url`	TEXT	Logo (origineel)
`socials`	JSONB	Social links	`{"linkedin":"..."}`
`region_id`	BIGINT	FK naar region
`org_type_id`	BIGINT	FK naar org_type

4. contact

Contactpersonen.

Field	Type	Description
`first_name`	TEXT	Voornaam
`last_name`	TEXT	Achternaam
`email`	CITEXT	E-mail (validated)
`phone_number`	VARCHAR(20)	Telefoon
`website`	TEXT	Website

5. work_location

Standplaats.

Field	Type	Description	Example
`headquarters_id`	BIGINT	FK headquarters
`contact_id`	BIGINT	FK contact
`coordinates`	JSONB	GPS	`{"lat": 52.0907, "lng": 5.1214}`

6. domain

Multi-site support.

Field	Type	Description
`name`	TEXT	Domain naam
`description`	TEXT	Beschrijving
`url`	TEXT	URL
`hosting_domain`	TEXT	Hosting
`cms`	TEXT	CMS type

7. job_contract_type

Junction tabel (M:M job ↔ contract_type).

Field	Type	Description
`job_id`	BIGINT	FK job
`contract_type_id`	BIGINT	FK contract_type

8. job_role (NEW v1.5)

Junction tabel (M:M job ↔ role).

Field	Type	Description
`job_id`	BIGINT	FK job
`role_id`	BIGINT	FK role

A job can have multiple roles (e.g., "Developer" + "Tech Lead").

9. job_theme (NEW v1.5)

Junction tabel (M:M job ↔ theme).

Field	Type	Description
`job_id`	BIGINT	FK job
`theme_id`	BIGINT	FK theme

A job can have multiple themes (e.g., "Digitalisering" + "Datagedreven werken").

10. job_document

Bijlagen (1:M met job).

Field	Type	Description
`job_id`	BIGINT	FK job
`doc_type`	TEXT	`source_html`, `tender_pdf`, `annex`, `terms`
`filename`	TEXT	Bestandsnaam
`file_size_bytes`	BIGINT	Grootte
`storage_path`	TEXT	Supabase path
`url`	TEXT	URL
`content_type`	TEXT	MIME type
`blob`	BYTEA	Binary (kleine files)
`parsed_text`	TEXT	AI-geparsed
`parse_status`	TEXT	`pending`, `completed`, `failed`

11. job_posting

Schema snapshots.

Field	Type	Description
`job_id`	BIGINT	FK job
`schema_version`	TEXT	Versie
`schema_json`	JSONB	JSON
`schema_hash`	TEXT	Hash

Integration Tables

integration_system

Field	Type	Description
`job_id`	BIGINT	FK job
`system_id`	TEXT	TESS, Flowlu
`portfolio_id`	TEXT	Portfolio ID
`org_id`	TEXT	Org ID
`project_id`	TEXT	Project ID

integration_frontend

Field	Type	Description
`job_id`	BIGINT	FK job
`domain_id`	BIGINT	FK domain
`post_id`	BIGINT	WordPress ID
`post_url`	TEXT	URL
`body_raw`	TEXT	Content
`metadata_description`	TEXT	SEO
`featured_media`	TEXT	Image

newsletter_job

Field	Type	Description
`newsletter_id`	BIGINT	FK newsletter
`job_id`	BIGINT	FK job

Reference Tables

region

Nederlandse provincies: Noord-Holland, Zuid-Holland, Utrecht, Gelderland, Noord-Brabant, Overijssel, Limburg, Flevoland, Groningen, Friesland, Drenthe, Zeeland

sector

Vakgebieden: Beleid en Bestuurlijke zaken, Communicatie en Voorlichting, Financien en Economische zaken, ICT en Automatisering, Juridisch, HR en Personeel

role

Functierollen (intern beheerd).

theme

Website thema's (intern beheerd).

org_type

Field	Type	Description
`org_sector`	ENUM	public/private/nonprofit/other
`name`	TEXT	Gemeente, Waterschap, etc.

contract_type

id	name	description
1	ZZP	Zelfstandige zonder personeel
2	Detachering	Via payroll organisatie
3	Payroll	Payroll constructie
4	Interim	Tijdelijke management positie

VBout shortcodes.

Version History

Version	Date	Changes
v1.5	2026-01-23	M:M roles/themes via `job_role` and `job_theme` junction tables
v1.4	2026-01-22	DDL alignment, flat ContractType, M:M junction, domain/job_posting tables
v1.3	2026-01-22	Platform rename, contact table, timestamps
v1.2	2026-01	Initial documented version
v1.1	2026-01	ISO-8601 dates, reference tables

Versiegeschiedenis wordt intern beheerd.

DPP-UNI-1 – De Publieke Partner Universal Job Posting Schema v1.5