Blueprint — Staff Details

Architect Agent 3 of the Pancha · 2026-05-02 · slug staff

1. Module summary

Staff Details is the personnel system of record for every paid or stipendiary worker in a Dharmika institution — archakas, administrative employees, kitchen and grounds staff, security, sevadars, and (for Vedapathashalas) instructors. Each staff member has one row in the canonical members table (the same row a donor or volunteer would have) and one row in a 1:1 staff_profiles table that holds employment-only fields. This dual-row design preserves the "one person, many operational views" promise: an archaka who is also a recurring donor and the parent of a student appears as a single members record with three sibling category rows. Staff Details owns the personnel side: medical conditions, qualifications (including Vedic capability checklists for archakas), training, experience, hierarchy, periodic SWOT reviews, pay structure, and encrypted identity documents. Pay structure feeds Vitta payroll (Agent 1); every change writes to audit_events (Agent 2); Events Planner (Agent 4) reads the Vedic-capability matrix to assign archakas to rituals.

2. Data model

All new tables inherit Base + AuditMixin + TenantMixin. Sanskrit-bearing columns (gothra, sutra, shakha, capability names) use Text to preserve VijayaDV PUA characters. Identity-document fields are LargeBinary blobs encrypted with a wrapping helper (see decrypt_identity_document in §5).

# src/aayojana/models/staff.py
from datetime import date, datetime
from sqlalchemy import (
    Boolean, Date, DateTime, ForeignKey, Index, Integer, JSON,
    LargeBinary, Numeric, String, Text, UniqueConstraint,
)
from sqlalchemy.orm import Mapped, mapped_column, relationship
from aayojana.models.base import AuditMixin, Base, TenantMixin, uuid4_str


class StaffProfile(Base, AuditMixin, TenantMixin):
    """1:1 with members. Holds staff-only employment fields.

    The same person's biographical data (name, dob, photo, gothra,
    nakshatram, family) lives on members — DO NOT redefine here.
    """
    __tablename__ = "staff_profiles"
    __table_args__ = (
        UniqueConstraint("member_id", name="uq_staff_profile_member"),
        Index("ix_staff_profile_supervisor", "supervisor_id"),
        Index("ix_staff_profile_employment_status", "employment_status"),
    )

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid4_str)
    member_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("members.id", ondelete="RESTRICT"), nullable=False
    )
    # canonical employee number per tenant (sequence_numbers backed)
    staff_code: Mapped[str] = mapped_column(String(32), nullable=False, index=True)
    # archaka | admin | kitchen | grounds | security | sevadar | instructor | other
    role_category: Mapped[str] = mapped_column(String(20), nullable=False, index=True)
    designation: Mapped[str | None] = mapped_column(String(127), nullable=True)
    department: Mapped[str | None] = mapped_column(String(63), nullable=True)
    branch_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("branches.id"), nullable=True, index=True
    )
    supervisor_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("staff_profiles.id"), nullable=True
    )
    # active | on_leave | suspended | exited
    employment_status: Mapped[str] = mapped_column(
        String(20), nullable=False, server_default="active"
    )
    employment_type: Mapped[str | None] = mapped_column(
        String(20), nullable=True
    )  # permanent | contract | sambhavana | volunteer-paid
    date_of_joining: Mapped[date | None] = mapped_column(Date, nullable=True)
    date_of_confirmation: Mapped[date | None] = mapped_column(Date, nullable=True)
    date_of_exit: Mapped[date | None] = mapped_column(Date, nullable=True)
    exit_reason: Mapped[str | None] = mapped_column(String(255), nullable=True)
    # Medical (flags + free-text)
    blood_group: Mapped[str | None] = mapped_column(String(5), nullable=True)
    has_diabetes: Mapped[bool] = mapped_column(Boolean, server_default="false")
    has_hypertension: Mapped[bool] = mapped_column(Boolean, server_default="false")
    has_asthma: Mapped[bool] = mapped_column(Boolean, server_default="false")
    has_heart_condition: Mapped[bool] = mapped_column(Boolean, server_default="false")
    other_medical: Mapped[str | None] = mapped_column(Text, nullable=True)
    current_medications: Mapped[str | None] = mapped_column(Text, nullable=True)
    emergency_contact_name: Mapped[str | None] = mapped_column(String(127), nullable=True)
    emergency_contact_phone: Mapped[str | None] = mapped_column(String(48), nullable=True)
    emergency_contact_relation: Mapped[str | None] = mapped_column(String(63), nullable=True)
    # Archaka-only Vedic identity (gothra/nakshatram already on members)
    sutra: Mapped[str | None] = mapped_column(Text, nullable=True)
    shakha: Mapped[str | None] = mapped_column(Text, nullable=True)
    accommodation_room_id: Mapped[str | None] = mapped_column(
        String(36), nullable=True
    )  # FK added when Asset Mgmt rooms table lands (Agent 4)

    member: Mapped["Member"] = relationship()  # type: ignore[name-defined]
    qualifications: Mapped[list["StaffQualification"]] = relationship(
        back_populates="staff", cascade="all, delete-orphan", lazy="selectin"
    )
    training_records: Mapped[list["StaffTrainingRecord"]] = relationship(
        back_populates="staff", cascade="all, delete-orphan", lazy="noload"
    )
    experience: Mapped[list["StaffExperience"]] = relationship(
        back_populates="staff", cascade="all, delete-orphan", lazy="noload"
    )
    swot_reviews: Mapped[list["StaffSwotReview"]] = relationship(
        back_populates="staff", cascade="all, delete-orphan", lazy="noload"
    )
    pay_structures: Mapped[list["StaffPayStructure"]] = relationship(
        back_populates="staff", cascade="all, delete-orphan", lazy="noload"
    )
    identity_documents: Mapped[list["StaffIdentityDocument"]] = relationship(
        back_populates="staff", cascade="all, delete-orphan", lazy="noload"
    )
    direct_reports: Mapped[list["StaffProfile"]] = relationship(
        "StaffProfile", remote_side="StaffProfile.supervisor_id", lazy="noload"
    )


class StaffQualification(Base, AuditMixin, TenantMixin):
    """Degrees, certifications, and Vedic capabilities.

    `vedic_capabilities` is a JSONB list of approved capability slugs
    (sandhya_vandanam, agnikaryam, rudra, chamaka, mahanayasam,
    temple_vastu, kalyanotsavam, …). The taxonomy is shipped per
    tenant in a `vedic_capability_catalog` config (see Open Q #4).
    """
    __tablename__ = "staff_qualifications"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid4_str)
    staff_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("staff_profiles.id", ondelete="CASCADE"), nullable=False, index=True
    )
    # academic | professional | vedic | language
    kind: Mapped[str] = mapped_column(String(20), nullable=False)
    title: Mapped[str] = mapped_column(Text, nullable=False)  # MA Sanskrit, Ghanapatha
    institution: Mapped[str | None] = mapped_column(Text, nullable=True)
    year_obtained: Mapped[int | None] = mapped_column(Integer, nullable=True)
    grade_or_class: Mapped[str | None] = mapped_column(String(48), nullable=True)
    vedic_capabilities: Mapped[list | None] = mapped_column(JSON, nullable=True)
    certificate_doc_id: Mapped[str | None] = mapped_column(
        String(36), nullable=True
    )  # points at staff_identity_documents row when scanned
    verified: Mapped[bool] = mapped_column(Boolean, server_default="false")
    verified_by: Mapped[int | None] = mapped_column(
        Integer, ForeignKey("users.id"), nullable=True
    )
    verified_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)

    staff: Mapped["StaffProfile"] = relationship(back_populates="qualifications")


class StaffTrainingRecord(Base, AuditMixin, TenantMixin):
    __tablename__ = "staff_training_records"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid4_str)
    staff_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("staff_profiles.id", ondelete="CASCADE"), nullable=False, index=True
    )
    program_name: Mapped[str] = mapped_column(Text, nullable=False)
    provider: Mapped[str | None] = mapped_column(Text, nullable=True)
    # internal | external | online | mentorship
    delivery: Mapped[str] = mapped_column(String(20), nullable=False)
    start_date: Mapped[date | None] = mapped_column(Date, nullable=True)
    end_date: Mapped[date | None] = mapped_column(Date, nullable=True)
    duration_hours: Mapped[int | None] = mapped_column(Integer, nullable=True)
    outcome: Mapped[str | None] = mapped_column(Text, nullable=True)
    cost: Mapped[str | None] = mapped_column(Numeric(12, 2), nullable=True)

    staff: Mapped["StaffProfile"] = relationship(back_populates="training_records")


class StaffExperience(Base, AuditMixin, TenantMixin):
    __tablename__ = "staff_experience"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid4_str)
    staff_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("staff_profiles.id", ondelete="CASCADE"), nullable=False, index=True
    )
    institution: Mapped[str] = mapped_column(Text, nullable=False)
    designation: Mapped[str | None] = mapped_column(Text, nullable=True)
    start_date: Mapped[date | None] = mapped_column(Date, nullable=True)
    end_date: Mapped[date | None] = mapped_column(Date, nullable=True)  # null = current/internal
    is_current: Mapped[bool] = mapped_column(Boolean, server_default="false")
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)
    reference_contact: Mapped[str | None] = mapped_column(Text, nullable=True)

    staff: Mapped["StaffProfile"] = relationship(back_populates="experience")


class StaffSwotReview(Base, AuditMixin, TenantMixin):
    __tablename__ = "staff_swot_reviews"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid4_str)
    staff_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("staff_profiles.id", ondelete="CASCADE"), nullable=False, index=True
    )
    period_label: Mapped[str] = mapped_column(String(63), nullable=False)  # e.g. "FY2025-26 Q3"
    review_date: Mapped[date] = mapped_column(Date, nullable=False)
    reviewer_id: Mapped[int] = mapped_column(
        Integer, ForeignKey("users.id"), nullable=False
    )
    strengths: Mapped[str | None] = mapped_column(Text, nullable=True)
    weaknesses: Mapped[str | None] = mapped_column(Text, nullable=True)
    opportunities: Mapped[str | None] = mapped_column(Text, nullable=True)
    threats: Mapped[str | None] = mapped_column(Text, nullable=True)
    development_actions: Mapped[str | None] = mapped_column(Text, nullable=True)
    overall_rating: Mapped[int | None] = mapped_column(Integer, nullable=True)  # 1..5
    acknowledged_by_staff: Mapped[bool] = mapped_column(Boolean, server_default="false")
    acknowledged_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)

    staff: Mapped["StaffProfile"] = relationship(back_populates="swot_reviews")


class StaffPayStructure(Base, AuditMixin, TenantMixin):
    """Versioned pay envelope for one staff. Vitta payroll iterates the
    *active* structure (where effective_to is null or future).
    """
    __tablename__ = "staff_pay_structures"
    __table_args__ = (
        Index("ix_staff_pay_active", "staff_id", "effective_from", "effective_to"),
    )

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid4_str)
    staff_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("staff_profiles.id", ondelete="CASCADE"), nullable=False, index=True
    )
    effective_from: Mapped[date] = mapped_column(Date, nullable=False)
    effective_to: Mapped[date | None] = mapped_column(Date, nullable=True)
    # salary | sambhavana | stipend | honorarium
    pay_kind: Mapped[str] = mapped_column(String(20), nullable=False)
    currency: Mapped[str] = mapped_column(String(3), nullable=False, server_default="INR")
    pf_applicable: Mapped[bool] = mapped_column(Boolean, server_default="false")
    esi_applicable: Mapped[bool] = mapped_column(Boolean, server_default="false")
    tds_section: Mapped[str | None] = mapped_column(String(20), nullable=True)
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)

    staff: Mapped["StaffProfile"] = relationship(back_populates="pay_structures")
    components: Mapped[list["StaffPayComponent"]] = relationship(
        back_populates="structure", cascade="all, delete-orphan", lazy="selectin"
    )


class StaffPayComponent(Base, AuditMixin, TenantMixin):
    """Line items inside a pay structure — basic, HRA, food allowance,
    accommodation perk, archaka sambhavana base, festival bonus etc.
    """
    __tablename__ = "staff_pay_components"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid4_str)
    structure_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("staff_pay_structures.id", ondelete="CASCADE"),
        nullable=False, index=True,
    )
    component_code: Mapped[str] = mapped_column(String(40), nullable=False)
    # earning | deduction | perk_in_kind
    component_type: Mapped[str] = mapped_column(String(20), nullable=False)
    label: Mapped[str] = mapped_column(Text, nullable=False)
    amount: Mapped[str] = mapped_column(Numeric(12, 2), nullable=False)
    is_taxable: Mapped[bool] = mapped_column(Boolean, server_default="true")
    formula: Mapped[str | None] = mapped_column(Text, nullable=True)
    # e.g. "0.4 * BASIC" — evaluated by Vitta payroll engine

    structure: Mapped["StaffPayStructure"] = relationship(back_populates="components")


class StaffIdentityDocument(Base, AuditMixin, TenantMixin):
    """Aadhaar, PAN, passport, bank passbook, qualification certificates.

    `payload_encrypted` holds the encrypted blob. The wrapping helper
    `aayojana.services.crypto.encrypt_identity` performs envelope
    encryption (KMS-managed DEK, see Open Q #5). Every read goes
    through `decrypt_identity_document(...)` which logs to
    audit_events with reason + requester.
    """
    __tablename__ = "staff_identity_documents"
    __table_args__ = (
        UniqueConstraint("staff_id", "doc_type", name="uq_staff_doc_type"),
    )

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid4_str)
    staff_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("staff_profiles.id", ondelete="CASCADE"), nullable=False, index=True
    )
    # aadhaar | pan | passport | driving_license | bank_passbook | voter_id | other
    doc_type: Mapped[str] = mapped_column(String(20), nullable=False)
    # masked surface for UI display, e.g. "XXXX XXXX 1234"
    masked_value: Mapped[str | None] = mapped_column(String(48), nullable=True)
    payload_encrypted: Mapped[bytes | None] = mapped_column(LargeBinary, nullable=True)
    # KMS key version used to wrap the DEK (rotation friendly)
    key_version: Mapped[str | None] = mapped_column(String(32), nullable=True)
    issued_on: Mapped[date | None] = mapped_column(Date, nullable=True)
    expires_on: Mapped[date | None] = mapped_column(Date, nullable=True)
    last_read_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    last_read_by: Mapped[int | None] = mapped_column(
        Integer, ForeignKey("users.id"), nullable=True
    )
    pending_two_key_request_id: Mapped[str | None] = mapped_column(
        String(36), nullable=True
    )  # FK to audit's pending_writes when set

    staff: Mapped["StaffProfile"] = relationship(back_populates="identity_documents")

3. Reuse map

Every model below is already defined. Staff Details references them via FK and never redefines:

Sanskrit lookup tables nakshatramTypes, tithiTypes, pakshamTypes, maasamTypes, padamTypes are reached transitively through Member.profile. Staff blueprint does not add to or duplicate them.

4. API surface

All routes mounted under /api/staff from aayojana/staff/router.py. Permission tier: tenant-admin or module-admin for writes; member+ for reads on own row only.

UI pages (HTML, served from staff/router.py separately): - /staff/ — directory grid - /staff/{id} — detail with tabs (Personal · Medical · Qualifications · Training · Experience · SWOT · Pay · Documents) - /staff/onboard — multi-step wizard - /staff/hierarchy — org-chart visual

5. Service layer

src/aayojana/services/staff_service.py

async def onboard_staff(
    db: AsyncSession,
    tenant_id: str,
    personal: PersonalDataIn,           # name, dob, gothra, etc → members
    employment: EmploymentDataIn,       # role_category, designation, doj
    qualifications: list[QualificationIn] = (),
    hierarchy: HierarchyIn | None = None,
    user_id: str | None = None,         # if linking to existing User
    actor: User = ...,
) -> StaffProfile:
    """Atomic. In one transaction:
      1. INSERT into members (or look up if member_id supplied) using
         existing aayojana.services.member_service.create_member
      2. INSERT into memberships with role='member' (status='active')
      3. Allocate staff_code from sequence_numbers (prefix per tenant)
      4. INSERT into staff_profiles
      5. INSERT each qualification
      6. Write audit_event 'staff.onboarded'
      7. Enqueue Comms welcome email (Agent 5)
    Rollback all on any failure.
    """

async def categorize_archaka_capabilities(
    db: AsyncSession, staff_id: str, capabilities: list[str], actor: User
) -> StaffQualification:
    """Upsert the single 'vedic'-kind StaffQualification row whose
    `vedic_capabilities` JSON list is the authoritative checklist.
    Validates each slug against the tenant's
    vedic_capability_catalog (see Open Q #4). Writes audit_event."""

async def find_archakas_for_ritual(
    db: AsyncSession, tenant_id: str, required_capabilities: list[str],
    on_date: date | None = None,
) -> list[StaffProfile]:
    """Selects active staff whose `vedic_capabilities` JSON contains
    every required slug. Optionally excludes those already rostered
    on `on_date` once the Events Planner roster table lands."""

async def submit_swot_review(
    db: AsyncSession, staff_id: str, payload: SwotIn, reviewer: User
) -> StaffSwotReview:
    """Create review row; emit 'staff.swot.created' audit event;
    fire Comms notification to staff member."""

async def upsert_pay_structure(
    db: AsyncSession, staff_id: str, payload: PayStructureIn, actor: User
) -> StaffPayStructure:
    """Closes the currently-active row (sets effective_to = new
    effective_from - 1 day) and inserts the new structure with
    components. Writes audit_event 'staff.pay.changed' carrying
    (old_total, new_total). Vitta payroll picks up the active row
    on the next run."""

async def encrypt_identity_document(
    db: AsyncSession, staff_id: str, doc_type: str, plaintext: str,
    issued_on: date | None, expires_on: date | None, actor: User,
) -> StaffIdentityDocument:
    """Calls aayojana.services.crypto.envelope_encrypt() to produce
    (ciphertext, key_version, masked). Stores a row. Creates a
    pending_writes entry (Agent 2) requiring a second authoriser
    before the row is marked committed."""

async def decrypt_identity_document(
    db: AsyncSession, doc_id: str, requesting_user_id: int,
    reason: str,
) -> str:
    """Loads ciphertext, calls envelope_decrypt(), updates
    last_read_at/last_read_by on the row, *and writes an audit_event*
    'staff.identity.read' carrying doc_type+reason+requester. Returns
    plaintext to caller. Caller must NOT cache."""

async def compute_hierarchy_tree(
    db: AsyncSession, tenant_id: str, root_id: str | None = None
) -> dict:
    """Recursive CTE over staff_profiles.supervisor_id; returns
    nested dict {staff, reports:[…]}. Cached 5 min per tenant."""

async def soft_exit_staff(
    db: AsyncSession, staff_id: str, exit_date: date, reason: str, actor: User
) -> StaffProfile:
    """Sets employment_status='exited'; closes active pay structure;
    sets memberships.status='suspended'; writes audit_event."""

6. UI / Templates

Templates live under src/aayojana/templates/staff/ (new directory). All extend base.html.

7. Migration plan

All three migrations carry the standard tenant_id nullable FK to organisations.id and a backfill block that no-ops on greenfield databases.

8. Cross-module dependencies

Reads from - members — biographical row (must exist before staff_profile insert) - memberships — to find existing role rows for the user - branches — for branch_id selector - users — reviewer / verifier / reader linkage - sequence_numbers — staff_code generation - (later) Asset Mgmt rooms — for accommodation_room_id

Writes to - Audit Trail (Agent 2) — every onboard / pay-change / qualification verify / SWOT submit / identity read writes an audit_events row; identity-document writes go through pending_writes (two-key) - Vitta Fin (Agent 1) — pay structure rows are read by Vitta payroll runs; Staff service emits pay.structure.activated domain event - Comms (Agent 5) — onboarding welcome, SWOT acknowledgement nudge, document-expiry reminders (Aadhaar 10y, PAN never, passport 10y) trigger Comms templates

Read by - Events Planner (Agent 4) — find_archakas_for_ritual populates the archaka roster - Vitta payroll (Agent 1) — active pay structures - Members Suite (sibling of this blueprint) — Staff Index sub-module surfaces a read-only join of members ⨝ staff_profiles

9. Implementation phases

Phase A — schemas + minimal CRUD (one sprint) - Migrations 0016–0017 - StaffProfile / StaffQualification / experience / training basic POST/GET - Onboarding wizard (without two-key, without encryption) - Directory + detail templates with Personal/Medical/Qualifications tabs only

Phase B — Vedic capability matrix + hierarchy + SWOT + identity docs (two sprints) - Migration 0018 + crypto helper (aayojana.services.crypto) - Vedic capability catalog (per-tenant config seed) - find_archakas_for_ritual API (consumed by Agent 4 stub) - Hierarchy tree + supervisor assignment - SWOT submit + acknowledge flow - Identity document encrypt/decrypt with two-key + audit logging

Phase C — pay structures wired to Vitta (joint with Agent 1) - Pay structure versioning UI - Vitta payroll consumer reads active structures - Document-expiry reminder cron (Comms) - Staff dashboard (counts by role_category, attrition KPIs, expiring documents)

10. Open questions

1. Member↔Staff mapping cardinality. Strict 1:1 enforced by uq_staff_profile_member. If a person rejoins after exit, do we create a new staff_profile (preserving history) or reactivate? Proposed: new row; old row kept with employment_status='exited' for audit. Decision needed.
2. Exit handling. Soft (employment_status='exited', keep row) vs hard delete. Proposed: soft only; pay structures auto-closed; identity documents purged after retention window (5 years per DPDP Act guidance). Decision needed.
3. Vedic-capability taxonomy. Fixed enum vs tenant-extensible JSON catalog. Proposed: tenant-extensible — ship a default catalog (sandhya_vandanam, agnikaryam, rudra, chamaka, mahanayasam, temple_vastu, kalyanotsavam, brahma_yajna, deva_yajna) but allow tenant admins to add (e.g., a Smarta tenant may add dakshinamurti_pooja). Decision needed.
4. Identity-doc encryption key custody. KMS-managed (GCP KMS in aayojana project) vs app-derived (libsodium with master secret in env). Proposed: GCP KMS — DEK per tenant, KEK per project; rotation via key_version column; audit trail of decrypt() calls already part of design. Decision needed (billing implication: GCP KMS ~₹1/month/key + ₹0.03/10k operations).
5. Two-key for identity docs — who is the second key? Tenant-admin role only, or any module-admin? Proposed: tenant-admin only, to match jewel-vault policy in Asset Mgmt. Decision needed.
6. Salary visibility. Is the staff member shown their own pay structure in self-service? Proposed: yes (read-only) — required for transparency. Decision needed.
7. Sambhavana vs salary in pay_kind. Vitta payroll will route differently (TDS section, EPF/ESI rules). Should pay_kind be enforced as one-of-set or free-text? Proposed: enforced enum (salary | sambhavana | stipend | honorarium). Decision needed.
8. Hierarchy cycle prevention. Recursive supervisor_id can in principle form a cycle. Proposed: validate on write (walk ancestors, refuse if self appears) and add a CHECK constraint via Alembic-managed trigger. Decision needed.