Introduction

Before automating anything or designing a user interface, I needed to make sure the data model was solid. Since the Talent Intake System relies on capturing and relating candidates, resumes, and job applications, I started by designing a structure in Dataverse that could support flexible workflows, easy querying, and clean integration with forms and flows.

In this post, I’ll walk through the four key tables that make up the foundation of the solution, how they relate to one another, and some of the decisions I made to keep the model lean but extensible.

Core Tables and Relationship Design

The solution uses four main Dataverse tables:

Table	Purpose
Candidate	Stores applicant information like name, email, and contact details.
Resume	Manages uploaded resume files and links back to the candidate.
Application	Represents a submitted application tied to a specific job posting.
Job Posting	Contains open positions visible on the public Power Pages form.

🧑‍💼 Candidate

Field Name	Type	Purpose
Full Name	String	Candidate’s full name.
Email	String	Candidate’s email address (for contact or login).

📄 Resume

Field Name	Type	Purpose
Candidate Name	Lookup (Candidate)	Links this resume to a candidate.
Resume Title	String	Descriptive title for the resume.
Resume	File (Virtual)	The uploaded resume document.
Resume_Name	String	Internal or uploaded file name.
Submission Date	DateTime	When the resume was submitted (e.g., via portal or form).

📝 Application

Field Name	Type	Purpose
Job Title	Lookup (Job Posting)	The job that this application is associated with.
Resume	Lookup (Resume)	The resume being submitted for this application.

📢 Job Posting

Field Name	Type	Purpose
Job Title	String	Name/title of the open position.
Department	String	Department within the organization.
Location	String	Where the job is based (or remote).
Security Clearance Requirement	String	Any security clearance required for the job.
Job Listing	Memo	Rich text job description body.
Skills	Memo	List of skills required or desired.
Work Experience	String	Required prior work experience.
Hiring Manager	Lookup (User)	Person overseeing the hiring process.
HR Coordinator	Lookup (User)	Person supporting the application process.

erDiagram
    Candidate ||--o{ Resume : "submits"
    Resume ||--o{ Application : "is used in"
    JobPosting ||--o{ Application : "is for"
    JobPosting }o--|| User : "Hiring Manager"
    JobPosting }o--|| User : "HR Coordinator"

    Candidate {
        string FullName
        string Email
    }

    Resume {
        string ResumeTitle
        file ResumeFile
        string ResumeFileName
        datetime SubmissionDate
    }

    JobPosting {
        string JobTitle
        string Department
        string Location
        string SecurityClearanceRequirement
        string Skills
        string WorkExperience
    }

    Application {
        string PlaceholderField
    }

    User {
        string Name
    }

This setup reflects the natural flow of a typical applicant experience: the candidate enters their details, uploads a resume, and then applies for a specific position.

Relationship Design

Here’s how the tables are connected in the Talent Intake System:

Candidate (1) → (N) Resume
Each candidate can have multiple resumes over time. This allows for versioning or updates without altering the original candidate record.
Resume (1) → (N) Application
A single resume can be used for multiple applications. This supports scenarios where a candidate submits the same resume to different job postings, or where applications are versioned without re-uploading files.
Application (N) → (1) Job Posting
Multiple applications can be submitted to the same job posting. This supports both single and recurring job openings.

Why Resume and Application Are Separated

The separation between Resume and Application is intentional and strategic:

Resumability: A candidate might begin an application, revise their resume, or use the same resume across multiple applications.
Auditability: It preserves submission history and allows tracking how many times a resume was submitted and to which postings.
Flexibility: Future enhancements like tagging resumes or attaching multiple documents (e.g., cover letters) can be isolated at the resume level without cluttering application logic.

This design keeps the data model normalized and scalable—ideal for a job portal or recruiting process.

Design Considerations

While keeping the schema straightforward, I added a few thoughtful touches to support future enhancements:

Optional Fields – Fields like “Resume Status” and “Application Source” provide hooks for future automation or reporting without overcomplicating the initial build.
Custom File Handling – Resumes are stored as attachments in the Resume table, not embedded in the Candidate table. This keeps the file logic isolated and easier to manage in flows.
Lean First, Expand Later – I avoided overengineering at this stage. The goal was to create a flexible, functional structure that could support the initial workflow but grow with future needs like approvals, interview tracking, or feedback.

Model Driven App

The Model-Driven App serves as the primary interface for HR professionals to manage job postings and oversee candidate applications. While the app provides full access to create and update postings, most day-to-day interactions—such as conducting interviews or reviewing applicant data—are streamlined through integrated Teams Adaptive Cards and Power BI dashboards. This approach keeps the app focused on administrative setup while shifting operational tasks to more natural, role-specific tools.

App and Form Design

The MDA is a simple app with the four tables added.

Job Posting

The job posting form consists of two tabs. The first is what allows HR professionals to enter in the job details. The second are the prompt outputs of three AI builder prompts, breaking down the job posting for future use.

Candidates

The Candidate table uses a straightforward, single-tab form. Since the candidate’s Name and Email are captured at the time of record creation—typically from the public Power Pages form—these fields are set to read-only to preserve data integrity. The only editable field on the form is Phone Number, which allows internal users to add or update contact information if needed.

This approach ensures that once a candidate is created, their core identity remains unchanged, while still allowing HR staff the flexibility to complete missing details or make necessary updates.

Resumes

This table and its form are used to connect the Candidate to a Resume.

Application

The Application table and its main form are the core of the MDA. It has four tabs and a Business Process Flow (BPF) to manage the process.

The first tab has all the information about the Resume being submitted, when it was submitted, the resume, and the overall assessment of the applicants resume.

The Resume tab captures all evaluations tied to the submitted resume. These fields are automatically populated by Copilot Studio agents as part of the application submission workflow. This approach ensures consistency in how resumes are assessed and minimizes the need for manual data entry by HR staff.

The Job Posting tab on the form serves as a snapshot of the job details at the time of submission. This design choice ensures that if the original job posting is later updated or modified, the application retains a historical record of what the position looked like when the candidate applied. This is especially useful for audit scenarios or when reviewing application context after a job has evolved.

The Assessment tab is where Copilot Studio agents evaluate the resume against the job posting criteria. As part of this automated process, the agent assigns a score from 1 to 5 based on the alignment between the applicant’s qualifications and the job requirements.

The Interview tab captures feedback from all stages of the human interview process. It’s organized into three sections—HR, Hiring Manager, and Overall Assessment. The HR and Hiring Manager sections are filled out during their respective interviews, while the Overall Assessment is automatically generated by an agent based on those inputs. This ensures the process remains efficient without losing the value of human insight.

Lessons Learned

One early lesson was the importance of modeling around the user experience, not just the data. For example, separating Resume from Candidate helped reduce complexity when handling file uploads and made it easier to validate submissions on the front end. It also improved flow performance by minimizing the size of the Candidate record payload.

Additionally, designing with Power Pages in mind meant I had to pay close attention to relationships and permissions—especially since the site is anonymous and needed to map submissions without requiring sign-in.

Up Next: Power Pages

With the data model in place, the next step was making it accessible to applicants. In the next post, I’ll walk through how I used Power Pages to build a multi-step, user-friendly application form—complete with anonymous access, file upload handling, and clean navigation between stages.

Copilot Studio and AI builder – Better together. Post 2 – Building the Core: Dataverse Table Relationship Design, and Model Driven App

Introduction