​

Reporting Hierarchy MVP Implementation Plan

​

Overview

​

Architecture

​

Data Flow

agent2_pepm.csv (names only)
    ↓
Agent Resolver Export (GET /admin/agents/resolver)
    ↓
Seed Generator Script (tools/generate_paid_today_hierarchy_seed.py)
    ↓
Hierarchy Bootstrap (POST /admin/hierarchy/bootstrap)
    ↓
config_agent_hierarchy table (SCD2)

​

Key Tables

• config_agent_hierarchy: SCD2 table using agent_entity_id (UUID) for all FK references
  • Schema: hierarchy_id, tenant_id, org_id, agent_entity_id, parent_agent_entity_id, effective_start_date, effective_end_date, change_status, is_active, created_at, created_by, record_hash
  • Located at: integration/bigquery/sql/tables/config_agent_hierarchy.sql
  • Existing queries: api/bigquery/phase4_hierarchy_queries.py

​

Implementation Tasks

​

A) Agent Resolver Export Endpoint

• Org-scoped (use get_org_id_from_request pattern from api/utils/org_scoping.py)
• RBAC: Admin/CEO only (use require_admin_or_ceo from api/routes/admin_identity.py)
• Header: Must accept X-Org-Id header (use x_org_id: Optional[str] = Header(None, alias="X-Org-Id"))
• Returns: agent_entity_id, first_name, last_name, full_name_normalized, agent_id (optional), email (optional)
• Normalization: MUST be identical in SQL and Python:
  • Uppercase, trim, remove punctuation: . , ' - (include hyphen removal)
  • Collapse whitespace
  • SQL: UPPER(TRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONCAT(c.first_name, ' ', c.last_name), '.', ''), ',', ''), '''', ''), '-', ''), ' ', ' ')))
  • Python: full.replace('.', '').replace(',', '').replace("'", '').replace('-', '').strip().upper() then collapse spaces
  • Deterministic: same normalization used in seed generator
• Query joins config_agent_profile_identity (for agent_entity_id, agent_id) with config_agent_profile_contact (for first_name, last_name, email)
• Filter: is_active=TRUE, effective_end_date IS NULL, tenant/org scope
• Response format: List of AgentResolverResponse objects (200 OK)
• Error handling: 403 if not admin/CEO, 400 if org_id missing for platform admin

SELECT DISTINCT
  i.agent_entity_id,
  i.agent_id,
  c.first_name,
  c.last_name,
  c.email,
  UPPER(TRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONCAT(c.first_name, ' ', c.last_name), '.', ''), ',', ''), '''', ''), '-', ''), '  ', ' '))) AS full_name_normalized
FROM config_agent_profile_identity i
LEFT JOIN config_agent_profile_contact c ON c.agent_entity_id = i.agent_entity_id
WHERE i.tenant_id = @tenant_id
  AND (i.org_id = @org_id OR (@org_id IS NULL AND i.org_id IS NULL))
  AND i.is_active = TRUE
  AND i.effective_end_date IS NULL

class AgentResolverResponse(BaseModel):
    agent_entity_id: str
    first_name: Optional[str]
    last_name: Optional[str]
    full_name_normalized: str
    agent_id: Optional[str]
    email: Optional[str]

​

B) Hierarchy Bootstrap Endpoint

• Request format: JSON body with rows array (NOT multipart CSV upload - use JSON for consistency with other admin endpoints)
• Accepts JSON rows with ONLY existing schema fields: child_agent_entity_id, parent_agent_entity_id, effective_start_date, effective_end_date (optional), change_status, active
• IMPORTANT: Schema does NOT have relationship_type or notes fields - MVP must not include these
• Header: Must accept X-Org-Id header (use x_org_id: Optional[str] = Header(None, alias="X-Org-Id"))
• UUID normalization: Normalize all UUIDs to lowercase for consistency (accept both cases, store lowercase)
• Validations:
  • Tenant/org isolation: Child and parent must belong to same org/tenant (reject cross-org refs)
  • IDs exist: Verify child_agent_entity_id and parent_agent_entity_id (if not NULL) exist in config_agent_profile_identity
  • Date normalized: YYYY-MM-DD format, validate date is valid (no timezone, no time component)
  • Single-parent policy (MVP): At most one active parent per child as-of date
  • Cycle prevention: Use detect_circular_hierarchy() from api/bigquery/phase4_hierarchy_queries.py to reject loops
  • Idempotency: Same (child_agent_entity_id, parent_agent_entity_id, effective_start_date) => reused/no-op (query existing row before insert)
• Error response format: FastAPI HTTPException with detail field (string or list of strings for validation errors)
  • 400: Validation errors (invalid IDs, cycles, cross-org refs)
  • 403: RBAC failure
  • 500: Internal server error
• Response format: HierarchyBootstrapResponse (200 OK with created/skipped counts)

• Writes SCD2 rows: Insert only (no in-place edits), use create_agent_hierarchy() from api/bigquery/phase4_hierarchy_queries.py
• Set change_status='APPROVED', is_active=TRUE (from active field)

class HierarchyBootstrapRow(BaseModel):
    child_agent_entity_id: str  # UUID, will be normalized to lowercase
    parent_agent_entity_id: Optional[str]  # NULL = root, UUID normalized to lowercase
    effective_start_date: date  # YYYY-MM-DD, no timezone
    effective_end_date: Optional[date] = None  # Optional, NULL = unbounded
    change_status: str = "APPROVED"  # Default: APPROVED
    active: bool = True  # Maps to is_active field

class HierarchyBootstrapRequest(BaseModel):
    rows: List[HierarchyBootstrapRow]

class HierarchyBootstrapResponse(BaseModel):
    created_count: int
    skipped_count: int  # Idempotent skips
    errors: List[Dict[str, Any]]  # Format: [{"row_index": 0, "error": "message"}, ...]

​

C) Reparent Endpoint

• Input: child_agent_entity_id, new_parent_agent_entity_id (nullable), effective_start_date, change_status (optional, default “APPROVED”), active (optional, default True)
• IMPORTANT: Schema does NOT have notes field - MVP must not include it
• Header: Must accept X-Org-Id header (use x_org_id: Optional[str] = Header(None, alias="X-Org-Id"))
• UUID normalization: Normalize all UUIDs to lowercase (accept both cases, store lowercase)
• Algorithm (SCD2):
  1. Scope + existence checks: Verify child and parent (if not null) exist in tenant/org scope
  2. NOOP check: If current parent (as-of effective_start_date) equals new_parent_agent_entity_id, return early (idempotent)
  3. Cycle check (single-parent optimized): Walk up from new_parent_agent_entity_id via parent links (as-of effective_start_date); if hit child_agent_entity_id => reject with 400
  4. Close existing: Find any overlapping existing relationship for child (active row with effective_end_date IS NULL), set effective_end_date = effective_start_date - 1 day (use close_agent_hierarchy())
  5. Insert new: Create new relationship row with effective_start_date, effective_end_date=NULL (use create_agent_hierarchy())
• Error response format: FastAPI HTTPException with detail field
  • 400: Validation errors (invalid IDs, cycles, cross-org refs, NOOP returns 200 with status=“noop”)
  • 403: RBAC failure
  • 500: Internal server error
• Response format: HierarchyReparentResponse (200 OK for created, 200 OK for noop)

class HierarchyReparentRequest(BaseModel):
    child_agent_entity_id: str  # UUID, normalized to lowercase
    new_parent_agent_entity_id: Optional[str]  # NULL = set root, UUID normalized to lowercase
    effective_start_date: date  # YYYY-MM-DD, no timezone
    change_status: Optional[str] = "APPROVED"  # Default: APPROVED
    active: Optional[bool] = True  # Default: True (maps to is_active)

class HierarchyReparentResponse(BaseModel):
    hierarchy_id: Optional[str]  # None if noop
    op_hash: str  # SHA-256 hash of canonical JSON representation
    status: str  # 'created' | 'noop'

• Use get_agent_hierarchy_rows() to find current parent
• Use detect_circular_hierarchy() for cycle check (walk up from new_parent)
• Use close_agent_hierarchy() and create_agent_hierarchy() for SCD2 operations
• Server-side hashing: Compute op_hash (SHA-256 of canonical JSON) and return in response (hashes NOT stored in DB)
• Audit logging: Log request_id, actor (user email), tenant_id, org_id, op_hash, status

​

D) Batch Reparent Endpoint

• Accepts list of reparent operations (same fields as /reparent)
• Header: Must accept X-Org-Id header (use x_org_id: Optional[str] = Header(None, alias="X-Org-Id"))
• UUID normalization: Normalize all UUIDs to lowercase
• Applies ops in deterministic order:
  • Validate all first (including cross-op cycles/conflicts) OR fail the whole batch (preferred)
  • Then apply SCD2 close+insert per op
• Returns per-op results: created/noop/error status for each operation
• Must be idempotent per op (same op can be replayed safely)
• Error response format:
  • 400: Validation errors (fail whole batch, return all errors in response)
  • 403: RBAC failure
  • 500: Internal server error
• Response format: HierarchyBatchReparentResponse (200 OK even if some ops fail - errors in per-op results)

class HierarchyReparentOp(BaseModel):
    child_agent_entity_id: str  # UUID, normalized to lowercase
    new_parent_agent_entity_id: Optional[str]  # UUID normalized to lowercase or null
    effective_start_date: date  # YYYY-MM-DD, no timezone
    change_status: Optional[str] = "APPROVED"  # Default: APPROVED
    active: Optional[bool] = True  # Default: True (maps to is_active)

class HierarchyBatchReparentRequest(BaseModel):
    operations: List[HierarchyReparentOp]

class HierarchyReparentOpResult(BaseModel):
    operation_index: int
    status: str  # 'created' | 'noop' | 'error'
    child_agent_entity_id: str
    op_hash: Optional[str]  # None if error
    error_message: Optional[str] = None
    hierarchy_id: Optional[str] = None  # None if error or noop

class HierarchyBatchReparentResponse(BaseModel):
    results: List[HierarchyReparentOpResult]
    batch_hash: str  # SHA-256 hash computed from deterministic order (sorted by child_agent_entity_id, then effective_start_date)
    total_created: int
    total_noop: int
    total_errors: int

• Scope: Compute op_hash and batch_hash ONLY on backend (Python). UI treats hashes as opaque metadata returned by API.
• Non-goals: No client-side hashing, no preview hashes, no cross-language parity tests.

1. op_hash computation (per reparent operation):
   • Formula: op_hash = sha256(UTF8(canonical_json(op_core)))
   • op_core fields (ONLY these, exclude notes/comments/UI metadata/timestamps):
     • op: “reparent” (string)
     • tenant_id: string
     • org_id: string (or null)
     • child_agent_entity_id: lowercase UUID string
     • new_parent_agent_entity_id: lowercase UUID string or null
     • effective_start_date: “YYYY-MM-DD” string
     • change_status: string (default “APPROVED”)
     • active: boolean (default True, maps to “Yes”/“No” in canonical JSON)
   • Canonical JSON rules (Python):
     • UTF-8 encoding, no BOM
     • Keys sorted lexicographically
     • No whitespace: json.dumps(..., sort_keys=True, separators=(',', ':'))
     • Arrays preserve order
     • Null values: null (JSON null, not string “null”)
   • Implementation: api/utils/hierarchy_hashing.py with compute_op_hash(op_core: Dict) -> str
   • Example: json.dumps(op_core, sort_keys=True, separators=(',', ':')).encode('utf-8') then hashlib.sha256(json_bytes).hexdigest()
2. batch_hash computation:
   • Compute op_hash for each operation first
   • Enforce deterministic server order for application (recommended: sort by child_agent_entity_id then effective_start_date)
   • Formula: batch_hash = sha256(UTF8(canonical_json({tenant_id, org_id, effective_start_date, operations: [{op_hash}, ...]})))
   • Batch structure:
     Copy

     Ask AI

     batch_core = {
         "tenant_id": tenant_id,
         "org_id": org_id,
         "effective_start_date": effective_start_date,  # From first op or meta
         "operations": [{"op_hash": op_hash1}, {"op_hash": op_hash2}, ...]  # Sorted by deterministic order
     }
     
   • Implementation: api/utils/hierarchy_hashing.py with compute_batch_hash(tenant_id: str, org_id: str, effective_start_date: str, op_hashes: List[str]) -> str
3. API behavior:
   • /admin/hierarchy/reparent returns op_hash in response (hash NOT stored in DB, computed on-the-fly)
   • /admin/hierarchy/batch_reparent returns per-op op_hash + batch_hash (hashes NOT stored in DB, computed on-the-fly)
   • Hashes are computed server-side only and returned as opaque metadata
   • UI treats hashes as opaque strings (no client-side computation, no preview hashes)
4. Audit logging:
   • Log fields: request_id (UUID), actor (user identity/email from JWT), tenant_id, org_id, op_hash(es), batch_hash (if applicable), status (success/failure)
   • Log format: Structured JSON log entry for audit trail
   • Log location: Application logs (structured JSON format for parsing)
5. Tests (backend only, Python):
   • Same op_core => same op_hash (deterministic)
   • Different parent or date => different op_hash
   • Batch ordering deterministic => stable batch_hash
   • Hashing does not affect DB writes or reads (hashes computed but not stored)
   • Explicit non-goals: No client-side hash computation, no parity tests with frontend, no impact to ingestion wizard/commission engine/dashboards

• Check all operations for:
  • Scope/existence (all IDs exist in tenant/org)
  • Cross-op cycles (op A’s child becomes parent of op B’s child, creating cycle)
  • Conflicts (multiple ops try to reparent same child to different parents)
• If any validation fails, return 400 with all errors (do not apply any ops)
• If all pass, apply each op sequentially using same logic as single reparent

• op_hash: SHA-256 of canonical JSON representation of operation (server-side Python)
  • Canonical JSON: Sort keys alphabetically, no whitespace, deterministic encoding
  • Fields included: child_agent_entity_id, new_parent_agent_entity_id (null as “null” string), effective_start_date, change_status, active
  • Implementation: api/utils/hierarchy_hashing.py with compute_op_hash(op: Dict) -> str
• batch_hash: SHA-256 of deterministic order (server sorts by child_agent_entity_id then effective_start_date)
  • Sort operations: sorted(operations, key=lambda op: (op['child_agent_entity_id'], op['effective_start_date']))
  • Concatenate all op_hash values in sorted order, then hash: SHA256(''.join(sorted_op_hashes))
• Audit logging: Log request_id (UUID), actor (user email from JWT), tenant_id, org_id, op_hashes (list), batch_hash, status (success/partial/failed)
  • Log format: Structured JSON log entry for audit trail

​

E) Validate Move Endpoint (Optional but Recommended)

• Input: child_agent_entity_id, new_parent_agent_entity_id (nullable), effective_start_date
• Output: ok/errors (no writes, validation only)
• Used later by drag/drop UI for pre-flight validation
• Checks: scope, existence, cycle detection (same as reparent validation)
• Header: Must accept X-Org-Id header
• UUID normalization: Normalize all UUIDs to lowercase
• Error response format: 400 for validation errors, 403 for RBAC failure

class HierarchyValidateMoveRequest(BaseModel):
    child_agent_entity_id: str  # UUID, normalized to lowercase
    new_parent_agent_entity_id: Optional[str]  # UUID normalized to lowercase or null
    effective_start_date: date  # YYYY-MM-DD, no timezone

class HierarchyValidateMoveResponse(BaseModel):
    is_valid: bool
    errors: List[str]  # Empty if valid

​

F) Tree and History Endpoints

• GET /admin/hierarchy/tree?as_of=YYYY-MM-DD
  • Returns tree structure (parent-child relationships) as-of date
  • Filter: effective_start_date <= as_of AND (effective_end_date IS NULL OR effective_end_date > as_of)
  • Format: Nested JSON structure (recommended for dashboard consumption):
    Copy

    Ask AI

    {
      "nodes": [
        {
          "agent_entity_id": "uuid",
          "parent_agent_entity_id": "uuid|null",
          "level": 0,
          "children": [...]
        }
      ]
    }
    
  • Header: Must accept X-Org-Id header
  • Error response: 400 for invalid date format, 403 for RBAC failure
• GET /admin/hierarchy/history?agent_entity_id=...
  • Returns all SCD2 rows for given agent_entity_id
  • Ordered by effective_start_date DESC
  • Header: Must accept X-Org-Id header
  • Error response: 400 for invalid UUID, 403 for RBAC failure, 404 if agent not found

• get_hierarchy_tree(tenant_id, org_id, as_of_date)
• get_hierarchy_history(tenant_id, org_id, agent_entity_id)

​

G) Seed Generator Script

• Inputs:
  • /mnt/data/agent2_pepm.csv (or data/reference/agent2_pepm.csv)
  • Resolver export (API call to GET /admin/agents/resolver or CSV file)
  • Optional: hierarchy_changeset.json (for overrides)
  • Optional: hierarchy_overrides.csv (for CSV-format overrides)
• Logic:
  1. Load agent2_pepm.csv, dedupe by normalized name: UPPER(TRIM(CONCAT(Agent_2_First_Name, ' ', Agent_2_Last_Name)))
  2. Join to resolver by EXACT normalized name only (fail-closed):
     • 0 matches => exception row UNKNOWN_AGENT (write to exceptions CSV)

     • 1 matches => exception row AMBIGUOUS_AGENT_NAME (write to exceptions CSV)

  3. Hierarchy construction (Option 1 default):
     • Robin Bundy is ROOT: parent_agent_entity_id = null
     • Default parent for every resolved paid agent = Robin Bundy (direct child)
     • Apply overrides from config (see H) Changeset/Override Support
  4. Output:
     • hierarchy_paid_today_seed.json: JSON array of HierarchyBootstrapRow objects (for API consumption)
     • hierarchy_paid_today_seed.csv: CSV format (for manual review)
     • Schema-aligned fields only: child_agent_entity_id, parent_agent_entity_id, effective_start_date, effective_end_date (optional), change_status, active
     • hierarchy_paid_today_exceptions.csv: Agent_2_First_Name, Agent_2_Last_Name, Agent_2_Business_Name, Root_Owner, error_type, error_message
     • Optional: hierarchy_changeset.json (export format for ops-based changeset)

def normalize_full_name(first_name: str, last_name: str) -> str:
    full = f"{first_name} {last_name}"
    # Remove punctuation: . , ' - (must match SQL exactly)
    full = full.replace('.', '').replace(',', '').replace("'", '').replace('-', '')
    # Trim and uppercase
    full = full.strip().upper()
    # Collapse multiple spaces
    full = ' '.join(full.split())
    return full

​

H) Changeset/Override Support

• docs/config/hierarchy_changeset.sample.json (new)

1. Changeset JSON Format (preferred, ops-based, schema-aligned):
   • File: hierarchy_changeset.json
   • Schema: Maps directly to /reparent or /batch_reparent WITHOUT non-schema fields
     Copy

     Ask AI

     {
       "meta": {
         "org_id": "creative_benefit_strategies",
         "as_of": "YYYY-MM-DD",
         "source": "seed|ui",
         "comment": "..."
       },
       "operations": [
         {
           "op": "reparent",
           "child_agent_entity_id": "UUID",
           "new_parent_agent_entity_id": "UUID|null",
           "effective_start_date": "YYYY-MM-DD",
           "change_status": "APPROVED",
           "active": true
         }
       ]
     }
     
   • IMPORTANT: No relationship_type or notes fields (not in schema)
   • Generator can read this file and apply overrides by generating final seed CSV
   • No full-tree snapshot imports. Ops-based only (replayable, auditable, SCD2-safe)
   • Sample must include Tommy Dang -> Richard Ballard reparent op (no notes field)

• Generator script reads changeset JSON and applies overrides
• Changeset JSON is structured for future drag/drop UI (replayable, auditable)
• Operations map directly to /reparent or /batch_reparent endpoints
• All fields must match actual schema (no relationship_type or notes)

​

I) UI/UX Changes (MVP Now, Future Drag/Drop Ready)

1. Hierarchy Bootstrap Upload:
   • Separate from old Bulk Import changeset flow
   • File upload for hierarchy_paid_today_seed.json (JSON array) OR CSV (for backward compatibility)
   • Parse JSON/CSV and call POST /admin/hierarchy/bootstrap with JSON body
   • Display results table (created/reused/errors) + download exceptions
   • Fix “Go to Bulk Import” button to route to Hierarchy Bootstrap
2. Reparent Modal (conventional):
   • Child search (shows entity id)
   • Parent search or “Set as root” checkbox
   • Effective start date picker
   • Change status (default: APPROVED)
   • Active toggle (default: Yes)
   • Call POST /admin/hierarchy/reparent
3. Bulk Moves Panel:
   • Upload hierarchy_changeset.json -> parse and call POST /admin/hierarchy/batch_reparent
   • Shows per-op results + export results JSON/CSV
4. Tree View:
   • Call GET /admin/hierarchy/tree?as_of=YYYY-MM-DD (org-scoped)
   • Display hierarchical tree (collapsible nodes)

• bootstrapHierarchy(rows) - returns response with created/skipped counts
• reparentHierarchy(request) - returns response with op_hash (opaque metadata, no client-side computation)
• batchReparentHierarchy(operations) - returns response with per-op op_hash + batch_hash (opaque metadata)
• validateMoveHierarchy(request) - returns validation result (no writes)
• getHierarchyTree(asOfDate) - returns tree structure
• getHierarchyHistory(agentEntityId) - returns SCD2 history
• getAgentResolver(orgId) - returns resolver export

// Use requestWithHeaders for X-Org-Id header
const customHeaders: Record<string, string> = {};
if (orgId) {
  customHeaders['X-Org-Id'] = orgId;
}

return apiClient.requestWithHeaders<ResponseType>(endpoint, {
  method: 'POST',
  body: JSON.stringify(request),
  customHeaders,
});

​

J) Tests (pytest)

1. Resolver endpoint:
   • Returns normalized names deterministically (verify full_name_normalized format matches seed generator)
   • Org/tenant scoping enforced (cross-org agents not returned)
   • RBAC (non-admin gets 403)
   • X-Org-Id header handling (platform admin requires header, org-scoped user uses JWT org_id)
2. Bootstrap endpoint:
   • Org isolation rejects cross-org refs (child and parent must be same org/tenant)
   • Idempotency (duplicate row => reused/no-op)
   • Invalid IDs => error (400)
   • Cycle reject (rejects cycles)
   • Single-parent overlap handling consistent with policy (at most one active parent per child/date)
   • UUID normalization (accepts both cases, stores lowercase)
   • Error response format (FastAPI HTTPException with detail field)
3. Reparent endpoint:
   • Reparent closes old row + inserts new (verify SCD2 pattern)
   • Set-root works (parent=NULL)
   • NOOP works (same parent => no changes, returns 200 with status=“noop”)
   • Cycle reject (400 if cycle created)
   • UUID normalization
4. Batch Reparent endpoint:
   • Batch validates all then applies (preferred: fail whole batch on validation errors)
   • Returns per-op results (created/noop/error)
   • Cross-op cycle detection (op A’s child becomes parent of op B’s child)
   • Conflicts detection (multiple ops try to reparent same child)
   • Idempotent per op (same op can be replayed safely)
   • UUID normalization
5. Tree endpoint:
   • Returns correct parent after reparent with effective dating
   • as_of date filtering works correctly
   • Response format (nested JSON structure)
6. Server-side hashing (backend only):
   • op_hash is deterministic (same op_core produces same hash)
   • Different parent or date => different op_hash
   • batch_hash is deterministic (same operations in deterministic order produce same hash)
   • Hash computation uses canonical JSON (UTF-8, sorted keys, no whitespace, arrays preserve order)
   • Hashes NOT stored in DB (computed on-the-fly, returned as metadata)
   • UI treats hashes as opaque metadata (no client-side computation)
7. Regression guard (prevent commission engine contamination):
   • Test/grep rule: commission engine modules must NOT import hierarchy query module
   • Verify no hierarchy joins in payout paths
   • Verify dashboards use hierarchy only for visibility, not payouts

​

File Changes Summary

​

New Files

• api/routes/admin_hierarchy.py (recommended: separate router for hierarchy endpoints)
• api/schemas/admin_hierarchy.py
• api/bigquery/admin_hierarchy_queries.py (or extend phase4_hierarchy_queries.py)
• api/utils/hierarchy_hashing.py (deterministic hashing utilities)
• tools/generate_paid_today_hierarchy_seed.py
• api/tests/test_admin_hierarchy.py
• api/tests/test_hierarchy_regression_guards.py (regression guard tests)
• dashboard/src/pages/admin/hierarchy.tsx (if new page)

​

Modified Files

• api/routes/admin.py (add resolver endpoint, or create separate router)
• api/bigquery/phase4_hierarchy_queries.py (add tree/history query functions)
• dashboard/src/lib/apiClient.ts (add hierarchy API methods)
• api/main.py (register new router: app.include_router(admin_hierarchy_router, prefix="/api/v1/admin", tags=["Admin Hierarchy"]))

​

Critical Constraints

1. NO commission engine changes: Do not touch commission logic, assignments, or payout paths. Hierarchy is ONLY for reporting/visibility and dashboard scoping. Do not add hierarchy joins to payout paths.
2. Guarantee no downstream breakage:
   • No schema changes to ingestion/commission tables
   • Hierarchy endpoints only write config_agent_hierarchy (no other tables)
   • Add regression guard test/grep rule: commission engine modules must NOT import hierarchy query module
   • Dashboards may use hierarchy only for visibility scoping and rollups, never payouts
3. Decimal-only rules: Do not introduce floats anywhere in shared libs. Hierarchy has no money but must not regress standards.
4. Strict tenant + org isolation: Primary org for Phase 2 prod validation: org_id = "creative_benefit_strategies". All operations enforce tenant/org scoping.
5. Deterministic, auditable behavior: No destructive edits. Use SCD2 (close prior row + insert new row). Ops-based changesets are replayable and auditable. Implement deterministic hashing (op_hash, batch_hash) for audit trail.
6. agent_entity_id only: All FK references use agent_entity_id (UUID), never agent_id
7. Fail-closed: Name matching must reject ambiguous cases (0 or >1 matches)
8. Normalization consistency: Normalization MUST be identical in SQL and Python (uppercase, trim, remove punctuation: . , ' -, collapse spaces). Include hyphen removal in SQL.
9. Schema alignment: config_agent_hierarchy table DOES NOT have relationship_type or notes fields. MVP must not include these in CSV/JSON payloads or inserts.
10. UUID normalization: All UUIDs normalized to lowercase (accept both cases in API, store lowercase in DB)
11. Date format: All dates use YYYY-MM-DD format, no timezone, no time component
12. Error response format: FastAPI HTTPException with detail field (string or list of strings)
13. Router registration: Must register router in api/main.py with prefix /api/v1/admin
14. X-Org-Id header: All endpoints must accept X-Org-Id header for org-scoping

​

Dependencies

• Existing: config_agent_hierarchy table (already exists)
• Existing: phase4_hierarchy_queries.py (cycle detection, create/close functions)
• Existing: config_agent_profile_identity and config_agent_profile_contact tables
• Existing: Org scoping utilities (api/utils/org_scoping.py)

​

Regression Guards

1. Commission Engine Isolation:
   • Grep test: Commission engine modules must NOT import hierarchy query module
   • Verify: api/bigquery/commission_assignments_queries.py does not import phase4_hierarchy_queries or admin_hierarchy_queries
   • Verify: api/routes/agents.py (commission endpoints) does not import hierarchy modules
   • Verify: No hierarchy joins in payout/commission calculation queries
   • Test pattern: Scan Python files for from api.bigquery.phase4_hierarchy_queries import or from api.bigquery.admin_hierarchy_queries import in commission-related modules
2. Schema Isolation:
   • Verify: Hierarchy endpoints only write to config_agent_hierarchy table
   • Verify: No schema changes to ingestion/commission tables
   • Verify: No writes to agent_report_v5, stage3_snapshots, or other commission tables
   • Test pattern: Mock BigQuery and verify only config_agent_hierarchy INSERT/UPDATE calls
3. Dashboard Usage:
   • Verify: Dashboards may use hierarchy only for visibility scoping and rollups
   • Verify: Dashboard queries do not use hierarchy for payout calculations
   • Document: Hierarchy is reporting-only, not used in commission engine

• Add grep-based test that scans commission engine modules for hierarchy imports
• Add integration test that verifies hierarchy writes only affect config_agent_hierarchy
• Add documentation comment in hierarchy modules: # REPORTING ONLY - DO NOT USE IN COMMISSION ENGINE
• Add CI check: Fail if commission modules import hierarchy modules

​

Deliverables

• Endpoints + tests passing
• Generator script + sample outputs
• Sample config files:
  • docs/config/hierarchy_changeset.sample.json (include Tommy Dang -> Richard Ballard reparent op, no notes field)
• Confirm commission/assignment/payout paths unchanged (no hierarchy joins)

​

Implementation Order

1. Resolver endpoint (GET /admin/agents/resolver)
2. Hierarchy model + reparent + tree read (POST /admin/hierarchy/reparent, GET /admin/hierarchy/tree, GET /admin/hierarchy/history)
3. Batch reparent + validate move (POST /admin/hierarchy/batch_reparent, POST /admin/hierarchy/validate_move optional)
4. Bootstrap import (POST /admin/hierarchy/bootstrap)
5. Seed generator script (tools/generate_paid_today_hierarchy_seed.py)
6. UI wiring for bootstrap + reparent + batch moves
7. Tests + regression coverage

​

Validation Checklist

• Resolver returns normalized names deterministically matching seed generator normalization
• Bootstrap rejects cycles, invalid IDs, multiple parents, cross-org refs
• Reparent implements SCD2 correctly (close old + insert new)
• Batch reparent validates all then applies (fail whole batch on validation errors)
• Tree endpoint respects effective dating
• Seed generator fails-closed on ambiguous names
• All endpoints enforce org-scoping
• Changeset JSON format supported (read/export, schema-aligned, no relationship_type/notes)
• Tests pass with mocked BigQuery
• Commission/assignment/payout paths confirmed unchanged (no hierarchy joins)
• Server-side hashing implemented (op_hash, batch_hash) - backend only, hashes NOT stored in DB
• Audit logging implemented (request_id, actor, tenant_id, org_id, op_hashes, batch_hash, status)
• Hashing tests verify deterministic behavior (same op_core => same hash, different => different hash)
• Regression guard tests pass (commission engine does not import hierarchy modules)
• Router registered in api/main.py with prefix /api/v1/admin
• All endpoints accept X-Org-Id header
• UUID normalization (accepts both cases, stores lowercase)
• Error response format consistent (FastAPI HTTPException with detail field)
• Tree response format (nested JSON structure)
• Bootstrap accepts JSON body (not multipart CSV)

​

Red Flags & Blockers Review

​

✅ RESOLVED

1. Schema alignment: Removed all references to relationship_type and notes fields
2. Normalization consistency: SQL normalization includes hyphen removal to match Python
3. UUID normalization: Added explicit requirement to normalize UUIDs to lowercase
4. Date format: Specified YYYY-MM-DD format, no timezone, no time component
5. Error response format: Specified FastAPI HTTPException with detail field
6. Router registration: Added requirement to register router in api/main.py
7. X-Org-Id header: Added requirement for all endpoints to accept header
8. Bootstrap request format: Changed from CSV upload to JSON body (consistent with other admin endpoints)
9. Tree response format: Specified nested JSON structure for dashboard consumption
10. Batch reparent error handling: Specified 200 OK with errors in per-op results (not 400 for partial failures)

​

⚠️ REMAINING CONSIDERATIONS

1. Idempotency detection: Implementation must query existing row before insert (not specified in plan, but implied)
2. CSV parsing for seed generator: Script must handle both JSON and CSV input (specified)
3. Dashboard error handling: Dashboard must handle FastAPI error format ({"detail": "..."} or {"detail": [...]})
4. API client method signatures: Must follow existing AgentMappingClient pattern with requestWithHeaders for X-Org-Id

​

Notes

• All endpoints follow existing FastAPI patterns (Pydantic schemas, dependency injection, HTTPException)
• Dashboard API client methods should use requestWithHeaders pattern for X-Org-Id header
• Error responses follow FastAPI standard format (no custom error schemas needed)
• Tree endpoint returns nested JSON structure (dashboard-friendly)
• Bootstrap endpoint accepts JSON body (not multipart CSV) for consistency