PT Tiaramas Ronagemilang is a licensed Indonesian manpower and recruitment agency (PPTKIS / P3MI) specializing in the sourcing, training, certification, and overseas placement of migrant workers. Operating within a highly regulated, time-sensitive industry, the agency coordinates complex, multi-stage deployment lifecycles for hundreds of candidates.

Historically, the heavy administrative burden of these operations has been managed through fragmented, decentralized methods—ranging from physical whiteboards and isolated Excel spreadsheets to informal WhatsApp threads. This lack of a single source of truth introduces severe operational vulnerabilities. Informal data tracking leads to untraceable financial advances (kasbon), while siloed document storage results in expired medical certificates or visas, ultimately leading to delayed deployments, lost revenue, and stranded candidates.

Project Chiron is a custom-engineered enterprise management system designed specifically to eliminate this operational chaos. By replacing informal workflows with a centralized, compliance-driven architecture, Chiron acts as the agency's un-bypassable Command Center.

1. Concurrency Control: Optimistic Locking

In a distributed back-office environment, multiple admins often interact with the same profile simultaneously.

• The Design: Implementation of Optimistic Concurrency Control (OCC) using a "Compare-and-Swap" mechanism via a version integer field on the Candidate model.
• Background Tasks: Celery tasks utilize a Retry-on-Conflict loop (refresh_from_db()) to respect OCC without dropping compliance mandates.
• API / UI Boundary: The version field is strictly required in all REST API serializers. A global DRF exception handler intercepts RecordModifiedError to return a 409 Conflict.
• Concurrent Verification: Verification actions bypass read-modify-write races entirely by utilizing conditional database-layer UPDATE statements chained to the RBAC queryset, mathematically preventing two admins from verifying the same document simultaneously.

2. The Immutable Financial Ledger

• The Design: An append-only FinancialTransaction model.
• DB-Level Enforcement: Immutability is enforced at the PostgreSQL level via a BEFORE UPDATE OR DELETE trigger.
• Compensating Reversals: Corrections are strictly handled by creating a counter-entry. Double-reversal race conditions are caught via DB-level IntegrityError constraints.

3. Strict State Machine Pipeline

The deployment pipeline (SOURCED → MEDICAL_CHECK → LKP_TRAINING → TUK_CERTIFICATION → DOC_PROCESSING → READY → DEPLOYED) must enforce legal compliance.

• The Design: A Finite State Machine utilizing django-fsm-2. Transitions are protected by strict Guard Conditions. Document-based guards require the document to be explicitly verified by an authorized administrator (Admin or Area Manager).

4. Idempotent, Un-bypassable Audit Trail

• The Design: Database-level PostgreSQL triggers log JSONB deltas for every mutation. A PostgresAuditMiddleware injects the User ID into the Postgres session, scoped strictly to write methods only.

5. Multi-Tenant Access Control (RBAC)

Security rules are codified centrally via Custom QuerySet Managers. A shared CandidateLinkedRBACQuerySet mixin guarantees document and ledger logic never drift out of sync.

• Admin / CEO: Global read/write access.
• Area Manager: Filtered document and financial access (target_destination).
• LKP Vendor: Filtered candidate view (assigned_lkp). Strictly denied access to documents and financials.
• Recruiter (Sponsor): Filtered candidate view (recruited_by). Read-only access to own candidates' financials; write access for initial unverified documents.

6. Temporal Document Vault, Verification & Expiry Engine

• The Design: AWS S3 / MinIO private buckets utilizing 15-minute Presigned URLs. Missing files and permission denials both raise a uniform DocumentAccessError mapped to 404 Not Found to neutralize ID-probing.
• Verification Immutability: Once an authorized admin flips a document to is_verified=True, that row becomes strictly immutable at both the model and PostgreSQL trigger level against both UPDATE and DELETE.
• Conditional Candidate Deletion: Candidates with unverified onboarding garbage can be cleanly deleted (via on_delete=CASCADE). However, the moment a candidate possesses a verified compliance document, the PostgreSQL trigger intercepts the deletion and halts the transaction.
• Automated Downgrades: A Celery task safely forces a candidate backward into DOC_PROCESSING if a critical document expires.

"Application code lies; the database does not."

When you build a system for a highly regulated, high-stakes environment like international manpower deployment, you cannot treat the backend as a simple CRUD wrapper. Standard ORMs are designed for developer velocity, not enterprise compliance. If you rely on application-level checks to enforce financial immutability or legal deployment gates, you are mathematically guaranteed to suffer a data breach or a compliance failure the moment a developer writes a raw .update() query or a race condition hits the server.

Why We Rejected Pessimistic Locking

In a modern web stack utilizing transaction-mode connection poolers like PgBouncer, pessimistic locking (SELECT ... FOR UPDATE) is catastrophic. If an admin starts a transaction and their connection drops, that DB connection remains open and the row locked, taking down the application. OCC via a version integer ensures zero connection starvation.

Pushing Immutability to PostgreSQL

ORMs offer bulk operations (e.g. .update()) that bypass Python signals and .save() methods. By placing BEFORE UPDATE OR DELETE triggers in PostgreSQL, we stripped the application layer of its authority to mutate verified compliance records or ledgers.

1. Core Schema (`chiron_core/models.py`)

2. Database Triggers (`migrations.py`)