Data Hierarchy

A high level summary of the Mustard Data Hierarchy.

4 key data tables

4 Key Models

There are 4 key models in the mustard app database. Organisations, Agreements, Profiles, and Users. All tables and business logic branches from these 4 key tables. Please note each model individually and how they relate to each other.

Organisations (organisation_accounts)

These represent each of mustards clients or customers. 1 account/organisation is usually a recruitment organisation, with a database of candidates stored in mustard. This is the root table for all organisation configuration and all related models have foreign keys pointing to this table.

Agreements (organisation_agreements)

As the table naming structure may suggest, organisations can contain many agreements. The history behind the naming of this model, is that each agreement represents the agreement or link between each organisation and candidate. The thinking at the time, was that 1 candidate may have a link to more than one organisation. This evolved. An agreement now directly represents a candidate, and is the root object that all candidate information is linked to. Agreements contain a json “data” field containing most metadata about a candidate, including all custom fields each organisation may require. Legacy features still duplicate this information into legacy tables, but agreement data is guaranteed to be the single source of truth.

Profiles (person_profiles)

Profiles are a legacy model. They were used as part of a legacy feature to enable candidates to sign up directly to an organisation. They were meant to represent the core data about a candidate, with many sub-tables linking to other key candidate information for onboarding. This grew to be complex, and with the next iteration of features this was ignored in favour of using the Agreement model more heavily. As such, any profile created via self sign up will have its data duplicated to all person_* models. But not all agreements will have an associated profile.

Users (auth_users)

This is the typical account model for allowing a user to sign up, log in, etc. We assign roles to these users by simple booleans. Each person_profile is guaranteed to have an associated auth_user for them to use to log in, but agreements do not. Agreements are now a simple record of a candidate. Each employee, administrator, and organisation owner has their own account, and this can be reused for authentication for most new purposes.

Submodels

Organisation Submodels

Simple Organisation Relationships

• organisation_accounts

• organisation_identifiers

• organisation_associations

• organisation_campaigns

• organisation_clients

• organisation_employee_invites

• organisation_employees

Integration Tables

• organisation_external_profile_batches

• organisation_external_profiles

• organisation_external_provider_credentials

• organisation_external_providers

• organisation_transfered_external_profiles

• organisation_integration_credentials

The above tables are all related to the import/export and syncing of agreements to and from Bullhorn and was built to extend for future arbitrary ATS’s where necessary.

Job Posts

• organisation_job_post_accesses

• organisation_job_post_invites

• organisation_job_post_prospects

• organisation_job_posts

Styled CVs

• organisation_styled_cvs

This model is of particular note, it represents both a json representation and direct link to a rendered pdf in S3 available for download.

Agreement Sub-models

• organisation_agreement_filters

• organisation_agreement_imports

• organisation_agreement_lists

• organisation_agreement_lists_agreements

• organisation_agreement_tokens

These are helper tables for some of the business logic required to support our candidate management.

Utilities

• utility_account_theme_styles

• utility_account_themes

• utility_companies

• utility_events

• utility_general_synonyms

• utility_honey_pot_endpoints

• utility_industries

• utility_roles

• utility_skill_dictionaries

• utility_skill_edges

• utility_skills

• utility_webhook_logs

• utility_webhooks

Utilities are mostly back-office tools for mustard to manage its client's accounts. Industries, companies, skills, and roles are examples of common natural language items that we could normalise to more common language (e.g. “RoR” to “Ruby on Rails”), these are the core records to manage this data.

Person/Profile Sub-models

• person_contracts

• person_educations

• person_experiences

• person_industries

• person_locations

• person_profiles

• person_review_profile_requests

• person_roles

• person_skills

• person_timeline_entries

The above are all the independent records to manage user onboarding. As time went on it was clear this list was going to continue growing, which is why we eventually included a document-based approach (organisation_agreements.data) in newer features.