Entities and Migrations

How the database schema is owned, organized, and changed. Diagrams of the tables themselves are in ../erd.md; the request-time view of the data layer is in README.md.

1. Who owns the schema

Flyway owns it, entirely. Hibernate runs with ddl-auto: validate (application.yml) — it never creates or alters anything, and at boot it checks every @Entity in model/ against the actual schema. If an entity and the migrations disagree, the backend refuses to start. That's a feature: drift is caught on the first boot, not on the first query.

jpa:
  hibernate:
    ddl-auto: validate
flyway:
  enabled: true
  locations: classpath:db/migration
  baseline-on-migrate: true
  out-of-order: true

• out-of-order: true — feature branches each take a migration number, and branches merge in whatever order review finishes. Without this flag, merging a branch whose V83 was applied before another branch's V82 would make Flyway refuse the "older" migration. With it, Flyway applies late-arriving lower versions instead of failing.
• baseline-on-migrate: true — lets Flyway adopt a database that already has content but no Flyway history table, instead of erroring.

2. The migration set

backend/src/main/resources/db/migration/ holds 87 files spanning versions V1–V88 — V26 was never shipped, so that number is simply unused (don't reuse it; take the next free number at the top, see §5). Together they create 91 tables, plus a handful of SQL reporting views (V13, V22, V65, V87) and all seed/reference data.

The backbone (selected versions)

Version	What it laid down
V1__core_rbac.sql	department, position, hierarchical role, permission, role_permission, users, user_role
V2__employee.sql	employee (PK employee_number) + the deferred users → employee FK
V3__attendance_leave.sql	timesheet, leave_request_type, leave_request, leave_balance, overtime_request
V4__payroll_core.sql	payroll, payslip, payroll_transactions, payroll_approval, payroll_changes, payslip_history, bonus
V5__contributions_compliance.sql	sss/philhealth/pagibig_contribution_rates, tin_compliance
V12__seed_2026_contribution_rates.sql	2026 PhilHealth/Pag-IBIG schedules (and the SSS seed later replaced by V45)
V14__recruitment.sql	The full recruitment chain (8 tables)
V45__sss_2026_official_table.sql	Drop-and-recreate of sss_contribution_rates in the official 2026 shape (61 brackets, MSC/EC/MPF share breakdown)
V52__billing.sql	billing_subscription (single-row), billing_webhook_event (billing.md)
V53__payroll_settings.sql	The payroll_settings singleton — extended by V55/V56/V60/V62/V63/V72/… into the ~60-field policy engine described in ../business-rules.md
V56__holiday_calendar_and_premium_multipliers.sql	ph_holidays calendar + the premium-pay multiplier matrix + employee.rest_day_of_week
V57__de_minimis_benefit_types.sql	De-minimis benefit ceilings (RR 29-2025)
V59__ewt_registry.sql	ewt_payees, ewt_transactions (expanded withholding tax)
V63__withholding_tax_engine.sql	Per-pay-period BIR tables (2018 + 2023 TRAIN cohorts) and year-end-adjustment support
V75__auth_hardening.sql	refresh_tokens, users.failed_login_attempts / locked_until
V82__saved_view.sql / V83__user_nav_preference.sql	Per-user saved grid views and navigation preferences

3. Entity domain map (91 tables)

Grouped the way the code groups them (class-name prefixes in model//service/). Counts add up to 91.

Domain	Tables	#
Org & RBAC (V1)	department, position, role (self-referencing hierarchy), permission, role_permission, users, user_role	7
Audit & auth	user_log (V8), refresh_tokens (V75), saved_view (V82), user_nav_preference (V83)	4
Employee master (V2)	employee	1
Attendance & leave (V3)	timesheet, leave_request_type, leave_request, leave_balance, overtime_request	5
Reimbursement (V6)	reimbursement_requests, reimbursement_transactions	2
Payroll core (V4)	payroll, payslip, payroll_transactions, payroll_approval, payroll_changes, payslip_history, bonus	7
Payroll config & compliance	payroll_settings (V53), payroll_settings_changes (V73), payslip_template_settings (V66), company_profile (V44), government_filing (V54), ph_holidays (V56), work_suspensions (V74), de_minimis_benefit_types (V57), deduction_types (V68), employee_deductions (V69), payslip_deduction_items (V70)	11
Statutory rate tables	sss_contribution_rates (V5→V45), philhealth_contribution_rates, pagibig_contribution_rates (V5), withholding_tax_brackets (V46/V63), tin_compliance (V5)	5
Expanded withholding tax (V59)	ewt_payees, ewt_transactions	2
Recruitment (V14, V77)	job_requisition, staffing_plan, staffing_plan_item, job_opening, job_applicant, job_applicant_resume, interview_schedule, interview_feedback, job_offer	9
CRM (V15)	crm_organization, crm_contact, crm_lead, crm_deal, crm_activity, crm_note	6
Inventory / warehouse (V17–V34)	supplier, warehouse, warehouse_location, item_category, inventory_item, inventory_stock, stock_transaction, purchase_receiving, purchase_receiving_item, item_serial, inventory_lot, warehouse_transfer, item_price, item_price_history, store, store_inventory, inventory_order, order_stage, profit_costing_method	19
Customer portal (V36–V37)	portal_user, customer_order, customer_order_item, shipment_tracking	4
Billing (V52)	billing_subscription, billing_webhook_event	2
Communications (V7)	conversation, messages, message_attachments, message_folders, message_status, notifications, announcement	7

Roughly 85 of these are mapped as JPA @Entity classes in model/; a few (pure seed/reference or view-backed data) are only touched via native queries.

4. Seed layering: dev vs demo

Two layers of seed data exist, and they live in different places:

1. In-repo seed migrations (part of the normal classpath set): V9–V11 seed lookup data, RBAC roles/permissions and a small demo dataset; V19 seeds inventory demo data. Every stack gets these. On top of them, the runtime DemoDataSeeder (an ApplicationRunner, see ../architecture.md §6) generates transactional demo data through the real payroll engine.
2. Demo-stack rich seeds — demo/seed/ contains V20–V24 "rich seed" files (86 extra employees, inventory, CRM, HR history, recruitment). They are not on the classpath; the demo compose file mounts the directory read-only and extends the Flyway search path via SPRING_FLYWAY_LOCATIONS: classpath:db/migration,filesystem:/demo-seed (docker-compose.demo.yml), so only the demo stack ever applies them.

Caution — version collision. The rich-seed files are numbered V20–V24, and the classpath now also has V20–V24 (V20__serial_lot.sql … V24__warehouse_transfer_rbac.sql, added later by the warehouse module). Flyway refuses to run when two migrations share a version number, so a fresh demo stack booting with both locations will fail with "Found more than one migration with version 20" until the seed files are renumbered into a free range. Keep this in mind before adding anything else under demo/seed/.

5. How to add a migration safely

1. Take the next free version number — currently V89 (check ls backend/src/main/resources/db/migration/ | sort -t V -k2 -n | tail first; parallel branches may have claimed it). Name it V<N>__short_snake_case_description.sql. Don't backfill the historical V26 gap — a fresh number keeps the history readable.
2. Never edit a migration that has been applied anywhere — Flyway stores a checksum per applied file, and editing one makes every existing database (dev machines, the demo VPS, every client stack) fail validation at boot. Fix mistakes with a new migration. Editing is only acceptable while your migration has never left your own branch/database (then: docker compose down -v and remigrate).
3. Write the migration first, then the entity. ddl-auto: validate means a mismatch between @Entity and schema fails startup — a very fast feedback loop. Column types matter (see V61__integer_column_types.sql for an example of paying that debt).
4. Adding a permission? Update the frontend too. Migrations that INSERT INTO permission ... (e.g. V46, V68) must be mirrored in frontend/src/constants/role-permissions.ts, which drives the role-management UI and route guards. Forgetting this is a recurring gotcha — see ../troubleshooting.md.
5. Reference data belongs in migrations, code defaults in the entity. Statutory rate tables are seeded by migration with an effective_date cohort (never UPDATEd in place — add a new cohort; see ../erd.md §3), while payroll_settings defaults live as field initializers in PayrollSettings.java and as SQL DEFAULTs, kept in sync by hand.
6. Verify locally with docker compose up -d --build (applies the new migration on a real Postgres 16) and watch the backend log for the Flyway summary + Hibernate validation passing.