Module: KPI Matrix Status: Implemented — reconciled to migration
modules/KpiMatrix/migrations/20260604185300_create_kpi_matrix_tables.phpLast 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
- Schema Overview
- Entity-Relationship Diagram
- Table Definitions
- Indexes & Constraints
- Seed Data (Config-Driven)
- Migration Strategy
- 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, andkpi_manual_entriesare scoped byassignment_id(not denormalizeduser_id/cycle_id).kpi_score_historyandkpi_salary_recommendationsare scoped byemployee_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' | pending→calculated→finalized (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 alreadyfinalized.
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 | outstanding … unsatisfactory |
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_possibleandmetrics_computed/metrics_total. The engine currently populatesmetrics_computed,metrics_total, anddata_sufficiency_pct; thetotal_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' | draft→submitted→approved/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' | pending→approved/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 theirkpi_template_metricsrows 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 byKpi_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
assignment_idscoping keeps per-employee score reads tight; the unique keys double as covering indexes for the common upsert path.- JSON columns (
computation_log,category_scores,template_snapshot) are write-once / read-on-display — no need to query inside them. kpi_score_history.category_scoresisLONGTEXT; consider application-layer compression for snapshots older than 1 year.- Trend/ranking queries are served by
idx_kpi_cr_composite_score,idx_kpi_sh_employee_cycle, andidx_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.