Constraints

21 Foreign Key Constraints

Constraint Name Child Column Parent Column Delete Rule
fk_task_updated_by_company
task.updated_by
task.company_id
user_account.user_id
user_account.company_id
Restrict delete
fk_task_worker_company
task.worker_id
task.company_id
worker.worker_id
worker.company_id
Restrict delete
fk_task_created_by_company
task.created_by
task.company_id
user_account.user_id
user_account.company_id
Restrict delete
fk_task_company
task.company_id
company.company_id
Restrict delete
fk_task_transition_task_company
task_transition_history.task_id
task_transition_history.company_id
task.task_id
task.company_id
Restrict delete
fk_task_transition_actor_company
task_transition_history.actor_id
task_transition_history.company_id
user_account.user_id
user_account.company_id
Restrict delete
fk_worker_document_worker
worker_document.worker_id
worker_document.company_id
worker.worker_id
worker.company_id
Restrict delete
fk_audit_event_company
audit_event.company_id
company.company_id
Restrict delete
fk_refresh_token_replacement_family
refresh_token.replaced_by_token_id
refresh_token.token_family_id
refresh_token.refresh_token_id
refresh_token.token_family_id
Restrict delete
fk_refresh_token_user_company
refresh_token.user_id
refresh_token.company_id
user_account.user_id
user_account.company_id
Restrict delete
fk_worker_company
worker.company_id
company.company_id
Restrict delete
fk_task_evidence_actor_company
task_evidence.recorded_by
task_evidence.company_id
user_account.user_id
user_account.company_id
Restrict delete
fk_task_evidence_task_company
task_evidence.task_id
task_evidence.company_id
task.task_id
task.company_id
Restrict delete
fk_task_checklist_task_company
task_checklist_item.task_id
task_checklist_item.company_id
task.task_id
task.company_id
Cascade on delete
fk_task_checklist_actor_company
task_checklist_item.completed_by
task_checklist_item.company_id
user_account.user_id
user_account.company_id
Restrict delete
fk_approval_request_task_company
approval_request.task_id
approval_request.company_id
task.task_id
task.company_id
Restrict delete
fk_approval_request_decider_company
approval_request.decided_by
approval_request.company_id
user_account.user_id
user_account.company_id
Restrict delete
fk_approval_request_requester_company
approval_request.requested_by
approval_request.company_id
user_account.user_id
user_account.company_id
Restrict delete
fk_external_submission_actor_company
external_submission.submitted_by
external_submission.company_id
user_account.user_id
user_account.company_id
Restrict delete
fk_external_submission_task_company
external_submission.task_id
external_submission.company_id
task.task_id
task.company_id
Restrict delete
fk_user_account_company
user_account.company_id
company.company_id
Restrict delete

Check Constraints

Table Constraint Name Constraint
task ck_task_business_data_not_blank (char_length(TRIM(BOTH FROM business_data_json)) > 0)
task ck_task_catalog_version_not_blank (char_length(TRIM(BOTH FROM workflow_catalog_version)) > 0)
task ck_task_content_revision (content_revision >= 0)
task ck_task_fingerprint_length (char_length((critical_fingerprint)::text) = 64)
task ck_task_fingerprint_lowercase ((critical_fingerprint)::text = lower((critical_fingerprint)::text))
task ck_task_source ((source)::text = ANY ((ARRAY['MANUAL'::character varying, 'SYSTEM_DDAY'::character varying, 'AI_CANDIDATE'::character varying])::text[]))
task ck_task_status ((status)::text = ANY ((ARRAY['DRAFT'::character varying, 'NEEDS_INFO'::character varying, 'READY_FOR_REVIEW'::character varying, 'APPROVED'::character varying, 'WAITING_WORKER'::character varying, 'WAITING_EXTERNAL'::character varying, 'COMPLETED'::character varying, 'CANCELLED'::character varying])::text[]))
task ck_task_title_not_blank (char_length(TRIM(BOTH FROM title)) > 0)
task ck_task_type ((task_type)::text = ANY ((ARRAY['RECONTRACT'::character varying, 'EMPLOYMENT_PERIOD_EXTENSION'::character varying, 'STAY_PERIOD_EXTENSION'::character varying])::text[]))
task ck_task_updated_at (updated_at >= created_at)
task ck_task_version (version >= 0)
task ck_task_workflow_id_not_blank (char_length(TRIM(BOTH FROM workflow_id)) > 0)
task_transition_history ck_task_transition_changed ((from_status)::text <> (to_status)::text)
task_transition_history ck_task_transition_from_status ((from_status)::text = ANY ((ARRAY['DRAFT'::character varying, 'NEEDS_INFO'::character varying, 'READY_FOR_REVIEW'::character varying, 'APPROVED'::character varying, 'WAITING_WORKER'::character varying, 'WAITING_EXTERNAL'::character varying, 'COMPLETED'::character varying, 'CANCELLED'::character varying])::text[]))
task_transition_history ck_task_transition_request_not_blank (char_length(TRIM(BOTH FROM request_id)) > 0)
task_transition_history ck_task_transition_to_status ((to_status)::text = ANY ((ARRAY['DRAFT'::character varying, 'NEEDS_INFO'::character varying, 'READY_FOR_REVIEW'::character varying, 'APPROVED'::character varying, 'WAITING_WORKER'::character varying, 'WAITING_EXTERNAL'::character varying, 'COMPLETED'::character varying, 'CANCELLED'::character varying])::text[]))
worker_document ck_worker_document_submission_status ((submission_status)::text = ANY ((ARRAY['MISSING'::character varying, 'SUBMITTED'::character varying, 'VERIFIED'::character varying])::text[]))
worker_document ck_worker_document_type ((document_type)::text = ANY ((ARRAY['PASSPORT_COPY'::character varying, 'ARC'::character varying, 'CONTRACT'::character varying, 'PERMIT'::character varying])::text[]))
worker_document ck_worker_document_updated_at (updated_at >= created_at)
worker_document ck_worker_document_version (version >= 0)
audit_event ck_audit_event_action_not_blank (char_length(TRIM(BOTH FROM action)) > 0)
audit_event ck_audit_event_actor_type ((actor_type)::text = ANY ((ARRAY['HR_USER'::character varying, 'WORKER_LINK'::character varying, 'AI_AGENT'::character varying, 'SYSTEM_RULE'::character varying])::text[]))
audit_event ck_audit_event_request_not_blank (char_length(TRIM(BOTH FROM request_id)) > 0)
audit_event ck_audit_event_summary_not_blank (char_length(TRIM(BOTH FROM change_summary)) > 0)
audit_event ck_audit_event_target_type_not_blank (char_length(TRIM(BOTH FROM target_type)) > 0)
audit_event ck_audit_event_user_role ((user_role IS NULL) OR ((user_role)::text = ANY ((ARRAY['ADMIN'::character varying, 'HR'::character varying, 'VIEWER'::character varying])::text[])))
audit_event ck_audit_event_version_not_blank (char_length(TRIM(BOTH FROM event_version)) > 0)
refresh_token ck_refresh_token_expiry (expires_at > created_at)
refresh_token ck_refresh_token_hash_length (char_length((token_hash)::text) = 64)
refresh_token ck_refresh_token_hash_lowercase ((token_hash)::text = lower((token_hash)::text))
refresh_token ck_refresh_token_replacement ((replaced_by_token_id IS NULL) OR (replaced_by_token_id <> refresh_token_id))
refresh_token ck_refresh_token_replacement_used ((replaced_by_token_id IS NULL) OR (used_at IS NOT NULL))
refresh_token ck_refresh_token_revoked_at ((revoked_at IS NULL) OR (revoked_at >= created_at))
refresh_token ck_refresh_token_updated_at ((updated_at >= created_at) AND ((used_at IS NULL) OR (updated_at >= used_at)) AND ((revoked_at IS NULL) OR (updated_at >= revoked_at)))
refresh_token ck_refresh_token_used_at ((used_at IS NULL) OR (used_at >= created_at))
refresh_token ck_refresh_token_version (version >= 0)
worker ck_worker_contract_period ((contract_start_date IS NULL) OR (contract_end_date IS NULL) OR (contract_end_date >= contract_start_date))
worker ck_worker_display_name_not_blank (char_length(TRIM(BOTH FROM display_name)) > 0)
worker ck_worker_updated_at (updated_at >= created_at)
worker ck_worker_version (version >= 0)
worker ck_worker_work_status ((work_status)::text = ANY ((ARRAY['ACTIVE'::character varying, 'ON_LEAVE'::character varying, 'RESIGNED'::character varying, 'TERMINATED'::character varying])::text[]))
company ck_company_name_not_blank (char_length(TRIM(BOTH FROM name)) > 0)
company ck_company_status ((status)::text = ANY ((ARRAY['ACTIVE'::character varying, 'SUSPENDED'::character varying, 'DISABLED'::character varying])::text[]))
company ck_company_updated_at (updated_at >= created_at)
company ck_company_version (version >= 0)
task_evidence ck_task_evidence_content (((file_reference IS NOT NULL) AND (char_length(TRIM(BOTH FROM file_reference)) > 0)) OR ((note IS NOT NULL) AND (char_length(TRIM(BOTH FROM note)) > 0)))
task_evidence ck_task_evidence_created_at (created_at >= recorded_at)
task_evidence ck_task_evidence_type ((evidence_type)::text = ANY ((ARRAY['DOCUMENT'::character varying, 'RECEIPT'::character varying, 'OFFICIAL_RESULT'::character varying, 'HR_CONFIRMATION'::character varying])::text[]))
task_checklist_item ck_task_checklist_code_not_blank (char_length(TRIM(BOTH FROM item_code)) > 0)
task_checklist_item ck_task_checklist_completion (((completed = false) AND (completed_by IS NULL) AND (completed_at IS NULL)) OR ((completed = true) AND (completed_by IS NOT NULL) AND (completed_at IS NOT NULL)))
task_checklist_item ck_task_checklist_label_not_blank (char_length(TRIM(BOTH FROM label)) > 0)
task_checklist_item ck_task_checklist_updated_at (updated_at >= created_at)
task_checklist_item ck_task_checklist_version (version >= 0)
approval_request ck_approval_request_approved_task_version (((status)::text <> 'APPROVED'::text) OR (approved_task_version IS NOT NULL))
approval_request ck_approval_request_approved_version ((approved_task_version IS NULL) OR (approved_task_version >= target_task_version))
approval_request ck_approval_request_changed_fields_not_blank (char_length(TRIM(BOTH FROM changed_fields_json)) > 0)
approval_request ck_approval_request_decision ((((status)::text = 'PENDING'::text) AND (decided_by IS NULL) AND (decided_at IS NULL) AND (approved_task_version IS NULL) AND (invalidated_at IS NULL)) OR (((status)::text = ANY ((ARRAY['APPROVED'::character varying, 'REJECTED'::character varying])::text[])) AND (decided_by IS NOT NULL) AND (decided_at IS NOT NULL) AND (invalidated_at IS NULL)) OR (((status)::text = 'INVALIDATED'::text) AND (invalidated_at IS NOT NULL)))
approval_request ck_approval_request_fingerprint_length (char_length((target_fingerprint)::text) = 64)
approval_request ck_approval_request_fingerprint_lowercase ((target_fingerprint)::text = lower((target_fingerprint)::text))
approval_request ck_approval_request_hr_snapshot_not_blank (char_length(TRIM(BOTH FROM hr_snapshot_json)) > 0)
approval_request ck_approval_request_source_versions_not_blank (char_length(TRIM(BOTH FROM source_versions_json)) > 0)
approval_request ck_approval_request_status ((status)::text = ANY ((ARRAY['PENDING'::character varying, 'APPROVED'::character varying, 'REJECTED'::character varying, 'INVALIDATED'::character varying])::text[]))
approval_request ck_approval_request_target_revision (target_content_revision >= 0)
approval_request ck_approval_request_target_version (target_task_version >= 0)
approval_request ck_approval_request_updated_at (updated_at >= created_at)
approval_request ck_approval_request_version (version >= 0)
external_submission ck_external_submission_created_at (created_at >= submitted_at)
external_submission ck_external_submission_destination_not_blank (char_length(TRIM(BOTH FROM destination)) > 0)
external_submission ck_external_submission_reference_not_blank (char_length(TRIM(BOTH FROM safe_reference)) > 0)
user_account ck_user_account_display_name_not_blank (char_length(TRIM(BOTH FROM display_name)) > 0)
user_account ck_user_account_email_not_blank (char_length(TRIM(BOTH FROM email)) > 0)
user_account ck_user_account_normalized_email ((normalized_email)::text = lower(TRIM(BOTH FROM email)))
user_account ck_user_account_normalized_email_not_blank (char_length(TRIM(BOTH FROM normalized_email)) > 0)
user_account ck_user_account_password_hash_not_blank (char_length(TRIM(BOTH FROM password_hash)) > 0)
user_account ck_user_account_role ((role)::text = ANY ((ARRAY['ADMIN'::character varying, 'HR'::character varying, 'VIEWER'::character varying])::text[]))
user_account ck_user_account_status ((status)::text = ANY ((ARRAY['ACTIVE'::character varying, 'SUSPENDED'::character varying, 'DISABLED'::character varying])::text[]))
user_account ck_user_account_updated_at (updated_at >= created_at)
user_account ck_user_account_version (version >= 0)