Skip to main content

Entity-Relationship Diagrams

The database schema, drawn from the Flyway migration SQL in backend/src/main/resources/db/migration/ (column and key names verified against the DDL — e.g. the employee PK really is employee_number, and timesheet.employee_id references it). How the schema is owned and changed is covered in backend/entities-and-migrations.md; what the payroll tables mean is in business-rules.md.

91 tables total. This page diagrams the payroll core, the statutory rate tables, and the recruitment chain; the remaining domains are summarized in §4.

1. Payroll core​

Everything the payroll engine reads or writes, plus the RBAC tables that gate it. Singular table names are as-is from the DDL (users is plural only to dodge the SQL reserved word). payroll_settings is a single-row policy table (see business-rules.md) with no FKs, shown here because every generation run reads it.

Reading notes, straight from the DDL:

  • employee.employee_number is the PK (V2); every HR/payroll table's employee_id (or employee_number in bonus/employee_deductions) references it. The four government ID columns are each UNIQUE — which is why the service layer pre-checks them to return a friendly 409 (backend/README.md §2).
  • RBAC (V1): users.role_id is the primary role, user_role adds extra roles, and role.parent_role_id forms a hierarchy whose permissions are unioned at login. users.failed_login_attempts/locked_until were added by V75 alongside refresh_tokens.
  • payslip is a snapshot, not a join: it denormalizes employee name, position, department, government IDs and every computed amount at generation time, so historical payslips survive later employee edits. payslip_deduction_items.employee_deduction_id is nullable (ON DELETE SET NULL) for the same reason — the label/amount snapshot outlives the assignment (V70).
  • Not drawn but in the same domain: payroll_transactions, payroll_changes and payslip_history (append-only audit tables hanging off payroll/payslip, V4), ph_holidays, work_suspensions, de_minimis_benefit_types, company_profile, government_filing, and the user_log audit trail written by AuditInterceptor.

2. Statutory rate tables — the effective-date cohort pattern​

Four standalone reference tables carry the government schedules. None has an FK; the payroll engine joins them by salary range and date: a lookup takes the bracket whose [salary_bracket_from, salary_bracket_to] contains the monthly salary, from the cohort with the greatest effective_date not after the payroll period end (findBracketAsOf, with an earliest-cohort fallback for historical periods). Rates are never UPDATEd in place — a new statutory year is a new set of rows with a new effective_date.

  • sss_contribution_rates got its official 2026 shape (per-bracket MSC and Regular/EC/MPF share breakdown, 61 brackets) in V45.
  • In philhealth_/pagibig_contribution_rates, a share value < 10 is a percentage of salary, otherwise a fixed peso amount — that's how the fixed floor/ceiling brackets and the percentage middle band coexist in one table (V12, interpreted in PayrollServiceImpl.calculatePhilhealthDeduction).
  • withholding_tax_brackets additionally cohorts by pay_period (DAILY/WEEKLY/SEMI_MONTHLY/MONTHLY/ANNUAL) since V63; tax = base_tax + rate_on_excess% × (income − taxable_income_from).

The actual 2026 numbers live in business-rules.md §5–6.

3. Recruitment chain​

From V14 (plus the 1:1 resume table from V77). FKs to department/position/employee exist on most of these (requester, interviewer, referrer, offer creator) but are omitted here for readability.

The functional flow (requisition → opening → applicant → offer → hire) is documented in modules/recruitment.md.

4. The other domains (counts only)​

Not diagrammed — their structure is conventional and the DDL is short:

  • CRM — 9 tables (V15, V89): crm_organization, crm_contact, crm_lead, crm_deal, crm_activity, crm_note, plus the settings layer — crm_picklist_item (pipeline stages / lead sources / activity types), crm_settings (singleton), and crm_settings_changes (field-level audit).
  • Inventory / warehouse — 19 tables (V17–V34): items, categories, suppliers, warehouses + location hierarchy, per-warehouse and per-store stock, stock transactions, purchase receiving, serial/lot tracking, transfers, pricing + price history, orders and costing.
  • Customer portal — 4 tables (V36, V37): portal_user (its own auth table — portal accounts are not ERP users), customer_order, customer_order_item, shipment_tracking.
  • Billing — 2 tables (V52): single-row billing_subscription + billing_webhook_event ledger (backend/billing.md).
  • Communications — 7 tables (V7): conversation, messages, message_attachments, message_folders, message_status, notifications, announcement.

The complete per-domain table inventory is in backend/entities-and-migrations.md §3.

5. Notable indexes​

Spot-checked CREATE INDEX statements worth knowing about:

  • Grid filter indexes (V41): the AG Grid server-side datasources filter/sort on non-FK columns, so the unbounded-growth tables got targeted indexes — timesheet(status), timesheet(work_date), payroll_transactions(transaction_date)/(transaction_type), user_log(log_date_time), and employee(last_name/first_name/status/date_hired).
  • Chat cursor pagination (V25): messages(conversation_id, message_id DESC) — matches the "newest page first, walk backwards by id" query exactly.
  • Rate cohort lookups: every statutory table indexes effective_date; withholding_tax_brackets adds (pay_period, effective_date) (V63).
  • Uniqueness as business rules: ph_holidays (holiday_date, holiday_name) unique (V56) makes holiday seeding idempotent; billing_subscription (provider, provider_subscription_id) unique (V52) makes webhook application idempotent; the four government-ID uniques on employee back the 409 pre-checks.
  • Auth: refresh_tokens(user_id) and refresh_tokens(family_id) (V75) — family revocation on token-reuse detection is one indexed scan.
  • Per-user features: saved_view(user_id, entity_key) (V82); employee_deductions(employee_number, active) (V69) for the "applicable deductions" lookup during payslip generation.