ai-enabled-hr-talent-automation

Master Management — Gap Analysis

Title: Master Management Gap Analysis

Version: 1.0

Owner: [TENANT_CONFIGURATION_REQUIRED — Engineering]

Status: Draft (Phase 1 inspection only — no code changed)

Last reviewed: 2026-09-09

Next review: [TENANT_CONFIGURATION_REQUIRED]

Reviewers: Engineering, Security, DBA

Purpose and scope

This document is the required Phase 1 deliverable before any Administration/Master-Management code is written. It records, from direct inspection of the real DDL/seed scripts and the real backend/frontend code — not from any prior documentation, which is known to drift — exactly what schema, API, Application-layer, UI, and permission scaffolding already exists for each of the 22 management areas requested, so that implementation does not invent schema, duplicate existing code, or contradict existing conventions.

Sources inspected: src/HrAutomation.Infrastructure/Database/scripts/{01-09}-*.sql, Database/seed/{01-09}-*.sql (every file read in full); src/HrAutomation.Api/{Program.cs,Controllers/*,Auth/*,Security/*}; src/HrAutomation.Application/{Contracts/*,Security/*,Skills/*}; src/HrAutomation.Infrastructure/Persistence/{HrAutomationDbContext.cs,Entities/*,StoredProcedureExecutor.cs}; src/HrAutomation.Agents/Tan/*; openapi/hr-onboarding-api.openapi.yaml; tests/HrAutomation.Tests/*; src/HrAutomation.Web/src/{app/router/*, features/administration/*, hooks/usePermissions.ts, features/auth/*, types/*, components/common/*, mocks/*}.

Cross-cutting findings (apply to every area below)

• No fine-grained permission enforcement exists in C# today. Every current endpoint checks only [Authorize] (valid JWT) plus a per-skill hardcoded RequiredRoles: string[] list (ISkill.RequiredRoles, checked in HrControllerBase.IsAuthorizedForSkill). AddAuthorization() in Program.cs registers zero ASP.NET Core policies. The DB already has a real permission model (iam.Permission, iam.RolePermission) but no C# code reads it. Building real permission.read/user.create/etc. checks is new work required by every single endpoint in this module — see Decision D1 below.
• No fine-grained permission model exists in React either. usePermissions.ts’s ROLE_PERMISSIONS is an explicitly-labeled “best-effort UI approximation,” hand-maintained, not fetched from the API. There is no /v1/users/me/permissions (or effective-permissions) endpoint today.
• Only 3 of ~140 relevant tables have EF Core entities today: iam.[User], iam.Role, iam.UserRole (thin POCOs, not full column mirrors). Every other table this module touches (iam.Permission, iam.RolePermission, iam.UserTenantAccess, iam.UserDepartmentAccess, iam.UserLocationAccess, iam.UserAuthenticationProvider, iam.AuthorizationPolicy(+Rule), all of org.*, almost all of ref.*, workflow.WorkflowInstance/State/Transition/Task, onboarding.GreenForm*, ai.*) needs a new EF Core entity class + DbSet + OnModelCreating mapping before any Application-layer code can query it.
• RLS does not cover master/config tables. security.fn_tenant_access_predicate is applied to 13 transactional tables only (Candidate, JobRequisition, Offer, etc.). None of org.*/ref.*/iam.* (the tables this module manages) have an RLS policy — tenant filtering for this module must be enforced entirely in the Application/EF Core layer (WHERE TenantId = @ctx.TenantId on every query), the same way TenantSessionContextMiddleware already sets SESSION_CONTEXT defensively but does not by itself protect these tables.
• No stored procedures exist for generic master-data CRUD, role-permission grant/revoke, or generic approval-request creation. Confirmed absent: iam.usp_GrantRolePermission, iam.usp_SetTenantConfiguration, workflow.usp_CreateApprovalRequest, workflow.usp_RecordApprovalDecision. The 33 stored procedures that do exist are workflow-critical, entity-specific actions (TAN/offer/employee-conversion lifecycle). Per the DDL scripts’ own header comments, ordinary master-data CRUD is intended to go through EF Core, not new stored procedures — except where a table has a documented hard invariant (see ref.NumberingRule below).
• A /admin route + 4 placeholder pages already exist in the frontend (features/administration/{AdminPage,AdminConfigurationPage,AdminRolesPage,AdminFeatureFlagsPage,AdminIntegrationsPage}.tsx), all explicitly labeled read-only/placeholder pending a real API. These will be extended, not replaced from scratch.
• reporting schema is empty (zero views/procs) — any “configuration health summary”/”impact preview” screen needs new read queries built directly against source tables; there is nothing to reuse.
• No mcp schema or McpTool table exists. MCP tool metadata lives only as ai.AiSkill.ToolAllowlistJson (per-skill allowlist) and ref.IntegrationConfiguration rows tagged IntegrationType='MCP' at the provider level, not a per-tool level.

Decisions required before/during implementation (see DECISIONS_REQUIRED.md for the register — new items to add)

• D1 (Security/Architecture): how should real permission-checking be implemented — a new IAuthorizationService reading iam.RolePermission per request (cached?), ASP.NET Core policy-based authorization backed by a custom IAuthorizationHandler, or continuing role-string checks with a wider hardcoded map? This decision affects every endpoint below and should be made once, not per-area.
• D2 (DBA/Architecture): iam.AuthorizationPolicy/AuthorizationPolicyRule are documented in the schema as “app-layer-evaluated ABAC metadata, not a SQL-enforced engine” — no evaluator exists in C#. Building §10 (Authorization policy management) requires designing and reviewing a rule-expression evaluator (the user’s rules explicitly forbid letting UI users define arbitrary code/SQL) — this is non-trivial new design, not a CRUD screen.
• D3 (Product): ref.WorkflowDefinition/StateDefinition/TransitionDefinition are seeded but not wired to any live stored procedure — editing them today would manage configuration that has no runtime effect on actual entity transitions (those are hardcoded in each entity’s own status column + stored procedure). The Administration UI can CRUD these tables safely, but must not imply that publishing a new workflow version changes live TAN/offer/employee behavior — it doesn’t, yet.
• D4 (Security): MCP tool-level configuration (enable/disable per tool, write-capability classification) has no backing table. Options: extend ref.IntegrationConfiguration with tool-level rows, or treat ai.AiSkill.ToolAllowlistJson as the only manageable surface and document the MCP admin screen as skill-allowlist editing, not a tool registry. Needs a schema decision before /admin/mcp-tools can be built as literally specified.

Gap analysis table

#	Management Area	Actual Table(s)	Existing API	Existing Application Handler	Existing UI	Existing Permission	Workflow/Approval Needed	Audit Ready	Gaps	Priority
1	Tenants & tenant configuration	org.Tenant, org.TenantConfiguration	None	None	None	None (D1)	Tenant config changes should require approval per ApprovalStatus column already on the table	audit.usp_WriteAuditEvent ready; no caller yet	No EF entity; no proc (iam.usp_SetTenantConfiguration does not exist — confirmed); Tenant CRUD itself is platform-scope, high blast radius	P2 (config only; full tenant lifecycle likely out of scope — confirm with user)
2	Organization master data (BU, Dept, Designation, JobGrade, Location, WorkMode, EmploymentType, CostCenter, Client, HolidayCalendar)	org.BusinessUnit, org.Department, org.Designation, org.JobGrade, org.Location, org.WorkMode, ref.EmploymentType, org.Client, org.CostCenter, org.HolidayCalendar, org.Holiday	None	None	None	None (D1)	No approval needed (simple lookups)	Ready, unused	No EF entities for any of these 10 tables; straightforward flat CRUD once entities exist	P0 — highest ROI, simplest shape, needed by every other area (Department dropdown etc.)
3	Users & user profiles	iam.[User] (mapped, thin), iam.UserProfile (not mapped)	GET /api/v1/users/me only	RequestContextFactory (auth context only, not CRUD)	None (list/detail/create)	None (D1)	User creation/deactivation should be audited; break-glass rule for last admin (new logic)	Ready, unused for user mgmt	Existing IamUser entity is a thin 5-6 prop POCO — needs extending to full profile; iam.usp_CreateUser exists and is usable; no list/detail/update/activate/deactivate endpoint exists	P0
4	User authentication-provider mappings	iam.UserAuthenticationProvider	None	None	None	None (D1)	N/A	Ready, unused	No EF entity; iam.usp_CreateUser already creates the initial mapping row — need separate link/unlink endpoints for mapping management post-creation	P0 (bundled with #3)
5	Roles	iam.Role (mapped, thin)	None (list is Vitest-mock only via ALL_ROLES constant in UI, not real API)	None	/admin/roles (read-only display of hardcoded ALL_ROLES, not DB-backed)	None (D1)	Deactivating a role used by an active approval matrix must be blocked (new logic)	Ready, unused	IamRole entity needs extending (IsSystemRole, effective dates); no create/update/activate/deactivate endpoints; current UI reads a hardcoded list, not the database — must be replaced	P0
6	Permissions	iam.Permission (91 seeded)	None	None	None	None (D1)	Governance-gated per user’s own spec (activate/deactivate “only through controlled governance”)	Ready, unused	No EF entity at all; per user’s spec, custom permission creation should likely be disabled/read-only initially (system permission catalog) — needs an explicit product decision, not silently built as full CRUD	P0 (read), P2 (write — governance decision needed)
7	Role-to-permission assignments	iam.RolePermission	None	None	None	None (D1)	Must not let an actor grant a permission they don’t themselves effectively hold (anti-escalation — new logic, no existing check)	Ready, unused	No EF entity; no proc (iam.usp_GrantRolePermission confirmed absent — direct EF Core insert/delete needed); anti-escalation logic is entirely new	P0
8	User-to-role assignments	iam.UserRole (mapped, thin)	None	None	None	None (D1)	Anti-escalation (actor cannot assign role above own grant authority); cannot remove last active admin (new logic)	Ready, unused	iam.usp_AssignUserRole/usp_RevokeUserRole exist and are directly usable; anti-escalation + last-admin-protection logic is entirely new application-layer work	P0
9	User tenant/department/location access scope	iam.UserTenantAccess, iam.UserDepartmentAccess, iam.UserLocationAccess	None	None	None	None (D1)	N/A	Ready, unused	No EF entities; no BusinessUnit-level or Client-level scope table exists — the user’s request for “business-unit… access” cannot be modeled beyond Department today without a schema change (reported per instructions, not invented)	P1
10	Authorization policies/rules	iam.AuthorizationPolicy, iam.AuthorizationPolicyRule	None	None	None	None (D1)	Requires an approval workflow per the user’s own spec	Ready, unused	No EF entities; no rule evaluator exists in C# — see Decision D2, this is a design task, not a CRUD screen; recommend scoping to read/view-only + basic CRUD of rule metadata in this pass, deferring “validate before publish” evaluator to a follow-up	P2
11	Workflow definitions/states/transitions/approval matrix	ref.WorkflowDefinition, ref.WorkflowStateDefinition, ref.WorkflowTransitionDefinition, ref.ApprovalMatrix, ref.ApprovalMatrixRule; instance tables workflow.WorkflowInstance/State/Transition/Task (separate, transactional)	GET /api/v1/workflows/{id} (reads ApprovalRequest, per ADR-006 — not these definition tables)	None for definitions	AdminConfigurationPage placeholder	None (D1)	Publish requires approval per user’s spec; must not imply live TAN/offer transitions change — see Decision D3	Ready, unused	No EF entities for any of the 5 definition tables; validation logic (unreachable states, invalid terminals, duplicate codes) is entirely new; must clearly document these definitions are currently descriptive/reporting-only, not wired to live procs	P1
12	Master/reference data needed by HR workflows	(umbrella — see #2, #16–19 for the actual tables)	—	—	—	—	—	—	This is a category label covering rows already itemized elsewhere in this table, not a distinct table set	—
13	Feature flags	ref.FeatureFlag (global), ref.FeatureFlagAssignment (per-tenant override)	None	None	AdminFeatureFlagsPage (reads client env vars, not DB)	None (D1)	Changes that “can affect workflow” require idempotent handling per user’s spec	Ready, unused	No EF entities; current UI is entirely disconnected from these tables (reads import.meta.env) — needs full replacement, not extension	P1
14	Notification templates	ref.NotificationChannel, ref.NotificationTemplate	None	None	None	None (D1)	No approval needed unless configured	Ready, unused	No EF entities; straightforward versioned CRUD once entities exist	P2
15	SLA policies/rules	ref.SlaPolicy, ref.SlaRule	None	None	None	None (D1)	No approval needed unless configured	Ready, unused	No EF entities; straightforward CRUD	P2
16	Interview-round definitions & feedback templates	ref.InterviewRoundDefinition, ref.InterviewCompetency, ref.InterviewFeedbackTemplate	None	None	None	None (D1)	Template versioning required if already used by a completed interview (new logic — must check recruitment.Interview references before allowing in-place edit)	Ready, unused	No EF entities; “already tied to a completed interview” reference-check is new logic	P1
17	Document type/checklist configuration	ref.DocumentType, ref.DocumentChecklist, ref.DocumentChecklistItem	None	None	None	None (D1)	User’s spec flags this as privacy/legal-sensitive — recommend read + restricted-write only in first pass	Ready, unused	No EF entities; ref.DocumentType.Classification (Public/Internal/Confidential/Restricted) already exists and should drive UI field-level access, not just document rows	P1
18	Verification configuration	ref.VerificationType	None	None	None	None (D1)	Same privacy/legal caveat as #17	Ready, unused	No EF entity; note seed data already disables CRIMINAL_CHECK pending legal review — a real signal this area needs conservative defaults	P1
19	Discrepancy type/severity configuration	ref.DiscrepancyType, ref.DiscrepancySeverity (RankOrder)	None	None	None	None (D1)	No approval needed	Ready, unused	No EF entities; straightforward CRUD	P2
20	Offer templates and numbering rules	ref.OfferTemplate, ref.OfferTemplateVersion; ref.NumberingRule	None	offer.usp_CreateOfferDraft reads ref.OfferTemplate today (consumption only, not admin)	None	None (D1)	Publish new template version requires approval; numbering rule CurrentSequence must never be written from a generic CRUD screen — it is only ever mutated under UPDLOCK/HOLDLOCK inside specific stored procs (usp_CreateTalentAcquisitionNumber, usp_GenerateEmployeeId, usp_CreateOfferDraft)	Ready, unused	No EF entities; template versioning logic (block edit-in-place if used) is new; numbering-rule “preview only” endpoint must compute a preview without touching CurrentSequence — new pure function, not a DB write	P1
21	AI/RAG/MCP/integration configuration metadata	ref.AiModelConfiguration, ref.RagConfiguration, ref.IntegrationConfiguration, ai.AiModel(+Version), ai.AiPrompt(+Version), ai.AiSkill(+Version, ToolAllowlistJson), ai.RagCorpus, ai.RagAccessPolicy, integration.ExternalSystem	None	None	AdminIntegrationsPage (hardcoded placeholder array)	None (D1)	Enabling AI model/prompt/skill config in production requires evaluation/approval evidence per user’s spec — no evaluation-evidence linkage exists in schema today beyond ai.AiEvaluationRun/Result (exists, unlinked to activation gating)	Ready for ref.*/ai.*, unused	No EF entities for any of these ~10 tables; no MCP-tool-level table — see Decision D4; secrets already schema-enforced safe (SecretVaultKeyRef CHECK constraint requires kv:// prefix or NULL)	P1 (config metadata), P3 (MCP tool registry — needs schema decision first)
22	Audit history & configuration-change history	audit.AuditEvent (+ SecurityEvent/DataAccessEvent/PrivilegedActionEvent/ErrorEvent)	GET /api/v1/audit-logs exists	AuditLogger/IAuditLogger	/admin has no audit browsing yet; features/audit/AuditPage.tsx exists separately at /audit	audit.read (already defined in usePermissions.ts, client-only)	N/A — read-only by design, append-only, trigger-protected (no UPDATE/DELETE possible even for db_owner)	Fully ready — this is the one area with a working end-to-end path already	Need to surface entity-scoped audit history (e.g. “this role’s audit trail”) via existing GET /api/v1/audit-logs with entity filters — likely just a query-parameter extension, not new infrastructure	P0 (reuse existing, just needs entity-filtered views wired into new admin detail pages)

Priority summary (recommended build order, consistent with the user’s own “Implementation Order”)

• P0 (build first — foundational, highest reuse, unblocks everything else): Organization master data (#2), Users (#3/#4), Roles (#5), Permissions read (#6), Role-permission assignment (#7), User-role assignment (#8), Audit reuse (#22), and — cross-cutting — Decision D1 (real permission-checking mechanism) must be resolved as part of this first batch since every subsequent endpoint depends on it.
• P1: User access scope (#9, with the BusinessUnit/Client gap flagged), Workflow/approval matrix definitions (#11, with the “not yet live” caveat documented in UI), Feature flags (#13), Interview rounds/feedback (#16), Document/verification config (#17/#18, conservative defaults), Offer templates/numbering (#20).
• P2: Tenant configuration (#1), Notification templates (#14), SLA policies (#15), Discrepancy config (#19), Authorization policy CRUD without the evaluator (#10).
• P3 / needs a decision before scheduling: MCP tool-level registry (#21’s MCP portion — no table exists), Authorization policy rule evaluator (#10’s “validate before publish” — no evaluator exists), full Tenant lifecycle CRUD beyond configuration (#1 — platform-scope, likely restricted to Platform Admin only, confirm blast radius with user first).

Change control

Version	Date	Author	Change
1.0	2026-09-09	Administration/Master-Management gap-analysis pass	Initial creation, based on full inspection of real DDL/seed scripts and real backend/frontend code

This site is open source. Improve this page.