Database schema

Every custom table Vakanssi creates: columns, indexes, and relationships, plus how schema migrations and uninstall behave.

On this page(16)

Vakanssi stores hiring data in dedicated, indexed custom tables instead of posts and postmeta. High-volume relational entities (applications, candidates, activity) get real indexes on the columns that hot queries filter by. It is the same reasoning behind WooCommerce’s High-Performance Order Storage. Job postings themselves are the exception: they are a regular custom post type (vakanssi_job) in wp_posts.

The plugin’s own measurements put the admin comfortably fast up to roughly 10,000 to 50,000 applications. Above that it keeps working but slows: at 100,000 applications the default inbox took 592 ms and the dashboard 1.08 s.

The free plugin creates 10 tables. Pro Pro feature, requires a Vakanssi Pro license adds four more (evaluations, candidate_tags, webhook_deliveries, presence) for 14 in total. Schema::extra_tables() (formerly pro_tables()) is the one place that split is declared, and it returns an empty list in the free build, so a free site never creates or expects the Pro tables.

A site that moves from Pro back to the free plugin keeps those four tables, with their data, but the free plugin only partly handles them. Checking Schema::table_exists() at runtime, it still deletes an application’s evaluations rows along with the application, and Remove all applicant data empties all four.

The free plugin does not export candidate tags, evaluations, or stored screening answers, remove a candidate’s tags on erasure or anonymization, or age out webhook_deliveries payloads. That code ships only in the premium build. Uninstall with Delete all data on uninstall on still drops all four tables (see Uninstall).

A few conventions apply throughout:

Naming. Table names are built as {$wpdb->prefix}vakanssi_{name}, where the prefix is your site’s $wpdb->prefix. This page writes them as wp_vakanssi_*; substitute your own prefix if it differs.
Migrations. Tables are created and upgraded by dbDelta(), keyed on the vakanssi_schema_version option. The current schema version is 18. See Migrations.
No foreign-key constraints. Relationships are by convention (id columns plus indexes), which is standard for dbDelta()-managed schemas. The relationship notes under each table describe what references what.
Storage engine. Every table is created with ENGINE=InnoDB, which is what lets a multi-step operation (the applicant-data reset, a migration import batch) run as a genuine all-or-nothing transaction instead of a best-effort sequence of separate statements. Upgrading an existing install converts any table a host previously created on another engine (typically MyISAM) automatically; a host that cannot offer InnoDB at all keeps working, just without that transactional guarantee.
Datetimes. Datetime columns are nullable and stamped in PHP (GMT) for MySQL strict-mode portability.
Access. Each table sits behind a repository class over $wpdb. Repositories are internal API: read data through the REST API and react to changes through hooks rather than querying or writing tables directly.

wp_vakanssi_candidates

One row per person: the CRM core. Identity and deduplication key on the normalized email (email_normalized), stored separately from the display email (email). The identity is nullable so email-less candidates (imports, referrals) are valid; MySQL permits multiple NULLs in a unique index, so they do not collide.

Column	Type	Default / notes
`id`	`bigint(20) unsigned`	Primary key, auto-increment
`email`	`varchar(191)`	`''` (the display email, as typed)
`email_normalized`	`varchar(191)`	`NULL` (dedup identity; unique)
`first_name`	`varchar(191)`	`''`
`last_name`	`varchar(191)`	`''`
`phone`	`varchar(64)`	`''`
`location`	`varchar(191)`	`''`
`source`	`varchar(64)`	`''` (how the person entered the system)
`meta`	`longtext`	`NULL`
`details_version`	`bigint unsigned`	`0` (optimistic-concurrency counter for person-level edits, the same mechanism as on `applications`)
`created_at`	`datetime`	`NULL`
`updated_at`	`datetime`	`NULL`

Indexes: PRIMARY KEY (id); UNIQUE KEY email_normalized (email_normalized); KEY created_at (created_at).

Referenced by applications.candidate_id, files.candidate_id, interviews.candidate_id, and candidate_tags.candidate_id.

wp_vakanssi_applications

The high-velocity entity: one row per application, indexed for the inbox and pipeline queries.

Column	Type	Default / notes
`id`	`bigint(20) unsigned`	Primary key, auto-increment
`job_id`	`bigint(20) unsigned`	`0` (the `vakanssi_job` post id)
`job_title`	`varchar(255)`	`''` (denormalized snapshot, so applications survive job deletion)
`candidate_id`	`bigint(20) unsigned`	`0`
`stage_id`	`bigint(20) unsigned`	`0`
`status`	`varchar(32)`	`'active'` (coarse lifecycle, derived from the stage type)
`source`	`varchar(64)`	`''`
`rating`	`tinyint(3) unsigned`	`0` (0 means unrated)
`assigned_to`	`bigint(20) unsigned`	`0` (assignee user id; 0 means unassigned)
`payload`	`longtext`	`NULL` (submission payload)
`consent_given`	`tinyint(1)`	`0`
`consent_basis`	`varchar(64)`	`''`
`consent_at`	`datetime`	`NULL`
`retention_until`	`datetime`	`NULL` (data-retention deadline)
`anonymized_at`	`datetime`	`NULL` (set by the retention purge when this application’s PII is scrubbed; distinguishes “already anonymised” from “no deadline assigned yet” on a row where `retention_until` is `NULL`)
`details_version`	`bigint unsigned`	`0` (optimistic-concurrency counter for drawer field edits; `claim_details_version()` bumps it only when the caller’s expected value still matches, so a second editor’s stale write loses instead of overwriting)
`created_at`	`datetime`	`NULL`
`updated_at`	`datetime`	`NULL`

Indexes: PRIMARY KEY (id); KEY job_status (job_id, status); KEY status_created (status, created_at); KEY candidate_id (candidate_id); KEY stage_id (stage_id); KEY assigned_to (assigned_to); KEY retention_until (retention_until); KEY created_at (created_at); KEY updated_at (updated_at).

status_created (schema 16) serves the Hiring HQ aggregates, which filter on status alone; job_status’s leftmost column is job_id, so it could not serve those queries.

candidate_id points to candidates.id; stage_id to stages.id; job_id to wp_posts.ID (the vakanssi_job post type); assigned_to to wp_users.ID.

wp_vakanssi_application_meta

Queryable custom application fields without postmeta bloat.

Column	Type	Default / notes
`id`	`bigint(20) unsigned`	Primary key, auto-increment
`application_id`	`bigint(20) unsigned`	`0`
`meta_key`	`varchar(191)`	`''`
`meta_value`	`longtext`	`NULL`

Indexes: PRIMARY KEY (id); UNIQUE KEY application_meta (application_id, meta_key).

The index is UNIQUE, and deliberately: ApplicationMetaRepository::set() deletes then inserts, which is not atomic, so concurrent requests writing the same key could leave duplicate rows. It carries a new name rather than a retyped application_key, because dbDelta() can add an index but never retype one.

application_id points to applications.id.

wp_vakanssi_stages

Pipeline stages, configurable per job: job_id = 0 marks a shared template, and any other value scopes a stage to that job.

Column	Type	Default / notes
`id`	`bigint(20) unsigned`	Primary key, auto-increment
`job_id`	`bigint(20) unsigned`	`0` (0 = template)
`name`	`varchar(191)`	`''`
`slug`	`varchar(191)`	`''` (unique within a job)
`position`	`int(11)`	`0` (sort order within the job)
`type`	`varchar(32)`	`'open'` (lifecycle bucket that drives the application’s derived `status`)
`color`	`varchar(16)`	`''` (board column color)
`created_at`	`datetime`	`NULL`

Indexes: PRIMARY KEY (id); UNIQUE KEY job_slug (job_id, slug); KEY job_position (job_id, position).

job_id points to wp_posts.ID (0 for the template). Referenced by applications.stage_id and interviews.stage_id.

wp_vakanssi_evaluations Pro feature, requires a Vakanssi Pro license

Scorecards: numeric scoring with multiple evaluators, one row per application, evaluator, and interview. A Pro-only table: the free build never creates it.

Column	Type	Default / notes
`id`	`bigint(20) unsigned`	Primary key, auto-increment
`application_id`	`bigint(20) unsigned`	`0`
`evaluator_id`	`bigint(20) unsigned`	`0` (the reviewing user)
`scorecard_id`	`bigint(20) unsigned`	`0`
`interview_id`	`bigint(20) unsigned`	`0` (the interview this scorecard came out of; 0 means not tied to an interview, such as a CV review)
`scores`	`longtext`	`NULL` (JSON map of criterion key to score)
`recommendation`	`varchar(32)`	`''`
`comment`	`longtext`	`NULL`
`created_at`	`datetime`	`NULL`
`updated_at`	`datetime`	`NULL` (set when a scorecard is revised; `NULL` on rows from before schema `18`, read as “not edited”)

Indexes: PRIMARY KEY (id); UNIQUE KEY application_evaluator_interview (application_id, evaluator_id, interview_id); KEY evaluator_id (evaluator_id).

One scorecard per evaluator per interview is enforced in the schema, not just in code, so a reviewer can hold a separate scorecard for each interview round but never two for the same one. There is no separate single-column application_id key: it would be a redundant leftmost prefix of the unique index.

application_id points to applications.id; evaluator_id to wp_users.ID; interview_id to interviews.id (a plain id, not a foreign key).

wp_vakanssi_notes

Internal team notes, written by the free Notes module. The Pro Collaboration module layers @mentions on top, which is what mentions holds. Notes are staff-only and never exposed to candidates.

Column	Type	Default / notes
`id`	`bigint(20) unsigned`	Primary key, auto-increment
`application_id`	`bigint(20) unsigned`	`0`
`user_id`	`bigint(20) unsigned`	`0` (the author)
`body`	`longtext`	`NULL`
`mentions`	`longtext`	`NULL` (JSON array of mentioned user ids)
`created_at`	`datetime`	`NULL`

Indexes: PRIMARY KEY (id); KEY application_created (application_id, created_at).

application_id points to applications.id; user_id to wp_users.ID.

wp_vakanssi_activity

The activity and audit log for compliance and history. Written by the audit logger across the plugin, read for timeline views.

Column	Type	Default / notes
`id`	`bigint(20) unsigned`	Primary key, auto-increment
`entity_type`	`varchar(32)`	`''` (for example `application`)
`entity_id`	`bigint(20) unsigned`	`0`
`user_id`	`bigint(20) unsigned`	`0` (the acting user)
`action`	`varchar(64)`	`''` (for example `stage_changed`)
`changes`	`longtext`	`NULL` (JSON change details)
`ip_address`	`varchar(45)`	`''`
`created_at`	`datetime`	`NULL`

Indexes: PRIMARY KEY (id); KEY entity (entity_type, entity_id); KEY user_id (user_id); KEY created_at (created_at); KEY action (action).

entity_type plus entity_id form a polymorphic reference (for entity_type = 'application', entity_id points to applications.id); user_id points to wp_users.ID.

wp_vakanssi_emails

The email log for deliverability tracking and duplicate suppression.

Column	Type	Default / notes
`id`	`bigint(20) unsigned`	Primary key, auto-increment
`application_id`	`bigint(20) unsigned`	`0` (0 when the email is not application-bound)
`template`	`varchar(64)`	`''` (template key, for example `confirmation`, `reject`)
`dedupe_key`	`varchar(191)`	`NULL` (the send claim; see below)
`recipient`	`varchar(191)`	`''`
`subject`	`text`	`NULL`
`status`	`varchar(32)`	`''`
`error`	`text`	`NULL`
`sent_at`	`datetime`	`NULL`
`created_at`	`datetime`	`NULL`

Indexes: PRIMARY KEY (id); UNIQUE KEY dedupe_key (dedupe_key); KEY application_id (application_id); KEY status (status).

dedupe_key is how one candidate email gets sent once. Before calling wp_mail(), the mailer inserts a status = 'sending' claim row carrying the key, then updates that row to sent or failed.

The UNIQUE index is what makes it work: a second concurrent request’s insert fails, which the mailer reads as “someone else has this one” rather than as an error. MySQL permits many NULLs in a unique index, so rows that predate a claim never collide.

wp_vakanssi_files

CV and attachment metadata. The file bytes are not in the database or the media library: they live in protected storage (by default a guarded folder inside your uploads directory) and are served only through an authenticated, capability-checked endpoint.

Column	Type	Default / notes
`id`	`bigint(20) unsigned`	Primary key, auto-increment
`application_id`	`bigint(20) unsigned`	`0`
`candidate_id`	`bigint(20) unsigned`	`0`
`attachment_id`	`bigint(20) unsigned`	`0` (media attachment id, when one exists)
`path`	`varchar(255)`	`''` (path within protected storage)
`filename`	`varchar(255)`	`''` (the original display filename)
`mime`	`varchar(100)`	`''`
`size`	`bigint(20) unsigned`	`0` (bytes)
`hash`	`varchar(64)`	`''`
`created_at`	`datetime`	`NULL`

Indexes: PRIMARY KEY (id); KEY application_id (application_id); KEY candidate_id (candidate_id).

application_id points to applications.id; candidate_id to candidates.id; attachment_id to wp_posts.ID.

wp_vakanssi_interviews

Scheduled interviews and candidate self-booking. The free Interviews module creates and updates fixed-time interviews here; the Pro Scheduling module Pro feature, requires a Vakanssi Pro license adds candidate self-booking on top. token powers the tokenized public booking page (Pro Scheduling); ics_uid and sequence keep calendar invites stable across reschedules and cancellations.

Column	Type	Default / notes
`id`	`bigint(20) unsigned`	Primary key, auto-increment
`application_id`	`bigint(20) unsigned`	`0`
`candidate_id`	`bigint(20) unsigned`	`0`
`job_title`	`varchar(255)`	`''` (denormalized snapshot)
`organizer_id`	`bigint(20) unsigned`	`0` (the scheduling user)
`interviewers`	`longtext`	`NULL` (JSON array of user ids)
`title`	`varchar(191)`	`''` (the interview type)
`stage_id`	`bigint(20) unsigned`	`0`
`mode`	`varchar(32)`	`'video'`
`location`	`varchar(255)`	`''` (meeting link or address)
`status`	`varchar(32)`	`'scheduled'`
`scheduled_at`	`datetime`	`NULL` (UTC; null while awaiting a candidate’s pick)
`duration`	`smallint(5) unsigned`	`45` (minutes)
`slots`	`longtext`	`NULL` (JSON of proposed times for self-service booking)
`token`	`varchar(64)`	`''` (the booking-page secret)
`candidate_name`	`varchar(191)`	`''` (write-once snapshot, see note below)
`candidate_email`	`varchar(191)`	`''` (write-once snapshot, see note below)
`ics_uid`	`varchar(191)`	`''` (stable iCalendar UID)
`sequence`	`smallint(5) unsigned`	`0` (RFC 5545 revision counter)
`notes`	`longtext`	`NULL` (staff-only; appears in the interviewers’ calendar invite, never the candidate’s)
`created_at`	`datetime`	`NULL`
`updated_at`	`datetime`	`NULL`

Indexes: PRIMARY KEY (id); KEY application_id (application_id); KEY token (token); KEY scheduled_at (scheduled_at).

application_id points to applications.id; candidate_id to candidates.id; organizer_id to wp_users.ID; stage_id to stages.id.

candidate_name and candidate_email are written once, at the point an interview is scheduled or proposed, and nothing updates them afterward. Outbound mail (invites, reschedules, cancellations, resends) resolves the live recipient from candidate_id instead, and falls back to these columns only when the candidate row itself is gone. The columns still drive the drawer’s history view of who an interview was originally booked for.

wp_vakanssi_candidate_tags Pro feature, requires a Vakanssi Pro license

Talent-pool labels, written by the Pro Sourcing module. A Pro-only table. One row per (candidate, tag) pair, indexed both ways so both “tags for this candidate” and “candidates with this tag” are cheap.

Column	Type	Default / notes
`id`	`bigint(20) unsigned`	Primary key, auto-increment
`candidate_id`	`bigint(20) unsigned`	`0`
`tag`	`varchar(64)`	`''`
`created_at`	`datetime`	`NULL`

Indexes: PRIMARY KEY (id); UNIQUE KEY candidate_tag (candidate_id, tag); KEY tag (tag).

candidate_id points to candidates.id.

wp_vakanssi_webhook_deliveries Pro feature, requires a Vakanssi Pro license

The outbound integration delivery log, written by the Pro Integrations module. A Pro-only table. endpoint_id references an endpoint record stored in the vakanssi_webhooks option (endpoints are not a table). The module’s daily cron deletes rows older than 30 days.

Column	Type	Default / notes
`id`	`bigint(20) unsigned`	Primary key, auto-increment
`endpoint_id`	`varchar(64)`	`''` (key of an endpoint in the `vakanssi_webhooks` option)
`event`	`varchar(64)`	`''` (for example `application.created`)
`url`	`varchar(255)`	`''`
`status`	`varchar(16)`	`''` (`sending` while the first attempt is in flight, then `pending`, `failed`, or `success`)
`response_code`	`int(11)`	`0`
`error`	`text`	`NULL`
`payload`	`longtext`	`NULL` (the JSON body, which carries candidate data; see the note below)
`attempts`	`smallint(5) unsigned`	`1`
`next_retry_at`	`datetime`	`NULL`
`created_at`	`datetime`	`NULL`

Indexes: PRIMARY KEY (id); KEY event (event); KEY created_at (created_at); KEY retry (status, next_retry_at).

payload is written only when a delivery may still be sent again. A successful delivery, or one to a destination Vakanssi refused to contact, stores NULL. A failed delivery keeps its payload so the admin Resend button has something to send.

The daily retention purge then bounds that window: it nulls failed payloads after 24 hours, and closes out any delivery still pending after 7 days as failed, dropping its payload. A privacy erasure nulls every payload carrying the candidate’s email, whatever its status.

wp_vakanssi_stage_events

An append-only analytics spine. One row per application created (entered the pipeline) or moved (stage change). It powers the Pro Analytics Pro feature, requires a Vakanssi Pro license funnel, time-in-stage, and trend reporting.

Only the premium build writes it. The free build creates the table but adds no rows; it only purges an application’s events when the application is deleted. On a site that moves to Pro, a one-time backfill rebuilds the stage history from the activity log, without each application’s creating user.

Column	Type	Default / notes
`id`	`bigint(20) unsigned`	Primary key, auto-increment
`application_id`	`bigint(20) unsigned`	`0`
`job_id`	`bigint(20) unsigned`	`0`
`from_stage_id`	`bigint(20) unsigned`	`0`
`to_stage_id`	`bigint(20) unsigned`	`0`
`event_type`	`varchar(32)`	`''` (for example `created`, `moved`)
`status`	`varchar(32)`	`''`
`user_id`	`bigint(20) unsigned`	`0`
`created_at`	`datetime`	`NULL`

Indexes: PRIMARY KEY (id); KEY application_id (application_id); KEY created_at (created_at); KEY to_stage_created (to_stage_id, created_at); KEY event_type_created (event_type, created_at).

application_id points to applications.id; job_id to wp_posts.ID; from_stage_id and to_stage_id to stages.id; user_id to wp_users.ID.

wp_vakanssi_presence Pro feature, requires a Vakanssi Pro license

Who is looking at what, right now: the live “who’s viewing” indicators the Presence module draws on the board and in the candidate drawer. One row per user, overwritten on each heartbeat, so the table stays the size of your team rather than growing with traffic.

Column	Type	Default / notes
`user_id`	`bigint unsigned`	Primary key (one row per user, not per view)
`screen`	`varchar(32)`	`''` (which surface they are on)
`job_id`	`bigint unsigned`	`0`
`open_id`	`bigint unsigned`	`0` (the application whose drawer is open, 0 for none)
`beat_at`	`datetime`	The last heartbeat, used to expire stale rows

Indexes: PRIMARY KEY (user_id); KEY beat_at (beat_at).

No name or avatar is stored: those are resolved from wp_users on read, so a renamed user never shows a stale name and the table holds no duplicated personal data. user_id points to wp_users.ID; job_id to wp_posts.ID; open_id to applications.id.

Presence shares the collaboration feature slug rather than having one of its own, so a Pro plan that includes Collaboration includes this too. A Pro-only table: the free build never creates it. See Collaboration.

Migrations

The Vakanssi\Database\Migrator service owns the schema lifecycle. It compares the stored vakanssi_schema_version option against the shipped Schema::VERSION (currently 18), and also compares which tables the last migration created against the set this build creates, returning early only when both match. This check is cheap enough to run on every init and admin_init.

The table comparison catches a site whose files were swapped for a copy that creates a different set of tables at the same schema version, such as switching between the free and premium builds. When either comparison differs, migration runs:

Loads WordPress’s wp-admin/includes/upgrade.php for dbDelta().
Runs targeted pre-migration cleanup for known version transitions, since dbDelta() cannot drop or re-type an index. For example, the move to schema 17 adds evaluations.interview_id and drops the old application_evaluator key so the wider application_evaluator_interview key can replace it, and the move to 18 adds evaluations.updated_at. Each step checks first and skips a table that is not installed (a Pro table on a free site).
Runs dbDelta() on every table definition this build owns. This is idempotent: missing tables are created and out-of-date columns are reconciled.
Converts any table still left on a non-InnoDB engine to InnoDB, since dbDelta() manages columns and indexes but never rewrites an existing table’s engine.
Stores the new schema version and fires the internal vakanssi/schema/migrated action, but only once every table exists, the schema’s UNIQUE indexes are genuinely in place, and the InnoDB conversion has finished (on a host where InnoDB is available at all); otherwise it leaves the old version stored so the next request retries.

Migrations trigger in two places: activation (forced, so a fresh install always builds the full schema) and init/admin_init after a plugin file update. A front-end visit, a REST request, a cron run, or an administrator loading any wp-admin page all apply the migration equally, with no reactivation needed. Roles sync the same way on their own independent revision (vakanssi_roles_version); see Capabilities & roles.

Uninstall

Deactivating the plugin is non-destructive: it leaves every table, row, role, capability, and option in place. Only deleting the plugin runs the uninstall handler.

Uninstalling always does these things, regardless of settings. The list of what the plugin stores lives in Vakanssi\Core\Uninstall\Inventory, which a test checks against a scan of the source tree.

Removes the three custom roles (plus any legacy wpats_* / hirepress_* roles) and strips all 13 vakanssi_* capabilities from the Administrator role.
Unschedules the plugin’s four recurring cron hooks (vakanssi/cron/retention_purge, webhook_cleanup, webhook_retry, expiry_purge), including their catch-up runs.
Deletes per-user interface state: saved Candidates views, dismissed notices, and the screen options WordPress core stored for the job post type.
Deletes the plugin’s own options: vakanssi_settings, vakanssi_version, vakanssi_schema_version, vakanssi_roles_version, vakanssi_modules, and the rest of the fixed list, plus every vakanssi_import_*, vakanssi_csv_import_lock_*, vakanssi_stage_events_backfill_*, and vakanssi_rc_* (apply-form rate counter) option swept by prefix.
Deletes every vakanssi_* transient and site transient, with their timeout rows.

On multisite, uninstall walks every site on every network and applies each site’s own purge setting to that site only.

Candidate data is kept unless you opt in

Uninstall keeps every wp_vakanssi_* table and all stored CV files by default. They are removed only when the site owner has enabled Delete all data on uninstall (Hiring, then Settings, General tab), which is off by default so removing the plugin never silently deletes hiring records.

When that purge setting (purge_on_uninstall in the vakanssi_settings option) is on, uninstall additionally:

Drops every table under the {prefix}vakanssi_ namespace, found with SHOW TABLES LIKE rather than taken from the running build’s list, so a site that moved from Pro to free loses its leftover Pro tables too. If the host blocks SHOW TABLES, it falls back to the known list.
Recursively deletes the protected CV-storage directory. The deletion is hardened inside ProtectedStorage (ownership sentinel, realpath, symlink rejection) and refuses a denylist of roots: /, ABSPATH, the theme root and its parent, the uploads base directory, and the plugin directory and its parent.
Moves the careers page the setup wizard created to the Trash, where it can still be restored. Only that page is touched: it is identified by its _vakanssi_created_page mark holding its own ID, so a page you picked yourself, or a copy of the wizard’s page, stays.
Deletes the plugin’s metadata on WordPress objects: every post meta key starting with _vakanssi_ (the job fields, such as salary, location, and closing date) and the vakanssi_country term meta on location terms.

Job posts in wp_posts (the vakanssi_job custom post type) and the department and location terms are never deleted in either case. See Backups & your data for the operational guidance.