PAUMS – System Requirements Specification (SRS) v2.0

What is PAUMS v2.0?

A:

PAUMS v2.0 is a production-ready enterprise system that provides authoritative, audit-safe utilization reporting across multiple regions with heterogeneous data sources. The system has evolved from a basic dashboard to a hierarchy-driven capacity planning system with executive-grade reporting.

What technology stack does PAUMS use?

A:

The system is built using FastAPI, MariaDB, and a lightweight HTML/JS frontend, with immutable snapshots as the core architectural principle.

How many core tables does PAUMS have?

A:

The PAUMS database consists of 9 core tables that implement the dual-region, snapshot-based architecture. All tables use InnoDB engine with appropriate indexing for performance.

What is the purpose of the audit_logs table?

A:

Stores audit trail for all system events including snapshot rebuilds, data uploads, and admin actions. Critical for compliance and traceability.

Field	Type	Description
id	int(11)	Primary key, auto-increment
event_type	varchar(255)	Type of audit event
username	varchar(255)	User who performed the action

What does the emea_allocations table store?

A:

Stores EMEA region allocation data. Contains monthly FTE allocation percentages that are converted to hours during snapshot creation.

What is the employees table used for?

A:

Master employee table containing workforce data. Includes FTE defaults, region, and role classification information.

What is the monthly_snapshot table and why is it critical?

A:

Core immutable snapshot table. Contains frozen monthly metrics at both organization and employee levels. This is the source of truth for all reporting.

Key Design: This table is immutable - historical snapshots never recalculated.

What is the project_hierarchy table for?

A:

Canonical project hierarchy table extracted from EMEA allocation files. Supports multi-level rollup reporting and drilldowns.

What is the purpose of the staging_apac table?

A:

Staging table for raw APAC timesheet data imported from Clockify exports. Used for data validation before processing into timesheets.

What are the key database relationships?

A:

employees (1) —— (many) timesheets
employees (1) —— (many) emea_allocations
employees (1) —— (many) monthly_snapshot (scope='EMPLOYEE')
projects (1) —— (many) timesheets
projects (1) —— (many) emea_allocations
project_hierarchy (1) —— (1) projects (via project_code)

What are the core design principles of PAUMS?

A:

Deterministic calculations - Identical results across rebuilds
Immutable snapshots - Historical data never recalculated
Region-specific semantics preserved - No forced normalization
Audit-safe by design - Full traceability and reproducibility
Executive-grade reporting - McKinsey-style PDF outputs

What are the key architectural enhancements in v2.0?

A:

Enhancement	Description
Dual Region Calculation Model	APAC: Actual hours from timesheets, EMEA: FTE allocation percentages
Immutable Snapshot Layer	Monthly snapshots with controlled rebuild capability
Project Hierarchy System	Canonical hierarchy storage with multi-level rollup
Executive Reporting Engine	6-page PDF reports with narrative + KPI layers

What user roles does PAUMS support?

A:

Role	Description	Capabilities
Admin	System administrator	Snapshot rebuild, uploads, exports, audit overrides
Viewer	Read-only user	Dashboards, reports, PDF exports
Executive	Leadership user	Executive summaries, narrative views

How does authentication work?

A:

JWT-based authentication
Token stored in browser localStorage
Authorization header: Bearer <token>
Role-based endpoint access control

What is the source of truth for reporting?

A:

The monthly_snapshot table is the single source of truth for all reporting.

Scope: ORG (organization) and EMPLOYEE levels
Key fields: available_hours, utilized_hours, billable_hours, tpmo_hours, actual_hours
Immutable guarantee: Historical snapshots never recalculated

How are employees classified in PAUMS?

A:

Represents workforce master data including region, job title, FTE defaults. Leadership classification via deterministic rules.

What is the project hierarchy system?

A:

Table: project_hierarchy
Stores canonical, normalized project hierarchy
Fields: hierarchy_key, level_1_domain, level_2_type, level_3_name, level_4_code
Enables multi-level rollups and drilldowns

How does APAC processing work?

A:

Source: timesheets table
Granularity: Daily actual hours
Logic: Hours summed per month
Purpose: Historical accuracy and billing alignment

How does EMEA processing work?

A:

Source: emea_allocations table
Granularity: Monthly allocation percentages
Logic: allocation_pct × FTE hours
Purpose: Forward-looking capacity planning

Are APAC and EMEA data merged at raw level?

A:

Design Decision: APAC and EMEA data are never merged at raw level. Only snapshot outputs are aggregated for reporting.

What are the authoritative metric definitions?

A:

Metric	Definition	Source
Available Hours	FTE default hours	monthly_snapshot.available_hours
Utilized Hours	Billable hours excluding TPMO	monthly_snapshot.utilized_hours
TPMO Hours	Project code 27290 hours	monthly_snapshot.tpmo_hours
Billable Hours	All project hours (including TPMO)	monthly_snapshot.billable_hours

What are the core calculation formulas?

A:

Utilization % = (Utilized Hours / Available Hours) × 100
Billable % = (Billable Hours / Available Hours) × 100
TPMO % of Available = (TPMO Hours / Available Hours) × 100
Idle Hours = Available Hours − Utilized Hours

How is leadership classification determined?

A:

Leadership Definition:
1. job_title IN LEADERSHIP_TITLES
   OR
2. full_name IN LEADERSHIP_NAME_EXCEPTIONS

All other employees → Project Managers

How are snapshots created?

A:

Triggered monthly via admin interface
Consumes raw APAC timesheets and EMEA allocations
Generates immutable ORG and EMPLOYEE scope snapshots

When can snapshots be rebuilt?

A:

Allowed before month lock for data corrections
Captures late APAC entries and EMEA allocation updates
Admin-explicit action with audit logging

What does snapshot lock mean?

A:

Prevents further modifications to monthly data
Admin-only override with justification
Ensures executive reporting stability

What dashboard APIs are available?

A:

Endpoint	Description	Version
/api/v1/dashboard/summary	Monthly KPIs and metrics	v1/v2
/api/v1/dashboard/trends/exec/narrative	Executive narrative and alerts	v2 only
/api/v1/dashboard/yearly-summary	Yearly aggregated view	v2 only

What reporting APIs are available?

A:

Endpoint	Output	Purpose
/api/v1/reports/executive-summary/pdf	6-page PDF report	Executive review
/api/v1/exports/yearly-csv	CSV export	Data analysis
/api/v1/exports/employee-drilldown	Excel format	Detailed review

What is the executive summary requirement?

A:

System shall aggregate yearly data from monthly_snapshot table
System shall classify employees using deterministic role buckets
System shall compute all metrics from immutable snapshots
System shall provide leadership inclusion toggle

What is the PDF export requirement?

A:

PDF shall exactly match UI values (bit-for-bit consistency)
PDF shall be downloadable on demand
PDF shall include generation timestamp and audit footer
PDF shall follow McKinsey-style 6-page format

What are the data integrity requirements?

A:

System shall never modify historical snapshots
System shall compute all metrics server-side
System shall maintain audit logs for all rebuild operations

What should I check if charts show wrong data?

A:

Check UI Layer - Compare chart labels vs tooltip values
Inspect Network Tab - Examine API payloads in developer tools
Check Service Logic - Validate allocation sums and aggregation
Query Database - Direct database validation
Trace Back to Ingestion - Excel row → normalized row → snapshot

What are common frontend failure patterns?

A:

Symptom	Root Cause
"Cannot set properties of null" errors	HTML ID changed but JS references old ID
Executive KPIs stuck at "Loading…"	Narrative API failed or no data
Single green bar in APAC charts	Backend aggregating into "Billable Work" category

What SQL checks can I run for validation?

A:

-- Allocation validation (EMEA)
SELECT employee_id, year, month,
       alloc_control,
       SUM(allocation) AS summed
FROM emea_allocations
GROUP BY employee_id, year, month
HAVING ABS(alloc_control - SUM(allocation)) > 0.001;

-- Snapshot vs project mismatch detection
SELECT s.employee_id, s.year, s.month,
       s.utilized_hours,
       SUM(p.billable_hours + p.tpmo_hours) AS project_total
FROM monthly_snapshot s
JOIN employee_project_monthly p USING (employee_id, year, month)
GROUP BY s.employee_id, s.year, s.month
HAVING ABS(s.utilized_hours - project_total) > 0.01;

What are the frontend components?

A:

File	Purpose	Status
dashboard.html	Main dashboard UI, charts, modals	✅ Stable
dashboard.js	Dashboard controller, API calls	✅ Stable
org_trend_chart.js	Org utilization trend chart	✅ Existing
org_billable_tpmo_chart.js	Billable vs TPMO chart	✅ Existing

What are the backend services?

A:

File	Purpose	Status
dashboard_service.py	Monthly summaries, resource list	✅
dashboard_trends_service.py	Org & employee trends	✅
yearly_summary_service.py	Executive yearly aggregation	✅
data_health_service.py	Missing data / anomalies	✅

System Overview