People and skills
Resources, Employees, Skills, ResourceSkills, SkillCertifications.
- Resources
- Employees
- Skills
- ResourceSkills
- SkillCertifications
Data model
Records and relationships behind Resource Planning System.
21 tables · 38 relationships
How the model is organised
Capacity planning
WeeklyCapacity, PTOImports, Assignments, AssignmentPeriods.
- WeeklyCapacity
- PTOImports
- Assignments
- AssignmentPeriods
Staffing requests
ResourceRequests, RequestSkills, RequestSuggestions, Projects.
- ResourceRequests
- RequestSkills
- RequestSuggestions
- Projects
Assignments and substitutions
Assignments, AssignmentPeriods, Substitutions, ResourceRequests.
- Assignments
- AssignmentPeriods
- Substitutions
- ResourceRequests
Rates and forecasts
RateCards, RateCardRows, Assignments, AssignmentPeriods, Practices.
- RateCards
- RateCardRows
- Assignments
- AssignmentPeriods
- Practices
Utilisation and bench
TimeActuals, AssignmentPeriods, Utilization, Bench, WeeklyCapacity.
- TimeActuals
- AssignmentPeriods
- Utilization
- Bench
- WeeklyCapacity
Keys and references
Employee and project mirrors retain source workspace and identity. AssignmentPeriods derive Resource from their parent. TimeActuals is unique by source time entry and revision; accepted history is preserved through linked corrections.
Migration order
- Bind Employees and Resources to verified principals and source systems.
- Load Practices, Roles, Skills and reviewed ResourceSkills evidence.
- Reconcile approved leave, holidays and complete weekly capacity inputs.
- Load effective RateCards, accepted requests and active Assignments with their exact periods.
- Reconcile source-approved TimeActuals, prior corrections and project memberships.
- Configure scopes and independent decisions; verify capacity, replay, substitution and rate cases.
Tables
21 tables
ResourcesThe person-level resource record — one row per billable ormaster
| Field | Type | Required | Details |
|---|---|---|---|
ResourceCode | text | yes | |
Employee | reference | → Employees (read-only HR mirror; null for non-employee resources) | |
FullName | text | yes | |
Email | text | yes | |
Practice | reference | yes | → Practices |
PrimaryRole | reference | yes | → Roles |
ResourceType | select | yes | FTE, Contractor, Vendor, Intern |
Location | text | US city / state | |
HomeState | select | US state code | |
BaseHoursPerWeek | number | yes | Default 40 for FTE |
BillRate | currency | Per hour, USD; masked except Resource Manager / Finance / Admin | |
CostRate | currency | Per hour, USD; masked except scoped Finance; Admin-only configuration does not grant cost access | |
ManagerOfRecord | reference | → Resources (self) — staffing manager, not HR manager | |
StartDate | date | yes | Roster entry date |
EndDate | date | Roster exit date | |
Status | select | yes | Active, OnLeave, Terminated, PendingStart |
Active | toggle | yes | |
PrincipalID | text | Verified platform principal; one active resource binding per person and workspace | |
SourceWorkspace | text | Configured source HR or supplier identity workspace |
SkillsThe catalog of named skills used to describe what a Resource canmaster
| Field | Type | Required | Details |
|---|---|---|---|
SkillCode | text | yes | |
Name | text | yes | |
Category | select | yes | Technical, Functional, Industry, Language, Methodology |
Description | text | ||
ProficiencyScale | select | yes | OneToFive, NoviceToExpert |
RetiredBy | reference | → Skills (self) — replacement skill when retired | |
Active | toggle | yes |
ResourceSkillsThe Resource × Skill matrix with proficiency, last-used, and a Line item of Resources.line item
| Field | Type | Required | Details |
|---|---|---|---|
Resource | reference | yes | → Resources |
Skill | reference | yes | → Skills |
Proficiency | number | yes | 1-5 |
YearsOfExperience | number | ||
LastUsedAt | date | Drives stale-skill flagging | |
Source | select | yes | SelfReported, ManagerAttested, CertificationDerived, ProjectDerived |
AttestedBy | reference | → Resources (Manager who attested) | |
AttestedAt | date | ||
Active | toggle | yes |
SkillCertificationsFormal certifications backing a ResourceSkills row — the Line item of ResourceSkills.line item
| Field | Type | Required | Details |
|---|---|---|---|
ResourceSkill | reference | yes | → ResourceSkills |
CertificationName | text | yes | |
Issuer | text | yes | e.g. AWS, Microsoft, PMI, Salesforce |
CertificateNumber | text | ||
IssuedAt | date | yes | |
ExpiresAt | date | ||
EvidenceURL | text | Link to PDF / verifier | |
VerifiedBy | reference | → Resources; independent scoped Resource Manager, not an Admin-only configuration grant | |
Status | select | yes | Active, Expired, Revoked, PendingVerify |
PracticesOrganizational practice unit — a Practice owns a set ofmaster
| Field | Type | Required | Details |
|---|---|---|---|
PracticeCode | text | yes | |
Name | text | yes | |
Description | text | ||
PracticeLead | reference | yes | → Resources |
Region | select | Northeast, Southeast, Midwest, West, South, National | |
Active | toggle | yes | |
AllowOvercommit | toggle | Default false; permitted documented independent capacity exceptions only |
RolesGeneric role catalog — Senior Engineer, Tech Lead, Projectmaster
| Field | Type | Required | Details |
|---|---|---|---|
RoleCode | text | yes | |
Name | text | yes | |
Practice | reference | → Practices (null = cross-practice) | |
Level | select | yes | Junior, Mid, Senior, Lead, Principal, Director |
DefaultBillRate | currency | Per hour, USD; masked except Resource Manager / Finance / Sales Engineer / Admin (Sales Engineer needs catalog default for opportunity quoting alongside RateCardRows.BillRate) | |
Description | text | ||
Active | toggle | yes |
RateCardsVersioned rate cards per Practice (and optionally per US region).master
| Field | Type | Required | Details |
|---|---|---|---|
RateCardCode | text | yes | |
Name | text | yes | |
Practice | reference | yes | → Practices |
Region | select | Northeast, Southeast, Midwest, West, South, National | |
Currency | select | yes | USD |
EffectiveFrom | date | yes | |
EffectiveTo | date | ||
Status | select | yes | Draft, Active, Superseded, Retired |
ApprovedBy | reference | → Resources (Practice Lead) | |
FinanceApprovedBy | reference | → Resources (Finance) | |
ApprovedAt | date | ||
PreparedBy | reference | → Resources; immutable preparing principal | |
FinanceApprovedAt | datetime | Distinct Finance approval timestamp |
RateCardRowsPer-Role rate row inside a RateCard. The lookup grain for Line item of RateCards.line item
| Field | Type | Required | Details |
|---|---|---|---|
RateCard | reference | yes | → RateCards |
Role | reference | yes | → Roles |
BillRate | currency | yes | Per hour, USD |
StandardCost | currency | Per hour, USD; protected Finance-only input, masked from all other readers of RateCardRows | |
MinDiscountPct | number | 0-100; floor for project-level discounting |
WeeklyCapacityPer-Resource per-Week capacity grid. Computed nightly bytransaction
| Field | Type | Required | Details |
|---|---|---|---|
Resource | reference | yes | → Resources |
WeekStart | date | yes | Monday |
WeekEnd | date | yes | Sunday |
BaseHours | number | yes | From Resources.BaseHoursPerWeek |
PTOHours | number | yes | Approved leave in week |
HolidayHours | number | yes | Public + company holidays in week |
AvailableHours | formula | yes | fx: MAX(0, BaseHours − nonoverlapping approved PTOHours − HolidayHours) |
AllocatedHours | formula | yes | fx: SUM(AssignmentPeriods.AllocationHours for this Resource and WeekStart where Assignment.Status IN Active,Completed,Substituted AND period Status IN Planned,InProgress,Closed,Frozen; use clipped accepted intervals, exclude cancelled periods) |
RemainingHours | formula | yes | fx: AvailableHours − AllocatedHours |
ObservedAt | datetime | Latest successful capacity-feed calculation | |
InputsComplete | toggle | False when required leave/calendar source is missing or stale |
PTOImportsInbound feed from `hrms-leave-attendance` — approved leave andtransaction
| Field | Type | Required | Details |
|---|---|---|---|
Resource | reference | yes | → Resources |
Date | date | yes | |
Hours | number | yes | |
LeaveType | select | yes | PTO, Sick, Holiday, Bereavement, Jury, Unpaid, Other |
SourceLeaveID | text | yes | ID in hrms-leave-attendance for traceback |
Status | select | yes | Approved, Cancelled |
ImportedAt | date | yes |
ResourceRequestsDemand from a Project — "we need a Senior Data Engineer withtransaction
| Field | Type | Required | Details |
|---|---|---|---|
RequestNumber | text | yes | |
Project | reference | yes | → Projects |
RequestedBy | reference | yes | → Resources (Project Manager) |
RoleNeeded | reference | yes | → Roles |
Practice | reference | → Practices (defaults from RoleNeeded.Practice) | |
StartDate | date | yes | |
EndDate | date | yes | |
AllocationPct | number | yes | 0-100 |
Priority | select | yes | Low, Medium, High, Critical |
Justification | text | ||
LocationPreference | text | US city / state / Remote | |
MaxBillRate | currency | Per hour, USD ceiling | |
MatchedResource | reference | → Resources (set on approval) | |
Status | select | yes | Draft, Submitted, PendingApproval, Approved, Rejected, Cancelled, Fulfilled |
Approver | reference | → Resources (Resource Manager) | |
ApprovedAt | date | ||
RejectionReason | text | ||
CapacitySnapshot | longtext | Reviewed weekly capacity versions and conflict details | |
OvercommitApprovedBy | reference | → Resources; independent Resource Manager acknowledgment under enabled practice policy | |
OvercommitReason | longtext | Required for any permitted capacity exception |
RequestSkillsRequired skills for a ResourceRequest with minimum Line item of ResourceRequests.line item
| Field | Type | Required | Details |
|---|---|---|---|
ResourceRequest | reference | yes | → ResourceRequests |
Skill | reference | yes | → Skills |
MinProficiency | number | yes | 1-5 |
Importance | select | yes | MustHave, NiceToHave |
Notes | text |
RequestSuggestionsTop-N candidate Resources scored by `skill-match-suggester` for a Line item of ResourceRequests.line item
| Field | Type | Required | Details |
|---|---|---|---|
ResourceRequest | reference | yes | → ResourceRequests |
Resource | reference | yes | → Resources |
MatchScore | number | yes | Composite 0-100 score from skill-match-suggester |
SkillMatchPct | number | % of required skills met at min proficiency | |
ProficiencyDelta | number | Average proficiency above required | |
CapacityFitPct | number | % of requested allocation that fits in available capacity | |
BillRateFitPct | number | % alignment with target bill rate | |
Rank | number | yes | 1 = top suggestion |
SuggestedAt | date | yes |
AssignmentsThe staffing record — a Resource on a Project at a Role at antransaction
| Field | Type | Required | Details |
|---|---|---|---|
AssignmentNumber | text | yes | |
Resource | reference | yes | → Resources |
Project | reference | yes | → Projects |
ResourceRequest | reference | → ResourceRequests (null for direct-staffed) | |
Role | reference | yes | → Roles |
RateCard | reference | → RateCards (resolved from Practice + StartDate) | |
BillRate | currency | yes | Per hour, USD; resolved from RateCardRows on create, frozen at assign |
CostRate | currency | Per hour, USD; from Resources.CostRate snapshot | |
StartDate | date | yes | |
EndDate | date | yes | |
AllocationPct | number | yes | 0-100 average across span |
Billable | toggle | yes | False for internal / investment work |
OverAllocated | toggle | yes | Set true by over-allocation-alert when sum > capacity |
Status | select | yes | Draft, Active, Completed, Cancelled, Substituted |
AssignedBy | reference | yes | → Resources (Resource Manager) |
AssignedAt | date | yes | |
ProjectSyncStatus | select | Pending, Succeeded, Failed, Unknown; staffing membership handoff is separate from local assignment | |
ProjectSyncKey | text | Stable project membership command identity | |
ProjectSyncReceipt | text | Verified target membership acknowledgement |
AssignmentPeriodsWeek-by-week allocation rows for an Assignment. Generated on Line item of Assignments.line item
| Field | Type | Required | Details |
|---|---|---|---|
Assignment | reference | yes | → Assignments |
Resource | reference | yes | → Resources; server-derived from Assignment.Resource; reject any mismatch |
WeekStart | date | yes | Monday |
WeekEnd | date | yes | Sunday |
AllocationHours | number | yes | Planned hours for the week |
ActualHours | number | Reconciled from approved Timesheets | |
VarianceHours | formula | yes | fx: ActualHours − AllocationHours |
Status | select | yes | Planned, InProgress, Closed, Frozen, Cancelled |
PeriodStart | date | yes | Actual accepted allocation interval start, clipped to assignment StartDate and this WeekStart |
PeriodEnd | date | yes | Actual accepted allocation interval end, clipped to assignment EndDate and this WeekEnd |
SubstitutionsA formal swap mid-Assignment — Resource A rotates off, Resourcetransaction
| Field | Type | Required | Details |
|---|---|---|---|
SubstitutionNumber | text | yes | |
OriginalAssignment | reference | yes | → Assignments |
ReplacementAssignment | reference | → Assignments (created on approval) | |
OutgoingResource | reference | yes | → Resources |
IncomingResource | reference | yes | → Resources |
EffectiveDate | date | yes | |
Reason | select | yes | Resignation, Reassignment, MedicalLeave, PerformanceSwap, ClientRequest, Other |
Notes | text | ||
ApprovedBy | reference | → Resources (Resource Manager) | |
Status | select | yes | Proposed, Approved, Rejected, Completed |
ProposedBy | reference | → Resources; independent of ApprovedBy |
BenchDerived (workflow-written) row per Resource flagged astransaction
| Field | Type | Required | Details |
|---|---|---|---|
Resource | reference | yes | → Resources |
FlaggedAt | date | yes | |
ForwardWindowWeeks | number | yes | Default 4 |
AverageUtilizationPct | number | Forward accepted AllocationHours / complete AvailableHours * 100 across the window; may exceed 100 for reviewed overcommit, null when denominator is zero or inputs incomplete | |
ProjectedAvailableHours | number | yes | Sum of RemainingHours across the window |
Practice | reference | yes | → Practices |
ManagerOfRecord | reference | → Resources | |
Notes | text | Practice Lead annotation | |
Status | select | yes | Active, Cleared, AcceptedInvestment |
UtilizationPer-Resource per-Period rollup of allocation, billable hours,transaction
| Field | Type | Required | Details |
|---|---|---|---|
Resource | reference | yes | → Resources |
PeriodStart | date | yes | Monday |
PeriodEnd | date | yes | Sunday |
AvailableHours | number | yes | From WeeklyCapacity |
AllocatedHours | number | yes | From AssignmentPeriods.AllocationHours |
ActualHours | number | yes | From AssignmentPeriods.ActualHours |
BillableHours | number | yes | Sum effective source-approved TimeActuals.Hours where Billable=true for this Resource and reporting period |
NonBillableHours | number | yes | Sum effective source-approved TimeActuals.Hours where Billable=false for this Resource and reporting period |
UtilizationPct | formula | yes | fx: AllocatedHours ÷ AvailableHours × 100 when AvailableHours > 0; otherwise null with zero-capacity flag |
BillableUtilizationPct | formula | yes | fx: BillableHours ÷ AvailableHours × 100 when AvailableHours > 0; otherwise null with zero-capacity flag |
ComputedAt | date | yes |
EmployeesRead-only identity mirror from Core HR; employment records and payroll remain owned by HR.master
| Field | Type | Required | Details |
|---|---|---|---|
EmployeeNumber | text | yes | |
SourceEmployeeID | text | yes | Unique in the configured source HR workspace |
FullName | text | yes | |
PrincipalID | text | Verified authenticated principal binding | |
Active | toggle | yes |
ProjectsScoped delivery-project mirror used for staffing; tasks, delivery approvals and billing remain in the connected project app.master
| Field | Type | Required | Details |
|---|---|---|---|
ProjectNumber | text | yes | |
SourceProjectID | text | yes | Unique in the configured source project workspace |
Name | text | yes | |
ProjectManager | reference | yes | → Resources |
Status | select | yes | Planning, Active, OnHold, Closed |
ObservedAt | datetime | yes | Latest verified source observation |
TimeActualsImmutable approved source-time observations for one assignment period; corrections retain source revision and reversal linkage.transaction
| Field | Type | Required | Details |
|---|---|---|---|
ActualNumber | text | yes | |
SourceTimeID | text | yes | Source workspace and time-entry identity |
SourceRevision | number | yes | Monotonic source revision |
AssignmentPeriod | reference | yes | → AssignmentPeriods |
Hours | number | yes | Nonnegative approved source hours |
Billable | toggle | yes | |
SourceApprovedAt | datetime | yes | |
Status | select | yes | Approved, Reversed |
Reverses | reference | → TimeActuals | |
ImportedAt | datetime | yes |