KPI Matrix How-to Guide Public

03 — Database Schema Design

03 — Database Schema Design Module: KPI Matrix Status: Implemented — reconciled to migration modules/KpiMatrix/migrations/20260604185300_create_kpi_matrix_tables.php Last Revised…

Guide version: r1 Module version: 1.4.0 Updated: 2026-07-22 Estimated time: 15 min 2 views

Module: KPI Matrix Status: Implemented — reconciled to migration modules/KpiMatrix/migrations/20260604185300_create_kpi_matrix_tables.php Last Revised: June 2026 Depends On: 02_KPI_CATEGORIES_AND_METRICS.md Referenced By: 04_SALARY_INTEGRATION.md, 05_CALCULATION_ENGINE.md

Authority: The shipped migration is the source of truth for column names and types. This document was refreshed to match it column-for-column. The migration builds tables with CodeIgniter dbforge; table/index names below are shown without a DB prefix because the migration's raw index DDL is unprefixed (the reference deployment uses an empty prefix). At runtime the configured prefix — if any — is applied automatically via $this->db->dbprefix().


Table of Contents

  1. Schema Overview
  2. Entity-Relationship Diagram
  3. Table Definitions
  4. Indexes & Constraints
  5. Seed Data (Config-Driven)
  6. Migration Strategy
  7. Data Retention & Archival

1. Schema Overview

The KPI Matrix module ships 9 new tables and 0 modifications to existing tables. KPI data is read from existing models (attendance, tasks, projects, tickets, etc.) at calculation time; no foreign keys point INTO existing tables.

Design Principles

Principle Implementation
No existing-table modifications KPI tables store user IDs but add no FK constraints to existing tables
Soft deletes Every table includes deleted TINYINT(1) DEFAULT 0
Trailing columns Every table ends with created_by, date_created, updated_at, deleted
Idempotent migration Each _create_* method early-returns if the table already exists (SHOW TABLES LIKE)
UUIDs on entities kpi_templates, kpi_review_cycles, kpi_employee_assignments carry a uuid for external references
JSON in TEXT/LONGTEXT Flexible structures (thresholds, category scores, computation logs) stored as JSON strings
DECIMAL(4,2) scores 1.00–5.00 continuous scale for all normalized/composite scores

New Tables Summary

# Table Keyed by Purpose
1 kpi_templates template_code Role-based weight templates (General Staff, Technical, etc.)
2 kpi_template_metrics (template_id, metric_key) Per-metric config within a template (weight, thresholds, automation)
3 kpi_review_cycles cycle_code Evaluation periods (monthly/quarterly/semi-annual/annual)
4 kpi_employee_assignments (cycle_id, employee_id) Employee ↔ template ↔ reviewer per cycle
5 kpi_metric_scores (assignment_id, metric_key) One computed metric score per assignment
6 kpi_composite_results assignment_id Final composite score per assignment
7 kpi_manual_entries (assignment_id, metric_key) Manual values for non-automated metrics
8 kpi_score_history (employee_id, cycle_id) Immutable score snapshots for trend/audit
9 kpi_salary_recommendations (employee_id, cycle_id) Advisory salary step recommendations

Note on scoping: kpi_metric_scores, kpi_composite_results, and kpi_manual_entries are scoped by assignment_id (not denormalized user_id/cycle_id). kpi_score_history and kpi_salary_recommendations are scoped by employee_id + cycle_id. This differs from earlier drafts of this document.


2. Entity-Relationship Diagram

 kpi_templates ──1:N──► kpi_template_metrics
   │ id, uuid, name,        │ template_id (→templates.id)
   │ template_code,         │ category_code, metric_key,
   │ category_weights(JSON) │ weight, category_weight,
   │                        │ scoring_thresholds(JSON),
   │                        │ override_thresholds(JSON),
   │                        │ scoring_direction, is_automated,
   │                        │ data_source_model/method, unit
   │
   ├───────────────────────────────────────────┐
   ▼                                            ▼
 kpi_review_cycles ──1:N──► kpi_employee_assignments ──1:1──► kpi_composite_results
   │ id, uuid, name,          │ id, uuid,                       │ assignment_id (→assignments.id)
   │ cycle_code, cycle_type,  │ cycle_id (→cycles.id),          │ composite_score, rating_band,
   │ template_id,             │ employee_id, template_id,       │ category_scores(JSON),
   │ period_start/end,        │ reviewer_id,                    │ metrics_computed/total,
   │ status, auto_calculate,  │ secondary_reviewer_id,          │ data_sufficiency_pct,
   │ recommendations_*        │ status, self_assessment_*,      │ salary_* , status
   │                          │ reviewer_notes,                 │
   │                          │ final_override_score            └──1:N──► kpi_metric_scores
   │                          │                                          │ assignment_id,
   │                          │                                          │ category_code, metric_key,
   │                          │                                          │ raw_value, normalized_score,
   │                          │                                          │ weight, source,
   │                          │                                          │ computation_log(JSON)
   │                          └──1:N──► kpi_manual_entries
   │                                     │ assignment_id, metric_key,
   │                                     │ value, unit, entered_by,
   │                                     │ approved_by, status
   │
   ├──1:N──► kpi_score_history
   │           │ employee_id, cycle_id, composite_score,
   │           │ rating_band, category_scores(LONGTEXT),
   │           │ snapshot_type, template_snapshot, is_final
   │
   └──1:N──► kpi_salary_recommendations
               │ employee_id, cycle_id, assignment_id,
               │ composite_result_id, composite_score, rating_band,
               │ current/recommended salary & step, step_change,
               │ flags(JSON), computation_log(LONGTEXT),
               │ override_*, status workflow, salary_history_id

3. Table Definitions

Columns are listed exactly as created by the migration (add_field/raw column strings). All tables additionally carry the four ERPat trailing columns: created_by INT NULL, date_created DATETIME NULL, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted TINYINT(1) DEFAULT 0.

3.1 kpi_templates

Column Type Notes
id INT(11) UNSIGNED AI PK
uuid VARCHAR(36) NOT NULL External reference id
name VARCHAR(100) NOT NULL Display name (e.g. "General Staff")
template_code VARCHAR(50) NOT NULL Machine code (general_staff, …)
description TEXT NULL
is_default TINYINT(1) DEFAULT 0 1 = system-seeded
status ENUM('active','inactive','archived') DEFAULT 'active'
category_weights TEXT NULL JSON {"ATT":20,"TSK":20,…} quick reference

Indexes: uk_kpi_template_code (template_code, deleted) UNIQUE · idx_kpi_templates_uuid (uuid)

3.2 kpi_template_metrics

Column Type Notes
id INT(11) UNSIGNED AI PK
template_id INT(11) UNSIGNED NOT NULL kpi_templates.id
category_code VARCHAR(10) NOT NULL ATT, TSK, PRJ, TKT, BHV, LVE, PRF, TMS
metric_key VARCHAR(50) NOT NULL e.g. att_rate (see doc 02 Appendix A)
metric_label VARCHAR(100) NOT NULL
weight DECIMAL(5,2) DEFAULT 0.00 Metric weight within its category
category_weight DECIMAL(5,2) DEFAULT 0.00 Category weight for grouping
scoring_thresholds TEXT NULL JSON; defaults from config
override_thresholds TEXT NULL JSON; per-template-metric override (engine prefers this)
scoring_direction VARCHAR(10) DEFAULT 'higher' higher / lower
is_automated TINYINT(1) DEFAULT 1 1 = engine-computed, 0 = manual
data_source_model VARCHAR(100) NULL e.g. Attendance_model
data_source_method VARCHAR(100) NULL e.g. get_details
unit VARCHAR(20) DEFAULT '%' %, hours, count, score
sort_order INT(4) DEFAULT 0

Indexes: idx_ktm_template_id · idx_ktm_category_code · uk_ktm_template_metric (template_id, metric_key, deleted) UNIQUE

The engine resolves thresholds as *override_thresholds (if present) → config `kpi_metrics[].thresholds** (Kpi_calculation_engine::_get_thresholds()`).

3.3 kpi_review_cycles

Column Type Notes
id INT(11) UNSIGNED AI PK
uuid VARCHAR(36) NOT NULL
name VARCHAR(100) NOT NULL e.g. "Q1 2026 Review"
cycle_code VARCHAR(50) NOT NULL e.g. 2026-Q1
cycle_type VARCHAR(20) NOT NULL DEFAULT 'quarterly' monthly/quarterly/semi_annual/annual
template_id INT(11) UNSIGNED NOT NULL Default template for the cycle
period_start DATE NOT NULL
period_end DATE NOT NULL
review_deadline DATE NULL
status ENUM('draft','open','calculating','review','finalized','archived') DEFAULT 'draft'
auto_calculate TINYINT(1) DEFAULT 1
recommendations_generated TINYINT(1) DEFAULT 0 Salary recs produced for this cycle
recommendations_generated_at DATETIME NULL
notes TEXT NULL

Indexes: uk_kpi_cycle_code (cycle_code, deleted) UNIQUE · idx_kpi_rc_cycle_type · idx_kpi_rc_status · idx_kpi_rc_template_id · idx_kpi_rc_period (period_start, period_end) · idx_kpi_rc_uuid

Status workflow (enforced by Kpi_review_cycles_model::transition_status()):

draft → open → calculating → review → finalized → archived

3.4 kpi_employee_assignments

Column Type Notes
id INT(11) UNSIGNED AI PK
uuid VARCHAR(36) NOT NULL
cycle_id INT(11) UNSIGNED NOT NULL kpi_review_cycles.id
employee_id INT(11) NOT NULL Employee being evaluated
template_id INT(11) UNSIGNED NOT NULL kpi_templates.id
reviewer_id INT(11) NULL Primary reviewer
secondary_reviewer_id INT(11) NULL Secondary reviewer
status VARCHAR(20) NOT NULL DEFAULT 'pending' pendingcalculatedfinalized (also in_progress/submitted/reviewed)
self_assessment_score DECIMAL(4,2) NULL
self_assessment_notes TEXT NULL
reviewer_notes TEXT NULL
final_override_score DECIMAL(4,2) NULL Manual composite override
override_reason TEXT NULL

Indexes: uk_kpi_ea_cycle_employee (cycle_id, employee_id, deleted) UNIQUE · idx_kpi_ea_uuid · idx_kpi_ea_employee_id · idx_kpi_ea_template_id · idx_kpi_ea_reviewer_id · idx_kpi_ea_status

The engine sets status = 'calculated' after computing an assignment and skips assignments already finalized.

3.5 kpi_metric_scores

One row per metric per assignment. Scoped by assignment_id only (no denormalized user/cycle columns).

Column Type Notes
id INT(11) UNSIGNED AI PK
assignment_id INT(11) UNSIGNED NOT NULL kpi_employee_assignments.id
category_code VARCHAR(10) NOT NULL
metric_key VARCHAR(50) NOT NULL
raw_value DECIMAL(18,4) NULL Raw computed value (null = insufficient data)
raw_value_detail TEXT NULL JSON breakdown
normalized_score DECIMAL(4,2) NULL 1.00–5.00 (null excluded from composite)
weight DECIMAL(5,2) DEFAULT 0.00 Weight snapshot at compute time
source VARCHAR(20) NOT NULL DEFAULT 'auto' automated / manual_entry / error
computation_log TEXT NULL JSON: inputs + formula + timestamp
reviewer_adjustment DECIMAL(4,2) NULL
adjustment_reason TEXT NULL
computed_at DATETIME NULL

Indexes: uk_kpi_ms_assignment_metric (assignment_id, metric_key, deleted) UNIQUE · idx_kpi_ms_category · idx_kpi_ms_metric_key

3.6 kpi_composite_results

One row per assignment.

Column Type Notes
id INT(11) UNSIGNED AI PK
assignment_id INT(11) UNSIGNED NOT NULL kpi_employee_assignments.id
composite_score DECIMAL(4,2) NOT NULL
rating_band VARCHAR(30) NOT NULL outstandingunsatisfactory
category_scores TEXT NOT NULL JSON { "ATT":4.08, "TSK":3.75, … }
total_metrics_evaluated INT(4) DEFAULT 0
total_metrics_possible INT(4) DEFAULT 0
metrics_computed INT(4) DEFAULT 0 Non-null metrics used
metrics_total INT(4) DEFAULT 0 Metrics in template
data_sufficiency_pct DECIMAL(5,2) DEFAULT 0.00 Coverage %; ≥60 ⇒ sufficient
salary_step_recommendation VARCHAR(50) NULL Human-readable hint
salary_recommendation_id INT(11) NULL kpi_salary_recommendations.id
salary_grade_id INT(11) NULL
salary_current_step INT(4) NULL
salary_recommended_step INT(4) NULL
reviewer_final_notes TEXT NULL
status ENUM('computed','published') DEFAULT 'computed'
calculated_at DATETIME NULL

Indexes: uk_kpi_cr_assignment (assignment_id, deleted) UNIQUE · idx_kpi_cr_composite_score · idx_kpi_cr_rating_band · idx_kpi_cr_status

Note: the migration includes both total_metrics_evaluated/total_metrics_possible and metrics_computed/metrics_total. The engine currently populates metrics_computed, metrics_total, and data_sufficiency_pct; the total_metrics_* pair is reserved/legacy.

3.7 kpi_manual_entries

Manual values for non-automated metrics (task_quality fallback, ticket_satisfaction, skill_growth, training_hours, training_completion). Scoped by assignment_id.

Column Type Notes
id INT(11) UNSIGNED AI PK
assignment_id INT(11) NOT NULL kpi_employee_assignments.id
metric_key VARCHAR(50) NOT NULL
value DECIMAL(18,4) NOT NULL
unit VARCHAR(20) NOT NULL DEFAULT 'count'
notes TEXT NULL Justification
evidence_file VARCHAR(255) NULL Optional upload path
entered_by INT(11) NOT NULL
approved_by INT(11) NULL
approved_at DATETIME NULL
status VARCHAR(20) NOT NULL DEFAULT 'draft' draftsubmittedapproved/rejected
rejection_reason TEXT NULL

Indexes: uk_kpi_me_assignment_metric (assignment_id, metric_key, deleted) UNIQUE · idx_kpi_me_metric_key · idx_kpi_me_entered_by · idx_kpi_me_status

3.8 kpi_score_history

Immutable snapshots for trend analysis and audit. Scoped by employee_id + cycle_id.

Column Type Notes
id INT(11) UNSIGNED AI PK
employee_id INT(11) NOT NULL
cycle_id INT(11) UNSIGNED NOT NULL
composite_score DECIMAL(4,2) NOT NULL
rating_band VARCHAR(30) NOT NULL
category_scores LONGTEXT NOT NULL JSON full breakdown
snapshot_type VARCHAR(20) NOT NULL DEFAULT 'computed' computed/reviewed/finalized
template_snapshot TEXT NULL JSON: template weights at compute time
is_final TINYINT(1) DEFAULT 0 1 = official finalized version
snapshot_date DATETIME NULL

Indexes: idx_kpi_sh_employee_cycle (employee_id, cycle_id) · idx_kpi_sh_snapshot_type · idx_kpi_sh_is_final · idx_kpi_sh_snapshot_date

3.9 kpi_salary_recommendations

Advisory salary step recommendations generated from composite results (see 04_SALARY_INTEGRATION.md). Scoped by employee_id + cycle_id.

Column Type Notes
id INT(11) UNSIGNED AI PK
employee_id INT(11) NOT NULL
cycle_id INT(11) NOT NULL
assignment_id INT(11) NOT NULL
composite_result_id INT(11) NULL
composite_score DECIMAL(4,2) NOT NULL
rating_band VARCHAR(30) NOT NULL
current_salary DECIMAL(18,2) NOT NULL From get_current_salary()
recommended_salary DECIMAL(18,2) NOT NULL Priced via Salary_grades_model
current_step INT(11) NULL
recommended_step INT(11) NULL
step_change INT(11) DEFAULT 0
salary_change DECIMAL(18,2) DEFAULT 0.00
change_percentage DECIMAL(6,2) DEFAULT 0.00
consecutive_count INT(11) DEFAULT 0 Consecutive same-rating cycles
bonus_action VARCHAR(50) NULL e.g. grade_promotion_eligible
flags TEXT NULL JSON, e.g. ["at_grade_ceiling"]
computation_log LONGTEXT NULL
override_step INT(11) NULL HR override
override_salary DECIMAL(18,2) NULL
override_reason TEXT NULL
overridden_by INT(11) NULL
status VARCHAR(20) NOT NULL DEFAULT 'pending' pendingapproved/rejected/deferred
effective_date DATE NULL
approved_by / approved_at INT / DATETIME NULL
rejected_by / rejected_at / rejection_reason INT / DATETIME / TEXT NULL
deferred_reason / deferred_until TEXT / DATE NULL
salary_history_id INT(11) NULL Link to the salary_history row created on approval

Indexes: uk_kpi_sr_employee_cycle (employee_id, cycle_id, deleted) UNIQUE · idx_kpi_sr_user (employee_id) · idx_kpi_sr_cycle · idx_kpi_sr_status


4. Indexes & Constraints

4.1 Logical Foreign Keys (Not DB-Enforced)

Per ERPat convention, FKs are documented but enforced in application code, not via DB constraints.

Child Column References Cascade (application)
kpi_template_metrics template_id kpi_templates.id Soft-delete metrics with template
kpi_review_cycles template_id kpi_templates.id Block template delete if cycles use it
kpi_employee_assignments cycle_id kpi_review_cycles.id Block cycle delete if assignments exist
kpi_employee_assignments template_id kpi_templates.id Block template delete if assigned
kpi_metric_scores assignment_id kpi_employee_assignments.id Soft-delete with assignment
kpi_composite_results assignment_id kpi_employee_assignments.id Soft-delete with assignment
kpi_manual_entries assignment_id kpi_employee_assignments.id Soft-delete with assignment
kpi_composite_results salary_recommendation_id kpi_salary_recommendations.id Nullable link
kpi_score_history cycle_id, employee_id kpi_review_cycles.id, users Never delete (audit)
kpi_salary_recommendations assignment_id, salary_history_id assignments, salary_history Link on approval

4.2 Uniqueness Guarantees

Table Unique key Guarantees
kpi_templates (template_code, deleted) One live template per code
kpi_template_metrics (template_id, metric_key, deleted) One row per metric per template
kpi_review_cycles (cycle_code, deleted) One live cycle per code
kpi_employee_assignments (cycle_id, employee_id, deleted) One assignment per employee per cycle
kpi_metric_scores (assignment_id, metric_key, deleted) One score per metric per assignment
kpi_composite_results (assignment_id, deleted) One composite per assignment
kpi_manual_entries (assignment_id, metric_key, deleted) One manual entry per metric per assignment
kpi_salary_recommendations (employee_id, cycle_id, deleted) One recommendation per employee per cycle

5. Seed Data (Config-Driven)

Important: the migration creates tables only — it does not seed any rows. Categories, the 29 metric definitions, rating bands, step mapping, and the 5 role templates live in modules/KpiMatrix/config/kpi_templates.php. Templates and their kpi_template_metrics rows are materialized through the models when an admin creates/duplicates a template (the metric metadata — label, unit, direction, automation, default thresholds — is copied from config by Kpi_template_metrics_model::save() / Kpi_templates_model::duplicate_template()).

5.1 Default Role Templates ($config['kpi_template_weights'])

template_code Name Target role
general_staff General Staff Administrative / general office
technical_developer Technical / Developer IT / engineering
customer_facing Customer-Facing / Support Sales / support / BPO
management Management / Supervisory Supervisors / managers
operations Operations / Production Warehouse / manufacturing

Each template provides 8 category weights summing to 100 (see 02_KPI_CATEGORIES_AND_METRICS.md §13).

5.2 Metric Definitions

All 29 metrics are defined in $config['kpi_metrics'], each with category, label, unit, direction, automated, optional model/method, and a five-band thresholds map. A populated template therefore yields up to 29 kpi_template_metrics rows (fewer if a template disables some).

5.3 Initial Review Cycle (Optional, Manual)

An initial cycle can be created from the UI or via SQL; it is not auto-created by the migration:

INSERT INTO kpi_review_cycles
  (uuid, name, cycle_code, cycle_type, template_id, period_start, period_end, status, auto_calculate, created_by, date_created)
VALUES
  (UUID(), 'Annual Review 2026', '2026-annual', 'annual', 1, '2026-01-01', '2026-12-31', 'draft', 1, 1, NOW());

6. Migration Strategy

6.1 Migration File

modules/KpiMatrix/migrations/20260604185300_create_kpi_matrix_tables.php — class Migration_Create_kpi_matrix_tables. Each table is built by a private _create_*() method that early-returns if the table already exists, then uses dbforge + raw CREATE [UNIQUE] INDEX statements. As a module migration it runs via php erpat migrate:modules (also covered by php erpat migrate:latest) and is tracked in the per-module migrations_kpi_matrix table, not the core migration table.

6.2 Creation / Teardown Order

up() creates, down() drops in reverse. Both cover 9 tables:

templates → template_metrics → review_cycles → employee_assignments →
metric_scores → composite_results → manual_entries → score_history →
salary_recommendations

6.3 Post-Migration Verification

-- All 9 KPI tables present (adjust prefix if configured)
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME LIKE 'kpi_%';

-- After creating a template, expect up to 29 metric rows per template
SELECT t.template_code, COUNT(m.id) AS metric_count
FROM kpi_templates t
LEFT JOIN kpi_template_metrics m ON m.template_id = t.id AND m.deleted = 0
WHERE t.deleted = 0
GROUP BY t.template_code;

7. Data Retention & Archival

7.1 Retention Policy

Data Retention Strategy
kpi_templates / kpi_template_metrics Indefinite Soft-delete only
kpi_review_cycles 5 years active archived status afterward
kpi_employee_assignments Follows cycle Soft-delete with cycle
kpi_metric_scores 3 years detailed Optionally purge computation_log/raw_value_detail, keep score
kpi_composite_results Indefinite Feeds salary progression
kpi_manual_entries 3 years Soft-delete after archival
kpi_score_history Indefinite Immutable audit trail
kpi_salary_recommendations Indefinite Compensation audit trail

7.2 Storage Estimation (500 employees, quarterly reviews)

Table Rows/Year Notes
kpi_templates / kpi_template_metrics ~10 / ~290 Static
kpi_review_cycles 4
kpi_employee_assignments ~2,000 employees × cycles
kpi_metric_scores ~58,000 employees × ~29 metrics × cycles
kpi_composite_results ~2,000
kpi_manual_entries ~10,000 manual metrics only
kpi_score_history ~8,000 snapshots (computed + finalized)
kpi_salary_recommendations ~2,000

Order-of-magnitude: ~250 MB/year, dominated by kpi_metric_scores and kpi_score_history JSON payloads.

7.3 Performance Considerations

  1. assignment_id scoping keeps per-employee score reads tight; the unique keys double as covering indexes for the common upsert path.
  2. JSON columns (computation_log, category_scores, template_snapshot) are write-once / read-on-display — no need to query inside them.
  3. kpi_score_history.category_scores is LONGTEXT; consider application-layer compression for snapshots older than 1 year.
  4. Trend/ranking queries are served by idx_kpi_cr_composite_score, idx_kpi_sh_employee_cycle, and idx_kpi_cr_rating_band.

Appendix A — Model-to-Table Mapping

9 models, one per table, all extending Crud_model:

Model Table
Kpi_templates_model kpi_templates
Kpi_template_metrics_model kpi_template_metrics
Kpi_review_cycles_model kpi_review_cycles
Kpi_employee_assignments_model kpi_employee_assignments
Kpi_metric_scores_model kpi_metric_scores
Kpi_composite_results_model kpi_composite_results
Kpi_manual_entries_model kpi_manual_entries
Kpi_score_history_model kpi_score_history
Kpi_salary_recommendations_model kpi_salary_recommendations

Appendix B — Column Type Reference

Type Usage
DECIMAL(4,2) Scores (1.00–5.00)
DECIMAL(5,2) Weights / percentages
DECIMAL(6,2) Change percentage
DECIMAL(18,2) Salary amounts
DECIMAL(18,4) Raw metric values
VARCHAR(36) UUIDs
VARCHAR(10/20/30/50/100/255) Category codes / status & units / rating bands / keys & codes / labels & model names / file paths
TEXT / LONGTEXT JSON payloads (history breakdown uses LONGTEXT)

Next: 04_SALARY_INTEGRATION.md — how composite scores feed salary step progression.

Was this guide helpful?

Report a content problem