Core ERD/Table Map#

The StudentHub backend organizes its data model around candidates, companies, contracts, work logs, payroll transfers, invoices, requests, and wallet-linked transfers. The following diagram and table map summarize the main entities and their relationships, as confirmed by models in common/models/ and migrations in console/migrations/ (docs/database/README.md):

Key Relationships#

• Candidates are linked to companies via contracts and work logs.
• Companies own requests, contracts, and transfers.
• Transfers group payroll batches per company and are split into TransferCandidate rows for each candidate.
• Invoices are generated per transfer.
• Work logs (CandidateWorkingHour, CandidateWorkingDate) track candidate attendance and are aggregated for payroll.
• WalletUser is managed in a separate wallet DB and links to transfer files and bank details.

Core Entity Reference#

Candidate#

• Primary Key: candidate_id
• Key Foreign Keys: None directly, but referenced by many tables (e.g., TransferCandidate, CandidateWorkingHour, Contract)
• Important Columns: Personal info, professional details (hourly rate, job search status), onboarding/verification flags (e.g., email/civil ID verification), status (READY=1, PENDING=0, ACTIVE=10), timestamps, soft delete flag (docs/database/README.md)
• Common Query Patterns: Used in candidate eligibility checks for payroll (see getPayableCandidateListFormat() in common/models/TransferCandidate.php)
• Status/State Machine: Onboarding/verification states are inferred from status fields and used in transfer eligibility logic (docs/database/README.md, common/models/TransferCandidate.php)

Company#

• Primary Key: company_id
• Key Foreign Keys: None directly, but referenced by Request, Transfer, Contract, etc.
• Important Columns: Company details, billing info, parent company, timestamps, soft delete flag (docs/database/README.md)
• Common Query Patterns: Used in transfer and request ownership, payroll splits.

Request#

• Primary Key: request_id
• Key Foreign Keys: company_id
• Important Columns: Job details, status, timeline, budget, payment terms (docs/database/README.md)
• Common Query Patterns: Used for candidate matching and job posting.

Contract#

• Primary Key: contract_uuid
• Key Foreign Keys: company_id, candidate_id
• Important Columns: Contract type, rates, start/end dates, status, timestamps (common/models/Contract.php - inferred)
• Common Query Patterns: Used in payroll calculations and candidate eligibility (common/models/TransferCandidate.php)

CandidateWorkingHour#

• Primary Key: candidate_working_hour_uuid
• Key Foreign Keys: candidate_id, store_id, cwlf_uuid
• Important Columns: date, start_time, end_time, total_time, status (0=pending, 1=approved, 2=rejected), via, timestamps (common/models/CandidateWorkingHour.php)
• Common Query Patterns: Filtering by status, summing total_time, updating stats in afterSave/afterDelete
• Status/State Machine: Status transitions are managed in updateStats() and afterSave (common/models/CandidateWorkingHour.php)

CandidateWorkingDate#

• Primary Key: Composite (candidate_id, store_id, date)
• Key Foreign Keys: candidate_id, store_id
• Important Columns: total_time, status, start_time, end_time, total_approved, total_rejected, total_pending, timestamps (common/models/CandidateWorkingHour.php - see updateStats)
• Common Query Patterns: Updated automatically by CandidateWorkingHour actions

Transfer#

• Primary Key: transfer_id
• Key Foreign Keys: company_id, parent_transfer_id, contract_uuid
• Important Columns: total, company_total, transfer_cost, currency_code, transfer_status (0=Cancel, 10=Draft, 5=Locked, 1=Payment Sent, 3=Received & Distributing Salary, 4=Transfer Completed), payment_received_on, timestamps, soft delete flag (common/models/Transfer.php)
• Common Query Patterns: filterPaymentReceived(), filterParent(), payable() (common/models/Transfer.php)
• Status/State Machine: Transitions enforced in lock(), paymentSent(), cancel() (common/models/Transfer.php)

TransferCandidate#

• Primary Key: tc_id
• Key Foreign Keys: transfer_id, candidate_id, company_id, store_id, bank_id, transfer_file_id, contract_uuid
• Important Columns: paid (0=unpaid, 1=paid), candidate_hourly_rate, company_hourly_rate, hours, minutes, seconds, bonus, bonus_commission, transfer_cost, candidate_total, company_total, currency_code, timestamps, soft delete flag (common/models/TransferCandidate.php)
• Common Query Patterns: payable(), havingBankInfo(), activeCivilId(), used in getPayableCandidateListFormat() (common/models/TransferCandidate.php)
• Status/State Machine: paid transitions trigger notifications and stats updates in afterSave (common/models/TransferCandidate.php)

Invoice#

• Primary Key: invoice_id
• Key Foreign Keys: transfer_id
• Important Columns: invoice_date, invoice_status (unpaid, paid), deleted (common/models/Invoice.php)
• Common Query Patterns: unpaidAlert() checks for missing payments (common/models/Invoice.php)
• Status/State Machine: Invoice status is updated by transfer actions and invoice generation

TransferFile#

• Primary Key: transfer_file_id
• Key Foreign Keys: None directly, but referenced by TransferCandidate
• Important Columns: File metadata, timestamps (inferred from usage in TransferCandidate and Transfer)

WalletUser#

• Primary Key: user_uuid
• Important Columns: username, email, status (0=DELETED, 9=INACTIVE, 10=ACTIVE), iban, bank_uuid, timestamps (common/models/WalletUser.php)
• Common Query Patterns: findByUsername(), findByEmail(), findByVerificationToken(), used for authentication and wallet management
• Status/State Machine: Status transitions for activation and verification (common/models/WalletUser.php)

Status/State Machines and Transition Rules#

• Work Logs: CandidateWorkingHour uses status (0=pending, 1=approved, 2=rejected). Transitions are managed in updateStats() and afterSave/afterDelete, which update CandidateWorkingDate aggregates (common/models/CandidateWorkingHour.php).
• Transfers: Transfer status transitions (INITIATED → LOCK → PAYMENT_SENT → TRANSFER_COMPLETE, with CANCEL as terminal) are enforced in lock(), paymentSent(), cancel() (common/models/Transfer.php).
• Candidate Onboarding/Verification: Status and verification flags are checked in payroll eligibility logic (docs/database/README.md, common/models/TransferCandidate.php).
• Invoice/Receipt: Invoice status (unpaid, paid) is updated by transfer actions and checked in unpaidAlert() (common/models/Invoice.php).

Business Rules Affecting Calculations#

• Hours Totals: CandidateWorkingHour::updateStats() sums total_time for sessions with end_time IS NOT NULL, updating CandidateWorkingDate aggregates (common/models/CandidateWorkingHour.php).
• Transfer Totals: TransferCandidate::saveCandidateTransfer() calculates candidate_total and company_total using contract/company rates, hours, minutes, seconds, bonuses, and commissions, rounding to 3 decimals. Transfer costs are added to company totals. Transfer::saveTransfer() accumulates these for the transfer (common/models/TransferCandidate.php, common/models/Transfer.php).
• Fees/Rounding: Rounding is performed in TransferCandidate::saveCandidateTransfer() using round(..., 3) (common/models/TransferCandidate.php).

Migrations & Schema Change Workflow#

• Directory: All migrations are in console/migrations/ (e.g., m240618_144001_candidate_work_log.php for work logs, m240620_143332_candidate_working_date.php for daily aggregates).
• Running Migrations: Use the standard Yii command from the repo root:
  ⧉

  ./yii migrate --migrationPath=@console/migrations
  

  For production, confirm with the operations team if additional flags (e.g., --interactive=0) or environment-specific configs are required (Unknown/Verify).
• Constraints & Data Backfills: Unique keys, soft delete markers, and data backfills are typically handled in the migration scripts that precede each model addition ([inference from migration naming and model usage]).

Top 10 Tables to Know#

1. candidate
2. company
3. request
4. transfer
5. transfer_candidate
6. invoice
7. candidate_working_hour
8. candidate_working_date
9. wallet_user
10. contract

All include timestamps and soft delete fields (docs/database/README.md).

Debugging by Table#

• Transfer missing candidate totals or invoices not generating: Check transfer_candidate (common/models/TransferCandidate.php), transfer (common/models/Transfer.php), and invoice (common/models/Invoice.php).
• Candidate hours show zero: Inspect candidate_working_hour (common/models/CandidateWorkingHour.php) and candidate_working_date aggregates.
• Candidate cannot be paid: Confirm onboarding flags and bank info checks in TransferCandidate::getPayableCandidateListFormat() (common/models/TransferCandidate.php).
• Wallet transfer bank details not updating: Check wallet_user, which syncs bank info to WalletTransfer on IBAN/bank changes (common/models/WalletUser.php).

Additional Notes#

• Most entities include _created_at, _updated_at, and a deleted flag for soft deletion (docs/database/README.md).
• Multi-language support, location tracking, and audit logging are present for key features.
• Token-based authentication and hierarchical permissions are used for user and staff access control.

If a relationship or status is inferred, it is labeled as such and supported by code references. Unknowns are marked where confirmation is not possible.